休言女子非英物，夜夜龙泉壁上鸣。这篇文章主要讲述MySQL—— 数据库操作基础 和 常用语法（DDL,DML,DQL,DCL）相关的知识，希望能为你提供帮助。
@toc
一、操作数据库
1、操作数据库1、创建 数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名;
2、删除 数据库
DROP DATABASE [if EXISTS] 数据库名;
3、使用 数据库
--如果表名或者字段名是特殊字符，则需要带``
use 数据库名;
4、查看数据库
SHOW DATABASES;
2、数据库的字段属性
UnSigned- 无符号的
- 声明了该列不能为负数
ZEROFILL- 0填充的
- 不足位数的用0来填充 , 如int(3),5则为005
Auto_InCrement- 通常理解为自增，自动在上一条记录的基础上默认+1
- 通常用来设计唯一的主键，必须是整数类型
- 可定义起始值和步长
- 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)
NULL 和 NOT NULL- 默认为NULL , 即没有插入该列的数值
- 如果设置为NOT NULL , 则该列必须有值
DEFAULT- 默认的
- 用于设置默认值
- 例如,性别字段,默认为" 男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为" 男" 的值
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 学号,
`name` VARCHAR(30) NOT NULL DEFAULT 匿名 COMMENT 姓名,
`pwd` VARCHAR(20) NOT NULL DEFAULT 123456 COMMENT 密码,
`sex` VARCHAR(2) NOT NULL DEFAULT 女 COMMENT 性别,
`birthday` DATETIME DEFAULT NULL COMMENT 出生日期,
`address` VARCHAR(100) DEFAULT NULL COMMENT 家庭住址,
`email` VARCHAR(50) DEFAULT NULL COMMENT 邮箱,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8注意点：格式：
CREATE TABLE IF NOT EXISTS `student`(
字段名 列类型 [属性] [索引] [注释],
字段名 列类型 [属性] [索引] [注释],
......
字段名 列类型 [属性] [索引] [注释]
)[表的类型][字符集设置][注释]常用命令：
SHOW CREATE DATABASE 数据库名;
-- 查看创建数据库的语句
SHOW CREATE TABLE 表名;
-- 查看表的定义语句
DESC 表名;
-- 显示表的具体结构4、数据库存储引擎INNODB
- 默认使用，安全性高，支持事务的处理，多表多用户操作
- 早些年使用，节约空间，速度较快
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大，约为2倍 |
修改修改表名 :
ALTER TABLE 旧表名 RENAME AS 新表名添加字段 :
ALTER TABLE 表名 ADD字段名 列属性[属性]修改字段 :
ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]删除字段 :
ALTER TABLE 表名 DROP 字段名举例
-- 修改表名
-- ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teachers;
-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teachers ADD age INT(11);
-- 修改表的字段(重命名，修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 [列属性];
ALTER TABLE teachers MODIFY age VARCHAR(11);
-- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 [列属性];
ALTER TABLE teachers CHANGE age age1 INT(1);
-- 字段重命名-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teachers DROP age1;
6、删除数据库 （drop table）语法：
DROP TABLE [IF EXISTS] 表名- IF EXISTS为可选 , 判断是否存在该数据表
- 如删除不存在的数据表会抛出错误
DROP TABLE IF EXISTS teachers;
所有的创建和删除尽量加上判断，以免报错~
二、外键 1.外键概念如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。
以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。
在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。
2.外键作用保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。
目标：学生表（student）的gradeid字段 要去引用年级表（grade）的 gradeid字段
方式一：在创建表的时候增加约束
/*
1. 定义外键key
2. 给外键添加约束（执行引用）references 引用
*/
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 学号,
`name` VARCHAR(30) NOT NULL DEFAULT 匿名 COMMENT 姓名,
`pwd` VARCHAR(20) NOT NULL DEFAULT 123456 COMMENT 密码,
`sex` VARCHAR(2)NOT NULL DEFAULT 女 COMMENT 性别,
`birthday` DATETIME DEFAULT NULL COMMENT 出生日期,
`address` VARCHAR(100) DEFAULT NULL COMMENT 家庭住址,
`email` VARCHAR(50) DEFAULT NULL COMMENT 邮箱,
`gradeid` INT(10) NOT NULL COMMENT 学生的年级,
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8-- 创建年级表
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL COMMENT 年级id,
`gradename` VARCHAR(50) NOT NULL COMMENT 年纪名称,
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）
方法二：创建表成功后，添加外键约束
/*
1. 定义外键key
2. 给外键添加约束（执行引用）references 引用
*/
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 学号,
`name` VARCHAR(30) NOT NULL DEFAULT 匿名 COMMENT 姓名,
`pwd` VARCHAR(20) NOT NULL DEFAULT 123456 COMMENT 密码,
`sex` VARCHAR(2)NOT NULL DEFAULT 女 COMMENT 性别,
`birthday` DATETIME DEFAULT NULL COMMENT 出生日期,
`address` VARCHAR(100) DEFAULT NULL COMMENT 家庭住址,
`email` VARCHAR(50) DEFAULT NULL COMMENT 邮箱,
`gradeid` INT(10) NOT NULL COMMENT 学生的年级,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
-- 创建年级表
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL COMMENT 年级id,
`gradename` VARCHAR(50) NOT NULL COMMENT 年纪名称,
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
以上的操作都是物理外键，数据库级别的外键，不建议使用！避免数据库过多造成困扰！
最佳实践- 数据库就是用来单纯的表，只用来存数据，只有行（数据）和列（属性）
- 我们想使用多张表的数据，使用外键，用程序去实现
文章图片
常规命令：
show databases;
#查看数据库中都有哪些库
create database mydb charset=utf8;
#创建mydb库
use mydb;
#选择mydb库
create table students(
id int primary key aotu,
name varchar(20) Not Noll,
score float,
birth date,
);
#创建学生表
show tables;
#查看当前库下的所有表
desc students;
#查看学生表都有哪些属性
select *from students;
#查看学生表都有哪些成员
select database();
#查看当前所在的库
select show table 表名;
# 查看该表的属性
drop table students;
#删除学生表，指没有关联的表
drop database mydb;
#删除mydb库
delete from 表名;
#删除该表
delete from user where name=张三;
#删除user表中‘张三’这个字段alter：针对表的字段alter table 旧表名 rename to 新表名;
#修改表名
rename 旧表名 to 新表名;
eg:alter table scores rename grades;
alter table 表名 modify 字段名数据类型;
#修改字段的数据类型
eg:alter table scores modify score int;
alter table 表名 change 旧字段新字段数据类型;
#修改字段名
eg:alter table scores change score grade int;
alter table 表名 add 新字段 数据类型;
#添加字段
eg:alter table scores add name varchar(10);
alter table 表名 drop 字段名；#删除字段
eg:alter table scores drop address;
alter table 表名 drop foreign key 外键约束名；#删除表的外键约束
eg:alter table scores drop foreign key name;
注意：删除关联表时，先解除关联，再进行删除。
insert插入：针对数据insert into scores(name,school,grade,teacher) values(jack,beijing,75,wangqiang);
#单行插入
insert into scores(name,school,grade,teacher) values(jack,beijing,75,wangqiang),
(tom,xian,86,liuhan),
(alice,shanghai,69,noll）；#多行插入update更新update scores set name=tom;
#将scores表中name全部更新为tom】
update school set num=num+20 where address=China;
#给school表中地址为China的加20一、DDL–数据定义语言作用：数据定义语言主要用来定义数据库中的各类对象，包括用户、库、表、视图、索引、触发器、事件、存储过程和函数等。
常见的DDL操作的基本用法如下：
CREATE USER#创建用户
CREATE DATABASE#创建数据库
CREATE TABLE#创建表
CREATE VIEW#创建视图
CREATE INDEX#创建索引
CREATE TRIGGER#创建触发器
CREATE EVENT#创建事件
CREATE PROCEDURE#创建存储过程
CREATE FUNCTION#创建自定义函数1、创建用户：CREATE USER username@[ip/domain/netmask]参数解释：
username:表示登陆mysql实例的用户名
示例：创建一个名称为bingwang，登陆ip为192.168.0.10的用户：
mysql>
CREATE USER bingwang@192.168.0.10;
2、创建数据库详细用法：
CREATE DATABASE db_name;
示例如下：
#创建一个名称为test_db，字符集为utf8的数据库
mysql>
CREATE DATABASE test_db DEFAULT CHARSET UTF8;
3、创建表：详细用法：
CREATE TABLE table_name;
示例如下：
#创建一个名称为t_test，字符集为utf8，存储引擎为InnoDB，字符校验集为utf8_general_ci的表：
mysql>
CREATE TABLE t_test (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
PRIMARY KEY(id)
) ENGINE = InnoDB DEFAUL CHARSET = UTF8 COLLATE = utf8_general_ci;
4、创建视图：详细用法：
CREATE VIEW view_name as <
SELECT phrase>
;
示例如下：
#创建一个视图t_view，用来查询t_test中的ID为1或者2的数据：
mysql>
CREATE VIEW test_view AS SELECT * FROM t_test WHERE id IN (1,2);
查看创建视图的过程：
mysql>
SHOW CREATE VIEW test_view;
5、创建索引有两种方法，
CREATE 和 ALTER【MySQL—— 数据库操作基础 和 常用语法（DDL,DML,DQL,DCL）】下面先介绍一下CREATE：
详细用法：
CREATE [UNIQUE] INDEX index_name ON table_name(field[num]) <
OPTIONS>
;
参数解释：
示例如下：
（1）给t_test表中的name字段添加一个唯一索引，使用BTREE作为其索引算法：
mysql>
CREATE UNIQUE INDEX name_ind ON t_test(name) USING BTREE;
mysql>
SHOW [INDEX/INDEXES] FROM t_test;
#查看t_test表中的索引，[INDEX/INDEXES]两个关键字都可以（2）给t_test表中的name字段的前5个字符创建一般索引，使用BTREE作为其索引算法：
mysql> CREATE INDEX name_index ON t_test(name(5));
关于索引的更多用法及优化在后面的文章中会详细讲解。
6、创建触发器：
详细用法：
CREATE TRIGGER trigger_name trigger_time trigger_event FOR EACH ROW
BEGIN
trigger_stmt
END;
示例：创建触发器内容稍多，此处先稍微提一下，后面专门章节介绍；
7、创建存储过程：
详细用法：
CREATE PROCEDURE procedure_name([proc_parameter[,...]])
BEGIN
…存储过程体
END8、创建自定义函数：
详细用法：
CREATE FUNCTION function_name([func_parameter[,...]])
RETURNS type
BEGIN
...函数体
END至此，简单的DDL操作介绍完成。
二、DML–数据操纵语言：作用：用来操作数据库中的表对象，主要包括的操作有：INSERT，UPDATE，DELETE
常见的DML的基本操作方法如下：#给表中添加数据
INSERT INTO ...
#修改表中的数据
UPDATE table_name SET ...
#删除表中的数据
DELETE FROM table_name WHERE <
condition>
;
注：:表示DML操作时的条件
1、向表中插入数据：详细用法：
mysql>
INSERT INTO table_name(field1,field2,[,...]) values(value1,value2),(value3,value4),...;
示例：向学生表中插入一条数据，name:‘xiaohong’, age:24, gender:‘M’ ，如下：
(1)创建表：
mysql>
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL DEFAULT ,
age TINYINT,
gender ENUM(F,M)
) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
(2)插入数据：
mysql>
INSERT INTO student(name,age,gender) VALUES(xiaohong,24,M);
Query OK, 1 row affected (0.09 sec)
mysql>
SELECT * FROM student;
+----+----------+------+--------+
| id | name| age| gender |
+----+----------+------+--------+
|1 | xiaohong |24 | M|
+----+----------+------+--------+
1 row in set (0.37 sec)2、修改表中的数据：详细用法：
UPDATE table_name SET field1 = value1, field2 = value2, … , WHERE ;
示例：将student表中id为1的记录中的name值修改为:“xiaohua”，如下：
mysql>
UPDATE STUDENT SET name = xiaohua WHERE id = 1;
Query OK, 1 row affected (0.67 sec)
Rows matched: 1Changed: 1Warnings: 0
mysql>
SELECT * FROM student;
+----+---------+------+--------+
| id | name| age| gender |
+----+---------+------+--------+
|1 | xiaohua |24 | M|
+----+---------+------+--------+
1 row in set (0.00 sec)3、删除表中的数据：详细用法：
mysql>
DELETE FROM table_name WHERE <
condition>
;
示例：删除student表中id为1的记录，如下：
mysql>
DELETE FROM student WHERE id = 1;
Query OK, 1 row affected (0.37 sec)
mysql>
SELECT * FROM student;
Empty set (0.00 sec)注意：注意！注意！！再注意！！！，
mysql>
DELETE FROM student;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KE至此，DML操作介绍完毕。
三、DQL–数据查询语言作用：主要用来查看表中的数据，也是平时使用最多的操作，主要命令为：
SELECT基本用法如下：
mysql>
SELECT fields FROM table_name WHERE <
condition>
;
注意事项：
1、简单不加条件的单表查询：用法：
mysql>
SELECT * FROM table;
2、单表中的条件查询：
常见的条件：> ，> =，< ，< = ，=，< > ，!=，IN，NOT IN，LIKE，NOT LIKE，REGEXP
示例：
#查询年龄大于23的记录
mysql>
SELECT * FROM student WHERE age >
23;
#查询年龄大于等于24的记录，和上面age>
23结果相同
mysql>
SELECT * FROM student WHERE age >
= 24;
#查询年龄小于24的记录
mysql>
SELECT * FROM student WHERE age <
24;
#查询年龄小于等于24的记录
mysql>
SELECT * FROM student WHERE age <
= 24;
#查询姓名等于xiaohong的记录
mysql>
SELECT * FROM student WHERE name = xiaohong;
#查询姓名不等于xiaohong的记录
mysql>
SELECT * FROM student WHERE name <
>
xiaohong;
#查询姓名不等于xiaohong的记录
mysql>
SELECT * FROM student WHERE name != xiaohong;
#查询姓名为xiaohong或者xiaohui的记录
mysql>
SELECT * FROM student WHERE name in (xiaohong,xiaohui);
#查询姓名不是xiaohong和xiaohui的记录等价于：where name != xiaohong and name != xiaohui
mysql>
SELECT * FROM student WHERE name not in (xiaohong,xiaohui);
#查询姓名以xiao开头的记录
mysql>
SELECT * FROM student WHERE name like xiao%;
#查询姓名以xiaohon开头的记录，后面模糊匹配一位，如：xiaohong，xiaohoni
mysql>
SELECT * FROM student WHERE name like xiaohon_;
#查询姓名中包含ao字符创的记录
mysql>
SELECT * FROM student WHERE name like %ao%;
#查询以hong结尾的记录
mysql>
SELECT * FROM student WHERE name not like %hong;
#使用正则表达式查询姓名以xiao开头的记录
mysql>
SELECT * FROM student WHERE name REGEXP(^xiao);
#使用正则表达式查询姓名以hong结尾的记录
mysql>
SELECT * FROM student WHERE name REGEXP(hong$);
注意：示例：查询student表中年龄大于" xiaohong" 年龄的记录的数量：
mysql>
SELECT COUNT(*) FROM student WHERE age >
(SELECT age FROM student WHERE name = xiaohong);
+----------+
| COUNT(*) |
+----------+
|2 |
+----------+
1 row in set (0.46 sec)3、分页查询：用法：
mysql>
SELECT * FROM table_name LIMIT start,num;
参数解释：
start:开始位置，默认从0开始；
num:偏移量，即：从开始位置向后查询的数据条数；
示例：查询test表中，第二页的数据，每页显示10条，如下：
mysql>
SELECT * FROM student LIMIT 1,10;
4、使用ORDER BY对查询结果进行排序：用法：
SELECT * FROM table_name <
where condition>
ORDER BY <
field>
ASC/DESC;
示例：从student表中查询出所有年龄大于20的学生记录，并且按照年龄age倒序排列，如下：
SELECT * FROM student WHERE age >
20 ORDER BY age DESC;
注意：如果在排序时ORDER BY 之后没有添加DESC和ASC关键字，默认按照ASC升序排列；
5、使用GROUP BY对查询结果集进行分组基本用法：
mysql>
SELECT res FROM table_name <
where condition>
GROUP BY <
field>
;
示例：查询student表中男生和女生的数量：
mysql>
SELECT gender,COUNT(*) FROM student GROUP BY gender;
6、使用GROUP BY之后，在使用HAVING完成分组之后的条件查询基本用法：
SELECT res FROM table_name <
where condition>
GROUP BY <
field>
<
having condition>
;
示例：查询student_course表中有3门成绩大于等于80的学生学号
（1）创建测试表结构：
mysql>
CREATE TABLE student_course(
sno INT(11) NOT NULL,
cno INT(11) NOT NULL,
grade SMALLINT NOT NULL DEFAULT 0
)ENGINE = InnoDB DEFAULT CHARSET = UTF8;
2）插入测试数据：
INSERT INTO student_course(sno,cno,grade) VALUES(1,100,79);
INSERT INTO student_course(sno,cno,grade) VALUES(1,101,89);
INSERT INTO student_course(sno,cno,grade) VALUES(1,102,87);
INSERT INTO student_course(sno,cno,grade) VALUES(1,103,99);
INSERT INTO student_course(sno,cno,grade) VALUES(2,100,90);
INSERT INTO student_course(sno,cno,grade) VALUES(2,101,80);
INSERT INTO student_course(sno,cno,grade) VALUES(2,102,77);
INSERT INTO student_course(sno,cno,grade) VALUES(2,103,79);
INSERT INTO student_course(sno,cno,grade) VALUES(3,100,89);
INSERT INTO student_course(sno,cno,grade) VALUES(3,101,90);
INSERT INTO student_course(sno,cno,grade) VALUES(3,102,83);
INSERT INTO student_course(sno,cno,grade) VALUES(3,103,91);
（3）查询：
mysql>
SELECT sno,SUM(CASE WHEN grade >
80 THEN 1 ELSE 0 END) num FROM student_course GROUP BY sno HAVING num >
= 3;
+-----+------+
| sno | num|
+-----+------+
|1 |3 |
|3 |4 |
+-----+------+
2 rows in set (0.45 sec)四、DCL–数据控制语言作用：用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果。
1、GRANT授予用户权限：基本用法：
mysql>
GRANT priv_type ON <
object_type>
TO user <
WITH GRANT OPTION | resource_option ...>
;
示例：给用户jerry授予对test_db数据库的增删改查权限，允许该用户从IP为’192.168.0.10’的网络登录
（1）方法一：
mysql>
GRANT INSERT,SELECT,UPDATE,DELETE ON test_db.* TO jerry@192.168.0.10 IDENTIFIED BY password WITH GRANT （2）方法二：
mysql>
CREATE USER jerry@192.168.0.10 IDENTIFIED BY password;
mysql>
GRANT INSERT,SELECT,UPDATE,DELETE ON test_db.* TO jerry@192.168.0.10;
2、REVOKE收回用户权限：
基本用法：
mysql>
REVOKE priv_type ON <
object_type>
FROM jerry@192.168.0.10;
示例：收回用户对test_db库的删除权限：
mysql>
REVOKE DELETE ON test_db.* FROM jerry@192.168.0.10;
3、查看给某个用户所授予的权限：
基本用法：
mysql>
SHOW GRANTS FOR user;
示例：查询给’jerry’@192.168.0.10’所授予的所有权限：
mysql>
SHOW GRANTS FOR jerry@192.168.0.10;
4、查询可授予的所有权限，使用技巧：
（1）首先将某个库(如：test_db)的所有权限授予给用户’jerry’@‘localhost’
mysql>
GRANT ALL ON test_db.* TO jerry@localhost IDENTIFIED BY jerry;
（2）收回某个权限，如：查询权限
mysql>
REVOKE SELECT ON test_db.* FROM jerry@localhost;
（3）查看剩余权限，就可以查到除了查询权限之外的权限，再加上查询权限即可授予的所有权限
mysql>
SHOW GRANTS FOR jerry@localhost;
推荐阅读
- 100 个 pandas 案例，强烈建议收藏！
- 基于esbuild的universal bundler设计
- #yyds干货盘点#Prometheus 之告警的艺术
- U盘设置只读模式图文详细教程分享
- 为啥U盘显示0字节?
- U盘不显示卷标怎样办？
- 电脑插入U盘蓝屏了怎样办?
- u盘出现copy.exe失去怎样处理?
- u盘文件不显示怎样办?u盘内文件无法显示