连接

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

语法结构

table_reference

{[INNER|CROSS]JOIN|{LEFT|RIGHT}[OUTER]JOIN}

table_reference

ON conditional_expr

SHOW COLUMNS FROM tdb_goods;

SHOW COLUMNS FROM tdb_goods_brands;

UPDATE  tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name

SET g.brand_name = b.brand_id;

SELECT * FROM tdb_goods\G;

SHOW COLUMNS FROM tdb_goods; 

ALTER TABLE tdb_goods

CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,

CHANGE brand_name brand_id SMALL_id SMALLINT UNSIGNED NOT NULL;

SHOW COLUMNS FROM tdb_goods;

SELECT * FROM tdb_goods\G;

CREATE...SELECT

创建数据表同时将查询结果写入到数据表。

CREATE TABLE[IF NOT EXISTES] tbl_name

[(create_definition,...)]

select_statement

SELECT brand_name FROM tdb_goods GROUP BY brand_name;

CREATE TABLE tdb_goods_brands

(

brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

brand_name VARCHAR(40) NOT NULL

)

SELECT brand_name FROM tdb_goods GROUP BY brand_name;

SHOW TABLES;

SELECT * FROM tdb_goods_brands\G;

多表更新

UPDATE table_references

SET col_name1 = {expr1|DEFAULT}

[, col_name2 ={expr2|DEFAULT}]...

[WHERE where_condition]

语法结构

{[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER]JOIN}

table_reference

ON conditional_expr

UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name;

SET goods_cate = cate_id;

SELECT * FROM tdb_goods\G;

INSERT...SELECT

将查询结果写入数据表

INSERT [INTO] tbl_name [(col_name,...)] SELECT...

SELECT * FROM tdb_goods_cates;

DESC tdb;       XXX

DESC tdb_goods_cates;

INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM

tdb_goods GROUP BY goods_cate;

SELECT * FROM tdb_goods_cates;

SELECT * FROM tdb_goods\G;

使用[NOT] EXISTS的子查询

如果子查询返回任何行，EXISTS将返回TRUE；否则为FALSE。

SELECT * FROM tdb_goods\G;

CREATE TABLE IF NOT EXISTS tdb_goods_cates(

cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

cate_name VARCHAR(40) NOT NULL

);

SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

使用[NOT] IN 的子查询

语法结构

operand comparison_operator [NOT] IN (subquery)=ANY运算符与IN等效

!=ALL或<>ALL运算符与NOT IN等效

使用[NOT] EXISTS 的子查询

如果子查询返回任何行，EXISTS将返回TRUE; 否则为FALSE。

使用比较运算符的子查询

=、>、<、>=、<>、!=、<=>

SELECT AVG(goods_price) FROM tdb_goods;

SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;

SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price >= 5636.36;

SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

SELECT goods_price FROM tdb_goods WHERE goods_cate='超级本'；

SELECT * FROM tdb_goods WHERE goods_cate = '超级本'；

SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price > (SELECT goods_price FROM tdb_goods WHERE goods_cate ='超级本' )//查找商品价格，大于超级本

SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price > ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate ='超级本' )

子查询

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

例如：SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);

其中SELECT * FROM t1，称为Outer Query/Outer Statement

SELECT col2 FROM t2，称为SubQuery

子查询指嵌套在查询内部，且必须始终出现在圆括号内。子查询可以包含多个关键字条件，如DISTINCT，GROUP BY，ORDER BY，LIMIT，函数等。

子查询的外层查询可以是：SELECT，INSERT，UPDATE，SET或DO。

子查询可以返回标量、一行、一列、或者子查询。

SHOW TABLES

SHOW COLUMNS FROM tdb_goods;

SELECT * FROM tdb_goods\G;

SET NAMES gbk;

SELECT * FROM tdb_goods\G;

本节知识点

INSERT

UPDATE

DELETE

SELECT

LIMIT

限制查询结果返回的数量

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

SELECT * FROM users LIMIT 2;

SELECT * FROM users LIMIT 2,2;   //(从第3条开始，显示2个)

SELECT * FROM users ORDER BY id DESC LIMIT 2,2；

================================================

CREATE TABLE test(

id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(20)

);

SELECT * FROM test;

INSERT test(username) SELECT username FROM users WHERE age>=30;

 SELECT * FROM test;

创建数据库：

CREATE DATABASE 数据库名称 CHARACTER  SET 字符编码名称;

查看当前服务器下的数据表列表：

SHOW DATABASES；

查看错误：

SHOW WARNINGS;

修改数据库：

ALTER DATABASE 数据库名称 CHARACTER SET 字符编码名称；

删除数据库：

DROP DATABASE 数据库名称；

MYSQL默认端口：3306

超级用户：root

ODER BY

对查询结果进行排序

[ODER BY{col_name|expr|position}[ASC|DESC],...]

//SELECT * FROM users ORDER BY id DESC;

HAVING

分组条件[HAVING where_condition]

SELECT sex,age FROM users GROUP BY 1 HAVING count(id)>=2;