1.MySQL 中的约束
约束类型
• 非空约束(not null)
• 唯一性约束(unique)
• 主键约束(primary key) PK
• 外键约束(foreign key) FK
• 检查约束(目前 MySQL 不支持、Oracle 支持)
创建表时添加约束
查询表中的约束信息 ：
SHOW KEYS FROM 表名
示例一
创建 departments 表包含 department_id 该列为主键且自动增长，department_name 列不允许重复，location_id 列不允含有空值。
create 示例二
创建 employees 表包含 employees_id 该列为主键且自动增长，last_name 列不允许含有空值，email 列不允许有重复不允许含有空值，dept_id 为外键参照 departments 表的主键。
create 修改表实现约束的添加与删除
主键约束
添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(列名)
示例 ：将 emp 表中的 employee_id 修改为主键且自动增长
添加主键：
alter 添加自动增长：
alter 删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY
注意：删除主键时，如果主键列具备自动增长能力，需要先去掉自动增长，然后在删除主键。
示例 ：删除 employee_id 的主键约束。
去掉自动增长：
alter 删除主键：
alter 非空约束
添加非空约束
ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL
示例 ：向 emp 表中的 salary 添加非空约束。
alter 删除非空约束
ALTER TABLE 表名 MODIFY 列名 类型 NULL
示例 ：删除 salary 的非空约束。
alter 唯一约束
添加唯一约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名)
示例 ：向 emp 表中的 name 添加唯一约束。
alter 删除唯一约束
ALTER TABLE 表名 DROP KEY 约束名
示例 ：删除 name 的唯一约束。
alter 外键约束
添加外键约束
ALTER TABLE 表 名 ADD CONSTRAINT 约 束 名 FOREIGN KEY( 列 名 ) REFERENCES 参照的表名(参照的列名)
示例一 ：修改 emp 表，添加 dept_id 列。
alter 示例二 ：向 emp 表中的 dept_id 列添加外键约束。
alter 删除外键约束
删除外键:
ALTER TABLE 表名 DROP FOREIGN KEY 约束名
删除外键索引(索引名与约束名相同):
ALTER TABLE 表名 DROP INDEX 索引名
示例 ：删除 dept_id 的外键约束。
删除外键：
alter 删除索引：
alter 2.MySQL 中的 DML 操作
插入数据：选择插入
NSERT INTO 表名(列名 1，列名 2，列名 3.....) VALUES(值 1，值 2，值 3......)
示例 ：向 departments 表中添加一条数据，部门名称为 market，工作地点 ID 为 1。
insert 完全插入
INSERT INTO 表名 VALUES(值 1，值 2，值 3......)
如果主键是自动增长，需要使用 default 或者 null 或者 0 占位。
示例一 ：向 departments 表中添加一条数据，部门名称为 development，工作地点 ID 为 2。使用default 占位。
insert 2示例二 ：向 departments 表中添加一条数据，部门名称为 human，工作地点 ID 为 3。使用 null 占位。
insert 示例三 ：向 departments 表中添加一条数据，部门名称为 teaching，工作地点 ID 为 4。使用 0 占位。
insert 自动增长(auto_increment)
MySQL 中的自动增长类型要求：
• 一个表中只能有一个列为自动增长。
• 自动增长的列的类型必须是整数类型。
• 自动增长只能添加到具备主键约束与唯一性约束的列上。
• 删除主键约束或唯一性约束，如果该列拥有自动增长能力，则需要先去掉自动增长然后在删除约束。
示例 ：创建一个 emp2 表。包含 id 该列为主键，包含 name，包含 seq_num 要求该列为具备唯一性约束，该列的值自动增长。
create 默认值处理
在 MySQL 中可以使用 DEFAULT 为字段设定一个默认值。如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。
创建表时指定列的默认值
示例 ：创建 emp3 表，该表包含 emp_id 主键且自动增长，包含 name，包含 address 该列默认值为”未知”。
create 修改表添加列的默认值
示例 ：修改 emp3 表，添加 job_id 该列默认值为 0。
alter 插入数据时的默认值处理
如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。如果是完全项插入需要使用 default 来占位。
示例 ：向 emp3 表中添加数据，要求 address 列与 job_id 列使用默认值作为该列的值。
insert 更新数据(UPDATE)
UPDATE 表名 SET 列名=值，列名=值 WHERE 条件
mysql 的 update 的特点
• 更新的表不能在 set 和 where 中用于子查询;
• update 后面可以做任意的查询
示例一 ：更新 emp3 表中的 id 为 1 的数据，添加 address 为 BeiJing。
update 示例二
方式一：更新 emp3 中 id 为 2 的数据，将地址修改为与 id 为 1 用户的地址相同
Oracle方式二：更新 emp3 中 id 为 2 的数据，将地址修改为与 id 为 1 用户的地址相同
update 删除数据(DELETE)
使用 DELETE 子句
DELETE FROM 表名 WHERE 条件
示例 ：删除 emp3 表中 emp_id 为 1 的雇员信息。
delete 使用 TRUNCATE 清空表
TRUNCATE TABLE 表名
示例 ：删除 emp3 表中的所有数据
truncate DELETE 与 TRUNCATE 区别
• truncate 是整体删除（速度较快）， delete 是逐条删除（速度较慢）；
• truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete 高的原因；
• truncate 是会重置自增值，相当于自增列会被置为初始值，又重新从 1 开始记录，而不是接着原来的值。而 delete 删除以后，自增值仍然会继续累加。
MySQL 中的事务处理
在 MySQL 中，默认情况下，事务是自动提交的，也就是说，只要执行一条 DML 语句就开启了事物，并且提交了事务
关闭 MySQL 的事务自动提交
START TRANSACTION
DML....
COMMIT|ROLLBACK
示例 ：向 emp3 表中添加一条数据，要求手动提交事务。
mysql3.MySQL 查询数据
MySQL 的基本查询
MySQL 的列选择
SELECT * | 投影列 FROM 表名
示例 ：查询 departments 表中的所有数据
select MySQL 的行选择
SELECT * | 投影列 FROM 表名 WHERE 选择条件
示例 ：查询 departments 表中部门 ID 为 4 的部门名称与工作地点 ID。
select SELECT 语句中的算术表达式
+ ：加法运算
- ：减法运算
* ：乘法运算
/ ：除法运算，返回商
% ：求余运算，返回余数
示例一 ：修改 employees 表添加 salary。
alter 示例二 ：查询雇员的年薪。
select 示例三 ：计算 employees 表中的员工全年薪水加 100 以后的薪水是多少？
select MySQL 中定义空值
包含空值的算术表达式计算结果为空。
示例 ：在 employees 中添加 commission_pct，计算年薪包含佣金。
alter MySQL 中的列别名
SELECT 列名 AS 列别名 FROM 表名 WHERE 条件
示例 ：查询 employees 表将雇员 laser_name 列名改为 name。
select MySQL 中的连字符
MySQL 中并不支持||作为连字符，需要使用 concat 函数。在参数数量上与 oracle 的 concat函数有区别。
示例 ：查询雇员表中的所有数据，将所有数据连接到一起，每列值中通过#分割。
select MySQL 中去除重复
在 SELECT 语句中用 DISTINCT 关键字除去相同的行。
示例 ：查询 employees 表，显示唯一的部门 ID。
select MySQL 中的比较条件
比较运算符
• 等于=
• 大于>
• 大于等于>=
• 小于<
• 小于等于<=
• 不等于!=或<>
示例一 ：查询 employees 表，员工薪水大于等于 3000 的员工的姓名与薪水。
select 示例二 ：查询 employees 表，员工薪水不等于 5000 的员工的姓名与薪水。
select 模糊查询
• like
• %表示任意多个任意字符
• _表示一个任意字符
示例 ：查询 employees 中雇员名字第二个字母是 e 的雇员信息。
select 逻辑运算符
• and
• or
• not
示例一 ：查询 employees 表中雇员薪水是 5000 的并且名字中含有 d 的雇员信息
select 示例二 ：查询 employees 表中雇员名字中不包含 u 的雇员信息
select 范围查询
• between ... and
• in 表示在一个非连续的范围内
示例一 ：查询 employees 表，薪水在 3000-8000 之间的雇员信息
select 示例二 ：查询 employyees 表，找出薪水是 5000,6000,8000 的雇员信息
select 空值判断
• 判断空 is null
• 判断非空 is not null
示例一 ：找出 emloyees 表中那些没有佣金的雇员
select 示例二 ：找出 employees 表中那些有佣金的雇员
select 使用 ORDER BY 排序
• 用 ORDER BY 子句排序
• ASC: 升序排序，默认
• DESC: 降序排序
示例一 ：查询 employees 表中的所有雇员，薪水按升序排序。
select 示例二 ：查询 employees 表中的所有雇员，雇员名字按降序排序。
select 4.MySQL 中常见的单行函数
大小写控制函数
LOWER(str) 转换大小写混合的字符串为小写字符串
UPPER(str) 转换大小写混合的字符串为大写字符串。
字符处理
CONCAT(str1,str2,...) 将 str1、str2 等字符串连接起来
SUBSTR(str,pos,len) 从 str 的第 pos 位（范围：1~str.length）开始，截取长度为 len的字符串
LENGTH(str) 获取 str 的长度
INSTR(str,substr) 获取 substr 在 str 中的位置
LPAD(str,len,padstr)/RPAD(str,len,padstr)
TRIM(str) 从 str 中删除开头和结尾的空格（不会处理字符串中间含有的空格）
LTRIM(str) 从 str 中删除左侧开头的空格
RTRIM(str) 从 str 中删除右侧结尾的空格
REPLACE(str,from_str,to_str) 将 str 中的 from_str 替换为 to_str（会替换掉所有符合from_str 的字符串）
数字函数
ROUND(arg1,arg2)：四舍五入指定小数的值。
ROUND(arg1)：四舍五入保留整数。
TRUNC(arg1,arg2)：截断指定小数的值，不做四舍五入处理。
MOD(arg1,arg2)：取余。
日期函数
SYSDATE() 或者 NOW() 返回当前系统时间，格式为 YYYY-MM-DD hh-mm-ss
CURDATE() 返回系统当前日期，不返回时间
CURTIME() 返回当前系统中的时间，不返回日期
DAYOFMONTH(date) 计算日期 d 是本月的第几天
DAYOFWEEK(date) 日期 d 今天是星期几，1 星期日，2 星期一，以此类推
DAYOFYEAR(date) 返回指定年份的天数
DAYNAME(date) 返回 date 日期是星期几
LAST_DAY(date) 返回 date 日期当月的最后一天
转换函数
DATE_FORMAT(date,format) 将日期转换成字符串（类似 oracle 中的 to_char()）
STR_TO_DATE(str,format) 将字符串转换成日期（类似 oracle 中的 to_date()）
示例一
向 employees 表中添加 hire_date 列 类型为 date 类型
alter 示例二
向 employees 表中添加一条数据，名字：King ，email：king@sxt.cn，部门 ID：1，薪水：9000，入职时间：2018 年 5 月 1 日，佣金：0.6
insert 示例三
查询 employees 表中雇员名字为 King 的雇员的入职日期，要求显示格式为 yyyy 年 MM 月 dd 日。
select 通用函数
IFNULL(expr1,expr2) 判断 expr1 是否为 null，如果为 null，则用 expr2 来代替 null
（类似 oracle 的 NVL()函数）
NULLIF(expr1,expr2) 判断 expr1 和 expr2 是否相等，如果相等则返回 null，如果不
相等则返回 expr1
IF(expr1,expr2,expr3) 判断 expr1 是否为真（是否不为 null），如果为真，则使用 expr2
替代 expr1；如果为假，则使用 expr3 替代 expr1（类似 oracle 的 NVL2()函数）
COALESCE(value,...) 判断 value 的值是否为 null，如果不为 null，则返回 value；如
果为 null，则判断下一个 value 是否为 null……直至出现不为 null 的 value 并返回或者返回最
后一个为 null 的 value
CASE WHEN THEN ELSE END 条件函数
5.多表连接查询
等值连接
示例 ：查询雇员 King 所在的部门名称
select 非等值连接
示例一 ：创建 sal_level 表，包含 lowest_sal，highest_sal，level。
create 示例二 ：插入数据
1000 2999 A
2000 4999 B
5000 7999 C
8000 12000 D
insert 示例三 ：查询所有雇员的薪水级别。
select 自连接
示例一 ：修改 employees 表，添加 manager_id 列
ALTER 示例二 ：修改数据 Oldlu 是 kevin 与 King 的经理
Taylor 示例三 ：查询每个雇员的经理的名字以及雇员的名字。
select 外连接(OUTER JOIN)
左外连接(LEFT OUTER JOIN)
示例一
向 employees 表中添加一条数据，名字：Lee，email：lee@sxt.cn，入职时间为今天。他没有薪水，没有经理，没有佣金。
insert into employees(last_name,email,hire_date) values('Lee','lee@sxt.cn',SYSDATE())示例二
查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员。
select 右外连接(RIGHT OUTER JOIN)
示例一
向 departments 表中添加一条数据，部门名称为 Testing，工作地点 ID 为 5。
insert 示例二
查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门。
select 全外链接
注意：MySQL 中不支持 FULL OUTER JOIN 连接
可以使用 union 实现全完连接。
UNION
可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了DISTINCT。
UNION ALL
只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。
语法结构
SELECT 投 影 列 FROM 表 名 LEFT OUTER JOIN 表 名 ON 连 接 条 件 UNION
SELECT 投影列 FROM 表名 RIGHT OUTER JOIN 表名 ON 连接条件
示例
查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门以及没有部门的雇员。
(SQL99 标准中的查询
MySQL5.7 支持 SQL99 标准。
SQL99 中的交叉连接(CROSS JOIN)
示例 ：使用交叉连接查询 employees 表与 departments 表
select SQL99 中的自然连接(NATURAL JOIN)
示例一 ：修改 employees 表中的 dept_id 列将该列的名称修改为 department_id
alter 示例二 ：使用自然连接查询所有有部门的雇员的名字以及部门名称。
select SQL99 中的内连接(INNER JOIN)
示例 ：查询雇员名字为 OldLu 的雇员 ID，薪水与部门名称。
select 6.聚合函数
AVG(arg)函数
对分组数据做平均值运算。
arg:参数类型只能是数字类型。
SUM(arg)函数
对分组数据求和。
arg:参数类型只能是数字类型。
MIN(arg)函数
求分组中最小数据。
arg:参数类型可以是字符、数字、日期。
MAX(arg)函数
求分组中最大数据。
arg:参数类型可以是字符、数字、日期。
COUNT 函数
返回一个表中的行数。
COUNT 函数有三种格式：
• COUNT(*)
• COUNT(expr)
• COUNT(DISTINCT expr)
7.数据组(GROUP BY)
创建数据组
示例 ：计算每个部门的平均薪水
select 约束分组结果(HAVING)
显示那些最高薪水大于 5000 的部门的部门号和最高薪水。
select 
188
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
