MySQL Common commands

--Get the time before the current time interaval The following argument is a few days before the date (Date_sub (now (), INTERVAL 1 day);--transcoding cast (Countreg as Char) cast (CONCAT (' "+ Download+ "', Adid) as char) as apk--connect two or more arrays without changing an existing array returns the composition after the new Array CONCAT () CONCAT (CONVERT (A.adid,char))--MySQL Case statement Case when and then else end--query as a number column select * from Visitlog where LENGTH (0+adid) =length (adid)--query data for the previous day select * From Visitorlogs where date (firsttime) =date (Date_add (now (), INTERVAL-1 Day));--Query duplicate data Select User_name,count (*) as Coun T from user_table GROUP by USER_NAME have count>1; --Chinese garbled set character_set_results=gb2312;--in DOS environment if EXPR1 is not Null,ifnull () returns EXPR1, otherwise it returns EXPR2. Ifnull (EXPR1,EXPR2)--intercepts the string 1. The string to intercept 2. Intercept Condition 3. The position that appears positive from left to right is truncated from right to left Substring_index (' string to intercept ', '. ', 2);--when data is inserted, such as when an error occurs, such as repeating data, no error is returned, only as a warning. --so use ignore make sure the statement itself is not a problem, otherwise it will also be ignored insert ignore into--export Database Mysqldump-u (username)-P (password) (dbname) > (path plus file name). sql--to import table one data into table two tables in the fields to be consistent insert into table2 (' Field 1 ', ' Field 2 ') Select field 1, field 2 from table1;--  If the data exists to perform an update operation if no execution is present, insert replace into ext (extid,noid,key1,value1) VALUES +values+; insert into Visitlog (IMEI, IMSI, PLA T, Version,visitdate, Jspname,adid,count) values "+" (?,?,?,?,?,?,?, 1) on "+" duplicate key update count=count+1--set right GRANT all privileges on * * to ' root ' @ '% ' identified by ' pwd ' with GRANT option;flush privileges;--grouping after displaying several data for each group Select A.fdid, A.adid, a.apk, A.pkgname, A.name from (select t1.*, (select COUNT (*) +1 from recommend where Fdid=t1.fdid and RIDs <t1.rid) as group_id from recommend T1) Awhere a.group_id<=5--packet query displays the data with the smallest date select Idfa,devicetoken,idfv,dcode, MI N (regdate) as date from Romuse GROUP by Idfa,devicetoken,idfv,dcode Order by date (regdate) Delete from Iappleapp where Typ e=2 and ID not in (select-ID from (SELECT-Max (ID) as ID, count (1) as Count from Iappleapp where type=2 GROUP by app Name ORDER BY count Desc) as B);--Save the local TXT file in the form of a command to the database mysql-uroot-p20110725 lock[database name]-E "load data local Infil E ' d:/zfy4.txt ' into tabLe PM_CLIENTLOG_W6 (indicated) fields terminated by ' | '  (imei,imsi,regdate,jspfile,packageid,plat,version) "--delete duplicate data delete from game where ID in (SELECT ID from (SELECT max (ID) As Id,count (title) as Count from game Group by title have count >1 order by count Desc) as tab) ALTER TABLE [Biaomin        G] Add [ziduanming] [leixing] (length) ALTER TABLE ISAPP Modify column appname varchar (50);--Get current date select Curdate ();            -Get the last day of the month.    Select Last_day (Curdate ());--Get the first day of the month Select Date_add (curdate (), Interval-day (Curdate ()) +1 days); --Get the first day of next month select Date_add (curdate ()-day (Curdate ()) +1,interval 1 month);--Get the number of days in the current month select DATEDIFF (Date_add (curdate ()-day (Curdate ()) +1,interval 1 month), Date_add (Curdate (), Interval-day (Curdate ()) +1 Day)) from dual; --Save the query results to a local TXT file/usr/local/mysql/bin/mysql-h romherom3.mysql.rds.aliyuncs.com-uuser_romhe-ppasswd_20110725 rom3 -E "Select Concat (IMEI, ', '), Null,concat (', ', ' imsi, ', '), Null,concat (', ', plat, ', '), Null,concat (', ', regdate) From Rom_user where date (regdate) >=date (' 2014-10-01 ') and soid >= ' 200188003000868 ' and soid<= ' 200188003001087 ' ">/home/webs/meng.txtmysql-h rdsk5j61yfna0312q977.mysql.rds.aliyuncs.com-uappstoreljd-pljd20151123 AppStore- E "Select Idfa from Iosrecimeisad where adid= ' 876336838 '" >/alidata1/linshi/zfy/ximalaya.txt/usr/local/mysql/bin/ mysql-hrds32m2q332m2q3.mysql.rds.aliyuncs.com-uappstore-pdelong20110725 appstore-e "SELECT * from Iosuser_old" >/ home/webs/zfy/iosold.txt--queries the specified database for the number of tables AppStore for the database you want to query select COUNT (*) TABLES, table_schema from INFORMATION_SCHEMA. TABLES where Table_schema = ' AppStore ' GROUP by Table_schema; ALTER TABLE ' iosappcheck ' ADD COLUMN ' registerdate ' varchar (255) NULL COMMENT ' Apple account registration time ';--Delete scheduled task Drop EVENT E_test_inser T (scheduled Task name)-If this event exists, it will generate error 1513 (HY000): Unknown event error, so it is best to add if Exists--drop event if EXISTS e_test;-- Stop scheduled task alter event E_test_insert (scheduled Task name) disable;--Start Event alter event E_test ENABLE (scheduled task name); 1. Hash index lookup data is basically able to locate data at once,Of course, there are a lot of collisions, performance will also fall. The Btree index has to be found on the node next to each other, it is obvious that the efficiency of the hash index is higher than that of btree in the exact search of data; 2. So not accurate search, it is also obvious, because the hash algorithm is based on the equivalent calculation, so for "like" and other scopes to find the hash index is invalid, not supported; 3. For the optimal prefix of the federated Index supported by Btree, the hash is not supported, and the fields in the federated index are either full or unused; 4. Hash does not support index sorting, the index value and calculated hash value is not necessarily the same size. --MySQL Add columns, modify columns, delete columns ALTER TABLE: Add, modify, delete table columns, constraints, and so on. View column: desc [table name]; Modify table name: Alter tables [T_book] Rename to [BBB]; Add columns: ALTER TABLE table name add column name varchar (30); Delete column: ALTER TABLE name Dr OP column name; Modify column name Mysql:alter table BBB change nnnnn hh int; Modify column name Sqlserver:exec sp_rename ' t_student.name ', ' nn ', ' column '; Modify Column name oracle:lter table BBB Rename column nnnnn to hh int; Modify column properties: ALTER TABLE T_book modify name varchar; sp_rename:sqlserv Er built-in stored procedures, with the definition of modified tables. --mysql view constraints, add constraints, delete constraints Add columns, modify columns, delete columns view field information for tables: DESC table name; View all information for table: Show create table table name; add PRIMARY KEY constraint: ALTER TABLE table name add constraint primary KEY ( Form: Pk_ table name) primary key table name (primary key field); Add FOREIGN KEY constraint: ALTER TABLE from TABLE ADD constraint foreign key (shape: Fk_ from Table _ Main Table) foreign key from table (foreign key field) references Main Table (primary key word Delete PRIMARY KEY constraint: ALTER TABLE table name drop primary key; Delete FOREIGN KEY constraint: ALTER TABLE name drop FOREIGN key foreign key (case-sensitive); Modify Table name: ALter table T_book Rename to BBB; Add columns: ALTER TABLE name add column name varchar (30); Delete column: ALTER TABLE table name drop column name; modify column name MySQL: ALTER TABLE BBB change nnnnn hh int; Modify column name Sqlserver:exec sp_rename ' t_student.name ', ' nn ', ' column '; Modify Column name Oracle:alter table　　 BBB Rename column nnnnn to hh int; Modify column properties: ALTER TABLE T_book modify name varchar; sp_rename:sqlserver built-in stored procedures, with modifications to the table definition.

　　

MySQL Common commands