进阶1：基础查询

/
语法：
*select 查询列表 from 表名;
类似于：System.out.println(打印东西);

特点：
1、查询列表可以是：表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格
*/

USE myemployees;

1.查询表中的单个字段

SELECT last_name FROM employees;

2.查询表中的多个字段

SELECT last_name,salary,email FROM employees;

3.查询表中的所有字段

方式一 一一列出

SELECT 
    `employee_id`,
    `first_name`,
    `last_name`,
    `phone_number`,
    `last_name`,
    `job_id`,
    `phone_number`,
    `job_id`,
    `salary`,
    `commission_pct`,
    `manager_id`,
    `department_id`,
    `hiredate` 
FROM
    employees ;

方式二 使用*

 SELECT * FROM employees;

4.查询常量值

 SELECT 100;
 SELECT 'john';

5.查询表达式

 SELECT 100%98;

6.查询函数

 SELECT VERSION();

7.起别名

/*
①便于理解
②如果要查询的字段有重名的情况，使用别名可以区分开来

*/

方式一：使用as

SELECT 100%98 AS 结果;

SELECT last_name AS 姓,first_name AS 名 FROM employees;

方式二：使用空格

SELECT last_name 姓,first_name 名 FROM employees;

• 案例：查询salary，显示结果为 out put

  SELECT salary "out put" FROM employees;

8.去重

• 案例：查询员工表中涉及到的所有的部门编号
• 使用Distinct 关键字

SELECT DISTINCT department_id FROM employees;

9.+号的作用

/*

java中的+号：
①运算符，两个操作数都为数值型
②连接符，只要有一个操作数为字符串

mysql中的+号：
仅仅只有一个功能：运算符

select 100+90; 两个操作数都为数值型，则做加法运算
select ‘123’+90;只要其中一方为字符型，试图将字符型数值转换成数值型
如果转换成功，则继续做加法运算
select ‘john’+90; 如果转换失败，则将字符型数值转换成0

select null+10; 只要其中一方为null，则结果肯定为null

• 使用IFNULL()函数排除为空异常

*/

• 案例：查询员工名和姓连接成一个字段，并显示为 姓名
• 拼接使用 Concat(str1, tr2, str3)函数

SELECT CONCAT('a','b','c') AS 结果;
SELECT CONCAT(last_name,first_name) AS 姓名FROM    employees;

进阶2：条件查询

/*

语法：

**select**<br />        **查询列表**<br />    **from**<br />        **表名**<br />    **where**<br />        筛选条件;

分类：

一、按条件表达式筛选

简单条件运算符：> < = != <> >= <=

二、按逻辑表达式筛选
逻辑运算符：
作用：用于连接条件表达式
&& || !
and or not
&&和and：两个条件都为true，结果为true，反之为false
||或or： 只要有一个条件为true，结果为true，反之为false
!或not： 如果连接的条件本身为false，结果为true，反之为false

三、模糊查询
like
between and
in
is null

*/

1、按条件表达式筛选

• 案例1：查询工资>12000的员工信息

SELECT     * FROM    employees WHERE    salary > 12000;

• 案例2：查询部门编号不等于90号的员工名和部门编号
  不等于号“<>”

  SELECT     last_name,department_id 
  FROM    employees
  WHERE    department_id <> 90;

2、按逻辑表达式筛选

• 案例1：查询工资z在10000到20000之间的员工名、工资以及奖金

SELECT    last_name,    salary,    commission_pct 
FROM    employees 
WHERE    salary >= 10000 AND salary <= 20000;

• 案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息

  SELECT    *FROM    employees
  WHERE    NOT(department_id>=90 AND  department_id<=110) OR salary>15000;

3、模糊查询

/*
like
between and
in
is null | is not null

*/

3.1 like

/
特点：
①一般和通配符搭配使用
通配符：
”%” ：任意多个字符,包含0个字符
”_ “：任意单个字符
/

• 案例1：查询员工名中包含字符a的员工信息

  select     * from    employees
  where    last_name like '%a%'; #abc

• 案例2：查询员工名中第三个字符为n，第五个字符为a的员工名和工资

  select    last_name,    salary
  FROM    employees
  WHERE    last_name LIKE '__n_a%';    ##两个下划线+n+下划线+a

• 案例3：查询员工名中第二个字符为_的员工名（推荐使用转义符）

  SELECT    last_name
  FROM    employees
  WHERE    last_name LIKE '_$_%' ESCAPE '$';

3.2 between and

/
①使用between and 可以提高语句的简洁度
②包含临界值
③两个临界值不要调换顺序
/

• 案例1：查询员工编号在100到120之间的员工信息

  SELECT    *
  FROM    employees
  WHERE    employee_id >= 120 AND employee_id<=100;
  #----------------------
  SELECT    *
  FROM    employees
  WHERE    employee_id BETWEEN 120 AND 100;

  3.3 in

  /
  含义：判断某字段的值是否属于in列表中的某一项
  特点：
  ①使用in提高语句简洁度
  ②in列表的值类型必须一致或兼容
  ③in列表中不支持通配符
  /

• 案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号

SELECT    last_name,    job_id
FROM    employees
WHERE    job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';    
#------------------
SELECT    last_name,    job_id
FROM    employees
WHERE    job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');

3.4 is null

/
=或<>不能用于判断null值
is null或is not null 可以判断null值
/

• 案例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;    
  #----------以下为×SELECT    last_name,    commission_pctFROM    employeesWHERE     salary IS 12000;

• 案例3：查询没有奖金的员工名和奖金率

  SELECT    last_name,commission_pct
  FROM    employees
  WHERE    commission_pct <=> NULL;#安全的等于号 “<=>”

• 案例4：查询工资为12000的员工信息

  SELECT  last_name,salary
  FROM      employees
  WHERE     salary <=> 12000;

• IS NULL 和 <=>

IS NULL:仅仅可以判断NULL值，可读性较高，建议使用
<=> :既可以判断NULL值，又可以判断普通的数值，可读性较低

进阶3：排序查询

/
语法：
select 查询列表
from 表名
【where 筛选条件】
*order by 排序的字段或表达式;

执行顺序：from -> where -> select -> order by

特点：
1、asc代表的是升序，可以省略，默认是升序
desc代表的是降序
2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段
3、order by子句在查询语句的最后面，除了limit子句
*/

1、按单个字段排序

SELECT * FROM employees ORDER BY salary DESC;

2、添加筛选条件再排序

• 案例：查询部门编号>=90的员工信息，并按员工编号降序

SELECT *
FROM employees
WHERE department_id>=90
ORDER BY employee_id 
DESC;

3、按表达式排序

• 案例：查询员工信息 按年薪降序

SELECT *,salary*12*(1+IFNULL(commission_pct,0))
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) 
DESC;

4、按别名排序

• 案例：查询员工信息 按年薪升序

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 
ASC;

5、按函数排序

• 案例：查询员工名，并且按名字的长度降序

SELECT LENGTH(last_name),last_name 
FROM employees
ORDER BY LENGTH(last_name) 
DESC;

6、按多个字段排序

• 案例：查询员工信息，要求先按工资降序，再按employee_id升序

  SELECT *FROM employees
  ORDER BY salary DESC,employee_id ASC;

进阶4：常见函数

/*

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节 2、提高代码的重用性
调用：select 函数名(实参列表) 【from 表】;
特点：
①叫什么（函数名）
②干什么（函数功能）

分类：
1、单行函数
如 concat、length、ifnull等
2、分组函数（见5.6节）

功能：做统计使用，又称为统计函数、聚合函数、组函数

常见函数：

一、单行函数1.1、字符函数：    length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)    concat    substr    instr    trim    upper    lower    lpad    rpad    replace1.2、数学函数：    round    ceil    floor    truncate    mod1.3、日期函数：    now    curdate    curtime    year    month    monthname    day    hour    minute    second    str_to_date    date_format1.4、其他函数：    version    database    user1.5、控制函数    if    case

*/

1、字符函数

1.1 length 获取参数值的字节个数

SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');
SHOW VARIABLES LIKE '%char%'#获取字符集

1.2 concat 拼接字符串

SELECT CONCAT(last_name,'_',first_name) 姓名 
FROM employees;

1.3 upper、lower

SELECT UPPER('john');
SELECT LOWER('joHn');

• 示例：将姓变大写，名变小写，然后拼接

SELECT CONCAT(UPPER(last_name),LOWER(first_name))  姓名 
FROM employees;

1.4 substr、substring

注意：索引从1开始

• 截取从指定索引处后面所有字符

SELECT SUBSTR('每个人只能陪你走一段路',4)  out_put;

• 截取从指定索引处指定字符长度的字符

SELECT SUBSTR('每个人只能陪你走一段路',6,8) out_put;#第一个参数为开始位置，第二个参数为长度

• 案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))  out_put
FROM employees;

1.5 instr 返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR('每个人只能陪你走一段路每个人','只能') AS out_put;

1.6 trim，去前后空格

SELECT LENGTH(TRIM('    每个人    ')) AS out_put;
SELECT TRIM('a' FROM 'aaaaaaaaa每aaaaaaaaaaaa个人aaaaaaaaaaaaa')  AS out_put;
#去掉前后的'a'
SELECT TRIM('aa' FROM 'aaaaaaaaa每aaaaaaaaaaaa个人aaaaaaaaaaaaa')  AS out_put;
#去掉前后的'aa'，奇数个a的会留1个

1.7 lpad 用指定的字符实现左填充至指定长度

SELECT LPAD('一段路',2,'*') AS out_put;
#如果长度参数小于当前的字符长度就截取了，输出：'一段'
SELECT LPAD('一段路',12,'*') AS out_put
#输出：*********一段路

1.8 rpad 用指定的字符实现右填充至指定长度

SELECT RPAD('一段路',6,'ab') AS out_put;#输出：一段路aba

1.9 replace 替换

SELECT REPLACE('每个人只能陪你走一段路每个人一段路','一段路','XXX') AS out_put;

2、数学函数

2.1 round 四舍五入

SELECT ROUND(-1.55);#输出：-2
SELECT ROUND(1.567,2);#第二个参数是小数点后保留的位数#输出：1.57

2.2 ceil 向上取整,返回>=该参数的最小整数

SELECT CEIL(-1.02);#输出：-1

2.3 floor 向下取整，返回<=该参数的最大整数

SELECT FLOOR(-9.99);#输出：-10

2.4 truncate 截断

SELECT TRUNCATE(1.69999,1);#第二个参数是小数点后截断的位数#输出：1.6

2.5 mod取余

/
mod(a,b) ： a-a/bb

mod(-10,-3) => -10- (-10)/(-3)（-3）= -1
/

SELECT MOD(10,-3);#输出：1SELECT MOD(-10,-3);#输出：-1SELECT 10%3;#输出：1

3、日期函数

3.1 now 返回当前系统日期+时间

SELECT NOW();#2021-05-03 19:49:02

3.2curdate 返回当前系统日期，不包含时间

SELECT CURDATE();#2021-05-03

3.3curtime 返回当前时间，不包含日期

SELECT CURTIME();#19:49:53

3.4 可以获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW()) 年;#2021SELECT YEAR('1996-12-5') 年;#1996SELECT  YEAR(hiredate) 年 FROM employees;#查询员工的入职时间SELECT MONTH(NOW()) 月;#5SELECT MONTHNAME(NOW()) 月;#May

3.5 str_to_date 将字符通过指定的格式转换成日期

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;#1996-12-05#%Y:四位年份#%y:两位年份#%m:月份（01，02...11，12)#%c:月份（1，2...11，12）#%d:日（01，02...)#%H:小时（24小时制）#%h:小时（12小时制）#%i:分钟（00，01...59）#%s:秒（00，01，02...59）

3.6 查询入职日期为1992—4-3的员工信息

SELECT * FROM employees WHERE hiredate = '1992-4-3';SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3-1992','%c-%d-%Y');

3.7 date_format 将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;#21年05月03日

3.8查询有奖金的员工名和入职日期(xx月/xx日 xx年)

SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期FROM employeesWHERE commission_pct IS NOT NULL;

4、其他函数

SELECT VERSION();SELECT DATABASE();SELECT USER();

5、流程控制函数

5.1 if函数： if else

SELECT IF(10<5,'大','小');#类似三元运算符的操作#小SELECT last_name,commission_pct,IF(commission_pct IS NULL,'呵呵','嘻嘻') 备注FROM employees;#如果奖金位NULL，输出：呵呵；否则输出：嘻嘻。输出作为备注。

5.2 case函数的使用一： switch case

/*
java中
switch(变量或表达式){
case 常量1：语句1;break;
……
default:语句n;break;
}

mysql中

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
……
else 要显示的值n或语句n;
end
*/

/*

案例：查询员工的工资，要求

部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资

*/

SELECT salary 原始工资,department_id,CASE department_id
  WHEN 30 THEN salary*1.1
  WHEN 40 THEN salary*1.2
  WHEN 50 THEN salary*1.3
  ELSE salaryEND AS 新工资
FROM employees;

5.3 case 函数的使用二：类似于 多重if

/*
java中：
if(条件1){
语句1；
}else if(条件2){
语句2；
}
……
else{
语句n;
}

mysql中：

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
……
else 要显示的值n或语句n
end
*/

案例：查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000，显示C级别
否则，显示D级别

SELECT salary,CASE WHEN salary>20000 THEN 'A'WHEN salary>15000 THEN 'B'WHEN salary>10000 THEN 'C'ELSE 'D'END AS 工资级别FROM employees;

6 分组函数

/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数

特点：
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数都忽略null值

3、可以和distinct搭配实现去重的运算

4、count函数的单独介绍
**一般使用count()用作统计行数

5、和分组函数一同查询的字段要求是group by后的字段

*/

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

6.2、参数支持哪些类型

SELECT SUM(last_name) ,AVG(last_name) FROM employees;
SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT MAX(hiredate),MIN(hiredate) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT COUNT(last_name) FROM employees;

6.3、是否忽略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;
#NULL与任何值运算都为NULL，所以忽略了NULL值

6.4、和distinct搭配

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

6.5、count函数的详细介绍

SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
#任何字段只要有一个不为NULL，则会被统计上，常用来统计总行数
SELECT COUNT(1) FROM employees;
#加了一列'1'，其实就是统计行数，COUNT(任何常量字段)

效率：
MYISAM存储引擎下 ，COUNT()的效率高
INNODB存储引擎下，COUNT()和COUNT(1)的效率差不多，比COUNT(字段)要高一些

6.6、和分组函数一同查询的字段有限制

SELECT AVG(salary),employee_id  
FROM employees;
#无意义的查询，8.0版本报错的

进阶5：分组查询

/*
语法：

select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;

特点：
1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类：分组前筛选和分组后筛选

• 问题1：分组函数做筛选能不能放在where后面
  答：不能
• 问题2：where——group by——having
  答：一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

3、分组可以按单个字段也可以按多个字段
4、可以搭配着排序使用

*/

1 简单的分组

• 案例1：查询每个工种的员工平均工资

  SELECT AVG(salary),job_id
  FROM employees
  GROUP BY job_id;

• 案例2：查询每个位置的部门个数

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

2 可以实现分组前的筛选

• 案例1：查询邮箱中包含a字符的 每个部门的最高工资

SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

• 案例2：查询有奖金的每个领导手下员工的平均工资

SELECT AVG(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

3 分组后筛选

• 案例1：查询哪个部门的员工个数>5

①查询每个部门的员工个数

SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;

② 筛选刚才①结果

SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;
#根据分组后的结果再进行筛选，使用Having

• 案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

• 案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资

SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000;

4.添加排序

• 案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序

SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000ORDER BY m ;

5 按多个字段分组

• 案例：查询每个工种每个部门的最低工资,并按最低工资降序

SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;

进阶6：连接查询

/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：

按年代分类：sql92标准:仅仅支持内连接sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接按功能分类：    内连接：        等值连接        非等值连接        自连接    外连接：        左外连接        右外连接        全外连接        交叉连接

*/

SELECT * FROM beauty;SELECT * FROM boys;SELECT NAME,boyName FROM boys,beautyWHERE beauty.boyfriend_id= boys.id;

1、sql92标准

1.1 等值连接

/
① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
/

• 案例1：查询女神名和对应的男神名

  SELECT NAME,boyName FROM boys,beauty
  WHERE beauty.boyfriend_id= boys.id;

• 案例2：查询员工名和对应的部门名

SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;

1.1.2 为表起别名

/
①提高语句的简洁度
②区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
/

• 查询员工名、工种号、工种名

SELECT e.last_name,e.job_id,j.job_title
FROM employees  e,jobs j
WHERE e.job_id = j.job_id;

1.1.3 两个表的顺序可以调换

• 查询员工名、工种号、工种名

SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.job_id=j.job_id;

1.1.4 可以加筛选

• 案例：查询有奖金的员工名、部门名

  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;

• 案例2：查询城市名中第二个字符为o的部门名和城市名

SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id = l.location_id AND city LIKE '_o%';

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

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

1.1.7 可以实现三表连接

• 案例：查询员工名、部门名和所在的城市

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%'
ORDER BY department_name DESC;

1.2、非等值连接

• 案例1：查询员工的工资和工资级别

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

/*

select salary,employee_id from employees;
select * from job_grades;
#以下为创建job_grades命令
CREATE TABLE job_grades(
  grade_level VARCHAR(3), 
  lowest_sal  int, 
  highest_sal int);
 INSERT INTO job_gradesVALUES ('A', 1000, 2999);
 INSERT INTO job_gradesVALUES ('B', 3000, 5999);
 INSERT INTO job_gradesVALUES('C', 6000, 9999);
 INSERT INTO job_gradesVALUES('D', 10000, 14999);
 INSERT INTO job_gradesVALUES('E', 15000, 24999);
 INSERT INTO job_gradesVALUES('F', 25000, 40000);

*/

1.3、自连接

相当于等值连接，在一个表内找。

比如员工名和上级的名称，第一遍查找员工表，找到领导编号；第二遍查找领导表，根据领导编号找到领导名字。

• 案例：查询 员工名和上级的名称

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;

2、sql99语法

/*
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

分类：
内连接（★）：inner

外连接
左外(★):left 【outer】

右外(★)：right 【outer】
全外：full【outer】

交叉连接：cross
*/

2.1 内连接

/*
语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

分类：
等值
非等值
自连接

特点：
①添加排序、分组、筛选

②inner可以省略

③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读

④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
*/

2.2.1 等值连接

• 案例1.查询员工名、部门名

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 jON e.job_id =  j.job_id
WHERE e.last_name LIKE '%e%';

• 案例3.查询部门个数>3的城市名和部门个数，（添加分组+筛选）
  • ①查询每个城市的部门个数
  • ②在①结果上筛选满足条件的

SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations lON d.location_id = l.location_id
GROUP BY city
HAVING COUNT(*)>3;

• 案例4.查询部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
  • ①查询每个部门的员工个数
  • ② 在①结果上筛选员工个数>3的记录，并排序

SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.department_id =d.department_id
GROUP BY department_name

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.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
  • N个表连接需要N-1个连接条件
  • 表是按顺序连接的，保证前面已连接的表有后面的连接条件

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;

2.2.2 非等值连接

• 查询员工的工资级别

SELECT salary, grade_level
FROM employees e
JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

• 查询每个工资级别中员工个数>20的工资级别，并且按工资级别降序

SELECT COUNT(*), grade_level
FROM employees e
JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
GROUP BY grade_level
HAVING COUNT(*) > 20
ORDER BY grade_level DESC;

2.2.3 自连接

• 查询员工的名字、上级的名字

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

2.2外连接

/
*应用场景：用于查询一个表中有，另一个表没有的记录

特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接 = 内连接的结果 + 表1中有但表2没有的 + 表2中有但表1没有的
*/

• 引入：查询男朋友 不在男神表的的女神名

 SELECT * FROM beauty; 
 SELECT * FROM boys; 
 SELECT b.*,bo.* 
 FROM boys bo 
 LEFT OUTER JOIN beauty b ON b.boyfriend_id = bo.id 
 WHERE b.id IS NULL;

• 案例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;

 USE girls; SELECT b.*,bo.* 
 FROM beauty b 
 FULL OUTER JOIN boys bo 
 ON b.boyfriend_id = bo.id; #示例，不可执行

 SELECT b.*,bo.* 
 FROM beauty b
 CROSS JOIN boys bo; #笛卡尔积

• sql92和 sql99
  /
  功能：sql99支持的较多
  可读性：sql99实现连接条件和筛选条件的分离，可读性较高
  /

进阶7：子查询

/
含义：
出现在*其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：

select后面：仅仅支持标量子查询
from后面：支持表子查询
where或having后面：★
标量子查询（单行） √
列子查询 （多行） √
行子查询

exists后面（相关子查询）
表子查询

按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
*/

1、where或having后面

/*
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用

> ,< , >=, <=, =, <>

列子查询，一般搭配着多行操作符使用
in/not in ：等于列表中的任意一个√

any/some：和子查询返回的某一值进行比较
all：和子查询返回的所有值比较

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

*/

1.1 标量子查询★

• 案例1：谁的工资比 Abel 高?

• ①查询Abel的工资

  SELECT salary
  FROM employees
  WHERE last_name = 'Abel'

• ②查询员工的信息，满足 salary>①结果

  SELECT *FROM employees
  WHERE salary>(    SELECT salary    FROM employees    WHERE last_name = 'Abel');

• 案例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号部门d 最低工资的部门id和其最低工资

• ①查询50号部门的最低工资

SELECT  MIN(salary)
FROM employees
WHERE department_id = 50

• ②查询每个部门的最低工资

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id

• ③ 在②基础上筛选，满足min(salary)>①

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 = 250);

1.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  <>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';

1.3 行子查询（结果集一行多列或多行多列）

• 案例：查询员工编号最小并且工资最高的员工信息

SELECT * FROM employees
WHERE (employee_id,salary)=(    SELECT MIN(employee_id),MAX(salary)    FROM employees);

• ①查询最小的员工编号

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

2、select后面

/
仅仅支持标量子查询
/

• 案例：查询每个部门的员工个数

SELECT d.*,(    SELECT COUNT(*)    
            FROM employees e    WHERE e.department_id = d.department_id ) 个数 
            FROM departments d;

• 案例2：查询员工号=102的部门名

SELECT (SELECT department_name,e.department_id    
        FROM departments d    
        INNER JOIN employees e    
        ON d.department_id=e.department_id    
        WHERE e.employee_id=102    ) 部门名;

3、from后面

/
将子查询结果充当一张表，要求必须起别名
/

• 案例：查询每个部门的平均工资的工资等级
• ①查询每个部门的平均工资

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 gON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

4、exists后面（相关子查询）

/*
语法：
exists(完整的查询语句)，表示查询的结果有没有值
结果：
1或0

*/

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);

• 案例1：查询有员工的部门名

#inSELECT department_name
FROM departments d
WHERE d.department_id IN(    SELECT department_id    FROM employees)
#exists
SELECT department_name
FROM departments d
WHERE EXISTS(    SELECT *    FROM employees e    WHERE d.department_id =e.department_id);

• 案例2：查询没有女朋友的男神信息

#inSELECT 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);

4、练习

• 1. 查询和Zlotkey相同部门的员工姓名和工资

• ①查询Zlotkey的部门
• ②查询部门号=①的姓名和工资

  SELECT department_id
  FROM employees
  WHERE last_name = 'Zlotkey'；

  SELECT last_name,salary
  FROM employees
  WHERE department_id = (    SELECT department_id    FROM employees    WHERE last_name = 'Zlotkey')；

• 2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
  • ①查询平均工资
  • ②查询工资>①的员工号，姓名和工资。

    SELECT AVG(salary)FROM employees

SELECT last_name,employee_id,salaryFROM employeesWHERE salary>(    SELECT AVG(salary)    FROM employees);

• 3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
  • ①查询各部门的平均工资
  • ②连接①结果集和employees表，进行筛选

    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id

SELECT employee_id,last_name,salary,e.department_idFROM employees e
INNER JOIN (    SELECT AVG(salary) ag,department_id    
            FROM employees    
            GROUP BY department_id) ag_dep
            ON e.department_id = ag_dep.department_id
            WHERE salary>ag_dep.ag ;

• 1. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
  • ①查询姓名中包含字母u的员工的部门
  • ②查询部门号存在于①中的任一部门的员工号和姓名

SELECT  DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'

SELECT last_name,employee_id
FROM employees
WHERE department_id IN(    SELECT  DISTINCT department_id    
                       FROM employees    
                       WHERE last_name LIKE '%u%');

• 5.查询在部门的location_id为1700的部门工作的员工的员工号
  • ①查询location_id为1700的部门
  • ②查询部门号 = ①中的任一部门号的员工号

SELECT DISTINCT department_id
FROM departments 
WHERE location_id  = 1700

SELECT employee_idFROM employees
WHERE department_id =ANY(    SELECT DISTINCT department_id    
                         FROM departments     
                         WHERE location_id  = 1700);
 #  = ANY 相当于 IN

• 6.查询管理者是K_ing的员工姓名和工资
  • ①查询姓名为K_ing的员工编号
  • ②查询哪个员工的manager_id = ①

SELECT employee_id
FROM employees
WHERE last_name  = 'K_ing'

SELECT last_name,salary
FROM employees
WHERE manager_id IN(    SELECT employee_id    FROM employees    WHERE last_name  = 'K_ing');
# 不知道子查询结果为单行还是多行，用IN

• 7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名
  • ①查询最高工资
  • ②查询工资=①的姓.名

SELECT MAX(salary)FROM employees

SELECT CONCAT(first_name,last_name) "姓.名"
FROM employees
WHERE salary=(    SELECT MAX(salary)    FROM employees);

进阶8：分页查询 ★

/*

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
#offset是要显示条目的起始索引（起始索引从0开始）
#size是 要显示的条目个数

特点：
①limit语句放在查询语句的最后
②公式
要显示的页数 page，每页的条目数size

select 查询列表 from 表 limit (page-1)*size,size;
#找规律如下size=10 page  1  0 2     10 3    20

*/

• 案例1：查询前五条员工信息

SELECT * FROM  employees LIMIT 0,5;
SELECT * FROM  employees LIMIT 5;

• 案例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 ;

进阶9：联合查询

/*
union 联合 合并：将多条查询语句的结果合并成一个结果

语法：
查询语句1
union
查询语句2
union
…

应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

特点：★
1、要求多条查询语句的查询列数是一致的！
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重，如果使用union all 可以包含重复项

*/

• 引入的案例：查询部门编号>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;

• 案例：查询中国用户中男性的信息以及外国用户中年男性的用户信息

SELECT id,cname FROM t_ca 
WHERE csex='男'
UNION ALLSELECT t_id,tname 
FROM t_ua 
WHERE tGender='male';

DML语言

/*
数据操作语言：
插入：insert
修改：update
删除：delete

*/

一、插入语句

方式一：经典的插入

/*
语法：
insert into 表名(列名,…) values(值1,…);

/
SELECT FROM beauty;

• 1.插入的值的类型要与列的类型一致或兼容

  INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);

• 2.不可以为null的列必须插入值。可以为null的列如何插入值？

  • 方式一：

INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)VALUES(13,'唐艺昕','女','1990-4-23','1898888889',NULL,2);

• 方式二：

INSERT INTO beauty(id,NAME,sex,phone)VALUES(15,'娜扎','女','1388888888');

• 3.列的顺序可以调换

  INSERT INTO beauty(NAME,sex,id,phone)VALUES('蒋欣','女',16,'1388888889');

• 4.列数和值的个数必须一致

INSERT INTO beauty(NAME,sex,id,phone)VALUES('关晓彤','女',17,'1388888899');

• 5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致

INSERT INTO beautyVALUES(18,'张飞','男',NULL,'1388888999',NULL,NULL);

方式二：

/*

语法：
insert into 表名
set 列名=值,列名=值,…
*/

INSERT INTO beautySET id=19,NAME='刘涛',phone='13888889999';

两种方式大pk ★

• 1、方式一支持插入多行,方式二不支持

INSERT INTO beautyVALUES(23,'唐艺昕1','女','1990-4-23','18788888888',NULL,2),(24,'唐艺昕2','女','1990-4-23','18788888889',NULL,2),(25,'唐艺昕3','女','1990-4-23','18788888899',NULL,2);

• 2、方式一支持子查询，方式二不支持

INSERT INTO beauty(id,NAME,phone)SELECT 26,'宋茜','11809866';INSERT INTO beauty(id,NAME,phone)SELECT id,boyname,'1234567'FROM boys WHERE id<3;

二、修改语句

/*

1.修改单表的记录★

语法：
update 表名
set 列=新值,列=新值,…
where 筛选条件;

2.修改多表的记录【补充】

语法：
sql92语法：
update 表1 别名,表2 别名
set 列=值,…
where 连接条件
and 筛选条件;

sql99语法：
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,…
where 筛选条件;

*/

• 1.修改单表的记录
  • 案例1：修改beauty表中姓唐的女神的电话为13899888899
  • 案例2：修改boys表中id为2的名称为张飞，魅力值 10

UPDATE beauty SET phone = '13899888899'WHERE NAME LIKE '唐%';

UPDATE boys SET boyname='张飞',usercp=10WHERE id=2;

• 2.修改多表的记录
  • 案例 1：修改张无忌的女朋友的手机号为17788889999
  • 案例2：修改没有男朋友的女神的男朋友编号都为2号

UPDATE boys boINNER JOIN beauty b ON bo.id =b.boyfriend_idSET b.phone ='17788889999',bo.userCP=1000WHERE bo.boyName ='张无忌';

UPDATE boys boRIGHT JOIN beauty b ON bo.id =b.boyfriend_idSET b.boyfriend_id =2WHERE bo.id IS NULL;SELECT * FROM boys;

三、删除语句

/*

• 方式一：delete
  语法：
  1、单表的删除【★】
  delete from 表名 where 筛选条件
  2、多表的删除【补充】
  sql92语法：
  delete 表1的别名,表2的别名
  from 表1 别名,表2 别名
  where 连接条件
  and 筛选条件;
  sql99语法：
  delete 表1的别名,表2的别名
  from 表1 别名
  inner|left|right join 表2 别名 on 连接条件
  where 筛选条件;
• 方式二：truncate
  语法：truncate table 表名;
  */

方式一：delete

1.单表的删除

• 案例：删除手机号以9结尾的女神信息

DELETE FROM beauty WHERE phone LIKE '%9';
SELECT * FROM beauty;

2.多表的删除

• 案例：删除张无忌的女朋友的信息

DELETE b
FROM beauty b
INNER JOIN boys bo ON b.boyfriend_id = bo.id
WHERE bo.boyName ='张无忌';

• 案例：删除黄晓明的信息以及他女朋友的信息

DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.boyfriend_id =bo.id
WHERE bo.boyName ='黄晓明';

方式二：truncate语句

truncate后不能加where，只能删除整张表

• 案例：将魅力值>100的男神信息删除

TRUNCATE TABLE boys ;

delete pk truncate【面试题★】

/*

• delete 可以加where 条件，truncate不能加
• truncate删除，效率高一丢丢
• 假如要删除的表中有自增长列，
  如果用delete删除后，再插入数据，自增长列的值从断点开始，
  而truncate删除后，再插入数据，自增长列的值从1开始。
• truncate删除没有返回值，delete删除有返回值
• truncate删除不能回滚，delete删除可以回滚.

*/

SELECT * FROM boys;DELETE FROM boys;TRUNCATE TABLE boys;INSERT INTO boys (boyname,usercp)VALUES('张飞',100),('刘备',100),('关云长',100);

DDL语言

/*

数据定义语言

库和表的管理

一、库的管理
创建、修改、删除
二、表的管理
创建、修改、删除

创建： create
修改： alter
删除： drop

*/

一、库的管理

1、库的创建

/
语法：
create database [if not exists]库名;
/

• 案例：创建库Books

CREATE DATABASE IF NOT EXISTS books ;

2、库的修改

RENAME DATABASE books TO 新库名;#已废除

更改库的字符集

ALTER DATABASE books CHARACTER SET gbk;

3、库的删除

DROP DATABASE IF EXISTS books;

二、表的管理

1.表的创建 ★

/*
语法：
create table 表名(
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
…
列名 列的类型【(长度) 约束】

)

*/

• 案例：创建表Book

CREATE TABLE book(    id INT,#编号    bName VARCHAR(20),#图书名    price DOUBLE,#价格    authorId  INT,#作者编号    publishDate DATETIME#出版日期);DESC book;#查看表

• 案例：创建表author

  CREATE TABLE IF NOT EXISTS author(    id INT,    au_name VARCHAR(20),    nation VARCHAR(10))DESC author;

2.表的修改

/
语法
*alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;

*/

• ①修改列名

ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

• ②修改列的类型或约束

ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

• ③添加新列

ALTER TABLE author ADD COLUMN annual DOUBLE;

• ④删除列

ALTER TABLE book_author DROP COLUMN  annual;

• ⑤修改表名

ALTER TABLE author RENAME TO book_author;DESC book;

3.表的删除

DROP TABLE IF EXISTS book_author;SHOW TABLES;

• 通用的写法：
  DROP DATABASE IF EXISTS 旧库名;
  CREATE DATABASE 新库名;
  DROP TABLE IF EXISTS 旧表名;
  CREATE TABLE 表名();

4.表的复制

INSERT INTO author VALUES(1,'村上春树','日本'),(2,'莫言','中国'),(3,'冯唐','中国'),(4,'金庸','中国');SELECT * FROM Author;SELECT * FROM copy2;

• 仅仅复制表的结构

CREATE TABLE copy LIKE author;

• 复制表的结构+数据

CREATE TABLE copy2 SELECT * FROM author;

• 只复制部分数据

CREATE TABLE copy3SELECT id,au_nameFROM author WHERE nation='中国';

• 仅仅复制某些字段

CREATE TABLE copy4 SELECT id,au_nameFROM authorWHERE 0;

常见的数据类型

/*
数值型：
整型
小数：
定点数
浮点数
字符型：
较短的文本：char、varchar
较长的文本：text、blob（较长的二进制数据）

日期型

*/

一、整型

/*
分类：
tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8字节

特点：
① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
③ 如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！

*/

• 如何设置无符号和有符号

DROP TABLE IF EXISTS tab_int;CREATE TABLE tab_int(    t1 INT(7) ZEROFILL,    t2 INT(7) ZEROFILL );DESC tab_int;

INSERT INTO tab_int VALUES(-123456);INSERT INTO tab_int VALUES(-123456,-123456);INSERT INTO tab_int VALUES(2147483648,4294967296);INSERT INTO tab_int VALUES(123,123);SELECT * FROM tab_int;

二、小数

/*
分类：

• 1.浮点型
  float(M,D)
  double(M,D)
• 2.定点型
  dec(M，D)
  decimal(M,D)

特点：

①
M：整数部位+小数部位
D：小数部位
如果超过范围，则插入临界值

②
M和D都可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度

③定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

*/
#测试M和D

DROP TABLE tab_float;CREATE TABLE tab_float(    f1 FLOAT,    f2 DOUBLE,    f3 DECIMAL);SELECT * FROM tab_float;DESC tab_float;

INSERT INTO tab_float VALUES(123.4523,123.4523,123.4523);INSERT INTO tab_float VALUES(123.456,123.456,123.456);INSERT INTO tab_float VALUES(123.4,123.4,123.4);INSERT INTO tab_float VALUES(1523.4,1523.4,1523.4);

• 原则：
  /
  所选择的类型越简单越好，能保存数值的类型越小越好
  /

三、字符型

• 较短的文本：
  char
  varchar
• 其他：
  binary和varbinary用于保存较短的二进制
  enum用于保存枚举
  set用于保存集合，一次可插入多个
• 较长的文本：
  text
  blob(较大的二进制)

特点：

CREATE TABLE tab_char(    c1 ENUM('a','b','c'));INSERT INTO tab_char VALUES('a');INSERT INTO tab_char VALUES('b');INSERT INTO tab_char VALUES('c');INSERT INTO tab_char VALUES('m');INSERT INTO tab_char VALUES('A');SELECT * FROM tab_set;

CREATE TABLE tab_set(    s1 SET('a','b','c','d'));INSERT INTO tab_set VALUES('a');INSERT INTO tab_set VALUES('A,B');INSERT INTO tab_set VALUES('a,c,d');

四、日期型

/*

• 分类：
  date只保存日期
  time 只保存时间
  year只保存年
  datetime保存日期+时间
  timestamp保存日期+时间

特点：

CREATE TABLE tab_date(    t1 DATETIME,    t2 TIMESTAMP);INSERT INTO tab_date VALUES(NOW(),NOW());SELECT * FROM tab_date;SHOW VARIABLES LIKE 'time_zone';SET time_zone='+9:00';

常见约束

/*

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

分类：六大约束
NOT NULL：非空，用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认，用于保证该字段有默认值
比如性别
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
CHECK:检查约束【mysql中不支持】
比如年龄、性别
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。比如学生表的专业编号，员工表的部门编号，员工表的工种编号

添加约束的时机：
1.创建表时
2.修改表时

约束的添加分类：

• 列级约束：
  六大约束语法上都支持，但外键约束没有效果
• 表级约束：
  除了非空、默认，其他的都支持

主键和唯一的大对比：

外键：
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表

*/

CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束

)
CREATE DATABASE students;

一、创建表时添加约束

1.添加列级约束

/*
语法：

直接在字段名和类型后面追加 约束类型即可。

只支持：默认、非空、主键、唯一

*/

USE students;DROP TABLE stuinfo;CREATE TABLE stuinfo(    id INT PRIMARY KEY,#主键    stuName VARCHAR(20) NOT NULL UNIQUE,#非空    gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查    seat INT UNIQUE,#唯一    age INT DEFAULT  18,#默认约束    majorId INT REFERENCES major(id)#外键);CREATE TABLE major(    id INT PRIMARY KEY,    majorName VARCHAR(20));#查看stuinfo中的所有索引，包括主键、外键、唯一SHOW INDEX FROM stuinfo;

2.添加表级约束

/*

语法：在各个字段的最下面
【constraint 约束名】 约束类型(字段名)
*/

DROP TABLE IF EXISTS stuinfo;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  = '女'),#检查    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键);SHOW INDEX FROM stuinfo;

• 通用的写法：★

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

二、修改表时添加约束 ★

/*
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.添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;

2.添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

3.添加主键

• ①列级约束

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

• ②表级约束

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

4.添加唯一

• ①列级约束

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

• ②表级约束

ALTER TABLE stuinfo ADD UNIQUE(seat);

5.添加外键

ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

三、修改表时删除约束

1.删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

2.删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT ;

3.删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY;

4.删除唯一

ALTER TABLE stuinfo DROP INDEX seat;

5.删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;SHOW INDEX FROM stuinfo;

标识列

/
又称为自增长列
含义：*可以不用手动的插入值，系统提供默认的序列值

特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key。
2、一个表可以有几个标识列？至多一个！
3、标识列的类型只能是数值型。
4、标识列可以通过 SET auto_increment_increment=3;设置步长
可以通过 手动插入值，设置起始值。

*/

一、创建表时设置标识列

DROP TABLE IF EXISTS tab_identity;CREATE TABLE tab_identity(    id INT,    NAME FLOAT UNIQUE AUTO_INCREMENT,    seat INT );TRUNCATE TABLE tab_identity;INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john');INSERT INTO tab_identity(NAME) VALUES('lucy');SELECT * FROM tab_identity;SHOW VARIABLES LIKE '%auto_increment%';SET auto_increment_increment=3;#步长设置为3

二、修改表时设置标识列

ALTER TABLE  tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

三、 修改表时删除标识列

ALTER TABLE  tab_identity MODIFY COLUMN id INT;

TCL语言

/*
Transaction Control Language 事务控制语言

事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

案例：转账

张三丰 1000
郭襄 1000

update 表 set 张三丰的余额=500 where name=’张三丰’
意外
update 表 set 郭襄的余额=1500 where name=’郭襄’

事务的特性：
ACID
原子性：一个事务不可再分割，要么都执行要么都不执行
一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：一个事务一旦提交，则会永久的改变数据库的数据.

事务的创建
隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句

delete from 表 where id =1;

显式事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用

set autocommit=0;

步骤1：开启事务
set autocommit=0;
start transaction;可选的
步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
…

步骤3：结束事务
commit;提交事务
rollback;回滚事务

savepoint 节点名;设置保存点

事务的隔离级别：

mysql中默认 第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;

开启事务的语句;
update 表 set 张三丰的余额=500 where name=’张三丰’

update 表 set 郭襄的余额=1500 where name=’郭襄’
结束事务的语句;

*/

SHOW VARIABLES LIKE ‘autocommit’;
SHOW ENGINES;

1.演示事务的使用步骤

#开启事务SET autocommit=0;START TRANSACTION;#编写一组事务的语句UPDATE account SET balance = 1000 WHERE username='张无忌';UPDATE account SET balance = 1000 WHERE username='赵敏';#结束事务ROLLBACK;#commit;SELECT * FROM account;

2.演示事务对于delete和truncate的处理的区别

SET autocommit=0;START TRANSACTION;DELETE FROM account;ROLLBACK;

3.演示savepoint 的使用

SET autocommit=0;START TRANSACTION;DELETE FROM account WHERE id=25;SAVEPOINT a;#设置保存点DELETE FROM account WHERE id=28;ROLLBACK TO a;#回滚到保存点SELECT * FROM account;

视图

含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据

比如：舞蹈班和普通班级的对比

• 案例：查询姓张的学生名和专业名

  SELECT stuname,majornameFROM stuinfo sINNER JOIN major m ON s.majorid = m.idWHERE s.stuname LIKE '张%';CREATE VIEW v1ASSELECT stuname,majornameFROM stuinfo sINNER JOIN major m ON s.majorid = m.id;SELECT * FROM v1 WHERE stuname LIKE '张%';

一、创建视图

/
语法：
create view 视图名
as
*查询语句;

*/
USE myemployees;

1.查询姓名中包含a字符的员工名、部门名和工种信息

• ①创建

  CREATE VIEW myv1ASSELECT last_name,department_name,job_titleFROM employees eJOIN departments d ON e.department_id  = d.department_idJOIN jobs j ON j.job_id  = e.job_id;

• ②使用

SELECT * FROM myv1 WHERE last_name LIKE '%a%';

2.查询各部门的平均工资级别

• ①创建视图查看每个部门的平均工资

CREATE VIEW myv2ASSELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id;

• ②使用

SELECT myv2.ag,g.grade_levelFROM myv2JOIN job_grades gON myv2.ag BETWEEN g.lowest_sal AND g.highest_sal;

3.查询平均工资最低的部门信息

SELECT * FROM myv2 ORDER BY ag LIMIT 1;

4.查询平均工资最低的部门名和工资

CREATE VIEW myv3ASSELECT * FROM myv2 ORDER BY ag LIMIT 1;SELECT d.*,m.agFROM myv3 mJOIN departments dON m.department_id =d.department_id;

二、视图的修改

方式一:create or replace

/*

create or replace view 视图名
as
查询语句;

*/

SELECT * FROM myv3 CREATE OR REPLACE VIEW myv3ASSELECT AVG(salary),job_idFROM employeesGROUP BY job_id;

方式二:alter

/
语法：
alter view 视图名
as
*查询语句;

*/

ALTER VIEW myv3ASSELECT * FROM employees;

三、删除视图

/*

语法：drop view 视图名,视图名,…;
*/

DROP VIEW emp_v1,emp_v2,myv3;

四、查看视图

DESC myv3;SHOW CREATE VIEW myv3;

五、视图的更新

CREATE OR REPLACE VIEW myv1ASSELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"FROM employees;CREATE OR REPLACE VIEW myv1ASSELECT last_name,emailFROM employees;SELECT * FROM myv1;SELECT * FROM employees;

1.插入

INSERT INTO myv1 VALUES('张飞','zf@qq.com');

2.修改

UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';

3.删除

DELETE FROM myv1 WHERE last_name = '张无忌';

4.具备以下特点的视图不允许更新

• ①包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all

CREATE OR REPLACE VIEW myv1ASSELECT MAX(salary) m,department_idFROM employeesGROUP BY department_id;SELECT * FROM myv1;#更新UPDATE myv1 SET m=9000 WHERE department_id=10;

• ②常量视图(SELECT 后紧跟常量)

CREATE OR REPLACE VIEW myv2ASSELECT 'john' NAME;SELECT * FROM myv2;#更新UPDATE myv2 SET NAME='lucy';

• ③Select中包含子查询

CREATE OR REPLACE VIEW myv3ASSELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资FROM departments;#更新SELECT * FROM myv3;UPDATE myv3 SET 最高工资=100000;

• ④Select中包含join

CREATE OR REPLACE VIEW myv4ASSELECT last_name,department_nameFROM employees eJOIN departments dON e.department_id  = d.department_id;#更新SELECT * FROM myv4;UPDATE myv4 SET last_name  = '张飞' WHERE last_name='Whalen';INSERT INTO myv4 VALUES('陈真','xxxx');

• ⑤from一个不能更新的视图

CREATE OR REPLACE VIEW myv5ASSELECT * FROM myv3;#更新SELECT * FROM myv5;UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;

• ⑥where子句的子查询引用了from子句中的表

CREATE OR REPLACE VIEW myv6ASSELECT last_name,email,salaryFROM employeesWHERE employee_id IN(    SELECT  manager_id    FROM employees    WHERE manager_id IS NOT NULL);#更新SELECT * FROM myv6;UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';

变量

/
系统变量：
全局变量
*会话变量

自定义变量：
用户变量
局部变量

*/

一、系统变量

/
说明：变量由系统定义，不是用户定义，属于*服务器层面
注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别
使用步骤：
1、查看所有系统变量

show global|【session】variables;

2、查看满足条件的部分系统变量

show global|【session】 variables like ‘%char%’;

3、查看指定的系统变量的值

select @@global|【session】系统变量名;

4、为某个系统变量赋值

方式一：