MySQL约束条件和查询关键字
1. 约束条件
约束条件(在数据类型的基础上在进行约束) 1. unsigend # 只能是正数 不能有负数 比如年龄 2. zerofill # 零填充 比如int类型 可以用零填充来显示 3. default # 默认值 在不填写的情况下 使用默认值 比如 性别 默认为男 4. not null # 非空 5. unique # 唯一 6. primary key # 主键 7. aoth_increment # 自增 8. not null unique # 非空且唯一 语法展示：、 单列唯一 create table t1(id int,name varchar(12) unique()); 联合唯一 create table t1(id int,name varchar(12),age int, unique(id,name)); # 括号内的执行 # 无符号 create table t1(id int unsigend); # 零填充 create table t1(id int zerofill); # 默认值 defauill create table t4 (id int, name varchar(32) default 'kevin'); insert into t4 values(1, 'jerry'); # # not null 非空 create table t5 (id int, name varchar(32) not null); insert into t5(id) values(1); # 主键和自增一般是搭配来用的 primary key auto_increment create table t9 ( id int primary key auto_increment, name varchar(32) );
查询关键字
模糊查询 select * from 表名 where 字段 '%a%'; # 查找的内容 """ 模糊查询:没有明确的筛选条件 关键字:like 关键符号: %:匹配任意个数任意字符 _:匹配单个个数任意字符 show variables like '%mode%'; select * from emp where name '%a%'； """ +----+--------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | 1 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 2 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 3 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 4 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 5 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 6 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 7 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 8 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 9 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 10 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 11 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 12 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 13 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 14 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 15 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 16 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 17 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | +----+--------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ 表的具体信息 where 关键字筛选功能 # 1.查询id大于等于3小于等于6的数据 select id,name from emp where id >= 3 and id <= 6; select * from emp where id between 3 and 6; # between 多少到多少 # 2.查询薪资是20000或者18000或者17000的数据 两种写法 select salary from emp where salary=20000 or salary=18000 or salary=17000; select * from emp where salary in(20000,18000,17000); # 第二章写法 # 3.查询员工姓名中包含o字母的员工姓名和薪资 # 在你刚开始接触mysql查询的时候，建议你按照查询的优先级顺序拼写出你的sql语句 """ 先是查哪张表 from emp 再是根据什么条件去查 where name like ‘%o%’ 再是对查询出来的数据筛选展示部分 select name,salary " select name,salary from emp where name like '%o%'; # 4.查询员工姓名是由四个字符组成的员工姓名与其薪资 select name,salary from emp where name like '____'; select name,salary from emp where char_length(name) = 4; char_length 是sql内置函数 相当于python的len # 5.查询id小于3或者大于6的数据 select * from emp where id < 3 or 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; select name,post from emp where post_comment is not NULL; '''在sql中，NULL和''不一样'' ================================================= 查询关键字之group by分组 分组: 按照某个指定的条件将单个单个的个体分成一个个整体 按照男女分组：男 女 按照年龄分组：20岁以下 20-30 30-40 # 单纯的分组是没有意义 在MySQL中分组之后，只能够获得分组的依据! 按照哪个字段分组就只能获取这个字段的值，别的字段不能拿到 分组一般配合聚合函数使用： sum max min avg count 分组的关键字：group by # 数据分组应用场景：每个部门的平均薪资，男女比例等 # 分组之后默认只能够直接过去到分组的依据 其他数据都不能直接获取 针对5.6需要自己设置sql_mode set global sql_mode = 'only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH'; # 1.按部门分组 select * from emp group by post; # 分组后取出的是每个组的第一条数据 select id,name,sex from emp group by post; # 验证 ================================================= """ 设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据， 不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取 """ set global sql_mode="strict_trans_tables,only_full_group_by"; # 重新链接客户端 select * from emp group by post; # 报错 select id,name,sex from emp group by post; # 报错 select post from emp group by post; # 获取部门信息 # 强调:只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名 # 2.获取每个部门的最高工资 # 以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果) # 每个部门的最高工资 select post,max(salary) from emp group by post; 补充:在显示的时候还可以给字段取别名 select post as '部门',max(salary) as '最高工资' from emp group by post; as也可以省略 但是不推荐省 因为寓意不明确 # 每个部门的最低工资 select post,min(salary) from emp group by post; # 每个部门的平均工资 select post,avg(salary) from emp group by post; # 每个部门的工资总和 select post,sum(salary) from emp group by post; # 每个部门的人数 select post,count(id) from emp group by post; 统计的时候只要是非空字段 效果都是一致的 这里显示age，salary，id最后演示特殊情况post_comment ================================================= # group_concat 分组之后使用 # 如果真的需要获取分组以外的数据字段 可以使用group_concat() select post,group_concat(name) from emp group by post; select post,group_concat(name,'|',sex, '|', gender) from emp group by post; # 可以获取多个分组之外的字段 分割符号可以随便 # 分组之后 在进行去重 select post,group_concat(distinct name) from emp group by post; # concat 不分组使用 select concat(name,sex) from emp; select concat(name,'|',sex) from emp; # concat_ws() 分组之后 获取以外数据 并且拼接连接符号 select post,concat_ws('|', name, age, gender) from emp group by post; =============================================== 关键字之having过滤 where与having都是筛选功能 但是有区别 where在分组之前对数据进行筛选 having在分组之后对数据进行筛选 1.统计各部门年龄在30岁以上的员工平均薪资,并且保留平均薪资大于10000的部门. # 先筛选出年龄在30岁以上的 select * from emp where age > 30; # 在进行分组，按照部门分组 select avg(salary) as avg_salary from emp where age > 30 group by post; # 保留平均薪资大于10000的部门 select avg(salary) as avg_salary from emp where age > 30 group by post having avg(salary) > 10000; =============================================== 关键字之distinct去重 distinct:去重 """带主键的数据去重有没有意义? 没有，主键本身就是唯一的""" select distinct id,age from emp; ============================================= 关键字之order by排序 select * from emp order by salary; #默认升序排 select * from emp order by salary desc; #降序排 #先按照age降序排，在年轻相同的情况下再按照薪资升序排 select * from emp order by age desc,salary; '''多字段排序，如果想让后面的字段排序生效，前提：前面的排序字段必须一样''' # 统计各部门年龄在20岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序 # 20岁以上的员工 select * from emp where age > 20; # 各部门的平均薪资 select avg(salary) from emp where age > 20 group by post having avg(salary) > 1000; # select avg(salary) from emp where age > 20 group by post having avg(salary) > 1000 order by avg(salary) desc; ============================================= 关键字之limit分页 # 限制展示条数 # 限制展示条数 select * from emp limit 3; # 查询工资最高的人的详细信息 select * from emp order by salary desc limit 1; # 分页显示 select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置 select * from emp limit 5,5;
