约束条件

今日主要讲解的都是表的一些查询:

''' select where group by having distinct order by limit regexp like '''

1. not null 与 defaultnot null （不可为空）/ default（自动传。）

1.1 not null：

是否可空，null表示空，非字符串not null - 不可空null - 可空

1.2 default：

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

#请勿表名重复： create table t1（id int not null defalut 2）;#如果不输入就记为2 # 数据类型不可为空 create table t1 (x int not null); mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | x | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ create table t2 (x int not null default 111); # 设置默认值 mysql> desc t2; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | x | int(11) | NO | | 111 | | +-------+---------+------+-----+---------+-------+1234567891011121314

2. unique

控制插入数据的唯一性； 单例唯一：

mysql> create table t3(name varchar(10) unique); # 指定单个数据的唯一性； mysql> desc t3; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(10) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> insert t3 values("sailan"); Query OK, 1 row affected (0.00 sec) mysql> insert t3 values("sailan"); # 再插入同样的报错； ERROR 1062 (23000): Duplicate entry 'sailan' for key 'name'12345678910

联合为一：

mysql> create table t4(name varchar(10),id int,ip int,port int,unique(ip,port),unique(name)); # 单个UNI指定了多个数据唯一性，这就是联合，后续可指定单例唯一； mysql> desc t4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(10) | YES | UNI | NULL | | | id | int(11) | YES | | NULL | | | ip | int(11) | YES | MUL | NULL | | | port | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+123456789

not null 和unique一起使用会产生的化学反应=>会被识别成表的主键（primary key），主键下面有介绍ao；

create table t6(id int,name varchar(10) not null unique); mysql> desc t6; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(10) | NO | PRI | NULL | | +-------+-------------+------+-----+---------+-------+ create table t5(id int,name varchar(10) unique); mysql> desc t5; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(10) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+12345678910111213141516

3. primary key

站在约束角度去看主键可以限制字段不为空且唯一，主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键，我不设置的话innodb底层会自动生成一个，但这属实无奈之举，我应每创建表格都定制主键，主键通常是ID字段。

特点：

主键的约束效果是not null+uniqueinnodb表有且只有一个主键，但是该主键可以是联合主键

单列主键：

create table t6( id int primary key auto_increment, # auto_increment 不传参自动增长 name varchar(5));12

联合主键：

create table t7( id int, name varchar(5), primary key(id,name));123

4. foreign key

用来关联两个表；

# 需先创建被关联表 #表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一 create table dep( id int primary key auto_increment, name varchar(6), comment varchar(30) ); # 再创建关联表 create table emp( id int primary key auto_increment, name varchar(10), gender varchar(5), dep_id int, #dep_id外键，关联父表（dep主键id），实现同步更新，同步删除 foreign key(dep_id) references dep(id) on delete cascade on update cascade ); # 先往被关联表插入数据 insert into dep(id,name) values (1,'技术部'), (2,'人力资源部'), (3,'销售部'); # 再往被关联子表插入数据 insert into emp(name,gender,dep_id) values ('egon',"male",1), ('alex1',"male",2), ('alex2',"male",2), ('alex3',"male",2), ('李坦克',"male",3), ('刘飞机',"male",3), ('张火箭',"male",3), ('林子弹',"male",3), ('加特林',"male",3) ; #删父表dep，子表emp中对应的记录跟着删； #更新父表dep，子表emp中对应的记录跟着改；123456789101112131415161718192021222324252627282930313233343536

快速找出两张表之间的关系 ；

分析步骤： #1、先站在左表的角度去找 是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id） #2、再站在右表的角度去找 是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id） #3、总结： #多对一： 如果只有步骤1成立，则是左表多对一右表 如果只有步骤2成立，则是右表多对一左表 #多对多 如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系 create table author( id int primary key auto_increment, name varchar(10) ); create table book( id int primary key auto_increment, name varchar(16) ); create table author2book( id int primary key auto_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 ); #一对一: 如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可 关联方式：foreign key+unique1234567891011121314151617181920212223242526272829303132

今日内容详细

前期表准备

create table emp( id int not null unique auto_increment, name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int ); #插入记录 #三个部门：教学，销售，运营 insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values ('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部 ('tom','male',78,'20150302','teacher',1000000.31,401,1), ('kevin','male',81,'20130305','teacher',8300,401,1), ('tony','male',73,'20140701','teacher',3500,401,1), ('owen','male',28,'20121101','teacher',2100,401,1), ('jack','female',18,'20110211','teacher',9000,401,1), ('jenny','male',18,'19000301','teacher',30000,401,1), ('sank','male',48,'20101111','teacher',10000,401,1), ('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门 ('呵呵','female',38,'20101101','sale',2000.35,402,2), ('西西','female',18,'20110312','sale',1000.37,402,2), ('乐乐','female',18,'20160513','sale',3000.29,402,2), ('拉拉','female',28,'20170127','sale',4000.33,402,2), ('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬银','female',18,'20130311','operation',19000,403,3), ('程咬铜','male',18,'20150411','operation',18000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3); # 当表字段特别多 展示的时候错乱 可以使用\G分行展示 select * from emp\G; # 个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象 你可以将字符编码统一设置成GBK

几个重要关键字的执行顺序

# 书写顺序 select id,name from emp where id > 3; # 执行顺序 from where select """ 虽然执行顺序和书写顺序不一致 你在写sql语句的时候可能不知道怎么写 你就按照书写顺序的方式写sql select * 先用*号占位 之后去补全后面的sql语句 最后将*号替换后你想要的具体字段 明天会一直使用 这里先理解 """

where筛选条件

# 作用:是对整体数据的一个筛选操作 # 1.查询id大于等于3小于等于6的数据 select id,name,age from emp where id>=3 and id<=6; select id,name from emp where id between 3 and 6; 两者等价 # 2.查询薪资是20000或者18000或者17000的数据 select * from emp where salary=20000 or salary=18000 or salary=17000; select * from emp where salary in (20000,18000,17000); # 3.查询员工姓名中包含字母o的员工的姓名和薪资 """ 模糊查询 like % 匹配任意多个字符 _ 匹配任意单个字符 """ select name,salary from emp where name like '%o%'; # 4.查询员工姓名是由四个字符组成的 姓名和薪资 char_length() _ select name,salary from emp where name like '____'; select name,salary from emp where char_length(name) = 4; # 5.查询id小于3或者id大于6的数据 select * from emp where id not between 3 and 6; # 6.查询薪资不在20000,18000,17000范围的数据 select * from emp where salary not in (20000,18000,17000); # 7.查询岗位描述为空的员工姓名和岗位名 针对null不用等号 用is select name,post from emp where post_comment = NULL; select name,post from emp where post_comment is NULL;

group by分组

# 分组实际应用场景 分组应用场景非常的多 男女比例 部门平均薪资 部门秃头率 国家之间数据统计 # 1 按照部门分组 select * from emp group by post; """ 分组之后 最小可操作单位应该是组 还不再是组内的单个数据 上述命令在你没有设置严格模式的时候是可正常执行的 返回的是分组之后 每个组的第一条数据 但是这不符合分组的规范:分组之后不应该考虑单个数据 而应该以组为操作单位(分组之后 没办法直接获取组内单个数据) 如果设置了严格模式 那么上述命令会直接报错 """ set global sql_mode = 'strict_trans_tables,only_full_group_by'; 设置严格模式之后 分组 默认只能拿到分组的依据 select post from emp group by post; 按照什么分组就只能拿到分组 其他字段不能直接获取 需要借助于一些方法(聚合函数) """ 什么时候需要分组啊？？？ 关键字 每个 平均 最高 最低 聚合函数 max min sum count avg """ # 1.获取每个部门的最高薪资 select post,max(salary) from emp group by post; select post as '部门',max(salary) as '最高薪资' from emp group by post; select post '部门',max(salary) '最高薪资' from emp group by post; # as可以给字段起别名 也可以直接省略不写 但是不推荐 因为省略的话语意不明确 容易错乱 # 2.获取每个部门的最低薪资 select post,min(salary) from emp group by post; # 3.获取每个部门的平均薪资 select post,avg(salary) from emp group by post; # 4.获取每个部门的工资总和 select post,sum(salary) from emp group by post; # 5.获取每个部门的人数 select post,count(id) from emp group by post; # 常用 符合逻辑 select post,count(salary) from emp group by post; select post,count(age) from emp group by post; select post,count(post_comment) from emp group by post; null不行 # 6.查询分组之后的部门名称和每个部门下所有的员工姓名 # group_concat不单单可以支持你获取分组之后的其他字段值 还支持拼接操作 select post,group_concat(name) from emp group by post; select post,group_concat(name,'_DSB') from emp group by post; select post,group_concat(name,':',salary) from emp group by post; # concat不分组的时候用 select concat('NAME:',name),concat('SAL:',salary) from emp; # 补充 as语法不单单可以给字段起别名 还可以给表临时起别名 select emp.id,emp.name from emp; select emp.id,emp.name from emp as t1; 报错 select t1.id,t1.name from emp as t1; # 查询每个人的年薪 12薪 select name,salary*12 from emp;

分组注意事项

# 关键字where和group by同时出现的时候group by必须在where的后面 where先对整体数据进行过滤之后再分组操作 where筛选条件不能使用聚合函数 select id,name,age from emp where max(salary) > 3000; select max(salary) from emp; # 不分组 默认整体就是一组 # 统计各部门年龄在30岁以上的员工平均薪资 1 先求所有年龄大于30岁的员工 select * from emp where age>30; 2 再对结果进行分组 select * from emp where age>30 group by post; select post,avg(salary) from emp where age>30 group by post;

having分组之后的筛选条件

""" having的语法根where是一致的 只不过having是在分组之后进行的过滤操作 即having是可以直接使用聚合函数的 """ # 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门 select post,avg(salary) from emp where age>30 group by post having avg(salary) > 10000 ;

distinct去重

""" 一定要注意 必须是完全一样的数据才可以去重！！！ 一定不要将逐渐忽视了 有逐渐存在的情况下 是不可能去重的 [ {'id':1,'name':'jason','age':18}, {'id':2,'name':'jason','age':18}, {'id':3,'name':'egon','age':18} ] ORM 对象关系映射 让不懂SQL语句的人也能够非常牛逼的操作数据库 表 类 一条条的数据 对象 字段对应的值 对象的属性 你再写类 就意味着在创建表 用类生成对象 就意味着再创建数据 对象点属性 就是在获取数据字段对应的值 目的就是减轻python程序员的压力 只需要会python面向对象的知识点就可以操作MySQL """ select distinct id,age from emp; select distinct age from emp;

order by排序

select * from emp order by salary; select * from emp order by salary asc; select * from emp order by salary desc; """ order by默认是升序 asc 该asc可以省略不写 也可以修改为降序 desc """ select * from emp order by age desc,salary asc; # 先按照age降序排 如果碰到age相同 则再按照salary升序排 # 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序 select post,avg(salary) from emp where age>10 group by post having avg(salary) > 1000 order by avg(salary) desc ;

limit限制展示条数

select * from emp; """针对数据过多的情况 我们通常都是做分页处理""" select * from emp limit 3; # 只展示三条数据 select * from emp limit 0,5; select * from emp limit 5,5; 第一个参数是起始位置 第二个参数是展示条数

正则

select * from emp where name regexp '^j.*(n|y)$';

多表操作

前期表准备

#建表 create table dep( id int, name varchar(20) ); create table emp( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'male', age int, dep_id int ); #插入数据 insert into dep values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营'); insert into emp(name,sex,age,dep_id) values ('jason','male',18,200), ('egon','female',48,201), ('kevin','male',18,201), ('nick','male',28,202), ('owen','male',18,203), ('jerry','female',18,204);

表查询

select * from dep,emp; # 结果 笛卡尔积 """ 了解即可 不知道也没关系 """ select * from emp,dep where emp.dep_id = dep.id; """ MySQL也知道 你在后面查询数据过程中 肯定会经常用到拼表操作 所以特地给你开设了对应的方法 inner join 内连接 left join 左连接 right join 右连接 union 全连接 """ # inner join 内连接 select * from emp inner join dep on emp.dep_id = dep.id; # 只拼接两张表中公有的数据部分 # left join 左连接 select * from emp left join dep on emp.dep_id = dep.id; # 左表所有的数据都展示出来 没有对应的项就用NULL # right join 右连接 select * from emp right join dep on emp.dep_id = dep.id; # 右表所有的数据都展示出来 没有对应的项就用NULL # union 全连接 左右两表所有的数据都展示出来 select * from emp left join dep on emp.dep_id = dep.id union select * from emp right join dep on emp.dep_id = dep.id;

子查询

""" 子查询就是我们平时解决问题的思路 分步骤解决问题 第一步 第二步 ... 将一个查询语句的结果当做另外一个查询语句的条件去用 """ # 查询部门是技术或者人力资源的员工信息 1 先获取部门的id号 2 再去员工表里面筛选出对应的员工 select id from dep where name='技术' or name = '人力资源'; select name from emp where dep_id in (200,201); select * from emp where dep_id in (select id from dep where name='技术' or name = '人力资源');

总结

表的查询结果可以作为其他表的查询条件 也可以通过起别名的方式把它作为一个张虚拟表根其他表关联 """ 多表查询就两种方式 先拼接表再查询 子查询 一步一步来 """