约束条件、清空列表的方式、其他SQL语句、数据准备、查询关键字、分组、过滤、去重、排序、分页

约束条件

　　在数据类型的基础上再添加限制条件

1.unsigned 去除符号

　　create table 表名（id int unsigned）；

2.zerofill 填充（0）

3. not null 非空

　　create table 表名（id int，name varchar（16））：

　　create table 表名（id int，name varchar（16）not null）；

ps：在MySQL中‘ ’和null不一样

4.unique 唯一

　　单列唯一：create table 表名（id int，name varchar（16）unique）

　　联合唯一：create table 表名（id int，ip varchar（16），port int，unique（ip，ort））；

5.default 默认值

　　create table 表名（id int，name varchar（16）default‘sb’）

6.primary 主键

　　create table 表名（id int primary key，name varchar（16））；

　　单纯的从约束条件来看主键相当于not null + unique，非空且唯一

　　InnoDB存储引擎要求每一张表必须要有一个主键，但是你会发现，之前创建的很多张表，也都创建成功了，为什呢，原因是：InnoDB存储引擎内部隐藏的有一个主键字段，但是这个隐藏的字段我们看不到，这个主键的目的主要就是用来帮助我们把表常见成功，仅此而已.

　　一般给id 、uid、sid、aid等加主键

7.auto_increment 主键每次主动+1

　　create table 表名（id int primary key auto-incremet，name varchar（16））；

ps：创建表时，主键固定格式 id int primary key auto_incremet

清空列表的方法

1.第一种

　　delete from 表名：不会把主键重置

2.第二种

　　truncate 表名：把主键重置从1开始、把数据清空

3.以后遇到清空列表使用truncate方式

　　因为：truncate清空后，数据还可以补救

　　恢复数据：binlog日志，记录你在数据库中执行的SQL·语句变化过程，通过binlog日志可以恢复数据

　　工作以后，可以自学

补充其他SQL语句

1.修改表名

　　alter table 表名 rename 新表名；

2.增加字段

　　alter table 表名 add 字段名数据类型约束条件

　　alter table 表名 add 字段名数据类型约束条件 first；最前面添加字段

　　alter table 表名 add 字段名数据类型约束条件 after 字段名；添加在哪个字段明后天

ps：如果是空表直接添加没事，如果有数据就要考虑情况

3.删除字段

　　alter table 表名 drop 字段名

4.修改字段

　　alter table 表名 modify 字段名数据类型；

　　alter table 表名 change 旧字段名新字段名旧数据类型

ps：modify 只能修改字段数据类型完整约束，不能修改字段名，但是change可以

数据准备

创建表：

　　create table emp(
　　　　　　　　　　id int primary key auto_increment,
　　　　　　　　　　name varchar(20) not null,
　　　　　　　　　　sex enum('male','female') not null default 'male', #大部分是男的
　　　　　　　　　　age smallint(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
　　　　('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);

查询关键字

select 查询的关键字 from 表名

查询关键字之where筛选

where 筛选功能

　　show variables like %mode% 模糊查询

模糊查询：没有明确是筛选条件

　　关键字：like

　　关键符号：

　　　　%：匹配任意个数任意字符

　　　　_：匹配单个个数任意字符

1.查询id大于等于3小于等于6的数据
　　select * from emp where id >=3 and id<=6;
　　select * 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字母的员工姓名和薪资
　　select name, salary from emp where name like '%o%';
　　select name, salary from emp where name like 'o%';
　　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;

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;

假如字段想设置为空，该如何设置?
　　最好设置为'',不要使用NULL
　　尤其是两者混用

查询关键字之group by分组

分组:

　　把一个整体分成若干个个体

关键字：group by

　　分组之后，得到的每一个分组中的第一条数据

　　分组之后只能得到分组的依据

　　分组之后默认只能够直接过去到分组的依据，其他数据都不能直接获取

针对5.6需要自己设置sql_mode set global sql_mode='only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';

聚合函数：

　　sum、max、min、avg、count

ps：分组后一般配合聚合函数使用

按部门分组：

　　select * from 表名 group by 部门；

　　select id，name，sex from 表名 group by 部门；验证：分组之后默认只能够直接过去到分组的依据，其他数据都不能直接获取

ps：遇到每字，肯定需要分组，按照每字后面字段进行分组

1.每个部门最低工资

　　select post，min（salary）from emp group by post；

2.每个部门平均工资

　　select post，avg（salary）from emp group by post；

3.每个部门的工资总和

　　select post，sum（salary）from emp group by post；

4.每个部门人数　　

　　select post, count(*) from emp group by post;
　　select post, count(id) from emp group by post;
　　select post, count(1) from emp group by post;

获取分组之外的数据：

　　group_concat：用在分组之后

　　select concat(name,'|',sex) from emp group by post;

　　coucat:用在分组之前

　　　　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;

补充：在显示时可以给字段起别名

　　select post as '部门',max(salary) as '最高工资' from emp group by post;

PS：as可以省略，但一般那不推荐

关键字之having过滤

　　having 和where一样用来筛选数据的，但是用法不一样

　　where用在分组前先帅选一遍，having用在分组之后筛选

eg：

统计各部门年龄在30岁以上的员工平均薪资,并且保留平均薪资大于10000的部门

1.1 select *from emp where age>30;先筛选出大于30的

1.2select avg（salary）from emp where age>30 group by post；进行分组

1.3select avg(salary) from emp where age >30 group by post having avg(salary) > 10000;

关键字之distinct去重

把重复的数据去掉
对有重复的展示数据进行去重操作一定要是重复的数据
　　select distinct id,age from emp;
　　select distinct post 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;

统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
　　1. 先筛选出年龄在10岁以上的员工
　　　　select * from emp age > 10

　　2. 按照部门进行分组，然后求平均工资
　　　　select avg(salary) as avg_salary from emp age > 10 group by post

　　3. 在筛选出平均工资大于1000的部门
　　　　select avg(salary) as avg_salary from emp where age > 10 group by post having avg(salary) > 1000

　　4. 然后对平均工资进行排序
　　　　select avg(salary) as avg_salary from emp where age > 10 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; # 第一个参数代表的是起始位置，第二个参数是限制的条数

分页：
　　第一页：select *from emp limit 0, 10;
　　第二页：select *from emp limit 10, 10;
　　第三页：select *from emp limit 20, 10;
　　第四页：select *from emp limit 30, 10;