↓跳到主要内容
  1. 所有文章/
  2. MySQL相关笔记/

MySQL常用命令

·📄 4394 字·🍵 9 分钟
技术探索 MySQL
目录

数据库操作 #

创建数据库 #

CREATE DATABASE day21_1;
#创建数据库 并指定数据库中数据的编码

CREATE DATABASE day21_2 CHARACTER SET gbk;

修改数据库 #

#如果创建之后 修改数据库编码

ALTER DATABASE day21_2 CHARACTER SET=utf8;

查看数据库 #

#查看数据库MySQL服务器中的所有的数据库:

show databases;
#查看某个数据库的定义的信息:

show create database 数据库名;

#例如：

show create database day21_1;

#查看正在使用的数据库:

select database();

删除数据库 #

drop database 数据库名称;

drop database day21_2;

切换数据库 #

#格式：use 数据库名;

use day21_1;

表结构操作 #

创建表 #

create table 表名(

  字段名 类型(长度) 约束,

  字段名 类型(长度) 约束

);

#例如：
#创建分类表
CREATE TABLE sort (

  sid INT, #分类ID

  sname VARCHAR(100) #分类名称

);

rename table 表名 to 新表名;
#作用：修改表名
RENAME TABLE sort TO category;
#为分类表sort 改名成 category

#蠕虫复制: 从已有的数据中去获取数据,然后将数据又进行新增操作: 数据成倍的增加.

#表创建高级操作: 从已有表创建新表(复制表结构)

Create table 表名 like 数据库.表名;

温馨提示：你创建了数据库，就创建了一块逻辑空间，实际在磁盘上创建了一个文件夹，你创建了一个表，实际磁盘生成了一个.ibd文件，你可以在C:\ProgramData\MySQL\MySQL Server 8.0\Data目录下验证一下，路径中的ProgramData是隐藏文件夹。

查看表 #

show tables;

#查看表结构：

#有两种方式

#方法一： desc 表名;

#方法二： SHOW COLUMNS FROM 表名;

DESC student;
SHOW COLUMNS FROM student;

删除表 #

drop table 表名;

drop table sort;

修改表 #

  • 添加一列
    alter table 表名 add 列名 类型(长度) 约束;

#1，为分类表添加一个新的字段为 分类描述 varchar(20)

ALTER TABLE sort ADD sdesc VARCHAR(20);

#当然，想添加多个字段分类怎么做呢？

/*添加多个列方法一*/

ALTER TABLE student

ADD address VARCHAR(200) NOT NULL,

ADD home_tel CHAR(11) NOT NULL;

/*add语句之间用逗号分隔，最后用分号结束*/

/*添加多个列方法二*/

ALTER TABLE student

ADD (address VARCHAR(200) NOT NULL,home_tel CHAR(11) NOT NULL);

#如果我想将这个字段添加到表中间而不是末尾怎么办呢？

alter table 表名 add 列名 类型(长度) 约束 after 某个字段;

#比如我想在age字段的后面加一个字段sex，而不是在最后一个字段末尾添加

alter table student add column sex char(1) not null comment '性别' after age;

    值得注意的是：如果表需要添加多列，而有一列字段home_tel之前已经添加过了，结果会显示Duplicate column name ‘home_tel’，那么你本次添加的多列字段都是无效的，即全部添加失败

  • 修改一列
    alter table 表名 modify 列名 类型(长度) 约束;

#作用：修改表修改列的类型长度及约束.

#例如：

#为分类表的分类名称字段进行修改，类型varchar(50) 添加约束 not null

ALTER TABLE sort MODIFY sname VARCHAR(50) NOT NULL; /* 添加约束NOT NULL */

ALTER TABLE student

#同理，和add类似，需要修改多个列的类型长度及约束，那么modify语句之间用逗号分隔，最后一句的末尾用分号结束。
MODIFY home_tel VARCHAR(20) NOT NULL; /*CHAR(11)修改为VARCHAR(200)*/

    alter table 表名 change 旧列名 新列名 类型(长度) 约束;

#作用：修改表修改列名.

#例如：

#为分类表的分类名称字段进行更换 更换为 snamesname varchar(30)

ALTER TABLE sort CHANGE sname snamename VARCHAR(30);
#同理，和add类似，需要修改多个列的字段名，那么change语句之间用逗号分隔，最后一句的末尾用分号结束。

    注意：change和modify都可以修改表的定义，不同的是change后面需要写两次列名，不太方便，但是change的优点是可以修改列名称，modify则不行。

    alter table 表名 character set 字符集;

#作用：修改表的字符集
#为分类表 category 的编码表进行修改，修改成 gbk

ALTER TABLE category CHARACTER SET gbk;
  • 删除一列
    alter table 表名 drop 列名;

#作用：修改表删除列.

#删除分类表中snamename这列

ALTER TABLE sort DROP snamename;

ALTER TABLE student

DROP home_address,

DROP home_tel;

#同理，和add类似，需要删除多列，那么drop语句之间用逗号分隔，最后一句的末尾用分号结束。

索引操作 #

创建/添加索引 #

建表的时候创建索引，也可以在已存在的表上添加索引。

CREATE TABLE 表名称(

  ......,

  INDEX [索引名称] (字段),

  ......

);

CREATE TABLE t_message(

  id INT UNSIGNED PRIMARY KEY,

  content VARCHAR(200) NOT NULL,

  type ENUM("公告", "通报", "个人通知") NOT NULL,

  create_time TIMESTAMP NOT NULL,

  INDEX idx_type (type)

);

#普通索引：

CREATE INDEX 索引名称 ON 表名(字段);  /*添加索引方式1*/

ALTER TABLE 表名 ADD INDEX 索引名称(字段); /*添加索引方式2*/

#唯一索引：

CREATE UNIQUE INDEX 索引名称 ON 表名(字段)

#联合索引：

CREATE INDEX 索引名称 ON 表名(字段1，字段2...)

查询索引 #


SHOW INDEX FROM 表名;

/*查看t_message表的索引*/

SHOW INDEX FROM t_message;

#查出来有添加的普通索引和主键索引

删除索引 #


DROP INDEX 索引名称 ON 表名;

/* 在t_message表中删除idx_type索引 */

DROP INDEX idx_type ON t_message;

表数据操作 #

添加数据 #

表名后面不写字段列表也可以插入数据，但是会影响速度。Mysql会进行词法分析，找到对应表结构，然后自动给你补上字段列表。所以表名后面不写字段列表，数据库难以高效的操作。

Insert into 表名 [字段1,字段2,......] values (值1,值2,......); /*插入单条记录*/

Insert into 表名 [字段1,字段2,......] values (值1,值2,......), (值1,值2,......); /*插入多条记录*/

INSERT INTO t_dept(deptno, dname, loc)

#IGNORE关键字只会插入数据库不存在的记录。比如主键冲突、唯一性冲突，数据库会报错，加上IGNORE之后数据库会忽略这条数据不会报错。
INSERT IGNORE INTO t_dept(deptno, dname, loc)
VALUES(70, "A", "北京"), (80, "B", "上海"); /*70部门已经存在*/

更新数据 #

UPDATE [IGNORE] 表名 SET 字段1=值1, 字段2=值2, ......
[WHERE 条件1 ......]
[ORDER BY ......]
[LIMIT ......];

#注意，如果这里有limit关键字，那么后面只能跟一个参数，即表示取前多少条数据，这里的limit不能有2个参数，ignore表示更新失败就直接忽略而不是报错。

#把每个员工的编号和他上司的编号+1，用order by子句完成
UPDATE t_emp SET empno=empno+1, mgr=mgr+1
ORDER BY empno DESC;


#把月收入前三名的员工底薪减100元，用LIMIT子句完成
UPDATE t_emp
SET sal=sal-100
ORDER BY sal+IFNULL(comm,0) DESC
LIMIT 3;


#把月收入前三名的员工底薪减100元，用LIMIT子句完成
UPDATE t_emp
SET sal=sal+200
WHERE deptno=10 AND DATEDIFF(NOW(),hiredate)/365 >= 20

多表更新数据 #

UPDATE 表1 JOIN 表2 ON 条件
SET 字段1=值1, 字段2=值2, ......;

引申出另一种写法

UPDATE 表1 JOIN 表2
SET 字段1=值1, 字段2=值2, ......
WHERE 条件;

#表连接的UPDATE语句可以修改多张表的记录

#把ALLEN调往RESEARCH部门，职务调整为ANALYST
/*表连接的几种写法*/
UPDATE t_emp e JOIN t_dept d ON e.ename="ALLEN" AND d.dname="RESEARCH"
SET e.deptno=d.deptno, e.job="ANALYST"
 
UPDATE t_emp e JOIN t_dept d
SET e.deptno=d.deptno, e.job="ANALYST"
WHERE e.ename="ALLEN" AND d.dname="RESEARCH"
 
UPDATE t_emp e,t_dept d
SET e.deptno=d.deptno, e.job="ANALYST"
WHERE e.ename="ALLEN" AND d.dname="RESEARCH"

UPDATE语句的表连接既可以是内连接，又可以是外连接。

UPDATE 表1 [LEFT | RIGHT] JOIN 表2 ON 条件SET 字段1=值1, 字段2=值2, ......;

UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
SET e.deptno=20
WHERE e.deptno IS NULL OR (d.dname="SALES" AND e.sal<2000);

删除数据 #

基本语法

DELETE [IGNORE] FROM 表名
[WHERE 条件1, 条件2, ...]
[ORDER BY ...]
[LIMIT ...];

子句执行顺序：FROM -> WHERE -> ORDER BY -> LIMIT -> DELETE

ignore表示删除失败就直接忽略而不是报错。

有了前面新增、更新数据的基础，下面的例子我就不展示数据表的变化了，基本语法比较容易理解。

#删除10部门中，工龄超过20年的员工记录

DELETE from t_emp
WHERE deptno=10 AND DATEDIFF(NOW(),hiredate)/365 >20;

#删除20部门中工资最高的员工记录
DELETE FROM t_emp
WHERE deptno=20
ORDER BY sal+IFNULL(comm,0) DESC
LIMIT 1;

提示：如果表中存在主键自增长,那么当删除之后, 自增长不会还原，下一条数据记录插入会在上一次计数的基础继续增加

快速删除数据 #

DELETE语句是在事务机制下删除记录，删除记录之前，先把要删除的记录保存到日志文件里，然后再删除记录。

TRUNCATE语句在事务机制之外删除记录，速度远超过DELETE语句。

TRUNCATE TABLE 表名;

注意：

  1. drop（drop table 表名）是完全删除表，包括表结构，数据库就查不到这个表了
  2. delete（delete from 表名）是删除表数据，保留表的结构，数据库中该表还存在，如果加where条件，可以只删除一行或者多行，下次插入id不会从1开始，而是从最后一次插入的id+1开始
  3. truncate （truncate table 表名）只能删除全表数据，会保留表结构，数据库中该表还存在，下次插入id从1开始

查询数据 #

运行顺序

  1. from
  2. on
  3. join
  4. where （可以使用表的别名）
  5. group by(可以开始使用select中字段的别名（不是表的别名），后面的语句中都可以使用)
  6. having + 聚合函数
  7. select
  8. distinct
  9. order by
  10. limit
Select [字段别名]/* from 数据源 [where条件子句] [group by子句] [having子句] [order by子句] [limit 子句];

去重查询 #

All或者 *: 默认保留所有的结果

Distinct: 去重, 查出来的结果,将重复给去除(所有字段都相同)

SELECT DISTINCT job FROM t_emp;

Distinct只能存在于select子句查询一个字段的情况，否则要么失效，要么语法报错。

字段别名 #

字段名 [as] 别名;

SELECT empno, sal*12 AS "income" FROM t_emp;

数据源 #

单表数据源: select * from 表名;

多表数据源: select* from 表名1,表名2...;

where语句 #

  • 判断条件
    判断条件:

比较运算符: >, <, >=, <= ,!= ,<>, =, like, between and, in/not in

逻辑运算符: &&(and), ||(or), !(not)
  • 模糊查询，非空判断
    例如判断某个字段是NULL就满足条件，WHERE comm IS NULL

如果不为空则满足条件，WHERE comm IS NOT NULL

A开头的， WHERE ename LIKE "A%"

包含字母A，WHERE ename LIKE "%A%"

聚合函数 #

Count(): 统计分组后的记录数: 每一组有多少记录

Max(): 统计每组中非空的最大值

Min(): 统计非空的最小值

Avg(): 统计平均值

Sum(): 统计和

Groupby子句 #

主要用来分组查询, 通过一定的规则将一个数据集划分为若干个小的区域，然后针对每个小区域分别进行数据汇总处理。也就是根据某个字段进行分组(相同的放一组,不同的分到不同的组)

根据不同的部门号分组显示平均工资
SELECT deptno, ROUND(AVG(sal)) FROM t_emp GROUP BY deptno;/*round四舍五入为整数*/

GROUP_CONCAT函数

这个函数可以把分组查询中的某个字段拼接成一个字符串

查询每个部门内底薪超过2000元的人数和员工姓名

SELECT deptno, COUNT(*), GROUP_CONCAT(ename)
FROM t_emp
WHERE sal >= 2000
GROUP BY deptno;

Having子句 #

WHERE子句是不允许出现聚合函数。而且WHERE优先级高于GROUP BY，在条件筛选的时候不知道按照什么范围去筛选，是全部数据筛选还是分部门数据筛选呢？

解决方案来了，那就是HAVING子句，HAVING子句的出现主要是为了WHERE子句不能使用聚合函数的问题，HAVING子句不能独立存在，必须依赖于GROUP BY子句而存在，GROUP BY 执行完成就立即执行HAVING子句

SELECT deptno, COUNT(*)
FROM t_emp
GROUP BY deptno HAVING AVG(sal) >= 2000;
#结果就出来了，部门号为20，底薪超过2000的有5人，部门号为10，底薪超过2000的有3人

查询每个部门中，查询每个部门中，1982年以后入职员工超过2个人的部门编号

SELECT deptno FROM t_emp
WHERE hiredate>="1982-01-01"
GROUP BY deptno HAVING COUNT(*) > 2
ORDER BY deptno;

Orderby子句 #

Order by 字段名 [asc|desc]; – asc是升序(默认的),desc是降序

SELECT empno, ename, sal, deptno FROM t_emp ORDER BY sal DESC;

多字段排序

使用order by 规定首要条件和次要条件排序。数据库会先按照首要条件排序，遇到首要排序内容相同的记录，那么会启用次要条件再次排序。

SELECT empno, ename, sal, hiredate 
FROM t_emp ORDER BY sal DESC, hiredate ASC;

Limit子句 #

Limit子句是一种限制结果的语句，用来做数据分页的。

比如我们看朋友圈，只会加载少量的部分信息，不会一次性加载全部朋友圈，那样只会浪费CPU时间、内存、网络带宽。而结果集的记录可能很多，可以使用limit关键字限定结果集数量。

  • 只用来限制长度(数据量): limit 数据量;

  • 限制起始位置,限制数量: limit 起始位置,长度;
  • 对于服务器来讲: 根据用户选择的页码来获取不同的数据: limit offset,length;

Length: 每页显示的数据量: 基本不变

Offset: offset = (页码 - 1) * 每页显示量

↑ ↓