一、插入记录INSERT
mysql> create TABLE users(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(20) NOT NULL,
-> password VARCHAR(32) NOT NULL,
-> age TINYINT UNSIGNED NOT NULL DEFAULT 10,
-> sex BOOLEAN
-> );（示例）
1.对于有默认增加的AUTO_INCREMENT用NULL就行：
（1）、mysql> INSERT users VALUES(NULL,’Tom’,123,25,1);（2）、mysql> INSERT users VALUES(DEFAULT,’Jim’,789,35,0);
2.还可以使用表达式：
mysql> INSERT users VALUES(DEFAULT,’Jim’,789,3*7-5,0);
3.用逗号表达式一次性插入多条记录
mysql> INSERT users VALUES(DEFAULT,’Jim’,789,DEFAULT,0),(DEFAULT,’Rose’,md5(‘123’),23,1);
mysql> INSERT users SET username =’Ben’,password=’456′;（一次性只能插入一个记录）
4.更新记录
(1)mysql> UPDATE users SET age = age – id,sex = 1;（把users里的所有记录age减去id，sex设成1）
(2)mysql> UPDATE users SET age = age + 10 WHERE id % 2 = 0;（用WHERE设置条件）
5,删除记录
mysql> DELETE FROM users WHERE ID = 3;（删除id为3记录，若再加入新纪录，新记录的id为默认递增）
6.SELECT 查询
（1）mysql> SELECT username,id FROM users;（可以用‘，’隔开重排序）
（2）mysql> SELECT users.id,users.username from USERS;（明确表）
（3）mysql> SELECT id AS userid,username AS uname FROM users;（用AS起别名）
7.WHERE语句
8.GROUP BY
（1）mysql> SELECT sex FROM users GROUP BY sex;（对sex按照sex分组）
（2）mysql> SELECT sex，id FROM users GROUP BY 1;（用序号表示前面的sex）
（3）mysql> SELECT sex,age FROM users GROUP BY sex HAVING age > 19;（按照age > 10分组，age必须在前面出现）
（4）mysql> SELECT sex FROM users GROUP BY sex HAVING count(id) >= 2;（如果id字段在函数里边，前面无需出现）
9.ORDER
（1）mysql> SELECT * FROM users ORDER BY id DESC;（按照id进行降序排序（desc））
（2）mysql> SELECT * FROM users ORDER BY age,id DESC;（先按照age进行顺序排序，如果age数值一样没有排序，则按照id降序排序）
10.LIMIT
（1）mysql> SELECT * FROM users LIMIT 2;
（2）mysql> SELECT * FROM users LIMIT 1,2;（按照0,1,2顺序排列）
（3）mysql> INSERT test(username) SELECT username FROM users WHERE age >= 10;（选择test表插入记录，为username字段赋值，从users里的username字段搜索age大于10的记录
小结：
1.INSERT:
2.UPDATE
3.DELETE
4.SELECT
一、什么是子查询
子查询返回的结果可以是：标量、一行、一列、子查询。
二、比较运算符子查询
（1）平均值 mysql> SELECT AVG(goods_price) FROM tdb_goods;
（2）四舍五入 mysql> SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;
（3）按照一个条件查询 mysql> SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;
（4）结合（2），（3）子查询
mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2)FROM tdb_goods);
（5）ANY,SOME,ALL的使用，如上图
mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate = ‘超级本’);
三、[NOT] IN/EXISTS引发子查询
四、INSERT……SELECT插入记录
mysql> CREATE TABLE IF NOT EXISTS tdb_goods_cates(
-> cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> cate_name VARCHAR(40) NOT NULL
-> );
mysql> SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
mysql> DESC tdb_goods_cates;
+———–+———————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———–+———————-+——+—–+———+—————-+
| cate_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| cate_name | varchar(40) | NO | | NULL | |
+———–+———————-+——+—–+———+—————-+
mysql> INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
五、create select 一步实现
->UPDATE tdb_goods AS g INTER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name
->SET brand_name = brand_id;
把商品表的表结构改变：(商品表结构的变量名和数据类型没有改变，现在改变它）
->ALTER TABLE tdb_goods
->CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
->CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
->SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER tdb_goods_cates
->ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
如果有不符合连接条件的记录，不显示。
->SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT tdb_goods_cates
->ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
->SELECT goods_id,goods_name,cate_name FROM tdb_goods RIGHT tdb_goods_cates
->ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
->SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
->INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate.id ,
->INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id;
创建树分类表（自身连接）
mysql> CREATE TABLE tdb_goods_types(
-> type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> type_name VARCHAR(50) NOT NULL,
-> parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
-> );
将type—id和parent-id连接
1.有子类查父类
mysql> 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;
2.由父表查子表
SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types p LEFT JOIN
->tdb_goods_types s ON s.parent_id = p.type_id;
（1）只显示父表的一个子表：
SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types p LEFT JOIN
->tdb_goods_types s ON s.parent_id = p.type_id （加上）GROUP BY p.type_name ORDER BY p.type_id;
(2)显示父表的子表个数
SELECT p.type_id,p.type_name,count(s.type_name) childcount FROM tdb_goods_types p LEFT JOIN
->tdb_goods_types s ON s.parent_id = p.type_id （加上）GROUP BY p.type_name ORDER BY p.type_id;
多表删除
（1）看看那个有重复
SELECT goods _id,goods_name FROM tdb_goods GROUP BY goods_name HAVING
->count(goods_name) >= 2;
（2）把上面的（1）当作一个表，参照它在tdb—goods里删除（1）的元素
->DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,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);
