一.MySQL 中的约束
1.约束类型
非空约束(not null)
唯一性约束(unique)
主键约束(primary key) PK
外键约束(foreign key) FK
检查约束(目前 MySQL 不支持、Oracle 支持)
2.创建表时添加约束
查询表中的约束信息
SHOW KEYS FROM 表名
示例
创建 departments 表包含 department_id 该列为主键且自动增长，department_name 列不 允许重复，location_id 列不允含有空值。
create table departments(department_id int primary key auto_increment,dep
artment_name varchar(30) unique,location_id int not null);
3.修改表实现约束的添加与删除
1.主键约束
添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(列名)
示例
将 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
注意：删除主键时，如果主键列具备自动增长能力，需要先去掉自动增长，然后在删除 主键。
示例
删除 employee_id 的主键约束。
去掉自动增长： alter table emp modify employee_id int;
删除主键：alter table emp drop primary key;
2.非空约束
添加非空约束
ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL
示例
向 emp 表中的 salary 添加非空约束。
alter table emp modify salary float(8,2) not null;
删除非空约束
ALTER TABLE 表名 MODIFY 列名 类型 NULL
示例
删除 salary 的非空约束。
alter table emp modify salary float(8,2) null;
3.唯一约束
添加唯一约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名)
示例
向 emp 表中的 name 添加唯一约束。
alter table emp add constraint emp_uk unique(name);
删除唯一约束
ALTER TABLE 表名 DROP KEY 约束名6.3.3.2.1示例
删除 name 的唯一约束。
alter table emp drop key emp_uk;
4.外键约束
添加外键约束
ALTER TABLE 表 名 ADD CONSTRAINT 约 束 名 FOREIGN KEY( 列 名 )
REFERENCES 参照的表名(参照的列名)
示例
修改 emp 表，添加 dept_id 列。
alter table emp add column dept_id int;
删除外键约束
删除外键:
ALTER TABLE 表名 DROP FOREIGN KEY 约束名
删除外键索引(索引名与约束名相同):
ALTER TABLE 表名 DROP INDEX 索引名
示例
删除 dept_id 的外键约束。
删除外键：alter table emp drop foreign key e_fk;
删除索引： alter table emp drop index e_fk;
二.MySQL 中的 DML 操作
1.添加数据(INSERT)
插入数据
选择插入
INSERT INTO 表名(列名 1，列名 2，列名 3.....) VALUES(值 1，值 2，值 3......)
示例
向 departments 表中添加一条数据，部门名称为 market，工作地点 ID 为 1。
insert into departments(department_name,location_id) values("market",1);
完全插入
INSERT INTO 表名 VALUES(值 1，值 2，值 3......)
如果主键是自动增长，需要使用 default 或者 null 或者 0 占位。
示例
向 departments 表中添加一条数据，部门名称为 development，工作地点 ID 为 2。使用
default 占位。
insert into departments values(default,"development",2);
2.自动增长(auto_increment)
MySQL 中的自动增长类型要求：
一个表中只能有一个列为自动增长。
自动增长的列的类型必须是整数类型。
自动增长只能添加到具备主键约束与唯一性约束的列上。
删除主键约束或唯一性约束，如果该列拥有自动增长能力，则需要先去掉自动增长然 后在删除约束。
示例
创建一个 emp2 表。包含 id 该列为主键，包含 name，包含 seq_num 要求该列为具备唯 一性约束，该列的值自动增长。
create table emp2(id int primary key ,name varchar(30),seq_num int unique
auto_increment);
3. 默认值处理
在 MySQL 中可以使用 DEFAULT 为字段设定一个默认值。如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。
创建表时指定列的默认值
示例
创建 emp3 表，该表包含 emp_id 主键且自动增长，包含 name，包含 address 该列默认 值为”未知”。
create table emp3(emp_id int primary key auto_increment,name varchar(30),
address varchar(50) default 'Unknown');
修改表添加列的默认值
示例
修改 emp3 表，添加 job_id 该列默认值为 0。
alter table emp3 add column job_id int default 0;
插入数据时的默认值处理
如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。如果是 完全项插入需要使用 default 来占位。
示例
向 emp3 表中添加数据，要求 address 列与 job_id 列使用默认值作为该列的值。
insert into emp3(name) values("admin");
insert into emp3 values(default,"oldlu",default,default);
4.更新数据(UPDATE)
UPDATE 表名 SET 列名=值，列名=值 WHERE 条件
示例
更新 emp3 表中的 id 为 1 的数据，添加 address 为 BeiJing。
update emp3 e set e.address = "BeiJing" where emp_id = 1;
5.删除数据(DELETE)
使用 DELETE 子句
DELETE FROM 表名 WHERE 条件
示例
删除 emp3 表中 emp_id 为 1 的雇员信息。
delete from emp3 where emp_id = 1
使用 TRUNCATE 清空表
TRUNCATE TABLE 表名
示例
删除 emp3 表中的所有数据
truncate table emp3;
6. DELETE 与 TRUNCATE 区别
truncate 是整体删除（速度较快）， delete 是逐条删除（速度较慢）；
truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete 高的原因；
truncate 是会重置自增值，相当于自增列会被置为初始值，又重新从 1 开始记录，而 不是接着原来的值。而 delete 删除以后，自增值仍然会继续累加。
三.�MySQL 中的事务处理
在 MySQL 中，默认情况下，事务是自动提交的，也就是说，只要执行一条 DML 语句
就开启了事物，并且提交了事务
关闭 MySQL 的事务自动提交
向 emp3 表中添加一条数据，要求手动提交事务。
四.MySQL 查询数据
1MySQL 的列选择
SELECT * | 投影列 FROM 表名
示例
查询 departments 表中的所有数据
select * from departments;
2.MySQL 的行选择
SELECT * | 投影列 FROM 表名 WHERE 选择条件
示例
查询 departments 表中部门 ID 为 4 的部门名称与工作地点 ID。
select department_name,location_id from departments where department_id =4;
3.SELECT 语句中的算术表达式
示例
修改 employees 表添加 salary。
alter table employees add column salary float(9,2);
4.MySQL 中定义空值
包含空值的算术表达式计算结果为空。
示例
在 employees 中添加 commission_pct，计算年薪包含佣金。
alter table employees add column commission_pct float(5,2);
select 12*salary*commission_pct from employees;
5.MySQL 中的列别名
SELECT 列名 AS 列别名 FROM 表名 WHERE 条件
示例
查询 employees 表将雇员 laser_name 列名改为 name。
select last_name as name from employees;
6.MySQL 中的连字符
MySQL 中并不支持||作为连字符，需要使用 concat 函数。在参数数量上与 oracle 的 concat 函数有区别。
示例
查询雇员表中的所有数据，将所有数据连接到一起，每列值中通过#分割。
select concat(employees_id,'#',last_name,'#',email,"#",salary,"#",commission_pct) from
employees;1.7MySQL 中去除重复
在 SELECT 语句中用 DISTINCT 关键字除去相同的行。
示例
查询 employees 表，显示唯一的部门 ID。
select distinct dept_id from employees;
7.模糊查询
like
%表示任意多个任意字符
_表示一个任意字符
示例
查询 employees 中雇员名字第二个字母是 e 的雇员信息。
select * from employees where last_name like '_e%'2.1.3逻辑运算符
8.范围查询
between ... and
in 表示在一个非连续的范围内
示例
查询 employees 表，薪水在 3000-8000 之间的雇员信息
select * from employees where salary between 3000 and 8000
9.空值判断
判断空 is null
判断非空 is not null
示例
找出 emloyees 表中那些没有佣金的雇员
select * from employees where commission_pct is null;
10. 用 ORDER BY 子句排序
示例
查询 employees 表中的所有雇员，薪水按升序排序。
select * from employees order by salary
五.MySQL 中常见的单行函数
1.大小写控制函数
LOWER(str) 转换大小写混合的字符串为小写字符串
UPPER(str) 转换大小写混合的字符串为大写字符串。
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 的字符串）
3.数字函数
ROUND(arg1,arg2)：四舍五入指定小数的值。
ROUND(arg1)：四舍五入保留整数。
TRUNC(arg1,arg2)：截断指定小数的值，不做四舍五入处理。
MOD(arg1,arg2)：取余。3.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 日期当月的最后一天
5.转换函数
DATE_FORMAT(date,format) 将日期转换成字符串（类似 oracle 中的 to_char()）
STR_TO_DATE(str,format) 将字符串转换成日期（类似 oracle 中的 to_date()）
六.�多表连接查询
1.等值连接
示例
查询雇员 King 所在的部门名称
select d.department_name from employees e,departments d where e.dept_id =
d.department_id and e.last_name = 'King'
2非等值连接
示例
创建 sal_level 表，包含 lowest_sal，highest_sal，level。
create table sal_level(lowest_sal int,highest_sal int ,level varchar(30))
3.自连接
示例
修改 employees 表，添加 manager_id 列
ALTER table employees add COLUMN manager_id int
4. 外连接(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())
右外连接(RIGHT OUTER JOIN)
示例
向 departments 表中添加一条数据，部门名称为 Testing，工作地点 ID 为 5。
insert into departments values(default,'Testing',5)
全外链接
注意：MySQL 中不支持 FULL OUTER JOIN 连接
可以使用 union 实现全完连接。
UNION
可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了
DISTINCT。
UNION ALL
只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数
语法结构
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 departmentsd 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)
�
