MySQL8.0_常用SQL语句 + 常用命令

常用命令

注意：此处常用命令仅限于linux的命令行，如果使用其他工具对mysql进行操作可能会略有不同！！！

连接数据库

mysql -u root -p

修改用户名和密码

alter -u 'root'@'%' identified with mysql_native_password by '123456';

刷新权限

flush privileges;

查看服务端口号

show variables like '%port%'

查看特定用户的host

update user set host = '%' user where user='root';

更改定用户的host

select user, host from user where user='root';

清屏

\!clear

查看已存在的数据库

show databases;

导入导出数据

# 将root用户的game数据库导出到game.sql文件中
mysqldump -u root -p game > game.sql
# 将game.sql文件导入到root用户的game数据库中
mysqldump -u root -p game < game.sql

SQL语句

数据库相关

#创建数据库
CREATE DATABASE game;
#删除数据库
DROP DATABASE game;
#进入特定数据库
USE game;

表相关

#建表
use game;
create table player(
 -- 设定为自增长的主键
 id int AUTO_INCREMENT primary key,
 name varchar(100),
 -- 设定数据类型为长度为10保留两位小数的十进制数
 gold DECIMAL(10,2),
 -- 设定默认值为1
 level int default 1,
 -- 设定非空且唯一
 play_id int not null unique,
 
);



#查看表的结构
desc player;
#修改表的字段类型
alter table game modify column id bigint;
#修改表的字段名称
alter table game rename column name to nick_name;
#添加字段
alter table game add column last_login datetime;
#删除字段
alter table game drop column last_login;
#删除表
drop table player;

数据的基本增删改查

#插入记录
insert into player (id,name) values (1, '张三'), (2, '张四');
-- 通过查询插入
insert into new_player select * from player;
#查询数据.其中as可将id列重命名
select id as id1, age from player where gold = 1000;
#更新数据
update player set level = 2 where name = '张三';
#删除数据
delete from player where gold = 0;

条件查询

#运算符的优先级
（）》 not 》 and 》 or
-- 此时先执行两个and再执行or
select id from player where level > 1 and level < 2 or gold > 0 and gold < 1000;
-- 此时先执行or再执行两个and
select id from player where level > 1 and (level < 2 or gold > 0) and gold < 1000;
-- 查询不在销售部且不在职的
select id from player WHERE NOT (department = 'sales' AND status = 'active');
-- 查询不在销售部且在职的
select id from player WHERE department <> 'sales' AND status = 'active';


#范围查询,查询等级为1, 2, 3的玩家id
select id from player where level in (1, 2, 3);
select id from player where level not in (1, 2, 3);
select id from player where level between 1 and 10;
select id from player where level not between 1 and 10;


#模糊查询
-- ‘%’：任意个字符
-- ‘_’：任意一个字符
select id from player where name like '王%';
select id from player where name like '%王%';
select id from player where name like '_王%';


#查找空与非空及空字符串
select id from player where name is null;
select id from player where name is not null;
select id from player where name = '';

查询后排序

#不特意指定时为升序
select id from player order by glod;
#升序
select id from player order by glod asc;
#降序
select id from player order by glod desc;
#指定多个排序条件
select id from player order by glod desc, exp;
select id from player order by glod desc, exp asc;
#按照第5列的字段排序
select id from player order by 5 desc;

聚合函数

常用函数：
count(*) 求记录条数
max() 求最大
min() 求最小
avg() 求平均

#查询玩家人数
select count(*) from player
#计算玩家金币平均值
select avg(glob) from player

去重

select distinct sex from player;

合并

#会有去重的效果，连接的表需要列数相同，数据类型相似，且列按正确的顺序排列
select sex from player
union
select sex from player1;
#取并集
select sex from player
union all
select sex from player1;
#取交集
select sex from player
intersect
select sex from player1;
#取差集
select sex from player
except
select sex from player1;

分组查询

#根据性别分组来查询玩家人数. 此时sex栏为null的人也会被count(*)视为一个组并查询该组的人数
select sex, count(*) from player group by sex;
#根据性别分组来查询玩家人数. 此时sex栏为null的人不会被count(sex)视为一个组
select sex, count(sex) from player group by sex;
#使用having对分组查询过滤.仅查看人数大于4的性别组的玩家人数
select sex, count(sex) from player group by sex having count(sex) > 4;

子查询

#用在where中
select level from player where level > (select avg(level) from player);
#用在select中
select level,(select avg(level) from player) from player;
#通过查询建表
create table new_player select * from player;
#通过查询插入
insert into new_player select * from player;
#判断查询结果是否存在，结果为1即存在，结果为0即不存在
select exists(select * from player);

表的连接

#内连接，返回左右表中所有匹配的数据
select * from player
inner join equip
where player.id = equip.player_id
-- 或者
select * from player, equip where player.id = equip.player_id
#左连接，返回左表中所有数据和右表中所有匹配的数据
select * from player
left join equip
where player.id = equip.player_id
#右连接，返回右表中所有数据和左表中所有匹配的数据
select * from player
right join equip
where player.id = equip.player_id

索引相关

一般对主键字段和经常查询字段建立索引

#在表中的某些字段上创建索引
creat [unique|fulltext|spatial] index index_name 
on table_name (index_col_name,...);
#查看索引
show index from table_name;
#删除索引
drop index index_name on table_name;
#添加索引,在table_name表的id字段上建立索引
alter table_name add index index_name (id)

视图相关

视图：虚拟表，并不存储实际的数据，而是作为查询语句存储在数据字典中。

根据视图语句的定义来动态的生成数据。

#创建视图
create view top10
as
select * from player order by desc limit 10;
#修改视图
create view top10
as
select * from player order by desc limit 5;
#删除视图
drop view top10;