MYSQL基础操作

---

DDL

Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段) 。

对数据库操作

查询所有数据库
show databases;查询当前数据库
select database() ;创建数据库
create database 数据库名; 删除数据库
drop database 数据库名;切换数据库
use 数据库名;

对表操作

查询当前数据库所有表
show tables;查看指定表结构
desc 表名 ;查询指定表的建表语句
show create table 表名 ;创建表结构
CREATE TABLE 表名(字段1 字段1类型 [ COMMENT 字段1注释 ],字段2 字段2类型 [COMMENT 字段2注释 ],字段3 字段3类型 [COMMENT 字段3注释 ],......字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;删除表
DROP TABLE [ IF EXISTS ] 表名;删除指定表, 并重新创建表
TRUNCATE TABLE 表名;

修改表

添加字段
ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];删除字段ALTER TABLE 表名 DROP 字段名;修改表名
ALTER TABLE 表名 RENAME TO 新表名;

DML

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

添加数据

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

修改数据

修改数据的具体语法为:
UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

删除数据

删除数据的具体语法为：
DELETE FROM 表名 [ WHERE 条件 ] ;删除语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
DELETE FROM 表名 ;

DQL

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

基本语法

SELECT字段列表
FROM表名列表
WHERE条件列表
GROUP BY分组字段列表
HAVING分组后条件列表
ORDER BY排序字段列表
LIMIT分页参数

基础查询

查询多个字段
SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;
SELECT * FROM 表名 ;字段设置别名
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

条件查询

条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表 ;

聚合函数

SELECT 聚合函数(字段列表) FROM 表名 ;
NULL值是不参与所有聚合函数运算的。select count(*) from emp; -- 统计的是总记录数
select count(idcard) from emp; -- 统计的是idcard字段不为null的记录数

分组查询

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];where与having区别
-执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组
之后对结果进行过滤。
-判断条件不同：where不能对聚合函数进行判断，而having可以。根据性别分组 , 统计男性员工 和 女性员工的数量
select gender, count(*) from emp group by gender ;根据性别分组 , 统计男性员工 和 女性员工的平均年龄
select gender, avg(age) from emp group by gender ;查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from emp where age < 45 group by
workaddress having address_count >= 3;统计各个工作地址上班的男性及女性员工的数量
select workaddress, gender, count(*) '数量' from emp group by gender , workaddress;

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;ASC : 升序(默认值)
DESC: 降序如果是升序, 可以不指定排序方式ASC ;如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;

分页查询

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

DCL

DCL英文全称是Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。

用户管理

查询用户
select * from mysql.user;创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;删除用户
DROP USER '用户名'@'主机名' ;

权限控制

查询权限
SHOW GRANTS FOR '用户名'@'主机名' ;授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
目的：保证数据库中数据的正确、有效性和完整性。

约束演示

CREATE TABLE tb_user(id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',name varchar(10) NOT NULL UNIQUE COMMENT '姓名' ,age int check (age > 0 && age <= 120) COMMENT '年龄' ,status char(1) default '1' COMMENT '状态',gender char(1) COMMENT '性别'
);

外键约束

外键：用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

添加外键
CREATE TABLE 表名(字段名 数据类型,...[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

删除/更新行为

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES
主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

多表查询

多表关系

一对多

案例: 部门 与 员工的关系
关系: 一个部门对应多个员工，一个员工对应一个部门
实现: 在多的一方建立外键，指向一的一方的主键

多对多

案例: 学生 与 课程的关系
关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

案例: 用户 与 用户详情的关系
关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另
一张表中，以提升操作效率
实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

多表查询概述

连接查询内连接：相当于查询A、B交集部分数据外连接：左外连接：查询左表所有数据，以及两张表交集部分数据右外连接：查询右表所有数据，以及两张表交集部分数据自连接：当前表与自身的连接查询，自连接必须使用表别名

内连接

隐式内连接
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;显式内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

外连接

左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... 

自连接

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;例如 查询员工 及其 所属领导的名字
select a.name , b.name from emp a , emp b where a.managerid = b.id;查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid =
b.id;

联合查询

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );