创建用户，库，表
创建用户：grant 操作权限 on 库名.表名 to '用户名@ip地址' identified by '密码';
查询用户：select user from mysql.user;
删除用户: delete from mysql.user where user='待删除的user';
新建库: create database 库名;
查询库: show databases;
切换到库: use 库名;
新建表: create table 表名(id int,name char(8));
查表：show tables;
查看表结构: desc 表名;
改表名等: alter table t1 change name name1 char(12);
删除表：drop table t1;
操作内容：
增：insert into t1 values(1,'alex'),(2,'wusir');
查：select * from t1;
改：update t1 set name='sb' where id=1;
删：delete from where id=1;
表的增删改
1.存储引擎：
innodb 数据存储在硬盘 存两个文件 支持事务、行级锁、外键
myisam 数据存储在硬盘 存三个文件 不支持事务、行级锁、外键
memory 数据存储在内存 重启server数据丢失
2. 常用的数据类型：int、float、datetime、char、varchar、enum、set
char和varchar的区别：
char:定长字符串、表示的长度小、浪费储存空间、读写速度快。
varchar:变长字符串、表示的长度大、节省储存空间、读写速度慢。
3. create table 表名（
id int unique auto_increment, # 唯一主键
id int primary key, # 设置主键
name char(12),
password char(32)
unique(name,password) # 联合唯一
primary key(name,password) # 设置联合主键
sex enum('male','female') not null default 'male' # 不为空和默认
foreign key(id) references 外表(字段名) # 设置外键
on update cascade on delete cascade # 级联更新，级联删除
)
# 创建表
create table 表名(
字段名 类型(长度约束) 其他约束,
字段名 类型(长度约束) 其他约束,
字段名 类型(长度约束) 其他约束);
# 删除表：
drop table 表名
# 修改表
alter table 表名 rename 新表名;
add 新字段 类型(长度约束) 其他约束 first;
drop 字段 ;
modify 原字段名 新类型(新长度) 新约束 after 某字段;
change 原字段名 新字段名 新类型(新长度) 新约束;
# 查看表结构
desc 表名 == describe 表名
show create table 表名; 查看详细表结构,存储引擎 编码 更复杂的约束条件
单表查询
增加数据：insert into 表名 values（...）
删除数据：delete from 表名 where id=3
更新数据：update 表名 set 字段名=新的值 where age>20
表的查询：
单表查询：
select 字段名 from 表名
select * from 表
去重 distinct
select distince 某个字段 from 表 # 对某个字段去重
对筛选的结果进行四则运算
select 字段*12 from 表
在显示查询结果的时候临时重命名
select 字段名 as 新名字 from 表;
select 字段名 新名字 from 表;
concat函数
concat('你想拼接的内容',字段名,'你想拼的其他内容','字段名')
concat_ws('连接符号',字段1,字段2,....)
case语句
SELECT
(case
when emp_name = 'jingliyang' then
emp_name
when emp_name = 'alex' then
concat(emp_name,'_bigsb')
else
concat(emp_name,'_sb')
end) as new_name
FROM employee;
where条件
select 字段 from 表 where 条件
比较运算符 > < >= <= <> !=
范围(范围更大) between a and b 查询a,b之间的所有内容
范围(范围更精准) in (a,b,c) 查询值为a或者b或者c的所有内容
like
select * from 表 where emp_name like '金%'（%是一个通配符,表示任意长度的任意內容）
select * from 表 where emp_name like '金三_'（_也是一个通配符,表示一个长度的任意内容）
逻辑运算符 and or not
身份运算符 is null/ is not null
正则匹配
所有人的身份证号,匹配所有身份证号是15位的居民 ^\d{15}$
select 字段 from 表 where age regexp '^\d{15}$';
group by（分组）
根据分组的字段自动的做去重，其他重复的项目都不会在结果中显示
但是可以使用count来计算每个组中的项，也可以使用group_concat来查看组内的名字
having
总是和group by 连用,where中不能出现聚合函数,所以和聚合函数有关的条件筛选也只能用having
对分组进行条件过滤
order by
默认从小到大排序 升序 asc
从大到小排序 降序 desc
limit
取前n个或者web开发中做分页功能
显示前n条 limit n
从第m条开始,显示n条 limit m,n
从第m+1条开始,显示n条 limit n offset m
多表查询（多练习）
内连接
select * from employee inner join department on employee.dep_id=department.id;
左连接
select * from employee left join department on employee.dep_id=department.id;
右连接
select * from employee right join department on employee.dep_id=department.id;
全外连接
select * from employee left join department on employee.dep_id=department.id
-> union
-> select * from employee right join department on employee.dep_id=department.id;
索引及pymysql
# 索引原理
# b+树
# 1.b 是balance 表示的是这个树最终是能够达到平衡的
# 2.数据不是平铺直叙的存储在硬盘山
# 3.影响查询速度的最重要的因素是树的高度
# 4.我们要做的事情,或者我们想要加速查询 降低树的高度
# 1.让索引的字段尽量的短
# 2.让索引的字段区分度高
# 5.b+树和普通的b树比起来有什么区别
# 1.b+树只在叶子节点存数据
# 有利于降低树的高度
# 稳定查询所有数据的io次数
# 2.在所有的叶子节点之间添加了双向链表
# 导致了所有的范围查询b+树的效率比b树高
# 6.mysql中 innodb myisam的索引都是由b+树完成的
# innodb 支持 聚集索引(叶子节点存具体的数据) + 辅助索引(叶子节点存地址)
# 聚集索引也叫 聚簇索引
# myisam 只支持辅助索引,也叫非聚集索引
索引
mysql中所有的key
index key 普通索引,能够加速查询,辅助索引
unique key 唯一 + 索引,辅助索引
primary key 唯一 + 非空 + 聚集索引
foreign key 本身没有索引的,但是它关联的外表中的字段是unique索引
primary key 和unique 标识的字段不需要再添加索引
能用unique的时候尽量不用index，unique除了是索引之外还能做唯一约束,如果做了唯一约束
创建索引
creeat index 索引名 on 表名(字段名)
删除索引
drop index 索引名 on 表名
关于索引的说明
1.条件一定是建立了索引的字段,如果条件使用的字段根本就没有创建索引,那么索引不生效
2.如果条件是一个范围,随着范围的值逐渐增大,那么索引能发挥的作用也越小
3.如果使用like进行模糊查询,那么使用a%的形式能命中索引,%a形式不能命中索引
4.尽量选择区分度高的字段作为索引列
5.索引列不能在条件中参与计算,也不能使用函数
6.在多个条件以and相连的时候,会优点选择区分度高的索引列来进行查询
在多个条件以or相连的时候,就是从左到右依次判断
7.制作联合索引
7.1.最左前缀原则 a,b,c,d 条件是a的能命中索引,条件是a,b能命中索引,a,b,c能命中,a,c.... 只要没有a就不能命中索引
如果在联合查询中,总是涉及到同一个字段,那么就在建立联合索引的时候将这个字段放在最左侧
7.2.联合索引 如果按照定义顺序,从左到右遇到的第一个在条件中以范围为条件的字段,索引失效
尽量将带着范围查询的字段,定义在联合索引的最后面
如果我们查询的条件总是多个列合在一起查,那么就建立联合索引
create index ind_mix on s1(id,email)
select * from s1 where id = 1000000 命中索引
但凡是创建了联合索引,那么在查询的时候,再创建顺序中从左到右的第一列必须出现在条件中
select count(*) from s1 where id = 1000000 and email = 'eva10%'; 命中索引
select count(*) from s1 where id = 1000000 and email like 'eva10%'; 可以命中索引
select * from s1 where id >3000 and email = 'eva300000@oldboy'; 不能命中索引
8.条件中涉及的字段的值必须和定义表中字段的数据类型一致,否则不能命中索引
关于索引的两个名词
覆盖索引 查一个数据不需要回表
select name from 表 where age = 20 不是覆盖索引
select age from 表 where age =20 是覆盖索引
select count(age) from 表 where age =20 是覆盖索引
合并索引
当我们为单独的一列创建索引的时候
如果条件是这一列,且使用正确就可以命中索引
当我们为两列分别创建单独的索引的时候
如果这两列都是条件,那么可能只能命中期中一个条件
如果这两列都是条件,那么可能会命中两个索引 - 合并索引
我们为多列直接创建联合所以
条件命中联合索引
执行计划
看看mysql准备怎么执行这条语句 可以看到是否命中索引,计划能命中哪些,实际命中了哪些,执行的顺序,是否发生了索引合并,覆盖索引
explain select * from s1;
pymysql
import pymysql
db = pymysql.connect(host='localhost', user='root',
database='homework')
cur = db.cursor(cursor=pymysql.cursors.DictCursor)
cur.execute('select * from class')
ret = cur.fetchone()
print(ret) # 查一个
ret1 = cur.fetchmany(4)
print(ret1) # 查四个
ret2= cur.fetchall()
print(ret2) # 查全部 少用
db.commit()
cur.close()
db.close()
概念补充
存储引擎
innodb : 外键 行级锁(并发修改) 事务(客户管理系统)
myisam : 表级锁 不支持外键\事务\行级锁
memory : 只能在内存中存储数据 重启server数据丢失
# 第一种方式: Myisam 是5.5之前默认的存储引擎
# 数据存在硬盘上,存三个文件,表结构,数据,和搜索目录
# 既不支持事务、也不支持外键、不支持行级锁
# 只支持表锁
# 对于只读操作比较多的情况 查询速度相对快
# 第二种方式: Innodb 是5.6之后的默认存储引擎
# 数据存在硬盘上,存两个文件,表结构,(数据和搜索目录)
# 支持事务
# 支持行级锁
# 支持外键
# 第三种方式: Memory
# 数据存在内存中,存一个文件,表结构(在硬盘上)
# 数据容易丢失,但读写速度都快
# 几个需要讲解的关键词
# 事务 transaction
# 在修改数据时保证了数据的安全性
# 行级锁和表级锁
# 外建约束
mysql中的基础数据类型
数字
int
float(8,2)
字符串
char 定长字符串 越是长度固定char越节省空间 读写速度快
varchar 变长字符串 越是长度不固定varchar越节省空间 读写速度慢
时间
year
date 入职日期 离职 开学 毕业
time
datetime 出生日期 交易记录 打卡时间
timestamp 一般不用
enum 和 set
enum 单选 （性别）
# enum('male','female')
set 多选(去重)
完整性约束
id int unsigned # 无符号的整数
id int default 0 # 默认为0
id int not null
id int unique
auto_increment # 相当于非空+自增且只能用于整数类型
# id int unique auto_increment
# id int primary key auto_increment
非空 + 唯一
# id int unique not null 如果没有主键,第一个设置非空唯一的就是主键
联合唯一
# id int,
# name char(12),
# unique(id,name)
primary key 主键 一张表只能有一个主键
# id int primary key
foreign key 外键
# id int,
# name char(12),
# tid int,
# foreign key(tid) references 外表(字段名) on update cascade on delete cascade
数据备份
数据备份
#语法：
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
#示例：
#单库备份
mysqldump -uroot -p123 db1 > db1.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; #关闭二进制日志，只对当前session生效
mysql> source /root/db1.sql
事物和锁
begin; # 开启事务
select * from emp where id = 1 for update; # 查询id值，for update添加行锁；
update emp set salary=10000 where id = 1; # 完成更新
commit; # 提交事务
