SQL
2020-10-10 09:43:18 0 举报AI智能生成
MYSQL 数据库语法
作者其他创作
大纲/内容
SQL
MYSQL
DDL
数据库定义语言： 数据库、表、视图、索引、存储过程
DML
数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
DCL
数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE
数据库结构
创建数据库
create database 名称 charset utf8；
查看数据库
show create database 名称；
show databases；
select database();
更改数据库
alter database 名称 charset GBK
使用数据库
use 名称；
删除数据库
drop database 名称；
总结
对于数据结构有 创建，查看，使用，删除四种语法
create database 名称;，show create database 名称;，drop database 名称;
use 名称; show databases;
其他：显示数据库下的表 show tables;
数据库表结构
新建数据库表
复制表
create tabke new_table select * from old_table;
复制表结构+记录，不复制主键外键和索引
create new_table select * from old_table where 1=2;
只复制表结构
更改数据库表
修改表名
alter table 名称 rename 新表名；
增加字段
alter table 名称 add 字段名 数据类型 [完整性约束 first(放在第一位)/ after field （放到字段后面）];
删除字段
alter table 名称 drop filed;
修改字段类型或者约束条件
alter table 名称 modify filed 数据类型 [完整性约束]；
只改属性
修改字段名
alter table 名称 change oldname newname 原数据属性和约束条件;
只改字段名
alter table 名称 change oldname newname 新数据属性和约束条件;
字段名和属性都改
查看数据库表
show tables;
show create table 表名\\G;
desc table 表名;
删除数据库表
删除表
drop table 表名;
修改表名称
rename table 原表名 to 新表名
数据库记录
增加记录
插入查询结果
删除记录
delete from 表名 where ...;
修改记录
查询记录
单表查询
语法
where 条件
group by field
having 筛选
order by feild
limit 限制条数
执行顺序
from 找到表
where
group by
having
select
distinct 去重
order by
limit
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数
子主题
select * from employee where name regexp '^jin.*[gn]$';
查看所有员工中名字是jin开头，n或者g结果的员工信息
多表查询
多表连接查询
inner join
left join
right join
full join
select * from employee left join department on employee.dep_id = department.idunionselect * from employee right join department on employee.dep_id = department.id
注意 mysql不支持全外连接 full JOIN， mysql可以使用此种方式间接实现全外连接，union与union all的区别：union会去掉相同的纪录
复合条件查询
子查询
#1：子查询是将一个查询语句嵌套在另一个查询语句中。#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字#4：还可以包含比较运算符：= 、 !=、> 、<等
带in关键字的子查询
select name from department where id not in (select distinct dep_id from employee);
not in 无法处理null的值
select * from dep where id not in (select distinct dep_id from emp where dep_id is not null);
带ANY关键字的子查询
select * from employee where salary = any (select max(salary) from employee group by depart_id);
“=ANY”等价于 IN 运算符，而“<>ANY”则等价于 NOT IN 运算符
ANY 运算符不能与固定的集合相匹配，比如下面的 SQL 语句是错误的
带ALL关键字的子查询
select * from employee where salary > all (select avg(salary) from employee group by depart_id);
all同any类似，只不过all表示的是所有，any表示任一
select * from employee where salary < any ( select avg(salary) from employee group by depart_id);
带比较运算符的子查询
比较运算符：=、!=、>、>=、<、<=、<>
带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。True或False当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
#department表中存在dept_id=203，Turemysql> select * from employee -> where exists -> (select id from department where id=200);
#department表中存在dept_id=205，Falsemysql> select * from employee -> where exists -> (select id from department where id=204);Empty set (0.00 sec)
in与exists
当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率
# exists的执行原理为：1、依次执行外部查询：即select * from class 2、然后为外部查询返回的每一行分别执行一次子查询：即(select * from stu where stu.cid=class.cid)3、子查询如果返回行，则exists条件成立，条件成立则输出外部查询取出的那条记录
in后跟的都是子查询，in()后面的子查询 是返回结果集的
not in与 not exists
not exists查询的效率远远高与not in查询的效率。
not in()子查询的执行顺序是：为了证明not in成立，即找不到，需要一条一条地查询表，符合要求才返回子查询的结果集，不符合的就继续查询下一条记录，直到把表中的记录查询完，只能查询全部记录才能证明，并没有用到索引。
not exists的执行顺序是：在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。
权限管理
登录
mysql -uroot -p
初次登录没有密码
select user()
查看账号
创建用户
create user 'egon'@'1.1.1.1' identified by '123';
create user 'egon'@'192.168.1.%' identified by '123';
create user 'egon'@'%' identified by '123';
初始状态下，管理员root，密码为空，默认只允许从本机登录localhost
设置密码
mysqladmin -uroot password \"123\"
设置初始密码 由于原密码为空，因此-p可以不用
mysqladmin -uroot -p\"123\" password \"456\"
mysql -h172.31.0.2 -uroot -p456
忘记密码
#1 关闭mysql net stop mysql
#3 net start mysql;在cmd中执行：mysql
#4 执行如下sql：
update mysql.user set authentication_string=password('') where user = 'root';
flush privileges;
#5 tskill mysqld #或taskkill -f /PID 7832
#6 重新启动mysql
更改密码
update mysql.user set password = password('...') where user = ' ..' and host = 'localhost';
对所有库的授权 *.*
grant select on *.* to 'egon1'@'localhost' identified by '123';
只在user表中可以查到egon1用户的select权限被设置为Y
对某一数据库：db1.*
grant select on db1.* to 'egon2'@'%' identified by '123';
只在db表中可以查到egon2用户的select权限被设置为Y
对某一个表：db1.t1
grant select on db1.t1 to 'egon3'@'%' identified by '123';
只在tables_priv表中可以查到egon3用户的select权限
对某一个字段：
可以在tables_priv和columns_priv中看到相应的权限
删除权限
revoke select on db1.* from 'egon'@'%';
数据类型
数值
int
11个数字，int不够的话 还有bigint（20个数字）
float/double
日期时间
date
YYYY-MM-DD格式，在1000-01-01 和 9999-12-31之间
datetime
YYYY-MM-DD HH:MM:SS格式
time
HH:MM:SS
YEAR(2|4)
字符串
char(m)
如果内容小于指定长度，右边填充空格，默认长度为1
varchar(m)
查询速度慢，节省存储空间，必须指定长度
text
存储二进制大数据，最大长度65535
枚举
enum
单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
集合
set
约束条件
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
一张表中必须有且只有一个主键。
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
缺省的默认值是NULL
UNSIGNED 无符号
ZEROFILL 使用0填充
其他
视图
view多表查询产生的临时表，可以存成视图，方便下次使用，并节省内存
create view teacher_view as select tid from teacher where tname='李平老师';
#1. 使用视图以后就无需每次都重写子查询的sql，但是这么效率并不高，还不如我们写子查询的效率高#2. 而且有一个致命的问题：视图是存放到数据库里的，如果我们程序中的sql过分依赖于数据库中存放的视图，那么意味着，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，而通常在公司中数据库有专门的DBA负责，你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便复制代码
DROP VIEW teacher_view
触发器
trigger使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询
NEW表示即将插入的数据行，OLD表示即将删除的数据行。
存储过程
程序与数据库结合使用的三种方式
#方式一： MySQL：存储过程 程序：调用存储过程#方式二： MySQL： 程序：纯SQL语句#方式三： MySQL: 程序：类和对象，即ORM（本质还是纯SQL语句）
procedure把一堆sql语句放到procedure里头，通过调procedure的名字就可以执行一堆语句。
无参
有参
#in 仅用于传入参数用
#out 仅用于返回值用
#inout 既可以传入又可以当作返回值
drop procedure proc_name;
事务
transaction 同时成功，同时失败，比如转账
pymysql
索引
类似书的目录，用于优化查询，主要是key
primary key 主键
unique key 无重复
index key 索引键
索引的两大类型
hash类型的索引：查询单条快，范围查询慢btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
#不同的存储引擎支持的索引类型也不一样InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；
用法
创建索引
CREATE在已存在的表上创建索引 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ;
create index ix_age on t1(age);
ALTER TABLE在已存在的表上创建索引 ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;
alter table t1 add index ix_sex(sex);
删除索引
DROP INDEX 索引名 ON 表名字;
命中索引，提高效率
条件明确，避免条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like、
选择区分度高的列作为索引
区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，
索引列不能参与计算，保持列“干净
在条件为name='egon' and gender='male' and id>333 and email='xxx'的情况下，我们完全没必要为前三个条件的字段加索引，因为只能用上email字段的索引，前三个字段的索引反而会降低我们的查询效率
排序条件为索引，则select字段必须也是索引字段，否则无法命中
其他注意事项
- 避免使用select *- count(1)或count(列) 代替 count(*)- 创建表时尽量时 char 代替 varchar- 表的字段顺序固定长度的字段优先- 组合索引代替多个单列索引（经常使用多个条件查询时）- 尽量使用短索引- 使用连接（JOIN）来代替子查询(Sub-Queries)- 连表时注意条件类型需一致- 索引散列值（重复少）不适合建索引，例：性别不适合
数据备份
MySQL数据备份
#1. 物理备份： 直接复制数据库文件，适用于大型数据库环境。但不能恢复到异构系统中如Windows。
#2. 逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。
使用mysqldump实现逻辑备份
mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql
mysqldump -uroot -p123 --all-databases > all.sql
恢复逻辑备份
[root@egon backup]# mysql -uroot -p123 < /backup/all.sql
mysql> use db1;mysql> SET SQL_LOG_BIN=0;mysql> source /root/db1.sql
#3. 导出表： 将表导入到文本文件中
SELECT... INTO OUTFILE 导出文本文件
LOAD DATA INFILE 导入文本文件
pymysql模块
pip3 install pymysql
链接、执行sql、关闭（游标）
execute()之sql注入
# 原来是我们对sql进行字符串拼接# sql=\"select * from userinfo where name='%s' and password='%s'\
增、删、改：conn.commit()
查：fetchone，fetchmany，fetchall
res3=cursor.fetchone()res4=cursor.fetchmany(2)res5=cursor.fetchall()
获取插入的最后一条数据的自增ID
0 条评论
回复 删除
下一页
