mysql学习记录_alter table player modify column name varchar(200)

        回顾一下之前学习过的数据库相关的东西，首先我的操作系统是win10，软件是mysql，图形化界面用的是navicat。

        首先我们需要新建数据库，之后我们设定为game，之后新建查询，以下是一些相关的命令行（增删改）：

	#调用game库
	USE game; 
	
	#创建表
	CREATE TABLE player (
	    id INT,
	    name VARCHAR(10),
	    level INT,
	    exp INT,
	    gold DECIMAL(10, 2)
	);
	DESC player;	#展示表结构
	
	#修改表结构信息
	ALTER TABLE player MODIFY COLUMN name VARCHAR(200);
	ALTER TABLE player RENAME COLUMN name to nick_name;
	ALTER TABLE player ADD COLUMN name VARCHAR(10);
	ALTER TABLE player DROP COLUMN name;
	ALTER TABLE player MODIFY LEVEL INT DEFAULT(1);
	
	drop table player;	#删除表
	
	#增加内容
	INSERT INTO player (id,name) VALUES (1,'kunkun');
	INSERT INTO player (id,name) VALUES (2,'caicai'),(3,'xuxu');
	INSERT INTO player (id,name) VALUES (4,'bingbing');
	
	#修改内容
	UPDATE player set level = 1 where name = 'caicai'
	UPDATE player set level = 100 where name = 'bingbing'
	UPDATE player set exp=0,gold=0;
	
	#删除内容
	delete from player WHERE gold = 0;
	
	#展示player表中所有内容
	SELECT * FROM player;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

        对于查询的部分，如果我们自己创建的库中表内东西太少，geeehour公众号弄了一个数据库，我直接把大佬的数据库用过来了，导入数据库到我们这：

        之后我们常用查询的命令如下：

	#查询
	
	#正常范围查询
	SELECT * FROM `player` where level > 50;
	#AND用法
	SELECT * FROM `player` where level > 1 AND level < 5;
	SELECT * FROM `player` where exp > 1 AND exp < 5;
	#OR用法，and优先级大于or
	SELECT * FROM `player` where exp > 1 AND exp < 5 OR level > 1 AND level < 5;
	#in的用法
	SELECT * FROM `player` where level IN(1,2,3,4,5);
	#between...and...用法
	SELECT * FROM `player` where level BETWEEN 1 AND 10;
	#not的用法
	SELECT * FROM `player` where level NOT BETWEEN 1 AND 10;
	#‘王’字开头的名字
	SELECT * FROM `player` where name LIKE '王%';
	#名字中有‘王’字的
	SELECT * FROM `player` where name LIKE '%王%';#百分号匹配任意个字符
	#名字为王X的
	SELECT * FROM `player` where name LIKE '王_';	#下划线匹配一个字符
	#名字为王XX的
	SELECT * FROM `player` where name LIKE '王__';
	#正则表达式：.表示任意字符；^表示开头；#$表示结尾
	#[abc]表示其中任意字符；[a-z]表示范围内任意字符；a|b表示a或者b
	SELECT * FROM `player` where name REGEXP '^王';#姓王
	SELECT * FROM `player` where name REGEXP '^王.$';#姓王且两字
	SELECT * FROM `player` where name REGEXP '王';	#有王字的
	SELECT * FROM `player` where name REGEXP '[王张]';	#有王或者张字的
	SELECT * FROM `player` where name REGEXP '王|张';		#同上
	
	#练习，查找邮件以zhangsan开头的玩家
	SELECT * FROM `player` where email REGEXP '^zhangsan' 
	SELECT * FROM `player` where email LIKE 'zhangsan%'
	#查找邮件地址以a/b/c开头的玩家
	SELECT * FROM `player` where email REGEXP '^[abc]'
	SELECT * FROM `player` where email REGEXP '^[a-c]'
	#查找邮件地址以net结尾的玩家
	SELECT * FROM `player` where email REGEXP 'net$'
	SELECT * FROM `player` where email LIKE '%net'
	#查找邮箱为空
	SELECT * FROM `player` where email is NULL;
	SELECT * FROM `player` where email is not NULL;
	
	#排序
	SELECT * FROM player ORDER BY `level`;	#默认升序
	SELECT * FROM player ORDER BY `level` DESC;	#desc降序
	SELECT * FROM player ORDER BY `level` DESC ,exp;	#LEVEL降序 exp升序
	SELECT * FROM player ORDER BY 5 DESC;		#第五列降序
	
	#聚合函数：avg求平均，count求项目数，max求最大值，min求最小值，sum求和
	SELECT COUNT(*) FROM player;	#玩家数
	SELECT AVG(level)	FROM player;	#玩家平均等级
	
	#GROUP BY用来分组
	SELECT sex,COUNT(*) FROM player GROUP BY sex;	#分成男女生，查询人数
	SELECT `level`,COUNT(*) FROM player GROUP BY `level`;	
	
	#HAVING用来对分组进行判定
	SELECT `level`,COUNT(`level`) FROM player GROUP BY `level`HAVING COUNT(level)>4;	
	
	SELECT `level`,COUNT(`level`) FROM player GROUP BY `level`HAVING COUNT(level)>4 order by count(level) DESC ;	
	
	#按照姓来统计数量并且排列，要求降序输出个数大于5的，只输出前三个。
	SELECT SUBSTR(name,1,1),COUNT(SUBSTR(name,1,1)) FROM player GROUP BY SUBSTR(name,1,1) HAVING COUNT(SUBSTR(name,1,1))>4 order by count(SUBSTR(name,1,1)) DESC LIMIT 3;	
	
	
	#distince去重
	SELECT DISTINCT sex FROM player;
	
	#UNION并集
	SELECT * FROM `player` where level BETWEEN 1 AND 3
	UNION
	SELECT * FROM `player` where exp BETWEEN 1 AND 3;
	
	#intersect交集
	SELECT * FROM `player` where level BETWEEN 1 AND 3
	INTERSECT
	SELECT * FROM `player` where exp BETWEEN 1 AND 3;
	
	#EXCEPT查找差集（满足a但是不满足b）
	SELECT * FROM `player` where level BETWEEN 1 AND 3
	EXCEPT
	SELECT * FROM `player` where exp BETWEEN 1 AND 3;
	```
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;在我们查询时，有时候可能无法直接查询到我们想要的数据，这时候我们可以通过子查询的方法：
	```
	SELECT AVG(`level`) FROM player;
	select * FROM player WHERE LEVEL >(SELECT AVG(`level`) FROM player);#子查询
	#查询level和平均值
	SELECT `level`,(SELECT avg(level)from player)FROM player;
	#round（）四舍五入
	SELECT `level`,ROUND((SELECT avg(level)from player))FROM player;
	#查询level和平均值和每个level与平均值的差值
	SELECT `level`,ROUND((SELECT avg(level)from player)),`level`-ROUND((SELECT avg(level)from player)) FROM player;
	
	#as起别名+混合输出
	SELECT `level`,ROUND((SELECT avg(level)from player))as average ,
	`level`-ROUND((SELECT avg(level)from player))as diff FROM player;
	
	CREATE TABLE new_player SELECT * from player where level < 5; 
	
	#into插入新table
	INSERT INTO new_player select * from player WHERE `level` BETWEEN 5 AND 10;
	#查询是否存在
	select EXISTS(SELECT * from player WHERE `level` >10)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106

        然后我们需要找到不同表间关联的数据时，通常会用表关联的方法：

	DESC equip	
	SELECT * from player
	inner join equip	#内连接，两者同时存在时打印
	ON player.id = equip.player_id
	
	SELECT * from player
	left join equip		#左连接，左表存在时打印，右边的内容可能为NULL
	ON player.id = equip.player_id
	
	SELECT * from player
	right join equip	#右连接，右表存在时打印，左边的内容可能为NULL
	ON player.id = equip.player_id
1
2
3
4
5
6
7
8
9
10
11
12

        最后我们通过视图的方法来看我们当前的一个数据情况：

	create view top10
	AS
	SELECT * FROM player ORDER BY LEVEL DESC LIMIT 10;	#降序输出最高10个
	
	#视图中的数据随表中数据变化
	SELECT * from top10;
	UPDATE player set level =10 WHERE id = 57
	#修改视图从小到大排level
	ALTER view top10
	AS
	SELECT * FROM player ORDER BY LEVEL LIMIT 10;
1
2
3
4
5
6
7
8
9
10
11

完结，撒花，同时感谢geekhour大佬的知识分享。