MySQL学习笔记 3：子查询、连接、运算符和函数

一、子查询与连接

1.子查询SubQuery

SELECT * FROM t1 WHERE col1=(SELECT col2 FROM t2)

其中SELECT * FROM t1称为Outer Query/Outer Statement,可以是SELECT, INSERT, UPDATE, SET或者DO

SELECT col2 FROM t2称为SubQuery, 可以包含多个关键字或者条件，如DISTINCT,GROUP BY ,ORDER BY, LIMIT, 函数等，子查询必须出现在圆括号内，查询结果可以是标量、一行、一列或者子查询。

2.使用比较运算符的子查询

operand {> | >= |< |<=} [ANY | SOME |ALL] subquery

operand = [ANY |SOME] subquery

operand {<> | !=} [ALL] subquery

当子查询返回的结果不止一个时加上ANY或者SOME或者ALL

3.使用[NOT] IN的子查询

IN等价于=ANY

NOT IN等价于！=ALL以及<>ALL

4.使用[NOT] EXISTS的子查询

返回TRUE或者FALSE

5.多表更新

UPDATE table_references SET col_name1={expr1|DEFAULT} [,col_name2={expr2|DEFAULT]...[WHERE condition]

更新数据表的值后数据表的结构没有变，为了达到减小存储空间的目的，应该用ALTER命令改变数据表的结构

6.连接JOIN

在SELECT语句、多表更新语句、多表删除语句中都支持JOIN操作

语法结构：table_reference_A {[INNER |CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN} table_reference_B ON conditional_expr

table_reference_A和table_reference_B分别表示连接的两张表，可以用AS命令起别名

ON设定连接条件，也可以用WHERE替代，但是一般用WHERE进行结果集过滤，用ON设定连接条件

6.1 INNER JOIN内连接

INNER JOIN和CROSS JOIN以及JOIN是等价的，内连接仅显示符合连接条件的记录

6.2 LEFT [OUTER] JOIN左外连接

显示左表的全部和右表符合连接条件的记录，在左表中存在而在右表中不存在的显示为NULL

6.3 RIGHT [OUTER] JOIN右外连接

显示右表的全部和左表符合连接条件的记录，在右表中存在而在左表中不存在的显示为NULL

7.创建数据表的同时将查询结果写入到数据表CREATE ... SELECT

CREATE TABLE B_table(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

brand_name VARCHAR(40) NOT NULL

)

SELECT brand_name FROM A_table GROUP BY brand_name;

8.多表连接

使用多个JOIN语句

9.无限极分类表的设计——自身连接

同一个数据表对其自身进行连接

CREATE TABLE tdn_goods_types(

type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

type_name VARCHAR(20) NOT NULL,

parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0

);

SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id=p.type_id;

SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.praent_id=p.type_id;

10.多表删除

查找重复记录：SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name)>=2

自身的多表删除：DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name)>=2 ) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;

二、运算符和函数

1.字符函数

1.1 CONCAT('imooc','play','on')字符连接

1.2 CONCAT_WS('-','imooc','play')使用指定的分隔符进行字符连接，分隔符为第一个参数

1.3 FORMAT(12560.75，1)数字格式化，反悔的是字符型，第二个参数为保留到小数点后的位数，四舍五入

1.4 LOWER('ABOT')转换成小写字母

      UPPER('abot')转换成大写字母

1.5 LEFT('MySQL'，2)获取左侧2位字符

      RIGHT('MySQL',3)获取右侧3位字符

1.6 LENGTH('MySQL')字符串的长度，包含空格

1.7 LTRIM('  MYSQL')删除前导空格

      RTRIM('MYSQL   ')删除后导空格

      TRIM('     MYSQL     ')删除前导和后导空格

      TRIM(LEADING ‘？’ FROM '??MYSQL')删除前导的指定字符

      TRIM(TAILING '?' FROM 'MYSQL??')删除后导的指定字符

      TRIM(BOTH '?' FROM '??MYSQL??')删除前导和后导的指定字符

      REPLACE('?MYSQL??LJ??','?','!')将字符串中的‘？’替换成‘！’

1.8 SUBSTRING('MYSQL',1，2)截取字符串，从第一位开始截取，截两位（第一位为M），起始位可以是负值

1.9 [NOT] LIKE模式匹配

      SELECT * FROM test WHERE first_name LIKE '%O%'查找数据表test中first_name包含字母o的记录，%表示任意个字符，_表示任意一个字符

      SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1'查找数据表test中first_name包含字符%的记录，ESCAPE '1'表示‘1’后面的1个‘%’被认为是普通字符

2.数值运算符和函数

2.1 CEIL(3.01)进一取整，返回4

      FLOOR(3.99)舍一取整，返回3

2.2 3 DIV 4整数除法，返回0

       3/4返回0.75

2.3 5.3 MOD 3取模，返回2.3

2.4 POWER(2，3)幂运算，计算2的3次方

2.5 ROUND(3.456，2)四舍五入，保留到3.456小数点后2位

2.6 TRUNCATE(125.89，1)数字截取，保留到小数点后1位，直接截取，不四舍五入

       TRUNCATE(125.89，-1)返回120

3.比较运算符和函数

3.1 [NOT] BETWEEN ... AND ...闭区间，返回1或者0

3.1 [NOT] IN(1，2，3，4)是否是给定的几个值中的一个，返回1或者0

3.3 IS  [NOT]  NULL只有NULL才是NULL,‘’和0都不是NULL

4.日期时间函数

4.1 NOW()当前的日期和时间

4.2 CURDATE()当前的日期

4.3 CURTIME()当前的时间

4.4 DATE_ADD('2014-3-12', INTERVAL 1 YEAR) 返回2015-3-12

4.5 DATEDIFF('2013-3-12','2014-3-12')日期差值计算，返回-365

4.6 DATE_FORMAT('2014-3-2','%m%d%Y')返回03/02/2014，日期格式化

5.信息函数

5.1 CONNECTION_ID()返回当前线程的ID

5.2 DATEBASE()返回当前的数据库

5.3 LAST_INSERT_ID()最后插入的记录的ID号，若同时插入多个记录，只返回第一个插入的记录的ID

5.4 USER()当前用户信息

5.5 VERSION()当前mysql版本信息

6.聚合函数

6.1 AVG()求平均值，只能对表中的信息进行求平均值操作

6.2 COUNT()计数

6.3 MAX()最大值

       MIN()最小值

       SUM()求和

7.加密函数

7.1 MD5('ABC')形成‘ABC’字符串的信息摘要

7.2 PASSWORD('admin')密码算法