Mysql——sql语句

一、数据库与表

1. 注释：-- xxxxx

2. 显示所有数据库：SHOW DATABASES

3. 选择数据库：USE 数据库名

4. 显示所有表：SHOW TABLES

5. 创建表：

   CREATE TABLE IF NOT EXISTS `runoob_tbl`(
      `runoob_id` INT  AUTO_INCREMENT,
      `runoob_title` VARCHAR(100) NOT NULL,
      `runoob_author` VARCHAR(40) NOT NULL,
      `submission_date` DATE,
      'runoob_flag'    int   NOT NULL DEFAULT 1,
      PRIMARY KEY ( `runoob_id` )
   )ENGINE=InnoDB DEFAULT CHARSET=utf8;
   

   • IF NOT EXISTS：不存在即创建
   • AUTO_INCREMENT：自动递增，可以使用SELECT last_insert_id()获取
   • DEFAULT：默认值
   • PRIMARY KEY：主键
   • ENGINE：引擎（外键不能夸引擎）
     • InnoDB：可靠的事务处理引擎，不支持全文本搜索
     • MyISAM：性能高，支持全文本搜索，不支持事务处理
     • MEMORY：等同于MyISAM，数据存储在内存，速度很快，适合用于临时表
   • CHARSET： 设置编码

6. 更新表：ALTER TABLE 表名 操作

   • 添加一列：ALTER TABLE 表名 ADD 字段 类型
   • 删除一列：ALTER TABLE 表名 DROP COLUMN 字段
   • 定义外键：

   ALTER TABLE 表名 
   ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段) REFERENCES 外表名 (外表主键);
   

7. 删除表：DROP TABLE 表名

8. 重命名表：RENAME TABLE 旧表名 TO 新表名

二、查询

1. 按条件查询：SELECT * FROM 表名 WHERE 条件
2. 分组：SELECT * FORM 表名 WHERE 条件 GROUP BY 字段 HAVING 分组条件
3. 排序：SELECT * FORM 表名 ORDER BY 字段1,字段2 DESC（DESC为降序，若同时拥有相同字段1才按字段2排序，否则不考虑字段2）
4. 显示规定条数记录：SELECT * FORM 表名 LIMIT 行数
   分页：SELECT * FORM 表名 LIMIT 开始行，行数（开始行从0开始）
5. 别名：SELECT 字段 AS 别名 FROM 表名,字段别名会返回给客户机，表的别名不会
6. 计算字段：SELECT 字段1+-*/字段2 AS 别名 FROM 表名
7. 顺序：WHERE，GROUP BY，HAVING，ORDER BY，LIMIT
8. 组合查询：SELECT语句1 UNION SELECT语句2 UNION....

1. 条件限定

• 操作符

  语句	说明
  != / <>	不等于
  BETWEEN a AND b	[a,b]
  IS NULL	为空

• 组合

  语句	说明
  AND	且
  OR	或
  IN(值1,值2…)	包含范围,可以填写其他SELECT语句
  NOT	取反，可与IN,BETWEEN,EXISTS联合使用

2. 处理

• 文本处理函数

  函数	说明
  Concat(字段1,字段2…)	拼接字段
  Length()	字符串长度
  Lower()	小写
  Upper()	大写
  Trim()	去除两端空格

• 日期时间处理

  函数	说明
  Date()	年月日（日期）
  DayOfWeek()	星期几
  Time()	时间
  Now()	当前时间
  DateDiff()	两日期之差
  Year() Month() Day() Hour() Minute() Second()	对应相关时间

• 数值处理

  函数	说明
  Abs()	绝对值
  Mod()	余数
  Sqrt()	平方根
  Rand()	随机数
  Exp()	指数值

3. 汇聚函数

函数	说明
AVG()	某列平均值
COUNT()	某列的行数
MAX()	某列最大值
MIN()	某列最小值
SUM()	某列之和

4. 联结

• 内联结
  SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段或 SELECT * FROM 表1,表2 WHERE 表1.字段 = 表2.字段
• 自联结：不止一次引用相同的表
  SELECT * FROM 表名 AS 别名1,表名 AS 别名2 WHERE 别名1.字段1 = 别名2.字段1 AND 别名2.字段2=xxx
• 自然联结：去除重复列的内联结
  SELECT * FROM 表1 NATURAL JOIN 表2
• 左联结：保留表1中要舍弃的部分
  SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段
• 左联结：保留表2中要舍弃的部分
  SELECT * FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段

5. 搜索

• 通配符
  搜索时间长，尽量使用操作符代替；尽量不要放在搜索模式开始处

  语句	说明
  LIKE ‘李%’	以李开头的字符（%可匹配0或多个）
  LIKE ‘%李%’	任意位置包含李的字符
  LIKE ‘李%王’	李开头王结尾的字符
  LIKE’ 李_’	与%用法相同，唯一区别为只能匹配1个字符

• 正则表达式

  语句	说明
  REGEXP ‘100’	包含100的所有字符
  REGEXP ‘100|200’	100或200
  REGEXP ‘[123]’	1或2或3
  REGEXP ‘[1-3]’	1或2或3
  REGEXP ‘\\.’	包含.的所有字符，\\为转义字符
  *	0个或多个
  +	1个或多个
  ?	0个或1个
  {n}	指定数目的匹配
  ^	开头
  $	结尾

• 全文本搜索
  MyISAM支持全文本搜索，查询效率高，不支持事务；InnoDB不支持全文本搜索，支持事务

  CREATE TABLE productnotes 
  ( 
  note_id int NOT NULL AUTO_INCREMENT, 
  note_date datetime NOT NULL, 
  note_text text NULL, 
  PRIMARY KEY(note_id), 
  FULLTEXT(note_text) 
  ) ENGINE=MyISAM; 
  

  1. 全文本搜索：除非使用BINARY方式，否则不区分大小写SELECT note_text FROM productnotes WHERE Match(note_text) Against('xxx')
  2. 查询扩展：放宽搜索范围SELECT note_text FROM productnotes WHERE Match(note_text) Against('xxx' WITH QUERY EXPANSION)
  3. 布尔文本搜索：返回包含该词的所有行（区别于普通全文搜索）SELECT note_text FROM productnotes WHERE Match(note_text) Against('xxx' IN BOOKEAN MODE)
  4. 使用说明：
     • 短词（小于等于3）被忽略
     • 内建的非用词被忽略
     • 出现次数超过50%被忽略；表中行数少于3行，不返回结果（对布尔搜索无效）
     • 忽略词中的单引号

三、插入

1. INSERT INTO 表名 VALUES(值1,值2...)必须为每一个字段提供值，如果没有值设为NULL，自增字段也设为NULL
2. INSERT INTO 表名(字段1，字段2...) VALUES(值1,值2...)
3. 插入多条：INSERT INTO 表名(字段1，字段2...) VALUES(值1,值2...),(值1,值2...),...
4. 提高整体性能：INSERT LOW_PRIORITY INTO 表名 VALUES(值1,值2...)
5. 插入检索出的数据：INSERT INTO 表1(字段1,字段2...) SELECT 字段1,字段2...FROM 表2 where 条件

四、更新

1. UPDATE 表名 SET 字段1=值1,... WHERE 条件
2. 即使发生错误也更新：UPDATE IGNORE 表名 SET 字段1=值1,... WHERE 条件
3. 提高整体性能：UPDATE LOW_PRIORITY 表名 SET 字段1=值1,... WHERE 条件

五、删除

1. DELETE FROM 表名 WHERE 条件
2. 提高整体性能：DELETE LOW_PRIORITY FROM 表名 where 条件
3. 删除表中所有行：TRUNCATE TABLE 表名

六、视图

视图本身没有数据，数据来源于基表。实现多次复用sql语句

• 利用视图简化复杂的联结：CREATE VIEW 视图名 AS SELECT 所需字段 from 表1 INNER JOIN 表2 ON 表1.字段=表2.字段，下次直接SELECT视图即可
• 重新格式化检索出的数据：CREATE VIEW 视图名 AS SELECT 一些复杂语句...
• 排除不需要的字段：CREATE VIEW 视图名 AS SELECT 需要的字段...

七、存储过程与游标

1. 存储过程

一组预先编译好的sql语句集，提高重用性

• 调用存储过程：CALL 过程名

• 创建存储过程：为区分；，将分隔符在开头设置为//，末尾恢复为；
  空参：

  DELIMITER //
  CREATE PROCEDURE 过程名()
  BEGIN
  	SELECT * FROM 表名 WHERE 条件;
  END //
  DELIMITER ;
  

  IN : 需要调用者传入：

  DELIMITER //
  CREATE PROCEDURE 过程名(IN sName VARCHAR(255))
  BEGIN
  	SELECT * FROM 表名 WHERE 条件=sName;
  END //
  DELIMITER ;
  

  call 过程名('张三')
  

  OUT : 作为返回值

  DELIMITER //
  CREATE PROCEDURE 过程名(IN sName VARCHAR(255),OUT sAge INT)
  BEGIN
  	SELECT 表.sName INTO sAge FROM 表名 WHERE 条件=sName;
  END //
  DELIMITER ;
  

  call 过程名('张三',@sAge);
  select @sAge;
  

• 删除存储过程：DROP PROCEDURE 过程名

• 显示创建过程：SHOW CREATE PROCEDURE 过程名

• 定义局部变量：DECLARE

2. 游标

不是一条select语句，而是被该语句检索出来的结果集。主要用于交互式应用，存储了游标后，应用程序可以根据需要滚动或浏览其中的数据。游标只能用于存储过程和函数。

• 创建游标：

  DELIMITER //
  CREATE PROCEDURE 过程名()
  BEGIN
  	DECLARE 游标名 CURSOR
  	FOR
  	SELECT语句;
  END //
  DELIMITER;
  

• 打开/关闭游标：OPEN/CLOSE 游标名

• 获得一行数据:FETCH 游标名 INTO 变量1,变量2...

• 循环：

  CREATE DEFINER=`root`@`localhost` PROCEDURE `q`()
  BEGIN
  	DECLARE a INT;
  	DECLARE b INT;
  	-- 边界
  	DECLARE flag INT DEFAULT 0;
  	-- 创建游标
  	DECLARE y CURSOR FOR SELECT * FROM t1;
  	-- 句柄语句
  	DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;
  	
  	OPEN y;
  	
  	-- 循环
  	REPEAT
  		-- 获取一行 
  		FETCH y INTO a,b;
  		select a,b;
  	UNTIL flag END REPEAT;
  	
  	CLOSE y;
  END //
  

八、触发器

在增删改之前或之后触发执行的操作，一般用于数据校验，日志文件等。
每个表每个事件每次只允许一个触发器执行。

• 创建触发器：CREATE TRIGGER 触发器名称 BEFORE/AFTER INSERT/UPDATE/DELETE ON 表名 FOR EACH ROW ....
• 删除触发器：DROP TRIGGER 触发器名
• 可以使用NEW 或 OLD记录触发前后记录：SELECT NEW.id,OLD.name...
• 记录插入后新id：SELECT NEW.id
• 记录删除前出档：INSERT INTO 表名 VALUES(OLD.id...)
• 保证大写SET NEW.name = Upper(NEW.name)

九、事务处理

一个完整的业务逻辑，保证sql语句要么全部执行，要么不执行。

• 事务开始：START TRANSACTION
• 回退：ROLLBACK，支持INSERT，UPDATE，DELETE；不能撤销CREATE 和DROP
• 提交：COMMIT，一般的sql语句会隐含提交（提交操作是自动的），事务处理块必须明确提交。
• 保留点：SAVEPOINT 保留点名
• 回退到保留点：ROLLBACK TO 保留点名
• 在执行完回退或提交后，事务会自动关闭，后续提交为隐含提交。
• 更改默认提交模式：SET autocommit =0不自动提交
• 四大特性：ACID
  1. A：原子性：事务是最小的工作单元不可再分
  2. C：一致性：同时成功或同时失败
  3. I：隔离性：事务之间相互隔离
  4. D：持久性：数据必须持久化到硬盘中，事务才算成功结束
• 隔离性：默认为3
  1. 读未提交（read uncommitted）：对方事务还没有提交，我们当前事务可以读取到对方为提交的数据。存在脏读现象。
  2. 读已提交（read committed）：对方提交后的数据，我方可读。不可重复读（原来的数据无法读取）
  3. 可重复读（repeatable read）：提交了也读不到，存在幻读现象
  4. 序列化读（Serialiazble）：效率低，需要排队。