MySQL数据库一 Li.058

数据库安装和设置UTF-8

SQL 介绍

SQL (Structured Query Language): 结构化查询语文. 其实就是定义了操作所有关系型数据库的一种规则
通用语法规则
- SQL 语句可以单选或多行书写, 以分号结尾
- 可使用空格和缩进来增强语句的可读性
- MySQL数据库的SQL语句不区分大小写, 关键字建议使用大写
- 单选注释: – 注释内容
- 多行注释 /* 注释内容 */
SQL 分类
- DDL(Data Definition Language): 数据定义语文. 用来操作数据库,表,列等
- DML(Data Manipulation Language): 数据操作语文. 用来对数据库中表的数据进行增删改.
- DQL(Data Query Language): 数据查询语言. 用来查询数据库表的记录(数据).
- DCL(Data Control Language): 数据控制语言. 用来定义数据库的访问权限和安全级别, 及创建用户.

DDL 查询和创建数据库

# 查询所有数据库
show databases ;
# 查询数据的创建的创建语句
show create database mysql;
# 创建数据库
create database mydb;
# 创建数据库(判断,如果不存在则创建)
create database if not exists mydb2;
# 创建数据库(指定字符集)
create database mydb3 character set UTF8mb4;
# 练习创建mydb4, 不存在则创建, 并指定UTF8字符
create database if not exists mydb4 character set UTF8mb4;

DDL 修改删除使用数据库

# 修改数据库(修饰字符集)
ALTER DATABASE mydb4 CHAR SET gbk;
show create database mydb4;
# 删除数据库
drop database mydb4;
# 删除数据库(判断, 如果存在则删除)
drop database if exists mydb4;
# 使用数据库
use mydb3;
# 查看当前使用的数据库
select DATABASE();

DDL 查询数据表

# 查询所有的数据表
use mysql;
show tables ;
# 查询表结构
desc user;
# 查询表字符集
show table status from mysql like 'user';

DDL 创建数据表

格式

create table 表名(
    列名 数据类型 约束,
    列名 数据类型 约束,
    列名 数据类型 约束
);

数据类型

int:           整数类型
double:        小数类型
datetime:      日期类型.包含年月日,格式yyyy-MM-dd         HH:mm:ss
timestamp:     日间戮类型.包含年月日时分秒,格式yyyy-MM-dd HH:mm:ss(如不赋值或赋值null,默认使用系统时间赋值)
varchar(长度): 字符串类型

# 创建一个product商品表(商品编号, 商品名称, 商品价格, 商品库存, 上架时间)
create table product(
    id INT,
    name VARCHAR(20),
    price DOUBLE,
    stock INT,
    insert_time DATE
);
desc product;

DDL 修改数据表

# 修改表名
alter table product rename to newProduct;
show tables ;
# 修改表的字符集
alter table newProduct character set utf8mb4;
show table status from mydb3 like 'newProduct';
# 单独添加一列
alter table newProduct add color VARCHAR(10);
desc newProduct;
# 修改某列的数据类型
alter table newProduct modify color INT;
desc newProduct;
# 修改列名和数据类型
alter table newProduct change color address VARCHAR(200);
desc newProduct;
# 删除某一列
alter table newProduct drop address;
desc newProduct;

DDL 删除数据表

# 删除数据表
drop table product;
show tables ;
# 删除数据表(判断, 如果存在则删除)
drop table if exists newProduct;
show tables ;

DML 新增表数据

# 给指定列添加数据
desc product;
insert into product(id,name) values (1, '电饭煲');
select * from product;
# 给全部列添加数据
insert into product values (2,'iPhone',8000.0,1,null);
select * from product;
# 批量添加数据
insert into product(id,name) values (3,'XiaoMi'),(4,'Vivo');
insert into product values (5,'MacBookPro',8000.0,1,'2020-02-02'),(6,'Mac Mini',5000.0,2,'2021-06-06');
select * from product;

DML 修改和删除表数据

update和delete未加where会更新或删除全表数据

# 修改表中的数据
update product set name='高压电饭煲',price=1000 where id=1;
update product set stock=2;
select * from product;

# 删除表中的数据
delete from product where id=2 or id=3;
delete from product ;
select * from product;

DQL 表数据查询–数据准备

# 数据准备, 商品编号 商品名称 商品价格 商品品牌 商品库存 添加时间
use mydb3;
create table product(
    id INT,
    name VARCHAR(20),
    price DOUBLE,
    brand VARCHAR(10),
    stock INT,
    insert_time DATE
);
insert into mydb3.product values
(1,'华为手机',3999,'华为',23,'2088-03-10'),
(2,'小米手机',2999,'小米',30,'2088-05-15'),
(3,'苹果手机',5999,'苹果',18,'2088-08-20'),
(4,'华为电脑',6999,'华为',14,'2088-06-16'),
(5,'小米电脑',4999,'小米',26,'2088-07-08'),
(6,'苹果电脑',8999,'苹果',15,'2088-10-25'),
(7,'联想电脑',7999,'联想',null,'2088-11-11');
select * from mydb3.product;

DQL 表数据查询–查询全部

/*
select 字段列表 from 表名列表
where 条件列表
group by 分组字段
having 分组后的过滤条件
order by 排序
limit 分页;  */

# 查询全部的表数据
select * from product;
# 查询指定字段的表数据
select name,price,brand from mydb3.product;
# 去除重复查询
select brand from product;
select distinct brand from product;
# 计算列的值(四则运算)
select name,stock+10 from mydb3.product;
# 如果一列为null, 可以进行替换ifnull(表达式1,表达式2), 表达式1:想替换的列, 表达式2:想替换的值
select name,ifnull(stock,0)+10 from mydb3.product;
# 起别名查询, 别名as关键字也可以省略的
select name,ifnull(stock,0)+10 as getSum from mydb3.product;
select name,ifnull(stock,0)+10 getSum from mydb3.product;

DQL 表数据查询–条件查询

查询条件分类

符号             功能
>                大于
<                小于
>=               大于等于
<=               小于等于
=                等于
<>或!=           不等于
between...and... 在某个范围之内(都包含)
in(...)          多选一
like占位符       模糊查询_单个任意字符%多个任意字符
is.null          是null
is.not.null      不是null
and或&&          并且
or或||           或者
not或!           非,不是

条件查询语法

select 列名列表 from 表名where 条件;

# 查询库存大于20的商品信息
select * from mydb3.product where stock>20;
# 查询品牌为华为的商品信息
select * from product where brand= '华为';
# 查询金额在4000 ~ 6000 之间的商品信息
select * from product where price>=4000 and price<=6000;
select * from product where price between 4000 and 6000;
# 查询库存为14,30,23的商品信息
select * from product where stock=14 or stock=30 or stock=23;
select * from product where stock in (14,30,23);
# 查询库存为null的商品信息
select * from product where stock is null;
# 查询库存不为null的商品信息
select * from product where stock is not null;
# 查询名称以小米为开头的商品信息
select * from product where name like '小米%';
# 查询名称第二个字是是'为'的商品信息
select * from product where name like '_为%';
# 查询名称为四个字符的商品信息
select * from product where name like '____';
# 查询名称中包含电脑的商品信息
select * from product where name like '%电脑%';

DQL 表数据查询–聚合函数查询

聚合函数的介绍
- 将一列数据作为一个整体, 进行纵向的计算
聚合函数的分类

函数名      功能
count(列名) 统计数量(一般选用不为null的列)
max(列名)   最大值
min(列名)   最小值
sum(列名)   求和
avg(列名)   平均值

聚合函数查询语法
- select 函数名(列名) from 表名 [where条件];

# 计算product 表中总记录条件
select count(*) from product;
# 获取最高价格
select max(price) from product;
# 获取最低库存
select min(stock) from product;
# 获取总库存数量
select sum(stock) from product;
# 获取品牌为苹果的总库存数量
select sum(stock) from product where brand='苹果';
# 获取品牌为小米的平均商品价格
select avg(price) from product where brand='小米';

DQL 表数据查询–排序查询

排序查询语法 select 列名列表 from 表名 [where 条件] order by 列名排序方式,列名排序方式…;
排序方式
- ASC 升序
- DESC 降序
如果有多个排序条件, 只有当前边的条件值一值时, 才会判断第二条件.

# 按照库存升序排序
select * from product order by stock asc ;
# 查询名称中包含手机的商品信息, 按照金额降序排序
select * from product where name like '%手机%' order by price desc;
# 按照金额升序排序, 如果金额相同, 按照库存降序排序
update product set price=6999 where id=6;
select * from product order by price asc ,stock desc;

DQL 表数据查询–分级查询

分级查询语法
- select 列名列表 from 表名 [where 条件] group by 分级列名 [having 分级后的条件过滤] [order by 排序列名排序方式] ;

# 按照品牌分组, 获取每组商品的总金额
select  brand,sum(price) from product group by brand;
# 对金额大于4000元的商品, 按照品牌分组, 获取每组商品的总金额
select brand,sum(price) from product where price>4000 group by brand;
# 对金额大于4000元的商品, 按照品牌分组, 获取每组商品的总金额, 只显示总金额大于7000元的
select brand,sum(price) getSum from product where price>4000 group by brand having getSum>7000;
# 对金额大于4000元的商品, 按照品牌分级, 获取每组商品的总金额, 只显示总金额大于7000元的, 并按照总金额的降序排列
select brand,sum(price) getSum from product where price>4000 group by brand having getSum>7000 order by getSum desc ;

DQL 表数据查询–分页查询

分页查询语法
- select 列名列表 from 表名 [where 条件] group by 分级列名 [having 分级后的条件过滤] [order by 排序列名排序方式] limit 当前大岁数,每页显示的条数;
- 公式: 当前页数 = (当前页数-1) * 每页显示的条数

# 每页显示3条数据
# 第1页 当前页数=(1-1)*3
select * from product limit 0,3;
# 第2页 当前页数=(2-1)*3
select * from product limit  3,3;
# 第3页 当前页数=(3-1)*3
select * from product limit  6,3;

MySQL 外键约束

为什么要有外键约束
- 当表与表之间的数据有相关联性的时候, 如果没有相关的数据约束, 则无法保证数据的准确性
外键约束的作用
- 让表与表之间产生关联关系
建表时添加外键约束

create table 表名(
    列名 数据类型 约束,
    ...
    constraint 外键名 foreign key(本表外键列名) references 主表名(主表主键列名)
);

删除外键约束

alter table 表名 drop foreign key 外键名;

建表后单独添加外键约束

alter table 表名 constraint 外键名 foreign key(本表外键列名) references 主表名(主表主键列名);

## 建立数据
use mydb3;
create table USER(
    id int primary key auto_increment,
    NAME varchar(20) not null
);
insert into USER values (null,'张三'),(null,'李四');

create table orderlist(
    id int primary key auto_increment,
    number varchar(20) not null,
    uid int,
    constraint ou_fk1 foreign key (uid) references USER(id)
);
insert into orderlist values (null,'hm001',1),(null,'hm002',1),
                             (null,'hm003',2),(null,'hm004',2);
select * from orderlist;

# 添加一个订单, 但是没有真实用户
insert into orderlist values (null,'hm005',3);
# 删除李四用户
delete from USER where NAME = '李四';
# 删除外键约束
alter table orderlist drop foreign key ou_fk1;
# 添加外键约束
alter table  orderlist add constraint ou_fk1 foreign key (uid) references USER(id);

外键的级联更新和级联删除

添加级联更新

alter table 表名 constraint 外键名 foreign key(本表外键列名)
references 主表名(主表主键列名)
on update cascade;

添加级联删除

alter table 表名 constraint 外键名 foreign key(本表外键列名)
references 主表名(主表主键列名)
on delete cascade;

同时添加级联更新和级联删除

alter table 表名 constraint 外键名 foreign key(本表外键列名)
references 主表名(主表主键列名)
on update cascade on delete cascade;

# 先删除外键
alter table orderlist drop foreign key ou_fk1;
## 级联更新和删除
alter table  orderlist add constraint ou_fk1 foreign key (uid)
    references USER(id)
on update cascade on delete cascade ;
# 将李四用户id修改为3
update USER set id=3 where id=2;
select * from USER;
select * from orderlist;
# 删除李四这个用户
delete from USER where id=3;

约束

什么是约束
- 对表中的数据进行限定保证数据的正确性, 有效性, 完整性
约束的分类

约束                          | 作用
primary key                   | 主键约束
primary key auto_increment    | 主键自增
unique                        | 唯一约束
not null                      | 非空约束
foreign key                   | 外键约束
foreign key on update cascade | 外键级联更新
foreign key on delete cascade | 外键级联删除

主键约束

主键约束的特点
- 主键约束默认包含非空和唯一两个功能
- 一张表只能有一个主键
- 主键一般用于表中数据的唯一标识
建表时添加主键约束

create table 表名(
    列名 数据类型 primary key,
    ...
    列名 数据类型 约束
);

删除主键主键约束

alter table 表名 drop primary key;

键表后单独添加主键约束

alter table 表名 modify 列名 数据类型 primary key;

# 创建学生表(编号,姓名,年龄) 编号设为主键
create table student(
    id int primary key ,
    NAME varchar(50),
    age int
);
# 查询学生表的详细信息
desc student;
# 添加数据
insert into student values (null,'张三',23);
insert into student values (1,'张三',23);
insert into student values (1,'李四',24);
insert into student values (2,'李四',24);
# 删除主键
alter table student drop primary key ;
# 添加主键约束
alter table student modify id int primary key ;

主键自增约束

建表时添加主键自增约束

create table 表名(
    列名 数据类型 primary key auto_increment,
    ...
    列名 数据类型 约束
);

删除主键自增约束

alter table 表名 modify 列名 数据类型;

建表后单独添加主键自增约束

alter table 表名 modify 列名 数据类型 auto_increment;

MySQL中的自增约束,必须配合键的约束一起使用!

# 创建学生表(编号,姓名,年龄) 编号设为主键 自增
drop table student;
create table student(
    id int primary key auto_increment,
    NAME varchar(50),
    age int
);
# 查询学生表的详细信息
desc student;
# 添加数据
insert into student values (null,'张三',23);
insert into student values (null,'李四',23);
select * from student;
# 删除自增约束
alter table student modify id INT;
# 建表后单独添加自增约束
alter table student modify id INT auto_increment;

唯一约束

建表时添加唯一约束

create table 表名(
    列名 数据类型 unique,
    ...
    列名 数据类型 约束
);

删除唯一约束

alter table 表名 drop index 列名;

建表后单独添加主键自增约束

alter table 表名 modify 列名 数据类型 unique;

# 创建学生表(编号,姓名,年龄) 编号设为主键 自增 年龄唯一
drop table student;
create table student(
    id int primary key auto_increment,
    NAME varchar(50),
    age int unique
);
# 查询学生表的详细信息
desc student;
# 添加数据
insert into student values (null,'张三',23);
insert into student values (null,'李四',23);
select * from student;
# 删除唯一约束
alter table student drop index age;
desc student;
# 建表后单独添加唯一约束
alter table student modify age INT unique ;

非空约束

建表时添加非空约束

create table 表名(
    列名 数据类型 not null,
    ...
    列名 数据类型 约束
);

删除非空约束

alter table 表名 modify 列名 数据类型;

建表后单独添加非空约束

alter table 表名 modify 列名 数据类型 not null;

# 创建学生表(编号,姓名,年龄) 编号设为主键 自增 姓名非空 年龄唯一
drop table student;
create table student(
    id int primary key auto_increment,
    NAME varchar(50) not null ,
    age int unique
);
# 查询学生表的详细信息
desc student;
# 添加数据
insert into student values (null,null,24);
insert into student values (null,'张三',23);
select * from student;
# 删除非空
alter table student modify NAME VARCHAR(50);
desc student;
# 建表后单独添加非空
alter table student modify NAME varchar(50) not null ;

多表操作

多表概念
- 多张数据表, 而表与表之间是可以有一定的关联关系, 这种关联关系通过外键约束实现
多表的分类
- 一对一
- 一对多
- 多对多
一对一
- 适用场景
  - 人和身份证. 一个人只有一个身份证, 一个身份证只能对应一个人
- 建表原则
  - 在什么问题一个表建立外键, 去关联另外一个表的主键

## 一对一
use mydb3;
create table person(
  id int primary key auto_increment,
  NAME varchar(20)
);
insert into person values (null,'张三'),(null,'李四');
create  table card(
    id INT primary key auto_increment,
    number varchar(20) unique not null ,
    pid int unique,
    constraint cp_fk1 foreign key (pid) references person(id)
);
insert into card values (null,'12345',1),(null,'56789',2);

一对多
- 适用场景
  - 用户和订单. 一个用户可以有多个订单
  - 商品分类和商品. 一个分类下可以有多个商品
- 建表原则
  - 在多的一方, 建立外键约束, 来关联一的一方主键

## 一对多
use mydb3;
create table USER(
    id int primary key auto_increment,
    NAME varchar(20)
);
insert into USER values (null,'张三'),(null,'李四');

create table orderlist(
    id int primary key auto_increment,
    number varchar(20),
    uid int,
    constraint ou_fk1 foreign key (uid) references USER(id)
);
insert into orderlist values (null,'hm001',1),(null,'hm002',1),(null,'hm003',2),(null,'hm004',2);

## 一对多示例二
create table category(
    id int primary key auto_increment,
    NAEM varchar(10)
);
insert into category values (null,'手机数码'),(null,'电脑办公');
create table product(
    id int primary key auto_increment,
    NAME varchar(30),
    cid int,
    constraint pc_fk1 foreign key (cid) references category(id)
);
insert into product values (null,'华为P30',1),(null,'小米note3',1),(null,'联想电脑',2),(null,'苹果电脑',2);

多对多
- 适用场景
  - 学生和谭程. 一个学生可以选择多个课程, 一个课程也可以被多个学生选择
- 建表原则
  - 需要借助第三张中间表, 中间表至少包含2个列. 这2个列作为中间表的外键, 分别关联2张表的主键.

use mydb3;
show tables ;
create table student(
    id int primary key auto_increment,
    NAME varchar(20)
);
insert into student values (null,'张三'),(null,'李四');

create table course(
    id int primary key auto_increment,
    NAME varchar(10)
);
insert into course values (null,'语文'),(null,'数学');

create table stu_course(
    id int primary key auto_increment,
    sid int,
    cid int,
    constraint sc_fk1 foreign key (sid) references student(id),
    constraint sc_fk2 foreign key (cid) references course(id)
);
insert into stu_course values (null,1,1),(null,1,2),(null,2,1),(null,2,2);

多表查询

多表查询分类
- 内连接查询
- 外连接查询
- 子查询
- 自关联查询
多表查询数据准备

# 多表操作数据准备
create database mydb4;
use mydb4;
create table USER(
    id int primary key auto_increment,
    NAME varchar(20),
    age int
);
insert into USER values (1,'张三',23);
insert into USER values (2,'李四',24);
insert into USER values (3,'王五',25);
insert into USER values (4,'赵六',26);

create table orderlist(
    id int primary key auto_increment,
    number varchar(30),
    uid int,
    constraint ou_fk1 foreign key (uid) references USER(id)
);
insert into orderlist values (1,'hm001',1);
insert into orderlist values (2,'hm002',1);
insert into orderlist values (3,'hm003',2);
insert into orderlist values (4,'hm004',2);
insert into orderlist values (5,'hm005',3);
insert into orderlist values (6,'hm006',3);
insert into orderlist values (7,'hm007',null);

create table category(
    id int primary key auto_increment,
    NAEM varchar(10)
);
insert into category values (1,'手机数码');
insert into category values (2,'电脑办公');
insert into category values (3,'烟酒茶糖');
insert into category values (4,'靴靴箱包');
insert into category values (null,'靴靴箱包');

create table product(
    id int primary key auto_increment,
    NAME varchar(30),
    cid int,
    constraint cp_fk1 foreign key (cid) references category(id)
);
insert into product values (1,'华为手机',1);
insert into product values (2,'小米手机',1);
insert into product values (3,'联想电脑',2);
insert into product values (4,'苹果电脑',2);
insert into product values (5,'中华香烟',3);
insert into product values (6,'玉溪香烟',3);
insert into product values (7,'计生用品',null);

create table us_pro(
    upid int primary key auto_increment,
    uid int,
    pid int,
    constraint up_fk1 foreign key (uid) references USER(id),
    constraint up_fk2 foreign key (pid) references product(id)
);
insert into us_pro values (null,1,1);
insert into us_pro values (null,1,2);
insert into us_pro values (null,1,3);
insert into us_pro values (null,1,4);
insert into us_pro values (null,1,5);
insert into us_pro values (null,1,6);
insert into us_pro values (null,1,7);
insert into us_pro values (null,2,1);
insert into us_pro values (null,2,2);
insert into us_pro values (null,2,3);
insert into us_pro values (null,2,4);
insert into us_pro values (null,2,5);
insert into us_pro values (null,2,6);
insert into us_pro values (null,2,7);
insert into us_pro values (null,3,1);
insert into us_pro values (null,3,2);
insert into us_pro values (null,3,3);
insert into us_pro values (null,3,4);
insert into us_pro values (null,3,5);
insert into us_pro values (null,3,6);
insert into us_pro values (null,3,7);
insert into us_pro values (null,4,1);
insert into us_pro values (null,4,2);
insert into us_pro values (null,4,3);
insert into us_pro values (null,4,4);
insert into us_pro values (null,4,5);
insert into us_pro values (null,4,6);
insert into us_pro values (null,4,7);

内连接查询

查询原理
- 内连接查询的是两张表有交集的部分数据(有主外键关联的数据)
查询语法
- 显式内连接: select 列名 from 表名1 [inner]join 表名2 on 条件;
- 隐式内连接: select 列名 from 表名1,表名2 where 条件;

# 显式内连接查询, 查询用户信息和对应的订单信息
select * from USER inner join orderlist on USER.id = orderlist.uid;
# 查询用户信息和对应的订单信息, 起别名
select * from USER as u inner join orderlist as o on u.id = o.uid;
# 查询用户姓名, 年龄 和 订单编号
select
    u.NAME,
    u.age,
    o.number
from
    USER u
        inner join
    orderlist o
    on
            u.id = o.uid;

# 隐式内连接, 查询用户姓名, 年龄 和 订单编号
select
    u.NAME,
    u.age,
    o.number
from
    USER u,
    orderlist o
where
        u.id = o.uid;

外连接查询

左外连接
- 查询原理: 查询左表的全部数据, 和左右两张表有交集部分的数据
- 查询语法: select 列名 from 表名1 left [outer] join 表名2 on 条件;
右外连接
- 查询原理: 查询右表的全部数据, 和左右两张表有交集部分的数据
- 查询语法: select 列名 from 表名1 right [outer] join 表名2 on 条件;

# 查询语法: select 列名 from 表名1 left [outer] join 表名2 on 条件;
select
    u.*,o.number
from
    USER u
        left join
    orderlist o
    on u.id = o.uid;
# 查询语法: select 列名 from 表名1 right [outer] join 表名2 on 条件;
select
    o.*,u.name
from
    USER u
        right join
    orderlist o
    on u.id = o.uid;

子查询

子查询概念
- 查询语句中嵌套了查询语句, 我们就将嵌套的查询称为子查询.
结果是单行单列的
- 查询作用: 可以将查询的结果作用为另一条语句的查询条件, 使用运算符判断. =»=«=等.
- 查询语法: select 列名 from 表名 where 列名=(select 列名from 表名 [where 条件])

# 查询年龄最高的用户姓名
select * from USER;
select name,max(age) from USER; # 错误使用
select NAME,age from USER where age=(select max(age) from USER);

结果是多行单列的
- 查询作用: 可以作为条件, 使用运算符IN或NOT IN进行判断
- 查询语法: select 列名 from 表名 where 列名 [not] in (select 列名 from 表名 [where 条件]);

# 查询张三和李四的订单信息
select * from orderlist where uid in (1,2);
select id from USER where NAME in ('张三','李四');
select * from orderlist where uid in (select id from USER where NAME in ('张三','李四'));

结果是多行多列的
- 查询作用: 查询结果可以作为一张虚拟表参与查询
- 查询语法: select 列名 from 表名 [别名],(select 列名 from 表名 [where条件]) [别名][where 条件];

# 查询订单表中id大于4的订单信息和所属用户信息
select * from orderlist where id>4;
select
    u.name,
    o.number
from
    USER u,
     (select * from orderlist where id>4) o
where
    o.uid = u.id;

自关联查询

自关联查询
- 在同一张表中数据有关联性, 我们可以把这张表当成多个表来查询

# 创建数据
create table employee(
    id int primary key auto_increment,
    NAME varchar(20),
    mgr int,
    salary double
);
insert into employee values
(1001,'孙悟空',1005,9000.00),
(1002,'猪八戒',1005,8000.00),
(1003,'沙和尚',1005,8500.00),
(1004,'小白龙',1005,7900.00),
(1005,'唐僧',null,15000.00),
(1006,'武松',1009,7600.00),
(1007,'李逵',1009,7400.00),
(1008,'林冲',1009,8100.00),
(1009,'宋江',null,16000.00);
# 查询所有员工的姓名及其直接上级的姓名, 没有上级的员工也需要查询
# empployee.mgr = employee.id
# 左表的全部数据, 和左右2张表有交集的部分, 左外连接
select
    e1.id,
    e1.NAME,
    e1.mgr,
    e2.id,
    e2.NAME
from
    employee e1
        left join
    employee e2
    on
            e1.mgr=e2.id;

多表查询练习

搞清哪几个表

表之间的连接方式

left join 不能与 on和where后and联用,会不起作用

# 查询用户的编号,姓名,年龄,订单编号
select u.id,u.NAME,u.age, o.number
from USER u inner join orderlist o
on u.id=o.uid;
select u.id,u.NAME,u.age, o.number
from USER u, orderlist o
where u.id=o.uid;
# 查询所有的用户. 用户的编号,姓名,年龄,订单编号
select u.id,u.NAME,u.age,o.number
from USER u left join orderlist o
on u.id=o.uid;
# 查询所有的订单. 用户的编号,姓名,年龄,订单编号
select u.id,u.NAME,u.age,o.number
from USER u right join orderlist o
on u.id=o.uid;
# 查询用户年龄大于23岁的信息. 显示用户的编号,姓名,年龄,订单编号
select u.id,u.NAME,u.age,o.number
from
    (select * from USER where age>23) as u,
    orderlist o
where u.id = o.uid;
select u.id,u.NAME,u.age,o.number
from USER u ,orderlist o
 where u.id = o.uid and u.age>23;
# 查询张三和李四用户的信息. 显示用户的编号,姓名,年龄,订单编号
select u.id,u.NAME,u.age,o.number
from USER u inner join orderlist o on u.id = o.uid and u.NAME in ('张三','李四');
# 查询商品分类的编号,分类名称,分类下的商品名称
select c.id,c.NAEM,p.NAME
from category c ,product p
where c.id = p.cid;
# 查询所有的商品分类. 商品分类的编号,分类名称,分类下的商品名称
select c.id,c.NAEM,p.NAME
from category c left join product p
on c.id = p.cid;
# 查询所有的商品信息. 商品分类的编号,分类名称,分类下的商品名称
select c.id,c.NAEM,p.NAME
from category c right join product p
on c.id = p.cid;
# 查询所有用户和所有的商品. 显示用户的编号,姓名,年龄,商品名称
# USER product us_pro中间表
# 条件 USER.id=us_pro.uid and us_pro.pid = product.id
select u.id,u.NAME,u.age,p.NAME
from USER u, product p, us_pro up
where u.id = up.uid and p.id = up.pid;
# 查询张三和李四这2个用户可以看到的商品. 显示用户的编号,姓名,年龄,商品名称
# USER product us_pro中间表
# 条件 USER.id=us_pro.uid and us_pro.pid = product.id and USER.NAME in ('张三','李四')
select u.id,u.NAME,u.age,p.NAME
from USER u, product p, us_pro up
where u.id = up.uid and p.id = up.pid and u.NAME in ('张三','李四');

数据库安装和设置UTF-8#

SQL 介绍#

DDL 查询和创建数据库#

DDL 修改 删除 使用数据库#

DDL 查询数据表#

DDL 创建数据表#

DDL 修改数据表#

DDL 删除数据表#

DML 新增表数据#

DML 修改和删除表数据#

DQL 表数据查询–数据准备#

DQL 表数据查询–查询全部#

DQL 表数据查询–条件查询#

DQL 表数据查询–聚合函数查询#

DQL 表数据查询–排序查询#

DQL 表数据查询–分级查询#

DQL 表数据查询–分页查询#

MySQL 外键约束#

外键的级联更新和级联删除#

约束#

主键约束#

主键自增约束#

唯一约束#

非空约束#

多表操作#

多表查询#

内连接查询#

外连接查询#

子查询#

自关联查询#

多表查询练习#

搞清哪几个表#

表之间的连接方式#

数据库安装和设置UTF-8

SQL 介绍

DDL 查询和创建数据库

DDL 修改删除使用数据库

DDL 查询数据表

DDL 创建数据表

DDL 修改数据表

DDL 删除数据表

DML 新增表数据

DML 修改和删除表数据

DQL 表数据查询–数据准备

DQL 表数据查询–查询全部

DQL 表数据查询–条件查询

DQL 表数据查询–聚合函数查询

DQL 表数据查询–排序查询

DQL 表数据查询–分级查询

DQL 表数据查询–分页查询

MySQL 外键约束

外键的级联更新和级联删除

约束

主键约束

主键自增约束

唯一约束

非空约束

多表操作

多表查询

内连接查询

外连接查询

子查询

自关联查询

多表查询练习

搞清哪几个表

表之间的连接方式