《MySQL必知必会》学习记录

mysql
关于学习
发布日期:   2023-03-02
文章字数:   3.5k
阅读时长:   14 分
阅读次数:  

MySQL必知必会

使用MySQL

# 返回可用数据库列表
show databases;

# 返回当前选择的数据库内的可用表
show tables;

# 显示表列
show columns from 表名;
describe 表名;

# 显示对应的SQL语句
show create database 数据库名;
show create table 表名;

检索数据

# 查询所有列
select * from 表名;

# 查询指定列
select 指定列名 from 表名;

# 查询不同的行(distinct去重)
select distinct 列名 from 表名;

# 限制结果, 查询前5行数据
select * from 表名 limit 5;

# 第一个数为开始位置（从0开始），第二个数是查询的行数
select * from 表名 limit 5， 5;

排序检索数据

# 排序(order by)默认升序
select * from 表名 order by 指定列名;

# 排序(order by desc）降序
select * from 表名 order by 指定列名 desc;

# 按多个列排序
select * from 表名 order by 指定列名1, 指定列名2;

过滤数据

# 操作符：
大于：> 
小于：< 
大于等于：>= 
小于等于：<= 
不等于：!= <> 
等于：= 
在指定两个值之间：between
空值检查：IS NULL

# 条件筛选(where)
select * from 表名 where 指定列名 操作符 指定条件;

# order by 需用在 where 后
select * 
from 表名 
where 指定列名 操作符 指定条件 
order by 指定列名1, 指定列名2;

# and 或 or 增加过滤条件(and 的计算优先级比 or 高，故可使用括号避免错误组合)
select * from 表名 where 指定列名 操作符 指定条件 and ···;
select * from 表名 where 指定列名 操作符 指定条件 or ···;

# in(条件1, 条件2) in 比 or 更高效
select * from 表名 where 指定列名 in (指定条件1, 指定条件2···);

# not 否定后续条件
select * from 表名 where 指定列名 not in (指定条件1, 指定条件2···);

通配符过滤

# like 和 %（匹配多个字符，不能匹配NULL）
select * from 表名 where 指定列名 like 'zh%'; # 查询指定列以zh为开头的所有列

select * from 表名 where 指定列名 like '%zh%'; # 查询中间存在zh字符的列

# _ 下划线与 % 类似，但只匹配单个字符
select * from 表名 where 指定列名 like '_zh'; # 查询zh前只有一个字符的所有列

起别名

select 指定列名 as 别名 from 表名; # as 起别名

使用数据处理函数

常用文本处理函数

left() # 截取并返回左边的字符串
right() # 截取并返回右边的字符串
concat() # 拼接字段
length() # 返回传的长度
locate() # 找出串的一个字串
lower() # 转小写
upper() # 转大写
ltrim() # 去除左空格
rtrim() # 去除右空格
substring() # 返回字串的字符
soundex() # 返回串的soundex值(对串的发音相似比较)

日期和时间处理函数

addDate() # 增加一个日期（天，周等）
addTime() # 增加一个时间（时，分等）
curDate() # 返回当前日期
curTime() # 返回当前时间
Now() # 返回当前日期和时间
Date() # 返回日期时间中的日期部分
Time() # 返回一个日期时间的时间部分
DateDiff() # 计算两个日期之间的差
Date_Add() # 高度灵活的日期运算函数
Date_Format() # 返回一个格式化的日期或时间串
DayOfWeek() # 对于一个日期，返回对应的星期几
Hour() # 返回一个时间的小时部分
Minute() # 返回一个时间的分钟部分
Second() # 返回一个时间的秒部分
Year() # 返回一个日期的年份部分
Month() # 返回一个日期的月份部分
Day() # 返回一个日期的天数部分

数值处理函数

Abs() # 返回一个数的绝对值
Cos() # 返回一个角度的余弦
Exp() # 返回一个数的指数值
Mod() # 返回除操作的余数
Pi() # 返回圆周率
Rand() # 返回一个随机数
Sin() # 返回一个角度的正弦
Sqrt() # 返回一个数的平方根
Tan() # 返回一个角度的正切

汇总数据

聚集函数

AVG() # 返回某列的平均值(忽略列值为NULL的行)
COUNT() # 返回某列的行数
	# (1.使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。2.使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。)
MAX() # 返回某列的最大值(忽略列值为NULL的行)
MIN() # 返回某列的最小值(忽略列值为NULL的行)
SUM() # 返回某列值之和(忽略列值为NULL的行)

聚集不同值

avg(distinct column)
# distinct 不能用于count(*), 故没有 count(distinct)
# distinct 必须使用列名，不能用于计算或表达式

组合聚集函数

select avg(column) as avgs,
count(*) as counts,
min(column) as mins,
max(column) as maxs
from table_name;

分组数据

创建分组（group by）

select products_id, count(*) as nums_prods
from products
group by products_id;

注意事项：

  1. group by 子句可以包含任意数目列
  2. group by 子句中列出的每一个列都必须是检索列或有效的表达式（但不能是聚合函数）。若在select中使用表达式则必须在group by子句中指定相同的表达式，不能使用别名
  3. select语句中每一个列都必须在group by子句中给出
  4. 若分组中具有NULL值，则NULL将作为一个分组返回。若有多行NULL值，它们将分为一组
  5. group by 子句必须在where子句之后，order by子句之前

过滤分组（having）

与where的区别：

  1. where过滤行，having过滤分组
  2. where在分组前过滤，having在分组后过滤
select product_id, count(*) as num_prods
from products
where product_price < 12
group by product_id
having count(*) > 2;

分组和排序

select product_id, count(*) as num_prods
from products
where product_price < 12
group by product_id
having count(*) > 2 # 必须用表达式，不能用别名
order by num_prods;

使用子查询

使用子查询过滤

select product_id
from products
where product_price in (
	select product_price
    from product_items
    where item_id = '1'
);

联结表

外键：某表中的一列，包含另一个表的主键值

创建联结

select vend_name, product_name
from vendors, products
where vendors.vend_id = products.vend_id

笛卡尔积：由没有联结条件的表关系返回的结果，其检索出的结果将是第一个表的行数乘于第二个表的行数

内部联结（等值联结）

取出两张表中匹配到的数据，匹配不到的不保留

select vend_name, product_name
from vendors inner join products
on vendors.vend_id = products.vend_id

创建高级联结

自联结

select p1.product_id, p2.product_id
from products as p1, products as p2
where p1.vend_id = p2.vend_id;

外部联结

取出连接表中匹配到的数据，匹配不到的也会保留，其值为NULL

# 包含左表的所有行
select customers.cust_id, orders.order_num
from customers left join orders
on customers.cust_id = orders.cust_id;

# 包含右表的所有行
select customers.cust_id, orders.order_num
from customers right join orders
on customers.cust_id = orders.cust_id;

组合查询

创建组合查询

select vend_id, prod_id, prod_price
from products
where prod_price <= 5
union # 使用union将输出结果组合成单个查询结果集，会自动去除重复的行
select vend_id, prod_id, prod_price
from products
where vend_id in (1001, 1002);

union使用规则：

  1. union必须由两条或以上的select语句组成，语句之间使用union做分隔
  2. union中每个查询必须包含相同的列，表达式或聚集函数
  3. 列数据类型必须兼容（可以不完全相同，但应能够隐式转换）

包含或取消重复的行

select vend_id, prod_id, prod_price
from products
where prod_price <= 5
union all# 不会去除重复的行，而是返回所有匹配的行
select vend_id, prod_id, prod_price
from products
where vend_id in (1001, 1002);

对组合查询结果排序

select vend_id, prod_id, prod_price
from products
where prod_price <= 5
union # 使用union将输出结果组合成单个查询结果集，会自动去除重复的行
select vend_id, prod_id, prod_price
from products
where vend_id in (1001, 1002)
order by vend_id, prod_price; 
# 实际上是对整个结果进行排序，并非最后一个select

全文本搜索

MyISAM：支持全文本搜索

InnoDB：不支持

select note_text
from productnotes
where Match(note_text) Against('rabbit');
# Match(指定被搜索的列)
# Against(指定要搜索的表达式)

插入数据

插入单条或多条数据

# 插入完整的行
insert into customers
values(
	'Jack', # 每一个值的顺序都必须与列的顺序一致
    20,
    'LA'
);
# 更安全写法，可以省略部分列
insert into customers(name, age, address)
values ('Jack', 20, 'LA');

# 插入多个行
insert into customers(name, age, address)
values ('Jack', 20, 'LA'), ('Mary', 23, 'NY');

插入检索出的数据

insert into customers(name, age, address)
select name, age, address from customersnew;

更新和删除数据

注意：更新和删除务必小心谨慎，避免更新或删除了全表，养成带where的习惯

# 更新所有行
update customers set age = 26;

# 更新指定行
update customers set age = 26 where name = 'Jack';

# 更新多行时发生错误也继续更新后续的
update ignore customers...

# 删除所有行
delete from customers;

# 删除指定行
delete from customers where age = '23';

# 更快的删除，删除原来的表并重建一个表
truncate table

创建和操纵表

# 创建表
create table customers if not exists(
    cust_id int not null auto_increment, # 自动增量
	cust_name varchar(50) not null,
    cust_age int null,
    cust_address varchar(100) null,
    cust_country varchar(20) not null default 'China', # 默认值
    primary key(cust_id) # 主键
)engine = innodb; # innodb引擎，支持事务

# 获取返回的最后一个auto_increment
select last_insert_id();

# 更新表（谨慎使用）
alter table customers
add cust_phone varchar(20); # 增加一列

alter table customers
drop column cust_phone; # 删除一列

# 删除表
drop table customers;

# 重命名
rename table customers to cust;

视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。视图一般用于检索而不用于更新

  1. 重用SQL语句，简化操作
  2. 使用表的组成部分而不是整个表
  3. 可以给用户授予表的特定部分的访问权限而不是整个表的访问权限

视图的使用规则：

  1. 必须唯一命名
  2. 可创建的视图数目没有限制
  3. 视图也可以嵌套
  4. 视图可以和表一起使用
  5. 视图不能索引
  6. Order by 可以用于视图中，但若从该视图检索数据的select中也包含order by 则视图中的order by 将会被覆盖
# 创建或更新视图
create or replace view viewname

# 查看创建视图的语句
show create view viewname;

# 删除视图
drop view viewname;

# 使用视图
create view productcustomers as
select cust_name, cust_age, prod_id
from customers, orders
where customers.cust_id = orders.cust_id;

select * from productcustomers;

存储过程

执行存储过程

# 执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格
call productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage
                   )

创建存储过程

# BEGIN和END语句用来限定存储过程体
create procedure productpricing()
begin 
	select avg(prod_price) as priceaverage
	from products;
end;

删除存储过程

drop procedure productpricing;

使用参数

# out 从存储过程传出，in 传入存储过程
create procedure productpricing(
	out pl decimal(8, 2),
    out ph decimal(8, 2),
    out pa decimal(8, 2)
)
begin
	select min(prod_price)
	into p1
	from products;
	select max(prod_price)
	into ph
	from products;
	select avg(prod_price)
	into pa
	from products;
end;

# 所有MySQL变量必须以@开头, 使用变量接存储过程输出的内容
call productpricing(
	@pricelow,
    @pricehigh,
    @priceaverage
);

select @pricelow, @pricehigh, @priceaverage;

触发器

MySQL在响应以下任意语句而自动执行的一条MySQL语句（或位于begin和end之间的一组语句）：

  1. delete
  2. insert
  3. update

仅有表支持触发器，视图和临时表均不支持

创建触发器

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。

# 每插入成功一次就显示'product added'
create trigger newproduct after insert on products
for each row select 'product added';

删除触发器

drop trigger newproduct;

使用触发器

  1. insert触发器
create trigger newproduct after insert on products
for each row select 'product added';
  1. delete触发器

在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到一个名为archive_orders的存档表中

create trigger deleteorder before delete on orders
for each row
begin
	insert into archive_orders(order_num, order_date, cust_id)
	values(OLD.order_num, OLD.order_date, OLD.cust_id);
end;
  1. update触发器

每次更新一个行时，NEW.vend_state中的值（将用来更新表行的值）都用Upper(NEW.vend_state)替换

create trigger updatevendor before update on vendors 
for each row set NEW.vend_state = Upper(NEW.vend_state);

事务管理

保证成批MySQL操作要么完全执行，要么完全不执行

  1. 事务（Transaction）：指一组SQL语句
  2. 回退（rollback）：指撤销指定SQL语句的过程
  3. 提交（commit）：指将未存储的SQL语句结果写入数据库表
  4. 保留点（savepoint）：指事务处理中设置的临时占位符，可对其发布回退

控制事务处理

# 开始事务
start transaction;
delete from products where prod_id = 1011;
select * from products;
# 回退
rollback;
select * from products;

使用commit

平时未开启事务的时候，执行SQL语句已有隐式commit

当开启事务后，需要进行明确的提交，SQL语句才会真正地执行

# 开始事务
start transaction;
delete from products where prod_id = 1011;
select * from products;
# 提交（之后事务会隐式关闭）
commit;
select * from products;

使用保留点

savepoint delete1;
rollback to delete1;

# 释放保留点
release savepoint;

修改默认的提交行为

# 0 为false 即设置为不自动提交，默认为1
set autocommit = 0;

数据库管理

备份数据

backup table;
或
select into outfile; # 转存在外部文件

数据库维护

# 检查表键是否正确
analyze table;
# 在myisam上针对索引进行检查
check table;
# 回收空间，优化性能
optimize table;

查看日志文件

  1. 错误日志：data\hostname.err
  2. 查询日志：data\hostname.log
  3. 二进制日志：data\hostname-bin
  4. 缓慢查询日志：data\hostname-slow.log（对确定数据库在何处需要优化有很大的作用）
# 刷新并重新开始所有的日志文件
flush logs;
mysql
赏
评论
  目录