MySQL中的约束函数主外键

1、主键约束
特点：
（1）一个表有且只能有一个主键约束
（2）主键约束意味着，唯一并且非空
（3）主键约束名PRIMARY
（4）创建主键会自动创建对应的索引，同样删除主键对应的索引也会删除

2、在建表的时候如何指定主键约束
create table 【数据库名.】表名称(
    字段名1 数据类型  primary key,
    字段名2 数据类型，
    。。。。
);

例如：
create table test.t_stu(
    sid int primary key,
    sname varchar(20),
    gender char
);

mysql> desc t_stu;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(11)     | NO   | PRI | NULL    |       |
| sname  | varchar(20) | YES  |     | NULL    |       |
| gender | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

insert into t_stu values(1,'张三','男'),(1,'李四','女');#错误的

mysql> insert into t_stu values(1,'张三','男'),(1,'李四','女');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'  违反了主键约束

insert into t_stu values(1,'张三','男'),(2,'李四','女');

3、建表后如何指定主键约束
alter table 【数据库名.】表名称 add primary key(字段列表);

create table test.t_stu(
    sid int,
    sname varchar(20),
    gender char
);
alter table test.t_stu add primary key(sid);

4、主键约束分为两种：
（1）单列主键约束
（2）复合主键约束
建表时如何指定复合主键约束：
create table 【数据库名.】表名称(
    字段名1 数据类型,
    字段名2 数据类型，
    。。。。,
    primary key(字段列表)
);
建表后如何指定复合主键约束：
alter table 【数据库名.】表名称 add primary key(字段列表);

create table t_course(
    cid int primary key,
    cname varchar(20)
);

#现在要求sid和cid的组合是主键，是唯一的
create table xuanke(
    sid int,
    cid int,
    score int,
    primary key(sid,cid)
);

5、删除主键约束
alter table 【数据库名.】表名称 drop primary key;

1、唯一键约束
特点：
（1）一个表可以有多个唯一键约束
（2）唯一键约束意味着，唯一，可以为NULL，既不是null的不可以有多个
（3）唯一键的约束名可以自己指定，也可以默认，
如果默认的话，一般如果单列唯一，默认就是列名，
如果是多列组合唯一，那么默认是该组合第1列的名称。
（4）创建唯一键约束，也会在对应列上建立索引。
而且删除唯一键约束的方式是通过删除对应索引来实现的。

2、唯一键约束也是分为两种
（1）单列的唯一键
（2）多列组合的唯一键

3、建表时如何指定唯一键
create table 【数据库名.】表名称(
    字段名1 数据类型  primary key,
    字段名2 数据类型 unique key， #只适用于单列的唯一键
    。。。。
);

create table 【数据库名.】表名称(
    字段名1 数据类型  primary key,
    字段名2 数据类型,
    。。。。,
    unique key(字段列) #既适用于单列，也适用于多列
);

create table books(
    bid int primary key,
    bname varchar(20) unique key,
    price double
);

insert into books values
(1,'《从入门到放弃》',100),
(2,'《从入门到放弃》',88);
ERROR 1062 (23000): Duplicate entry '《从入门到放弃》' for key 'bname' 

insert into books values
(1,'《从入门到放弃》',100),
(2,'《从入门到成功》',88);

4、建表后如何指定唯一键约束
alter table 【数据库名.】表名称 add unique key(字段列表);

create table books(
    bid int primary key,
    bname varchar(20) ,
    price double
);
alter table books add unique key(bname);

5、删除唯一键约束
alter table 【数据库名.】表名称 drop index 索引名;

alter table books drop index bname;

6、如何查看某个表格的索引名
show index from 【数据库名.】表名称;

show index from books;

1、非空约束
特点：
（1）一个表可以有很多的非空约束
（2）非空约束只能针对某一个字段来说
（3）非空约束意味着，该字段不能存入null值

2、如何在建表时指定非空约束呢
create table 【数据库名.】表名称(
    字段名1 数据类型  primary key,
    字段名2 数据类型 【unique key】 not null,
    字段名3 数据类型 not null，
    。。。。
);

例如：
create table test.t_stu(
    sid int primary key,
    sname varchar(20) not null,
    gender char
);
insert into t_stu values(1,'张三','男');

insert into t_stu values(2,null,'男');#错误的
ERROR 1048 (23000): Column 'sname' cannot be null

insert into t_stu values(2,'李四',null);

3、建表后如何指定某个字段非空呢？
alter table 【数据库名.】表名称 modify 字段名 数据类型 not null;

create table test.t_stu(
    sid int primary key,
    sname varchar(20),
    gender char
);
alter table t_stu modify sname varchar(20) not null;

4、建表后，取消某个字段的非空约束
alter table 【数据库名.】表名称 modify 字段名 数据类型;

alter table t_stu modify sname varchar(20);

1、默认值约束
特点：
（1）一个表可以有很多的默认值约束
（2）默认值约束只能针对某一个字段来说
（3）默认值约束意味着，该字段如果没有手动赋值，会按默认值处理

2、如何在建表时指定默认值约束？
create table 【数据库名.】表名称(
    字段名1 数据类型  primary key,
    字段名2 数据类型 【unique key】【not null】 default 默认值,
    字段名3 数据类型 default 默认值，
    。。。。
);

例如：
create table test.t_stu(
    sid int primary key,
    sname varchar(20) not null,
    gender char default '男'
);

insert into t_stu values(1,'张三');
ERROR 1136 (21S01): Column count doesn't match value count at row 1 ' 列数与值数量不匹配

insert into t_stu values(1,'张三',default);
insert into t_stu(sid,sname) values(2,'李四');

3、建表后如何指定某个字段有默认值呢？
alter table 【数据库名.】表名称 modify 字段名 数据类型 default 默认值;

create table test.t_stu(
    sid int primary key,
    sname varchar(20) not null,
    gender char 
);
alter table t_stu modify gender char default '男';

提醒：如果某个字段既要非空，又要默认值，那么alter 语句后面两个都要写。
create table test.t_stu(
    sid int primary key,
    sname varchar(20) not null,
    gender char not null
);
增加gender还有默认值
alter table t_stu modify gender char default '男' not null;

4、如何取消某个字段的默认值约束
alter table 【数据库名.】表名称 modify 字段名 数据类型 【not null】; #不写默认值约束

insert into t_stu values (1,'张三','女');
update t_stu set gender = default where sid = 1;

1、检查约束：check
但是mysql暂不支持
在oracle或sql server中使用

create table t_stu(
    sid int primary key,
    sname varchar(20),
    gender char check('男' or '女')
);

insert into t_stu values(1,'张三','男');

insert into t_stu values(2,'李四','妖');

1、自增约束
特点：
（1）一个表只能有一个自增约束
因为一个表只有一个维护自增值的变量。
（2）自增约束的列只能是整数列
（3）自增约束的列必须是键列（主键，唯一键，外键），
实际中一般是主键自增最多

2、如何在建表时指定某个列自增
create table 【数据库名.】表名称(
    字段名1 xxInt  primary key auto_increment,
    字段名2 数据类型 【unique key】【not null】 default 默认值,
    字段名3 数据类型 default 默认值，
    。。。。
);

create table t_stu(
    sid int auto_increment, #错误 
    sname varchar(20)
);
ERROR 1075 (42000): Incorrect table definition; 
there can be only one auto column and it must be defined as a key

create table t_stu(
    sid int primary key auto_increment,
    sname varchar(20)
);

mysql> desc t_stu;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)


3、如何给自增列添加数据呢
自增列，如果指定值，就按照指定的来，如果没有指定值就自增。
如果指定的值是0或null，就按照自增的来。


insert into t_stu values('张三');#错误的
ERROR 1136 (21S01): Column count doesn't match value count at row 1' 

insert into t_stu(sname) values('张三');  #sid=1
insert into t_stu(sname) values('李四');  #sid=2

insert into t_stu values(5,'王五');      #sid=5
insert into t_stu(sname) values('赵六');  #sid=6
insert into t_stu values(0,'钱七');     #sid=7
insert into t_stu values(null,'王八');   #sid = 8
insert into t_stu values(-1,'负数');     #sid=-1
insert into t_stu(sname) values('测试');    #sid=9
insert into t_stu values(3,'333');         #sid=3
insert into t_stu(sname) values('测试顶顶顶顶');#sid=10

建议：正常使用时，不要乱插

4、建表后增加自增
alter table 【数据库名.】表名称 modify 字段名 数据类型 auto_increment;

5、如何取消自增
alter table 【数据库名.】表名称 modify 字段名 数据类型;

1、外键约束
特点：
（1）一个表可以有很多个外键约束
（2）外键约束是需要一个表的两个字段或两个表的两个字段之间建立外键约束
（3）外键约束一定是在从表/子表中建立的
（4）在从表中外键约束的列，与在主表中外键约束参考的列，这两个列的名称可以不同，
但是意义、数据类型必须一致。
（5）外键约束是同时约束双方的行为的
对于主表来说：修改和删除就受约束了
对于从表来说：添加和修改就受约束了
（6）主表被参考的字段/列必须是键列

建表时，先建主表，再建从表。
删表时，先删从表，再删主表。

从表、子表：参考别人的，依赖别人的
主表、父表：被参考的，被依赖的
举例：
（1）部门表和员工表
    员工表中有一个字段，表示该员工所在的部门
    部门表是主表
    员工表是从表，说员工选择进入哪个部门
    这样的话，外键建立在员工表
    
    部门表中表示部门编号，用did表示,int类型
    员工表中表示该员工所在的部门，我用编号表示，可以使用did，也可以使用deptid int类型
    
（2）学生表，课程表，选课表
    主表：学生表和课程表
    从表：选课表，这个表有两个外键，一个参考学生表，一个参考课程表
    

2、约束的等级（5个）
（1）Cascade方式：级联
    主动权在主表上。
    如果主表被依赖字段修改了，从表对应的外键字段跟着修改
    如果主表被依赖字段的记录删除了，从表对应的外键字段的记录也会删除
（2）Set null方式
    主动权在主表上。
    如果主表被依赖字段修改了，从表对应的外键字段设置为null
    如果主表被依赖字段的记录删除了，从表对应的外键字段的值设置为null
    这里要求，外键字段必须不能有非空约束。
（3）No action方式：不作为
（4）Restrict方式：严格
  （3）（4），如果主表的被依赖字段的值被引用了，那么主表对该字段的修改和删除就被完全限制了。就不能和删除。
  （3）（4）主表没有主动权。必须先处理从表对应的值，然后才能修改和删除。
（5）Set default方式：mysql的Innodb引擎不支持。  
  
3、如何在建表时指定外键
create table 【数据库名.】从表名称(
    字段名1 xxInt  primary key auto_increment,
    字段名2 数据类型 【unique key】【not null】 default 默认值,
    字段名3 数据类型 default 默认值，
    。。。。,
    foreign key(从表的某字段) references 主表名(被参考字段) 【on update 等级】【on delete 等级】 
);
  
例如：
create database 0513db;
use 0513db;

#主表
create table dept(
    did int ,
    dname varchar(20)
);    

#从表
create table emp(
    eid int primary key,
    ename varchar(20),
    did int,
    foreign key(did) references dept(did) on update cascade on delete set null
);
insert into emp values(1,'张三',1);#错误的，因为主表还没有对应记录
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`did`) REFERENCES `dept` (`did`) ON DELETE SET NULL ON UPDATE CASCADE)


insert into dept values(1,'咨询部'),(2,'教学部')
insert into emp values(1,'张三',1);
mysql> insert into emp values(2,'李四',4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`did`) REFERENCES `dept` (`did`) ON DELETE SET NULL ON UPDATE CASCADE)

delete from dept where did = 1;
update dept set did = 5 where did = 2;

4、建表后如何指定外键
alter table 【数据库名.】从表名称 add foreign key(从表的某字段) references 主表名(主表被引用字段) 【on update 等级】【on delete 等级】 ;

5、如何删除外键
alter table 【数据库名.】从表名称 drop foreign key 约束名;

alter table emp drop foreign key emp_ibfk_1;

6、如何查看某个表的外键约束名
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
    
    
SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';    

/*
字符串函数
(1)length(xx)：求字节数，和字符集有关
(2)char_length(xx)：求字符数
(3)CONCAT(str1,str2,...)
(4)CONCAT_WS(分隔符,str1,str2,...)
(5)UPPER(xx)；转大写
(6)LOWER(xx)：转小写
(7)LEFT(s,n)：返回最左边的3个字符
   RIGHT(s,n)：返回最右边的3个字符
(8)LTRIM(s)：去掉左边的空格
   RTRIM(s)：去掉右边的空格
    TRIM(s)：去掉前后空白符
   TRIM(【BOTH 】s1 FROM s) ：从s中去掉前后的s1
   TRIM(【LEADING】s1 FROM s)：从s中去掉开头的s1
   TRIM(【TRAILING】s1 FROM s)：从s中去掉结尾的s1
(9)SUBSTRING(s,index,len)   
*/
#求字符串的长度
#例如：查询员工的姓名的字符的个数
SELECT ename AS "姓名", CHAR_LENGTH(ename) AS "姓名的字数" FROM t_employee;

#求字符串的拼接
SELECT 'hello' + 'world'; #在mysql中，+只表示求和，不表示拼接
SELECT CONCAT('hello','world','java');
SELECT CONCAT_WS('-','hello','world','java');

#转大小写
SELECT UPPER('hello'),LOWER('HELLO');

SELECT LEFT('hello',3);

SELECT CONCAT('[',LTRIM('    hello    world    '),']');

SELECT CONCAT('[',TRIM('    hello    world    '),']');

SELECT TRIM(BOTH '#' FROM '###hello  world###');
SELECT TRIM(LEADING '#' FROM '###hello  world###');
SELECT TRIM(TRAILING '#' FROM '###hello  world###');

SELECT SUBSTRING('hello',2);#从第二个字符，不是下标的概念

/*
数学函数：
*/
SELECT CEIL(2.3),FLOOR(2.3),ROUND(2.3)
SELECT CEIL(2.6),FLOOR(2.6),ROUND(2.6)

SELECT RAND() #[0,1)的随机值

#ROUND(x,y)，保留小数点后y位，看第y+1位，决定四舍五入
#TRUNCATE(x,y)，保留小数点后y位，y位后直接截掉
SELECT ROUND(2.12345,4),TRUNCATE(2.12345,4)

SELECT ename,salary FROM t_employee;
SELECT ename, ROUND(salary,0) FROM t_employee;

/*
日期时间函数
(1)系统时间
NOW()和SYSDATE()

CURRENT_DATE()：只有日期
CURRENT_TIME()：只有时间
CURRENT_TIMESTAMP()：日期时间
(2)求出年等
year(x)
month(x)
day(x)
(3)DATEDIFF(x,y)：间隔天数
(4)格式化
DATE_FORMAT：把日期转字符串
STR_TO_DATE：把字符串转日期
*/
#系统时间
SELECT NOW()
SELECT SYSDATE()

SELECT CURRENT_DATE()
SELECT CURRENT_TIME()
SELECT CURRENT_TIMESTAMP()

SELECT ename,birthday,YEAR(birthday),MONTH(birthday),DAY(birthday) FROM t_employee
SELECT ename,YEAR(NOW()) - YEAR(birthday) AS "年龄" FROM t_employee;

SELECT ename, YEAR(NOW()) - YEAR(hiredate) + 1 AS "入职年数" FROM t_employee

SELECT ename,birthday, DAYOFWEEK(birthday) FROM t_employee;
SELECT DAYOFWEEK(NOW())

SELECT birthday,DATEDIFF(NOW(), birthday) FROM t_employee;

SELECT DATEDIFF(NOW(),'2019-05-13');

SELECT DATE_FORMAT(birthday,'%Y年%m月%d日') FROM t_employee;

/*
流程控制函数
（1）if(value,x,y)：如果value为true,返回x,否则y
（2）ifnull(value,x)：如果value是null值，就用x代替，否则用value自己的值
在mysql中null值是非常特殊
①判断null值用：<=>或is null ,is not null，不能用=和!=
②计算null值，所有运算符，遇到null，运算结果都是null
（3）case 字段/表达式 
    when 常量值  then 值/表达式
    when 常量值  then 值/表达式
    when 常量值  then 值/表达式
    else 值/表达式
    end
    
    类似于Java的switch...case
（4）case 
     when 条件1  then  值/表达式
     when 条件2  then  值/表达式
     when 条件3  then  值/表达式
     else 值/表达式
     end
    类似于Java的if..else if...
*/
SELECT IF(FALSE,1,2)

#求员工的姓名和实发工资
#实发工资 = 薪资 + 奖金 = 薪资 + 薪资 * 奖金比例
SELECT ename,salary + salary * commission_pct FROM t_employee;

SELECT ename,salary + salary * IFNULL(commission_pct,0) FROM t_employee;

SELECT ename,job_id, CASE job_id
  WHEN 1 THEN '教学总监'
  WHEN 2 THEN '讲师'
  WHEN 3 THEN '助教'
  ELSE '其他'
  END AS "职位说明"
FROM t_employee;

SELECT ename, salary, CASE 
    WHEN salary >= 20000 THEN '高富帅'
    WHEN salary >= 15000 THEN '潜力股'
    WHEN salary >=  8000 THEN '屌丝'
    ELSE '土鳖'
    END AS "情况"
FROM t_employee;

/*
其他函数
*/

#加密函数
INSERT INTO t_user VALUES('chailinyan','123456');
INSERT INTO t_user VALUES('admin',PASSWORD('123456'));
INSERT INTO t_user VALUES('yanghongqiang',MD5('123456'));

SELECT * FROM t_user WHERE username = 'chailinyan' AND `password` = '123456';
SELECT * FROM t_user WHERE username = 'admin' AND `password` = PASSWORD('123456');
SELECT * FROM t_user WHERE username = 'yanghongqiang' AND `password` = MD5('123456');

/*
分组函数
(1)count(*)：统计记录数
count(*)和count(常量)结果是一样的
count(字段名)统计非null值
(2)sum(x)：总和
(3)max(x)：最大值
   min(x)：最小值
(4)avg(x)：平均值   
*/
#查询员工总数
SELECT COUNT(*) FROM t_employee;
SELECT COUNT(1) FROM t_employee;
SELECT COUNT(100) FROM t_employee;
SELECT COUNT(commission_pct)  FROM t_employee;

#查询男员工数量
SELECT COUNT(*) FROM t_employee WHERE gender = '男';

#查询薪资大于20000的女员工的数量
SELECT COUNT(*) FROM t_employee WHERE gender ='女' AND salary > 20000;

#查询每个月看要给所有员工发的实发工资的总和是多少
#select sum(实发工资) from t_employee;
SELECT SUM(salary + salary*IFNULL(commission_pct,0)) FROM t_employee;
#保留到分，后面四舍五入
SELECT ROUND(SUM(salary + salary*IFNULL(commission_pct,0)),2) FROM t_employee;

#查询最高工资和最低工资
SELECT MAX(salary),MIN(salary) FROM t_employee;

#查询年龄最大的员工的年龄
SELECT MAX(YEAR(NOW()) - YEAR(birthday)) FROM t_employee;

#查询入职最早的一个员工的入职日期
SELECT MIN(hiredate) FROM t_employee;

#查询平均薪资
SELECT AVG(salary) FROM t_employee;

#查询员工的平均年龄
SELECT ROUND(AVG(YEAR(NOW()) - YEAR(birthday)),0) FROM t_employee;

/*
关联查询的结果有七种情况：
（1）A∩B
（2）A
（3）A - A ∩ B
（4）B
（5）B - A ∩ B
（6）A ∪ B
（7）A ∪ B - A ∩ B

如何实现这些结果？
1、内连接：inner join 
    （1）A∩B
2、外连接
（1）左外连：left join
    （2）A
    （3）A - A ∩ B
（2）右外连接：right join
    （4）B
    （5）B - A ∩ B
（3）全外连接：full join
    但是不直接支持全外连接，但是可以使用union（合并）结果来实现以下两种结果
    （6）A ∪ B        用  （2）A  union（合并） （4）B
    （7）A ∪ B - A ∩ B    用  （3）A - A ∩ B  union（合并）（5）B - A ∩ B
*/

/*
内连接：A∩B
1、官方写法
两张表内连接关联查询
    select 字段列表
    from A表名 inner join B表名
    on 1个关联条件
    where 其他条件等

三张表内连接关联查询
    select 字段列表
    from A表名 inner join B表名 inner join C表名
    on 2个关联条件
    where 其他条件等

关联条件的个数 = 表数 - 1
如果少了关联条件，就会笛卡尔积的

2、另一种写法

两张表内连接关联查询
    select 字段列表
    from A表名 , B表名
    where 1个关联条件  and 其他条件等
*/

#查询所有员工的编号，姓名，部门编号和他所在部门的名称
/*
select eid,ename, did,dname 
from t_employee inner join t_department;

错误
错误代码： 1052
Column 'did' in field list is ambiguous
*/
/*
select eid,ename,t_employee.did,dname 
from t_employee inner join t_department;

出现了笛卡尔积  A表的数量 * B表的数量
*/
SELECT eid,ename,t_employee.did,dname 
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did;

#给表名取别名
SELECT eid,ename,emp.did,dname 
FROM t_employee AS emp INNER JOIN t_department AS dept
ON emp.did = dept.did;

SELECT eid,ename,emp.did,dname 
FROM t_employee AS emp , t_department AS dept
WHERE emp.did = dept.did;

#查询员工编号，员工姓名，员工的职位编号，职位名称，部门编号，部门名称
SELECT eid,ename,t_employee.`job_id`,job_name,t_employee.`did`,dname
FROM t_employee INNER JOIN t_job INNER JOIN t_department
ON t_employee.`did` = t_department.did AND t_employee.`job_id` = t_job.`job_id`;

#查询薪资高于15000的男员工编号，员工姓名，员工的职位编号，职位名称，部门编号，部门名称
SELECT eid,ename,t_employee.`job_id`,job_name,t_employee.`did`,dname
FROM t_employee INNER JOIN t_job INNER JOIN t_department
ON t_employee.`did` = t_department.did AND t_employee.`job_id` = t_job.`job_id`
WHERE salary > 15000 AND gender = '男'

/*
左连接：
（1）A
select 字段列表
from A left join B
on 关联条件
where 其他条件 等

（2）A -  A∩B

select 字段列表
from A left join B
on 关联条件
where 关联字段 is null and 其他条件 等

*/

#查询员工的编号，姓名，部门编号，部门名称
#包括那些没有分配部分的员工
SELECT eid,ename,t_employee.did,dname
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did;

#查询那些没有分配部门的员工
SELECT eid,ename,t_employee.did,dname
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL;

/*
右连接：
（1）B
select 字段列表
from A right join B
on 关联条件
where 其他条件 等

（2）B -  A∩B

select 字段列表
from A right join B
on 关联条件
where 关联字段 is null and 其他条件 等

*/

#查询员工的编号，姓名，部门编号，部门名称
#包括那些没有分配部分的员工
SELECT eid,ename,t_employee.did,dname
FROM t_department RIGHT JOIN t_employee
ON t_employee.did = t_department.did;

#查询那些没有分配部门的员工
SELECT eid,ename,t_employee.did,dname
FROM t_department RIGHT JOIN t_employee
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL;

/*
右连接：
（1）B
select 字段列表
from A right join B
on 关联条件
where 其他条件 等

（2）B -  A∩B

select 字段列表
from A right join B
on 关联条件
where 关联字段 is null and 其他条件 等

*/

#查询员工的编号，姓名，部门编号，部门名称
#包括那些没有分配部分的员工
SELECT eid,ename,t_employee.did,dname
FROM t_department RIGHT JOIN t_employee
ON t_employee.did = t_department.did;

#查询那些没有分配部门的员工
SELECT eid,ename,t_employee.did,dname
FROM t_department RIGHT JOIN t_employee
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL;

/*
使用UNION来实现全外连接的查询结果：
（1）A ∪ B
（2）A ∪ B - A ∩ B
迂回成
（1）A UNION B
（2）A - A ∩ B UNION B - A ∩ B
*/

#查询所有员工和所有部门的信息，包括那些没有分配部门的员工和没有安排员工的部门
SELECT * 
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.`did`

UNION

SELECT * 
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.`did`

#查询那些没有分配部门的员工和没有安排员工的部门
SELECT * 
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.`did`
WHERE t_employee.did IS NULL

UNION

SELECT * 
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.`did`
WHERE t_employee.did IS NULL

/*
select语句有6大子句：
(1)from子句
(2)where子句
(3)group by子句
(4)having子句
(5)order by子句
(6)limit子句

强调：每一个select的6大子句的顺序是(1)-(6)

(1)from子句，后面跟表，视图，多行多列的二维表的结构
     from意思从哪里筛选数据
(2)where子句：后面跟条件
     where意思取那几行，要哪些行
(3)group by：后面跟字段
     group by意思是分组
     
 select 结果字段列表
 from 表名称
 where 条件
 group by 分组字段列表;
 要求： 结果字段列表中不要出现和分组无关的字段
 
(4)having：后面跟条件
    having：意味着在分组统计结果中再次对统计结果加条件，不是对原表中的记录加条件
    
    having和where的区别：
    ①where后面不能跟分组函数，having后面可以
    ②where是用于在原表的记录中筛选，having可以是对原表的筛选，但是更多的是用于对统计结果再加条件

(5)order by：后面跟字段或表达式
   order by：排序
   升序：省略asc或加asc
   降序：desc
(6)limit：取几条记录
   limit m,n
   m表示从第几天记录开始取，n表示最多取n条件
   通常用于分页查询
*/

#查询每个部门的人数
SELECT did,COUNT(*) 
FROM t_employee
GROUP BY did

/*
以下代码，语法不报错，但是语义有问题
建议、要求：分组查询的结果的字段列表中不要出现和分组无关的字段
*/
SELECT ename,did,COUNT(*) 
FROM t_employee
GROUP BY did

#查询每个部门的平均薪资
SELECT did,AVG(salary)
FROM t_employee
GROUP BY did;

#查询每个部门的最高薪资
SELECT did,MAX(salary)
FROM t_employee
GROUP BY did;

#查询每一个部门的男、女的最高薪资
SELECT did,gender,MAX(salary)
FROM t_employee
GROUP BY did,gender;

#查询每一个部门的男、女的人数
SELECT did,gender,COUNT(salary)
FROM t_employee
GROUP BY did,gender;

#查询每一个部门的薪资高于15000的员工的人数
SELECT did,COUNT(*)
FROM t_employee
WHERE salary > 15000
GROUP BY did;

#having
#查询每个部门的人数，只显示部门人数超过10人的
/*
select did,count(*)
from t_employee
where COUNT(*) > 10
group by did;

错误代码： 1111
Invalid use of group function
*/
SELECT did,COUNT(*)
FROM t_employee
GROUP BY did
HAVING COUNT(*) > 10

#查询每个部门的男员工的人数，只显示人数超过2人
SELECT did,COUNT(*)
FROM t_employee
WHERE gender = '男'
GROUP BY did
HAVING COUNT(*) > 2


#查询员工姓名和薪资，按照薪资的降序排列
SELECT ename,salary
FROM t_employee
ORDER BY salary DESC

#查询每个部门的员工的人数，按照人数的升序排列
SELECT did,COUNT(*)
FROM t_employee
GROUP BY did
ORDER BY COUNT(*) ASC

#查询每个部门的员工的人数，按照人数的升序排列，如果人数相同，按照did降序排列
SELECT did,COUNT(*)
FROM t_employee
GROUP BY did
ORDER BY COUNT(*) ASC, did DESC


#查询每个部门的男员工的人数，按照部门编号降序排列
SELECT did,COUNT(*)
FROM t_employee
WHERE gender = '男'
GROUP BY did
ORDER BY did DESC

#查询每个部门的男员工的人数，按照部门编号降序排列，显示人数超过1人
SELECT did,COUNT(*)
FROM t_employee
WHERE gender = '男'
GROUP BY did
HAVING COUNT(*)>1
ORDER BY did DESC

/*
limit m,n
m：从第几条开始，第一条，用0表示
n：最多取n条

假设page代表第几页，nums代表每页显示的数量
limit (page-1)*nums ,nums

每页显示10条，第1页  limit (1-1)*10,10  -> limit 0,10
每页显示10条，第2页  limit (2-1)*10,10  -> limit 10,10
每页显示10条，第3页  limit (3-1)*10,10  -> limit 20,10

问？每页显示25条，第5页
limit (5-1)*25, 25
*/

#查询员工表的员工信息，每页显示10条，第1页
SELECT * 
FROM t_employee
LIMIT 0,10

#查询员工表的员工信息，每页显示10条，第2页
SELECT * 
FROM t_employee
LIMIT 10,10

#查询员工表的员工信息，每页显示10条，第1页
SELECT * 
FROM t_employee
LIMIT 20,10