SQL

SQL：结构化查询语言，全称是 Structured Query Language

通常分为四类（括号内为他们的一些关键字）：

DDL：数据定义语言（create，drop，alter，truncate等）——定义数据库对象（库、表、列这些）
DML：数据操作语言（insert，update，delete，call等）——用于CUD（增删改）
DQL：数据查询语言（select，where等）——用于查询数据
DCL：数据控制语言（grant，revoke等）——定义数据库的访问权限和安全级别，创建用户

通常我们可以使用命令行或者图形化GUI（比如Navicat）来操作数据库，看个人喜好，对于数据的导入导出，非常推荐使用图形化GUI进行操作，看上去更加人性化和直观

请善用Ctrl + F进行查找

基础

  1  -- 以下如无特殊说明，game均指代为你的数据库名字，注意，没有写分号说明这个行不是代码的结束，也就是代码换行了。
  2  -- 查看当前已经存在的数据库
  3  show databases;
  4  
  5  
  6  #### 创建数据库和表
  7  -- 创建一个新的数据库
  8  create database game;
  9  
 10  -- 删除数据库
 11  drop database game;
 12  
 13  -- 进入到指定数据库中（创建表必须步骤），然后创建表(player)
 14  use game;
 15  create table player (
 16      id INT,
 17      name VARCHAR(100),
 18      level INT,
 19      exp INT,
 20      gold DECIMAL(10,2)
 21  )
 22  
 23  -- 查看表结构
 24  desc player;
 25  
 26  -- 修改表结构类型/字段名，新增字段，删除字段，设置默认值
 27  alter table player modify column name varchar(200); # 这里表示修改name的类型为varchar(200)，player为你的表名，name表示你要修改类型的字段名
 28  alter table player rename column name to nick_name; # 这里表示把字段名name修改为nick_name
 29  alter table player add column last_login datetime;  # 新增一个字段，名为last_login，类型为datetime
 30  alter table player drop column last_login; # 删除字段last_login
 31  alter table player modify level INT default 1;  # 设置level字段的默认值为1，modify后跟字段名和字段类型（不做任何设置的话，默认值为null）
 32  
 33  -- 删除表
 34  drop table player;
 35  
 36  #######################################################################
 37  
 38  #### 对表内数据进行操作（假设表结构为id，name，level，exp，gold一样，类型你可以参考上面创建表的）
 39  -- 新增数据
 40  insert into player (id, name, level, exp, gold) values (1, '张三', 1, 1, 1); # 标准写法
 41  insert into player values (1, '张三', 1, 1, 1);   # 数据顺序和表结构一致，可以使用这种写法，效果同标准写法
 42  insert into player (id, name) values (1, '张三'); # 只写部分对应的名称，没有列出来的部分会使用设置好的默认值进行填充
 43  
 44  insert into player (id, name) values (1, '张三'), (2, '李四'), (1, '王五');   # 你也可以同时插入多条数据，当然它也可以有上面的三种写法
 45  
 46  -- 查询数据
 47  select * from player;   # 查询player表的所有列，*代表所有的列，你也可以指定
 48  
 49  -- 更新数据（where条件子句在#### 条件子句 目录中）
 50  update player set level = 1 where name = '张三'; # 更新player表的数据，把名字为'张三'对的那行数据的level字段设为1
 51  update player set exp = 1; # 更新player表数据，把表内数据的exp值都设为1
 52  update player set exp = 0, gold = 0; # 同上，这里是同时设置了多个字段的值，也可以用where
 53  
 54  -- 删除数据
 55  delete from player where gold = 0; # 删除player表gold为0的全部数据
 56  delete from player;  # 删除表内所有结构
 57  
 58  #######################################################################
 59  
 60  #### 数据导入和导出
 61  -- 数据导出（执行后会要求你输入密码），文件会保存在你的mysql目录下
 62  mysqldump -u 用户名 -p 数据库名称 表名称(省略则导出整个数据库的所有数据) > 文件名
 63  mysqldump -u root -p game > game.sql # 例子，省略了表名称，导出整个数据库的所有数据到game.sql文件中
 64  
 65  -- 数据导入（执行后会要求你输入密码），没有报错就说明成功
 66  mysql -u 用户名 -p 数据库名称 < 文件名
 67  mysql -u root -p game < game.sql # 例子，省略了表名称
 68  
 69  #######################################################################
 70  
 71  #### 条件子句(where, and, or, not, in等)，可以同时使用，优先级为，可以使用括号来提升优先级
 72  -- 可以搭配select，update，insert等一起使用，下面是一些例子
 73  select * from player where level = 1; # 查询表中level字段值等于1的数据
 74  select * from player where level > 1; # 查询表中level字段值大于1的数据
 75  select * from player where level > 1 and level < 5;  # 查询表中level字段值大于1同时小于5的数据
 76  select * from player where level > 1 and level < 5 or exp > 1 and exp < 5;  # 查询表中level字段值大于1同时小于5的，或者exp字段值大于1同时小于5的数据
 77  
 78  select * from player where level in (1, 3, 5); # 查询表中level字段值为1或3或5的数据
 79  select * from player where level between 1 and 10; # 查询表中level字段值在1到10的数据，等价于(where level >= 1 and level <= 10)
 80  select * from player where level not in (1, 3, 5); # 查询表中level字段值不为1或3或5的数据
 81  
 82  -- 通配符查询(%表示任意个字符，_表示一个字符)，你也可以使用正则表达式来实现
 83  select * from player where name like '王%'; # 查询表中name字段值第一个字为'王'的数据
 84  select * from player where name like '%王%'; # 查询表中name字段值含有'王'的数据
 85  select * from player where name like '王_'; # 查询表中name字段值第一个字为'王'，且长度为2的数据
 86  select * from player where name like '王__'; # 查询表中name字段值第一个字为'王'，且长度为3的数据
 87  
 88  -- 正则表达式（常用通配符在下面表格查询）
 89  select * from player where name regexp '^王.$'; # 查询表中name字段值第一个字为'王'，且长度为2的数据
 90  select * from player where name regexp '王'; # 查询表中name字段值含有'王'的数据
 91  select * from player where name regexp '[王张]'; # 查询表中name字段值含有'王'或者'张'的数据
 92  select * from player where name regexp '王|张'; # 查询表中name字段值含有'王'或者'张'的数据 
 93  
 94 -- 练习（表为player，字段名为email） 
 95 # 查找邮件地址以zhangsan开头的玩家 
 96 select * from player where email regexp '^zhangsan'; 
 97 select * from player where email like 'zhangsan%'; 
 98 # 查找邮件地址以a/b/c开头的玩家 
 99 select * from player where email regexp '^[abc]'; 
100 select * from player where email regexp '^[a-c]'; 
101 # 查找邮件地址以new结尾的玩家 
102 select * from player where email regexp 'net$'; 
103 select * from player where email like '%net'; 
104  
105 -- 查询空值 
106 select * from player where email = null; # 错误写法！！！！！！！！ 
107 select * from player where email is null; # 查询player表邮箱为null的数据 
108 select * from player where email is not null; # 查询player表邮箱<不>为null的数据 
109 select * from player where email <=> null; # 等同于(is null)，不推荐 
110 select * from player where email = ''; # 查询空字符串，注意查询空字符串和null是不一样的操作 
111  
112 -- 排序 
113 select * from player order by level asc; # 按player表的level字段升序进行排序(默认为升序，也就是asc可以省略) 
114 select * from player order by level desc; # 按player表的level字段降序进行排序 
115 select * from player order by level desc, exp asc; # 按player表的level字段降序，值相同的情况下，按exp字段进行升序 
116 select * from player order by 5 desc; # 假设level在表里是第5个字段，那么这里等同于按player表的level字段降序进行排序 
117  
118 -- 聚合函数(AVG平均，COUNT总数，MAX最大值，MIN最小值，SUM求和) 
119 select count(*) from player; # 查询player表中数据的总条数 
120 select avg(level) from player; # 查询player表level字段的平均值 
121  
122 -- 分组(可以结合聚合使用)，看不懂最下面有图可以帮助参考 
123 select level, count(level) from player group by level; # 查询每个等级的玩家数量，相当于统计每个level有多少相同的值 
124 select sex, count(*) from player group by sex; # 查询每个性别有多少条数据 
125  
126  
127 select level, count(level) from player group by level having count(level) > 4; # 查询基础上，只看level大于4的数据统计 
128 select level, count(level) from player group by level having count(level) > 4 order by count(level) desc; # 在查询基础上，只看level大于4的数据，同时要求按照level的降序进行展示 
129  
130 -- 截取字符串 
131 select substr(name, 1, 1), count(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 3, 3; 
132 # substr：用于截取字符串，第一个参数为需要被截取的字符串，第二个参数是开始的位置，第三个是截取的长度 
133 # substr(name, 1, 1)，表示截取name的第一个字符，也就是姓氏 
134 # 使用group by对姓氏进行分组，count(substr(name, 1, 1))表示获取姓氏的总条数 
135 # 使用having过滤姓氏数量大于等于五的姓氏 
136 # order by对姓氏总数进行降序排列 
137 # limit：如果只是limit 3，那么就是只返回3条，如果为limit 3, 3，那么就表示从第四个数据开始返回，返回3条（分页查询原理），换句话说，第一个3表示返回的条数，第二个3表示从第几个数据开始返回 
138  
139 -- distinct去重/union合并（并集）/intersect交集/except差集 
140 select distinct sex from player; # 获取全部玩家性别类型（换句话说就是对sex进行了并集，相同的数据只展示一次，也就是展示全部可能的结果） 
141  
142 select * from player where level between 1 and 3 
143 union 
144 select * from player where exp between 1 and 3; 
145 # 相当于获取了所有等级1到3和经验1到3的玩家，等同于(where (level between 1 and 3) or (exp between 1 and 3)) 
146 # union会默认去除重复的记录，如果你不需要去除，可以向下面这样写 
147 select * from player where level between 1 and 3 
148 union all 
149 select * from player where exp between 1 and 3; 
150  
151 select * from player where level between 1 and 3 
152 intersect 
153 select * from player where exp between 1 and 3; 
154 # 获取同时符合两个查询条件的值，等同于(where (level between 1 and 3) and (exp between 1 and 3)) 
155  
156 select * from player where level between 1 and 3 
157 except 
158 select * from player where exp between 1 and 3; 
159 # 查找等级在1到3，但是经验不在1到3的玩家 
160  
161 ####################################################################### 
162  
163 #### 子查询(一个查询结果作为另一个查询结果的条件)，as别名，子查询创建新表/插入数据，exists判断是否有查询结构 
164 -- 子查询 
165 select avg(level) from player; # 查询玩家平均等级 
166 select * from player where level > (select avg(level) from player); # 查询大于平均等级的玩家 
167 select level, round((select avg(level) from player)), round((select avg(level) from player)) from player; 
168 # round用于对数据四舍五入到一个整数 
169 # 这条语句是用于查询所有玩家的等级和平均等级的差值，你可以在最下面看图来帮你理解这句话 
170  
171 -- 别名 
172 select avg(level) as avg_value from player; # 查询玩家平均等级，并且把列名设为avg_value 
173 select level, round((select avg(level) from player)) as average, 
174 round((select avg(level) from player)) as diff 
175 from player; 
176 # 同样这段效果可以在下面找图帮你理解 
177  
178 -- 子查询创建新表/插入数据 
179 create table new_player select * from player where level < 5; # 查询等级小于5的玩家数据，同时新创建一个表叫做new_player，然后把查询到的数据存入进去（表结构和查询的表结构是一致的） 
180  
181 insert into new_player select * from player where level between 6 and 10; # 查询玩家等级在6到10之间的数据，然后把他们插入到表new_player中 
182  
183 -- 判断查询结果是否存在(只会返回0或者1) 
184 select exists (select * from player where level > 100); # 是否存在等级大于100的玩家数据，存在返回1，否则为0

重点（表关联/索引/视图）、

表关联：

用于查询多个表中的数据，关联表之间必须有相同的字段（一般使用主键和外键关联），有以下三种类型

内连接（inner join）：返回两个表中都有的数据

左连接（left join）：返回左表中所有的数据

右连接（right join）：返回右表中所有的数据

 1  select * from player inner join equip on player.id = equip.player_id;
 2  # player为玩家表，有一个id字段，equip为装备表，有一个player_id字段，他们通过这个字段进行内连接
 3  
 4  select * from player left join equip on player.id = equip.player_id;
 5  # player为玩家表，有一个id字段，equip为装备表，有一个player_id字段，他们通过这个字段进行内连接
 6  # 由于是左连接，会返回player的全是数据，然后右边会拼接equip的数据，如果没有和player匹配的equip数据，就用null来填充
 7  
 8  select * from player right join equip on player.id = equip.player_id;
 9  # player为玩家表，有一个id字段，equip为装备表，有一个player_id字段，他们通过这个字段进行内连接
10  # 由于是右连接，会返回equip的全是数据，然后右边会拼接player的数据，如果没有和equip匹配的player数据，就用null来填充
11  
12  -- 你也可以用where来实现，也可以加别名，效果同内连接，例如
13  select * from player p, equip e where p.id = e.player_id;
14  
15  ### 注意：如果不指定查询条件，会产生笛卡尔积（很有可能吃完你的数据库分配的内存导致GG）
16  ### 表关联本质就是笛卡尔积再加上条件过滤实现的

索引：

提高查询效率的数据结果

 1  -- unique:唯一索引，fulltext:全文索引，spatial:空间索引，index_name:索引名称，tbl_name:你的表名，index_col_name:表内一个或者多个字段名（也就是指明我们要对哪些字段进行索引的创建）
 2  -- 通常用于主键或者经常查询的字段（也就是where后面的条件）
 3  create [unique|fulltext|spatial] index index_name on tbl_name (index_col_name,...);
 4  alter table tbl_name add index index_name (index_col_name,...);  # 也可以用这句
 5  
 6  -- 假设我们现在有一张表testDemo，里面有一个字段为email，我们对这个字段进行索引的创建
 7  create [unique|fulltext|spatial] index email_index on testDemo (email);
 8  
 9  -- 查看表的索引(testDemo可以替换成你的表名)
10  show index from testDemo;
11  
12  -- 删除表索引，email_index为索引名称，testDemo为表名称
13  drop index email_index on testDemo;

视图：

动态展示查询的结果（类似于排行榜），这些结果发生变化后（增删改），视图也会动态进行更新

他会把查询的结果保存在一张表里（在view下的表），可以用select进行查询

1  -- 创建视图
2  create view top10 as select * from player order by level desc limit 10; # 创建一个名为top10的视图，保存玩家等级中最高的前十名玩家数据
3  
4  -- 修改视图
5  alter view top10 as select * from player order by exp desc limit 10; # 修改名为top10的视图，把他更改为保存玩家经验最高的前十名玩家数据
6  
7  -- 删除视图(top10为视图名字)
8  drop view top10