【无标题】

创建表的完整语法

create table t1(字段名1 数据类型 约束条件 约束条件 约束条件 约束条件,。。。)

注意：字段名和数据类型是必须写的，不能省略，约束条件是可选的，可有可无，而且可以有多个

表结构中最后一个字段不能有逗号

约束条件

作用：用于保证数据的完整性和一致性 主要分为：

 unsigned

无符号

id int unsigned

zerofil

使用0填充

default

为该字段设置默认值

create table t4 (id int, name varchar(32) default 'kevin');
    insert into t4 values(1, 'jerry');
    insert into t4(id) values(1);

not full

标识该字段不能为空

create table t5 (id int, name varchar(32) not null);
insert into t5(id) values(1);

unique

标识该字段的值是唯一的

单列唯一
    create table t6 (id int, name varchar(32) unique);
多列唯一
    create table t7 (
        id int,
        ip varchar(32),
        port varchar(32),
        unique(ip, port)
    );

主键(primary key)

主键单纯从约束上来看，它相当于是非空且唯一 unique not null

id unique not null ---------> id primary key
    create table t8 (id int primary key);
    create table t8 (id int unique not null);

 InnoDB存储引擎规定每一张表都要有一个主键，但是，我之前创建的表都没有指定主键, 表是怎么创建成功的?

是因为InnoDB存储引擎内部有一个隐藏的主键，这个主键我们看不到，它也不能够加快查询速度，仅仅是为了帮助我们把表创建成功. 所以，以后我们创建表的时候都主动的创建一个主键，我们自己创建的主键能够加快查询速度，因为是一个索引.

 一般情况下，主键应该创建哪个字段? 大多都给id字段加了，所以，每一张表都要有一个id字段，并且一张表中不只是有一个主键，可以有多个主键，但是，大多数情况下，都只有一个

auto_incrment

自增：每一次主动比上一次加1

  create table t9 (
        id int primary key auto_increment,
        name varchar(32)
    );

清空表的两种方式

 delete from 表名；

这种操作方式不会清空id值，再次添加数据的时候id会会从上次记录的开始

truncate 表名；

清空表，重置id值

一些其他的SQL语句

修改表名

ALTER TABLE 表名 RENAME 新表名;

增加字段

ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]

ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]  FIRST;# 将字段添加到第一个

 ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名; #将字段添加到某一个字段之后

删除字段

ALTER TABLE 表名 DROP 字段名;

修改字段

ALTER TABLE 表名 MODIFY  字段名 数据类型 [完整性约束条件…];

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

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

查询关键字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; 

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.查询员工姓名是由四个字符组成的员工姓名与其薪资

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 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;

8. 查询为空

select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;

查询关键字之group by分组

分组: 按照某个指定的条件将单个单个的个体分成一个个整体
按照男女分组：男 女
按照年龄分组：20岁以下 20-30 30-40

在MySQL中分组之后，只能够获得分组的依据! 按照哪个字段分组就只能获取这个字段的值，别的字段不能拿到

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

    sum max min avg count

分组的关键字：group by

数据分组应用场景：每个部门的平均薪资，男女比例等

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

1.按部门分组
select * from emp group by post;  # 分组后取出的是每个组的第一条数据

select id,name,sex from emp group by post;  # 验证

设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，
不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取

2.获取每个部门的最高工资

select post,max(salary) from emp group by post;
补充:在显示的时候还可以给字段取别名
select post as '部门',max(salary) as '最高工资' from emp group by post;

每个部门的最低工资

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;

分组补充函数

group_concat

如果真的需要获取分组以外的数据字段 可以使用group_concat()

每个部门的员工姓名

select post,group_concat(name) from emp group by post;

select post,group_concat(name,'|',sex) 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;

select post,group_concat(distinct name separator '%') 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去重

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;