基于MySQL的SQL语句全讲解

2022-07-18 00:00:00 查询 字段 商品 分类 约束



本文目录:

    一、SQL语句
    二、SQL约束
    三、SQL语句(DQL)
    四、多表操作
    五、多表关系实战
    六、多表查询

一、SQL语句

1. SQL概述

SQL语句介绍

数据库是不认识JAVA语言的,但是我们同样要与数据库交互,这时需要使用到数据库认识的语言SQL语句,它是数据库的代码。结构化查询语言(Structured Query Language)简称SQL,是关系型数据库管理系统都需要遵循的规范。不同的数据库生产厂商都支持SQL语句,但都有特有内容。

SQL语句分类

  • SQL分类:
    • 数据定义语言:简称DDL(Data Definition Language),用来定义数据库对象:数据库,表,列等。关键字:create,alter,drop等
    • 数据操作语言:简称DML(Data Manipulation Language),用来对数据库中表的记录进行更新。关键字:insert,delete,update等
    • 数据控制语言:简称DCL(Data Control Language),用来定义数据库的访问权限和安全级别,及创建用户。
    • 数据查询语言:简称DQL(Data Query Language),用来查询数据库中表的记录。关键字:select,from,where等

SQL通用语法

  • SQL语句可以单行或多行书写,以分号结尾
  • 可使用空格和缩进来增强语句的可读性
  • MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
    • 例如:SELECT * FROM user。
  • 同样可以使用/**/的方式完成注释
  • MySQL中的我们常使用的数据类型如下
**类型名称 ** 说明
int(integer) 整数类型
double 小数类型
decimal(m,d) 指定整数位与小数位长度的小数类型
date 日期类型,格式为yyyy-MM-dd,包含年月日,不包含时分秒
datetime 日期类型,格式为 YYYY-MM-DD HH:MM:SS,包含年月日时分秒
timestamp 日期类型,时间戳
varchar(M) 文本类型, M为0~65535之间的整数

2. DDL之数据库操作:database

创建数据库

格式:

create database 数据库名;
create database 数据库名 character set 字符集;

例如:

#创建数据库 数据库中数据的编码采用的是安装数据库时指定的默认编码 utf8
CREATE DATABASE webdb_1;
#创建数据库 并指定数据库中数据的编码
CREATE DATABASE webdb_2 CHARACTER SET utf8;

查看数据库

查看数据库MySQL服务器中的所有的数据库:

show databases;

查看某个数据库的定义的信息:

show create database 数据库名;

例如:

show create database webdb_1;

删除数据库

drop database 数据库名称;

例如:

drop database webdb_2;

使用数据库

  • 查看正在使用的数据库:
select database();
  • 其他的数据库操作命令

    切换数据库:

use 数据库名;
例如:
use webdb_1;

3. DDL之表操作:table

创建表

  • 格式:
create table 表名(
字段名 类型(长度) [约束],
字段名 类型(长度) [约束],
...
);

类型:
varchar(n) 字符串
int 整形
double 浮点
date 时间
timestamp 时间戳
约束:(详情查看第四章)
primary key 主键,被主键修饰字段中的数据,不能重复、不能为null
  • 例如:创建分类表
CREATE TABLE category (
cid INT primary key, #分类ID
cname VARCHAR(100) #分类名称
);

查看表

  • 查看数据库中的所有表:
格式:show tables;
  • 查看表结构:
格式:desc 表名;
例如:desc category;

删除表

  • 格式:drop table 表名;
例如:drop table category;

修改表结构格式

  • alter table 表名 add 列名 类型(长度) [约束];

    作用:修改表添加列.

例如:#1,为分类表添加一个新的字段为 分类描述 varchar(20)
ALTER TABLE category ADD `desc` VARCHAR(20);
  • alter table 表名 modify 列名 类型(长度) 约束;

    作用:修改表修改列的类型长度及约束.

例如:#2, 为分类表的描述字段进行修改,类型varchar(50) 添加约束 not null
ALTER TABLE category MODIFY `desc` VARCHAR(50) NOT NULL;
  • alter table 表名 change 旧列名 新列名 类型(长度) 约束; 作用:修改表修改列名.
例如:#3, 为分类表的分类名称字段进行更换 更换为 description varchar(30)
ALTER TABLE category CHANGE `desc` description VARCHAR(30);
  • alter table 表名 drop 列名;

    作用:修改表删除列.

例如:#4, 删除分类表中description这列
ALTER TABLE category DROP description;
  • rename table 表名 to 新表名;

    作用:修改表名

例如:#5, 为分类表category 改名成 category2
RENAME TABLE category TO category2;
  • alter table 表名 character set 字符集(了解);

    作用:修改表的字符集

例如:#6, 为分类表 category 的编码表进行修改,修改成 gbk
ALTER TABLE category CHARACTER SET gbk;

4. DML数据操作语言

插入表记录:insert

  • 语法:
-- 向表中插入某些字段
insert into 表 (字段1,字段2,字段3..) values (值1,值2,值3..);
-- 向表中插入所有字段,字段的顺序为创建表时的顺序
insert intovalues (值1,值2,值3..);
  • 注意:
    • 值与字段必须对应,个数相同,类型相同
    • 值的数据大小必须在字段的长度范围内
    • 除了数值类型外,其它的字段类型的值必须使用引号引起。(建议单引号)
    • 如果要插入空值,可以不写字段,或者插入null。
  • 例如:
INSERT INTO category(cid,cname) VALUES('c001','电器');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','书籍');

INSERT INTO category(cid) VALUES('c005');
INSERT INTO category(cname,cid) VALUES('耗材','c006');

更新表记录:update

用来修改指定条件的数据,将满足条件的记录指定列修改为指定值

  • 语法:

    update 表名 set 字段名=值,字段名=值,...;
    update 表名 set 字段名=值,字段名=值,... where 条件;
    • 更新符合条件记录的指定字段
    • 更新所有记录的指定字段
  • 注意:

    • 列名的类型与修改的值要一致.
    • 修改值得时候不能超过大长度.
    • 除了数值类型外,其它的字段类型的值必须使用引号引起

删除记录:delete

  • 语法:
delete from 表名 [where 条件];

5. DOS操作数据乱码解决

我们在dos命令行操作中文时,会报错

insert into category(cid,cname) values(‘c010’,’中文’);		
ERROR 1366 (HY000): Incorrect string value: '\xB7\xFE\xD7\xB0' for column 'cname' at row 1

错误原因:因为mysql的客户端设置编码是utf8,而系统的cmd窗口编码是gbk

  1. 查看MySQL内部设置的编码
show variables like 'character%'; 查看所有mysql的编码
  1. 需要修改client、connection、results的编码一致(GBK编码)

    解决方案1:在cmd命令窗口中输入命令,此操作当前窗口有效,为临时方案。

set names gbk;
解决方案2:安装目录下修改my.ini文件,重启服务所有地方生效。

二、SQL约束

1. 主键约束

PRIMARY KEY 约束标识数据库表中的每条记录。

  • 主键必须包含的值。
  • 主键列不能包含 NULL 值。
  • 每个表都应该有一个主键,并且每个表只能有一个主键。

添加主键约束

  • 方式一:创建表时,在字段描述处,声明指定字段为主键:
CREATE TABLE persons
(
id_p int PRIMARY KEY,
lastname varchar(255),
firstname varchar(255),
address varchar(255),
city varchar(255)
)
  • 方式二:创建表时,在constraint约束区域,声明指定字段为主键:
    • 格式:[constraint 名称] primary key (字段列表)
    • 关键字constraint可以省略,如果需要为主键命名,constraint不能省略,主键名称一般没用。
    • 字段列表需要使用小括号括住,如果有多字段需要使用逗号分隔。声明两个以上字段为主键,我们称为联合主键。
CREATE TABLE persons
(
firstname varchar(255),
lastname varchar(255),
address varchar(255),
city varchar(255),
CONSTRAINT pk_personID PRIMARY KEY (firstname,lastname)
)
  • 方式三:创建表之后,通过修改表结构,声明指定字段为主键:
    • 格式:ALTER TABLE persons ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)
CREATE TABLE persons
(
firstname varchar(255),
lastname varchar(255),
address varchar(255),
city varchar(255)
)
ALTER TABLE persons ADD PRIMARY KEY (firstname,lastname)

删除主键约束

如需撤销 PRIMARY KEY 约束,请使用下面的 SQL:

ALTER TABLE persons DROP PRIMARY KEY

2. 自动增长列

我们通常希望在每次插入新记录时,数据库自动生成字段的值。

我们可以在表中使用 auto_increment(自动增长列)关键字,自动增长列类型必须是整形,自动增长列必须为键(一般是主键)。

  • 下列 SQL 语句把 "persons" 表中的 "p_id" 列定义为 auto_increment 主键
CREATE TABLE persons
(
p_id int PRIMARY KEY AUTO_INCREMENT,
lastname varchar(255),
firstname varchar(255),
address varchar(255),
city varchar(255)
)
  • 向persons添加数据时,可以不为p_id字段设置值,也可以设置成null,数据库将自动维护主键值:
INSERT INTO persons (firstname,lastname) VALUES ('Bill','Gates')
INSERT INTO persons (p_id,firstname,lastname) VALUES (NULL,'Bill','Gates')
  • 扩展:默认AUTO_INCREMENT 的开始值是 1,如果希望修改起始值,请使用下列 SQL 语法:
ALTER TABLE persons AUTO_INCREMENT=100
  • 面试题

问:针对auto_increment ,删除表中所有记录使用 delete from 表名 或使用 truncate table 表名,二者有什么区别?

删除方式:
delete 一条一条删除,不清空auto_increment记录数。
truncate 直接将表删除,重新建表,auto_increment将置为零,从新开始。

3. 非空约束

NOT NULL 约束强制列不接受 NULL 值。

NOT NULL 约束强制字段始终包含值。这意味着,如果不向字段添加值,就无法插入新记录或者更新记录。

  • 下面的 SQL 语句强制 "id_p" 列和 "lastname" 列不接受 NULL 值:
CREATE TABLE persons
(
id_p int NOT NULL,
lastname varchar(255) NOT NULL,
firstname varchar(255),
address varchar(255),
city varchar(255)
)

4. 约束

UNIQUE 约束标识数据库表中的每条记录。UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了性的保证。PRIMARY KEY 拥有自动定义的 UNIQUE 约束。请注意,每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。

添加约束

与主键添加方式相同,共有3种

  • 方式一:创建表时,在字段描述处,声明:
CREATE TABLE persons
(
id_p int UNIQUE,
lastname varchar(255) NOT NULL,
firstname varchar(255),
address varchar(255),
city varchar(255)
)
  • 方式二:创建表时,在约束区域,声明:
CREATE TABLE persons
(
id_p int,
lastname varchar(255) NOT NULL,
firstname varchar(255),
address varchar(255),
city varchar(255),
CONSTRAINT 名称 UNIQUE (Id_P)
)
  • 方式三:创建表后,修改表结构,声明字段:
ALTER TABLE persons ADD [CONSTRAINT 名称] UNIQUE (Id_P)

删除约束

  • 如需撤销 UNIQUE 约束,请使用下面的 SQL:
ALTER TABLE persons DROP INDEX 名称
  • 如果添加约束时,没有设置约束名称,默认是当前字段的字段名。

5. 默认约束

default 约束 用于指定字段默认值。当向表中插入记录时,如果没有明确的为字段赋值,则自动赋予默认值。

  • 添加默认约束,在创建表时候添加
CREATE TABLE t_user(user_id INT(10) DEFAULT 3);
  • 删除默认约束
ALTER TABLE t_user MODIFY user_id INT(10);

三、SQL语句(DQL)

1. DQL准备工作和语法

准备工作

#创建商品表:
create table product(
pid int primary key,
pname varchar(20),
price double,
category_id varchar(32)
);
INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);

语法

select [distinct] 
* | 列名,列名
from
where 条件

2. 简单查询

  • 练习
#查询所有的商品.	
select * from product;
#查询商品名和商品价格.
select pname,price from product;
#别名查询.使用的关键字是asas可以省略的).表别名:
select * from product as p;
#别名查询.使用的关键字是asas可以省略的).列别名:
select pname as pn from product;
#去掉重复值.
select distinct price from product;
#查询结果是表达式(运算查询):将所有商品的价格+10元进行显示. 
select pname,price+10 from product;

3. 条件查询

比较运算符 <  <=  =   =  <> 大于、小于、大于(小于)等于、不等于

BETWEEN  ...AND... 显示在某一区间的值(含头含尾)

IN(set) 显示在in列表中的值,例:in(100,200)

LIKE ‘张pattern’ 模糊查询,Like语句中,% 代表零个或多个任意字符,_ 代表一个字符, 例如:first_name like '_a%';

IS NULL 判断是否为空
逻辑运行符 and 多个条件同时成立

or 多个条件任一成立

not 不成立,例:where not(salary>100);
  • 练习
#查询商品名称为“花花公子”的商品所有信息:
SELECT * FROM product WHERE pname = '花花公子'
#查询价格为800商品
SELECT * FROM product WHERE price = 800
#查询价格不是800的所有商品
SELECT * FROM product WHERE price != 800
SELECT * FROM product WHERE price <> 800
SELECT * FROM product WHERE NOT(price = 800)
#查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price > 60;
#查询商品价格在2001000之间所有商品
SELECT * FROM product WHERE price >= 200 AND price <=1000;
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
#查询商品价格是200800的所有商品
SELECT * FROM product WHERE price = 200 OR price = 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 '_想%';
#商品没有分类的商品
SELECT * FROM product WHERE category_id IS NULL

#查询有分类的商品
SELECT * FROM product WHERE category_id IS NOT NULL

4. 排序查询

通过order by语句,可以将查询出的结果进行排序。暂时放置在select语句的后。

  • 格式:
SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
#ASC 升序 (默认)
#DESC 降序
  • 练习:
#使用价格排序(降序)
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;

5. 聚合查询

之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询,它是对一列的值进行计算,然后返回一个单一的值;另外聚合函数会忽略空值。

今天我们学习如下五个聚合函数:

  • count:统计指定列不为NULL的记录行数;

  • sum:计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;

  • max:计算指定列的大值,如果指定列是字符串类型,那么使用字符串排序运算;

  • min:计算指定列的小值,如果指定列是字符串类型,那么使用字符串排序运算;

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

练习:

#查询商品的总条数
SELECT COUNT(*) FROM product;
#查询价格大于200商品的总条数
SELECT COUNT(*) FROM product WHERE price > 200;
#查询分类为'c001'的所有商品的总和
SELECT SUM(price) FROM product WHERE category_id = 'c001';
#查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id = 'c002';
#查询商品的大价格和小价格
SELECT MAX(price),MIN(price) FROM product;

6. 分组查询

分组查询是指使用group by字句对查询信息进行分组。

  • 格式:
SELECT 字段1,字段2FROM 表名 GROUP BY分组字段 HAVING 分组条件;

分组操作中的having子语句,是用于在分组后对数据进行过滤的,作用类似于where条件。

  • having与where的区别:

    • having是在分组后对数据进行过滤.

      where是在分组前对数据进行过滤

    • having后面可以使用分组函数(统计函数)

      where后面不可以使用分组函数。

练习:

#统计各个分类商品的个数
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;
#统计各个分类商品的个数,且只显示个数大于1的信息
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;

四、多表操作

实际开发中,一个项目通常需要很多张表才能完成。例如:一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系,接下来我们将在单表的基础上,一起学习多表方面的知识。

1. 表与表之间的关系

  • 一对一关系
    • 常见实例:客户和订单,商品和分类
    • 一个订单只能对应一个客户,一个商品只能对应一个分类
  • 一对多关系:
    • 常见实例:客户和订单,分类和商品,部门和员工.
    • 一个客户端可以对应多个订单
  • 多对多关系:
    • 常见实例:学生和课程、用户和角色
    • 多对多关系建表原则:需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键.

2. 外键约束

现在我们有两张表“分类表”和“商品表”,为了表明商品属于哪个分类,通常情况下,我们将在商品表上添加一列,用于存放分类cid的信息,此列称为:外键

     此时“分类表category”称为:主表,“cid”我们称为主键。“商品表products”称为:从表,category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系,呈现就是一对多关系。
  • 外键特点:

    • 从表外键的值是对主表主键的引用。
    • 从表外键类型,必须与主表主键类型一致。
  • 声明外键约束

语法:
alter table 从表 add [constraint][外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);

[外键名称]用于删除外键约束的,一般建议“_fk”结尾
alter table 从表 drop foreign key 外键名称
  • 使用外键目的:
    • 保证数据完整性

3. 一对多操作

分析

  • category分类表,为一方,也就是主表,必须提供主键cid
  • products商品表,为多方,也就是从表,必须提供外键category_id

实现:分类和商品

#创建分类表
create table category(
cid varchar(32) PRIMARY KEY ,
cname varchar(100) -- 分类名称
);

# 商品表
CREATE TABLE `products` (
`pid` varchar(32) PRIMARY KEY ,
`name` VARCHAR(40) ,
`price` DOUBLE
);

#添加外键字段
alter table products add column category_id varchar(32);

#添加约束
alter table products add constraint product_fk foreign key (category_id) references category (cid);

操作

#1 向分类表中添加数据
INSERT INTO category (cid ,cname) VALUES('c001','服装');

#2 向商品表添加普通数据,没有外键数据,默认为null
INSERT INTO products (pid,pname) VALUES('p001','商品名称');

#3 向商品表添加普通数据,含有外键信息(category表中存在这条数据)
INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名称2','c001');

#4 向商品表添加普通数据,含有外键信息(category表中不存在这条数据) -- 失败,异常
INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名称2','c999');

#5 删除指定分类(分类被商品使用) -- 执行异常
DELETE FROM category WHERE cid = 'c001';

4. 多对多

分析

  • 商品和订单多对多关系,将拆分成两个一对多。
  • products商品表,为其中一个一对多的主表,需要提供主键pid
  • orders 订单表,为另一个一对多的主表,需要提供主键oid
  • orderitem中间表,为另外添加的第三张表,需要提供两个外键oid和pid

实现:订单和商品

#商品表[已存在]

#订单表
create table `orders`(
`oid` varchar(32) PRIMARY KEY ,
`totalprice` double #总计
);

#订单项表
create table orderitem(
oid varchar(50),-- 订单id
pid varchar(50)-- 商品id
);

#订单表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_orders_fk foreign key (oid) references orders(oid);

#商品表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_product_fk foreign key (pid) references products(pid);

#联合主键(可省略)
alter table `orderitem` add primary key (oid,pid);

操作

#1 向商品表中添加数据
INSERT INTO products (pid,pname) VALUES('p003','商品名称');

#2 向订单表中添加数据
INSERT INTO orders (oid ,totalprice) VALUES('x001','998');
INSERT INTO orders (oid ,totalprice) VALUES('x002','100');

#3向中间表添加数据(数据存在)
INSERT INTO orderitem(pid,oid) VALUES('p001','x001');
INSERT INTO orderitem(pid,oid) VALUES('p001','x002');
INSERT INTO orderitem(pid,oid) VALUES('p002','x002');

#4删除中间表的数据
DELETE FROM orderitem WHERE pid='p002' AND oid = 'x002';

#5向中间表添加数据(数据不存在) -- 执行异常
INSERT INTO orderitem(pid,oid) VALUES('p002','x003');

#6删除商品表的数据 -- 执行异常
DELETE FROM products WHERE pid = 'p001';

五、多表关系实战

1. 实战1:省和市

  • 方案1:多张表,一对多

2. 实战2:用户和角色

  • 多对多关系

六、多表查询

提供表结构如下:

# 分类表
CREATE TABLE category (
cid VARCHAR(32) PRIMARY KEY ,
cname VARCHAR(50)
);

#商品表
CREATE TABLE products(
pid VARCHAR(32) PRIMARY KEY ,
pname VARCHAR(50),
price INT,
flag VARCHAR(2), #是否上架标记为:1表示上架、表示下架
category_id VARCHAR(32),
CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);

1. 初始化数据

#分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
#商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');

2. 多表查询

  1. 交叉连接查询(基本不会使用-得到的是两个表的乘积) [了解]
  • 语法:select * from A,B;
  1. 内连接查询(使用的关键字 inner join  -- inner可以省略)
  • 隐式内连接:select * from A,B where 条件;

  • 显示内连接:select * from A inner join B on 条件;

#1.查询哪些分类的商品已经上架
#隐式内连接
SELECT DISTINCT c.cname
FROM category c , products p
WHERE c.cid = p.category_id AND p.flag = '1';

#内连接
SELECT DISTINCT c.cname
FROM category c INNER JOIN products p
ON c.cid = p.category_id
WHERE p.flag = '1';
  1. 外连接查询(使用的关键字 outer join -- outer可以省略)
  • 左外连接:left outer join
    • select * from A left outer join B on 条件;
  • 右外连接:right outer join
    • select * from A right outer join B on 条件;
#2.查询所有分类商品的个数
#左外连接
INSERT INTO category(cid,cname) VALUES('c004','品');

SELECT cname,COUNT(category_id)
FROM category c LEFT OUTER JOIN products p
ON c.cid = p.category_id
GROUP BY cname;

下面通过一张图说明连接的区别:

3. 子查询

子查询:一条select语句结果作为另一条select语法一部分(查询条件,查询结果,表等)。语法select ....查询字段 ... from ... 表.. where ... 查询条件

#3 子查询, 查询“化妆品”分类上架商品详情
#隐式内连接
SELECT p.*
FROM products p , category c
WHERE p.category_id=c.cid AND c.cname = '化妆品';

#子查询
###作为查询条件
SELECT *
FROM products p
WHERE p.category_id =
(
SELECT c.cid FROM category c
WHERE c.cname='化妆品'
);

###作为另一张表
SELECT *
FROM products p ,
(SELECT * FROM category WHERE cname='化妆品') c
WHERE p.category_id = c.cid;

子查询练习:

#查询“化妆品”和“家电”两个分类上架商品详情
SELECT *
FROM products p
WHERE p.category_id in
(SELECT c.cid
FROM category c
WHERE c.cname='化妆品' or c.name='家电'
);

--END--

相关文章