目录
1.Mysql概述
1.1 数据库相关概念
数据库（DB）：数据存储的仓库
数据管理系统(DBMS)：操纵和管理数据库的大型软件
SQL：操纵关系型数据库的编程语言，是一套标准
1.2 MySQL的安装及启动
1.3 为什么要用数据库
1.4 RDBMS和非RDBMS的对比
关系型数据库（RDBMS）
概念：
优势：
非关系型数据库（非RDBMS）
概念：
有哪些非关系型数据库：
1.5 ER模型与表记录的四种关系
关系型数据库设计规则：
表、记录、字段
表的关联关系：
一对一关联、一对多关联、多对多关联、自我引用
1.6 sql 和mysql 区别
sql : SQL是一种用于操作数据库的语言
mysql : MySQL是一个开源的数据库管理系统,一个数据库软件
2.基本的select语句
2.1 sql的分类
2.2 导入现有的数据表、表的数据
方式1：source文件的全路径名（命令行）
方式2：基于具体的图形界面的工具可以导入数据
2.3 列的别名
SELECT employee_id eml_id , last_name AS lname , department_id "dept_id"
FROM employees;
2.4 去除重复行
查询部门表中有哪些部门id?
1.错误的没有去重的情况：
SELECT department_id FROM employees；
2.正确的去重情况：
SELECT DISTINCT department_id FROM employees；
2.5 空值参与运算
1.空值：null
2.null不等同于0，' '， 'null'
3.空值参与运算：结果一定也为空
SELECT employee_id,salary "月工资", salary * (1+commisson_pot) * 12 "年工资"，commisson_pot
SELECT employee_id,salary "月工资", salary * (1+IFNULL(commisson_pot,0)) * 12 "年工资"，commisson_pot
2.6 着重号 ` `
SELECT * FROM `order`
2.7 查询常数
SELECT '冯怡婷',employee_id,last_name FROM employees；
2.8 显示表结构
DESCRIBE employees; #显示了表中字段的详细信息 DESC employees;
2.9 过滤数据
2.9.1 练习：查询90号部门的员工信息
SELECT * FROM employees #过滤条件 WHERE department_id=90;
2.9.2 练习：查询last_name为'King'的员工信息
SELECT * FROM employees WHERE LAST_NAME = 'King'
2.9.3 课后练习：
1.查询员工12个月的工资总和，并起别名为ANNULL SALARY
SELECT employee_id,last_name,salaty *12 "ANNULL SALARY" FROM employees;
2.查询employees表中去重复的job_id以后的数据
SELECT DISTINCT job_id FROM employees;
3.查询工资大于12000的员工姓名和工资
SELECT last_name，salary FROM employees WHERE salary>12000;
4.查询员工浩为176的员工的姓名和部门号
SELECT last_name,department_id FROM employees WHERE employee_id = 176;
3.运算符
3.1 算数运算符
练习：查询员工id为偶数的员工信息
SELECT employee_id,last_name,salary FROM employees WHERE employee_id %2 = 0;
3.2 比较运算符
3.2.1 练习：查询表中commission_pot为null的数据有哪些
SELECT last_name,salary,commission_pot FROM employees WHERE commission_pot IS NULL; #或 SELECT last_name,salary,commission_pot FROM employees WHERE ISNULL(commission_pot);
3.2.2 练习：查询表中commission_pot不为null的数据有哪些
SELECT last_name,salary,commission_pot FROM employees WHERE commission_pot IS NOT NULL; #或 SELECT last_name,salary,commission_pot FROM employees WHERE NOT commission_pot <=> NULL;
3.2.3 练习 lesast \greateat
SELECT LEAST('g','b','t','m'),GREATEST('g','b','t','m')
FROM 表名；
SELECT LEAST(first_name,last_name),LEAST(LENGTH(first_name),LENGTH(last_name)),
FROM 表名；
3.2.4 练习between...and
#查询工资在6000到8000的员工信息 SELECT employee_id,last_name,salary FROM employees WHERE salary BETWEEN 6000 AND 8000; #交换6000和8000之后，查询不到数据 SELECT employee_id,last_name,salary FROM employees WHERE salary BETWEEN 8000 AND 6000; #查询工资不在6000到8000的员工信息 SELECT employee_id,last_name,salary FROM employees WHERE salary < 6000 or salary> 8000;
3.2.5练习 in \ not in
#查询部门为10，20，30部门的员工信息 SELECT last_name,salary,department_id FROM employees #WHERE department_id = 10 or department_id = 20 or department_id = 30; WHERE department_id IN (10,20,30); #查询工资不是6000，7000，8000的员工信息 SELECT last_name,salary FROM employees WHERE salary NOT IN(6000,7000,8000);
3.2.6 练习like
#查询last_name中包含字符‘a’的员工信息 SELECT last_name FROM employees WHERE last_name LIKE '%a%'; #查询last_name中以字符‘a’开头的员工信息 SELECT last_name FROM employees WHERE last_name LIKE 'a%'; #查询last_name中包含字符‘a’且包含字符‘e’的员工信息 #写法1： SELECT last_name FROM employees WHERE last_name LIKE '%a%' AND last_name LIKE '%e%'; #写法2： SELECT last_name FROM employees WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%' ; #查询员工表的job_id中包含字符‘a’和‘e’的，并且a在e的前面 SELECT job_id FROM employees WHERE job_id LIKE '%a%e%';
3.2.7 练习 _
3.2.8 练习 正则表达式
3.3逻辑运算符
4.排序和分页
4.1 排序
#1.排序 升序ASC 降序DESC SELECT employee_id,last_name,salary FROM employees ORDER BY salary DESC; #如果在ORDER BY后没有显示指明排序的方式，则默认升序排列。 #2.我们可以使用列的别名，进行排序 SELECT employee_id,salary，salary *12 annual_sal FROM employees ORDER BY annual_sal; #列的别名只能在ORDER BY中使用，不能在WHERE中使用 SELECT employee_id,salary,salary *12 annual_sal FROM employees WHERE annual_sal > 8000; #3.二级排序 显示员工信息，按照department_id降序排列，salary的升序排列 SELECT employee_id,salary,department_id FROM employees ORDER BY department_id DESC,salary ASC;
4.2 分页
#每页显示20条记录，此时显示第1页 SELECT employee_id,last_name FROM employees LIMIT 0，20； #每页显示20条记录，此时显示第2页 SELECT employee_id,last_name FROM employees LIMIT 20，20 #每页显示20条记录，此时显示第3页 SELECT employee_id,last_name FROM employees LIMIT 40，20 #LIMIT格式：LIMIT位置偏移量，条目数 #结构“LIMIT 0，条目数”等价于“LIMIT 条目数” #每页显示pageSize条记录，此时显示第pageNo页 #公式：LIMIT(pageNo-1) * pageSize #WHERE...ORDER BY...LIMIT顺序： SELECT employee_id,last_name,salary FROM employees WHERE salary > 8000 ORDER BY salary DESC #LIMIT 0，10 LIMIT 10
4.2.1 练习：表里有107条数据，我们只想要显示滴32，33条数据怎么办呢？
SELECT employee_id,last_name FROM employees LIMIT 31，2； SELECT employee_id,last_name FROM employees LIMIT 2 OFFSET 31；
4.2.2 练习：查询员工表中工资最高的员工信息
SELECT employee_id,last_name，salary FROM employees ORDER BY salary DESC LIMIT 0,1;
4.3 小结
4.3.1 练习：选择工资不在8000-17000的员工的姓名和工资，按工资降序排列，系那是第21到40 位置
SELECT last_name，salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC LIMIT 20,20;
4.3.2 练习：查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT employee_id,last_name,email,department_id FROM employees #WHERE email LIKE '%e%' WHERE email REGEXP '[e]' ORDER BY LENGTH(email) DESC,department_id ASC
4.3.3 案例：查询员工名和姓连接成一个字段，并显示为 姓名
SELECT CONCAT('a','b','c') AS 结果
SELECT CONCAT(last_name,first_name) AS 姓名
FROM employees;
4.3.4 案例：显示出表employees的全部列，各个列之间用逗号连接，列头显示成out_put
SELECT CONCAT(`last_name`,',',`first_name`,',',`salary`) AS out_put FROM employees;
4.3.5 案例：查询员工名中第二个字符为_的员工名
SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$'
4.3.6
4.3.7 案例：按年薪的高低显示员工信息和 年薪【按表达式排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
4.3.8 案例：按年薪的高低显示员工信息和 年薪【按别名排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC;
5.函数
5.1 字符函数
# 1.length
SELECT LENGTH('join')
# 2.concat 拼接字符串
SELECT CONCAT(last_name,'—',first_name) 姓名 FROM employees;
# 3.upper、lower
SELECT UPPER('join');
SELECT LOWER('join');
#实例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;
# 4.substr、substring
SUBSTR(string,ps,len)
string:指定字符串
pos:规定字符串从何处开始，（这里的第一个位置是1不是0，为正数时则从字段开始出开始，为负数则从结尾处开始）
len:要截取字符串的长度
注意：索引从1开始
#截取从指定索引处后面所有字符(陆展元)
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;
#截取从指定索引处指定字符长度的字符(李莫愁)
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;
# 5.instr 返回子串第一次出现的索引，若找不到返回0
SELECT INSTR('李莫愁爱上了陆展元','李莫愁') AS out_put
#6.trim 去掉字符串前后的空白或字头字尾
SELECT TRIM( '学习' ) AS out_put
SELECT TRIM('a' FROM 'aaa学aaa习aaa') AS out_put
#7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('哈喽',10,'*') AS out_put
#8.rpad 用指定的字符实现右填充指定长度
SELECT rPAD('哈喽',10,'*') AS out_put
#9.replace替换
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put
案例：姓名中首字母大写，其他字母小写，然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)) 别名
5.2 数学函数
#1.round 四舍五入 SELECT ROUND(1.45) #2.cell 向上取整，返回>=该参数的最小整数 SELECT CELL(-1.02); #3.floor 向下取整，返回<=该参数的最大整数 SELECT FLOOR(-9.02); #4.truncate 截断 SELECT TRYNCATE(1.6999,1); #5.mod取余 #mod(a,b): a-a/b*b SELECT MOD(10,3); SELECT 10%3;
5.3 日期函数
#1.now 返回当前系统日期+时间
SELECT NOW();
#2.curdate 返回当前系统日期，不包含时间
SELECT CURDATE();
#3.curtime 返回当前时间，不包含日期
SELECT CURTIME();
#4.
select year(now()) 年, month(now()) 月;
select year('1998-8-18') 年;
select monthname(now()) 月;
select datediff(now(), '1998-10-8');
#5.str_to_data：将日期格式的字符转换成指定格式日期
select str_to_date('1992-4-3', '%Y-%m-%d') output;
#查询入职日期为1992-4-3的员工信息
select * from employees where hiredate='1992-4-3';
select * from employees where hiredate=str_to_date('4-3-1992', '%m-%d-%Y');
#6.date-format：将日期转换成字符
select date_format(now(), '%Y年%m月%d日');
#查询有奖金的员工名和入职日期
select last_name, date_format(hiredate, '%m月/%d日 %Y年') 入职日期
from employees
where commission_pct is not null;
5.4 其他函数
SELECT VERSION();用于返回MySQL数据库的当前版本 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; #2.case函数 case的使用法一： case 要判断的字段或表达式 when 条件1 then 要显示的值1或语句1 when 条件2 then 要显示的值2或语句2 when 条件3 then 要显示的值3或语句3 ...... else 要显示的值n或语句n end 【as 别名】 #案例：查询员工的工资，要求： 部门号=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 salary end as 新工资 from employees; case的使用法二： case when 条件1 then 要显示的值1或语句1 when 条件2 then 要显示的值2或语句2 when 条件3 then 要显示的值3或语句3 ...... else 要显示的值n或语句n end 【as 别名】 #查询员工的工资情况： 如果工资>2000，显示A级别 如果工资>1500，显示B级别 如果工资>1000，显示C级别 其他工资，显示D级别; select salary 原始工资, department_id, case when salary>20000 then 'A' when salary>15000 then 'B' when salary>10000 then 'C' else 'D' end as 工资级别 from employees;
5.6 分组函数
功能：用作统计使用，又称为聚合函数或统计函数或组函数
sum 求和、max 最大值、min 最小值、avg 平均值、count 计数 #使用 select sum(salary) from employees; select max(salary) from employees; select min(salary) from employees; select avg(salary) from employees; select count(salary) from employees; #组合 select sum(salary) 和, max(salary) 最大, round(avg(salary)) from employees; #忽略NULL #和distinct搭配 select sum(distinct salary),sum(salary) from employees; select count(distinct salary),sum(salary) from employees; # select count(*) from employees; select count(1) from employees; #和分组函数一同查询的字段要求是group by后的字段
6.分组查询
#语法： select 分组函数，列（要求出现在group by 的后面） from 表 where 帅选条件 group by 分组的列 order by 子句 #查询每个工种的最高工资 select max(salary), job_id from employees group by job_id; #查询每个位置的部门个数 select count(*), location_id from departments group by location_id; #查询邮箱中包含a字符的，每个部门的平均工资 select avg(salary), department_id, email from employees where email like '%a%' group by department_id; #查询有奖金的每个领导手下员工的最高工资 select max(salary), manager_id from employees where commission_pct is not null group by manager_id; #查询哪个部门的员工个数大于2 select count(*), department_id from employees group by department_id having count(*) > 2; #查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资 select job_id, max(salary) from employees where commission_pct is not null group by job_id having max(salary) > 12000; #查询领导编号大于102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资 select manager_id, min(salary) from employees where manager_id > 102 group by manager_id having min(salary) > 5000; #按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些 select length(concat(first_name, last_name)) 长度, count(*) from employees group by length(concat(first_name, last_name)) having count(*) > 5; #查询每个部门，每个工种员工的平均工资 select avg(salary), department_id, job_id from employees group by department_id, job_id; #查询每个部门，每个工种员工的平均工资，平均工资大于10000，并且按平均工资的高低显示出来 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;
7.连接查询（多表连接）
含义：当查询的字段来自于多个表时，就会用到连接查询
7.1 等值连接(sql92)
SELECT NAME,boyName FROM beauty,boys WHERE beauty.boyfriend = boys.id; #为表起别名：提高语句的简洁度，区分多个重名的字段 SELECT NAME,boyName FROM beauty AS g,boys AS b WHERE g.boyfriend = b.id; #案例：查询城市中第二个字符为o的部门名和城市名 SELECT department_name,city FROM departments d,location l WHERE d.`location_id`=l.`location_id` AND city LIKE '_o%'; #查询每个城市的部门个数 SELECT COUNT(*) 个数，city FROM departments d,location l WHERE d.`location_id`=l.`location_id` GROUP BY city; #三表连接 案例：查询员工名、部门名和所在的城市 SELECT last_name,department_name,city FROM departments d,location l,employees e WHERE d.`location_id`=l.`location_id` AND d.`department_id`=e.`department_id`;
7.2 非等值连接(sql92)
#查询员工的工资和工资级别 SELECT salary,grade_level FROM employees e,job_grades g WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;
7.3 自连接(sql92)
#查询员工名和上级名 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`;
8.sql99语法
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
按功能分类：(sql99)
内连接：inner
等值连接
非等值连接
自连接
外连接：
左外连接 left 【outer】
右外连接 right 【outer】
全外连接 full【outer】
交叉连接 cross
8.1内连接
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件；
8.2等值连接：
#查询员工名、部门名 SELECT last_name,department_name FROM departments d INNER JOIN employees e ON d.`department_id`=e.`department_id`; #查询部门个数>3的城市名和部门个数（添加分组+筛选） SELECT city,COUNT(*) 部门个数 FROM departments d INNER JOIN location l ON d.`location_id`=l.`location_id` GROUP BY city HAVING COUNT(*)>3; #三表 #查询员工名、部门名、工种名，并按部门名降序（添加三表连接） 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 departments_name DESC;
8.3 非等值连接：
#查询工资级别的个数>2的个数，并且按照工资级别降序 SELECT COUNT(*),salary,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.4 自连接：
#查询员工名和上级名 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%';
8.5 外连接：
1.用于查询一个表中有，另一个表没有的记录
2.外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
3.左外连接：left join左边的是主表
右外连接：right join 右边的是主表
4.左外和右外交换两个表的顺序，可以实现同样的效果
5.全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
#查询男朋友 不在男神表的女神名 #左外连接 SELECT g.name FROM beauty AS g LEFT OUTER JOIN boys AS b ON g.`boyfriend` = b.`id` WHERE g.`id` IS NOT NULL; #右外连接 SELECT g.name FROM boys b RIGHT OUTER JOIN beauty g ON g.`boyfriend` = b.`id` WHERE g.`id` IS NOT NULL; #全外连接 SELECT g.*,b.* FROM beauty g FULL OUTER JOIN boys b ON g.`boyfriend` = b.`id` #交叉连接 SELECT g.*,b.* FROM beauty g CROSS JOIN boys b （笛卡尔乘积）
9. 子查询
含义：出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置：
select 后面：仅仅支持标量子查询
from后面：支持表子查询
where或having后面 : 标量子查询（单行）
列子查询（多行）
行子查询
exists后面（相关子查询）: 表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集只有一行多列）
表子查询（结果集一般为多行多列）
9.1 where或having后面
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
);
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)
);
案例2：返回其他工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id以及salary
#①查询job_id为‘IT_PROG0’部门任一工资
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.行子查询（结果集一行多列或多列多行）
案例：查询员工编号最小并且工资最高的员工信息
#①查询最小的员工编号
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)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
9.2 select后面
案例：查询每个部门的员工个数
SElECT d.*,( SELECT COUNT(*) FROM employees WHERE e.`department_id` = d.`department_id` )个数 FROM departments d;
9.3 from后面
#将子查询结果充当一张表，必须起别名
案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②连接①的结果集和job_grades表，筛选条件平均工资between lowest_sal and highest_sal
SELECT ag_dept.*,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_dept.ag BETWEEN lowest_sal AND highest_sal
9.4 exists后面（相关子查询）
语法：exists(完整的查询语句）
结果：1或0
SELECT EXISTS (SELECT employee_id FROM employees);
案例1：查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id` = e.`department_id`
)
案例2：查询没有女朋友的男神信息
#in
SELECT b.*
FROM boys b
WHERE b.id NOT IN (
SELECT boyfriend_id
FROM beauty
)
#exists
SELECT b.*
FROM boys b
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty g
WHERE b.`id` = g.`boyfriend`
)
10.分页查询
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：select 查询列表
from 表
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段
limit offset , size
(offset:要显示的条目的起始索引（索引从0开始）)
(size:要显示的条目个数)
详细见4分页
11.联合查询
union 联合 合并：将多条查询语句德结果合并成一个结果。
语法：
查询语句1
union
查询语句2
.....
应用场景：要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时。
特点：1.要求多条查询语句的查询列数是一致的
2.要求多条查询语句的查询的每一列的类型和顺序一致
3.union关键字默认去重，如果使用union all不去重
引入的案例：查询部门编号>90或邮箱包含a的员工姓名和id
SELECT lasy_name,id FROM employees WHERE email LIKE '%a%' OR department_id>90; SELECT lasy_name,id FROM employees WHERE email LIKE '%a%' UNION SELECT lasy_name,id FROM employees WHERE department_id>90;
12.DML语言
数据操纵语言
插入：insert
修改：update
删除：delete
12.1 插入语句
方式一：
语法：insert into 表名（列名，......）value（值1 ，.......）
eg. insert into beauty(name, sex , age) value('冯怡婷','女',20),（'张三','男',21）
支持多行插入，支持子查询
方式二：
语法： insert into 表名
set 列名=值，列名=值，.......
eg.insert into beauty
set age=20,name='冯怡婷',sex='女'
12.2 修改语句
1.修改单表的记录
语法：
update 表名
set 列= 新值，列=新值，.........
where 筛选条件
案例：修改表中姓冯的女神电话号为111，魅力值为100
UPDATE beauty SET phone='111',usercp = 100 WHERE name LIKE '冯%';
2.修改多表的记录【补充】
语法：
sql92语法：
update 表1 别名，表2 别名
set 列= 新值，列=新值，.........
where 连接条件
and 筛选条件；
sql99 语法：
update 表1 别名
inner |left|right join 表2 别名
on 连接条件
set 列=值，.......
where 筛选条件；
案例：修改张无忌的女朋友的手机号为1114
UPDATE boys b INNER JOIN beauty g ON b.`id`= g.`boyfriend_id` SET b.`phone`='1114' WHERE g.boyName = '张无忌';
12.3 删除语句
方式一：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 表名；
案例：删除手机号以9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';
案例：删除张无忌女朋友的信息
DELETE g FROM beauty g INNER JOIN boys b ON g.`boyfriend_id`=b.`id` WHERE b.`boyName`='张无忌';
delete和truncate区别：
13.DDL语言
数据定义语言
库和表的管理
1.库的管理
创建、修改、删除
2.表的管理
创建、修改、删除
创建：create
修改：alter
删除：drop
13.1库的管理
1.库的创建：
案例：创建库books
CREATE DATABASE books;
2.库的修改
RENAME DATABASE books To 新库名;
更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;
3.库的删除
DROP DATABASE IF EXISTS books;
13.2 表的管理
1.表的创建
create table 表名（
列名 列的类型【（长度）约束】
列名 列的类型【（长度）约束】
列名 列的类型【（长度）约束】
......
）
CREATE TABLE book( id INT,#编号 bName VACHAR(20), price DOUBLE, publishdate DATETIME; );
2.表的修改
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】；
#修改列名 ALTER TABLE book CHARACTER COLUMN（可以省略） 原列名 新列名 类型; ALTER TABLE book CHARACTER COLUMN publishdate pubDate DATETIME; #修改列的类型或约束 ALTER TABLE book MODIFY COLUMN pubdate 新类型; ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMD;
#添加新列 ALTER TABLE book ADD COLUMN 新列名 类型; ALTER TABLE book ADD COLUMN annual DOUBLE; #删除列 ALTER TABLE book DROP COLUMN 列名; ALTER TABLE book DROP COLUMN annul; #修改表名 ALTER TABLE book RENAME TO 新表名 ALTER TABLE book RENAME TO book_
3.表的删除
DROP TABLE 表名; DROP TABLE IF EXISTS 表名; 通用的写法: DROP DATABASE IF EXISTS 旧库名; CREATE DATABASE 新库名; DROP DATABASE IF EXISTS 旧表名; CREATE TABLE 表名 ;
4.表的复制
INSERT INTO author VALUES (1,'村上春树','日本'), (2,'莫言','中国'), (3,'冯唐','中国'), (4,'金庸','中国'); 1.仅仅复制表的结构 CREATE TABLE copy LIKE author; 2.复制表的结构+数据 CREATE TABLE copy SELECT * FROM author 3.只复制部分数据 CREATE TABLE copy SELECT id,name FROM author WHERE nation = '中国'; 4.仅仅复制某些字段 CREATE TABLE copy SELECT id,name FROM author WHERE 1=2;
13.3 库和表的管理
14.常见的数据类型
数值型：
整型
小数：定点数
浮点数
字符型：
较短的文本：char、varchar
较长的文本：text、blob（较长的二进制数据）
日期型
14.1 整型
分类：tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8
如何设置无符号和有符号
CREATE TABLE tab_int( t1 INT, t1 INT UNSIGNEG )
14.2小数
浮点型：float(M.D) double(M,D) m总长度 d小数位
定点数：dec(M,D）decmal(M,D)
特点：
14.3 字符型
较短的文本：char、varchar
较长的文本：text、blob（较长的二进制数据）
特点：
14.4 日期型
15. 常见约束
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
分类：六大约束
NOT NULL：非空，用于保证该字段的值不能为空
比如姓名、学号等
DEFAULL：默认，用于保证该字段有默认值
比如性别
PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
CHECK：检查约束【Mysql中不支持】
比如年龄、性别
FOREIGN KEY：外键 用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
在主表添加外键约束，用于引用主表中某列的值
比如学生的专业编号，员工表的部门编号，员工表的工种编号
约束的添加实时机：
1.创建表时
2.修改表时
约束的添加分类：
列级约束：
六大约束语法上都支持，但外键约束没有效果
表级约束：
除了非空、默认，其他的都支持
CREATE TABLE 表名(
字段名 字段类型 列级约束，
字段名 字段类型 ，
表级约束
15.1 创建表时添加 列级约束
#1.添加列级约束
直接在字段名和类型后面追加 约束类型即可。
只支持：默认、非空、主键、唯一
USE students;
CREATE TABLE stuinfo(
id INT FOREIGN KEY, #主键
stuName VARCHAR(20) NOT NULL, #非空
gender CHAR(1) CHECR(gender='男' OR gender ='女'),#检查
age INT DEFAULT 18,#默认约束
majorId INT FOREIGN KEY REFERENCES major(id)#外键
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
DESC stuinfo;
#查看stuinfo表中所有的索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;
15.2 创建表时添加表级约束
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 CHECR(gender='男' OR gender ='女'),#检查
CONSTRAINT fk_studio_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);
CREATE TABLE stuinfo(
id INT,
stuName VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorId INT,
PRIMARY KEY(id),#主键
UNIQUE(seat),#唯一键
CHECR(gender='男' OR gender ='女'),#检查
FOREIGN KEY(majorid) REFERENCES major(id)#外键
);
通用的写法：
CREATE TABLE stuinfo(
id INT FOREIGN KEY, #主键
stuName VARCHAR(20) NOT NULL, #非空
gender CHAR(1),
seat INT UNIQUE,#唯一
age INT DEFAULT 18,#默认约束
majorid INT,
CONSTRAINT fk_studio_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);
主键和唯一的对比 :
外键的特点：
15.3 修改表时添加约束
语法：
15.4 修改表时删除约束
15.5 练习
16. 标识列
又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值
17.TCL 事务的介绍
17.1 事务的介绍
事务的属性：
17.2 事务的创建
17.3 事务并发问题
17.4
17.5 回滚点的演示
18. 视图
18.1 视图的介绍
含义：虚拟表，和普通表一样使用
MySQL5.1版本出现的新特性，是通过表动态生成的数据
案例：查询姓张的学生名和专业名：
第一种方法： select stuname,majorname from stuinfo s inner join major m on s.`majorid`=m.`id` where s.`stuname` like '张%'; 第二种方法： CREATE VIEW v1 AS select stuname,majorname from stuinfo s inner join major m on s.`majorid`=m.`id`; select * from v1 where stuname like '张%';
18.2 视图的创建
语法：create view 视图名
as
查询语句；
案例：
1.查询姓名中包含a字符的员工名、和工种信息
①创建 CREATE VIEW v1 AS SELECT last_name,department_name,job_title FROM employees e JOIN departments d ON e.department = d.department_id JOIN jobs j ON j.job_id=e.job_id; ②使用 SELECT * FROM v1 WHERE last_name LIKE '%a';
2.查询各部门的平均工资级别
①创建视图查看每个部门的平均工资 CREATE VIEW v2 AS SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id; ②使用 SELECT v2.`ag`,g.grade_level FROM v2 JOIN job_grades g ON v2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
18.3 视图的修改
18.4 视图的删除
18.5 查看视图
案例：
18.6 视图的更新
具备以下特点视图不允许更新：
18.7 视图和表的对比
19. 变量
系统变量：
全局变量
会话变量
自定义变量：
用户变量
局部变量
19.1 系统变量
说明：变量由系统提供，不是用户定义，属于服务器层面
19.1.1 全局变量
19.1.2 会话变量
19.2 自定义变量
19.2.1 用户变量
19.2.2 局部变量
20. 存储过程的函数
20.1 存储过程的介绍
20.2 存储过程的语法
1.创建语法：
2.调用语法：
20.3 空参的存储过程
20.4 带in模式的存储过程
20.5 带out模式的存储过程
20.6 带inout模式的存储过程
20.7 删除存储过程
20.8 查看存储过程的信息
21. 函数
21.1 函数的介绍
21.2 函数的创建和调用语法
1.创建语法
CREATE FUNCTION 函数名（参数列表）RETURNS 返回类型
BEGIN
函数体
END
注意：
1.参数列表 包含两部分
参数名 参数类型
2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议
return 值；
3.函数体中仅有一句话，则可以省略begin end
4.使用delimiter语句设置结束标记
2.调用语法
SELECT 函数名（参数列表）
3.案例
21.3 函数的查看和删除
查看函数 SHOW CREATE FUNCTION myf3; 删除函数 DROP FUNCTION myf3;
案例：
22.流程控制结构
顺序结构：程序从上往下一次执行
分支结构：程序从两条或多条路径中选择一条去执行
循环结构：程序在满足一定条件的基础上，重复执行一段代码
22.1 分支结构
if函数：
case结构：
案例：
if结构：
案例：
22.2 循环结构
分类：while、loop、repeat
循环控制：
1.while
2.loop
3.repeat
案例：
将cbss_account_code 是 9022071282862111的cust_id改成99140753965，group_id 改成9090790279900025374 表名是tf_f_account.还有个 pay_name改成中国农业银行股份有限公司白城分行
UPDATE tf_f_account
SET cust_id=99140753965,group_id = 9090790279900025374,pay_name='中国农业银行股份有限公司白城分行'
WHERE cbss_account_code=9022071282862111;
UPDATE tf_f_account
SET cust_id=99140753965,group_id = 9090790279900025374,pay_name='中国农业银行股份有限公司白城分行'
WHERE cbss_account_code=9022071282862111;
select
count(*)
from
information_schema.COLUMNS
where
TABLE_SCHEMA='BUILDING'
and
table_name='dm_res_qk_operation2'
42万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
