4-1 Inserting records Insert
INSERT [into] tbl_name [(col_name)] {values| VALUE} ({expr| DEFAULT},...), (...), ... #可以一次性插入多条记录INSERT [into] tbl_name SET col_name = {Expr | DEFAULT}, ... #可以使用子查询INSERT [into] tbl_name [(Col_name,...)] SELECT ... #可以将查询的结果插入到指定的表中 4-2 Update Record updatesUPDATE [low_priority] [IGNORE] tbl_reference SET COL_NAME1={EXPR1 | DEFAULT} [, col_name2={expr2| DEFAULT}] ... [WHERE Where_condition] #单表更新 4-3-1 Deleting Records DeleteDELETE from Tbl_name [WHERE where_condition] #单表删除 4-3-2 multiple table deletions Delete t1 from t1,t2 WHERE t1.id= t2.id
or
delete from T1 USING t1,t2 where t1.id=t2.id
2, the data table T1 in the data table t2 no matching records are found and deleted
DELETE T1 from T1 left join T2 on t1.id=t2.id WHERE t2.id is null
or
delete from t1,using T1 left join T2 On T1.id=t2.id WHERE t2.id is null
3, find the same record data from the two tables and delete the data from the two tables
Delete t1,t2 from T1 left joins T2 on T1.id=t 2.id WHERE t1.id=25
Note that the t1,t2 in the delete t1,t2 from here cannot be an alias
#delete T1,t2 from TABLE_NAME as T1 left join Table2_name as T2 on T1.id=t2.id where table_name.id=25
execution in data is wrong (MYSQL version is not less than 5.0 in 5.0
The above statement is rewritten as
#delete table_name,table2_name from table_name as T1 left join Table2_name as T2 on T1.id=t2.id where table_name.id=25
execution in the data is wrong (MySQL version less than 5.0 is possible in 5.0)
attached: The above statement runs the Environment MYSQL 4.0.2 +
4-4 Find Records SelectSELECT select_expr [, select_expr ...][From table_reference[WHERE where_condition][GROUP by {col_name | position} [asc| DESC],....] #分组, you can specify column names and column locations [having where_condition] #对某一部分分组, ensure that the grouping condition is either an aggregate function (max,sum), or that the statement appears in the SELECT statement [ORDER by {col_name | expr | position} [asc| DESC],...] #多用于升降序[LIMIT {[offset,] row_count | row_count offset offset}] #限制查询结果返回数量]5-1 Sub-querya subquery (subquery) refers to a select sentence that appears in other SQL statements. #SELECT * from T1 #outer query/outer statement outside queryWHEREcol1 = (SELECT col2 from T2); #subquery subquerya subquery is nested inside a query and must always appear within parentheses, and can contain multiple keywords or conditions, such as DISTINCT, GROUP by, ORDER by, LIMIT, function, and so on. The subquery outer query can be: Select,insert,update,set or do. a subquery can return a scalar, a row, a column, or a subquery. subqueries that use comparison operatorsincluding =/>/</>=/<=/<>/!=/<=>grammatical structure: operand comparison_operator subquery#SELECT goods_id,goods_name,goods_price from Tdb_goods WHERE goods_price >= (SELECT ROUND (AVG (Goods_price), 2) from Tdb_goods);A subquery result greater than one result is required to be decorated with any/some/alloperand comparison_operator any (subquery)operand comparison_operator SOME (subquery)
operand comparison_operator All (subquery)
| Operators/Keywords |
Any |
SOME |
All |
>/>=
|
Minimum value |
Minimum value |
Maximum Value |
| </<= |
Maximum Value |
Maximum Value |
Minimum value |
| = |
Any value |
Any value |
|
| <>/!= |
|
|
Any value |
subquery by [not] insyntax structure:the operand comparison_operator [not] in (subquery) =any operator is equivalent to in. ! The =all or <>all operator is equivalent to not. subqueries using [NOT] EXISTSIf the subquery returns any rows, exists returns TRUE, otherwise false is returned. 5-2 using Insert .... Select to write query results to the data tablesyntax structure:INSERT [into] tbl_name [(Col_name,...)] SELECT ... #->insert tbl_goods_cates (cate_name) #插入商品品牌名->select goods_cate from Tdb_goods GROUP by Goods_cate; #从商品表中查找品牌名5-3 Multi-table updatessyntax structure:UPDATE table_references SET col_name1 = {EXPR1 | DEFAULT} [, col_name2 = {EXPR2 | DEFAULT}] ... [WHERE Where_condition] #->update tdb_goods INNER JOIN tbd_goods_cates #使用内连接更新->on goods_cate = cate_name #条件为 table 1 field = table 2 fieldSET goods_cate = cate_id; #设置表1字段 name = table 2 Field ID 5-4 Multi-table update one step in placeCreating a data table and writing query results to the datasheetsyntax structure:CREATE TABLE [IF not EXISTS] tbl_name [(create_definition,...)] select_statement #->create TABLE tdb_goods_brands #创建商品类型表(->brand_id SMALLINT UNSIGNED PRIMARY KEY auto_increment, #创建类型表ID字段->brand_name Vachar (+) not NULL #创建类型名字段, length 40, non-empty)->select brand_name from Tdb_goods GROUP by Brand_Name; #从商品表中查找类型5-5 ConnectionsReference relationships for tables:table_references #表1{[INNER | Cross] JOIN | {Lefe | Right} [OUTER] JOIN} #连接类型table_references #表2On conditional_expr #连接条件Connection TypeINNER Join, INNER join # in MySQL, join,cross join and INNER join are equivalent, A∩Bleft [OUTER] join, Zuo outer JOIN # AU (A∩B)right [OUTER] join, LEFT OUTER join # (A∩B) UBMulti-table connection #->select goods_id,goods_name,cate_name,brand_name,goods_price from Tdb_goods as G #使用别名, find->inner JOIN tdb_goods_cates as C on g.cate_id = c.cate_id #条件1->inner JOIN Tdb_goods_brands as B on g.brand_id = b.brand_id; #条件2
Design of an infinite classification table (three fields id,name, parent ID) with self-connection implementation #->select s.type_id,s.type_name,p.type_name #查询->from Tdb_goods_types as S left JOIN Tdb_goods_types as P #左外连接 must use alias in the from subquery->on s.parent_id = p.type_id; #连接条件Multiple table Deletionssyntax structure: DELETE tbl_name[.*] [, tbl_name[.*]] ... From Table_references [WHERE where_condition] #->delete T1 from tdb_goods as T1 #删除表t1表->left JOIN #使用左外连接-(SELECT goods_id,goods_name from Tdb_goods GROUP by Goods_name have count (goods_name) >= 2) as T2 #子查询 the number of product name types is greater than 2->on t1.goods_name= t2.goods_name #连接条件->where t1.goods_id > t2.goods_id; #删除条件为id更大的
Beginner's MySQL learning Note (iii)
