python全栈开发day56-mysql
1.数据库和表 show总结
SHOW DATABASES;返回可用数据库的一个列表。
SHOW TABLES;返回当前选择的数据库内可用表的列表。
SHOW COLUMNS FROM customers 等同于DESC customers。
SHOW STATUS，用于显示广泛的服务器状态信息；
SHOW CREATE DATABASE和SHOW CREATE TABLE，分别用来显示创建特定数据库或表的MySQL语句
SHOW GRANTS，用来显示授予用户（所有用户或特定用户）的安全权限；
SHOW ERRORS和SHOW WARNINGS，用来显示服务器错误或警告消息
8 使用通配符进行过滤
where name like '%jet' # 匹配0个或多个
like '%jet%'
name like 'my_' # 匹配一个，不多不少
9 正则表达式
REGEXP与like区别
REGEXP：行内匹配 不区分大小写
like：整行匹配
. #匹配任意一个字符
| #1000|2000 相当于or
where name regexp '[123] Ton' # 匹配几个字符之一
[^123]却匹配除这些字符外的任何东西
特殊字符转义
\\- #匹配-
\\. #匹配.
\\f #换页
\\n #换行
\\r #回车
\\t #制表
\\v #纵向制表
\\\ #匹配\
[:alnum:] 任意字母和数字（同[a-zA-Z0-9]）
[:alpha:] 任意字符（同[a-zA-Z]）
[:blank:] 空格和制表（同[\\t]）
[:cntrl:] ASCII控制字符（ASCII 0到31和127）
[:digit:] 任意数字（同[0-9]）
[:graph:] 与[:print:]相同，但不包括空格
[:lower:] 任意小写字母（同[a-z]）
[:print:] 任意可打印字符
[:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] 包括空格在内的任意空白字符（同[\\f\\n\\r\\t\\v]）
[:upper:] 任意大写字母（同[A-Z]）
[:xdigit:] 任意十六进制数字（同[a-fA-F0-9]）
匹配的数量：
* 0个或多个匹配
+ 1个或多个匹配（等于{1,}）
? 0个或1个匹配（等于{0,1}）
{n} 指定数目的匹配
{n,} 不少于指定数目的匹配
{n,m} 匹配数目的范围（m不超过255）
定位元字符：
^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾
10 创建计算字段
1） 拼接字符串
concat() 将多个列拼接在一起，其他数据库用+ 或|| 拼接
Rtrim Ltrim Trim
group_concat() 结合groupby一起使用
2） 执行算术运算
colA*colB as C,
11 使用数据处理函数
1） 常用的文本处理函数
Left() 返回串左边的字符
Right() 返回串右边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
LTrim() 去掉串左边的空格
RTrim() 去掉串右边的空格
Soundex() 返回串的SOUNDEX值
SubString() 返回子串的字符
Upper() 将串转换为大写
Lower() 将串转换为小写
2） 常用的日期和时间处理函数
AddDate() 增加一个日期（天、周等）
AddTime() 增加一个时间（时、分等）
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期，返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分
3） 数值处理函数
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切
12 汇总函数
1） 聚集函数
AVG() 返回某列的平均值，AVG()函数忽略列值为NULL的行。
COUNT() 返回某列的行数
使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
MAX() 返回某列的最大值
MAX()函数忽略列值为NULL的行。
MIN() 返回某列的最小值
IN()函数忽略列值为NULL的行。
SUM() 返回某列值之和以下
SUM()函数忽略列值为NULL的行。
运用标准的算术操作符，所有的聚集函数都可用来执行多个列上的计算。
2） 聚集不同值
以上5个聚集函数都可以如下使用：
对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认
行为）；
只包含不同的值，指定DISTINCT参数。
select avg(distinct(cola)) as avg_price # 剔除重复行再计算平均值
如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT），
否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。
13 分组数据
group by WITH ROLLUP
GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，
为数据分组提供更细致的控制。
如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上
进行汇总。换句话说，在建立分组时，指定的所有列都一起计算
（所以不能从个别的列取回数据）。
GROUP BY子句中列出的每个列都必须是检索列或有效的表达式
（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在
GROUP BY子句中指定相同的表达式。不能使用别名。
除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子
句中给出。
如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列
中有多行NULL值，它们将分为一组。
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
having：
HAVING和WHERE的差别 这里有另一种理解方法，WHERE在数据
分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重
要的区别，WHERE排除的行不包括在分组中。这可能会改变计
算值，从而影响HAVING子句中基于这些值过滤掉的分组
order by：
SELECT子句及其顺序：
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
14 使用子查询
格式化SQL
1）利用子查询进行过滤
虽然子查询一般与IN操作符结合使用，但也可以用于测试等于（=）、不等于（<>）等。
2） 作为计算字段使用子查询
15 连结表
1）笛卡儿积（cartesian product） 由没有联结条件的表关系返回
的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘
以第二个表中的行数。
2）内部联结（等值联结）
from t1 inner join t2
on t1.id = t2.id
效果等价于 where t1.id = t2.id
3) 联结多个表
不要联结不要的表，联结的越多性能下降的越厉害
16 创建高级联结
1） 使用表别名
缩短SQL语句；
允许在单条SELECT语句中多次使用相同的表
2） 自联结
select pro from tables1
where id in (
select id from tables1 where name = 'sb' );
select pro
from tables1 t1,tables1 t2
where t1.id = t2.id
and t2.name = 'sb';
3) 自然联结
。。。。
4） 外联结
from t1 left outer join t2
on t1.id = t2.id #t1中所有的数据
from t1 right outer join t2
on t1.id = t2.id #t2中所有的数据
5） 使用带聚集函数的联结
17 组合查询
1） union会默认去掉各个select中重复的行，如果不想去重使用union all
UNION必须由两条或两条以上的SELECT语句组成，语句之间用关
键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个
UNION关键字）。
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过
分析各个列不需要以相同的次序列出）。
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以
隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。
2） 对使用union的查询结果进行排序
然ORDER BY子句似乎只是最后一条SELECT语句的组成部分，但
实际上MySQL将用它来排序所有SELECT语句返回的所有结果。
3） 组合不同的表 为使表述比较简单，本章例子中的组合查询使
用的均是相同的表。但是其中使用UNION的组合查询可以应用
不同的表。
18 全文本搜索
1） 使用全文本搜索
（1）创建或修改表时，要指定全文本搜索的列full_text(列名)
（2） 查询方式1：select 列名 from 表名 where match(列名) against(匹配字符串)
（3） 查询方式2：select 列名 match(列名) against(匹配字符串) as rank from 表名
19 插入数据
插入完整的行
插入行的一部分
插入多行
插入某些查询的结果，直接跟select不用values
20 更新和删除数据
更新表中特定行；
update 表名 set 列名1=‘新值1’,列名2=‘新值2’ where 列名（条件）
更新表中所有行。
ingnore
IGNORE关键字 如果用UPDATE语句更新多行，并且在更新这些
行中的一行或多行时出一个现错误，则整个UPDATE操作被取消
（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发
生错误，也继续进行更新，可使用IGNORE关键字，如下所示：
UPDATE IGNORE customers…
删除表中的行：
delete from 表名 where 条件
如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，
21 创建和操纵表
1） 创建表
create table if not exists t7 (
id int,
name char(10)
) engine=innodb;
2）使用NULL
3）primary key ==not null unique
4）auto_increment
每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。
select last_insert_id;此语句返回最后一个auto_increment.
5）指定默认值 default 与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量。
6）引擎类型
ENGINE=InnoD
常用引擎对比：外键不能跨引擎
InnoDB是一个可靠的事务处理引擎（参见第26章），它不支持全文本搜索； MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）； MyISAM是一个性能极高的引擎，它支持全文本搜索（参见第18章），但不支持事务处理。
7） 更新表。
alter table 系列
复杂结构的表，建议新建表再通过insert select 语句进行。
insert into t7 select * from t6;
8） 删除表
drop table t1;
9 ) 重命名表
rename table t1 to t2；
alter table t1 rename to t2;
21 使用视图
1） 为什么使用视图
（1）重用SQL语句。
（2）简化复杂的SQL操作。在编写查询后，可以方便地重用他而不必知道它的基本查询细节
（3）使用表的组成部分而不是整个表
（4）保护数据。可以给用户授权表的特定部分的访问权限而不是整个表的访问权限。
（5）更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
2）使用视图
（1）创建视图相关命令：
create view 视图名
drop view 视图名
show create view 视图名;
create or replace view 视图名;
或者 drop view 视图名 if exists，后创建。
（2）create view v1 as
select 。。。
from 表名或视图名
where 。。。
（3） 使用视图重新格式化
如给复杂的查询名别名
（4）用视图过滤不想要的数据
（5）使用视图与计算字段
3） 视图的更新update delete
含有以下内容视图不能更新：
分组（使用GROUP BY和HAVING）；
联结；
子查询；
并；
聚集函数（Min()、Count()、Sum()等）；
DISTINCT；
导出（计算）列。
23 使用存储过程
不能编写存储过程？你依然可以使用 MySQL将编写存储过程的安全和访问与执行存储过程的安全和访问区分开来
1） 创建不带参数的存储过程，调用存储过程，删除存储过程
# delimiter 定界符
delimiter //
create procedure p1()
begin
select name as n1
from t7
where id =1;
end //
delimiter;
注意：在命令行程序中定义存储过程
drop procedure if exists p1;
2） 创建带参数的存储过程，调用存储过程
注释--、OUT、IN、OUTIN、comment
declare total dec
create table product(
id int primary key auto_increment, // 设置为自增的字段，必须设置为主键
price int not null
);
insert into product(price) values(80),(90),(89);
delimiter //
create procedure productpricing(
out max_ decimal(8,2),
out min_ decimal(8,2),
out avg_ decimal(8,2)
)
begin
select max(price) into max_ from product;
select min(price) into min_ from product;
select avg(price) into avg_ from product;
end//
delimiter ; # 两者之间有空格。
------------调用------------
call productpricing(
@max_,
@min_,
@avg_
)
--------
select @max_ ....
3） 创建智能存储过程
--Name:ordertotal
--Parameters:oid = order id
-- taxable = 0 if not taxable,1 if taxable
-- ototal = order total variable
call ordertotal(1,1,@total)
4） 检查存储过程
show create procedure ordertotal;
show procedure status like 'XXXOOO'
24 使用游标
只能用于存储过程 不像多数DBMS，MySQL游标只能用于存储过程（和函数）。
定义游标，打开游标，fetch每行数据，关闭游标
delimiter //
create procedure cursor_test()
begin
declare o int;
declare t decimal(8,2);
declare done boolean default 0;
declare oid cursor
for
select id
from product;
declare continue handler for sqlstate '02000' set done=1;
create table if not exists ordertotals
(id int,total decimal(8,2));
open oid;
repeat
fetch oid into o;
call ordertotal(o,1,t);
insert into ordertotals(id,total) values(o,t);
until done end repeat;
close oid;
end//
delimiter ;
25 使用触发器
1）小试牛刀
delimiter //
create trigger product_insert after insert on product1
for each row select 'add';
delimiter ;
（1） 触发器作用于表，而不能作用于视图
（2） 触发器只能由update，insert，delete事件
(3) insert 前会生成一个new临时记录，里边更新相应的要插入的数据，但auto_increment 列为0，after之后才会更新
(4) delete 前会生成一个old表里边有相应的要删除的数据
（5） update 之前会生成两个表new 和 old
##遗憾的是，MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内
26 管理事务处理
InnoDB 支持明确的事务处理
MyISAM 不支持
1） 查看当前使用数据库或表使用的引擎
看你的mysql现在已提供什么存储引擎:
mysql> show engines;
看你的mysql当前默认的存储引擎:
mysql> show variables like '%storage_engine%';
你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
mysql> show create table 表名;
2) 几个名词解释
事务（transaction）指一组SQL语句；
回退（rollback）指撤销指定SQL语句的过程；
提交（commit）指将未存储的SQL语句结果写入数据库表；
保留点（savepoint）指事务处理中设置的临时占位符（placeholder），
你可以对它发布回退（与回退整个事务处理不同）。
3）控制事务处理
（1）MySQL使用下面的语句来标识事务的开始：
start transaction
（2）使用ROLLBACK
小例子：
select * from product;
start transaction;
delete from product;
select * from product;
rollback;
select * from product;
哪些能操作能回退，哪些操作不能回退？
事务处理用来管理INSERT、UPDATE和DELETE语句。
你不能回退SELECT语句（这样做也没有什么意义。）
你不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。
（3） commit
start transaction;
delete from b1 where id = 1;
delete from b2 where id = 1;
commit # 两条delete语句必须都执行成功才会提交，否则则会rollback
隐含事务关闭 当COMMIT或ROLLBACK语句执行后，事务会自动关闭（将来的更改会隐含提交）
（4） 保留点
（savepoint）指事务处理中设置的临时占位符（placeholder），
savepoint tag1
rollback to tag1; 回退到指定的保留点，可以不用回到事务的起始位置start transaction.
释放保留点 保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。自MySQL 5以来，也可以用RELEASESAVEPOINT明确地释放保留点
(5) 更改默认的提交行为
set autocommit = 0; # 就不会默认提交。
标志为连接专用 autocommit标志是针对每个连接而不是服务器的。
27 字符集和语言
字符集为字母和符号的集合；
. 编码为某个字符集成员的内部表示；
. 校对为规定字符如何比较的指令。
1） 字符集
show chartacter set # 这条语句显示所有可用的字符集以及每个字符集的描述和默认校对。
show collation # 此语句显示所有可用的校对，以及它们适用的字符集 ，一次区分大小写（由_cs表示），
一次不区分大小写（由_ci表示）
show variables like 'character%' # 当前
show variables like 'collation%'
create table a() default character set utf8 collate utf8-general_ci
串可以在字符集之间进行转换。为此，使用Cast()或Convert()函数。
28 安全管理
select user from user； # 查询所有的用户列表。
create user wcy identified by '123';
rename user wcy to wucy;
drop user wucy;
show grants for wucy;
grant select,insert on db1.* to wucy;
revoke select,insert on db1.* from wucy;
set password for wucy = password('456');
set password = password('456'); 设置当前用户的密码
GRANT和REVOKE可在几个层次上控制访问权限
整个服务器，使用GRANT ALL和REVOKE ALL；
整个数据库，使用ON database.*；
特定的表，使用ON database.table；
特定的列；
特定的存储过程。
29 数据库维护
1） 备份
(1)使用命令行实用程序mysqldump转储所有数据库内容到某个外部
文件。在进行常规备份前这个实用程序应该正常运行，以便能正
确地备份转储文件。
(2)可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据
（并非所有数据库引擎都支持这个实用程序）。
(3)可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所
有数据到某个外部文件。这两条语句都接受将要创建的系统文件
名，此系统文件必须不存在，否则会出错。数据可以用RESTORE
TABLE来复原。
首先刷新未写数据 为了保证所有数据被写到磁盘（包括索引
数据），可能需要在进行备份前使用FLUSH TABLES语句。
2) 进行数据库维护
analyze table
check table
repair table
optimize table
为查看当前设置，可使用SHOW VARIABLES; 和SHOW STATUS;。）
补充：索引
Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引
PRIMARY KEY（主键索引） ALTER TABLE `table_name` ADD PRIMARY KEY ( `col` )
UNIQUE(唯一索引) ALTER TABLE `table_name` ADD UNIQUE (`col`)
INDEX(普通索引) ALTER TABLE `table_name` ADD INDEX index_name (`col`)
FULLTEXT(全文索引) ALTER TABLE `table_name` ADD FULLTEXT ( `col` )
组合索引 ALTER TABLE `table_name` ADD INDEX index_name (`col1`, `col2`, `col3` )
Mysql各种索引区别：
普通索引：最基本的索引，没有任何限制
唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引：它 是一种特殊的唯一索引，不允许有空值。
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。创建复合索引时应该将最常用（频率）作限制条件的列放在最左边，依次递减。
组合索引最左字段用in是可以用到索引的，最好explain一下select。
浙公网安备 33010602011771号