MySQL
1. union
1.1 union将查询结果集相加
案例：找出工作岗位是SALESMAN和MANAGER的员工？
第一种：
select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
第二种：
select ename,job from emp where job in('MANAGER','SALESMAN');
第三种：union
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';
1.2 将两张不相干的表中的数据拼接
select ename from emp
union
select dname from dept;
select ename,sal from emp
union
select dname from dept;
# 维度不一样的数据不能拼接
2. limit （分页查询）
2.1 limit 简介
limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）
2.2 limit 作用
limit取结果集 中的部分数据，这是它的作用。
2.3 语法机制
limit startIndex, length
startIndex表示起始位置，从0开始，0表示第一条数据。
length表示取几个
案例：取出工资前5名的员工（思路：降序取前5个）
select ename,sal from emp order by sal desc limit 0, 5;
select ename,sal from emp order by sal desc limit 5; # startIndex此处可以省略
2.4 limit的执行顺序
limit是sql语句最后执行的一个环节。
select 5
...
from 1
...
where 2
...
group by 3
...
having 4
...
order by 6
...
limit 7
...;
2.5 通用的标准分页sql
案例：找出工资排名在第4到第9名的员工？
select ename,sal from emp order by sal desc limit 3,6;
思考：如果查询的语句非常庞大，成千上万条，将查询的数据显示在一页上不现实。此时，就需要将数据分页显示。
每页显示3条记录：
第1页：0, 3
第2页：3, 3
第3页：6, 3
第4页：9, 3
第5页：12, 3
总结公式：
每页显示pageSize条记录：
第pageNo页：(pageNo - 1) * pageSize, pageSize
pageSize是什么？是每页显示多少条记录
pageNo是什么？显示第几页
3. 表
3.1 创建表
建表语句的语法格式：
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
....
);
3.2 MySQL中字段常见的数据类型
| 类型 | 描述 |
|---|---|
| Char(长度) | 定长字符串，存储空间大小固定，适合作为主键或外键 |
| Varchar(长度) | 变长字符串，存储空间等于实际数据空间 |
| double(有效数字位数，小数位) | 数值型 |
| Float(有效数字位数，小数位) | 数值型 |
| Int( 长度) | 整型 |
| bigint(长度) | 长整型 |
| Date 日期型 | 年月日 |
| DateTime 日期型 | 年月日 时分秒 毫秒 |
| time 日期型 | 时分秒 |
| BLOB | 二进制大对象（存储图片、视频等流媒体信息）Binary Large OBject（二进制大对象） |
| CLOB | 字符大对象（存储较大文本，比如，可以存储4G的字符串） Character Large OBject（字符大对象） |
| 其它… | … |
3.3 字段数据类型选择
【1】char和varchar怎么选择？
（1）在实际的开发中，当某个字段中的数据长度不发生改变的时候，
是定长的，例如：性别、生日等都是采用char。
（2）当一个字段的数据长度不确定，例如：简介、姓名等都是采用
varchar。
【2】BLOB和CLOB类型的使用
以下面的表举例
电影表: t_movie
| id(int) | name(varchar) | playtime(date/char) | haibao(BLOB) | history(CLOB) |
|---|---|---|---|---|
| 1 | 蜘蛛侠 | |||
| 2 | ||||
| 3 |
对于BLOB、CLOB类型来说，这二者确实可以存储大对象，但是数据库的资源本身就很宝贵，用来存储占空间特别大的图片、电影等数据属实有点浪费，一般将这些类型的数据存储到云盘中，表中保留是这些数据的路径。
注意：表名在数据库当中一般建议以：t_或者tbl_开始。
实例：创建学生表
学生信息包括：
学号、姓名、性别、班级编号、生日
学号：bigint
姓名：varchar
性别：char
班级编号：int
生日：char
create table t_student(
no bigint,
name varchar(255),
sex char(1),
classno varchar(255),
birth char(10)
);
3.3 表中插入数据insert
【1】语法格式：
insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....)
要求：字段的数量和值的数量相同，并且数据类型要对应相同。
【2】案例
（1）案例1
插入数据，字段名和values按顺序一一对应。
（2）案例2
插入数据，字段名和values一一对应，字段名-values的位置可以改变，只要对应就可以。
（3）案例3
插入数据，缺省的字段以NULL补充，当然，也可以自己指定缺省的字段内容。
需要注意的地方：
（1）当一条insert语句执行成功之后，表格当中必然会多一行记录。
（2）即使多的这一行记录当中某些字段是NULL，后期也没有办法在执行
insert语句插入数据了，只能使用update进行更新。
（3）字段可以省略不写，但是后面的value对数量和顺序都有要求。
（4）案例4 插入多条数据
3.4 表的复制
语法：
create table 表名 as select语句;
将查询结果当做表创建出来。
3.5 将查询结果放到一张表中
3.6 更新数据update
语法格式：
update 表名 set 字段名1=值1,字段名2=值2... where 条件;
注意：没有条件整张表数据全部更新。
案例：将部门10的LOC修改为SHANGHAI，将部门名称修改为RENSHIBU
3.7 删除数据delete
语法格式：
delete from 表名 where 条件;
注意：没有条件全部删除。
案例：删除10部门数据。
删除大表中的数据：
truncate table 表名; // 表被截断，不可回滚。永久丢失。
delete不会释放物理存储空间，数据可以通过回滚恢复；truncate释放数据
的物理存储空间，数据无法回滚，无法恢复。
删除表：
drop table 表名; // 这个通用。
drop table if exists 表名; // oracle不支持这种写法。
3.8 对表结构的操作
采用alter table来增加/删除/修改表结构，不影响表中的数据
3.8.1 添加字段add
语法格式：
alter table 表名 add 新字段 字段类型;
案例：向t_student中加入联系电话字段，字段名称为：contatct_tel 类型为varchar(40)
alter table t_student add contact_tel varchar(40);
3.8.2 修改字段
案例1：student_name无法满足需求，长度需要更改为100
案例2：sex字段名称感觉不好，用gender替换sex
3.8.3 删除字段
案例：删除联系电话字段
增删改查有一个术语：CRUD操作
Create（增） Retrieve（检索） Update（修改） Delete（删除）
3.9 约束
3.9.1 约束的概念
在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束
| 约束 | 含义 |
|---|---|
| 非空约束(not null) | 约束的字段不能为NULL |
| 唯一约束(unique) | 约束的字段不能重复 |
| 主键约束(primary key) | 约束的字段既不能为NULL，也不能重复（简称PK） |
| 外键约束(foreign key) | …（简称FK） |
| 检查约束(check) | 注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。 |
3.9.2 非空约束 not null
非空约束，针对某个字段设置其值不为空，如：学生的姓名不能为空。
drop table if exists t_user;
create table t_user(
id int,
username varchar(255) not null,
password varchar(255)
);
insert into t_user(id,password) values(1,'123');
ERROR 1364 (HY000): Field 'username' doesn't have a default value
insert into t_user(id,username,password) values(1,'lisi','123');
3.9.3 唯一性约束
唯一约束修饰的字段具有唯一性，不能重复。但可以为NULL。
案例1：给某一列添加unique
drop table if exists t_user;
create table t_user(
id int,
username varchar(255) unique // 列级约束
);
insert into t_user values(1,'zhangsan');
insert into t_user values(2,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'
insert into t_user(id) values(2);
insert into t_user(id) values(3);
insert into t_user(id) values(4);
案例2：给两个列或者多个列添加unique
drop table if exists t_user;
create table t_user(
id int,
usercode varchar(255),
username varchar(255),
unique(usercode,username) // 多个字段联合起来添加1个约束
// unique 【表级约束】
);
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
insert into t_user values(3,'222','zs');
select * from t_user;
insert into t_user values(4,'111','zs');
ERROR 1062 (23000): Duplicate entry '111-zs' for key 'usercode'
drop table if exists t_user;
create table t_user(
id int,
usercode varchar(255) unique,
username varchar(255) unique
);
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
ERROR 1062 (23000): Duplicate entry '111' for key 'usercode'
* 注意：not null约束只有列级约束。没有表级约束。
3.9.4 主键约束
【1】给一张表添加主键约束
drop table if exists t_user;
create table t_user(
id int primary key, // 列级约束
username varchar(255),
email varchar(255)
);
insert into t_user(id,username,email) values(1,'zs','zs@123.com');
insert into t_user(id,username,email) values(2,'ls','ls@123.com');
insert into t_user(id,username,email) values(3,'ww','ww@123.com');
select * from t_user;
insert into t_user(id,username,email) values(1,'jack','jack@123.com');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
insert into t_user(username,email) values('jack','jack@123.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
（1）id是主键，因为添加了主键约束，主键字段中的数据不能为NULL，也不能重复。
（2）主键的特点：不能为NULL，也不能重复。
【2】主键相关的术语
（1）主键约束 : primary key
（2）主键字段 : id字段添加primary key之后，id叫做主键字段
（3）主键值 : id字段中的每一个值都是主键值。
【3】主键有什么作用
（1）表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
（2）主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样。）
【4】主键的分类
（1）根据主键字段的字段数量来划分：
单一主键（推荐的，常用的。）
复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。）
（2）根据主键性质来划分：
自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）
最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。
【5】 一张表的主键约束只能有1个
【6】使用表级约束方式定义主键
drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
primary key(id)
);
insert into t_user(id,username) values(1,'zs');
insert into t_user(id,username) values(2,'ls');
insert into t_user(id,username) values(3,'ws');
insert into t_user(id,username) values(4,'cs');
select * from t_user;
insert into t_user(id,username) values(4,'cx');
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
【7】复合主键
drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
password varchar(255),
primary key(id,username)
);
insert .......
【8】mysql提供主键值自增
drop table if exists t_user;
create table t_user(
id int primary key auto_increment, // id字段自动维护一个自增的数字，从1开始，以1递增。
username varchar(255)
);
insert into t_user(username) values('a');
insert into t_user(username) values('b');
insert into t_user(username) values('c');
insert into t_user(username) values('d');
insert into t_user(username) values('e');
insert into t_user(username) values('f');
select * from t_user;
提示:Oracle当中也提供了一个自增机制，叫做：序列（sequence）对象。
3.9.5 外键约束
【1】关于外键约束的相关术语
（1）外键约束: foreign key
（2）外键字段：添加有外键约束的字段
（3）外键值：外键字段中的每一个值。
【2】业务背景：
请设计数据库表，用来维护学生和班级的信息？
第一种方案：一张表存储所有数据
| no(pk) | name | classno | classname |
|---|---|---|---|
| 1 | zs1 | 101 | 北京大兴区经济技术开发区亦庄二中高三1班 |
| 2 | zs2 | 101 | 北京大兴区经济技术开发区亦庄二中高三1班 |
| 3 | zs3 | 102 | 北京大兴区经济技术开发区亦庄二中高三2班 |
| 4 | zs4 | 102 | 北京大兴区经济技术开发区亦庄二中高三2班 |
| 5 | zs5 | 102 | 北京大兴区经济技术开发区亦庄二中高三2班 |
缺点：冗余。【不推荐】
第二种方案：两张表（班级表和学生表）
t_class 班级表
| cno(pk) | cname |
|---|---|
| 101 | 北京大兴区经济技术开发区亦庄二中高三1班 |
| 102 | 北京大兴区经济技术开发区亦庄二中高三2班 |
t_student 学生表
| sno(pk) | sname | classno(该字段添加外键约束fk) |
|---|---|---|
| 1 | zs1 | 101 |
| 2 | zs2 | 101 |
| 3 | zs3 | 102 |
| 4 | zs4 | 102 |
| 5 | zs5 | 102 |
将以上表的建表语句写出来：
t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表，t_class表叫做父表。
顺序要求：
删除数据的时候，先删除子表，再删除父表。
添加数据的时候，先添加父表，在添加子表。
创建表的时候，先创建父表，再创建子表。
删除表的时候，先删除子表，在删除父表。
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,
primary key(sno),
foreign key(classno) references t_class(cno)
);
insert into t_class values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');
insert into t_student values(1,'zs1',101);
insert into t_student values(2,'zs2',101);
insert into t_student values(3,'zs3',102);
insert into t_student values(4,'zs4',102);
insert into t_student values(5,'zs5',102);
insert into t_student values(6,'zs6',102);
select * from t_class;
select * from t_student;
insert into t_student values(7,'lisi',103);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.INT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
【3】外键可以为NULL
【4】外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
注意：被引用的字段不一定是主键，但至少具有unique约束。
