MySQL的基本使用
数据库的介绍
数据库管理系统:
Mysql,oracle,sqlite,access,sql,server等
数据库服务器:
数据库管理系统运行的机器,就是数据库服务器.
数据库分为两种,
有关系型和非关系型
Redis
Celery
MySQL的初始
1.登录mysql
mysql -u root -p
远程登录
mysql -u root -p -h106.52.85.190 -P端口
# 注意使用命令远程链接mysql，必须使用的地方有mysql
mysql -hIP -P端口 -uroot -p密码
2.修改密码
mysqladmin -uroot -p123 password "新密码"
mysqladmin -uroot -p password "新密码"
3.忘记密码,破解(linux)
# 1. 修改配置文件(linux命令行)
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
# 在[mysqld] 下加上skip-grant-tables
# 2. 重启mysql服务(linux命令行)
systemctl restart mysql
# 3.登录root管理用户,不需要输入密码(linux命令行)
mysql -u root -p # 直接回车,即可进入mysql命令
# 4. 设置新密码(mysql命令行)
mysql> update mysql.user set authentication_string=password('root123') where user="root"
and host="localhost";
# 5. 刷新授权表(mysqL命令行)
mysql> flush privileges;
# 6.进入mysql的配置文件,删除掉先加入的内容,保存退出,重启mysql服务.(linux命令行)
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
# 重启mysql服务(linux命令行)
systemctl restart mysql
4.设置统一字符编码(linux版本)
# 1. 修改mysqld配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
#加上如下代码
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
# 修改mysql客户端的配置文件
sudo vim /etc/mysql/conf.d/mysql.cnf
[mysql]
default-character-set=utf8
[client]
default-character-set=utf8
# 2、重启MySQL服务
systemctl restart mysql
# 3、查看一下
show variables like "%char%";
注意有坑:
在配置统一字符编码之前的数据库,建议建表的时候,指定字符编码.
MySQL的操作
增删查改
1.操作数据库(目录)
# 增(添加数据库)
create database db1 default charset utf8;
# 查(查看数据库)
show databases; # 查看所有数据库
show create database db1; # 查看指定的数据库
# 改(修改数据库编码)
alter database db1 charset gbk; # 修改数据库的字符编码
# 删(删除数据库)
drop database db1;
2.操作数据表(文件)
# 增(添加数据表)
create table t1(id int,name varchar(32)); # 指定字段
# 查(查看表和表属性)
show tables; # 查看所有表(当前数据库下的)
show create table t1; # 查看指定表
desc t1; # 查看表属性
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
# 改(修改表)
alter table t1 modify name char(6); # (modify) 只能修改约束条件和类型
alter table t1 change name Name char(7); # (change) 力度更大,可以改字段名加(modify)的功能
alter table t1 add price decimal(5,2) not null;# (add) 新增price字段
# 删(删除表)
drop table t1; # 指定对应的表名;
alter table book drop price; # 删除指定的字段名
truncate table t1; # 清空所有记录,并且重置至初识状态,id从1开始
3.操作表记录(文件内容)
# 增(添加表内容)
1.如果所有字段都添加
insert into t1 values(1,"xiaoming"),(2,"xiaohong"); # 按位置添加
insert into t1(id,name) values(1,'xiaoming'),(2,"xiaohong") # 指定字段添加
2.如果指定字段添加
insert into t1(name) values("xiaoming"),("xiaohong");
# 查(查找表内容)
select * from t1; # 查看所有字段
select * from t1 where id=1; # 指定字段
# 改(更改表内容)
update t1 set name="xiaomi" where id=1; # 注意,不where指定条件,默认会修改所有name的内容
# 删(删除表内容)
delete from t1 where name="xiaomi"; # 注意,不where指定id或条件,跟上面说的一样
MySQL的数据类型
1.数值类型
整数类型
""" unsigned 指定该字段为无符号类型 """
tinyint # 2**8(2的8次方范围)1个字节 # 注意:当插入的值超过这个范围，会报1264错误。
'有符号 -128---127
无符号 0---255 '
'# 有符号例子
mysql> create table t1(num tinyint) #创建表结构
mysql> desc t1; #查看表信息
mysql> insert into t1 values(-128),(127);
mysql> select * from t1; #查看表内容
# 无号例子 #unsigned 指定无符号类型
mysql> create table t2(num tinyint unsigned);
mysql> desc t2; #查看表信息
mysql> insert into t2 values(0),(255);
mysql> select * from t2; #查看表内容'
int # 2**32(2的32次方范围)4个字节
' 有符号：-2147483648 ～ 2147483647
无符号： 0 ～ 4294967295 '
bigint # 2**64(2的64次方范围)8个字节
' 有符号：-9223372036854775808 ～ 9223372036854775807
无符号：0 ～ 184467440737 '
浮点型
decimal:
'''
定义：
准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。m:65d:30
精确度：
**** 随着小数的增多，精度始终准确 ****
对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因
# 例子
mysql> create table t1(num decimal(5,2));
mysql> insert into t1 values(456.12);
mysql> select * from t1;
'''
2.日期类型
year #(年) 无论year指定何种宽度，最后都默认是year(4)
''' # 例子
mysql> create table t1(born_year year);
mysql> desc t1;
mysql> insert into t1 values(1901),(2155);# 超出这个范围会报1264错误。
mysql> select * from t1; '''
date # (日期格式)
''' # 例子
mysql> create table t1(born_date date);
mysql> desc t1;
mysql> insert into t1 values("2019-5-5");
mysql> select * from t1; '''
time # (时间格式)
datetime #(***)(日期时间格式)（1000-01-01 00:00:00/9999-12-31 23:59:59）
timestamp #(了解)（1970-01-01 00:00:00/2037 年某时）
'''
#三个集合语句
mysql> create table t1(d date,t time,dt datetime);
mysql> desc t1;
mysql> insert into t1 values(now(),now(),now());#当前日期时间
mysql> select * from t1;
1. 单独插入时间时，需要以字符串的形式，按照对应的格式插入
2. 插入年份时，尽量使用4位值
3. 插入两位年份时，<=69，以20开头，比如50, 结果2050
>=70，以19开头，比如71，结果1971 '''
3.字符串类型
char: # 字符长度范围：0-255
# 定长,执行效率高,浪费空间.
varchar: # 字符长度范围：0-65535
# 不定长,速度慢,范围大,节省空间.
# 根据实际场景,来平衡使用,char要放在varchar的前面
char_length:# 查看字符数
'''# 例子,查看
mysql> create table t1(c char(5),v varchar(5));
mysql> desc t1;
mysql> insert into t1 values('字符串 ','字符串 '); # 增加了空白格
mysql> select c,char_length(c),v,char_length(v) from t1; # 查询字符长度
# char填充空格来满足固定长度，但是在查询时却会很不要脸地删除尾部的空格（装作自己好像没有浪费过空间一样），然后修改sql_mode让其现出原形。
# 略施小计，让char现出原形
mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; # 让它显示原先的字符长度
'''
#length： #查看字节数
mysql> select c,length(c),v,length(v) from t1;
4.枚举类型和集合类型
1. # 枚举类型(单选) enum("xxx", "xxx"....)
2. # 集合类型(多选) set("xxx", "xxx","xxx"...)
enum # 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set # 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）
'''# 枚举和集合例子
mysql> create table t1(id int,name varchar(32),sex enum('male','female'),hobby set('music','read','run','movie'));
mysql> desc t1;
mysql> insert into t1 values(1,'alex','female','music,movie,aaaa'),
-> (2,'egon','female','read,run,music');
mysql> select * from t1;
'''
mysql语法严格模式
插入字段不在列表中,值为空,如果报错,mysql为严格模式
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
严格模式:
严格模式主要有以下场景:
不支持对not null字段插入null值
不支持对自增长字段插入”值
不支持text字段有默认值
1.开启与关闭Strict Mode方法
找到MySQL安装目录下的my.cnf（windows系统则是my.ini)文件
在sql_mode中加入STRICT_TRANS_TABLES则表示开启严格模式，如没有加入则表示非严格模式，修改后重启mysql即可
例如这就表示开启了严格模式：
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
MySQL字段类型
# 约束条件
not null # 字段不能为空
default # 设置默认值
unique # 主键,字段唯一
auto_increment # 自增长,默认为1 该字段必须为主键.主键自增长,被约束的字段必须同时被key约束,一般用于id
primary key # 外键,建立表之间的关系
1.not null 和 defult的用法
not null # 约束字段不能为空
default # 设置默认值
# 用法: (字段名称 数据类型 约束条件)
mysql> create table t1(id int,name varchar(32),sex enum('male','female') not null default 'male');
mysql> desc t1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
+-------+-----------------------+------+-----+---------+-------+
mysql> insert into t1(id,name) values(1,'egon'); # 插入数据
mysql> select * from t1;
+------+------+------+
| id | name | sex |
+------+------+------+
| 1 | egon | male |
+------+------+------+
2.unique的用法
# 单列唯一
# 方式一:
# 直接在字段后加约束条件
mysql> create table t3(id int unique auto_increment,name varchar(32) unique);
mysql> desc t3;
mysql> insert into t3(name) values('it','id'); # 指定字段,auto_increment自增长,默认为1
mysql> insert into t3 values(7,'ip'),(8,'ip'); # 插入相同的名字会报错
ERROR 1062 (23000): Duplicate entry 'ip' for key 'name'
# 方式二:#两着效果相同
unique(字段名)
mysql> create table t3(id int,name varchar(32),unique(id),unique(name));
mysql> desc t3;
mysql> insert into t3 values(1,'IT'),(2,'IT'); # 插入相同的名字会报错
ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'
# 多列联合唯一
unique(字段名1,字段名2...) # 联合唯一的,只有两两相等,才会报错,and的关系
mysql> create table t3(id int,ip char(15),port int,unique(id),unique(ip,port));
mysql> desc t3;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| ip | char(15) | YES | MUL | NULL | |
| port | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
mysql> insert into t3 values(1,'192.168.0.1',8080),
-> (2,'192.168.0.1',8090),
-> (3,'192.168.0.2',8080);
mysql> select * from t3;
#报错例子
mysql> insert into t3 values(4,'192.168.0.1',8080); # 插入数据
ERROR 1062 (23000): Duplicate entry '192.168.0.1-8080' for key 'ip'
3.primary key
# 用法同unique
# auto_increment自增长,默认起始值为1
mysql> create table t3(id int primary key auto_increment,name varchar(32));
mysql> desc t3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
mysql> insert into t3(name) values('alex'),('egon');
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
| 1 | alex |
| 2 | egon |
+----+------+
# 重复键值,会报错
mysql> insert into t3 values(2,'yuan');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
4.auto_increment
''' 前提:字段必须为主键.
作用:是主键自增长
被约束的字段必须同时被key约束。'''
mysql> create table t3(id int primary key auto_increment,name varchar(32));
mysql> desc t3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
mysql> insert into t3(name) values('alex'),('egon');
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
| 1 | alex |
| 2 | egon |
+----+------+
mysql> insert into t3 values(5,'yuan'); # 可以自己指定id只要不违反约束条件即可。
# 对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长，一般情况下，delete后面会接where条件，对指定的内容进行删除。
mysql> delete from t3; # 清空表 再重新插入数据起始位置不会从1开始
mysql> truncate table t3;# 清空所有记录,并且重置至初识状态(ID归零)清空auto_increment的索引值
# 关于起始偏移量和步长（了解）
# 1. 查看默认值
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 | # 默认步长为1
| auto_increment_offset | 1 | # 默认起始偏移量为1
+--------------------------+-------+
# 2. 设置步长
mysql> set session auto_increment_increment=5; # 当前会话级别
mysql> set global auto_increment_increment=5; # 设置全局，需要退出重新加载才能生效。
# 3.设置起始偏移量
mysql> set session auto_increment_offset=3;
mysql> set global auto_increment_offset=3;
# 注意：起始偏移量要<=步长，否则设置无效。
5.foreign key
foreign key，# 外键 ,建立表之间的关系。
# 1、建立表关系
# a、先建被关联的表，且保证被关联的字段唯一
mysql> create table department(
-> id int primary key ,
-> name varchar(32),
-> comment varchar(128)
-> );
mysql> desc department;
+---------+--------------+------+-----+---------+--------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+--------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(32) | YES | | NULL | |
| comment | varchar(128) | YES | | NULL | |
+---------+--------------+------+-----+---------+--------+
# b、再建关联的表
mysql> create table employee(
-> id int primary key auto_increment, # 主键自增长
-> name varchar(32),
-> age tinyint unsigned,
-> dep_id int,
-> foreign key(dep_id) references department(id) # 指定外建字段，跟哪张表哪个字段建立关系。
-> );
mysql> desc employee;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| dep_id | int(11) | YES | MUL | NULL | |
+--------+---------------------+------+-----+---------+----------------+
# 2、插入数据
# a、先往被关联表插入记录
mysql> insert into department values
-> (1,'公关部','公关能力有限部门'),
-> (2,'销售部','销售能力有限部门'),
-> (3,'技术部','靠技术吃饭部门');
# b、再往关联表插入记录
mysql> insert into employee values
-> (1,'任盈盈',18,1),
-> (2,'张无忌',23,2),
-> (3,'令狐冲',25,2),
-> (4,'小龙女',24,1),
-> (5,'灭绝师太',56,1),
-> (6,'欧阳锋',53,3);
# 3.删除数据和更新数据
# a、先删除关联的数据
mysql> delete from employee where dep_id=2;
# b、再删除被关联的数据
mysql> delete from department where id=2;
# c、因为有外建约束，无法进行更新操作。
# mysql> update employee set id=5 where dep_id=3;
# ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'
# mysql> update department set id=8 where id=2
# 4、解决上面的问题，只需在建 关联表 的时候，指定一下同步即可。
# 建立关联表
mysql> create table employee(
-> id int primary key,
-> name varchar(32),
-> age tinyint unsigned,
-> dep_id int,
-> foreign key(dep_id) references department(id)
on delete cascade # 删除时同步
on update cascade # 更新时同步
-> );
# 删除部门
mysql> delete from department where id =1;
mysql> select * from department ;
+----+-----------+--------------------------+
| id | name | comment |
+----+-----------+--------------------------+
| 2 | 销售部 | 销售能力有限部门 |
| 3 | 技术部 | 靠技术吃饭部门 |
+----+-----------+--------------------------+
mysql> select * from employee; # 与之关联的员工一并删除了。
+----+-----------+------+--------+
| id | name | age | dep_id |
+----+-----------+------+--------+
| 2 | 张无忌 | 23 | 2 |
| 3 | 令狐冲 | 25 | 2 |
| 6 | 欧阳锋 | 53 | 3 |
+----+-----------+------+--------+
# 更新部门
mysql> update department set id=8 where id=2;
mysql> select * from department;
+----+-----------+--------------------------+
| id | name | comment |
+----+-----------+--------------------------+
| 3 | 技术部 | 靠技术吃饭部门 |
| 8 | 销售部 | 销售能力有限部门 |
+----+-----------+--------------------------+
# 与之关联的表也会更改
mysql> select * from employee;
MySQL的查询语法
# 1.单表查询语法
SELECT 字段1,字段2...
FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
2.关键字的执行优先级（重点）
# 注解：
1.from # 找到表:from
2.where # 拿着where指定的约束条件，去文件/表中取出一条条记录
3.group by # 将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.having # 将分组的结果进行having过滤
5.select # 执行select
6.distinct # 去重
7.order by # 将结果按条件排序：order by
8.limit # 限制结果的显示条数
单表查询
数据结构准备
# 插入数据结构
mysql> create table employee(
-> id int primary key auto_increment,
-> name varchar(32) not null,
-> sex enum('male','female') not null default 'male',
-> age tinyint unsigned not null default 28,
-> hire_date date not null,
-> post varchar(32),
-> post_comment varchar(128),
-> salary decimal(9,2),
-> office int, # 一个部门一个屋子
-> dep_id int
-> );
# 插入数据
mysql> insert into employee(name,sex,age,hire_date,post,post_comment,salary,office,dep_id)
values
('任盈盈','female',18,'2018-11-14','sale',null,8000,401,1),
('小龙女','female',23,'2017-11-24','sale',null,9000,401,1),
('王语嫣','female',22,'2016-1-14','sale',null,7000,401,1),
('黄蓉','female',21,'2018-12-14','sale',null,8000,401,1),
('秋香','female',19,'2019-1-11','sale',null,8000,401,1),
('周芷若','female',20,'2018-11-11','sale',null,7000,401,1),
('赵敏','female',21,'2017-2-18','sale',null,8000,401,1),
('令狐冲','male',24,'2017-1-24','operation',null,7000,402,2),
('张无忌','male',23,'2016-5-21','operation',null,7500,402,2),
('郭靖','male',25,'2016-7-28','operation',null,8000,402,2),
('杨康','male',24,'2016-8-28','operation',null,7000,402,2),
('杨铁心','male',54,'2015-4-18','operation',null,9000,402,2),
('杨过','male',27,'2017-5-28','operation',null,7000,402,2),
('张三丰','male',68,'2014-8-8','teacher','主授太极拳',11000,403,3),
('任我行','male',54,'2015-2-4','teacher','主授吸星大法',10000,403,3),
('岳不群','male',50,'2016-12-24','teacher','主授华山剑法',9000,403,3);
1.简单查询
# 查看全部,复杂写法(字段一个个写)
select id,name,sex,age,hire_date,post,post_comment,salary,office,dep_id from employee;
# 查看全部表内容(实际不推荐这种,按上面那种来写)
select * from employee;
# 查看指定表字段内容
select id ,name,salary from employee;
# 避免重复distinct
select distinct post from employee; # 查看指定内容,只显示单个不重复内容
# 通过四则运算查询(加减乘除)
select name,salary * 12 from employee; # 原样显示(salary*12)
# 修改样式用as 取别名,不加as 默认后面就是别名
select name,salary * 12 as annual_salary from employee;
2.where用法
# where约束条件
'''
where字句中可以使用：
比较运算符：><>= <= <> !=
between 80 and 100 值在80到100之间
in(80,90,100) 值是80或90或100
like '姓%' '姓_' 可以是%或_，%表示匹配任意多字符, _表示匹配一个字符
逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
'''
# 1、单条件查询
# 查询销售的所有员工
select name from employee where post='sale';
# 2、多条件查询
# 查询销售中工资大于等于8000的员工姓名和薪资
select name,salary from employee where post='sale' and salary >=8000;
# 3、关键字between and
# 查询不在8000到10000的员工姓名 ,只需在between 前面加not ---> (not between)
# 查询工资在8000到10000的员工姓名
select name from employee where salary between 8000 and 10000;
# 4、关键字is null(判断某个字段是否为null不能用等号，需要用is)
# 查询值位不为空的员工,只需在is后面加not ---> (is not)
# 查询职位描述为空的员工
select name,post_comment from employee where post_comment is null;
# 特殊符号 '' 是空字符串,不是null
# 更新
update employee set post_comment='' where id=1;
# 查询
select name ,post_comment from employee where post_comment='';
# 5、关键字IN集合查询
# or 原型(复杂)
select name,salary from employee where salary=8000 or salary=9000 or salary=10000;
#查询工资不为8000或9000或10000的,只需在in前面加not ---> (not in)
# 查询工资为8000或9000或10000的员工姓名和工资
select name,salary from employee where salary in (8000,9000,10000);
# 6、关键字LIKE模糊查询
# a、通配符"%",查找'杨'开头的
select name from employee where name like '杨%';
# b、通配符"_" #查找以"杨"开头的两个字符,'__',查找杨的三个字符
select naem from employee where name like '杨_'
3.group by用法
# 单独使用GROUP BY关键字分组
# 注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数。如:select post from employee group by post;
# GROUP BY关键字和GROUP_CONCAT()函数一起使用
# 按照岗位分组，并查看组内成员名
SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;
#更改别名
SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;
# 聚合函数
# MAX：最大值
# MIN：最小值
# AVG：平均值
# SUM：求和
# COUNT：计数
# 查询每个职位最高的薪资
select post,max(salary) from employee group by post;
# 查询每个职位共有多个员工
select post,count(id) from employee group by post;
4.having用法
# having过滤
# HAVING与WHERE不一样的地方在于:
# ！！！执行优先级从高到低：where > group by > having
# 1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
# 2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
# 查询各岗位内包含的员工个数小于4的岗位名、岗位内包含员工名字、个数
select post,group_concat(name),count(id) from employee group by post having count(id) < 4;
5.order by用法
# 按单列排序
select * from employee order by salary; # 默认为升序
SELECT * FROM employee ORDER BY salary ASC; # 升序
SELECT * FROM employee ORDER BY salary DESC; # 降序
# 按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
SELECT * from employee ORDER BY age,salary DESC;
6.limit用法
# 限制查询的记录数 limit
select * from employee order by salary desc limit 3; #默认初始位置为0 #查询第三条
select * from employee order by salary desc limit 0,5 #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
7.regexp用法
# 使用正则表达式查询
SELECT * FROM employee WHERE name REGEXP '^任'; #以任开头匹配
SELECT * FROM employee WHERE name REGEXP '女$'; #以女结尾匹配
# 小结：对字符串匹配的方式
WHERE name = '任盈盈';
WHERE name LIKE '杨%';
WHERE name REGEXP '女$';
多表查询
表关系说明
'''
一对一
一对多
多对多
'''
# 表连接方式
inner join # 内连接
left join # 左连接
right join # 右连接
union # 全连接(mysql中用左连接右连接实现)
# 子查询
#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等
一对一
# 一对一关系
# 两张表：学生表和客户表
# 一对一：一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系
# 关联方式 :foreign key + unique
# 一定是student来foreign key表customer，这样就保证了：
# 1 学生一定是一个客户，
# 2 客户不一定是学生，但有可能成为一个学生
# 1.创建表
# 先建客户表
create table customer(
id int primary key auto_increment,
name varchar(32) not null,
qq varchar(32) not null,
tel varchar(32) not null
);
# 再建学生表
create table student(
id int primary key auto_increment,
class_name varchar(32) not null,
customer_id int unique, #该字段一定要是唯一的,#也可以建在客户表
foreign key(customer_id) references student(id)
on delete cascade on update cascade # 关联同步删除同步更新
);
# 2.插入记录
# 先插被关联的客户表
insert into customer(name,qq,tel) values
('alex',1223,135666544),
('egon',34322,13438895),
('yuan',84322,13954893);
# 再插关联的表
insert into student(class_name,customer_id) values
('脱产2期',1),
('脱产3期',2),
('周末13期',3);
一对多
# 以出版社和书籍为例
# 1.创建表
create table press(
id int primary key auto_increment,
name varchar(32)
);
create table book(
id int primary key auto_increment,
name varchar(32),
press_id int,
foreign key(press_id) references press(id)
on delete cascade on update cascade # 关联同步删除同步更新
);
# 2.插入记录
# 先插被关联的表
inser into press(name) values
('华山出版社'),
('少林出版社'),
('明教出版社'),
('武当出版社'),
('丐帮出版社');
# 再插关联的表
insert into book(name,press_id) values
('独孤九剑',1),
('华山剑法',1),
('九阳神功',2),
('九阴真经',2),
('易筋经',2),
('葵花宝典',3),
('乾坤大挪移',3),
('太极拳',4),
('天罡北斗阵',4),
('吸星大法',5);
多对多
# 1.创建表
# 先创建作者表
create table author(
id int primary key auto_increment,
name varchar(32),
);
# 再建存放作者与书的关系表,即查询二者的关系查这表就可以了.
# author2book 建二者关系表时,用2把二者连接起来,约定熟成这样标识
create table author2book(
id int not null unique quto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id)
on delete cascade on update cascade,
foreign key(book_id) references book(id)
on delete cascade on update cascade,
primary key(author_id,book_id)
);
# 2.插入记录
# 插入作者记录
insert into author(name) values
('张无忌'),
('令狐冲'),
('东方不败'),
('张三丰'),
('冲虚道长');
# 再插入对应的关系记录
insert into author2book(author_id,book_id) values
(1,3),
(1,7),
(2,1),
(2,2),
(3,6),
(4,8);
表连接方式(join..)
# 外连接语法
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
交叉连接（笛卡尔积）
# 注意：这种连接毫无意义
mysql> select * from book,press; # 简单粗暴的将两张表连接起来
# 如果只想取到两表有关系的记录用where条件
mysql> select * from book,press where book.press_id=press.id;
#也可以用sql对应的语法实现.
1.inner join内连接用法
# 内连接，只取两表的共同部分
select * from book inner join press on book.press_id = press.id;
+----+------------+----------+----+------------+
| id | name | press_id | id | name |
+----+------------+----------+----+------------+
| 1 | 独孤九剑 | 1 | 1 | 华山出版社 |
| 2 | 华山剑法 | 1 | 1 | 华山出版社 |
| 3 | 九阳神功 | 2 | 2 | 少林出版社 |
| 4 | 九阴真经 | 2 | 2 | 少林出版社 |
| 5 | 易筋经 | 2 | 2 | 少林出版社 |
| 6 | 葵花宝典 | 3 | 3 | 明教出版社 |
| 7 | 乾坤大挪移 | 3 | 3 | 明教出版社 |
| 8 | 太极拳 | 4 | 4 | 武当出版社 |
| 9 | 天罡北斗阵 | 4 | 4 | 武当出版社 |
+----+------------+----------+----+------------+
2.left join 左连接用法
#先插入一条新的book记录
insert into book(name) values('降龙十八掌');
# 左连接，在内连接的基础上保留左边的记录。
select * from book left join press on book.press_id = press.id;
+----+------------+----------+------+------------+
| id | name | press_id | id | name |
+----+------------+----------+------+------------+
| 1 | 独孤九剑 | 1 | 1 | 华山出版社 |
| 2 | 华山剑法 | 1 | 1 | 华山出版社 |
| 3 | 九阳神功 | 2 | 2 | 少林出版社 |
| 4 | 九阴真经 | 2 | 2 | 少林出版社 |
| 5 | 易筋经 | 2 | 2 | 少林出版社 |
| 6 | 葵花宝典 | 3 | 3 | 明教出版社 |
| 7 | 乾坤大挪移 | 3 | 3 | 明教出版社 |
| 8 | 太极拳 | 4 | 4 | 武当出版社 |
| 9 | 天罡北斗阵 | 4 | 4 | 武当出版社 |
| 10 | 吸星大法 | 5 | 5 | 丐帮出版社 |
| 11 | 降龙十八掌 | NULL | NULL | NULL |
+----+------------+----------+------+------------+
3.right join 右连接用法
# 先插入一条新的press记录
insert into press(name) values('峨眉出版社');
# 右连接，在内连接的基础上保留右边的记录。
select * from book right join press on book.press_id = press.id;
+------+------------+----------+----+------------+
| id | name | press_id | id | name |
+------+------------+----------+----+------------+
| 1 | 独孤九剑 | 1 | 1 | 华山出版社 |
| 2 | 华山剑法 | 1 | 1 | 华山出版社 |
| 3 | 九阳神功 | 2 | 2 | 少林出版社 |
| 4 | 九阴真经 | 2 | 2 | 少林出版社 |
| 5 | 易筋经 | 2 | 2 | 少林出版社 |
| 6 | 葵花宝典 | 3 | 3 | 明教出版社 |
| 7 | 乾坤大挪移 | 3 | 3 | 明教出版社 |
| 8 | 太极拳 | 4 | 4 | 武当出版社 |
| 9 | 天罡北斗阵 | 4 | 4 | 武当出版社 |
| 10 |吸星大法 | 5 | 5 | 丐帮出版社 |
| NULL | NULL | NULL | 6 | 峨眉出版社 |
+------+------------+----------+----+------------+
4.union全连接用法
# 全外连接，在内连接的基础上保留左右两表没有对应关系的记录。
# 注意：mysql不支持全外连接 full JOIN
mysql> select * from book full join press on book.press_id = press.id;
#报错
ERROR 1054 (42S22): Unknown column 'book.press_id' in 'on clause'
# 强调：mysql可以使用此种方式间接实现全外连接(自己实现全外连接的效果)
# union 连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
select * from book left join press on book.press_id = press.id
union
select * from book right join press on book.press_id = press.id;
+------+-----------------+----------+------+-----------+
| id | name | press_id | id | name |
+------+-----------------+----------+------+-----------+
| 1 | 独孤九剑 | 1 | 1 | 华山出版社 |
| 2 | 华山剑法 | 1 | 1 | 华山出版社 |
| 3 | 九阳神功 | 2 | 2 | 少林出版社 |
| 4 | 九阴真经 | 2 | 2 | 少林出版社 |
| 5 | 易筋经 | 2 | 2 | 少林出版社 |
| 6 | 葵花宝典 | 3 | 3 | 明教出版社 |
| 7 | 乾坤大挪移 | 3 | 3 | 明教出版社 |
| 8 | 太极拳 | 4 | 4 | 武当出版社 |
| 9 | 天罡北斗阵 | 4 | 4 | 武当出版社 |
| 10 | 吸星大法 | 5 | 5 | 丐帮出版社 |
| 11 | 降龙十八掌 | NULL | NULL | NULL |
| 12 | 如来神掌 | NULL |NULL | NULL |
| NULL | NULL | NULL | 6 | 峨眉出版社 |
+------+-----------------+----------+-------+------+- -+
添加数据多表查询
# 为book表新增单价字段
alter table book add price decimal(5,2) not null;
# 查看表结构
mysql> desc book;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| press_id | int(11) | YES | MUL | NULL | |
| price | decimal(5,2) | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+
# 更新新增字段的值
update book set price=60 where id=1;
update book set price=30 where id=2;
update book set price=70 where id=3;
update book set price=40 where id=4;
update book set price=80 where id=5;
update book set price=40 where id=6;
update book set price=50 where id=7;
update book set price=80 where id=8;
update book set price=30 where id=9;
update book set price=40 where id=10;
简单连表查询
# 示例一;
# 以内连接的方式查询book和press表，并且book表中的price字段值必须大于50,即找出单价大于50元的书籍以及书籍的出版社名称
select book.name,press.name from book inner join press on book.press_id =
press.id where price >50;
+--------------+-----------------+
| name | name |
+--------------+-----------------+
| 独孤九剑 | 华山出版社 |
| 九阳神功 | 少林出版社 |
| 易筋经 | 少林出版社 |
| 太极拳 | 武当出版社 |
+--------------+-----------------+
# 示例二:
# 以内连接的方式查询book和press表，并且以price字段的降序方式显示书籍名称、价格和出版社名称。
select book.name,book.price,press.name from book inner join press on
book.press_id = press.id order by price desc;
+-----------------+-------+-----------------+
| name | price | name |
+-----------------+-------+-----------------+
| 太极拳 | 80.00 | 武当出版社 |
| 易筋经 | 80.00 | 少林出版社 |
| 九阳神功 | 70.00 | 少林出版社 |
| 独孤九剑 | 60.00 | 华山出版社 |
| 乾坤大挪移 | 50.00 | 明教出版社 |
| 九阴真经 | 40.00 | 少林出版社 |
| 葵花宝典 | 40.00 | 明教出版社 |
| 吸星大法 | 40.00 | 丐帮出版社 |
| 天罡北斗阵 | 30.00 | 武当出版社 |
| 华山剑法 | 30.00 | 华山出版社 |
+-----------------+-------+-----------------+
子查询 (in...)
# 1：子查询是将一个查询语句嵌套在另一个查询语句中。
# 2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
# 3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
# 4：还可以包含比较运算符：= 、 !=、> 、<等
1.in 或者 = 用法
# 1、查询书籍平均价格在45元以上的出版社的id和名称
#子查询
select id ,name from press where id in
(select press_id from book group by press_id having avg(price) > 45);
#连表查询
select press.id,press.name from press inner join book on press.id = book.press_id group by book.press_id having avg(book.price) > 45;
# 2、查看少林出版社的所有书籍名称
#子查询
select name from book
where press_id in
(select id from press where name ='少林出版社');
#连表查询
select book.name from book inner join press on book.press_id =
press.id where press.name = '少林出版社';
#3.查询出版书籍大于2的出版社名称
#子查询
select name from press
where id in
(select press_id from book group by press_id having count(press_id) > 2);
#连表查询
select press.name from press inner join book on press.id = book.press_id group by book.press_id having count(book.id) > 2;
2.比较运算符 > < != 用法
# 查询书籍价格大于所有书籍平均价格的书名与单价
select name,price from book
where price >
(select avg(price) from book);
# 查询大于出版社里平均价格的书名与单价
select name , price from book as t1
inner join
(select press_id,avg(price) avg_price from book group by press_id) as t2
on t1.press_id = t2.press_id
where t1.price > t2.avg_price;
3.exists查询
# EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
# 而是返回一个真假值。True或False
# 当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
# press表中存在name="少林出版社"，Ture
mysql> select * from book
-> where exists
-> (select * from press where name="少林出版社");
# press表中存在name="峨眉出版社"，False不进行查询
mysql> select * from book
-> where exists
-> (select * from press where name="峨眉出版社");
Empty set (0.00 sec)
PyMySQL模块
import pymysql
# from pymysql import *
# 创建和数据库服务器的连接 connection
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='root123456',
db='students',
charset='utf8')
# 创建游标对象
cursor = conn.cursor()
# 中间可以使用游标完成对数据库的操作
sql = "select * from student;"
# 执行ｓｑｌ语句的函数 返回值是该ＳＱＬ语句影响的行数
count = cursor.execute(sql)
print("操作影响的行数%d" % count)
# print(cursor.fetchone()) # 返回值类型是元祖，表示一条记录
# 获取本次操作的所有数据
for line in cursor.fetchall():
print("数据是%s" % str(line))
# 关闭资源 先关游标
cursor.close()
# 再关连接
conn.close()
