00-MySQL 基础操作

MySQL 基础操作

前言

• 记录 MySQL 的基本 CRUD 操作示例，文档提供最基础的示例及比较重要的注意事项。详细的操作样例建议需要时查阅官方文档：https://dev.mysql.com/doc/refman/5.7/en/。

操作数据库

• 创建数据库： CREATE DATABASE {db_name} CHARSET utf8mb4 。详细语法可参考：https://dev.mysql.com/doc/refman/5.7/en/create-database.html

• 常用命令：
  • create database $dbname default character set utf8 collate utf8_general_ci;
  • create database $dbname default character set utf8mb4 collate utf8mb4_unicode_ci;

• 删除数据库： DROP DATABASE {db_name} 。详细语法可参考：https://dev.mysql.com/doc/refman/5.7/en/drop-database.html
  

  删除数据库请多次确认，建议通过 mycli 等具备二次确认能力的客户端进行删除等危险操作。

• 数据库修改：通常并不建议直接对 database 进行变更（ALTER DATABASE），如果有需要可以联系DBA

• 查看 MySQL 中有哪些库： SHOW DATABASES;

  除非拥有全局的 SHOW DATABASES 权限，否则你只能看到相应的你具备权限的库。不要简单的以 SHOW DATABASES 是否有结果来判断该 MySQL 实例是否在线使用

• 查看数据库创建语句： SHOW CREATE DATABASES db_name;

操作表

• 创建表：Create 的详细语法参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/create-table.html

  • 需要重点关注的地方：必须给每个表创建主键（自增列或者有业务意义的具体属性），非必需的情况下统一使用 InnoDB 引擎。

  • CREATE TABLE IF NOT EXISTS t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(32)) ENGINE=innodb

• 删除表：DROP TABLE T1 IF EXISTS;

  • Drop 需要删除数据文件，操作大表时（>20GB 或超过 1000 万行数据）要注意对 IO 系统的影响。
  • Drop 操作包含一个隐式提交事务的操作，不要在事务中执行这个操作。

• 修改表：表的所有列，表自身的各类属性都可以使用 Alter 语句：https://dev.mysql.com/doc/refman/5.7/en/alter-table.html进行修改。

  • 示例： ALTER TABLE t1 ADD COLUMN age int unsigned;
  • DDL 语句多多少少都会对业务产生影响，具体的内容参考操作手册中有关 DDL 的内容。

• 查看表：查看表的信息，一般使用如下几种方式。

  • SHOW CREATE TABLE t1 ：查看建表语句。
  • DESC t1 ：查看表结构，索引等信息。
  • SHOW TABLES ：查看当前库中包含哪些表。
• 操作表的语句，属于 DDL (Data definition Languages)，它们都会触发一个隐式提交事务的操作，因此不要在一个事务中嵌套该类操作，如下示例：

BEGIN; -- 显式开启一个事务
UPDATE `testdb`.`user` SET age=30 WHERE name='liming';
ALTER TABLE `testdb`.`user` add column location varchar(20) default 'UNKNOWN'); -- 被这个 ALTER 语句隐式提交
ROLLBACK;  -- 回滚无效，此时实际上上述更新操作已经生效！

操作数据

• 写入数据：一般使用 Insert 语句，更复杂的、多样化的使用方法参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/insert.html，此处仅给出最基本的使用示例。
  • 单行写入： INSERT INTO t1(id,name) VALUES(1,'aaa')
  • 多行写入： INSERT INTO t1(id,name) VALUES(1,'aaa'), (2,'bbb')
• 删除数据：一般使用 Delete 语句，删除所有数据的时候一般用 Truncate 语句。
  • DELETE FROM t1 WHERE id = 1 ：Delete 语句攒删除数据的时候推荐使用主键，或者是有索引的列作为条件。注意：DELETE 语句不会释放磁盘空间，且大批量删除一般比较慢。
  • TRUNCATE t1 ：Delete 语句删除整张表的数据时一般会非常慢，所以使用 Truncate 会快很多。注意：Truncate 在 MySQL 中被当做 DDL 进行处理，会隐式提交当前的事务，并会短暂的对数据读写产生影响，一般在业务低峰期操作。
• 修改数据：一般使用 Update 语句，更复杂的、多样化的使用方法参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/update.html，此处仅给出最基本的使用示例。
  • UPDATE t1 SET NAME = 'ccc' WHERE id=1 ：一定要加上 where 条件，如果一定要做全表更新，要注意剩余的磁盘空间，千万级表的全表更新可能会产生一个超过 200GB 的 binlog 文件。
• 查询数据：一般使用 Select 语句，更复杂的、多样化的使用方法参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/select.html，此处仅给出最基本的使用示例。
  • SELECT * FROM t1 WHERE id = 1 ：查看 t1 里符合 WHERE 条件的数据。
  • 如何判断 SELECT 语句的好坏，以及如何优化 Select 语句请参考操作手册中索引相关的内容。

修改参数

• 这个功能需要 SUPER 权限，在不了解参数意义的情况下，尽量不要随意修改参数。
• 一般会使用 SET 命令来修改 MySQL 的参数，默认修改的是当前连接的参数（显式修改当前连接的参数需要使用 SESSION 关键字），不会影响全局设置。如果要对全局参数进行修改，那么需要使用 SET GLOBAL {variable_name} = {value}; 的方式进行修改。
• 常见的参数调整例如修改最大连接数，可以使用 SET GLOBAL max_connection = {value} 来调整，更详细的参数内容参考 官方文档：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

安装mysql连接器

• python连接并操作mysql数据库，需要安装mysql连接器
• 插件手册：https://pypi.org/project/mysql-connector-python/

pip install mysql-connector-python
import mysql.connector
#连接数据库
cnx = mysql.connector.connect(user='root',password='password',host='192.168.47.13',database='mycms')
#创建游标对象cursor
cursor = cnx.cursor()
#通过cursor对象执行SQL语句，比如
cursor.execute(),执行SQL
cursor.fetchall(),执行查询