学习MySQL仅此一篇就够了（SQL语句）

第一阶段 基础概述

• 关系型数据库

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库

特点：1.使用表存储数据，格式统一，便于维护

2.使用SQL语言操作，标准统一，使用方便

• 数据模型

数据库-->表

SQL通用语法

• SQL通用语法

1.SQL语句可以单行或多行书写，以分号结尾

2.SQL语句可以使用空格缩进增强语句的可读性

3.MYSQL数据库的SQL语句不区分大小写，关键字建议使用大小写

4.注释：

• 单行注释：--注释内容或 #注释内容

• 多行注释：/注释内容/

分类	说明
DDL	数据定义语句，用来定义数据对象
DML	数据操作语句，用来对数据库表中的数据进行增删改
DQL	数据查询语句，用来查询数据库中表的记录
DCL	数据控制语句，用来创建数据库用户，控制数据库的访问权限

DDL数据库操作

#查询
show atabases;
#查询当前数据库
select database();
#创建
create database 数据库名;
#删除
drop database 数据库名;
#使用
use 数据库名;

表操作

#查询当前数据库所有表
show tables;
#查询表结构
desc 表名;
#查询指定表的建表语句
show create table 表名;
#创建表
create table 表名(
	字段1 字段类型,
	字段2 字段类型,
	字段3 字段类型
);
eg:
create table stu(
    id int comment '编号',
    name varchar(50) comment '姓名',
    age int comment '年龄',
    gender varchar(1) comment '性别'
);

数据类型

在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型。

Text 类型：

数据类型	描述
CHAR(size)	保存固定长度的字符串（可包含字母、数字以及特殊字 符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size)	保存可变长度的字符串（可包含字母、数字以及特殊字 符）。在括号中指定字符串的最大长度。最多 255 个字 符。 注释：如果值的长度大于 255，则被转换为 TEXT 类型。
TINYTEXT	存放最大长度为 255 个字符的字符串。
TEXT	存放最大长度为 65,535 个字符的字符串。
BLOB	用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。
MEDIUMTEXT	存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。
LONGTEXT	存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.)	允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。 注释：这些值是按照你输入的顺序存储的。 可以按照此格式输入可能的值： ENUM('X','Y','Z')
SET	与 ENUM 类似， SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。

Number 类型：

数据类型	描述
TINYINT(size)	-128 到 127 常规。 0 到 255 无符号*。在括号中规定最 大位数。
SMALLINT(size)	-32768 到 32767 常规。 0 到 65535 无符号*。在括号中 规定最大位数。
MEDIUMINT(size)	-8388608 到 8388607 普通。 0 to 16777215 无符号*。在 括号中规定最大位数。
INT(size)	-2147483648 到 2147483647 常规。 0 到 4294967295 无 符号*。在括号中规定最大位数。
BIGINT(size)	-9223372036854775808 到 9223372036854775807 常规。 0 到18446744073709551615 无符号*。在括号中规定最大位 数。
FLOAT(size,d)	带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d)	带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d)	作为字符串存储的 DOUBLE 类型，允许固定的小数点。

注意：这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED属性，那么范围将从 0 开始，而不是某个负数。

Date 类型：

数据类型	描述
DATE()	日期。格式： YYYY-MM-DD 注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'
DATETIME()	日期和时间的组合。格式： YYYY-MM-DD HH:MM:SS 注释：支持的范围是'1000-01-01 00:00:00' 到 '9999-12- 31 23:59:59'
TIMESTAMP()	时间戳。 TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的描述来存储。格式： YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC
TIME()	时间。格式： HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59'
YEAR()	2 位或 4 位格式的年。 注释： 4 位格式所允许的值： 1901 到 2155。 2 位格式所允许 的值： 70 到69，表示从 1970 到 2069

 

#表操作-修改
#添加字段
alter table 表名 add 字段名 类型（长度） [comment注释] [约束];
alter table stu add nikename varchar(20) comment '昵称';
#修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);
#修改字段名和字段类型
alter table 表名 change 旧字段名 类型(长度) [comment注释] [约束];
alter table stu change nikename username varchar(20) comment '用户名'；
#删除字段
alter table 表名 drop 字段名;
alter table stu drop username;
#删除表
drop table 表名;
#删除指定表，并重新创建该表
truncate table stu;

DML数据库操作

• DML-添加数据

#给指定字段添加数据
insert into 表名(字段1，字段2.) values(值1，值2);
#给全部字段添加数据
insert into 表名 values(值1，值2);
#批量添加数据
insert into 表名(字段1，字段2) values(值1，值2)，(值1)；

insert into emp(id, worko, name, gender, age, idcard, workaddress, entrydate) values (9, '1231', '王宇璇', '男', 13, '123', '北京', '2021-1-1');
select * from emp;

• DML-修改数据

update 表名 set 字段1=值1，字段2=值2...[where 条件];

#修改id为1的数据，将name修改为sxx
update emp set name = 'sxx' where id = 2;

#修改id为1的数据，将name修改为sx，gender修改为男
update emp set name = 'sx', gender = '男' where id = 2;

#修改所有员工入职日期2023-1-1
update emp set entrydate = '2023-1-1';

• DML-删除数据

delete from 表名 [where 条件]
#删除性别为女的员工
delete from emp where gender = '女';
#删除全部员工
delete from emp;

DQL数据库操作

• DQL-语法

select * from emp where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数
#查询指定字段
select name from emp;
#查询所有字段
select * from emp;
#查询所有员工的工作地址，起别名
select workaddress as '工作地址' from emp;
#查询公司员工的上班地址！不重复
select distinct  workaddress as '工作地址' from emp;

• DQL-条件查询

#根据年龄查找员工
select * from emp where age = 10;
select * from emp where age > 10;
select * from emp where age < 10;
select * from emp where age ！= 10;
select * from emp where age > 1 && age < 10;

#查找没有身份证号的员工
select * from emp where idcard is null;

#查询有身份证号的员工
select * from emp where idcard is not null;

#查询性别为女且年龄小于30的员工
select * from emp where gender = '女' and age < 30;

#查询年龄为20，30，40的员工
select * from emp where age = 10 or age = 13 or  age = 23;

#查询三个字的员工
select * from emp where name like '___';

#查询身份证号最后一位为x的员工信息
select * from emp where idcard like '%x';

• DQL-聚合函数

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

#统计员工数量
select count(*) from emp;
select count(id) from emp;

#统计员工平均年龄
select avg(age) from emp;

#统计北京地区员工年龄之和
select sum(age) from emp where workaddress = '北京'; 

• DQL-分组查询

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

#根据员工性别进行分组
select gender, count(*) from emp group by gender;

#查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress, count(*) where age < 45 group by workaddress having count(*) >=3;

• DQL-排序查询

select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2;
-asc升序
-desc:降序

#根据年龄对公司的员工进行升序排序
select * from emp order by asc ;

#根据入职时间对员工进行降序排序
select * from emp order by entrydate desc ;

#根据年龄对公司员工进行升序排序，年龄相同，再按照入职时间降序排序
select * from emp order by age asc ,entrydate desc ;

• DQL-分页查询

select 字段列表 from 表名 limit 起始索引，查询记录数;

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

*起始索引是从0开始，起始索引=（查询页码-1）*每页显示记录数

DCL数据库操作

用来管理数据库用户，控制数据库的访问 权限。

• DCL-管理用户

1.查询用户
use mysql;
select * from user;
2.创建用户
create user '用户名'@'主机名' identified by '密码';
3.修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_passwd by '新密码';
4.删除用户
drop user '用户名'@'主机名';

#创建用户
use mysql;
select * from user;
create user 'wyx'@'localhost' identified by '123456';

#创建用户可以在任意主机访问该数据库
create user 'wyx'@'%' identified by '123456';

#修改密码
alter user 'wyx'@'localhost' identified  with mysql_native_password by '1234';

#删除用户
drop user 'wyx'@'localhost';

• DCL-权限控制

#查询权限
show grants for '用户名'@'主机名';
show grants for 'wyx'@'localhost';

#授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
grant all on mysql.* to 'wyx'@'localhost';

#撤销权限
remove 权限列表 on 数据库名.表名 from '用户名'@'主机名';
revoke all on mysql.* from 'wyx'@'localhost';