Common mysqlsql tips for daily collection and sorting _ MySQL

This article is a collection and collation of common mysqlsql skills. it is helpful for you to learn about mysqlsql skills. if you are interested, learn it together, paste the code directly.

1. number Auxiliary table

// Create table test (id int unsigned not null primary key); delimiter // create procedure pnum (cnt int unsigned) begindeclare I int unsigned default 1; insert into num select I; while I * 2 <cnt doinsert into num select I + id from num; set I = I * 2; end while; end // delimiter; ##### column value discontinuous problem: the id value in Table a is 3,100,101,110,111, set @ q = 0; select id, @ q: = @ q + 1 as cn from a; ##### grouping discontinuous sets @ a = 0; select min (id) as start_v, max (id) as end_v from (select id, cn, id-cn as diff from (select id, @ a: = @ a + 1 as cn from pi) as p) as pp group by diff; ##### use test for discontinuous values; 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 end from pincer as qq where not exists (select * from pincer as pp where qq. a + 1 = pp. a) and a = 2 ;################

2. birthday issues

select name,birthday,if(cur>today,cur,next) as birth_dayfrom(select name,birthday,today,date_add(cur,interval if(day(birthday)=29 && day(cur)=28,1,0) day)as cur, date_ad(next,interval if(day(birthday)=29 && day(next)=28,1,0) day) as nextfrom(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,  (year(now())-year(birth_date) )as diff,  now() as today  from employees) as a) as b) as c

3. date problem ---- computing workday

Create table sals (id int, date datetime, cost int, primary key (id); select date_add ('2017-01-01 ', interval floor (datediff (date, '2017-01-01 ')/7) * 7 day) as week_start, date_add ('2017-01-01', interval floor (datediff (date, '2017-01-01 ') /7*7 + 6 days) as week_end, sum (cost) from sales; calculate the Business day (specify the number of business days for two date segments) create procedure pgetworkdays (s datetime, e datetime) beginselect floor (days/7) * 5 + days % 7 case when 6 between wd and wd + days % 7-1 then 1 else 0 endcase then 7 between wd and wd + days % 7-1 then 1 else 0 endfrom (select datediff (e, s) + 1 as days, weekday (s) + 1 as wd) as a; end;

Mysql SQL statement overview

1. description: create a database

CREATE DATABASE database-name

2. description: Delete a database.

drop database dbname

3. description: back up SQL server

--- Create deviceUSE masterEXEC sp_addumpdevice 'disk', 'testback', 'C: \ mssql7backup \ MyNwind_1.dat 'for BACKUP data --- start backup database pubs TO testBack

4. description: create a new table.

Create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...)
Create a new table based on an existing table:

A: create table tab_new like tab_old (use the old table to create A new table) B: create table tab_new as select col1, col2... From tab_old definition only

5. description: delete a new table.

drop table tabname

6. description: add a column.

Alter table tabname add column col type Note: after a column is added, it cannot be deleted. After columns are added to DB2, the data type cannot be changed. the only change is to increase the length of the varchar type.

7. description: add a primary key: Alter table tabname add primary key (col)
Delete a primary key: Alter table tabname drop primary key (col)

8. description: create an index: create [unique] index idxname on tabname (col ....)

Delete index: drop index idxname

Note: The index cannot be changed. to change the index, you must delete it and recreate it.

9. description: create view viewname as select statement

Delete view: drop view viewname

10. description: several simple basic SQL statements

Select: select * from table1 where range insert: insert into table1 (field1, field2) values (value1, value2) delete: delete from table1 where range update: update table1 set field1 = value1 where range query: select * from table1 where field1 like '% value1 %' --- the like syntax is very subtle, query information! Sort: select * from table1 order 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 operators
A: UNION operator
The UNION operator combines two other result tables (such as TABLE1 and TABLE2) and removes any duplicate rows from the table to generate a result table. When ALL is used together with UNION (that is, union all), duplicate rows are not eliminated. In either case, each row of the derived table is from either TABLE1 or Table2.
B: random T operator
The distinct T operator derives a result table by including all rows in Table 1 but not in table 2 and eliminating all repeated rows. When ALL is used with distinct T (distinct t ALL), duplicate rows are not eliminated.
C: INTERSECT operator
The INTERSECT operator derives a result table by only including the rows in TABLE1 and TABLE2 and eliminating all repeated rows. When ALL is used with INTERSECT (intersect all), duplicate rows are not eliminated.
Note: the query results of several computation words must be consistent.

12. Note: Use external connections

A. left (outer) join:
Left outer join (left join): the result set contains the matched rows in the connected table, and all rows in the left connected 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 join): the result set includes both matched join rows in the connection table and all rows in the right join table.
C: full/cross (outer) join:
Full outer join: includes not only matching rows in the symbolic join table, but also all records in the two join tables.
12. Group: Group:
A table can only obtain group-related information after the query.
Group-related information: (statistical information) standard of count, sum, max, min, and avg groups)
When grouping in SQLServer: Fields of the text, ntext, and image types cannot be used as grouping bases.
Fields in the selecte statistical function cannot be put together with common fields;

13. perform operations on the database:

Detaching a database: sp_detach_db; appending a database: sp_attach_db indicates that the complete path name must be appended.

14. how to modify the database name:

sp_renamedb 'old_name', 'new_name'

The above is a common mysql SQL tip _ MySQL content collected and sorted daily. For more information, see PHP Chinese network (www.php1.cn )!