MySQL基础

一、SQL

• 数据库：保存有组织的数据的容器（通常是一个文件或一组文件）。
• 表：某种特定类型数据的结构化清单。
• 模式：关于数据库和表的布局及特性的信息。
• 列：表中的一个字段。所有表都是由一个或多个列组成的。
• 数据类型：所容许的数据的类型。每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据。
• 行：表中的一个记录。
• 主键：一列（或一组列），其值能够唯一区分表中的每个行。
• SQL：结构化查询语言。

二、MySQL

• 概念：MySQL是一种DBMS（数据库软件）。
• 优势：成本：开放源码，免费使用；性能：执行很快；可信赖：多数大公司的选择；简单：容易安装和使用。
• DBMS分类：基于共享文件系统的DBMS；基于客户机-服务器的DBMS。

三、MySQL使用

1.连接

• 连接信息：主机名、端口号、用户名、口令。

2.选择数据库

• 查看所有可用数据库：

show databases;

• 选择数据库：

use mysql;

• 查看数据库中的所有表：

show tables;

• 查看表每一列的字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息。

show columns from customers #方法一
describe customers #方法二

四、检索数据

1.检索单个列

select prod_id from products;

2.检索多个列

select prod_id, prod_name from products;

3.检索不同的行

select * from products;

4.检索不同的行

select distinct vend_id from products;

5.限制结果

• 限制检索结果行数

select prod_name from products limit 5;

• 限制检索结果开始行数和检索行数

select prod_name from products limit 5, 5;

• 使用完全限定的表名

select products.prod_name from products;

五、排序检索数据

• 按单个列排序

select prod_name from products order by prod_name;

• 按多个列排序

select prod_id, prod_price, prod_name 
from products 
order by prod_price, prod_name;

• 按指定方向排序

select prod_id, prod_price, prod_name
from products
order by prod_price desc, prod_name;

六、过滤数据

• 检查单个值

select prod_id, prod_price, prod_name
from products
where prod_name = 'fuses' #可以使用=、<>、!=、<、>、<=、>=

• 范围值检查

select prod_id, prod_price, prod_name
from products
where prod_price between 5 and 10;

• 空值检查

select prod_id, prod_price, prod_name
from products
where prod_price is null;

七、数据过滤

• and操作符

select prod_id, prod_price, prod_name
from products
where vend_id = 1003 and prod_price <= 10;

•  or操作符

select prod_id, prod_price, prod_name
from products
where vend_id = 1003 or vend_id = 1003;

• 计算次序

select prod_id, prod_price, prod_name
from products
where (vend_id = 1003 or vend_id = 1003) and prod_price >= 10 #and操作符优先级高于or

• in操作符

select prod_id, prod_price, prod_name
from products
where vend_id in (1002, 1003);

in相对于or的优点：

（1）语法清楚直观；（2）计算次序容易管理；（3）执行更快；（4）可以包含其他select语句。

• not操作符

select prod_id, prod_price, prod_name
from products
where vend_id not in (1002, 1003);

八、使用通配符进行过滤（like操作符）

• %通配符（无法匹配null）

select prod_id, prod_price, prod_name
from products
where prod_name like '%anvil%';

• _通配符：匹配单个字符

select prod_id, prod_price, prod_name
from products
where prod_name like '_ ton anvil';

通配符搜索的处理一般比前面讨论的其他搜索所花时间更长。

九、用正则表达式进行搜索（regexp操作符）

like操作符匹配整个列，regexp操作符在列值内进行匹配。

• 基本字符匹配

select prod_id, prod_price, prod_name
from products
where prod_name regexp '.000';

• 进行or匹配

select prod_id, prod_price, prod_name
from products
where prod_name regexp '1000|2000';

• 匹配几个字符之一

select prod_id, prod_price, prod_name
from products
where prod_name regexp '[123] ton';

• 匹配范围

select prod_id, prod_price, prod_name
from products
where prod_name regexp '[1-5] ton';

• 匹配特殊字符

select prod_id, prod_price, prod_name
from products
where prod_name regexp '\\.';

\\f换页，\\n换行，\\r回车，\\t制表，\\v纵向制表，\\\匹配单斜杠

• 匹配字符类

select prod_id, prod_price, prod_name
from products
where prod_name regexp '[:alnum:]';

[:alnum]任意字母和数字，同[a-zA-Z0-9]

[:alpha:]任意字符，同[a-zA-Z]

[:blank:]空格和制表，同[\\t]

[:cuntrl:]ASCLL控制字符，ASCLL0到31和127

[:digit:]任意数字，同[0-9]

[:print:]任意可打印字符

[:graph:]与[:print:]相同，但不包括空格

[:lower:]任意小写字母，同[a-z]

[:upper:]任意大写子母，同[A-Z]

[:punct:]即不在[:alnum:]又不在[:cntrl:]中的任意字符

[:space:]包括空格在内的任意空白字符，同[\\f\\n\\r\\t\\v]

[:xdigit:]任意十六进制数字，同[a-fA-F0-9]

• 匹配多个实例

select prod_id, prod_price, prod_name
from products
where prod_name regexp '\\([0-9] sticks?\\)';

* 0个或多个匹配

+ 1个或多个匹配

？0个或1个匹配

{n} 指定数目的匹配

{n, }不少于指定数目的匹配

{n,m}匹配数目的范围（m不超过255）

• 定位符

select prod_id, prod_price, prod_name
from products
where prod_name regexp '^[0-9\\.]';

^ 文本的开始

$ 文本的结尾

[[:<:]] 词的开始

[[:>:]] 词的结尾

十、创建计算字段

字段：基本上与列的意思相同，经常互换使用，不过数据库列一般称为列，术语字段通常用在计算字段的连接上。

拼接：将值联结到一起构成单个值。

• 拼接字段：使用concat()函数

select concat(vend_name, '(', vend_country, ')')
from vendors;

• 使用别名（又名导出列）

select concat(vend_name, '(', vend_country, ')') as vend_title
from vendors;

• 执行算数计算

select prod_id, quantity, item_price, 
	   quantity*item_price as expanded_price
from orderitems
where order_num = 20005;

十一、使用数据处理函数

• 文本处理函数

Left()返回串左边的字符串

Right()返回串右边的字符串

Length()返回串的长度

Locate()找出串的一个子串

Lower()将串转换为小写

Upper()将串转换为大写

LTrim()去掉串左边的空格

RTrim()去掉串右边的空格

SubString()返回子串的字符

Soundex()返回串的SOUNDEX值

• 日期和时间处理函数

AddDate()增加一个日期

AddTime()增加一个时间

CurDate()返回当前日期

CurTime()返回当前时间

Date()返回日期时间的日期部分

DateDiff()计算两个日期之差

Date_Add()高度灵活的日期运算函数

Date_Format()返回一个格式化的日期或时间串

Day()返回一个日期的天数部分

DayOfWeek()对于一个日期，返回对应的星期几

Hour()返回一个时间的小时部分

Minite()返回一个时间的分钟部分

Month()返回一个日期的月份部分

Now()返回当前时间和日期

Second()返回一个时间的秒部分

Time()返回一个日期时间的时间部分

Year()返回一个日期的年份

• 数值处理函数

Abs()返回一个数的绝对值

Cos()返回一个角度的余弦

Exp()返回一个数的指数值

Mod()返回除操作的余数

Pi()返回圆周率

Rand()返回一个随机数

Sin()返回一个角度的正弦

Sqrt()返回一个数的平方根

Tan()返回一个角度的正切

十二、汇总函数

1.聚集函数：运行在行组上，计算和返回单个函数值。

• AVG()函数：返回所有列的平均值或特定列或行的平均值。（忽略列值为null的行）

select avg(prod_price) as avg_price
from products
where vend_id = 1003;

• COUNT()函数：确定表中行的数目或符合特定条件的行的数目。指定列名则忽略null，用*则不忽略。

select count(*) as num_cust
from customers;

select count(cust_email) as num_cust
from customers;

• MAX()函数：返回指定列的最大值。（忽略列值为null的行）

select max(prod_price) as max_price
from products;

• MIN()函数：返回指定列的最小值。（忽略列值为null的行）

select min(prod_price) as min_price
from products;

• SUM()函数：返回指定列值的和。（忽略列值为null的行）

select sum(item_price*quantity) as total_price
from orderitems
where order_num = 20005;

2.聚集不同值

• ALL：为默认，不需要指定，所有参数参与计算。

select avg(all prod_price) as avg_price
from products;

• DISTINCT：只包含不同的值。

select avg(distinct prod_price) as avg_price
from products;

3.组合聚集函数

select count(*) as num_items,
	   min(prod_price) as min_price,
       max(prod_price) as max_price,
       avg(prod_price) as avg_price
from products;

十三、数据分组

• 创建分组

select vend_id, count(*) as num_prods
from products
group by vend_id;

使用with rollup可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。

• 过滤分组：having支持所有where操作符。where过滤行，having过滤分组。

select cust_id, count(*) as orders
from orders
group by cust_id
having count(*) >= 2;

• 分组和排序

select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >= 50
order by ordertotal;

• select子句顺序

（1）select 要返回的列或表达式

（2）from 从中检索数据的表

（3）where 行级过滤

（4）group by 分组说明

（5）having 组级过滤

（6）order by 输出排序顺序

（7）limit 要检索的行数

十四、子查询

• 利用子查询进行过滤

select cust_name, cust_contact
from customers
where cust_id in (select cust_id 
				  from orders
                  where order_num in (select order_num
									  from orderitems
                                      where prod_id = 'TNT2'));

• 作为计算字段使用子查询

select cust_name, cust_state, 
	   (select count(*)
        from orders
        where orders.cust_id = customers.cust_id) as orders
from customers;

十五、联结表

外键：为某个表中的一列，包含另一个表的主键值，定义了两个表之间的关系。

可伸缩性：能够适应不断增加的工作量而不失败。

• 创建联结（会使性能下降）

select vend_name, prod_name, prod_price
from vendors, products
where vendors.vend_id = products.vend_id
order by vend_name, prod_name;

不指定where子句将会返回笛卡尔积。

• 内部联结：与where子句相同功能。

select vend_name, prod_name, prod_price
from vendors inner join products
where vendors.vend_id = products.vend_id
order by vend_name, prod_name;

• 联结多个表

select prod_name, vend_name, prod_price, quantity
from orderitems, products, vendors
where products.vend_id = vendors.vend_id
and orderitems.prod_id = products.prod_id
and order_num = 20005;

十六、创建高级联结

• 使用表别名

from customers as c, orders as o, orderitems as oi
where c.cust_id = o.cust_id
and oi.order_num = o.order_num
and prod_id = 'TNT2';

• 自联结

select p1.prod_id, p1.prod_name
from products as p1, products as p2
where p1.vend_id = p2.vend_id
and p2.prod_id = 'DTNTR';

• 自然联结：排除多次出现

select c.*, o.order_num, o.order_date, 
	   oi.prod_id, oi.quantity, oi.item_price
from customers as c, orders as o, orderitems as oi
where c.cust_id = o.cust_id
and oi.order_num = o.order_num
and prod_id = 'FB';

• 外部联结：包含没有关联行的那些行

select customers.cust_id, orders.order_num
from customers left outer join orders
on customers.cust_id = orders.cust_id;

• 使用带聚集函数的联结

select customers.cust_name, customers.cust_id, count(orders.order_num) as num_ord
from customers left outer join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;

十七、组合查询

• 创建组合查询：默认取消重复的行

select vend_id, prod_id, prod_price
from products
where prod_price <= 5
union
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 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 all
select vend_id, prod_id, prod_price
from products
where vend_id in (1001, 1002)
order by vend_id, prod_price;

十八、全文本搜索

MyISAM引擎支持全文本搜索，InnoDB不支持。

• 启用全文本搜索

创建表时使用fulltext()

•  进行全文本搜索：对比通配符和正则表达式，这个的返回结果以优先级排序。

select note_text
from productnotes
where match(note_text) against('rabbit');

演示排序工作：

select note_text, match(note_text) against('rabbit') as ranks
from productnotes;

• 使用查询扩展：设法放宽所返回的全文本搜索结果的范围。

select note_text
from productnotes
where match(note_text) against('anvils' with query expansion);

• 布尔文本搜索：不需要fulltext索引

select note_text
from productnotes
where match(note_text) against('anvils' in boolean mode);

+ 包含，词必须存在

- 排除，词必须不出现

>包含，且增加等级值

<包含，且减少等级值

()把词组成子表达式

~取消一个词的排序值

*词尾的通配符

''''定义一个短语

• 全文本搜索的使用说明

（1）短词（3个或3个以下字符的词，数目可改）被忽略且从索引中删除；

（2）内建的非用词列表被忽略，可覆盖；

（3）一个词出现在50%的行中则为非用词，不适用于in boolean mode；

（4）表中行数少于三行不返回结果；

（5）忽略词中单引号；

（6）不具有词分割符的语言不能恰当地返回结果；

十九、插入数据

• 插入完整的行

insert into phones(phone_name,
				   phone_price)
values('huawei mate 40', 
		4999);

省略列的前提：该列允许定义为NULL值或在表定义中给出默认值。

插入语句很耗时，可使用insert low_priority into来降低其优先级，使查询语句先执行。

• 插入多个行 （比一个一个加更快）

insert into phones(phone_name,
				   phone_price)
values('xiaomi 11', 
		3999),
	  ('iqoo 8',
       2999);

• 插入检索出的数据

insert into phones(phone_name,
				   phone_price)
select phone_name, phone_price
from phones;

二十、更新和删除数据

• 更新数据

update phones
set phone_name = 'iphone 13 pro',
	phone_price = 7999
where phone_id = 1;

如果在更新过程中有一个更新失败则取消所有更新；

可以通过设置为null来删除某个列值。

• 删除数据

delete from phones
where phone_id = 8 #删除特定行
delete from phones #删除所有行

使用truncate table可以更快的删除所有行。（实质是删除原来的表重新创建一个表）

二十一、创建和操纵表

• 创建表

create table phones #create table if not exists phones
(
	phone_id int not null auto_increment,
    phone_name char(50) not null,
    phone_price int not null,
    primary key(phone_id)
)engine=InnoDB;

null是没有值，不是空串。

每个表只允许一个auto_increment列，而且它必须被索引。

指定默认值phone_name char(50) not null default 'phone'。

引擎类型：

（1）InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索。

（2）MEMORY在功能等同于MyISAM，但由于数据库存储在内存中，速度很快。

（3）MyISAM是一个性能极高的引擎，它支持全文本搜索。

引擎可以混用。外键不能跨引擎。

• 更新表

alter table phones
add phone_size int #添加列
alter table phones
drop column phone_size #删除列

• 删除表

drop table customer;

• 重命名表

rename table phone to phones;

二十二、使用视图

视图时虚拟的表，视图只包含使用时动态检索数据的查询。

规则：

（1）视图必须唯一命名；

（2）可以创建的视图数目没有限制；

（3）为了创建视图，必须有足够的访问权限；

（4）视图可以嵌套；

（5）order by可以用在视图中，但会被select中的order by覆盖；

（6）视图不能索引，也不能有关联的触发器或默认值；

（7）视图可以和表一起使用。

• 利用视图简化复杂的联结

create view productcustomers as
select cust_name, cust_contact, prod_id
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num;

select cust_name, cust_contact
from productcustomers
where prod_id = 'TNT2';

• 用视图重新格式化检索出的数据

select * from phones;
create view vendorlocations as
select concat(rtrim(vend_name), '(', rtrim(vend_country), ')') as vend_title
from vendors;

select * from vendorlocations;

• 使用视图过滤不想要的数据

create view customeremail as
select cust_id, cust_name, cust_email
from customers
where cust_email is not null;

select * from customeremail;

• 使用视图与计算字段

create view orderitemsexpanded as
select order_num, prod_id, quantity, item_price, quantity*item_price as expanded_price
from orderitems;

select * from orderitemsexpanded;

• 更新视图

一般来说视图是可更新的，但更新一个视图将更新其基表。

如果视图中有以下操作不能更新：

（1）分组；（2）联结；（3）子查询；（4）并；（5）聚集函数；（6）DISTINCT；（7）导出列。

二十三、使用存储过程

• 创建存储过程

delimiter //
create procedure productpricing()
begin
	select avg(prod_price) as avg_price
    from products;
end //

• 执行存储过程

call productpricing();

• 删除存储过程

drop procedure productpricing;

• 使用参数

delimiter //
create procedure productpricing(
	out pl decimal(8, 2),
    out ph decimal(8, 2),
    out pa decimal(8, 2)
    )
begin
	select min(prod_price) as min_price
    into pl
    from products;
    select max(prod_price) as max_price
    into ph
    from products;
	select avg(prod_price) as avg_price
    into pa
    from products;
end//
call productpricing(@pricelow,
				    @pricehigh,
                    @priceaverage)//

delimiter //
create procedure ordertotal(
	in onumber int,
    out ototal decimal(8, 2)
    )
begin
	select sum(item_price*quantity)
    from orderitems
    where order_num = onumber
    into ototal;
end//
call ordertotal(20005, @total)//

• 建立智能存储过程

delimiter //
create procedure ordertotal(
	in onumber int,
    in taxable boolean,
    out ototal decimal(8, 2)
    )comment 'Obtain order total, optionally adding tax'
begin
	declare total decimal(8, 2);
    declare taxrate int default 6;
    
    select sum(item_price*quantity)
    from orderitems
    where order_num = onumber
    into total;
    
    if taxable then
		select total+(total/100*taxrate) into total;
	end if;
    select total into ototal;
end//
call ordertotal(20005, 1, @total)//

• 检查存储过程

show create procedure ordertotal #显示用来创建一个存储过程的create语句

show procedure status #列出所有存储过程
show procedure status like 'ordertotal' #指定过滤模式

二十四、使用游标（智能用于存储过程和函数）

• 创建游标

delimiter //
create procedure processorders()
begin
	declare ordernumbers cursor for
    select order_num from orders;
end//

• 打开和关闭游标

delimiter //
create procedure processorders()
begin
	declare ordernumbers cursor for
    select order_num from orders;
    
    open ordernumbers;
    close ordernumbers;
end//

• 使用游标数据

delimiter //
create procedure processorders()
begin
	declare done boolean default 0;
	declare o int;
    declare t decimal(8, 2);
    
	declare ordernumbers cursor for
    select order_num from orders;
    
    declare continue handler for sqlstate '02000' set done=1;
    
    create table if not exists 
	ordertotals(order_num int, total decimal(8, 2));
    
    open ordernumbers;
    repeat
		fetch ordernumbers into o;
        call ordertotal(o, 1, t);
        insert into ordertotals(order_num, total)
        values(o, t);
    until done end repeat;
    
    close ordernumbers;
end//

二十五、使用触发器

• 创建触发器

create trigger newproduct after insert on phones
for each row select 'phone added' into @arg

• 删除触发器

drop trigger newproduct

• insert触发器

create trigger neworder after insert on orders
for each row select new.order_num into @arg//

• delete触发器

create trigger deleteorder before delete on orders
for each row select old.order_date into @arg

• update触发器

create trigger updatephone before update on phones
for each row select new.phone_name into @arg

触发器不能调用存储过程。

二十六、管理事务处理

• 使用ROLLBACK

select * from phones;
start transaction;
delete from phones where phone_id = 1;
select * from phones;
rollback;
select * from phones;

• 使用COMMIT

start transaction;
delete from phones where phone_id = 1;
delete from phones where phone_id = 2;
commit;

如果有一个语句失败，则会撤销前面执行的语句。

当commit和rollback语句执行后，事务将会自动关闭。

• 使用保留点：在事务处理完成（执行一条rollback或commit）后自动释放。

start transaction;
savepoint delete1;
delete from phones where phone_id = 2;
rollback to delete1;

• 更改默认的提交行为

set autocommit = 0;

不自动提交，直到autocommit被设置为真，针对的是每个连接而不是服务器。

二十七、全球化和本地化

• 字符集：字母和符号的集合。
• 编码：某个字符集成员的内部表示。
• 校对：规定字符如何比较的指令。

show character set;显示所有可用的字符集以及每个字符集的描述和默认校对。

show collation;显示所有可用的校对以他们使用的字符集。

show variables like 'character%';
show variables like 'collation%'; 

确定所用的字符集和校对。

•  给表指定字符集和校对

create table mytable
(
	columnn1 int,
    columnn2 varchar(10)
)default character set hebrew
 collate hebrew_general_ci;

• 给特定列指定字符集和校对

create table mytable
(
	columnn1 int,
    columnn2 varchar(10) character set latin1 collate latin1_general_ci
)default character set hebrew
 collate hebrew_general_ci;

• 在order by中指定校对

select * from customers
order by lastname, firstname collate latin1_general_cs;

二十八、安全管理

• 查看用户

use mysql;
select user from user;

• 创建用户账号

create user ben identified by '1234';

• 重命名

rename user ben to siri;

• 删除用户账号

drop user siri;

• 查看访问权限

show grants for siri;

• 设置用户权限

grant select on crashcourse.* to siri;

• 撤销用户权限

revoke select on crashcourse.* from siri;

• 更改口令

二十九、数据库维护

• 备份数据

backup table 或 set into outfile

 

• 进行数据库维护 

analyze table phones;

check table phones;

replace table phones;修复相应的表

optimize table phones;回收空间

•  诊断启动问题

--help 显示帮助；

--safe-mode 装载减去某些最佳配置的服务器；

--verbose 显示全文本消息；

--version 显示版本信息然后退出；

• 查看日志文件

三十、改善性能