基础——Mysql数据库

数据库

• 关系型数据库
• 跨平台
• 支持多种编程语言（python、java、php）
• 基于磁盘存储，数据是以文件形式存放在数据库目录/var/lib/mysql下

库名

1. 数字、字母、下划线，不能用纯数字
2. 库名区分大小写
3. 不能使用特殊字符和mysql关键字

基本sql命令

1、查看已有库；
show databases;
2、创建库并指定字符集；
create database 库名 charset utf8;
3、查看当前所在库；
select database();
4、切换库；
use 库名;
5、查看库中已有表；
show tables;
6、删除库；
drop database 库名;

表管理

1、创建表并指定字符集；
 create table 表名(字段名  字段类型)charset=utf8
2、查看创建表的语句 (字符集、存储引擎)；
  show create table 表名
3、查看表结构;
  desc 表名
4、删除表;
  drop table 表名1，表名2

表记录管理

1、增 ： insert into 表名(字段名)values();   多个 insert into 表名(字段名)values(),();
2、删 ： delete from 表名 where 条件;
3、改 ： update 表名 set 字段名=值 where 条件;
4、查 ： select 字段名/* from 表名 where 条件;

表字段管理

1、增 ： alter table 表名 add 字段名  字段类型  first/after 字段名;
2、删 ： alter table 表名 drop 字段名;
3、改 ： alter table 表名 modify 字段名 新类型;
4、表重命名：alter table 表名 rename 新表名;

日期时间运算

select * from 表名 where 字段名 运算符(NOW()-interval 间隔);
间隔单位: 1 day | 3 month | 2 year
eg1:查询1年以前的用户充值信息
select* from user where time<(NOW()-interval 1 year);

日期时间函数

• now() 返回服务器当前时间
• curdate() 返回当前日期
• curtime() 返回当前时间
• date(date) 返回指定时间的日期
• time(date) 返回指定时间的时间

模糊比较（like）

where 字段名 like 表达式(%_)
eg1 : 查询北京的姓赵的学生信息
select * from students where address='北京' and name like '赵%';

查询（select）

• order by
  给查询的结果进行排序

order by 字段名 ASC(正序)/DESC（倒序）
eg1:查询成绩从高到低排列
select * from students order by score DESCC

• limit
  限制显示查询记录的条数

limit n:显示前n条
limit m,n:从第(m+1)条记录开始，显示n条
eg1：分页：每页显示10条，显示第6页的内容
limit(6-1)*10 10

数据库高级

MySql 普通查询

3、select …聚合函数 from 表名
1、where …
2、group by …
4、having …
5、order by …
6、limit …;

• 聚合函数

方法	功能
avg	平均值
max	最大值
min	最小值
sum	求和
count	求总数

• group by
  group by后字段名必须要为select后的字段
  查询字段和group by后字段不一致,则必须对该字段进行聚合处理(聚合函数)

• having语句
  对分组聚合后的结果进行进一步筛选

eg1 : 找出平均攻击力大于105的国家的前2名,显示国家名称和平均攻击力
select country,avg(attack) as number from sanguo  group by country having number>105 order by number DESC limit 2;

注意
having语句通常与group by联合使用
having语句存在弥补了where关键字不能与聚合函数联合使用的不足,where只能操作表中实际存在的字 段,having操作的是聚合函数生成的显示列

• distinct语句
  不显示字段重复值

eg1：表中有哪些国家
select distinct country from 表名

索引

• 定义
  对数据库表的一列或多列的值进行排序的一种结构

• 优点
  加快数据检索速度

• 缺点
  占用物理存储空间
  当对表中数据更新时,索引需要动态维护,降低数据维护速度

• 创建索引
  建表时：

create table 表名(
字段名 数据类型，
字段名 数据类型，
index(字段名),
index(字段名),
unique(字段名)
);

在已有表中创建索引

create [unique] index 索引名 on 表名(字段名);

• 查看索引

desc 表名;

• 删除索引

drop index 索引名 on 表名；

外键

让当前表字段的值在另一个表的范围内选择

-语法

foreign key(参考字段名)
references 主表(被参考字段名)
on delete 级联动作
on update 级联动作
create table slave (stu_id int  ,name char(32),money decimal(6,2),  foreign key (stu_id) references master(id)  on delete cascade on update cascade);
使用规则：
1、主表、从表字段数据类型要一致
2、主表被参考字段 ：KEY的一种，一般为主键

删除外键：alter table 表名 drop foreign key 外键名;
查询外键名 ：show create table 表名;

• 级联动作

cascade
​数据级联删除、更新(参考字段)
restrict(默认)
​从表有相关联记录,不允许主表操作
set null
​主表删除、更新,从表相关联记录字段值为NULL

• 已有表添加外键

alter table 表名 add foreign key(参考字段) references 主表(被参考字段) on delete 级联动作 on update 级联动作

嵌套查询（子查询）

定义：把内层的查询结果作为外层的查询条件
语法格式：

select ... from 表名 where 条件(select ....);

示例：

1、把攻击值小于平均攻击值的英雄名字和攻击值显示出来
  select name,attack from sanguo where attack<(select avg(attack)from sanguo)
2、找出每个国家攻击力最高的英雄的名字和攻击值(子查询)
 select name,attack from sanguo where (country,attack)in (select country,max(attack)from
  sanguo group by country); 

连接查询

- 内连接（结果同多表查询）
select 字段名 from  表1 inner join 表2 on 条件 inner join 表3 on 条件;
eg1 : 显示省市详细信息
select province.pname,city.cname from province inner join city on province.pid=city.cp_id;  
eg2 : 显示 省 市 县 详细信息
  select province.pname,city.cname,county.coname from province inner join city on province.pid=city.cp_id inner join county on city.cid=county.copid;

数据导入

source 文件名.sql

作用

把文件系统的内容导入到数据库中
语法（方式一）

load data infile “文件名”
into table 表名
fields terminated by “分隔符”
lines terminated by “\n”
示例
scoretable.csv文件导入到数据库db2的表

1、将scoretable.csv放到数据库搜索路径中
   mysql>show variables like 'secure_file_priv';
         /var/lib/mysql-files/
   Linux: sudo cp /home/tarena/scoreTable.csv /var/lib/mysql-files/
2、在数据库中创建对应的表
  create table scoretab(
  rank int,
  name varchar(20),
  score float(5,2),
  phone char(11),
  class char(7)
  )charset=utf8;
3、执行数据导入语句
load data infile '/var/lib/mysql-files/scoreTable.csv'
into table scoretab
fields terminated by ','
lines terminated by '\n'

语法（方式二）

source 文件名.sql

数据导出

作用

将数据库中表的记录保存到系统文件里

语法格式

select … from 表名
into outfile “文件名”

fields terminated by “分隔符”
lines terminated by “分隔符”;

注意
1、导出的内容由SQL查询语句决定
2、执行导出命令时路径必须指定在对应的数据库目录下

表的复制（备份）

1、表能根据实际需求复制数据

2、复制表时不会把KEY属性复制过来
create table 表名 select 查询命令;

锁（自动加锁和释放锁）

全程重点，理论和锁分类及特点

目的

解决客户端并发访问的冲突问题

锁类型分类

读锁(共享锁)：select 加读锁之后别人不能更改表记录,但可以进行查询
写锁(互斥锁、排他锁)：加写锁之后别人不能查、不能改

锁粒度分类

表级锁 ：myisam
行级锁 ：innodb

添加授权用户

1、用root用户登录mysql
   mysql -uroot -p123456
2、授权
   grant 权限列表 on 库.表 to "用户名"@"%" identified by "密码" with grant option;
3、刷新权限（不刷新不生效）
   flush privileges;

权限列表

all privileges 、select 、insert ... ... 
库.表 ： *.* 代表所有库的所有表

示例

1、添加授权用户work,密码123,对所有库的所有表有所有权限
  mysql>grant all privileges on *.* to 'work'@'%' identified by '123' with grant option;
  mysql>flush privileges;
  
  mysql>grant all privileges on db2.* to 'work01'@'%' identified by '123' with grant option;
  mysql>flush privileges;
2、添加用户duty,对db2库中所有表有所有权限

事务和事务回滚

1、开启事务
   mysql>begin; # 方法1
   mysql>start transaction; # 方法2
2、开始执行事务中的1条或者n条SQL命令
3、终止事务
   mysql>commit; # 事务中SQL命令都执行成功,提交到数据库,结束!
   mysql>rollback; # 有SQL命令执行失败,回滚到初始状态,结束!

事务四大特性（ACID）

• 1、原子性（atomicity）

一个事务必须视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作

• 2、一致性（consistency）

数据库总是从一个一致性的状态转换到另一个一致性的状态

• 3、隔离性（isolation）

一个事务所做的修改在最终提交以前，对其他事务是不可见的

• 4、持久性（durability）

一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失

注意

1、事务只针对于表记录操作(增删改)有效,对于库和表的操作无效
2、事务一旦提交结束，对数据库中数据的更改是永久性的

pymysql使用流程

1. 建立数据库连接(db = pymysql.connect(...))

2. 创建游标对象(c = db.cursor())

3. 游标方法: c.execute("insert ....")

4. 提交到数据库 : db.commit()

5. 关闭游标对象 :c.close()

6. 断开数据库连接 :db.close()