MySQL语句-DDL篇

-- ① 操作库


-- 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

-- ② 建表 



-- 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

-- 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;

-- ③ 操作表 


-- 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;

-- ④ 操作表中的列



-- 查表结构  标记: 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

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



-- 取消约束的方法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 ‘测试专用‘
   

-- ⑥ 触发器 根据练习的实际情况(对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‘);

-- ⑦ 存储过程

/* 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 

-- ⑧ 操作数据


-- 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