【MySQL】基础命令

一、登录数据库服务器

登录方式

• 远程连接（ssh）
• 本地服务器（本地搭建）

我这里通过本地 docker 的方式，启动数据库服务，登录命令：

$ docker exec -it mysql /bin/bash

查看数据库版本信息

$ mysql --version
$ mysql -V

登录数据库

$ mysql -h localhost -u root -p
# -h：指连接的主机名
# -u：用户名
# -p：密码

# 然后输入密码，进入数据库

查看数据库版本

mysql> SELECT VERSION();

正常会出现数据库的版本号，环境准备完成！

二、语法规范

开始数据库操作之前， 先了解一下基本的 SQL 规范：

• 命令不区分大小写，建议 关键字大写；
• 命令中间用逗号分隔，以 分号 结尾；
• 命令根据需要可缩进或换行；
• 注释方法

# 单行注释
-- 单行注释

/*
多行
注释
*/

三、数据库操作

创建数据库

mysql> CREATE DATABASE [数据库名];

# 还可以使用条件判断语句，当数据库不存在才创建
mysql> CREATE DATABASE if not exists demo;

查看当前所有数据库

mysql> SHOW DATABASES;

打开指定库

mysql> USE <数据库名>;

查看当前库的所有表

mysql> SHOW TABLES;

查看其他库的所有表

mysql> SHOW TABLES FROM <数据库名>;

删除数据库

mysql> DROP DATABASE <数据库名>;

# 还可以使用条件判断语句，当数据库存在才进行删除命令
mysql> DROP DATABASE IF EXISTS <数据库名>;

四、表操作

创建表

mysql> CREATE TABLE 表名 (
    属性名 数据类型 [完整约束条件],
    属性名 数据类型 [完整约束条件],
    ...
    ...
    属性名 数据类型 [完整约束条件]
);

上面创建表的时候涉及到一个完整性约束条件，如下表：

约束条件	说明
PRIMARY KEY	标识该属性为该表的主键，可以唯一的标识对应的元组
FOREIGN KEY	标识该属性为该表的外键，是与之联系某表的主键
NOT NULL	标识该属性不能为空
UNIQUE	标识该属性的值是唯一的
AUTO_INCREMENT	标识该属性的值是自动增加，MySQL 语句的特色
DEFAULT	为该属性设置默认值

示例：

# 创建学生表
mysql> CREATE TABLE student(
	sid INT PRIMARY KEY AUTO_INCREMENT UNIQUE,
	sname varchar(20) NOT NULL,
	sage INT NOT NULL,
	sgender varchar(2) NOT NULL DEFAULT '男'
)charset='utf8';
	
Query OK, 0 rows affected (0.01 sec)

查看表结构

mysql> DESC <表名>;
mysql> DESCRIBE <表名>;

复制表

# 方法一
mysql> CREATE TABLE <new_table_name> LIKE <old_table_name>;

# 方法二 2=1、1=0、1<>1 表示恒假，只为了获取表结构使用
mysql> CREATE TABLE <new_table_name> AS (SELECT * FROM <old_table_name> WHERE 2=1);

# eg.
mysql> CREATE TABLE student_1 LIKE student;
# 1<>1 表示 1 不等于 1，也是恒假
mysql> CREATE TABLE student_1 AS (SELECT * FROM student WHERE 1<>1);

修改表名

mysql> ALTER TABLE [旧表名] RENAME [新表名];

# eg.
mysql> ALTER TABLE student RENAME new_student;

删除表

mysql> DROP TABLE [表名];
mysql> DROP TABLE [表名1], [表名2];

删除被关联的父表：

• 方法 1 : 先删除子表，再删除父表；
• 方法 2 : 删除父表的外键约束，再删除该表。

增加列

mysql> ALTER TABLE [表名] ADD COLUMN [新增字段名] [数据类型] [完整性约束条件] [FIRST | AFTER [已有字段名]];

# FIRST：为可选参数，作用是将新增字段设置为表的第一个字段 
# AFTER：作用是将新增字段添加到“已有字段名”后面

# eg.
mysql> ALTER TABLE student ADD COLUMN age INT(3) NOT NULL FIRST;
mysql> ALTER TABLE student ADD COLUMN age INT(3) NOT NULL AFTER id;

删除列

mysql> ALTER TABLE [表名] DROP [属性名];

# eg.
mysql> ALTER TABLE student DROP name;

修改列名

mysql> ALTER TABLE [表名] CHANGE [旧属性名] [新属性名] [新数据类型];

# eg
mysql> ALTER TABLE student CHANGE name new_name varchar(20);

修改列数据类型

mysql> ALTER TABLE [表名] MODIFY [属性名] [数据类型];

# eg.
mysql> ALTER TABLE student MODIFY name varchar(30);

五、数据操作

增

插入数据/新增数据

mysql> INSERT INTO [表名] (field1, field2, field3) VALUES (value1, value2, value3);

# eg. 插入测试数据
mysql> INSERT INTO  student (sname, sage, sgender) VALUES ('周杰伦', 20, '男');
mysql> INSERT INTO  student (sname, sage, sgender) VALUES ('高圆圆', 18, '女');

查

1. 列数据查询

查询指定列数据

mysql> SELECT sname FROM student;
mysql> SELECT sname, sgender FROM student;

查询所有列数据

mysql> SELECT * FROM student;
# 如果数据量过大，使用 `*` 通配符，会降低检索和应用程序的性能

使用完全限定的表名

# 表关系如下：school 数据库中存在表 student，student 表中存在 sname 列
mysql> SELECT sname FROM student;　
mysql> SELECT student.sname FROM student;
mysql> SELECT student.sname FROM school.student;

# 其实这三条语句是等同的，这就是完全限定名的语法，在一些特定的情景中使用。

2. 关键字 DISTINCT - 查询数据按列去重

# 按性别查询，默认查询全部符合的结果
mysql> SELECT sgender FROM student;
男
男
女
女
...

# 使用 DISTINCT 按性别去重，因为只有男或女，所以查询结果仅为两个
mysql> SELECT DISTINCT sgender FROM student;
男
女

DISTINCT 关键字可添加多列，当添加多列时，仅去重多列数据同时重复，某一列没有重复，剩余列重复也不会被去重；那么，如果为检索的列为 * 的话，所有行都将被检索出来。

3. 关键字 LIMIT - 限制结果

检索返回数据的前 5 行

# 查询 student 表中返回数据的前 5 行
mysql> SELECT * FROM student LIMIT 5; 

检索返回数据的行数区间

# 查询学生表中 从第 5 行开始，后面 5 行的数据
mysql> SELECT * FROM student LIMIT 5, 5;

第一个数为开始位置，第二个数为要检索的行数，检索出来的第一行为 行 0，而不是行 1，因此，LIMIT 1,1 将检索出第二行，而不是第一行。

行数不够时，只返回它能返回的行数。例如：LIMIT 10，5 在只有 12 条数据的情况下只返回 2 条数据。

MYSQL 5 还有一种用法：OFFSET

# 查询学生表中 从第 3 行开始，后面 5 行的数据
mysql> SELECT * FROM student LIMIT 5 OFFSET 3;

4. 关键字 ORDER BY - 检索数据排序

关系数据库设计理论认为：如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。

按单列排序

# 按照姓名排序(A-Z)
mysql> SELECT sname FROM student ORDER BY sname;

按多列排序

# 首先按照年龄排序，然后再按照姓名排序
mysql> SELECT sname, sage FROM student ORDER BY sage, sname;

这里需要注意，如果第一个排序列中的值都是唯一，则不会按照第二个排序列去排序。

降序排序 - DESC

# 按照姓名排序（Z-A）
mysql> SELECT sname FROM student ORDER BY sname DESC;

# 按照年龄倒叙排列，姓名依然正序排列
# 得出结果是大龄在前
mysql> SELECT sname, sage FROM student ORDER BY sage DESC, sname;

如果需要对多个列进行降序排列，必须对每个列指定 DESC 关键字。

正序排列 - ASC

升序排序时可以指定关键字 ASC，但实际上，很少会使用，因为默认就是升序排序。

5. 组合使用：ORDER BY + LIMIT

# 如何找出年龄最大的同学
mysql> SELECT * FROM student ORDER BY sage DESC LIMIT 1;

6. WHERE 语句 - 过滤数据

使用 WHERE 子句

数据根据 WHERE 子句指定的搜索条件进行过滤，WHERE 子句在 FROM 子句之后，

# 查找姓名为周杰伦的同学的所有信息
mysql> SELECT * FROM student WHERE sname = "周杰伦";

WHERE 子句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定两个值之间

在使用BETWEEN时，必须指定两个值——所需范围的低端值和高端值，这两个值必须用AND关键字分隔。BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。举例如下：

# 检索年龄小于 19 的所有同学的信息
mysql> SELECT * FROM student WHERE sage < 19;

# 检索年龄不等于 15 的所有同学的信息
mysql> SELECT * FROM student WHERE sage <> 15;
SELECT * FROM student WHERE sage != 15;

# 检索年龄在 19-20 区间的所有同学的信息(包含 19 和 20)
mysql> SELECT * FROM student WHERE sage BETWEEN 19 AND 20;

空值检查

# 添加一项电话列
mysql> ALTER TABLE student ADD COLUMN sphone varchar(20) AFTER sgender;

# 查找所有电话为空的学生信息
mysql> SELECT * FROM student WHERE sphone IS NULL; 

7. 聚集函数

• AVG()：计算某列平均值，忽略值为 null 的行

• SUM()：计算某列值之和，忽略值为 null 的行

• MAX()：返回某列最大值，忽略值为 null 的行

• MIN()：返回某列最小值，忽略值为 null 的行

• COUNT()：对所有行计数，忽略值为 null 的行

mysql> SELECT AVG(sage) FROM student;  # 19.0000
mysql> SELECT SUM(sage) FROM student;  # 133
mysql> SELECT MAX(sage) FROM student;  # 20
mysql> SELECT MIN(sage) FROM student;  # 18
mysql> SELECT COUNT(sname) FROM student WHERE sname = '高圆圆';  # 2

删

删除所有数据

mysql> DELETE FROM <表名>;

删除单个数据

mysql> DELETE FROM student [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

改

修改整列数据

mysql> UPDATE student SET sphone=123;

按条件修改单条数据

# 修改年龄最大的 名为高圆圆的学生 电话号码
mysql> UPDATE student SET sphone=1234567 WHERE sname='高圆圆' ORDER BY sage DESC LIMIT 1;

六、多表查询

交叉连接

# 学生表和课程表
# 课程表包含 id 和 科目名称两个列

# 写法一；官方建议写法
mysql> SELECT * FROM student CROSS JOIN course;
# 写法二
mysql> SELECT * FROM studen, course;

交叉连接返回结果为两表的笛卡尔积，当数据量庞大时，返回结果也非常大。应该尽量避改这种查询。

多表按条件交叉连接

# 可以添加 WHERE 子句; 查询两个表中学生的课程 ID 相等的数据
mysql> SELECT * FROM student CROSS JOIN course WHERE student.course_id = course.id;

交叉连接使用 WHERE 语句，会先生成两个表的笛卡尔积，然后再筛选满足条件的记录。

因此，表的数量越多，交叉连接会越慢。一般情况下不建议使用交叉连接。

内连接

两个表用 INNER JOIN 连接，条件子句用 ON 连接

# 查询学生对应的课程名称
mysql> SELECT s.sname, c.cname FROM student s INNER JOIN course c ON s.course_id = c.cid; 

外连接

左外连接

mysql> SELECT s.sname, c.cname FROM student s LEFT JOIN course c on s.course_id = c.cid;

根据表 1 的返回记录来展示结果，如果表 1 的数据在表 2 中没有结果，那么显示为 NULL

右外连接

mysql> SELECT s.sname, c.cname FROM student s RIGHT JOIN course c on s.course_id = c.cid;

同左外连接原理，显示右表的全部记录。

使用外连接查询时，根据需要是否显示那边的全部记录，来选择是用左连接还是右连接。

子查询

条件语句嵌套，套娃需要根据实际的题来进行练习。