文章目录
表复制和去重
表复制
-- 为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据
create table my_tab01
(id int,
`name` varchar(32),
sal double,
job varchar(32),
deptno int);
DESC my_tab01
-- 演示如何自我复制
-- 1.先把emp 表的记录复制到 my_tab01
insert into my_tab01
(id,`name`,sal,job,deptno)
select empno,ename,sal,job,deptno from emp;
-- 2.自我复制
insert into my_tab01
select * from my_tab01;
-- 检查数据
select * from my_tab01
-- 检查数据行数
select count(*) from my_tab01;
表去重
-- 如何删除掉一张表重复记录
-- 1.先创建一张表 my_tab02
-- 2.让 my_tab02 有重复的记录
create table my_tab02 like emp; -- 把emp表的结构（列），复制到my_tab02
DESC my_tab02;
insert into my_tab02
select * from emp;
select * from my_tab02;
-- 3.去重
-- 思路
-- (1)先创建一张临时表 my_tmp,该表的结构和 my_tab02一样
create table my_tmp like my_tab02;
desc my_tmp;
-- (2)把my_tab02 的记录 通过distinct 关键字 处理后 把记录复制到my_tmp
insert into my_tmp
select distinct * from my_tab02;
-- (3)清除掉my_tab02 记录
delete from my_tab02;
select * from my_tab02;
-- (4)把my_tmp表的记录复制到my_tab02
insert into my_tab02
select * from my_tmp;
select * from my_tab02;
-- (5)drop 掉临时表 my_tmp
drop table my_tmp;
select * from my_tmp;
合并查询
介绍
有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，union all
union all
union all就是将两个查询结果合并，不会去重
-- 该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行
select ename,sal,job from emp where sal > 2500 union all select ename,sal,job from emp where job = 'manager';
union
该操作符与union all相似，但是会自动去掉结果集中重复行
-- 该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行
select ename,sal,job from emp where sal > 2500 union select ename,sal,job from emp where job = 'manager';
MySQL表外连接
- 前面我们学习的查询,是利用where子句对两张表或者多张表，形成的笛卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示
- 比如:列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门
外连接
左外连接
如果左侧的表完全显示我们就说是左外连接
右外连接
如果右侧的表完全显示我们就说是右外连接
外连接案例
案例表源码
-- 创建stu
create table stu (
id int,
`name` varchar(32)
);
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
select * from stu;
-- 创建exam
create table exam(
id int,
grade int
);
insert into exam values(1,56),(2,76),(11,8);
select * from exam;
案例练习1
-- 使用左连接(显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号，成绩显示为空)
-- 一般方法(发现有问题)
select `name`,stu.id,grade
from stu ,exam
where stu.id = exam.id;
-- 改为左外连接形式
select `name`,stu.id,grade
from stu left join exam
on stu.id = exam.id;
-- 使用右连接
-- 右外连接形式
-- 即：右边的表(exam)和左表没有匹配的记录，也会把右表的记录显示出来
select stu.id,`name`,grade
from stu right join exam
on stu.id = exam.id
案例练习2
-- 列出部门名称和这些部门的员工信息(名字和工作)，同时列出那些没有员工的部门。
-- 左外连接
select dname,ename,job
from dept left join emp
on emp.deptno = dept.deptno
-- 右外连接
select dname,ename,job
from dept right join emp
on emp.deptno = dept.deptno
MySQL约束
基本介绍
约束用于确保数据库数据满足特定的商业规则。
在mysql中，约束包括: not null、unique,primary key,foreign key,和check五种.
主键(primary key)
字段名 字段类型 primary key
用于唯一的表示表行的数据，当定义主键约束后，该列不能重复
演示
create table t1
(
id int primary key,/*表示id列是主键*/
`name` varchar(32)
)
-- 主键列的值不能重复
insert into t1
values(1,'jack');
insert into t1
values(2,'tom');
-- error
-- Duplicate entry '1' for key 'PRIMARY'
insert into t1
values(1,'taotao');
细节
- primary key 不能重复而且不能为null
- 一张表最多只能有一个主键，但可以是复合主键
- 主键的指定方式，有两种
直接在字段名后指定：字段名 primary key
在表定义最后写primary key(列名)
- 使用desc 表名，可以看到primary key的情况
- 提示：在实际开发中，每个表往往都有主键
复合主键
一张表最多只能有一个主键，但可以是复合主键
-- error -- Multiple primary key defined
create table t2
(id int primary key,
`name` char(32) primary key
)
-- 复合主键写法
create table t2
(id int,
`name` char(32),
primary key(id,`name`) -- 复合主键
)
-- 主键写法2
create table t2
(id int,
`name` char(32),
primary key(id) -- 复合主键写法，但是依然是一个主键
)
-- 复合主键解释
insert into t2
values(1,'jack');
-- right
insert into t2
values(1,'tom');
-- error Duplicate entry '1-jack' for key 'PRIMARY'
insert into t2
values(1,'jack');
not null(非空)
基本介绍
如果在列上定义了not null，那么当插入数据时，必须为列提供数据
字段名 字段类型 not null
unique(唯一)
基本介绍
当定义了唯一约束后，该列值是不能重复的
字段名 字段类型 unique
案例使用
-- unique使用
create table t3
(id int unique, -- 表示id 列是不可重复的
`name` char(32)
)
细节
如果没有指定 not null,则 unique 字段可以有多个null
但是：如果加了nut null 又加了 unique 其实就相当于设定了一个主键，意义不大
create table t3
(id int unique, -- 表示id 列是不可重复的
`name` char(32)
);
-- right
insert into t3
values(null,'tom');
-- right
insert into t3
values(null,'jack');
外键(foreign key)
基本介绍
用于定义主表和从表之间的关系:外键约束要定义在从表上，主表则必须具有主.键约束或是unique约束.当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null
foreign(本表字段名) references 主表名(主键名或unique字段名)
alter table 表名 add constraint 外键名 foreign key （次表字段） references 主表名（主表字段）
外键示意图
案例演示
-- 外键演示
-- 创建 主表 my_class
create table my_class(
id int PRIMARY key,
`name` varchar(32) not null default '');
-- 创建 从表 my_stu
create table my_stu(
id int primary key, -- 学生编号
`name` varchar(32) not null default '',
class_id int, -- 学生所在班级的编号
-- 下面指定外键关系
constraint 'FF' foreign key (class_id) references my_class(id)) ;
-- 测试数据
insert into my_class
values(100,'java'),(200,'web');
select * from my_class;
-- right
insert into my_stu
values(1,'tom',100);
-- right
insert into my_stu
values(2,'jack',200);
-- error Cannot add or update a child row: a foreign key constraint fails (`taotao_db01`.`my_stu`, CONSTRAINT `my_stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `my_class` (`id`))
insert into my_stu
values(3,'bob',300);
select * from my_stu
细节
- 外键指定的字段必须是一个“主键”或者一个 unique
- 表的类型是innodb，这样的表才支持外键
navicat 语句
show table status from 数据库名
- 外键字段的类型要和主键字段的类型一致（长度可以不同）
- 外键字段的值，必须在主键字段中出现过，或者为null【前提是外键字段允许为null】
-- right
insert into my_stu
values(4,'mary',null); -- 此时外键连接的虽然是主键，但是依然可以加入null
- 一旦建立主外建的关系，数据不能随意删除了
-- error
delete from my_class
where id = 100;
check
基本介绍
用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在10002000之间如果不再10002000之间就会提示出错。
提示:oracle 和sql server均支持check ,但是mysql5.7目前还不支持check ,只做语法校验，但不会生效。
在mysql中实现check的功能，一般是在程序中控制，或者通过触发器完成
案例演示
-- 演示check的使用
-- mysql5.7目前不支持check，只做语法校验，但不会生效
-- 测试
create table t4(
id int primary key,
`name` varchar(32),
sex varchar(6) check (sex in(`man`,`woman`)),
sal double check (sal > 1000 and sal < 2000)
);
-- 添加数据
insert into t4
values(1,'jack','mid',1);
select * from t4;
1907
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
