6.1约束类型
• 非空约束(not null)
• 唯一性约束(unique)
• 主键约束(primary key) PK
• 外键约束(foreign key) FK
• 检查约束(目前 MySQL 不支持、Oracle 支持)
添加约束有两种: 一种创建表时就直接添加约束,另一种创建表之后再修改添加表约束
先说第一种:
6.2创建表时添加约束
查询表中的约束信息
SHOW KEYS FROM 表名
6.2.1示例一
创建 departments 表包含 department_id 该列为主键且自动增长，department_name 列不允许重复，location_id 列不允含有空值。
create table departments(department_id int primary key auto_increment,department_name varchar(30) unique,location_id int not null);
6.2.2示例二
创建 employees 表包含 employees_id 该列为主键且自动增长，last_name 列不允许含有空值，email 列不允许有重复不允许含有空值，dept_id 为外键参照 departments 表的主键。
create table employees(employees_id int primary key auto_increment,last_name varchar(30) not null,email varchar(40) not null unique,dept_id int,constraint emp_fk foreign key(dept_id) references departments(department_id));
再说第二种:
6.3修改表实现约束的添加与删除
6.3.1主键约束
6.3.1.1 添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(列名)
6.3.1.1.1示例
将 emp 表中的 employee_id 修改为主键且自动增长
添加主键：alter table emp add primary key(employee_id);
添加自动增长：alter table emp modify employee_id int auto_increment;
6.3.1.2 删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY
注意：删除主键时，如果主键列具备自动增长能力，需要先去掉自动增长，然后在删除主键。
6.3.1.2.1示例
删除 employee_id 的主键约束。
去掉自动增长：
删除主键：alter table emp drop primary key
6.3.2.1 添加非空约束
ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL
6.3.2.1.1示例
向 emp 表中的 salary 添加非空约束。
alter table emp modify salary float(8,2) not null;
6.3.2.2 删除非空约束
ALTER TABLE 表名 MODIFY 列名 类型 NULL
6.3.2.2.1示例
删除 salary 的非空约束。
alter table emp modify salary float(8,2) null;
6.3.3唯一约束
6.3.3.1 添加唯一约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名)
6.3.3.1.1示例
向 emp 表中的 name 添加唯一约束。
alter table emp add constraint emp_uk unique(name);
6.3.3.2 删除唯一约束
ALTER TABLE 表名 DROP KEY 约束名
6.3.3.2.1示例
删除 name 的唯一约束。
alter table emp drop key emp_uk;
6.3.4外键约束
6.3.4.1 添加外键约束
ALTER TABLE 表 名 ADD CONSTRAINT 约 束 名 FOREIGN KEY( 列 名 )
REFERENCES 参照的表名(参照的列名)
6.3.4.1.1示例一
修改 emp 表，添加 dept_id 列。
alter table emp add column dept_id int;
6.3.4.1.2示例二
向 emp 表中的 dept_id 列添加外键约束。
alter table emp add constraint e_fk foreign key(dept_id) references departments(department_id)
6.3.4.2 删除外键约束
删除外键:
ALTER TABLE 表名 DROP FOREIGN KEY 约束名
删除外键索引(索引名与约束名相同):
ALTER TABLE 表名 DROP INDEX 索引名
6.3.4.2.1示例
删除 dept_id 的外键约束。
删除外键：alter table emp drop foreign key e_fk;
删除索引： alter table emp drop index e_fk;
MySQL 中的 DML 操作
7.1添加数据(INSERT)
7.1.1插入数据
7.1.1.1 选择插入
INSERT INTO 表名(列名 1，列名 2，列名 3.....) VALUES(值 1，值 2，值 3......)
7.1.1.1.1示例
向 departments 表中添加一条数据，部门名称为 market，工作地点 ID 为 1。
insert into departments(department_name,location_id) values("market",1);
7.1.1.2 完全插入
INSERT INTO 表名 VALUES(值 1，值 2，值 3......)
如果主键是自动增长，需要使用 default 或者 null 或者 0 占位。
7.1.1.2.1示例一
向 departments 表中添加一条数据，部门名称为 development，工作地点 ID 为 2。使用
default 占位。
insert into departments values(default,"development",2);
7.1.1.2.2示例二
向 departments 表中添加一条数据，部门名称为 human，工作地点 ID 为 3。使用 null 占
位。
insert into departments values(null,"human",3);
7.1.1.2.3示例三
向 departments 表中添加一条数据，部门名称为 teaching，工作地点 ID 为 4。使用 0 占
位。
insert into departments values(0,"teaching",4);
7.1.2自动增长(auto_increment)
MySQL 中的自动增长类型要求：
一个表中只能有一个列为自动增长。
自动增长的列的类型必须是整数类型。
自动增长只能添加到具备主键约束与唯一性约束的列上。
删除主键约束或唯一性约束，如果该列拥有自动增长能力，则需要先去掉自动增长然
后在删除约束。
7.1.2.1 示例
创建一个 emp2 表。包含 id 该列为主键，包含 name，包含 seq_num 要求该列为具备唯一性约束，该列的值自动增长。
create table emp2(id int primary key ,name varchar(30),seq_num int unique
auto_increment);
7.1.3默认值处理
在 MySQL 中可以使用 DEFAULT 为字段设定一个默认值。如果在插入数据时并未指
定该列的值，那么 MySQL 会将默认值添加到该列中。
7.1.3.1 插入数据时的默认值处理---为默认值使用占位时,只能使用default 来占位,不能使用0 或null;
如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。如果是
完全项插入需要使用 default 来占位。
7.1.3.1示例
向 emp3 表中添加数据，要求 address 列与 job_id 列使用默认值作为该列的值。
选择列的插入方式:
insert into emp3(name) values("admin");
完全项的插入方式:
insert into emp3 values(default,"oldlu",default,default);
7.2更新数据
7.2更新数据(UPDATE)----MySQL写完语句 之后,默认的是事物提交的.
UPDATE 表名 SET 列名=值，列名=值 WHERE 条件
注意:
7.2.1mysql 的 update 的特点
更新的表不能在 set 和 where 中用于子查询; --下面方式二的方法不是子查询
update 后面可以做任意的查询
7.2.1.1 示例一
更新 emp3 表中的 id 为 1 的数据，添加 address 为 BeiJing。
update emp3 e set e.address = "BeiJing" where emp_id = 1;
7.2.1.2 示例二
方式一：更新 emp3 中 id 为 2 的数据，将地址修改为与 id 为 1 用户的地址相同
Oracle方式在MySQL中会报错
Oracle:update emp3 e set e.address = (select address from emp3 where emp_id = 1)
where e.emp_id = 2;
MySQL: update emp3 e ,(select address from emp3 where emp_id = 1)t set e.address = t.address where e.emp_id =2;
方式二：更新 emp3 中 id 为 2 的数据，将地址修改为与 id 为 1 用户的地址相同
update emp3 e set e.address = (select t1.address from (select emp_id, add
ress from emp3)t1 where t1.emp_id = 1 ) where e.emp_id = 2;
7.3删除数据(DELETE)
7.3.1使用 DELETE 子句
DELETE FROM 表名 WHERE 条件
7.3.2DELETE 与 TRUNCATE 区别
truncate 是整体删除（速度较快）， delete 是逐条删除（速度较慢）；
truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete 高的因；
truncate 是会重置自增值，相当于自增列会被置为初始值，又重新从 1 开始记录，而不是接着原来的值。而 delete 删除以后，自增值仍然会继续累加。
8 MySQL 中的事务处理
在 MySQL 中，默认情况下，事务是自动提交的，也就是说，只要执行一条 DML 语句就开启了事物，并且提交了事务
8.1关闭 MySQL 的事务自动提交
START TRANSACTION
DML.... COMMIT|ROLLBACK
8.1.1示例
向 emp3 表中添加一条数据，要求手动提交事务。
六、 MySQL 查询数据
1 MySQL 的基本查询
1.1MySQL 的列选择
SELECT * | 投影列 FROM 表名
1.1.1示例
查询 departments 表中的所有数据
select * from departments;
1.2MySQL 的行选择
SELECT * | 投影列 FROM 表名 WHERE 选择条件
1.3SELECT 语句中的算术表达式
1.3.1示例一
修改 employees 表添加 salary。
alter table employees add column salary float(9,2);
1.3.2示例二
查询雇员的年薪。
select employees_id,last_name,email,12*salary from employees;
1.3.3示例三
计算 employees 表中的员工全年薪水加 100 以后的薪水是多少？
select employees_id,last_name,email,12*salary+100 from employees;
1.4MySQL 中定义空值
包含空值的算术表达式计算结果为空。
1.5MySQL 中的列别
SELECT 列名 AS 列别名 FROM 表名 WHERE 条件
1.6MySQL 中的连字符
MySQL 中并不支持||作为连字符，需要使用 concat 函数。在参数数量上与 oracle 的 concat函数有区别。
1.7MySQL 中去除重复
在 SELECT 语句中用 DISTINCT 关键字除去相同的行。
2 约束和排序数据
2.1MySQL 中的比较条件
2.1.1比较运算符
2.1.2模糊查询---也是like
%表示任意多个任意字符
_表示一个任意字符
MySQL中的"_"和Oracle的不太相同如下
2.1.2.1 示例
查询 employees 中雇员名字第二个字母是 e 的雇员信息。
select * from employees where last_name like '_e%'
2.1.3逻辑运算符--and---or-not
2.1.4范围查询
between ... and
in 表示在一个非连续的范围内
2.1.5空值判断--•判断空 is null ---判断非空 is not null
2.2使用 ORDER BY 排序---同Oracle
用 ORDER BY 子句排序
ASC: 升序排序，默认
DESC: 降序排序
3 MySQL 中常见的单行函数
3.1大小写控制函数
LOWER(str)
UPPER(str)
3.2字符处理
CONCAT(str1,str2,...) 将 str1、str2 等字符串连接起来
SUBSTR(str,pos,len) 从 str 的第 pos 位（范围：1~str.length）开始，截取长度为 len的字符串
LENGTH(str) 获取 str 的长度
INSTR(str,substr)
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 的字符串）
3.3数字函数
ROUND(arg1,arg2)：四舍五入指定小数的值。
ROUND(arg1)：四舍五入保留整数。
TRUNC(arg1,arg2)：截断指定小数的值，不做四舍五入处理。
MOD(arg1,arg2)：取余。
3.4日期函数----MySQL和Oracle不同,MySQL都有()
SYSDATE() 或者 NOW() 返回当前系统时间，格式为 YYYY-MM-DD hh-mm-ss
CURDATE() 返回系统当前日期，不返回时间
CURTIME() 返回当前系统中的时间，不返回日期
DAYOFMONTH(date) 计算日期 d 是本月的第几天
DAYOFWEEK(date)
DAYOFYEAR(date)
DAYNAME(date)
LAST_DAY(date)
3.5转换函数
DATE_FORMAT(date,format) 将日期转换成字符串（类似 oracle 中的 to_char()）
STR_TO_DATE(str,format)
3.6示例一
向 employees 表中添加 hire_date 列 类型为 date 类型
alter table employees add column hire_date date
3.7示例二
向 employees 表中添加一条数据，名字：King ，email：king@sxt.cn，部门 ID：1，薪水：9000，入职时间：2018 年 5 月 1 日，佣金：0.6
insert into employees values(default,'King','king@sxt.cn',1,9000,0.6,STR_TO_DATE('2018年 5 月 1 日','%Y 年%m 月%d 日'))
3.9通用函数
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
4 多表连接查询
4.1等值连接
4.2非等值连接
4.3自连接
5 外连接(OUTER JOIN)
5.1左外连接(LEFT OUTER JOIN)
5.2右外连接(RIGHT OUTER JOIN)
5.3全外链接
注意：MySQL 中不支持 FULL OUTER JOIN 连接
可以使用 union 实现全完连接
5.3.1UNION
可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了
DISTINCT。
5.3.2UNION ALL
只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。
5.3.3语法结构
SELECT 投 影 列 FROM 表 名 LEFT OUTER JOIN 表 名 ON 连 接 条 件 UNION SELECT 投影列 FROM 表名 RIGHT OUTER JOIN 表名 ON 连接条件
6 SQL99 标准中的查询
MySQL5.7 支持 SQL99 标准。
6.1SQL99 中的交叉连接(CROSS JOIN)
7 聚合函数
7.1AVG(arg)函数
对分组数据做平均值运算。
arg:参数类型只能是数字类型。
