目录
可以当个速通教学,写一些常用的命令,不会太深入的去剖析
24-3-22数据库的基本操作
1.数据库的基本操作:
查看当前已存在的数据库:
show databases;
创建一个名为game的数据库:
create database game;
删除一个名为game的数据库:
drop database game;
2.表的基本操作:
使用一个名为game的数据库:
use game;
查看数据库里所有的表:
show tables;
创建一个名为player的表:
create table player (
id int,
name varchar(100),
level int,
exp int,
gold decimal(10,2)
)
查看表的结构:
desc player;
或者:
show create table player;
修改表结构(修改name的varchar值为200):
alter table player modify column name varchar(200);
修改player的表名为nick_name:
alter table player rename column name to nick_name;
添加一个last_login字段记录玩家最后登录的时间:
alter table player add column last_login datetime;
删除一个字段:
alter table player drop column last_login;
删除整个表:
drop table player;
3.数据的增删改查:
插入一条数据:
insert into player (id,name,level,exp,gold) values(1,'张三',1,1);
查看表里的所有信息:
select * from player;
插入多条数据:
insert into player (id,name) values (2,'李四'),(3,'王五');
指定表的默认值:
alter table player modify level int default 1;
修改表的数据: (把李四同学的level修改为1)
update player set level = 1 where name = '李四';
一次性修改所有的数据; (把所有玩家的exp和gold修改为0)
update player set exp=0 , gold=0;
删除表数据: (把所有gold=0的玩家给删除)
delete from player where gold=0;
4.数据的导入导出:
导出数据库: game是数据库名,player是表名,r如果后面没有表名则导出所有表
mysqldump -u root -p 000000 game player > game.sql
导入数据库: 把game.sql导入到game数据库里面
mysql -u root -p 000000 game < game.sql
5.常用语句:
主键:
设置主键: 其中xuehao是主键,因为设置了primary key #在创建时添加主键
create table xuesheng (xuehao int(10) primary key,name varchar(10),kechenghao int(10));
添加主键: 后续给表添加主键
alter table xs3 modify xuehao int primary key;
删除主键:
alter table xs3 drop primary key;
复合主键: 创建复合主键primary key(列1,列2)
create table xs4 (xuehao int(10) , name varchar(5),tel int (11),primary key(xuehao , name));
添加复合主键:
alter table xs3 add primary key(xuehao,name);
删除主键:
alter table xs1 drop primary key;
外键:
非空约束:
address varchar(100) not null,
后续添加非空约束:
alter table xs2 modify id int(10) not null;
删除非空约束:
alter table xs2 modify name varchar(4);
在创建表时添加唯一约束:
tel int(15) unique;
创建表后为一个表添加一个唯一约束:
alter table xs1 modify name varchar(4) unique;
删除唯一约束:
alter tabel xs1 drop index name;
默认约束:
create table xs4 (tel int(11) default '0371'));
为已经存在的表添加默认约束:
alter table xs1 modify address varchar(100) difault 'zhongguo';
删除默认约束:
alter table xs1 modify address varchar(100);
检查约束:
a
where字句:
where子句: 查找level=1的 (不止=,像> >= < <=也可以加进去)
select * from player where level = 1;
where子句多条件: 查找level大于1小于5的玩家
select * from player where level > 1 and level <5;
优先顺序: NOT > AND > OR
查找level大于1小于5的玩家,或者查找exp大于1小于5的玩家:
select * from player where level > 1 and level < 5 or exp > 1 and exp <5;
可以实用()来改变优先级,例如:level > 1 and (level < 5 or exp > 1) and exp <5;
select * from player where level > 1 and (level < 5 or exp > 1) and exp <5;
查找等级为1,3,5的玩家: IN:指定多个值
select *from player where level in(1,3,5);
查找等级在1和10之间的玩家: betwenn ... and ... 指定一个连续范围
select * from player where level between 1 and 10;
与下面的语句执行结果是一样的:
select * from player where level >= 1 and level <=10;
查找等级不在1和10之间的玩家: NOT:取反.
select * from player where level not between 1 and 10;
NOt可以加在任何一个条件语句前面
select * from player where level not in(1,3,5);
查找所有王字开头的玩家: like:模糊查询 %代表任意个字符,_代表任意一个字符
select * from player where name like '王%';
查找名字中带有王字的玩家:
select * from player where name like '%王%';
查找以王字开头的两个字的玩家:
select * from player where name like '王_';
查找王字开头,三个字的玩家:
select * from player where name like '王__';
正则表达式:
查找姓王,名字只有两个字的玩家: regexp:正则表达式
select * from player where name regexp '王.$';
匹配名字中包含王或张的玩家: 下面两个都可以匹配名字中包含王或张的玩家
select * from player where name regexp '[王张]';
select * from player where name regexp '王|张';
查找空值:
查找邮箱为空的玩家:
select * from player where email is null;
不介意用下面的语句,上面的语句看起来更直观,下面的语句仅为科普
select * from player where email <=> null;
查找邮箱不为空的玩家:
select * from player where email is not null;
查找空字符串: null值与空字符串不一样.null表示没有值,空字符串表示只填写了一个空:
select * from player where email = '';
order by排序:
给level升序排列: order by排序 如果不指定,默认升序排序
select * from player order by level;
给level降序排列:
select * from player order by level desc;
先按照等级降序排列,再按照exp升序排列: 如果想要按照多个列来进行排序的话,可以在后面加上列名
select * from player order by level,exp asc;
按照level降序来排列: 使用列的序号来排序.因为第5列是level所以用数字5
select * from player order by 5 desc;
聚合函数:
聚合函数用来对某个列执行一些计算(比如求和、平均值、最大值、最小值)
常用的聚合函数:
AVG() #返回集合的平均值
COUNT() #返回集合中的项目数
MAX() #返回最大值
MIN() #返回最小值
SUM() #求和
所有玩家的总人数:
select count(*) from player;
查询所有玩家的平均等级:
select avg(level) from player;
GROUP BY:分组
查找所有玩家中的男女比例:
select * from player sex, count(*) from player group by sex;
所有玩家中每个等级的玩家有多少名:
select * from player level, count(level) from player group by level;
HAVING:筛选分组后的数据
刚刚的结果中数量大于4的等级有哪些:
select * from player level, count(level) from player group by level having count(level)>4;
对上面语句的结果用order by进行降序排列:
select * from player level, count(level) from player group by level having count(level)>4 order by count(level) desc;
生活中这些例子比较多,比如热搜榜、排行榜
小练习---统计每个姓氏玩家的数量,并将结果按照数量来降序排列,只显示数量大于等于5的姓氏:
select substr(name,1,1) from player
group by substr(name,1,1)
having count(substr(name,1,1)) >=5
order by count(substr(name,1,1)) desc;
LIMIT:限制数量
返回姓氏排行榜的前三名:
LIMIT 3
返回姓氏排行榜第4到第7名:
LIMIT 3,4
DISTINCT:去重
去除重复的记录:
select distinct sex from player;
UNION:合并查询结果集(并集)
查询所有level为1~3和exp为1~3的玩家: UNION默认会去除重复的记录,可以用union all来取消去除重复记录
select * from player where level between 1 and 3
union all
select * from player where exp between 1 and 3;
OR和UNION有点类似,只不过OR是用来合并两个条件的,UNION是用来合并两个查询的
INTERSECT:合并结果集(交集):
查询两个结果的交集:
select * from player where level between 1 and 3
intersect
select * from player where exp between 1 and 3;
EXCEPT:合并结果集(差集):
查询两个结果的差集: 与交集相反
select * from player where level between 1 and 3
except
select * from player where exp between 1 and 3;
6.子查询:
子查询:把一个查询的结果作为另一个查询的条件
查询所有等级大于平均等级的玩家: 先查询平均等级
select avg(level) from player
select * from player where level > (select avg(level) from player);
查询所有玩家的等级和平均等级的差值:
select level, round((secelt avg(level) from player)),level - round((secelt avg(level) from player)) from player;
as别名: 给列起别名方便阅读,提高可读性
select level, round((secelt avg(level) from player)) as average,
level - round((secelt avg(level) from player)) as diff
from player;
用子查询创建一个新的表: 把所有等级小于5的玩家插入到新的表中
create table new_player select * from player where level < 5
把等级在6~10的玩家插入到表中:
insert into new_player select * from player where level between 6 and 10;
查询是否有等级大于100的玩家: exists只会返回0和1 0代表false.1代表true
select exists( select * from player where level => 100)
7.表关联:
表关联的几个类型:
INNER JOIN #内连接:只返回两个表中都有的数据
LEFT #左连接:返回左表中所有的数据和右表中匹配的数据,右表中没有的数据用null填充
RIGHT #右连接:返回右表中所有的数据和左表中匹配的数据,左表中没有的数据用null填充
把玩家表和装备表关联起来:
select * from player
inner join equip
on player.id = equip.player_id;
可以使用left join 和 right join 来连接
用where关键字来指定:
select * from player , equip
where player.id = equip.player_id;
使用别名来指定:
select * from player p, equip e
where p.id = e.player_id;
8.索引
索引是一种用来提高查询效率的数据结构
CREATE INDEX -创建索引:
create [unique|fulltext|spatial] INDEX index_name on tbl_name (index_col_name)
#unique 唯一索引
#fulltext 全文索引#spatial 空间索引
#tbl_name 表名
#indx_col_name 字段名
CREATE INDEX email_index on fast(email)
查看索引:
show index from fast
删除索引:
drop index email_index on fast
通过alter创建索引: name表示的是fast表中要添加索引的字段
alter table fast add index on name_idnex (name)
9.视图:
视图是一种虚拟存在的表,它本身并不包含数据,而是作为一个查询语句,保存在数据字典中.
当我们查询视图的时候,它会根据查询语句的定义,来动态的生成数据,
CREATE VIEW:创建视图
创建一个等级前十的玩家的视图:
create view top10
as
select * from player order by level desc limit 10;
创建的视图可以使用正常的select语句来查询视图.
select * from top10
而且当表中的数据发生变化的时候,视图中的数据也会随着变化.
把第1名的等级改成10:
update player set level = 10 where id = 64
再去查看视图:
select * from top10
修改视图使用alter view语句
把等级前十的玩家倒序排序:
alter view top10
as
select * from player order by level limit 10;
drop view 删除视图:
drop view top10