约束
创建表时，添加相应约束
非空约束
不能为null
create table t_user( id int, username varchar(255) not null, password varchar(255));
唯一约束
关键字：unique
单字段，列级约束
不能重复，但可以为NULL。可以认NULL和NULL不一样。
drop table if exists t_user; create table t_user( id int, username varchar(255) unique);
多字段唯一，表级约束
drop table if exists t_user; create table t_user( id int, username varchar(255), phonenum int,unique(username,phonenum));
可以看成将两个字段合并为一个组，组不能重复。
主键约束
关键字：primary key
既不能为null，也不能重复
单一主键
drop table if exists t_user; create table t_user( id int primary key, username varchar(255),email varchar(255));
第二行由于主键约束，运行失败。
insert into t_user values(1,'zs','zs@126.com'),(2,'ls','ls126.com'); insert into t_user values(2,'ww','ww@126.com');
你可以尝试一下，id 为 null 是不是不能插入。
复合主键、业务主键
和unique表级约束语法差不多，primary key(字段1，字段 2)
不推荐，可能产生依赖或业务改变造成影响
自然主键
推荐，使用自然数1、2、3。。。，使用关键字auto_increment
id int primary key auto_increment
外键约束
为解决数据冗余问题，将一张表的主键作为另一张表的外键，用于select时的表连接。
建一个学生表和班级表，学生表有一个外键班号，来自班级表
drop table if exists t_student; drop table if exists t_class; create table t_class( cno int, cname varchar(255), primary key(cno) ); create table t_student( sno int, sname varchar(255), classno int, foreign key(classno) references t_class(cno));
insert into t_class values(101,'xxxxxxxxxxxxxxx'); insert into t_class values(102,'yyyyyyyyyyyyyyy'); insert into t_student values(1,'zs1',101); insert into t_student values(2,'zs21',101); insert into t_student values(3,'zs4',102); insert into t_student(sno,sname) values(4,'zs5'); insert into t_student values(5,'zs6',103);
最后一句无法运行，因为103不在表t_class中。
外键可以为NULL，可以理解为，学校来了个转学的学生，先添加进来，再分配班级。
下一节使用Navicat，有一些提示，一些函数名字过长，总是手打，浪费时间。
常见函数
语法
select 函数名() [from 表名]
mysql相关
database()
返回当前使用数据库
version()
返回mysql版本
user()、system_user()、session_user()
返回当前用户
单行处理函数
字符函数
长度相关
返回字符串字节长度
length(s)
s:字符串
一个汉字是算三个字节，一个数字或字母算一个字节。这是在utf-8字符集下。
char_length(s)
s:字符串
不管汉字还是数字或者是字母都算是一个字节。
字符串拼接
CONCAT(s1,s2...sn) 字符串 s1,s2 等多个字符串合并为一个字符串
CONCAT_WS(x, s1,s2...sn) 同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符
大小写
UPPER(s) 将字符串转换为大写
LOWER(s) 将字符串 s 的所有字母变成小写字母
查询员工表员工名，小写展示
SELECT LOWER(ename) 员工名 from emp;
子串
SUBSTR(s, start [,length]) 从字符串 s 的 start 位置截取长度为 length 的子字符串，位置从1开始。
SUBSTRING、MID 一样
SUBSTRING_INDEX(s, delimiter, number) 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。
如果 number 是正数，返回第 number 个字符左边的字符串。
如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。
例如，查询'lady_killer9'中'_'左面的子串，'lady_killer9'中第二个'l'右边的子串
SELECT SUBSTRING_INDEX('lady_killer9','_',1),SUBSTRING_INDEX('lady_killer9','l',-2);
去空格
LTRIM(s) 去掉字符串 s 开始处的空格
RTRIM(s) 去掉字符串 s 结尾处的空格
TRIM(s) 去掉字符串 s 开始和结尾处的空格
填充
LPAD(s1,len,s2) 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len
RPAD(s1,len,s2) 在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len
注：最终长度必为len，不够填充，多了截断
替换
REPLACE(s,s1,s2) 将字符串 s2 替代字符串 s 中的字符串 s1
前面去空格的时候，“Hello World”中间的空格没有去掉，可以使用replace进行替换
索引/位置
LOCATE(s1,s) 从字符串 s 中获取 s1 的开始位置
POSITION(s1 IN s) 从字符串 s 中获取 s1 的开始位置
比较字符串
STRCMP(s1,s2) 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1
数学函数
取整
最近取整(四舍五入)
ROUND(x[,d]) 返回离 x 最近的整数，d为小数点后保留的位数，若有d，则结果为小数
向上取整
CEIL(x) 返回大于或等于 x 的最小整数
向下取整
FLOOR(x) 返回小于或等于 x 的最大整数
截断
TRUNCATE(x,y) 返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）
取余
MOD(x,y) 返回 x 除以 y 以后的余数，和%作用一样
日期函数
NOW() 返回当前日期和时间
CURDATE() 返回当前日期，CURRENT_DATE()一样
想想之前的内容，我们可以通过NOW函数和截断来得到结果，先不要看下方截图，自己想一下。
再使用一下函数
CURTIME()，CURRENT_TIME 返回当前时间
还可以获取年、月、日、时、分、秒，分别是YEAR、MONTH、DAY、HOUR、MINUTE、SECOND
STR_TO_DATE(string, format_mask) 将字符串转变为日期
注意：格式需要对应，可以使用数字，英文，英文简写
时分秒分别使用%H(24小时制)/%h(12小时制)、%i、%s
DATE_FORMAT(d,f) 按表达式f的要求显示日期 d
流程控制函数
IF(expr,v1,v2) 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。类似三目运算符。
IFNULL(v1,v2) 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。
查询的员工名及本月可以得到的所有钱（基本工资+奖金），并备注有无奖金
SELECT ename,(sal+ifnull(comm,0)) 'money', IF(comm is NULL,'没奖金,呵呵','有奖金,哈哈') 备注 from emp;
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END
CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回ELSE后的result，而当有一个成立之后，后面的就不执行了。
查询员工名，部门号，工资
如果部门号为10，工资显示1.1倍
如果部门号为20，工资显示1.2倍
其他部门编号，工资显示1.3倍
SELECT ENAME,DEPTNO,SAL, CASE DEPTNO WHEN 10 THEN SAL*1.1 WHEN 20 THEN SAL*1.2 ELSE SAL*1.3 END 新工资 FROM emp ORDER BY DEPTNO;
多行处理函数
分组查询的时候讲过了分组函数，总结一下
- sum、avg 一般用于处理数值型
- max、min、count可以处理任何类型
- 分组函数都忽略null值
- MYISAM 存储引擎下，COUNT(*)的效率高，INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多
还有标准差STD这些，用的不多。
存储引擎
我们之前见过存储引擎的
建表时的存储引擎
所有存储引擎
MyISAM
灵活的AUTO_INCREMENT 字段处理
可被转换为压缩、只读表来节省空间
缺点：不支持事务
使用三个文件表示每个表：
- 格式文件-存储表结构的定义（xxx.frm)
- 数据文件-存储表行的内容（xxx.MYD)
- 索引文件-存储表上索引（xxx.MYI）
在安装目录下的data文件夹下有根据数据库分类的文件夹，进入mysql目录可以看到自带的mysql数据库的一些表。
mysql安装路径\data\mysql
InnoDB
优点：支持事务、行级锁等，数据安全得到保证。
InmoDB存储引擎是MySQL的缺省引擎。
它管理的表具有下列主要特征：
每个lnnoDB表在数据库目录中以.frm格式文件表示
InnoDB表空间 tablespace 被用于存储表的内容，无法压缩
提供一组用来记录事务性活动的日志文件
用COMMIT(提交)、SAVEPOINT(保存点)及ROLLBACK(回滚）支持事务处理
提供全ACID兼容
在MySQL 服务器崩溃后提供自动恢复
多版本(MVCC)和行级锁定
支持外键及引用的完整性，包括级联删除和更新
Memory
原来叫HEPA
缺点：不支持事务。数据容易丢失。查询速度最快
MEMORY存储引擎管理的表具有下列特征：
- 在数据库目录内，每个表均以.frm格式的文件表示。
- 表数据及索引被存储在内存中。
- 表级锁机制。
- 不能包含TEXT 或BLOB字段。
未完待续...
事务
事务：不可再分的业务逻辑单元，例如，银行转账，A用户给B用户转1000元，需要 update A用户的余额-1000，同时 update B用户的余额+1000，两个语句必须同时成功或失败。
开启事务->执行记录到操作历史->提交或回滚（事务结束）
TCL语句，
commit; 提交
rollback; 回滚
四大特性
A（atomicity)： 原子性：事务是最小的工作单元，不可再分。
C（consistency)：一致性：事务必须保证多条DML语句同时成功或者同时失败。
I（isolation）：隔离性：事务A与事务B之间具有隔离。
D（durability）：持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。
隔离性
第一级别：读未提交（read uncommitted)
对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
查看全局事务级别
select @@global.tx_isolation;
设置全局事务级别为 读未提交
set global transaction isolation level read uncommitted;
开启事务
start transaction;
插入id为3的记录的事务还未提交，但是却可以读到数据。
第二级别：读已提交（read committed)
对方事务提交之后的数据我方可以读取到。oracle数据库默认。
这种隔离级别解决了：脏读现象没有了。
读已提交存在的问题是：不可重复读。
设置全局事务级别为 读已提交
set global transaction isolation level read committed;
注意，要开两个控制台，不然未提交的还会显示。
id为3的数据提交之后才可以读。
第三级别：可重复读（repeatable read)
这种隔离级别解决了：不可重复读问题。mysql数据库默认。
这种隔离级别存在的问题是：读取到的数据是幻象。
设置全局事务级别为 可重复读
set global transaction isolation level repeatable read;
id为3的数据删除，且已提交。 但是仍可读取已删除了的id为3的数据。
第四级别：序列化读/串行化读(serializable)
解决了所有问题，但效率低。因为需要事务排队，必须一个事务commit之后，其他事务才能继续。
资源
lady_killer.sql以及参考手册等资源
提取码: xbkm
