Common query statements for MySQL

1 1. Connect the database code:2 >Mysql-IP address of the H host-U log in database user name-p Log in Password3 4 2. Display the database5 >show databases;6 7 3. Connect to the database8 > Usedbname;9 Ten 4. Display the currently connected database One >Select Database(); A  - 5. Show Table Structure - >descTableName the  -  - Action table: -     1. modifying table names +     >Alter Tabletable_old rename table_new; -  +     2. Delete a table A     >Drop TableTabelname; at  -     3. Import the Database -     >source file Full path -  -     4. modifying field Lengths -     >Alter TableTABLE_NAME Modify COLUMN_NAMEvarchar( -) ; in         4.1. Modify field names -     >Alter TableKeyword_info Change enabled Cate_numvarchar( -); to         4.2. Add Table field name +     >Alter TableKeyword_infoAddEnabledvarchar( -) -  the     5. modifying field Types *     >Alter TableTalbe_name Modify column_nameint( A); $ Panax Notoginseng     6. Query the Build Table statement -     >ShowCreate Tabletable_name; the  +     7. query field deduplication and statistics non-repeating fields A     >Select distinct(column_name) fromtable_name; the     >Select Count(DISTINCTCOLUMN_NAME) fromtable_name +  -     8. Delete table field contents $     >Delete  fromTableNamewherecolumn_nameinch('xx','xx','xx'); $     >Delete  fromKeyword_infowhereKeywordinch('Design','New Products','Tide Card','Noble'); -  -     9. Querying for duplicate fields the     >SelectCOLUMN_NAME,Count(1) as Count  fromUser_tableGroup  bycolumn_name having Count>1;  - Wuyi     TenDelete a full repeating field (if there are 10,000 duplicates I'm not going to do it manually 10,000 times, loop through a function) the     >DELETE  fromKeyword_infoWHEREKeywordinch(SelectKeyword from(SelectKeywordCount(1) as Count  fromKeyword_infoGROUP  byKeyword having Count>1) asb) LIMIT1 -  Wu      One. Update table field contents -     >Updatetable_nameSETcolumn_name=New valueWHEREcolumn_name=Old value About  $      A. query table Insert new Table -     >INSERT  intoTALBE1 (CLOUMN1)SELECTCloum1_like fromTable2 LIMIT1; -     >INSERT  intoKeywords_search_info (Nick)SELECTShop_name fromSearch LIMIT1; -  A      -add a primary key to an existing table +         1the. field must not be empty the     >Alter TableMytable_1Alter columnNameChar(1) not NULL -         2. Delete the original primary key $     >Alter TableMytable_1Drop constraintpk_mytable_1 (primary key name) the         3. Add a new primary key the     >Alter TableMytable_1Add constraintPk_mytable_1Primary Key(Id,name) the         4. Example the     >Alter TableKeyword_infoAdd constraintKeywordPrimary Key(keyword); -  in      -. Multi-Table Association ( onimmediately following the Main table field) the     >Select distinctI.shopid from(Item I Left JoinTask_info T onI.shopid=T.SHOP_ID) Left JoinShop S onI.shopid=s.shop_idwhereT.type='1'LimitTen,5; the  About Advanced Functions: the     1. rounding the     >round(x,d) the  +     2. Seeking an average -     >avg(x) the Bayi a simple stored procedure: the  the //create a stored procedure - CREATE PROCEDUREP14 () -  the BEGIN     the   DECLAREVINT;  the   SETV= 0;  the    whileV<  - Do -     DELETE  fromKeyword_infoWHEREKeywordinch(SelectKeyword from(SelectKeywordCount(1) as Count  fromKeyword_infoGROUP  byKeyword having Count>1) asb) LIMIT1;  the     SETV=V+ 1;  the   END  while;  the END;94  the  theCall P14 ();//execute this stored procedure the 98 Drop procedure if existsP4//To delete a stored procedure

Common query statements for MySQL