Mysql 学习笔记

# 特点

# 特征

  • 免费
  • 开源
  • 关系型

# 优点

  • 全平台
  • 多线程
  • 语言支持好
  • 连接方式多样

# 缺点

  • 比NoSQL性能差
  • 比Oracle功能少
  • MyISAM不支持事务
  • MyISAM不支持外键
  • MyISAM不支持行级锁
  • InnoDB不支持全文索引
  • InnoDB必须有主键

# 数据类型

类型空间(byte)范围
BIT(M)(M+7)/8
BOOL, BOOLEAN, TINYINT(M)1
SMALLINT(M)2
MEDIUMINT(M)3
INT(M), INTEGER(M)4
BIGINT(M)8
FLOAT(M, N)4
DOUBLE(M, N)8
DECIMAL(M, N)?
NUMERIC(M, N)?
DATE3[1000-01-01, 9999-12-31]
TIME3[-838:59:59, 838:59:59]
YEAR1[1901, 2155]
DATETIME8[1000-01-01 00:00:00, 9999-12-31 23:59:59]
TIMESTAMP4[1970-01-01 00:00:00, 2038-01-19 03:14:07]
CHAR(M)[0, 2^8 – 1]
VARCHAR(M)[0, 2^16 – 1]
TINYTEXT(M)[0, 2^8 – 1]
TEXT(M)[0, 2^16 – 1]
MEDIUMTEXT(M)[0, 2^24 – 1]
LONGTEXT(M)[0, 2^32 – 1]
TINYBLOB(M)[0, 2^8 – 1]
BLOB(M)[0, 2^16 – 1]
MEDIUMBLOB(M)[0, 2^24 – 1]
LONGBLOB(M)[0, 2^32 – 1]
BINARY(M)[0, 2^8 – 1]
VARBINARY(M)[0, 2^16 – 1]
ENUM?
JSON?

# 命令

# 系统操作

$ mysql [-h <ip>[:<port>]] -u <username> -p; # 连接数据库
$ mysql -u<username> -p <databasename> < <filename>; # 恢复数据库
$ mysqldump -u<username> -p <databasename> > <filename>; # 导出数据库
$ which mysqld; # 配置文件路径

# 库操作

$ CREATE DATABASE [IF NOT EXISTS] <databasename>; # 创建数据库
$ <CREATE...> DEFAULT CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI; # 设定字符集和排序规则
$ DROP DATABASE <databasename>; # 删除数据库
$ USE <databasename>; # 打开数据库
$ SHOW DATABASES; # 展示库列表

# 表操作

$ CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <tablename> ( # 创建表
      ID INT NOT NULL AUTO_INCREMENT,...
  ) [ENGINE = MyISAM DEFAULT CHARSET = UTF8MB4 COLLATE = UTF8MB4_UNICODE_CI AUTO_INCREMENT = 1];
$ DROP TABLE <tablename>; # 删除表
$ SHOW TABLES; # 展示表列表
$ SHOW CREATE TABLE <tablename>; # 查看表结构
$ DESCRIBE <tablename>; # 查看表结构

$ ALTER TABLE <tablename> ADD <field> <datatype>; # 添加字段

$ ALTER TABLE <tablename> ADD <indexname> <datatype> FOREIGN KEY (field) REFERENCES (field); # 添加外键字段
$ ALTER TABLE <tablename> DROP <field>; # 删除字段
$ ALTER TABLE <tablename> MODIFY <field> <datatype>; # 修改字段
$ ALTER TABLE <tablename> CHANGE <field> <field> <datatype>; # 修改字段
$ ALTER TABLE <tablename> ALTER <field> SET DEFAULT <value>; # 修改默认值
$ ALTER TABLE <tablename> RENAME TO <tablename>; # 修改表名
$ ALTER TABLE <tablename> ENGINE = <engine_name>; # 设置引擎
$ ALTER TABLE <tablename> CHARACTER SET <character> DEFAULT COLLATE <orderrule>;

# 索引

$ SHOW INDEX FROM <tablename>; # 展示索引
$ CREATE INDEX <indexname> ON <tablename> (<field>[(length)][ASC|DESC]); # 添加索引
$ CREATE UNIQUE INDEX <indexname> ON <tablename> (<field>[(length)][ASC|DESC]); # 添加唯一索引
$ ALTER TABLE <tablename> ADD INDEX <indexname> (<field>[(length)][ASC|DESC]); # 
$ DROP INDEX <indexname> ON <tablename>;  # 删除索引

# 增删改查

$ INSERT INTO <tablename> (<field>,...) VALUES (<value>,...); # 插入
$ UPDATE <tablename> SET <field> = <value>... [WHERE...]; # 更新
$ DELETE FROM <tablename> [WHERE...]; # 删除
$ SELECT <field>,... FROM <tablename>; # 查询
$ <SELECT...> WHERE <bool>; # 查询WHERE
$ <SELECT...> WHERE <bool> AND <bool>; # 查询AND
$ <SELECT...> WHERE <bool> OR <bool>; # 查询OR
$ <SELECT...> WHERE <field> LIKE <value>; # 查询LIKE
$ <SELECT...> WHERE <field> BETWEEN <value> AND <value>; # 查询BETWEEN
$ <SELECT...> ORDER BY [<field>|FIELD(<value>,...)] [ASC|DESC]; # 排序
$ <SELECT...> GROUP BY <field>; # 分组
$ <SELECT...> UNION [ALL | DISTINCT] [SELECT ...]; # 联合
$ <SELECT...> [INNER | LEFT | RIGHT] JOIN <tablename> ON [tablename.]<field> = [tablename.]<field> # 连接

# JSON

$ SELECT <field>->"$.<key>" <FROM...>; # 查询值
$ SELECT <field>->>"$.<key>" <FROM...>; # 查询值(去字符串化)
$ SELECT JSON_EXTRACT(<field>,'$.<key>') <FROM...>; # 查询值
$ <INSERT...> VALUES(JSON_ARRAY(<item>,...)); # 插入数组
$ <INSERT...> VALUES(JSON_OBJECT(<key>,<value>,...)); # 插入对象
$ <UPDATE...> SET <field> = JSON_ARRAY_APPEND(<field>,'$.<key>',<value>); # 修改数组(尾部插入)
$ <UPDATE...> SET <field> = JSON_ARRAY_INSERT(<field>,'$.<key>',<value>); # 修改数组(选择插入)
$ <UPDATE...> SET <field> = JSON_INSERT(<field>,'$.<key>',<value>); # 修改对象(不覆盖插入)
$ <UPDATE...> SET <field> = JSON_REPLACE(<field>,'$.<key>',<value>); # 修改对象(不新增修改)
$ <UPDATE...> SET <field> = JSON_SET(<field>,'$.<key>',<value>); # 修改对象(覆盖插入)
$ <UPDATE...> SET <field> = JSON_REMOVE(<field>,'$.<key>'); # 修改对象(删除)
$ SELECT JSON_CONTAINS(<field>,<value>,'$.<key>') <FROM...>; # 值存在
$ SELECT JSON_CONTAINS_PATH(<field>,['one'|'all'],'$.<key>') <FROM...>; # 路径存在
$ SELECT JSON_KEYS(<field>,'$.<key>') <FROM...>; # 键值
$ SELECT JSON_SEARCH(<field>,['one'|'all'],<value-like>,'$.<key>') <FROM...>; # 路径
$ SELECT JSON_MERGE_PRESERVE([array|object],[array|object]) <FROM...>; # 合并
$ SELECT JSON_QUOTE([array|object]) <FROM...>; # 字符串化
$ SELECT JSON_UNQUOTE([array|object]) <FROM...>; # 去字符串化
$ SELECT JSON_DEPTH(<field>,'$.<key>') <FROM...>; # 深度
$ SELECT JSON_LENGTH(<field>,'$.<key>') <FROM...>; # 长度
$ SELECT JSON_TYPE(<field>) <FROM...>; # 类型
$ SELECT JSON_VALID(<field>) <FROM...>; # 格式正确

# 事务

$ BEGIN; # 开始
$ ROLLBACK; # 回滚
$ COMMIT; # 提交
$ SAVEPOINT <pointname>; # 创建保存点
$ ROLLBACK TO <pointname>; # 回滚保存点
$ RELEASE SAVEPOINT <pointname>; # 释放保存点
$ SET AUTOCOMMIT = <0 | 1>; # 设置自动提交
$ SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL <READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE>; # 设置隔离级别

# 权限

$ CREATE USER <username>@<ip> IDENTIFIED BY <password>; # 创建用户
$ RENAME USER <username>@<ip>; # 修改用户
$ DROP USER <username>@<ip>; # 删除用户
$ SET PASSWORD FOR <username>@<ip> = <password>; # 修改密码
$ SHOW GRANTS FOR <username>@<ip>; # 查看用户权限
$ GRANT <privilegetype> ON <databasename>.<tablename> TO <username>@<ip>; # 授权用
$ GRANT <privilegetype> ON <databasename>.<tablename> TO <username>@<ip> IDENTIFIED BY <password>; # 创建并授权用户
$ REVOKE <privilegetype> ON <databasename>.<tablename> FROM <username>@<ip>; # 取消授权
$ FLUSH PRIVILEGES; # 刷新权限

# 视图

$ DESCRIBE <viewname>; # 查看视图
$ SHOW CREATE VIEW <viewname>; # 查看视图
$ CREATE VIEW <viewname>[(field...)] AS <SELECT...>; # 创建视图
$ DROP VIEW <viewname>; # 删除视图

# 常用函数

$ SELECT COUNT(*) FROM <tablename>; # 查询条数
$ UPDATE <tablename> SET <field> = NOW(); # 获取时间
$ UPDATE <tablename> SET <field> = MD5(<value>); # 获取MD5加密
$ UPDATE <tablename> SET <field> = FLOOR(RAND() * (100 - 1) + 1); # 获取1~100随机数

# 问题

# 常用函数

SUBSTRING(<string>,<index>,<length>); # 截取

# 字符集区别

字符集字节字符大小写排序
准确性		排序
速度
utf83常用不区分不准确较快
utf8mb44Unicode不区分不准确较快
utf8mb4_bin4Unicode区分准确普通
utf8mb4_general_ci4Unicode不区分不准确较快
utf8mb4_unicode_ci4Unicode不区分准确普通
utf8_general_cs4Unicode区分准确普通

# 配置文件

max_connections=1024 # 连接数量
back_log=500 # 连接挂起数量
interactive_timeout=7200 # 连接扫描间隔
wait_timeout=7200 # 连接断开时间
thread_cache_size=64 # 复用线程数量
table_cache=512 # 打开表数量
record_buffer=16773120 # 扫描缓冲区
sort_buffer=16777208 # 排序缓冲区
key_buffer_size=512M # 索引块的缓冲区
innodb_buffer_pool_size=256M # InnoDB引擎缓冲区
query_cache_size=32M # 查询缓存
tmp_table_size=64M # 临时表大小
[mysqld]
bind-address=0.0.0.0 # 监听IP

# 特殊数据储存类型

  • 经纬度: DECIMAL(10, 7)
  • 手机号: BIGINT NOT NULL

# 信息库information_schema

  • SCHEMATA表: 数据库的信息
  • TABLES表: 数据库的表信息
  • COLUMNS表: 数据库表的列信息
  • STATISTICS表: 数据库表的索引信息
  • USER_PRIVILEGES表: 用户的权限信息
  • SCHEMA_PRIVILEGES表: 权限方案
  • TABLE_PRIVILEGES表: 用户的授权表信息
  • COLUMN_PRIVILEGES表: 用户的授权列信息
  • CHARACTER_SETS表: 字符集的信息
  • COLLATIONS表: 字符集的对照信息
  • COLLATION_CHARACTER_SET_APPLICABILITY表: 字符集的校对信息
  • TABLE_CONSTRAINTS表: 数据库表的约束信息
  • KEY_COLUMN_USAGE表: 数据库表的约束键列信息
  • ROUTINES表: 存储子程序的信息
  • VIEWS表: 视图的信息
  • TRIGGERS表: 触发程序的信息

# 关键词

ADDALLALTER
ANALYZEANDAS
ASCASENSITIVEBEFORE
BETWEENBIGINTBINARY
BLOBBOTHBY
CALLCASCADECASE
CHANGECHARCHARACTER
CHECKCOLLATECOLUMN
CONDITIONCONNECTIONCONSTRAINT
CONTINUECONVERTCREATE
CROSSCURRENT_DATECURRENT_TIME
CURRENT_TIMESTAMPCURRENT_USERCURSOR
DATABASEDATABASESDAY_HOUR
DAY_MICROSECONDDAY_MINUTEDAY_SECOND
DECDECIMALDECLARE
DEFAULTDELAYEDDELETE
DESCDESCRIBEDETERMINISTIC
DISTINCTDISTINCTROWDIV
DOUBLEDROPDUAL
EACHELSEELSEIF
ENCLOSEDESCAPEDEXISTS
EXITEXPLAINFALSE
FETCHFLOATFLOAT4
FLOAT8FORFORCE
FOREIGNFROMFULLTEXT
GOTOGRANTGROUP
HAVINGHIGH_PRIORITYHOUR_MICROSECOND
HOUR_MINUTEHOUR_SECONDIF
IGNOREININDEX
INFILEINNERINOUT
INSENSITIVEINSERTINT
INT1INT2INT3
INT4INT8INTEGER
INTERVALINTOIS
ITERATEJOINKEY
KEYSKILLLABEL
LEADINGLEAVELEFT
LIKELIMITLINEAR
LINESLOADLOCALTIME
LOCALTIMESTAMPLOCKLONG
LONGBLOBLONGTEXTLOOP
LOW_PRIORITYMATCHMEDIUMBLOB
MEDIUMINTMEDIUMTEXTMIDDLEINT
MINUTE_MICROSECONDMINUTE_SECONDMOD
MODIFIESNATURALNOT
NO_WRITE_TO_BINLOGNULLNUMERIC
ONOPTIMIZEOPTION
OPTIONALLYORORDER
OUTOUTEROUTFILE
PRECISIONPRIMARYPROCEDURE
PURGERAID0RANGE
READREADSREAL
REFERENCESREGEXPRELEASE
RENAMEREPEATREPLACE
REQUIRERESTRICTRETURN
REVOKERIGHTRLIKE
SCHEMASCHEMASSECOND_MICROSECOND
SELECTSENSITIVESEPARATOR
SETSHOWSMALLINT
SPATIALSPECIFICSQL
SQLEXCEPTIONSQLSTATESQLWARNING
SQL_BIG_RESULTSQL_CALC_FOUND_ROWSSQL_SMALL_RESULT
SSLSTARTINGSTRAIGHT_JOIN
TABLETERMINATEDTHEN
TINYBLOBTINYINTTINYTEXT
TOTRAILINGTRIGGER
TRUEUNDOUNION
UNIQUEUNLOCKUNSIGNED
UPDATEUSAGEUSE
USINGUTC_DATEUTC_TIME
UTC_TIMESTAMPVALUESVARBINARY
VARCHARVARCHARACTERVARYING
WHENWHEREWHILE
WITHWRITEX509
XORYEAR_MONTHZEROFILL

# 参考

License

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License.