条件查询
USE myemployees;
#1.查询表中的单个字段
SELECT last_name FROM employees;
#2.查询多个字段，查多少个字段，字段的顺序都无所谓的，不一定要根据原表中的顺序来写
SELECT last_name , salary, email FROM employees;
#3.查询所有字段，字段与字段之间用逗号隔开，最后一个字段不要加逗号
#方式一：
SELECT
employee_id,
first_name,
last_name,
email,
phone_number,
job_id,
salary,
commission_pct,
manager_id,
department_id,
hiredate
FROM
employees ;
#这个可以双击表名、或者字段名生成.自动生成的话是下面这样的：自动生成是`employee_id`这样的。在sql语言中那个``有没有是无所谓的(``这个是在键盘左上角的那个键，不是单引号哦，这个叫着重号，就是提示你括起来的是一个字段名或者数据库名、表名什么的，就是提示作用，有没有都无所谓。但是有一个时候必须要用着重号，就是：比如你有一个字段名是name，但是name直接写会被当作关键字，你要让它认为你是字段名或是什么名，你就可以用`name`这样写)，自动生成默认就有。你选中某个语句，按下F12可以格式化你输入的语句
#方式二：
SELECT
`employee_id`,
`first_name`,
`last_name`,
`email`,
`phone_number`,
`job_id`,
`salary`,
`commission_pct`,
`manager_id`,
`department_id`,
`hiredate`
FROM
employees ;
#更快的做法是：但是用这个方式不能改变查询结果的字段顺序，它查询到的顺序和表中的顺序是一样的。
#方式三：
SELECT * FROM employees;
#4.查询常量池的某个常量,查询常量池.注意：SQL语言字符和字符串都是用''括起来的。你查询常量的话，查询结果的字段名就是你常量值
SELECT 100 ;
SELECT 'John';
#5.查询表达式，这个可以查询表达式结果，这个就相当于查询3.33……，SQL没有整数除整数就是整数这个说法的。还有就是查询某个表达式或者常量的话，不用写from某个表，且数据库里面没有这个带查询的值常量也会有查询结果显示。查询的结果是字段名是表达式，比如100 / 3，值是表达式的结果，如3.3333（默认显示小数点后面4位）
SELECT 100 / 3;
#6.查询函数，在select 某个方法;就是相当于先执行那个方法然后再打印或者说显示这个方法的返回值。这个version()方法前面讲过了，它的返回值是数据库软件的版本号。select的结果是：字段名是方法名，值是版本号字符串
SELECT VERSION();
#7.起别名，我们查询表达式、函数、常量的时候字段名显示的样子是它本身的样子，不好看，不易理解，所以我们可以给字段起别名。
/*
写别名的好处：
1.便于理解
2.如果要查询的字段有重名的情况，使用别名可以区分开来
*/
#取别名方式一：
SELECT 100/3 AS 结果; #显示字段名为结果，值为3.3333
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#取别名方式二：省略as
SELECT 100/3 结果;
SELECT last_name 姓,first_name 名 FROM employees;
#要是你想取的别名里面有空格或者#等特殊符号的话，你得把别名加上单引号或者双引号，按照习惯建议用双引号表示别名。
SELECT salary AS "out put" FROM employees;
#如果你是下面这样就最后一个字段被取了别名
SELECT last_name,job_id,salary AS sal FROM employees;
#8.去重
#案例：查询员工表中涉及的全部部门的编号（读题意可以知道这个查询结果是应该不重复出现一样的部门的）
#没有去重写法：select department_id from employees;
SELECT DISTINCT department_id FROM employees;#去重，去重的是这前面有distinct的字段的值去重.distinct这个关键词只能放在所有字段前面，不能SELECT DISTINCT department_id,DISTINCT job_id FROM employees;这样，SELECT DISTINCT department_id,job_id FROM employees;可以这样写，distinct得放在所有字段的前面，要是distinct后面有多个字段的话，是让这个几个字段组合起来是唯一的，即查询结果的中每条记录字段值的组合不会出现一样的。
#9.+号的作用
/*
在java中的+号有两个作用，一个是连接字符串，一个是进行数值运算
但是在SQL中+号的作用只有一个就是数值计算。但是它计算的时候还是有一点不同的，规则如下：
1.+号两边都是数值的话，就作简单的加法运算，结果是值，比如select 12+13;结果就是25
2.+号两边有字符串的话，就会尝试把字符串转为数值，如果转换成功，就把字符串转换得到的数值进行数值运行，如果不成功，就把没有转换成功的字符串当作数值0参与+号的值的运算。比如
select 'abc'+12;结果是12 select '12'+15;结果是27 select 'abc'+'12';结果是12 select 'anb'+'12sf';结果是12
3.+号两边只要有一边是null，结果肯定为null,比如SELECT 12+NULL;结果就是null。不仅仅+，减乘除都是一样的，只要一边有null结果就算null。
*/
SELECT 12+13;
SELECT 'abc'+12;
SELECT '12'+15;
SELECT 'abc'+'12';
SELECT 'anb'+'12sf';
SELECT 12+NULL;
SELECT last_name+first_name AS 姓名 FROM employees;#字符串这个值才加单引号，字段看起来像字符串但是不用加单引号。还有一点要注意的是，显示的是一列，这一列是last_name字段和first_name字段里面对应的值相加的结果（第一个加第一个，第二个加第二个这样对应相加）
#所以要达到案例要求我们就不能像java那样直接用+号了，我们需要用一个函数CONCAT(str1,str2,str3……)这个函数像java里的可变参数，所以括号里面可以有多个参数，没有限制，一个两个三个等等都可以。
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
#要注意的是：concat()括号里面的参数，要是有一条字段为null，那么那条记录的拼接结果是null。比如SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;这个拼接，要是employees表中，第二条记录last_name或者first_name的字段值null，那么拼接结果，第二条记录就是null。
#补充一个知识点：ifnull(表达式1，表达式2)函数
SELECT IFNULL(commission_pct,0) FROM employees;#即要是这个commission_pct字段的值是null，那么这个IFNULL(commission_pct,0)函数返回的结果是0，要是不是null，就返回commission_pct的字段值。当然这个括号里面可以填的不仅仅是字段，还可以是表达式、函数等。它看第一个参数的返回是不是null来决定ifnull返回什么的
#想知道ifnull()的作用在哪个表上的，看后面的from就行了。
SELECT
CONCAT(first_name, ',',last_name, ' , ' ,job_id, ' ,' ,IFNULL (commission_pct,0)) AS OUT_PUT
FROM employees;
条件查询
#——、按条件表达式筛选
#案例1:查询工资>12000的员工信息
SELECT
*
FROM
employees
WHERE salary > 12000 ;
#案例2:查询部门编号不等于90号的员工名和部门编号
SELECT
last_name,
department_id
FROM
employees
WHERE department_id <> 90 ;
#二、按逻辑表达式筛选
#案例1:查询工资z在10000到20000之间的员工名、工资以及奖金
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE salary >= 10000 AND salary <= 20000 ;
#案例2:查询部门编号不是在90到1io之间，或者工资高于15000的员工信息
SELECT
*
FROM
employees
WHERE NOT (department_id >= 90 AND department_id <= 110) OR salary > 15000 ;
#三、模糊查询
/*
like
between and
in
is null和is not nul1
*/
#1.like
#案例1:查询员工名中包含字符a的员工信息
/*
like一般和通配符搭配使用
通配符：
%：表示任意多个字符,包括0个字符，所以下面那个语句就算有一个人的名字就叫a，那么也会被读到
_：表示任意单个字符，这个不包括0个字符
*/
SELECT
*
FROM
employees
WHERE last_name LIKE '%a%' ;
#案例2:查询员工名中第三个字符为n，第五个字符为l的员工名和工资
SELECT
last_name,
salary
FROM
employees
WHERE last_name LIKE '__n_l%' ;#注意这个e前面写了两个_
#案例3:查询员工名中第二个字符为_的员工名。因为这里_是通配符，所以你必须得转义。
#方式一：借系统定义的转移字符\来转义
SELECT
last_name
FROM
employees
WHERE last_name LIKE '_\_%' ;
#方式二：自己定义转移字符，用
SELECT
last_name
FROM
employees
WHERE last_name LIKE '_$_%' ESCAPE '$';# ESCAPE '$'这个表示自定义转移字符$，所以这里的$相当于转移字符。但是只对这一句里面有效果，后面的用$就没有转义效果了。$这个随便写什么，不一定是$
#2.between……and
/*
注意事项：
使用between……and可以语句的简洁度
但是between……and是包含临界值的
两个临界值要是后面的临界值比前面的小，比如改为这样employee_id BETWEEN 120 AND 100 ;那么结果是查询到0条记录。相当于找一个"120<=值 且 值<=100"的记录，这当然找不到了
*/
#案例1:查询员工编号在100到120之间的员工信息
#不使用between……and
SELECT
*
FROM
employees
WHERE employee_id >= 100
AND employee_id <= 120 ;
#使用between……and
SELECT
*
FROM
employees
WHERE employee_id BETWEEN 100
AND 120 ;
#3.in
/*
作用：判断in关键词前面的那个字段的值是不是属于in列表中的某一项，若是返回true，不是返回false
特点：
可以语句的简洁度
in(……)括号里面的值的类型必须都是一个类型的或者说兼容 什么叫兼容呢，比如in('1232',12)这样是没有问题的。但是in('ac',12)就不行
in括号里面值是不支持通配符的，like才支持用通配符
*/
#案例:查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
#方式一：不用in
SELECT
last_name,
job_id
FROM
employees
WHERE job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID = 'AD_PRES' ;
#方式二：使用in
SELECT
last_name,
job_id
FROM
employees
WHERE job_id IN ('IT_PROT', 'AD_VP', 'AD_PRES') ;
#4、is null
/*
null不能用=、<>来判断某个字段值是不是null，得用is和is not来判断
*/
#案例1:查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct IS NULL ;
#案例2:查询有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct IS NOT NULL ;
#补充：安全等于 <=>
/*
这个符号既可以判断值是不是null
它也能判断某个字段值是不是等于某个数值
*/
#案例1:查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct <=> NULL ;
#案例2：查询工资为12000的员工信息
SELECT
last_name,
salary
FROM
employees
WHERE salary <=> 12000 ;
#not between……and的使用。案例：选择工资不在8000到17000的员工的姓名和工资。
SELECT
last_name,
salary
FROM
employees
WHERE salary NOT BETWEEN 8000
AND 17000 ;
排序查询
USE myemployees ;
/*
语法格式：
select 查询列表 from 表
【where筛选条件】
order by 排序列表【asc|desc】;
上面的执行顺序是：from 表->where子句 ->select 列表 ->order by子句。
所以这个order by是后面做的，即先进行前面的查询，然后把查询的结果进行排序。
特点:
1、asc代表的是升序,desc代表的是降序。如果不写，默认是升序
2、ORDER BY后面放字段，表达式，函数，别名都是可以的。且可以放多个东西，不一定只按一个东西进行排序
3、order by子句一般是放在查询语句的最后面，limit子句除外。即有limit子句的话，limit子句放在最后，其他情况下，order by都是放一个子句最后面的。
*/
#案例一:查询员工信息,要求工资从高到低排序【没有where的查询】
SELECT
*
FROM
employees
ORDER BY salary DESC ;
#案例一【扩展】:查询员工信息,要求工资从低到高排序(ASC可以省略)
#方式一：
SELECT
*
FROM
employees
ORDER BY salary ASC ;
#方式二
SELECT
*
FROM
employees
ORDER BY salary ;
#案例2:查询部门编号>=90的员工信息，按入职时间的先后进行排序【有where筛选条件的排序查询】
SELECT
*
FROM
employees
WHERE department_id >= 90
ORDER BY hiredate ASC ;
#案例3:按年薪的高低显示员工的信息和年薪【按表达式排序】
/*
这里要注意的是IFNULL(commission_pct, 0)，为什么怎么写呢？因为IFNULL(commission_pct, 0)字段可能是null，要是这个null参与运算的话，结果就是null，所以我们用了这个方法，让它如果为null
就返回0.还有就是salary * 12 * (1+ IFNULL(commission_pct, 0))是一个表达式，select是可以查询表达式的，后面的那个“年薪”是取了一个别名，不然显示的字段名就是salary * 12 * (1+ IFNULL(commission_pct, 0))
这个表达式，不好看。表达式的查询是会跟着其他列一起扩展的，这个一点和常量一样，直接查询一个select 12;就显示一个记录，要是select id,12 from XX;就会显示id记录一样多的12.还有一点就是：
这里的ORDER BY后面跟的是一个表达式，其实就可以理解为字段名。
*/
SELECT
*,
salary * 12 * (1+ IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY salary * 12 * (1+ IFNULL(commission_pct, 0)) DESC ;
#案例四：按年薪的高低显示员工的信息和年薪【按别名排序】
#ORDER BY后面也支持别名，这就相当于用这个别名代替上面的案例三的salary * 12 * (1+ IFNULL(commission_pct, 0))这个字段名
SELECT
*,
salary * 12 * (1+ IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY 年薪 DESC ;
SELECT LENGTH(last_name) FROM employees;
#案例5:按姓名的长度显示员工的姓名和工资【按函数排序】
#这里先扩展一个函数，length(str)就是返回字符串的长度。
SELECT LENGTH('john');
#函数里面是可以放字段名的，这样的查询的话，就会把每个记录的那个字段的字段值代替那个字段名。这个规律前面讲了那么多函数了，应该已经看出来了吧。
SELECT
LENGTH (last_name) 字节长度,
last_name,
salary
FROM
employees
ORDER BY LENGTH(last_name) DESC ;
#案例6:查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】
/*
下面这样写的话，先会按工资进行升序排列，因为工资这个字段写在前面嘛，然后要是工资一样的话，就按员工编号进行降序排列。
*/
SELECT
*
FROM
employees
ORDER BY salary ASC,
employee_id DESC ;
#注意：ASC可以省略，要注意的是这样写，工资是按升序排的，不是按降序排列的。
SELECT
*
FROM
employees
ORDER BY salary ,
employee_id DESC ;
#总之，ORDER BY 后面放字段，表达式，函数，别名都是可以的。且可以放多个东西，不一定只按一个东西进行排序。
#你看下面这样写也是可以的。
SELECT
* ,LENGTH (last_name) 字节长度
FROM
employees
ORDER BY salary ,
字节长度 DESC ;
#下面讲3个题目
#1.查询员工的姓名和部门号和年薪，按年薪降序按姓名升序
SELECT
last_name,
department_id,
salary * 12 * (1+ IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY 年薪 DESC,
last_name ASC ;
#2.选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT
last_name,
salary
FROM
employees
WHERE salary NOT BETWEEN 8000
AND 17000
ORDER BY salary DESC ;
#3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
#可以按LENGTH(email)排序，但是前面的select没有显示email这个字段的
SELECT
*
FROM
employees WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,
department_id ASC ;
字符函数
/*
常见函数：
函数的概念:类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名。
使用函数的好处:（和方法的好处一样）
1、隐藏了实现细节。你只要知道这个方法需要什么参数，然后功能是什么，返回什么就行了。
2、提高代码的重用性
调用格式:select 函数名(实参列表)【from 表名】; 当实参列表里面有用到字段的时候需要后面有“from 表名”，其他的时候不用加。每个函数必须有返回值。这句语句相当于执行了函数的语句，且把返回值给输出。
分类:
1、单行函数
如concat、 length、ifnull等
2、分组函数
分组函数的功能:做统计使用,又称为统计函数、聚合函数、组函数。
*/
/*
单行函数分为：
字符函数
数学函数
日期函数
其他函数【补充】
流程控制函数【补充】
*/
#——、字符函数
#1.length 获取参数值的字节个数
SELECT LENGTH('john') ;#输出4
SELECT LENGTH('张三丰hahaha') ;#输出15。一个汉字在usf8中占3个字节
#为什么呢？我们可以用下面这个语句看看当前使用的MySQL中的字符的编码表
SHOW VARIABLES LIKE '%char%';
#2.concat 拼接字符串
#比如我们想把last_name和first_name之间用_拼接起来显示，写法如下
SELECT
CONCAT(last_name, '_', first_name) 姓名
FROM
employees ;
#3.upper、lower
SELECT UPPER('john' );#显示JOHN
SELECT LOWER( 'joHn' );#显示john
#示例:将姓变大写,名变小写,然后拼接
SELECT CONCAT (UPPER(last_name),LOWER (first_name)) 姓名 FROM employees;
#4.substr、substring（这个两个方法一样的，substr是substring的缩写）
#substr有四个函数重载,我们输入“substr(”就可以看到提示。这里我们就讲两个重载
#重载一：
#注意:SQL中索引从1开始
SELECT SUBSTR('李莫愁爱上了陆展元',6) out_put;#显示“了陆展元”。因为索引从1开始。这个out_put是我们取的字段别名。表示取[6,末尾]的子字符串。
#重载二：
SELECT SUBSTR('李莫愁爱上了陆展元',7,3) out_put;#显示“陆展元”。表示从7这个索引开始取，包括7，取三个字符。这里是说取3个字符，不是说取三个字节。
#案例:姓名中首字符大写，其他字符小写，然后用_拼接，显示出来
SELECT
CONCAT(
UPPER(SUBSTR(last_name, 1, 1)),
'_',
LOWER(SUBSTR(last_name, 2))
) out_put
FROM
employees ;
#5.instr返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR ('杨不悔殷六侠爱上了殷六侠','殷六侠') AS out_put;#显示4
SELECT INSTR ('杨不悔殷六侠爱上了殷六侠','殷侠') AS out_put;#显示0
#6.trim
#这个表示去除字符前面和后面的空格
SELECT LENGTH(TRIM(' 张翠山 ') ) AS out_put;#所以显示结果是9
#这个表示去除字符前后的aa字符
SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') AS out_put;#结果显示“a张aaaaaaaaaaa翠山a”因为它把aa当一个整体的，所以前后的aa被去除了，但是a后面只有一个a，所以a最后那个a没有被去除
#7、lpad用指定的字符实现左填充，使之达到指定长度。
SELECT LPAD('殷紊素',10,'*')AS out_put;#因为殷素素是三个字符，还不到10个字符，所以在前面填充7个*。所以显示*******殷素素
SELECT LPAD('殷紊素',2,'*')AS out_put;#因为殷素素是三个字符，但是要显示的是两个字符，所以会从右边擦除掉多余的字符，然后显示
#8.rpad用指定的字符实现右填充指定长度
SELECT RPAD('殷紊素',12, 'ab' ) AS out_put;#显示“殷素素ababababa”
#9.replace替换
SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;#会全部替换指定字符
分组函数
#二、分组函数
/*
功能:用作统计使用，又称为聚合函数或统计函数或组函数。即我们给它一组值，然后经过统计和处理，返回一个值
分组函数括号里面一般放字段，当然也可以放别的东西，你放一个常量也没有问题。select sum(12);结果就是12
分类:
sum求和、avg平均值、max最大值、min最小值、count计算个数。一般是这五个，当然也有其他的分组函数，但是这个比较常见
语法：select 分组函数(字段) from 表名
*/
#1、简单的使用
#执行下面这个语句可以看出。分组函数与单行函数不同，单行函数给函数一个字段，返回的是多行记录，这里你给它一个字段返回的就是一个记录
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;#统计这个salary这个字段值在employees非null的记录有多少个
SELECT SUM(salary) 和, AVG(salary) 平均, MAX(salary) 最高, MIN(salary) 最低, COUNT(salary) 个数 FROM employees;
SELECT SUM(salary) 和, ROUND(AVG(salary),2) 平均, MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数 FROM employees;
#2.这几个分组函数支持哪些类型的参数
/*
sum、avg、max、min、count这五个函数的参数都是支持数值类型的数据
像sum和avg的参数一般只支持数值型的数据
像max、min函数的参数可以支持：数值型、日期型、字符型等任何类型的数据，count也一样可以支持任何类型的数据。因为这几个类型都可以用order by来排序。比如日期，2019年是大于2000年的，日期数值大的大。字符比较的是首字母的asc码，asc码大的大，首字母一样比较下一个字符
像count这个函数，就不管什么类型的参数都可以支持了，但是它只统计非null的记录个数
*/
#下面是一些测试而已：
#下面这两个不行，因为sum和avg一般的参数一般只支持数值型的数据。虽然下面这两个语句执行没有报错，但是结果是错的，没有意义，所以我们认为它也是错的。
SELECT SUM(last_name) ,AVG(last_name) FROM employees;
SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;
#下面这两个可以，因为max、min函数的参数可以支持：数值型、日期型、字符型
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT MAX(hiredate),MIN(hiredate) FROM employees;
#3.是否忽略null值
/*
sum把所有记录的某个字段的值相加，要是这个字段某个记录的值为null会直接跳过null值，相当于把所有非null的值相加
avg相当于把那个字段所有非null的记录的值全部相加/那个字段所有非null的记录的个数
max和min统计的时候也是会忽略null值的，相当于统计某个字段中值不是null的最大值和最小值
count是统计某个字段所有非null的记录个数
所有结论：上面五个分组函数都是忽略null值的
*/
#下面是一些测试而已：
SELECT SUM(commission_pct) ,AVG (commission_pct) ,SUM(commission_pct)/35, SUM(commission_pct)/107 FROM employees;
SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT commission_pct FROM employees;
#4.可以和distinct搭配实现去重的运算
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;#结果是397900和691400.SUM(DISTINCT salary)表示把salary字段的记录去重后再求和，SUM(salary)这个表示直接把这个salary中非null的值求和
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;#COUNT(DISTINCT salary)是把工资一样的记录去了，然后再统计个数
#除sum、count外，其他三个分组函数也一样，就不用一个个去展示了
#5.特别介绍一下count
#像sum、avg、max、min没有重载，所有参数一般是字段。但是count函数是有重载的，下面我们来介绍一下count
SELECT COUNT(salary) FROM employees;#统计salary这个字段这一列有多少个记录
SELECT COUNT(*) FROM employees;#统计整个表的记录个数。就算有一行记录所有字段都是null，它也会被统计+1，但是count(字段)要是某个记录的这个字段值是null，统计的个数不会+1的。
#下面的两个写法都一样，括号里面加任何一个常量都一样。和count(*)效果是一样的，都是统计整个表的记录数。它就像是在原来表之前添加一列1或者'崔侠'，且字段名和字段值都是这个常量1/崔侠，只要原表中有记录，就会添加这个常量，然后再统计这个字段名的这一列的记录的个数。count(1)和count(*)一样，就算某个记录的全部字段值都是null，也会被统计+1.
SELECT COUNT('崔侠')FROM employees;
SELECT COUNT(1)FROM employees;
/*
效率:
MYISAM存储引擎下, count(*)的效率高
INNODB存储引擎下(我们现在用的是这个)，count(*)和count(1)的效率差不多，比count(字段)要高一些。因为count(字段)中会判断一下，这个字段是不是null，但是count(1)那一列是不可能有null的，所以count(1)会快一点。count(*)是一直都是蛮快的。
*/
#6.和分组函数一同查询的字段要求是group by后的字段
#你看像下面这样写就不行，虽然语法上并没有报错。但是显示的效果是没有意义的。
SELECT AVG(salary) , employee_id FROM employees;
#习题：先扩展一个函数
#这里用到了datediff(日期类型数据1,日期类型数据2)，表示用第一个日期减后面的日期，得到的天数。
#比如
SELECT DATEDIFF('2022-10-29','2022-11-2'); #显示-4。日期类型用字符常量表示就算这样写的'XX-XX-XX'
SELECT DATEDIFF('2022-2-29','2022-2-1'); #显示null，因为2022年2月没有29号，只有28号
SELECT DATEDIFF('2022-2-28','2022-2-1'); #显示27
#题目：查询员工表中的最大入职时间和最小入职时间的相差天数(DIFFRENCE)
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate) ) DIFFRENCE
FROM employees;
#题目：查询都门编号为90的员工个数
SELECT COUNT(*)
FROM employees
WHERE department_id= 90;#分组查询可以加where筛选符合条件的行然后再统计的。因为执行顺序from>where>select
分组查询
/*
语法格式：
SELECT column,group_function(column)
FROM table
[WHERE condition]
GROUP BY group_by_expression
[having 对分组后字段进行筛选的条件]
[ORDER BY column] ;
注意：group_function(column)表示的是分组函数。where子句是要放在from子句后面的group by的前面的。
执行顺序是：from->where->group by->select->order by（其实select中要是有分组函数，分组函数会比select先执行的）
所以更准确的写法是：
from->where->group by->分组函数->having子句->计算所有表达式(计算完所有表达式然后才好做select嘛)->select->order by
特点:
1、分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果集 group by子句的后面 having
建议：
1.分组函数做条件肯定是放在having子句中
2.能用分组前又能分组后进行筛选的条件,就优先考虑放在where子句中
2、group by子句支持单个字段分组，多个字段分组(多个字段之间用逗号隔开，改变group by后面多字段的顺序是没有任何影响的)，支持表达式和函数
3、分组查询后面也可以跟order by子句的。order by一般是放在最后的，除非有limit子句。
4、select后面的字段和order by后面的字段不一定要求是group by后面有出现的字段，视频里说是要select后面的字段一定要是group by后面出现的字段，但是我试了下面的代码发现
group by后面没有出现的字段也是可以放select后面的。(这句话是我后来加的：虽然在select后面加没有在group by后面出现的字段，没有报错，但是select没有在group by后面出现的字段的值已经没有意义了，为什么呢？因为你分组了，比如下面这个没有报错的代码，你按job_id分组，然后你查询了department_id,email这两个字段，这两个字段既不是分组函数(select后面跟分组函数是有意义的，就算分组函数里面的参数是一个没有在group by后面出现的字段，也是有意义的，原因你看后面的解释就知道了)，也没有在group by后面出现。那么你想一个情况，就是下面这个代码你按照job_id分组，然后那些job_id一样的会被分为一组。然后你分为多少组你select的就会是多少行是吧，这一点你看过那么多分组查询后应该会可以看出来。一个分组会返回一行是吧。但是这一行是那一个分组合成的，你要是这个分组的一个字段所有的字段值都一样，就返回那个一样的值，因为这个是通过job_id分组的嘛，所以这个合成的这一行的job_id都一样。但是这个分组所有的department_id，email不一定一样呀，怎么能用一个值表示这整个分组的这个字段呢？所以不能select非group by后面出现的字段。那么为什么分组函数是在select后面出现的呢？因为分组函数是把整个分组的那个字段统计然后返回的，它可以代表那个分组那个字段的所有记录的平均值、总和等等，这个可以理解吧)
比如：
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),department_id,email
FROM employees
GROUP BY job_id
ORDER BY job_id;
#但是having中的字段是一定要是select中出现了的字段，因为having是对一次查询后的表进行筛选。
#下面可以
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),department_id
FROM employees
GROUP BY job_id
having department_id>10
ORDER BY job_id;
#下面不可以
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id
having department_id>10
ORDER BY job_id;
*/
#简单的分组查询
#案例1 :查询每个工种的最高工资
SELECT MAX(salary), job_id
FROM employees
GROUP BY job_id;
#案例2:查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
#添加分组前的筛选条件
#案例1:查询邮箱中包含a字符的,每个部门的平均工资
SELECT AVG(salary) , department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
#案例2:查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary), manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
#添加分组后的筛选条件
#案例1:查询哪个部门的员工个数>2
/*
1、查询每个部门的员工个数
SELECT cOUNT(*) , department_id
FROM employees
GROUP BY department_id;
2、根据1、的结果进行筛选，查询哪个部门的员工个数>2
(注意要是直接像下面这样写，不行，是错误的。where能做的是对from后面的表进行筛选的，因为employees表中没有count(*)这个字段，所有肯定是错的)
SELECT COUNT(*) , department_id
FROM employees
where COUNT(*)>2
GROUP BY department_id;
所以我们要想的是怎么对分组之后的表进行筛选，前面的例子都是对分组前那个原表中就有的字段进行晒选的。
所以就用到了having
SELECT COUNT(*) , department_id FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
*/
SELECT COUNT(*) , department_id FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
#案例2:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
#注意：如果根据from后面的原始表就能筛选行的筛选条件放where后面，根据已经进行分组操作后的表进行晒选的筛选条件就放在having后面
#1、查询每个工种有奖金的员工的最高工资
SELECT MAX(salary) ,job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;
#2、根据1、结果继续筛选,最高工资>12000。答案就是下面这个：这里分开写是让你刚学好做一点，分为两个步骤来写，先写怎么分组，然后再去写对分组后的表的查询。
SELECT MAX(salary) ,job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
#下面这个是测试：having后面能不能放非分组函数的判断条件，结果证明可以放非分组函数的判断条件.
SELECT MAX(salary) ,job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING job_id>'SA_MB';
#但是必须放分组后的表有的字段的判断条件，你看下面这个查询就不行，会报错
SELECT MAX(salary) ,job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING department_id>10;
#下面这样也不行
SELECT MAX(salary) ,job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING salary>10;
#案例3:查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资
#1、查询每个领导手下的员工固定最低工资
SELECT MIN(salary) , manager_id
FROM employees
GROUP BY manager_id;
#2、添加筛选条件:编号>102
SELECT MIN(salary) , manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id;
#3、添加筛选条件:最低工资>5000
SELECT MIN(salary) , manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary) >5000;
#前面讲的group by都是按单个的字段进行分组的，group by也能按函数或者表达式进行分组。
#案例:按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
#1、查询每个姓名长度一样的员工个数
SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name);
#2、添加筛选条件
/*
#这样写错误
SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING LENGTH(last_name)>5;
#下面这样也是不行的，原因和上面一样
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING LENGTH(last_name)>5;
上面这两个这样写不行，但是下面这一个这样写可以，取了别名后可以用别名或者原来的名字，但是为什么上面这样写不行呢？
因为在having子句中，优先把单行函数看为是函数而不是字段名，LENGTH(last_name)优先被当作函数且last_name是里面的字段，然后having发现
last_name在表中分组后的表中没有找到，所以报错了。但是下面这样给它取了别名就避免的这个问题。它就认为len_name这个是字段。
SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING len_name>5;
但是对于分组函数的话，是having子句不会优先识别这个分组函数为函数的，还是优先把那个分组函数当作是字段名的。
#所以下面这个可以执行成功
SELECT AVG(salary) a,department_id,job_id,length(last_name)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id,length(last_name)
HAVING AVG(salary)>10000
ORDER BY AVG(salary) DESC;
#但是这样不行
SELECT AVG(salary) a,department_id,job_id,length(last_name)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id,length(last_name)
HAVING length(last_name)
ORDER BY AVG(salary) DESC;
#这样也是可以的，因为单行函数放在的是order by中，不是放在having中，having中放的还是分组函数
SELECT AVG(salary) a,department_id,job_id,length(last_name)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id,length(last_name)
HAVING AVG(salary)>10000
ORDER BY length(last_name) DESC;
#下面这样可以，因为给分组函数取了别名，having里面不是直接放单行函数，还是放别名
SELECT AVG(salary) a,department_id,job_id,length(last_name) b
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id,length(last_name)
HAVING b
ORDER BY b DESC;
*/
SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
#下面这样写也是可以的。说明group by和having子句都是支持别名的。但是where子句是不支持别名的。
SELECT COUNT(*) c,LENGTH(last_name) len_name
FROM employees
GROUP BY len_name
HAVING c>5;
#下面讲按多个字段分组
#案例:查询每个部门的每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;#相当于department_id和job_id这两个都一样才被认为是一个分组。
#添加排序
#案例:查询每个部门的每个工种的员工的平均工资，并且按平均工资的高低显示
SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING AVG(salary)>10000
ORDER BY AVG(salary) DESC;
#下面这样也是可以的,order by、group by和having子句都是可以用别名的，where子句不行。
SELECT AVG(salary) a,department_id,job_id b
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,b
HAVING a>10000
ORDER BY a DESC;
连接查询-等值连接查询
#进阶6:连接查询-等值连接
/*
含义:连接查询又称多表查询。当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象:表1有m行，表2有n行，结果=m*n行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件
连接查询的分类：
按年代分类：
1、sql92标准：1992年推出的标准。sql92标准支持内连接和部分外连接。但是在mysql中的sql92只支持内连接，在Oracle等的sql92中可以支持内连接和部分外连接，但是对于外连接也没有支持得很好。
2、sql99标准【推荐】：1999年推出的标准。sql99支持：内连接、外连接、交叉连接。但是在mysql中的sql99标准支持：内连接、外连接(支持内连接和外连接，不支持全外连接)、交叉连接。
按功能分类(分为三类):
内连接:(内连接又分为三类)
等值连接
非等值连接
自连接
外连接:(外连接也分为三类)
左外连接
右外连接
全外连接
交叉连接
*/
#测试，引入主题
#案例：查找beauty和boys表中男女朋友对应的关系
SELECT * FROM beauty;#先给你看看beauty表有什么
SELECT * FROM boys;#给你看看boys表中有什么
SELECT NAME,boyName FROM boys,beauty;#给你看看两个表直接连接并查看男女朋友的结果，发现不对，比如，这样查询发现，柳岩的男朋友怎么有张无忌、鹿晗、黄晓明、段誉呢？
SELECT * FROM boys,beauty;#所以我们查一下这两个表直接连接的完整结果
#然后我们添加连接条件，就正确了
SELECT NAME, boyName FROM boys, beauty
WHERE boyfriend_id= boys.id;#注意，查询中涉及两个表都有的字段要设置"表名.字段名"
#一、sql92标准
#1、等值连接
#案例1:查询女神名和对应的男神名
#其实它的过程是：先产生了全部连接的表(即第一张表的一个字段和第二张表的每一个字段都匹配的表，比如boys表原来有4行3列，beauty有12行7列，结果就有48行10列的那个表，你懂我意思吧)，然后再筛选符合beauty.boyfriend_id= boys.id条件的把它显示出来。
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;
#案例2:查询员工名和对应的部门名
USE myemployees;
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id =departments.department_id;#注意一点：原来这个employees表有107行记录，但是这个查询显示了106行。departments有27行记录，然后employees中有一个记录(即有一个员工)的department_id是null，其他的员工的部门编号在departments表中的27行记录的department_id里字段里都能找到。所以就显示106行。因为null不等于departments表department_id字段里的任何一个值嘛，连接条件没有成立
SELECT NULL=12;#显示null，看出null和任何值运算或者判断结果都是null，所以上面的查询，employees.department_id和departments.department_id只要有一个null，就不能返回1，所以不会显示.
SELECT NULL=NULL;#null=null返回也是null,所以要是等值连接中的WHERE employees.department_id =departments.department_id;这个employees.department_id和departments.department_id都是null，也是不会显示的
SELECT employees.department_id,departments.department_id,employees.department_id =departments.department_id FROM employees, departments;#这个显示很多行，一样的显示1不一样的显示0
SELECT NULL!=NULL;#返回也是null。返回null相当于false。
SELECT last_name FROM employees WHERE NULL!=NULL;#没有报错，但是返回0条记录
SELECT last_name FROM employees WHERE NULL=NULL;#没有报错，但是返回0条记录
#当然，等值连的where中也可以写其他表达式或函数，可以不是带字段的判断
SELECT * FROM employees, departments WHERE 1;#显示连接的全部结果
#2、为表起别名，方便书写，as可以省略。表起别名是在from那里起的
#查询员工名、工种号、工种名
SELECT last_name , e.job_id,job_title
FROM employees AS e,jobs j
WHERE e.job_id=j.job_id;
#注意：下面这样是错误的，表起别名了，在本条语句中就不能用原来的名字了。而且from是在select前执行的，所以执行到select时，别名已经起好了，select就不能用原来表名了
SELECT last_name , employees.job_id,job_title
FROM employees AS e,jobs j
WHERE e.job_id=j.job_id;
#但是字段取别名，还是可以用原来字段名的
SELECT last_name AS aa
FROM employees
WHERE last_name='Kochhar';
#下面这样不行，下面这样写是错误的，因为where在select前执行，所以where执行的时候还不知道aa是last_name字段
SELECT last_name AS aa
FROM employees
WHERE aa='Kochhar';
#字段取别名一般只有order by和limit可以用到这个别名了，因为select很迟执行的
SELECT last_name AS aa
FROM employees
ORDER BY aa;
#看字段取别名，还是可以用原来名字的，用别名也行
SELECT last_name AS aa
FROM employees
ORDER BY last_name;
#3、两个表的顺序是否可以调换。可以调换，丝毫不影响。
#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id, j.job_title
FROM jobs j, employees e
WHERE e.job_id=j.job_id;
#4、可以对连接后的表筛选吗？答：可以
#在等值连接中，where用于筛选连接条件了，你要是想筛选连接后的表，得用"and+筛选条件"
#案例1:查询有奖金的员工名、部门名
SELECT last_name, department_name , commission_pct
FROM employees e, departments d
WHERE e.department_id=d.department_id
AND e.commission_ pct IS NOT NULL;
/*
这里总结一个and的理解，之前我以为and是和where、having等一样的关键字呢，其实不然，比如上面这个代码e.department_id=d.department_id
AND e.commission_ pct IS NOT NULL;这个整个是where的子句，相当于where筛选行时，是要求这个e.department_id=d.department_id这个条件
和e.commission_ pct IS NOT NULL这个条件都成立才行。就是并的意义。我还想去看看and这个关键字的执行顺序呢。现在这样一看开就恍然大悟了
e.department_id=d.department_id AND e.commission_ pct IS NOT NULL;这整个一起执行的，是作为where的子句执行的。
*/
#案例2:查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d, locations l
WHERE d.location_id = l.location_id AND city LIKE '_o%';
#5、可以分组吗?答：可以
#案例1:查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d, locations l
WHERE d.location_id=l.location_id
GROUP BY city;
#案例2:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id, MIN(salary)
FROM departments d, employees e
WHERE d.department_id=e.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id;
#注意：通过下面这个代码的测试，判断如果按某个字段分组，会把该字段所有值相等的记录分为一组，也会该字段值为null的分为一组。不会排除掉该字段值为null的记录再进行分组的
SELECT commission_pct,MIN(salary)
FROM employees
GROUP BY commission_pct
#6、可以加排序吗？答：可以
#案例:查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e, jobs j
WHERE e.job_id=j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;
#7、可以实现三表连接？答：可以。你要等值连接多个表就用多个and来进行等值连接的筛选。你要连接两个表就用一个and，三个就继续添加and就行了
#然后你要是想连接完表后继续添加筛选行的条件，就继续添加and就行了(现在我对and的理解有改变了，你看下面的对and的总结就懂我意思了)。
/*
这里总结一个and的理解，之前我以为and是和where、having等一样的关键字呢，其实不然，比如上面这个代码e.department_id=d.department_id
AND e.commission_ pct IS NOT NULL;这个整个是where的子句，相当于where筛选行时，是要求这个e.department_id=d.department_id这个条件
和e.commission_ pct IS NOT NULL这个条件都成立才行。就是并的意义。我还想去看看and这个关键字的执行顺序呢。现在这样一看开就恍然大悟了
e.department_id=d.department_id AND e.commission_ pct IS NOT NULL;这整个一起执行的，是作为where的子句执行的。
*/
#案例:查询员工名、都门名和所在的名字以s开头的城市
SELECT last_name, department_name, city
FROM employees e, departments d, locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id
AND city LIKE 's%';
连接查询-非等值连接查询
#进阶6:连接查询-非等值连接.
#就是连接条件不是等于就行了。比如连接条件是大于、小于、不等于、在什么和什么之间、like呀等等
#案例1:查询员工的工资和工资级别
SELECT salary, grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;
#非等值连接也可以加筛选和排序等
#查询工资级别为A的员工工资,并让工资从高到低排序地显示
SELECT salary, grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
AND g.grade_level='A'
ORDER BY salary DESC;
自连接
#进阶6:连接查询-自连接
#表自己和自己连接，可以看为自己复制了一个表，然后自己和自己生成的复制品连接，前面两种连接查询都是一个表和其他表之间的连接。自连接就是把自己这个表当作两张表或者更多的表去使用.然后自连接的筛选条件可以用等值的筛选，也可以用非等值的筛选。
#案例:查询员工名和上级的名称。(这两个员工和上级的名称都放在一个表里面，就是有一个表放了全部员工的id和信息，一个员工的信息后面有一个字段可以知道它上级的id，所以通过自连接可以用员工的上级id，在这个表里面去找到它上级的信息，因为都放在一个表里嘛，所以需要自连接)
#自连接一般都是要用到别名的
#案例:查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e, employees m
WHERE e.manager_id =m.employee_id ;
连接查询-sql99
内连接（包括等值连接、非等值连接、自连接）
#sql99语法
/*
语法:
select 查询列表
from 表1 别名【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
sql92和sql99语法上的不同：
sql92几个表连接用的是逗号隔开，但是在sql99标准中用的是【连接类型】和join隔开
sql92的多表的连接条件用的是where+连接条件，sql99用的是on+连接条件
sql92多表连接后的筛选条件你可以写在where的and后面，sql99你连接后的表的筛选你直接写在where后面
即：在sql99中你多表的连接用“【连接类型】join”，你多表连接的连接条件用“on 连接条件”，你连接后的表的筛选条件用的是【where 筛选条件】，要分组用group by，分组后的筛选用having，想对最终的结果进行排序就用order by
对于上面提到的【连接类型】,是看你用的是什么连接的，你看下面
这是我们sql99主要要学的几个连接(打★的是要重点学的):
内连接(★): 连接类型用inner join关键字
等值
非等值
自连接
外连接：
左外(★) 连接类型用left 【outer】
右外(★) 连接类型用right 【outer】
全外 连接类型用full 【outer】
交叉连接 连接类型用的是cross join
*/
#一）内连接
/*
语法:(注意：from和inner后面都可以给表起别名)
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;
内连接的分类：
等值
非等值
自连接
特点：
①添加排序、分组、筛选
②inner可以省略
③比起sql92，sql99筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
*/
#1、等值连接
#案例1.查询员工名、部门名（inner可以省略）
SELECT last_name,department_name
FROM departments d
JOIN employees e
ON e.`department_id` = d.`department_id`;
#案例2.查询名字中包含e的员工名和工种名（添加筛选条件筛选连接后的表记录）
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`= j.`job_id`
WHERE e.`last_name` LIKE '%e%';
#3. 查询部门个数>3的城市名和部门个数，（添加分组+分组后的筛选）
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
#案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
#1、查询每个部门的员工个数
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
#2、在1、结果上筛选员工个数>3的记录，并排序
SELECT COUNT(*) 个数,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
#5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接，超过两个表的连接就用多个inner join……on……就行了，这个inner join和on可以看为是一起的，即连接类型和连接条件是一对的）
#下面这个三表连接的过程是；employees用e.`department_id`=d.`department_id`这个条件和departments这个表连接生成一个表，然后用这个生成的这个大表和jobs根据e.`job_id` = j.`job_id`连接条件来进行连接（e.`job_id`相当于大表中的来自于employees这个表的那个job_id字段）
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;
#二）非等值连接
#查询员工的工资级别（inner可以省略）
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
#三）自连接
#查询员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`;
#查询姓名中包含字符k的员工的名字、上级的名字（自连接+筛选连接后的表）
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`
WHERE e.`last_name` LIKE '%k%';
外连接查询
#二、外连接
#在我们用的这个mysql中你使用92语法和99语法写的sql都是可以有效的，可以正确执行不报错，
/*
应用场景：用于查询一个表中有，另一个表没有的记录
语法格式(与sql99的内连接一样)：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
特点：
1、外连接的查询结果为主表中的所有记录，然后
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
可以这么认为：外连接查询结果=内连接结果(如果从表中有和它匹配的字段值)+主表中有而从表没有的记录
相当于：你直接画一个主表，然后再看他们的连接条件(即看on),然后主表后面添加从表的字段，把从表中符合连接条件的那些记录直接添加到主表的后面，把那些主表中有但是，没有再从表中匹配的那些主表记录的后面添加null。就是这样组成一张表。但是要注意一点，要是从表里面有多个和主表某一记录匹配的，会让主表这个记录把从表符合匹配条件的记录全部都匹配
2、这里涉及到主表和从表，那么哪个是主表哪个是从表呢？
左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果（即：你把原来“表1 left join 表2”,写为“表2 right join 表1”，效果是一样的）
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
5、这几个外连接的outer都是可以省略的
*/
#引入：查询男朋友不在男神表的女神名。（题目里怎么看哪个表是主表呢？就看，你想要查询的东西是什么，这里我们看出来题意是，你要查询的东西主要是女神嘛不是男神，所以你连接的结果是要显示所有女神的嘛，然后把男朋友不在男神表的女神名筛选并select出来）
#注意：bo.*就是那个bo表示的那个表boys的全部字段。不要*前面加了一个别名就不认识了。*就是取全部字段，你直接写一个字段就是查询一个字段的值，你用*就是查询全部字段的值。之前你没有用别名，直接用*，是因为之前用*的地方都是from后面跟了一个表，没有出现from后面跟多个表
SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id= bo.id
WHERE bo.id IS NULL;
SELECT * FROM boys;
SELECT * FROM beauty;
#案例1：查询哪个部门没有员工
#左外
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
#右外
SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
#全外连接(这个在mysql中是不支持的，但是使用方法还是讲一下，这样以后也可以知道全外连接是怎么用的，以后你用Oracle等软件的时候可以用全外连接)
#全外连接的使用如下：
USE girls;
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.id;
#交叉连接
#相当于beauty表和boys笛卡尔乘积的结果，在92版本是下面这样写的，99版本就不用逗号了，用cross join这个连接两个表。即，beauty的每一个记录都匹配一遍boys的全部记录，所以beauty有12行记录，boys有4行记录，迪达克乘积的结果就是48行的一张表
/*
SELECT b.*,bo.*
FROM beauty b,boys bo;
*/
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;#48行10列
SELECT *FROM boys;#4行3列
SELECT *FROM beauty;1#2行7列
#sql92和sql99相比，为什么建议使用sql99呢？
/*
因为
从功能上讲：sql99支持的较多
从可读性上讲：sql99实现连接条件和筛选条件的分离，可读性较高
*/
子查询
#进阶7：子查询
/*
含义：
出现在其他语句中的select语句，称为子查询或叫内查询。这个子查询可以用在很多地方，不仅仅是在查找里面用子查询，在增删改里面都可以用子查询。因为我们现在只学到查询且子查询用在查询中是最常见的，所以我们这里以查询中的子查询为例来讲解。
相对于内查询，我们把内查询外部的查询语句，称为主查询或外查询。
例子：
select first_name from employees
where department_id in(
select department_id from departments
where location_id=1700
)
你看上面这个例子不就相当于查询里面的筛选条件里面又是一个查询。那么外面这个select我们称为主查询，里面的那个select语句我们称为子查询。子查询都会放在小括号里面。
其实查询中的子查询也不止放在where后面放其他查询其他位置也行。请看下面的分类就懂了。
（再提醒一遍，我们假设这个子查询是在查询语句里面的，我们以查询里面的子查询为例子）
分类：
按子查询出现的位置分类：
1、放在select后面：
仅仅支持标量子查询(仅支持子查询结果集是一行一列的)
2、放在from后面：
支持表子查询（即结果集只要是一个表就行，管他几行几列都可以）
支持标量子查询
支持列子查询
支持行子查询
3、放在where或having后面：★重点
支持标量子查询 ★用得比较多
支持列子查询 ★用得比较多
支持行子查询（表子查询也行） 用得比较少
4、放在exists后面（放在exists后面的子查询又叫相关子查询）：
支持表子查询（表子查询是：只要是一个表就行，管他几行几列，一行多列行，一列多行也行）
支持标量子查询
支持列子查询
支持行子查询
按子查询的结果集的行列数不同分类：
标量子查询（即结果集只有一行一列），标量子查询也叫单行子查询
列子查询（即结果集只有一列多行），列子查询也叫多行子查询
行子查询（即结果集有一行多列。其实行子查询也可以是表子查询的，即可以是多行多列。）
表子查询（即结果集一般为多行多列，多行多列也包括一行多列，一列多行，一行一列，总之只要是个表就行）
*/
#一、where或having后面
/*
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）
特点：
1、子查询放在小括号内
2、子查询一般放在条件的右侧
3、标量子查询，
一般搭配着单行操作符使用：> < >= <= = <>
列子查询，
一般搭配着多行操作符使用：in、any/some、all。可以> < >= <= = <>搭配any/some、all一起再配上一列多行的表，in的话不能和> < >= <= = <>一起用。> < >= <= = <>后面也不能直接跟个一列多行的表。
4、子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果（即，先进行子查询然后才进行主查询，准确的说应该是运行到包含子查询的语句时，在那个子句中先执行子查询，然后再执行，那个子句中的主查询部分。比如你select有一个子查询，你主查询的from还是先执行的，然后运行到select时，先执行select中的子查询部分，然后再执行select的其他部分）
*/
#1.标量子查询★
#案例1：谁的工资比Abel高？
#①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel';#查到的结果是一行一列，就是一个值。这里查到的是11000
#②查询员工的信息，满足 salary>①结果
SELECT *
FROM employees
WHERE salary>( #这里相当于where salary>11000。当时写where salary>11000的话，就是硬编码了，不够适用以后Abel的工资改变的情况了。
SELECT salary
FROM employees
WHERE last_name = 'Abel' #子查询的select语句不用加;
);
#案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资(这个案例可以说明，一个查询里面可以有多个子查询)
#①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141;
#②查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143;
#③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE employee_id = 141
) AND salary>(
SELECT salary
FROM employees
WHERE employee_id = 143
);
#案例3：返回公司工资最少的员工的last_name,job_id和salary（这个案例说明，子查询用到了分组函数）
#①查询公司的 最低工资
SELECT MIN(salary)
FROM employees
#②查询last_name,job_id和salary，要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
#①查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
#②查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
#③ 在②基础上筛选，满足min(salary)>①，分组后的筛选用的是having
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
#非法使用标量子查询
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT salary #这里查询的是多行的值，但是前面是用>号的，所以会出错。即，前面用的是> < >= <= = <>，然后只要后面这个子查询结果不是一行一列，就不行。后面这个子查询的结果是空也不行
FROM employees
WHERE department_id = 50
);
#2.列子查询（多行子查询）★
#案例1：返回location_id是1400或1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)#这个查询的结果是单列多行的一个表
#②查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
#上面的代码等价于这个
SELECT last_name
FROM employees
WHERE department_id =ANY(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
#如果上面的例子是返回location_id不是1400或1700的部门中的所有员工姓名
#代码是这样的
SELECT last_name
FROM employees
WHERE department_id NOT IN(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
#那么它等价于
SELECT last_name
FROM employees
WHERE department_id <>ALL(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
#案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
#①查询job_id为‘IT_PROG’工种任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
#②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
SELECT MAX(salary)
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
SELECT MIN( salary)
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#3、行子查询（结果集一行多列或多行多列）
#案例：查询员工编号最小并且工资最高的员工信息
#下面用前面的做法来做：
#①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;
#②查询最高工资
SELECT MAX(salary)
FROM employees;
#③查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
)AND salary=(
SELECT MAX(salary)
FROM employees
);
#用行子查询来查询是下面这样写的：
SELECT *
FROM employees
WHERE (employee_id,salary)=( #相当于这个括号里面就是一个表，我们把子查询中要查询的employees表里面的一些东西拿出来当作一个表。然后(employee_id,salary)=(……)就是遍历主查询中的要查询的表的每一个记录，要是那一条记录等于子查询的表的对应的值，那么就返回true，然后主查询的select会把那个主查询表中的那个记录填充到新表中，以便于最后显示最终结果。然后继续看主查询表中的下一个记录
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
#下面是我扩展的行子查询。因为上面这个例子只涉及到了一行多列，没有涉及到多行多列。
#因为下面这个子查询的结果是一行记录，所以要用=来连接
#它表示主查询的表的记录中有某个记录的employee_id和salary要等于子查询的表的记录，那么返回就是true，否则就是返回false。
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT employee_id,salary
FROM employees
WHERE employee_id=109
);
#一行用in来连接也是可以的，但是多行用=来连接就不行
SELECT *
FROM employees
WHERE (employee_id,salary)IN(
SELECT employee_id,salary
FROM employees
WHERE employee_id=109
);
/*
注意，下面这个不行，必须要求(employee_id,salary)和子查询的表的列对应。不然会报错。
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT employee_id,salary,last_name
FROM employees
WHERE employee_id=109
);
*/
#因为这里的子查询的结果是一个多行多列的表格，所以，这里要用in来连接
#它表示主查询的表的记录中有某个记录的employee_id和salary要等于子查询的表的某个记录，那么就返回true，否则返回false。
SELECT *
FROM employees
WHERE (employee_id,salary)IN(
SELECT employee_id,salary
FROM employees
WHERE employee_id=109 OR employee_id=110
);
#二、select后面
/*
仅仅支持标量子查询
*/
#案例：查询每个部门的员工个数
#注意这个select后面的子查询只能是放查询结果是一行一列的子查询。
#相当于主查询的departments表的每一个记录都去判断一下是不是符合筛选条件，因为这里没有筛选条件，所以departments表中的每一个记录都会select一次，然后select的时候就会执行子查询，并判断那个主查询那个正准备select的记录，是否符合子查询的WHERE e.department_id = d.`department_id`筛选条件，子查询表中那个正进行判断的记录要是符合条件，就放到一个临时表里面，不符合就不放进去，然后遍历子查询表的下一条记录，等子查询表的记录全部遍历完，再计算那个临时表的记录数，然后返回给外面的主查询的select，然后扔到一个新的临时表中(这个表是为了主查询select最终显示准备的)，然后看主查询表的第二个记录，继续前面的步骤……。最后把那个主查询要显示的临时表显示出来。
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
) 个数
FROM departments d;
#三、from后面
/*
注意：将子查询结果相当于一张表，且这个select后面的子查询的表必须要起别名。
*/
#案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
#四、子查询放在exists后面（这样的子查询又叫相关子查询）
#exists的使用：使用的格式为exists(子查询)，这个返回的是一个true或false。即，要是exists里面的子查询的表要为空表，就返回0，否则放返回1.它不管子查询结果是返回多少列，只要子查询结果返回的表中有记录就返回1.
#案例1：查询有员工的部门名
#一般来说，能用exist的地方都能用in来代替，所以我们先看用in的写法
#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
SELECT department_id
FROM employees
)
#exists
#相当于遍历主查询的记录，要是第一条记录符合WHERE d.`department_id`=e.`department_id`，即d表的第一条记录的department_id字段的字段值，有等于子查询的employees表的第一个记录的department_id，就把记录添加到一个临时表里面，要是不符合就不添加到临时表里面，然后继续遍历employees的下一个记录，主查询还是第一条记录，继续执行前面的步骤……然后遍历完employees的记录，看看那个临时表是不是有记录，有记录就返回1，没有就返回false，返回1就符合主查询的where判断，就select主查询要select的字段，然后看主查询的第二个记录，继续前面的操作
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);
#案例2：查询没有女朋友的男神信息
#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
SELECT boyfriend_id
FROM beauty
)
#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE bo.`id`=b.`boyfriend_id`
);
分页查询
#进阶8：分页查询 ★
/*
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求。比如你浏览某网站，这个网站比如数据库里面一共有10000条记录，你手机显示这些全部的记录嘛，所以用到了分页，就是把这10000条记录分为若干条为一页，然后你点击下一页就给加载下一页的资源或者你选择跳转到那一页就给你显示那一页的资源，不是一下子要把全部记录都加载过来的，要是一下子把10000条记录都加载过来，加载的时间就很长。
语法： 执行顺序（每一步都生成一个临时表）
select 查询列表 7
from 表 1
【连接类型 join 表2 2
on 连接条件 3
where 筛选条件 4
group by 分组字段 5
having 分组后的筛选 6
order by 排序的字段】 8
limit 【offset,】size; 9
注意点：
1、limit就是用于进行分页查询的
2、offset要显示条目的起始索引（起始索引从0开始，不是从1开始的）
3、size 要显示的条目个数
分页查询的特点：
①limit子句放在查询语句的最后，且执行的顺序也是最后，相当于他的作用是：选择已经操作好的表的哪几项显示出来
②因为我们的客户端会经常向服务器端递交sql请求，所以我们分页查询的这个起始点一般不是固定的一个数，就比如上面那个网站的例子，你要是固定查询的记录的起始位置，那么你就每次用这个sql语句都是从某一个记录开始找n条记录，每次查询的都是一样的，所以我们实际写的时候会用到变量，比如下面这样写：
假设要显示的页数是page这个变量，每页的条目数size这个变量
select 查询列表
from 表
……
limit (page-1)*size,size;
#这样我们，每次点击下一页的时候，你让page+1，然后再去用这个sql语句就可以显示下一页了。
举个例子看看上面的这个(page-1)*size对不对，假设我们的size是10，page一开始是1
第一次看到的是 下标为[(1-1)*10,9]的记录（0和9都取到），即第1条记录到第10条记录都看到了 然后点击网站的下一页，然后page就+1，page就是2了
第二次看到的是 下标为[(2-1)*10,19]的记录，即第11条记录到第20条记录都看到了 然后点击网站的下一页，然后page就+1，page就是3了
然后看到的是 下标为[(3-1)*10,29]的记录 …… ……
*/
#案例1：查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;#第一条记录的索引是0。所以这里就是查询employees表中的从第一条记录开始，数5条记录，然后把他们显示出来
SELECT * FROM employees LIMIT 5;#效果和上面一样，即，如果limit是从第一条记录开始，就可以省略那个【0,】
#案例2：查询第11条——第25条
SELECT * FROM employees LIMIT 10,15;
#案例3：有奖金的员工信息，并且工资较高的前10名显示出来(即，加了筛选和排序)
SELECT
*
FROM
employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10 ;
#下面这个查询的结果是35条
SELECT
*
FROM
employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC ;
#那么要是你限制他显示前100条，那么是什么效果呢？
SELECT
*
FROM
employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 100 ;#这里我们要看第1条记录开始，数100条记录，把他显示出来，但是因为这个查询的表一共就35条，所以只显示了35条，且不报错
联合查询
#进阶9:联合查询
/*
union联合合并:将多条查询语句的结果合并成一个结果，相当于多个查询结果的并集。
语法:
查询语句1
union
查询语句2
union
……;
注意：这个查询语句1，查询语句2……这些语句后面是不加分号的。就在整个联合查询语句的末尾有一个分号。且，这里面的查询语句1.查询语句2……这些查询语句都是一个完整的查询语句，即他们独立出来也可以运行的，你单独执行查询语句1也行，单独执行查询语句2也是ok的
引入的案例:查询部门编号>90或邮箱包含a的员工信息
不用联合查询：
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
使用联合查询：
SELECT *FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
上面这个例子你可能会想union不就是替代or的嘛。其实不然，要是查询的结果要来自于多个表，你就不好用or了，你总不能在from后面直接写两个表吧，那样就两表连接查询了，而且也不好查。所以你那个时候用union就会简单多了。比如下面的这个例子：
#案例:查询中国用户中男性的信息以及外国用户中年男性的用户信息.
SELECT id,cname,csex FROM t_ca WHERE csex='男'
UNION
SELECT t_id,tName,tGender FROM t_ua WHERE tGender='male' ;#这个联合查询是显示一个表，相当于把两个查询结果上下粘连在一起。联合形成的新表的字段是显示id,cname,csex，即第一个查询的字段。因为这一点所以联合查询要求几个联合查询的语句的查询的字段意义一样。你看这个例子，你查询t_ca表的id,cname,csex查询的是id、姓名、性别，你查询t_ua表的t_id,tName,tGender，也是查询id、姓名、性别。所以意义一样。
应用场景:当要查询的结果来自于多个表，且多个表没有直接的连接关系(即不好用from后面连接多个表来实现一个查询)，但查询的信息一致时(即几个联合查询的查询字段的意义一样时)，使用联合查询比较合适。
注意事项：
1、要求多条查询语句的查询列数是一致的!
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3. union关键字默认查询结果是去重的，如果使用union all查询结果是不去重的。
注意事项演示1：当联合查询的几个查询语句查询的字段个数不是一样的，如下，执行就会报错
#案例:查询中国用户中男性的信息以及外国用户中年男性的用户信息。
SELECT id, cname, csex FROM t_ca WHERE csex='男'
UNION
SELECT t_id,tName FROM t_ua WHERE tGender='male' ;
注意事项演示2：当你联合查询的多条查询语句的几个查询字段意义不一样，虽然执行不会报错，但是这样就查询出来也没有意义了。
#案例:查询中国用户中男性的信息以及外国用户中年男性的用户信息
SELECT id, cname FROM t_ca WHERE csex='男'
UNION
SELECT tName,t_id FROM t_ua WHERE tGender='male ';
注意事项演示3：联合查询会自动把结果去重。相当于看联合查询的最终生成的待select显示的临时表，先去重，即比对要显示的临时表的所有记录，把临时表中那些完全一样的记录去除掉(要那临时表里两条记录的所有字段值都一样才算重复会被去重)，然后再显示。要用UNION ALL来连接联合查询的子语句，就不会自动去重了。
#案例:查询中国用户中男性的信息以及外国用户中年男性的用户信息
#会去重的版本
SELECT id, cname FROM t_ca WHERE csex='男'
UNION
SELECT t_id,tname FROM t_ua WHERE tGender='male';
#不去重的版本
#案例:查询中国用户中男性的信息以及外国用户中年男性的用户信息
SELECT id, cname FROM t_ca WHERE csex='男·
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male';
*/
DML增删改
#一、插入语句
#使用场景：就是你的表已经存在了，你想要在某个表里面添加数据的时候就需要用到插入语句
#方式一：经典的插入。
/*
语法：
insert into 表名(列名,...) values(值1,...);
*/
#下面是一些经典插入的要注意事项。
#1.插入的值的类型要与列的类型一致或兼容(兼容就是可以隐式地转换就也可以)
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);#注意：这里你photo这里的数据类型是blob类型的，那个先不讲，以后学。然后因为这里photo字段是可以为null的，所以我们就让他为空。但是不能VALUES(13,'唐艺昕','女','1990-4-23','1898888888',,2);这样空着，你必须在里面填一个null。
/*
如果要插入字段是INT型的就得填充数值
如果要插入字段是VARCHAR、CHAR等字符类型的就得把填充字符(子符就是要用单引号或者双引号括起来的数据，建议使用单引号)
如果要插入字段是日期型的就得用单引号括起来且要符合日期的格式,DATETIME的日期格式为'YYYY-mm-dd HH:ii:ss'，DATE日期型的格式为'YYYY-mm-dd',TIME的日期型格式为'HH-ii:ss'。DATETIME是可以接收'YYYY-mm-dd'这样的字符串，他会隐式地转换为那天的“0时0分0秒”
BLOB是二进制数据类型
字段要是字段nullable的，就是那个字段的值可以为空。或者说，你在图形界面打开表，看到里面的字段后面没有nullable就是那个字段不能为空。
……这里只是随意总结了一下字段的类型，因为没有学到字段的数据类型，之后学到的时候会总结得详细些
*/
#2.不可以为null的列必须插入值。可以为null的列如何插入值？
#方式一：(这一种是，你表后面的括号里面写全部的列名，然后就在你新添加记录想填null的字段的位置直接填上null)
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);
#方式二：(这一种方式是：你要添加某条记录，要想那条记录的某个字段是null值，你就像下面这样，表名后面的括号里面省略你想要填null的字段(这样那个字段就会用默认的值来填充，你没有特意设置默认值，那就默认用null填充)，注意：值依然是对应表名后面的字段的，看下面例子就知道了，即你要是表名后面括号里省略一个字段，那个对应的值的位置也省略不写，这样就能把字段和值对应起来了)
INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'娜扎','女','1388888888');
#3.列的顺序是否可以调换。答：可以，但是也要一一对应上，比如'蒋欣'就是和NAME对应，所以新添加的记录中，name字段的值就是'蒋欣'
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('蒋欣','女',16,'110');
#4.列数和值的个数必须一致
/*
你看下面这个例子，列名有5个，值只有4个，所以出错了
INSERT INTO beauty(NAME,sex,id,phone,boyfriend_id)
VALUES('关晓彤','女',17,'110');
*/
#下面这样是正确的
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('关晓彤','女',17,'110');
#5.可以省略表名后面的括号和列名，这样默认就是所有列，而且列的顺序和表中列的顺序一致，看下面的例子就知道了。
INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);
#方式二：
/*
语法：
insert into 表名
set 列名=值,列名=值,...
*/
#例子：（这种方式表名后面不用写括号和列名，还有就是用了set关键字，要是想新添加的记录的某个字段值是默认值，就不用在set后面写就行了，set后面是用“列名=值”这样的格式的，且多个“列名=值”之间是用逗号隔开的，且对set后面的各个“列名=值”先后顺序是无所谓的）
INSERT INTO beauty
SET id=19,NAME='刘涛',phone='999';
#改变set后面“列名=值”的顺序也没有关系
INSERT INTO beauty
SET id=20,phone='999',NAME='刘涛2';
#两种方式大pk ★
#1、方式一支持插入多行(插入的格式看例子就知道了),方式二不支持
#省略全部列名，可以插入多行
INSERT INTO beauty
VALUES(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2)
,(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2)
,(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2);
#不省略全部列名也可以插入多行
INSERT INTO beauty(id,NAME,sex,phone)
VALUES(26,'唐艺昕1','女','1898888888')
,(27,'唐艺昕2','女','1898888888')
,(28,'唐艺昕3','女','1898888888');
#2、方式一支持子查询，方式二不支持
#即，你insert后面可以跟select语言，那个select语句就是子查询
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','11809866';#这个语句执行后，beauty表里面会插入一行id是26，name是'宋茜'，phone是'11809866'的一条记录。这里相当于这个select后面是一个临时表，只是这个临时表是一行而已，insert后面用子查询就是相当于把临时表整个表的对应的数据插入到某表中对应的字段里面去，就是相当于插入一个表的数据嘛，但是子查询的临时表的列要和待插入表的列对应起来。
#上面这个例子举得有点特别，相当于表中只有一行记录，这样就相当于INSERT INTO beauty(id,NAME,phone) VALUES(27,'宋茜','11809866');这样的效果了。
#看下面的例子，这里子查询的结果是多行的一张表，这张临时表的id字段来自于boys表的id，name1字段来自于boys的boyname字段，这个临时表还有一个字段的字段名是'1234567'，值也是'1234567'，这一点不用讲了吧，常量查询前面说过了，字段名和值都是那个常量，行数会和表一起变化，即那个表中其他字段的记录有多少条，这个常量就会复制多少条。
INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname name1,'1234567'
FROM boys WHERE id<3;#这个相当于把临时表的第一条记录的id字段值填充到beauty表的待添加的新纪录的id字段，name1字段的值填充到beauty表的待添加的新纪录的name字段里面，'1234567'字段的值添加到beauty表的待添加的新纪录的phone字段里面，新纪录剩下的字段值用默认值填充，然后添加临时表里面的第二条记录，……。
#下面讲一个案例试一试自己的应用能力。
#案例：向my_employees表中插入下列数据
/*
ID FIRST_NAME LAST_NAME USERID SALARY
1 patel Ralph Rpatel 895
2 Dancs Betty Bdancs 860
3 Biri Ben Bbiri 1100
4 Newman Chad Cnewman 750
5 Ropeburn Audrey Aropebur 1550
*/
#方式一：
INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);
#方式二：(这个联合查询相当于一个查询，所以这个是在insert后面用了一个子查询，insert后面用子查询就是把子查询后面表的数据依次插入到对应的表里面，这个联合查询就是一个表)
INSERT INTO my_employees
SELECT 1,'patel','Ralph','Rpatel',895 UNION
SELECT 2,'Dancs','Betty','Bdancs',860 UNION
SELECT 3,'Biri','Ben','Bbiri',1100 UNION
SELECT 4,'Newman','Chad','Cnewman',750 UNION
SELECT 5,'Ropeburn','Audrey','Aropebur',1550;
DDL表的创建和删除
#DDL
/*
DDL语言即，数据定义语言。主要用于：库和表的管理。
一、库的管理主要包括
创建、修改、删除
二、表的管理主要包括
创建、修改、删除
关键词(库和表的创建修改删除用的关键字都一样)：
库或表的创建： create
库或表的修改： alter
库或表的删除： drop（这个删除是删除表，前面讲的delete和truncate都是删除表里面的数据，表还是存在的）
*/
#一、库的管理
#1、库的创建
/*
语法：
create database [if not exists]库名 [character set 字符集];
因为这个字符集一般我们用默认的就行了，所以我们一般省略
*/
#案例：创建库Books。
CREATE DATABASE books ;
#带字符集的创建books1数据库
CREATE DATABASE books1 CHARACTER SET utf8;#这个和你上面那个创建books的效果一样
#CREATE DATABASE books ;#如果原来已经有了books数据库，那么你再执行这样的创建数据库，就会报错。所以建议下面这样写。
CREATE DATABASE IF NOT EXISTS books ;#这个语句的意思是：如果没有存在books数据库，就创建books数据库。如果存在就不创建，也不报错。
#2、库的修改
#库名的修改使用下面这个语句，但是这个语句现在已经不能用了，因为这个语句修改容易出现数据丢失，所以在版本升级的时候，就已经丢弃了这句语句了。写出来只是给你了解一下而已，不用学这个语句。
RENAME DATABASE books TO 新库名;
#所以要怎么修改库名呢？答：不能修改。我们无法用sql语句来修改库名了。你非要改可以去找到books文件夹到，然后停止mysql服务，再把文件夹的名字改了。
#库的修改只有两个方面要改：一个是数据库名，一个是数据库的字符集。数据库名不能改了，我们看看能不能改字符集。
#更改库的字符集(比如我们要把books数据库的字符集改为gbk),更改数据库的字符集是可以的，这个语句没有被遗弃。
#格式：ALTER DATABASE 表名 CHARACTER SET 字符集名;
ALTER DATABASE books CHARACTER SET gbk;
#3、库的删除
DROP DATABASE books;
#DROP DATABASE books;要是你删除一个不存在的数据库，就会报错，所以最好删除的语句像下面这样写，如果那个books数据库存在，就删除数据库，不然就不删除。这样如果如果books不存在也不会报错。
DROP DATABASE IF EXISTS books;
#二、表的管理
#1.表的创建 ★
/*
语法：
create table 表名(
列名 列的类型【(长度) 约束】, #这里注意：列名和列的类型是必须的，列的长度和约束是可选的。
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
...
列名 列的类型【(长度) 约束】
)
注意：你没有设置某个字段不能为null，那么他默认就是可以为null的。你没有设置字段的默认值，一般默认值是null，这个默认值得看字段类型的，要是是日期型的就不是null了。
*/
#案例：创建表Book
CREATE TABLE book(
id INT,#编号
bName VARCHAR(20),#图书名。这个20表示这个书名这个字符的最大长度，20表示的是字符数，不是字节数。
price DOUBLE,#价格
authorId INT,#作者编号
publishDate DATETIME#出版日期
);
#案例：创建表author。你要是创建表的时候当前数据库里面已经有这个author表了，就会报错，所以建议还是加上IF NOT EXISTS，这个IF NOT EXISTS是可选的。加上IF NOT EXISTS创建表的时候就会先进行判断，要是发现当前数据库里面有这个表，就不会创建了，这样就不会报错了。
CREATE TABLE IF NOT EXISTS author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
)
#2.表的修改
/*
语法：
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】; #这个类型和约束看情况，delete的时候就不用加，看下面例子就知道了。当然下面例子没有涉及到约束，后面讲。
这个column的话，前面是change可以省略column，但是前面是add|drop|modify这些的话就不能省略，所以建议一律加上column，这样不会混淆，一会加一会不加的。
*/
/*
表的修改包括下面五个内容：
1、修改列名
2、修改列的类型或约束
3、添加新列
4、删除列
5、修改表名
*/
#①修改列名(可以同时改变字段类型)
#格式为：ALTER TABLE 表名 CHANGE 【COLUMN】 旧字段名 新字段名 新字段类型; 注意：这里你不改变列的类型，你也是得加上新列的类型的，没办法，语法就是这样。
#改变列名不改变列类型
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
#不改变列名改变列类型
ALTER TABLE book CHANGE COLUMN pubDate pubDate VARCHAR(4);
#②修改列的类型或约束。修改表时添加约束的语法后面讲。
#格式：ALTER TABLE 表名 MODIFY COLUMN 列名 列的新类型 【新约束】;
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
#③添加新列（默认新添加列是放在原表的列的最后的。你添加了first就是让添加的列在表的开头，你添加“after 列1”就是让新添加的列在某列的后面）
#格式：ALTER TABLE 表名 ADD COLUMN 新列名 新列的类型 【约束】 【first|after 列名】;
ALTER TABLE author ADD COLUMN annual DOUBLE;
#测试添加的列不在最后的添加列方式
CREATE TABLE test_add_column(
t1 INT,
t2 INT,
t3 INT
);
ALTER TABLE test_add_column ADD COLUMN newT INT FIRST;#这样添加的话，表的列的排列就是newT t1 t2 t3
ALTER TABLE test_add_column ADD COLUMN newT2 INT AFTER t2;#这样添加的话，表的列的排列就是newT t1 t2 newT2 t3
#④删除列
#格式：ALTER TABLE 表名 DROP COLUMN 列名;
ALTER TABLE author DROP COLUMN annual;
#⑤修改表名
#格式：ALTER TABLE 旧表名 RENAME 【TO】 新表名;
#3.表的删除
DROP TABLE book_author;
#但是上面的写法要是book_author不存在还去删除的话，就会报错，所以建议像下面这样写。这个IF EXISTS可以用在表和库的创建和删除的语句上，其他就地方就不知道了。
DROP TABLE IF EXISTS book_author;
#4.表的复制
INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
SELECT * FROM Author;
SELECT * FROM copy2;
#1.仅仅复制表的结构（相当于复制了一个去除掉记录的author表，然后把这个复制得到的表的表名叫做copy）
CREATE TABLE copy LIKE author;
#2.复制表的结构+数据
#复制全部数据。下面这个语句相当于将author表的结构复制到copy2中，且把author表的全部数据都复制给copy2表
CREATE TABLE copy2
SELECT * FROM author;
#只复制部分数据。下面这样写相当于把这个子查询的临时表复制到copy3中，包括临时表的结构和数据。临时表的哪些字段的字段类型和约束是和author的字段的字段类型和约束一样的。所以得到的copy3的字段的字段类型和约束都是和author一样的(除了主键和默认值这两个约束，比如临时表不能拿到author表的id字段的主键这个约束，所以原来author的id是主键，但是临时表复制得到的id并不是主键，所以你复制得到的copy3这个表的id就不是主键了，其他的约束都是可以复制过来的)。
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';
#比如你想复制某个表的部分结构，不带任何数据，那么得这么写呢？因为like是复制某个表整个结构，所以还是得用复制表结构+数据的方式，但是你可以用只复制部分数据的这个方式，然后把筛选条件设为全部记录来判断的结果都是false，这样的话，不就得到一个只有框架没有数据的临时表了嘛。
#所以写法如下：
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;
#要把别的数据库的表复制到本数据库，你需要怎么做？
#案例：将myemployees数据库中表departments中的数据插入到test数据库的dept2这个新表中
#做法：你选中test数据库，然后执行下面这个语句。
CREATE TABLE dept2
SELECT department_id,department_name
FROM myemployees.departments;
#所以其他跨库的操作也类似，但是你操作的还是本数据库，只是你可以去别的数据库那里拿数据罢了。
常见约束
#常见约束
/*
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性。其实是限制添加数据。
约束的分类：六大约束
1、NOT NULL：非空，用于保证该字段的值不能为空。比如姓名、学号等。
2、DEFAULT:默认，用于保证该字段有默认值。即你插入数据的时候没有填入这个字段的数据，他就会用默认值来填充你新插入记录的那个字段，然后再插入到表里面。比如性别。
3、PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且主键一定是非空的。比如学号、员工编号等
4、UNIQUE:唯一，用于保证该字段的值具有唯一性，但是可以为空。比如座位号。
5、CHECK:检查约束【但是这个约束在mysql中不支持，这个执行不会给你报错，但是没有出现这个约束的效果】。比如性别，你可以通过设置他的check约束让这个字段只能输入“男”或者“女”。
6、FOREIGN KEY:外键，用于限制两个表的关系，用于保证该表的这个字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。
比如例子：你学生表里面有学号，姓名，专业号。你有另一个表，叫专业表，这个表里面有专业号，专业名称。可以把学生表的专业号这个字段设置外键，然后这个外键关联自专业表的专业号。所以你学生表相当于从表，专业表相当于主表。
添加约束的时机：
1.创建表时
2.修改表时
约束的添加分类：
列级约束：
六大约束语法上都支持，但是效果上这样添加的外键约束是没有效果的，添加其他约束有效果。
表级约束：
除了非空、默认这两个约束，其他的约束这样添加约束都是有效的。
注意：列级约束不能添加约束名，表级约束可以添加约束名
主键和唯一的大对比：
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许多个列组合为一个键
主键 √ × 至多有1个 √，但不推荐
唯一 √ √ 可以有多个 √，但不推荐
注意：添加唯一键约束的字段的记录中是不能重复的，null值也不能重复。你唯一键约束的字段可以为null，因为null不等于null，所以允许一个表里面有多个记录的哪个设置了唯一键的字段都会null。
比如：
这个stuinfo这个表的第四个字段是非空约束的，但是下面这样插入两条记录的那个字段值都是null就不行。
insert into stuinfo values (1,'john','男',null,19,1);
insert into stuinfo values (2,'lily','男',null,19,2);
外键：
1、要求在从表设置外键关系，从表引用主表的某个字段的数据。
2、从表的外键列的类型和主表的关联列的类型和意义要求一致，字段名无要求
3、主表的关联列必须是一个key（即主表那个列的约束是主键或唯一键，其实是外键也行，但是一般是主键或唯一键约束）
4、插入数据时，先插入主表，再插入从表。就比如你学生表有专业号且是外键，且这个外键来自于专业表，现在你要添加一个新开设的专业的学生，你肯定得先添加专业再添加学生呀。
5、删除数据时，先删除从表，再删除主表。比如你专业表要删除某个专业，但是你要是删除了那个专业，然后学生表要是还有学生是那个专业的怎么办，所以要先把学生表那个专业的学生全删了，然后删专业
*/
#一、创建表时添加约束
#1.添加列级约束
/*
语法：直接在创建表的写字段名和类型的后面追加约束类型即可，看下面例子就行。
只支持添加这些约束：默认、非空、主键、唯一四个约束，外键不能用这个添加列级约束的这个方式来添加约束，check约束mysql中不支持，不管是用列级约束和表级约束添加约束都没有效果。
*/
USE students;
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#添加主键约束.你用图形界面看stuinfo表的这个主键，你会发现，你添加了主键，他的非空就默认选上了。
stuName VARCHAR(20) NOT NULL UNIQUE,#添加非空约束和唯一键。可以同时添加多个约束，直接加就是了，多个约束之间用空格隔开就行了,没有顺序要求。
gender CHAR(1) CHECK(gender='男' OR gender ='女'),#添加检查约束。虽然mysql中添加这个check约束是没有效果的，但是你写了语法上也不会报错的，这里写了是为了让你看看是怎么添加check约束的，是怎么写的，这样以后用要是用sqlserver时也会用。
seat INT UNIQUE,#添加唯一约束
age INT DEFAULT 18,#添加默认约束
majorId INT REFERENCES major(id)#添加外键约束，可以写，语法没有报错，但是没有效果。
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
DESC stuinfo;#这个查看表的结构可以看到表的一部分约束，可以看到表某个字段是否为否空，默认值，这个字段是主键还是唯一键，不能看外键。
SHOW INDEX FROM stuinfo;#查看stuinfo中的所有索引。这样语句显示的一行就是这个表的一个索引。这个语句显示的表的table字段是说这条索引来自于哪个表，non_unique表示这个索引是不是具有唯一性(0表示有唯一性)，key_name表示索引名，colum_name表示索引是针对哪一个列来设置的。你设置主键、外键、唯一键都是会自动在索引表里面添加一个记录的，所以在这里我们可以看到我们的键有没有添加成功。所以你结合desc和这个语句一起看，就可以看到那些约束有没有添加成功了。
#2.添加表级约束
/*
语法：在创建表的各个字段的最下面先写一个逗号，然后写若干个【constraint 约束名】 约束类型(字段名) ,……,【constraint 约束名】 约束类型(字段名)。
注意：【constraint 约束名】可以省略，即不起约束名，你不起约束名，相当于有一个隐藏的【constraint 字段名】，即你那个约束的名字和你字段的名字一样。你起很多约束名时要注意，约束名不能重复。
*/
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT, #注意表字段和表级约束之间要用逗号隔开。
CONSTRAINT pk PRIMARY KEY(id),#添加主键约束
CONSTRAINT uq UNIQUE(seat),#添加唯一键约束
CONSTRAINT ck CHECK(gender ='男' OR gender = '女'),#添加检查约束，语法不报错，但是添加没有效果。这里写出来是给你看看要是在sqlserve等数据库软件里要用表级约束来添加检查约束得怎么添加。
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#添加外键约束
#非空和默认约束不能用表级约束来添加，所以这里就不写了。
);
SHOW INDEX FROM stuinfo;#这里我们可以看到，外键和主键和唯一键约束都都添加上了。看到外键的这个索引的索引名叫fk_stuinfo_major，唯一键的索引名叫uq，但是主键的索引名不叫pk，而是叫PRIMARY，在mysql中这个语句是显示主键索引为PRIMARY的，看不到你给他取的索引名(索引名和那个约束名一样的)，但是在oracle等中看到的是你给他取的索引名，但是mysql中显示的是PRIMARY。
#通用添加字段的写法：★即你的外键用添加表级约束的写法，你添加其他键用添加列级约束的写法。
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20),
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #一般外键的约束名你这么起“fk_当前表名_主表名”。
);
#设置组合的主键
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
seat2 INT,
PRIMARY KEY(id,stuname), #设置组合主键，就是这两个键组合在一起是一个主键。并不是把id和stuname都设为主键，主键只能有一个。
UNIQUE(seat),
CHECK (gender ='男'OR gender ='女'),
FOREIGN KEY (majorid) REFERENCES major(id)
);
SHOW INDEX FROM stuinfo;#这个看起来显示了有两行索引都是stuinfo表且是PRIMARY的。为了验证是只有一个主键和还是两个主键，我们进行下面的测试。
INSERT INTO stuinfo VALUES (1,'john','男',NULL,19,1,1);
INSERT INTO stuinfo VALUES (2,'john','男',NULL,19,2,2);
SELECT * FROM stuinfo;#发现插入成功了，如果上面设置了两个主键，那么上面添加两个学生名字一样的就会添加失败，但是并没有
INSERT INTO stuinfo VALUES (3,'luna','男',NULL,19,1,1);
INSERT INTO stuinfo VALUES (3,'lisha','男',NULL,19,2,2);
SELECT * FROM stuinfo;#发现id一样也行，说明id不是主键。
INSERT INTO stuinfo VALUES (4,'hello','男',NULL,19,1,1);
INSERT INTO stuinfo VALUES (4,'hello','男',NULL,19,2,2);
SELECT * FROM stuinfo;#发现INSERT INTO stuinfo VALUES (4,'hello','男',NULL,19,2,2);这一句没有添加进去，且INSERT INTO stuinfo VALUES (4,'hello','男',NULL,19,2,2);这一句执行的时候提示重复了，所以这就是组合主键，即要两个字段合起来当作一个主键。
#设置组合唯一键
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
seat2 INT,
PRIMARY KEY(id,stuname),
UNIQUE(seat,stuname), #设置组合唯一键也是和设置组合主键一样的，同理。效果这里就不测试了。
CHECK (gender ='男'OR gender ='女'),
FOREIGN KEY (majorid) REFERENCES major(id)
);
#二、修改表时添加约束
/*
1、添加列级约束(只要支持列级约束添加约束的那几个约束都可以用下面这个格式来添加)
alter table 表名 modify column 字段名 字段类型 新约束;
注意修改表时添加列级约束和创建表添加列级约束一样只支持：默认、非空、主键、唯一
2、添加表级约束(只要支持表级约束添加约束的那几个约束都可以用下面这个格式来添加)
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
注意修改表时添加表级约束和创建表添加表级约束一样，除了非空、默认，其他的都支持。
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT
)
DESC stuinfo;
#1.添加非空约束(非空只能用列级约束添加，不能用表级约束添加)
#把原来可以为null的改为不能为null
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
DESC stuinfo;
#把前面修改为not null的字段改为可以为null
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20);
#或者下面这样也行
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
DESC stuinfo;
#2.添加默认约束(默认只能用列级约束添加，不能用表级约束添加)
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
DESC stuinfo;
#3.添加主键(主键可以用列级约束添加，也能用表级约束添加)
#①用列级约束添加主键
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
DESC stuinfo;
#②用表级约束添加主键(这个CONSTRAINT pk可以省略，表示不自己取约束名。因为mysql中主键的约束名总是PRIMARY，所以你起了约束名其实也是没有用的，在其他数据库软件的话有用)
ALTER TABLE stuinfo ADD CONSTRAINT pk PRIMARY KEY(id);
DESC stuinfo;
#修改表时添加组合主键(或者叫联合主键)
ALTER TABLE 表名 ADD CONSTRAINT 约束名(一般是“pk_表名”) PRIMARY KEY(字段1,字段2);
#4.添加唯一(唯一能用列级约束添加，也能用表级约束添加)
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
DESC stuinfo;
#5.添加外键(添加外键只能用表级约束添加，用列级约束添加没有效果，虽然语法上不报错)(不管major和stuinfo有没有记录，都可以添加major的id字段为stuinfo表某个字段的外键，只要那个major表的id是主键\唯一键\外键就行。但是有一点要注意：就是要是主表和从表都有记录时，从表中的外键字段中的所有记录的值要么是null，要么就是能在主表的关联列里面找到对应的值，不然就不能成功添加外键。)
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); #这里majorid是stuinfo表的要添加外键的字段，major这个是外键的主表，id是外键主表的关联列
SHOW INDEX FROM stuinfo;
#CONSTRAINT fk_stuinfo_major这个可以省略，就是表示不自己设置约束名。所以修改表时添加约束要用表级约束来添加也是可以添加约束名的。
#注意可以同时修改列的类型和列的约束，这里回顾一下修改列的类型的格式(前面讲过的)：ALTER TABLE 表名 MODIFY COLUMN 列名 列的新类型 【新约束】;
ALTER TABLE stuinfo MODIFY COLUMN gender VARCHAR(3) UNIQUE;
DESC stuinfo;
#三、修改表时删除约束(其实相当于重新修改了一遍列的约束而已)
#1.删除非空约束
DESC stuinfo;
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;#因为默认的就是可以为null，所以这个null也可以省略。
DESC stuinfo;
#2.删除默认约束
DESC stuinfo;
ALTER TABLE stuinfo MODIFY COLUMN age INT;#相当于把列的约束设置为这样的约束。修改为没有约束。
DESC stuinfo;
#3.删除主键
#主键、唯一键、外键约束你不能用前面修改约束那样的方式来删除，得用drop来删除。还有就是删除这几个键都是用约束名(索引名)来删除的，但是删除主键用DROP PRIMARY KEY。不是用约束名。
#直接删除主键约束，因为一个表就一个主键，所以你这么写，就知道要删除哪一个主键了。
SHOW INDEX FROM stuinfo;
ALTER TABLE stuinfo DROP PRIMARY KEY;
SHOW INDEX FROM stuinfo;
SHOW INDEX FROM stuinfo;
#ALTER TABLE stuinfo MODIFY COLUMN id INT;#删除主键不能用这样修改，只能用上面的drop方式
SHOW INDEX FROM stuinfo;
#4.删除唯一
#删除唯一键不像删除主键那样，因为主键一个表只能有一个字段是主键，但是唯一键，一个表可以有多个字段为唯一键，所以drop唯一键的话后面要跟列名。
SHOW INDEX FROM stuinfo;
ALTER TABLE stuinfo DROP INDEX seat;
SHOW INDEX FROM stuinfo;
SHOW INDEX FROM stuinfo;
#ALTER TABLE stuinfo MODIFY COLUMN seat INT;#删除唯一键不能用这样修改，只能用上面的drop方式
SHOW INDEX FROM stuinfo;
#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
SHOW INDEX FROM stuinfo;#你可以发现，上面的语句删除还有查看索引的时候还有外键，这是因为MySQL的bug，所以你还需要做以下的事情才能删除索引。这个bug就是mysql删除外键的时候会自动再建一个同名的索引，所以你查看的时候发现还有那个外键名字一样的索引。
DROP INDEX fk_stuinfo_major ON stuinfo;#，执行这句语句就行了，如果只是执行这个语句，没有执行上面那个drop语句，也是不能删除这个索引的，所以这两个语句都要写。
SHOW INDEX FROM stuinfo;#这样就行了
#不知道为什么，我一起执行上面4个语句，要执行两次上面4个语句才能删除，但是上面4句语句一句句执行，就可以每句语句执行一遍就删除了外键索引了。
SHOW INDEX FROM stuinfo;
#ALTER TABLE stuinfo MODIFY COLUMN majorid INT;#删除外键也不能用这样修改的方式删除，只能用上面的drop方式
SHOW INDEX FROM stuinfo;
/*
表级约束和列级约束的区别：
位置 支持的约束类型(不谈check，MySQL不支持) 是否可以起约束名
列级约束： 列的后面 语法都支持，但外键没有效果 不可以
表级约束： 所有列的下面 默认和非空不支持，其他支持 可以（mysql中主键取了约束名也没有效果，约束名也用不了）
*/
#补充一点知识：外键设置级联删除和级联置空
#比如现在是major中只有一个id是主键，没有其他键，stuinfo表中没有什么键
#传统的方式添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id);
SHOW INDEX FROM stuinfo;#可以看到外键添加成功。
SELECT *FROM major;#这里看到我们表里面没有数据
INSERT INTO major VALUES(1,'java'),(2,'h5'),(3,'大数据');#我们在表中插入一些数据用于测试
SELECT *FROM stuinfo;#我们这里看到这个表里面也没有数据
INSERT INTO stuinfo
SELECT 1,'john1','女',NULL, NULL,1 UNION ALL
SELECT 2,'john2','女',NULL, NULL,1 UNION ALL
SELECT 3,'john3','女',NULL, NULL,2 UNION ALL
SELECT 4,'john4','女',NULL, NULL,2 UNION ALL
SELECT 5,'john5','女',NULL, NULL,1 UNION ALL
SELECT 6,'john6','女',NULL, NULL,3 UNION ALL
SELECT 7,'john7','女',NULL, NULL,3 UNION ALL
SELECT 8,'john8','女',NULL, NULL,1;
SELECT *FROM stuinfo;#可以看到表的数据已经添加上去了
#比如我们要删除主表的某个专业，因为从表有数据用到了主表的数据，所以下面这个语句执行是会报错的。
DELETE FROM major WHERE id=3;
#上面用的是普通的添加外键的方式，所以删除的时候必须先把从表的对应数据删除才能删除主表的对应数据。
#下面来讲另外两个方式添加外键，这两种方式添加的外键可以不用先删从表再去删除主表的数据。
#第一种：级联删除。(这个方式你删除主表的某个数据，从表引用了这个数据的记录也会被自动删除。这样就不用你手动去删除从表的数据了。)
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;#我们先把原来的外键先删掉
DROP INDEX fk_stu_major ON stuinfo;#因为这个MySQL的bug，我们删除外键时他会自动生成一个同名的索引，我们把这个索引也删了
SHOW INDEX FROM stuinfo;#看到现在这个stuinfo这个表是没有索引的
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;#我们添加外键的时候添加这个几个关键字，ON DELETE CASCADE，这样删除主表的外键关联列的数据时，从表的外键字段为这个值的记录会被顺带着删除
SHOW INDEX FROM stuinfo;#看到现在这个stuinfo这个表的外键索引已经添加成功了
SELECT *FROM stuinfo;#我们先看看这个表的数据，发现还有学生的专业号是3
SELECT *FROM major;#我们先看看这个表的数据，看到3号专业还有。
DELETE FROM major WHERE id=3;#发现这一次删除没有问了
SELECT *FROM stuinfo;#发现从表中专业号是3的学生也被删除了
SELECT *FROM major;#发现主表中3号专业已经删除了
#第二种：级联置空。(这个方式你删除主表的某个数据，然后从表引用了这个主表的这个数据的记录就对应的外键字段的值会被置空)
#我们还是先把外键都删一下
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;
DROP INDEX fk_stu_major ON stuinfo;
SHOW INDEX FROM stuinfo;#看到现在这个stuinfo这个表是没有索引的
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;#我们用这样的添加外键的方式来添加外键
SHOW INDEX FROM stuinfo;#看到外键添加成功了
SELECT *FROM stuinfo;#看到表里面有专业为2的学生
SELECT *FROM major;#看到表里面有2号专业
DELETE FROM major WHERE id=2;#然后我们删除2号专业，发现这个语句也是可以执行成功的
SELECT *FROM stuinfo;#看到原来专业号为2的学生没有被删除，但是他们的专业号变为null了
SELECT *FROM major;#看到专业表里面2号专业被删除成功了
455
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
