MySQL

• 针对库的sql语句
• 针对表的sql语句
• 针对记录的SQL语句
• 配置文件统一字符编码
• 存储引擎
• MySQL的基本数据类型
• 严格模式
• 整型中括号内的数字作用
• 创建表的完整语法
• 约束条件
• 补充一些其他SQL语句
• 查询关键字
  • 查询关键字之where筛选
  • 查询关键字之group by分组
  • 补充说明group_concat、concat、concat_ws
  • 关键字之having过滤
  • 关键字之distinct去重
  • 关键字之order by排序
  • 关键字之limit分页
  • 关键字之regexp正则
• 清空表补充

针对库的sql语句

#information_schema库是mysql默认在内存中创建的库
    游客模式只能查看少部分数据库，管理员身份才能查看所有数据库
1. 查看库
	show databases; # 查看所有库
    格式：show create database 库名;# 查看具体库的信息
    	show create database db1;  
2. 增加库
	格式：create database 库名;
    	create database db1;
3. 修改库 # 一般很少用，甚至于不用
格式： alter database 库名 修改内容
alter database db1 charset='gbk';#修改db1的字符编码为gbk

4. 删除库
	格式：drop database 库名;
    drop database db1;	
    
5.查看当前所在库名
	select database();
    
6. 如何选择数据库
	格式：use 库名;
    use mysql;
    
7.查看记录
  select * from user; 

针对表的sql语句

1. 查看表
	1.1show tables;  # 查看库下的所有表
    格式：desc 表名；
    1.2desc  t1;  # 查看表结构
    1.3show create table t1;#查看具体表的sql语句
    
2. 增加表
	create table t1(id int, name varchar(16),age int,gender varchar(32));
'''id,name,age,gender等在MySQL中称为字段；    
	int，varchar在MySQL中称为数据类型，是限制存储数据的
	字段与字段间用逗号隔开；
	'''
	    
3. 修改
	# 修改表名
    格式：alter table 旧名 rename 新名;
    alter table t1 rename tt1; # 重命名表名
    
 4. 删除
	drop table tt1;
    
 5.查看表数据
  select * from user;    

针对记录的SQL语句

'''肯定是要先有库和表，要有库和表的前提才能增加记录'''
# 记录：表里面的一行行数据
库   >>>>   表 >>>>> 记录

• 增加记录

  	全字段增加:
  	insert into t1 values(1, 'kevin', 20);  # 单条数据
      insert into t1 values(3, 'kevin1', 20),(4, 'kevin2', 20),(5, 'kevin3', 20),(6, 'kevin4', 20);#多条数据，数据与数据间逗号隔开
      
      部分字段增加数据，需要在表名后面指定字段名：
      insert into t1(id, name) values(1, 'kevin');  # 单条数据
      insert into t1 (id,name) values (2,'jj'),(7,'uu');#多条数据

• 查看

  	select * from t1;#查看t1表所有字段
      select id from t1;#只查看t1表id字段
      select id,namenamw from t1;#只查看t1表id、name字段

• 改

  格式：update 表名 set 字段1='v1',字段2='v2',字段3='v3', where id=1;
  	update t1 set name='tank' where id=2;#把id为2的name改为tank
      update t1 set name='tank',age=30 where id=2;#id为2的name改为tank age改为30
      update t1 set name='tony' where age=30 and name='tank';#改两个条件的一个字段
      
      update t1 set money=money+'1' where id=1;将id为1的money字段值加1
      ############以后写更新语句的时候，千万检查要有where条件###############################
      update t1 set money=money+'1' ;  # 没有where语句条件影响的是全表

• 删除

  格式：delete from 表名 where id=1;
  	delete from t1 where id =1;
  delete from t1 ;# 没有where语句条件影响的是全表

配置文件统一字符编码

 步骤：
 1. 复制my-default.ini文件夹
 2. 将新复制的文件重命名为my.ini#my.ini是windows系统配置文件
 3. 将以下内容添加到复制文件my.ini中 
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
#[mysqld]代表服务端，[client]自带客户端、[mysql]代表客户端
   以后增加配置到相应的服务端或者客户端后增加配置就行，比如给服务端增加配置就到服务端下面增加响应代码
      
  '''配置文件后一定要重启服务端，若是重新配置字符编码，那在配置之前创建的库和文件还是使用之前的字符编码，需要重新创建新库新文件才能正常使用已经配置的字符编码'''

'''mac系统，它的配置文件，默认是没有的，如果你想加一个配置文件，也可以，但是需要自己手动创建
/etc/my.cnf
1、sudo vim /etc/my.cnf------->把你的配置写到这个文件里去------>：wq'''

存储引擎

'''存储引擎其实就是数据库存储数据的方式!!!'''
show engines;#查看MySQL支持的数据引擎
  
MySQL中有哪些存储引擎,MySQL一共支持9种存储引擎，我们只需要掌握MyISAM、InnoDB

• MyISAM

  	'''
  		是MySQL5.5及之前版本的默认存储引擎，它相对InnoDB的存取速度更快了，但是，相对InnoDB数据不够安全.
  		它不支持事务、行锁（操作某一行数据时其他人不能操作）、外键（数据与数据之间建立关系），支持表锁（操作整个表时，他人不能操作）
  	'''

• InnoDB

  	'''
  		是MySQL5.6及之后版本的默认存储引擎，它相对MyISAM的存取速度更慢了，但是相对MyISAM数据更安全.
  		它支持事务、行锁、外键，不支持表锁
  	'''

• MEMORY

      '''数据存放在内存中，一旦断电，数据立马丢失，重启服务端数据就没了，不能长期保存数据'''

  测试验证：
  建个库：
  create database db4;
  进入库：
  use db4;
增加指定引擎的表：
  create table t1 (id int) engine=MyISAM;#engine指定存储引擎为MyISAM
create table t2 (id int) engine=InnoDB;
  create table t3 (id int) engine=MEMORY;
  
  '''
  不同的存储引擎的区别：
  	1. MyISAM引擎产生3个文件
  	  .frm  >>> 存表结构
  	  .MYD  >>> 存表数据
  	  .MYI  >>> 存数据索引，加快查询速度
      2. InnoDB 产生2个文件
      	.frm  >>> 表结构
      	.ibd  >>> 表索引+表数据
    	3. MEMORY产生1个文件
    		.frm  >>> 表结构
  '''

MySQL的基本数据类型

• 整型（正负号也占用存储数据）# 存储数据的大小范围不一样，整型括号中的数字不代表存储的范围，不代表限制的长度，代表的是展示的位数

  	1.tinyint   
    2.smallint   
    3.int   
    4.bigint   
      
      范围的比较：bigint>int>smallint>tinyint
      tinyint: 1个字节 --8位 范围：-128~127（有符号）范围：0~255（无符号）
      smallint:2个字节 --16位 范围：-32768~32767（有符号）范围：0~65535（无符号）
      int:4个字节--32位
      bigint：8个字节--64位
      
      '''默认情况下，存储数据带不带符号
  带符号'''
      测试验证：存储数据有没有带符号：
      创建表：
      create table t4 (id tinyint);
      再增加记录：
      insert into t4 values (-128),(127);
      再查看字段记录：
       select *from t4;
+------+
| id   |
+------+
| -128 |
|  127 |
+------+  #由此得出带符号
          
      # 默认情况存储是带符号的，其他整型也是如此

• 浮点型# 小数float、double、decimal精确度不一样

     1. float(255, 30)'''总共255位表示存储位数，小数占30位'''
     2.double(255, 30)
     3. decimal(65, 30) '''总共65位表示存储位数，小数占30位'''
      
      
      测试验证：float、double、decimal有何不一样：
      #创建表：
      create table t5 (id float(255, 30));
      create table t6 (id double(255, 30));
      create table t7 (id decimal(65, 30));
      #增加记录：
      insert into t5 values(1.1111111111111111111111);
      insert into t6 values(1.1111111111111111111111);
      insert into t7 values(1.1111111111111111111111);
      #查看字段记录：
       select *from t5;
        >> 1.111111164093017600000000000000
  +----------------------------------+
  | id                               |
  +----------------------------------+
  | 1.111111164093017600000000000000 |
  +----------------------------------+
       select *from t6;
        >> 1.111111111111111200000000000000
  +----------------------------------+
  | id                               |
  +----------------------------------+
  | 1.111111111111111200000000000000 |
  +----------------------------------+
       select *from t7;
        >>  1.111111111111111111111100000000
  +----------------------------------+
  | id                               |
  +----------------------------------+
  | 1.111111111111111111111100000000 |
  +----------------------------------+        
      # 得出结论：精确度不一样
      decimal>double> float
'''如果对数据的要求万无一失，可以使用字符串来保存'''

• 字符串#字符串中括号中得数字代表的就是：限制存储的长度

    1.char()定长，根据我们在括号内填写的数字最多存储该数字的字符，字符不够则用空格填充，超出报错。
     优势：整存整取,速度快
     劣势：浪费存储空间  
    2.varchar()可变长，根据我们在括号内填写的数字最多存储该数字的字符 ，不够有几个存几个，超出报错。
  	 优势：节省存储空间
     劣势： 存去数据的速度较char 慢
      
  	  char(4)：定长，超出4位，报错，不够4位，空格填充
      varchar(4): 可变长，不够4位，有几位存几位，超出4位，有几位存几位
      测试验证：
      #创建表：
      create table t8 (id int, name char(4));
      create table t9 (id int, name varchar(4));
      #增加记录：
      insert into t8 values(1, 'kevin');
      insert into t9 values(1, 'kevin');
      #查看字段记录：
      select *from t8;
      >>>kevi
  +------+------+
  | id   | name |
  +------+------+
  |    1 | kevi |
  +------+------+
      select *from t9;
      >>>kevi
  +------+------+
  | id   | name |
  +------+------+
  |    1 | kevi |
  +------+------+
  
  研究定长和可变长：
  insert into t8 values(1, 'k');
  insert into t9 values(1, 'e');
  验证方法1:char_length查询字段长度
  select char_length(name) from t8;
  select char_length(name) from t9;
  验证方法2:设置严格模式  
  sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';   


  默认情况下MySQL针对char的存储会自动填充空格和删除空格
   '''以后建表的时候，都用varchar'''

• 日期类型

  	1.date年月日
    2.datetime年月日时分秒
    3.time时分秒
    4.year年
    
    create table t13 (id  int, 
                      reg_time date,
                      login_time datetime,
                      logout_time time,
                      birth_day year
                     );
    insert into t13 values(1, '2023-04-04','2023-04-04 11:11:11', '11:11:11', 1995);#输入的数据必须加引号
  >>>                  
  +------+------------+---------------------+-------------+-----------+
  | id   | reg_time   | login_time          | logout_time | birth_day |
  +------+------------+---------------------+-------------+-----------+
  |    1 | 2023-04-04 | 2023-04-04 11:11:11 | 11:11:11    |      1995 |
  +------+------------+---------------------+-------------+-----------+

      '''实际工作中一般用datetime，它可以转换为其他任意时间类型''' 

• 枚举与集合

  	 1.枚举：enum()多选一 ，必须使用enum()中固定字段任意一个
      测试创建表：
      create table t15 (
          			id int, gender 										enum('male','female','other')
                       );
      #增加记录：
      insert into t15 values(1,'aa');#没有使用enum()中字段任意一个，aa就没有增加进去
      insert into t15 values(2,'male');
      #查看记录：
       select * from t15;             
  >>                  
  +------+--------+
  | id   | gender |
  +------+--------+
  |    1 |        |  
  |    2 | male   |
  +------+--------+    

      2.集合：set()多选多 ,也包含多选一，必须使用set()中固定字段任意一个或多个
        create table t16 (
          			id int, 
            hobby set('read','music','tangtou','xijio','anmo')
                       );
      #增加记录：
       insert into t16 values(1,'rrrrrrr'); #没有使用set()中字段任意一个或多个，就没有增加进去
       select * from t16;            
  >>  
  +------+-------+
  | id   | hobby |
  +------+-------+
  |    1 |       |
  +------+-------+ 
         insert into t16 values(4,'read');
         select * from t16;            
  >>         
  +------+-------+
  | id   | hobby |
  +------+-------+
  |    1 |       |
  |    4 | read  |
  +------+-------+     
          insert into t16 values(5,'music,tangtou,xijio');  
          select * from t16;
  >>                  
  +------+---------------------+
  | id   | hobby               |
  +------+---------------------+
  |    1 |                     |
  |    4 | read                |
  |    5 | music,tangtou,xijio |
  +------+---------------------+         

严格模式

# mysql5.6 之前不会直接报错，需要设置一个参数，才会报错
show variables like '%mode%'; # 模糊查询
两种修改方式：
1. 配置文件修改(永久修改）-一定要重启服务端
步骤：
将my-default.ini文件内容最后一行sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 复制到配置文件my.ini中        
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
        
2. 临时修改
步骤：          
第一步：
          set global sql_mode='STRICT_TRANS_TABLES';
第二步：
          退出客户端，重新进客户端
3. set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';   

整型中括号内的数字作用

"""整型中括号内的数字代表的不是数据长度，不代表存储的范围，代表的是展示的长度"""

验证整型中括号内的数字作用：
#创建表：
create table t11 (id int(3));
#增加记录：
insert into t11 values(99999);
#查看字段记录：
select *from t11;
>>> 99999#很明显不是限制长度
+-------+
| id    |
+-------+
| 99999 |
+-------+


* 补充
create table t12(id int(3) zerofill); # zerofill 可以把不足位数的数字用0填充
insert into t12 values(9);
select *from t12;
>>> 009
+------+
| id   |
+------+
|  009 |
+------+

创建表的完整语法

show create table t15;#查看具体表的sql语句
  
 格式：
CREATE TABLE `表名` (
  `字段名` 字段类型(数字) 约束条件 约束条件 约束条件,
  `字段名1` 字段类型1(数字) 约束条件1 约束条件1 约束条件1,
  `字段名1` 字段类型1(数字) 约束条件1 约束条件1 约束条件1) 

create table t15 (
  id int(11) DEFAULT NULL,
  hobby set('read','music','tangtou','xijio','anmo') 
) 



"""
	1. 字段名和字段类型是必须要写的
	2. 数字与约束条件是可选的，并且约束条件可以有多个，空格隔开
	3. 最后一条数据末尾逗号不能加
"""

约束条件

就是在记录数据的数据类型基础上再添加额外的限制

• unsigned#不能填负号，否则报错

	create table t1(id int unsigned);
     insert into t1 values(-2);#报错
	insert into t1 values(2);

• zerofill#0填充

	create table t12(id int(3) zerofill); # zerofill 可以把不足位数的数字用0填充
	 insert into t3 values(3);>>>003

• not null# 非空，不能为空

    create table t2(
        id int, 
        name varchar(32)
    );
   insert into t2 (id) values (2);  #没有指定非空可以正常执行     
        
   create table t3(
        id int, 
        name varchar(32) not null#这里指姓名不能为空
    );
    insert into t3 values(2,'kk');

• default默认值

	 create table t4(
        id int, 
        name varchar(32));
     insert into t4 values(2,'kk');
>>    
+------+------+
| id   | name |
+------+------+
|    2 | kk   |
+------+------+

	 create table t5 (id int,name varchar(32) default 'oo');#默认oo
    insert into t9 (id) values (1);
>>    
+------+------+
| id   | name |
+------+------+
|    1 | oo   |
+------+------+ #默认使用‘oo'   

• unique唯一、unique(字段类型，字段类型）多列唯一

	# 单列唯一
    	create table t6 (
        	id int,
            name varchar(32) unique
        );#这里指姓名单列唯一
        insert into t6 values (2,'yy'),(3,'yy');#报错
    	insert into t6 values (2,'yy'),(3,'uu');#这里单列姓名就唯一没有报错
>>
+------+------+
| id   | name |
+------+------+
|    2 | yy   |
|    2 | kk   |
+------+------+

	# 联合唯一unique(字段类型，字段类型）多列唯一

    	create table t7 (
        	id int,
            host varchar(32) ,
            port varchar(32) ,
            unique(host,port)
        );#这里指host,port多列唯一
        insert into t7 values(1,'127.0.0.1',3306),(2,'127.0.0.1',3306);#报错host列和port列重复了

• primary key主键

	# 单纯从约束条件来看，它的功能相当于是not null+unique，非空且唯一
    '''主键可以加快查询速度!!!因为主键本质上也是一种索引!!!'''
    
 '''   InnoDB存储引擎规定一张表至少要有一个主键，你会发现，我们之前创建表的时候，并没有主键，但是，也创建成功了，为什么呢？
   
    	这是因为InnoDB存储引擎内部有一个隐藏的字段，作为了主键，我们看不到，并且，这个隐藏的主键不能加快查询速度，就是因为InnoDB存储引擎建表就是由主键来构成的，加这个隐藏的字段作为主键，仅仅是为了把表创建成功。
    
        所以，以后我们在创建表的时候，都要创建一个主键字段，一般情况我们都给表中的id或者cid或者pid或者uid...字段添加主键

如果表中没有主键，但是有非空且唯一的字段，那么这个字段就自动升级为主键了；如果表中有多个非空且唯一的字段，那么第一个字段就自动升级为主键'''
    id---->(uid, cid, sid, ....id)
    
如何给字段添加主键
    id int primary key#id字段增加了primary key就变成了主键字段

    create table t8 (
        id int primary key,  # 这个id字段就已经有了主键的特性
        name varchar(32)
    );
    desc t8;
    >>
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+    #key 列PRI就代表了id是主键具有非空且唯一

下面来验证下id是不是具有非空且唯一的特性 ：
insert into t8 (name) values('ii');#报错，没有id字段没有填，证明具有非空特性
insert into t8 values (2,'ii'),(2,'pp');#报错，id字段填写一样，证明具有唯一特性
insert into t8 values (3,'ii'),(2,'ii');#没报错
>>
+----+------+
| id | name |
+----+------+
|  2 | ii   |
|  3 | ii   |
+----+------+

• auto_increment# 自增

    auto_increment配合primary key使用
    create table t9 (
    	id int primary key auto_increment,#给主键id增加自增的特性
        name varchar(32)
    );
    desc t9;
    >>
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+  
#此时id 也有自增的特性
验证：
insert into t9 (name) values ('tt'),('qq');
select * from t9;
>>
+----+------+
| id | name |
+----+------+
|  1 | tt   |
|  2 | qq   |
+----+------+   #id实现自增功能
    
    '''
    以后我们在创建id字段的时候，固定语法结构：
    id int primary key auto_increment
    主键字段在添加值的时候，就不用在单独添加了，而是自动生成。
    
    '''在一张表中，我们可以通过主键字段来唯一确定一条记录'''

补充一些其他SQL语句

• 修改表名

      格式：alter table 旧表名 rename 新表名;
      alter table t1 rename t2;	

• 增加字段

	添加字段到表格最后：
      格式：alter table 表名 add 字段名  数据类型 约束条件;#默认添加新字段到表格最后
    		alter table t1 add age int;
            
    添加新字段到表格首行：    
      格式：alter table 表名 add 字段名  数据类型 约束条件 first;#添加新字段到表格首行
    		alter table t1 add salary decimal(9,2) first;
            
    添加新字段到旧字段xx之后：    
      格式：alter table 表名 add 新字段名  数据类型 约束条件 after 字段名; #添加新字段到表格id之后
        	alter table t1 add gender varchar(16) after id;

• 删除字段

    格式：alter table 表名 drop 字段名;
    alter table t1 drop salary;

• 修改字段类型

1. modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
    格式：alter table 表名 modify  需要修改的字段名  新数据类型 约束条件;
    alter table t1 modify name char(16);#把name varchar(32)改为name char(16)
    """只能修改为相同数据类型，比如把int改为varchar就不行,int改为bigint就可以"""
    
2. change修改字段名、数据类型    
    格式：alter table 表名 change 旧字段名 新字段名 数据类型 约束条件;
    alter table t1 change name name1 char(16);#name改为name1

查询关键字

where：查询条件
select * #指所有的字段
select 字段名 
from 指定表名

# 数据准备
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,#年纪位数最多32767，不能为负数不能为空，为空默认28
  hire_date date not null,#入职日期为date类型年月日 不能为空
  post varchar(50),
  post_comment varchar(100),
  salary double(9,2),#工资总共9位 小数占两位
  office int, #一个部门一个屋子
  depart_id int#部门id
);

#录入数据
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);

mysql> select * from emp;
+----+------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| 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筛选

# where------>筛选条件的
"""
	模糊查询:没有明确的筛选条件
	关键字:like
	关键符号:
		%:匹配任意个数任意字符
		_:匹配单个个数任意字符
show variables like '%mode%';
"""

1.查询id大于等于3小于等于6的数据
第一种方式：select * from emp where id >= 3 and id <= 6;
第二种方式：select * from emp where id between 3 and 6; 
+----+-------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name  | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  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 |
+----+-------+--------+-----+------------+---------+--------------+----------+--------+-----------+

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);  # 简写
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 14 | 程咬金    | male   |  18 | 1997-03-12 | operation | NULL         | 20000.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 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+

3.查询员工姓名中包含o字母的员工姓名和薪资
# 在你刚开始接触mysql查询的时候，建议你按照查询的优先级顺序拼写出你的sql语句
"""
先是查哪张表 from emp
再是根据什么条件去查 where name like ‘%o%’
再是对查询出来的数据筛选展示部分 select name,salary
"""
select name,salary from emp where name like '%o%';
+------+------------+
| name | salary     |
+------+------------+
| tom  | 1000000.31 |
| tony |    3500.00 |
| owen |    2100.00 |
+------+------------+
select name,salary from emp where name like '%o';#以o结尾
select name,salary from emp where name like 'o%';#以o开头

4.查询员工姓名是由四个字符组成的员工姓名与其薪资
第一种方式：select name,salary from emp where name like '____';#4个_
第二种方式：select name,salary from emp where char_length(name) = 4; #char_length为MySQL函数获取字符串长度
+------+----------+
| name | salary   |
+------+----------+
| tony |  3500.00 |
| owen |  2100.00 |
| jack |  9000.00 |
| sank | 10000.00 |
+------+----------+

5.查询id小于3或者大于6的数据
第一种方式：select *  from emp where id not between 3 and 6;
第二种方式：select * from emp where id<3 or id>6;

6.查询薪资不在20000，18000，17000范围的数据
第一种方式：select * from emp where salary not in (20000,18000,17000);
第二种方式：select * from emp where salary != 20000 and salary != 18000 and salary != 17000;

7.查询岗位描述为空的员工名与岗位名#针对null不能用等号，只能用is
select name,post from emp where post_comment is NULL;
+-----------+-----------+
| name      | post      |
+-----------+-----------+
| tom       | teacher   |
| kevin     | teacher   |
| tony      | teacher   |
| owen      | teacher   |
| jack      | teacher   |
| jenny     | teacher   |
| sank      | teacher   |
| 哈哈      | sale      |
| 呵呵      | sale      |
| 西西      | sale      |
| 乐乐      | sale      |
| 拉拉      | sale      |
| 僧龙      | operation |
| 程咬金    | operation |
| 程咬银    | operation |
| 程咬铜    | operation |
| 程咬铁    | operation |
+-----------+-----------+
select name,post from emp where post_comment = NULL;  # 查询为空！Empty set (0.00 sec)
select name,post from emp where post_comment is not NULL;#是 非空，其实就是不是空Empty set (0.00 sec) 

'''在sql中，NULL和''表示含义不一样，前者表示空值，后者表示空字符串；在实际工作者最好设置为'',不要使用NULL尤其是两者混用'''

查询关键字之group by分组

# 为什么需要分组
分组的好处在于可以快速统计出某些数据
"""
按照某个指定的条件将单个单个的个体分成一个个整体
	eg:  按照男女分组
		按照肤色分组
		按照年龄分组
"""
5.6以上的版本默认都会直接报错
	原因是分组之后 select后面默认只能直接填写分组的字段，不能再写其他字段
    原因是分组的目的就是按照分组的条件来管理诸多数据 最小单位应该是分组的依据而不是单个单个的数据
  如果是MySQL5.6及以下版本 需要自己手动添加，在配置文件中增加：
    	set global sql_mode = 'only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
        
        
# 聚合函数：
	聚合函数主要就是配合分组一起使用
	max             统计最大值
    min				统计最小值
    sum				统计求和
    count			统计计数
    avg				统计平均值
    
# 数据分组应用场景：每个部门的平均薪资，男女比例等

1.按部门分组
select * from emp group by post;  # 按部门分组
+----+------+--------+-----+------------+----------+--------------+------------+--------+----------+
| id | name | sex    | age | hire_date  | post     | post_comment | salary     | office | depart_id|
+----+------+--------+-----+------------+----------+--------------+------------+--------+----------+
| 13 | 僧龙 | male   |  28 | 2016-03-11 | operation | NULL         |   10000.13 |    403 |         3|
|  8 | 哈哈 | female |  48 | 2015-03-11 | sale      | NULL         |    3000.13 |    402 |         2|
|  1 | tom | male   |  78 | 2015-03-02 | teacher   | NULL         | 1000000.31 |    401 |         1 
+----+--------+--------+-----+------------+-----------+--------------+------------+--------+--------+
#分组后显示的是每组第一条数据

"""
设置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.获取每个部门的最高工资  
问题解析：
先对部门进行分组,然后利用max函数查询薪资最高
select post,max(salary) from emp group by post;
+-----------+-------------+
| post      | max(salary) |
+-----------+-------------+
| operation |    20000.00 |
| sale      |     4000.33 |
| teacher   |  1000000.31 |
+-----------+-------------+

select post,max(salary) from emp;#获取emp的最高工资
+---------+-------------+
| post    | max(salary) |
+---------+-------------+
| teacher |  1000000.31 |
+---------+-------------+

补充:在显示的时候还可以给字段取别名
select post as '部门',max(salary) as '最高工资' from emp group by post;
+-----------+--------------+
| 部门       | 最高工资      |
+-----------+--------------+
| operation |     20000.00 |
| sale      |      4000.33 |
| teacher   |   1000000.31 |
+-----------+--------------+
as也可以省略 但是不推荐省 因为语义不明确
3.每个部门的最低工资
select post,min(salary) from emp group by post;
4. 每个部门的平均工资
select post,avg(salary) from emp group by post;
5. 每个部门的工资总和
select post,sum(salary) from emp group by post;
6. 每个部门的人数
select post,count(id) from emp group by post;
select post,count(*) from emp group by post;#按照字段数据值最多的统计数量
select post,count(1) from emp group by post;#按照第一列统计数量
统计的时候只要是非空字段 效果都是一致的 
这里显示age，salary，id最后演示特殊情况post_comment

补充说明group_concat、concat、concat_ws

1. group_concat()  分组之后使用
如果真的需要获取分组以外的数据字段 可以使用group_concat()
# 每个部门的员工姓名
select post,group_concat(name) from emp group by post;
+-----------+------------------------------------------------+
| post      | group_concat(name)                             |
+-----------+------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,僧龙                    |
| sale      | 拉拉,乐乐,西西,呵呵,哈哈                           |
| teacher   | sank,jenny,jack,owen,tony,kevin,tom            |
+-----------+------------------------------------------------+

select post,group_concat(name,'|',sex,'|',age) from emp group by post;#还可增加|进行分割
+-----------+------------------------------------------------------------------------------------+
| post      | group_concat(name,'|',sex,'|',age)                                                 |     
+-----------+------------------------------------------------------------------------------------+
| operation | 程咬铁|female|18,程咬铜|male|18,程咬银|female|18,程咬金|male|18,僧龙|male|28            |     
| sale      | 拉拉|female|28,乐乐|female|18,西西|female|18,呵呵|female|38,哈哈|female|48             |        
teacher|sank|male|48,jenny|male|18,jack|female|18,owen|male|28,tony|male|73,kevin|male|81,tom|male|78 |
+-----------+------------------------------------------------------------------------------------+

select post,group_concat(distinct name) from emp group by post; #distinct name去重

select post,group_concat(distinct name separator '%') from emp group by post;
+-----------+------------------------------------------------+
| post      | group_concat(distinct name separator '%')      |
+-----------+------------------------------------------------+
| operation | 程咬铁%程咬铜%程咬银%程咬金%僧龙               |
| sale      | 拉拉%乐乐%西西%呵呵%哈哈                       |
| teacher   | sank%jenny%jack%owen%tony%kevin%tom            |
+-----------+------------------------------------------------+

2. concat()  分组之前使用
select concat(name,sex) from emp;
select concat(name,'|',sex) from emp;
+----------------------+
| concat(name,'|',sex) |
+----------------------+
| tom|male             |
| kevin|male           |
| tony|male            |
| owen|male            |
| jack|female          |
| jenny|male           |
| sank|male            |
| 哈哈|female          |
| 呵呵|female          |
| 西西|female          |
| 乐乐|female          |
| 拉拉|female          |
| 僧龙|male            |
| 程咬金|male          |
| 程咬银|female        |
| 程咬铜|male          |
| 程咬铁|female        |
+----------------------+

3. concat_ws()
select post,concat_ws('|', name, age) from emp group by post;
+-----------+---------------------------+
| post      | concat_ws('|', name, age) |
+-----------+---------------------------+
| operation | 僧龙|28                   |
| sale      | 哈哈|48                   |
| teacher   | tom|78                    |
+-----------+---------------------------+

关键字之having过滤

having 用在分组之后筛选数据，一般与聚合函数使用
"""
where与having都是筛选功能 但是有区别
	where在分组之前对数据进行筛选
	having在分组之后对数据进行筛选

关键字where group by 同时出现的情况下，group by必须在where之后
where 先对整张表进行一次筛选，group by再对筛选过后的表进行分组

我们一定要有一个简单的认识 一条SQL语句的结果也可以看成是一张全新的表
"""
1.统计各部门年龄在30岁以上的员工平均薪资,并且保留平均薪资大于10000的部门.
第一步筛选30岁以上员工
select * from emp where age>30;
第二步按照部门筛选平均工资
select post,avg(salary) from emp where age>30 group by post;
第三步保留平均工资大于10000的部门
select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;
+---------+---------------+
| post    | avg(salary)   |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+

关键字之distinct去重

# 对有重复的展示数据进行去重操作 一定要是重复的数据
select distinct age from emp; #对年纪去重
+-----+
| age |
+-----+
|  78 |
|  81 |
|  73 |
|  28 |
|  18 |
|  48 |
|  38 |
+-----+
select post,group_concat(distinct name) from emp group by post;
+-----------+------------------------------------------------+
| post      | group_concat(distinct name)                    |
+-----------+------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,僧龙               |
| sale      | 拉拉,乐乐,西西,呵呵,哈哈                       |
| teacher   | sank,jenny,jack,owen,tony,kevin,tom            |
+-----------+------------------------------------------------+
不要对主键去重没哟意义，主键就是唯一的

关键字之order by排序

升序asc
降序desc

select * from emp order by salary; #默认升序排
select * from emp order by salary desc; #降序排

#先按照age降序排，若年纪相同的情况下再按照薪资升序排
select * from emp order by age desc,salary;
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary     | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
|  2 | kevin     | male   |  81 | 2013-03-05 | teacher   | NULL         |    8300.00 |    401 |         1 |
|  1 | tom       | male   |  78 | 2015-03-02 | teacher   | NULL         | 1000000.31 |    401 |         1 |
|  3 | tony      | male   |  73 | 2014-07-01 | teacher   | NULL         |    3500.00 |    401 |         1 |
|  8 | 哈哈      | female |  48 | 2015-03-11 | sale      | NULL         |    3000.13 |    402 |         2 |
|  7 | sank      | male   |  48 | 2010-11-11 | teacher   | NULL         |   10000.00 |    401 |         1 |
|  9 | 呵呵      | female |  38 | 2010-11-01 | sale      | NULL         |    2000.35 |    402 |         2 |
|  4 | owen      | male   |  28 | 2012-11-01 | teacher   | NULL         |    2100.00 |    401 |         1 |
| 12 | 拉拉      | female |  28 | 2017-01-27 | sale      | NULL         |    4000.33 |    402 |         2 |
| 13 | 僧龙      | male   |  28 | 2016-03-11 | operation | NULL         |   10000.13 |    403 |         3 |
| 10 | 西西      | female |  18 | 2011-03-12 | sale      | NULL         |    1000.37 |    402 |         2 |
| 11 | 乐乐      | female |  18 | 2016-05-13 | sale      | NULL         |    3000.29 |    402 |         2 |
|  5 | jack      | female |  18 | 2011-02-11 | teacher   | NULL         |    9000.00 |    401 |         1 |
| 17 | 程咬铁    | female |  18 | 2014-05-12 | operation | NULL         |   17000.00 |    403 |         3 |
| 16 | 程咬铜    | male   |  18 | 2015-04-11 | operation | NULL         |   18000.00 |    403 |         3 |
| 15 | 程咬银    | female |  18 | 2013-03-11 | operation | NULL         |   19000.00 |    403 |         3 |
| 14 | 程咬金    | male   |  18 | 1997-03-12 | operation | NULL         |   20000.00 |    403 |         3 |
|  6 | jenny     | male   |  18 | 1900-03-01 | teacher   | NULL         |   30000.00 |    401 |         1 |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+

'''多字段排序，如果想让后面的字段排序生效，前提：前面的排序字段必须一样'''

# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp where age>10 group by post having avg(salary)>1000 order by avg(salary) desc;
+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| teacher   | 151842.901429 |
| operation |  16800.026000 |
| sale      |   2600.294000 |
+-----------+---------------+

关键字之limit分页

# 限制展示条数
select * from emp limit 3;#展示3条
+----+-------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 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 |
+----+-------+------+-----+------------+---------+--------------+------------+--------+-----------+

# 查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 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 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+

# 分页显示
select * from emp limit 3,2;  # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
+----+------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex    | age | hire_date  | post    | post_comment | salary  | office | depart_id |
+----+------+--------+-----+------------+---------+--------------+---------+--------+-----------+
|  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 |
+----+------+--------+-----+------------+---------+--------------+---------+--------+-----------+

select * from emp limit 5,5;
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name   | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  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 |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+

关键字之regexp正则

select * from emp where name regexp '^j.*(n|y)$';
+----+-------+------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name  | sex  | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+-------+------+-----+------------+---------+--------------+----------+--------+-----------+
|  6 | jenny | male |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
+----+-------+------+-----+------------+---------+--------------+----------+--------+-----------+

清空表补充

创建一个表：create table t1(id int primary key auto_increment,name varchar(32));
录入字段：insert into t1 (name) values('kevin'),('jack');
select *from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | kevin |
|  2 | jack  |
+----+-------+

1. delete from t1;  # 不能把主键重置
select *from t1;
Empty set (0.00 sec) #删除t1记录，但是表还存在
insert into t1 (name) values('kevin'),('jack');#重新录入字段验证
select *from t1;
+----+-------+
| id | name  |
+----+-------+
|  3 | kevin |
|  4 | jack  |
+----+-------+# 不能把主键重置，新增记录从3开始

2. truncate 清空主键从1开始
create table t15(id int primary key auto_increment,name varchar(32));
insert into t15(name)values('jack'),('tony');
truncate t15;# 清空把主键值重置从1开始
insert into t15(name)values('bibi'),('jerry');
+----+-------+
| id | name  |
+----+-------+
|  1 | bibi  |
|  2 | jerry |
+----+-------+#删除后可以主键重置从1开始

'''所以，以后如果有清空表的需求，我们选择truncate，因为truncate清空表之后，还可以恢复数据'''
 DBA可以恢复数据------>binlog日志----->记录你操作sql语句的过程
 DBA:数据库管理员