MySQL 记录增删改查

记录

插入记录

INSERT [ INTO ] tb_name [ (col_name,...)] {VALUES | VALUE}

({expr | DEFAULT} ,...) , (...) , ...;

INSERT [INTO] tb_name SET col_name = { expr | DEFAULT } , ...

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

 

eg.：

INSERT test VALUES('max',13,1000);

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

查找记录

查询表达式（select_expr）

SELECT select_expr [,select_expr ...]

[

FROM table_references

[WHERE where_condition]

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

[HAVING where_condition]

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

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

]

• 星号（*）表示所有
• 查询表达式可用 [AS] alias_name 赋予别名，可用于 GROUP BY ，ORDRE BY ，HAVING
• GROUP BY 查询结果分组ASC升序，DESC降序
• HAVING 分组设置条件
• ORDER BY 对查询结果进行排序
• LIMIT 限制返回查询结果的数量

eg.：#没有WHERE语句，则默认选择所有

SELECT * FROM test;



SELECT sex FROM users GROUP BY sex;



SELECT sex,age FROM users GROUP BY sex HAVING age > 18;



SELECT * FROM users ORDER BY id DESC;



SELECT * FROM users LIMIT 2;



SELECT * FROM users LIMIT 2,2; #从0开始，第一个2是从第三开始查询



SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(good_name) >=2 ;

子查询（Sub Query）:

• 另一个查询语句中的SELECT子句
• 必须在圆括号里面
• 外查询可以是SELECT，INSERT，UPDATE，SET，DO
• 返回标量、一行、一列、子查询
• 比较运算符 != >= <= = < > <=> <>

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

\G;#记录显示

SET NAMES gbk;

 

更新记录

单表更新

UPDATE [LOW_PRIORITY][IGNOGE] table_reference SET

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

[WHERE where_condition]

多表更新

UPDATE table_references

SET col_name1 = {expr1|DEFAULT}

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

[WHERE where_condition]

//更新多表

UPDATE tdb_goods INNER JOIN tdb_goods_cates ON good_cate = cate_name

SET goods_cate = cate_id;

创建表时，将查询结果写入表

CREATE TABLE [IF NOT EXISTS] tbl_name

[(create_definition,...)]

select_statement

//创建时,填入查询结果

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;

删除记录

DELETE FROM tb_name [WHERE where_condition]

多表删除

DELETE tbl_name[.*][,tbl_name[.*]]...

FROM table_references

[WHERE where_condition]

//删除重复

DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name 
HAVING count(good_name) >=2 ) AS t2 
ON t1.goods_name = t2.goods_name 
WHERE t1.goods_id > t2.goods_id;