MySQL的一些约束条件

1.unsigned 去除符号

即规定填进去的数据不能是有符号数，否则会报错

2.not null

非空，即规定了所填写字段不能为空。

ps： 注意的是 在MySQL中 ''和null是不同的

• '' 是一个空字符串，表示一个具体的空值，占用存储空间。

• NULL 是一个占位符，表示缺少值或未知的值，不占用存储空间。

• 对于字符串类型的列，'' 和 NULL 是两个不同的值。'' 表示一个空字符串，而 NULL 表示缺少值。

所以当规定了not null后再填入会报错。

3.zerofill

用0来填充剩下的位数

4.unique 

唯一，确保某个键的数据值不允许重复

单列唯一：只确定某个值唯一（在其后面加unique）

•  create table t3(id int unique,name varchar(12) );
•  insert into t3  values(1,'qq');
•  insert into t3  values(1,'ww');  #不能成功插入，因为id规定了其唯一性

联合唯一：确定其中几个值不能完全相同，但单独的值可以相同。即组合不同，单独可以相同。

• create table t4(id int, ip varchar(16),port int,unique(ip,port) );
• insert into t4  values(1,'127.0.0.1',3306);
•  insert into t4  values(2,'127.0.0.2',3306);
• insert into t4  values(3,'127.0.0.1',3305);
• insert into t4  values(4,'127.0.0.1',3306); #不能插入成功，不能完全一样。

5.default

默认值，即默认哪一列是一个默认值

 6.primary key

主键（Primary Key）在关系型数据库中用于唯一标识表中的每一行数据的列或列组合。主键具有以下特征：

1. 唯一性：主键的值在表中必须是唯一的，每一行的主键值都不相同。
2. 非空性：主键的值不能为空，即每一行都必须有一个有效的主键值。
3. 不可更改性：主键值一旦确定，一般情况下是不能修改的。如果确实需要修改主键值，通常需要先删除原有行，再插入一行新数据

• create table t6(id int primary key,name varchar(12));
• insert into t6 values(1,'qq');
• insert into t6 values(1,'ww');   #不能成功插入，因为id为主键，不能重复

7.auto_increment

附加条件，让主键可以自动加1

• create table t7(id int primary key auto_increment ,name varchar(12));

若删除这条记录后，再继续添加记录进去发现，此时主键的id并不是原来已经删除的id号，而是继续往下加了1。

• delete from t7 where id=2;
• select * from t7;
•  insert into t7(name) values('ww');  #

若用delete来说删除整个表，再重新放入新的数据后仍然会从id号的下一个计数开始。

• 用truncate t7 来删除表 ，可以把主键id的值重新置1开始
• 用词方法来清除表之后，若想要恢复数据可以在binlog日志中恢复，记录了你在数据库中执行SQL语句的变化过程，通过binlog日志可以恢复数据

对数据表的修改

•  alter table t9 rename tt9;  #修改表名为tt9
•  alter table tt9 add gender varchar(16);  #新增键
•  alter table tt9 add uid int first;   #在最前面增加
• alter table tt9 add age int after name;  #在某个键后面增加

---

例子：对员工信息的数据处理

1.数据存储

# 数据准备

create table emp(
  id int primary key auto_increment,   #定义id为主键
  name varchar(20) not null,   #定义姓名不能为空
  sex enum('male','female') not null default 'male', #默认性别为男性
  age smallint(3) unsigned not null default 28,  #年龄不能为负数、不能为空、默认为28
  hire_date date not null,   #员工来的日期
  post varchar(50),    #部门
  post_comment varchar(100),  #岗位描述
  salary double(15,2),  #工资
  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),
('qaz','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('wsx','female',38,'20101101','sale',2000.35,402,2),
('edc','female',18,'20110312','sale',1000.37,402,2),
('rfv','female',18,'20160513','sale',3000.29,402,2),
('tgb','female',28,'20170127','sale',4000.33,402,2),
('aa','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('qq','male',18,'19970312','operation',20000,403,3),
('ww','female',18,'20130311','operation',19000,403,3),
('ee','male',18,'20150411','operation',18000,403,3),
('rr','female',18,'20140512','operation',17000,403,3);

•  查询id大于等于3小于等于6的数据

select * from emp where id>=3 and id<=6;

select * from emp where id between 3 and 6;

•  查询薪资是20000或者18000或者17000的数据

select * from emp where salary=20000 or salary=18000 or salary= 17000;

select * from emp where salary in (20000, 18000, 17000);

#select * from emp where salary  not in (20000, 18000, 17000); #不在这几个内

• 查询员工姓名中包含o字母的员工姓名和薪资

select  name,salary from emp where name like '%o%'; 

select name,salary from emp where name like 'o%';  #模糊匹配以o开头的

select name,salary from emp where name like '%o'；  #以o结尾的

• 查询员工姓名是由四个字符组成的员工姓名与其薪资

select name,salary from emp where name like'____';

select name,salary from emp where char_length(name) = 4;

• 查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is

select post,name from emp where  post_comment is NULL;

分组

分组使用到了 group by 关键，是一种用于将数据根据特定的列或条件进行分类和聚合的操作。分组通常与聚合函数（如 MAX、MIN、SUM、COUNT、AVG 等）一起使用，以便对每个组内的数据执行聚合计算。

• 按照部门进行分组

select * 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, count(*) from emp group by post;

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

• 由于分组后只会显示属于该分组的第一个数据的字段信息
• 如select post,name from emp group by post;  #只会显示一个

•  获取分组以外的数据字段 可以使用group_concat()

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

•  不分组展示某个全部信息

select concat(id,'|',name) from emp;

 select id,name from emp;

 

having过滤

 HAVING 是在 SQL 查询中用于对分组数据进行过滤的子句。它在 GROUP BY 子句之后使用，并允许在聚合函数结果上应用条件进行筛选。

having本质上和where的功能是相同的，但不同的是having是针对分组来过滤的，用在分组之后，而where用在分组之前先过滤一遍

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

1.先筛选出年龄在30岁以上的平均工资

2.在在此基础上按照部门分组、

3.在各部门的分组中取平均薪资并保留大于10000的部门 

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

distinct去重

DISTINCT 是 SQL 中用于在查询结果中去除重复行的关键字。它指示数据库只返回不同的（唯一的）行，从而消除重复的数据。

select distinct id,age from emp;

select distinct age from emp;

order by排序

ORDER BY 是 SQL 查询中用于对结果进行排序的子句。它允许按照一个或多个列对结果集进行升序（ASC）或降序（DESC）排序。

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 avg(salary) from emp where age>10;

2.按照部门分组后再对平均工资进行排序

select avg(salary) from emp where age>10 group by post having avg(salary) >1000 order by avg(salary) desc;

limit分页

LIMIT 是 SQL 中用于限制查询结果行数的子句。它可以用于执行分页操作，从结果集中选择指定范围的行。

• 限制展示的行数

select * from emp limit 3;   #限制三条

• limit a,b     #  a:第一个参数位置是起始位置  b: 第二个位置参数是展示的条数

select * from emp limp 0,5;  #从第1 个位置开始，展示5条

 

regexp正则

REGEXP  是 SQL 中用于在查询中使用正则表达式进行模式匹配的运算符。

• .：匹配任意字符（除了换行符）。
• ^：匹配字符串的开头。
• $：匹配字符串的结尾。
• []：匹配括号内的任意字符。
• [^]：不匹配括号内的任何字符。
• *：匹配前面的模式零次或多次。
• +：匹配前面的模式一次或多次。
• ?：匹配前面的模式零次或一次。
• ()：捕获匹配的子表达式。

不同的数据库系统可能支持不同的正则表达式语法和函数。

正则表达式提供了一种强大的模式匹配工具，允许在 SQL 查询中进行更灵活和复杂的模式匹配操作。

• 查找name中以j开头包含n或者y的名字

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

1. 查询岗位名以及岗位包含的所有员工名字

select post ,group_concat(name) from emp group by post;
2. 查询岗位名以及各岗位内包含的员工个数

select post,count(id) from emp group by post;
3. 查询公司内男员工和女员工的个数

select count(sex) from emp where sex='male';

select count(sex) from emp where sex='female';
4. 查询岗位名以及各岗位的平均薪资

select post,avg(salary) from emp group by post;
5. 查询岗位名以及各岗位的最高薪资

select post,max(salary) from emp group by post;
6. 查询岗位名以及各岗位的最低薪资

select post,min(salary) from emp group by post;
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

select avg(salary) from emp where sex='male';

select avg(salary) from emp where sex='female';