随笔(2)——SQL回顾

SQL回顾

1. 约束
   1. 唯一，检查
      1. ALTER TABLE A ADD CONSTRAINT U1 / CHECK1 UNIQUE(NAME) / CHECK (AGE>18)
   2. 默认，非空
      1. ALTER TABLE A CHANGE COLUMN LOCATION LOCATION VHARCHAR(11) DEFAULT  '上海' /NOT NULL
2. 修列
   1. 删除A1列
      1. ALTER TABLE A DROP A1
   2. 添加B1列
      1. ALTER TABLE A ADD COLUMN B1 INT
   3. 修改B1列数据类型
      1. ALTER TABLE A  MODIFY B1 VARCHAR(33)
   4. 修改B1列属性
      1. ALTER TABLE A CHANGE B1 B INT(7)
3. USER
   1. 创建用户
      1. CREATE USER 'U1'@'LOCALHOST' IDENTIFIED BY '123'
   2. 删除用户
      1. DROP USER U1
   3. 用户授权
      1. GRANT SELECT,INSERT ON A TO 'U1'@'LOCALHOST' WITH GRANT OPTION
   4. 用户收权
      1. REVOKE SELECT ON A FROM 'U1'@'LOCALHOST'
4. VIEW
   1. CREATE VIEW V1 AS SELECT * FROM STUDENG
5. TRIGGER
   1. CREATE TRIGGER T1 BEFORE INSERT ON A FOR EACH ROW SEI SCORE=SCORE+NEW.SCORE
6. PROCEDURE
   1. 带参数
      1. DELIMITER//  CREATE PROCEDURE P1(IN NAME VARCHAR(33)) BEGIN SELECT * FROM A WHERE SNAME=NAME END//
   2. 不带参
      1. DELIMITER// CREATE PROCEDURE P2() BEGIN SELECT * FROM A WHERE SNAME="小黄" END//