MySQL table data additions and deletions, sub-query

//manipulating records in a data tableCreate TableUser2 (IDsmallintUnsignedPrimary Keyauto_increment, usernamevarchar( -) not NULL, Passwordvarchar( -) not NULL, Agetinyint  not NULL default Ten, sex Boolean)--insert--InsertUser2Values(NULL,'Sun Chu','123', -,1);//the self-increment sequence can be null or default insteadInsertUser2Values(default, "Sun Qian", "123", -,1);InsertUser2SetUsername="Lisi", password="456";//InsertSet Insert SyntaxInsertTest (username)SelectUsername fromUser2Order  byIddescLimit2;//Query Insertion--update--UpdateUser2SetAge=Age+5;//no conditions will update all columnsUpdateUser2SetAge=Age-ID, sex=0;UpdateUser2SetAge=Age+Ten whereId%2=0;--delete--Delete  fromUser2whereId=2;--select--SelectVersion ();SelectNow ();--clause--whereconditional clauses can be followed by columns, functions, arithmetic logical operationsGroup  bygroup clause followed by column name, column position havingis also one of the conditional clauses, mainly to solve the problem where the aggregation function cannot be used togetherOrder  bysort clauses, ASC ascending, desc descending sort Limit2Limit on quantity, number of queries--Sub-query-------------------------------------------------------------------------------------------CREATE TABLE IF  not EXISTSTdb_goods (goods_idSMALLINTUNSIGNEDPRIMARY KEYauto_increment, Goods_nameVARCHAR( Max) not NULL, Goods_cateVARCHAR( +) not NULL, Brand_NameVARCHAR( +) not NULL, Goods_priceDECIMAL( the,3) UNSIGNED not NULL DEFAULT 0, Is_show BOOLEAN not NULL DEFAULT 1, Is_saleoff BOOLEAN not NULL DEFAULT 0  );---sub-query issued by comparison operator---Select round(avg(Goods_price),2) fromTdb_goods; Query the average of a columnSelectGoods_id,goods_name,goods_price fromTdb_goodswhereGoods_price>(Select round(avg(Goods_price),2) fromTdb_goods);//query for information larger than average//For subqueries that return multiple columns, you can add any、some, the all modifierSelectGoods_id,goods_name,goods_price fromTdb_goodswhereGoods_price>  any(SelectGoods_price fromTdb_goodswhereGoods_cate="super-Ben");---In addition, there are subqueries raised by in (not) and exist (not exist)----Multi-table query---CREATE TABLE IF  not EXISTStdb_goods_cates (cate_idSMALLINTUNSIGNEDPRIMARY KEYauto_increment, Cate_nameVARCHAR( +)  );InsertTdb_goods_cate (Cate_name)SelectGoods_cate fromTdb_goodsGroup  byGoods_cate;//Query InsertionUpdateTdb_goodsInner JoinTdb_goods_cates onGoods_cate=Cate_nameSetGoods_cate=cate_id;//Update the Product list with reference to the category tableCreate TableTdb_brand (brand_idSMALLINTUnsignedPrimary KEYauto_increment, Brand_Namevarchar( +) not NULL)SelectBrand_Name fromTdb_goodsGroup  byBrand_Name;//Create a new table and assign a valueUpdateTdb_goods asGInner JoinTdb_brand asB onG.brand_name=B.brand_nameSetG.brand_name=b.brand_id;//Update Product name informationAlter Tabletdb_goods Change Goods_cate cate_idsmallintUnsigned not NULL, change brand_name brand_idsmallintUnsigned not NULL;//Modify Table Structure

MySQL table data additions and deletions, sub-query