1 MySQL 常见命令
-- 小知识点
-- 1. 自增属性必须是主键
-- 2. 建表时指定联合主键
CREATE TABLE employee (
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(id, deptId)
);
1.1 登录指令
mysql [-hhostName -PportNumber] -uuserName -ppassword
-
[]中的选项为可选，如果登录的是本地的mysql，可以省略
-h，如果端口号是3306，可以省略-P。 -
除
-p选项以外，其余选项均可以使用空格将选项名与选项值隔开。基于明文密码的安全问题，建议-p选项后面不要直接跟密码，而是在登陆过程中输入。
1.2 常用SQL语句
支持SQL的数据库均可使用
| 语句 | 操作 |
|---|---|
| SHOW DATABASES; | 列出当前数据库系统下所有的数据库 |
| USE dbName; | 选择数据库 |
| SELECT DATABASE(); | 查看当前所处的数据库 |
| SHOW TABLES; | 列出当前数据库下所有的表 |
| SHOW TABLES FROM anotherDB; | 查看anotherDB下的所有表 |
| DESC tableName; | 查看表tableName的所有字段及其属性 |
| SELECT VERSION(); | 在MySQL中查看当前MySQL的版本 |
| SHOW VARIABLES LIKE ‘%char%’; | 查看当前DBMS支持的字符集 |
1.3 MySQL中执行sql脚本
mysql -h localhost -P 3306 -u root -p123456 <F:\BaiduNetdiskDownload\myemployees.sql
此指令在Windows下，使用PowerShell执行会报错，须使用cmd执行
作为替代，可以使用-e选项，在mysql命令中执行语句:mysql -h localhost -P 3306 -u root -p123456 -e " source F:\\BaiduNetdiskDownload\\myemployees.sql"source F:\\BaiduNetdiskDownload\\myemployees.sql
此指令是在进入mysql后使用
2 基础查操作
SELECT
查询列表
FROM
表名
# 这是单行注释
-- 这也是一个单行注释
/* 但是这是一个多行注释 */
/* 1. 查询列表可以是: 表中的字段、常量值、表达式、函数
* 2. 查询的结果是一个虚拟的表格
* 3. 当需要用到和关键字相同的字段等其他内容时，可以使用 "``"将其包裹起来
* 4. 请不要乱用"``"，只有包裹表中的字段名才可以用"``"
* 5. 使用双引号或者单引号包裹的内容会被当做真正的字符串进行处理
*/
2.1 基础查询语句
SELECT last_name,salary,email FROM employees;
2.1.1 查询所有字段
SELECT * FROM employees;
2.1.2 查询常量值
SELECT 100;
SELECT 'join';
2.1.3 查询表达式
SELECT 100*100;
2.1.4 查询函数
SELECT VERSION();
2.1.5 为字段起别名
SELECT VERSION() AS version;
SELECT `last_name` AS '姓', `first_name` AS '名' FROM employees;
-- 如果查询的字段有重名的情况，起别名可以区分开来
-- 起别名时 AS 可以省略
2.1.6 去重
-- 使用 DISTINCT 关键字去重
SELECT DISTINCT `department_id` FROM `employees`;
2.1.7 + 的作用(不能直接计算NULL)
/* sql 中的 + 号
* SELECT 100+90; 两个操作数都为数值型，则做加法运算
* SELECT '123' + 90; 其中一方为字符型，试图将字符型转换成数值型，如果转换成功，继续做加法运算
* SELECT 'jhon'+90; 如果转换失败则将字符型转换成0，然后继续做加法运算
* SELECT NULL + 90; 只要其中一方为null，则结果肯定是null
*/
2.1.8 字符串拼接(不能直接拼接NULL)
SELECT CONCAT(last_name, '.', first_name) AS `姓名` FROM employees;
/*
* 字符串拼接中有一个参数的值为NULL，整个结果就会是NULL
* 此时可以通过IFNULL函数来避免这个尴尬情况
*/
SELECT
CONCAT (
`first_name`, ",",
`last_name`, ",",
`job_id`, ",",
IFNULL(`commission_pct`, 0)
) AS output
FROM employees;
2.1.9 IFNULL 函数
SELECT
IFNULL(commission_pct, 0) AS 奖金率,
-- 第一个参数是要判断的表达式，第二个参数是当表达式为NULL时，表达式返回的结果
commission_pct
FROM employees;
2.2 条件查询
SELECT DISTINCT
CONCAT(first_name, ".", last_name) "name",
salary
FROM employees
where salary>=10000;
2.2.1 条件运算符的使用
SELECT *
FROM employees
WHERE salary >= 12000;
2.2.2 逻辑表达式(连接多个条件表达式)
支持 AND OR NOT
SELECT
CONCAT(first_name, ".", last_name) AS "name",
salary,
commission_pct
FROM employees
WHERE salary>=1000 AND salary<=12000;
2.2.3 模糊查询
LIKE: 一般和通配符搭配使用
-- LIKE
SELECT *
FROM employees
WHERE last_name LIKE '%a%';
2.2.3.1 通配符
% 任意多个字符，包含0个字符
_ 任意单个字符
-- %
SELECT *
FROM employees
WHERE last_name LIKE '%a%';
-- _
SELECT *
FROM employees
WHERE last_name LIKE '_a_';
2.2.3.2 转义
可以通过 \ 对特殊字符进行转义
可以通过 ESCAPE 关键字指定转义字符
-- \ 转义
SELECT *
FROM employees
WHERE last_name LIKE '%\_a%';
-- ESCAPE
SELECT *
FROM employees
WHERE last_name LIKE '%$_a%' ESCAPE "$";
2.2.4 正则实现模糊匹配
默认不区分大小写，如果要区分大小写，需要使用 BINARY 关键字。
另外，关于正则的相关详情，请自行google。
SELECT first_name FROM employees WHERE first_name REGEXP "^a.{3}$";
-- BINARY
SELECT first_name FROM employees WHERE first_name REGEXP BINARY "^a.{3}$";
2.2.5 范围查询
2.2.5.1 BETWEEN AND
匹配结果包含临界值
-- BETWEEN AND
SELECT *
FROM employees
WHERE employee_id BETWEEN 100 AND 120;
2.2.5.2 IN
用于判断某字段的值是否属于IN列表中的某一项
列表的值必须统一或兼容
列表中不允许使用通配符
-- IN
SELECT last_name, job_id
FROM employees
WHERE job_id IN ('IT_PROT', 'AD_VP', 'AD_PRES');
2.2.5.3 IS NULL 和 IS NOT NULL
<>、=、!=不能用于判断NULL值
-- IS NULL
SELECT last_name, commission_pct
FROM employees
WHERE commission_pct IS NULL;
-- IS NOT NULL
SELECT last_name, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
2.2.6 安全等于 <=>
可以用于判断是否是NULL，也可以判断普通的值
-- <=>
SELECT last_name, commission_pct
FROM employees
WHERE commission_pct <=> NULL;
-- -------------------------
SELECT last_name, salary
FROM employees
WHERE salary <=> 12000;
2.3 排序查询
SELECT 查询列表
FROM 表
WHERE 筛选条件
ORDER BY 排序列表 ASC(升序)|DESC(降序)
-- ORDER BY 子句自办放在查询语句的最后面，limit子句除外
2.3.1 基本使用
-- 查询员工信息，要求工资从高到底排序
SELECT * FROM employees ORDER BY salary DESC;
2.3.2 添加筛选条件
-- 查询部门编号 >= 90 的员工信息，按入职时间的先后进行排序
SELECT * FROM employees WHERE department_id >=90 ORDER BY hiredate ASC;
2.3.3 按表达式排序
-- 按年薪的高低显示员工的信息和年薪(降序排列)
select *, salary*12*(1+IFNULL(commission_pct, 0)) 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct, 0)) DESC;
2.3.3.1 表达式支持别名功能
-- 按年薪的高低显示员工的信息和年薪(降序排列)，使用别名
select *, salary*12*(1+IFNULL(commission_pct, 0)) 年薪
FROM employees
ORDER BY 年薪 DESC;
2.3.4 按函数结果排序
-- 按姓名的长度显示员工的姓名和工资
SELECT LENGTH(last_name) 字节长度, last_name, salary
FROM employees
ORDER BY length(last_name) DESC;
2.3.5 排序条件组合排序
-- 查询员工信息，要求先按工资升序，再按员工编号降序
SELECT *
FROM employees
ORDER BY salary ASC, employee_id DESC;
3 函数
- 函数的调用:
SELECT FUNCTION_NAME() - 如果函数中用到了表中的字段，则需要添加
FROM子句:SELECT FUNCTION_NAME() FROM tableName
3.1 单行函数
3.1.1 字符函数
3.1.1.1 LENGTH 返回字符串字节长度
SELECT LENGTH('xiaoyao');
-- -------------------------------
SELECT DISTINCT LENGTH(last_name) AS name_length
FROM employees
ORDER BY name_length;
3.1.1.2 CONCAT 拼接字符串
SELECT CONCAT(last_name, '_', last_name) FROM employees;
3.1.1.3 UPPER、LOWER 转换成大小写
SELECT UPPER(last_name) FROM employees;
SELECT LOWER(last_name) FROM employees;
-- 将姓大写、名小写，然后拼接
SELECT CONCAT(UPPER(last_name), LOWER(first_name)) AS 姓名 FROM employees;
3.1.1.4 SUBSTR/SUBSTRING 截取子串
此方法存在重载
-- 重载1 返回从索引开始的子串(包含索引处的字符)
-- 此函数的第一个参数为源字符串，第二个参数为需要截取的索引(从1开始，并且每一个字符占一个索引，和该字符的字节大小无关)
SELECT SUBSTRING('2021年我一定要好好学习', 6) output;
-- 重载2 截取从指定索引开始，指定长度的子串
SELECT SUBSTRING('2021年我一定要好好学习', 6, 2) output;
3.1.1.5 INSTR 返回子串第一次出现的索引，如果找不到，返回0
SELECT INSTR('2021年我一定要好好学习', "我") output;
3.1.1.6 TRIM 去掉字符串前后的指定字符串(中间保留)
SELECT TRIM('aa' FROM 'aaaaaaxiaoyaoaaaaa');
3.1.1.7 LPAD 左填充
用指定的字符串将目标字符串左填充至指定长度。
如果源字符串长度超过了指定长度，会截断。
SELECT LPAD('XIAOYAO', 10, '#$');
SELECT LPAD('XIAOYAO', 3, '#$');
3.1.1.8 RPAD 右填充
用指定的字符串将目标字符串右填充至指定长度。
如果源字符串长度超过了指定长度，会截断。
SELECT RPAD('XIAOYAO', 10, '#$');
SELECT RPAD('XIAOYAO', 3, '#$');
3.1.1.9 REPLACE 替换
所有目标子串都会被替换。
SELECT REPLACE("小呀么小儿郎，背着书包上学堂。。。小呀么小儿郎，背着书包上学堂。。。", "小儿", "哒哒哒");
3.1.2 数学函数
3.1.2.1 ROUND 四舍五入
SELECT ROUND(1.55); # 先取绝对值，四舍五入之后再加上正负号
SELECT ROUND(1.55, 2); # 小数点后保留2位
3.1.2.2 CEIL 向上取整
返回 >= 该参数的最小整数
SELECT CEIL(1.55);
3.1.2.3 FLOOR 向下取整
返回 <= 该参数的最大整数
SELECT FLOOR(1.55);
3.1.2.4 TRUNCATE 截断
SELECT TRUNCATE(1.55, 1);
3.1.2.5 MOD 取余
a - a / b * b;
SELECT MOD(10, 3);
3.1.3 日期函数
3.1.3.1 NOW 返回当前系统日期 + 时间
SELECT NOW();
3.1.3.2 CURDATE 返回当前日期
SELECT CURDATE();
3.1.3.3 CURTIME 返回当前时间
SELECT CURTIME();
3.1.3.4 获取日期中指定的部分
更多内容请自行google。
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
SELECT DAY(NOW());
SELECT WEEK(NOW());
SELECT WEEKDAY('2021-1-4 16:46:01');
SELECT DAYNAME('2021-1-4 16:46:01');
SELECT DAYOFWEEK('2021-1-4 16:46:01');
SELECT DATEDIFF(MAX(hiredate), MIN(hiredate)) FROM employees;
3.1.3.4.1 STR_TO_DATE 将字符串转化为日期
-- 相关占位符请自行google
SELECT STR_TO_DATE("1995-08-10", "%Y-%c-%d");
3.1.3.4.2 DATE_FORMAT 将日期转换成字符串
SELECT DATE_FORMAT(STR_TO_DATE("1995-08-10", "%Y-%c-%d"), "%c-%d %Y");
3.1.4 其它函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
3.1.5 流程控制函数
3.1.5.1 IF 自带else效果
SELECT IF(10>5, "TRUE", "FALSE");
3.1.5.2 CASE
-- THEN 的后面使用语句时，需要放在BEGIN END中
CASE 要判断的字段或表达式
WHEN 常量1 THEN "要显示的值1"或"语句1"
WHEN 常量2 THEN "要显示的值2"或"语句3"
WHEN 常量3 THEN "要显示的值3"或"语句3"
ELSE "要显示的值N"或"语句N"
END
-- EXAMPLE
SELECT CASE "KANWOSHENWEI"
WHEN "KANWOSHENWEI" THEN "无坚不摧"
END;
-- CASE 实现多重if的效果
SELECT
CASE
WHEN 3>5 THEN "FALSE"
ELSE USER()
END;
3.2 组函数
SUM AVG MAX MIN COUNT
这5个函数均会忽略NULL值
可以搭配DISTINCT实现过滤
SELECT
SUM(salary) 和,
AVG(salary) 平均,
MAX(salary) 最高,
MIN(salary) 最低,
COUNT(salary) 个数
FROM employees;
SELECT SUM(DISTINCT salary) FROM employees;
SELECT AVG(DISTINCT salary) FROM employees;
SELECT COUNT(DISTINCT salary) FROM employees;
3.2.1 COUNT 的详细介绍
SELECT COUNT(*) FROM employees 只要某一行有不为空的值，则结果 +1，因此常用来统计表格行数。
SELECT COUNT(任意常量值) FROM employees 和上一条效果一样。
效率比较:
MYISAM 存储引擎下，COUNT(*)效率高
INNODB 存储引擎下，COUNT(*)和COUNT(常量)的效率差不多，但是比COUNT(字段)要高一些。
4 分组查询 —— GROUP BY
SELECT 分组函数, 列(要求出现在 GROUP BY 的后面)
FROM 表
WHERE 筛选条件
GROUP BY 分组工具
HAVING 筛选条件
ORDER BY 子句;
4.1 分组前筛选
-- 查询每个工种的最高工资
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
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;
4.2 分组后筛选
-- 查询哪个部门的员工个数 > 10
-- 1. 查询每个部门的员工个数
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id;
-- 2. 根据 1 的结果进行筛选，查询哪个部门的员工个数 > 10
-- 分组后的筛选
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;
-- 查询每个工种有奖金的员工的最高工资 > 12000 的工种编号和最高工资
-- 根据查询需求拆分
-- 1. 查询每个工种有奖金的员工的最高工资
SELECT MAX(salary), job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;
-- 2. 根据上一步的结果继续筛选，最高工资 > 12000
SELECT MAX(salary), job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000;
4.3 多字段分组
-- 查询每个部门每个工种的员工的平均工资
SELECT AVG(salary), department_id, job_id
FROM employees
GROUP BY department_id, job_id;
4.4 分组查询 + 排序
-- 查询每个部门每个工种的员工的平均工资，并按平均工资的高低显示
SELECT AVG(salary), department_id, job_id
FROM employees
GROUP BY job_id, department_id
ORDER BY AVG(salary) DESC;
5 跨表查询
笛卡尔乘积现象: 表1 m 行，表2 n 行， 查询结果 m*m 行
原因: 多表查询时没有有效的连接条件
-- 列出女神对应的男朋友，女神和其男友分别在两张表内
SELECT NAME, boyName FROM boys, beauty
WHERE beauty.boyfriend_id = boys.id;
5.1 为表起别名
为表起了别名后，查询的字段就不能用原来的表名进行限定了。
-- 案例: 查询员工名、工种号、工种名
-- 为表起别名
SELECT e.last_name, e.job_id, j.job_title
FROM employees e, jobs j
WHERE e.`job_id`=j.`job_id`;
5.2 SQL92 语法
5.2.1 等值连接(连接条件为 =)
-- 案例: 查询员工名对应的部门名
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id=departments.department_id;
5.2.1.1 等值连接 + 筛选
-- 查询有奖金的员工名部门名
SELECT last_name, department_name
FROM employees e, departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;
-- 查询城市名中第二个字符为 'o' 的部门名和城市名
SELECT department_name, city
FROM departments d, locations l
WHERE d.`location_id`=l.`location_id`
AND city LIKE '_o%';
5.2.1.2 等值连接 + 分组
-- 查询每个城市的部门个数
SELECT COUNT(*) 个数, city
FROM departments d, locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;
5.2.2 非等值连接
SELECT salary, grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
ORDER BY salary ASC;
5.2.3 自连接
同一张表查找多遍，即为自连接
-- 查询员工名和上级的名称
SELECT e.`last_name`, m.`last_name`
-- 因为同一张表需要使用多次，因此需要取两个别名
FROM employees e, employees m
WHERE e.`manager_id`=m.`employee_id`;
5.3 SQL99语法
5.3.1 内连接
INNER JOIN (INNER 可以省略)
-- 语法
SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件
WHERE 筛选条件
GROUP BY 分组条件
HAVING 分组后筛选条件
5.3.1.1 内连接 + 等值连接
-- 查询员工名、部门名
SELECT last_name, department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;
5.3.1.1.1 内连接 + 等值连接 + 筛选
-- 查询名字中包含 '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%";
5.3.1.1.2 内连接 + 等值连接 + 分组 + 筛选
-- 查询部门个数 >3 的城市名和部门个数
SELECT city, COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*) > 3;
5.3.1.1.3 内连接 + 三表连接
三表连接对于表的顺序有一定的要求，JOIN的表一定要跟前面的表有一定的关联。
-- 查询员工名、部门名、工种名，并按照部门名降序
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;
5.3.1.1.4 内连接 + 非等值连接
-- 查询员工数 >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;
5.3.1.1.5 内连接 + 自连接
-- 查询姓名中包含字符 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%";
5.3.2 外连接
应用场景: 用于查询一个表中有，而另一个表中没有的记录
特点:
- 外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配到的值
如果从表中没有和它匹配的，则显示NULL
外连接的查询结果 = 内连接的结果 + 主表中有而从表中没有的记录 - 左外连接 LEFT JOIN 左边的是主表
右外连接 RIGHT JOIN 右边的是主表 - 左外和右外交换两个表的顺序，可以实现相同的效果
5.3.2.1 左外连接
LEFT OUTER / OUTER可以省略
-- 查询男朋友不在男神表的女神名(使用左外连接)
SELECT b.name, bo.*
FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;
5.3.2.2 右外连接
RIGHT OUTER / OUTER 可以省略
-- 查询男朋友不在男神表的女神名(使用右外连接)
SELECT b.name, bo.*
FROM boys bo
RIGHT OUTER JOIN beauty b ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;
5.3.2.3 全外连接(MySQL暂不支持)
FULL OUTER / OUTER 可以省略
5.3.3 交叉连接
CROSS JOIN
交叉连接就是实现笛卡尔乘积，不过是使用SQL99语法实现。
SELECT b.*, bo.*
FROM beauty b
CROSS JOIN boys bo;
6 子查询
出现在其它语句中的SELECT语句，称为子查询或者内查询。
外部的查询语句称为外查询或主查询。
分类:
- 子查询出现的位置:
SELECT后面(仅仅支持标量子查询)
FROM后面(支持表子查询)
WHERE或HAVING后面(支持标量子查询、列子查询、表子查询)
EXISTS后面(表子查询) - 按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(一般为多行多列)
6.1 WHERE 或 HAVING 后面
- 标量子查询
- 列子查询
- 行子查询
- 子查询一般放在小括号内
- 子查询一般放在条件的右侧
- 标量子查询，一般搭配着单行操作符使用(> < >= <= = <>)
列子查询一般搭配着多行操作符使用(IN ANY/SOME ALL)
6.1.1 标量子查询
-- 谁的工资比Abel高？
-- 1. 查询Abel的工资
SELECT salary
FROM employees
WHERE last_name='Abel';
-- 2. 查询员工的信息，满足 salary > setp1 的结果
SELECT *
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
-- 案例2: 返回 job_id 与141号员工相同，salary比143号员工多的员工姓名， job_id 和工资
SELECT last_name, job_id, salary 工资
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE employee_id = 143
) AND salary > (
SELECT salary
FROM employees
WHERE employee_id = 143
);
-- 案例3: 返回公司工资最少的员工的last_name，job_id和salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);
-- 案例4: 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
6.1.2 列子查询
列子查询需要搭配多行操作符使用
IN/ NOT IN/ ANY|SOME/ ALL
-- 案例1: 返回location_id是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
SELECT employee_id, CONCAT(first_name, ' ', last_name), job_id, salary
FROM employees
WHERE salary < ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id != 'IT_PROG';
6.1.3 行子查询
结果集一行多列或着多行多列
-- 案例: 查询员工编号最小并且工资最高的员工信息
-- 第一种实现
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
);
6.2 SELECT 后面
-- 案例1: 查询每个部门的员工个数
SELECT d.department_name, (
SELECT COUNT(*)
FROM employees e
WHERE e.`department_id` = d.`department_id`
) 员工个数
FROM departments d;
-- 案例2: 查询员工编号=102的部门名
SELECT (
SELECT department_name
FROM departments d
INNER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` = 102
);
6.3 FROM 后面
-- 案例1: 查询每个部门的平均工资的工资等级
SELECT av_dep.*, g.grade_level
FROM (
SELECT AVG(salary) ag, department_id
FROM employees
GROUP BY department_id
) av_dep
INNER JOIN job_grades g ON av_dep.ag BETWEEN g.lowest_sal AND g.highest_sal;
6.4 EXISTS 后面
又称相关子查询。
EXISTS 返回结果是个布尔值(1或者0)，表示关键字后面跟的是否由内容。
本例中表示SELECT子查询是否有结果。
SELECT EXISTS (
SELECT *
FROM employees
WHERE salary=1000000
);
-- 案例: 查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);
7 分页查询
SELECT 查询列表
FROM 表
JOIN_TYPE JOIN 表2 ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后的筛选
ORDER BY 排序后的字段
LIMIT offset, size;
-- offset: 要显示的条目的起始索引(起始索引从0开始)
-- size: 要显示的条目个数
-- 案例: 查询前5条员工信息
SELECT * FROM employees LIMIT 0, 5;
SELECT * FROM employees LIMIT 5;
-- 查询地11条到第25条
SELECT * FROM employees LIMIT 10, 15;
8 联合查询
将多条查询语句的结果合并成一个结果
必须使用union的场景: 当要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。
需要注意的事项:
- 多条查询语句的查询列数是一致的
- 多条查询语句的查询结果的每一列的类型和顺序最好一致
- UNION关键字默认去重，如果想不去重，可以使用UNION ALL
-- 案例: 查询部门编号 >90 或邮箱中包含a的员工信息
SELECT * FROM employees WHERE email LIKE "%a%"
UNION
select * FROM employees WHERE department_id > 90;
9 插入操作
9.1 经典语法
INSERT INTO tableName (columnName1, columnName2...)
-- value列表类型和columnName列表类型顺序一致或兼容
VALUES (value1, value2, ...);
INSERT INTO beauty(id, NAME, sex, borndate, phone, photo, boyfriend_id)
VALUES (13, '唐艺昕', '女', '1990-5-23', 18988888888, NULL, 2);
-- 2. 不可以为NULL的列必须插入值，可以为NULL的值可以不写，或者以NULL填充，但是列列表和值列表的数量和类型顺序必须一致或兼容
INSERT INTO beauty(id, NAME, sex, borndate, phone, photo, boyfriend_id)
VALUES (13, '唐艺昕', '女', '1990-5-23', 18988888888, NULL, 2);
INSERT INTO beauty(id, NAME, sex, phone)
VALUES(15, '迪丽热巴', '女', 11111111111);
-- 可以省略列列表，此时默认所有列，而且列的顺序和表中列的顺序一致
9.2 插入操作方式二
INSERT INTO tableName
SET column1 = value1, column2 = value2...;
--
INSERT INTO beauty
SET id=19, NAME='刘涛', phone=999;
9.3 两种方式对比
- 经典方式支持一次插入多行，方式二不支持
INSERT INTO beauty(id, NAME, sex, borndate, phone, photo, boyfriend_id) VALUES (23, '唐艺昕1', '女', '1990-5-23', 18988888888, NULL, 2) ,(24, '唐艺昕2', '女', '1990-5-23', 18988888888, NULL, 2) ,(25, '唐艺昕3', '女', '1990-5-23', 18988888888, NULL, 2); - 经典方式支持子查询，方式二不支持
INSERT INTO beauty(id, NAME, phone) SELECT 26, '朱茜', 110;
9.4 SELECT + UNION 实现一次插入多行
基于经典方式支持子查询的特性，可以使用 SELECT + UNION 实现一次插入多行
INSERT INTO beauty(id, NAME, sex, borndate, phone, photo, boyfriend_id)
SELECT 23, '唐艺昕1', '女', '1990-5-23', 18988888888, NULL, 2 UNION
SELECT 24, '唐艺昕2', '女', '1990-5-23', 18988888888, NULL, 2 UNION
SELECT 25, '唐艺昕3', '女', '1990-5-23', 18988888888, NULL, 2;
10 改操作
10.1 修改单表记录
UPDATE tableName
SET column1=value1, column2=value2, ...
WHERE filterCondition;
-- 案例: 修改beauty表中姓 '唐' 的女神的电话未13888888888;
UPDATE beauty
SET phone=13888888888
WHERE NAME LIKE '唐%';
-- 案例: 修改boys表中id为2的名称为 '张飞'，魅力值为 25;
UPDATE boys
SET boyname='张飞', usercp=25
WHERE id=2;
10.2 修改多表记录
- SQL92 语法
UPDATE table1 alias1, table2 alias2 ... SET column1=value1, column2=value2, ... WHERE connect_condition1 AND connect connect_condition2 AND ... AND filter_condition1 AND filter_condition2 AND ...; - SQL99 语法
UPDATE table1 alias1 INNER|LEFT|RIGHT JOIN table2 alias2 ON connect_condition1 INNER|LEFT|RIGHT JOIN table3 alias3 ON connect_condition2 ... SET column1=value1, column2=value2, ... WHERE filter_condition1 AND filter_condition2 AND ...;
-- 案例: 修改张无忌的女朋友手机号为114
UPDATE boys bo
INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`phone`=114
WHERE bo.`boyName`='张无忌';
11 删除操作
11.1 方式1 DELETE
11.1.1 单表删除
-- 删除 手机号以9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';
11.1.2 多表删除
-- SQL92
DELETE alias1, alias2... -- 此处填写的是想要删除的记录所属的表的别名
FROM table1 alias1, table2 alias2, ...
WHERE connect_conditions
AND filter_conditions;
-- SQL99
DELETE alias1, alias2... -- 此处填写的是想要删除的记录所属的表的别名
FROM table1 alias1
INNER|LEFT|RIGHT JOIN table2 alias2 ON connect_condition1
INNER|LEFT|RIGHT JOIN table3 alias3 ON connect_condition3
WHERE filter_conditions;
-- 删除张无忌的女朋友的信息
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`='黄晓明';
11.2 方式2 TRUNCATE TABLE tableName
TRUNCATE 删除会将表清空
11.3 TRUNCATE 和 DELETE 对比
- TRUNCATE是清空表，因此不能添加WHERE子句(没有必要)，基于此，其效率也比DELETE要高。
- 如果用DELETE删除后，再插入数据，自增长列的值从断点开始，而使用TRUNCATE删除后，在插入数据，自增长列的值从1开始。
- DELETE 删除有返回值，TRUNCATE 删除没有返回值。
- TRUNCATE 删除不能回滚，DELETE 可以回滚。
12 库表管理
12.1 创建库
CREATE DATABASE IF NOT EXISTS dbName;
12.2 修改库名
因为安全问题，已废弃
12.3 修改库的字符集
ALTER DATABASE dbName CHARACTER SET newCSName;
12.4 删除库
DROP DATABASE IF EXISTS dbName;
12.5 创建表
CREATE TABLE tableName (
column1 columnType1 (column1Length) column1Limit,
column2 columnType2 (column2Length) column2Limit,
...
);
-- 案例: 创建一张book表
CREATE TABLE book (
id INT,
book_name VARCHAR(20),
price DOUBLE,
author_id INT,
publish_date DATETIME,
isbn VARCHAR(20)
);
-- 案例: 创建作者表
CREATE TABLE author(
id INT,
name VARCHAR(20),
nation VARCHAR(10)
);
12.6 表的修改
12.6.1 修改列名
最后的数据类型必须加上，因此修改列名的同时也可以修改列的类型，另外 COLUMN 可以省略
ALTER TABLE tableName CHANGE COLUMN oldColumn newColumn newDataType;
-- 将book表的 'book_name' 列改为 'name'
ALTER TABLE book CHANGE COLUMN book_name name VARCHAR(20);
12.6.2 修改列的类型或约束
-- 修改列的类型/约束
ALTER TABLE tableName MODIFY COLUMN columnName newType newLimit;
-- 修改 book 表的 isbn 列的类型为 INT，并添加唯一约束
ALTER TABLE book MODIFY COLUMN isbn INT UNIQUE;
修改约束请参考后续章节 修改表时添加约束
12.6.3 添加新列
ALTER TABLE tableName ADD COLUMN columnName dataType;
-- 为作者表添加年薪列
ALTER TABLE author ADD COLUMN annual DOUBLE;
12.6.4 删除列
-- 不能添加 IF EXISTS
ALTER TABLE tableName DROP COLUMN columnName;
-- 删除 author 表的 annual 列
ALTER TABLE author DROP COLUMN annual;
12.6.5 修改表名
ALTER TABLE tableName RENAME TO newName;
-- 修改 book 表的表名为 books
ALTER TABLE book RENAME TO books;
12.6.6 删除表
DROP TABLE tableName；
-- 创建一个名称为test的表，然后删除它
CREATE TABLE test(num INT);
DROP TABLE IF EXISTS test;
12.6.7 表的复制
12.6.7.1 仅复制表的结果
INSERT INTO authors VALUES(1, '村上春树', '日本'),
(2, '莫言', '中国'),
(3, '冯唐', '中国'),
(4, '金庸', '中国');
CREATE TABLE authors_copy LIKE authors;
12.6.7.2 复制表的结构 + 数据
CREATE TABLE tar_table
SELECT * FROM src_table
WHERE filter_conditions; -- 通过WHERE可以复制部分数据
12.6.7.3 仅复制表的部分结构
CREATE TABLE tar_table
SELECT column1, column2, ...
FROM src_table
WHERE 0; -- 此处也可以写一个所有数据均不满足的条件
12.6.7.4 复制表的部分结构 + 数据
代码参考 12.6.7.3
13 数据类型
13.1 整型
下列5种以及它们的无符号类型(UNSIGNED)
它们的范围可以自行计算
此处仅以 INT 和 INT UNSIGNED 举例，其它类型都类似。
| 数据类型 | 大小(byte) |
|---|---|
| TINYINT | 1 |
| SMALLINT | 2 |
| MEDIUMINT | 3 |
| INT/INTEGER | 4 |
| BIGINT | 8 |
13.1.1 定义
-- 创建一张表，两列数据，分别为 INT 和 INT UNSIGNED
CREATE TABLE int_test (
t INT,
tu INT UNSIGNED
);
13.1.2 整型数据插入
INSERT INTO int_test VALUES(
-1, 129
);
13.1.3 注意事项
- 如果定义数据类型时不设置有无符号，默认是有符号的，如果想设置有符号的，需要添加 UNSIGNED 关键字，请参阅
13.1.1 - 如果插入的数值超过了概述率类型的范围，会报
OUT OF RANGE的错误，并且插入失败(MySQL 8.x) - 如果不设置长度，会有默认的长度
这里的长度不是存储时的数据长度而是显示时显示的长度
如果设置了长度，只有搭配了ZEROFILL关键字，才会在显示时，如果显示的长度不够设置的长度，会在前面使用0填充。CREATE TABLE int_test1 ( t INT(10) ZEROFILL, tu INT(10) UNSIGNED ZEROFILL ); INSERT INTO int_test1 VALUES( 1, 11 ); SELECT * FROM int_test1; /* mysql> SELECT * FROM int_test1; +------------+------------+ | t | tu | +------------+------------+ | 0000000001 | 0000000011 | +------------+------------+ 1 row in set (0.00 sec) */ - 使用了
ZEROFILL关键字修饰的列，其数据类型为无符号，因此不需要添加UNSIGNED-- 上一条的验证代码中创建的表结构，查看该表的数据结构发现 t 和 tu 的类型都是 INT UNSIGNED 类型 /* mysql> DESC int_test1; +-------+---------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------------+------+-----+---------+-------+ | t | int(10) unsigned zerofill | YES | | NULL | | | tu | int(10) unsigned zerofill | YES | | NULL | | +-------+---------------------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) */
13.2 小数型
分为浮点型和定点数型， FLOAT DOUBLE DEC(M, D)/DECIMAL(M, D)
M 和 D 的含义:
M: 整数位数 + 小数位数
D: 小数位数
插入的数据符合D的规定，但是超出了M的规定，会导致 OUT OF RANGE 错误，并插入失败
| 数据类型 | 大小(byte) |
|---|---|
| FLOAT | 4 |
| DOUBLE | 8 |
| DEC(M, D) / DECIMAL(M, D) | M + 2 |
13.2.1 MD 和临界值的验证
- MD可以省略，如果省略将会有一个默认值
对于DECIMAL，默认的M是10，默认的D是0
对于FLOAT和DOUBLE，则会根据插入的数值的精度来决定精度 - 符合D但是不符合M的数据会报
OUT OF RANGE错误
并且插入失败(MySQL8.x)或者插入临界值(MySQL8以前的版本) - 位数少于D的数据会被以0填充
- 位数多余D的数据，会有根据多余的位数和数据类型有不同的行为
直接舍弃或者四舍五入(MySQL8.x)，8以前的版本尚未验证
但是这种行为不值得提倡，应该在插入数据前自己根据需要和数据类型先自行处理一下再插入
CREATE TABLE fdd_test(
f FLOAT(5, 2),
do DOUBLE(5, 2),
de DECIMAL(5, 2)
);
/*
mysql> desc FDD_TEST;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| f | float(5,2) | YES | | NULL | |
| do | double(5,2) | YES | | NULL | |
| de | decimal(5,2) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> INSERT INTO fdd_test VALUES
-> (5.22, 5.22, 5.22),
-> (5.222, 5.222, 5.222),
-> (5.225, 5.225, 5.225),
-> (5.2, 5.2, 5.2),
-> (1234.22, 1234.22, 1234.22);
ERROR 1264 (22003): Out of range value for column 'f' at row 5
mysql> SELECT * FROM fdd_test;
Empty set (0.00 sec)
*/
-- 上面的结果验证了关于 D 的验证，总位数超过 D ，会报 OUT OF RANGE 错误并
INSERT INTO fdd_test VALUES
(5.22, 5.22, 5.22),
(5.222, 5.222, 5.222),
(5.225, 5.225, 5.225),
(5.2, 5.2, 5.2);
/*
mysql> select * from FDD_TEST;
+------+------+------+
| f | do | de |
+------+------+------+
| 5.22 | 5.22 | 5.22 |
| 5.22 | 5.22 | 5.22 |
| 5.22 | 5.22 | 5.23 |
| 5.20 | 5.20 | 5.20 |
+------+------+------+
4 rows in set (0.00 sec)
mysql> INSERT INTO fdd_test VALUES(5.227, 5.227, 5.227);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from FDD_TEST;
+------+------+------+
| f | do | de |
+------+------+------+
| 5.22 | 5.22 | 5.22 |
| 5.22 | 5.22 | 5.22 |
| 5.22 | 5.22 | 5.23 |
| 5.20 | 5.20 | 5.20 |
| 5.23 | 5.23 | 5.23 |
+------+------+------+
5 rows in set (0.00 sec)
mysql> INSERT INTO fdd_test VALUES(5.2254, 5.2254, 5.2254);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> INSERT INTO fdd_test VALUES(5.2255, 5.2255, 5.2255);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from FDD_TEST;
+------+------+------+
| f | do | de |
+------+------+------+
| 5.22 | 5.22 | 5.22 |
| 5.22 | 5.22 | 5.22 |
| 5.22 | 5.22 | 5.23 |
| 5.20 | 5.20 | 5.20 |
| 5.23 | 5.23 | 5.23 |
| 5.23 | 5.23 | 5.23 |
| 5.23 | 5.23 | 5.23 |
+------+------+------+
7 rows in set (0.00 sec)
mysql> INSERT INTO fdd_test VALUES(5.2253, 5.2253, 5.2253);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from FDD_TEST;
+------+------+------+
| f | do | de |
+------+------+------+
| 5.22 | 5.22 | 5.22 |
| 5.22 | 5.22 | 5.22 |
| 5.22 | 5.22 | 5.23 |
| 5.20 | 5.20 | 5.20 |
| 5.23 | 5.23 | 5.23 |
| 5.23 | 5.23 | 5.23 |
| 5.23 | 5.23 | 5.23 |
| 5.23 | 5.23 | 5.23 |
+------+------+------+
8 rows in set (0.00 sec)
*/
-- 多次试验结果证明:
-- 在 MySQL8.x 的版本上，D的位数不够会以 0 填充
-- 但是位数超过了的，行为有点复杂，和超过的位数有关系，需要自行验证
-- 因此，不建议直接插入不符合数据类型要求的值，建议先根据数据类型和自己的需求在插入数据前先自行处理再插入
13.3 字符型
CHAR 和 VCHAR(V是variable的意思，表示长度根据实际长度来决定)
| 字符串类型 | 最多字符数 | 描述及存储需求 |
|---|---|---|
| CHAR(M) | M | M为0~255之间的整数 |
| VARCHAR(M) | M | M为0~65535之间的整数 |
- CHAR(M) 表示最多存储M个字符
M可以省略，默认为1
即使存储的字符数少于M，开辟的存储空间依然是M个字符的空间
由于长度固定，其效率比VCHAR高 - VCHAR(M) 表示最多存储M个字符
M不可以省略
用多少开辟多少空间，根据内容长度确定
13.4 枚举型
可以将列的数据类型定义为枚举类型，要求插入的值必须是列表中指定的值之一
如果列表成员为1~255，则需要1个字节存储
如果列表成员为255~65535，则需要两个字节存储
最多需要65535个成员
插入时不区分大小写
CREATE TABLE enum_test(
e ENUM('A', 'B', 'C')
);
/*
mysql> DESC enum_test;
+-------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| e | enum('A','B','C') | YES | | NULL | |
+-------+-------------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> INSERT INTO enum_test VALUES('A'),('B'),('C'),('D');
ERROR 1265 (01000): Data truncated for column 'e' at row 4
mysql> SELECT * FROM enum_test;
Empty set (0.00 sec)
mysql> INSERT INTO enum_test VALUES('A'),('B'),('C');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM enum_test;
+------+
| e |
+------+
| A |
| B |
| C |
+------+
3 rows in set (0.00 sec)
*/
-- 上面的结果说明，不在枚举中的值不允许被插入(MySQL8.x)，8以前的版本是警告，会形成一个空值。
13.5 SET类型
和ENUM类型类似，里面可以保存0~64个成员，和ENUM最大的区别是SET类型一次可以选区多个成员，而ENUM只能选一个，根据成员数的不同，存储所占的字节也不同
- 注意，插入数据是可以忽略大小写但是不能有空格
| 成员数 | 字节数 |
|---|---|
| 1~8 | 1 |
| 9~16 | 2 |
| 17~24 | 3 |
| 25~32 | 4 |
| 33~64 | 8 |
CREATE TABLE set_test(
s SET('a','b','c','d')
);
INSERT INTO set_test VALUES ('A');
INSERT INTO set_test VALUES ('a,b');
/*
mysql> INSERT INTO set_test VALUES
-> ('A, b, c');
ERROR 1265 (01000): Data truncated for column 's' at row 1
mysql> SELECT * FROM set_test;
+------+
| s |
+------+
| a |
| a,b |
+------+
2 rows in set (0.00 sec)
mysql> INSERT INTO set_test VALUES
-> ('A,b,c');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM set_test;
+-------+
| s |
+-------+
| a |
| a,b |
| a,b,c |
+-------+
3 rows in set (0.00 sec)
*/
-- 插入时可以忽略大小写但是不能有空格
13.6 日期和时间类型
| 类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| DATE | 4 | 1000-01-01 | 9999-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 |
DATE只保存日期
TIME只保存时间
YEAR只保存年
DATETIME保存日期+时间
TIMESTAMP保存日期+时间
| 类型 | 字节 | 范围 | 时区等的影响 |
|---|---|---|---|
| DATETIME | 8 | 1000-9999 | 不受 |
| TIMESTAMP | 4 | 1970-2038 | 受 |
14 常见约束
约束是一种限制，为了保证表中的数据的准确和可靠性
添加约束的时机: 创建表时、修改表时
约束的分类
- 列级约束: 六大约束语法上都支持，但是外键约束没有效果
- 表级约束: 除了非空、默认，其它的都支持
CREATE TABLE tableName(
column1(length) column1Type column1Limit,
column2(length) column2Type column2Limit,
column3(length) column3Type column3Limit,
...
);
六大约束
- NOT NULL: 非空，用于保证该字段的值不能为空
- DEFAULT: 默认值，用于保证该字段有默认值
- PRIMARY KEY: 主键，保证该字段的值唯一且非空
一张表中只能有一个主键
允许用多个字段组合成一个主键，此时允许部分重复，但是组成主键的多个字段全部重复就会报错，但是不推荐组合主键 - UNIQUE: 用于保证该字段的值唯一，一张表中允许存在多个
允许为空，但是至多只能有一个NULL
唯一键也能由多个字段组合，同样允许部分重复，但是不推荐组合唯一键。 - CHECK: 检查约束 [MySQL中不支持]
- FOREIGN KEY: 外键约束
用于限制两个表的关系，保证该字段的值必须来自于主表的关联列的值
主表中的被引用列必须是一个key(必须唯一不重复)
插入数据时，先插入主表，再插入从表
删除时先删除从表数据，再删除主表数据
14.1 添加列级约束
CREATE DATABASE students;
USE students;
CREATE TABLE stuinfos(
id INT PRIMARY KEY, -- 主键约束
name VARCHAR(20) NOT NULL, -- 非空约束
gender CHAR(1) CHECK (gender='男' OR gender='女'), -- 检查约束
seat INT UNIQUE, -- 唯一约束
age INT DEFAULT 18, -- 默认约束
major_id INT REFERENCES majors(id) -- 外键约束
);
CREATE TABLE majors(
id INT PRIMARY KEY,
major_name VARCHAR(20)
);
14.2 添加表级约束
DROP TABLE stuinfos;
CREATE TABLE stuinfos(
id INT,
name VARCHAR(20) NOT NULL,
gender CHAR(1),
seat INT,
age INT DEFAULT 18,
major_id INT,
-- 开始表级约束
CONSTRAINT pk /*这里的名字随便起，并且这一部分可以省略，，它会有一个默认名*/ PRIMARY KEY(id), -- 给id添加主键约束
UNIQUE(seat), -- 唯一键
CONSTRAINT ck CHECK(gender = '男' OR gender='女'), -- 检查
CONSTRAINT fk_stuinfos_major FOREIGN KEY(major_id) REFERENCES majors(id) -- 外键
);
14.3 修改表时添加约束
-- 添加非空约束
ALTER TABLE stuinfos MODIFY COLUMN name VARCHAR(20) NOT NULL;
-- 添加默认约束
ALTER TABLE stuinfos MODIFY COLUMN age INT DEFAULT 19;
-- 添加主键约束
ALTER TABLE stuinfos MODIFY COLUMN id INT PRIMARY KEY; -- 此语句语法没有问题，但是会因为已经存在主键而失败
ALTER TABLE stuinfos ADD PRIMARY KEY(id);
-- 添加唯一键约束
ALTER TABLE stuinfos MODIFY COLUMN seat INT UNIQUE;
ALTER TABLE stuinfos ADD CONSTRAINT uk UNIQUE(seat);
-- 添加外键约束
ALTER TABLE stuinfos ADD FOREIGN KEY(major_id) REFERENCES major(id);
14.4 修改表时删除约束
-- 删除非空约束
ALTER TABLE stuinfos MODIFY COLUMN name VARCHAR(20) NULL; -- NULL可以省略
-- 删除默认约束
ALTER TABLE stuinfos MODIFY COLUMN age INT; -- 不写即可
-- 删除主键约束
ALTER TABLE stuinfos DROP PRIMARY KEY;
-- 删除唯一键约束
ALTER TABLE stuinfo DROP INDEX uniqueColumn; -- uniqueColumn 可以通过 SHOW INDEX FROM tableName查到
-- 删除外键约束
ALTER TABLE stuinfos DROP FOREIGN KEY fk_id; -- kd_id 由添加外键时定义的名称
15 标识列(自增长列)
创建表时设置标识列
CREATE TABLE tab_identity(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
INSERT INTO tab_identity VALUES(5, 'jhon');
INSERT INTO tab_identity VALUES(NULL, 'jhon');
INSERT INTO tab_identity(name) VALUES('jhon');
15.1 关于自增长的起始值和步长
SHOW VARIABLES LIKE "%auto_increment%";
/*
auto_increment_increment -- 步长，可以设置
auto_increment_offset -- 起始值，不支持设置，但是可以通过在插入第一条数据时指定自增长列的数据即可达到一样的效果
mysql> TRUNCATE TABLE tab_identity;
Query OK, 0 rows affected (0.04 sec)
mysql> SET auto_increment_increment=3;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM tab_identity;
Empty set (0.01 sec)
mysql> INSERT INTO tab_identity VALUES(5, 'jhon');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tab_identity(name) VALUES('jhon');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tab_identity(name) VALUES('jhon');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM tab_identity;
+----+------+
| id | name |
+----+------+
| 5 | jhon |
| 7 | jhon |
| 10 | jhon |
+----+------+
3 rows in set (0.00 sec)
*/
15.2 注意事项
- 标识列必须是一个key
- 一个表最多只能有一个标识列
- 标识列的类型只能是数值型
- 标识列可以设置步长，也可以通过手动插入值来设置起始值
15.3 修改列时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
15.4 修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;
16 事务控制
事务: 一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
不是所有的存储引擎(关于存储引擎的概念，请自行google)都支持事务，在MySQL中使用最多的存储引擎有:INNODB/MYISAM/MENORY等，其中INNODB是支持事务的，是MySQL8.x的默认引擎。
事务的特性: 原子性，一致性，隔离性，持久性
16.1 事物的创建
隐式事务: 事务没有明显的开启和结束的标记
比如 INSERT、UPDATE、DELETE
显示事务
- 必须先设置自动提交功能为禁用
SET AUTOCOMMIT=0;，只对当前会话有效 START TRANSACTION;- 编写事务中的sql语句
- 结束事务:
COMMIT;提交事务或者ROLLBACK;回滚事务
16.2 隔离性
对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题。
MySQL提供了解决方案，就是设置事务隔离级别。
关于事务隔离级别，请自行google。
16.3 保存点
SAVEPOINT point;
ROLLBACK TO point;
17 视图
虚拟表，和普通表一样使用MySQL5.1出现的新特性，是通过表动态生成的数据
行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。
使用场景: 多个地方用到同样的查询结果并且该查询结果使用的sql语句较复杂
17.1 创建和使用
CREATE VIEW v1 AS
SELECT name, major_name
FROM stuinfos s
INNER JOIN majors m ON s.`major_id` = m.`id`;
SELECT * FROM v1 WHERE name LIKE "%J%";
17.2 视图的修改
方式1:
-- 如果存在则修改，否则创建
CREATE OR REPLACE viewName AS
sqls;
方式2:
ALTER VIEW viewName AS
sqls;
17.3 删除视图
DROP VIEW view1, view2, ...;
17.4 查看视图结构
DESC viewName;
SHOW CREATE VIEW viewName;
17.5 视图的更新
默认视图的更新会影响原始表。
但是以下类型的试图不能被更新:
- 分组函数、DISTINCT、GROUP BY、HAVING、UNION 或者 UNION ALL
- 常量视图
- SELECT中包含子查询
- JOIN
- FROM 一个不能更新的视图
- WHERE子句的子查询引用了FROM子句中的表
-- 插入
INSERT INTO viewName VALUES(valuesList);
-- 修改
UPDATE viewName SET col1=val1, col2=val2, ... WHERE filter_conditions;
-- 删除
DELETE FROM viewName WHERE filter_conditions;
18 变量
系统变量: 全局变量、会话变量
自定义变量: 用户变量、局部变量
18.1 系统变量
-- 使用语法
-- 查看所有的系统变量:
SHOW GLOBAL|SESSION VARIABLES; -- GLOBAL和SESSION可以不写，默认是查看SESSION
-- 查看满足条件的部分系统变量
SHOW GLOBAL|SESSION VARIABLES LIKE 'partenString';
-- 查看指定的系统变量
SELECT @@GLOBAL|SESSION.varName;
-- 为某个系统变量赋值
SET GLOBAL|SESSION var=val;
SET @@GLOBAL|SESSION.var=val;
18.2 用户变量
18.2.1 声明并初始化
SET @varName=value;
SET @varName:=value;
SELECT @varName:=value;
18.2.2 更新用户变量的值
-- sql中的变量是一种弱类型
SET @varName=value;
SET @varName:=value;
SELECT @varName:=value;
SELECT field INTO varName FROM tableName;
18.2.3 查看用户变量的值
SELECT @varName;
18.3 局部变量
作用于: 仅在定义它的BEGIN END中有效
应用在 BEGIN END 的第一句话。
-- 声明
DECLARE varName varType;
DECLARE varName varType DEFAULT varVal;
-- 赋值
SET varName=value;
SET varName:=value;
SELECT @varName:=value;
SELECT field INTO varName FROM tableName;
-- 使用
SELECT varName;
19 存储过程和函数
19.1 创建
CREATE PROCEDURE pdName(args)
READS SQL DATA /* MySQL开启bin-log后，定义存储过程或者函数以及触发器时，会出现错误号为1418的错误，因此需要在此处指定该语句，具体作用请自行google */
BEGIN
sql1;
sql2;
END
注意:
- 参数列表包含三部分
参数模式 参数名 参数类型
eg:
IN name VARCHAR(20);
参数模式:
IN: 该参数可以作为输入，也就是该参数需要调用方传入值
OUT: 该参数可以作为输出，也就是该参数可以作为返回值
INOUT: 该参数既可以作为输入，又可以作为输出 - 如果sqls只有一句，BEGIN END可以省略
- 存储过程提中的每条sql语句的结尾必须加分号
存储过程的结尾可以使用 DELIMITER 重新设置
语法: DELIMITER 结束标记
例: DELIMITER $ - 定义了存储过程后，只要是在当前库，任何会话都可以使用。
19.2 调用
CALL 存储过程名(实参列表);
-- 注意: 使用 'DELIMITER $' 定义了结束标记为 '$'后，当前会话中结束标记就必须使用 '$'
-- 因此在当前会话中调用存储过程的语法为: CALL 存储过程名(实参列表)$
19.3 空参列表
-- 案例:
-- 1. 创建一个只有一列，数据类型为INT的表
-- 2. 然后创建一个空参的存储过程，该存储过程可以向表中插入5条数据
-- 3. 调用该存储过程向表中插入数据。
CREATE TABLE int_table (num INT);
DELIMITER $
CREATE PROCEDURE inert5records()
BEGIN
INSERT INTO int_table VALUES(1), (2), (3), (4), (5);
END $
19.4 带 IN 模式的存储过程
-- 案例: 创建存储过程实现根据女神名，查询对应的男神信息
CREATE PROCEDURE getBoyInfo(IN /*IN 可以省略*/ girlName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.`id` = b.`boyfriend_id`
WHERE b.name = girlName;
END $
CALL getBoyInfo('柳岩') $
19.5 带 IN 模式的多参数存储过程
-- 案例: 创建存储过程，实现通过检索admin表格，判断用户是否登陆成功
CREATE PROCEDURE ifLogSuccess(IN username VARCHAR(20), IN passwd VARCHAR(20))
BEGIN
DECLARE ret INT DEFAULT 0;
SELECT COUNT(*) INTO ret
FROM admin
WHERE admin.`username` = username
AND admin.`password` = passwd;
SELECT IF(ret > 0, '成功', '失败');
END $
CALL ifLogSuccess('张飞', '8888') $
19.6 创建带 OUT 模式的存储过程
-- 案例: 根据女神名返回对应的男神名
CREATE PROCEDURE getBoyName(IN girlName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
SELECT bo.`boyName` INTO boyName
FROM boys bo
INNER JOIN beauty b ON bo.`id` = b.`boyfriend_id`
WHERE b.name = girlName;
END$
SET @bName$ -- 这一句可以不写，然后直接使用
CALL getBoyName('小昭', @bName) $
SELECT @bName$
19.7 多返回值存储过程
-- 案例: 根据女神名返回对应的男神名和魅力值
CREATE PROCEDURE getBoyNameAndUserCP(IN girlName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN
SELECT bo.`boyName`, bo.`userCP` INTO boyName, userCP
FROM boys bo
INNER JOIN beauty b ON bo.`id` = b.`boyfriend_id`
WHERE b.name = girlName;
END$
CALL getBoyNameAndUserCP('小昭', @bName, @userCP) $
SELECT @bName, @userCP$
19.8 INOUT模式的存储过程
-- 案例: 传入 a 和 b 两个值，最终 a 和 b都翻倍并返回
CREATE PROCEDURE num2return(INOUT a INT, INOUT b INT)
BEGIN
SET a = a * 2;
SET b = b * 2;
END$
SET @a = 10$
SET @b = 15$
CALL num2return(@a, @b)$
SELECT @a, @b$
19.9 查看存储过程的信息
SHOW CREATE PROCEDURE pdName;
20 函数
函数和存储过程很像，存储过程的返回值数量没有要求，但是函数有且仅有一个返回值。
CREATE FUNCTION funcName(参数列表) RETURNS 返回类型
BEGIN
函数体
END
注意:
- 参数列表包含两部分，参数名和参数类型
- 函数体必须有return语句，如果没有会报错
- 如果return语句没有放在函数体的最后也不会报错，但是不建议
- 函数体中仅有一句话，则可以省略BEGIN END
- 定义函数体前需要使用DELIMITER语句设置结束标记。
20.1 调用函数
SELECT funcName(args);
20.2 无参函数
-- 案例: 返回公司的员工个数
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
READS SQL DATA
BEGIN
DECLARE ret INT DEFAULT 0;
SELECT COUNT(*) INTO ret
FROM employees;
RETURN ret;
END$
SELECT myf1()$
20.3 有参有返回
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
READS SQL DATA
BEGIN
SET @sal = 0;
SELECT salary INTO @sal
FROM employees
WHERE last_name = empName;
RETURN @sal;
END$
SELECT myf2('Kochhar')$
20.4 查看函数
SHOW CREATE FUNCTION funcName;
20.5 删除函数
DROP FUNCTION funcName;
21 流程控制结构
21.1 CASE 结构
-- 案例: 创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩: 90-100显示A，80-90显示B，60-80显示C，否则显示D
DELIMITER $
CREATE PROCEDURE test_case(IN score INT)
NO SQL
BEGIN
CASE
WHEN score >= 90 AND score <= 100 THEN SELECT 'A';
WHEN score >=80 THEN SELECT 'B';
WHEN score >=60 THEN SELECT 'C';
ELSE SELECT 'D';
END CASE;
END$
CALL test_case(95)$
21.2 IF 结构
-- 只能用在BEGIN END中
-- 案例: 创建存储过程，根据传入的成绩，来返回等级，比如传入的成绩: 90-100返回A，80-90返回B，60-80返回C，否则返回D
DELIMITER $
CREATE FUNCTION test_if(score INT) RETURNS CHAR
NO SQL
BEGIN
IF score >= 90 AND score <= 100 THEN RETURN 'A';
ELSEIF score >=80 THEN RETURN 'B';
ELSEIF score >=60 THEN RETURN 'C';
ELSE RETURN 'D';
END IF;
END$
SELECT test_if(86)$
22 循环结构
-- WHILE
[label: ]WHILE loop_condition DO
loop_body;
[LEAVE label;] -- 此语句用于跳出循环
END WHILE[ label];
-- LOOP
[label: ]LOOP
loop_body;
END LOOP[ label];
-- REPEAT
[label: ]REPEAT
loop_body;
UNTIL close_condition
END REPEAT[ label];
-- LEAVE 和 ITERATE 分别类似于breeak和continue，需要结合标签使用
22.1 WHILE循环举例
-- 案例: 批量插入，根据次数插入到admin表中多条记录
DELIMITER $
CREATE PROCEDURE pro_while(IN count INT)
CONTAINS SQL
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<= count DO
INSERT INTO admin(username, `password`) VALUES(CONCAT('Rose', i), '666');
SET i = i+1;
END WHILE;
END$
CALL pro_while(10)$
-- 案例: 批量插入，根据次数插入到admin表中多条记录，如果超过了20次则停止插入
DELIMITER $
CREATE PROCEDURE pro_while1(IN count INT)
CONTAINS SQL
BEGIN
DECLARE i INT DEFAULT 1;
a: WHILE i<= count DO
INSERT INTO admin(username, `password`) VALUES(CONCAT('Rose', i), '0000');
IF i >= 20 THEN LEAVE a;
END IF;
SET i = i+1;
END WHILE a;
END$
CALL pro_while1(21)$
附录
附录1 myemployees.sql
/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.5.15 : Database - myemployees
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE IF NOT EXISTS `myemployees` /*!40100 DEFAULT CHARACTER SET gb2312 */;
USE `myemployees`;
/*Table structure for table `departments` */
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
`department_id` int(4) NOT NULL AUTO_INCREMENT,
`department_name` varchar(3) DEFAULT NULL,
`manager_id` int(6) DEFAULT NULL,
`location_id` int(4) DEFAULT NULL,
PRIMARY KEY (`department_id`),
KEY `loc_id_fk` (`location_id`),
CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312;
/*Data for the table `departments` */
insert into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Adm',200,1700),(20,'Mar',201,1800),(30,'Pur',114,1700),(40,'Hum',203,2400),(50,'Shi',121,1500),(60,'IT',103,1400),(70,'Pub',204,2700),(80,'Sal',145,2500),(90,'Exe',100,1700),(100,'Fin',108,1700),(110,'Acc',205,1700),(120,'Tre',NULL,1700),(130,'Cor',NULL,1700),(140,'Con',NULL,1700),(150,'Sha',NULL,1700),(160,'Ben',NULL,1700),(170,'Man',NULL,1700),(180,'Con',NULL,1700),(190,'Con',NULL,1700),(200,'Ope',NULL,1700),(210,'IT ',NULL,1700),(220,'NOC',NULL,1700),(230,'IT ',NULL,1700),(240,'Gov',NULL,1700),(250,'Ret',NULL,1700),(260,'Rec',NULL,1700),(270,'Pay',NULL,1700);
/*Table structure for table `employees` */
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`employee_id` int(6) NOT NULL AUTO_INCREMENT,
`first_name` varchar(20) DEFAULT NULL,
`last_name` varchar(25) DEFAULT NULL,
`email` varchar(25) DEFAULT NULL,
`phone_number` varchar(20) DEFAULT NULL,
`job_id` varchar(10) DEFAULT NULL,
`salary` double(10,2) DEFAULT NULL,
`commission_pct` double(4,2) DEFAULT NULL,
`manager_id` int(6) DEFAULT NULL,
`department_id` int(4) DEFAULT NULL,
`hiredate` datetime DEFAULT NULL,
PRIMARY KEY (`employee_id`),
KEY `dept_id_fk` (`department_id`),
KEY `job_id_fk` (`job_id`),
CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=gb2312;
/*Data for the table `employees` */
insert into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`,`hiredate`) values (100,'Steven','K_ing','SKING','515.123.4567','AD_PRES',24000.00,NULL,NULL,90,'1992-04-03 00:00:00'),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(102,'Lex','De Haan','LDEHAAN','515.123.4569','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(103,'Alexander','Hunold','AHUNOLD','590.423.4567','IT_PROG',9000.00,NULL,102,60,'1992-04-03 00:00:00'),(104,'Bruce','Ernst','BERNST','590.423.4568','IT_PROG',6000.00,NULL,103,60,'1992-04-03 00:00:00'),(105,'David','Austin','DAUSTIN','590.423.4569','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(106,'Valli','Pataballa','VPATABAL','590.423.4560','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(107,'Diana','Lorentz','DLORENTZ','590.423.5567','IT_PROG',4200.00,NULL,103,60,'1998-03-03 00:00:00'),(108,'Nancy','Greenberg','NGREENBE','515.124.4569','FI_MGR',12000.00,NULL,101,100,'1998-03-03 00:00:00'),(109,'Daniel','Faviet','DFAVIET','515.124.4169','FI_ACCOUNT',9000.00,NULL,108,100,'1998-03-03 00:00:00'),(110,'John','Chen','JCHEN','515.124.4269','FI_ACCOUNT',8200.00,NULL,108,100,'2000-09-09 00:00:00'),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369','FI_ACCOUNT',7700.00,NULL,108,100,'2000-09-09 00:00:00'),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469','FI_ACCOUNT',7800.00,NULL,108,100,'2000-09-09 00:00:00'),(113,'Luis','Popp','LPOPP','515.124.4567','FI_ACCOUNT',6900.00,NULL,108,100,'2000-09-09 00:00:00'),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','PU_MAN',11000.00,NULL,100,30,'2000-09-09 00:00:00'),(115,'Alexander','Khoo','AKHOO','515.127.4562','PU_CLERK',3100.00,NULL,114,30,'2000-09-09 00:00:00'),(116,'Shelli','Baida','SBAIDA','515.127.4563','PU_CLERK',2900.00,NULL,114,30,'2000-09-09 00:00:00'),(117,'Sigal','Tobias','STOBIAS','515.127.4564','PU_CLERK',2800.00,NULL,114,30,'2000-09-09 00:00:00'),(118,'Guy','Himuro','GHIMURO','515.127.4565','PU_CLERK',2600.00,NULL,114,30,'2000-09-09 00:00:00'),(119,'Karen','Colmenares','KCOLMENA','515.127.4566','PU_CLERK',2500.00,NULL,114,30,'2000-09-09 00:00:00'),(120,'Matthew','Weiss','MWEISS','650.123.1234','ST_MAN',8000.00,NULL,100,50,'2004-02-06 00:00:00'),(121,'Adam','Fripp','AFRIPP','650.123.2234','ST_MAN',8200.00,NULL,100,50,'2004-02-06 00:00:00'),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234','ST_MAN',7900.00,NULL,100,50,'2004-02-06 00:00:00'),(123,'Shanta','Vollman','SVOLLMAN','650.123.4234','ST_MAN',6500.00,NULL,100,50,'2004-02-06 00:00:00'),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','ST_MAN',5800.00,NULL,100,50,'2004-02-06 00:00:00'),(125,'Julia','Nayer','JNAYER','650.124.1214','ST_CLERK',3200.00,NULL,120,50,'2004-02-06 00:00:00'),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','ST_CLERK',2700.00,NULL,120,50,'2004-02-06 00:00:00'),(127,'James','Landry','JLANDRY','650.124.1334','ST_CLERK',2400.00,NULL,120,50,'2004-02-06 00:00:00'),(128,'Steven','Markle','SMARKLE','650.124.1434','ST_CLERK',2200.00,NULL,120,50,'2004-02-06 00:00:00'),(129,'Laura','Bissot','LBISSOT','650.124.5234','ST_CLERK',3300.00,NULL,121,50,'2004-02-06 00:00:00'),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234','ST_CLERK',2800.00,NULL,121,50,'2004-02-06 00:00:00'),(131,'James','Marlow','JAMRLOW','650.124.7234','ST_CLERK',2500.00,NULL,121,50,'2004-02-06 00:00:00'),(132,'TJ','Olson','TJOLSON','650.124.8234','ST_CLERK',2100.00,NULL,121,50,'2004-02-06 00:00:00'),(133,'Jason','Mallin','JMALLIN','650.127.1934','ST_CLERK',3300.00,NULL,122,50,'2004-02-06 00:00:00'),(134,'Michael','Rogers','MROGERS','650.127.1834','ST_CLERK',2900.00,NULL,122,50,'2002-12-23 00:00:00'),(135,'Ki','Gee','KGEE','650.127.1734','ST_CLERK',2400.00,NULL,122,50,'2002-12-23 00:00:00'),(136,'Hazel','Philtanker','HPHILTAN','650.127.1634','ST_CLERK',2200.00,NULL,122,50,'2002-12-23 00:00:00'),(137,'Renske','Ladwig','RLADWIG','650.121.1234','ST_CLERK',3600.00,NULL,123,50,'2002-12-23 00:00:00'),(138,'Stephen','Stiles','SSTILES','650.121.2034','ST_CLERK',3200.00,NULL,123,50,'2002-12-23 00:00:00'),(139,'John','Seo','JSEO','650.121.2019','ST_CLERK',2700.00,NULL,123,50,'2002-12-23 00:00:00'),(140,'Joshua','Patel','JPATEL','650.121.1834','ST_CLERK',2500.00,NULL,123,50,'2002-12-23 00:00:00'),(141,'Trenna','Rajs','TRAJS','650.121.8009','ST_CLERK',3500.00,NULL,124,50,'2002-12-23 00:00:00'),(142,'Curtis','Davies','CDAVIES','650.121.2994','ST_CLERK',3100.00,NULL,124,50,'2002-12-23 00:00:00'),(143,'Randall','Matos','RMATOS','650.121.2874','ST_CLERK',2600.00,NULL,124,50,'2002-12-23 00:00:00'),(144,'Peter','Vargas','PVARGAS','650.121.2004','ST_CLERK',2500.00,NULL,124,50,'2002-12-23 00:00:00'),(145,'John','Russell','JRUSSEL','011.44.1344.429268','SA_MAN',14000.00,0.40,100,80,'2002-12-23 00:00:00'),(146,'Karen','Partners','KPARTNER','011.44.1344.467268','SA_MAN',13500.00,0.30,100,80,'2002-12-23 00:00:00'),(147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','SA_MAN',12000.00,0.30,100,80,'2002-12-23 00:00:00'),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','SA_MAN',11000.00,0.30,100,80,'2002-12-23 00:00:00'),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','SA_MAN',10500.00,0.20,100,80,'2002-12-23 00:00:00'),(150,'Peter','Tucker','PTUCKER','011.44.1344.129268','SA_REP',10000.00,0.30,145,80,'2014-03-05 00:00:00'),(151,'David','Bernstein','DBERNSTE','011.44.1344.345268','SA_REP',9500.00,0.25,145,80,'2014-03-05 00:00:00'),(152,'Peter','Hall','PHALL','011.44.1344.478968','SA_REP',9000.00,0.25,145,80,'2014-03-05 00:00:00'),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718','SA_REP',8000.00,0.20,145,80,'2014-03-05 00:00:00'),(154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','SA_REP',7500.00,0.20,145,80,'2014-03-05 00:00:00'),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','SA_REP',7000.00,0.15,145,80,'2014-03-05 00:00:00'),(156,'Janette','K_ing','JKING','011.44.1345.429268','SA_REP',10000.00,0.35,146,80,'2014-03-05 00:00:00'),(157,'Patrick','Sully','PSULLY','011.44.1345.929268','SA_REP',9500.00,0.35,146,80,'2014-03-05 00:00:00'),(158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','SA_REP',9000.00,0.35,146,80,'2014-03-05 00:00:00'),(159,'Lindsey','Smith','LSMITH','011.44.1345.729268','SA_REP',8000.00,0.30,146,80,'2014-03-05 00:00:00'),(160,'Louise','Doran','LDORAN','011.44.1345.629268','SA_REP',7500.00,0.30,146,80,'2014-03-05 00:00:00'),(161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','SA_REP',7000.00,0.25,146,80,'2014-03-05 00:00:00'),(162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','SA_REP',10500.00,0.25,147,80,'2014-03-05 00:00:00'),(163,'Danielle','Greene','DGREENE','011.44.1346.229268','SA_REP',9500.00,0.15,147,80,'2014-03-05 00:00:00'),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','SA_REP',7200.00,0.10,147,80,'2014-03-05 00:00:00'),(165,'David','Lee','DLEE','011.44.1346.529268','SA_REP',6800.00,0.10,147,80,'2014-03-05 00:00:00'),(166,'Sundar','Ande','SANDE','011.44.1346.629268','SA_REP',6400.00,0.10,147,80,'2014-03-05 00:00:00'),(167,'Amit','Banda','ABANDA','011.44.1346.729268','SA_REP',6200.00,0.10,147,80,'2014-03-05 00:00:00'),(168,'Lisa','Ozer','LOZER','011.44.1343.929268','SA_REP',11500.00,0.25,148,80,'2014-03-05 00:00:00'),(169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','SA_REP',10000.00,0.20,148,80,'2014-03-05 00:00:00'),(170,'Tayler','Fox','TFOX','011.44.1343.729268','SA_REP',9600.00,0.20,148,80,'2014-03-05 00:00:00'),(171,'William','Smith','WSMITH','011.44.1343.629268','SA_REP',7400.00,0.15,148,80,'2014-03-05 00:00:00'),(172,'Elizabeth','Bates','EBATES','011.44.1343.529268','SA_REP',7300.00,0.15,148,80,'2014-03-05 00:00:00'),(173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','SA_REP',6100.00,0.10,148,80,'2014-03-05 00:00:00'),(174,'Ellen','Abel','EABEL','011.44.1644.429267','SA_REP',11000.00,0.30,149,80,'2014-03-05 00:00:00'),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','SA_REP',8800.00,0.25,149,80,'2014-03-05 00:00:00'),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','SA_REP',8600.00,0.20,149,80,'2014-03-05 00:00:00'),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','SA_REP',8400.00,0.20,149,80,'2014-03-05 00:00:00'),(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','SA_REP',7000.00,0.15,149,NULL,'2014-03-05 00:00:00'),(179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','SA_REP',6200.00,0.10,149,80,'2014-03-05 00:00:00'),(180,'Winston','Taylor','WTAYLOR','650.507.9876','SH_CLERK',3200.00,NULL,120,50,'2014-03-05 00:00:00'),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','SH_CLERK',3100.00,NULL,120,50,'2014-03-05 00:00:00'),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','SH_CLERK',2500.00,NULL,120,50,'2014-03-05 00:00:00'),(183,'Girard','Geoni','GGEONI','650.507.9879','SH_CLERK',2800.00,NULL,120,50,'2014-03-05 00:00:00'),(184,'Nandita','Sarchand','NSARCHAN','650.509.1876','SH_CLERK',4200.00,NULL,121,50,'2014-03-05 00:00:00'),(185,'Alexis','Bull','ABULL','650.509.2876','SH_CLERK',4100.00,NULL,121,50,'2014-03-05 00:00:00'),(186,'Julia','Dellinger','JDELLING','650.509.3876','SH_CLERK',3400.00,NULL,121,50,'2014-03-05 00:00:00'),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','SH_CLERK',3000.00,NULL,121,50,'2014-03-05 00:00:00'),(188,'Kelly','Chung','KCHUNG','650.505.1876','SH_CLERK',3800.00,NULL,122,50,'2014-03-05 00:00:00'),(189,'Jennifer','Dilly','JDILLY','650.505.2876','SH_CLERK',3600.00,NULL,122,50,'2014-03-05 00:00:00'),(190,'Timothy','Gates','TGATES','650.505.3876','SH_CLERK',2900.00,NULL,122,50,'2014-03-05 00:00:00'),(191,'Randall','Perkins','RPERKINS','650.505.4876','SH_CLERK',2500.00,NULL,122,50,'2014-03-05 00:00:00'),(192,'Sarah','Bell','SBELL','650.501.1876','SH_CLERK',4000.00,NULL,123,50,'2014-03-05 00:00:00'),(193,'Britney','Everett','BEVERETT','650.501.2876','SH_CLERK',3900.00,NULL,123,50,'2014-03-05 00:00:00'),(194,'Samuel','McCain','SMCCAIN','650.501.3876','SH_CLERK',3200.00,NULL,123,50,'2014-03-05 00:00:00'),(195,'Vance','Jones','VJONES','650.501.4876','SH_CLERK',2800.00,NULL,123,50,'2014-03-05 00:00:00'),(196,'Alana','Walsh','AWALSH','650.507.9811','SH_CLERK',3100.00,NULL,124,50,'2014-03-05 00:00:00'),(197,'Kevin','Feeney','KFEENEY','650.507.9822','SH_CLERK',3000.00,NULL,124,50,'2014-03-05 00:00:00'),(198,'Donald','OConnell','DOCONNEL','650.507.9833','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(199,'Douglas','Grant','DGRANT','650.507.9844','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(200,'Jennifer','Whalen','JWHALEN','515.123.4444','AD_ASST',4400.00,NULL,101,10,'2016-03-03 00:00:00'),(201,'Michael','Hartstein','MHARTSTE','515.123.5555','MK_MAN',13000.00,NULL,100,20,'2016-03-03 00:00:00'),(202,'Pat','Fay','PFAY','603.123.6666','MK_REP',6000.00,NULL,201,20,'2016-03-03 00:00:00'),(203,'Susan','Mavris','SMAVRIS','515.123.7777','HR_REP',6500.00,NULL,101,40,'2016-03-03 00:00:00'),(204,'Hermann','Baer','HBAER','515.123.8888','PR_REP',10000.00,NULL,101,70,'2016-03-03 00:00:00'),(205,'Shelley','Higgins','SHIGGINS','515.123.8080','AC_MGR',12000.00,NULL,101,110,'2016-03-03 00:00:00'),(206,'William','Gietz','WGIETZ','515.123.8181','AC_ACCOUNT',8300.00,NULL,205,110,'2016-03-03 00:00:00');
/*Table structure for table `jobs` */
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
`job_id` varchar(10) NOT NULL,
`job_title` varchar(35) DEFAULT NULL,
`min_salary` int(6) DEFAULT NULL,
`max_salary` int(6) DEFAULT NULL,
PRIMARY KEY (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
/*Data for the table `jobs` */
insert into `jobs`(`job_id`,`job_title`,`min_salary`,`max_salary`) values ('AC_ACCOUNT','Public Accountant',4200,9000),('AC_MGR','Accounting Manager',8200,16000),('AD_ASST','Administration Assistant',3000,6000),('AD_PRES','President',20000,40000),('AD_VP','Administration Vice President',15000,30000),('FI_ACCOUNT','Accountant',4200,9000),('FI_MGR','Finance Manager',8200,16000),('HR_REP','Human Resources Representative',4000,9000),('IT_PROG','Programmer',4000,10000),('MK_MAN','Marketing Manager',9000,15000),('MK_REP','Marketing Representative',4000,9000),('PR_REP','Public Relations Representative',4500,10500),('PU_CLERK','Purchasing Clerk',2500,5500),('PU_MAN','Purchasing Manager',8000,15000),('SA_MAN','Sales Manager',10000,20000),('SA_REP','Sales Representative',6000,12000),('SH_CLERK','Shipping Clerk',2500,5500),('ST_CLERK','Stock Clerk',2000,5000),('ST_MAN','Stock Manager',5500,8500);
/*Table structure for table `locations` */
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
`location_id` int(11) NOT NULL AUTO_INCREMENT,
`street_address` varchar(40) DEFAULT NULL,
`postal_code` varchar(12) DEFAULT NULL,
`city` varchar(30) DEFAULT NULL,
`state_province` varchar(25) DEFAULT NULL,
`country_id` varchar(2) DEFAULT NULL,
PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=gb2312;
/*Data for the table `locations` */
insert into `locations`(`location_id`,`street_address`,`postal_code`,`city`,`state_province`,`country_id`) values (1000,'1297 Via Cola di Rie','00989','Roma',NULL,'IT'),(1100,'93091 Calle della Testa','10934','Venice',NULL,'IT'),(1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP'),(1300,'9450 Kamiya-cho','6823','Hiroshima',NULL,'JP'),(1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US'),(1500,'2011 Interiors Blvd','99236','South San Francisco','California','US'),(1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US'),(1700,'2004 Charade Rd','98199','Seattle','Washington','US'),(1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA'),(1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA'),(2000,'40-5-12 Laogianggen','190518','Beijing',NULL,'CN'),(2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN'),(2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU'),(2300,'198 Clementi North','540198','Singapore',NULL,'SG'),(2400,'8204 Arthur St',NULL,'London',NULL,'UK'),(2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK'),(2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK'),(2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE'),(2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR'),(2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH'),(3000,'Murtenstrasse 921','3095','Bern','BE','CH'),(3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL'),(3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
附录2 girls.sql
/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.7.18-log : Database - girls
*********************************************************************
*/
/*!40101 SET NAMES utf8mb4 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`girls` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `girls`;
/*Table structure for table `admin` */
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`password` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*Data for the table `admin` */
insert into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666');
/*Table structure for table `beauty` */
DROP TABLE IF EXISTS `beauty`;
CREATE TABLE `beauty` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`sex` char(1) DEFAULT '女',
`borndate` datetime DEFAULT '1987-01-01 00:00:00',
`phone` varchar(11) NOT NULL,
`photo` blob,
`boyfriend_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
/*Data for the table `beauty` */
insert into `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) values (1,'柳岩','女','1988-02-03 00:00:00','18209876577',NULL,8),(2,'苍老师','女','1987-12-30 00:00:00','18219876577',NULL,9),(3,'Angelababy','女','1989-02-03 00:00:00','18209876567',NULL,3),(4,'热巴','女','1993-02-03 00:00:00','18209876579',NULL,2),(5,'周冬雨','女','1992-02-03 00:00:00','18209179577',NULL,9),(6,'周芷若','女','1988-02-03 00:00:00','18209876577',NULL,1),(7,'岳灵珊','女','1987-12-30 00:00:00','18219876577',NULL,9),(8,'小昭','女','1989-02-03 00:00:00','18209876567',NULL,1),(9,'双儿','女','1993-02-03 00:00:00','18209876579',NULL,9),(10,'王语嫣','女','1992-02-03 00:00:00','18209179577',NULL,4),(11,'夏雪','女','1993-02-03 00:00:00','18209876579',NULL,9),(12,'赵敏','女','1992-02-03 00:00:00','18209179577',NULL,1);
/*Table structure for table `boys` */
DROP TABLE IF EXISTS `boys`;
CREATE TABLE `boys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`boyName` varchar(20) DEFAULT NULL,
`userCP` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*Data for the table `boys` */
insert into `boys`(`id`,`boyName`,`userCP`) values (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
