MySQL常用命令查询手册

朱雀 2023-07-10 08:08 184阅读 0赞

### 目录 ###

*   *  一、进入MySQL
     *  二、修改密码
     *  三、数据库的操作
     *   *  1.创建数据库
         *  2.查看数据库
         *  3.删除数据库
         *  4.修改数据库
         *  5.使用/切换数据库
         *  6.查看正在使用的数据库
     *  四、表结构的操作
     *   *  1.创建表
         *  2.查看数据库中所有的表
         *  3.查看表结构
         *  4.查看创建表的SQL语句
         *  5.删除表
         *  6.判断表是否存在，如果存在则删除表
         *  7.修改表的结构
         *   *  （1）添加表列-add
             *  （2）修改列类型-modify
             *  （3）修改列名-change
             *  （4）删除列-drop
             *  （5）修改表名-rename
             *  （6）修改字符集
     *  五、表数据的操作
     *   *  1.插入数据
         *  2.复制数据
         *  3.修改表记录
         *   *  （1）不带条件的修改数据，即修改所有的行。
             *  （2）带条件修改记录
         *  4.删除表记录
         *   *  （1）不带条件删除数据
             *  （2）带条件删除数据
             *  （3）使用truncate删除数据
             *  （4）delete和truncate区别
         *  5.查询表中的数据
         *   *  （1）查询表所有行和列的数据
             *  （2）查询指定列
             *  （3）对列指定别名进行查询
             *  （4）对列和表同时指定别名进行查询
             *  （5）查询指定列并且结果不出现重复数据
             *  （6）查询结果参与运算
             *  （7）条件查询
     *  六、MySQL查询语句
     *   *  1.排序查询
         *   *  （1）单列排序
             *  （2）组合排序
         *  2.聚合函数
         *   *  （1）五个聚合函数
             *  （2）语法格式
         *  3.分组查询
         *  4.having与where的区别
         *  5.LIMIT语句
     *  七、MySQL数据库表的约束
     *   *  1.主键约束
         *   *  （1）创建主键约束
             *   *  创建表时增加主键
                 *  已存在表增加主键
             *  （2）删除主键约束
             *  （3）主键自增
         *  2.唯一约束
         *  3.非空约束
         *  4.默认值
         *  5.外键约束
         *   *  （1）创建外键约束
             *   *  创建表时增加外键
                 *  已存在表增加外键
             *  （2）删除外键约束
         *  6.主键与外键的区别
     *  八、多表查询
     *   *  1.内连接
         *   *  隐式内连接
             *  显式内连接
         *  2.外连接
         *   *  左外连接
             *  右外连接

## 一、进入MySQL ##

mysql -u root -p

退出

quit

## 二、修改密码 ##

先进入MySQL，然后输入

set password for 用户名@localhost = '新密码'

示例修改密码为12345678  
或者直接在终端中修改：

mysqladmin -u root -p password 12345678

## 三、数据库的操作 ##

### 1.创建数据库 ###

create database 数据库名;

判断数据库是否已经存在，不存在则创建数据库

create database if not exists 数据库名;

创建数据库并指定字符集，如utf8

create database 数据库名 character set 字符集;

### 2.查看数据库 ###

查看所有数据库

show databases;

查看某个数据库的定义信息

show create database 数据库名;

### 3.删除数据库 ###

drop database 数据库名;

### 4.修改数据库 ###

修改数据库默认的字符集

alter database 数据库名 default character set 字符集;

### 5.使用/切换数据库 ###

use 数据库名;

### 6.查看正在使用的数据库 ###

select database();

## 四、表结构的操作 ##

### 1.创建表 ###

create table 表名 (
        字段名1 字段类型1, 
        字段名2 字段类型2
    );

举例：

create table student (
        id int,
        name varchar(20),
        birthday date
    );

### 2.查看数据库中所有的表 ###

show tables;

### 3.查看表结构 ###

describe 表名;

### 4.查看创建表的SQL语句 ###

show create table 表名;

### 5.删除表 ###

drop table 表名;

### 6.判断表是否存在，如果存在则删除表 ###

drop table if exists 表名;

### 7.修改表的结构 ###

#### （1）添加表列-add ####

alter table 表名 add 列名 数据类型;

例如：

alter table student add id int;

#### （2）修改列类型-modify ####

alter table 表名 modify 列名 新的类型;

例如：

alter table student modify name varchar(50);

#### （3）修改列名-change ####

alter table 表名 change 旧列名 新列名 类型;

例如：

alter table student change remark intro varchar(30);

#### （4）删除列-drop ####

alter table 表名 drop 列名;

例如：

alter table student drop intro;

#### （5）修改表名-rename ####

rename table 表名 to 新表名;

例如：

rename table student to student2;

#### （6）修改字符集 ####

alter table 表名 character set 字符集;

例如：

alter table student character set gbk;

## 五、表数据的操作 ##

### 1.插入数据 ###

insert into 表名 (字段名1, 字段名2, 字段名3, ...) values (值1, 值2, 值3, ...),(...);

### 2.复制数据 ###

将“表名2”中的所有的列复制到“表名1”中

insert into 表名1 select * from 表名2;

只复制部分列

insert into 表名1 (列1, 列2, ...) select (列1, 列2, ...) from 表名2;

### 3.修改表记录 ###

#### （1）不带条件的修改数据，即修改所有的行。 ####

update 表名 set 字段名=值;

#### （2）带条件修改记录 ####

update 表名 set 字段名=值 where 字段名=值;

例如：

update students set name='哆啦A梦' where id=2018001;

### 4.删除表记录 ###

#### （1）不带条件删除数据 ####

delete from 表名;

#### （2）带条件删除数据 ####

delete from 表名 where 字段名=值;

#### （3）使用truncate删除数据 ####

truncate table 表名;

#### （4）delete和truncate区别 ####

truncate 相当于删除表的结构，再创建一张表

### 5.查询表中的数据 ###

#### （1）查询表所有行和列的数据 ####

select * from 表名;

#### （2）查询指定列 ####

select 字段名1, 字段名2, 字段名3, ... from 表名;

#### （3）对列指定别名进行查询 ####

select 字段名1 as 别名1, 字段名2 as 别名2, ... from 表名;

例如：

select name as '姓名', age as '年龄' ... from students;

查看students表中的name字段和age字段的记录，其中name字段指定别名为 “姓名”，age字段指定别名为 “年龄”。

#### （4）对列和表同时指定别名进行查询 ####

select 字段名1 as 别名1, 字段名2 as 别名2, ... from 表名 as 别名;

例如：

select name as '姓名', age as '年龄' ... FROM students as '学生信息表';

查看students表中的name字段和age字段的记录，其中name字段指定别名为 “姓名”，age字段指定别名为 “年龄”，students表指定别名为 “学生信息表”。

#### （5）查询指定列并且结果不出现重复数据 ####

select distinct 字段名 from 表名;

例如：

select distinct address from student;

去掉重复的地方

#### （6）查询结果参与运算 ####

某列数据和固定值运算

select 列名1 + 固定值 from 表名;

某列数据和其他列数据参与运算

select 列名1 + 列名2 from 表名;

注意: 参与运算的必须是数值类型

#### （7）条件查询 ####

select 字段名 from 表名 where 条件;

## 六、MySQL查询语句 ##

### 1.排序查询 ###

通过 ORDER BY 子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序)

ASC: 升序，默认值
    DESC: 降序

#### （1）单列排序 ####

只按某一个字段进行排序，单列排序。

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];

例如：

-- 查询所有数据,使用年龄降序排序
    select * from student order by age desc;

#### （2）组合排序 ####

同时对多个字段进行排序，如果第 1 个字段相等，则按第 2 个字段排序，依次类推。

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];

例如：

-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序 
    select * from student order by age desc, math asc;

### 2.聚合函数 ###

平常我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询， 它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值 NULL。

#### （1）五个聚合函数 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70]

#### （2）语法格式 ####

SELECT 聚合函数(列名) FROM 表名;

例如：

-- 查询学生总数
    select count(id) as 总人数 from student;
    
    或
    select count(*) as 总人数 from student;

由于聚合函数会忽略空值NULL，建议如果统计个数则不要使用有可能为 null 的列，但如果需要把 NULL 也统计进去，那么可以使用IFNULL()。

-- 如果列名不为空，返回这列的值。如果为 NULL，则返回默认值。
    IFNULL(列名，默认值)

例如：

select count(ifnull(id,0)) from student;

### 3.分组查询 ###

分组查询是指使用 GROUP BY 语句对查询信息进行分组，相同数据作为一组。

SELECT 字段1, 字段2, ... FROM 表名 GROUP BY 分组字段 [HAVING 条件];

GROUP BY 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。 分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

例1：一个学生成绩表，该表中有 “性别” 字段和 “数学成绩” 字段。

-- 按性别进行分组，求男生和女生数学的平均分
    select sex, avg(math) from student3 group by sex;

实际上是将每组的 math 求了平均,返回每组统计的结果  
例如：

-- 对分组查询的结果再进行过滤
    SELECT sex, COUNT(*) FROM student WHERE age > 25 GROUP BY sex having COUNT(*) >2;

### 4.having与where的区别 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 1]  
如：

SELECT 字段1, 字段2, ... FROM 表名称 WHERE 筛选条件1 GROUP BY 字段3 having 筛选条件2;

where要在分组之前，having要在分组之后。  
筛选条件1不能是聚合函数，筛选条件2可以是聚合函数。

### 5.LIMIT语句 ###

LIMIT 是限制的意思，LIMIT 的作用就是限制查询记录的条数。

SELECT 字段1 [AS 别名1], 字段2 [AS 别名2], ... 
    FROM 表名 [WHERE子句] [GROUP BY子句] [HAVING 子句] [ORDER BY子句] [LIMIT子句];

LIMIT 语法格式

LIMIT offset,length;

如：

-- 查询学生表中数据，从第 3 条开始显示，显示 6 条。
    select * from student limit 2,6;

## 七、MySQL数据库表的约束 ##

数据库约束的种类  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 2]

### 1.主键约束 ###

#### （1）创建主键约束 ####

##### 创建表时增加主键 #####

字段名 字段类型 PRIMARY KEY  
例1:

CREATE TABLE tb_info (
        id INT PRIMARY KEY,
        name VARCHAR(30),
        age INT
    );

例2:

CREATE TABLE tb_info (
        id INT,
        name VARCHAR(30),
        age INT,
        PRIMARY KEY(id)
    );

##### 已存在表增加主键 #####

ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

例：

ALTER TABLE tb_info ADD PRIMARY KEY(id);

#### （2）删除主键约束 ####

删除单表主键约束

ALTER TABLE 表名 DROP PRIMARY KEY;

删除有外键连接的表的主键  
第一步：解除外键约束

ALTER TABLE 外键表 DROP FOREIGN KEY 外键名称

第二步：删除主键

ALTER TABLE 主键表 DROP PRIMARY KEY;

#### （3）主键自增 ####

-- 表示自动增长(字段类型必须是整数类型)
    AUTO_INCREMENT

如：

CREATE TABLE tb (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(30),
        age INT
    );

默认地 AUTO\_INCREMENT 的开始值是 1

创建表时指定起始值

CREATE TABLE 表名 (
        列名 INT PRIMARY KEY AUTO_INCREMENT
    ) AUTO_INCREMENT=起始值;

如：指定起始值为5

CREATE TABLE tb (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(30),
        age INT
    ) AUTO_INCREMENT=5;

创建好以后修改起始值

ALTER TABLE 表名 AUTO_INCREMENT=起始值;

备注：DELETE 和 TRUNCATE 对自增长的影响

DELETE:删除所有的记录之后，自增长没有影响。  
TRUNCATE:删除以后，自增长又重新开始。.

### 2.唯一约束 ###

唯一约束: 表中某一列不能出现重复的值

基本格式

字段名 字段类型 UNIQUE

如：tb表中的 name 字段中的记录不能出现重复的

CREATE TABLE tb (
        id INT,
        name VARCHAR(30) UNIQUE,
        age INT
    );

### 3.非空约束 ###

非空约束:某一列不能为null

基本语法格式

字段名 字段类型 NOT NULL

如：tb表中的 id 字段不能出现null

CREATE TABLE tb (
        id INT NOT NULL,
        name VARCHAR(30),
        age INT
    );

### 4.默认值 ###

基本语法格式

字段名 字段类型 DEFAULT 默认值

如：tb表中的 age 字段的默认值为18

CREATE TABLE tb (
        id INT,
        name VARCHAR(30),
        age INT DEFAULT 18
    );

### 5.外键约束 ###

#### （1）创建外键约束 ####

##### 创建表时增加外键 #####

首先创建第一张被关联表Vendors商品供应商表。

-- 供应商列表
    CREATE TABLE Vendors (
    	-- 供应商ID：主键列，自增长
    	vend_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '供应商ID',
    	-- 供应商名：可变字符，非空
    	vend_name VARCHAR (30) NOT NULL COMMENT '供应商名',
    	-- 供应商地址
    	vend_address VARCHAR (100) NOT NULL COMMENT '地址',
    	-- 供应商城市
    	vend_city VARCHAR (20) NOT NULL COMMENT '城市',
    	-- 供应商州
    	vend_state VARCHAR (20) NOT NULL COMMENT '州',
    	-- 供应商邮编
    	vend_zip VARCHAR (20) NOT NULL COMMENT '邮编',
    	-- 供应商国家
    	vend_country VARCHAR (20) NOT NULL COMMENT '国家'
    );

然后创建第二张关联表Products产品表。

-- 产品目录表
    CREATE TABLE Products (
    	-- 产品ID：主键列，自增长
    	prod_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    	-- 供应商ID：外键
    	vend_id INT NOT NULL COMMENT '供应商ID',
    	-- 产品名
    	prod_name VARCHAR (30) NOT NULL COMMENT '产品名',
    	-- 产品价格
    	prod_price DOUBLE NOT NULL COMMENT '产品价格',
    	-- 产品描述
    	prod_desc VARCHAR (100) COMMENT '产品描述',
    	FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id)
    );

##### 已存在表增加外键 #####

格式：

ALTER TABLE 从表 ADD CONSTRAINT 外键约束名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主键字段名);

例如：

-- 已存在表增加外键
    ALTER TABLE Products ADD FOREIGN KEY products_vendors_fk_1 (vend_id) REFERENCES Vendors (vend_id);

#### （2）删除外键约束 ####

ALTER TABLE 从表 DROP FOREIGN KEY 外键名称;

### 6.主键与外键的区别 ###

主关键字(primary key ): 是表中的一个或多个字段，它的值用于唯一地标识表中的某一条记录。  
外关键字(foreign key) : 是用于建立或加强两个表数据之间的链接的一列或多列。

**如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。**

主键和外键的区别：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 3]  
增加主键：

alter table 表名 add primary key (列名);

删除主键：

alter table 表名 drop primary key;

增加外键：

alter table 表名1 add foreign key 外键名 (外键字段) references 表名2 (主键字段);

删除外键：

alter table 表名 drop foreign key 外键名;

## 八、多表查询 ##

### 1.内连接 ###

用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如:从表.外键=主表.主键

#### 隐式内连接 ####

隐式内连接：看不到 JOIN 关键字，条件使用 WHERE 指定

SELECT 字段名 FROM 左表, 右表 WHERE 条件

如：

创建一个学生个人信息表

CREATE TABLE students(
        Id INT(30),
        Name VARCHAR(10),
        Age INT(4),
        Gender VARCHAR(25),
        PRIMARY KEY(Id)
    );

创建一个学习成绩表

CREATE TABLE course(
        Id INT(30),
         Java INT(5),
         Python INT(5),
         MySQL INT(5),
         Hadoop INT(5),
         C INT(5),
         PHP INT(5),
         Linux INT(5),
         English INT(5),
         Math INT(5),
         CONSTRAINT id_course FOREIGN KEY(Id) REFERENCES students(Id)
    );

学习成绩表的外键连接学生个人信息表的主键。这个时候，如果我们想要查询学生的Java成绩，

SELECT name, Java FROM students, course WHERE course.Id = students.Id;

#### 显式内连接 ####

显示内连接：使用 INNER JOIN … ON 语句, 可以省略 INNER

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

如：

还是上面创建的students表和course表

SELECT * FROM students INNER JOIN course c ON students.Id = c.Id;

查询students表的所有信息，按ID插入course表的数据，一并查询出来。

### 2.外连接 ###

#### 左外连接 ####

左外连接：使用 LEFT OUTER JOIN … ON，OUTER 可以省略

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

用左边表的记录去匹配右边表的记录，如果符合条件的则显示;否则，显示 NULL 。可以理解为：在内连接的基础上保证左表的数据全部显示。

#### 右外连接 ####

右外连接:使用 RIGHT OUTER JOIN … ON，OUTER 可以省略

SELECT 字段名 FROM 左表 RIGHT [OUTER] JOIN 右表 ON 条件

用右边表的记录去匹配左边表的记录，如果符合条件的则显示;否则，显示 NULL。可以理解为：在内连接的基础上保证右表的数据全部显示。

以上内容仅供参考学习，如有侵权请联系我删除！  
如果这篇文章对您有帮助，左下角的大拇指就是对博主最大的鼓励。  
您的鼓励就是博主最大的动力！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200301191758301.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200301192459769.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200301194924582.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200301204239692.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ==,size_16,color_FFFFFF,t_70