1 MySQL 中的约束
1.1约束类型
• 非空约束(not null)
• 唯一性约束(unique)
• 主键约束(primary key) PK
• 外键约束(foreign key) FK
• 检查约束(目前 MySQL 不支持、Oracle 支持)
1.2 MySQL和Oracle创建表时添加约束
1）修改表时添加主键
MySQL：Alter table emp modify(id constraint pk primary key);
Oracle：Alter table emp add primary key(id);
删除主键约束
Alter table emp drop primary key;
删除主键约束时，如果主键列具备自增长能力，增需先去掉自增，再删除主键。
添加自增
Alter table emp modify id int auto_increment;
去掉自增能力
Alter table emp modify id int (null);//不加null只能在unique情况下而primary key 不能加null；
2）修改表时添加非空约束；
MySQL：Alter table emp modify salary not null;
Oracle：Alter table emp modify salary float(8,2) not null;
删除MySQL非空约束
Alter table emp modify salary float(8,2) null;
3）修改添加唯一性约束
MySQL：Alter table emp modify(last_name unique);
Oracle：Alter table emp add constraint emp_uk unique(last_name);
删除唯一性约束
Alter table emp drop key emp_uk;
4）修改时添加外键
MySQL：Alter table emp add constraint e_fk foreign key(dep_id) references departments(departmetn_id);
Oracle：Alter table emp add constraint e_fk foreign key(dep_id) references departments(department_id);
删除外键
第一步
Alter table emp drop foreign key e_fk;
第二步(删除外键索引，索引名与外键别名相同)
Alter table emp drop index e_fk;
5）查看表的所有约束
Show keys form emp;
Show tables;
Show databases;
7 MySQL 中的 DML 操作
2 MySQL中的DML操作
2.1添加数据(INSERT)
选择插入
INSERT INTO 表名(列名 1，列名 2，列名 3.....) VALUES(值 1，值 2，值 3......)
2.1.1示例
向 departments 表中添加一条数据，部门名称为 market，工作地点 ID 为 1。
insert
2.1.2完全插入
INSERT INTO 表名 VALUES(值 1，值 2，值 3......)
如果主键是自动增长，需要使用 default 或者 null 或者 0 占位。
示例
向 departments 表中添加一条数据，部门名称为 development，工作地点 ID 为 2。使用default 占位。
insert into departments values(default,"development",2);
向 departments 表中添加一条数据，部门名称为 human，工作地点 ID 为 3。使用 null 占位。
insert into departments values(null,"human",3);
向 departments 表中添加一条数据，部门名称为 teaching，工作地点 ID 为 4。使用 0 占位。
insert into departments values(0,"teaching",4);
2.1.3 自动增长(auto_increment)
MySQL 中的自动增长类型要求：
• 一个表中只能有一个列为自动增长。
• 自动增长的列的类型必须是整数类型。
• 自动增长只能添加到具备主键约束与唯一性约束的列上。
• 删除主键约束或唯一性约束，如果该列拥有自动增长能力，则需要先去掉自动增长然后在删除约束。
示例
创建一个 emp2 表。包含 id 该列为主键，包含 name，包含 seq_num 要求该列为具备唯一性约束，该列的值自动增长。
create table emp2(id int primary key ,name varchar(30),seq_num int unique auto_increment);
2.2 默认值处理
在 MySQL 中可以使用 DEFAULT 为字段设定一个默认值。如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。
2.2.1 创建表时指定列的默认值
示例
创建 emp3 表，该表包含 emp_id 主键且自动增长，包含 name，包含 address 该列默认值为”未知”。
create table emp3(emp_id int primary key auto_increment,name varchar(30), address varchar(50) default 'Unknown');
2.2.2 修改表添加列的默认值
示例
修改 emp3 表，添加 job_id 该列默认值为 0。
alter table emp3 add column job_id int default 0;
2.2.3 插入数据时的默认值处理
如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。如果是完全项插入需要使用 default 来占位。
示例
向 emp3 表中添加数据，要求 address 列与 job_id 列使用默认值作为该列的值。
insert into emp3(name) values("admin");
insert into emp3 values(default,"oldlu",default,default);
2..3 更新数据(UPDATE)
UPDATE 表名 SET 列名=值，列名=值 WHERE 条件
2.3.1mysql 的 update 的特点
• 更新的表不能在 set 和 where 中用于子查询
• update 后面可以做任意的查询
示例一
更新 emp3 表中的 id 为 1 的数据，添加 address 为 BeiJing。
update emp3 e set e.address = "BeiJing" where emp_id = 1;
示例二
方式一：更新 emp3 中 id 为 2 的数据，将地址修改为与 id 为 1 用户的地址相同
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;
2.4 删除数据(DELETE)
2.4.1使用 DELETE 子句
DELETE FROM 表名 WHERE 条件
示例
删除 emp3 表中 emp_id 为 1 的雇员信息。
delete from emp3 where emp_id = 1
2.4.2 使用 TRUNCATE 清空表
truncate table 表名
示例
删除 emp3 表中的所有数据
truncate table emp3;
2.4.5 DELETE 与 TRUNCATE 区别
• truncate 是整体删除（速度较快）， delete 是逐条删除（速度较慢）；
• truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete 高的原因；
• truncate 是会重置自增值，相当于自增列会被置为初始值，又重新从 1 开始记录，而不是接着原来的值。而 delete 删除以后，自增值仍然会继续累加。
3 MySQL 中的事务处理
在 MySQL 中，默认情况下，事务是自动提交的，也就是说，只要执行一条 DML 语句就开启了事物，并且提交了事务
3.1关闭 MySQL 的事务自动提交
START TRANSACTION
DML.... COMMIT|ROLLBACK
示例
向 emp3 表中添加一条数据，要求手动提交事务。
start transaction;
insert into emp3 values(default,"oldlu",default,default);
commit;
3.2 MySQL 中的连字符
MySQL 中并不支持||作为连字符，需要使用 concat 函数。在参数数量上与 oracle 的 concat函数有区别。
示例
查询雇员表中的所有数据，将所有数据连接到一起，每列值中通过#分割。
select concat(employees_id,'#',last_name,'#',email,"#",salary,"#",commission_pct) from employees;
4 MySQL 中常见的单行函数
4.1大小写控制函数
LOWER(str) 转换大小写混合的字符串为小写字符串
UPPER(str) 转换大小写混合的字符串为大写字符串。
4.2 字符处理
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 的字符串）
4.3 数字函数
ROUND(arg1,arg2)：四舍五入指定小数的值。
ROUND(arg1)：四舍五入保留整数。
TRUNC(arg1,arg2)：截断指定小数的值，不做四舍五入处理。
MOD(arg1,arg2)：取余。
4.4日期函数
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 日期当月的最后一天
4.5转换函数
DATE_FORMAT(date,format) 将日期转换成字符串（类似 oracle 中的 to_char()）
STR_TO_DATE(str,format) 将字符串转换成日期（类似 oracle 中的 to_date()）
示例一
向 employees 表中添加 hire_date 列 类型为 date 类型
alter table employees add column hire_date date
示例二
向 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 日'))
示例三
查询 employees 表中雇员名字为 King 的雇员的入职日期，要求显示格式为 yyyy 年 MM月 dd 日。
select DATE_FORMAT(hire_date,'%Y 年%m 月%d 日') from employees where last_name ='King
4.6 通用函数
IFNULL(expr1,expr2) 判断 expr1 是否为 null，如果为 null，则用 expr2 来代替 null（类似 oracle 的 NVL()函数）
NULLIF(expr1,expr2)
IF(expr1,expr2,expr3)
COALESCE(value,...) 判断 value 的值是否为 null，如果不为 null，则返回 value；如果为 null，则判断下一个 value 是否为 null……直至出现不为 null 的 value 并返回或者返回最一个为 null 的 value
CASE WHEN THEN ELSE END
4.7 MySQL支持left outr join 和 right outer join 但不支持full outer join
全外链接
注意：MySQL 中不支持 FULL OUTER JOIN 连接，可以使用 union 实现全完连接。
4.7.1UNION
可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了distinct。
4.7.2 UNION ALL
只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。
4.7.3 语法结构
SELECT 投 影 列 FROM 表 名 LEFT OUTER JOIN 表 名 ON 连 接 条 件 UNION
SELECT 投影列 FROM 表名 RIGHT OUTER JOIN 表名 ON 连接条件
示例
查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门以及没有部门的雇员。
(select e.last_name,d.department_name from employees e LEFT OUTER JOIN departments d on e.dept_id = d.department_id) UNION (select e1.last_name,d1.department_name from employees e1 RIGHT OUTER JOIN departments d1 on d1.department_id = e1.dept_id)
5子查询
可以将子查询放在许多的 SQL 子句中，包括：
• WHERE 子句
• HAVING 子句
• FROM 子句
5.1使用子查询的原则
• 子查询放在圆括号中。
• 将子查询放在比较条件的右边。
• 在单行子查询中用单行运算符，在多行子查询中用多行运算符。
示例
select em.last_name,em.salary from employees em where em.salary > (select e.salary fromemployees e where e.last_name = 'Oldlu')
