黑马程序员Mysql

2023-11-16

MySQL

-- 1\DDL操作之数据库操作

-- 查看所有的数据库

show databases;

-- 创建数据库

-- CREATE database mydb1;

CREATE database if not exists mydb1;

-- 选择使用哪个数据库

use mydb1;

-- 删除数据库

DROP database mydb1;

drop database if exists mydb1;

-- 修改数据库编码

-- alter database mydb1 character set utf8;

--2\创建表

create database if not EXISTS mydb1;

use mydb1;

create table if not exists student(

sid int,

name varchar(20), -- 不是日期不是数字就是字符串就用varchar

gender VARCHAR(20),

age int,

birth date,

address VARCHAR(20),

score DOUBLE

);

-- 数值类型

-- TINYINT

-- SMALLINT

-- MEDIUMINT

-- INT

-- BIGINT

-- FLOAT

-- DOUBLE

-- DECIMAL

-- 字符串类型

-- CHAR

-- VARCHAR

-- 日期类型

-- DATE

-- time

-- year

-- DATETIME

-- TIMESTAMP

-- 3\对表结构的其他常用操作

-- 查看当前数据库的所有的表

show TABLEs;

-- 查看指定表的创建语句

show create table student;

-- 查看表结构

DESC table student;

-- 删除表

-- drop table student;

-- 4\对表结构的操作--修改表结构格式

-- 修改表添加列 alter table 表名 add 列名类型(长度) [约束];

alter TABLE student add dept VARCHAR(20)

-- 修改列名和类型

ALTER TABLE 表名 change 旧列名新列名类型(长度) 约束

ALTER TABLE student CHANGE dept department VARCHAR(30);

-- 修改表删除列

-- alter table 表名 drop 列名

ALTER TABLE student drop department;

-- 修改表名

-- rename table 表名 to 新表名

RENAME TABLE student to studentinfo;

-- DML更新表数据

数据操作语言(Data Manipulation Language),

-- 1/数据插入操作insert

方式一:INSERT into 表(列名1,列名2,列名3...) values(值1,值2,值3...)

向表中某些列插入某些值,值与列的顺序相互对应

-- 单行插入

INSERT into studentinfo (sid,name,gender,age,birth,address,score) VALUES(1001,'zxc','男',12,'1022-12-12','逼近',124);

-- 多行插入,中间用',' 隔开,每次插入可以不对全部列进行插入

INSERT into studentinfo (sid,name,gender,age)

VALUES(1001,'zxc','男',12),

(1002,'zasd','女',22);

用; 作为一行代码的结束,可以随便进行换行

方式二:INSERT into 表 values(值1,值2,值3...)

-- 向表中插入所有列

INSERT into studentinfo

VALUES(1223,'adasscca','nan',123,'1245-4-23','CASCADE',145465),

(1224,'adasscca','nan',123,'1245-4-23','CASCADE',145465);

-- 2/更新操作update

方式一:updata 表名 set 字段名=值,字段名=值...;

不进行条件设置,会将表中的全部对应字段名都进行更改

UPDATE studentinfo set gender='女',score=100;

方式二updata 表名 set 字段名=值,字段名=值...where 条件;

进行条件设置,不会将全部对应的字段名的相应内容都进行更改

UPDATE studentinfo set name="张三" where sid=1001;

UPDATE studentinfo set name="张三" where sid>1100;

-- 3\删除操作delete

方式一:DELETE FROM 表名 [where 条件];

DELETE from studentinfo where sid>1100;

DELETE FROM studentinfo;

方式二truncate table 表名或者truncate 表名

TRUNCATE TABLE studentinfo;

TRUNCATE studentinfo;

-- DML的练习题

use mydb1;

CREATE TABLE if not EXISTS employee(

id int,

NAME VARCHAR(20),

gender VARCHAR(4),

salary int

);

INSERT into employee VALUES(1,'张三','男',2000),

(2,'李四','男',1000),

(3,'王五','女',4000);

UPDATE employee set salary=500;

UPDATE employee set salary=3000 where name="张三";

-- 约束

- 1\主键约束

是一个列或者多个列的组合,其值能唯一表示表中的每一行,方便在RDBMS中尽快定位

主键约束相当于: 唯一约束+非空约束的组合,主键约束列不允许重复,也不允许出现空值

-- 每个表最多只允许一个主键

-- 当创建主键约束时,系统默认会在所在的列和列组合上建立对应的唯一索引

use mydb1;

-- 创建单列主键

方式一:定义字段的同时指定主键

create TABLE if not EXISTS employ(

eid int PRIMARY KEY,

name VARCHAR(20),

salary DOUBLE

);

方式二:定义完字段之后指定主键

create TABLE if not exists employ2(

eid int,

name VARCHAR(20),

salary DOUBLE,

constraint zhujianmingzi PRIMARY KEY(eid)

-- CONSTRAINT+主键名可以进行省略

);

-- 创建多列主键(联合主键)

create TABLE if not EXISTS employ3(

eid int,

name VARCHAR(20),

salary DOUBLE,

PRIMARY key(eid,name)

);

-- 通过修改表结构进行主键的添加

create TABLE if not EXISTS employ4(

eid int,

name VARCHAR(20),

salary DOUBLE

);

ALTER TABLE employ4 add PRIMARY KEY(eid,name);

-- 删除主键约束

alter table <数据表名> drop PRIMARY KEY;

不区分单列主键多列主键,因为一个表中只允许有一个主键

ALTER TABLE employ4 drop primary key;

-- 2\自增长约束

auto_increment:当主键定义为自增长之后,这个主键的值就不再需要用户进行输入数据了,而是数据库自动进行按步长进行增长操作,和主键一起使用

create TABLE if not EXISTS employ5(

eid int PRIMARY KEY auto_increment,

name VARCHAR(20),

salary DOUBLE

);

insert INTO employ5 (name,salary) VALUES ('zxc',123456);

insert INTO employ5 (name,salary) VALUES ('a',1);

insert INTO employ5 (name,salary) VALUES ('b',2);

-- 默认情况下,auto_increment的初始值是1,步长是1

-- 一个表中只能有一个字段使用auto_increment进行约束,且该字段必须有唯一索引,避免序号重复

-- auto_increment约束的字段,必须具有NOT NULL的属性

-- auto_increment约束的字段,只能是整数类型(TINYINT,SMALLINT,INT,BIGINT)

-- auto_increment约束字段的最大值受该字段的数据类型的约束,如果达到上限,auto_increment就会失效

-- 自定义auto_increment

方式一:创建表时指定

create TABLE if not EXISTS employ6(

eid int PRIMARY KEY auto_increment,

name VARCHAR(20)

)auto_increment = 100;

insert INTO employ6 (name) VALUES ('a');

insert INTO employ6 (name) VALUES ('b');

方式二:创建表之后指定

create TABLE if not EXISTS employ7(

eid int PRIMARY KEY auto_increment,
name VARCHAR(20)

);

ALTER TABLE employ7 auto_increment=100;

insert INTO employ7 (name) VALUES ('a');

insert INTO employ7 (name) VALUES ('b');

-- 对于自增长,delete删除后再进行数据添加,会接着上一个数据节点进行序号添加,TRUNCATE每次删除之后再添加数据,都会从1开始.

-- 3\非空约束

字段的值不能是空

方式一:创建表时指定

create TABLE if not EXISTS notnull1(

eid int NOT NULL,

name VARCHAR(20) NOT NULL

);

insert INTO notnull1 (eid,name) VALUES (111,'a');

insert INTO notnull1 (name) VALUES ('a');

方式二:创建表后指定

create TABLE if not EXISTS notnull2(

eid int,

name VARCHAR(20)

);

ALTER TABLE notnull2 MODIFY eid int not null;

ALTER TABLE notnull2 MODIFY name VARCHAR(20) not null;

-- 删除非空约束

alter TABLE notnull2 MODIFY eid int;

alter TABLE notnull2 MODIFY name VARCHAR(20);

-- 4\唯一约束

Unique Key:所有记录中字段的值不能重复出现.

注意:mysql中null和任何值都不相同,和自己也算不同

方式一:创建表时指定

create TABLE if not EXISTS unique1(

eid int unique,

name VARCHAR(20)

);

insert INTO unique1 VALUES (1,'a');

-- 删除约束

ALTER TABLE unique1 drop index eid;

-- 方式二:创建之后指定

create TABLE if not EXISTS unique2(

eid int,

name VARCHAR(20)

);

alter table unique2 add CONSTRAINT yueshuming unique (eid);

-- 删除约束

ALTER TABLE unique2 drop index yueshuming;

-- 5\默认约束default

方式一:

create TABLE if not EXISTS default1(

eid int default 0,

name VARCHAR(20)

);

insert INTO default1 (name)VALUES ('a');

方式二:

create TABLE if not EXISTS default2(

eid int,

name VARCHAR(20)

);

ALTER TABLE default2 MODIFY eid int default 0;

-- 删除默认约束

ALTER TABLE default2 MODIFY eid int default null;

-- 6\零填充约束:

插入数据时,当该字段的值的长度小于定义的长度时,会在该值的前面补上相应的0.zerofill默认为interesting(10).

-- 当使用zerofill时,默认会自动加unsigned(无符号)属性,使用unsigned属性后,数值范围是原值的2倍.

create TABLE if not EXISTS zerofill1(

eid int ZEROFILL,

name VARCHAR(20)

);

-- 删除

ALTER TABLE zerofill1 MODIFY eid int;

--DQL数据查询

语法格式

SELECT

[all|distinct]

<目标列的表达式1> [别名],

<目标列的表达式2> [别名]...

from <表名或视图名> [别名],<表名或视图名> [别名]...

[where<条件表达式>]

[group by <列名>] [having<条件表达式>] having是对分组之后的结果进行条件筛选. 执行顺序from-where-GROUP BY-having

[order by <列名1>[asc|desc],<列名2>[asc|desc]]

排序,asc升序,desc降序.升序可以省略不写,按两列进行排序时,先按照字段1进行排序,再按照字段2进行内部排序.常常放在最后面

[limit<数字或列名>] limit m-表示显示前m条商品信息. limit m,n 表示从第m个商品开始一共显示n条商品信息

CREATE DATABASE if not EXISTS mydb2;

use mydb2

create table product(

pid int PRIMARY KEY auto_increment,

pname VARCHAR(20) not null,

price double,

category_id VARCHAR(20)

);

INSERT into product VALUES(null,'海尔洗衣机',5000,'c001')

INSERT into product VALUES(null,'美的冰箱',3000,'c001')

INSERT into product VALUES(null,'格力空调',5000,'c001')

INSERT into product VALUES(null,'九阳电饭煲',5000,'c001')

INSERT into product VALUES(null,'啄木鸟衬衣',300,'c002');

INSERT into product VALUES(null,'恒源祥西裤',800,'c002');

INSERT into product VALUES(null,'花花公子夹克',440,'c002');

INSERT into product VALUES(null,'劲霸休闲裤',266,'c002');

INSERT into product VALUES(null,'海澜之家卫衣',180,'c002');

INSERT into product VALUES(null,'杰克琼斯运动裤',430,'c002');

INSERT into product VALUES(null,'兰蔻面霜',300,'c003');

INSERT into product VALUES(null,'雅诗兰黛精华水',200,'c003');

INSERT into product VALUES(null,'香奈儿香水',350,'c003');

INSERT into product VALUES(null,'SK-II神仙水',350,'c003');

INSERT into product VALUES(null,'资生堂粉底液',180,'c003');

INSERT into product VALUES(null,'老北京方便面',56,'c004');

INSERT into product VALUES(null,'良品铺子海带丝',17,'c004');

INSERT into product VALUES(null,'三只松鼠坚果',88,null);

-- 简单查询

-- 1\查询所有的商品

-- select 列选择 from 表名;

select * from product

select pid,pname,price,category_id from product

-- 2\查询商品名和商品价格

select pname,price from product

-- 3\别名查询.使用关键字as(as可以省略)主要用在多表查询,对不同表的相同列明进行区分

3.1表别名,取名为p

select * from product as p;

3.2列别名

select pname as '商品名',price as'商品价格' from product

-- 4\去除重复值 DISTINCT

select distinct price from product;

select distinct * from product; 去除所有列值都相同的重复值

-- 5\查询结果是表达式

将所有商品的价格加10元进行显示

select pname,price+10 as new_price from product

-- 算数运算符

+ - * /(DIV) %(MOD)

select 6+2;

select pname,price+10 as new_price from product

计较运算符

= > >= < <= <=> <> != is null isnull is not null least greatest between and in not in like REGEXP

逻辑运算符

! not and && || or XOR逻辑异或

查询商品名为海尔洗衣机的全部商品

select * from product where pname='海尔洗衣机';

查询价格不是800的商品

select * from product where price != 800;

查询价格在200~1000的商品

select * from product where price between 200 and 1000;

查询价格为200或800的商品

select * from product where price in(200,800);

查询含有"鞋"字的所有商品

select * from product where pname like '%裤%'; -- 模糊匹配,%表示此处可以含有字符且数量不限

查询以'海'开头的所有商品

select * from product where pname like '海%';

查询第二个字为'蔻'的商品

select * from product where pname like '_蔻%'; -- _表示一个字符

查询category_id为null的商品

select * from product where category_id is null; -- 不能用 =null 因为null和任何值都不相等

使用least求最小值

select least(2,34,745);

使用greates求最大值

select greatest(2,34,745);

在上面两个函数的比较值之间,出现null则比较结果就是null

-- 排序

使用价格排序降序;

select * from product order by price desc;

在价格降序的基础上以分类降序排序

select * from product order by price desc, category_id desc;

显示商品的价格(去重),再降序排序

select distinct price from product order by price desc;

-- 聚合查询

以列为查询单位,进行平均值,最大值,最小值查询

count() 统计指定列不为null 的记录行数

查询商品的总条目

select count(category_id) from product;

select count(*) from product;

查询价格大于200商品的总条目

select count(pid) from product where price>200;

sum()计算指定列的数值和,如果列类型不是数值类型,返回值为0

查询分类为c001的所有商品的总和

select sum(price) from product where category_id = 'c001';

max()计算指定列的最大值,字符串或数字

查询商品的最大价格

select max(price) from product;

select min(price) from product;

min()

avg()计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0

聚合查询中忽视null的存在

-- 分组查询

统计各个分类商品的数量

select category_id,count(pid) from product group by category_id;

统计各个分类商品的数量,且只显示数量大于4的商品

select category_id,count(pid) count from product group by category_id having count>4 ORDER BY count;

执行顺序from - GROUP BY - count - select - having - ORDER BY

-- 分页查询limit

查询前五条信息

select * from product limit 5;

从第四条开始显示,总共显示6条

select * from product limit 3,6;

-- 将一张表中的数据导入另一张表中,要求另一个表必须存在

insert into table2(field1,field2...) select value1,value2... FROM table1

create table product2(

pname VARCHAR(20),

price DOUBLE

);

insert into product2 (pname,price) select pname,price from product;

select * from product2;

-- 多表操作

多表关系一对一一对多多对一多对多

-- 外键约束: 是表的一个特殊字段,经常与主键约束一起使用,对于两个具有关联关系的表而言,相关联字段中主键所在的表就是主表(父表),外键所在的表就是从表(子表)

create database if not exists mydb3;

use mydb3;

-- 1\创建外键约束...一对多

方式一:

[CONSTRAINT <外键名>] FOREIGN KEY 字段名[,字段名2...] REFERENCES <主表名> 主键列1[,主键列2...]

创建主表,用来约束从表中的外键

create table if not exists dept(

deptno VARCHAR(20) PRIMARY key, -- 部门号

name VARCHAR(20) -- 部门名字

);

创建从表并创建外键约束

create table if not exists emp(

eid VARCHAR(20) PRIMARY KEY, -- 员工编号

ename VARCHAR(20), -- 员工名字

age int, -- 员工年龄

dept_id VARCHAR(20), -- 员工所属部门

CONSTRAINT emp_fk FOREIGN key(dept_id) references dept(deptno) -- 外键约束

);

-- 方式二:

alter table <数据表名> add CONSTRAINT <外键名> FOREIGN key(<列名>) REFERENCES <主表名>(<列名>)

创建主表,用来约束从表中的外键dept2

create table if not exists dept2(

deptno VARCHAR(20) PRIMARY key, -- 部门号

name VARCHAR(20) -- 部门名字

);

创建从表并创建外键约束

create table if not exists emp2(

eid VARCHAR(20) PRIMARY KEY, -- 员工编号

ename VARCHAR(20), -- 员工名字

age int, -- 员工年龄

dept_id VARCHAR(20) -- 员工所属部门

);

alter TABLE emp2 add CONSTRAINT emp2_fk FOREIGN key (dept_id) REFERENCES dept2(deptno)

-- 2\数据插入

要先写入主表主键内容

INSERT into dept VALUES('1001','研发部');

INSERT into dept VALUES('1002','销售部');

INSERT into dept VALUES('1003','财务部');

INSERT into dept VALUES('1004','人事部');

从表的外键列值一定要依托于主表主键内容

INSERT into emp VALUES('1','乔峰',20,'1001');

INSERT into emp VALUES('2','段誉',21,'1001');

INSERT into emp VALUES('3','虚竹',13,'1001');

INSERT into emp VALUES('4','阿紫',18,'1002');

INSERT into emp VALUES('5','扫地僧',35,'1002');

INSERT into emp VALUES('6','李秋水',33,'1003');

INSERT into emp VALUES('7','鸠摩智',50,'1003');

INSERT into emp VALUES('8','天山童姥',60,'1005'); 不能实现

-- 删除主从表数据

主表的数据被从表依赖时,不可以删除,不被依赖时候可以删除

从表的数据可以随意删除

-- 删除外键约束

alter table <表名> drop FOREIGN key <外键约束名>;

alter table emp drop FOREIGN key emp_fk;

-- 2\多对多;两张主表,一张从表.从表中至少有两个外键列,对应两个主表中的主键

创建学生表(左侧主表)

create table if not exists student(

sid int PRIMARY key auto_increment,

name VARCHAR(20),

age int,

gender VARCHAR(20)

);

创建课程表(右侧主表)

create table if not exists course(

cid int PRIMARY key auto_increment,

cidname VARCHAR(20)

);

创建中间表student_course/score(从表)

create TABLE if not EXISTS score(

sid int,

cid int,

score DOUBLE

);

建立外键约束(两次)

alter table <数据表名> add CONSTRAINT <外键名> FOREIGN key(<列名>) REFERENCES <主表名>(<列名>)

alter TABLE score add FOREIGN KEY(sid) REFERENCES student(sid);

alter TABLE score add FOREIGN KEY(cid) REFERENCES course(cid);

给学生表添加数据

INSERT into student VALUES(1,'小龙女',18,'女');

给课程表添加数据

INSERT into course VALUES(1,'语文');

给中间表添加数据

INSERT into score VALUES(1,1,89);

删除时,中间表可以删除,两边的主表不可以进行随意的删除

--3多表联合查询

use mydb3;

创建部门表

create table if not exists dept3(

deptno VARCHAR(20) PRIMARY key, -- 部门号

name VARCHAR(20) -- 部门名字

);

创建员工表

create table if not exists emp3(

eid VARCHAR(20) PRIMARY KEY, -- 员工编号

ename VARCHAR(20), -- 员工名字

age int, -- 员工年龄

dept_id VARCHAR(20) -- 员工所属部门

);

INSERT into dept3 VALUES('1001','研发部');

INSERT into dept3 VALUES('1002','销售部');

INSERT into dept3 VALUES('1003','财务部');

INSERT into dept3 VALUES('1004','人事部');

INSERT into emp3 VALUES('1','乔峰',20,'1001');

INSERT into emp3 VALUES('2','段誉',21,'1001');

INSERT into emp3 VALUES('3','虚竹',13,'1001');

INSERT into emp3 VALUES('4','阿紫',18,'1001');

INSERT into emp3 VALUES('5','扫地僧',35,'1002');

INSERT into emp3 VALUES('6','李秋水',33,'1002');

INSERT into emp3 VALUES('7','鸠摩智',50,'1002');

INSERT into emp3 VALUES('8','天山童姥',60,'1003');

INSERT into emp3 VALUES('9','慕容博',58,'1003');

INSERT into emp3 VALUES('10','丁春秋',71,'1005');

-- 1交叉连接查询

交叉连接查询返回被连接的两个表的所有数据行的笛卡尔积.

笛卡尔积可以理解为一张表的每一行去和另一个表的任意一行进行匹配

假如A表有m行数据,B表有n行数据,则返回m*n行数据

笛卡尔积会产生很多冗余的数据,后期的其他查询可以在该集合的基础上进行条件筛选

格式:select * from 表1,表2,表3...;

select * from emp3,dept3;

得到的结果是用员工表中的每一行与部门表中的每一行进行匹配.其中有很多是错误匹配,也就是冗余数据.

-- 2内连接查询(交叉连接查询的条件版本)

就是求多张表的交集部分

隐式内连接

select * from A,B where 条件

显示内连接

select * from A [INNER] JOIN B on 条件

隐式/显式

1查询每个部门的所属员工

select * from emp3,dept3 where emp3.dept_id=dept3.deptno;

select * from emp3 join dept3 on emp3.dept_id=dept3.deptno;

2查询研发部门的所属员工

select * from emp3,dept3 where emp3.dept_id=dept3.deptno and dept3.name='研发部';

select * from emp3 join dept3 on emp3.dept_id=dept3.deptno and dept3.name='研发部';

3查询研发部和销售部的所属员工

select * from emp3,dept3 where emp3.dept_id=dept3.deptno and (dept3.name='研发部' or dept3.name='销售部');

select * from emp3 inner join dept3 on emp3.dept_id=dept3.deptno and name in('研发部','销售部');

4查询每个部门的员工数并进行升序排序

select name,count(*) from emp3,dept3 where emp3.dept_id=dept3.deptno GROUP BY deptno;

5查询人数大于等于三的部门,并按照人数进行降序排序

select name,count(*) from emp3,dept3 where emp3.dept_id=dept3.deptno GROUP BY deptno having count(*)>=3 ORDER BY count(*);

-- 3外连接查询

左外连接(left outer join) 是以左表为基准，将两表的数据进行连接，然后将左表没有对应项显示为NULL；

select * from A left [outer] join B on 条件 left [outer] join C on 条件2;

右外连接(right outer join) select * from A right outer join B on 条件

满外连接(full outer join) select * from A full outer join B on 条件. mysql对满外连接的支持不好,使用union链接实现左外和右外的并集

1查询哪些部门有员工,哪些部门没有员工

select * from dept3 LEFT JOIN emp3 on deptno=dept_id;

2查询哪些员工有相应部门,哪些员工没有相应部门

select * from emp3 left JOIN dept3 on deptno=dept_id;

select * from dept3 right JOIN emp3 on deptno=dept_id;

3实现满外连接

union是将上下两个结果进行拼接并且去重

select * from dept3 LEFT JOIN emp3 on deptno=dept_id union select * from dept3 right JOIN emp3 on deptno=dept_id;

union all是将上下两个结果直接进行拼接,无去重

select * from dept3 LEFT JOIN emp3 on deptno=dept_id union all select * from dept3 right JOIN emp3 on deptno=dept_id;

-- 4子查询

子查询就是在一个完整的查询语句中,嵌套若干个不同功能的小查询,从而一起完成复杂查询的一种编写形式,通俗一点就是包含select嵌套的查询

1单行单列:返回一个具体列的内容,可以理解为一个单值数据

2单行多列:返回一行数据中多个列的内容

3多行单列:返回多行记录之中同一列的内容,相当于给出了一个操作范围

4多行多列:查询返回的结果是一张临时表

1查询年龄最大的员工

select max(age) from emp3;

select * from emp3 where age = 71;

上面的两个语句可以转化为下面的语句

select * from emp3 where age = (select max(age) from emp3); -- 单行单列

2查询研发部和销售部的信息,包括员工号员工名字.

关联查询:

select eid,ename,name from emp3 join dept3 where deptno=dept_id and name in ('研发部','销售部');

子查询初始形式:

select deptno from dept3 where name in ('研发部','销售部');

select * from emp3 WHERE dept_id in ('1001','1002')

子查询合并形式:

select * from emp3 WHERE dept_id in (select deptno from dept3 where name in ('研发部','销售部')); -- 多行单列

3查询研发部20岁以下的员工信息

select * from emp3 join dept3 on deptno=dept_id and name='研发部' and age<20;

select deptno from dept3 where name = '研发部';

select * from emp3 where dept_id=(select deptno from dept3 where name = '研发部') and age<20;

子查询关键字

1 ALL

select...from...where c>all(查询语句); 表示C需要大于查询语句的所有返回值

查询年龄大于'1003'部门所有员工的员工信息

select * from emp3 where age>all(select age from emp3 where dept_id='1003');

查询不属于任何一个部门的员工信息

select * from emp3 where dept_id != all(select deptno from dept3)

select * from emp3 where dept_id not in (select deptno from dept3)

2 any/some

select...from...where c>any(查询语句); 表示C大于查询语句返回值的任意一个值.some 和 any 用法相同

查询年龄大于'1003'部门任意一个员工年龄的员工信息

select * from emp3 where age>any(select age from emp3 where dept_id='1003');

3 IN(选项1,选项2,选项3...)

4 EXISTS

select...from...where exists(查询语句) 当数据量较大时,使用exists不使用in

查询语句如果有数据返回,exists会返回true,where条件成立.查询语句无数据输出,WHERE 条件不成立

查询公司是否有大于60岁的员工,有则输出

select * from emp3 a where exists(select * from emp3 b where a.age>60)

查询有所属部门的员工信息

select * from emp3 a where EXISTS(select * from dept3 b where a.dept_id = b.deptno)

-- 5自关联查询

信息查询时,有时需要进行对表自身进行关联查询,一张表当作多张表使用.

自关联时表必须起别名

select 字段列表 from 表1 a, 表1 b where 条件;

select 字段列表 from 表1 a [left] join 表1 b on 条件;

创建一个自关联表,表中数据对表中数据进行约束.

create table t_sanguo(

eid INT PRIMARY KEY,

ename VARCHAR(20),

manager_id int, -- 外键列

FOREIGN KEY (manager_id) references t_sanguo(eid) -- 添加自关联约束

);

insert into t_sanguo values(1,'刘协',NULL);

insert into t_sanguo values(2,'刘备',1);

insert into t_sanguo values(3,'关羽',2);

insert into t_sanguo values(4,'张飞',2);

insert into t_sanguo values(5,'曹操',1);

insert into t_sanguo values(6,'许褚',5);

insert into t_sanguo values(7,'典韦',5);

insert into t_sanguo values(8,'孙权',1);

insert into t_sanguo values(9,'周瑜',8),

(10,'鲁肃',8);

进行关联查询

1 查询每个三国人物及他的上级

select a.ename,b.ename from t_sanguo a,t_sanguo b where a.eid=b.manager_id;

2 查询所有人物及上级

select a.ename,b.ename from t_sanguo a left join t_sanguo b on b.eid=a.manager_id;

链接：https://pan.baidu.com/s/16-sy1SZYzARHRtQUT9XPAQ?pwd=ot2n
提取码：ot2n

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)