Daily collection and collation common Mysql SQL Tips _mysql

No more nonsense to say, directly to everyone paste code.

1, Digital Auxiliary table

Create a table CREATE TABLE test (ID int unsigned NOT NULL primary key);
Delimiter//CREATE PROCEDURE pnum (CNT int unsigned) begin declare i int unsigned default 1;
INSERT into num select I;
While i*2 < CNT does insert into num select I+id from num;
Set i=i*2;
End while;
End//delimiter;
# # # #列值不连续问题: The ID value in table A is 1,2,3,100,101,110,111 set @q=0;
Select Id,@q:=@q+1 as CN from A;
# # # #对不连续的进行分组 set @a=0;  Select min (id) as Start_v,max (ID) as End_v from (select Id,cn,id-cn as diff to (select Id,@a:=@a+1 as CN from PI) as P
As PP group by diff;
# # # #对不连续的值填充 use test;
DROP TABLE if EXISTS pincer;
CREATE table pincer (a int UNSIGNED);
Insert into pincer values (1), (2), (5), (100), (101), (103), (104), (105); Select A+1 as Start, (select min (a)-1 from pincer as WW where ww.a>qq.a) as the from pincer as QQ where NOT exists (SE
Lect * from pincer as PP where qq.a+1=pp.a) and a< (select Max (a) from pincer); ################ Select Id,num,ranknum,diff from (select Id,num,ranknum,num-ranknum as diff from (Select Id,num,if (@id =id, @rownum: = @rownum +1, @rownum: =1) ranknum, @id: =id from TT, (select @rownum: =0, @id: =null) a) b) C G
Roup by Id,diff has count (*) >=2; ################

2, Birthday question

Select Name,birthday,if (cur>today,cur,next) as Birth_day
from (
select Name,birthday,today,date_add (cur, Interval if (Day (birthday) =29 && Day (cur) =28,1,0) as cur, date_ad (next,interval if (Day (birthday) =29 & & Day (Next) =28,1,0)
as Next from (select Name,birthday,today,
  date_add (Birthday,interval diff Year) as Cur,
  date_add (birthday,interval diff+1 year) as Next, from
(
select Concat (Laster_name, "", First_ Name) as name,
  birth_date as Birthday
  (now ())-year (birth_date) as diff, now
  ()
  as Employees as a
) as B
) as C

3, date problem----calculation working day

CREATE TABLE sals (id int, date datetime, cost int,primary key (ID);
Select Date_add (' 1900-01-01 ',
            interval floor (DATEDIFF (date, ' 1900-01-01 ')/7) *7 Day)
            as Week_start,
            date _add (' 1900-01-01 ',
            interval floor (DATEDIFF (date, ' 1900-01-01 ')/7*7+6 day)
            as Week_end,
            sum (cost) from Sales;
Calculated weekday (Specify how many workdays are available for 2 date segments)
CREATE PROCEDURE pgetworkdays (S datetime,e datetime)
begin
Select Floor (DAYS/7) * 5+days%7 case at
6 between WD and Wd+days%7-1 then 1 else 0 end case
then 7 between WD and Wd+days%7-1 then 1 else 0 End
from 
(select DateDiff (e,s) +1 as Days,weekday (s) +1 as WD) as A;
End

MySQL SQL statement Encyclopedia

1, Description: Create a database
CREATE DATABASE Database-name

2, Note: Delete the database

Drop Database dbname

3, Description: Backup SQL Server
Device to create backup data---
Use master
EXEC sp_addumpdevice ' disk ', ' testback ', ' C:\mssql7backup\MyNwind_1.dat '
---start Backup
BACKUP DATABASE pubs to Testback

4, Description: Create a new table

CREATE TABLE TabName (col1 type1 [NOT NULL] [primary key],col2 type2 [NOT NULL],..)
To create a new table from an existing table:
A:create table tab_new like Tab_old (Create a new table using the old table)
B:create table tab_new as Select Col1,col2 ... from tab_old definition only

5, Description: Delete the new table

drop table TabName

6. Description: Add a column
Alter table tabname Add column col type
Note: The column will not be deleted after it has increased. The data type can not be changed when the column in the DB2 is added, and the only change is the length of the varchar type.

7. Description: Add primary key: Alter table TabName Add primary key (COL)
Description: Delete primary key: Alter table tabname drop primary key (COL)

8. Description: Creating index: Create [unique] index idxname on tabname (col ...)

Deleting indexes: Drop INDEX Idxname

Note: The index is not to be changed and you want to change the rebuild must be deleted.

9. Description: Create VIEW: Created view viewname as SELECT statement

Delete view: Drop View ViewName

10, Description: A few simple basic SQL statements
Selection: SELECT * FROM table1 where
Inserting: INSERT INTO table1 (field1,field2) VALUES (value1,value2)
Delete: Delete from table1 where scope
Update: UPDATE table1 set field1=value1 where scope
Find: SELECT * FROM table1 where field1 like '%value1% '---the syntax of like is very subtle, look for information!
Sort: SELECT * from table1 ordered by FIELD1,FIELD2 [DESC]
Total: Select count as TotalCount from table1
Sum: Select SUM (field1) as Sumvalue from table1
Average: Select AVG (field1) as Avgvalue from table1
Max: Select Max (field1) as MaxValue from table1
Min: select min (field1) as MinValue from table1

11, Description: Several advanced query operation words
A:union operator
The UNION operator derives a result table by combining the other two result tables (such as TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all is used with union (that is, union ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.
B:except operator
The EXCEPT operator derives a result table by including all rows that are in TABLE1 but not in TABLE2, and all duplicate rows are eliminated. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated.
C:intersect operator
The INTERSECT operator derives a result table by including only the rows in TABLE1 and TABLE2 and eliminates all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated.
Note: Several query result rows that use an operator must be consistent.

12, Description: Use of external connections

A, left (outer) join:
Left OUTER join (left connection): The result set includes a matching row for the join table and all rows of the left-attached table.
Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left-out JOIN b on a.a = B.C
B:right (outer) Join:
Right outer join (right connection): The result set includes both matching connection rows for the join table and all rows of the right join table.
C:full/cross (outer) Join:
All outer joins include not only matching rows for symbolic join tables, but also all records in two connected tables.
12, grouping: GROUP by:
A table, once the group is completed, the query can only get the group-related information.
Group-related information: (statistics) COUNT,SUM,MAX,MIN,AVG grouping criteria)
When grouping in SQL Server: You cannot group by a field of type Text,ntext,image
The fields in the SELECTE statistic function cannot be put together with the normal fields;

13, to the database operation:

Separate database: sp_detach_db; Additional databases: sp_attach_db indicates that the attach requires a full path name

14. How to modify the name of the database:

Sp_renamedb ' Old_name ', ' new_name '