MySQL(3)-子查询、连接、运算符及函数

子查询

1. 子查询是指出现在其他SQL语句内的SELECT子句
2. 子查询指嵌套在查询内部,且必须始终出现在圆括号内
3. 子查询可以包含多个**关键字**或条件：
    DISTINCT(去重输出)   GROUP BY(分组)   ORDER BY(排序)   LIMIT及函数
4. 子查询的外层查询可以是: SELECT INSERT UPDATE SET DO

---

连接

连接分为：外连接和内连接，其中外连接又分为左外连接和右外连接

-------------------------------内连接--------------------------------
[表1] INNER JOIN [表2] ON [条件]; //表1与表2之间内连接

eg：
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;//将tdb_goods表和tdb_goods_cates表连接

-------------------------------外连接--------------------------------
[左表] LEFT JOIN [右表] [条件];//显示左表全部和共有的,右数据表依赖于左数据表
[左表] RIGHT JOIN [右表] [条件];//显示共有的及右表全部,左数据表依赖于右数据表

---

ANY SOME ALL关键字

SELECT goods_id, goods_name FROM tdb_goods WHERE goods_price > ANY (SELECT goods_price FROM WHERE goods_name = '超极本'); //上述语句中ANY指大于最小值

----ANY----
>/>=:最小值
</<=:最大值
=:任意值(等价于IN)

----SOME----
>/>=:最小值
</<=:最大值
=:任意值(等价于IN)

----ALL----
>/>=:最大值
</<=:最小值
!=:任意值(等价于NOT IN)

---

MODIFY与CHANGE的区别

ALTER TABLE [表名] MODIFY [列] [新属性1] [新属性2]..;
ALTER TABLE [表名] CHANGE [旧列名] [新列名] [新属性1] [新属性2]..;
//也就是说MODIFY只改变列的属性,而CHANGE可以改变列名.

---

MySQL函数

字符函数

/*CONCAT() //字符连接*/
SELECT CONCAT('imooc', 'MySQL');//将imooc和MySQL连接
SELECT CONCAT('imooc', '-', 'MySQL');//CONCAT不仅仅只是连接两个字符串
SELECT CONCAT([列1], [列2],..) FROM [表名];//将表中列1,列2,..信息连接一起 然后输出

/*CONCAT_WS() //使用特定的分隔符将字符连接*/
SELECT CONCAT_WS('|', 'a', 'b', 'c'); //结果为a|b|c,即就是用'|'将'a' 'b' 'c'分隔开

/*FORMAT() //数字格式化*/
SELECT FORMAT(12550.765, 2);//格式化为12,550.77 即保留小数点后两位(四舍五入)

/*LOWER() //大写转换成小写*/
SELECT LOWER('MySQL'); //结果为mysql纯粹的小写

/*UPPER() //小写转换成大写*/
SELECT UPPER('mysql'); // 结果为MYSQL

/*LEFT() //获取左侧字符串*/
SELECT LEFT('MySQL', 2); //结果为前两个字符串即My
SELECT LOWER(LEFT('MySQL', 2)); //结果为my

/*RIGHT() //获取右侧字符串*/
SELECT RIGHT('MySQL', 3); //结果为从右向左的3位,即SQL

/*LENGTH() //字符串长度获取*/
SELECT LENGTH('MYS QL  '); //如果字符串中包含空格的话,空格也算是一个字符串的长度

/*LTRIM() //删除前导空格*/
SELECT LTRIM('  MySQL    '); //结果为(MySQL   ),即就是删除了前2个空格

/*RTRIM() //删除后导空格*/
SELECT RTRIM('  MySQL    '); //结果为(  MySQL),即就是删除了后4个空格

/*TRIM() //删除字符串空格*/
SELECT TRIM('  MySQL    '); //结果为(MySQL)

ps:
SELECT TRIM(TRAILING '?' FROM '??MySQL???');//删除后导'?' 输出结果??MySQL
SELECT TRIM(LEADING '?' FROM '??MySQL???'); //删除前导'?' 输出结果为MySQL???
SELECT TRIM(BOTH '?' FROM '??MySQL???'); //删除所有问号 但是如果是字符串??My??SQL???,那结果即为My??SQL,也就是说前导后导删除了但无法删除中间的

如果要解决上述穿插??的情况,可以用到REPLACE();
SELECT REPLACE('??My??SQL???', '?', ''); //就是让字符串中的问号替换为空字符(不是空格),输出结果为MySQL

/*SUBSTRING() //进行字符串的截取*/
SELECT SUBSTRING('MySQL', 1, 2);//字符串MySQL从第1位截取,截2位,输出结果为My
SELECT SUBSTRING('MySQL', 3); //默认未填写第三个长度参数(长度参数一定是非负的),则从第三位向后所有都取
SELECT SUBSTRING('MySQL', -4); //表示从右向左的第四个元素为起始位置,向右开始取
PS:程序上字符串开始从0开始,但MySQL字符串开始从1开始！！！

/*(NOT) LIKE //模式匹配*/
SELECT id, name FROM student WHERE name LIKE '%t%'; //查找名字含有t的学生信息,若名字含有%怎么办呢？
SELECT id, name FROM student WHERE name LIKE '%1%%' ESCAPE '1'; //可以引入字符1然后搜索1% 除去1即可得到名字含有%的学生的信息
PS:%代表任意个字符 _代表任意一个字符

数值运算符与函数

/*CEIL() //进一取整(向上取整)*/
SELECT CEIL(3.01); //返回一个比整数位大1的整数,即结果为4

/*FLOOR() //舍一取整(向下取整)*/
SELECT FLOOR(3.99); //不论小数多大 仅取整数部分

/*DIV //整数除法*/
SELECT 3/4; //结果为0.75
SELECT 3 DIV 4; //结果为0

/*MOD //该运算符等价于%*/
SELECT 5.3 MOD 3; //结果为2.2
SELECT 5.3 % 3; //结果为2.2

/*ROUND() //四舍五入函数*/
SELECT ROUND(1.384, 2); //结果为1.38
SELECT ROUND(1.384, 1); //保留小数点后1位,1.4
PS:ROUND()等价于FORMAT()

/*TRUNCATE() //数字截断函数*/
SELECT TRUNCATE(125.89, 2);//截断至小数点后两位,结果为125.89
SELECT TRUNCATE(125.89, 1);//结果为125.8
SELECT TRUNCATE(125.89, 0);//结果为125
SELECT TRUNCATE(125.89, -1);//结果为120
SELECT TRUNCATE(125.89, -2);//结果为100
根据上述语句及结果即可看出截断的规律,截断但并非做四舍五入操作

/*(NOT) BETWEEN...AND... //判断范围*/
SELECT 15 BETWEEN 1 AND 22; //判断15是否在1~22之间,结果是肯定的 所以输出结果为1
SELECT 35 BETWEEN 1 AND 22; //结果为0
SELECT 35 NOT BETWEEN 1 AND 22; //结果为1

/*(NOT) IN() //判断在不在列出值之中*/
SELECT 5 IN(5, 12， 44, 53); //5在5, 12, 44, 53之中,所以结果为1

/*CURDATE() //显示当前年月日*/
SELECT CURDATE();

/*CURTIME //显示当前时间*/
SELECT CURTIME();
PS:SELECT CURDATE(), CURTIME();和SELECT NOW();实现的功能是一样的

/*DATE_ADD() //对年月日进行操作*/
SELECT DATE_ADD('2014-3-12', INTERVAL 365 DAY);
SELECT DATE_ADD('2014-2-5', INTERVAL 2 YEAR);
SELECT DATE_ADD('2014-6-12', INTERVAL 4 MONTH);
SELECT DATE_ADD('2014-7-2', INTERVAL 3 WEEK);

/*DATEDIFF() //计算时间之差*/
SELECT DATEDIFF('2015-2-12', '2016-2-12');//结果-365

/*DATE_FORMAT() //日期格式化函数*/
SELECT DATE_FORMAT('2014-3-14', '%m/%d/%Y');
//%m代表月份(%m代表03%M代表March) %d代表日期(%d输出14%D输出14th) %Y代表年份(输出2014)%y(输出14)

信息函数

/*DATEBASE() //查看当前使用的数据库名称*/
SELECT DATEBASE();

/*USER() //查看当前登陆数据库的用户*/
SELECT USER();

/*VERSION() //查看当前数据库版本*/
SELECT VERSION();

聚合函数

/*AVG() //平均值*/
SELECT AVG(id) FROM test; //AVG()函数用于计算表中数据 而不能写成SELECT AVG(4, 5, 6);

/*COUNT() //计数*/
SELECT COUNT(goods_id) FROM tdb_goods;

/*MAX() //最大值*/
/*MIN() //最小值*/
/*SUM() //求和*/

加密函数

/*MD5() //信息摘要函数*/
SELECT MD5('awag'); //输出awag通过md5加密后的字串

/*PASSWORD() //密码加密*/
SELECT PASSWORD('GWAG'); //输出GWAG通过PASSWORD加密后输出字串
SET PASSWORD=PASSWORD('dimitar'); //修改数据库密码