MySQL基本操作 - downtiser/python-one GitHub Wiki

mysql的一些基本命令:(操作命令末尾要加分号)

• mysql -uroot -p 管理员登录
• create database test charset utf8; 创建数据库,并指定字符编码类型
• show databases; 显示有哪些数据库
• use mysql; 选择一个数据库
• ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码'; 修改root密码
• show tables; 显示数据库中有哪些表
• desc 表; 显示表的结构
• select * from 表; 显示表中数据 末尾加上\G可以使数据以列的形式展示，增加可读性,注意此时就不用加分号
• select User from user; 查看当前有哪些用户
• create user 'downtiser'@'%' identified by '123456'; 创建一个用户并指定密码和能够登录的主机
• grant all on test.* to 'downtiser'@'%' with grant option; 给指定用户授权，其中all 代表拥有SELECT, INSERT, UPDATE, DELETE, CREATE, DROP 所有权限, to 后面指定用户，@ 后指定登录主机,'%' 代表所有主机, identified by 后面是用户密码
• show grant for downtiser; 显示某个用户的权限
• drop database test; 删除一个数据库
• MySQL数据类型
• 创建一个表:要先指定表名，再指定每个字段的数据类型和是否为空，id字段还设置了自增属性，同时最后将id字段设置为主键 *

  mysql> create table student(  
  -> id int auto_increment,  
  -> name char(32) not null,  #指定字符串最大长度
  -> age tinyint not null,  
  -> register_date date not null,  
  -> primary key (id));
  

• 往指定表中插入信息：insert into student (name,age,register_date) values("Noob", "19", "2018-8-12");

select的几种用法

• 查询表中由limit指定个数，offset指定起始位置的一定量的数据，offset后的数是指查询从这个数往后一位为起始数据:
  • select * from student limit 2 offset 1;
• where子句：用于过滤出符合某些条件的数据：
  • 操作符表：
  • select * from student where age<22 or id=3; 查询表中age字段数据小于22或者id为3的数据
  • select * from student where register_date like "2018-%-13"; 用like实现模糊查找，查找register_date为2018年某月13号的数据;

update 查询: 查询符合指定条件的数据并将找到的数据进行修改更新:

• update student set register_date="2018-7-13", age=30 where id>=4: set指定要修改的字段和值，where匹配要更改的数据

delete 删除：删除符合指定条件的数据:

• delete from student where id=4;

排序 order by：末尾加asc为升序，加desc为降序，默认为升序

• select * from student order by register_date desc;

group by:分组

• select name,count(*) from student group by register_time; 此时会报错ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.student.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by,
  查询了一下官方说明文档，意思是说mysql默认启动了一个模式only_full_group_by，导致不能使用不匹配'group by'子句中列的非聚合的列作为select子句的参数名，非聚合列即为主键或为内容都是独特的非空列，非聚合列会导致这么一个问题：当用group by子句筛选出一组列时，有些列内容相同，有些列内容不同，就无法用在屏幕上以一格数据来表示. 为解决这个问题，一是可以修改sql_mode,将sql_mode中的only_full_group_by去除，但可能会导致一些问题。第二种是将非聚合列用any_value()函数包括起来，这样在显示时会只显示改组中第一行数据的指定非聚合列的内容，这样会丢失一些数据，所以使用select和group by时最好两个子句中的指定列相对应。
• 上述命令修改如下:select any_value(name),count(*) from student group by register_date;,结果如下: *

+-----------------+----------+
| any_value(name) | count(*) |
+-----------------+----------+
| Noob            |        3 |
| Idiot           |        2 |
| Nameless        |        2 |
+-----------------+----------+
3 rows in set (0.00 sec))

• 而原表数据如下：可见select中使用了和group by子句中不对应的非聚合列，MySQL在分组后就会在非聚合列一栏只显示按主键顺序匹配到的该组的第一行数据的对应内容，造成一部分数据的显示不全，所以最好不要这样用。

+----+----------+-----+---------------+
| id | name     | age | register_date |
+----+----------+-----+---------------+
|  1 | Noob     |  19 | 2018-06-13    |
|  2 | Idiot    |  19 | 2018-08-13    |
|  3 | Loser    |  28 | 2018-08-13    |
|  4 | Nameless |  30 | 2018-07-13    |
|  5 | Lupus    |  30 | 2018-07-13    |
|  6 | Pig      |  24 | 2018-06-13    |
|  7 | lol      |  34 | 2018-06-13    |
+----+----------+-----+---------------+

• 标准的应该这样写：select register_date,count(*) from student group by register_date;结果如下:

+---------------+----------+
| register_date | count(*) |
+---------------+----------+
| 2018-06-13    |        3 |
| 2018-08-13    |        2 |
| 2018-07-13    |        2 |
+---------------+----------+

• 结果中的count(*)字段名意义不清，可以给这个字段用as重命名:select register_date,count(*) as stu_amount from student group by register_date;: *

+---------------+------------+
| register_date | stu_amount |
+---------------+------------+
| 2018-06-13    |          3 |
| 2018-08-13    |          2 |
| 2018-07-13    |          2 |
+---------------+------------+

• 在使用sum聚合函数时，还可以在group by之后加上with rollup统计整张表指定内容的和：select age,sum(age) as the_whole_age from student group by age with rollup;: *

+-----+---------------+
| age | the_whole_age |
+-----+---------------+
|  19 |            38 |
|  24 |            24 |
|  28 |            28 |
|  30 |            60 |
|  34 |            34 |
| NULL |           184 |
+-----+---------------+

• 可以用coalesce()函数替换掉表中的NULL: select coalesce(age, "Total") as age_group,sum(age) as the_whole_age from student group by age with rollup;

+-----------+---------------+
| age_group | the_whole_age |
+-----------+---------------+
| 19        |            38 |
| 24        |            24 |
| 28        |            28 |
| 30        |            60 |
| 34        |            34 |
| Total     |           184 |
+-----------+---------------+

• 几种聚合函数:
  • COUNT 计算表中的记录（行数）
  • SUM 计算表中数值列的数据合计值
  • AVG 计算表中数值列的数据平均值
  • MAX 求出表中任意列中数据的最大值
  • MIN 求出表中任意列中数据的最小值
• ALTER命令:修改表名或修改字段
  • 增加字段：alter table 表名 add 字段名 enum("m","f") not null 增加一个枚举型非空字段
  • 删除字段：alter table 表名 drop 字段名 删除指定字段
  • 修改字段:
    • modify修改字段数据类型:alter table student modify sex enum("m","f","mf") not null default "F" 如果设置非空字段，一般要在最后通过default设置默认内容.
    • change修改字段名及字段类型:alter table 表名 change 旧字段名 新字段名 字段类型:``

外键：用于为不同表建立关联

* 创建一个表，并关联外键:  

  create table record(
     id int primary key auto_increment,
     day int not null,
     statue char(32) not null,
     stu_id int not null,
     key fk_key (stu_id), #设置外键名为fk_key,并将其与stu_id字段关联
     constraint fk_key foreign key (stu_id) references student (id)  #foreign key设置关联外键的字段，references 后面跟被关联的表和被应用为外键的字段
     );

* 插入数据，并指定外键:`insert into record (day,statue,stu_id) values(1,"Yes",1);` 若引用的外键不存在，会抛出错误，如果把被引用表中的某个被引用数据删除，也会出错.
* MySQL中NULL值的处理：NULL不能用常规比较符作比较，有专门的方法来处理
    * IS NULL:当列值是NULL,返回True
    * IS NOT NULL:当列的值不是NULL,返回True
    * <=>:当比较的两个值为NULL时返回True,NULL<=>NULL 返回True。

MySQL连接：几个表可以互相查询数据

• inner join:获取两个表中字段匹配关系的记录。现有表A，表B如下:
• A:

+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+

• B:

+---+
| b |
+---+
| 3 |
| 4 |
| 5 |
| 6 |
+---+

• 取这两个表指定列的交集:
  • select * from A inner join B on A.a = B.b; A.a和B.b指定A的a字段和B的b字段.结果如下:

+---+---+
| a | b |
+---+---+
| 3 | 3 |
| 4 | 4 |
+---+---+

• left join:获取左表所有记录，如果右表没有对应匹配的记录则值为NULL。
• select * from A left join B on A.a=B.b;

+---+------+
| a | b    |
+---+------+
| 3 |    3 |
| 4 |    4 |
| 1 | NULL |
| 2 | NULL |
+---+------+

• right join:和left join相反，获取右表所有记录，如果左表没有对应匹配的记录则值为NULL。
• select * from A right join B on A.a=B.b;

+------+---+
| a    | b |
+------+---+
|    3 | 3 |
|    4 | 4 |
| NULL | 5 |
| NULL | 6 |
+------+---+

• full join: 获取两张表的所有记录，互相匹配不到的值为NULL，相当于并集。MySQL中不直接支持full join，但可以用 union实现
• select * from A left join B on A.a=B.b union select * from A right join B on A.a=B.b;

+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    1 | NULL |
|    2 | NULL |
| NULL |    5 |
| NULL |    6 |
+------+------+

MySQL事务：用于处理操作量大，复杂度高的数据。如果对某一个表中的某一个数据进行处理会涉及到对其他很多相关数据的修改，这些操作就构成一个事务。MySQL中只有使用Innodb引擎的数据库才能启用事务

• 事务需满足的四个条件：
  1. 原子性：一组事务要么成功，要么撤回
  2. 稳定性：有非法数据（外键约束等），事务撤回
  3. 隔离性：事务独立运行，一个事务处理后的结果影响了其他事务，那么其他事务会撤回。
  4. 可靠性：如果软硬件崩溃，InnoDB数据表驱动会利用日志文件重构修改。
• 操作事务：
  1. begin;开启一个事务
  2. 进行指定的操作，如插入数据，修改数据等
  3. rollback;如果操作有误，就进行回滚，相当于取消事务
  4. commit;确认操作无误后提交事务

索引：通过对某一个字段建立索引，通过某种算法，使得在查找大量数据时检索的速度大大增加，但另一方面会降低表的更新速度。索引分为单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

普通索引：

• show index from student;查看一张表中的索引
• 创建（删除）索引：
  • create index 索引名 on 表名(字段名(索引长度)); 例如create index name_index on student(name(32));索引长度最好不要超过字段值的长度。
  • drop index 索引名 on 表名;删除索引
• 修改表结构添加（删除）索引：
  • alter table 表名 add index 索引名(字段名(索引长度));添加索引
  • alter table 表名 drop index 索引名;删除索引
• 创建表时添加索引：

CREATE TABLE mytable( 
  ID INT NOT NULL,  
  username VARCHAR(16) NOT NULL, 
  INDEX [indexName] (username(length)) 
  );  

唯一索引：索引列的值必须唯一

* 创建索引：`create unique index 索引名 on 表名(字段名(索引长度));`
* 删除索引: `drop unique index 索引名 on 表名(字段名(索引长度));`
* alter添加索引：`alter table 表名 add unique 索引名(字段名(索引长度));`
* alter删除：`alter table 表名 drop unique 索引名;`

python操作MySQL:

#Downtiser
import pymysql
#建立链接
conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='gu996080',db='test')
# 创建游标
cursor = conn.cursor()

affect_row = cursor.execute("select * from student;") #执行命令并返回受影响的行数
print(affect_row)
print(cursor.fetchone())  #取其中一条数据
print(cursor.fetchmany(2)) #获取指定数量条数据
print(cursor.fetchall()) #取剩下的所有数据

stu_info = [('down',25,"2019-12-15", "Male"),
            ('boom',35,"2017-6-15", "Female")
            ]
# 一次性执行多条命令
#cursor.executemany('insert into student (name, age, register_date,gender) values(%s,%s,%s,%s)',stu_info)

conn.commit() #提交修改，将结果保存到数据库中，其实是默认开启了事务