文章目录
1、数据库管理系统
数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。
数据库管理系统、数据库和表的关系
数据库管理程序(DBMS)可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体 User 的数据。
数据库管理系统、数据库和表的关系如图所示：
2、SQL概念
SQL（Structured Query Language），结构化查询语言。简单来说，其实就是定义了操作所有关系型数据库的一种语法规则。因为SQL是一种语法规则，所以在所有的关系型数据库中都支持。但不同的数据SQL语句都会有一些区别，所以应该针对学习。
SQL语句的分类：
- DDL 数据定义语言(Data Definition Language)。如：建库，建表
- DML 数据操纵语言(Data Manipulation Language)。如：对表中的记录操作增删改
- DQL 数据查询语言(Data Query Language)。如：对表中的查询操作
- DCL 数据控制语言(Data Control Language)。如：对用户权限的设置
一般操作数据库或表，针对四个方面，即增删改查，也称为CRUD。（Create、Retrieve、Update、Delete）
3、MySQL的数据类型
4、MySQL的基本使用
4.1、DDL：操作数据库与表
4.1.1、数据库操作
（1）创建数据库
-- 创建数据库
CREATE DATABASE 数据库名;
-- 判断数据库是否已经存在，不存在则创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;
-- 创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
-- 创建数据库，判断是否存在，若存在则制定字符集
CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET 字符集;
（2）查看数据库
-- 查看所有的数据库
SHOW DATABASES;
-- 查看某个数据库的定义信息
SHOW CREATE DATABASE 数据库名;
（3）修改数据库
-- 修改数据库默认的字符集
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
（4）删除数据库（谨慎操作！）
-- 删除指定的数据库
DROP DATABASE 数据库名;
-- 判断数据库是否存在，若存在则删除该数据库
DROP DATABASE IF EXISTS 数据库名;
（5）使用数据库
-- 查看当前正在使用的数据库名称
SELECT DATABASE ();
-- 使用指定的数据库
USE 数据库名;
4.1.2、表操作
操作表之前，一定要先使用USE 数据库名;操作，再去操作表。
（1）创建表
-- 创建一个新的表
CREATE TABLE 表名 (
字段名1 字段类型1,
字段名2 字段类型2,
.....
字段名n 字段类型n
);
-- 快速创建一个表结构相同的表
CREATE TABLE 新表名 LIKE 旧表名;
例如，创建users表包含id，name，birthday字段：
CREATE TABLE users (
id int, -- 整数
name varchar(20), -- 字符串
birthday data -- 生日，最后没有逗号
);
（2）查看表
-- 查看数据库中的所有表
SELECT DATABASE ();
-- 查看表结构
DESC 表名;
-- 查看创建表的SQL语句
SHOW CREATE TABLE 表名;
（3）修改表结构
-- 修改表名
RENAME TABLE 表名 TO 新表名;
-- 添加表列 ADD
ALTER TABLE 表名 ADD 列名 类型;
-- 修改列名 CHANGE
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
-- 修改列类型 MODIFY
ALTER TABLE 表名 MODIFY 列名 新的类型;
-- 删除列 DROP
ALTER TABLE 表名 DROP 列名;
（4）删除表
-- 直接删除表
DROP TABLE 表名;
-- 判断表是否存在，如果存在则删除表
DROP TABLE IF EXISTS 表名;
4.2、DML：操作表中数据
DML主要用于对表中的记录进行增删改操作。
4.2.1、插入数据
格式：
INSERT [INTO] 表名 [字段名] VALUES (字段值)
其中INSERT INTO 表名表示往哪张表中添加数据，(字段名 1,字段名 2, …)表示要给哪些字段设置值，VALUES (值1,值2,…)表示设置具体的值。
-- 插入全部字段：所有的字段名都写出来
INSERT INTO 表名 (字段名1, 字段名2, 字段名3,...) VALUES (值1, 值2, 值3,...);
-- 不写字段名
INSERT INTO 表名 VALUES (值1, 值2, 值3,...);
-- 插入部分数据
INSERT INTO 表名 (字段名1, 字段名3,...) VALUES (值1, 值3,...);
Tips：没有添加数据的字段会使用 NULL
insert的注意事项：
- 插入的数据应与字段的数据类型相同。
- 数据的大小应在列的规定范围内，例如不能将长度为 80 的字符串加入到长度为 40 的列中。
- 在values中列出的数据位置必须与被加入的列的排列位置相对应。在MySQL中可以使用value，但不建议使用，功能与 values 相同。
- 字符和日期型数据应包含在单引号中。MySQL中也可以使用双引号做为分隔符。
- 不指定列或使用 null，表示插入空值。
4.2.2、更新数据
格式：
UPDATE 表名 SET 列名=值 [WHERE 条件表达式]
其中UPDATE 表名表示需要更新的表名，SET表示需要修改的列值，WHERE表示设符合条件的数据才会被更新。可以同时更新一个或多个字段，可以再WHERE字句中指定任何条件。
-- 不带条件修改数据
UPDATE 表名 SET 字段名=值; -- 修改所有的行
-- 带条件修改数据
UPDATE 表名 SET 字段名=值 WHERE 字段名=值;
4.2.3、删除数据
格式：
DELETE FROM 表名 [WHERE 条件表达式]
如果没有指定 WHERE子句，MySQL 表中的所有记录将被删除。可以在 WHERE 子句中指定任何条件。
-- 不带条件删除数据
DELETE FROM 表名;
-- 带条件删除数据
DELETE FROM 表名 WHERE 字段名=值;
-- 使用 truncate 删除表中所有记录
TRUNCATE TABLE 表名; -- 删除表的结构，再创建一张表
4.3、DQL：查询表中数据
格式：
SELECT 列名 FROM 表名 [WHERE 条件表达式]
查询不会对数据库中的数据进行修改，只是一种显示数据的方式。SELECT命令可以读取一行或多行记录。可以使用*号来代替所有字段，可以使用WHERE语句来包含任何条件。
4.3.1、简单查询
-- 查询表所有行和列的数据
SELECT * FROM 表名;
-- 查询指定列
SELECT 字段名1, 字段名2, 字段名3,... FROM 表名;
4.3.2、指定列的别名进行查询
使用别名的好处：显示的时候使用新的名字，并不修改表的结构。别名可以用于多表查询操作。
-- 对列指定别名
SELECT 字段名1 AS 别名, 字段名2 AS 别名,... FROM 表名;
-- 对列和表同时指定别名
SELECT 字段名1 AS 别名, 字段名2 AS 别名,... FROM 表名 AS 表别名;
4.3.3、清除重复值
-- 查询指定列并且结果不出现重复数据
SELECT DISTINCT 字段名 FROM 表名;
4.3.4、查询结果参与运算
-- 某列数据和固定值运算
SELECT 列名1 + 固定值 FROM 表名;
-- 某列数据和其他列数据参与运算
SELECT 列名1 + 列名2 FROM 表名;
Tips：参与运算的必须是数值类型！
4.3.5、查询条件
为什么要条件查询？ 如果没有查询条件，则每次查询所有的行。实际应用中，一般要指定查询的条件。对记录进行过滤。
语法：
SELECT 字段名 FROM 表名 WHERE 条件;
流程：取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回。
（1）运算符：
-- 查询某列的值大于80的数据
SELECT * FROM 表名 WHERE 列名>80;
-- 查询某列的值不等于80的数据,两种写法
SELECT * FROM 表名 WHERE 列名<>80;
SELECT * FROM 表名 WHERE 列名!=80;
（2）逻辑运算符：
-- 查询满足两个条件
SELECT * FROM 表名 WHERE 条件1 and 条件2;
-- 查询多个条件其中一个满足
SELECT * FROM 表名 WHERE 条件1 or 条件2 or 条件3;
（3）IN关键字：
SELECT 字段名 FROM 表名 WHERE 字段 in ( 数据1, 数据2...);
in里面的每个数据都会作为一次条件，只要满足条件的就会显示。
-- 查询字段是1或3或5的数据
SELECT 字段名 FROM 表名 WHERE 字段 in (1,3,5);
-- 查询字段不是1或3或5的数据
SELECT 字段名 FROM 表名 WHERE 字段 not in (1,3,5);
（4）范围查询：
BETWEEN 值1 AND 值2 -- 表示从值1到值2的范围，包头又包尾
-- 查询某字段大于等于10，且小于等于20的数据
SELECT 字段名 FROM 表名 WHERE 字段 BETWEEN 10 AND 20;
（5）LIKE关键字：
-- LIKE表示模糊查询
SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
-- 查询某字段以a开头的数据
SELECT * FROM 表名 WHERE 字段名 LIKE 'a%';
-- 查询某字段包含a的数据
SELECT * FROM 表名 WHERE 字段名 LIKE '%a%';
-- 查询某字段以a开头,且只有2个字符的数据
SELECT * FROM 表名 WHERE 字段名 LIKE 'a_';
（6）排序：
通过ORDER BY子句，可以将查询出的结果进行排序。（排序只是显示方式，不会影响数据库中数据的顺序）
-- ASC：升序，默认值。 DESC：降序
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
单列排序： 只按某一个字段进行排序，单列排序。
组合排序： 同时对多个字段进行排序，如果第 1 个字段相等，则按第 2 个字段排序，依次类推。
-- ASC：升序，默认值。 DESC：降序
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];
（7）聚合函数：
之前做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值 NULL。
| SQL中的聚合函数 | 作用 |
|---|---|
| max(列名) | 求这一列的最大值 |
| min(列名) | 求这一列的最小值 |
| avg(列名) | 求这一列的平均值 |
| count(列名) | 统计这一列有多少条记录 |
| sum(列名) | 求这一列的综合 |
-- 聚合函数语法
SELECT 聚合函数(列名) FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
我们发现对于 NULL的记录不会统计，建议如果统计个数则不要使用有可能为 null 的列，但如果需要把 NULL也统计进去呢？
-- 如果列名不为空，返回这列的值。如果为NULL,则返回默认值
IFNULL(列名，默认值)
-- 使用示例:查询user表中id字段，如果为null，则使用0代替
SELECT IFNULL(id,0) FROM users;
可以利用IFNULL()函数，如果记录为 NULL，给个默认值，这样统计的数据就不会遗漏。
-- 如果列名不为空，返回这列的值。如果为NULL,则返回默认值
SELECT COUNT(IFNULL(id,0)) FROM users;
（8）分组：
分组查询是指使用GROUP BY语句对查询信息进行分组，相同数据作为一组。
SELECT 字段1, 字段2,... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
GROUP BY 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。分组的目的就是为了统计，一般分组会跟聚合函数一起使用。
-- 按性别进行分组，求男生和女生数学的平均分
SELECT sex,AVG(math) FROM student GROUP BY sex;
| 子句 | 作用 |
|---|---|
| WHERE 子句 | ①对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，先过滤再分组 ②where后面不可以使用聚合函数 |
| HAVING 子句 | ①having子句的作用就是筛选满足条件的组，即在分组之后过滤数据，先分组再过滤 ②having后面可以使用聚合函数 |
（9）分页查询：limit
LIMIT 是限制的意思，所以 LIMIT 的作用就是限制查询记录的条数，即我们常用的分页查询。offset表示起始行数，从0开始计数，如果省略则默认为0；length指返回数据的行数。
-- LIMIT语法格式：
LIMIT offset,length;
4.4、DCL：设置用户权限
我们现在默认使用的都是 root 用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。
4.4.1、创建用户
-- 语法
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
| 描述 | |
|---|---|
| 用户名 | 将创建的用户名 |
| 主机名 | 指定该用户在哪个主机上可以登陆，如果是本地用户可用 localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符% |
| 密码 | 该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器 |
具体操作：
-- 创建user1用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为123。
create user 'user1'@'localhost' identified by '123';
-- 创建 user2 用户可以在任何电脑上登录 mysql 服务器，密码为 123
create user 'user2'@'%' identified by '123';
创建的用户名都可以在MySQL数据库中的user表中可以查看到，且密码经过了加密。
4.4.2、给用户授权
创建用户成功后，此用户是没有什么权限的，需要给用户进行授权。
-- 语法
GRANT 权限1,权限2,...ON 数据库名.表名 TO '用户名'@'主机名';
| 描述 | |
|---|---|
| GRANT…ON…TO | 授权关键字 |
| 权限 | 授予用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。如果要授予所有的权限则使用 ALL |
| 数据库名. 表名 | 该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.* |
| ’ 用户名’@’ 主机名’ | 给哪个用户授权，注：有 2 对单引号 |
具体操作：
-- 给 user1 用户分配对 test 这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询
grant create,alter,insert,update,select on test.* to 'user1'@'localhost';
-- 给 user2 用户分配所有权限，对所有数据库的所有表
grant all on *.* to 'user2'@'%';
用户名和主机名要与上面创建的相同，要加单引号。
4.4.3、撤销授权
-- 语法
REVOKE 权限1,权限2,...ON 数据库.表名 revoke all on test.* from 'user1'@'localhost';'用户名'@'主机名';
| 描述 | |
|---|---|
| REVOKE…ON…FROM | 撤销授权的关键字 |
| 权限 | 用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。所有的权限则使用 ALL |
| 数据库名. 表名 | 对哪些数据库的哪些表，如果要取消该用户对所有数据库和表的操作权限则可用*表示，如*.* |
| ’ 用户名’@’ 主机名’ | 给哪个用户撤销 |
具体操作：
-- 撤销 user1 用户对 test 数据库所有表的操作的权限
revoke all on test.* from 'user1'@'localhost';
用户名和主机名要与上面创建的相同，要加单引号。
4.4.4、查看授权
-- 语法
SHOW GRANTS FOR '用户名'@'主机名';
具体操作：
4.4.5、删除用户
-- 语法
DROP USER '用户名'@'主机名';
具体操作：
-- 删除 user2
drop user 'user2'@'%';
4.4.6、修改密码
-- 修改root管理员密码
mysqladmin -uroot -p password 新密码
-- 修改普通用户密码
set password for '用户名'@'主机名' = password('新密码');
需要在未登陆 MySQL 的情况下操作，新密码不需要加上引号。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
