MySQL基础

        and（&&）：两个条件如果同时成立，结果为true，否则为false；
        or（||）：两个条件只要有一个成立，结果为true，都不成立为false；
        not（!）：如果条件成立，则not后为false，否则为true。
示例：

3. 模糊查询【特殊的条件运算符】

like、 between and、 in 、is null、is not null

3.1. like：

特点：

一般和通配符搭配使用。

通配符：

①：%：任意多个字符，包含0个字符。

②：下划线 _ ：任意单个字符。

举例：

特殊情况：使用转义字符

或自定义转义字符：ESCAPE关键字

3.2. between and

①提高语句简洁度；

②包含临界值，等同于>=、<= ；

③两个临界值不能调换顺序，类型要一致。

举例：

查询员工编号在100到120之间的员工信息

复杂：

select *

from employees

where employee_id >=100 and employee_id <=120;

简单：

select *

from employees

where employee_id between100 and 120;

3.3. in

判断某字段的值是否属于in列表中的某一项，满足其中一项就可以。

①：使用in提高简洁度；

②：in列表的值类型必须统一或兼容；

③：不支持模糊查询的 '%' 、 '_' 等。因为 in 等同于 '=' 。

举例：

查询员工编号是某、某、某、其中的一个员工名和工种编号：

复杂：

select last_name，jib_id

from employees

where job_id='某' or '某' or '某';

简单：

select last_name，jib_id

from employees

where job_id IN ('某', '某', '某');

3.4. is null、is not null：用于判断null值

①：= 或 <> 不能用于判断 null 值，用 is null 或者 is not null ；

②：is null 或者 is not null不能用于判断普通数值。

举例：

查询没有奖金的员工名和奖金率：

错误：【“=”不能判断空值】

select last_name，commission_pct

from employees

where commission_pct = NULL; 【错误写法】

正确：

select last_name，commission_pct

from employees

where commission_pct IS NULL; 【正确写法】

3.5. 安全等于 <=>

可以用来判断 null 值甚至普通数值。

举例：

3.6. is null 和安全等于

比较：

IS NULL：仅仅可以判断NULL值，可读性高。推荐使用。

<=>：既可以判断NULL值，又可以判断普通数值，可读性较低。

3.7. ifnull函数

判断某字段或表达式是否为null。

格式：

IFNULL (expr1,expr2)：如果第一个参数不为空，则返回第一个参数，否则返回第二个参数。

举例：

3.8. isnull函数

判断某字段或表达式是否为null。

格式：

ISNULL (check_expression)：如果参数为null，则返回1，否则返回0。

2.4 测试题1【**】

3.排序查询【重点】

3.1 概念

在基础查询/条件查询的基础上进行排序。

3.2 语法和执行顺序

①select 查询列表

②from 表

③【where 条件】

④order by 排序的字段 | 表达式 | 函数 | 别名【asc|desc】

执行顺序：

②③①④

3.3 特点

①asc升序，desc降序。如果不写，默认为升序。

②order by子句中可以支持单个字段、多个字段、表达式、函数、别名。

③order by子句一般是放在查询语句中的最后面，limit子句除外。

3.4 使用：

一般情况“的”前面为筛选条件，“的”后面为查询的内容。

1.按字段排序：

2.按表达式排序：

3.按别名排序：

4.按函数排序：

5.按多个字段排序：

4.常见函数【重点】

4.1 概念

类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名。

4.2 好处

1. 隐藏了实现细节。

2. 提高代码的重用性。

4.3 调用

select 函数名（实参列表）

【 from表】；

函数中参数用到表中的内容时需要【 from表】，没有用到表中的字段则不需要。

4.4 特点

1.该函数叫什么（函数名）；

2.该函数干什么（函数功能）。

4.5 分类

1.单行函数：如：contac length ifnull等；

2.分组函数：做统计用，又称为统计函数、聚合函数、组函数。

4.6 单行函数

单行函数有字符函数、数学函数、日期函数、流程控制函数、其他函数等

4.6.1. 字符函数

有的函数和java中一样有多种重载形式，介绍部分如下：

①length：获取参数值的字节个数。

utf-8编码一个汉字为3个字节：

②concat：拼接字符。

③upper、lower：

④substr、substring：截取字符。注意SQL语言中索引从1开始。

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

⑥trim：去前后空格。

⑦lpad：用指定的字符实现左填充指定长度。

⑧rpad：用指定的字符实现右填充指定长度。

⑨replace：替换字符。

4.6.2. 数学函数

有的函数和java中一样有多种重载形式，介绍部分如下：

①round：四舍五入。

②ceil：向上取整，返回大于等于该参数的最小整数。

③floor：向下取整，返回小于等于该参数的最大整数。

④truncate：截断，小数点后保留几位。

⑤mod：取余。被除数是正数（负数），结果就是正数（负数）。

⑥rand：获取随机数，返回0~1之间的小数。

4.6.3. 日期函数

①now：获取当前系统日期+时间。

②curdate：获取当前系统日期，不包括时间。

③curtime：获取当前系统时间，不包括日期。

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

⑤str_to_date：将日期格式的字符转换成指定格式的日期类型。【日期的解析】

⑥date_format：将日期转换成字符。【日期的格式化】

4.6.4. 其他函数

①version：获取当前mysql版本：SELECT version()；

②database：获取当前所在库：SELECT database()；

③user：获取当前的用户：SELECT USER()；

④password：('字符') ：返回该字符的密码形式。

⑤md5 （'字符'）：返回该字符的md5密码形式。

4.6.5. 流程控制函数

①if函数

格式：

if（条件表达式，表达式1，表达式2）：如果条件表达式成立，返回表达式1，否则返回表达式2。

处理双分支，if...else的效果【三元运算符】。

②case函数

使用1：处理等值判断。

使用2：处理条件判断。

使用1：

【类似于java中 swich...case 语句】：

格式：【是值后面不加分号，是语句后面加分号】

case 要判断的变量或字段或表达式

when 常量1 then 要显示的值1或语句1；

when 常量2 then 要显示的值2或语句2；

...

else 要显示的值n或语句n；

end 【别名】

举例：

结果：

使用2：

【类似于java中多重 if 】：

格式：【条件满足执行后面的语句。是值后面不加分号，是语句后面加分号】

case

when 条件1 then 要显示的值1或语句1；

when 条件2 then 要显示的值1或语句2；

...

else 要显示的值n或语句n；

end 【别名】

举例：

结果：

4.6.6. 单行函数案例

4.7 分组函数

用作统计使用，又称为聚合函数或统计函数或组函数

4.7.1 分类

1. sum（字段）：求和。

2. max（字段）：求最大值。

3. min（字段）：求最小值。

4. avg（字段）：求平均值。

5. count（字段）：计数（统计行数）。

4.7.2 特点

1、以上五个分组函数都忽略null值，除了count (*)；

2、sum和avg一般用于处理数值型，max、min、count可以处理任何数据类型；

3、都可以搭配 distinct 使用，用于统计去重后的结果；

SELECT COUNT ( DISTINCT salary )，COUNT( salary ) FROM employees；

4、count 的参数可以支持：字段、*(所有行数)、常量值（一般写1）。【建议使用 count (*) ，效率高】；

5、和分组函数一同查询的字段要求是group by后的字段，其他的不行。

4.7.3 使用

一次统计一个：

也可一次统计多个或嵌套其他函数使用：

结果：

和 distinct 搭配使用：

count函数：

4.7.4 分组函数案例

5.分组查询【重点】

使用group by子句将表中的数据分成若干组。

“每个啥”就按照啥分组！“的啥” 就查啥！

5.1 语法和执行顺序

⑤ select 查询的字段 (要求出现在group by后面)，分组函数

① from 表

②【where筛选条件】 (有where的话一定在from后面)

③ group by 分组的字段（后面少用别名，mysql中支持，但oracl等不支持）

④【having 分组后的筛选】（后面少用别名，mysql中支持，但oracl等不支持）

⑥【order by】（后面完全支持别名）

5.2 特点

        1、可以按单个字段分组；
        2、和分组函数一同查询的字段要是分组后的字段；
        3、对于分组筛选：

针对的表位置关键字

①分组前筛选：原始表 group by的前面（from后面） where

②分组后筛选：分组后的结果集 group by的后面 having

分组函数（即分组后，原始表中没有）做条件肯定是放在HAVING子句中，且能使用分组前筛选的字段优先考虑使用分组前筛选，效率好。

4、可以按多个字段分组，字段之间用逗号隔开，无先后顺序。还可按照表达式或者函数分组(较少用)；

5、可以支持排序（整个分组查询的最后）；

6、group by、having后可以支持别名（但少用，mysql中支持，但oracl等不支持）。

5.3 简单使用

要点： “每个啥”就按照啥分组！“的啥” 就查啥！个数就是count！再看啥条件！

简单使用：

案例1：查询每个工种的最高工资。【按工种分类，查最高工资】

SELECT MAX(salary) ，job_id

FROM employees

GROUP BY job_id;；

结果：

案例2：查询每个位置上的部门个数。【按位置分类，查部门个数】

SELECT COUNT( *)， location_id

FROM departments

GROUP BY location_id；

结果：

5.4 添加分组前的筛选条件

案例1：查询邮箱中包含a字符的每个部门的平均工资。【按部门分类，查平均工资，条件为邮箱包含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；

结果：

5.5 添加分组后的筛选条件

案例1：查询哪个部门的员工个数大于2。

1.查询每个部门员工的个数；【按部门分组，查员工个数】

2.根据1的结果查询员工个数大于2的。【分组后筛选】

因为where条件只能放在from后面，只能查询from后面的原始表中有的属性，但employees表中没有count属性，所以不能用where。且是分组后的查询，只能用 HAVING 放在语句最后面：

SELECT COUNT(*) ，department_id

FROM employees

GROUP BY department_id

HAVING COUNT(*) >2；

结果：

案例2：查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资。

1.查询每个工种有奖金的员工的最高工资；【按工种分组，查最高工资，条件为有奖金】

2.根据1查询最高工资大于12000的。【分组后筛选】

因为条件是有奖金，奖金commissin_pct属性在原始表employees中有，所以是分组前查询，可以用where子句放在from后面直接查询，而MAX(salary)大于12000是分组后的条件，用HAVING放在语句最后查询。

SELECT MAX(salary) ，job_id

FROM employees

WHERE commissin_pct IS NOT NULL

GROUP BY jod_id

HAVING MAX(salary) >12000；

结果：

案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资。

1.查询领导编号大于102的每个领导手下的员工固定最低工资；【按领导分组，查最低工资，条件为领导编号>102】

2.分组后添加筛选条件：最低工资大于5000。【分组后筛选】

领导编号manager_id原表中就有，为分组前查询。最低工资MIN(salary)原表中没有，为分组后查询。

SELECE MIN(salary) ，manager_id

FROM employees

WHERE manager_id>102

GROUP BY manager_id

HAVING MIN(salary)>5000；

结果：

5.6 按表达式或函数分组

例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些。【分组后筛选。且GROUP BY、 HAVING后都支持别名】

SELECT COUNT(*) c， LENGTH (last_name) len_name

FROM enployees

GROUP BY len_name

HAVING c>5；

结果：

5.7 按多个字段分组

例：查询每个部门每个工种的员工的平均工资。【每个啥即按啥分组(两属性一样的为一个组)：即按照部门和工种分组，部门和工种相同的员工为一组】

SELECT AVG(salary) ，department_id ， job_id

FROM employees

GROUP BY department_id ，job_id；

结果：

5.8 分组查询添加排序

例：查询每个部门每个工种的员工的平均工资，并且按照平均工资由高到低显示。【按照部门和工种分组，查询平均工资，并排序】

SELECT AVG(salary) ，department_id ， job_id

FROM employees

GROUP BY department_id ，job_id

ORDER BY AVG(salary) DESC；

结果：

5.9 分组函数案例

每个啥、各个啥，所有啥就是按啥分组；个数就是count！条件是原始表属性就用where，是分组后属性就用HAVING。

案例1：查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内。【按照管理者分组，查询最低工资，条件有两个，最低工资为分组后筛选，没有管理者为分组前筛选】

SELECT MIN(salary) ，manager_id

FROM employees

WHERE manager_id IS NOT NULL

GROUP BY manager_id

HAVING MIN(salary)>=6000；

案例2：

案例3：

6.多表连接查询【重点】

需要查询的字段涉及到多个表，又称为多表查询、多表连接。要会把题目分解！！！

6.1 笛卡尔乘积现象

表1有m行，表2有n行，则结果为m*n行。发生原因：如果连接条件省略或无效则会出现，导致多个表的所有行实现完全连接。解决办法：添加上有效的连接条件。

举例：

需要查询女神名称和对应的男神名称：

结果不对：

解决办法：加上有效的连接条件。

6.2 分类【sq192、sq199】

1. 按年代分类：

①sq192标准：在mysql中仅仅支持内连接（等值连接、非等值连接、自连接）。

②sq199标准【推荐使用】：mysql中支持内连接+外连接（左外和右外）+交叉连接。

2. 按功能分类：

①内连接：等值连接、非等值连接、自连接。

②外连接：左外连接、右外连接、全外连接（mysql不支持）。

③交叉连接。

7.sql92标准

在mysql中仅仅支持内连接（等值连接、非等值连接、自连接）。

7.1 语法

⑤select 查询列表，...

①from 表1 别名，表2 别名...

②where 连接条件

【and 筛选条件】

③【group by 分组字段】

④【havig 分组后的筛选】

⑥【order by 排序字段或表达式】

7.2 等值连接

连接查询的where子句中用来连接两个表的条件称为连接条件或连接谓词，连接条件中的各连接字段类型必须是可比的，但名字不必相同。

连接条件中使用到的比较运算符主要有：=、>、<、>=、<=、!=（或<>）等。当连接运算符为“=”时，称为等值连接。使用其他运算符称为非等值连接。

7.2.1 语法和执行顺序

⑤select 查询列表

①from 表1 别名，表2 别名

②where 表1.key=表2.key

【and 筛选条件】

③【group by 分组字段】后面少用别名，mysql中支持，但oracl等不支持

④【havig 分组后的筛选】后面少用别名，mysql中支持，但oracl等不支持

⑥【order by 排序字段】后面完全支持别名

7.2.2 特点

1. 等值连接的结果为多个表的交集；

2. n表连接，至少需要n-1个连接条件；

3. 多个表不分主次，没有顺序要求；

4. 一般为表起别名，提高阅读性和性能，避免歧义。一旦起别名，则查询的字段就不能用原来的表名限制：

例：查询员工名、工种号、工种名

SELECT last_name，e . job_id，job_title

FROM employees AS e， jobs j

WHERE e . ' job_id '=j . ' job_id '；

注意：select后面的表名要是换成原始表名则会出错，因为执行顺序为先执行FROM，会将别名形成新视图，会不认识原始表名。

5.可以搭配前面介绍的所有子句使用，比如筛选、分组、排序。

7.2.3 使用

每个啥各个啥就是按照啥分组！个数就是count！再看查啥、啥条件！

1.简单使用：

案例：查询员工名和对应的部门名。【使用到两个表中字段：employees表、departments表。连接条件：员工表中的部门号=部门表中的部门号】

SELECT last_name，department_name

FROM employees，departments

WHERE employees.department_id=departments.department_id；

2.多表连接查询一般都使用别名，提高简洁度、区分重名字段：

案例：查询员工名、工种号、工种名。【三个字段属于两个表，连接查询】

SELECT last_name，e . job_id，job_title

FROM employees AS e， jobs j

WHERE e . ' job_id '=j . ' job_id '；

注意：

①select后面只有job_id有歧义，两个表中都存在，需要限制表名。last_name和job_title只存在一个表中无歧义，可以不用限制表名。

②如果使用了别名，则select后查询的字段就不能使用原来的表名限制，要使用别名。

3.可以加筛选条件，用and连接：

案例：查询城市中第二个字符为o的部门名和城市名。【部门名、城市名属于两个表，连接查询。筛选条件：城市第二个字符为o】

SELECT department_name ，city

FROM department d ， locations l

WHERE d. 'location_id' =l. 'location_id' AND city LIKE '_o%'；

4.可以加分组：

案例：查询每个城市的部门个数。【按照城市分组，查部门个数，方便查看也可以把city查询显示。城市、部门属于两个表，连接查询】

SELECT COUNT(*) 个数， city

FROM department d， location l

WHERE d.'location_id'=l.'location_id'

GROUP BY city；

案例：查询每个国家下的部门个数大于2的国家编号。【查询方式为部门表、位置表两张表连接查询；查询内容为部门个数和国家编号；查询条件为部门个数>2】

1. 查询每个国家下的部门个数和国家编号。【用国家分组，查部门个数和国家编号】

2. 筛选条件：部门个数大于2。【分组后筛选】

select country_id， count(*) 部门个数

from department d, locations l

where d.location_id=l.location_id

group by country_id

having 部门个数>2；

案例：查询每个工种、每个部门的部门名、工种名和最低工资。【每个啥即按啥分组(两属性一样的为一个组)：按照工种和部门分组，工种和部门相同的员工为一组。且要查询部门名、工种名，需要三表连接查询】

select department_name，job_title，MIN（salary）最低工资

from employees e，department d，jobs j

where e.department_id=d.department_id

and e.jib_id=j.job_id

group by department_name，job_title；

5.可以加排序：

案例：查询每个工种的工种名和员工的个数，并且按照员工个数降序。【按照工种分组，查询工种名和员工个数，并降序。工种名和员工属于两个表，连接查询】

SELECT job_title， COUNT(*)

FROM employees e，jobs j

WHERE e.jib_id=j.job_id

GROUP BY job_title

ORDER BY COUNT(*) DESC；

6.可以实现三表连接：

案例：查询员工名、部门名和所在城市。【员工、部门、城市属于三个表，连接查询】

SELECT last_name ，department_name ， city

FROM employees e ，department d ， location l

WHERE e.'department_id'=d.'department_id'

AND d.'location_id'=l.'location_id'；【可以继续加筛选或排序：AND city LIKE 's%'；】

案例：选择city在Toronto工作的员工的 last_name， job_id，department_id，department_name。【last_name， job_id：员工表。department_id，department_name：部门表。且city在位置表，因此三表连接】

select last_name， job_id，d.department_id，department_name

from employees e，department d，location l

where e.department_id=d.department_id

AND d.location_id=l.location_id AND city=Toronto；（AND是分组前的筛选条件）

7.3 非等值连接

当连接运算符为“=”时，称为等值连接。使用其他运算符称为非等值连接【非等于】。

7.3.1 语法和执行顺序

⑤select 查询列表

①from 表1 别名，表2 别名

②where 非等值的连接条件

【and 筛选条件】

③【group by 分组字段】后面少用别名，mysql中支持，但oracl等不支持

④【havig 分组后的筛选】后面少用别名，mysql中支持，但oracl等不支持

⑥【order by 排序字段】后面完全支持别名

7.3.2 使用

每个啥各个啥就是按照啥分组！个数就是count！再看查啥、啥条件！

案例：查询员工的工资和工资级别。【工资和工资级别属于两个表，连接查询。】

SELECT salary ， grade_level

FROM employees e， job_grades g

WHERE salary BETWEEN g.'lowest_sal' AND g.'highest_sal'；【后面可继续加筛选、分组、排序等】

过程解释：

员工表中的工资：

工资等级表：

会使用员工表中的每一行挨个匹配工资等级表中的每一行，因此连接条件为：使用员工表中的salary字段逐个和工资级别表中的最低薪资、最高薪资比对，筛选出结果。

7.4 自连接

相当于等值连接，只不过自己连接自己。

7.4.1 语法和执行顺序

⑤select 查询列表

①from 表1 别名1，表1 别名2

②where 等值的连接条件

【and 筛选条件】

③【group by 分组字段】后面少用别名，mysql中支持，但oracl等不支持

④【havig 分组后的筛选】后面少用别名，mysql中支持，但oracl等不支持

⑥【order by 排序字段】后面完全支持别名

7.4.2 使用

每个啥各个啥就是按照啥分组！个数就是count！再看查啥、啥条件！

案例：查询员工名和其上级名。【员工名、上级领导名（也是员工），都在员工表中，自连接】

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；

过程讲解：

首先在员工表中查询员工名：

再根据每个员工的manager_id，再在员工表中查询上级这个员工的姓名，因此连接条件为：manager_id=employee_id。

案例：选择指定的员工的姓名、员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式：【员工名、上级领导名（也是员工），都在员工表中，自连接】

SELECT e.last_name employees，e.employee_id “Emp#” ，m.last_name manager， m.employee_id “Mgr#” 【别名含有特殊符号要用引号】

FROM employees e， employees m

WHERE e.manager_id=m.employee_id；

AND e.last_name=‘kochhar’；

8.sql99标准

mysql中支持内连接（等值、非等值、自连接）+外连接（左外和右外）+交叉连接。

关键字：

内连接：inner

左外：left 【outer】

右外：right 【outer】

全外：full 【outer】

交叉：cross

8.1 语法及好处

⑤select 字段，...

①from 表1 别名

【连接类型：inner|left outer|right outer|cross】 join 表2 别名

on 连接条件

【连接类型：inner|left outer|right outer|cross】 join 表3 别名

on 连接条件

②【where 筛选条件】

③【group by 分组字段】

④【having 分组后的筛选条件】

⑥【order by 排序的字段或表达式】

好处：

语句上，连接条件和筛选条件实现了分离，简洁明了！

8.2 内连接

8.2.1 分类

等值、非等值、自连接。

8.2.2 语法

select 查询列表

from 表1 别名 inner join 表2 别名

on 连接条件；

8.2.3 特点

①表的顺序可以调换。

②内连接的结果=多表的交集。

③n表连接至少需要n-1个连接条件。

8.3 内连接-等值连接

8.3.1 特点

①可以添加排、分组、筛选。

②Inner可以省略。

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

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

8.3.2 使用

1.等值连接简单使用：

案例：查询员工名、部门名。

SELECT last_name，department_name

FROM employees e

INNER JOIN departments d

ON e.department_id=d.department_id；

2. 等值连接+筛选：

案例：查询名字中包含e的员工名和工种名。【筛选条件：名字包含e】

SELECT last_name，job_title

FROM employees e

INNER JOIN jobs j

ON e.jib_id=j.job_id

WHERE e.last_name LIKE ‘%e%’；

3.等值连接+分组+分组后筛选：

案例：查询部门个数>3的城市名和部门个数。【（分组+分组后筛选）。隐含条件：每个城市的部门个数！即按照城市分组。部门个数>3是分组后筛选。】

SELECT city，COUNT(*) 部门个数

FROM department d

INNER JOIN location l

ON d.location_id=l.location_id

GROUP BY city

HAVING COUNT(*)>3；

4.等值连接+分组+分组后筛选+排序：

案例：查询哪个部门的员工个数>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.等值连接（三表连接）+排序：

案例：查询员工名、部门名、工种名，并按部门名降序。【三表连接并降序】

SELECT last_name ，department_name ， job_title

FROM employees e

INNER JOIN department d ON e.'department_id'=d.'department_id'

INNER JOIN jobs j ON e . job_id=j . job_id

ORDER BY department_name DESC；

8.4 内连接-非等值连接

使用：

简单使用：

案例：查询员工的工资级别。【涉及两个表，非等值】

select salary，grade_level

from employees e

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；

8.5 内连接-自连接

使用：

案例：查询员工名和直接上级的名称。

sql92语法：

SELECT e.last_name，m.last_name

FROM employees e，employees m

WHERE e.`manager_id`=m.`employee_id`；

sql99语法：

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%'；】（可以继续加筛选等）

结果：

8.6 外连接

一般用于查询除了交集部分剩余的不匹配的行。即用于查询一个表中有，另外一个表中没有的记录。

分主从表，会拿主表中的每一行去匹配从表。【怎么确定主表：需要查询哪个表中的所有记录，哪个就为主表】

外连接的查询结果为主表中的所有记录。如果从表中有和它匹配的，则显示匹配的值；如果从表中没有和它匹配的，则显示null。即外连接查询结果=内连接查询结果+主表中有而从表中没有的记录。全外连接（mysql不支持）=内连接的结果+表1中有但表2中没有的+表2有但表1中没有的记录。

举例：查询boyfriend_id不在boys表中的girl。

8.6.1 分类

左外连接、右外连接、全外连接。

8.6.2 语法

select 查询列表

from 表1 别名 left outer | right outer 表2 别名

on 连接条件；

8.6.3 特点

1.内连接是用来查询两个表的交集，外连接用来查询一个表中有，另一个表没有的记录。

2.外连接的查询结果为主表中的所有记录。如果从表中有和它匹配的，则显示匹配的值如果从表中没有和它匹配的，则显示null 外连接查询结果=内连接查询结果+主表中有而从表中没有的记录。

3.左外连接：left join，左边是主表。右外连接：right join，右边是主表。

4.左外和右外交换两个表的顺序，可以实现同样的结果。

5.全外连接（mysql不支持）=内连接的结果+表1中有但表2中没有的+表2有但表1中没有的。

6.交叉连接：两个表的笛卡尔乘积。

8.6.4 使用

案例1：查询boyfriend_id不在boys表中的女生。

①左外连接写法：

先确定主表：左外连接LEFT左边为主表。主要查询beauty表中的没有男朋友的女生信息，男朋友可能为null。因此主表是beauty表，从表是boys表。

SELECT b.name，bo.* （查询bo.* 只是为了显示出来更直观）

FROM beauty b

LEFT OUTER JOIN boys bo

ON b.boyfriend_id=bo.id；

若只想显示boyfriend_id不在boys表中的girl部分（即boyfriend_id为null），加上筛选条件即可【最好选主键】：

SELECT b.name

FROM beauty b

LEFT OUTER JOIN boys bo

ON b.boyfriend_id=bo.id

WHERE bo.id IS NULL；

②右外连接写法：

先确定主表：右外连接RIGHT右边为主表。主要查询beauty表中的没有男朋友的女生信息，男朋友可能为null。因此主表是beauty表，从表是boys表。

SELECT b.name，bo.*

FROM boys bo

RIGHT OUTER JOIN beauty b

ON b.boyfriend_id=bo.id

WHERE bo.id IS NULL；

结果一样：

案例2：查询哪个部门没有员工。

①左外连接写法：

先确定主表：左外连接LEFT左边为主表。主要查询部门表中的没有员工的部门信息，员工可能为null。因此主表是departments表，从表是employees表。

select d.*，e.employee_id

from departments d

left outer join employees e

on d.department_id=e.department_id；

若只想显示没有员工的部门（即employee_id为null），加上筛选条件即可【最好选主键】：

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；

②右外连接写法：右外连接RIGHT右边为主表。主要查询部门表中的没有员工的部门信息，员工可能为null。因此主表是departments表，从表是employees表。

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；

8.6.5 全外连接

全外连接（mysql不支持）=内连接的结果（交集部分）+表1中有但表2中没有的记录+表2有但表1中没有的记录。两个表可以不分主从关系。

举例：

SELECT b.*，bo.*

FROM beauty b

FULL OUTER JOIN boys bo

ON b.boyfriend_id=bo.id；

8.7 交叉连接

两个表的笛卡尔乘积。两个表可以不分主从关系。

举例：

SELECT b.*，bo.*

FROM beauty b

CROSS JOIN boys bo；

jianyishiy

8.8 连接查询总结

1.sq192、sq199比较：建议使用sq199：

①功能：sq199支持的较多。

②可读性：sq199实现连接条件和筛选条件的分离，可读性较高。

2.连接查询使用：

8.9 连接查询案例

案例1：查询编号>3的女神的男朋友信息，如果有则列出详细信息，如果没有用null填充。【左外/右外连接都可以，主要查询女神信息，男朋友可能为null。因此主表为beauty表。】

SELECT b.id，b.name，bo.*

FROM beauty b

LEFT OUTER JOIN boys bo

ON b.boyfriend_id=bo.id

WHERE b.id>3；

案例2：查询哪个城市没有部门。【左外/右外连接都可以，主要查询城市信息，部门可能为null。因此主表为locations表。】

SELECT city，d.* （查询d.* 只是为了显示出来更直观）

FROM departments d

RIGHT OUTER JOIN locations l

ON d.location_id=l.location_id

WHERE d.department_id IS NULL；

案例3：查询部门名为SAL或IT的员工信息。【左外/右外连接都可以，主要查询部门信息，部门名为SAL或IT的部门的员工可能为null。因此主表为departments表。】

SELECT e.*，d.department_name

FROM departments d

LEFT OUTER JOIN employees e

ON d.department_id=e.department_id

WHERE d.department_name IN（‘SAL’,‘IT’）；

9.子查询

9.1 概念

出现在其他语句内部的select语句，称为子查询或内查询。

一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句称为子查询或内查询，在外面的查询语句称为主查询或外查询。

9.2 分类

1.按照子查询出现的位置：

①select后面：仅仅支持标量子查询（一行一列）。

②from后面：支持表子查询（多行多列）。

③where或having后面：【重点】

【重点】支持标量子查询（一行一列）、【重点】列子查询（一列多行）、行子查询。

④exists后面（相关子查询。有值返回true，没值返回false）：支持表子查询（多行多列）。

2.按照结果集的行列数不同：

①标量子查询【单行子查询】：结果集只有一行一列。

②列子查询【多行子查询】：结果集只有一列多行。

③行子查询：结果集只有一行多列。

④表子查询：结果集一般为多行多列。

9.4 where/having后面-标量子查询（也叫单行子查询）【重点】

特点：

1.子查询都放在小括号内；

2.子查询一般放在条件的右侧；

3.标量子查询一般搭配单行操作符使用：> < = <> >= <=；列子查询一般搭配多行操作符使用：any、all、in、not in；

4.子查询优先于主查询执行，主查询使用了子查询的执行结果；

5.非法使用子查询的情况： a、子查询的结果为一组值； b、子查询的结果为空。

案例1（where后面）：谁的工资比Abel高？

①查询Abel的工资；【此步结果为一行一列：标量子查询】

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

select *

from employees

where salary>（

select salary

from employees

where last_name='Able'

）；

案例2（where后面）：查询job_id与141号员工相同并且工资大于143号员工的姓名、job_id、工资。

① 查询141号员工的job_id；【此步结果为一行一列：标量子查询】

②查询143号员工的salary；【此步结果为一行一列：标量子查询】

③查询员工的姓名、job_id、工资，要求job_id=①且salary>②。

select last_name，job_id，salary

from employee

where job_id=(

select job_id

from employees

where employees_id=141

)

AND salary>(

select salary

from employees

where employee_id=143

)；

案例3（having后面）查询最低工资大于50号部门最低工资的部门id和其最低工资。

①查询50号部门的最低工资；【此步结果为一行一列：标量子查询】

②查询每个部门的最低工资；【每个：以部门分组】

③筛选②>①。

select MIN（salary），department_id

from employees

GROUP BY department_id

HAVING MIN（salary）>（

select MIN（salary）

from employees

where department_id=50

)；

9.4 where/having后面-列子查询（也叫多行子查询）【重点】

特点：

结果集有多行，一般搭配多行操作符使用：any、all、in、not in ★in：属于子查询结果中的任意一个就行，any和all往往可以用其他查询代替。

注意：由于any（任意一个）、all（所有）不好理解，一般情况下会替换：

a>any（10，20，30）就可替换成：a>min（10，20，30）

a<any（10，20，30）就可替换成：a<max（10，20，30）

a>all（10，20，30）就可替换成：a>max（10，20，30）

a<all（10，20，30）就可替换成：a<min（10，20，30）

IN等价于=ANY，NOT IN等价于<>ALL

......

案例1(where后面，使用in)：返回location_id是1400或1700的部门中的所有员工姓名。

①查询location_id是1400或1700的部门编号；【此步结果为一列多行：列子查询，要使用多行操作符】

②查询员工姓名，部门号是1中某个。即：IN（ ①的结果）或者=ANY（①的结果）。

select last_name

from employees

where department_id IN( 【此处也可用=ANY，效果一样，即等于任意一个即可】

select DISTINCT department_id

from departments

where lacation_id IN (1400,1700)

)；

案例2(where后面，使用any)：返回其他部门中比job_id为‘IT_PROG’部门的任意工资低的员工号、姓名、job_id以及salary。

①查询job_id为‘IT_PROG’部门的任意工资；【此步结果为一列多行：列子查询，要使用多行操作符】

②查询员工号、姓名、job_id以及salary。其中：salary<any（①的结果）

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’；

或者替换any：

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’；

9.5 where/having后面-行子查询

行子查询：结果集一行多列或多行多列（用得少，了解即可）查询。

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

①查询最小的员工编号；

②查询最高工资。

普通方法：

select *

from employees

where employees_id=(

select MIN(employees_id)

from employees )

AND salary=(

select MAX(salary)

from employees

)；

发现两个条件都是等于号，使用行子查询：

select *

from employees

where (employee_id，salary)=(

select MIN(employees_id)，MAX(salary)

from employees

)；

9.6 select后面

特点：仅仅支持标量子查询。

？案例1：查询每个部门的员工个数。【“每个部门”：也可以直接用部门分组，查询count（*），但此方法分组个数为0的显示不出来】

select d.*，(

select count(*)

from employees e

where e.department_id=d.department_id ) 个数

from department d；

？案例2：查询员工号=102的部门名。【也可以用连接查询做，连接两个表】

select (

select department_name

from departments d

inner join employees e

on e.department_id=d.department_id

where e.employee_id=102

) 部门名；

9.7 from后面

特点：将子查询结果充当一张表，要求必须起别名，否则找不到表。

案例：查询每个部门的平均工资的工资等级。【可以使用部门分组，查询平均工资的工资等级（连接查询）】

①先查询每个部门的平均工资；【结果为一张表】

②连接①的结果集和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；

9.8 exists后面（也叫相关子查询）

exists后面的查询和前面子查询不一样，前面的是先执行子查询，外查询使用到了子查询的结果。而 exists后面的查询为先执行外查询或主查询，再使用exists过滤。

语法：

exists（完整的查询语句。不关心结果几行几列，只关心结果存不存在）

Boolean类型，结果：1或者0，即括号里查询的内容存在或不存在。一般都可用IN方法替换。

案例1：查询有员工的部门名。【两个表，连接查询】

select depatment_name

from departments d

where exists(

select *

from employees e

where d.department_id=e.department_id

)；

用IN方法：

select depatment_name

from departments d

where d.department_id IN(

select department_id

from employees

)；

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

）；

9.9 子查询案例

累了，等俺有时间了再回来看。P94、P98

10.分页查询【重点】

10.1 应用场景

当要查询的条目数太多，一页显示不全。实际的web项目中需要根据用户的需求提交对应的页数查询的sql语句（比如当前查询在第一页，只有当用户点击下一页才会去查询下一页的内容，不会一次性查询所有词条，否则浪费）。

10.2 语法

SQ192：

⑥ select 字段|表达式，...

① from 表

②【where 连接条件】

③【and 筛选条件】

④【group by 分组字段】

⑤【having 分组后的筛选】

⑦【order by 排序的字段】

⑧ limit 起始的条目索引，条目数；【如果从第一条索引开始，起始条目索引可以省略】

SQ199【重点】：
⑦ select 字段|表达式，...

① from 表1 别名

②【连接类型 join 表2 别名】

③【on 连接条件】

④【where 筛选条件】

⑤【group by 分组字段】

⑥【having 分组后的筛选】

⑧【order by 排序的字段】

⑨ limit 起始的条目索引，条目数；【如果从第一条索引开始，起始条目索引可以省略】

10.3 特点

1.起始条目索引从0开始；

2.limit子句放在查询语句的最后最后！！执行顺序也是最后！

3.学会公式：每页显示条目数：size。要显示的页数 page。

select 查询列表

from 表

limit （page-1）*size，size

10.4 使用

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

select *

from employees

LIMIT 0，5；

从第一条开始可以省略起始索引：

select *

from employees

LIMIT 5；

案例2：查询第11条到第25条员工信息。

select *

from employees

LIMIT 10，15；

案例3：查询有奖金的员工信息，并且工资较高的前十名显示出来。

select *

from employees

where commission_pct IS NOT NULL

ORDER BY salary DESC

LIMIT 10；

11.测试题3【**】

已知两个表：

1.查询所有学员的邮箱的用户名（注：邮箱中@前面的字符为用户名）

使用substr函数截取字符，使用instr函数获取‘@’字符的索引值：

select substr （email，1，instr（email，‘@’）-1）

from stuinfo；

2.查询男生和女生的个数。【使用性别的男、女分组】

select count（*）个数，sex

from stuinfo

group by sex；

3.查询年龄>18岁的所有学生的姓名和年级名称。【涉及到两个表的字段：连接查询+筛选条件>18岁】

select name，gradeName

from stuinfo s

inner join grade g

on s.gradeId=g.id

where age>18；

4.查询哪个年级的学生最小年龄>20岁。

①查询每个年级学生的最小年龄；【使用年级分组，查最小年龄】

②查询①的age>20的年级。【分组后筛选：having】

select min（age），gradeId

from stuinfo

group by gradeId

having min（age）>20；

5.试说出查询语句中涉及到的所有关键字，以及执行先后顺序。

⑦ select 字段|表达式，...

① from 表1 别名

②【连接类型 join 表2 别名】

③【on 连接条件】

④【where 筛选条件】

⑤【group by 分组字段】

⑥【having 分组后的筛选】

⑧【order by 排序的字段】

⑨ limit 起始的条目索引，条目数；【如果从第一条索引开始，起始条目索引可以省略】

注意：

每一个字句都会生成符合条件的虚拟表格。

12.联合查询

12.1 引入

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

12.2 应用场景

要查询的结果来自于多个表，且多个表没有直接的关系，但查询的信息一致时。比如在网页上进行内容搜索时，搜索到的信息肯定是来自于数据库中的好多表。

好处：

1.将一条比较复杂的查询语句拆分成多条语句；

2.适用于查询多个表的时候，且查询的列基本一致。

12.3 语法

select 字段|常量|表达式|函数【from 表】【where 条件】

union 【all】

select 字段|常量|表达式|函数【from 表】【where 条件】

union 【all】

select 字段|常量|表达式|函数【from 表】【where 条件】

union 【all】

.....

select 字段|常量|表达式|函数【from 表】【where 条件】

即：

查询语句1

union 【all】

查询语句2

union 【all】

......

12.4 特点

1、多条查询语句的查询的列数必须是一致的；

2、多条查询语句的查询的列的类型和顺序几乎相同；

3、union代表去重，union all代表不去重。

12.5 使用

引入案例：查询部门编号>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合并结果，效率更高。

案例：两张表如下：

查询中国和外国用户男性的信息。【查询内容来自于两张表，且表之间没有直接关系，查询相同的信息且列数都为3列。因此使用联合查询】

select id，cname，csex

from t_ca

where csex='男'

UNION

select t_id，tName，tGenter

from t_ua

where tGenter='male'；

六、DML语言

数据操作语言（增删改）：

插入：insert

修改：update

删除：delete

1.插入

指表已经存在，向表中添加数据。

1.1 方式1：经典的插入（用得多）

语法：

insert into 表名（字段名，...）

values （值1，...）；

特点：

1、字段类型【字符和日期型要加单引号，数值不用】和值类型一致或兼容（可以相互转换），而且一一对应。

【字段的顺序可以换，只要值和其一一对应即可】；

2、可以为空的字段：①可以不用插入值【字段名和值都省略】；②或用null填充【字段名写着，值用NULL填充】；

3、不可以为空的字段，必须插入值；

4、字段个数和值的个数必须一致；

5、字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致。

举例：

结果：

1.2 方式2

语法：

insert into 表名

set 列名=值，列名=值，...；

举例：

INSERT INTO beauty

SET id=19，NAME='刘涛'，phone='999'；

1.3 两种方式比较

1、方式1支持插入多行，方式2不支持。

语法（一条语句批量插入多行）：

insert into 表名（字段名，...）

values（值1，...），

values（值2，...）， ...；

2、方式1支持子查询【子查询可以省略values语句】，方式2不支持。

举例：

INSERT INTO beauty（id，name，phone）

SELECE id，boyname，'123456' 【先执行子查询，再将结果插入到对应字段】

FROM boys WHERE id<3；

2.修改

指表已经存在，修改表中的数据。

2.1 语法

1.修改单表的记录（重点）：

① update 表名

③ set 字段=新值，字段=新值...

②【where筛选条件】；

执行顺序：①②③

2.修改多表的记录（补充）：

sp192语法（只支持内连接）：

update 表1 别名1，表2 别名2

set 字段=新值，字段=新值... 【两个表连接成的大虚拟表的所有字段】

where 连接条件 and 筛选条件；

sp199语法【重点】：

update 表1 别名1

inner / left / right join 表2 别名2

on 连接条件

set 字段=新值，字段=新值... 【两个表连接成的大虚拟表的所有字段】

where 筛选条件；

2.2 使用

单表修改单个字段：将姓唐的手机号改为12833333。

UPDATE beauty

SET phone='12833333'

WHERE name like '唐%'；

单表修改多个字段：将2号的男朋友修改为张飞，魅力值为10。

UPDATE boys

SET boyname='张飞' ，usercp=10

WHERE id=2；

多表例：修改张无忌的女朋友手机号为114。【涉及两个表的字段，需要先连接表】

update boys bo

inner join beauty

on bo.id=b.boyfriend_id

set b.phone='114'

where bo.boyname='张无忌'；

多表例：修改没有男朋友的女神的男朋友编号都为2号。【需要查询一个表中有，另一个表没有的记录：外连接。主要查询女神，男朋友编号可能为null，因此beauty表为主表】

update boys bo

right join beauty b

on bo.id=b.boyfriend_id

set b.boyfriend_id=2

where bo.id IS NULL；【？没懂为啥是bo.id IS NULL 】

3.删除

3.1 方式1：delete语句

单表的删除【重点】：

delete from 表名

【where 筛选条件】

【limit 条目数】；

多表的删除【级联删除，补充】：

sq192：

delete 表1的别名1【，表2的别名2】【写哪个表的别名删除哪个表的信息】

from 表1 别名1，表2 别名2 【数据源为两个表的笛卡尔积】

where 连接条件 and 筛选条件；

sq199语法【重点】：

delete 表1的别名1【，表2的别名2】【写哪个表的别名删除哪个表的信息】

from 表1 别名1 【数据源为两个表的笛卡尔积】

inner / left / right join 表2 别名2

on 连接条件

where 筛选条件；

举例：

删除单表案例：删除手机号最后一位为9的女神信息。

DELETE FROM beauty

WHERE phone LIKE '%9'；

删除多表案例1：删除张无忌的女朋友的信息。【用到了两个表的字段，连接。张无忌的女朋友存在，内连接】

DELETE b 【删除beauty表的女生信息】

FROM beauty b

INNER JOIN boys bo

on bo.id=b.boyfriend_id

where bo.boyname='张无忌'；

删除多表案例2：删除黄晓明的信息及他的女朋友的信息。

DELETE b，bo 【删除beauty表和boys表的信息】

FROM beauty b

INNER JOIN boys bo

on bo.id=b.boyfriend_id

where bo.boyname='黄晓明'；

3.2 方式2：truncate语句

清空数据，删除表中所有数据。

使用delete删除表中所有数据：delete from 表名；

truncate语法：

truncate table 表名；

3.3 两种方式的区别【重点，面试题】

1.truncate不能加where条件，而delete可以加where条件；

2.truncate删除表数据，效率高一丢丢；

3.truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始；
delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始。

4.truncate删除没有返回值，delete删除有返回值。【删除表中数据后，delete会提示几行受影响，truncate不会】

5.truncate删除不能回滚，delete删除可以回滚。

4.增删改案例

案例1：

使用一次性插入多行：

或者使用子查询插入（多个子查询：联合查询）：

案例2：只插入了3行，剩下自己写。

案例3：

案例4：

七、DDL语句：数据定义语言

库和表的管理：

用到的关键字：

创建：create

修改：alter

删除：drop

DDL语句中修改alter、删除drop表，指修改表的结构、删除表（删完表就不存在了）。

而上文学习的DML数据操作语言中，修改update、删除delete等，仅仅是针对表中的数据进行操作。

1.库的管理

★凡是用到别的库的表【复制或连接】：库名 . 表名

1.1 创建库

create database [ if not exists ] 库名 [character set 字符集名]；

举例：

1.2 修改库

一般不修改，容易出现问题。

可以更改库的字符集：

ALTER DATABASE 库名 CHARACTER SET 字符集；

1.3 删除库

drop database [ if exists ] 库名；

2.表的管理

2.1 创建表：create【重点】

语法：

create table IF NOT EXISTS 表名（

列名列的类型【长度、约束】，

...

列名列的类型【长度、约束】（最后一列没有逗号）

）；

案例1：创建book表

案例2：创建学生表

CREATE TABLE IF NOT EXISTS stuinfo(

stuId INT，

stuName VARCHAR（20），

gender CHAR，

bornDate DATETIME

)；

2.2 修改表：alter

表中可以修改的有：修改字段名、修改表名、修改字段类型和列级约束、添加字段、删除字段。

语法：

ALTER TABLE 表名 ADD【添加】/ MODIFY【修改】/ DROP【删除】/ CHANGE 【改列名】 COLUMN 字段名【字段类型约束】；

注意：

只有修改列名时，CHANGE后的COLUMN关键字可省略。其他修改，COLUMN均不可省略。因此写的时候都不要省略就行。

1.修改字段名：

语法：

ALTER TABLE 表名 CHANGE [COLUMN] 旧列名新列名类型；

ALTER TABLE studentinfo CHANGE [COLUMN] sex gender CHAR；

2.修改表名：

语法：

ALTER TABLE 表名 RENAME [TO] 新表名；

ALTER TABLE stuinfo RENAME [TO] studentinfo；

3.修改字段类型和列级约束：

语法：

ALTER TABLE 表名 MODIFY COLUMN 列名新类型【新约束】；

ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ；

4.添加字段：

语法：

ALTER TABLE 表名 ADD COLUMN 列名类型【first / after 字段名】；

ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first；

5.删除字段：

语法：

ALTER TABLE 表名 DROP COLUMN 列名；

ALTER TABLE studentinfo DROP COLUMN email；

2.3 删除表：drop

语法：

DROP TABLE [IF EXISTS] 表名；

DROP TABLE [IF EXISTS] studentinfo；

☆通用的写法（比如工作时，需要自己创建库、创建表）：

drop database if exists 旧库名；

create database 新库名；

drop table if exists 旧表名；

create table 新表名；

2.4 复制表

1.仅仅复制表的所有结构【所有字段】：

语法：

CREATE TABLE 新表名 LIKE 被复制的表名；

2.复制表的所有结构+所有数据【添加子查询】

语法：

CREATE TABLE 新表名

SELECT *

FROM 被复制的表名；

3.只复制部分数据【查询部分即可】：

例：CREATE TABLE copy3

SELECT id，au_name

FROM author

WHERE nation='中国'；

4.只复制部分结构：

例：CREATE TABLE copy4

SELECT id，au_name

FROM author

WHERE 1=2；【恒不成立，或者可以写WHERE 0； 0即false。因为where条件不成立，因此没有满足条件的数据，数据获取不到；只能查询获取到字段名，空列】

3.库和表的管理案例

1.凡是用到别的库的表【复制或连接】：库名 . 表名

4.常见数据类型

创建表、修改表等涉及到数据类型和约束。

原则：

所选择的类型越简单越好，能保存数值的类型越小越好。

分类：

1.数值型：

①整型；②小数：浮点型、定点型。

2.字符型：

较短的文本：char、varchar；

较长的文本：text、blob(较长的二进制数据)。

3.日期型

4.1 整型

分类： tinyint smallint mediumint int / integer bigint

字符数： 1 2 3 4 8

特点：

①如果不设置无符号还是有符号，默认为有符号。如果想设置无符号【比如要求数值大于0】，需要添加unsigned关键字；

②如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值；

③如果不设置长度，会有默认长度。长度不代表数值范围，类型决定范围。长度代表数值显示的最大宽度（控制台显示出来结果的位数）。如果不够会用0在左边填充，但必须搭配zerofill使用，并且默认变成无符号整型。

案例：设置无符号和有符号：

CREATE TABLE tab_int（

t1 INT， // 默认有符号

t2 INT UNSIGNED

）；

4.2 小数

分类：

1.浮点型：fioat(M，D) 、double(M，D)。float4个字节；double8个字节。

2.定点型：decimal(M，D)或简写成dec(M，D)。M+2个字节。

特点：

①M和D的含义。M：整数位数+小数位数；D：小数位数。如果超出范围，则报out of range异常，并插入临界值。

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

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

4.3 字符型

分类：

1.较短的文本： char；varchar

其他： binary和varbinary：用于保存较短的二进制；enum用于保存枚举；set用于保存集合

2.较长的文本： text；blob(较大的二进制数据)

特点：

写法 M的意思特点空间的耗效率

char char(M) 最大的字符数，可以省略，默认为1 固定长度的字符比较耗费高

varchar varchar（M）最大的字符数，不可以省略 可变长度的字符 比较节省 低

注意：

像性别这种固定长度的信息建议用char，变化较大的信息建议用varchar。

4.4 日期型

分类：

date：只保存日期；

time：只保存时间；

year：只保存年；

datetime：保存日期+时间；

timestamp：保存日期+时间；

特点：

字节范围受时区、语法模式等影响

datetime 8 1000年-9999年【范围大】不受

timestamp 4 1970年-2038年【范围小】受，更能反映当前时区真实时间

5.常见约束

创建表、修改表等涉及到数据类型和约束。

5.1 含义

一种限制，用于限制表中的数据，为了保证表中的数据的准确、一致性和可靠性。

5.2 分类

六大约束：【重点】

①NOT NULL：非空。用于保证该字段的值不能为空。比如姓名、学号等。

②DEFAULT：默认。用于保证该字段有默认值。

③UNIQUE：唯一。用于保证该字段的值具有唯一性，可以为空。比如座位号，不是很重要可以不写，但写上就不能重复。

④CHECK：检查约束【MYSQL中不支持】。比如检查性别中只能存在男、女两种字符。

⑤PRIMARY KEY：主键。用于保证该字段的值具有唯一性，并且非空。比如学号、员工编号等。

⑥FOREIGN KEY：外键。用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。比如学生表的专业编号、员工表的部门编号等。

5.3 添加约束的时机

肯定是添加数据之前：

1.创建表时；

2.修改表时。

5.4 约束的添加分类-列级约束和表级约束

1.列级约束：

六大约束语法上都支持，但CHECK和外键约束没有效果，外键约束是表级约束。

2.表级约束：

除了非空、默认，其他的都支持。

3.对比☆：

位置支持的约束类型是否可以起约束名

列级约束：列的后面语法都支持，但CHECK和外键没有效果 不可以

表级约束：所有列的下面默认和非空不支持，其他支持可以（对主键约束没有效果）

5.5 主键和唯一对比【面试题】

保证唯一性是否允许为空一个表中可以有多少个是否允许组合

主键 √ × 至多有一个 √（但不推荐）

唯一 √ √ 可以有多个 √（但不推荐）

其中是否允许组合指：两个列组合成一个主键或唯一键。

5.6 外键

1.要求在从表上设置外键关系；

2.从表的外键的列的类型和主表的关联列的类型要求一致或兼容，关联列名称无要求；

3.主表的关联列必须是一个key（一般是主键或唯一）；

4.插入数据时，先插入主表，再插入从表；删除数据时，先删除从表，再删除主表。

5.7 创建表时添加约束

5.7.1 添加列级约束

语法：

直接在字段名和类型名后面追加约束类型即可。列级约束只支持：默认、非空、主键、唯一。

举例：

5.7.2 添加表级约束：

语法：创建表时在各个字段（列）的最下面添加。除了非空、默认，其他的都支持。

【constraint 约束名】约束类型（字段名）

外键约束语法：

【constraint 约束名（外键约束名一般写成fk_当前表名_主表名）】 foreign key（字段名）references 主表名（字段名）

举例：

通用的写法：

CREATE TABLE 表名（

字段名字段类型 列级约束，

表级约束

）

举例：

5.8 修改表时添加约束

5.8.1 添加列级约束

alter table 表名 modify column 字段名字段类型 新约束；

5.8.2 添加表级约束

alter table 表名 add 【constraint 约束名】 约束类型（字段名）【外键的引用】；

举例：

5.9 修改表时删除约束

修改字段为不加约束即删除约束。

查看表约束：

SHOW INDEX FROM 表名；

1.删除非空约束：

ALTER TABLE 表名 MODIFY COLUMN 字段名类型 NULL；（即设置成为空即可，或者删除NULL也可以）

2.删除默认约束：

ALTER TABLE 表名 MODIFY COLUMN 字段名类型；（即不加默认值即可）

3.删除主键：

ALTER TABLE 表名 DROP PRIMARY KEY；（主键只有一个，不用加字段名）

4.删除唯一：

ALTER TABLE 表名 DROP INDEX 唯一键约束名；

5.删除外键：

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名；

6.标识列

标识列又称为自增长列。

6.1 含义

可以不用手动地插入值，系统提供默认的序列值（默认从1开始）。

6.2 特点

1.标识列必须和主键搭配吗？不一定，但要求是一个key；

2.一个表可以有几个标识列？最多一个；

3.标识列的类型只能是数值型；

4.标识列可以通过：SET auto_increment_increment=步长；来设置步长。

可以通过手动插入一个值设置起始值。

6.3 创建表时设置标识列

字段类型后面加：AUTO_INCREMENT

举例：

创建表时加上标识列：

给表中插入数据：

结果：

通过：SET auto_increment_increment=3；改步长为3后：

6.4 修改表时设置标识列

ALTER TABLE 表名 MODIFY COLUMN 字段名类型约束类型 AUTO_INCREMENT；

举例：

6.5 修改表时删除标识列

ALTER TABLE 表名 MODIFY COLUMN 字段名类型约束类型；（即：去掉标识列即可）

八、TCL：事务控制语言

1.数据库事务的含义

通过一组逻辑操作单元（一组DML——sql语句），将数据从一种状态切换到另外一种状态

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

引入案例，事务需要的场合：

张三丰和郭襄各有1000元，现张三丰给郭襄转账500。伪代码：

update 表 set 张三丰的余额=500

where name='张三丰’；

update 表 set 郭襄的余额=1500

where name='郭襄’；

以上两条语句必须同时成功或失败，不会存在张三丰余额-500出现意外，而郭襄余额不变的情况。

mysql中的存储引擎了解：innodb引擎支持事务。

2.事物的特点（ACID属性，面试题）

事务的（ACID）属性：

原子性（Atomicity）：事务是一个不可分割的工作单位。事务中的操作要么都执行，要么都回滚。

一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态。

隔离性（Isolation）：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发的各个事务之间不能互相干扰。

持久性（Durability）：一个事务一旦提交，则数据将永久化到本地，除非其他事务对其进行修改。

3.事务的分类和使用步骤

事物的分类：

隐式事务；显式事务。

3.1 隐式事务

也叫自动事务。比如insert、update、delete语句等本身就是一个隐式事务。

使用步骤：

没有明显的开启和结束事务的标志，默认自动提交事务。

自动提交状态：

查看当前自动提交状态：SHOW VARIABLES LIKE ‘autocommit’；
关闭自动提交：set autocommit=0；（只对当前会话有效）

3.2 显式事务

具有明显的开启和结束事务的标志。需要使用多条sql语句作为一个事务时要使用显式事务。因为使用到的一些单独sql语句（insert、update等）会默认一条语句结束后自动提交事务，因此使用显式事务之前需要先关闭自动提交状态。

3.3 显式事务使用步骤：

1.开启事务

先取消自动提交事务的功能：set autocommit=0；

【再开启事务：start transaction；】：此语句可以不写，当关闭自动提交时则默认事务已开启。

2.编写事务的一组逻辑操作单元（多条sql语句）

增删改查：select、insert、update、delete。

注意：不能写create、alter、drop，没效果。

3.提交事务或回滚事务

提交：commit；

回滚： rollback；

回滚到指定地方：rollback to 回滚点名；

3.3 使用到的关键字

        set autocommit=0；
        start transaction；【可选】
        commit；提交事务
        rollback；回滚事务

        savepoint 断点
        commit to 断点
        rollback to 断点

3.4 delete和truncate在事务使用时的区别

delete支持回滚。比如执行完delete事务后，在事务结尾写上rollback；回滚，表中的数据不会删除，因为成功回滚。

truncate不支持，即使在事务结尾写上rollback；回滚，还是会直接清空表中数据。

举例：

3.5 事物的并发问题

1.事务并发问题如何发生？

当多个事务同时操作同一个数据库的相同数据时，如果没有采取必要的隔离机制，就会导致各种并发问题。

2.事务的并发问题有哪些？

脏读（未提交，开启两个命令行）：一个事务读取到了另外一个事务未提交的数据【针对更新】。

不可重复读（一个事务提交前和提交后另一个事物两次读到不一样数据）：同一个事务中，多次读取到的数据不一致。

幻读（一个事务更新（主要是插入）前后，另一个事务读取）：一个事务读取数据时，另外一个事务进行更新，导致第一个事务读取到了没有更新的数据【针对插入、删除】。

3.如何避免事务的并发问题？

通过设置事务的隔离级别来避免并发问题。

3.6 隔离级别

Mysql支持四种事务隔离级别：

1.READ UNCOMMITTED：（读未提交数据）

2.READ COMMITTED：（读已提交数据）可以避免脏读

3.REPEATABLE READ：（可重复读）可以避免脏读、不可重复读和一部分幻读

4.SERIALIZABLE：（串行化）可以避免脏读、不可重复读和幻读

mysql中默认第三个隔离级别：REPEATABLE READ

oracle中默认第二个隔离级别：READ COMMITTED

各个级别可以解决的问题：

隔离级别越高，解决的问题越多，但性能越低。比如可串行化，一个事务执行时，其他事物只能等待。

使用隔离级别：

使用命令行：

查看隔离级别： select @@tx_isolation；

设置当前会话隔离级别： set session transaction isolation level 隔离级别名；

设置数据库系统的全局的隔离级别： set global transaction isolation level 隔离级别名；

？此处关于隔离级别两个视频没看懂。

九、视图

1.含义

理解成一张虚拟的表，通过普通表动态生成的数据，只保存了sql逻辑，不保存查询结果。和普通表一样使用。【便于理解：相当于给一个查询起别名，类似于封装了一个查询为视图，可以随时调用】

比如：普通班级，为了应付领导视察，专门从各班抽出舞蹈跳的好的组成了舞蹈班，只要领导来，就直接用舞蹈班。

应用场景：

1.多个地方用到同样的查询结果；

2.该查询结果使用的sql语句较复杂。

2.视图的创建

语法：

CREATE VIEW 视图名

查询语句；【一般是比较复杂的查询】

使用：

案例1：

案例2：

案例3：创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息。

以前的写法：先查询每个部门最高工资，分组后筛选工资>12000的部门id，再查询这个id的部门信息：

使用视图：更易于理解，提高sql重用性。

3.视图的好处

使用视图的好处：

1.提高了sql语句重用性；

2.简化复杂的sql操作，不必知道查询细节；

3.和表实现了分离，保护数据，提高了安全性。

4.视图结构的查看【逻辑】

语法：

DESC 视图名；

或：SHOW CREATE VIEW 视图名；

5.视图的修改【逻辑】

修改视图：指视图结构（sql逻辑）的更新。

5.1 方式一

语法：

CREATE OR REPLACE VIEW 视图名

查询语句；

举例：

create or replace view test_v7

SELECT last_name FROM employees

WHERE employee_id>100；

5.2 方式二

语法：

ALTER VIEW 视图名

查询语句；

举例：

ALTER VIEW test_v7

SELECT employee_id FROM employees；

6.视图的删除

语法：

DROP VIEW 视图名，视图名，...；

如：

DROP VIEW test_v1，test_v2，test_v3；

7.视图的更新【针对数据】

前面视图的创建、修改、删除是针对视图结构（sql逻辑）的改变。此处视图的更新是针对某个视图保存的sql逻辑对应的数据，进行增删改查。

7.1 查看视图的数据 ★

举例1：

SELECT *

FROM my_v4；

举例2：

SELECT *

FROM my_v1

WHERE last_name='Partners'；

7.2 插入视图的数据

INSERT INTO my_v4 （last_name,department_id）

VALUES （'虚竹'，90）；

7.3 修改视图的数据

UPDATE my_v4

SET last_name ='梦姑'

WHERE last_name='虚竹'；

7.4 删除视图的数据

DELETE FROM my_v4；

7.5 注意

视图一般用于查询，而不是更新。

视图的可更新性和视图中封装的查询的定义有关系。以下类型的视图是不能更新的（基本都不能更新）：

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

②常量视图

③Select中包含子查询

④join

⑤from一个不能更新的视图

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

8.视图和表的区别

使用方式使用占用物理空间

视图 creat view 一般用于查询，不能增删改很少，仅仅保存的是sql逻辑

表 creat table 增删改查占用，保存了数据

9.测试题讲解

1.关于建表、约束的案例：

2.关于事务的案例：

3.关于视图的案例：

十、变量

1.分类

①系统变量：全局变量；会话变量。

②自定义变量：用户变量；局部变量。

2.系统变量

变量由系统提供，不是用户定义，属于服务器层面。

①全局变量：

作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接）有效，但不能跨重启。

②会话变量：

作用域：仅仅针对于当前会话（连接）有效。

2.1 使用注意：

以下使用，如果是全局系统变量，则需要加global；如果是会话系统变量，则需要加session，如果不写，则默认session。

2.2 使用

1.查看所有的额系统变量：

①查看所有全局变量：

show global variables；

②查看所有会话变量：不写session也默认查询会话变量。

show 【session】 variables；

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

show global /【session】 variables like ' %char% '；（查询包含‘char’的系统变量）

3.查看指定的某个系统变量的值：【一般情况，查看某个具体的值都是select】

select @@global /【session】. 系统变量名；

4.为某个系统变量赋值：

方式一：

set global /【session】系统变量名=值；

方式二：

set @@global /【session】. 系统变量名=值；

3.自定义变量

变量是由用户自定义的，不是系统提供。

3.1 用户变量

作用域：

针对于当前会话（连接）有效，同于系统变量中的会话变量的作用域。

应用在任何地方，也就是begin end里面或者begin end外面均可。

使用步骤：

1.声明并初始化：

三种方式，赋值操作符有 = 或者：=

set可以搭配两种，select只能搭配：=

① SET @用户变量名=值；

② SET @用户变量名：=值；

③ SELECT @用户变量名：=值；

2.赋值：

两种方式：

第一种：同上面声明语句，通过SET或SELECT。即这三种语句既可以当声明，也可以当赋值。

① SET @用户变量名=值；

② SET @用户变量名：=值；

③ SELECT @用户变量名：=值；

例如：

SET @name=‘John’；

第二种：通过SELECT INTO

SELECT 字段 INTO @变量名

FROM 表；

注意：

SELECT后面的字段查出来必须是一个值才能赋给后面的变量，一组值就不行。

例如：

SET @count=1；【第一步：变量必须先声明并初始化】

SELECT COUNT（*） INTO @count 【第二步：赋值】

FROM employees；

SELECT @count；【第三步：查看变量】

3.使用（查看、比较、运算等）

比如查看变量的值：

SELECT @用户变量名；

3.2 局部变量

作用域：

仅仅在定义它的begin end里面有效。

只能应用在begin end中的第一句话。

使用步骤：

1.声明（可以只声明，不初始化）

只声明：

DECLARE 变量名类型；

声明并初始化：

DECLARE 变量名类型 DEFAULT 值；

2.赋值：

和上面用户变量一模一样，两种方式。但是很矫情！！局部变量有的没有@符号！不记了记不住哼！

第一种：通过SET或SELECT。

① SET 局部变量名=值；

② SET 局部变量名：=值；

③ SELECT @局部变量名：=值；

第二种：通过SELECT INTO

SELECT 字段 INTO 局部变量名

FROM 表；

3.使用：

比如查看变量的值：没有@符号！

SELECT 局部变量名；

3.3 对比用户变量和局部变量

作用域定义和使用的位置语法

用户变量 当前会话会话中的任何地方必须加@符号，不用限定类型

局部变量 begin end中只能在begin end中第一句话一般不用加@（除了select后面），需要限定类型

案例：

声明两个变量并赋初始值，求和并打印。

1.用户变量写法：

SET @m=1；

SET @n=2；

SET @sum=@m+@n；

SELECT @sum；

2.如果直接用局部变量：

DECLARE m INT DEFAULT 1；

DECLARE n INT DEFAULT 2；

SET sum=m+n；

SELECT sum；

就会报错！因为局部变量只能写在begin end中第一句话使用，具体见下面讲的存储过程。

十一、存储过程和函数

类似于java中的方法。

好处：

1.提高代码的重用性；

2.简化操作；

3.减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。

1.存储过程

1.1 含义

一组经过预先编译好的sql语句的集合，可以理解成批处理语句，多用于增删改。

视图只是封装了一组SQL语句的逻辑，是虚拟表；存储过程相当于方法，可以传参。

1.2 好处

①提高了sql语句的重用性，减少了开发程序员的压力；

②提高了效率；

③减少了传输次数。

1.3 分类

①无返回无参；

②仅仅带in类型，无返回有参；

③仅仅带out类型，有返回无参；

④既带in又带out，有返回有参；

⑤带inout，有返回有参。

注意：

in、out、inout都可以在一个存储过程中带多个。

1.4 创建存储过程

语法：

create procedure 存储过程名（in | out | inout 参数名参数类型，...）

begin

存储过程体（一组合法的SQL语句）

end

说明：

1.参数列表包含三部分：

参数模式；参数名；参数类型

参数列表举例：IN stuname VARCHAR（20）

参数模式：

IN：该参数可以作为输入，也就是该参数需要调用方传入值。

OUT：该参数可以作为输出，也就是该参数可以作为返回值。

INOUT：该参数即可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值。

2.如果存储过程体仅仅只有一句话，begin end可以省略。

存储过程体中的每条SQL语句的结尾要求必须加分号，因此容易混淆结束标记，存储过程的结尾可以使用delimiter重新设置：delimiter 结束标记。如：delimiter $

举例：

设置新的结束标记：

delimiter $

使用：

        CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名参数类型，...)
        BEGIN
                sql语句1；
                sql语句2；

END $

1.5 调用存储过程

语法：

call 存储过程名（实参列表）结束标记

1.6 删除存储过程

语法：

DROP PROCEDURE 存储过程名；（一次只能删除一个）

1.7 查看存储过程的信息

错误写法：DESC 存储过程名；

正确语法：

SHOW CREATE PROCEDURE 存储过程名；

注意：

存储过程不能修改。要修改，就删除重建。

1.8 举例

1.创建带in模式的存储过程：创建存储过程实现判断用户是否登陆成功。【输入用户名、密码均为输入型（in）】

创建存储过程：

CREATE PROCEDURE myp1（IN username VARCHAR(20)，IN PASSWORD VARCHAR(20)）

BEGIN

DECLARE result INT DEFAULT 0； //声明局部变量并初始化

SELECT COUNT(*) INTO result //赋值

FROM admin

WHERE admin.username=username

AND admin.password=PASSWORD；

SELECT IF(result>0，'成功'，'失败')； //使用

END $

调用：

CALL myp1（'张飞'，'8888'） $

2.创建带out模式的存储过程：根据女神名，返回对应的男神名。【女神名为输入型（in），男神名为输出型（out）】

创建存储过程：

CREATE PROCEDURE myp2（IN beautyName VARCHAR(20)，OUT boyName VARCHAR(20)）

BEGIN

SELECT bo.boyName INTO boyName

FROM boys bo

INNER JOIN beauty b ON bo.id=b.boyfriend_id

WHERE b.name=beautyName；

END $

调用：

SET @bName$ //begin end 外面可定义的变量：用户变量。接受男神名的变量

CALL myp2（'小昭'，@bName）$

或者这样调用查看：

CALL myp2（'小昭'，@bName）$

SELECT @bName$

3.创建带多个out模式的存储过程：根据女神名，返回对应的男神名和男神魅力值。【女神名为输入型（in），男神名、魅力值为输出型（out）】

创建存储过程：

CREATE PROCEDURE myp3（IN beautyName VARCHAR(20)，OUT boyName VARCHAR(20)， OUT userCP INT）

BEGIN

SELECT bo.boyName，bo.userCP INTO boyName，userCP

FROM boys bo INNER JOIN beauty b ON bo.id=b.boyfriend_id

WHERE b.name=beautyName；

END $

调用并查看：

CALL myp3（'小昭'，@bName，@usercp）$

SELECT @bName，@usercp$

4.创建带inout模式的存储过程：传入a和b两个值，最终a和b都翻倍并返回。【a、b为输入型且为输出型】

创建存储过程：

CREATE PROCEDURE myp4（inout a int，inout b int）

begin

set a=a2； //给局部变量赋值

set b=b2；

end $

调用并查看：

set @m=10$ //定义两个用户变量，传值给a、b set

@n=20$

call myp4（@m，@n）$ //调用存储过程

select @m，@n$ //查看打印

5.创建存储过程实现传入用户名和密码，插入到amin表中。【用户名和密码均为输入型（in）】

创建存储过程：

CREATE PROCEDURE test_pro1（in username varchar(20)，in loginpwd varchar(20)）

begin

inser into admin(admin.username，password)

values(username，loginped)；

end $

调用：

call test_pro1（'admin'，'0000')$

查看：

6.创建存储过程，实现传入女神编号，返回女神名称和女神电话。【女神编号为输入型（in），女神名称和女神电话为输出型（out）】

创建存储过程：

CREATE PROCEDURE test_pro2（IN id INT，OUT name VARCHAR(20) ，OUT phone VARCHAR(20)）

BEGIN

SELECT b.name，b.phone INTO name，phone //局部变量初始化并赋值

FROM beauty b

WHERE b.id=id；

END $

调用：

call test_pro2（1，@n，@p)$

查看：

7.创建存储过程实现传入两个女神生日，返回两者年龄大小对比结果。【生日为输入型（in），对比结果为输出型（out）】

创建存储过程：

CREATE PROCEDURE test_pro3（IN birth1 DATETIME，IN birth2 DATETIME，OUT result INT）

BEGIN

SELECT DATEDIFF（ birth1， birth2） INTO result //局部变量初始化并赋值

END $

调用：

call test_pro3（'1998-1-1'，now（），@result)$

查看：结果为负数，前者比后者年龄大。

8.创建存储过程，根据传入的条目数和起始索引，查询beauty表的记录。【条目数和起始索引为输入型】

创建存储过程：

CREATE PROCEDURE test_pro4（IN startIndex INT，IN size INT）

BEGIN

SELECT *

FROM beauty

LIMIT startIndex，size；

END $

调用：

call test_pro4（3，5)$

2.函数

2.1 含义

同存储过程一样，一组经过预先编译的sql语句的集合，可以理解成批处理语句。

2.2 好处

1、提高了sql语句的重用性，减少了开发程序员的压力；

2、提高了效率；

3、减少了传输次数。

2.3 存储过程和函数的区别【重点】

存储过程：

可以有0个返回，也可以有多个返回。适合做批量插入、批量更新【增删改】。

函数：

有且仅有1个返回。适合做处理数据后返回一个结果。

2.4 创建函数

学过的函数：LENGTH、SUBSTR、CONCAT等。

语法：

CREATE FUNCTION 函数名(参数名参数类型，...) RETURNS 返回类型

BEGIN

函数体

END

说明：

1.参数列表包含两部分：参数名、参数类型；

2.函数体：肯定会有return语句，如果没有则会报错。如果return语句没有放在函数体的最后，不会报错，但不建议；

3.如果函数体中仅有一句话，则可以省略begin end；

4.使用delimiter语句设置结束标记。

2.5 调用函数

语法：

SELECT 函数名（实参列表）

1.无参有返回

案例：返回公司的员工个数。

create function myf1（） returns int

begin

declare c int default 0； //定义局部变量

select count(*) into c //给变量赋值

from employees；

return c；

end $

调用：

select myf1() $

2.有参有返回

案例：根据员工名，返回他的工资。

create function myf2（empName varchar(20)）returns double

begin

set @sal=0； //定义用户变量（用户变量、局部变量都可以）

select salary into @sal //赋值

from employees；

where last_name=empName；

return @sal；

end $

调用：

select myf2('kochhar')$

案例：根据部门名，返回该部门的平均工资。

create function myf3（deptName varchar(20)）returns double

begin

declare sal double； //定义局部变量（用户变量、局部变量都可以）

select AVG（salary）into sal //给变量赋值

from employees e

JOIN departments d ON e.department_id=d.department_id

where d.department_name=deptName；

return sal；

end $

调用：

select myf3('IT')$

2.6 查看函数

SHOW CREATE FUNCTION 函数名；

2.7 删除函数

DROP FUNCTION 函数名；

注意：

函数和存储过程一样，不能修改，只能删除重建。

2.8 函数和存储过程的区别

函数：

关键字：FUNCTION

调用语法：SELECT 函数()

返回值：只能是一个

应用场景：一般用于查询结果为一个值并返回时，当有返回值而且仅仅一个

存储过程：

关键字：PROCEDURE

调用语法：CALL 存储过程()

返回值：可以有0个或多个

应用场景：一般用于更新

2.9 案例

创建函数，实现传入两个float，返回二者之和。

create function test_fun1（num1 float，num2 float）returns float

begin

declare sum float default0； //定义局部变量

set sum=num1+num2；

return sum；

end $

调用：

select test_fun1(1，2) $

十二、流程控制结构

顺序结构：程序从上往下依次执行；

分支结构：程序从两条或多条路径中选择一条去执行；

循环结构：程序在满足一定条件的基础上，重复执行一段代码。

1.分支结构

1.1 if函数

功能：

实现简单的双分支。

语法：

if（条件，值1，值2）

如果条件成立，则if函数返回值1，否则返回值2。

特点：

可以用在任何位置。

1.2 case语句

语法：

情况一：类似于switch，一般用于实现等值判断。

case 表达式|变量|字段

when 要判断的值1 then 要返回的结果1或语句1(如果是语句，需要加分号)

when 要判断的值2 then 要返回的结果2或语句2(如果是语句，需要加分号)

...

else 要返回的结果n或语句n(如果是语句，需要加分号)

end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）

情况二：类似于多重if，一般用于实现区间判断。

case

when 要判断的条件1 then 返回的结果1或语句1(如果是语句，需要加分号)

when 要判断的条件2 then 返回的结果2或语句2(如果是语句，需要加分号)

...

else 返回的结果n或语句n(如果是语句，需要加分号)

end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）

特点：

① 可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，begin end中或begin end外面；也可以作为独立的语句去使用，此时只能放在begin end中；

② 如果when中的值满足或条件成立，则执行对应的then后面的语句，并且结束case；如果都不满足，则执行else中的语句或值；

③ else可以省略，如果else省略了，并且所有when条件都不满足，则返回null。

案例：（case语句情况二）创建存储过程，根据传入的成绩显示等级，90~100：A；80~90：B；60~80：C；否则，显示D。

create procedure test_case(in score int)

begin

case

when score>=90 and score<=100 then select 'A'；

when score>=80 then select 'B'；

when score>=60 then select 'C'；

else select 'D'；

end case；

end $

调用：

call test_case(95)$

1.3 if结构：if elseif语句

语法：

if 条件1 then 语句1；

elseif 条件2 then 语句2；

...

【else 语句n；】

end if；

特点：

只能用在begin end中！！！

案例：根据传入的成绩返回等级，90~100：返回A；80~90：返回B；60~80：返回C；否则，返回D。【返回，不是显示】

create function test_if（score int）returns char

begin

if score>=90 and score<=100 then return 'A'；

elseif score>=80 then return 'B'；

elseif score>=60 then return 'C'；

else return 'D'；

end if；

end $

查看：

select test_if(86)$

1.4 三者比较

应用场合

if函数 简单双分支

case结构 等值判断的多分支

if结构 区间判断的多分支

2.循环结构

分类【都放在begin end中】：

1.while【先判断后执行】

2.loop【没有条件的死循环】

3.repeat【先执行后判断】

循环控制：

iterate类似于continue，继续，结束本次循环，继续下一次；

leave类似于break，跳出，结束当前所在的循环。

2.1 while

java中：

while（循环条件）{

循环体；

}

语法：写上标签才可以搭配循环控制使用。

【标签：】WHILE 循环条件 DO

循环体；

END WHILE 【标签】；

特点：

①只能放在BEGIN END里面。

②如果要搭配leave跳转语句，需要使用标签，否则可以不用标签。

③leave类似于java中的break语句，跳出所在循环！！！

2.2 loop

语法：

【标签：】loop

循环体；

end loop【标签】；

特点：

可以用来模拟简单的死循环。

2.3 repeat

语法：类似于java中的do...while，先执行。

【标签：】repeat

循环体；

until 结束循环的条件

end repeat 【标签】；

2.4 案例

1.没有添加循环控制：

案例：批量插入，根据次数插入到admin表中多条记录。【因为循环只能放在BEGIN END里面，所以必须有函数或者存储过程做依托。此案例没有返回值，因此用存储过程】

首先在java中循环语句：

int i=1；

while(i<=insertcount){

//插入语句

i++；

}

对比写存储过程：

create procedure pro_while1(in insertCount int)

begin

declare i int default 1； //声明并初始化局部变量

while i<=insertCount do

insert into admin(username，password) values（concat('Rose'，i)，'666'）；

set i=i+1； //赋值

end while；

end $

调用并查看：

call pro_while1(100)$

select *from admin $

2.添加leave语句，循环控制：

案例：批量插入，根据次数插入到admin表中多条记录，如果次数大于20则停止。

create procedure test_while1(in insertCount int)

begin

declare i int default 1； //声明并初始化局部变量

a：while i<=insertCount do //a为标签名，使用循环控制必须加标签

insert into admin(username，password) values（concat('xiaohua'，i)，'000'）；

if i>=20 then leave a； //循环控制

end if；

set i=i+1； // i小于20，继续赋值

end while a；

end $

调用并查看：

call test_while1(100)$

select *from admin $

3.添加iterate语句，循环控制：

案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次。

java中：

int i=0；

while(i<insertCount){

i++；

if(i%2==0){

continue；

}

插入语句

}

对比写存储过程：

create procedure test_while1(in insertCount int)

begin

declare i int default 0；

a：while i<=insertCount do

set i=i+1；

if mod(i，2)!=0 then iterate a； //结束本次循环

end if；

insert into admin(username,password) values（concat('xiaohua'，i)，'000'）；

end while a；

end $

调用并查看：

call test_while1(100)$

select *from admin $

经典案例：177视频

2.5 循环总结

甘甘脆脆小饼干

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础

一、为什么要学习数据库之前Java中学过存储数据的容器：数组、集合、文件等。数组和集合都是存储在内存中，程序一旦重新启动数据就会消失。文件在硬盘中可以永久存储，但存储大量数据时查找很困难，不适合查询。数据库的好处：1.持久化数据到本地； 2.可以实现结构化查询，方便管理二、数据库的相关概念1.DB数据库，保存一组有组织的数据的容器；2.DBMS数据库管理系统，又称为数据库软件（产品），用于管理DB...
复制链接

扫一扫