MySQL基本操作

基本配置

• 登陆
  mysql -uroot -p(可跳过登录密码)
  mysql -uroot -proot
• 获取版本号
  mysql -v
  mysql --version
• 登陆同时打开指定数据库
  mysql -uroot -p -D db_name(数据库名称，下同)
• 退出
  exit
  quit
• 获取登陆者
  SELECT USER()
• 获取MySQL版本信息
  SELECT VERSION()
• 获取当前时间
  SELECT NOW()
• 获取当前打开的数据库
  SELECT DATABASE()

数据库基本操作

• 创建数据库
  CREATE DATABASE db_name
  CREATE SCHEMA db_name
• 如果不存在就创建
  CREATE DATABASE IF NOT EXISTS db_name
• 创建的同时指定编码格式
  CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset ([]包含的语句可省略，charset用GBK，UTF8等编码格式替代，下同)
• 查看当前服务器下所有数据库
  SHOW DATABASES
  SHOW SCHEMAS
• 查看指定数据库的详细信息
  SHOW CREATE DATABASE db_name
• 修改指定数据库的编码格式
  ALTER DATABASE db_name [DEFAULT] CHARACTER SET [=] charset
• 打开指定数据库
  USE db_name
• 获取当前打开的数据库
  SELECT DATABASE()
  SELECT SCHEMA()
• 删除指定数据库
  DROP DATABASE db_name
• 指定数据库如果存在就删除
  DROP DATABASE IF EXISTS db_name

数据表基本操作

• 创建数据表
  CREATE TABLE [IF NOT EXISTS] tbl_name(字段名称 字段类型 [完整性约束条件]，字段名称 字段类型 [完整性约束条件]，…)ENGINE=存储引擎 CHARSET=编码格式
  字段类型和完整性约束条件若不清楚可参考文字末尾的字段类型和完整性约束条件。
  ENGINE和CHARSET若无明确要求可省略。
• 查看当前数据库下已有数据表
  SHOW TABLES
  SHOW [FULL] TABLES [{FROM|IN} db_name] [LIKE ‘pattern’ | WHERE expr]
• 查看数据表详细信息
  SHOW CREATE TABLE tbl_name(数据表名称，下同)
• 查看表结构
  DESC tbl_name
  DESCRIBE tbl_name
  SHOW COLUMNS FROM tbl_name
• 删除表结构
  DROP TABLE[IF EXISTS] tbl_name
• 添加字段
  ALTER TABLE tbl_name ADD 字段名称 字段类型 [完整性约束条件] [FIRST|AFTER字段名称]
• 删除字段
  ALTER TABLE tbl_name DROP 字段名称
• 添加默认值
  ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值
• 删除默认值
  ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT
• 修改字段类型
  ALTER TABLE tbl_name MODIFY 字段名称 字段类型[FIRST|AFTER字段名称]
• 修改字段名称、字段类型、字段属性
  ALTER TABLE tbl_name CHANGE 原字段名称 新字段名称 字段类型 字段属性 [FIRST | AFTER 字段名称]
• 添加主键
  ALTER TABLE tbl_name ADD PRIMARY KEY(字段名称)
• 删除主键
  ALTER TABLE tbl_name DROP PRIMARY KEY
• 添加唯一
  ALTER TABLE tbl_name ADD UNIQUE KEY|INDEX [index_name] (字段名称)
• 删除唯一
  ALTER TABLE tbl_name DROP index_name
• 修改数据表名称
  ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
  RENAME TABLE tbl_name TO new_tbl_name
• 修改AUTO_INCREMENT的值
  ALTER TABLE tbl_name AUTO_INCREMENT=值

数据基本操作

添加记录

• 不指定字段名称依次赋值
  INSERT tbl_name VALUE(value…)
• 指定字段名称赋值
  INSERT tbl_name (字段名称) VALUES(value…)
• 以set形式赋值
  INSERT tbl_name SET 字段名称=值
• 以select形式赋值
  INSERT tbl_name[(字段名称…)] SELECT 字段名称,… FROM tbl_name [WHERE 条件]
• 一次添加多条记录
  INSERT tbl_name[(字段名称,…)] VALUES(值,…),(值,…),(值,…)

修改记录

• 修改表中所有记录
  UPDATE tbl_name SET 字段名称=值,字段名称=值
• 修改表中部分记录
  UPDATE tbl_name SET 字段名称=值,字段名称=值 [WHERE 条件]

删除记录

• 删除所有记录
  DELETE FROM tbl_name
• 删除指定记录
  DELETE FROM tbl_name [WHERE 条件]
• 清空数据表
  TRUNCATE [TABLE] tbl_name

查询记录

• 查询表中所有记录
  SELECT * FROM tbl_name
• 查询指定记录
  SELECT 字段名称,… FROM tbl_name
• 不进入数据库进行记录查询
  SELECT 字段名称,… FROM db_name.tbl_name
• 给字段起别名
  SELECT 字段名称 [AS] 别名,… FROM db_name.tbl_name
• 给数据库起别名
  SELECT 字段名称,… FROM tbl_name [AS] 别名
• 不进入数据表进行指定记录查询
  SELECT tbl_name.col_name,… FROM tbl_name
• 等于
  SELECT 字段名称,… FROM tbl_name WHERE col_name= 值
• 不等
  SELECT 字段名称,… FROM tbl_name WHERE col_name <>|!= 值
• 大于
  SELECT 字段名称,… FROM tbl_name WHERE col_name > 值
• 小于
  SELECT 字段名称,… FROM tbl_name WHERE col_name < 值
• null查询
  SELECT 字段名称,… FROM tbl_name WHERE col_name IS [NOT] NULL
• 指定范围
  SELECT 字段名称,… FROM tbl_name WHERE col_name [NOT] BETWEEN … AND …
• 指定集合
  SELECT 字段名称,… FROM tbl_name WHERE col_name [NOT] IN(值,…)
• 逻辑与
  SELECT 字段名称,… FROM tbl_name WHERE (… AND …)
• 逻辑或
  SELECT 字段名称,… FROM tbl_name WHERE (… OR …)
• 指定匹配
  SELECT 字段名称,… FROM tbl_name WHERE col_name LIKE pattern
• 任意长度匹配
  SELECT 字段名称,… FROM tbl_name WHERE col_name LIKE ‘…%…’
• 任意字符匹配
  SELECT 字段名称,… FROM tbl_name WHERE col_name LIKE ‘…_…’
• 分组
  SELECT 字段名称,… FROM tbl_name WHERE 筛选条件 GROUP BY col_name
• 排序
  SELECT 字段名称,… FROM tbl_name WHERE 筛选条件 ORDER BY 字段名称 ASC|DESC(升序|降序)
• 限制显示条数
  SELECT 字段名称,… FROM tbl_name LIMIT offset(记录偏移量或记录开始条数+1),row_count(记录显示条数)
• 多表内联查询
  SELECT 字段名称,… FROM tbl_name1 INNER JOIN tbl_name2 ON 连接条件
• 多表左外连接
  SELECT 字段名称,… FROM tbl_name1 LEFT [OUTER] JOIN tbl_name2 ON 条件
• 多表右外连接
  SELECT 字段名称,… FROM tbl_name1 RIGHT [OUTER] JOIN tbl_name2 ON 条件
• 子查询
  SELECT 字段名称 FROM tbl_name WHERE col_name=(SELECT col_name FROM tbl_name)
• 联合查询-简单合并
  SELECT 字段名称,… FROM tbl_name1 UNION ALL SELECT 字段名称… FROM tbl_name2
• 联合查询-去重
  SELECT 字段名称,… FROM tbl_name1 UNION SELECT 字段名称… FROM tbl_name2

外键约束

• 建表指定外键约束
  CREATE TABLE [IF NOT EXISTS] tbl_name(字段名称 字段类型 [完整性约束条件]，字段名称 字段类型 [完整性约束条件]，…) [CONSTRAINT 外键名称 ]FOREIGN KEY(字段名称) REFERENCES 主表(字段名称)
• 动态添加外键
  ALTER TABLE tbl_name ADD [CONSTRAINT 外键名称] FOREIGN KEY(外键字段，下同) REFERENCES 主表(主键字段)
• 动态删除外键
  ALTER TABLE tbl_name DROP FOREIGN KEY fk_name
• 建表时级联删除（父表删除或更新，子表也跟着删除或更新）
  CREATE TABLE [IF NOT EXISTS] tbl_name(字段名称 字段类型 [完整性约束条件]，字段名称 字段类型 [完整性约束条件]，…) [CONSTRAINT 外键名称 ]FOREIGN KEY(字段名称) REFERENCES 主表(字段名称) ON DELETE CASCADE
• 动态级联删除
  ALTER TABLE tbl_name ADD [CONSTRAINT 外键名称] FOREIGN KEY(外键字段，下同) REFERENCES 主表(主键字段) ON DELETE CASCADE

常用函数

• 进一取整
  CEIL()
• 舍掉小数部分
  FLOOR()
• 四舍五入
  ROUND()
• 截取小数点后几位
  TRUNCATE()
• 取余
  MOD()
• 取绝对值
  ABS()
• 幂运算
  POWER()
• 圆周率
  PI()
• 随机数
  RAND()或者RAND(X)
• 得到数字符号
  SIGN(X)
• 计算e的x次方
  EXP(X)
• 平均值
  AVG()
• 计数
  COUNT()
• 第一条记录
  FIRST()
• 最后一条记录
  LAST()
• 最大值
  MAX()
• 最小值
  MIN()
• 总数
  SUM()
• 转大写
  UCASE()
• 转小写
  LCASE()
• 提取字符
  MID()
• 计算长度
  LEN()
• 当前系统的日期和时间
  NOW()
• 格式化
  FORMAT()

字段类型

• 整数型

数据类型	存储范围	字节
TINYINT	有符号：-27 ~ 27-1，无符号：0 ~ 28 -1	1
SMALLINT	有符号：-215 ~ 215-1，无符号：0 ~ 216 -1	2
MEDIUMINT	有符号：-223 ~ 223-1，无符号：0 ~ 224 -1	3
INT	有符号：-231 ~ 231-1，无符号：0 ~ 232 -1	4
BIGINT	有符号：-263 ~ 263-1，无符号：0 ~ 264 -1	8
BOOL,BOOLEAN	0为false，1为true，相当于TINYINT(1)	1

• 浮点型

数据类型	存储范围	字节
FLOAT[(M,D)]	-3.40E+38到-1.17E-38，0，1.175E-38到3.40E+38	4
DOUBLE[(M,D)]	-1.79E+308到-2.22E-308，0，2.22E-308到1.79E+308	8
DECIMAL[(M,D)]	内部已字符串形式存储数值，和DOUBLE一样	M+2

M是数字总位数，D是小数点后面的位数。若M和D被省略，则根据数据硬件允许的范围来保存。单精度浮点型大约精确到小数点后7位。

• 字符型

数据类型	存储范围
CHAR(M)	M个字节，0<=M<=255
VARCHAR(M)	L+1个字节，L<=M,0<=M<=65535
TINYTEXT	L+1个字节，L<28
TEXT	L+2个字节，L<216
MEDIUMTEXT	L+3个字节，L<224
LONGTEXT	L+4个字节，L<232
ENUM(‘value1’,‘value2’,…)	1或2个字节，取决于枚举值的个数（最多65535）
SET(‘value1’,‘value2’,…)	1，2，3，4或8个字节，取决于set成员数量（最多64）

1. CHAR长度固定，存储数据后若没有达到指定长度，则会用空格填充，检索的时候会去掉空格；VARCHAR在保存的时候则不进行填充，尾部的空格会留下。
2. CHAR效率高于VARCHAR，占用空间也高于VARCHAR，CHAR是用空间换时间，VARCHAR是用时间换空间。
3. TEXT在检索的时候不存在大小写转换，也不能有默认值。

• 时间类型

数据类型	存储范围	字节
TIME	-838:59:59 ~ 838:59:59	3
DATE	1000-01-01 ~ 9999-12-31	3
DATETIME	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8
TIMESTAMP	1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:06	4
YEAR	1901 ~ 2155	1

完整型约束条件

名称	约束
UNSIGNED	无符号，没有负数，从0开始
ZEROFILL	零填充，当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED
NOT NULL	非空约束，也就是插入值的时候这个字段必须要给值,值不能为空
DEFAULT	默认值，如果插入记录的时候没有给字段赋值，则使用默认值
PRIMARY KEY	主键，标识记录的唯一性，值不能重复，一个表只能有一个主键，自动禁止为空
AUTO_INCREMENT	自动增长，只能用于数值列，而且配合索引使用,默认起始值从1开始，每次增长1
UNIQUE KEY	唯一性，一个表中可以有多个字段是唯一索引，同样的值不能重复，但是NULL值除外
FOREIGN KEY	外键约束

备注

• 常用MySQL的关键字需要大写，库名、表名、字段名称等使用小写
• SQL语句支持折行操作，拆分的时候不能把完整单词拆开
• 数据库名称、表名称、字段名称不要使用MySQL的保留字，如果必须要使用，需要用反引号``将其括起来
• 每个数据表中至少有一列，行可以有零行一行或者多行组成
• 表名要求唯一，不要包含特殊字符，最好含义明确
• 存储引擎分MyISAM和InnoDB，InnoDB为默认
• 子表的外键字段和主表的主键字段类型要相似；如果是数值型要求一致，并且无符号也要一致；如果是字符型，要求类型一致，长度可以不同
• 如果外键字段没有创建索引，MySQL会自动帮我们添加索引
• 子表的外键关联的必须是父表的主键
• DELETE 清空数据表的时候不会重置AUTO_INCREMENT的值，可以通过ALTER 语句将其重置为1
• SQL关键字文中尚未列举完全，还有遗漏；SQL语句丰富多样，可自由组合，使用视实际情况而定