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

✅作者简介：大家好我是@每天都要敲代码，希望一起努力，一起进步！
📃个人主页：@每天都要敲代码的个人主页
🔥系列专栏：MySQL专栏
💬推荐一款模拟面试、刷题神器，从基础到大厂面试题 👉点击跳转刷题网站进行注册学习

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

三：表结构（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)            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）班
            101                 高一（1）班
            102                 高一（2）班
            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;

（3）外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
注意：被引用的字段不一定是主键，但至少具有unique约束。

结束语

今天的分享就到这里啦！快快通过下方链接注册加入刷题大军吧！各种大厂面试真题在等你哦！
💬刷题神器，从基础到大厂面试题👉点击跳转刷题网站进行注册学习