数据库（5）

数据库

1、数据库

2、数据的种类

关系型数据库：oracle、mysql，sql server等

非关系型数据库：redis、mongodb等

3、数据库关系

数据库的存储地址

RDBMS和数据库的关系

Relational Datebase Management System

一个数据库由多张表组成

多个数据库存储在数据库服务器上

4、SQL结构化数据查询语言

当前的关系型数据库，都支持SQL语言进行操作

4.1 DQL

数据查询语言，select

查看当前选择的数据库
SELECT DATABASE();

使用当前的数据库

USE pai0805;

查看当前数据库的表
SHOW TABLES;

查看数据表的结构
DESC classes;

查询当前表中的数据 *代表查询当前表中的所有字段
SELECT * FROM classes;

查询当前表中的数据，展示班级的名字
SELECT classes.classname FROM classes;
SELECT classname FROM classes;
SELECT classname AS ‘班级的名字’ FROM classes;

查询的语法结构
select 字段的名字 [as 别的名字] from 表名;

4.2 DML

数据操作语言，对数据进行增（insert）删（delete）改（update）操作

在数据库中，在表里面插入新数据

注意点：

自增字段，在插入数据的时候，需要用数据进行占位这个数据可以使（0，null，default）

全列插入的语法模式：

insert into 表名 values(字段1的值，字段2的值，字段3的值)

部分列插入

insert into 表名（列1，列2） values（字段1的值，字段2的值）

插入多行数据

部分列插入多行数据

insert into 表名（列1，列2） values（字段1的值，字段2的值），(字段1的值，字段2的值)…

全列插入多行数据

insert into 表名 values(字段1的值，字段2的值，字段3的值)，（字段1的值，字段2的值，字段3的值）。。。

修改数据

update 表名 set 列2=列2的值，列3=列3的值 where id=xx

where条件（方便定位到我们需要的数据）

4.3 DCL

数据控制语言，进行权限的回收和授权

4.4 DDL

数据定义语言，进行数据库表的管理，create，drop，

4.4.1 创建数据库

CREATE DATABASE pai0805;

CREATE DATABASE pai0805 CHARSET=utf8;（使用这个版本）

4.4.2 删除数据库

删除指定的数据库
DROP DATABASE if exists xxxxx；（推荐，保证表不存在的时候不会报错）

删除一张表

DROP TABLE if exists xxxxx；（推荐，保证表不存在的时候不会报错）

4.4.3 修改数据库

修改数据库的名字

rename database oldname to newname（新版本无法使用）

修改数据库字符集

alter database 数据库名 character set gbk

4.4.5 数据库常见的约束类型

not null 非空约束，保证当前的字段不能为空

default 默认值约束，如果当前的字段没有输入数据，使用默认值填充，保证该字段存在默认值

primary key 主键，表示我们物理上的存储顺序，这个字段不可能为空，保证数据的唯一性

foreign key 外键，限制两张表之间的关系，用来约束当前表与另外一张表之间的关系

unique 唯一值，表示该字段在当前的主句表中不重复

注意点：

我们创建外键的步骤：

FOREIGN KEY(本表中字段名) REFERENCES 关联表(字段名)（默认约束名）

CONSTRAINT 约束名 FOREIGN KEY(本表中的字段名) REFERENCES 关联表(字段名)（自定义约束名）

1、定义外键的字段名已经字段的类型，注意和关联表的字段要求一致

2、建立外键之间的连接foreign key（当前表的外键字段）reference 产生关联那张表的表名（外键连接字段）

3、班级和对应的学生表 班级（主表）学生表是从表，外键写在从表身上

外键

外键的作用：1、可以有效避免无效数据插入

2、可以使表相互关联

外键的不足：1、增加时间成本

2、容易出现插入错误

3、删除时需要将子表数据先删除

4.4.6 约束

4.4.6.1 添加字段约束

4.4.6.2 添加表级约束

4.4.7 主键与唯一约束的区别

4.4.7.1 组合主键

4.4.7.2 区别

4.4.8 修改数据库表

4.4.8.1 删除字段

alter table 表名 drop 字段名;

删除外键

1、解除约束alter table users drop foreign key 外键名

2、然后才能删除外键字段

alter table 表名 drop 字段名;

删除xxx的外键（创建表的过程中并没有设置索引名）

1、查看索引名 show create table 表名 或 show index table 表名

2、alter table 表名 drop foreign key 外键名；

3、alter table 表名 drop 字段名;

4.4.8.2 删除数据库表

drop table if exists xxxxx；（推荐，保证表不存在的时候不会报错）

drop table xxxxx；

4.4.8.3 创建数据库表

create table 表名

CREATE TABLE student1(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
stuname VARCHAR(20),
age SMALLINT UNSIGNED DEFAULT 18,
gender ENUM('女','男'),
height DECIMAL(3,2),
weight DECIMAL(5,2),
class_id INT UNSIGNED DEFAULT 0

);
CONSTRAINT uid PRIMARY KEY(id),#主键
CONSTRAINT uqkey UNIQUE(phone),#唯一值

4.4.8.4 修改数据库表

修改表名：

alter table 旧表名 rename to 新表名

修改字段的名字：

alter table 表名 change column 旧字段名 新字段名 数据类型 (后面还可以加上约束);

alter table 表名 modify column 字段名 数据类型 约束;(添加数据类型和约束)

综合

alter table 表名 change|modify 字段名 字段类型 字段约束;

注意单：

change需要修改的是字段的名字、数据类型、字段的约束。

modify需要修改的是字段的类型、字段的约束。

修改字段约束：

alter table 表名 drop index 字段名;(删除唯一值索引)

alter table 表名 drop PRIMARY KEY;(删除主键)

增加字段：

alter table 表名 add 字段名 字段类型 字段的约束;

删除字段：

alter table 表名 drop 字段名;

总结：

alter table 表名 add|drop|change|modify 字段名 字段的类型 字段的约束;

全列插入的语法模式：

insert into 表名 values(字段1的值，字段2的值，字段3的值)

部分列插入

insert into 表名（列1，列2） values（字段1的值，字段2的值）

插入多行数据

部分列插入多行数据

insert into 表名（列1，列2） values（字段1的值，字段2的值），(字段1的值，字段2的值)…

全列插入多行数据

insert into 表名 values(字段1的值，字段2的值，字段3的值)，（字段1的值，字段2的值，字段3的值）。。。

修改数据

update 表名 set 列2=列2的值，列3=列3的值 where id=xx

where条件（方便定位到我们需要的数据）

4.4.8.5 复制表结构

仅仅复制表结构，数据是不复制的

create table 表名 like 被复制的表名;

4.4.8.6 复制表

不仅复制表结构，也复制数据

create table 表名 select * from 被复制表名;

部分复制

create table 表名 select 字段一,字段二,… from 被复制表名;

内容检索复制

create table 表名 select 字段一,字段二,… from 被复制表名 where 引导条件;

4.4.9 总结

1、查看外键约束的约束名

show create table 表名;

2、解除外键字段的外键约束

alter table 表名 drop foreign key 外键名；

3、删除外键字段的外键名

alter table 表名 drop 字段名;

4.5 CCL

指针控制语言，通过控制指针来完成表的操作，declare cursor

4.6 TPL

事务管理语言，对事务进行管理 begin transaction、commit、rollback

CRUD（增删改查）：DQL,DML,DDL

数据库语言不区分大小写

5、具体操作

5.1 创建一个数据库

create database xxxx； 创建一个数据库
create database xxxx charset=utf8（可以使用中文）

查看当前选择的数据库

select database（）；

删除指定的数据库

drop database xxxxx；

使用当前的数据库、

use xxxxxx；

查看当前数据库的表

show tables;

创建一张表

create table if not exists xxxx;（推荐，保证表存在的时候不会报错）

create table xxxx;

5.2 创建数据库表

5.3 数据库的数据类型

5.3.1 整数

注意点：

使用zerofill 会使用0填充，但是会将数据的类型改成无符号数

并且int(7)写在此处，只是确定显示的位数，不是规定数值的长度

总结：

1、如果不设置有符号还是无符号，默认有符号，如果向设置无符号，unsigned关键字

2、如果插入的数值超出范围，会警告out of range value for column 字段名，插入的是临界值

3、如果不设置长度，会有默认长度

4、长度代表显示的最大宽度，如果不够会用0填充，搭配zerofill使用，可以看出效果

5.3.2 小数

decimal

特点：

1、M=小数位数+整数位数

D=小数位数

如果超出范围，则插入临界值

2、M、D都可以省略，但是

如果是decimal，则M为10，D为0

如果是float和double，根据插入的数值决定精度

3、定点型的精度较高

如果要求插入的数据的精确度较高，入货币运算

5.3.3 字符串

char()表示的是固定长度的字符串char（4），“asd“补充成”asd “

varchar()表示的可变长，varchar(4)，写入的是“asd”，写入的就是“asd”不会进行补充

TEXT表示的是大文本数据。字符数大于4000可以使用

创建一个表

create table classes(

id int primary key auto_increment not null

classname varchar(10) not null

num int not null default 0

);

5.3.4 日期

date

time

timestamp

6、标识列（自增长）

6.1 定义

可以不用手动增加值，系统自动提供

目的：不需要手动增加值，让系统可以自动增长，系统提供的

6.2 创建表的时候设置标识列

CREATE TABLE classes(
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL ,
classname VARCHAR(10) NOT NULL,
num  INT NOT NULL DEFAULT 0 

);

6.3 设置自增长的标识

AUTO_INCREMENT自增长，表示该值会自动增加

查看相关属性：
SHOW VARIABLES LIKE "%auto_increment%"

设置相关属性
设置步长
SET auto_increment_increment=3;

设置初始值
SET auto_increment_offset=3;

6.4 特点

1、auto_increment不一定要和主键搭配，但是一定要和一个key（约束）搭配

2、一个表最多有一个自增长字段

3、auto_increment标识列的类型只能是数值型（int double等）

4、步长与初始值是系统的全局变量，所以一旦修改之后，全局都生效，全局都受影响注意初始值不能大于步长，所以要想修改初始值必须先修改步长

7、数据库的备份与恢复

7.1 导出数据库

mysqldump -uroot -p 数据库的名字 > 自己起的名字.sql

7.2恢复数据库

mysql -uroot -p 新数据库的名字 < 自己起的名字

7.3 注意点

退出当前的数据库

新数据库的名字需要提前设置好

8、数据库的查询

8.1 数据的查询

分组
SELECT 分组依据1,2…,GROUP_CONCAT(需要展示的字段名1,2…) FROM 表名 GROUP BY 分组依据1,2…;
分组依据：一个字段或者多个字段
如果需要展示其他字段的内容，需要使用GROUP_CONCAT这个方法进行连接
起别名
SELECT 分组依据1,2…,GROUP_CONCAT(需要展示的字段名1,2…) as(写不写都可以) 别名 FROM 表名 GROUP BY 分组依据1,2…;

SELECT * FROM goods GROUP BY cate_id;

SELECT cate_id FROM goods GROUP BY cate_id;

SELECT cate_id,brande_id,GROUP_CONCAT(NAME,price) '产品与价格' FROM goods GROUP BY cate_id,brande_id;

SELECT cate_id,GROUP_CONCAT(price) '产品与价格' FROM goods GROUP BY cate_id;

SELECT cate_id,AVG(price) '价格均值' FROM goods GROUP BY cate_id;

SELECT cate_id,SUM(price) '价格总和' FROM goods GROUP BY cate_id;

SELECT cate_id,COUNT(price) '数据个数总和' FROM goods GROUP BY cate_id;

SELECT brande_id,GROUP_CONCAT(price) '数据个数总和' FROM goods WHERE price>3 GROUP BY brande_id ;

SELECT brande_id,GROUP_CONCAT(price) '数据个数总和' FROM goods WHERE price>3 GROUP BY brande_id HAVING COUNT(*)>2;

SELECT brande_id,GROUP_CONCAT(NAME) '带火的物品名',ROUND(AVG(price),3) '带火的平均价格' FROM goods WHERE NAME LIKE '%火%' GROUP BY brande_id;

其中round是代表小数的位数

SELECT
分组字段名,出现数据条件 改变之后的列名
FROM 表名
WHERE 筛选条件
GROUP BY 分组的列名
HAVING 分组之后的筛选条件;

where 引导的筛选条件在原始表中已经可以实现
having 引导的筛选条件是已经分组完成之后才可以做的

order by 排序条件 desc（降序）|asc（升序）

综合注意点：

分组查询中的筛选条件分为两类

    数据源             位置

分组前筛选 原始表 group by 的前面 where

分组后筛选 分组后的结果集 group by的后面 having

聚合函数做筛选条件，放在having 的后面

能用 where 做条件筛选的，就不用having ，因为会有性能问题

分组支持单个字段或者多个字段的分组，多个字段之间用，分开，或者使用表达式

可以使用排序函数.

8.2 连接查询

连接查询–多表查询

在连接的时候需要指定连接条件

SELECT goods.name,brande.brandename FROM goods,brande WHERE goods.brande_id=brande.id;

如果表名过长的话，我们可以给这张表起别名 在from 中起别名

8.2.1 分类

分类：

按年代分类：

sql92：仅支持内连接

sql99：内连接，外连接（左右，全不支持），交叉连接

按照功能来分：

内连接：

1、等值连接

2、非等值连接

3、自连接

外连接：

1、左外连接

2、右外连接

3、全外连接

交叉连接

8.2.2 多表联查

SELECT brande.`brandename`,COUNT(goods.`cate_id`),GROUP_CONCAT(goods.`name`),AVG(goods.`price`)
FROM goods,brande,cate
WHERE goods.`brande_id`=brande.`id` AND goods.`cate_id`=cate.`id`
GROUP BY brande.`brandename`
ORDER BY AVG(goods.`price`) ASC;

SELECT goods.`name`,brande.`brandename`,cate.`name`
FROM goods,cate,brande
WHERE goods.`brande_id`=brande.`id` AND goods.`cate_id`=cate.`id`
;

8.2.3 非等值连接

SELECT goods.price,price_grade.price_level
FROM goods,price_grade
WHERE price_grade.min_price<goods.price AND goods.price<price_grade.max_price

# WHERE goods.price between price_grade.min_price AND price_grade.max_price两种写法都可以

;

8.2.4 自连接

# 使用自关联

SELECT city.atitle,province.atitle
FROM areas AS city, areas AS province 
WHERE city.pid=province.aid AND province.atitle="浙江省"
;

SELECT atitle FROM areas WHERE pid IS NULL;

SELECT city.atitle,province.atitle
FROM areas AS city, areas AS province 
WHERE city.aid=province.pid AND city.atitle="杭州市"
;

8.2.4 总结

多表的连接的查询，是多张表的交集部分

多表的连接需要条件

多表的顺序没有要求

一般需要为表起别名，使用as

可以搭配所有的搭配字句，比如排序、分组、条件筛选

8.3 sql99语法

8.3.1 内连接

8.3.1.1 内连接语法

SELECT 查询字段
FROM 表名1
INNER JOIN 表名2
ON 连接条件
WHERE 筛选条件
GROUP BY
ORDER BY

8.3.1.2 内连接的等值连接

SELECT goods.name,brande.brandename
FROM goods
INNER JOIN brande
ON goods.`brande_id`=brande.`id`
WHERE goods.`name`LIKE "%火%"

;

SELECT brande.brandename,COUNT(goods.`name`),GROUP_CONCAT(goods.`name`)
FROM brande
INNER JOIN goods
ON goods.`brande_id`=brande.`id`
GROUP BY brande.`brandename`
HAVING COUNT(goods.name)>3
;
总结：
1、添加分组排序筛选
2、inner可以省略
3、筛选条件可以放在where的后面，连接条件放在on的后面，方便阅读
4、inner join 和93语法中的等值连接效果是一样的，都是查询多表的交集

8.3.1.3 内连接的非等值连接

SELECT price_grade.`price_level`,COUNT(goods.`price`),GROUP_CONCAT(goods.`price`)
FROM goods
INNER JOIN price_grade
ON goods.`price` BETWEEN price_grade.`min_price` AND price_grade.`max_price`
GROUP BY price_grade.`price_level`
HAVING COUNT(goods.`price`)
ORDER BY price_grade.`price_level` ASC
;

8.4 sql93与sql99的区别

功能：sql99支持的更多

可读性：sql99实现连接条件与筛选条件的分离，可读性较高

8.4.1 外连接

应用场景：一个表中有，一个表中没有的记录

内连接

左连接

#左连接

SELECT * FROM classes LEFT JOIN students
ON students.class_id=classes.id
;
1、两张表匹配到的数据
2、左表特有的数据，对于右表中不能匹配到的数据以null值表示

右连接

#右连接

SELECT * FROM classes RIGHT JOIN students
ON students.class_id=classes.id
;

1、两张表匹配到的数据
2、左表特有的数据，对于右表中不能匹配到的数据以null值表示

8.4.2 交叉连接

#交叉连接

SELECT * FROM classes CROSS JOIN students;

SELECT * FROM classes , students;

8.4.3 全外连接

#全外连接

SELECT students.*,classes.* FROM students FULL OUTER JOIN classes
ON tudents.class_id=classes.id
;

注意：全外是MYSQL不支持的，但是ORANCLE支持

9、子查询

9.1 定义

定义：出现在其他语句中的select语句，称为子查询或者内查询

外部的查询语句，称为主查询或者外查询

#子查询

SELECT ROUND(AVG(price),3) FROM goods;

SELECT goods.`name`,goods.`price` FROM goods WHERE price > (SELECT ROUND(AVG(price),3) FROM goods)
;

9.2 分类

9.2.1 按照子查询出现的位置来分

select 的后面 --仅仅-标量子查询

From的后面–表子查询

Where或者having的后面–标量子查询或者列子查询–行子查询（少）重点

exists 后面(相关子查询) 表子查询

9.2.2 按照结果集的行列数不同

标量子查询：（结果只有一行一列）

列子查询（结果有一列多行）

行子查询（一行多列）

表子查询（结一般为多行多列）

9.3 where 和 having重点

9.3.1 特点

1、子查询放在（）里面

2、子查询一般放在条件的右侧

3、标量子查询，一般配合单行操作符使用（> < >= <= !=）

4、列子查询，一般配合多行操作符使用（in any all some）

5、子查询的优先级高于主查询

9.3.2 标量子查询

SELECT * FROM goods ORDER BY goods.price LIMIT 0,1;

limit 第几行，到第几行 表示只取一段数据

distinct 去重操作

select distinct brande_id from goods where cate_id=2 or cate_id=3;

#标量子查询

SELECT * FROM goods 
WHERE 
goods.price > (
		SELECT goods.price 
		FROM goods 
		WHERE goods.name="短裤"
		)
;

9.3.3 列子查询

结果集返回的是多行

使用的是多行比较操作符

案例

查询cate_id=2 或者cate_id=3的品牌中所有商品的价格以及名字

1、查询cate_id=2或者cate_id的品牌

SELECT brande_id FROM goods WHERE cate_id=2 OR cate_id=3

注意：这个查询结果最好去重

SELECT DISTINCT brande_id FROM goods WHERE cate_id=2 OR cate_id=3

9.3.4 行子查询

select * from goods
where (id,price)=(
select min(id),max(price) from goods
)

9.3.5 在select后面

select (查询语句)

注意点：只支持标量子查询，一行一列

9.3.6 在from后面

将子查询的结果集当做表（数据源来处理）

注意点：将查询结果放在from后面，充当一个数据源，一张表，要求必须起别名，才能被当成一张表

9.3.7 放在exists后面(相关子查询)

相关子查询

exists（完整的查询语句）

结果是0或者1

放在exists

select exists（select * from goods）

返回值是1

SELECT brande.`brandename` FROM brande
WHERE EXISTS (
SELECT * FROM goods
WHERE goods.`brande_id`=brande.`id`)

9.4 分页查询

Select 查询列表

From表

On 连接条件

Where 筛选条件

Group by 分组字段

Having 分组后的筛选

Order by 排序字段

Limit offset,size

Offset 显示条目的起始索引从0开始

Size 要显示的条目个数

执行顺序：

from - where - group by - having - order by - limit

特点：

1、limit 语句放在查询语句的最后

2、公式表达 limit

1 0

2 10

3 20

显示的页数 page每次的页数

Select * from goods limit (page-1)*size,size

9.5 联合查询

定义：将多条查询语句的结果合并成一个

查询语句1

Union

查询语句2 （查询的结构需要时相似的）

查询的字段个数是一致的（字段的名称不要求一致）

应用场景

要查询的结果来自多张表，且多张表之间没有直接的关系，但查询的信息一致的时候

注意点：

1、联合查询的查询字段的个数必须一致

2、尽量的使相同字意义的一起

3、使用union会自动去重

4、使用union all 不会去重

10、视图

10.1 定义

一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是使用视图的时候动态生成的，只保存sql的逻辑，不保存结果

10.2 使用

视图
是一张虚拟的表
只保存sql的逻辑，不保存结果
视图中的行列的数据，来源于自定义视图的时候查询中使用的表，是使用视图的时候动态生成
create view 视图名
as
查询语句

10.3 应用场景

多个地方用到相同的场景

该查询结果使用sql比较复杂

10.4 创建视图

10.4.1 基础语法

create view 视图名
as
查询语句

修改视图，让他的重用型增强