MySQL常用语句

 

1 操作数据库

-- ① 操作库


-- 1 查库列表
SHOW DATABASES

-- 2 删除库  
DROP DATABASE IF EXISTS sh;

-- 3 如果库不存在则创建  标记:  默认编码为utf8
CREATE DATABASE IF NOT EXISTS sh;

-- 4 使用库
USE sh

-- 5 显示表列表
SHOW TABLES

-- 6 视图列表  
SHOW TABLES LIKE '%v%';

-- 7 存储过程列表  名称以p开头  标记:  procedure  status  like  '后%'
SHOW PROCEDURE STATUS LIKE 'p%'

-- 8 存储函数列表  名称以f开头
SHOW FUNCTION STATUS LIKE 'f%'

-- 9 触发器列表
SHOW TRIGGERS

-- 10 查MySQL的默认存储引擎  标记:  5.5默认InnoDB  每个版本都不一样
SHOW VARIABLES LIKE '%storage_engine%';

-- 11 自动提交  标记: mysql默认自动开启  InnoDB支持事务,所以是
SHOW VARIABLES LIKE '%autocommit%';

-- 12 存储引擎列表  标记: 默认InnoDB
SHOW ENGINES; 

-- 13 索引列表  标记: 需要根据表名查询
SHOW INDEX FROM deck

-- 14 查看当前端口号
SHOW GLOBAL VARIABLES LIKE 'port';

-- 显示库编码  标记: 编码utf8是默认的
SHOW CREATE DATABASE sh

-- 15 登录  mysql目录bin - 地址栏 - cmd    mysql -u root -p
-- 16 连接localhost  用户名root  密码root
-- 17 启动服务 net start mysql   停止stop

-- 18 查看mysql版本号
SELECT VERSION();

-- 19 查看当前所在的库
SELECT DATABASE();

-- 20 不切换库  要求: 在sh库中查询db库中的rack_sql表
SELECT * FROM db.rack_sql

 

2 创建数据表

-- ② 建表 



-- 1 如果表存在, 则删除
DROP TABLE IF EXISTS deck

-- 2 要求:  建表deck  id列自增  主键id  存储引擎InnoDB  表自增16  编码utf8
CREATE TABLE IF NOT EXISTS deck(
  id          INT(50)       AUTO_INCREMENT COMMENT '自增' ,
  c_name      VARCHAR(50)   ,
  attribute   CHAR(50)        ,
  race        CHAR(50)      ,
  atk         INT(50)       ,
  def         INT(50)       ,
  PRIMARY KEY (id)
)ENGINE=INNODB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

-- 3 查表结构  查表中字段类型和约束  标记:  describe可缩写成desc   
DESCRIBE deck

-- 查看表结构  5条语句效果相同
DESC t;        -- 建议使用  和降序的关键字一样
DESCRIBE t;    -- 可缩写成desc
SHOW COLUMNS IN t;
SHOW COLUMNS FROM t;
EXPLAIN t;

-- 4 查建表语句
SHOW CREATE TABLE deck

-- 5 查看表的状态
SHOW TABLE STATUS LIKE 'deck'

-- 6 创建表deck2,并将deck表的结构和数据全部复制  此时deck表是没有数据的
CREATE TABLE deck2 SELECT * FROM deck

-- 7 创建表deck3 并将deck表的结构复制  不要一复制数据  标记: like
CREATE TABLE deck3 LIKE deck

-- 8 在14的deck3表中  插入deck表全部数据  标记: insert into    6=7+8
INSERT INTO deck3 SELECT * FROM deck


-- 9 表名deck6  int/integer  bigint对应Long  s3是blob  s4日期值  s5日期和时间
CREATE TABLE IF NOT EXISTS deck6(
  s1    INTEGER(11)        ,
  s2    BIGINT(20)         ,
  s3    FLOAT(10.3)        ,
  s4    BLOB               ,
  s5    DATE               ,
  s6    DATETIME       
)ENGINE=INNODB;


-- 10 要求: deck7  s1是tinyint  s2是bigint  s3金额decimal   s4长文本  
-- 多个主键  列为s1,s2,s3  唯一索引  全文索引  标记: 存储引擎myisam
CREATE TABLE IF NOT EXISTS deck7(
  s1    TINYINT(50)         ,                                                                                                                                                                                                                                                                                                                                                                                                                          
  s2    BIGINT(50)          ,                                                                                                                                                                                                                                                                                                                                                                                                       
  s3    DECIMAL(10,0)       ,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  s4       TEXT                ,                                                                                                                                                                                                                                                                                                                                                                   
  PRIMARY KEY (s1,s2,s3)    ,
  UNIQUE INDEX s1Idx (s1)   ,
  FULLTEXT INDEX s6Idx (s4) ,
  INDEX multiIdx (s1,s2,s3)                                                                                                                                                                                                                                                                                                                                                                                                
)ENGINE=MYISAM;

-- 查表结构
DESCRIBE deck7


-- 11 定义外键  关联表  父表的主键id被子表所关联  要求: 引擎innodb
CREATE TABLE f1(
 id INT(50)PRIMARY KEY
)ENGINE=INNODB;

-- 子表:  外键名f 引擎innodb  标记: constraint  foreign key  references f1(主id)
CREATE TABLE f2 (
  id INT(50) PRIMARY KEY  AUTO_INCREMENT,
  race INT(50),
  CONSTRAINT f FOREIGN KEY(race) REFERENCES f1(id)
)ENGINE=INNODB;

-- 12 子表外键设值后  父子值都不可改  父表不能删除行(表)  子表可删除行(表)
DROP TABLE f2;
DROP TABLE f1;

 

3操作表

-- ③ 操作表 


-- 1 改表名  要求:  修改表名后,查看效果  两条语句一起执行
RENAME TABLE deck2 TO deck1;
SHOW TABLES;

-- 2 删除上面修改名称的表  表结构和数据一起删除   
DROP TABLE IF EXISTS deck1


-- 由于后续需要使用到5张表的结构  所以,要在这里提前创建好


-- 恢复上面删除的deck2  创建时要求一并插入数据
CREATE TABLE deck2 SELECT * FROM deck

-- deck3没有被删除过,不用管  标记: 易错点,SQLyog中增删改不会及时刷新

-- 创建deck4, 并插入deck全部数据
CREATE TABLE deck4 SELECT * FROM deck

-- 创建deck5  列为id  attribute deckRace effect  标记: engine=innodb
CREATE TABLE deck5 (
  id INT(50),
  attribute CHAR(50),
  deckRace CHAR(50),
  effect CHAR(50)
)ENGINE=INNODB;

 

4操作列

-- ④ 操作表中的列



-- 查表结构  标记: describe可缩写成desc  
DESC deck6

-- 1 新增一列为第一列  要求: 列名s0 类型decimal(10,2) 非空  标记: first
ALTER TABLE deck6 ADD COLUMN s0 DECIMAL(10,2) FIRST

--    新增一列到最后  列名s0 类型decimal  标记:  after  不写时也是默认到最后一行
ALTER TABLE deck6 ADD COLUMN s7 DECIMAL(10,2) AFTER s6

-- 2 将新列s0移动到第三列  新列名为s0  原第二列是s2  标记:  modify   after  
ALTER TABLE deck6 MODIFY COLUMN s0 DECIMAL(10,2) AFTER s2

--    不移动位置 只修改列的类型  将新添加的列  修改类型为int(50)
ALTER TABLE deck6 MODIFY COLUMN s0 INT(50)

--    修改类型又修改位置  要求: 列名s0  类型从int修改成decimal  位置从3改成5  
ALTER TABLE deck6 MODIFY COLUMN s0 DECIMAL(10,2) AFTER s4

-- 3 修改列名  s0改成s05  类型decimal  约束非空,默认值0.00  标记:  change
ALTER TABLE deck6 CHANGE COLUMN s0 s05 DECIMAL(10,2) NOT NULL DEFAULT '0.00'

-- 查表结构
DESC deck6

-- 4 修改列的约束  列为s05  类型改int  将3中新增的约束 非空和默认值 取消掉
ALTER TABLE deck6 MODIFY COLUMN s05 DECIMAL(10,2) 

-- 5 删除列s7  就是之前用不同的方式(位置) 新建出来的列s9
ALTER TABLE deck6 DROP COLUMN s7

 

5 约束

-- ⑤ 约束  在列上  新增/删除  主键,非空,自增,默认值  标记:  表和列已经存在



-- 取消约束的方法1  逐步取消自增, 默认值, 主键, 非空


-- 查表结构  deck表id列  主键(有非空) 自增 无默认值  标记: extra下auto_increment
DESC deck

-- 1 要求:  在id列是主键时, 取消自增, 此方式会给个默认值0  标记:  modify int
ALTER TABLE deck MODIFY id INT

--    取消自增的方式2  要求: 不能执行,因为不在"一路畅通的体系"
ALTER TABLE deck CHANGE COLUMN id id INT(32) NULL

-- 2 在id列是主键时, 取消默认值  因为1会给默认值  标记: 双alter 再drop default 
ALTER TABLE deck ALTER COLUMN id DROP DEFAULT

-- 3 取消自增,取消默认值后,可以取消主键  要求: 取消所有主键  剩下非空约束
ALTER TABLE deck DROP PRIMARY KEY

-- 4 取消非空  标记: 就是在操作列时改名但名不变  change 列名,不改名 类型  null
ALTER TABLE deck CHANGE COLUMN id id INT(11) NULL



-- 将无主键,空,无自增,无默认值  还原成: 主键,非空, 自增,无默认值


-- 5 标记: alter  change  旧名  新名  主键  非空  自增  注释
ALTER TABLE deck CHANGE COLUMN id id INT(50) 
PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT 'id列,主键,非空,自增,无默认值'

-- 查表结构  上面是建表时一次性创建列的约束, 然后分步取消, 又一次性创建来恢复
DESC deck


-- 取消约束的方法2  先取消主键  再取消非空和默认值  
-- 需要手动取消自增(SQL语句会导致取消后有默认值)  


-- 6 要求: 取消主键  剩下非空约束和默认值  表中有数据,所以不能删除列
ALTER TABLE deck DROP PRIMARY KEY

-- 7 要求: 取消非空约束和默认值  标记: 如果有默认值的话,这更像是覆盖了旧列约束
ALTER TABLE deck CHANGE COLUMN id id INT(32) NULL



-- 添加约束的方法2  从0到有,逐一添加约束  标记: 方法1是在建表时就添加 


-- 查表结构  
DESC deck

-- 8 添加主键  标记: 主键自带非空约束 
ALTER TABLE deck MODIFY id INT(50) PRIMARY KEY;

-- 9 添加默认值  标记: 设置默认值  所以是alter alter set
ALTER TABLE deck ALTER id SET DEFAULT '0'

-- 10 取消默认值  标记:  个人习惯  主键是自增,不是默认值  
ALTER TABLE deck ALTER id DROP DEFAULT

-- 11 添加自增  标记: 取消默认值后, 才能添加自增  反之也是
ALTER TABLE deck MODIFY id INT(50) AUTO_INCREMENT

-- 查表结构  
DESC deck

-- 12 添加非空约束  deck  c_name列
ALTER TABLE deck MODIFY c_name VARCHAR(50) NOT NULL

-- 13 方法2  带上原有的所有约束  要求: 不能执行,因为不在"一路畅通的体系"    
ALTER TABLE deck MODIFY race VARCHAR(50)
PRIMARY KEY NOT NULL AUTO_INCREMENT DEFAULT '新属性' COMMENT '测试专用'
   

 

6 利用触发器插入数据

-- ⑥ 触发器 根据练习的实际情况(对deck表的增删改)  创建触发器  3个不同类型



-- 1 触发器  当向deck表插入数据之后, 要在myinsert表中留下记录
CREATE TABLE myinsert(
 id INT(50) AUTO_INCREMENT COMMENT '记录插入的次数',
 record VARCHAR(50),
 PRIMARY KEY (id)
);


-- 创建触发器1  向deck表插入数据后, 会触发  标记: trigger
CREATE TRIGGER t1 AFTER INSERT ON deck
FOR EACH ROW INSERT INTO myinsert (record) VALUES ('after insert');



-- 2 当向deck表修改数据之后, 要在myupdate表中留下记录
CREATE TABLE myupdate(
 id INT(50) AUTO_INCREMENT COMMENT '记录修改的次数',
 record VARCHAR(50),
 PRIMARY KEY (id)
);


-- 创建触发器2  当向deck表修改数据之后, 会触发  标记: 在update后要有on
CREATE TRIGGER t2 AFTER UPDATE ON deck
FOR EACH ROW INSERT INTO myupdate (record) VALUES ('after update');



-- 3 当向deck表删除数据之前, 要在mydelete表中留下记录
CREATE TABLE mydelete(
 id INT(50) AUTO_INCREMENT COMMENT '记录删除的次数',
 record VARCHAR(50),
 PRIMARY KEY (id)
);


-- 创建触发器3  向deck表删除数据之前, 会触发
CREATE TRIGGER t3 BEFORE DELETE ON deck
FOR EACH ROW INSERT INTO mydelete (record) VALUES ('before delete');

 

7 利用存储过程恢复数据

-- ⑦ 存储过程

/* 1 插入数据  deck共5张表  给单表/多表/子查询/视图等练习模块使用
   表结构已经写好  不能删除,因为删除后触发器也会被删除  不能删库 
   写个存储过程  使用事务  用一个变量记录事务回滚还是提交了
  
truncate table deck;  truncate table deck2; truncate table deck3;  
truncate table deck4;  truncate table deck5;

*/

DROP PROCEDURE IF EXISTS five_table;

DELIMITER //
CREATE PROCEDURE five_table (OUT back5 VARCHAR(50))
  BEGIN
  DECLARE error INTEGER DEFAULT 0;  
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error=1;  
  START TRANSACTION ;
  INSERT INTO deck (id,c_name,attribute,race,atk,def) VALUES
  (1,  '自然木鳞龙',      '地',  '龙族',      2500, 1800) ,
  (2,  '卡通黑魔术师',    '暗',  '魔法师族',  2500, 2100) ,
  (3,  '卡通黑魔导少女',  '暗',  '魔法师族',  2000, 1700) ,
  (4,  '卡通机械巨人',    '地',  '机械族',    3000, 3000) ,
  (5,  '卡通混沌战士',    '地',  '战士族',    3000, 2500) ,
  (6,  '卡通电子龙',      '光',  '机械族',    2100, 1600) ,
  (7,  '卡通恶魔',        '暗',  '恶魔族',    2500, 1200) ,
  (8,  '卡通鹰身女郎',    '风',  '鸟兽族',    1300, 1400) ,
  (9,  '卡通破坏剑士',    '地',  '战士族',    2600, 2300) ,
  (10, '卡通人鱼',        '水',  '水',        1400, 1500) ;
  INSERT INTO deck2 (id,c_name,attribute,race,atk,def) VALUES
  (1,  '自然木龙兽',      '地',  '兽族',      2800, 2400)  ,
  (2,  '自然兽',          '地',  '兽族',      2200, 1700)  ,
  (3,  '自然地龟',        '地',  '岩石族',    2350, 1600)  ,
  (4,  '自然蜻蜓',        '地',  '昆虫族',    1200, 400)   ,
  (5,  '自然樱桃',        '地',  '植物族',    200,  200)   ,
  (6,  '自然竹笋',        '地',  '植物族',    2000, 2000)  ,
  (7,  '自然蝴蝶',        '地',  '昆虫族',    500,  1200)  ,
  (8,  '自然南瓜',        '地',  '植物族',    1400, 800)   ,
  (9,  '自然壁',          '地',  '岩石族',    1500, 1000)  ,
  (10, '自然木鳞龙',      '地',  '龙族',      2500, 1800)  ;
  INSERT INTO deck3 (id,c_name,attribute,race,atk,def) VALUES
  (1,  '超魔导剑士',      '暗',  '魔法师族',  2900, 2400)  ,
  (2,  '混沌之黑魔术师',  '暗',  '魔法师族',  2800, 2600)  ,
  (3,  '魔导战士',        '暗',  '魔法师族',  1600, 1000)  ,
  (4,  '熟练的黑魔术师',  '暗',  '魔法师族',  1900, 1700)  ,
  (5,  '魔力引导者',      '地',  '魔法师族',  1700, 1400)  ,
  (6,  '魔导骑士',        '光',  '魔法师族',  1600, 2000)  ,
  (7,  '黑魔导少女',      '暗',  '魔法师族',  2000, 1700)  ,
  (8,  '黑魔术师',        '暗',  '魔法师族',  2500, 2100)  ,
  (9,  '黑魔法神官',      '暗',  '魔法师族',  3200, 2600)  ,
  (10, '神圣魔导王',      '暗',  '魔法师族',  2700, 1700)  ;
  INSERT INTO deck4 (id,c_name,attribute,race,atk,def) VALUES
  (1,  '神兽王 巴巴罗斯', '地',  '兽战士族',  3000, 1200)  ,
  (2,  '兽神机王',        '地',  '兽战士族',  3800, 1200)  ,
  (3,  '武神器 八咫',     '光',  '鸟兽族',    1700, 800)   ,
  (4,  '武神-倭',         '光',  '兽战士族',  1800, 200)   ,
  (5,  '武神-御雷',       '光',  '兽战士族',  1900, 1500)  ,
  (6,  '漆黑的豹战士',    '暗',  '兽战士族',  1600, 600)   ,
  (7,  '武神器-蜂',       '光',  '兽族',      1700, 500)   ,
  (8,  '武神帝-须佐之男', '光',  '兽战士族',  2400, 1600)  ,
  (9,  '武神商-迦具士',   '光',  '兽战士族',  2500, 2000)  ,
  (10, '武神器-丛云',     '光',  '兽族',      1600, 900)   ;
  INSERT INTO deck5 (id,attribute,deckRace,effect) VALUES
  (1, '地','效果极强','攻击力中等'),
  (2, '暗','效果强','攻击力高'),
  (3, '光','效果强','攻击力高'),
  (4, '炎','效果一般','攻击力中等'),
  (5, '神','效果极强','攻击力极高');
  IF error = 1 
  THEN    ROLLBACK;  SET back5 = '有报错,事务回滚了';    
  ELSE    COMMIT;    SET back5 = '无报错,事务提交了';
  END IF;
END //
DELIMITER ; 

CALL five_table(@back5);

SELECT @back5 

 

8 操作数据

-- ⑧ 操作数据


-- 1 插入数据  要求: deck表
INSERT INTO deck(id,c_name,attribute,race,atk,def) VALUES
(1,  '自然木鳞龙',      '地',  '龙族',      2500, 1800) ,
(2,  '卡通黑魔术师',    '暗',  '魔法师族',  2500, 2100) ,
(3,  '卡通黑魔导少女',  '暗',  '魔法师族',  2000, 1700) ,
(4,  '卡通机械巨人',    '地',  '机械族',    3000, 3000) ,
(5,  '卡通混沌战士',    '地',  '战士族',    3000, 2500) ,
(6,  '卡通电子龙',      '光',  '机械族',    2100, 1600) ,
(7,  '卡通恶魔',        '暗',  '恶魔族',    2500, 1200) ,
(8,  '卡通鹰身女郎',    '风',  '鸟兽族',    1300, 1400) ,
(9,  '卡通破坏剑士',    '地',  '战士族',    2600, 2300) ,
(10, '卡通人鱼',        '水',  '水',        1400, 1500) ;

-- 2 修改数据
UPDATE deck SET atk=3000 WHERE id=1

-- 3 删除数据
DELETE FROM deck WHERE id=1

-- 查询数据
SELECT * FROM deck

-- 4 快速删除  要求:  deck  全部数据  标记: truncate 
TRUNCATE TABLE deck;

-- 5 查询数据  要求: 5表查询  标记: 查询方式为视图+左连接  
DROP VIEW IF EXISTS v_five

CREATE VIEW v_five AS
SELECT * FROM deck  UNION ALL
SELECT * FROM deck2 UNION ALL 
SELECT * FROM deck3 UNION ALL
SELECT * FROM deck4

SELECT a.id,a.c_name,a.attribute,a.race,a.atk,a.def,b.deckRace,b.effect FROM 
v_five a LEFT JOIN deck5 b ON a.attribute=b.attribute


-- 6 插入检索出来的数据  在表t中插入一条数据  数据为deck2中id为3
INSERT INTO t(id,c_name) SELECT id,c_name FROM deck2 WHERE id=3;

-- 7 修改t中多个值
UPDATE t SET c_name='自然兽' , atk=2200 WHERE c_name='自然地龟'; 

SELECT * FROM t;

UPDATE t SET c_name='自然兽', atk=2200, def = 1700 WHERE c_name='自然兽'; 

 

9和10子句查询

-- ⑨ 子句查询的应用



-- 1 and和or的优先级  要求:  必要为地属性  任一为atk>2500 或者 机械族 结果2,5,9
SELECT * FROM deck WHERE attribute='地' AND (atk>2500 OR race='机械族');


-- 2 必要条件atk>2100 想选魔法师族,不选卡通恶魔因为防御力1200弱  结果1,2,3,4,5,9
SELECT * FROM deck WHERE 
(atk>2100 AND def !=1200) OR race='魔法师族';


-- 3 必要条件地属性 排除6个 必要条件atk>2700  四个中再排除两个  机械族再排除一个
-- 有特点的也可以选出来  名称中有自然字样的  种族是魔法师族的
-- 将必要条件的结果 和 任一条件的结果 合并 要求:只用and和or  结果id为1,2,3,4
SELECT * FROM deck WHERE (attribute='地' AND atk>2700 AND race='机械族')
OR c_name LIKE '%自然%'  OR race='魔法师族';



-- 4 查指定数值之外的记录行  要求: 使用not in  条件为多条  值为2000,2500
SELECT * FROM deck WHERE atk NOT IN(2000,2500);

-- 5  要求: 使用not  必要条件atk>2300  战士族除外  结果id为1,2,4,7
SELECT * FROM deck WHERE NOT race='战士族' AND atk>2300;

-- 6 范围查询  要求:  between  范围值为2000到2600  标记: 含两端点 结果1,2,3,6,7,9 
SELECT * FROM deck WHERE atk BETWEEN 2000 AND 2600;

--   范围查询方法2  要求: >=和<=  标记:  都是and拼接多条件  between可读性高
SELECT * FROM deck WHERE atk>=2000 AND atk<=2600;

-- 7 要求: atk>2700 或者 名中有'黑魔'  不去重  结果2,3,4,5
SELECT * FROM deck WHERE atk>2700 OR c_name LIKE '%黑魔%';

-- 8 双列指定排顺序  要求: atk降序  def升序  标记: 降序为第一条件,降序后升序
SELECT * FROM deck ORDER BY atk DESC ,def ASC;

-- 9  要求: 检索的第2列降序  第3列升序  第1列不要求  标记: 用数字指定列排序
SELECT id,atk,def FROM deck ORDER BY 2 DESC,3 ASC;

-- 10 要求: 显式书写  关键字写全  内容为:分组,过滤,排序升序  数值随意 
SELECT * FROM deck GROUP BY atk HAVING atk>2100 ORDER BY atk ASC;

-- 11 要求: deck表  atk表  从大到小的顺序  前三名  不去重 
SELECT * FROM deck ORDER BY atk DESC LIMIT 0,3;

-- 12   要求:  atk列  从大到小的顺序  只查询第4和第5名  结果要是id值1和2
SELECT * FROM deck ORDER BY atk DESC LIMIT 3,2;

-- 13 汇总  要求: 检索的列为atk,条数,分组的组合计  去重  降序  汇总
SELECT atk,COUNT(atk),SUM(atk) FROM deck GROUP BY atk DESC WITH ROLLUP;


-- 14 对比  要求:  只在一行中统计合计值  deck表  atk合计值22900  def合计值19100
SELECT SUM(atk),SUM(def) FROM deck;

--    要求: 在查询结果的基础上新增一行, 用来统计atk汇总值22900 标记: 也可distinct
SELECT atk,COUNT(atk),SUM(atk) 
FROM deck GROUP BY atk DESC WITH ROLLUP;


-- 15 要求: 检索atk列,条数,和 不去重  子句为去重,降序,汇总,过滤  结果为2,1,3条
SELECT atk,COUNT(atk),SUM(atk) 
FROM deck GROUP BY atk DESC WITH ROLLUP HAVING atk>2100;


-- 16 把having当成where  要求: atk>2700 或 名有'魔导' 不去重,要过滤  id为3,4,5
SELECT * FROM deck HAVING atk>2700 OR c_name LIKE '%魔导%';


-- 17 要求:  排除atk值为2000的数据  对atk列  去重  降序  
SELECT * FROM deck GROUP BY atk DESC HAVING atk!=2000;



-- ⑩ 子句  mysql的7个子句总结



-- 1 select子句  标记: 检索通配符*,单列,全部列
SELECT id,c_name,attribute,race,atk,def FROM deck;

-- 2 from子句  要求:  deck和deck5  结果为50条  标记: 交叉连接(笛卡尔积)  
SELECT * FROM deck a, deck5 b;

-- 3 where 子句  要求: 两个数值列大于2100  标记: 条件
SELECT * FROM deck WHERE atk>2100 AND def >2100;

-- 4 group by 子句  要求: 将atk分组 检索条数 针对每组  标记: 分组  
SELECT atk,COUNT(*) FROM deck GROUP BY atk;

-- 5 having 子句  要求: deck表  筛选atk值  条件为重复记录数大小1  标记: 筛选
SELECT atk,COUNT(*) FROM deck GROUP BY atk HAVING COUNT(*)>1;

-- 6 order by子句  要求: atk降序  def升序  标记: 排序
SELECT * FROM deck ORDER BY atk DESC , def ASC;

-- 7 limit子句  要求: 取出id为4,5  标记: 限制
SELECT * FROM deck LIMIT 3,2;

 

11 聚合函数

-- 11 聚合函数  记录数/合计/平均/最大/最小  合并


-- 1 条数  要求: 去重  
SELECT COUNT(DISTINCT atk) FROM deck;

-- 2 合计值 
SELECT SUM(DISTINCT atk) FROM deck;

-- 3 平均值
SELECT AVG(DISTINCT atk) FROM deck;

-- 4 最大值  标记: 最大值时  去重无意义
SELECT MAX(atk) FROM deck;

-- 5 最小值
SELECT MIN(atk) FROM deck;

-- 6 将某列的值拼接成str  要求:  race列  去重  结果为7条  标记: group_concat   
SELECT GROUP_CONCAT(DISTINCT race) FROM deck;


-- 7 要求:　６个聚合函数  别名  去重      
SELECT 
COUNT(DISTINCT atk) AS atkCount ,
SUM(DISTINCT atk) AS atkSum,
AVG(DISTINCT atk) AS atkAvg,
MAX(atk) AS atkMax,
MIN(atk) AS atkMin,
GROUP_CONCAT(DISTINCT race) AS atkConcat
FROM deck;


-- 8 聚合函数max()和with rollup的区别

-- 聚合函数只显示一行结果
SELECT SUM(DISTINCT atk) FROM deck;

-- 聚合函数+汇总 则显示列的这个列的所有行, 并且新增一行作为记录合计值使用
SELECT SUM(DISTINCT atk) FROM deck GROUP BY atk WITH ROLLUP;


-- 9 聚合函数max()和倒序限制  取第一名的区别
SELECT MAX(atk) FROM deck;

SELECT atk FROM deck ORDER BY atk DESC LIMIT 0,1;


-- 10 拼接成一个字符串
SELECT CONCAT(id,c_name,attribute,race,atk) FROM deck

-- 可以添加分隔符  ','  就是将分隔符逗号当成一个str来拼接了  
SELECT CONCAT(id,',',c_name,',',race,',',atk) FROM deck


-- 11 在concat基础上添加分隔符  可一次性指定分隔符  去除了添加多个分隔符的繁琐
SELECT CONCAT_WS(',',id,c_name,race,atk) FROM deck


-- 12 在分组后  将组中的值合并  以逗号分隔  以str表示
SELECT GROUP_CONCAT(atk) FROM deck GROUP BY atk

-- 如果不用分组子句  则是直接返回列的所有的值
SELECT GROUP_CONCAT(id) FROM deck 

-- 也可以去重
SELECT GROUP_CONCAT(DISTINCT race) FROM deck;

 

12 REGEXP

-- 12 regexp   


-- 1 要求:  前缀'卡通'  后缀4字符  结果id为2,4,5,8,9  标记: like 下划线的个数
SELECT * FROM deck WHERE c_name LIKE '卡通____';

-- 2 要求:  第一位2,二位任意,三位0,四位0  结果id为2,3,7,9,10  标记: 逗号  
SELECT * FROM deck WHERE atk REGEXP '2.00';

-- 修改atk值  要求: 写两条语句  将id为8的atk值改成2350  id为10的改成2450  
UPDATE deck SET atk=2350 WHERE id=8;
UPDATE deck SET atk=2450 WHERE id=10;

-- 3 要求: atk列  第二位是1-5  第三位是1-5的数值  标记:  []
SELECT * FROM deck WHERE atk REGEXP '2[1-5][1-5]0';

-- 新上述修改的值还原  要求: 上述id为8的atk值改成1300  id为10的改成1400
UPDATE deck SET atk=1300 WHERE id=8;
UPDATE deck SET atk=1400 WHERE id=10;



-- 4 改名  要求:  id为10  在原名前加上.5  卡通黑魔术师
UPDATE deck SET c_name='.5卡通黑魔术师' WHERE id=2;

-- 5 查前缀  要求:  c_name列  名称前缀是.  标记:  ^定位  [双转义句号]
SELECT * FROM deck WHERE c_name REGEXP '^[0\\.]';

-- 将上述修改的值还原  原值需要自己从上2条语句中判断
UPDATE deck SET c_name='卡通黑魔术师' WHERE id=2;

 

13 多表查询 合并

-- 13 多表查询 之 合并



-- 1 多表合并  去重  标记:自然木鳞龙等都重复, 但id不重复, 所以不能算是重复
SELECT * FROM deck 
UNION
SELECT * FROM deck2;


-- 2 多表合并  要求:  合并不去重  四表  从大到小  前三  结果为6条,最小2900   
SELECT * FROM deck 
UNION ALL
SELECT * FROM deck2
UNION ALL
SELECT * FROM deck3
UNION ALL
SELECT * FROM deck4
ORDER BY atk DESC LIMIT 0,6;


-- 3 union合并多表且有条件  要求: 合并不去重  仅一个where  atk>2100
SELECT * FROM (
  SELECT * FROM deck 
  UNION ALL
  SELECT * FROM deck2
  UNION ALL
  SELECT * FROM deck3
  UNION ALL
  SELECT * FROM deck4
) AS fourTable
WHERE atk>2100;


-- 4 上述的语句无法快速的查看效果  要求:  上述语句条件为19条
SELECT COUNT(*) FROM (
  SELECT * FROM deck 
  UNION ALL
  SELECT * FROM deck2
  UNION ALL
  SELECT * FROM deck3
  UNION ALL
  SELECT * FROM deck4
) AS fourTable
WHERE atk>2100;


-- 5 要求: 4表合并的结果中  哪些atk是重复的?  重复的数值是多少?
SELECT f4.atk,COUNT(f4.atk)-1 FROM (
  SELECT * FROM deck 
  UNION ALL
  SELECT * FROM deck2
  UNION ALL
  SELECT * FROM deck3
  UNION ALL
  SELECT * FROM deck4
) AS f4
WHERE f4.atk>2100 
GROUP BY f4.atk DESC
HAVING COUNT(f4.atk)>1;

 

14 多表查询 连接

-- 14 连接多个表


-- 1 内连接  要求:　内连接  左表deck右表deck2  检索列为a和b的c_name
SELECT a.c_name,b.c_name FROM 
deck a INNER JOIN deck2 b ON
a.id=b.id;


-- 2 左连接  要求: deck和5  属性关联  结果有2条右表部分为null  标记: 右right
SELECT * FROM 
deck a LEFT JOIN deck5 b ON
a.attribute=b.attribute;

 
-- 3 聚合函数+内连接  要求: 左表atk平均值  右表同样  去重
--   标记: 聚合函数已经是查询一条了, 如果指明id=1则要查询这第一条了
--   结果值为2128.5714  和  1655.0000  前面的语句没有少执行的话, 就是这个值 
SELECT AVG(DISTINCT a.atk),AVG(DISTINCT b.atk) FROM 
deck a INNER JOIN deck2 b ON
a.id=b.id;


--   要求: deck的atk平均值为2128.5714  deck2的为1655.0000  均去重
SELECT AVG(DISTINCT atk) FROM deck;
SELECT AVG(DISTINCT atk) FROM deck2;


-- 5 三表连接  要求:  内连接  三表连接  检索列为a.b.c的c_name
SELECT a.c_name,b.c_name,c.c_name FROM (
  deck a INNER JOIN deck2 b ON a.id=b.id
)
INNER JOIN deck3 c ON 
a.id=c.id;


-- 6 四表连接  要求: 内连接  四表连接  检索列为a.b.c的c_name
SELECT a.c_name,b.c_name,c.c_name,d.c_name FROM(
  (deck a INNER JOIN deck2 b ON a.id=b.id)
  INNER JOIN deck3 c ON a.id=c.id
)
INNER JOIN deck4 d ON a.id=d.id;

-- 7 where连接两表  要求: 检索列列用表别名  表设别名ab
SELECT a.c_name,b.c_name
FROM deck a, deck2 b
WHERE a.id=b.id;


-- 8 隐式的交叉连接 
SELECT a.c_name,b.effect
FROM deck a, deck5 b

-- 显式的交叉连接  cross
SELECT a.c_name,b.effect
FROM deck a CROSS JOIN deck5 b


-- 9 隐式的内连接  无inner join  中间表是两表笛卡尔积
-- 原来内连接也可以用逗号来代替inner join    
SELECT a.c_name,b.effect
FROM deck a, deck5 b 
WHERE a.attribute=b.attribute

-- 显式的内连接  有  中间表是笛卡尔积  但是是经过两表on过滤后的笛卡尔积
SELECT a.c_name,b.effect
FROM deck a INNER JOIN deck5 b ON
a.attribute=b.attribute 

 

15 不同的连接

-- 15 不同的连接 
-- 内  where  左  右  全外(两表并集)
-- 交叉连接(笛卡尔积)  左/右表独有  并集去交集


-- 1 内连接  要求: 两表 属性关联 结果8条  标记: 5中没风和水的介绍, 所以少8和10
SELECT * FROM 
deck a INNER JOIN deck5 b ON
a.attribute=b.attribute;


-- 2 where连接  要求:  和inner join效果一样  少8和10共两条  条件为属性
SELECT * FROM deck a ,deck5 b
WHERE a.attribute=b.attribute;


-- 3 左连接  要求: 左表deck,右表deck5 关联属性  结果为10条 8和10右侧为null
SELECT * FROM 
deck a LEFT JOIN deck5 b ON
a.attribute=b.attribute;


-- 4 右连接  要求: 关联属性  结果为10条 左表少8和10,右表多炎和神属性
SELECT * FROM 
deck a RIGHT JOIN deck5 b ON
a.attribute=b.attribute;


-- 5 两表并集  要求:左连接+union+右 去重  标记: 两表全部 mysql不支持的外连接    
SELECT * FROM 
deck a LEFT JOIN deck5 b ON
a.attribute=b.attribute
UNION
SELECT * FROM 
deck a RIGHT JOIN deck5 b ON
a.attribute=b.attribute;


-- 6 交叉连接(笛卡尔积)  要求:  deck和deck5  结果为50条
SELECT * FROM deck,deck5;


-- 7 左表独有 要求: id为8和10 右侧为null 标记: 左连接减左表  右属性is null
SELECT * FROM 
deck a LEFT JOIN deck5 b ON 
a.attribute=b.attribute
WHERE b.attribute IS NULL;


-- 8 右表独有  要求:  剩右表中的2行  属性为炎和神  左侧部分为null
SELECT * FROM 
deck a RIGHT JOIN deck5 b ON 
a.attribute=b.attribute
WHERE a.attribute IS NULL;



-- 9 并集去交集  两表全部-内连接  要求: 结果为4条  2条右侧为null,2条左侧
SELECT * FROM
deck a LEFT JOIN deck5 b ON
a.attribute=b.attribute
WHERE b.attribute IS NULL
UNION
SELECT * FROM
deck a RIGHT JOIN deck5 b ON
a.attribute=b.attribute
WHERE a.attribute IS NULL;

 

16 临时表

-- 16 临时表



-- 1 建临时表  要求: 原表decl 无序 三条 新表deck15  标记: temporary as后有()
CREATE TEMPORARY TABLE deck15 AS (
  SELECT * FROM deck LIMIT 0,3
);

-- 查临时表  标记: 像视图和正常表一样查询
SELECT * FROM deck15;

-- 删临时表  标记: 像视图和正常表一样删除
DROP TABLE DECK15;


-- 2 要求: deck16  检索atk  条数  合计值  分组,降序,汇总
CREATE TEMPORARY TABLE deck16 AS (
 SELECT atk,COUNT(atk),SUM(atk) FROM deck GROUP BY atk DESC WITH ROLLUP
);

SELECT * FROM deck16;

-- 查看表的列表  无法查询到临时表  可以查询出视图表
SHOW TABLES;



-- 3 临时表+左连接  要求: 左表独有  标记:  union做的全外连接  失败了
CREATE TEMPORARY TABLE deck17 AS(

SELECT a.id,a.c_name,b.deckRace FROM 
deck a LEFT JOIN deck5 b ON 
a.attribute=b.attribute
WHERE b.attribute IS NULL

);

SELECT * FROM deck17;



-- 4 普通表 和 临时表都可修改
ALTER TABLE deck20 RENAME new20;

-- 临时表也可以直接创建,要定义类型 只是没有数据  想要数据也可以用插入+查询的方式
-- 5 直接创建一个临时表
CREATE TEMPORARY TABLE temporaryTable (id INT,c_name VARCHAR(50));

-- 查看临时表
SELECT * FROM temporarytABLE;

-- 6 直接将数据插入到临时表中
INSERT INTO temporaryTable SELECT id,c_name FROM deck2;

 

17 子查询

-- 17 子查询的种类  包含: 标量,行,表子查询  列子查询在19中   



-- exists查询


-- 题目: 查询deck表中的全部数据  条件是如果deck和deck2两表的id相等
-- 1 方法1 使用where子查询
SELECT * FROM deck a WHERE 
a.id IN (SELECT b.id FROM deck2 b);


-- 2 方法2 使用exists查询
SELECT * FROM deck a WHERE 
EXISTS (SELECT 1 FROM deck2 b WHERE b.id = b.id);



-- where子查询


-- 3 找出deck表atk最大值, 且要是id最大的  结果为id=5 卡通混沌战士 atk=3000 
SELECT * FROM deck 
WHERE atk=(SELECT MAX(atk) FROM deck) 
ORDER BY id DESC LIMIT 0,1;


-- 4 找出atk为2500中  def最小的值  结果id为7 卡通恶魔  def 1200
--   标记: min函数  查询def的最小值  作为条件  给外层
SELECT * FROM deck WHERE def = 
(SELECT MIN(def) FROM deck WHERE atk=2500 );



-- from子查询


-- 5 将子查询作为表去查询  标记: 外层的排序会覆盖内层的排序,内层的顺序会失效    
SELECT * FROM 
(SELECT * FROM deck ORDER BY def DESC) AS a 
ORDER BY a.atk ASC;


-- 6 四表连接  要求: 内连接  四表连接  检索列分别为a.b.c的c_name
SELECT a.c_name,b.c_name,c.c_name,d.c_name FROM(
  (deck a INNER JOIN deck2 b ON a.id=b.id)
  INNER JOIN deck3 c ON a.id=c.id
)
INNER JOIN deck4 d ON a.id=d.id;



-- 7 要求: 外层查atk列  用in  子查询: 检索列def  条件id=5  标记: 5为混沌战士
SELECT * FROM deck WHERE atk IN (
  SELECT def FROM deck WHERE id=5
);



-- 8 要求: 外层检列随意  不去重  子查询:  deck中地属性条数, 作新列给外层
SELECT c_name,race,atk,
(SELECT COUNT(attribute) FROM deck WHERE attribute='地') AS subColumn
FROM deck;

 

18 列子查询

-- 18
-- 子查询的关键字  all 和 any  标记: 也就是列子查询



-- 1 3中的记录行,大于2中最大值  要求: 使用>all  标记: 3200,2900 由于2800相等不算
SELECT * FROM deck3 WHERE atk >ALL
(SELECT atk FROM deck2)


-- 2 1表最低是1300  2表中小于这个值有3条  2表中的自然南瓜1400  正好要比1300大
SELECT * FROM deck2 WHERE atk<ALL
(SELECT atk FROM deck)


-- 3 要求: 使用any  2中大于1表的任一数值  结果7条  都比1300大  标记:  别名some 
SELECT * FROM deck2 WHERE atk >ANY
(SELECT atk FROM deck)


-- 4 对比  =any和in  效果一样 
SELECT * FROM deck2 WHERE atk =ANY
(SELECT atk FROM deck)

--   使用in
SELECT * FROM deck2 WHERE atk IN
(SELECT atk FROM deck)



-- 5 对比  <>all和not in
SELECT * FROM deck2 WHERE atk <>ALL
(SELECT atk FROM deck)

--   使用notin
SELECT * FROM deck2 WHERE atk NOT IN
(SELECT atk FROM deck)

 

19 视图

-- 19 视图



--   如果视图存在就删除 
DROP VIEW IF EXISTS v1

-- 1 要求:  deck表  atk表  查前三  视图中列要设属于视图的列名v_原列名      
CREATE VIEW v1 (v_name,v_atk,vdef) AS
SELECT c_name,atk,def FROM deck ORDER BY atk DESC LIMIT 0,3


-- 2 计算字段  要求:  atk和def相加  指定属于视图的列的名称 v_原列名  
CREATE VIEW v2 (v_name,v_atkdefSum) AS
SELECT c_name,atk+def FROM deck 


-- 3 格式化处理数  要求:  使用concat和trim  原表检索字段设别名concat_title   
CREATE VIEW v3 AS
SELECT CONCAT(TRIM(id) , '(' ,  TRIM(c_name)  , ')' ) AS concat_title
FROM deck


-- 4 要求:  日期加时间  日期  时间  标记:  now  curdate  curtime  
CREATE VIEW v4 (v_currentTime,v_curdate,v_curtime) AS 
SELECT NOW(),CURDATE(),CURTIME() AS current;

-- 5 正则表达式  and  模糊查询  要求:名称中卡通开头,后4个字符 atk值是3000-4000
CREATE VIEW v5 AS
SELECT * FROM deck WHERE c_name LIKE '卡通____' AND atk REGEXP '3[0-9]00'

-- 6 and和or优先级  将必要条件 和 任一 合并  要求: 只用and和or  结果id为1,2,3,4
--   必要条件地属性 排除6个 必要条件atk>2700 四个中再排除两个 机械族再排除一个
--   有特点的也可以选出来  名称中有自然字样的  种族是魔法师族的
CREATE VIEW v6 AS
SELECT * FROM deck WHERE (attribute='地' AND atk>2700 AND race='机械族')
OR c_name LIKE '%自然%'  OR race='魔法师族'


-- 7 原表分组汇总  要求: 分组  降序  汇总  检索列atk,count,sum  不指定视图列名
CREATE VIEW v7 AS
SELECT atk,COUNT(atk) AS atkCount,SUM(atk) AS atkSum
FROM deck GROUP BY atk DESC WITH ROLLUP


-- 8 使用视图保护原表数据  要求: 将c_name列名改成v_name  标记: 提高安全性
CREATE VIEW v8 (id,v_name,attribute,race,atk,def) AS
SELECT * FROM deck


-- 9 视图的视图  在v1的基础上再新建一个视图  标记: 视图嵌套
CREATE VIEW v9 (v1_name,v1_atk,v1_def)AS
SELECT * FROM v1 LIMIT 0,2


-- 10 排序  查询视图时,如果指定顺序,那么这个顺序会覆盖"原视图中的排序"顺序
CREATE VIEW v10 AS
SELECT * FROM deck ORDER BY atk DESC

SELECT * FROM v10 ORDER BY atk ASC

 

20 操作视图

-- 20 操作视图



-- 11 查询视图数据  要求: 查v11前6的名称和攻击力  结果为3800到2900  
SELECT c_name,atk FROM v11 ORDER BY atk DESC LIMIT 0,6
 
--    要求: v8 列v_name属于视图, c_name是原表  标记: 检索的列名是视图的   
SELECT v_name FROM v8

--    标记: 列名为原表中的列名  "查看数据"只显示列名,不显示列类型
SELECT * FROM v8



-- 12 修改视图数据  标记: 对视图操作,会影响到原表(基本表) 


-- 创建视图v12  内容是deck2表
CREATE VIEW v12 AS
SELECT * FROM deck2 

-- 修改视图本身数据  要求:  v12  id为2的列  atk值改成2700
UPDATE v12 SET atk=2700 WHERE id=2

-- 就是修改原表中的数据, 视图会自动更新
SELECT * FROM v12
SELECT * FROM deck2

-- 删除行
DELETE FROM v12 WHERE id=2

-- 添加行
INSERT INTO v12 (id,c_name,attribute,race,atk,def) VALUES
(2, '自然兽', '地', '兽族', 2200, 1700);



-- 13 修改视图本身  要求:  v12  deck2改成deck3  不是修改视图内容
ALTER VIEW v12 AS
SELECT * FROM deck3

-- 删除视图
DROP VIEW v1;
DROP TABLE v1;



-- 14  查询信息: 视图列表, 建视图语句, 结构


-- 方法1  查看数据表列表  标记: 视图也在普通表的列表中
SHOW TABLES;

-- 方法2  查看视图列表  视图名模糊查询    
SHOW TABLES LIKE '%v%';

-- 方法3  不切换数据库  列表在information_schema
SELECT * FROM information_schema.VIEWS


-- 查看建视图的语句  标记: 可看到编码格式
SHOW CREATE VIEW v1
SHOW CREATE TABLE v12

-- 查看视图的列结构  要求: 视图v1  标记: "查看结构"是不显示数据的
DESC v12

 

21 视图的多表查询

-- 21 视图的多表查询



-- 15  4表合并  要求: 合并  不去重
CREATE VIEW v15 AS
SELECT * FROM deck
UNION ALL
SELECT * FROM deck2
UNION ALL
SELECT * FROM deck3
UNION ALL
SELECT * FROM deck4 



-- 16 要求:  where连接两表 检索id和名称 视图列不指定名称,检索列重复时要指定 表别名   
CREATE VIEW v16 AS
SELECT a.id,a.c_name,b.attribute
FROM deck a, deck b
WHERE a.id=b.id



-- 17 要求: 两表内连接  属性关联  检索id,名称,右表任意列  重名时给视图列别名 
CREATE VIEW v17 AS
SELECT a.id,a.c_name,a.attribute,b.deckRace,b.effect FROM 
deck a INNER JOIN deck5 b ON 
a.attribute=b.attribute



-- 18 属于mysql的外连接方式 左连接+union+右连接
CREATE VIEW v18 
(id,c_name,attribute,race,atk,def,r_id,r_attribute,r_deckRace,r_effect) AS
SELECT * FROM 
deck a LEFT JOIN deck5 b ON
a.attribute=b.attribute
UNION
SELECT * FROM 
deck a RIGHT JOIN deck5 b ON
a.attribute=b.attribute



-- 去除不想要查询的列
CREATE VIEW v18_2
(id,c_name,attribute,race,atk,def,r_deckRace,r_effect) AS
SELECT a.id,a.c_name,a.attribute,a.race,a.atk,a.def,b.deckRace,b.effect FROM 
deck a LEFT JOIN deck5 b ON
a.attribute=b.attribute
UNION
SELECT a.id,a.c_name,a.attribute,a.race,a.atk,a.def,b.deckRace,b.effect  FROM 
deck a RIGHT JOIN deck5 b ON
a.attribute=b.attribute



-- 19 并集去交集
CREATE VIEW v19
(id,c_name,attribute,race,atk,def,r_deckRace,r_effect) AS
SELECT a.id,a.c_name,a.attribute,a.race,a.atk,a.def,b.deckRace,b.effect FROM
deck a LEFT JOIN deck5 b ON
a.attribute=b.attribute
WHERE b.attribute IS NULL
UNION
SELECT a.id,a.c_name,a.attribute,a.race,a.atk,a.def,b.deckRace,b.effect FROM
deck a RIGHT JOIN deck5 b ON
a.attribute=b.attribute
WHERE a.attribute IS NULL