建表命令

  1. create table `student` (
  2.     s_id integer(11) not null primary key comment '学生ID',
  3.     s_name varchar(16) not null collate utf8mb4_unicode_ci comment '学生名字'
  4. ) ENGINE =Innodb default character set utf8mb4 comment '学生表';

表操作

  1. ALTER TABLE tbl_name
  2.     [alter_option [, alter_option] ...] 
  3.     [partition_options]
  5. alter_option: { 
  6.     table_options
  7.         | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
  8.         | ADD [COLUMN] (col_name column_definition,...) 
  9.         | ADD {INDEX | KEY} [index_name] [index_type] (key_part,...) [index_option] ... 
  10.         | ADD {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...) [index_option] ... 
  11.         | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...) [index_option] ...
  12.         | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (key_part,...) [index_option] ...
  13.         | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name,...) reference_definition 
  14.         | ADD CHECK (expr)
  15.         | ALGORITHM [=] {DEFAULT | INPLACE | COPY} 
  17.         | ALTER [COLUMN] col_name { SET DEFAULT {literal | (expr)} | DROP DEFAULT}
  18.         | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]
  19.         | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name] 
  20.         | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  21.         | {DISABLE | ENABLE} KEYS
  22.         | {DISCARD | IMPORT} TABLESPACE
  23.         | DROP [COLUMN] col_name
  24.         | DROP {INDEX | KEY} index_name
  25.         | DROP PRIMARY KEY
  26.         | DROP FOREIGN KEY fk_symbol
  27.         | FORCE
  28.         | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
  29.         | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
  30.         | ORDER BY col_name [, col_name] ...
  31.         | RENAME {INDEX | KEY} old_index_name TO new_index_name | RENAME [TO | AS] new_tbl_name
  32.         | {WITHOUT | WITH} VALIDATION
  33. }
  34. -- 跳过partition_options
  36. key_part:
  37.     col_name [(length)] [ASC | DESC]
  39. table_option: { 
  40.        AUTO_INCREMENT [=] value
  41.     | AVG_ROW_LENGTH [=] value
  42.     | [DEFAULT] CHARACTER SET [=] charset_name
  43.     | CHECKSUM [=] {0 | 1}
  44.     | [DEFAULT] COLLATE [=] collation_name
  45.     | COMMENT [=] 'string'
  46.     | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
  47.     | CONNECTION [=] 'connect_string'
  48.     | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
  49.     | DELAY_KEY_WRITE [=] {0 | 1}
  50.     | ENCRYPTION [=] {'Y' | 'N'}
  51.     | ENGINE [=] engine_name
  52.     | INSERT_METHOD [=] { NO | FIRST | LAST }
  53.     | KEY_BLOCK_SIZE [=] value
  54.     | MAX_ROWS [=] value
  55.     | MIN_ROWS [=] value
  56.     | PACK_KEYS [=] {0 | 1 | DEFAULT}
  57.     | PASSWORD [=] 'string'
  58.     | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT} | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
  59.     | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
  60.     | STATS_SAMPLE_PAGES [=] value
  61.     | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
  62.     | UNION [=] (tbl_name[,tbl_name]...)
  63. }
  1. -- 添加字段
  2. alter table student add s_sex tinyint(1) not null default 0 comment '性别' after s_name;
  3. -- 删除字段
  4. ALTER TABLE student DROP COLUMN s_sex;
  5. -- 修改字段
  6. alter table student modify s_sex integer(3) not null default 1 comment '性别2';
  7. -- 查看索引
  8. show index from `tableName`;
  9. -- 添加索引
  10. alter table student add INDEX s_index_name (s_name) ;
  11. -- 删除索引
  12. alter table student drop index s_index_name;

事务和锁

  1. -- 语句规则
  2. START TRANSACTION
  3.     [transaction_characteristic [, transaction_characteristic] ...]
  5. transaction_characteristic: { 
  6.     WITH CONSISTENT SNAPSHOT| READ WRITE| READ ONLY 
  7. }
  8. -- WITH CONSISTENT SNAPSHOT 仅在REPEATABLE READ下生效
  10. BEGIN [WORK]
  11. COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
  12. ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
  13. SET autocommit = {0 | 1}

START TRANSACTION or BEGIN start a new transaction.

  • DDL语句不能rollback

隐式提交: 非主动执行commit语句，完成事务

  • DDL语句触发隐式提交
  • 修改表的语句
  • 事务语句和锁语句
    • BEGIN
    • LOCK TABLES
    • SET autocommit = 1 (if the value is not already 1)
    • START TRANSACTION
    • UNLOCK TABLES.

      设置事务隔离级别

      ```sql SET [GLOBAL | SESSION] TRANSACTION transaction_characteristic [, transaction_characteristic] …

transaction_characteristic: { ISOLATION LEVEL level| access_mode }

level: { REPEATABLE READ| READ COMMITTED| READ UNCOMMITTED| SERIALIZABLE }

access_mode: { READ WRITE| READ ONLY

}

set transaction isolation level read committed ; set global transaction isolation level read committed ;

  1. <a name="YKPdc"></a>
  2. ## 其他
  3. <a name="pOuXX"></a>
  4. ### 查看变量
  5. ```sql
  6. show variables ;
  7. show variables like '%char%';

查看表结构

  1. show create table business;
  2. CREATE TABLE `business` (
  3.   `business_id` varchar(64) NOT NULL,
  4.   `business_name` varchar(32) NOT NULL,
  5.   `business_desc` varchar(256) NOT NULL DEFAULT '',
  6.   `user_id` varchar(64) NOT NULL,
  7.   `visibility` tinyint(4) NOT NULL DEFAULT '0',
  8.   `add_time` timestamp NOT NULL DEFAULT '2019-12-31 16:00:00',
  9.   PRIMARY KEY (`business_id`),
  10.   UNIQUE KEY `business_business_id_uindex` (`business_id`),
  11.   UNIQUE KEY `business_name_index` (`business_name`)
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='业务'; -- CHARSET 表字符集
  14. -- desc查看表结构
  15. desc business;
  17. -- 查看字段的全部信息
  18. show full columns from business;
  19.  Field                Type                    Collation        Null  Key  Default            Privileges                             Comments
  20. business_id    varchar(64)    utf8_general_ci     NO      PRI        ""         select,insert,update,references       "备注"

查看执行计划

  1. -- DESCRIBE,EXPLAIN 以及 DESC 三个在(MySQL parser)中属于同义词.
  2. -- DESCRIBE 一般用于获取表结构
  3. -- EXPLAIN 获取执行计划，MySQL是如何执行查询的
  4. DESC business;
  5. desc select * from business where business_id = 'xxxx';
  6. explain select * from business where business_id = 'xxxx';

查询计划的输出格式

  1.           ![image.png](https://cdn.nlark.com/yuque/0/2021/png/191132/1630976866178-46a51bb5-6c9a-49fa-8db9-569b8bb1d7e9.png#clientId=u04440e27-3f8c-4&from=paste&height=70&id=u3c9852e0&margin=%5Bobject%20Object%5D&name=image.png&originHeight=106&originWidth=999&originalType=binary&ratio=1&size=23436&status=done&style=none&taskId=u76b18ec9-b6ad-40ac-92b7-6b5b6287551&width=662.5)
  • id、select 标识符
  • select_type、select类型
    • simple (没有使用子查询或者union链接查询)
  • table、表信息
  • type、描述表是如何进行链接的
    • system 表只有一行，特殊的 const join类型
    • const 最多只有一行匹配
      • 出现在主键和唯一索引，他们都只会匹配一条数据
    • eq_ref
    • ref 最左匹配 & 选择的key不是主键和唯一索引， 最好是使用 = 或者是 !=
    • fulltext 使用fulltext索引
    • ref_or_null
    • index_merge
    • unique_subquery
    • index_subquery
    • range
    • index
    • ALL
  • pssible_keys、可能被选择的索引
  • key、真实使用的索引
  • key_len、被选择索引的长度
  • ref、和索引比较的列
  • rows、估计要检查的行数
  • filtered、按表条件过滤的行百分比
  • extra、额外信息 关注一下filesort，temporary table等等信息
    • Using filesort
    • Using temporary
    • Using where
    • Using index
    • Using index condition

官方测试数据下载