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记录集