MySQL基础知识

DDL之数据库操作: database

1.创建数据库

格式:
create database | schema 数据库名; #直接创建数据库，如果存在则报错
create database if not exists 数据库名; #如果数据库不存在则创建
create database 数据库名 character set 字符集; #创建数据库时设置字符集

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

show databases;

3.删除数据库

drop database 数据库名;

4.使用数据库

use 数据库名;
查看正在使用的数据库:
select database();

DDL之表操作: table

1.创建表

格式：
create table if not exists 表名(
字段名 类型(长度) [约束],
字段名 类型(长度) [约束],
...
);

类型：
varchar(n) 字符串
int 整型
double 浮点
date 时间
timestamp 时间戳
约束：
primary key 主键，被主键修饰字段中的数据，不能重复、不能为null

2.查看表

查看数据库中的所有表：
格式:show tables;

查看表结构：
格式：desc 表名;

3.删除表

格式：drop table 表名;

4.修改表结构格式

修改表添加列
alter table 表名 add 列名 类型(长度) [约束];
修改表修改列名
alter table 表名 change 旧列名 新列名 类型(长度) 约束;
修改表删除列
alter table 表名 drop 列名;
修改表名
rename table 表名 to 新表名;

DML数据操作语言

1.插入表记录:insert

语法：
-- 向表中插入某些字段
insert into 表 (字段1,字段2,字段3..) values(值1,值2,值3..);
-- 向表中插入所有字段,字段的顺序为创建表时的顺序
insert into 表 values(值1,值2,值3..)
注意：
值与字段必须对应，个数相同，类型相同
值的数据大小必须在字段的长度范围内
除了数值类型外，其它的字段类型的值必须使用引号引起.（建议单引号）’’ “”``
如果要插入空值，可以不写字段，或者插入 null

2.更新表记录:update

语法：
-- 更新所有记录的指定字段
update 表名 set 字段名=值,字段名=值,...;
-- 更新符号条件记录的指定字段
update 表名 set 字段名=值,字段名=值,... where 条件;
注意：
列名的类型与修改的值要一致.
修改值得时候不能超过最大长度.
除了数值类型外，其它的字段类型的值必须使用引号引起.

3.删除记录:delete

语法：
delete from 表名 [where 条件];
或者
truncate table 表名;
注意:
delete 一条一条删除，不清空auto_increment记录数.
truncate 直接将表删除，重新建表，auto_increment将置为零，从新开始.

除此之外：delete 和 truncate 还有以下区别：
1. Delete 可以按照行来删除，truncate 只能全表删除；
2. Delete 是DML 操作，truncate 是DDL 操作；
3. Delete 在删除前会锁行，truncate 删除前会锁表；
4. Truncate 删除比 delete 要快，不需要写日志；
5. Truncate 删除之后就不能恢复了， delete 可以恢复.

SQL约束

1.主键约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录.
主键必须包含唯一的值.
主键列不能包含 NULL 值.
每个表都应该有一个主键，并且每个表只能有一个主键.

1.添加主键约束
方式一:创建表时，在字段描述处，声明指定字段为主键.
方式二:创建表时，在constraint约束区域，声明指定字段为主键.
格式：[constraint 名称] primary key (字段列表)
关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用.
字段列表需要使用小括号括住，如果有多字段需要使用逗号分隔.声明两个以上字段为主键，我们称为联合主键.

2.删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;

3.自动增长列
ALTER TABLE 表名 AUTO_INCREMENT
注意:
我们可以在表中使用 auto_increment（自动增长列）关键字，
自动增长列类型必须是整形，自动增长列必须为键(一般是主键).

2.非空约束

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

3.唯一约束

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

4.外键约束

FOREIGN KEY

DQL数据查询语言

1.准备数据

#创建商品表：
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,'杰克琼斯',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,'海澜之家',1,'c002');

2.语法

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

3.简单查询

#1.查询所有的商品.
select * from product;
#2.查询商品名和商品价格.
select pname,price from product;
#3.别名查询.使用的关键字是as（as可以省略的）.
#3.1表别名:
select * from product as p;
#3.2列别名：
select pname as pn from product;
#4.去掉重复值.
select distinct price from product;
#5.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
select pname,price+10 from product;

4.条件查询

实例:
#查询商品名称为“花花公子”的商品所有信息：
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;
#查询商品价格在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);
#查询含有'霸'字的所有商品
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;

5.排序查询

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

格式:
SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
ASC 升序 (默认)
DESC 降序
#1.使用价格排序(降序)
SELECT * FROM product ORDER BY price DESC;
#2.在价格排序(降序)的基础上，以分类排序(降序)
SELECT * FROM product ORDER BY price DESC,category_id DESC;
#3.显示商品的价格(去重复)，并排序(降序)
SELECT DISTINCT price FROM product ORDER BY price DESC;

6.聚合查询

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

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

7.分组查询

分组查询是指使用group by字句对查询信息进行分组。
格式：
SELECT 字段1,字段2… FROM 表名 GROUP BY分组字段 HAVING 分组条件;
分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。
having 与 where 的区别:
1).having是在分组后对数据进行过滤.,where是在分组前对数据进行过滤
2).having后面可以使用分组函数(统计函数),where后面不可以使用分组函数。

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

8.分页查询

分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示
方式。例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。
格式：
SELECT 字段1，字段2... FROM 表名 LIMIT M,N
M: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数
N: 整数，表示查询多少条数据 offset 偏移量（每页显示多少条数据）
SELECT 字段1，字段2... FROM 表名 LIMIT 0,5
SELECT 字段1，字段2... FROM 表名 LIMIT 5,5
#查询product表的前5条记录
SELECT * FROM product LIMIT 0,5
等价于
SELECT * FROM product LIMIT 5

9.insert into select语句

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。
基本语法:
INSERT INTO table2
SELECT column_name(s)
FROM table1;

实例:
create table product2(
pid int primary key,
pname varchar(20),
price double
);
insert into product2 select pid,pname,price from product where category_id = 'c001';

多表操作

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

1.表与表之间的关系

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

2.外键约束

外键特点：
从表外键的值是对主表主键的引用。
从表外键类型，必须与主表主键类型一致。

声明外键约束
语法：
alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主
表 (主表的主键);
[外键名称] 用于删除外键约束的，一般建议“_fk”结尾
alter table 从表 drop foreign key 外键名称
使用外键目的：
保证数据完整性

3.一对多操作

实现分类和商品:

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

# 商品表
CREATE TABLE products (
pid varchar(32) PRIMARY KEY,
name VARCHAR(40) ,
price DOUBLE ,
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';

多表查询

1.数据准备

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_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');
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;
2.内连接查询(使用的关键字 inner join -- inner可以省略)
隐式内连接： select * from A,B where 条件;
显示内连接： select * from A inner join B on 条件;
3.外连接查询(使用的关键字 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 条件;

#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';
        
#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='家电'
);