MySQL基础篇【第五篇】| union、limit、DDL、DML、约束
✅作者简介：大家好我是@每天都要敲代码，希望一起努力，一起进步！
📃个人主页：@每天都要敲代码的个人主页
🔥系列专栏：MySQL专栏
💬推荐一款模拟面试、刷题神器，从基础到大厂面试题 👉点击跳转刷题网站进行注册学习
目录
一：union的使用
二：limit及通用分页SQL（重要）
三：表结构（DDL）
1. 创建表（creat）
2. 修改表（alter）
3. 删除表（drop）
四：处理表中数据（DML）
1. 插入表中的数据 insert
2. 修改表中的数据 update
3. 删除表中的数据 delete & truncate
五：约束
1. 非空约束 not null
2. 唯一约束 unique
3. 主键约束 primary key
4. 外键约束 foreign key
结束语
一：union的使用
union：可以将查询结果集相加，合并集合
（1）查询job包含MANAGER和包含SALESMAN的员工
方法一：使用or操作符
select ename,job from emp where job='MANAGER' or job = 'SALESMAN';
方法二：使用in操作符
select ename,job from emp where job in ('MANAGER','SALESMAN');
方法三： 采用union来合并
select ename,job from emp where job='MANAGER' unionselect ename,job from emp where job='SALESMAN';
（2）两张不相干的表中的数据拼接在一起显示；只能用unoin
select ename from emp unionselect dname from dept;-- select ename,dname from emp,dept; --笛卡尔现象 
（3）合并结果集的时候，需要查询字段对应个数相同（列数相等）
在Oracle中更严格，不但要求个数相同，而且还要求类型对应相同。
mysql> select ename,sal from emp -- 两列 -> union -> select dname from dept; -- 一列-- ERROR 1222 (21000): The used SELECT statements have a different number of columns二：limit及通用分页SQL（重要）
（1）limit是mysql特有的，主要用于分页查询，其他数据库中没有，不通用！
（2）MySQL提供了limit ，主要用于提取前几条或者中间某几行数据
（3）语法：limit startIndex, length
startIndex表示起始位置，从0开始，0表示第一条数据
length表示取几个（4）limit是sql语句最最后执行的一个环节：
select 5 ... from 1 ... where 2 ... group by 3 ... having 4 ... order by 6 ... limit 7 ...;（5）例如：select * from table limit m,n; 其中m是指记录开始的index，从0开始，表示第一条记录，n是指从第m+1条开始，取n条！
select * from tablename limit 2,4-- 即取出第3条至第6条，4条记录-- 下标是从0开始的，2对应着第3条数据（1） 取得emp前5条数据
select * from emp limit 0,5;select * from emp limit 5; --省略了0,默认是从下表为0出开始
（2）从emp第二条开始取两条数据
select * from emp limit 1,2;
（3）取得薪水最高的前5名
思路：先降序，在使用limit截取前5条
select * from emp order by sal desc limit 5;
（4）通用的标准分页SQL，例如：
每页显示pageSize条记录：
第pageNo页有多少记录：(pageNo - 1) * pageSize, pageSizepageSize是每页显示多少条记录
pageNo是显示第几页
java代码{ int pageNo = 2; -- 页码是2 int pageSize = 10; -- 每页显示10条 limit (pageNo - 1) * pageSize, pageSize}三：表结构（DDL）
1. 创建表（creat）
创建表的时候，表中有字段，每一个字段有：
* 字段名
* 字段数据类型
* 字段长度限制
* 字段约束，后面会讲
（1）建表语句的语法格式：
create table 表名( 字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型, .... );（2）关于MySQL当中字段的数据类型？以下只说常见的
int 整数型(java中的int)
bigint 长整型(java中的long)
float 浮点型(java中的float double)
char 定长字符串(String)
varchar 可变长字符串(StringBuffer/StringBuilder)
date 日期类型 （对应Java中的java.sql.Date类型）
BLOB 二进制大对象（存储图片、视频等流媒体信息）Binary Large OBject （对应java中的Object）
CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串。）Character Large OBject（对应java中的Object）
（3） varchar 和 char 区别
一、长度是否可变varchar 类型的长度是可变的，而 char 类型的长度是固定的。
char 类型是一个定长的字段，以 char(10) 为例，不管真实的存储内容多大或者是占了多少空间，都会消耗掉 10 个字符的空间
二、存储长度
char 长度最大为 255 个字符，varchar 长度最大为 65535 个字符
三、检索效率方面
varchar 类型的查找效率比较低，而 char 类型的查找效率比较高
（4）char和varchar怎么选择
在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的例如：性别、生日等都是采用char。
当一个字段的数据长度不确定的例如：简介、姓名等都是采用varchar。
（5）BLOB和CLOB类型的使用
例如：电影表，包括序号id(int)、电影名name(varchar)、上映时间playtime(date/char)
海报haibao(是一张图片用BLOB)、故事情节history(CLOB)
注意：像int、varchar、date/char可以用insert直接插入；但是BLOB、CLOB需要用IO流才能插入
（1） 表名在数据库当中一般建议以：t_或者tbl_开始。
例如：创建学生表：
学生信息包括：学号、姓名、性别、班级编号、生日
学号：bigint
姓名：varchar
性别：char
班级编号：varchar
生日：char
create table t_student( no bigint, name varchar(255), sex char(1), classno varchar(255), birth char(10));
show tables; 
（2）表的复制
语法：create table 表名 as select语句; 将查询结果当做表创建出来
create table t_emp as select * from emp; --as可省略-- 把emp表中查询的结果，插入到新表中；相当于复制
t_emp表中的数据和emp表中数据完全一样，相当于复制！
2. 修改表（alter）
对于表结构的修改，大家使用工具完成即可，因为在实际开发中表一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使需要修改表结构，我们也可以直接使用工具操作；修改表结构的语句不会出现在Java代码当中。
例如：以t_user表作为操作对象
（1）添加字段，alter table 表名 add 字段名 类型
alter table t_user add paswword varchar(255);select * from t_user;
（2）修改字段
alter table 表名 modify 字段名 类型
alter table 表名 change 原字段 新字段 类型;
假如：把paswword的大小设置为100
alter table t_user modify paswword varchar(100);desc t_user;
假如：把paswword改为password
alter table t_user change paswword password varchar(100);select * from t_user;
（3）删除字段，alter table 表名 drop 字段名
alter table t_user drop password;select * from t_user;
3. 删除表（drop）
drop table if exists t_student; --如果t_student这个表存在就删除它，orcale不支持drop table t_student; --比较通用
四：处理表中数据（DML）
插入、修改和删出都属于DML，主要包含的语句：insert、update、delete
增删改查有一个术语：CRUD操作
Create（增） Retrieve（检索） Update（修改） Delete（删除）
1. 插入表中的数据 insert
语法格式： insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....)
要求：字段的数量和值的数量相同，并且数据类型要对应相同。
insert into t_student (no,name,sex,classno,birth) values (1,'zhangsan','1','yanyi','1999-1-14);
（1）这里顺序可以颠倒，但是一定要把对上，例如：把no放到最后一列
insert into t_student (name,sex,classno,birth,no) values ('wangwu','2','gaozhong','2000-1-14',3);
（2）也可以只插入一个字段，其它字段会默认为空
insert into t_student (name) values ('zhaoliu');
（3）插入的字段默认值都是NULL，我们也可以修改它的默认值
desc t_student;--默认值都是NULL，我们插入时，如果没有指定的数据都是NULL
修改默认值，我们重新创建一张表t_s，修改sex默认值是1，那么以后在创建表，只要这个字段没有给定值，默认就是1，而不是NULL
create table t_s( no bigint, name varchar(255), sex char(1) default 1, --指定默认值 classno varchar(255), birth char(10));
在插入一组数据，不指定sex字段的值，默认就是1，而不是NULL
insert into t_s (name) values ('zl');
（4）我们也可以把前面的字段省了，但是此时例的数量和顺序都不能错，有几列就写几列
insert into t_s values (1,'gh','2','dasi','1999-06-04');
（5）还可以一次插入多个值，中间用逗号分开
insert into t_s (no,name,sex,classno,birth) values (2,'zhangsan','1','chuzhong','2000-2-3'),(3,'lisi','2','gaozhong','2001-2-3');
（6）将查询的结果插入到一张表中，完成批量插入！
语法：insert into 表名 select语句; 将查询结果插入到一个已经存在的表中
insert into t_student select * from t_s;-- 将后面查询的结果插入到 t_student表中
2. 修改表中的数据 update
语法格式：update 表名 set 字段名1=值1 , 字段名2=值2... where 条件;
注意：没有where条件整张表数据全部更新！
（1）将t_dept表中的，部门为10的LOC修改为SHANGHAI，将部门名称修改为RENSHIBU
update t_dept set DNAME='RENSHIBU',LOC='SHANGHAI' where deptno=10;
（2）更新dname的所有数据；不加条件就行
update t_dept set dname = 'zl';-- 没有where条件，表示把所有的dname改成zl
3. 删除表中的数据 delete & truncate
语法格式：delete from 表名 where 条件;
注意：没有条件全部删除。
（1）删除10部门数据
delete from t_dept where deptno=10;
（2）删除所有记录
delete from t_dept;
（3）删除大表中的数据 truncate
注：使用delete删除表，很慢，但是可以回滚找回数据；
对于大数据表的删除使用truncate，删除之后就找不到了
truncate table t_emp; -- 表被截断，不可回滚，永久丢失
五：约束
什么是约束（Constraint）？常见的约束有哪些呢？
（1）在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
（2）常见的约束有哪些呢？
非空约束(not null)：约束的字段不能为NULL
唯一约束(unique)：约束的字段不能重复
主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）
外键约束(foreign key)：...（简称FK）
检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。
1. 非空约束 not null
（1）先创建一个用户表，并添加username非空约束
create table t_user( id int, username varchar(255) not null, --非空约束 password varchar(255));在插入数据，我们把username字段不设置，默认为空，就会报错！
insert into t_user (id,password) values (1,'123');
username给上初始值，就完全没问题
insert into t_user (id,username,password) values (1,'zahngsan','123');
2. 唯一约束 unique
唯一约束修饰的字段具有唯一性，不能重复；但可以为NULL
（1）给name列添加unique，表示姓名不可重复
--删除t_user表drop table if exists t_user; --建表create table t_user( id int, username varchar(255) unique, --名字不能重复 password varchar(255) );--插入数据insert into t_user values (1,'zhangsan','123');insert into t_user values (2,'zhangsan','456'); --再次插入相同姓名，就会报错--ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username' 
（2）但是可以把名字都使用默认的NULL，NULL和NULL之间是不能直接用等号连接的
insert into t_user (id,password) values (2,'123');insert into t_user (id,password) values (3,'456');
（3）给两个列或者多个列添加unique
第一种写法：联合写【表级约束】
--删除t_user表drop table if exists t_user; --建表create table t_user( id int, username varchar(255), password varchar(255), unique(username,password) --联合写，表示两个字段连起来不能重复，但是可以重复其中一个 );-- 插入数据insert into t_user values(1,'zs','123');insert into t_user values(2,'zs','456');insert into t_user values(3,'lisi','123');select * from t_user;
第二种写法：分开写 【列级约束】
--删除t_user表drop table if exists t_user; --建表 create table t_user( id int, username varchar(255) unique, password varchar(255) unique --分开写，任何一个都不能重复);-- 插入数据insert into t_user values(1,'zs','123');insert into t_user values(2,'zs','456'); --会报错insert into t_user values(3,'lisi','123'); --会报错
3. 主键约束 primary key
每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的
主键约束 primary key 约束的字段既不能为NULL，也不能重复
主键相关的术语？
主键约束 : primary key
主键字段 : id字段添加primary key之后，id叫做主键字段
主键值 : id字段中的每一个值都是主键值。
主键有什么作用？
表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样。）
主键的分类？
根据主键字段的字段数量来划分：
单一主键（推荐的，常用的。）
复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。）
根据主键性质来划分：
自然主键：主键值最好就是一个和业务没有任何关系的自然数。这种方式是推荐的
业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。不推荐用；不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。一张表的主键约束只能有1个！
（1）给username例加上主键约束
--删除t_user表drop table if exists t_user; --建表 create table t_user( id int, username varchar(255) primary key, --列级约束 password varchar(255) -- primary key(username)，这样就属于表级约束);-- 插入数据insert into t_user values(1,'zs','123');insert into t_user values(2,'zs','234'); --err，username字段不能相等insert into t_user (id,password) values(2,'234'); --err，username字段不能为NULL 
根据以上的测试得出：username是主键，因为添加了主键约束，主键字段中的数据不能为NULL，也不能重复。
（2）主键值自增 auto_increment
注：Oracle当中也提供了一个自增机制，叫做：序列（sequence）对象。
--删除t_user表drop table if exists t_user; --建表 create table t_user( id int primary key auto_increment, username varchar(255) );-- 插入数据insert into t_user (username) values ('a'); -- 不用写id，会自增生成insert into t_user (username) values ('b');insert into t_user (username) values ('c');select * from t_user;
4. 外键约束 foreign key
外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的主键
（1）请设计数据库表，用来维护学生和班级的信息
第一种方式：写成一张表，数据很冗余
no(pk)nameclassnoclassname--------------------------------------------------------------------------------------1zs1101 高一（1）班2zs2101 高一（1）班3zs3102 高一（2）班4zs4102 高一（2）班第二种方式：写成两张表
班级表：t_class
cno(pk)cname-------------------------------------------------------101 高一（1）班101 高一（1）班102 高一（2）班102 高一（2）班学生表：t_student
sno(pk)snameclassno(该字段添加外键约束fk)-------------------------------------------1zs11012zs2101 3zs3102 4zs4102 -- 如果classno不添加外键约束fk，可以写随意值；-- 添加了外检约束fk，只能写外键约束里的值，写其他的会报错（1）将以上表的建表语句写出来： t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表；t_class表叫做父表。
（2）顺序要求：
删除数据的时候，先删除子表，再删除父表。
添加数据的时候，先添加父表，在添加子表。
创建表的时候，先创建父表，再创建子表。
删除表的时候，先删除子表，在删除父表。
-- 删除表：先删除子表t_student，在删除父表t_calssdrop table if exists t_student; drop table if exists t_class; -- 创建表：先创建父表t_calss，在创建字表t_studentcreate 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) --添加外键约束 );-- 插入数据：先插入父表t_calss，在插入字表t_studentinsert into t_class values (101,'Class 1, senior 1');insert into t_class values (102,'Class 1, senior 2');insert into t_student values (1,'zl1','101');insert into t_student values (2,'zl2','101');insert into t_student values (3,'zl3','102');insert into t_student values (4,'zl4','102');-- insert into t_student values (5,'zl5','103'); -- 会报错，因为添加了外键约束，classno只能来自cno的范围select * from t_student;select * from t_class;我们可以把上面的sql语句注释去掉，放到一个文本文件里，然后改成.sql结尾；最后用source就可以执行啦！
（2）外键值可为NULL
insert into t_student (sno,sname) values (5,'zl5');select * from t_student;
（3）外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
注意：被引用的字段不一定是主键，但至少具有unique约束。
结束语
今天的分享就到这里啦！快快通过下方链接注册加入刷题大军吧！各种大厂面试真题在等你哦！
💬刷题神器，从基础到大厂面试题👉点击跳转刷题网站进行注册学习
