【学习】数据库MySQL

℃change

已于 2022-05-12 17:17:28 修改

阅读量1.5k

点赞数

分类专栏：总结文章标签：数据库 mysql 学习

于 2022-05-09 18:38:49 首次发布

本文链接：https://blog.csdn.net/weixin_45326506/article/details/124672403

版权

总结专栏收录该内容

5 篇文章 2 订阅

订阅专栏

视频学习
感谢老师！！
以下是笔记

一、选择语句

SELECT * 
FROM  customer 
WHERE customer_id =1 
ORDER BY first_name

use + 数据库名称查询某个数据库
select * from 数据表查询某个表

1. 选择子句（select、as、distinct）

SELECT  DISTINCT last_name,
		first_name,
        points,
        (points +100)*10 AS '设置列名'
FROM customers

select 后跟的列元素查询多个列时可以用逗号隔开

as 可给列设置名称

distinct关键字：可过滤掉列中重复的选项

2. WHERE 子句

SELECT *
FROM customers
-- WHERE points >2000
-- WHERE state = 'va'
-- WHERE birth_date >'1980-01-01'

where关键字：用于条件限制，可以设置数值、字符、日期

1. and、or和NOT IN运算符

and 、or 在查询过程中连接两个查询条件

优先级：and>or 、多打括号少出错

not in :适用于多个条件同时满足，写起来会笔记方便

SELECT *
FROM customers
-- WHERE state = 'va' or state = 'co' or state = 'ca'
-- WHERE state IN ('va','co','ca')

-- WHERE state NOT IN ('va','co','ca')

2.between and 查询范围

SELECT *
FROM customers
-- WHERE points >= 1000 and points <= 3000
WHERE points between 1000 and 3000

用于设置查询的范围

3. like 模糊查找

SELECT *
FROM customers
-- WHERE points >= 1000 and points <= 3000
WHERE last_name LIKE 'B%'

% 表示多个字符

_ 表示1个字符

not like 表示排除查询条件中的内容

4.regexp 正则函数查询

SELECT *
FROM customers
WHERE last_name REGEXP '[A-H]B'

使用正则函数查询：

^b 指定开头是b开头的
$ 指定结尾
| 约等于or的功能
[abcd] 字符包含abcd中的一个即可
[a-f] 字符包含a-f中额一个即可，不含f

5.is null 为空

SELECT *
FROM customers
-- WHERE phone is null
WHERE phone is not null

关键字 + is null ：查找关键字为空的行
关键字 + is not null ：查找关键字不为空的行

3.ORDER BY 排序

SELECT *
FROM customers
ORDER BY first_name DESC ，last_name

order by 分组，默认升序排列

desc 降序排列

先以first_name为准排序，如果相同再以last_name排序

1. limit句子_限度

SELECT *
FROM customers
LIMIT 5,3

LIMIT : 偏移量，现实数量。limit永远放最后

二、数据库连接

1.内连接(join on)

SELECT shipped_date, o.customer_id,first_name,last_name
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id

使用 join +需要连接的表 + on + 列名相连

2.跨数据连接

SELECT *
FROM sql_store.order_items o
JOIN sql_inventory.products pr
	on o.product_id = pr.product_id

两个不用的数据库表相连接只需要标注数据库名.表名即刻

3.自连接

select e.employee_id,
	   e.last_name,
        m.last_name
from employees e
join employees m
	on e.reports_to = m.employee_id

自己和自己连接，将关键字做连接，注意单词拼写问题。

4.多表连接

SELECT cl.name,
		pm.name
FROM payments pay
join  clients cl
	on cl.client_id = pay.client_id
join payment_methods pm
	on pay.payment_method = pm.payment_method_id

多加几个join on

5.复合连接条件

select *
from order_items oi
join order_item_notes oin
	on oi.order_id = oin.order_id
    and oi.product_id = oin.product_id

使用多个条件来连接两个表格

6.隐式连接语法

select *
from orders o ,customers c

可以省略join on 但会出现很多不必要的麻烦，因此推荐使用显示

7.外连接

外连接、内连接在语法的区别的主要在于 join的前缀，

外连接
- 左外连接：以左边的表为基准去查找右边符合连接调制的行
- 右外连接：以右边的表为基准·······································
内连接：查找双方同时满足连接条件的行

select *
from orders o 
left join customers c
-- right join customers c
	on c.customer_id = o.customer_id
order by o.order_id

8.多表外连接

select c.customer_id,
	   c.first_name,
        o.order_id,
        s.name
from customers c
left join orders o
	on c.customer_id = o.customer_id
left join shippers s
	on o.shipper_id = s.shipper_id
order by o.order_id

在多表连接的时候尽量使用左连接，因为左右连接同时使用，很容易自己都搞不清楚了

9.自外连接

select e.employee_id,
		e.first_name,
        m.first_name
from employees e
left join employees m
	on e.reports_to = m.employee_id

类比：自连接

10.using 句子

select *
from order_items oi
left join order_item_notes oin
	-- on oi.order_id= oin.order_id
    USING(order_id, product_id)

在join on 中连接两个表的列名相同们可以使用using，这样界面更加简介。

多个相同的列明使用“逗号”连接

11.自然连接

natural join +表格名称

select *
from orders o
natural join customers c

不建议采用、盲盒连接类型。

12.交叉连接

笛卡尔积

select *
from customers c
cross join products p
order by c.first_name

13.联合查询 UNION

select customer_id,
		first_name,
        points,
        'silver' as 'type'
from customers
where  points >=3000
UNION
select customer_id,
		first_name,
        points,
        'gold' as 'type'
from customers
where  points <= 3000 and points >=2000
order by customer_id

UNION关键字，连接两个查询语句

三、列属性

1.插入单行

insert into +表名（表列名,也可不加，默认首行列名顺序） +values ( 添加的值)

INSERT INTO customers (
	first_name,
    last_name,
    birth_date,
    address,
    city,
    state)
values(
	'john',
    'smith',
    '1990-01-01',
    'address',
    'city',
    'ca')

2.插入多行

INSERT INTO shippers (name)
values ('shippers1'),
		('shippers2'),
        ('shippers3')

插入值，用逗号分割

3.插入分层行

如何往多表中插入数据

INSERT INTO orders(customer_id,order_date,status)
values(1,'2019-01-02',1);

INSERT INTO order_items
values(LAST_INSERT_ID(),1,1,1),
		(LAST_INSERT_ID(),2,2,2)

4.创建表的复制

从一张表复制数据到另一张表中

CREATE TABLE +新表名 + as + 需要复制的表（这里可以写一个查询语句，将想要查询的结果复制到新表中）

创建一张新表
选择需要复制表的全部

CREATE TABLE orers_archived as
select * from orders

若复制一部分则加where筛选条件

5.更新单行

sql更新数据

update invoices  -- 选择更新的表格
set
-- 需要更新的列值
	payment_total = invoice_total * 0.5,
    payment_date = due_date
where invoice_id = 1  -- 需要更新的行

6.更新多行

在mysql中直接修改多条是不行的，因为有安全模式阻挡我们修改，

deit—preferences —sql editor——最后一行safr updates 关闭安全模式

update invoices  -- 选择更新的表格
set
-- 需要更新的列值
	payment_total = invoice_total * 0.5,
    payment_date = due_date
where invoice_id in (1,2)  -- 需要更新的行

7. update 中子查询

在where中嵌套

use sql_invoicing;

update invoices
set payment_total = invoice_total
where client_id in (  -- 在这里增加一个括号嵌套一个查询语句
					select client_id
					from clients
					where state in ('NY','CA')
					)

8.删除行

delete from invoices   -- 删除语句，如果不加后面的选择语句那么就将删去整个表格
where clinet_id = (
					select *
					from clients
					where name =  'Myworks'
					)

四、聚合函数

其实就是数据库中存在着很多初始化函数

select  
	'2019年上半年' as eng ,
	max(invoice_total),
    min(invoice_total),
    avg(invoice_total),
    sum(invoice_total),
    count(invoice_total)
from invoices
where invoice_date>'2019-06-30'
union
select  
	'2019年下半年' as eng ,
	max(invoice_total),
    min(invoice_total),
    avg(invoice_total),
    sum(invoice_total),
    count(invoice_total)
from invoices
where invoice_date>'2019-06-30'

1.GROUP BY分组

用于将指定列元素分组，汇总显示

2.HAVING 用于筛选

于where的区别在于：

where用于分组前进行筛选
having 用于分组以后的筛选、同时用到的列一定需要在查找中有的列

select 
	c.customer_id,
	c.first_name,
    c.last_name,
    sum(oi.quantity * oi.unit_price) as sum
from customers c
join orders o using (customer_id)
join order_items oi using (order_id)
where points > 1000
group by
	c.customer_id,
	c.first_name,
    c.last_name
having sum<100    -- 这里的条件需要选择select 那块已有的

3. ROLLUP 运算符

用于数据汇总的，不同的数据库运算符不一样，但是都是有这里汇总功能

select 
	state,
    city,
    sum(invoice_total) as total_sales
from invoices i
join clients c using (client_id)
group by state,city with rollup

五、复杂查询

1.子查询

查询嵌套

select first_name
from employees
where  salary >= (
	select avg(salary)  from employees
)

2.IN运算符

IN 操作符允许在WHERE 子句中规定多个值。

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

1.子查询和连接

连接和子查询都能实现一样的功能，因此在使用中，优先考虑易读性，推荐使用连接

3.all （全部）关键字

all全部的意思

select *
from invoices
where invoice_total > all (
	select invoice_total
    from invoices
    where client_id = 3
)

all : 表示大于括号中全部的内容

4.ANY 关键字

“ = any” 和 in 是等效的

5.相关子查询

SELECT * 
FROM invoices i
where invoice_total >(
    	-- 这段括号中的 用于搜索 一个组内的平局值
			select avg(invoice_total)
			from invoices im
			where im.client_id = i.client_id
)
-- 返回 许多组中 平局值最大的 行

6.EXISTS 运算符

实现和in差不多的功能，但效率更高一些

7.select句子中的子查询

select client_id,
		name,
        (select sum(invoice_total) from invoices)
from clients

8.FROM中的子查询

可以在from中写查询语句，但仅限于简单的查询语句

六、数值函数

MySQL - String Functions (tutorialspoint.com) 连接就放这里了，看不懂的懂，就看你了。

SELECT ROUND(数值，小数点后几位)  -- 用于四舍五入，精确到小数点后几位
SELECT TRUNCATE(数值，截取到小数点后几位) -- 截断函数，截取到小数点后几位
select ceiling(3.14159267)  -- 去掉小数点后的数，向上取整，
select floor(3.14159267)   -- 地板函数，向下取整，就去掉小数点后面的数字，输出整数
select abs(-3.14159267) -- 绝对值函数
select rand()  -- 随机函数

1.字符串函数

select length('sky')  -- 返回字符串长度
select upper('sky')  -- 转化成大写
select lower('SKy')  -- 转化成小写

select ltrim('     sky') -- 去掉字符左边的空格
select rtrim('     sky') -- 去掉字符右边的空格
select trim('     sky') -- 去掉字符两边的空格

select right('ababababab',3) -- 从左往右数，输出前几个字符
select substring('abcdefghijk',3,2) -- 输出字符串中，从第三个开始，输出2个字符
select locate('a','abcdefghijk')  -- 查询a在字符串中的那个位置
concat(first_name,last_name ) as name  -- 将两个字符合并

2.日期函数

select now(),curdate(),curtime()  -- 显示现在的日期和时间，显示现在的日期，显示现在的时间
select year(now())  -- 返回年、 类比还可以返回月、日、时、分、秒
select dayname(now())  -- 返回 星期 monthname：返回 月

3.日期格式化

select date_format(now(),'%y%m%d')  -- 输出日期
select tIME_format(now(),"%H:%i %p") -- 输出时间

4.计算日期和时间

select datediff('2019-01-05 9:00','2019-01-01 17:00') -- 日期计算
select time_to_sec('9:00') - time_to_sec('9:02')  -- 时间计算，单位为秒

5.IFULL 和 COALESCE 函数

ifnull ：将空值换做指定字符串

coalesce ：当值为空的时候返回指定选项的值

select customer_id,
	    shipped_date,
        ifnull(shipped_date,'ifnull'),
        coalesce(shipped_date, customer_id,"coalesce") -- 第一个值如果为空的话，选择第二个列的值
from orders

6.if函数

判断语句

select 
		order_id,
		order_date,
        if(
			year(order_date) = year(now()),
			'活跃',  -- 满足条件执行
            '不活跃')  -- 不满足条件执行
from orders

case 运算符

if的升级版，可以满足多个要求判断

select 
	order_id,
	case
		when year(order_date) = year(now())  then '今年'
        when year(order_date) = year(now()) - 1 then '去年'
        when year(order_date) < year(now()) -1 then '以前'
        else 'else'
	end as category
from orders

七、视图create view

1.制作视图

选择相关需要的数据柔和做一个表格

create view views as
SELECT 
	concat(first_name,last_name),
    points,
    case
		when points>2000 then '牛皮'
        when points>1000 then '还行'
        else '垃圾'
	end as category
FROM customers

但这只是一张视图，并没有相关数据

2.更改或者删除视图

删除视图
```
drop view 视图名
```

修改视图 or replace

create or replace view 视图名 as  查询语句

3.更新视图

删除视图中的信息

DELETE FROM 视图名
where 条件（删除复合条件的哪些信息）

更新视图

update 视图名
+ 查询语句（更新那一条信息）

禁止修改语句

with check option  -- 禁止修改视图表语句，添加以后，将无法对视图进行任何修改信息的操作

4.视图的优点

直接对于表格信息进行操作，会有一定误删的可能，使用表格则没有任何关系
简化查询

八、存储过程

1.新建存储

可以理解成函数的功能

delimiter $$
create procedure get_clients() -- 存储名称
begin
	select * from clients;  -- 存储内容惭怍本体
end $$
delimiter ;

调用：

call sql_invoicing.get_clients(); -- call 存储名

2.删除存储

drop procedure + 过程名

3.形参存储调用

制作有参过程

delimiter$$
create prepare 过程名
(
	参数 类型
)
begin
	select * from 表名
    where 参数
end$$
delimiter ;

-调用有参过程

call 过程名（'参数'）

------------------------------以下开始摆烂，笔记开始潦草--------------------------------

九、触发器

十、事务

事务：是代表单个工作单元的一组sql语句

将对数据库进行的一组操作，成为工作单元，我们往往希望对于数据单元的更改是一起成功或者失败

事务的特性：

原子性（要嘛一起被执行、要嘛执行失败整体退出）
一致性（使用事务过程中，数据库始终保持一致的状态）
隔离性质
持久性（提交以后，修改是永久的）

1.创建事务

start transaction;

 修改语句
 
 commit;

2.并发和锁定

并发问题

丢失更新（导致较晚操作的数据覆盖较早修改的数据）——使用“锁”

读脏数据（读取了未被提交的数据）——建立隔离级别

可重复读（读了2次，每次结果不一样）

幻读（查询中缺失一行或者多行，可能因为另外一个事务正在操作）
锁定问题
.事务隔离级别

未提交读（Read Uncommitted）：事务可以读取未提交的数据，也称作脏读（Dirty Read）。一般很少使用。

提交读（Read Committed）：是大都是 DBMS （如：Oracle, SQLServer）默认事务隔离。执行两次同意的查询却有不同的结果，也叫不可重复读。

可重复读（Repeable Read）：是 MySQL 默认事务隔离级别。能确保同一事务多次读取同一数据的结果是一致的。可以解决脏读的问题，但理论上无法解决幻读（Phantom Read）的问题。

可串行化（Serializable）：是最高的隔离级别。强制事务串行执行，会在读取的每一行数据上加锁，这样虽然能避免幻读的问题，但也可能导致大量的超时和锁争用的问题。很少会应用到这种级别，只有在非常需要确保数据的一致性且可以接受没有并发的应用场景下才会考虑。

solation Level 脏读可能性（Dirty Read）不可重复读可能性（Non Repeatable Read）幻读可能性（Phantom Read）
ead Uncommitted yes yes yes
epeatable Read yes yes
erializable yes

solation Level	脏读可能性（Dirty Read）	不可重复读可能性（Non Repeatable Read）	幻读可能性（Phantom Read）
ead Uncommitted	yes	yes	yes
epeatable Read		yes	yes
erializable			yes

3.死锁

十一、数据类型

数据库中会有哪些类型

字符串、数值类型、日期时间类型、blob类型、几何地区值得空间类型

1.字符串类型

char
varchar	max: 65535
mediumtext	max:16MB
longtext	max:4GB
tingtext	max:255 bytes
text	max:64kb

2.整型

tinyint	[-128,127]
unsigned tinyint	[0,255]
smallint	[-32k,32k]
mediumint	[-8m,8m]
int	[-2b,2b]
bigint	[-9z,9z]

尽量选择合适的数据类型，以节约空间，提高查询熟读

3.定点数型和浮点型

decimal（p,s）	decimal(一共有几位数字，小数点后有几位)
dec
numberic
fixed

float	4b
double	8b

如果是银行一类对于数要求精确的用定点数类型、如果像天文一类数字很大，而且精度要求不高的可以用浮点类型

4.布尔类型

5.枚举集合类型

6.时间和日期类型

date、time、datetime(8b 可以存储很多年的)、timestamp（只能存储到2038年）、year

7.blobs

存储图像、音频、视频的类型

8.join类型

9.json类型

十二、数据库设计

1.数据建模

连接分析业务需求
构建业务概念模型
生成数据模型——逻辑模型
改进模型，构建一个实体的数据模型

2.概念模型

创建概念模型，表示业务中的实体、事务或概念以及他们的关系

一般使用uml语言来构建模型

3.逻辑模型

细化概念模型中的信息

拆分概念模型中比较笼统的名称

指定实体之间的关系（一对一、一对多、多对多）

4.实体模型EER

新建数据库、修改名称、双击 adddiagram、

主键：

外键：

外键约束：设置外键约束，本质上是保护数据

5.数据标准化（Normalization）

1. first normal from (1NF)

第一范式要求一行中的每一个单元格都应该有单一值，且不能出现重复列

2. second normal from(2NF)

3. Third Normal Form（3 NF）

6.创建数据库和删除数据库

创建数据库
```
create database + 数据库名
```
删除数据库
```
drop database + 数据库名
```

7.创建表

create table if not exists + 表名
(
    列名 属性，
)；

8.修改表

alter table + 需要修改的表名
	add 列名 属性,   -- 添加列
	modify column 列名 属性, -- 修改列的属性
    drop 列名 -- 删除列
    ;

9 创建关系

create table 表名
(
	列名 关系，
)

10.更改主键、外键

alter table 表名
	add primary key (列名1，列名2); -- 增加主键
	drop primary key 列名 -- 删除主键
	
	add foreign key 列名 -- 增加外键
	drop foreign key 列名 -- 删除外键

十三、高效的索引

1.索引

本质是数据库引擎用来快速查找数据的数据结构

同时也会增加数据库大小，降低运行速度

每次对数据库进行操作额时候都需要更新索引

2.创建索引

explain select * from customers where points >1000;
-- exlain + 查询语句   ：查看搜搜信息

create index idx_state on customers(points)
-- create index 索引名称 on customers(需要找到额要求)

3.查看索引

show indexes in 表名；

4.前缀索引

当给某一个列字段添加索引时，如果该列字段的字符串值很长时，那么我们创建的索引则会很大且很慢，这个时候如果可以只索引列开始的部分字符串，那么就可以节约索引空间，从而提高索引效率，此时这种类型的索引就叫“前缀索引”（索引值重复性越低，查询效率就越高）

5. 全文索引

·······

十四、数据库的管理

1.创建用户

create user +用户名@ip地址
create user + 用户名@计算机名称
create user + 用户名@域名
create user + 用户名 + identified by +密码

2.查看用户

select * from mysql.user;

3.删除用户

drop user + 名称;

4.修改密码

set password for 用户名 = '密码'； -- 修改用户的密码
set password for = '密码'； -- 修改当前密码

5.授予权限

grant select,insert,update,delete,execute -- 授予 什么权限
on 数据库名.表名  -- （这里表名可以填*号表示全部）
to 用户名 -- 授权给那个用户

6.查看权限

show grants for + 用户名

7.撤销权限

revoke create view 
on 数据库.表
from  用户名;