01sql必知必会王者荣耀带你入门sql

作者: singworld 分类: Sql 发布时间: 2019-07-09 20:11

01创建一张表并修改

DROP TABLE IF EXISTS `player`;
CREATE TABLE `player`  (
  `player_id` int(11) NOT NULL AUTO_INCREMENT,
  `team_id` int(11) NOT NULL,
  `player_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `height` float(3, 0) NOT NULL,
  PRIMARY KEY (`player_id`) USING BTREE,
  UNIQUE INDEX `player_name`(`player_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- 增加字段
ALTER TABLE player ADD (age int(11));
-- 修改字段名
ALTER TABLE player RENAME COLUMN age to player_age;
-- 修改字段类型
ALTER TABLE player MODIFY player_age float(3,1);
-- 删除age
ALTER TABLE player DROP COLUMN player_age;

王者荣耀数据查询处理

王者荣耀建表语句
https://github.com/cystanford/sql_heros_data

-- 查询名字
SELECT name from heros t;
-- 查询全部
SELECT * from heros t;
-- 查询常量
SELECT '王者荣耀' as platform ,name from heros t;
-- 如果常数是数字可以不加引号
SELECT 123 as platform ,name from heros t;
-- 去除重复行
SELECT DISTINCT t.attack_range FROM heros t;
-- 带上英雄名称
SELECT DISTINCT t.name,t.attack_range FROM heros t;
-- 显示英雄名称及最大生命值,按生命值从高到低排序
SELECT name,hp_max FROM heros ORDER BY hp_max DESC;
--按法力值从低到高排序,当法力值相等生命值从高到低排序
SELECT name, hp_max FROM heros ORDER BY mp_max,hp_max DESC;
-- 约束返回数量 使用limit关键字 
SELECT `name`,hp_max FROM heros ORDER BY hp_max LIMIT 1,5;

3总结

  1. 关键字低执行顺序 SELECT FROM WHERE GROUP BY HAVING ORDER BY
  2. SELECT语句的执行顺序 FROM WHERE GROUP BY HAVING SELECT字段 DISTINCT ORDER BY LIMIT
  3. 大部分初学者经常使用SELECT * ,而好的习惯是只查询需要的列
  4. 当你知道只有一条记录的时候,可以使用LIMIT来进行约束

Leave a Reply

Your email address will not be published. Required fields are marked *