Mysql数据库基础（二）

SQL概述

SQL的通用语法：

语句可以单行或多行书写，以分号结尾
可以通过空格/缩进来增强语句的可读性，空格可以是多个
MySQL数据库的SQL语句不区分大小写，但关键字建议使用大写区分开来

SQL的分类：

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：数据库、表、字段等
DML（Data Manipulation Language）：数据操作语言，用来对数据库表中的数据进行增删改
DCL（Data Control Language）：数据控制语言，用来创建数据库用户、控制数据库的访问权限
DQL（Data Query Language）：数据查询语言，用来查询数据库中表的记录

DDL

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：数据库、表、字段等

数据库操作：

查询

-- 查询所有的数据库
SHOW DATABASES;

-- 查询当前的数据库
SELECT DATABASE();

创建

1 2	-- 创建数据库 CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

utf8 长度只有三个字符，有一些特殊的字符有四个字符，可以使用utf8mb4

删除

1 2	-- 删除数据库 DROP DATABASE [IF EXISTS] 数据库名;

使用
1
USE 数据库名;

表操作：

创建

-- 创建表
CREATE TABLE 表名(
    字段1 字段1类型[COMMENT 字段1注释],
    字段2 字段2类型[COMMENT 字段2注释]
)[COMMENT 表注释];

注意：最后一个字段后面没有逗号；在Mysql中字符串是varchar(长度)

查询

-- 查询当前数据库所有表
SHOW TABLES;

-- 查看表结构
DESC 表名;

-- 查询指定表的建表语句
SHOW CREATE TABLE 表名;

在这里就需要提及Mysql的数据类型了，其主要分为三类：数值类型、字符串类型、日期时间类型

数值类型

例如 123.45，那么它的精度(M)则是它的长度5，标度(D)则是它的小数点后位数2
字符串类型
- 二进制数据例如文件、压缩包等，一般都会有一个专门的文件服务器来存储，因为存储在数据库中会影响其性能
- 变长字符串会根据你的存储内容来计算占用空间，性能较差；定长字符串则都会占用指定的空间，性能较好
日期时间类型

添加字段

1 2	-- 创建表 ALTER TABLE 表名 ADD 字段名字段类型 [COMMENT 注释] [约束];

修改字段

-- 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型;

-- 修改字段名和其数据类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [COMMENT 注释] [约束];

删除字段

1 2	-- 删除字段 ALTER TABLE 表名 DROP 字段名;

修改表名

1 2	-- 修改表名 ALTER TABLE 表名 RENAME TO 新表名;

删除表

-- 删除表
DROP TABLE [IF EXISTS] 表名;

-- 只删除表中的数据
TRUNCATE TABLE 表名;

DML

DML（Data Manipulation Language）：数据操作语言，用来对数据库表中的数据进行增删改

添加数据
1
2
3
4
5
-- 给指定字段添加数据
INSERT INTO 表名 (字段1,字段2,...) VALUES(值1,值2,...);

-- 给所有字段添加数据
INSERT INTO 表名 VALUES(值1,值2,...);
1. 如果想批量添加数据，可以在关键字VALUES后面再增加,(值1,值2,…)【以逗号分隔开】
2. 插入的数据要一一对应且注意该字段的数据类型

更新数据

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

没有添加条件则会更新所有数据！

删除数据
1
DELETE FROM 表名 [WHERE 条件];
没有添加条件则会删除所有数据！同时delete是不能删除一个字段的值的

数据库操作最基本也是最重要的是查询操作

DQL

DQL（Data Query Language）：数据查询语言，用来查询数据库中表的记录

基本查询

-- 查询指定字段
SELECT 字段1 [AS 别名1],字段2 [AS 别名2],... FROM 表名;

-- 查询所有
SELECT * FROM 表名;

-- 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

条件查询

1	SELECT 字段列表 FROM 表名 WHERE 条件列表;

聚合函数

将一列数据作为一个整体，进行纵向计算。聚合函数一般配合分组查询

聚合函数是作用于字段的，一般NULL值是不参与任何聚合函数的运算

分组查询

1	SELECT 字段列表 FROM 表名 [WHERE 条件列表] GROUP BY 分组字段名 [HAVING 分组后过滤的条件];

WHERE 和 HAVING 的区别：

执行时机不同：where 是在分组之前进行过滤，不满足 where 条件的不参与分组；而 having 是在分组之后对结果进行过滤
判断条件不足：where 不能对聚合函数进行判断，而 having 可以

需要注意的是：

执行顺序：where > 聚合函数 > having
分组之后，查询的字段一般是聚合函数和分组字段，查询其他字段毫无意义

排序查询

1	SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2;

默认排序方式是 升序（ASC）。同时还有一种排序方式，即 降序（DESC）

当进行多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

分页查询

1	SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询记录数;

起始索引的计算方式：（查询页码 - 1） * 每页显示记录数。当查询的是第一页数据时，起始索引可以省略掉

分页查询是是数据库的方言，即不同数据库有不同的实现方式，比如 Mysql 就是 LIMIT 的了

案例练习

创建一个 emp（员工）表：

CREATE TABLE emp (
	id INT COMMENT '编号',
	workno VARCHAR ( 10 ) COMMENT '工号',
	NAME VARCHAR ( 10 ) COMMENT '名字',
	gender CHAR COMMENT '性别',
	age TINYINT UNSIGNED COMMENT '年龄',
	idcard CHAR ( 18 ) COMMENT '身份证号',
	workaddress VARCHAR ( 50 ) COMMENT '工作地址',
entrydate date COMMENT '入职时间' 
) COMMENT '员工表';

插入相应的数据：

INSERT INTO emp ( id, workno, NAME, gender, age, idcard, workaddress, entrydate )
VALUES
	( 1, 1, '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01' ),
	( 2, 2, '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01' ),
	( 3, 3, '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01' ),
	( 4, 4, '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01' ),
	( 5, 5, '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01' ),
	( 6, 6, '杨道', '男', 28, '12345678931234567X', '北京', '2006-01-01' ),
	( 7, 7, '范骚', '男', 40, '123456789212345670', '北京', '2005-05-01' ),
	( 8, 8, '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01' ),
	( 9, 9, '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01' ),
	( 10, 10, '陈友凉', '男', 53, '123456789012345670', '上海', '2011-01-01' ),
	( 11, 11, '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01' ),
	( 12, 12, '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01' ),
	( 13, 13, '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01' ),
	( 14, 14, '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01' ),
	( 15, 15, '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01' ),
	( 16, 16, '周芷若', '女', 18, NULL, '北京', '2012-06-01' );

实际练习：

-- 查询年龄为20,21,22,23的员工信息
select * from emp WHERE age IN(20,21,22,23);

-- 查询性别男，年龄在20~40岁(含)以内的姓名为三个字的员工信息
select * from emp WHERE gender = '男' && age BETWEEN 20 AND 40 && name like '___';

-- 统计员工表中，年龄小于60的男女员工人数
select gender,COUNT(*) from emp WHERE age < 60 GROUP BY gender;

-- 查询年龄小于等于35岁的员工信息，并根据其年龄进行升序，如果年龄相同，则按照入职时间降序
SELECT * from emp where age <= 35 ORDER BY age,entrydate DESC;

-- 查询年龄在20~40岁(含)以内的前5个男员工信息，并根据其年龄进行升序，如果年龄相同，则按照入职时间降序
select * from emp where gender = '男' && age BETWEEN 20 and 40 ORDER BY age,entrydate desc LIMIT 5;

SELECT 的多种查询方式是有先后执行顺序的，具体如下图：

DCL

DCL（Data Control Language）：数据控制语言，用来管理数据库用户、控制数据库的访问权限

管理用户

-- 查询用户，用户信息存储在系统数据库mysql的user表中
use mysql;
select * from user;

-- 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

-- 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

-- 删除用户
DROP USER '用户名'@'主机名';

当主机名为 % 时，则表示在任意主机都可以访问到

新创建的用户默认是没有任何权限的，当前只能访问到information_schema该数据库

这类SQL开发人员操作的比较少，主要是DBA（Database Administrator 数据库管理员）会使用

权限控制

在mysql中定义了很多种权限，但是常用的就以下几种：

-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';

-- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

-- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

授予多个权限时，多个权限之间以逗号分隔开；授权时数据库和表名以*表示，则代表所有