MySQL基础篇【第五篇】| union、limit、DDL、DML、约束

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' union
select ename,job from emp where job='SALESMAN';
```
（2）注：对于两张不相干的表中的数据拼接在一起显示，只能用unoin！
```
select ename from emp 
union
select 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, pageSize

注：pageSize是每页显示多少条记录，pageNo是显示第几页！
```
java代码{
    int pageNo = 2; -- 页码是2
    int pageSize = 10; -- 每页显示10条
    limit (pageNo - 1) * pageSize, pageSize
}
```
MySQL8.0新特性：limit...offset....

例：查询emp表中第6,7条数据

方法一：下标从0开始，第6条就对应着下标5，显示6,7表示显示2条数据；所以是limit5,2，5代表从下标5开始，2表示偏移量！
```
select * from emp limit 5,2;
```
方法二：使用MySQL8的新特性，limit...offset；前一个参数是要查询的数据个数，后面才是偏移量，与前面的参数调过来即可！
```
select * from emp limit 2 offset 5;
```
三：表结构（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流才能插入！

（6）表名在数据库当中一般建议以：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;
```
（7）表的复制

语法：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

非空约束，表示当前字段不能为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

第一种写法：联合写【表级约束】

例如：unique(username,password) 联合写，表示两个字段连起来不能重复，但是可以重复其中一个字段；属于表级约束！
```
--删除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;
```
第二种写法：分开写【列级约束】

例如：username unique ,password unique 分开写，表示任何一个字段都不能重复；属于列级约束！
```
--删除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（非空约束not null），也不能重复（唯一约束unique）！

主键相关的术语？
①主键约束 : primary key
②主键字段 : id字段添加primary key之后，id叫做主键字段
③主键值 : id字段中的每一个值都是主键值。
主键有什么作用？
①表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
②主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样。）
主键的分类？
根据主键字段的字段数量来划分：
①单一主键（推荐的，常用的）
②复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式）
根据主键性质来划分：
①自然主键：主键值最好就是一个和业务没有任何关系的自然数；这种方式是推荐的。
②业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。不推荐用；不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

结论：一张表的主键约束只能有1个！

（1）给username例加上主键约束

列级约束：username varchar(255) primary key, 这样就属于列级约束

表级约束：password varchar(255)，primary key(password)，这样就属于表级约束
```
--删除t_user表
drop table if exists t_user; 
--建表 
create table t_user( 
   id int,
   username varchar(255) primary key, --列级约束
   password varchar(255) 
);
-- 插入数据
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

外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段一般来源于某张表的主键(至少有unique约束，可以为NULL)!

（1）请设计数据库表，用来维护学生和班级的信息

第一种方式：写成一张表，数据很冗余
```
no(pk)			name			classno			classname
--------------------------------------------------------------------------------------
1				zs1				101				 高一（1）班
2				zs2				101				 高一（1）班
3				zs3				102				 高一（2）班
4				zs4				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				 
 
-- 如果classno不添加外键约束fk，可以写随意值；
-- 添加了外检约束fk，只能写外键约束里的值，写其他的会报错
```
（1）将以上表的建表语句写出来： t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表；t_class表叫做父表。

（2）顺序要求：
删除数据的时候，先删除子表，再删除父表。

删除表的时候，先删除子表，在删除父表。
添加数据的时候，先添加父表，在添加子表。
创建表的时候，先创建父表，再创建子表。
```
-- 删除表：先删除子表t_student，在删除父表t_calss
drop table if exists t_student; 
drop table if exists t_class; 
-- 创建表：先创建父表t_calss，在创建字表t_student
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) --添加外键约束
        
);
 
 
-- 插入数据：先插入父表t_calss，在插入字表t_student
insert 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;
```
外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
答：被引用的字段不一定是主键，但至少具有unique约束！
相关阅读:
一本通1073；津津的储蓄计划
 JVM概念
 【Linux】网络协议：（序列化和反序列化）json 的安装和简单使用
 pytorch初学笔记（一）：如何加载数据和Dataset实战
 导入发运地点wsh_locations
制作自己的前端组件库并上传到npm上
 rhcsa5（日志、维护准确时间）
在Github上封神的JDK源码，看完竟吊打了面试官，厉害了
 采集SEO方法-添加关键词
 [计算机网络]IP协议
原文地址：https://blog.csdn.net/m0_61933976/article/details/126323003

一：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