MySQL的使用

MySQL服务操作(管理员权限的命令提示符窗口)

启动MySQL服务

net start mysql;

停止MySQL服务

net stop mysql;

登录MySQL

显式登录，密码会显示出来

mysql -u用户名 -p密码

隐式登录，写到-p处回车，再输入密码，密码不可见

mysql -u用户名 -p
密码

登录指定IP地址的下的MySQL

mysql -hIP地址 -u用户名 -p密码

登录指定IP地址的下的MySQL

mysql --host=IP地址 --user=root --password=root

退出MySQL，都是退出命令，也可以按Ctrl + C进行退出

exit
quit

SQL注释

单行注释，- -后面必须有空格，#后面可以不加空格

-- 注释信息
#注释信息

多行注释，同Java中的多行注释用法一致

/* 注释信息 */

DDL：操作数据库

create

创建数据库，当要创建的数据库已存在时，会在控制台打印错误信息

create database 数据库名称;

判断数据库是否存在，若不存在则创建，存在则不会打印错误信息

create database if not exists 数据库名称;

创建数据库并指定使用的字符集，如：utf8 和 gbk

create database 数据库名称 character set 字符集名称;

判断数据库是否存在，若不存在则对其进行创建并指定其使用的字符集，存在则不会打印错误信息

create database if not exists 数据库名称 character set 字符集名称;

retrieve

查询所有数据库的名称

show databases;

查询指定数据库所使用的字符集

show create database 数据库名称;

查询当前所使用的数据库，当当前使用的数据库不存在时，会返回null

select database();

update

修改数据库所使用的字符集

alter database 数据库名称 character set 字符集名称;

delete

删除数据库，当数据库不存在时，会在控制台打印错误信息

drop database 数据库名称;

判断数据库是否存在，存在则会将其删除，不存在则不会打印错误信息

drop database if not exists 数据库名称;

使用数据库

use 数据库名称;

DDL：操作表

create

数据类型	数据名称	说明
整数类型	tinyint	很小的整数
	smallint	小的整数
	mediumint	中等大小的整数
	int(dataLength)	普通大小的整数；在括号中指定可写入的数据的长度，只能填入整数；括号可以省略，省略后默认数据长度为11
小数类型	float	单精度浮点数
	double(m,d)	双精度浮点数，在括号中写入整个数据的长度和小数点后面数据的长度，如:double(5，2)，小数长度为5，小数点后面有两位数；括号省略不写后，可以写入任意长度的小数
	decimal(m,d)	压缩严格的定点数
日期类型	year	yyyy，1901~2155
	time	HH:mm:ss，-838:59:59~838:59:59
	date	yyyy-MM-dd，1000-01-01~9999-12-03
	datetime	yyyy-MM-dd HH:mm:ss，1000-01-01 00:00:00~9999-12-31 23:59:59
	timestamp	yyyy-MM-dd HH:mm:ss，1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC
字符串	char(M)	M为0~255之间的整数
	varchar(M)	M为0~65535之间的整数
blob	tinyblob	允许长度0~255字节
	blob	允许长度0~65535字节
	mediumblob	允许长度0~167772150字节
	longblob	允许长度0~4294967295字节
clob	tinytext	允许长度0~255字节
	text	允许长度0~65535字节
	mediumtext	允许长度0~167772150字节
	longtext	允许长度0~4294967295字节
二进制	varbinary(M)	允许长度0~M个字节的变长字节
	binary(M)	允许长度0~M个字节的定长字节

创建表，创建多个列时，列与列之间要用逗号分隔开，最后一个列后面不需要加逗号

create table 表名(
	列名1 数据类型1,
		...
	列名n 数据类型n
);

创建表演示

create table student(
	id int,
	name varchar(10);
	age int,
	score double(4,1),
	birthday date,
	insert_time timestamp
);

复制表，不包含数据

create table 表名 like 被复制的表名;

retrieve

查询数据库中所有表的名称

show tables;

查询表结构

desc 表名;

update

修改表名

alter table 表名 rename to 新表名;

修改表使用的字符集

alter table 表名 character set 字符集名称;

添加单列

alter table 表名 add 列名 数据类型;

添加多列

alter table 表名 add(
	列名1 数据类型1,
		 ...
	列名n 数据类型n
);

修改列的数据类型

alter table 表名 modify 要修改的列名 修改后的数据类型;

修改列的名称和数据类型

alter table 表名 change 要修改的列名 修改后的列名 修改后的数据类型;

delete

删除列

alter table 表名 drop 列名;

删除表，当数据库不存在时，会在控制台打印错误信息

drop table 表名;

判断表是否存在，存在则会将其删除，不存在则不会打印错误信息

drop table if exists 表名;

DML：操作数据

值要与指定的列或表中的列一一对应
除数字类型外，其他类型需要使用引号(’’，"")包裹

create

为指定列插入数据

insert into 表名(列1,...,列n) values(值1,...,值n);

为所有列插入数据

insert into 表名 values(值1,...,值n);

DQL：retrieve

语法

关键字	作用
select	字段列表
from	表名列表
where	条件列表
group by	分组字段
having	分组之后的条件
order by	排序
limit	分页限定

基础查询

查询表中的所有数据

select * from 表名;

查询表中指定列下的所有数据

select 要查询的列名1, ..., 要查询的列名n from 表名;

查询表中指定列所有的数据，并去除重复

select distinct 要查询的列名1, ..., 要查询的列名n from 表名; 

运算时，若有值为null，就将其修改成指定的值

select ifnull(要查询的列名1,替换后的值), ifnull(要查询的列名2,替换后的值) from 表名;

给指定列起别名，可用一个空格代替as

select 列名1 as 别名1, 列名2 别名2 from 表名;

条件查询

查找指定列中大于给定参数的所有信息

select * from 表名 where 列名 > 指定参数;

查找指定列中大于等于给定参数的所有信息

select * from 表名 where 列名 >= 指定参数;

查找指定列中等于给定参数的所有信息，SQL中没有==

select * from 表名 where 列名 = 指定参数;

查找指定列中不等于给定参数的所有信息，!=可以用<>代替

select * from 表名 where 列名 != 指定参数;

查找指定列中大于等于给定参数1，小于等于给定参数2的所有信息，and可以用&&代替，但不建议使用

select * from 表名 where 列名 >= 指定参数1 and 列名 <= 指定参数2;

查找指定列中范围在参数1和参数2之间的数据，包括参数1和参数2

select * from 表名 where 列名 between 指定参数1 and 指定参数2;

查找指定列的符合参数1或者参数2的数据，可以用in代替或条件的判断，括号中的参数个数是可变的

select * from 表名 where 列名 = 指定参数1 or 列名 = 指定参数2;
select * from 表名 where 列名 in (指定参数1,指定参数2);

查询该列中值为null的所有数据，null值不能使用=判断

select * from 表名 where 列名 is null;

查询该列中值不为null的所有数据，null值不能使用!=判断

select * from 表名 where 列名 is not null;

模糊查询

查询字符长度为三个字符的所有数据，一个_就代表一个字符

select * from 表名 where 列名 like '___';

查询任意字符长度的所有数据，%代表任意个字符

select * from 表名 where 列名 like '%';

查询符合以指定字符开头的，后面只有一个任意字符的所有数据

select * from 表名 where 列名 like '指定字符_';

查询符合以指定字符开头的，后面可以有任意个字符的所有数据

select * from 表名 where 列名 like '指定字符%';

查询符合第一个字符为任意字符，第二个字符以指定字符开头的，后面可以有任意个字符的所有数据

select * from 表名 where 列名 like '_指定字符%';

查询列中指定字符前任意字符，且字符长度不限，而指定字符后只有一个任意字符的数据

select * from 表名 where 列名 like '%指定字符_';

查询列中首位和末位字符任意，中间是指定字符的所有数据

select * from 表名 where 列名 like '_指定字符_';

查询列中任意位置有指定字符的所有数据

select * from 表名 where 列名 like '%指定字符%';

排序查询(默认asc排序)

将表中的所有数据以指定列值的升序排序

select * from 表名 group by 列名1 asc, ..., 列名n asc;
select * from 表名 order by 列名1 asc, ..., 列名n asc;

将表中的所有数据以指定列值的降序排序

select * from 表名 group by 列名1 desc, ..., 列名n desc;
select * from 表名 order by 列名1 desc, ..., 列名n desc;

聚合函数

语法

关键字	作用
count	计算个数
max	计算最大值
min	计算最小值
sum	计算和
avg	计算平均值

计算表中有多少条数据，后面跟order by列名时，数据的计算不会发生变化；后面跟group by列名时，会查询每一行或每一列是否有数据，有就返回1，没有就返回0

select count(*) from 表名;

计算该列中有多少条不为空的数据，后面跟order by列名时，数据的计算不会发生变化；后面跟group by列名时，会查询每一行或每一列是否有数据，有就返回1，没有就返回0

select count(列名) from 表名;

查询指定列中数值最大的数据

select *, max(列) from 表名;

查询指定列中数值最小的数据

select *, min(列) from 表名;

计算指定列中所有数据的和

select sum(列) from 表名;

计算指定列中所有数据的平均值

select avg(列) from 表名;

分组查询

通过给定的列将表中的数据进行分组，重复的数据将会被过滤，只展示一次；select和from的中间建议使用进行分组的列

select 列 from 表名 group by 分组条件;

单独使用，与where的使用方法类似

select * from 表名 having 条件判断;

having后面必须跟聚合函数返回的结果

select * from 表名 group by 字段 having 条件判断;

where后面跟普通条件判断，having后面跟聚合函数条件判断

select * from 表名 where 条件判断 group by 字段 having 条件判断;

分页查询

查询从指定索引开始的指定条数据(起始索引 = 当前页码 - 1)

select * from 表名 limit 起始索引,显示条数;

update

修改表中的所有数据为指定数据

update 表名 set 要修改的列名1 = 要修改成的数据1, ...,要修改的列名n = 要修改成的数据n;

修改表中指定数据

update 表名 set 要修改的列名1 = 要修改成的数据1, ...,要修改的列名n = 要修改成的数据n where 条件判断;

delete

删除表中所有数据(一条一条进行删除)

delete from 表名;

删除表，再创建一个完全一样的空表

truncate table 表名;

根据条件删除对应的数据

delete from 表名 where 条件判断;

约束

语法

1级关键字	2级关键字	作用
primary key		主键约束
	auto_increment	自增约束
not null		非空约束
unique		唯一约束
foreign key		外键约束
	on update cascade	级联更新
	on delete cascade	级联删除

建表时添加约束

create table 表名(
	/* 为id添加主键约束和自增约束,一个表中只能有一个主键,当列被添加主动约束后,会默认添加非空约束和唯一约束;自增约束只能为主键添加 */
	id int primary key auto_increment,
	/* 为name添加非空约束,表示该列的值不能为空 */
	name varchar(10) not null,
	/* 为phone添加唯一约束,表示该列中的值不允许有重复 */
	phone int(11) unique,
	/* 外键列 */
	gender_id int(1),
	/* 为gender_id添加外键约束,且设置了级联更新和级联删除,constraint 外键名称可以省略,省略后外键名称会被自动生成;关联主表时主表必须存在,且必须关联主键;foreign key后面的()中需要写入要作为外键的列名,且必须与主键的数据类型一致;当往表中插入数据时,外键列中所写入的数据必须在被关键主表的主键中存在;设置级联更新后当被关联的主键中的数据被改变时,外键列中对应的数据也会被修改;设置级联更新后,当被关联的主键中的数据被删除时,外键列对应的所有数据会被删除,需谨慎使用 */
	constraint 外键名称 foreign key(gender_id) references 主表名称(主键列名称) on update cascade on delete cascade
);

对已有表进行添加或删除约束

添加约束

添加非空约束，唯一约束，主键约束，自动增长约束

alter table 表名 modify 列名 数据类型 约束类型;

添加外键约束

alter table 表名 add constraint 外键名称 foreign key(外键列名称) references 主表名称(主表列名称);

添加外键约束并设置级联更新

alter table 表名 add constraint 外键名称 foreign key(外键列名称) references 主表名称(主表列名称) on update cascade;

添加外键约束并设置级联删除

alter table 表名 add constraint 外键名称 foreign key(外键列名称) references 主表名称(主表列名称) on delete cascade;

删除约束

删除非空约束，自动增长

alter table 表名 modify 列名 数据类型;

删除唯一约束

alter table 表名 drop index 列名;

删除主键约束

alter table 表名 drop primary key;

删除外键约束，外键列无论有无级联更新或级联删除都会被删除

alter table 表名 drop foreign key 外键名称;

数据库的备份和还原(需要在命令提示符窗口中使用)

备份数据库下指定表到指定位置，当指定数据库名称时，生成的文件中会有用法提示；表名称可省略，省略后数据库中的所有表都会被备份

mysqldump -u用户名 -p密码 数据库 表名称 > 保存的路径/文件名称.sql

还原数据库

步骤一:登录mysql
	mysql -u用户名 -p密码
步骤二:指定要还原到的数据库
	use 数据库名称;
步骤三:数据库还原
	source 保存的路径/文件名称.sql

多表查询

内连接

隐式内连接

通过关联关系查询两个表中的所有数据

select
	*
from
	表1, 表2
where
	表1.关联字段 = 表2.关联字段;

显式内连接

通过关联关系查询两个表中的所有数据；inner是可以省略的

select
	*
from
	表1
inner join
	表2
where
	表1.关联字段 = 表2.关联字段;

外连接

左外连接

以表1为主，根据关联关系查询1中的所有数据及与表2的交集部分；outer是可以省略的

select
	字段列表
from
	表1
left outer join
	表2
on
	表1.关联字段 = 表2.关联字段;

右外连接

以表2为主，根据关联关系查询表2中的所有数据及与表1的交集部分

select
	字段列表
from
	表1
right outer join
	表2
on
	表1.关联字段 = 表2.关联字段;

子查询

查询中嵌套查询，从而获取想要的数据，如下所示

查询学生表中学习成绩最高的学生的所有信息

select
	*
from
	学生表
where
	学生表.成绩 = (select
					max(成绩)
				  from
				  	学生表);

查询学生表中学习成绩小于平均成绩的学生的所有信息

select
	*
from
	学生表
where
	学生表.成绩 < (select
				  	avg(成绩)
				  from
				  	学生表);

查询学生表中学习语文或数学的所有的学生信息

select
	*
from
	学生表
where
	学生表.课程id
in
	(select
		课程id
	from
		课程表
	where
		name = '语文' or name = '数学');

查询入学时间是2020-9-1的学生信息和班级信息

select
	*
from
	班级表,
	(select
		*
	from
		学生表
	where
		入职时间 = '2020-9-1') 学生表
where
	班级表.id = 学生表.班级id;

通过学生表中的班级人数，查询出班级编号，班级名称和班级人数

select
	班级表.班级id, 班级表.班级名称,学生表.班级人数
from
	班级表,
	(select
		班级id, count(id) 班级人数
	from
		学生表
	group by
		班级id) 学生表
where
	班级表.id = 学生表.班级id;

事务

事务提交(MySQL默认自动提交，1代表自动提交，0代表手动提交)

查询事务的提交方式

select @@autocommit;

修改事务的提交方式，对数据进行操作后需要重启SQLyog才能看到数据有无变化，SQLyog重启后会将事务的提交方式改为自动提交

set @@autocommit = 参数;

手动提交

commit;

事务的隔离级别

隔离级别	说明
read uncommitted	读未提交: 脏读，不可重复读，幻读
read committed	读已提交: 不可重复读，幻读
repeatable read	可重复读: 幻读
serializable	串行化: 无问题

查询数据库的隔离级别

select @@tx_isolation;

修改数据库的隔离级别，需要重启才能查出是否修改成指定的隔离级别，若不想重启就能查出修改的隔离级别，就将global替换成session

set global transaction isolation level 隔离级别;

开启事务

start transaction;

DCL:管理用户，授权

管理用户

查询并使用

1.切换到mysql数据库
	use mysql;
2.查询用户表
	select * from user;
注: host列中的%表示可以在任意主机下使用用户登录数据库

创建用户

create user '用户名'@'主机名' identified by '密码';

删除用户，删除用户必须使用命令删除

drop user '用户名'@'主机名';

修改用户密码，并重新加载权限表

update user set password = password('新密码') where user = '用户名';
flush privileges;

修改用户密码

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

忘记用户名密码

命令提示符窗口中停止mysql服务，需要管理员身份

net stop mysql

命令提示符窗口中停止mysql服务，需要管理员身份

net stop mysql

使用无验证方式启动mysql服务

mysqld --skip-grant-tables

输入以下命令登录mysql服务，再使用上面修改密码的方法修改密码即可

mysql

修改完密码后，打开任务管理器，右键点击结束任务(E)将mysqld.exe进程终止

启动mysql服务，再进行登录即可

net start mysql

授权管理

权限	说明	具体说明
all [privileges]	所有权限
ALTER	Alter_priv	Tables
ALTER ROUTINE	Alter_routine_priv	Stored routines
CREATE	Create_priv	Databases, tables, or indexes
CREATE ROUTINE	Create_routine_priv	Stored routines
CREATE TABLESPACE	Create_tablespace_priv	Server administration
CREATE TEMPORARY TABLES	Create_tmp_table_priv	Tables
CREATE USER	Create_user_priv	Server administration
CREATE VIEW	Create_view_priv	Views
DELETE	Delete_priv	Tables
DROP	Drop_priv	Databases, tables, or views
EVENT	Event_priv	Databases
EXECUTE	Execute_priv	Stored routines
FILE	File_priv	File access on server host
GRANT OPTION	Grant_priv	Databases, tables, or stored routines
INDEX	Index_priv	Tables
INSERT	Insert_priv	Tables or columns
LOCK TABLES	Lock_tables_priv	Databases
PROCESS	Process_priv	Server administration
PROXY	See proxies_priv table	Server administration
REFERENCES	References_priv	Databases or tables
RELOAD	Reload_priv	Server administration
REPLICATION CLIENT	Repl_client_priv	Server administration
REPLICATION SLAVE	Repl_slave_priv	Server administration
SELECT	Select_priv	Tables or columns
SHOW DATABASES	Show_db_priv	Server administration
SHOW VIEW	Show_view_priv	Views
SHUTDOWN	Shutdown_priv	Server administration
SUPER	Super_priv	Server administration
TRIGGER	Trigger_priv	Tables
UPDATE	Update_priv	Tables or columns
USAGE	Synonym for “no privileges”	Server administration

查询权限

show grants for '用户名'@'主机名';

授予权限，*表示所有

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

删除用户权限

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