MySQL是一个关系型数据库管理系统。简单复习数据库MySQL知识。

目录

0.数据库用户设置

0.1 创建用户

create user 用户名@主机名 identified by '密码';
# 例如
create user loong@localhost identified by '123';
# 主机名是允许登录的主机名，可以写通配符表示 % 表示允许所有
create user loong@'%' identified by '123';

0.2 删除用户

drop user 用户名@主机名;
drop user loong@localhost;
drop user loong@'%';

0.3 修改用户密码

1. 方法一

update user set password = password('新密码') where user = '用户名';
# password 为给密码加密的函数

2. 方法二

set password for 用户名@主机名 = password('新密码');

0.4 root用户密码忘记

1. 先停止MySQL服务

# CMD命令窗口（以管理员身份运行）
net stop mysql 

2. 使用无验证方式启动MySQL服务

mysqld --skip-grant-tables

3. 打开新的CMD窗口，直接输入命令：mysql，再回车就可以登录成功。

4. use mysql;

use mysql;

5. update user set password = password(‘新密码’) where user =’root’;

update user set password = password('新密码') where user ='root';

6. 关闭两个窗口

7. 打开任务管理器，手动结束mysqld.exe的进程。

8. 启动MySQL服务。

9. 使用新密码登录。

0.5 查询用户权限

show grants for 用户名@主机名;

0.6 授予权限

grant 权限列表 on 数据库名.表名 to 用户名@主机名;
grant all on *.* to 用户名@主机名;# 所有权限

0.7 撤销权限

revoke 权限列表 on 数据库名.表名 from 用户名@主机名;

1.数据库的使用

1.1 MySQL服务启动和关闭

//开启
net start mysql80 //mysql80为服务器的默认名称

//关闭
net stop mysql80

1.2 登录MySQL

mysql -h 服务器地址 -u 用户名 -p密码 //-p与密码间没用空格，当是在本地时 -h 可以省略或者写localhost或127.0.0.1

mysql -u root -prootmm //root 为用户名，rootmm 为命名，这种写法容易造成密码泄露

mysql -u root -p //回车后再输入密码，这时输入的密码是加密后的

1.3 查看数据库字符集

show character set;

1. MySQL字符集系统变量

系统变量名	说明
character_set_server	服务器安装时指定的默认编码格式。服务器启动时通过该变量设置字符集。使用utf8mb4作为MySQL的默认字符集。
character_set_client	用来设置客户端使用的字符集。用来决定MySQL如何解释客户端发到服务端的SQL命令。
character_set_connection	用来设置连接数据库时的字符集。用来决定MySQL如何处理客户端发来的SQL命令。
character_set_results	数据库给客户端返回时使用的编码格式，当SQL返回结果时，这个变量的取值决定了发给客户端的字符编码。
character_set_database	用来设置默认创建数据库的编码格式，如果在创建数据库时没用设置编码格式，就按照这个默认字符集设置当前选中的数据库。
character_set_system	数据库系统使用的编码格式，这个值一直是utf8，不需要设置。它是为存储系统元数据的编码格式。数据库、表和字段都用这个字符集。

1.4 查看当前系统参数

shwo variables like 'char%';

1.5 修改系统参数

character_set_database=utf8; //其他类似，直接赋结果即可。

需要注意的是，这种修改方式只在当前客户端有效，关闭后就会恢复成默认值。若要永久性生效，则需要在配置文件 my.ini 中修改。

2.退出MySQL

//两种方法都行
exit 
quit 

3.数据库

3.1 创建数据库（create）

create database 数据库名;

3.2 删除数据库（drop）

drop database 数据库名;
drop database if exists 数据库名; //如果存在此数据库才执行

3.3 选择数据库（use）

use 数据库名;

3.4 查看数据库（show）

show databases;

3.5 修改数据库（alter）

alter database 数据库名 
default character set gb2312
default collate gb2312_chinese_ci;

3.6 显示数据库中所有表的信息

show table status from 数据库名;

表名以 loong 开头的表的信息

show table status from 数据库名 like 'loong';

查询结果按列打印

show table status from 数据库名 like 'loong'\G;

4.表

4.1 创建表（create）

4.1.1 常规创建

create table 表名(
	字段名 字段类型
);

示例：

create table loong(
   tutorial_id INT NOT NULL AUTO_INCREMENT comment '编号',
   tutorial_title VARCHAR(100) NOT NULL,
   tutorial_author VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( tutorial_id )
);

4.1.2 复制其他表

create table 表名1 [[like 表名2] | select 语句 ];

1. 复制表结构

create table 表名1 like 表名2; //表1复制表2的结构

2. 复制表结构及数据

create table 表名1 select * from 表名2;

4.2 删除表（drop）

drop table [if exists] 表名;

4.3 查看表（show)

show tables;

4.4 修改表（alter）

alter table 表名 修改选项

4.4.1 添加字段（add）

alter table 表名 add 新字段名 数据类型 [约束条件] [first|after 已存在的字段名];

• first：将新添加的字段设置为表的第一个字段；
• after：将新添加的字段添加到指定的已存在字段名的后面。

4.4.2 修改字段数据类型（modify）

alter table 表名 modify 字段名 新数据类型;

4.4.3 删除字段（drop）

alter table 表名 drop 字段名;

4.4.4 修改字段名（change）

alter table 表名 change 旧字段名 新字段名 新数据类型

4.4.5 修改表名（rename）

alter table 旧表名 rename [to] 新表名;

• to：为可选参数，用否不影响。

4.5 查看表结构（desc）

describe tablename; //tablename 为表名，describe 也可以简写为 desc
desc tablename;

//显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。两则的效果差不多
show columns from 表名;

4.6 显示建表语句

show create table 表名;

4.7 表的数据操作

4.7.1 插入数据（insert into）

1. 指定字段插入数据

insert into 表名(字段1，字段2，····，字段n)
value
(值1，值2，····，值n);

2. 不指定字段插入数据

insert into 表名 value
(值1，值2，···，值n);

两者区别：

• 指定字段：可以不按照表中字段的顺序。
• 不指定字段：值的顺序必须与表中字段的顺序相同。

3. 同时插入多条数据

insert into 表名(字段1，字段2，····，字段n)
value
(值1，值2，···),
(值1，值2，···),
.......
(值1，值2，···);

4. 将其他表的数据插入表中

insert into 表名1(字段1，字段2，····)
select 对应字段1，对应字段2，····
from 表名2;

4.7.2 修改数据

update 表名 set 字段1=值1[,字段2=值2,字段3=值3····] [where 子句] [limit 子句];

4.8 删除数据

4.8.1

delete from 表名 [where 子句] [order by 子句] [limit 子句];

如果不使用 where 条件，将删除所有数据。

4.8.2

truncate table 表名;

删除表中全部数据。

4.8 显示表的详细索引信息

show index from 表名;

5.查看版本支持的数据库存储引擎

show engines \G;

6.数据类型

6.1 数值类型

tinyint
smallint
mediumint
int或integer
bigint
float
double
decimal

6.2 日期和时间类型

date //YYYY-MM-DD
time //HH:MM:SS
year //YYYY
datetime //YYYY-MM-DD HH:MM:SS
timestamp //YYYYMMDD HHMMSS

6.3 字符串类型

char //定长字符串
varchar //变长字符串
tinyblob //不超过255个字符的二进制字符串
tinytext //短文本字符串
blob //长文本数据
text //二进制形式的中等长度文本数据
mediumblob //中等长度文本数据
mediumtext //
longblob
longtext

7.查询数据（select）

select 字段1，字段2····
from 表名;

8.where子句

select 数据1，数据2，···· from 表名1，表名2·····
where 条件1 [and 或者 or] 条件2····;

表之间使用逗号（，）分割，并使用 WHERE 语句来设定查询条件。可以使用AND或者OR指定一个或多个条件。

以 A=10，B=20 为例：

操作符	描述	实例
=	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
<> 或 !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
<=	小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。

9.update更新

update 表名 set 字段1=新值，字段2=新值;

可以同时更新一个或多个字段。也可以在后面加上 WHERE 子句来指定任何条件，更新数据表中指定行的数据时 WHERE 子句是非常有用的。

10.delete语句

delete from 表名;

如果没有指定 WHERE 子句，MySQL表中的所有记录将被删除。可以在 WHERE 子句中指定任何条件，想删除数据表中指定的记录时 WHERE 子句是非常有用的。

11.like语句

select 字段1，字段2，···字段n
from 表名1，表名2，···表名
where 字段1 like 条件 [and 或者 or] 字段2='其他值';

可以在WHERE子句中指定任何条件，可以在WHERE子句中使用LIKE子句。使用LIKE子句代替等号(=)。LIKE 通常与 % 一同使用，类似于一个元字符的搜索。

12.排序（order by）

select 字段1，字段2，···字段n from
表名1，表名2，···
order by 字段1，[字段2，····] [asc 或者 desc]

可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升排列。

13.分组

根据一个或多个列对结果集进行分组。在分组的列上可以使用 COUNT, SUM, AVG,等函数。

select 字段名，function(字段名)
from 表名
where 字段名 operator value
group by 字段名;//是按照这个字段名进行分组

13.1 使用 with rollup

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

select 字段名，function(字段名)
from 表名
where 字段名 operator value
group by 字段名 //是按照这个字段名进行分组，这个一般与输出的字段名一样
with rollup;

14.查看当前数据库名称

select database();