MySQL—— 概述 SQL语句

数据库相关概念

MySQL数据库

MySQL数据库是一种关系型数据库(RDBMS)
关系型数据库是建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

特点：

1.使用表存储数据，格式统一，便于维护
2.使用SQL语言操作，标准统一，使用方便    

数据模型：

客户端使用SQL语句，通过数据库管理系统创建数据库，也可以在指定的数据库中创建表

一个数据库服务器可以创建多个数据库，一个数据库中可以创建多个表

SQL

SQL通用语法

1.SQL语句可以单行或多行书写，以分号结尾
2.SQL语句可以使用空格或缩进来增强语句的可读性
3.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
4.注释:
单行注释：-- 注释内容 或 #注释内容(MySQL特有)
多行注释：/* 注释内容 */

SQL分类

DDL-数据库操作

创建
CREATE DATABASE [IF NOT EXISTS] 数据库名

[DEFAULT CHARSET 字符集] [COLLATE 排序规则]；

-- 创建了名为test的数据库
CREATE DATABASE TEST;

IF NOT EXISTS：如果test数据库不存在就创建，存在则不创建

DEFAULT CHARSET：设定字符集

COLLATE 排序规则：设定排序规则

查询所有数据库
SHOW DATABASES;

SHOW DATABASES;

其中test是自己创建的，其余的是系统自带的

使用
USE 数据库名; 

-- 使用test数据库
USE TEST;

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

SELECT DATABASE();

USE TEST; 使用test数据库，那么当前数据库就是test

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

-- 删除名为test的数据库
DROP DATABASE TEST;

IF EXISTS：如果test数据库存在就删除，不存在则不删除

DDL-表操作

数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型

数值类型：

字符串类型：

CHAR(10)表示字符串长度为10，不足10用空格补齐，性能较好

VARCHAR(10)表示字符串最大长度为10，不足10为实际长度，性能较差

日期类型：

创建

创建表

CREATE TABLE 表名（
字段1 字段1类型 [COMMENT 字段1注释]，

字段2 字段2类型 [COMMENT 字段2注释]，

字段3 字段3类型 [COMMENT 字段3注释]，

                ...

字段n 字段n类型 [COMMENT 字段n注释]

        ）[COMMENT 表注释];

CREATE TABLE student(Sno INT COMMENT "学号" ,Sname VARCHAR(5) COMMENT "姓名" ,Stime DATE COMMENT "入学时间"
)COMMENT "学生信息表";

查询

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

SHOW TABLES;

得到刚才创建的student表

查询表结构
DESC 表名;

-- 查询student表的表结构
DESC student;

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

-- 查询student表的建表语句
SHOW CREATE TABLE student;

修改

添加字段
ALTER TABLE 表名 ADD 字段名类型(长度) [COMMENT 注释][约束];

-- 为student表增加一个新的字段”昵称”为nickname，类型为varchar(20)
ALTER TABLE student ADD nickname varchar(20) COMMENT "昵称";

修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

-- 将student表Sname的数据类型改为VARCHAR(10)
ALTER TABLE student MODIFY Sname VARCHAR(10);

修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释][约束];

-- 将emp表的nickname字段修改为username，类型为varchar(10)
ALTER TABLE student CHANGE nickname username varchar(10) COMMENT"昵称";

删除字段
ALTER TABLE 表名 DROP 字段名;

-- 将student表的字段username删除
ALTER TABLE student DROP username;

修改表名
ALTER TABLE 表名 RENAME TO 新表名;

-- 将student表的表名修改为stu
ALTER TABLE student RENAME TO stu;

删除

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

DROP TABLE stu;

删除指定表，并重新创建该表
TRUNCATE TABLE 表名;

TRUNCATE TABLE stu;

注意:在删除表时，表中的全部数据也会被删除

DML-添加数据

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

INSERT INTO student (Sno, Sname, Stime) VALUE (1,'张三','2023-10-3');

给全部字段添加数据
INSERT INTO 表名 VALUES (值1,值2,...);

INSERT INTO student VALUE (2,'李四','2023-10-4');

批量添加数据
INSERT INTO 表名(字段名1,字段名2,...) VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...); INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);

INSERT INTO student VALUE (3,'王五','2023-10-5'),(4,'赵六','2023-10-6');

注意：
1.插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
2.字符串和日期型数据应该包含在引号中。
3.插入的数据大小，应该在字段的规定范围内。

DML-修改数据

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

-- 修改张三的入学时间为2023-10-10
UPDATE student SET Stime='2023-10-10' WHERE Sname='张三';

注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

DML-删除数据

DELETE FROM 表名「WHERE条件]；

-- 删除赵六的信息
DELETE FROM student WHERE Sname='赵六';

注意：
DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
DELETE 语句不能删除某一个字段的值(可以使用UPDATE将该字段的值设为NULL)。

DQL-表查询

基本查询
条件查询(WHERE)
聚合函数(count、max、min、avg、sum)

分组查询(GROUP BY)

排序查询(ORDER BY)

分页查询(LIMIT)

存在如下一张表：

基本查询

查询多个字段
SELECT 字段1,字段2,字段3 ... FROM 表名;

SELECT id,name FROM emp;

SELECT * FROM 表名;（查询所有字段）

SELECT * FROM emp;

设置别名
SELECT 字段1 [AS 别名1], 字段2 [AS 别名2] ... FROM 表名;

SELECT id AS '序号',name AS '姓名' FROM emp;

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

SELECT DISTINCT workaddress FROM emp;

条件查询

条件类型

语法
SELECT 字段列表 FROM表名 WHERE 条件列表;

-- 1.查询年龄等于88的员工
SELECT * FROM emp WHERE age=88;

-- 2.查询年龄小于20的员工信息
SELECT * FROM emp WHERE age<20;

-- 3.查询年龄小于等于20的员工信息
SELECT * FROM emp WHERE age<=20;

-- 4.查询没有身份证号的员工信息
SELECT * FROM emp WHERE idcard IS NULL;

-- 5.查询有身份证号的员工信息
SELECT * FROM emp WHERE idcard IS NOT NULL;

-- 6.查询年龄不等于88的员工信息
SELECT * FROM emp WHERE age!=88;
SELECT * FROM emp WHERE age<>88;

-- 7.查询年龄在15岁(包含)到20岁(包含)之间的员工信息
SELECT * FROM emp WHERE age>=15 AND age<=20;
SELECT * FROM emp WHERE age BETWEEN 15 AND 20;

-- 8.查询性别为女且年龄小于25岁的员工信息
SELECT * FROM emp WHERE gender='女' AND age<25;

-- 9.查询年龄等于18或20或40的员工信息
SELECT * FROM emp WHERE age=18 OR age=20 OR age=40;
SELECT * FROM emp WHERE age IN (18,20,40);

-- 10.查询姓名为两个字的员工信息
SELECT * FROM emp WHERE name LIKE '__';

-- 11.查询身份证号最后一位是X的员工信息
SELECT * FROM emp WHERE idcard LIKE '%X';

聚合函数

介绍
将一列数据作为一个整体，进行纵向计算

注意： null值不参与所有聚合函数运算

常见聚合函数

语法
SELECT 聚合函数(字段列表) FROM 表名;

-- 1.统计该企业员工数量
SELECT COUNT(*) FROM emp;

-- 2.统计该企业员工的平均年龄
SELECT AVG(age) FROM emp;

-- 3.统计该企业员工的最大年龄
SELECT MAX(age) FROM emp;

-- 4.统计该企业员工的最小年龄
SELECT MIN(age) FROM emp;

-- 5.统计西安地区员工的年龄之和
SELECT SUM(age) FROM emp WHERE workaddress='西安';

分组查询

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

-- 1.根据性别分组，统计男性员工和女性员工的数量
SELECT gender,COUNT(*) FROM emp GROUP BY gender;

-- 2.根据性别分组，统计男性员工和女性员工的平均年龄
SELECT gender,AVG(age) FROM emp GROUP BY gender;

-- 3.查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
SELECT workaddress,COUNT(*) FROM emp WHERE age<45 GROUP BY workaddress HAVING COUNT(*)>=3;

where与having区别
执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组。而having是分组之后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。

注意
执行顺序：where > 聚合函数 > having.
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

排序查询

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

-- 1.根据年龄对公司的员工进行升序排序
select * from emp order by age ASC;
select * from emp order by age;

-- 2.根据入职时间，对员工进行降序排序
select * from emp order by entrydate DESC;

-- 3.根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序
select * from emp order by age ASC,entrydate DESC;

排序方式
ASC：升序(默认值)    
DESC：降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

分页查询

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

-- 1.查询第1页员工数据，每页展示10条记录
select * from emp limit 0,10;
select * from emp limit 10;

-- 2.查询第2页员工数据，每页展示10条记录-------->(页码-1)*页展示记录数
select * from emp limit 10,10;

注意
1.起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数
2.分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LMIT
3.如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10

DQL语句执行顺序

DCL-管理用户

查询用户

USE mysql;
SELECT * FROM user;

USE mysql;
SELECT * FROM user;

默认在mysql数据库中有四个用户，我们之前使用的是root

Host是主机名，User是用户名

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

-- 创建用户 aaa，只能够在当前主机localhost访问，密码123456;
CREATE USER 'aaa'@'localhost' IDENTIFIED BY '123456';

-- 创建用户bbb，可以在任意主机访问该数据库，密码123456;
CREATE USER 'bbb'@'%' IDENTIFIED BY '123456';

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

-- 修改用户bbb的访问密码为1234
ALTER USER 'bbb'@'%' IDENTIFIED WITH mysql_native_password BY'1234';

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

-- 删除aaa@localhost用户
DROP USER'aaa'@'localhost';

注意
主机名可以使用%通配
这类SQL开发人员操作的比较少，主要是DBA(Database Administrator数据库管理员)使用

DCL-权限控制

创建了用户后，该用户还没有对数据库中表的权限，需要进行权限控制

只有拥有相应的权限，才能进行相应的操作

查询用户拥有的权限
SHOW GRANTS FOR ‘用户名'@'主机名';

SHOW GRANTS FOR 'bbb'@'%';

目前还没有任何权限

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

-- 给'bbb'@'%'用户授予数据库test中emp表的所有权限
GRANT ALL ON test.emp TO 'bbb'@'%';

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

-- 给'bbb'@'%'用户撤销数据库test中emp表的所有权限
REVOKE ALL ON test.emp FROM 'bbb'@'%';

注意
多个权限之间，使用逗号分隔
授权时，数据库名和表名可以使用 * 进行通配，代表所有。