mysql-day03

luoyiang

已于 2024-08-29 20:36:54 修改

阅读量285

点赞数 12

文章标签： mysql 数据库

于 2024-08-29 20:35:11 首次发布

本文链接：https://blog.csdn.net/m0_64370841/article/details/141687882

版权

SQL-Constraint

-- ----------------------------------------------------------
--        约束：Constraint
--     就是用于限定字段值，在进行DML操作时遵守的规则，
--     可以保证数据的一致性和安全性，完整性，不满足规则的数据不能被保存到数据库中。
-- 约束的分类：
--     1. 默认约束  2. 非空约束  3. 唯一性约束  4. 主键约束
--     5. 自增约束  6. 外键约束  7. 检查约束
-- -----------------------------------------------------------

-- -----------------------------------------------------------
--   1. 默认值约束：
-- -----------------------------------------------------------
-- 建表前指定
create table table_default(
tid int,
tname varchar(20),
tage int default 18,
tgender char(1)
);
insert into table_default values(1000,'A',38,'f');
select * from table_default;
insert into table_default (tid,tname,tgender) values (1002,'B','f');
desc table_default;
insert into table_default (tid) values (1003);

-- 建表后指定
-- 语法: alter table  表名 moidfy 字段名 字段类型 default  value;
alter table table_default modify tgender char(1) default 'f'; 
desc table_default;

-- 取消默认值约束
-- 语法: alter table  表名 moidfy 字段名 字段类型;
alter table table_default modify tgender char(1) ; 
desc table_default;



-- -----------------------------------------------------------
--   2. 非空约束：
--    应用场景: 当想要限定字段的值不能为null时，可以使用非空约束
--     not null，简称NN
-- -----------------------------------------------------------
create table table_NN(
tid int,
tname varchar(20) not null,
tage int
);
desc table_NN;
insert into table_NN values (1000,'A',12);
insert into table_NN values (1001,null,12);
select * from table_NN;

 
 -- 建表后添加非空约束
 --  语法： alter table 表名 modify 字段名 字段类型 not null;
 create table table_NN_1(
tid int,
tname varchar(20),
tage int
);
desc table_NN_1;
alter table table_NN_1 modify tname varchar(20) not null;

-- 取消非空约束
-- 语法： alter table 表名 modify 字段名 字段类型;
alter table table_NN_1 modify tname varchar(20);
desc table_NN_1;

create table table_NN_2(
tid int,
tname varchar(20),
tage int,
CONSTRAINT _A
);



-- -----------------------------------------------------------
--   3. 唯一约束：
--    应用场景: 当想要限定字段的值不能为重复时，可以使用非空约束
--       注意 null值是可以重复的。 可以理解为 null！=null
--     unique:简称UK
-- -----------------------------------------------------------
-- 建表时创建
create table table_UK(
tid int,
tcard varchar(18) unique,
tname varchar(10)
);
desc table_UK;
insert into table_UK values(1001,'A1001','A');
insert into table_UK values(1002,'A1001','B');
insert into table_UK values(1003,null,'C');
insert into table_UK values(1004,null,'D');
select * from table_UK;

-- 唯一约束的表级约束写法
create table table_UK_1(
tid int,
tcard varchar(18),
tname varchar(10),
constraint uk_table_UK_1_tcard unique(tcard)
);
desc table_UK_1;

-- 建表后添加
--  
alter table table_UK_1 modify tname varchar(10) unique;

-- 取消唯一性约束
-- 语法： alter table 表名 drop index 唯一约束名称;
--   查看约束名称，可以使用 show create  table 表名;
alter table table_UK_1 drop index tname;
desc table_UK_1;

show create table table_UK_1;



-- -----------------------------------------------------------
--   4. 主键约束：
--    应用场景: 用于标识表中的每一条记录的唯一性
--        优点：可以快速找到表中的某一条记录
--     primary key：简称 PK
--     唯一且非空。
--     可以设置一个字段为主键约束，这种情况可以写成列级约束或表级约束。
-- 可以设置多个字段为主键约束，这种情况只能写成表级约束
--  注意： 表中只能存在一个主键约束
-- -----------------------------------------------------------
-- 建表时的列级写法
create table table_pk(
tid  int primary key,
tname varchar(10),
tage int
);
desc table_pk;
insert into table_pk values(1001,'A',18);
insert into table_pk values(1001,'B',18);
insert into table_pk values(null,'C',18);

-- 建表时的表级写法
create table table_pk_1(
tid  int,
tname varchar(10),
tage int,
constraint pk_table_pk_1_tid_tname primary key(tid,tname)
);
desc table_pk_1;
insert into table_pk_1 values(1001,'A',18);
insert into table_pk_1 values(1001,'B',18);
insert into table_pk_1 values(1002,'B',18);
select * from table_pk_1;

-- 建表后添加主键约束
create table table_pk_2(
tid  int,
tname varchar(10),
tage int
);
desc tbale_pk_2;
-- 语法
-- alter table 表名 modify 字段名 字段类型 primary key；
-- alter table 表名 add primary key(字段1，字段2，···)
alter table table_pk_2 modify tid int primary key;
alter table tbale_pk_2 add primary key(tid);
desc table_pk_2;

-- 取消主键约束  注意非空约束还在
-- alter table 表名 drop primary key：
alter table table_pk_2 drop primary key;



-- -----------------------------------------------------------
--   5. 自增键约束：(自增长序列)
--    应用场景: 一般用于配合主键约束一起使用，因为主键约束不建议认为的操作
--     列类型，必须是整形
--     该列必须设置为主键约束或者唯一性约束
--     设置自增约束的字段上的值默认的初始值1，每增加一条记录，字段值增加1
--     一个表中只能有一个自增约束
-- -----------------------------------------------------------
create table table_increment(
id int primary key auto_increment,
name varchar(10),
age int
);
desc table_increment;
insert into table_increment(name,age) values ('A',18);
insert into table_increment(name,age) values ('B',18);
select * from table_increment;
delete from table_increment where id =2;
-- 可以看出，将自增序列删除后，新的自增还是按照原来的状态进行+1，并非删除的位置开始
insert into table_increment(name,age) values ('C',18);

-- 指定自增时默认值
alter table table_increment_1 auto_increment=8;
insert into table_increment_1 (name,age) values('A',18);
select * from table_increment_1;

-- 如果自增字段写出来进行赋值，还希望使用自增，可赋值为null。
-- 数据库会自动使用自增序列的新值来给该字段赋值
insert into table_increment_1 (id,name.age) values(null,'B',18);

-- 建表后添加自增
create table table_increment_2(
id int primary key,
name varchar(10),
age int
);
desc table_increment_2;
-- 语法： alter table 表名 modify 字段名 字段类型 auto——increment
alter  table table_increment_2 modify id int auto_increment;
desc table_increment_2;

-- 取消自增
alter table table_increment_2 modify id int;
desc table_increment_2;



-- -----------------------------------------------------------
--   6. 外键约束： 字段A的值依赖于字段B的值，字段A就需要使用外键约束
--                 字段B必须是主键约束，字段A的值可以为null。
-- -----------------------------------------------------------
-- 建表时写法
drop table table_pk_a;
create table table_pk_a(
id int primary key auto_increment,
name varchar(10)
);
create table table_fk_b(
id int,
name varchar(10),
constraint fk_table_fk_b_id_table_pk_a_id foreign key(id) references table_pk_a(id)
);
insert into table_fk_b values(null,'A');
insert into table_fk_b values(null,'B');
select * from table_fk_b;
insert into table_fk_b values(null,'C');

-- 向父表中插入数据
insert into table_fk_a values(null,'A');
insert into table_fk_a values(null,'B');
select * from table_fk_a;
-- 向子表中插入数据
insert into table_fk_b values(null,'A');
insert into table_fk_b values(null,'B');
select * from table_fk_b;

-- 删除父表中的id为2的数据
delete from table_pk_a where id =2;


-- 创建一张表内的两个字段有依赖关系
create table table_fk_c(
id int primary key,
name varchar(10),
mgr int,
constraint fk_mgr_id foreign key(mgr) references table_fk_c(id)
);
insert into table_fk_c values(1001,'boss1',null);
insert into table_fk_c values(1002,'boss2',null);
insert into table_fk_c values(1003,'小明',1003);
insert into table_fk_c values(1004,'小红',1002);
select * from table_fk_c;

-- 建表后设置外键
create table table_fk_d(
id int primary key,
name varchar(10),
mgr int
);
-- 语法： alter table 表名1 add constraint 约束名称 foreign key(字段A) references 表名2(字段B)
alter table table_fk_d add constraint fk_mgr_id_1 foreign key(mgr) references table_fk_d(id);
desc table_fk_d;

-- 取消外键约束
-- 语法：alter table 表名 drop foreign key 外键约束名称;
alter table table_fk_d drop foreign key fk_mgr_id_1;
insert into tbale_fk_d values(1001,'A',null);
insert into tbale_fk_d values(1002,'B',1004);
select * from table_fk_d;



-- -----------------------------------------------------------
--   7. 检查约束： 
--      应用场景：使用条件来限制某一个字段的值。比如余额不能小于0，性别必须是f和m等
-- -----------------------------------------------------------
--  建表时写法：
create table table_ck(
id int,
name varchar(10),
gender char(1) check(gender='f' or gender= 'm')
);
desc table_ck;
insert into table_ck values(1001,'tom','男');
insert into table_ck values(1002,'tom','f');
insert into table_ck values(1003,'tom','m');
select * from table_ck;

-- 删除检查性约束
-- 语法： alter table 表名 drop check 约束名称; alter table table_ck drop check table_ck_chk_1;
alter table table_ck drop check table_ck_chk_1;
show create table table_ck;

SQL-JoinQuery

-- ----------------------------
-- 关联查询：多表查询
-- ----------------------------

-- 1.最简单的关联查询: 这种查询的结果大多数的记录是没有意义的
--    这种查询结果称为笛卡尔积。
--    表A中有m条记录，表B中有n条记录，查询时没有指定关系字段和关联条件，查询出来的数据总条数是m*n条。
select * from emp;
select * from emp,join dept;



-- 2. 写法分类
--    第一种写法：from子句中指定多个表名，用逗号隔开，使用where子句进行指定关系条件
--                select ... from A,B,C where A.字段=B.字段 and A.字段=C.字段
--    第二种写法：from子句中用join连接表名，使用on来制定关联条件
--                select ... from A join B join C on A.字段=B.字段 and A.字段=C.字段

--   前提： 员工表和部门编号字段有关系
-- 练习1： 查询每个员工的信息及其所在部门的信息
select * from emp,dept where emp.deptno = dept.deptno;
select * from emp a join dept b on a.deptno = b.deptno;

-- 练习2： 查询每个员工的信息及其领导信息
SELECT
	e.ename 员工姓名,
	e.job 员工职位,
	e.sal 员工工资,
	m.ename 领导姓名,
	m.job 领导职位,
	m.sal 领导工资 
FROM
	emp e,
	emp m 
WHERE
	e.mgr = m.empno;
	
-- 练习3： 查询每个员工的信息及其下属信息
SELECT
	a.ename 员工姓名,
	a.job 员工职位,
	a.sal 员工工资,
	b.ename 下属姓名,
	b.job 下属职位,
	b.sal 下属工资 
FROM
	emp a,
	emp b 
WHERE
	a.empno = b.mgr;
	
	
	
-- 3. join连接的分类
--     第一类：内连接[inner] join
--            查询出来的结果是：必须满足关联条件的记录进行组合显示
--     第二类：外连接            outer join
--               -左外连接 left  outer join
--                以左表为驱动表，驱动表里的数据全都显示，另一个表显示满足条件的数据
--               -右外连接 right outer join
--                以右表为驱动表，驱动表里的数据全都显示，另一个表显示满足条件的数据

-- 练习1：使用右外连接，查询员工信息及其部门信息
select e.*,d.* from emp e right outer join dept d on e.deptno = d.deptno;
-- 练习2：使用左外连接查询员工信息及其领导信息
SELECT
	e.ename 员工姓名,
	e.job 员工职位,
	e.sal 员工工资,
	m.ename 领导姓名,
	m.job 领导职位,
	m.sal 领导工资
FROM
	emp e
	LEFT OUTER JOIN emp m ON e.mgr = m.empno;
-- 练习3： 使用左外连接查询员工信息及其下属信息
SELECT
	a.ename 员工姓名,
	a.job 员工职位,
	a.sal 员工工资,
	b.ename 下属姓名,
	b.job 下属职位,
	b.sal 下属工资 
FROM
	emp a
	LEFT OUTER JOIN emp b ON a.empno = b.mgr;
	
	
	
	
	
	-- ---------------------------------------------------------
	--   集合查询： 两个查询语句 连接到一起
	--      关键词union 去重效果
	--      关键字union all 不会去重
	--     要求：
	--         1. 两个查询语句要显示的字段个数一致，如果不一致，可使用常量或null补齐
	--         2. 显示的字段的类型从左到右也要一致，否则没有意义
		-- ---------------------------------------------------
-- 第一个查询语句:查询20号部门的员工编号，姓名，职位，工资,部门编号
-- 第二个查询语句:查询30号部门的员工编号，姓名，职位，工资,部门编号
select empno,ename,job,sal,deptno from emp where deptno=20
union
select empno,ename,job,sal,mgr from emp where deptno = 30;

select empno,ename,job,sal,deptno from emp where deptno = 20 or deptno = 18
union
select empno,ename,job,sal, deptno from emp where deptno = 30 or deptno = 20;

	
	
	
	
-- ---------------------------------------------------------
-- 高级关联查询： 就是子查询
--  应用场景：就是一个查询语句A需要使用另一个查询语句B的结果的时候
--           A为主查询 B为子查询  整体是子查询(高级关联查询)
-- ---------------------------------------------------------
	
	-- 子查询位于where子句中  子查询的结果用于充当过滤筛选的条件
-- 需求1：查询员工编号为7369的领导信息
select * from emp where empno =(select mgr from emp where empno = 7369);
-- 需求2：查询员工编号为7369的部门信息
select * from dept where deptno =(select deptno from emp where empno = 7369);
-- 需求3：查询与7369同部门的同事信息
select * from emp where deptno=(select deptno from emp where empno =7369)and empno !=7369;
	
	

-- 第二类: 子查询位于from子句中:  子查询相当于一张表
-- 需求1: 查询员工的姓名，工资，及其部门的平均工资。
SELECT
	ename,
	sal,
	b.avg_sal 
FROM
	emp a
	JOIN ( SELECT deptno, avg( ifnull( sal, 0 ) ) avg_sal FROM emp GROUP BY deptno ) b ON a.deptno = b.deptno
-- 需求2: 查询员工的信息及其部门总人数，以及部门有几个职位
SELECT
	a.*,
	b.persons,
	b.jobs 
FROM
	emp a
	JOIN ( SELECT deptno, count( * ) persons, count( DISTINCT job ) jobs FROM emp GROUP BY deptno ) b ON a.deptno = b.deptno;



-- 第三类: 子查询位于having子句中:  子查询充当过滤筛选的条件
-- 需求1: 查询部门平均工资大于10号部门平均工资的其他部门的平均工资，工资之和，以及总人数
SELECT
	deptno,
	avg( ifnull( sal, 0 ) ) 
FROM
	emp 
GROUP BY
	deptno 
HAVING
	avg( ifnull( sal, 0 ) ) > ( SELECT avg( ifnull( sal, 0 ) ) FROM emp WHERE deptno = 30 );
	
	
	
-- 第四类: 子查询位于select子句中:  相当于join的外连接的另类写法。
-- 需求1：查询员工的姓名，工资，及其部门的平均工资
SELECT
	ename,
	sal,
	( SELECT avg( ifnull( sal, 0 ) ) FROM emp WHERE deptno = a.deptno ) 
FROM
	emp a;
-- 需求2：查询员工的信息及其部门总人数，以及部门有几个职位
select ename,sal,comm,deptno,
(select count(1)from emp where deptno = a.deptno) persons,
(select count(distinct job)from emp where deptno = a.deptno) jobs from emp a;