avatar
文章
196
标签
12
分类
12
首页
档案
标签
分类
链接
关于
列表
123
首页
档案
标签
分类
链接
关于
列表
MySQL的使用
|数据库
阅读量:

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

启动MySQL服务

1
net start mysql;

停止MySQL服务

1
net stop mysql;

登录MySQL

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

1
mysql -u用户名 -p密码

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

1
2
mysql -u用户名 -p
密码

登录指定IP地址的下的MySQL

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

登录指定IP地址的下的MySQL

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

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

1
2
exit
quit

SQL注释

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

1
2
-- 注释信息
#注释信息

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

1
/* 注释信息 */

DDL：操作数据库

create

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

1
create database 数据库名称;

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

1
create database if not exists 数据库名称;

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

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

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

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

retrieve

查询所有数据库的名称

1
show databases;

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

1
show create database 数据库名称;

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

1
select database();

update

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

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

delete

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

1
drop database 数据库名称;

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

1
drop database if not exists 数据库名称;

使用数据库

1
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个字节的定长字节

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

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

创建表演示

1
2
3
4
5
6
7
8
create table student(
	id int,
	name varchar(10);
	age int,
	score double(4,1),
	birthday date,
	insert_time timestamp
);

复制表，不包含数据

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

retrieve

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

1
show tables;

查询表结构

1
desc 表名;

update

修改表名

1
alter table 表名 rename to 新表名;

修改表使用的字符集

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

添加单列

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

添加多列

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

修改列的数据类型

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

修改列的名称和数据类型

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

delete

删除列

1
alter table 表名 drop 列名;

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

1
drop table 表名;

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

1
drop table if exists 表名;

DML：操作数据

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

create

为指定列插入数据

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

为所有列插入数据

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

DQL：retrieve

语法

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

基础查询

查询表中的所有数据

1
select * from 表名;

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

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

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

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

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

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

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

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

条件查询

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

模糊查询

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

排序查询(默认asc排序)

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

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

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

1
2
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

1
select count(*) from 表名;

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

1
select count(列名) from 表名;

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

1
select *, max(列) from 表名;

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

1
select *, min(列) from 表名;

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

1
select sum(列) from 表名;

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

1
select avg(列) from 表名;

分组查询

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

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

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

1
select * from 表名 having 条件判断;

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

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

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

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

分页查询

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

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

update

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

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

修改表中指定数据

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

delete

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

1
delete from 表名;

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

1
truncate table 表名;

根据条件删除对应的数据

1
delete from 表名 where 条件判断;

约束

语法

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

建表时添加约束

1
2
3
4
5
6
7
8
9
10
11
12
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
);

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

添加约束

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

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

添加外键约束

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

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

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

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

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

删除约束

删除非空约束，自动增长

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

删除唯一约束

1
alter table 表名 drop index 列名;

删除主键约束

1
alter table 表名 drop primary key;

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

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

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

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

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

还原数据库

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

多表查询

内连接

隐式内连接

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

1
2
3
4
5
6
select
	*
from
	表1, 表2
where
	表1.关联字段 = 表2.关联字段;

显式内连接

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

1
2
3
4
5
6
7
8
select
	*
from
	表1
inner join
	表2
where
	表1.关联字段 = 表2.关联字段;

外连接

左外连接

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

1
2
3
4
5
6
7
8
select
	字段列表
from
	表1
left outer join
	表2
on
	表1.关联字段 = 表2.关联字段;

右外连接

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

1
2
3
4
5
6
7
8
select
	字段列表
from
	表1
right outer join
	表2
on
	表1.关联字段 = 表2.关联字段;

子查询

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

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

1
2
3
4
5
6
7
8
9
select
	*
from
	学生表
where
	学生表.成绩 = (select
					max(成绩)
				  from
				  	学生表);

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

1
2
3
4
5
6
7
8
9
select
	*
from
	学生表
where
	学生表.成绩 < (select
				  	avg(成绩)
				  from
				  	学生表);

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

1
2
3
4
5
6
7
8
9
10
11
12
13
select
	*
from
	学生表
where
	学生表.课程id
in
	(select
		课程id
	from
		课程表
	where
		name = '语文' or name = '数学');

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

1
2
3
4
5
6
7
8
9
10
11
12
select
	*
from
	班级表,
	(select
		*
	from
		学生表
	where
		入职时间 = '2020-9-1') 学生表
where
	班级表.id = 学生表.班级id;

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

1
2
3
4
5
6
7
8
9
10
11
12
select
	班级表.班级id, 班级表.班级名称,学生表.班级人数
from
	班级表,
	(select
		班级id, count(id) 班级人数
	from
		学生表
	group by
		班级id) 学生表
where
	班级表.id = 学生表.班级id;

事务

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

查询事务的提交方式

1
select @@autocommit;

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

1
set @@autocommit = 参数;

手动提交

1
commit;

事务的隔离级别

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

查询数据库的隔离级别

1
select @@tx_isolation;

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

1
set global transaction isolation level 隔离级别;

开启事务

1
start transaction;

DCL:管理用户，授权

管理用户

查询并使用

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

创建用户

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

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

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

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

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

修改用户密码

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

忘记用户名密码

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

1
net stop mysql

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

1
net stop mysql

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

1
mysqld --skip-grant-tables

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

1
mysql

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

结束进程

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

1
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

查询权限

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

授予权限，*表示所有

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

删除用户权限

1
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
文章作者: 123
文章链接: https://gao5805123.github.io/123/2020/06/09/MySQL%E7%9A%84%E4%BD%BF%E7%94%A8/
版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 123！
数据库
打赏
  • 微信
    微信
  • 支付宝
    支付宝
相关推荐
2021-06-29
SQL使用要点
2020-06-25
Navicat的破解使用(数据库图形化工具)
2020-07-29
sql查询语句
2021-10-28
事务的隔离级别
2020-07-09
在MySQL数据库表中写入汉字报 1366 - Incorrect string value xE5x91\x9C(xE5(x91\x9C' for column 'specifications_set' at row 1 异常
2020-06-30
安装压缩包中的MySQL
繁