MYSQL笔记（进阶） --cmd操作

MYSQL笔记（进阶）--cmd操作

MYSQL笔记（序） --cmd操作，点我跳转！

MYSQL笔记（正文） --cmd操作，点我跳转！

MYSQL笔记（高阶） --cmd操作，点我跳转！

1.子查询

查找记录时，显示的文字数据为乱码时，可以用SET NAMES gbk，只影响客户端显示数据时的编码，不影响实际数据的编码。

子查询（Subquery）是指出现在其他SQL语句内的SELECT字句。

--1.子查询
SELECT  * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
    -- 其中SELECT  * FROM t1，称为Outer Query/Outer Statement
    -- SELECT col2 FROM t2，称为SubQuery

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

使用的比较运算符： =、>、<、>=、<=、<>、!=、<=>

语法结构： operand comparison_operator [ANY|SOME|ALL] (subquery) 运算对象 比较运算符 [关键字] 子查询

operand comparison_operator [NOT] {IN | EXISTS} (subquery)

=ANY 运算符与IN等效、 !=ALL或<>ALL运算符与 NOT IN等效、 EXISTS如果存在则返回TRUE，否则返回FALSE

--1.例
SELECT ROUND(AVG(price),2) FROM tb_name; 
    -- 查找表中所有price字段的平均值，结果四舍五入，小数点后保留2位
    -- 假设得到的结果为6666

SELECT price FROM tb_name WHERE price >= 6666;
    -- 查找表中price大于等于6666的值

SELECT price FROM tb_name WHERE price >= (SELECT ROUND(AVG(price),2) FROM tb_name);
    -- 利用子查询来查找表中price值大于总price平均值的数据

SELECT price FROM tb_name WHERE price > ANY(SELECT price FROM tb_name WHERE name = '翰林');
    -- 当子查询返回的行数大于1时，需要使用ANY、SOME、ALL关键字

3.多表更新&连接类型&连接条件

INNER JOIN--内连接，显示表A和表B符合连接条件的记录

{ LEFT | RIGHT } [OUTER] JOIN--左/右外连接，显示表A的全部记录及表B符合连接条件的记录，如果表B没有表A中的数据，则显示NULL，比如表A存在id为15的值，表B中没有id没有15，则显示NULL；

内左右

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

通常使用ON关键字来设定连接条件，使用WHERE关键字进行结果集记录的过滤。

--1.多表更新（通过表2的字段来更新表1）
UPDATE table_name {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
table_name1 ON conditional_expr
SET col_name1={expression|DEFAULT}... 
[WHERE where_condition];
    -- conditional_expression 条件句表达式

--2.例
UPDATE tb_name1 INNER JOIN tb_name2 ON teaname1 = teaname2 SET teaname1 = teaid2;
    -- 将表1的name改为表2对应name的id
    -- 如果两表中存在相同的字段名，则需要在表名后加上表的别名
    -- tb_name1 AS tb1,tb_name2 tb2, tb1.name = tb2.name，AS可不写

--1.创建数据表同时将查询结果写入到数据表 CREATE...SELECT  
CREATE TABLE [IF NOT EXISTS] tb_name
[(create_definition)] select_statement;
    -- definition定义  statement声明

--2.例
CREATE TABLE tb_name1
(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(40) NOT NULL
)
SELECT name FROM tb_name GROUP BY name;
    -- 创建表B，将表A的name按组添加到表B中

--1.多表连接
SELECT id,name,brand FROM tb_name1 AS a
INNER JOIN tb_name2 AS b ON a.id = b.id
INNER JOIN tb_name3 AS c ON a.brand = c.brand;

--2.例
SELECT id,b.name FROM tb_name1 AS a
LEFT JOIN tb_name2 AS b ON a.id = b.id;

总结：内外连接均用在多表连接查询，比如：查询显示表A某些字段和表B某些字段，涉及两表的字段，并且两表存在某种关联。

1 、表关联的效率要高于子查询，因为子查询走的是笛卡尔积
2 、表关联可能有多条记录，子查询只有一条记录，如果需要唯一的列，最好走子查询

4.无限级分类表设计&自身连接

比如：一个商品网站，商品分为很多类，而每个类下面又有很多类，这样设计数据库的时候可能要创建很多表，为了方便可以将这些类都放在同一张数据库表中。

如何查询连接自身表呢？假设还有一张一样的表当做子表或父表；

--1.结果如前图所示
SELECT a.type_id,a.type_name,b.type_name
FROM tb_name1 AS a LEFT JOIN tb_name2 AS b
ON a.parent_id = b.id;  
--区别，与ON后的条件有关系，当a.parent_id = 0时，在右表中寻找所有b.id = 1的结果

--2.结果如后图所示
SELECT a.type_id,a.type_name,b.type_name
FROM tb_name1 AS a LEFT JOIN tb_name2 AS b
ON a.id = b.parent_id ;  
--区别，与ON后的条件有关系，当a.id = 1时，在右表中寻找所有b.parent_id = 1的结果

--3.结果如图所示
SELECT a.type_id,a.type_name,count(b.type_name) AS child_count
FROM tb_name1 AS a LEFT JOIN tb_name1 AS b
ON a.id = b.parent_id GROUP BY a.type_name ORDER BY a.id;

4.多表删除

当一张表中有多条相同的记录时，可先查找多条记录相同的数据，然后通过此查询的结果来删除剩下其他多余的数据；

DELETE t1 FROM tb_name1 AS t1 LEFT JOIN
(SELECT goods_id,goods_name FROM tb_name1 
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;
        --删除表中相同的记录，保留id小的记录，删除id大的记录

5.字符函数

1.CONCAT('AA','BB'); - - AABB

2.CONCAT_WS('|','A','B','C') - - A|B|C

3.FORMAT(12345.678,1) - - 12345.7

4.5.LOWER('MYSQL') - - mysql

6.7.LEFT('MySQL',2) - - My

8.LENGTH('My SQL') - - 6

9.10.LTRIM('  MySQL    ') - - MySQL    只删除前面的空格

11.TRIM({BOTH|LEADING|TRAILING} '?' FROM '??MY??SQL??') - - 分别去掉前后（MY??SQL）、前、后的？

14.REPLACE('??MY??SQL???','??','!') - - !MY!SQL!?

12.SUBSTRING('MYSQL',1,2) - - MY、SUBSTRING('MYSQL',-2) - - QL

13.LIKE '%1%%' ESCAPE '1' - - 查找含有%的记录，_代表任一字符，%代表任意个字符

6.数值运算符

1.CEIL(3.01) - - 4

2.SELECT 10 DIV 3; - - 3

3.FLOOR(3.99) - - 3

4.SELECT 5.3 % 3; - - 2.3，SELECT 5.3 MOD 3; - - 2.3

5.POWER(3,3); - - 27

6.ROUND(3.652,1) - - 3.7，ROUND(3.652) - - 4

7.TRUNCATE(125.89,1) - - 125.8

7.日期时间函数

1.DATE_ADD('2018-4-11',INTERVAL 1 {YEAR|WEEK}) - - 2019-4-11

2.DATEDIFF('2018-4-11','2017-4-11') - - 365

3.DATE_FORMAT('2018-4-11','%m/%d/%Y') - - 04/11/2018

8.信息函数

SELECT xxx;

9.聚合函数

SELECT AVG(price) AS avg_price FROM tb_name;

10.加密函数

PASSWORD()用在修改数据库密码；