本文详细介绍了MySQL8.2版本的基础操作，包括SQL的DDL（数据定义语言）、DML（数据操作语言）、DQL（数据查询语言）、DCL（数据控制语言），以及用户管理、权限控制、函数和约束等内容，同时提到了可视化工具Navicat15的辅助作用。
MySQL版本：mysql8.2 可视化工具：Navicat15
一、sql基础
sql语法分类：
1.DDL数据定义语言
1.1数据库操作
#查询所有数据库
show databases;
#查询当前数据库
select database();
#创建数据库 create database if not exists 数据库名 charset=字符集;
create database if not exists mydb1 charset=utf8;
#切换 (选择要操作的) 数据库
use mydb1;
#修改数据库编码
alter database mydb1 character set utf8mb4;
#删除数据库
drop database if exists mydb1;1.2表创建
#创建表
create table if not exists tb_user (
ID int comment '编号',
name varchar(50) comment '姓名',
age int comment '年龄',
gender varchar(1) comment '性别'
)comment '用户表';1.3表查询
#查询当前数据库所有表
show tables;
#查询表结构
desc tb_user; 
#查询指定表的建表语句
show create table tb_user;
1.4mysql数据类型
分为数值类型、字符串类型、日期类型。
decimal(M,D)：M：精度，数值的总位数 D：标度，小数点后的位数（举例：123.66，M=5，D=2）
举例：
定义年龄，年龄无负数：age TINYINT UNSIGNED
定义一位小数的分数：score DOUBLE(4,1)
char(10)：即使只存了一个字符，也占用10个字符的空间，性能好
varchar(10)：存了一个字符即占用1个字符的空间，性能较差
CREATE TABLE EMPLOYEE_MSG(
ID INT UNSIGNED COMMENT'编号',
WORKNO VARCHAR(10) COMMENT'员工工号',
NAME VARCHAR(10) COMMENT'员工姓名',
GENDER CHAR(1) COMMENT'性别',
AGE TINYINT UNSIGNED COMMENT'年龄',
IDCARD CHAR(18) COMMENT'身份证号',
JOINDATE DATE COMMENT'入职时间'
) comment '员工信息表';1.5表结构修改
#在指定表的指定位置添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [AFTER/BEFORE 字段名] [COMMENT 注释] [约束];
#修改字段的数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
#修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
#修改表名
ALTER TABLE 表名 RENAME TO 新表名;
----------------------------------------
#删除字段
ALTER TABLE 表名 DROP 字段名;
#删除表
DROP TABLE [IF EXISTS] 表名;
#删除指定表，并重新创建该表，适用于清理表数据
TRUNCATE TABLE 表名;2.DML数据操作语言 增删改
2.1插入
2.1.1给指定表插入数据
#给指定字段添加数据
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,...); -- 全部字段-- 举例
INSERT INTO employee(ID,NAME) VALUES(1,'佟湘玉'),(2,'白展堂'),(3,'郭芙蓉');
INSERT INTO employee VALUES
(4,'EMP004','吕秀才','男','25','EMP004','1995-01-01','吕轻侯'),
(5,'EMP005','李大嘴','男','29','EMP005','1995-01-01','李秀莲'),
(6,'EMP006','莫小贝','女','10','EMP006','1993-01-01','小贝'); 
2.1.2将表1数据插入到表2
方法一 INSERT INTO SELECT：
INSERT INTO Table2(field1,field2,…) SELECT value1,value2,… FROM Table1; -- 插入指定字段
INSERT INTO Table2 SELECT * FROM Table1; -- 插入全部字段*注意：要求Table2 已存在
方法二 SELECT INTO：
SELECT vale1, value2 INTO Table2 FROM Table1; -- 指定字段
SELECT * INTO Table2 FROM Table1; -- 全部字段*注意：要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中
- 指定字段顺序与值的顺序一一对应
- 字符串、日期型数据包含在单引号中
- 插入数据的大小在字段的规定范围内
2.2修改
UPDATE 表名 SET 字段名1=值1,字段名2=值2,... [WHERE 条件];
-- 举例
UPDATE employee SET WORKNO='EMP001',GENDER='女',AGE='30',IDCARD='EMP001',NICKNAME='佟掌柜' WHERE ID=1;
UPDATE employee SET JOINDATE='1993-01-01' WHERE JOINDATE IS NULL;
2.3删除
DELETE FROM 表名 [WHERE 条件];*注意：
- 没有条件则会删除整张表的所有数据
- DELETE语句不能删除某一个字段的值（可以使用UPDATE，如：UPDATE employee SET NICKNAME=NULL WHERE ID=6;）
3.DQL数据查询语言
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
3.1基础查询
#查询指定字段
SELECT 字段1,字段2,... FROM 表名;
#查询所有字段
SELECT * FROM 表名;
#设置别名
SELECT 字段1 [AS 别名1],字段2 [AS 别名2],... FROM 表名;
#去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;3.2条件查询 WHERE
SELECT 字段列表 FROM 表名 WHERE 条件列表;运算符： mysql支持4种运算符：1.算数运算符 2.比较运算符 3.逻辑运算符 4.位运算符
*LIKE：模糊匹配（_匹配单个字符，%匹配任意个字符）
举例：
#查询姓名为两个字的员工信息
SELECT * FROM EMP WHERE NAME LIKE '__';
#查询身份证号最后一位为X的员工信息
SELECT * FROM EMP WHERE IDCARD LIKE '%X';
#查询第二个字为'蔻'的所有商品
select * from product where pname like '_蔻%';
3.3排序查询 ORDER BY
SELECT 字段名1,字段名2,……
FROM 表名
ORDER BY 字段名1 [asc|desc],字段名2[asc|desc]……
- asc 升序，desc 代表降序，如果不写则默认升序
- order by 用于子句中可以支持单个字段，多个字段，表达式，函数，别名
- order by 子句，放在查询语句的最后面，LIMIT子句除外
3.4聚合查询 COUNT MAX MIN AVG SUM
SELECT 聚合函数(字段列表) FROM 表名;
- null值不参与所有聚合函数运算
3.5分组查询 GROUP BY
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];where 与 having 的区别
- 判断时机不同：where 是分组前进行过滤，不满足 where 则不参与分组；having 是分组后对结果进行过滤
- 判断条件不同：where 不能对聚合函数进行判断，而 having 可以
注意：
- 执行顺序：where > 聚合函数 > having
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义（以下举例）
举例：根据性别分组统计男员工和女员工的数量，若写为：select name,gender,count(*) from emp group by gender，则查询结果的 name 字段会取每个分组的第一个值，无任何意义
#根据性别分组，统计男性员工 和 女性员工 的数量
select gender,count(*) as 人数 from employee group by gender having gender is not null;
select gender,count(*) as 人数 from employee where gender is not null group by gender;
##根据性别分组，统计男性员工 和 女性员工 的平均年龄
select gender,round(avg(age),1) as 平均年龄 from employee where gender is not null group by gender;
#查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress,count(*) as address_count from emp where age<45 group by workaddress having address_count>=3;3.6分页查询 LIMIT
#显示前n条
SELECT 字段列表 FROM 表名 LIMIT n;
#分页显示
SELECT 字段列表 FROM 表名 LIMIT m,n;
-- m: 整数，表示从第几条索引开始，计算方式 m =（当前页-1）*每页显示条数
-- n: 整数，表示查询多少条数据
- 可以理解为：从第 n 条开始，显示 m 条记录
- 分页查询不同的数据库有不同的实现，MySQL 中是LIMIT
- 如果查询的是第一页的数据，起始索引可以忽略，直接简写为：LIMIT 查询记录数
#查询第1页员工数据，每页展示10条记录
SELECT * FROM EMP LIMIT 10;
#查询第2页员工数据，每页展示10条记录
SELECT * FROM EMP LIMIT 10,10;3.7案例练习
-- 1．查询年龄为20,21,22,23岁的女性员工信息。
SELECT *
FROM EMPLOYEE
WHERE AGE IN(20,21,22,23) AND GENDER='女';
-- 2．查询性别为男，并且年龄在20-40岁(含)以内的姓名为三个字的员工。
SELECT *
FROM EMPLOYEE
WHERE GENDER='男' && (AGE BETWEEN 20 AND 40) && NAME LIKE '___';
-- 3．统计员工表中,年龄小于60岁的，男性员工和女性员工的人数。
SELECT GENDER,COUNT(ID)
FROM EMPLOYEE
WHERE AGE<60
GROUP BY GENDER;
-- 4．查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
SELECT NAME,AGE
FROM EMPLOYEE
WHERE AGE<=35
ORDER BY AGE ASC,JOINDATE DESC;
-- 5、查询性别为男，且年龄在20-40岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
SELECT *
FROM EMPLOYEE
WHERE GENDER='男' && AGE BETWEEN 20 AND 40
ORDER BY AGE ASC,JOINDATE DESC
LIMIT 5;4.DCL数据控制语言
4.1用户管理
#查询用户
USE MYSQL;
SELECT * FROM USER;
#创建用户
USE MYSQL;
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
#修改用户密码
USE MYSQL;
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
#删除用户
USE MYSQL;
DROP USER '用户名'@'主机名';*创建用户后，默认未分配权限，用户只有登录权限，无法查看到具体有哪些数据库。
案例练习：
-- 创建用户 iteast，只能够在当前主机localhost访间，密码123456
USE MYSQL;
CREATE USER 'iteast'@'localhost' IDENTIFIED BY '123456';
-- 创建用户heima ，可以在任意主机访问该数据库，密码123456
CREATE USER 'heima'@'%' IDENTIFIED BY '123456';
-- 修改用户 heima的访问密码为1234
ALTER USER 'heima'@'%' IDENTIFIED WITH mysql_native_password BY '1234';
-- 删除itcast@localhost用户
DROP USER 'heima'@'%'; 4.2权限控制
#查询权限
SHOW GRANTS FOR '用户名'@'主机名';
#授权权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'; -- 授权指定数据库指定表权限
GRANT 权限列表 ON *.* TO '用户名'@'主机名'; -- 授权所有数据库的所有表权限
#撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'; -- 撤销指定数据库指定表权限
REVOKE 权限列表 ON *.* FROM '用户名'@'主机名'; -- 撤销所有数据库所有表权限5.函数
5.1字符串函数
5.2数值函数
5.3日期函数
5.4流程函数
MySQL函数大全：http://t.csdnimg.cn/G1grE
6.约束
概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
使用方式：在创建/修改表的时候添加约束
6.1常见的约束分类
*自增：auto_increment
练习：
create table 0421A(
ID int PRIMARY KEY auto_increment comment 'ID唯一标识',
name varchar(10) UNIQUE not null comment '姓名',
age int CHECK(age>0 and age<=120) comment '年龄',
status char(1) DEFAULT '1' comment '状态',
GENDER char(1) comment '性别'
) comment '练习建表约束';
-- 勤写备注，在show create table 时也可以把备注查出来6.2外键约束
说明：如果上述两张表已建立外键关联，删除部门表中ID=1的研发部则不会成功，因为员工表中还存在人员属于该部门。
-
添加/删除外键的语法
#建表时添加
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
#修改表添加
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
ALTER TABLE employee ADD CONSTRAINT fk_emp_dep_id FOREIGN KEY(DEPARTMENT_ID) REFERENCES department(ID);
#删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;如果存在外键的情况下删除父表数据，提示如下：
外键名称为自定义名称，可根据此规则命名：fk_子表简称_父表简称_外键字段名
-
外键的删除/更新行为
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREINGN KEY(外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;在可视化界面中，可直接操作修改：
扫一扫
617
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
