MySQL-表操作
表操作
插入记录
创建如下的表：
create table users ( id smallint unsigned primary key auto_increment, username varchar(20) not null, password varchar(32) not null, age tinyint not null default 10, sex boolean );
当前users表id 字段为自增，在插入数据的时候，并不知道当前的值为多少，可以使用如下方式：
insert users values(NULL, 'Tom', '123', 25, 1);
对于有默认值的可以直接写DEFAULT.
insert users values(NULL, 'John', '246', DEFAULT, 0);
一次性插入多个值：
insert users values(DEFAULT, 'Amy', '147', 3*7-1, 1), (NULL, 'Rose', md5('123'), DEFAULT, 0);第二种方法：
INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},....此方法可以使用子查询（SubQuery），一次只能插入一条记录。
insert users set username='Ben', password='456';
第三种方法：
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...
此方法可以将查询结果插入到指定数据表。
insert test(username) select username from users where age >= 20;
更新记录
单表更新
如果没有where条件，则更新全部的行。
例如，让age字段的值都加上5
update users set age = age + 5;
让age等于age减去id值，sex都设置为0
update users set age = age - id, sex = 0;
删除记录
单表删除
DELETE FROM tbl_name [WHERE where_condition]
如果没有where条件语句，则会删除所有的记录。
查询语句
查找记录
只查找id，username这两列
select id, username from users;
星号（*）表示所有列。tbl_name.*可以表示命名表的所有列。
查询表达式可以使用[AS] alias_name为其赋予别名。
select id as userId, username as uname from users;
别名可用于GROUP BY, ORDER BY或HAVING子句。
查询结果分组GROUP BY
[GROUP BY {col_name|position} [ASC|DESC], ...]HAVING
设置分组条件
[HAVING where_condition]
ORDER BY 对查询结果进行排序
[ORDER BY {col_name|expr|position} [ASC|DESC], ...]限制查询结果返回的数量
[LIMIT {[offset,] row_count | row_count OFFSET offset}]选择users表中的第3、4行数据
select * from users limit 2,2;
子查询和连接
子查询
子查询是指在另一个查询语句中的SELECT子句。
例句：
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
其中，SELECT * FROM t1 称为Outer Query[外查询](或者Outer Statement),SELECT column1 FROM t2 称为Sub Query[子查询]。
子查询必须出现在圆括号之间。
子查询可以包含多个关键字或条件，如DISTINCT， GROUP BY， ORDER BY ，LIMIT，函数等。
子查询的外层查询可以是：SELECT,INSERT,UPDATE,SET或DO。
使用比较运算符的子查询
从tdb_goods表中取goods_price的平均价格
select round(avg(goods_price),2) from tdb_goods;
avg求平均值，round(avg(goods_price),2)表示保留两位小数。
从tdb_goods表中取出goods_price 的价格大于平均价格的项目。
select goods_id, goods_name, goods_price from tdb_goods where goods_price >= (select round(avg(goods_price),2) from tdb_goods);
当子查询有多个结果时，会提示出错，这时可以使用ANY SOME ALL这些关键字。
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 的子查询
IN与=ANY运算符等下
NOT IN与!=ALL或<>ALL运算符等效
INSERT…SELECT
将查询的结果写入数据表
INSERT [INTO] tbl_name [(col_name)] SELECT ...
现在创建一个分类表tdb_goods_cates
create table if not exists tdb_goods_cates( cate_id smallint unsigned primary key auto_increment, cate_name varchar(40) not null);
要把tab_goods中所有的类别添加到tdb_goods_cates中，使用如下语句：
insert tdb_goods_cates (cate_name) select goods_cate from tdb_goods group by goods_cate;
多表更新
参照另外的表来更新本表的记录
table_reference
现在需要根据分类表tdb_goods_cates来更新tab_goods
update tdb_goods inner join tdb_goods_cates on goods_cate = cate_name set goods_cate = cate_id;
创建数据表同时将查询结果写入数据表
现在创建一个品牌表，在创建品牌表tdb_goods_brands的同时，写入数据：
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;
参照品牌表tdb_goods_brands更新商品表tdb_goods：
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;
此时查看tdb_goods表，会发现goods_cate和brand_name的数据类型还是varchar(40)， 修改表结构：
alter table tdb_goods change goods_cate cate_id smallint unsigned not null,change brand_name brand_id smallint unsigned not null;
连接
在MySQL中，JOIN、CROSS JOIN和INNER JOIN是等价的。
连接条件
使用ON关键字来设定连接条件，也可以使用WHERE来代替。
通常使用ON关键字来设定连接条件
使用WHERE关键字进行结果集记录的过滤。
内连接
查找tdb_goods.cate_id等于tdb_goods_cates.cate_id的记录。
select goods_id, goods_name,cate_name from tdb_goods inner join tdb_goods_cates on tdb_goods.cate_id = tdb_goods_cates.cate_id;
外连接
左外连接
select goods_id, goods_name,cate_name from tdb_goods left join tdb_goods_cates on tdb_goods.cate_id = tdb_goods_cates.cate_id;
上述例子，选择了tdb_goods中全部数据
右外连接
多表连接
显示tdb_goods的全部完整信息，显示品牌信息、分类信息。
select goods_id, goods_name,cate_name,brand_name from tdb_goods as g inner join tdb_goods_cates as c on g.cate_id = c.cate_idinner join tdb_goods_brands as b on g.brand_id = b.brand_id;
无限分类的数据表设计
形势如下：
CREATE TABLE tdb_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 sleft join tdb_goods_types as pon s.parent_id = p.type_id;
查找父类下所有的子类
select p.type_id, p.type_name, s.type_name from tdb_goods_types pleft join tdb_goods_types son p.type_id = s.parent_id;
多表删除
选出名字重复的商品：
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_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;