MySQL学习笔记
目录
1.SQL概述
保存数据的容器:
数组
集合
文件
1.1：数据库的好处
实现数据持久化
使用完整的管理系统统一管理,易于查询
DB
数据库(database):存储数据的“仓库”。它保存了一系列有组织的数据。
DBMS
数据库管理系统(Database Management System) 。数据库是通过DBMS创建和操作的容器
SQL
结构化查询语言(Structure Query Language) :专门用来与数据库通信的语言。
1.2：数据库管理系统
常见的数据库管理系统: MySQL、Oracle、 DB2、 SqlServer等
1.3：SQL语言概述
SQL的优点:
1、不是某个特定数据库供应商专有的语言,几乎所有DBMS都支持SQL
2、简单易学
3、虽然简单,但实际上是一种强有力的语言,灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。
1.4：SQL语言的分类
-
DML (Data Manipulation Language):数据操纵语句,用于添加、删除、修改、查询数据库记录,并检查数据完整性
-
INSERT:添加数据到数据库中
-
UPDATE:修改数据库中的数据
-
DELETE:删除数据库中的数据
-
SELECT:选择（查询)数据SELECT是SQL语言的基础,最为重要。
-
-
DDL (Data Definition Language):数据定义语句,用于库和表的创建、修改、删除。
-
CREATE TABLE:创建数据库表
-
ALTER TABLE:更改表结构、添加、删除、修改列长度
-
DROP TABLE:删除表
-
CREATE INDEX:在表上建立索引
-
DROP INDEX:删除索引
-
-
DCL (Data Control Language):数据控制语句,用于定义用户的访问权限和安全级别。
-
GRANT:授予访问权限
-
REVOKE:撤销访问权限
-
COMMIT:提交事务处理
-
ROLLBACK:事务处理回退.SAVEPOINT:设置保存点
-
LOCK:对数据库的特定部分进行锁定
-
2.安装与使用
2.1:MySql数据库产品的介绍
MySQL数据库隶属于MySQL AB公司,总部位于瑞典,后被oracle收购。
优点:成本低:开放源代码,一般可以免费试用.
性能高:执行很快
简单:很容易安装和使用.
-
DBMS分为两类:
-
基于共享文件系统的DBMS(Access )
-
基于客户机 一服务器的DBMSMySQL、 Oracle、 SqlServer
-
2.2:MySql数据库的安装
安装地址
mysql-8.0.19-winx64.zip安装教程_泡椒猪肝的博客-CSDN博客_mysql压缩包安装教程8.0.19
2.3：启动和停止LMySQL服务
方式一:通过计算机管理方式
右击计算机一管理一服务一启动或停止MySQL服务
方式二:通过命令行方式
启动: net start mysql服务名
停止: net stop mysql服务名
2.4：MySQL服务端的登录和退出
登录
mysql -h主机名-u用户名-p密码
退出
exit
2.5：MySql数据库的使用
-
不区分大小写
-
每句话用;或\g结尾
-
各子句一般分行写
-
关键字不能缩写也不能分行
-
用缩进提高语句的可读性
1.进入mysql,在命令行中输入: mysql-uroot-p####(其中:####表示密码)
2.查看mysql中有哪些个数据库: show databases; (2)
3.使用一个数据库: use数据库名称; (3. atguigu)
4.新建一个数据库: create database数据库名(1. atguigu)
5.查看指定的数据库中有哪些数据表: show tables; (4, 6, 9)
6.建表：create table custome (d,varchar(30),age int, name varchar(30),name varchar(30),birthday date);
7,查看表的结构: desc表名(7)
8.删除表: drop table表名(8)
1,查看表中的所有记录: select* from表名;
2.向表中插入记录: insert into表名(列名列表) values(列对应的值的列表);
3.注意:插入varchar或date型的数据要用单引号引起来
4.修改记录: update表名set列1=列1的值,列2= 列2的值where...
5.删除记录: delete from表名where....
1.查询所有列: select* from表名;
2.查询特定的列: select列名1,列名2,...from表名
3.对查询的数据进行过滤:使用where子句
4.运算符:
select * from customer where salary >= 2000 and salary <= 3000; select * from customer where salary between 2000 and 3000; select * from customer where salary = 2000 or salary = 1000 or salary = 3100; select * from customer where salary in (1000, 2000, 3100); select name from customer where name like 'xo%';--查询name中有o的人的名字 select name from customer where name like'_r%';--查询name中第3个字母是r的人的名字 select * from customer where email is null; --查询email 为空的所有人的信息 select * from customer where email is not null;--查询email不为空的所有人的信息 select * from customer order by salary;--查询所有客户信息,且按salary升序排列 select * from customer order by salary desc;查询所有客户信息,且按salary降序排列
2.6: 数据库图形化工具
Navicat Premium 12.1.16.0 安装与激活（图文教程）_DanielMaster的博客-CSDN博客_nativecat16激活^v59^pc_rank_34_2,201^v3^control&utm_term=navicat16%E6%BF%80%E6%B4%BB&spm=1018.2226.3001.4187
3.数据处理之查询 (DQL)
3.1:基本的SELECT语句
SELECT * | { [DISTINCT] column | expression [alias],...}FROM table;
-
SELECT 标识选择哪些列。
-
FROM 标识从哪个表中选择。
选择全部列
-
SELECT * FROM departments;
选择特定的列
SELECT department_id, location_id FROM departments;
注意:
SQL 语言大小写不敏感
SQL 可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写。使用缩进提高语句的可读性。
列的别名:
重命名一个列。
便于计算。
紧跟列名,也可以在列名和别名之间加入关键字“AS’ ,别名使用双引号,以便在别名中包含空格或特殊的字符并区分大小写。
使用别名
SELECT last name AS namel, commission_pct comm FROMemployees; SELECT last name "Name" ,salary*12 "Annual Salary" FROM employees;
字符串字
符串可以是SELECT列表中的一个字符,数字,日。期。
日期和字符只能在单引号中出现。
每当返回一行时,字符串被输出一次。
显示表结构
使用DESCRIBE命令,表示表结构 DESC[RIBE] tablename
DESCRIBE employees
3.2:过滤和排序数据
过滤使用WHERE子句,将不满足条件的行过滤掉。
SELECT *|{[DISTINCT] column | expression [alias] ,...} FROM table[WHERE condition (s)];
WHERE 子句紧随FROM子句
WHERE子句
SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department id = 90;
比较运算
SELECT last name, salary FROM employees WHERE salary <=3000;
==(不是==) > 大于 >=大于、等于 < 小于 <=小于、 <>等于不等于(也可以是!=)
其它比较运算
BETWEEN.... AND...在两个值之间(包含边界)
BETWEEN 使用BETWEEN运算来显示在一个区间内的值
SELECT last name, salary FROMe mployees WHERE salary BETWEEN 2500 AND 3500军
IN(set) 等于值列表中的一个
使用IN运算显示列表中的值。
SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);
LIKE 模糊查询
-
使用LIKE运算选择类似的值
-
选择条件可以包含字符或数字:
-
% 代表零个或多个字符
-
_(任意个字符)代表一个字符
-
SELECT first name FROM employees WHERE first name LIKE 'S%'
LIKE
'%' 和 '_' 可以同时使用。
SELECT last name FROM employees WHERElast_name LIKE '_o%'
IS NULL 空值
使用IS (NOT) NULL判断空值
SELECT last_name, manager id FROM employees WHERE manager id IS NULL
逻辑运算
操作符 含义
AND 逻辑并
AND要求并的关系为真。
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >=10000 AND job_id LIKE '&MAN%';
OR 逻辑或
OR要求或关系为真。
SELECT employee id, last_name, job_id,salary FROM employees WHERE salary >= 10000 OR job_id LIKE 'MAN';
NOT 逻辑否
SELECT last name,job_id
FROM employees
WHERE job_idNOT IN('IT_PROG', 'ST_CLERK',SA _REP');
ORDER BY子句使用
ORDER BY子句排序
ASC(ascend) :升序
DESC(descend )降序。ORDER BY子句在SELECT语句的结尾。
SELECT last_name, job_id, department_id,hire_date FROM employees ORDER BY hire date ;
group by
语法: select 分组函数,列(要求出现在group by的后面) from表 【where筛选条件】 group by 分组的列表 【order by 子句】 查询列表必须特殊,要求是分组函数和group by后出现的字段 注意:
降序排序
SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date DESC;
按别名排序
SELECT employee_id, last name, salary*12 annsal FROM employees ORDER BY annsal;
多个列排序
按照ORDER BY列表的顺序排序。
SELECT last name, department id, salary FROM employees ORDER BY department id, salary DESC;
流程控制函数
#1.if函数: if else的效果
SELECT IF (10<5,'大'小');
SELECT last_name, commission_pct, IF (commission_pct IS NULL,'没奖金,呵呵','有奖金,嘻嘻')备注FROM employees;
3.3:分组函数
分组函数作用于一组数据,并对一组数据返回一个值。
组函数类型
-
AVG () 平均值
-
COUNT() 计算个数
-
MAX () 最大值
-
MIN () 最小值
-
SUM() 求和
-
特点: 1、sum、 avg一般用于处理数值型max, min, count 可以处理任何类型 2、以上分组函数都忽略nul1值 3、可以和distinct搭配实现去重的运算 4. count #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)平均,AX (salary)最高,MIN(salary)最低,coUNT (salary)个数 FROM employees; #2.查询员工表中的最大入职时间和最小入职时间的相差天数 (DIFFRENCE) SELECT DATEDIFF (MAX (hiredate),MIN (hiredate)) DIFFRENCE FROM employees;
组函数语法
SELECT [column,] group function (column),... FROM table [WHERE condition] [GROUP BY column] [ORDER BYcolumn];
AVG(平均值)和SUM(合计)函数
可以对数值型数据使用AVG和SUM函数。
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%';
MIN(最小值)和MAX(最大值)函数
可以对任意数据类型的数据使用MIN和MAX函数。
SELECT MIN (hire_date), MAX (hire_date) FROM employees;
COUNT(计数)函数
COUNT (*)返回表中记录总数,适用于任意数据类型.
SELECT COUNT (*) FROM employees WHERE department_id = 50;
COUNT (expr)返回expr不为空的记录总数。
SELECT COUNT (commission_pct)FROM employees WHERE department_id = 50;
3.4:分组查询
一、语法 select 分组函数，分组后的字段 from 表 【where 筛选条件】 group by 分组的字段 【having 分组后的筛选】 【order by 排序列表】 二、特点 使用关键字 筛选的表 位置 分组前筛选 where 原始表 group by的前面 分组后筛选 having 分组后的结果 group by 的后面
可以使用GROUP BY子句将表中的数据分成若干组
SELECT column, group_function (column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column] ;
明确: WHERE一定放在FROM后面
在SELECT 列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中。
SELECT department_id, AVG (salary) FROM employees GROUP BY department_id;
包含在GROUP BY子句中的列不必包含在SELECT列表中
SELECT AVG (salary) FROM employees GROUP BY department_id
在GROUP BY子句中包含多个列
SELECT department_id dept_id, job_id, SUM (salary) FROM employees GROUP BY department_id, job_id
非法使用组函数
-
不能在WHERE子句中使用组函数。
-
可以在HAVING子句中使用组函数。
SELECT department_id, AVG (salary) FROM employees WHERE AVG (salary) > 8000 GROUP BY department_id;
WHERE AVG (salary) > 8000 * ERROR at line 3: ORA-00934: group function is not allowed here
WHERE 子句中不能使用组函数
过滤分组
过滤分组:HAVING 子句
使用HAVING过滤分组:
1,行已经被分组。
2.使用了组函数。
3.满足HAVING子句中条件的分组将被显示。
SELECT column,group_function FROM table [WHERE condition] [ORDER BY group_by_expression] [HAVING group_condition] [GROUP BY column] ;
HAVING子句
SELECT department_id, MAX (salary) FROM employees GROUP BY department_id HAVINGMAX (salary) >10000;
分组查询 /* 语法： select 查询列表 from 表 【where 筛选条件】 group by 分组的字段 【order by 排序的字段】; 特点： 1、和分组函数一同查询的字段必须是group by后出现的字段 2、筛选分为两类：分组前筛选和分组后筛选 针对的表 位置 连接的关键字 分组前筛选 原始表 group by前 where 分组后筛选 group by后的结果集 group by后 having 问题1：分组函数做筛选能不能放在where后面 答：不能 问题2：where——group by——having 一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率 3、分组可以按单个字段也可以按多个字段 4、可以搭配着排序使用 */ #引入：查询每个部门的员工个数 SELECT COUNT(*) FROM employees WHERE department_id=90; #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、分组后筛选 #案例：查询哪个部门的员工个数>5 #①查询每个部门的员工个数 SELECT COUNT(*),department_id FROM employees GROUP BY department_id; #② 筛选刚才①结果 SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>5; #案例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的领导编号和最低工资 manager_id>102 SELECT manager_id,MIN(salary) FROM employees 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>6000 ORDER BY m ; #5.按多个字段分组 #案例：查询每个工种每个部门的最低工资,并按最低工资降序 SELECT MIN(salary),job_id,department_id FROM employees GROUP BY department_id,job_id ORDER BY MIN(salary) DESC;
3.5:多表查询
#进阶6：连接查询
/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类：
按年代分类：
sql92标准:仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接
*/
SELECT * FROM beauty;
SELECT * FROM boys;
SELECT NAME,boyName FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;
#一、sql92标准
#案例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`;
#2、为表起别名
/*
①提高语句的简洁度
②区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`;
#3、两个表的顺序是否可以调换
#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;
#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%';
#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`;
#6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;
#7、可以实现三表连接？
#案例：查询员工名、部门名和所在的城市
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;
#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;
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal int,
highest_sal int);
INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES('C', 6000, 9999);
INSERT INTO job_grades
VALUES('D', 10000, 14999);
INSERT INTO job_grades
VALUES('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000);
*/
#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`;
笛卡尔集
-
笛卡尔集会在下面条件下产生
-
省略连接条件
-
连接条件无效
-
所有表中的所有行互相连接.
-
-
为了避免笛卡尔集,可以在WHERE加入有效的连接条件。
Mysql连接
使用连接在多个表中查询数据。
SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;
-
在WHERE子句中写入连接条件。
-
在表中有相同列时,在列名之前加上表名前缀
等值连接
SELECT beauty.id, NAME, boyname FROM beauty ,boys WHERE beauty. 'boyfriend_id' =boys.id;
#二、sql99语法
#1、等值连接
/*
① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
*/
语法：
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
特点：
① 一般为表起别名
②多表的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果是多表的交集部分
/*
分类：
内连接（★）：inner
外连接
左外(★):left 【outer】
右外(★)：right 【outer】
全外：full【outer】
交叉连接：cross
*/
#一）内连接
/*
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;
分类：
等值
非等值
自连接
特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
*/
#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 j
ON e.`job_id`= j.`job_id`
WHERE e.`last_name` LIKE '%e%';
#3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）
#①查询每个城市的部门个数
#②在①结果上筛选满足条件的
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
#案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
#①查询每个部门的员工个数
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
#② 在①结果上筛选员工个数>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 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;
#二、外连接
/*
应用场景：用于查询一个表中有，另一个表没有的记录
特点：
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和 sql99pk
/*
功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高
*/
非等值连接：
#二）非等值连接 #查询员工的工资级别 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;
自连接：
语法： select 查询列表 from 表 别名1,表 别名2 where 等值的连接条件 【and 筛选条件】 【group by 分组字段】 【having 分组后的筛选】 【order by 排序字段】
#三）自连接 #查询员工的名字、上级的名字 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%';
区分重复的列名
-
使用表名前缀在多个表中区分相同的列
-
在不同表中具有相同列名的列可以用表的别名加以区分。
-
如果使用了表别名,则在select语句中需要使用表别名代替表名
-
表别名最多支持32个字符长度,但建议越少越好
表的别名
-
使用别名可以简化查询.
-
使用表名前缀可以提高执行效率。
SELECT bt.id, NAME, boyname FROM beauty bt, boys b; WHERE bt. 'boyfriend id'=b.id;
连接n个表,至少需要n-1个连接条件。例如:连接三个表,至少需要两个连接条件。
SQL99:使用ON子句创建连接
#二、sql99语法
/*
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
分类：
内连接（★）：inner
外连接
左外(★):left 【outer】
右外(★)：right 【outer】
全外：full【outer】
交叉连接：cross
*/
#一）内连接
/*
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;
分类：
等值
非等值
自连接
特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
*/
#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 j
ON e.`job_id`= j.`job_id`
WHERE e.`last_name` LIKE '%e%';
#3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）
#①查询每个城市的部门个数
#②在①结果上筛选满足条件的
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
#案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
#①查询每个部门的员工个数
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
#② 在①结果上筛选员工个数>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 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;
#二）非等值连接
#查询员工的工资级别
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;
#三）自连接
#查询员工的名字、上级的名字
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%';
#二、外连接
/*
应用场景：用于查询一个表中有，另一个表没有的记录
特点：
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和 sql99pk
/*
功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高
*/
1、内连接 语法： select 查询列表 from 表1 别名 【inner】 join 表2 别名 on 连接条件 where 筛选条件 group by 分组列表 having 分组后的筛选 order by 排序列表 limit 子句; 特点： ①表的顺序可以调换 ②内连接的结果= ③n表连接至少需要n-1个连接条件 分类： 等值连接 非等值连接 自连接
自然连接中是以具有相同名字的列为连接条件的。
可以使用ON子句指定额外的连接条件
这个连接条件是与其它条件分开的
ON子句使语句具有更高的易读性
Join连接
分类:
-
内连接 [inner] join on
-
外连接.
-
左外连接left [outer] join on.
-
右外连接 right [outer] join on
-
ON子句
SELECT bt.id, NAME, boyname FROM beauty bt inner join boys b On bt 'boyfriend_id'=bid
使用ON子句创建多表连接
SELECT employee_id, city, department_name FROM employees e JOIN departments d ON d.department_id = e.department_id JOIN locations l ON d.location_id = l.location_id;
子查询
一、含义 嵌套在其他语句内部的select语句称为子查询或内查询， 外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多 外面如果为select语句，则此语句称为外查询或主查询 二、分类 1、按出现位置 select后面： 仅仅支持标量子查询 from后面： 表子查询 where或having后面： 标量子查询 列子查询 行子查询 exists后面： 标量子查询 列子查询 行子查询 表子查询 #-、where或having后面 1、标量子查询(单行子查询) 2、列子查询(多行子查询) 3、行子查询(多列多行) 特点: ①子查询放在小括号内 ②子查询一般放在条件的右侧 ③标量子查询,一般搭配着单行操作符使用,> く>= <== <> 列子查询,一般搭配着多行操作符使用 in, any/ some、 all ④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果 2、按结果集的行列 标量子查询（单行子查询）：结果集为一行一列 列子查询（多行子查询）：结果集为多行一列 行子查询：结果集为多行多列 表子查询：结果集为多行多列 三、示例 where或having后面 1、标量子查询 案例：查询最低工资的员工姓名和工资 ①最低工资 select min(salary) from employees ②查询员工的姓名和工资，要求工资=① select last_name,salary from employees where salary=( select min(salary) from employees ); 2、列子查询 案例：查询所有是领导的员工姓名 ①查询所有员工的 manager_id select manager_id from employees ②查询姓名，employee_id属于①列表的一个 select last_name from employees where employee_id in( select manager_id from employees );
案例
#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号部门最低工资的部门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 ); #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'; #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 ); #二、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 ) 部门名; #三、from后面 /* 将子查询结果充当一张表，要求必须起别名 */ #案例：查询每个部门的平均工资的工资等级 #①查询每个部门的平均工资 SELECT AVG(salary),department_id FROM employees GROUP BY department_id SELECT * FROM job_grades; #②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal SELECT ag_dep.*,g.`grade_level` FROM ( SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id ) ag_dep INNER JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal AND highest_sal; #四、exists后面（相关子查询） /* 语法： exists(完整的查询语句) 结果： 1或0 */ SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000); #案例1：查询有员工的部门名 #in SELECT 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：查询没有女朋友的男神信息 #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` );
多行子查询
返回多行 子查询操作符返回多行。 使用多行比较操作符。 操作符 含义 IN/NOT IN 等于列表中的任意一个 ANY | SOME 和子查询返回的某一个值比较 ALL 和子查询返回的所有值比较
联合查询
联合查询 /* 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 ALL SELECT t_id,tname FROM t_ua WHERE tGender='male';
4.常见函数
4.1:字符函数
大小写控制函数这类函数改变字符的大小写。
函数 结果
LOWER (' SQL Course ') sql course
UPPER ('SQL Course') SQL COURSE
字符控制函数
这类函数控制字符:
concat拼接 substr截取子串 upper转换成大写 lower转换成小写 trim去前后指定的空格和字符 ltrim去左边空格 rtrim去右边空格 replace替换 lpad左填充 rpad右填充 instr返回子串第一次出现的索引 length 获取字节个数
函数 结果
CONCAT ('Hello','World') HelloWorld
SUBSTR (' Helloworld' ,1,5) Hello
LENGTH (' HelloWorld') 10
INSTR (' HelloWorld','W') 6
LPAD (salary, 10,'*') *****24000
RPAD (salary,10,'*') 24000*****
TRIM ('H'FROM" HelloWorld') elloWorld
REPLACE ('abcd','b',',') amcd
4.2:数学函数
ceil:向上取整 round:四舍五入 mod:取模 floor:向下取整 truncate:截断 rand:获取随机数,返回o-1之间的小数
ROUND: 四舍五入 ROUND(45.926, 2)--------------------->45.93 TRUNCATE: 截断 TRUNC(45.926, 2)----------------------->45.92 MOD:求余 MOD(1600, 300)--------------------------->100
4.3:日期函数
now：返回当前日期+时间 year:返回年 month：返回月 day:返回日 date_format:将日期转换成字符 curdate:返回当前日期 str_to_date:将字符转换成日期 curtime：返回当前时间 hour:小时 minute:分钟 second：秒 datediff:返回两个日期相差的天数 monthname:以英文形式返回月
now:获取当前日期
str_to_date:将日期格式的字符转换成指定格式的日期
STR_TO_DATE('9-13-1999', '%m-%d-%Y') 1999-09-13
date_format:将日期转换成字符
DATE_FORMAT('2018/6/6','%Y年%m月%d日') 2018年06月06日
格式符 功能
%Y 四位的年份
%y 2位的年份
%m 月份(01,02...11,12)
%c 月份(1,2,...11,12)
%d 日(01,02,.)
%H 小时(24小时制)
%h 小时(12小时制)
%i 分钟( 00,01...59)
%s 秒(00,01,59)
#1. 显示系统时间(注:日期+时间) SELECT NOW (); #2. 查询员工号,姓名,工资,以及工资提高百分之20%后的结果(new salary) SELECT employee_id, last_name, salary, salary*1.2 "new salary" FROM employees; #3.将员工的姓名按首字母排序,并写出姓名的长度(length) SELECT LENGTH (last_name)长度, SUBSTR (last_name, 1, 1) 首字符, last_name FROM employees ORDER BY 首字符; #4.做一个查询,产生下面的结果, #<last_name> earns <salary> monthly but wants <salary*3> Dream SalaryKing earns 24000 monthly but wants 72000 SELECT CONCAT (last_name, ' earns ', salary, ' monthly but wants ', salary*3) AS "Dream Salary" FROM employees WHERE salary=24000; #5.使用case-when,按照下面的条件： job grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E 产生下面的结果 Last_name Job_id Grade king AD_PRES A; SELECT Last_name,job_id AS job, CASE job_id WHEN 'AD_PRES' THEN 'A' WHEN 'ST_MAN' THEN 'B' WHEN 'IT_PROG' THEN 'C' WHEN 'SA_REP' THEN 'D' WHEN 'ST_CLERK' THEN 'E' END AS grade WHERE job_id = 'AD_PRES'
4.4:其他函数【补充】
version 当前数据库服务器的版本
database 当前打开的数据库
user当前用户
password('字符')：返回该字符的密码形式
md5('字符'):返回该字符的md5加密形式
4.5：流程函数
①if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2 ②case情况1 case 变量或表达式或字段 when 常量1 then 值1 when 常量2 then 值2 ... else 值n end ③case情况2 case when 条件1 then 值1 when 条件2 then 值2 ... else 值n end
-
条件表达式
-
在SQL语句中使用IF-THEN-ELSE逻辑
-
使用方法:
-
CASE表达式
-
CASE表达式在需要使用IF-THEN-ELSE逻辑时: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END
-
练习:查询部门号为10,20,30的员工信息,若部门号为10,则打印其工资的1.1倍,20号部门,则打印其工资的1.2倍,30号部门打印其工资的1.3倍数 下面是使用case表达式的一个例子: SELECT last_name, job_id, salary, CASE job_id WHEN 'IT PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA REP'THEN 1.20*salary ELSE salary END "REVISED SALARY" FROM employees;
-
4.6：分组函数
max 最大值 min 最小值 sum 和 avg 平均值 count 计算个数
特点
①语法 select max(字段) from 表名; ②支持的类型 sum和avg一般用于处理数值型 max、min、count可以处理任何数据类型 ③以上分组函数都忽略null ④都可以搭配distinct使用，实现去重的统计 select sum(distinct 字段) from 表; ⑤count函数 count(字段)：统计该字段非空值的个数 count(*):统计结果集的行数 案例：查询每个部门的员工个数 1 xx 10 2 dd 20 3 mm 20 4 aa 40 5 hh 40 count(1):统计结果集的行数 效率上： MyISAM存储引擎，count(*)最高 InnoDB存储引擎，count(*)和count(1)效率>count(字段) ⑥ 和分组函数一同查询的字段，要求是group by后出现的字段
5.数据处理之增删改 (DML)
-
使用DML语句
-
向表中插入数据
-
更新表中数据
-
从表中删除数据
-
#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','1898888888',NULL,2); #方式二： INSERT INTO beauty(id,NAME,sex,phone) VALUES(15,'娜扎','女','1388888888'); #3.列的顺序是否可以调换 INSERT INTO beauty(NAME,sex,id,phone) VALUES('蒋欣','女',16,'110'); #4.列数和值的个数必须一致 INSERT INTO beauty(NAME,sex,id,phone) VALUES('关晓彤','女',17,'110'); #5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致 INSERT INTO beauty VALUES(18,'张飞','男',NULL,'119',NULL,NULL); #方式二： /* 语法： insert into 表名 set 列名=值,列名=值,... */ INSERT INTO beauty SET id=19,NAME='刘涛',phone='999'; #两种方式大pk ★ #1、方式一支持插入多行,方式二不支持 INSERT INTO beauty VALUES(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2) ,(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2) ,(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2); #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 UPDATE beauty SET phone = '13899888899' WHERE NAME LIKE '唐%'; #案例2：修改boys表中id好为2的名称为张飞，魅力值 10 UPDATE boys SET boyname='张飞',usercp=10 WHERE id=2; #2.修改多表的记录 #案例 1：修改张无忌的女朋友的手机号为114 UPDATE boys bo INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id` SET b.`phone`='119',bo.`userCP`=1000 WHERE bo.`boyName`='张无忌'; #案例2：修改没有男朋友的女神的男朋友编号都为2号 UPDATE boys bo RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id` SET b.`boyfriend_id`=2 WHERE 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语句 #案例：将魅力值>100的男神信息删除 TRUNCATE TABLE boys ; #delete pk truncate【面试题★】 /* 1.delete 可以加where 条件，truncate不能加 2.truncate删除，效率高一丢丢 3.假如要删除的表中有自增长列， 如果用delete删除后，再插入数据，自增长列的值从断点开始， 而truncate删除后，再插入数据，自增长列的值从1开始。 4.truncate删除没有返回值，delete删除有返回值 5.truncate删除不能回滚，delete删除可以回滚. */ SELECT * FROM boys; DELETE FROM boys; TRUNCATE TABLE boys; INSERT INTO boys (boyname,usercp) VALUES('张飞',100),('刘备',100),('关云长',100);
5.1:数据操纵语言
-
DML (Data Manipulation Language数据操纵语言)可以在下列条件下执行:
-
向表中插入数据
-
修改现存数据
-
删除现存数据
-
-
事务是由完成若干项工作的DML语句组成的!
5.2:插入数据
5.2.1:INSERT语句
一、方式一 语法： insert into 表名(字段名,...) values(值,...); 特点： 1、要求值的类型和字段的类型要一致或兼容 2、字段的个数和顺序不一定与原始表中的字段个数和顺序一致 但必须保证值和字段一一对应 3、假如表中有可以为null的字段，注意可以通过以下两种方式插入null值 ①字段和值都省略 ②字段写上，值使用null 4、字段和值的个数必须一致 5、字段名可以省略，默认所有列 二、方式二 语法： insert into 表名 set 字段=值,字段=值,...; 两种方式 的区别： 1.方式一支持一次插入多行，语法如下： insert into 表名【(字段名,..)】 values(值，..),(值，...),...; 2.方式一支持子查询，语法如下： insert into 表名 查询语句;
语法使用INSERT语句向表中插入数据
INSERT INTO table [(column [, column...])] VALUES (value [, value...]);
使用这种语法一次只能向表中插入一条数据。
-
为每一列添加一个新值。
-
按列的默认顺序列出各个列的值。
-
在INSERT 子句中随意列出列名和他们的值。
-
字符和日期型数据应包含在单引号中。
INSERT INTO departments (department_id, department_name, manager_id, location_id)VALUES(70, 'Public Relations', 100, 1700) ;
1 row created
INSERT INTO employees (employee_id,last_name, email, hire_date, job_id) VALUES (300,' Tom',' tom@ 126.com', to_date ('2012-321' ,'YYYy-mm-dd') ,' SA_RAP') ;
1 row created.
向表中插入空值隐式方式:在列名表中省略该列的值。
INSERT INTOdepartments (department_id,
department_name
VALUES(30, 'Purchasing') ;
1 row created.
显示方式:在VALUES子句中指定空值。
INSERT INTO departments VALUES(100, 'Finance',NULL,NULL); 1 row created.
5.2.2:插人指定的值
NOW ()函数:记录当前系统的日期和时间。
INSERT INTO employees (employee_id, first name, last name, email, phone number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (113, 'Louis', 'Popp', 'LPOPP', '515.124.4567', NOW () , 'AC_ACCOUNT', 6900, NULL, 205, 100) ; 1 row created.
5.2.3:从其它表中拷贝数据
在INSERT 语句中加入子查询。
INSERT INTO emp2 SELECT * FROM employees WHERE department id = 90; 3 rows created.
INSERT INTO sales_reps (id, name, salary, commission_pct) SELECT employee_id, last _name, salary, commission_pct FROM employees WHERE job_id LIKE '%REP%'; 4 rows created.
不必书写VALUES 子句。
子查询中的值列表应与INSERT 子句中的列名对
5.2.4：UPDATE语句语法
使用UPDATE 语句更新数据。
UPDATE table SET column = value[, column = value, ...] [WHERE condition] ;
可以一次更新多条数据。
如果需要回滚数据,需要保证在DML前,进行设置: SET AUTOCOMMIT = FALSE;
5.2.5:更新数据
一、修改单表的记录 ★ 语法：update 表名 set 字段=值,字段=值 【where 筛选条件】; 二、修改多表的记录【补充】 语法： update 表1 别名 left|right|inner join 表2 别名 on 连接条件 set 字段=值,字段=值 【where 筛选条件】;
使用WHERE 子句指定需要更新的数据。
UPDATE employees SET department_id = 70 WHERE employee_id = 113; 1 row updated.
如果省略WHERE 子句,则表中的所有数据都将被更新
UPDATE copy_emp SET department_id = 110; 22 rows updated.
5.2.6:更新中的数据完整性错误
UPDATE employees SET department_id = 55 WHERE department_id = 110;
错误代码: 1452
Cannot add or update a child row: a foreign key constraint fails ('myemployees'. 'employees', CONSTRAINT dept_id_fk' FOREIGN KEY ('department_id') REFERENCES 'departments' ('department_id'))
不存在55号部门
另例:
update employees set manager_id = 299 where employee_id = 203;
5.2.7:DELETE语句
方式一：使用delete 一、删除单表的记录★ 语法：delete from 表名 【where 筛选条件】【limit 条目数】 二、级联删除[补充] 语法： delete 别名1,别名2 from 表1 别名 inner|left|right join 表2 别名 on 连接条件 【where 筛选条件】 方式二：使用truncate 语法：truncate table 表名 方式1：delete语句 单表的删除： ★ delete from 表名 【where 筛选条件】 多表的删除： delete 别名1，别名2 from 表1 别名1，表2 别名2 where 连接条件 and 筛选条件; 方式2：truncate语句 两种方式的区别【面试题】★ 1.truncate删除后，如果再插入，标识列从1开始 delete删除后，如果再插入，标识列从断点开始 2.delete可以添加筛选条件 truncate不可以添加筛选条件 3.truncate效率较高 4.truncate没有返回值 delete可以返回受影响的行数 5.truncate不可以回滚 delete可以回滚
使用DELETE 语句从表中删除数据。
DELETE FROM table [WHERE condition];
使用WHERE 子句删除指定的记录。
DELETE FROM departments WHERE department_name = 'Finance'; 1 row deleted.
如果省略WHERE 子句,则表中的全部数据将被删除
DELETE FROM copy_emp; 22 rows deleted.
5.2.8:删除中的数据完整性错误
DELETE FROM departments WHERE department_id = 60;
错误代码: 1451Cannot delete or update a parent row: a foreign key constraint fails ('myemployees'. 'employees', CONSTRAINT 'dept_id_fk' FOREIGN KEY (' department_id')REFERENCES'departments' ('department_id'))
You cannot delete a row that contains a primary key that is used as a foreign key in another table.
5.2.9：案例
#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','1898888888',NULL,2);
#方式二：
INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'娜扎','女','1388888888');
#3.列的顺序是否可以调换
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('蒋欣','女',16,'110');
#4.列数和值的个数必须一致
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('关晓彤','女',17,'110');
#5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);
#方式二：
/*
语法：
insert into 表名
set 列名=值,列名=值,...
*/
INSERT INTO beauty
SET id=19,NAME='刘涛',phone='999';
#两种方式大pk ★
#1、方式一支持插入多行,方式二不支持
INSERT INTO beauty
VALUES(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2)
,(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2)
,(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2);
#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
UPDATE beauty SET phone = '13899888899'
WHERE NAME LIKE '唐%';
#案例2：修改boys表中id好为2的名称为张飞，魅力值 10
UPDATE boys SET boyname='张飞',usercp=10
WHERE id=2;
#2.修改多表的记录
#案例 1：修改张无忌的女朋友的手机号为114
UPDATE boys bo
INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='119',bo.`userCP`=1000
WHERE bo.`boyName`='张无忌';
#案例2：修改没有男朋友的女神的男朋友编号都为2号
UPDATE boys bo
RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE 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语句
#案例：将魅力值>100的男神信息删除
TRUNCATE TABLE boys ;
#delete pk truncate【面试题★】
/*
1.delete 可以加where 条件，truncate不能加
2.truncate删除，效率高一丢丢
3.假如要删除的表中有自增长列，
如果用delete删除后，再插入数据，自增长列的值从断点开始，
而truncate删除后，再插入数据，自增长列的值从1开始。
4.truncate删除没有返回值，delete删除有返回值
5.truncate删除不能回滚，delete删除可以回滚.
*/
SELECT * FROM boys;
DELETE FROM boys;
TRUNCATE TABLE boys;
INSERT INTO boys (boyname,usercp)
VALUES('张飞',100),('刘备',100),('关云长',100);
6.子查询
-
描述子查询可以解决的问题。
-
定义子查询。
-
列出子查询的类型
-
书写单行子查询和多行子查询。
概念:
出现在其他语句内部的select语句,称为子查询或内查询内部嵌套其他select语句的查询,称为外查询或主查询.
示例:
select first_name from employees where department_id in( select department_id from departments where location_id=1700 )
-
注意事项子查询要包含在括号内.
-
将子查询放在比较条件的右侧.
-
单行操作符对应单行子查询,多行操作符对应多行子查询。
6.1:子查询类型
6.2:单行子查询
-
只返回一行。
-
使用单行比较操作符。
含义 操作符 = Equal to > Greater than >= Greater than or equal to < Less than <= Less than or equal to <> Not equal to
6.2.1:子查询语法
SELECT select_list
FROM table
WHERE expr operator (SELECT select_list
FROM table) ;
-
子查询(内查询)在主查询之前一次执行完成。
-
子查询的结果被主查询(外查询)使用
6.2.2:使用子查询解决问题
子查询
SELECT last name FROM employees WHERE salary >(SELECT salary FROM employees WHERElast_name=' Abel')
6.2.3:执行单行子查询
题目:返回job_id与141号员工相同, salary比143号员工多的员工姓名,job_id和工资
6.2.4:子查询中的HAVING子句
-
首先执行子查询。
-
向主查询中的HAVING子句返回结果。
6.2.5:非法使用子查询
SELECT employee_id, last_name FROM employees WHERE salary=(SELECT MIN (salary)FROM employees GROUP BY department id);
ERROR at line 4:ORA-01427: single-row subquery returns more thanone row
多行子查询使用单行比较符
6.2.6:子查询中的空值问题
SELECT last name, job_id FROM employees WHERE job_id=(SELECT job_id FROM employees WHERE last_name = 'Haas')
no rows selected
子查询不返回任何行
6.3:多行子查询
返回多行。
使用多行比较操作符。
操作符 含义 IN/NOT IN 等于列表中的任意一个 ANY | SOME 和子查询返回的某一个值比较 ALL 和子查询返回的所有值比较
体会any和all的区别
6.3.1：在多行子查询中使用ANY操作符
题目:返回其它部门中比job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_id以及salary
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY (SELECT salary FROM employee's WHERE job_id = 'IT_PROG') AND job id <> 'IT PROG'
6.3.2：在多行子查询中使用ALL操作符
题目:返回其它部门中比job_id为IT_PROG'部门所有工资都低的员工的员工号、姓名、job_id以及salary
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';
6.3.3：子查询中的空值问题
SELECT emp.last_name FROM employees emp WHERE emp.employee_id NOT IN(SELECT mgr.manager _id FROM employees mgr); no rows selected
7.创建和管理表 (DDL)
-
创建数据库
-
创建表
-
描述各种数据类型
-
修改表的定义
-
删除,重命名和清空表
7.0：库和表的管理
#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; #1.仅仅复制表的结构 CREATE TABLE copy LIKE author; #2.复制表的结构+数据 CREATE TABLE copy2 SELECT * FROM author; #只复制部分数据 CREATE TABLE copy3 SELECT id,au_name FROM author WHERE nation='中国'; #仅仅复制某些字段 CREATE TABLE copy4 SELECT id,au_name FROM author WHERE 0;
7.1：创建数据库
-
创建一个保存员工信息的数据库
-
create database employees;
-
-
相关其他命令
-
show databases;查看当前所有数据库
-
use employees; "使用”一个数据库,使其作为当前数据库
-
一、创建库 create database 【if not exists】 库名【 character set 字符集名】; 二、修改库 alter database 库名 character set 字符集名; 三、删除库 drop database 【if exists】 库名;
-
7.2：命名规则
-
数据库名不得超过30个字符,变量名限制为29个。
-
必须只能包含A-Z, a-z, 0-9, 共63个字符。
-
不能在对象名的字符间留空格。
-
必须不能和用户定义的其他对象重名。
-
必须保证你的字段没有和保留字、数据库系统或常用方法冲突。
-
保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了
7.3：CREATETABLE语句
-
必须具备:
-
CREATE TABLE 权限存储空间
-
CREATE TABLE [schema. ] table (column datatype [DEFAULT expr] [, ....]);
-
-
必须指定:
-
表名
-
列名,数据类型,尺寸
-
7.4:创建表
语法
-
CREATE TABLE dept(deptno INT (2), dnameVARCHAR (14), locVARCHAR (13)); Table created.
确认
DESCRIBE dept
7.4:常用数据类型
INT 使用4个字节保存整数数据定长字符数据。 CHAR(size) 若未指定,默认为1个字符,最大长度255 VARCHAR(size) 可变长字符数据,根据字符串实际长度保存,必须指定长度 FLOAT(M,D) 单精度, M=整数位+小数位, D=小数位。D<=M<=255,0<=D<=30,默认M+D<=6 DOUBLE(M,D) 双精度。D<=M<=255,0<=D<=30,默认M+D<=15 DATE 日期型数据,格式'YYYY-MM-DD' BLOB 二进制形式的长文本数据,最大可达4G TEXT 长文本数据,最大可达4G
创建表 CREATE TABLE emp( #int类型,自增 emp_id INT AUTO_INCREMENT, #最多保存20个中英文字符 emp_name CHAR (20), #总位数不超过15位 salary DOUBLE, #日期类型 birthday DATE, #主键 PRIMARY KEY (emp_id) );
7.5:使用子查询创建表
-
使用 AS subquery 选项,将创建表和插入数据结合起来
CREATE TABLE table [(column, column....)]AS subquery;
-
指定的列和子查询中的列要一一对应.
-
通过列名和默认值定义列
复制现有的表: create table emp1 as select * from employees; create table emp2 as select * from employees where 1=2; --创建的emp2是空表。
使用子查询创建表举例
CREATE TABLE dept80
AS
SELECT employee_id, last_name,salary*12 ANNSAL,
hire_date
FROM employees
WHERE department_id = 80;
Table created.
DESCRIBE dept80
7.6:ALTER TABLE语句
使用ALTER TABLE 语句可以实现:
-
向已有的表中添加列
-
修改现有表中的列
-
删除现有表中的列
-
重命名现有表中的列
修改一个列
可以修改列的数据类型,尺寸和默认值
ALTER TABLE dept80 MODIFY (last name VARCHAR (30));
Table altered.
ALTER TABLEdept80MODIFY(salary double (9,2) default 1000);
Table altered.
对默认值的修改只影响今后对表的修改
删除一个列
使用DROP COLUMN子句删除不再需要的列.
ALTER TABLE dept 80 DROP COLUMN job_id; Table altered.
重命名一个列
使用 CHANGE old_column new_column dataType子句重命名列
ALTER TABLE dept80 CHANGE department_name dept name varchar (15);
Table altered.
删除表
数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
DROP TABLE语句不能回滚
DROP TABLE dept80;
Table dropped.
清空表
TRUNCATE TABLE语句:
-
删除表中所有的数据
-
释放表的存储空间
TRUNCATE TABLE detail_dept; Table truncated.
TRUNCATE语句不能回滚
可以使用DELETE语句删除数据,可以回滚
对比:
delete from emp2; select * from emp2; rollback; select * from emp2;
7.7:改变对象的名称
执行RENAME语句改变表,视图的名称
ALTER table dept RENAME TO detail_dept; Table renamed.
必须是对象的拥有者
8.数据类型
-
数值类型
-
字符类型
-
日期类型
8.1:整型
整数类型 字节 范围 Tinyint 1 有符号: -128~127 无符号: 0~255 Smallint 3 有符号: -32768~32767 无符号: 0~65535 Mediumint. 4 有符号: -8388608~8388607 无符号: 0~1677215 (好吧,反正很大,不用记住) Int、 integer 8 有符号: - 2147483648~2147483647 无符号: 0~4294967295 (好吧,反正很大,不用记住) Bigint 8 有符号:-9223372036854775808~9223372036854775807 无符号: 0~9223372036854775807*2+1 (好吧,反正很大,不用记住)
特点： ①都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号 ②如果超出了范围，会报out or range异常，插入临界值 ③长度可以不指定，默认会有一个长度 长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型
8.2:小数
浮点数类型 字节 范圈 float 4 士1.75494351E-38~士3.402823466E+38 (好吧,反正很大,不用记住) double 4 士2.2250738585072014E-308~士 1.7976931348623157E+308 (好吧,反正很大,不用记住) 定点数类型 字节 范围 DEC (M, D) M+2 最大取值范圈与double相同,给定decimal的有效取值范圈由M和D决定 DECIMAL (M, D)
8.3:位类型
位类型 字节 范围 Bit(M) 1~8 Bit(1)~bit(8)
8.4:char和varchar类型
说明:用来保存MySQL中较短的字符串。
字符串类型 最多字符数 描述及存储需求 char (M) M M为0~255之问的整数 varchar (M) M M为0~65535之问的整数
char：固定长度的字符，写法为char(M)，最大长度不能超过M，其中M可以省略，默认为1 varchar：可变长度的字符，写法为varchar(M)，最大长度不能超过M，其中M不可以省略
8.5:binary和varbinary类型
说明:类似于char和varchar,不同的是它们包含二进制字符串而不包含非二进制字符串。
8.6:Enum类型
说明:又称为枚举类型哦,要求插入的值必须属于列表中指定的值之一。
如果列表成员为1~255,则需要1个字节存储
如果列表成员为255-65535,则需要2个字节存储
最多需要65535个成员!
8.7:Set类型
说明:和Enum类型类似,里面可以保存0~64个成员。和Enum类型最大的区别是: SET类型一次可以选取多个成员,而Enum只能选一个根据成员个数不同,存储所占的字节也不同
成员数 字节数 1-8 1 9-16 2 17-24 3 25-32 4 33-64 8
8.8:日期和时间类型
日期和时间类型 字节 最小值 最大值 date 4 1000-01-0 19999-12-31 datetime 8 1000-01-01 00:00:00 9999-12-31 23:59:59 timestamp 4 19700101080001 2038年的某个时刻 time 3 -838:59:59 838:59:59 year 1 1901 2155
year年 date日期 time时间 datetime 日期+时间 8 timestamp 日期+时间 4 比较容易受时区、语法模式、版本的影响，更能反映当前时区的真实时间
8.9：datetime和timestamp的区别
1, Timestamp支持的时间范围较小,取值范围19700101080001--2038年的某个时间Datetime的取值范围: 1000-1-1--9999-12-31
2、timestamp和实际时区有关,更能反映实际的日期,而datetime则只能反映出插入时的当地时区。
3、timestamp的属性受Mysql版本和SQLMode的影响很大
9.约束和分页
-
描述约束,
-
创建和维护约束
-
数据库分页
9.1:什么是约束
-
为了保证数据的一致性和完整性, SQL规范以约束的方式对表数据进行额外的条件限制。
-
约束是表级的强制规定
-
可以在创建表时规定约束(通过CREATETABLE 语句) ,或者在表创建之后也可以(通过ALTER TABLE 语句)
9.2:约束
-
有以下六种约束:
-
NOT NULL 非空约束,规定某个字段不能为空
-
UNIQUE 唯一约束,规定某个字段在整个表中是唯一的
-
PRIMARYKEY 主键(非空且唯一)
-
FOREIGN KEY 外键
-
CHECK 检查约束
-
DEFAULT 默认值
-
注意: MySQL不支持check约束,但可以使用check约束,而没有任何效果;
具体细节可以参阅W3Cschool手册
-
根据约束数据列的限制,约束可分为:
-
单列约束:每个约束只约束一列
-
多列约束:每个约束可约束多列数据
-
-
根据约束的作用范围,约束可分为:
-
列级约束只能作用在一个列上,跟在列的定义后面.
-
表级约束可以作用在多个列上,不与列一起,而是单独定义
-
9.3:NOT NULL 约束
-
非空约束用于确保当前列的值不为空值,非空约束只能出现在表对象的列上。
-
ull类型特征:
-
所有的类型的值都可以是nul1,包括int、float等数据类型.
-
空字符串""不等于nul1,0也不等于null
-
-
创建not null 约束:
CREATE TABLE emp ( id INT (10) NOT NULL, NAME VARCHAR (20) NOT NULL DEFAULT 'abc', sex CHAR NULL );
-
增加not null约束:
ALTER TABLE emp MODIFY sex VARCHAR (30) NOT NULL;
-
取消not null 约束:
ALTER TABLE emp MODIFY sex VARCHAR (30) NULL;
-
取消not null 约束,增加默认值:
ALTER TABLE emp MODIFY NAME VARCHAR (15) DEFAULT 'abc' NULL;
9.4:UNIQUE 约束
-
同一个表可以有多个唯一约束,多个列组合的约束。在创建唯一约束的时候,如果不给唯一约束名称,就默认和列名相同。
-
MySQL会给唯一约束的列上默认创建一个唯一索引
CREATE TABLE USER( id INT NOT NULL, NAME VARCHAR (25), PASSWORD VARCHAR (16), #使用表级约束语法 CONSTRAINT uk name _pwd UNIQUE (NAME, PASSWORD) );
-
表示用户名和密码组合不能重复
-
添加唯一约束
-
ALTER TABLE USERADD UNIQUE (NAME, PASSWORD) ;
-
ALTER TABLE USER ADD CONSTRAINT uk_name_pwd UNIQUE (NAME, PASSWORD) ;
-
ALTER TABLE USER MODIFY NAME VARCHAR (20) UNIQUE;
-
删除约束
-
ALTER TABLE USER DROP INDEX uk_name_pwd;
9.5:PRIMARY KEY 约束
-
主键约束相当于唯一约束+非空约束的组合,主键约束列不允许重复,也不允许出现空值.
-
如果是多列组合的主键约束,那么这些列都不允许为空值,并且组合的值不允许重复。
-
每个表最多只允许一个主键,建立主键约束可以在列级别创建,也可以在表级别上创建。
-
MySQL的主键名总是PRIMARY,当创建主键约束时,系统默认会在所在的列和列组合上建立对应的唯一索引。
PRIMARY KEY 约R
列级模式 CREATE TABLE emp4( id INT AUTO INCREMENT PRIMARY KEY, NAME VARCHAR (20) );
表级模式 CREATE TABLE emp5( id INT NOT NULL AUTO_INCREMENT, NAME VARCHAR (20), pwd VARCHAR (15), CONSTRAINT emp5_id pk PRIMARY KEY (id) );
组合模式 CREATE TABLE emp6( id INT NOT NULL, NAME VARCHAR (20), pwd VARCHAR (15), CONSTRAINT emp7_pk PRIMARY KEY (NAME, pwd) );
删除主键约束
ALTER TABLE emp5 DROP PRIMARY KEY;
添加主键约束
ALTER TABLE emp5ADD PRIMARY KEY (NAME, pwd);
修改主键约束
ALTER TABLE emp5MODIFY id INT PRIMARY KEY;
9.6:FOREIGN KEY 约束
-
外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
-
从表的外键值必须在主表中能找到或者为空。当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据。
-
还有一种就是级联删除子表数据。
-
注意:外键约束的参照列,在主表中引用的只能是主键或唯一键约束的列.
-
同一个表可以有多个外键约束
创建外键约束:
主表 CREATE TABLE dept ( dept id INT AUTO INCREMENT PRIMARY KEY, dept name VARCHAR (20) );
从表 CREATE TABLE emp ( emp id INT AUTO INCREMENT PRIMARY KEY, last name VARCHAR (15), dept id INT,); CONSTRAINT emp_dept_id_fk FOREIGN KEY (dept_id) REFERENCESdept (dept_id)
创建多列外键组合,必须使用表级约束:
主表 CREATE TABLE classes ( id INT, NAME VARCHAR (20), number INT, PRIMARY KEY (NAME, number) );
从表
CREATE TABLE student(
id INT AUTO INCREMENT PRIMARY KEY,
classes name VARCHAR (20),
classes number INT,
FOREIGN KEY (classes_name, classes_number)
REFERENCES classes (NAME, number)
);
删除外键约束:
ALTER TABLE emp DROP FOREIGN KEY emp dept id_fk;
增加外键约束:
ALTER TABLE emp ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY (dept id) REFERENCES dept (dept_id);
FOREIGN KEY:在表级指定子表中的列
REFERENCES:标示在父表中的列
ON DELETE CASCADE(级联删除):当父表中的列被删除时,子表中相对应的列也被删除
ON DELETE SET NULL(级联置空):子表中相应的列置空
CREATE TABLE student ( id INT AUTO INCREMENT PRIMARY KEY, NAME VARCHAR (20), classes name VARCHAR (20), classes number INT, /*表级别联合外键* / FOREIGN KEY (classes name, classes number) REFERENCES classes (NAME, number) ON DELETE CASCADE) ;
9.7:CHECK约束
MySQL可以使用check约束,但check约束对数据验证没有任何作用,添加数据时,没有任何错误或警告
CREATE TABLE temp ( id INT AUTO INCREMENT, NAME VARCHAR (20), age INT CHECK (age > 20), PRIMARY KEY (id) );
9.8:MySQL中使用limit实现分页
-
背景:
-
查询返回的记录太多了,查看起来很不方便,怎么样能够实现分页查询呢?
-
-
分页原理:
-
所谓分页显示,就是将数据库中的结果集,一段一段显示出来需要的条件
-
MySQL中使用1imit实现分页
怎么分段,当前在第几段(每页有几条,当前在第几页)
前10条记录: SELECT * FROM table LIMIT 0,10;
第11至20条记录:SELECT * FROM table LIMIT 10,10;
第21至30条记录: SELECT * FROM table LIMIT 20, 10;
公式:
(当前页数-1)*每页条数,每页条数
SELECT * FROM table LIMIT (PageNo - 1) *PageSize, PageSize;
注意:limit子句必须放在整个查询语句的最后!
一、应用场景 当要查询的条目数太多，一页显示不全 二、语法 select 查询列表 from 表 limit 【offset，】size; 注意： offset代表的是起始的条目索引，默认从0卡死 size代表的是显示的条目数 公式： 假如要显示的页数为page，每一页条目数为size select 查询列表 from 表 limit (page-1)*size,size;
9.9：约束案例总结
一、常见的约束 NOT NULL：非空，该字段的值必填 UNIQUE：唯一，该字段的值不可重复 DEFAULT：默认，该字段的值不用手动插入有默认值 CHECK：检查，mysql不支持 PRIMARY KEY：主键，该字段的值不可重复并且非空 unique+not null FOREIGN KEY：外键，该字段的值引用了另外的表的字段 主键和唯一 1、区别： ①、一个表至多有一个主键，但可以有多个唯一 ②、主键不允许为空，唯一可以为空 2、相同点 都具有唯一性 都支持组合键，但不推荐 外键： 1、用于限制两个表的关系，从表的字段值引用了主表的某字段值 2、外键列和主表的被引用列要求类型一致，意义一样，名称无要求 3、主表的被引用列要求是一个key（一般就是主键） 4、插入数据，先插入主表 删除数据，先删除从表 可以通过以下两种方式来删除主表的记录 #方式一：级联删除 ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE; #方式二：级联置空 ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL; 二、创建表时添加约束 create table 表名( 字段名 字段类型 not null,#非空 字段名 字段类型 primary key,#主键 字段名 字段类型 unique,#唯一 字段名 字段类型 default 值,#默认 constraint 约束名 foreign key(字段名) references 主表（被引用列） ) 注意： 支持类型 可以起约束名 列级约束 除了外键 不可以 表级约束 除了非空和默认 可以，但对主键无效 列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求 三、修改表时添加或删除约束 1、非空 添加非空 alter table 表名 modify column 字段名 字段类型 not null; 删除非空 alter table 表名 modify column 字段名 字段类型 ; 2、默认 添加默认 alter table 表名 modify column 字段名 字段类型 default 值; 删除默认 alter table 表名 modify column 字段名 字段类型 ; 3、主键 添加主键 alter table 表名 add【 constraint 约束名】 primary key(字段名); 删除主键 alter table 表名 drop primary key; 4、唯一 添加唯一 alter table 表名 add【 constraint 约束名】 unique(字段名); 删除唯一 alter table 表名 drop index 索引名; 5、外键 添加外键 alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）; 删除外键 alter table 表名 drop foreign key 约束名; 四、自增长列 特点： 1、不用手动插入值，可以自动提供序列值，默认从1开始，步长为1 auto_increment_increment 如果要更改起始值：手动插入值 如果要更改步长：更改系统变量 set auto_increment_increment=值; 2、一个表至多有一个自增长列 3、自增长列只能支持数值型 4、自增长列必须为一个key 一、创建表时设置自增长列 create table 表( 字段名 字段类型 约束 auto_increment ) 二、修改表时设置自增长列 alter table 表 modify column 字段名 字段类型 约束 auto_increment 三、删除自增长列 alter table 表 modify column 字段名 字段类型 约束
10.事务
-
事务的概念和特性
-
事务的隔离级别
-
事务的案例演示
-
事务：一条或多条sql语句组成一个执行单位，一组sql语句要么都执行要么都不执行
Transaction Control Language 事务控制语言
10.1:事物的概念
事务:事务由单独单元的一个或多个SQL语句组成,在这个单元中,每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态;如果单元中的所有SQL语句均执行成功,则事物被顺利执行。
10.2：MySQL中的存储引擎(了解)
1、概念:在mysq1中的数据用各种不同的技术存储在文件(或内存)中。
2、通过show engines;来查看mysq1支持的存储引擎。
3、在mysql中用的最多的存储引Y有: innodb,myisam , memory 等。其中innodb支持事务,而myisam、 memory等不支持事务。
10.3：事务的特点
事务的ACID(acid)属性
1.原子性(Atomicity)原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
2.一致性(Consistency)事务必须使数据库从一个一致性状态变换到另外一个一致性状态
3.隔离性(Isolation)事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
4.持久性(Durability)持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响
10.4：事务的使用
-
以第一个DML语句的执行作为开始。
-
以下面的其中之一作为结束:
-
COMMIT 或 ROLLBACK 语句
-
DDL 或DCL语句(自动提交)
-
用户会话正常结束
-
系统异常终了
-
-
隐式（自动）事务：没有明显的开启和结束，本身就是一条事务可以自动提交，比如insert、update、delete 显式事务：具有明显的开启和结束
-
了解： 隐式（自动）事务：没有明显的开启和结束，本身就是一条事务可以自动提交，比如insert、update、delete 显式事务：具有明显的开启和结束 使用显式事务： ①开启事务 set autocommit=0; start transaction;#可以省略 ②编写一组逻辑sql语句 注意：sql语句支持的是insert、update、delete 设置回滚点： savepoint 回滚点名; ③结束事务 提交：commit; 回滚：rollback; 回滚到指定的地方：rollback to 回滚点名;
-
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;
10.5：数据库的隔离级别（并发事务）
-
对于同时运行的多个事务,当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种并发问题:
-
脏读:对于两个事务T1, T2, T1读取了已经被T2更新但还没有被提交的字段.之后,若T2回滚,T1读取的内容就是临时且无效的.
-
不可重复读:对于两个事务T1, T2, T1读取了一个字段,然后T2更新了该字段.之后,T1再次读取同一个字段,值就不同了.
-
幻读:对于两个事务T1, T2, T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行.之后,如果T1再次读取同一个表,就会多出几行.
-
-
数据库事务的隔离性:数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响,避免各种并发问题.
-
一个事务与其他事务隔离的程度称为隔离级别.数据库规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但并发性越弱.
-
数据库提供的4种事务隔离级别:
-
隔离级别 描述 READ UNCOMMITTED(读未提交数据) 允许事务读取未被其他事物提交的变更.脏读,不可重复读和幻读的问题都会出现 READ COMMITED 只允许事务读取已经被其它事务提交的变更,可以避免脏读,但不可重复读和幻读问(读已提交数据)题仍然可能出现 REPEATABLE READ 确保事务可以多次从一个字段中读取相同的值,在这个事务持续期间,禁止其他事物(可重复读)对这个字段进行更新,可以避免脏读和不可重复读,但幻读的问题仍然存在。 SERIALIZABLE(串行化) 确保事务可以从一个表中读取相同的行,在这个事务持续期间,禁止其他事务对该表执行插入,更新和删除操作,所有并发问题都可以避免,但性能十分低下,
-
-
Oracle支持的2种事务隔离级别: READ COMMITED,SERIALIZABLE。 Oracle默认的事务隔离级别为: READCOMMITEDMysql支持4种事务隔离级别.
-
Mysql默认的事务隔离级别为: REPEATABLE READ
10.6：MySQL设置隔离级别
-
每启动一个mysql程序,就会获得一个单独的数据库连接.每个数据库连接都有一个全局变量@@tx_isolation,表示当前的事务隔离级别.
-
查看当前的隔离级别: SELECT @@tx_isolation;
-
设置当前mySQL连接的隔离级别:set transaction isolation level read committed;
-
设置数据库系统的全局的隔离级别:set global transaction isolation level read committed;
-
脏读 不可重复读 幻读 read uncommitted:读未提交 × × × read committed：读已提交 √ × × repeatable read：可重复读 √ √ × serializable：串行化 √ √ √
11.视图
-
什么是视图
-
创建或修改视图
-
删除视图
-
查看视图
11.1：什么是视图
视图:MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表,行和列的数据来自定义视图的查询中使用的表并且是在使用视图时动态生成的,只保存了sql逻辑,不保存查询结果。
视图 create view 只是保存了sql逻辑 增删改查，只是一般不能增删改 表 create table 保存了数据 增删改查
应用场景:
多个地方用到同样的查询结果。
该查询结果使用的sql语句较复杂
示例:
CREATE VIEW my_v1 AS SELECT studentname,majorname FROM student s INNER JOIN major m ON s.majorid=m.majorid WHERE s.majorid=1;
11.2：视图的好处
-
重用sql语句
-
简化复杂的sql操作,不必知道它的查询细节
-
保护数据,提高安全性
11.3：创建或修改视图
创建视图的语法:
create [or replace] view view_name As select_statement [with | cascaded | local | check option]
创建视图 /* 语法： create view 视图名 as 查询语句; */ USE myemployees; #1.查询姓名中包含a字符的员工名、部门名和工种信息 #①创建 CREATE VIEW myv1 AS SELECT last_name,department_name,job_title FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN jobs j ON j.job_id = e.job_id; #②使用 SELECT * FROM myv1 WHERE last_name LIKE '%a%'; #2.查询各部门的平均工资级别 #①创建视图查看每个部门的平均工资 CREATE VIEW myv2 AS SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id; #②使用 SELECT myv2.`ag`,g.grade_level FROM myv2 JOIN job_grades g ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`; #3.查询平均工资最低的部门信息 SELECT * FROM myv2 ORDER BY ag LIMIT 1; #4.查询平均工资最低的部门名和工资 CREATE VIEW myv3 AS SELECT * FROM myv2 ORDER BY ag LIMIT 1; SELECT d.*,m.ag FROM myv3 m JOIN departments d ON m.`department_id`=d.`department_id`;
修改视图的语法:
alter view view_name As select_statement [with | cascaded | local | check option]
视图的修改 #方式一： /* create or replace view 视图名 as 查询语句; */ SELECT * FROM myv3 CREATE OR REPLACE VIEW myv3 AS SELECT AVG(salary),job_id FROM employees GROUP BY job_id; #方式二： /* 语法： alter view 视图名 as 查询语句; */ ALTER VIEW myv3 AS SELECT * FROM employees;
视图的可更新性和视图中查询的定义有关系,以下类型的视图是不能更新的。
-
包含以下关键字的sql语句:分组函数、distinct、 group byhaving, union或者union all。
-
常量视图
-
Select中包含子查询
-
join
-
from一个不能更新的视图
-
where子句的子查询引用了from子句中的表
11.4：删除视图
-
删除视图的语法:用户可以一次删除一个或者多个视图,前提是必须有该视图的drop权限。
-
drop view [if exists] view_name,view_name ..[restrict | cascade]
-
/* 语法：drop view 视图名,视图名,...; */ DROP VIEW emp_v1,emp_v2,myv3;
11.5：查看视图
查看视图的语法:
show tables;
如果需要查询某个视图的定义,可以使用
show create view命令进行查看:
show create view view_name \G
DESC myv3; SHOW CREATE VIEW myv3;
11.6：视图的更新
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
FROM employees;
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM 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 = '张无忌';
#具备以下特点的视图不允许更新
#①包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;
SELECT * FROM myv1;
#更新
UPDATE myv1 SET m=9000 WHERE department_id=10;
#②常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
SELECT * FROM myv2;
#更新
UPDATE myv2 SET NAME='lucy';
#③Select中包含子查询
CREATE OR REPLACE VIEW myv3
AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;
#更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资=100000;
#④join
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON 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 myv5
AS
SELECT * FROM myv3;
#更新
SELECT * FROM myv5;
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;
#⑥where子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE 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';
12.存储过程和函数
-
什么是存储过程和函数
-
使用存储过程和函数的好处
-
创建存储过程和函数
-
修改存储过程和函数
-
调用存储过程和函数
-
查看存储过程和函数
【SQL命令】---->【MySQL引擎】-----（分析）----->【语法正确】------>【可识别命令】-----（执行）---------->【执行结果】-------（返回）------>【客户端】
/* 含义：一组预先编译好的SQL语句的集合，理解成批处理语句 1、提高代码的重用性 2、简化操作 3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率 区别： 存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新 函数：有且仅有1 个返回，适合做处理数据后返回一个结果 */
12.1：什么是存储过程和函数
存储过程和函数:事先经过编译并存储在数据库中的一段sql语句的集合。
使用好处:1、简化应用开发人员的很多工作
2、减少编译次数，减少数据在数据库和应用服务器之间的传输
3、提高了数据处理的效率
12.2：创建存储过程和函数
创建存储过程:
create procedure存储过程名([proc_parameter[,....]]) [characteristic…Jroutine_body
创建函数:
create function 函数名([func_parameter[,…]]) returns type [characteristic....]routine_body
-
proc_parameter:
-
[in | out | inout] param_name type
-
-
Func_paramter:
-
param_name type
-
-
Tуpe:
-
任何有效的mysql数据类型
-
-
Characteristic:
-
language sql(默认, 且推荐)
-
| {not] deterministic
-
| {contains sql | no sql | reads sql data | modifies sql data}
-
| sql security {definer | invoker}
-
| comment 'string
-
-
Rountine_body:
-
有效的sql过程语句
-
-
#注意： #一、创建语法 CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型 BEGIN 函数体 END /* 注意： 1.参数列表 包含两部分： 参数名 参数类型 2.函数体：肯定会有return语句，如果没有会报错 如果return语句没有放在函数体的最后也不报错，但不建议 return 值; 3.函数体中仅有一句话，则可以省略begin end 4.使用 delimiter语句设置结束标记 */ /* 1、参数列表包含三部分 参数模式 参数名 参数类型 举例： in stuname varchar(20) 参数模式： in：该参数可以作为输入，也就是该参数需要调用方传入值 out：该参数可以作为输出，也就是该参数可以作为返回值 inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值 2、如果存储过程体仅仅只有一句话，begin end可以省略 存储过程体中的每条sql语句的结尾要求必须加分号。 存储过程的结尾可以使用 delimiter 重新设置 语法： delimiter 结束标记 案例： delimiter $ */案例：函数 #2.有参有返回 #案例1：根据员工名，返回它的工资 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('k_ing') $ #案例2：根据部门名，返回该部门的平均工资 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')$
12.3：调用存储过程或函数
-
调用存储过程:
-
call 存储过程名(参数列表)
-
-
调用函数:
-
Select函数名(参数列表)
-
-
#二、调用语法 SELECT 函数名(参数列表) CALL 存储过程名(实参列表); #--------------------------------案例演示----------------------------------- #1.空参列表 #案例：插入到admin表中五条记录 SELECT * FROM admin; DELIMITER $ CREATE PROCEDURE myp1() BEGIN INSERT INTO admin(username,`password`) VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000'); END $ #调用 CALL myp1()$ #2.创建带in模式参数的存储过程 #案例1：创建存储过程实现 根据女神名，查询对应的男神信息 CREATE PROCEDURE myp2(IN beautyName VARCHAR(20)) BEGIN SELECT bo.* FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name=beautyName; END $ #调用 CALL myp2('柳岩')$ #案例2 ：创建存储过程实现，用户是否登录成功 CREATE PROCEDURE myp4(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 myp3('张飞','8888')$
12.4：修改存储过程或函数
-
修改存储过程:
alter procedure存储过程名[charactristic…]
-
修改函数:
alter functiona数名[charactristic…]
characteristic:
{contains sql | no sql | reads sql data | modifies sql data}
| sql security {definer |invoker}
comment ‘string’
#3.创建out 模式参数的存储过程
#案例1：根据输入的女神名，返回对应的男神名
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyname INTO boyname
FROM boys bo
RIGHT JOIN
beauty b ON b.boyfriend_id = bo.id
WHERE b.name=beautyName ;
END $
#案例2：根据输入的女神名，返回对应的男神名和魅力值
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT)
BEGIN
SELECT boys.boyname ,boys.usercp INTO boyname,usercp
FROM boys
RIGHT JOIN
beauty b ON b.boyfriend_id = boys.id
WHERE b.name=beautyName ;
END $
#调用
CALL myp7('小昭',@name,@cp)$
SELECT @name,@cp$
#4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $
#调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$
12.5：删除存储过程或函数
说明:一次只能删除一个存储过程或者函数,并且要求有该过程或函数的alter routine权限
删除存储过程:
drop procedure [if exists] 存储过程名
删除函数:
drop function [if exists] 函数名
#语法：drop procedure 存储过程名 DROP PROCEDURE p1; DROP PROCEDURE p2,p3;#× #四、查看存储过程的信息 DESC myp2;× SHOW CREATE PROCEDURE myp2;
删除函数 DROP FUNCTION myf3;
12.6：查看存储过程或函数
1查看存储过程或函数的状态:
show {procedure | function} status like存储过程或函数名
2查看存储过程或函数的定义:
show create {procedure function}存储过程或函数名
3.通过查看information_schema.routines了解存储过程和函数的信息(了解)
select * from rountines where rounine_name =存储过程名|函数名
#三、查看函数 SHOW CREATE FUNCTION myf3;
13.流程控制结构
13.0、分支结构
1.if函数
语法：if(条件,值1，值2) 功能：实现双分支 应用在begin end中或外面
#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
IF score>90 THEN SET ch='A';
ELSEIF score>80 THEN SET ch='B';
ELSEIF score>60 THEN SET ch='C';
ELSE SET ch='D';
END IF;
RETURN ch;
END $
SELECT test_if(87)$
#案例2：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500
CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
END IF;
END $
CALL test_if_pro(2100)$
#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
CASE
WHEN score>90 THEN SET ch='A';
WHEN score>80 THEN SET ch='B';
WHEN score>60 THEN SET ch='C';
ELSE SET ch='D';
END CASE;
RETURN ch;
END $
SELECT test_case(56)$
13.1：case
功能：实现多分支
语法： 情况1：类似于switch case 变量或表达式 when 值1 then 语句1; when 值2 then 语句2; ... else 语句n; end
情况2： case when 条件1 then 语句1; when 条件2 then 语句2; ... else 语句n; end
应用在begin end 中或外面
位置： 可以放在任何位置， 如果放在begin end 外面，作为表达式结合着其他语句使用 如果放在begin end 里面，一般作为独立的语句使用
13.1.1：作为表达式
#案例:查询员工的工资的情况, 如果工资>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;
语法 位值
情况一 case 表达式
when 值1 then值1 Begin end中
when 值2 then值2 Begin end外面
....
else值n
end;
情况二 case
when 条件1 then 值1
when 条件2 then 值2
else值n
end;
13.1.2：作为独立的语句
语法 位置
情况一 case Begin end中
表达式when值1 then 语句1;
when 值2 then 语句2;
else 语句n;
end case;
情况二 case
when条件1 then 语1;
when 条件2 then 语句2;
...
else 语句n;
end case;
13.2：循环结构
分类： while、loop、repeat 循环控制： iterate类似于 continue，继续，结束本次循环，继续下一次 leave 类似于 break，跳出，结束当前所在的循环
名称 语法 特点 位置
while Label:while loop_condition 先判断后执行 Begin end中
do
loop_list
End while label;
repeat Label:repeat 先执行后判断
loop_list
Until end_condition
end repeat label;
loop Label:loop 没有条件的死循环
loop_list
End loop label;
#1.while
/*
语法：
【标签:】while 循环条件 do
循环体;
end while【 标签】;
联想：
while(循环条件){
循环体;
}
*/
#2.loop
/*
语法：
【标签:】loop
循环体;
end loop 【标签】;
可以用来模拟简单的死循环
*/
#3.repeat
/*
语法：
【标签：】repeat
循环体;
until 结束循环的条件
end repeat 【标签】;
*/
#1.没有添加循环控制语句
#案例：批量插入，根据次数插入到admin表中多条记录
DROP PROCEDURE pro_while1$
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)$
/*
int i=1;
while(i<=insertcount){
//插入
i++;
}
*/
#2.添加leave语句
#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END $
CALL test_while1(100)$
#3.添加iterate语句
#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
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),'0000');
END WHILE a;
END $
CALL test_while1(100)$
/*
int i=0;
while(i<=insertCount){
i++;
if(i%2==0){
continue;
}
插入
}
*/
13.3：if结构
语法： if 条件1 then 语句1; elseif 条件2 then 语句2; .... else 语句n; end if; 功能：类似于多重if，实现多重分支
只能应用在begin end 中
#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
IF score>90 THEN SET ch='A';
ELSEIF score>80 THEN SET ch='B';
ELSEIF score>60 THEN SET ch='C';
ELSE SET ch='D';
END IF;
RETURN ch;
END $
SELECT test_if(87)$
#案例2：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500
CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
END IF;
END $
CALL test_if_pro(2100)$
#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
CASE
WHEN score>90 THEN SET ch='A';
WHEN score>80 THEN SET ch='B';
WHEN score>60 THEN SET ch='C';
ELSE SET ch='D';
END CASE;
RETURN ch;
END $
SELECT test_case(56)$
14：系统变量
/*
系统变量：
全局变量
会话变量
自定义变量：
用户变量
局部变量
*/
#一、系统变量
/*
说明：变量由系统定义，不是用户定义，属于服务器层面
注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别
使用步骤：
1、查看所有系统变量
show global|【session】variables;
2、查看满足条件的部分系统变量
show global|【session】 variables like '%char%';
3、查看指定的系统变量的值
select @@global|【session】系统变量名;
4、为某个系统变量赋值
方式一：
set global|【session】系统变量名=值;
方式二：
set @@global|【session】系统变量名=值;
*/
#1》全局变量
/*
作用域：针对于所有会话（连接）有效，但不能跨重启
*/
#①查看所有全局变量
SHOW GLOBAL VARIABLES;
#②查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
#③查看指定的系统变量的值
SELECT @@global.autocommit;
#④为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;
#2》会话变量
/*
作用域：针对于当前会话（连接）有效
*/
#①查看所有会话变量
SHOW SESSION VARIABLES;
#②查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
#③查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
#④为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';
#二、自定义变量
/*
说明：变量由用户自定义，而不是系统提供的
使用步骤：
1、声明
2、赋值
3、使用（查看、比较、运算等）
*/
#1》用户变量
/*
作用域：针对于当前会话（连接）有效，作用域同于会话变量
*/
#赋值操作符：=或:=
#①声明并初始化
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
#②赋值（更新变量的值）
#方式一：
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
#方式二：
SELECT 字段 INTO @变量名
FROM 表;
#③使用（查看变量的值）
SELECT @变量名;
#2》局部变量
/*
作用域：仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话
*/
#①声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;
#②赋值（更新变量的值）
#方式一：
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT 局部变量名:=值;
#方式二：
SELECT 字段 INTO 具备变量名
FROM 表;
#③使用（查看变量的值）
SELECT 局部变量名;
#案例：声明两个变量，求和并打印
#用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;
#局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;
#用户变量和局部变量的对比
作用域 定义位置 语法
用户变量 当前会话 会话的任何地方 加@符号，不用指定类型
局部变量 定义它的BEGIN END中 BEGIN END的第一句话 一般不用加@,需要指定类型
扫一扫
1801
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
