MySQL常用语法

MySQL常用语法

导入*.sql文件

先连接数据，切换到想要导入的数据库，再执行 source *.sql文件路径; 回车即可

连接数据库

mysql -uroot -p -P3306 -h127.0.0.1 --prompt "\u@\h \d>"

• -u // 登录用户
• -p // 登录密码，这里要回车后输入
• -P // 端口
• -h // host
• –prompt “\u@\h \d>” // 登录时，修改mysql命令行提示符
  • \u // 当前登录用户
  • @ // 原义字符
  • \h // 当前host
  • \d // 当前数据库名称

命令行状态下，修改提示符

PROMPT \u@\h \d>;

查看字符集相关变量

SHOW VARIABLES [LIKE 'character%'];

修改默认字符集

SET character_set_client=utf8;
SET character_set_connection=utf8;
SET character_set_database=utf8;
SET character_set_results=utf8;
SET character_set_server=utf8;
SET character_set_system=utf8;

显示数据库列表

SHOW DATABASES;

创建数据库

CREATE DATABASE `db1` [CHARACTER SET utf8];

• db1 // 数据库名称
• utf8 // 数据库字符集，可选参数

删除数据库

DROP DATABASE `db1`;

切换数据库

USE `db1`;

显示当前数据库的数据表列表

SHOW TABLES;

创建数据表

CREATE TABLE `t01` (
  `id` INT UNSIGNED AUTO_INCREMENT COMMENT '自增主键',
  `name` VARCHAR(20) COMMENT '姓名',
  `age` TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
  `phone_number` VARCHAR(11) COMMENT '手机号码',
  `stu_no` VARCHAR(20) COMMENT '学号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_phone_number` (`phone_number`)
);

创建唯一索引

CREATE UNIQUE INDEX `uk_stu_no` ON `t01` (`stu_no`);

删除索引

ALTER TABLE `t05` DROP INDEX `uk_stu_no`;

删除数据表

DROP TALBE `t01`;

显示创建数据表的语句

SHOW CREATE TABLE `t01`;

显示数据表字段列表

DESC `t01`;

SHOW COLUMNS FROM `t01`;

添加数据表的字段

• 置于最前

• ALTER TALBE `t01` ADD COLUMN `id` TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '自增主键' FIRST;
  

• 置于name字段后

  ALTER TABLE `t01` ADD COLUMN `sex` TINYINT UNSIGNED DEFAULT 0 COMMENT '性别，{0: 未知, 1: 男, 2: 女}' AFTER name;
  

• 默认置于最后

  ALTER TABLE `t01` ADD COLUMN `create_time` BIGINT UNSIGNED COMMENT '创建时间';
  

• 一次性添加多个

  ALTER TABLE `t01` ADD COLUMN (
    `class_id` TINYINT UNSIGNED DEFAULT 0 COMMENT '班级id',
    `student_no` VARCHAR(16) COMMENT '学号',
    `update_time` BIGINT UNSIGNED COMMENT '更新时间'
  );
  

  注意：这里不能指定位置了，只能置于最后

  如下两个范例都是错误的：

  ALTER TABLE `t01` ADD COLUMN (
    `class_id` TINYINT UNSIGNED DEFAULT 0 COMMENT '班级id' FIRST,
    `student_no` VARCHAR(16) COMMENT '学号' AFTER `name`,
    `update_time` BIGINT UNSIGNED COMMENT '更新时间'
  );
  

  ALTER TABLE `t01` ADD COLUMN (
    `class_id` TINYINT UNSIGNED DEFAULT 0 COMMENT '班级id',
    `student_no` VARCHAR(16) COMMENT '学号',
    `update_time` BIGINT UNSIGNED COMMENT '更新时间'
  ) AFTER `name`;
  

修改数据表的字段

• 只改类型，不改名字：

  ALTER TABLE `t01` MODIFY COLUMN `update_time` INT UNSIGNED COMMENT 'update time';
  

• 直接覆盖原来的：

  ALTER TABLE `t01` CHANGE COLUMN `update_time` `utime` INT UNSIGNED COMMENT 'update time';
  

删除数据表的字段

ALTER TABLE `t01` DROP COLUMN `utime`;

执行本地 SQL 文件中的 SQL 语句

source ./path/to/sql-file.sql;

退出数据库终端

quit;

exit;

查询语句

SELECT `*`|字段列表 FROM 表列表 [WHERE 条件列表];

范例：

• 查询mytable中的所有数据

  SELECT `*` FROM `mytable`;
  

• 查询mytable中的id和name

  SELECT `id`, `name` FROM `mytable`;
  

• 带条件的查询

  SELECT `*` FROM `mytable` WHERE `id` = 3;
  SELECT `*` FROM `mytable` WHERE `age` < 20;
  

• 多个条件查询，AND、OR、NOT、IN

  SELECT `*` FROM `mytable` WHERE `age` < 20 AND `gender` = 0;
  

插入语句

INSERT [INTO] `表名` [(`字段1`[, `字段2`[, ...]])] VALUES (值1[, 值2[, ...]])

• 如果表名后有指定字段，那么values后必须指定与之对应数量的值。
• 如果表名后没有指定字段，那么values后必须指定与表字段对应数量的值。
• 如果有自增字段，一般给null值，如果给null值，系统会自动生成

更新语句

UPDATE `表名` SET `字段1` = 值1, `字段2` = 值2, ... [WHERE ...];

注意：执行更新语句的时候一定要带上条件，否则会将表中的数据无条件全部更新。

删除语句

DELETE FROM `表名` [WHERE ...];

注意：执行删除语句的时候一定要带上条件，否则会将表中的数据无条件全部删除。

常见查询函数

• 总条数：count
  • 无法对null值进行计算
• 最大值、最小值：max、min
  • 无法对null值进行计算
• 平均值：avg

SELECT 函数名(`字段名`) FROM `表名`;

排序 (ORDER BY)

SELECT ... FROM ... [WHERE ...] [ORDER BY `排序字段1` [ASC|DESC], `排序字段2` [ASC|DESC], ...];

• ASC：升序 (默认)
• DESC：降序

获取指定范围的数据 (LIMIT)

SELECT ... FROM ... [WHERE ...] [ORDER BY ...] [LIMIT 数量] [OFFSET 数量];

范例：

SELECT `*` FROM `mytable` LIMIT 5; -- 获取前5条

SELECT `*` FROM `mytable` ORDER BY `id` DESC LIMIT 5; -- 获取后5条

SELECT `*` FROM `mytable` LIMIT 2, 5; -- 跳过2条，取5条

SELECT `* `FROM `mytable` LIMIT 5 OFFSET 2; -- 跳过2条，取5条

交叉连接 (INNER JOIN)

SELECT ... FROM ... [INNER JOIN `表名` ON 条件列表] [ORDER BY ...];

补充说明：

• INNER JOIN 等价于 JOIN
• 表1 INNER JOIN 表2 ON 条件列表 等价于 FROM 表1, 表2 WHERE 条件列表
• 如果不带条件，则返回交叉连接后的记录集，返回数量 = 表1的记录数 x 表2的记录数
• 如果带条件，则返回交叉连接后满足条件的记录集

范例：

SELECT `*` FROM `t_student` AS `s` INNER JOIN `t_class` AS `c` ON `s`.`cid` = `c`.`id`;

左连接 (LEFT JOIN)

SELECT ... FROM ... [LEFT JOIN `表名` ON 条件列表] [ORDER BY ...];

补充说明：

• LEFT JOIN 的返回记录集由两部分组成：
  • 1记录集：交叉连接后，满足条件的记录集
  • 2记录集：左表中未出现在上述1记录集中的记录集
• 2记录集中的右表字段会设置为NULL
• 执行流程：LEFT JOIN 会先以左表为基准与右表进行交叉连接，再对交叉连接后的结果集进行条件过滤，得到上述的1记录集，然后再补上上述的2记录集

右连接 (RIGHT JOIN)

SELECT ... FROM ... [RIGHT JOIN `表名` ON 条件列表] [ORDER BY ...];

补充说明：

• RIGHT JOIN 的返回记录集由两部分组成：
  • 1记录集：交叉连接后，满足条件的记录集
  • 2记录集：左表中未出现在上述1记录集中的记录集
• 2记录集中的左表字段会设置为NULL
• 执行流程：RIGHT JOIN 会先以右表为基准与左表进行交叉连接，再对交叉连接后的结果集进行条件过滤，得到上述的1记录集，然后再补上上述的2记录集