MySQL

置顶数据库

发布时间 : 2020-03-19 21:51

字数:4.3k 阅读 :

概述

# 数据库DB：存储数据的仓库，数据是有组织的进行存储；
# 数据库管理系统DBMS：操纵和管理数据库的大型软件；
# SQL：操作关系型数据库的编程语言，定义了一套操作；

# 常见数据库：Oracle、MySQL、SQL Server
# mysql，旧版本以5.7为主流，新版本是8

database > table (column | row)

Mysql

# mysql库是mysql的核心数据库
# infomation_schema 是 SQL注入常见的数据库，提供对数据库元数据的只读访问，包括数据库、表、列、索引、权限等信息

information_schema数据库

# 常用表
schemata: 列出所有数据库
tables: 列出所有表及其属性
columns: 列出所有列及其属性
statistics: 列出索引信息
user_privileges 和 schema_privileges: 列出用户权限

# 查询所有数据库
select schema_name from information_schema.schemata;
# 查询所有表
select table_name from information_schema.tables;
select * from information_schema.tables where table_name='emp'; # 查找emp表的信息，所属用户等
# 查询所列
select column_name from information_schema.columns;
select column_name from information_schema.columns where table_name='emp'; # 查看emp表的字段
# 
use information_schema
show tables;
select * from tables; # 有table_name(表名), table_schema（库名）等
select * from columns; # 有table_name(表名), table_schema（库名）, column_name(列名，字段)等

SQL

SQL通用语法

# SQL可以单行或者多行书写，以分号结尾
# SQL可以使用空格/缩进 来增强预计的可读性
# MySQL数据库的SQL语句不区分大小写，关键词建议使用大写
# 注释：单行注释: -- 注释内容 或者 # 注释内容； 多行注释：/* 注释内容 */

SQL 分类

# DDL: 数据库定义语言，用于定义数据库对象（数据库、表、字段）；
# DML: 数据操作语言，用来对数据库表中的数据进行增删改；
# DQL: 数据查询语言，用来查询数据表中表的记录；
# DCL: 数据控制语言，用来创建数据库用户、控制数据库的访问权限；

数据类型

类型	bytes	有符号范围(signed)	无符号(unsigned)范围	描述
tinyint	1	(-128, 127)	(0, 255)	小整数值
smallint	2	(-32768, 32767)	(0, 65535)	大整数值
mediumint	3	(-8388608, 8388607)	(0, 16777215)	大整数值
int/integer	4	(-2147483648, 2147483647)	(0, 4294967295)	大整数值
bigint	8	(-2^63, 2^63-1)	(0, 2^64-1)	极大整数值
float	4	(-3.402823466 E+38, 3.402823466351 E+38)	0 和 (1.175494351 E-38, 3.402823466 E+38)	单精度浮点数值
double	8	(-1.7976931348623157 E+308, 1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308, 1.7976931348623157 E+308)	双精度浮点数值
decimal		依赖于 M (精度) 和 D (标度) 的值	依赖于 M (精度) 和 D (标度) 的值	小数值(精确点数)

字符类型

类型	大小 bytes	描述
char	0-255	定长字符串（需要指定长度）
varchar	0-65535	变长字符串（需要指定长度）
tinyblob	0-255	不超过255个字符的二进制数据
tinytext	0-255	短文本字符串
blob	0-65535	二进制形式的长文本数据
text	0-65535	长文本数据
mediumblob	0-16777215	二进制形式的中等长度文本数据
mediumtext	0-16777215	中等长度文本数据
longblob	0-4294967295	二进制形式的极大文本数据

日期时间类型

类型	bytes	范围	格式	描述
date	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
time	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或者持续时间
year	1	1901 至 2155	YYYY	年份值
datetime	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	4	1970-01-01 00:00:01 值 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

其他

权限

权限	说明
ALL,ALL PRIVILEGES	所有权限
select	查询数据
insert	插入数据
update	修改数据
delete	删除数据
alter	修改表
drop	删除数据库/表/视图
create	创建数据库/表

运算符

运算符	描述
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或者 !=	不等于
between … and …	在某个范围之内(含最小，最大值)
in (…)	在 in 之后的列表中的值，多旋一
like 占位符	模糊匹配 (_匹配单个字符，%匹配任意字符)
is NULL	是 NULL

逻辑运算符

逻辑运算符	功能
AND 或 &&	并且（多个条件都成立）
OR 或 \|\|	或者（多个条件任意一个成立）
NOT 或！	非，不是

聚合函数

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

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

安装

Docker 安装 MySQL5.7

安装命令

docker run -d -p 3306:3306 --privileged=true \
-v /home/hjx/docker/mysql/conf/my.cnf:/etc/my.cnf \
-v /home/hjx/docker/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=hadoop \
--name mysql mysql:5.7 \
--character-set-server=utf8mb4 \
--collation-server=utf8mb4_general_ci
language-shell复制代码

创建用户

# 先进入容器
docker exec -it mysql bash

# 执行MySQL命令, 输入root密码, 连接MySQL
mysql -uroot -p

# 输入密码后, 执行下面命令创建新用户 (用户名: test , 密码: test123)
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY 'test123' WITH GRANT OPTION;
language-shell复制代码

Docker 安装 MySQL8

拉起镜像

docker pull mysql:8.0.12
language-shell复制代码

mkdir -p /usr/mysql/conf /usr/mysql/data
chmod -R 755 /usr/mysql/
language-shell复制代码

创建配置文件

vim /usr/mysql/conf/my.cnf

[client]
#socket = /usr/mysql/mysqld.sock
default-character-set = utf8mb4

[mysqld]
#pid-file        = /var/run/mysqld/mysqld.pid
#socket          = /var/run/mysqld/mysqld.sock
#datadir         = /var/lib/mysql
#socket = /usr/mysql/mysqld.sock
#pid-file = /usr/mysql/mysqld.pid
datadir = /usr/mysql/data
character_set_server = utf8mb4
collation_server = utf8mb4_bin
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Custom config should go here

创建容器

docker run --restart=unless-stopped -d --name mysql8 \
-v /usr/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-v /usr/mysql/data:/var/lib/mysql \
-p 3306:3306 -e MYSQL_ROOT_PASSWORD=hadoop mysql
language-shell复制代码

修改mysql密码以及可访问主机

use mysql
# 修改访问主机以及密码等，设置为所有主机可访问
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '新密码';
# 刷新
flush privileges;
language-shell复制代码

靶场环境

phpStudy

# 解压安装后，配置环境变量
在path中添加：D:\phpstudy_pro\Extensions\MySQL5.7.26\bin
# 连接, 默认密码 root
mysql -u root -p 
mysql -h 192.168.16.2 -P 3396 -p

使用

快速使用

database

查看库

show databases;

创建库

create database db_name; # create database mz charset utf8mb4;

删除库

drop database db_name;

选择库

use db_name;

查看表

show tables;

查看创建库创建语句

show create database db_name;

查看选中的数据库

select database();

修改数据库字符集

alter database db_name default charset=utf8;

table

查看表结构

desc table_name; # 查看Type，Key等

查看创建表的语句

show create table table_name; # 例如 show create table user;

创建表

create table table_name(
	字段1	字段类型	comment '字段1的注释',
	字段2	字段类型	comment '字段2的注释',
	...
) comment '表注释'

# 示例
create table test(
	id int comment '编号',
	name varchar(50) comment '姓名',
	age int comment '年龄',
	gender varchar(1) comment '性别'
) comment '用户表';
# 实例2
create table emp(
id int comment '编号',
workno varchar(10) comment '工号',
name varchar(10) comment '姓名',
gender char(1) comment '性别',
age tinyint unsigned comment '年龄',
idcard char(18) comment '身份证号',
entrydate date comment '入职时间'
) comment '员工表';

修改表

# 添加一个字段
alter table emp add nickname varchar(20) comment '昵称';
# 修改数据类型
alter table emp modify nickname varchar(30);
# 修改字段名字，将nickname字段修改为username
alter table emp change nickname username varchar(50) comment '用户名';
# 删除字段，删除username字段
alter table emp drop username;
# 修改表名，将emp修改为employee
alter table emp rename to employee;

删除表

drop table employee;

Data

create table employee(
id int comment '编号',
workno varchar(10) comment '工号',
name varchar(10) comment '姓名',
gender char(1) comment '性别',
age tinyint unsigned comment '年龄',
idcard char(18) comment '身份证号',
entrydate date comment '入职时间'
) comment '员工表';

插入数据

insert into employee (id, workno, name, gender, age, idcard, entrydate) values (1,'001','mz','男',18,'000000000000110000','2025-09-09');
# 不指定字段，需要按顺序
insert into employee values (2,'002','mzz','女',19,'000000000000120000','2025-09-10');
# 批量添加
insert into employee values 
(3,'003','mzz3','女',19,'000000000000130000','2025-09-13'),
(4,'004','mzz4','女',19,'000000000000140000','2025-09-14'),
(5,'005','mzz5','女',19,'000000000000150000','2025-09-15');

修改数据

update employee set name='mzz6' where id=4;
# 修改多个字段
update employee set id=3,name='mzz3',gender='男' where id=5;
# 多个条件
update employee set name='mzz6' where id=5 and

删除数据

# 删除符合条件的数据
delete from employee where id=3;
# 清空表
delete from employee;

user

查看用户

# 查看所有用户
select * from mysql.user;
select user,host from mysql.user;
# 查看当前用户
select user();

创建用户

create user 'mz'@'localhost' identified by '123456';

修改密码

# 修改用户密码为123
alter user 'mz'@'localhost' identified with mysql_native_password by '123';

删除用户

drop user 'mz'@'localhost';

权限

权限	说明
ALL,ALL PRIVILEGES	所有权限
select	查询数据
insert	插入数据
update	修改数据
delete	删除数据
alter	修改表
drop	删除数据库/表/视图
create	创建数据库/表

查看权限

show grants for '用户名'@'主机名';
# 查看 mz 用户的权限
show grants for 'mz'@'localhost';
use information_schema; # 不需要权限
show tables;
select * from tables;
# 查看 root 的权限
show grants for 'root'@'localhost';

添加权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
## 查询权限 select
# 将mz数据库的test表的查询权限给mz用户
grant select on mz.test to 'mz'@'localhost';
# 将mz数据库的所有表的查询权限给mz用户
grant select on mz.* to 'mz'@'localhost';

## create,drop,alter
# 给mz库下的创建、删除、修改表的权限给mz
grant create,drop,alter on mz.* to 'mz'@'localhost';
# 给全部mz库下面的表权限给mz用户
grant all on mz.* to 'mz'@'localhost';

撤销权限

revoke 权限列表 on 数据库名.表名 to '用户名'@'主机名';
# 撤销 select 权限
revoke select no mz.* from 'mz'@'localhost';

修改host登录

# 修改Host是某IP登录
update user set host='ip' where user='root';
# 运行任意IP连接
update user set host='%' where user='root';
# h指定ip地址: mysql -u root -h ip -p 

# 修改密码
alter user 'root'@'ip' identified with mysql_native_password by 'root'

# 刷新配置
flush privileges;

查询

select 
	字段列表
from 
	表名列表
where
	条件列表
group by 
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit
	分页参数

# 数据表准备
create table emp(
id int comment '编号',
workno varchar(10) comment '工号',
name varchar(10) comment '姓名',
gender char(1) comment '性别',
age tinyint unsigned comment '年龄',
idcard char(18) comment '身份证号',
workaddress varchar(50) comment '工作地址',
entrydate date comment '入职时间'
)comment '员工表';
# 数据准备
INSERT INTO emp VALUES (1, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-01'),(2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01'),(3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01'),(4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01'),(5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01'),(6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01'),(7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01'),(8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01'),(9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01'),(10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01'),(11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01'),(12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01'),(13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01'),(14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01'),(15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01'),(16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');

基础查询 select

# 全表查询
select * from emp;
# 查询某些字段
select name,gender from emp;
# 查询别名显示
select name as 姓名, gender as 性别 from emp;
# 去重查询
select distinct gender from emp;


## 练习
# A. 查询指定字段 name, workno, age 并返回
select name,workno,age from emp;
# B. 查询返回所有字段
select * from emp;
# C. 查询所有员工的工作地址,起别名
select workaddress as '工作地址' from emp;
# D. 查询公司员工的上班地址有哪些(不要重复)
select distinct workaddress from emp;

聚合函数 count

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

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

## 语法：select 聚合函数(字段列表) from 表名;
select count(*) from emp;
# 统计表有多少行，*可以用1代替
select count(*) as total from emp;
# 统计idcard非空行数量
select count(idcard) as total from emp;

## 统计员工年龄
# 平均年龄
select avg(age) from emp;
# 最大年龄
select max(age) from emp;
# 最小年龄
select min(age) from emp;
# 全部年龄
select sum(age) from emp;

# 统计西安地区的平均年龄
select avg(age) from emp where workaddress='西安';

条件查询 where

运算符	描述
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或者 !=	不等于
between … and …	在某个范围之内(含最小，最大值)
in (…)	在 in 之后的列表中的值，多旋一
like 占位符	模糊匹配 (_匹配单个字符，%匹配任意字符)
is NULL	是 NULL

逻辑运算符	功能
AND 或 &&	并且（多个条件都成立）
OR 或 \|\|	或者（多个条件任意一个成立）
NOT 或！	非，不是

## 练习
# A. 查询年龄等于 88 的员工
select * from emp where age=18;
# B. 查询年龄小于 20 的员工信息
select * from emp where age<20;
# C. 查询年龄小于等于 20 的员工信息
select * from emp where age<=20;
# D. 查询没有身份证号的员工信息
select * from emp where idcard is null;
# E. 查询有身份证号的员工信息
select * from emp where idcard is not null;
# F. 查询年龄不等于 88 的员工信息
select * from emp where age!=88;
select * from emp where age<>88;
# G. 查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息
select * from emp where age >= 15 and age <=20;
select * from emp where age between 15 and 20;
# H. 查询性别为 女 且年龄小于 25岁的员工信息
select * from emp where gender='女' and age<25;
# I. 查询年龄等于18 或 20 或 40 的员工信息
select * from emp where age=18 or age=20 or age=40;
select * from emp where age in(18,20,40);
# J. 查询姓名为两个字的员工信息 _ %
select * from emp where name like '__';
# K. 查询身份证号最后一位是X的员工信息
select * from emp where idcard like '_________________X';
select * from emp where idcard list '%X';

分组查询 group by

# where 是分组前过滤, having 是分组后的过滤条件

# 根据性别分组：统计男性和女性的数量
select gender,count(*) from emp group by gender;
select gender,count(*) total from emp group by gender;

# 根据性别分组：统计男性和女性的数量
select gender,avg(age) from emp group by gender;

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

# 根据工作地址和性别统计人数
select workaddress,gender, count(*) 'total' from emp group by workaddress,gender;

排序查询 order by (降序desc)

# 默认升序
# 按照年龄从小到大升序
select * from emp order by age;
select * from emp order by age asc;

# 按照年龄从大到小，降序
select * from emp order by age desc;

# 根据入职时间先降序，(同入职时间的)根据年龄升序
select * from emp order by entrydate desc, age asc;

分页查询 limit

# 对查询结果进行分页
select * from emp limit 0,10;
select * from emp limit 10,20;

联合查询 union

# 将多次查询结果联合在一起，形成一次查询结果
# 多表查询必须列数一样
# 会自动去重，不想去重可以使用 union all

# 查询ID小于10和age=88
select * from emp where id < 10
union
select * from emp where age = 88;

# 查询ID小于10和age=88，不去重
select * from emp where id < 10
union all
select * from emp where age = 88;

其他

MySQL 触发器

MySQL 数据库引擎

MySQL 事务

MySQL 分区分表

MySQL 视图

MySQL 数据库设计及SQL优化

MySQL 数据备份还原

SQL 注入

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以邮件至 hjxstart@126.com