10分钟学会SQL语句
|
433
|
0
|
SQL,未分类

1839 字
|
37 分钟

什么是数据库

In computing, a database is an organized collection of data stored and accessed electronically from a computer system.
在计算中，数据库是从计算机系统以电子方式存储和访问的数据的有组织的集合。

大白话就是，数据库就是存储数据的仓库，其本质是一个文件系统，数据库按照特定的格式将数据存储起来。用户可以对数据库中的数据进行增、删、改、查的操作。

为啥要使用数据库

数据可以存储在集合、文件中会遇到的问题：

  1. 集合：数据存储在内存中，一旦程序执行完毕了，数据消失了，数据不能永久性的储存。
  2. 文件：数据储存在磁盘中，可以永久性储存，但是当文件储存数据量达到几个G时，文件的打开

都成问题，数据的操作就更成问题了。

  1. 数据库：数据储存的磁盘中，可以永久性储存；当数据量很大时，数据操作起来也很流畅；适合数
    据频繁的增删改查的操作。

如何操作数据库

使用SQL语句。

1.数据库操作

创建数据库

-- UTF8mb4 格式
create database <database_name> character set UTF8mb4 collate utf8mb4_bin;

-- UTF8
create database <database_name> character set UTF8 collate utf8_bin;

查看数据库

-- 查看所有的数据库
show databases;

-- 查看指定数据库的字符编码. 格式: show create database <database_name>;
SHOW CREATE DATABASE test1;

删除数据库

-- 删除数据库. 格式: drop database 数据库名; 
DROP DATABASE test1;

使用数据库

-- 查看当前使用的数据库. 格式: select database(); 
SELECT DATABASE();

-- 设置当前使用的数据库. 格式: use 数据库名; 
USE test1;

2.表操作

创建表

/*
格式:
create table 表名 (
    字段名 数据类型[⻓度] [约束], 
    字段名 数据类型[⻓度] [约束], 
    ...
);
注:[]中的内容是可选项
*/

-- 创建表student, 字段包括 编号id 姓名name 年龄age 
CREATE TABLE student (
    id INT,
    NAME VARCHAR(100),
    age INT
);

查看表

-- 查看所有表, 格式: show tables
SHOW TABLES;

-- 查看指定表的建表结构, 格式: show create table 表名; 
SHOW CREATE TABLE student;

删除表

 -- 删除表, 格式: drop table 表名; 
 DROP TABLE student;

修改表结构格式

/*
对表中的列进行修改
1. 添加新的列, 格式: alter table 表名 add 新列名 数据类型(⻓度);
2. 修改列的数据类型(⻓度), 格式: alter table 表名 modify 列名 修改后的数据类型(⻓ 度);
3. 修改列的名称, 格式: alter table 表名 change 列名 新列名 新列名的数据类型(⻓度);
4. 删除指定列, 格式: alter table 表名 drop 列名;
*/
ALTER TABLE student ADD `desc` VARCHAR(100); -- 添加新的列
ALTER TABLE student MODIFY `desc` VARCHAR(50);-- 修改列的数据类型(⻓度) 
ALTER TABLE student CHANGE `desc` description VARCHAR(50);-- 修改列的名称 
ALTER TABLE student DROP description;-- 删除指定列

/*
对表进行修改
1. 修改表的名称, 格式: rename table 表名 to 新表名;
2. 修改表的字符编码, 格式: alter table 表名 character set 字符编码;
*/
RENAME TABLE student TO stu; -- 修改表的名称
ALTER TABLE stu CHARACTER SET gbk; -- 修改表的字符编码

3.表中记录操作

插入表记录

/*
插入表记录
方式一, 对指定的字段插入值, 格式: insert into 表名(字段1, 字段2, ...) values (值 1, 值2, ...);
方式二, 对所有字段插入值, 格式: insert into 表名 values(值1, 值2, ...);
*/
INSERT INTO student(id, NAME, age) VALUES(1, 'tom', 24);
INSERT INTO student(NAME, age) VALUES('lili', 22);
INSERT INTO student(id, NAME, age) VALUES(3, 'jim', NULL);

-- 对所有字段插入值
INSERT INTO student VALUES(4, 'jack', 26);
INSERT INTO student VALUES(5, 'zhangsan', 26), (6,'lisi',27);

注意:
1. 值与字段必须对应, 个数相同, 类型相同
2. 值的数据大小必须在字段的指定⻓度范围内
3. 除了整数、小数类型外, 其他字段类型的值必须使用引号引起来 (建议单引号) 
4. 如果要插入空值, 可以不写字段, 或者插入null

删除表记录

-- 删除表记录, 格式: delete from 表名 where 条件; 
DELETE FROM student WHERE id=1;
DELETE FROM student WHERE age IS NULL;

4.SQL约束

什么是约束？

约束, 其实就是一种限制条件, 让你不能超出这个控制范围.而在数据库中的约束, 就是指表中的数据内容不能胡乱填写, 必须按照要求填写. 好保证数据的完整性与安全性.

主键约束

PRIMARY KEY 约束:

  • 主键必须包含唯一的值, 不能重复。
  • 主键列不能包含 NULL 值。
  • 每个表都应该有一个主键，并且每个表只能有一个主键。
添加主键约束
/** 
方式一:
创建表时，在字段描述处，声明指定字段为主键。格式: 字段名 数据类型[⻓度] PRIMARY KEY
*/
CREATE TABLE person(
  pid int primary key, -- 添加了主键约束 
  lastname varchar(255),
  firstname varchar(255),
  address varchar(255)
);

/**
方式二:
创建表时，在constraint约束区域，声明指定字段为主键。格式: [constraint 名称] primary key (字段列表) 
关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用。 
字段列表需要使用小括号括住，如果有多字段需要使用逗号分隔。声明两个以上字段为主键，我们称为联合主键。
*/
CREATE TABLE person (
  pid int,
  lastname varchar(255),
  firstname varchar(255),
  address varchar(255),
  constraint pk_person primary key (pid) -- 添加主键约束, 单一字段
);

CREATE TABLE person (
  pid INT,
  lastname VARCHAR(255),
  firstname VARCHAR(255),
  address VARCHAR(255),
  CONSTRAINT pk_person PRIMARY KEY (lastname, firstname) -- 添加主键约束,多个字段, 我们称为联合主键。
);

INSERT INTO person VALUES(1, '星驰','周','香港');
INSERT INTO person VALUES(1, '德华','刘','香港');
INSERT INTO person VALUES(2, '德华','刘',NULL); -- 插入失败

/**
方式三:
创建表之后，通过修改表结构，声明指定字段为主键。格式: ALTER TABLE 表名 ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)
*/
CREATE TABLE person (
  pid int,
  lastname varchar(255),
  firstname varchar(255),
  address varchar(255),
);
alter table person add constraint pk_person primary key (lastname, firstname); -- 添加联合主键
删除主键约束
/**
如需删除 PRIMARY KEY 约束，请使用下面的 SQL。 
格式: ALTER TABLE 表名 DROP PRIMARY KEY
*/
alter table person drop primary key;

自动增⻓列

我们通常希望在每次插入新记录时，数据库自动生成字段的值。
我们可以在表中使用 auto_increment(自动增⻓列)关键字，自动增⻓列类型必须是整形，自动增⻓列必须为键(通常是用于主键)。

CREATE TABLE person (
  pid INT PRIMARY KEY AUTO_INCREMENT,
  lastname VARCHAR(255),
  firstname VARCHAR(255),
  address VARCHAR(255)
);

-- 向person添加数据时，可以不为pid字段设置值，也可以设置成null，数据库将自动维护主键值:
insert int operson(lastname,firstname,address) values('富成','郭','香港'); 
insert int operson(pid,lastname,firstname,address) values(null,'⻰','成','香港');

-- 扩展:默认AUTO_INCREMENT 的开始值是 1，如果希望修改起始值，请使用下列 SQL 语法:
alter table person auto_increment=100;

非空约束

/**
  添加非空约束
  格式: 字段名 数据类型[⻓度] NOT NULL
*/
CREATE TABLE person (
  pid INT PRIMARY KEY AUTO_INCREMENT,
  lastname VARCHAR(255) not null,
  firstname VARCHAR(255) not null,
  address VARCHAR(255)
);

/**
    删除非空约束
    格式: ALTER TABLE 表名 MODIFY 字段名 数据类型[⻓度]
*/
 alter table person modify lastname varchar(255); 
 alter table person modify firstname varchar(255);

唯一约束

添加唯一约束
/**
  与主键添加方式相同，共有3种 
  方式一:
  创建表时，在字段描述处，声明唯一:
  格式: 字段名 数据类型[⻓度] UNIQUE
*/
CREATE TABLE person(
  pid INT,
  lastname VARCHAR(255),
  firstname VARCHAR(255),
  address VARCHAR(255) UNIQUE -- 添加唯一约束
);
INSERT INTO person(pid,lastname,firstname,address)VALUES(1,'富 成','郭','香港'); 
INSERT INTO person(pid,lastname,firstname,address)VALUES(2,'润 发','周','香港');-- 插入失败,值重复

/**
方式二:
创建表时，在约束区域，声明唯一: 
格式: [constraint 名称] UNIQUE (字段)
*/
CREATE TABLE person (
  pid INT,
  lastname VARCHAR(255),
  firstname VARCHAR(255),
  address VARCHAR(255),
  CONSTRAINT uni_address UNIQUE (address) -- 添加唯一约束
);

/**
 方式三:创建表后，修改表结构，声明字段唯一:
 格式: ALTER TABLE 表名 ADD [CONSTRAINT 名称] UNIQUE (字段)
*/
CREATE TABLE person (
  pid INT,
  lastname VARCHAR(255),
  firstname VARCHAR(255),
  address VARCHAR(255)
);
ALTER TABLE person ADD CONSTRAINT uni_address UNIQUE (address) -- 添加唯一约束
删除唯一约束
/**
删除 UNIQUE 约束， 
*/    

-- 有唯一约束名称, 使用约束名称删除
 alter table person drop index uni_person_address; 

 -- 如果添加唯一约束时，没有设置约束名称，默认是当前字段的字段名。
 -- 格式: ALTER TABLE 表名 DROP INDEX 名称
 alter table person drop index address; -- 没有唯一约束名称, 使用字段名删除

默认约束

/**
  添加默认约束
  在创建表时候添加，格式: 字段名 数据类型[⻓度] DEFAULT 默认值
*/
CREATE TABLE person( 
  pid INT,
  lastname VARCHAR(255),
  firstname VARCHAR(255),
  address VARCHAR(255) DEFAULT '香港' -- 添加默认约束
);
INSERT INTO person(pid,lastname,firstname,address)VALUES(1,'富 成','郭','北京');
INSERT INTO person(pid,lastname, firstname) VALUES(2,'⻰','成'); -- 使用 address的默认值
INSERT INTO person(pid,lastname,firstname,address)VALUES(3,'润发','周', NULL);

/**
    删除默认约束
    格式: ALTER TABLE 表名 MODIFY 字段名 数据类型[⻓度]
*/
ALTER TABLE person MODIFY address VARCHAR(255);

5.SQL语句(DQL)

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 条件

-- 查询所有的商品.
SELECT * from product;

-- 查询商品名和商品价格.
select pname,price from product;

-- 查询价格,去掉重复值.
select DISTINCT price from product;

-- 查询结果是表达式(运算查询):将所有商品的价格+10元进行显示. 
select pname,price+10 from product;

-- 别名查询.使用的关键字是as(as可以省略的).列别名
select pname,price+10 as '价格' from product;
select pname,price+10 '价格' from product;

-- 别名查询.使用的关键字是as(as可以省略的).表别名
select * from product as p;
select * from product p;

条件查询

比较运算符< <= > >= = <>小于、大于(小于)、大于、大于(等于)、等于、不等于
BETWEEN …AND…显示在某一区间的值(含头含尾)
IN(set)显示在in列表中的值，例:in(100,200)
LIKE ‘字符’模糊查询，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; -- mysql特有的符号 

#查询商品价格大于60元的所有商品信息
select * from product where price>60;

#查询商品价格在200到1000之间所有商品
select * from product where price>=200 and price<=1000; 
select * from product where price between 200 and 1000; 

#查询商品价格是200或800的所有商品
select * from product where price=200 or price=800; 
select * from product where price in(200,800);

# LIKe 中的%代表零个或多个任意字符; _代表匹配一个任意字符 
#查询商品名称含有'霸'字的所有商品
select * from product where pname like '%霸%'; 

#查询商品名称以'香'开头的所有商品
select * from product where pname like '香%'; 

#查询商品名称第二个字为'想'的所有商品
select * from product where pname like '_想%'; 

#商品没有分类id的商品
select * from product where category_id is NULL; 

#查询有分类id的商品
select * from product where category_id is NOT NULL;

排序查询

/**
通过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;

聚合查询

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

今天我们学习如下五个聚合函数:
count:统计指定列不为NULL的记录行数; 
sum:计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0; 
max:计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算;
min:计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算;
avg:计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0;
*/
#查询商品的总条数
select count(*) from product; -- 不推荐 
select count(pid) from product;
select count(category_id) 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;

分组查询

/**
分组查询是指使用group by字句对查询信息进行分组。
格式:
SELECT 字段1,字段2... FROM 表名 GROUP BY 分组字段 HAVING 分组条件; 

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

having与where的区别:
    having是在分组操作执行后, 对分组后的数据进行过滤.
    where是在分组操作执行前, 对分组前的数据 只能使用表原始列进行条件过滤
    having后面可以使用 聚合函数
    where后面不可以使用 聚合函数。
    当一条SQL语句中, 既有where 又有 group by \ having时, 先执行 where, 再执行 group by, 最后执行having
*/

#统计各个分类商品的个数
select category_id, count(*) from product group by category_id;

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

#统计价格>200元的 各个分类商品的个数,且只显示个数大于1的信息
-- select category_id,count(*)from product where price>200 group by category_id ;
select category_id,count(*)from product where price>200 group by category_id HAVING count(*)>1;

条数限制查询！！！注意点(其他)

/**
    LIMIT是MySQL内置函数，其作用是用于限制查询结果的条数。
*/
格式: select * from 表名 limit m,n
其中: m是指记录开始的index，从0开始，表示第一条记录，n是指从第m+1条开始，取n条。
例如:
select * from tablename limit 2,4 -- 即取出第3条至第6条，4条记录


-- 注意点(其他)
Limit 单参数使用，表示返回的结果集最多有 count 条。
SELECT [col] FROM [table_name] LIMIT [count];

Limit 双参数使用
SELECT [col] FROM [table_name] LIMIT [offset],[count];

Limit 搭配 Offset 使用
SELECT [col] FROM [table_name] LIMIT [count] OFFSET [offset];

其中col表示字段名，table_name表示数据表名称，offset是偏移量，count是最大数量。


# 应用场合:分⻚
分⻚查询格式:
SELECT * FROM 表名 LIMIT startRow,pageSize;
例如:
select * from products limit 0,5; #第一⻚，每⻚显示5条。
select * from products limit 5,5; #第二⻚，每⻚显示5条。
select * from products limit 10,5; #第三⻚，每⻚显示5条。
select * from products limit startRow,5; #第curPage⻚，每⻚显示5条, startRow 的值如何计算呢?
-- 后台计算出⻚码、⻚数(⻚大小)
-- 分⻚需要的相关数据结果分析如下,
-- 注意:下面是伪代码不用于执行
int curPage = 2; -- 当前⻚数
int pageSize = 5; -- 每⻚显示数量
int startRow = (curPage - 1) * pageSize; -- 当前⻚, 记录开始的位置(行数)计算 
int totalSize = select count(*) from products; -- 记录总数量
int totalPage = Math.ceil(totalSize * 1.0 / pageSize); -- 总⻚数

6.表之间的关系

表与表之间的关系

一对一关系:

  • 在实际的开发中应用不多.因为一对一可以创建成一张表
  • 常⻅实例:身份证表与员工信息表

一对多关系:

  • 常⻅实例:客户和订单，分类和商品，部⻔和员工, 省份和城市

多对多关系:

  • 常⻅实例:学生和课程、用户和⻆色, 演员和电影, 商品和订单

外键约束

不推荐、禁止使用外键。
理由：影响开发效率和性能，对一张表的操作会受到另一张表的影响。

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

    此时“分类表category”称为:主表，“cid”我们称为主键。“商品表products”称为:从表， category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系，就这让表与表之间产生了关系。

    外键特点:
     从表外键的值是对主表主键的引用。
     从表外键类型，必须与主表主键类型一致。 声明外键约束

  使用外键目的: 
      保证数据完整性：
          - 要考虑从表添加数据。
          - 要考虑主键删除数据。
*/

 -- 添加外键方式：
 -- 方式1：表图形化界面工具，通过：add Relation
 -- 方式2：语法
  alter table 从表 add [constraint][外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);
  alter table products add constraint products_category_fk foreign key(category_id) references category(cid);
  -- 省略 constraint 也是OK的
    alter table products add constraint products_category_fk foreign key(category_id) references category(cid);

  -- [外键名称]用于删除外键约束的，一般建议“_fk”结尾
  删除外键语法：
  alter table 从表 drop foreign key 外键名称
  alter table products drop foreign key products_category_fk;

-- 初始化数据
# 分类表
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表示上架、0表示下架 
  category_id VARCHAR(32)
);
#分类
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');
INSERTINTOproducts(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');

外键约束的坏处：
-- 插入失败，因为 category 表没有c004
INSERT INTO products(pid,pname,price,flag,category_id)VALUES('p010','相
宜本草222',200,'1','c004');
-- 删除失败，因为这个主键被另外一个外键依赖。
DELETE FROM category WHERE cid='c002';

7.多表关系

省和市：

/**
方案1：多张表，一对多
*/
-- 创建省份表
create table province(
  pid int PRIMARY KEY,
  pname varchar(32), -- 省份名称 
  description varchar(100) -- 描述
);
-- 创建城市表
create table city (
  cid int PRIMARY KEY,
  cname varchar(32), -- 城市名称
  description varchar(100), -- 描述
  province_id int,
  CONSTRAINT city_province_fk foreign key(province_id) references province(pid)
);

/**
方案2：一张表，自关联一对多
*/
create table area (
    id int PRIMARY key AUTO_INCREMENT,
    `name` varchar(32),
    description varchar(100),
    parent_id int,
    CONSTRAINT area_area_fk FOREIGN KEY(parent_id) REFERENCES area(id)
);
INSERT into area values(null, '辽宁省', '这是一个省份', null); 
INSERT into area values(null, '大连市', '这是一个城市', 1); 
INSERT into area values(null, '沈阳市', '这是一个城市', 1); 
INSERT into area values(null, '河北省', '这是一个省份', null); 
INSERT into area values(null, '石家庄市', '这是一个城市', 4); 
INSERT into area values(null, '保定市', '这是一个城市', 4);

用户和⻆色：

-- 用户表
create table `user` (
    uid varchar(32) PRIMARY KEY,
    username varchar(32),
    `password` varchar(32)
);

-- ⻆色表
create table role (
    rid varchar(32) PRIMARY KEY,
    rname varchar(32)
);

-- 中间表
create table user_role(
    user_id varchar(32),
    role_id varchar(32),
    CONSTRAINT user_role_pk PRIMARY KEY(user_id,role_id),
    CONSTRAINT user_id_fk FOREIGN KEY(user_id) REFERENCES `user`(uid),
    CONSTRAINT role_id_fk FOREIGN KEY(role_id) REFERENCES role(rid)
);

8.多表查询

初始化数据

# 分类表
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表示上架、0表示下架 
  category_id VARCHAR(32),
  CONSTRAINT products_category_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);

#分类
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');
INSERTINTOproducts(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');

交叉连接查询

语法: select * from A,B;

# 效果: category、products中的字段都查询出来了，查询结果先全部按 cid 排序，所有 cid 相同的再按 pid 排序。
select * from category,products ORDER BY cid,pid;

内连接查询(使用的关键字 inner join — inner可以省略)

/**
隐式内连接: select * from A,B where 条件; 
显示内连接: select * from A inner join B on 条件;
*/
-- 查询那些分类的商品已经上架 -- 隐式内连接
SELECT DISTINCT
*
FROM
category c,
products p
WHERE c.cid = p.category_id;

-- 显示内连接 
SELECT DISTINCT
*
FROM 
category c INNER JOIN products p
ON c.cid = p.category_id;

外连接查询(使用的关键字 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;

子查询

/**
子查询:
一条select语句结果作为另一条select语法一部分(查询条件，查询结果，表等)。 
语法: select ....查询字段 ... from ... 表.. where ... 查询条件   
*/
例1：子查询
-- 子查询, 查询“化妆品”分类上架商品详情 
-- 内连接方式
select p.*
from category c, products p
WHERE
c.cid = p.category_id and c.cname = '化妆品'

-- 子查询方式 第一种(作为查询条件值使用) 
select * from products p
where
p.category_id = (SELECT cid from category where cname='化妆品')

-- 子查询方式 第二种(作为 一张表 使用) 
select p.*
FROM
products p, (select * from category where cname='化妆品') c
WHERE
    p.category_id = c.cid;


# 子查询
select
*
from
products
WHERE
category_id in (select cid from category where cname='家电' or cname='化妆品');
-- select cid from category where cname='家电' or cname='化妆品'; 
-- select cid from category where cname in ('家电', '化妆品');

参考链接：

菜鸟教程：MySQL 创建数据库

慕课网：

http://www.imooc.com/wiki/mysqllesson

https://www.runoob.com/mysql/mysql-create-database.html

SQL
下一篇