MySQL SQL statements Daquan

ddl-Data Definition language (create,alter,drop,declare)
dml-Data Manipulation Language (Select,delete,update,insert)
dcl-Data Control Language (Grant,revoke,commit,rollback)

First, a brief introduction to the underlying statement:
1, Description: Create a database
Create databases Database-name
2, Description: Delete database
Drop databases dbname
3, Description: Backup sql Server
-Create device for backup data
Use master
EXEC sp_addumpdevice ' disk ', ' testback ', ' C:\mssql7backup\MyNwind_1.dat '
-Start Backup
Backup DATABASE pubs to Testback
4, Description: Creating a new Table
CREATE TABLE tabname (col1 type1 [NOT NULL] [primary key],c Ol2 type2 [NOT NULL],..)
Create a new table from an existing table:
A:create table tab_new like Tab_old (create new table with old table)
[color= #FF0000]b:create table tab_new as Select Col1,col2. From tab_old definition Only[/color]
5, Description: Delete new table
drop table TabName
6, Description: Add a column
Alter table TabName Add column col type
Note: Columns cannot be deleted after they are added. DB2 the column plus the data type can not be changed, the only change is to increase the length of the varchar type.
(

The ALTER command performs modifications to the structure of the table.
1. Add a column
ALTER TABLE tablename add NewFile definition;
2. Change the data type of the table
ALTER TABLE tablename change Columname newdefinition;
3. Add an index to the table
ALTER TABLE tablename Add index columname newdefinition;
4. Add unique columns to the table
ALTER TABLE tablename add unique columname ...
5. Remove a column from the table
ALTER TABLE tablename drop columname;

)

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: Create INDEX: [unique] index idxname on tabname (col ...)
Drop INDEX: Idxname
Note: The index is immutable and you must remove the rebuild if you want to change it.
9. Description: Creating view: Create VIEW viewname AS SELECT statement
Delete view: Drop View ViewName
10, Description: A few 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
Updated: Update table1 set field1=value1 where range
Find: SELECT * FROM table1 where field1 like '%value1__ '
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
Maximum: 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 (for example, TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all is used with the 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 in TABLE1 but not in TABLE2 and eliminating all duplicate rows. 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 rows in TABLE1 and TABLE2 and eliminating all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated.
Note: Several query result rows that use an operation word must be consistent.

12. Description: Use external connection
A, LEFT OUTER join:
Left OUTER join (left JOIN): The result set includes a matching row for the join table and all rows of the left join table.
Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C
B:right outer join:
Right outer join (right Join): The result set includes both the matching join row for the join table and all rows of the right join table.
C:full outer join:
Full outer joins: Includes not only the matching rows of the symbolic join table, but also all the records in the two join tables.

Next, let's look at some nice SQL statements
1. Description: Copy table (copy structure only, source table name: A new table name: B) (Access available)
Law one: SELECT * into B from a where 1<>1
Law II: SELECT top 0 * into B from a
[Color= #FF0000]
2. Description: Copy table (copy data, source table name: A target table name: B) (Access available)
Insert into B (A, B, c) select d,e,f from B; [/color]
3. Description: Copy of table across databases (use absolute path for specific data) (Access available)
Insert into B (A, B, c) Select d,e,f from B in ' specific database ' where condition
Example:.. From B in ' "&server.mappath (". ") & "\data.mdb" & "' Where."

4, Description: Sub-query (table name 1:a table name 2:b)
Select A,b,c from a where a in (select D from B) or: Select A,b,c from a where a in (all-in-a-

5, Description: Display the article, the author and the last reply time
Select A.title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table where Table.title=a.title) b

6, Description: External connection query (table name 1:a table name 2:b)
Select A.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C

7, Description: Online view query (table name 1:a)
SELECT * FROM (select A,b,c from a) T where t.a > 1;

8, Description: Between usage, between limits the query data range includes the boundary value, not between does not include
SELECT * FROM table1 where time between time1 and time2
Select A,b,c, from table1 where a is not between value 1 and value 2

9. Description: How to use
SELECT * FROM table1 where a [not] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')

10, Description: Two related tables, delete the main table is already in the secondary table does not have information
Delete from table1 where NOT EXISTS (SELECT * from table2 where table1.field1=table2.field1)

11, Description: Four table linked to check the problem:
SELECT * from a left inner join B in a.a=b.b right inner join C on A.A=C.C inner join D on A.A=D.D where ...

12, Description: Schedule five minutes before the reminder
Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5

13, Description: A SQL statement to take care of database paging
Select Top b.* from (select Top 20 primary key field, sort field from table name order by sort field desc) A, table name B where B. primary key field = A. primary key field order by a. Sort field

14, Description: The first 10 records
Select Top Ten * from table1 where range

15, Description: Select in each group B value the same data corresponding to a maximum record of all information (similar to the usage can be used in the forum monthly leaderboard, monthly hot product analysis, ranked by the subject score, and so on.)
Select A,b,c from tablename ta where a= (select Max (a) from TableName TB where tb.b=ta.b)

16. Description: Include all rows in TableA but not in TableB and TableC and eliminate all duplicate rows to derive a result table
(select a from TableA) except (select a from TableB) except (select a from TableC)

17, Description: Randomly remove 10 data
Select Top * FROM tablename ORDER by NEWID ()

18, Description: Random selection of records
Select NEWID ()

19. Description: Delete duplicate records
Delete from TableName where ID not in (the Select Max (ID) from tablename GROUP by Col1,col2,...)

20, Description: List all the table names in the database
Select name from sysobjects where type= ' U '

21, Description: List of all the
Select name from syscolumns where id=object_id (' TableName ')

22, Description: List the type, Vender, PCs fields, arranged in the Type field, case can easily implement multiple choices, similar to case in select.
Select Type,sum (case vender if ' A ' then the PCs else 0 end), sum (case vender if ' C ' then PCs else 0 end), sum (case vender WH En ' B ' then PCs else 0 end) from tablename GROUP By type
Show Results:
Type Vender pcs
PC A 1
PC A 1
Disc B 2
Disc A 2
Mobile B 3
Mobile C 3

23. Description: Initialize table table1
TRUNCATE TABLE table1

24. Description: Select records from 10 to 15
Select Top 5 * FROM (select top [from table] ORDER by ID ASC) Table_ alias ORDER by id DESC

1. New User:

>create USER name identified by ' Ssapdrow ';

2. Change Password:

>set PASSWORD for Name=password (' FDDDFD ');

3. Rights Management

>show GRANTS for name; View name User Rights

>grant SELECT on db_name.* to name; All permissions to the name user db_name database

>revoke SELECT on db_name.* to name; Grant's anti-operation, remove permission;

First, the database operation:

1. View the database:

>show DATABASES;

2. Create a database:

>create DATABASE db_name; Db_name for Database name

3. Use the database:

>use db_name;

4. Delete the database:

>drop DATABASE db_name;

Second, create the table:

1. Create a table:

>create TABLE table_name (

>id TINYINT UNSIGNED not NULL auto_increment,//id value, unsigned, non-null, incremented-uniqueness, can be the master key.

>name VARCHAR () not NULL

>score TINYINT UNSIGNED not NULL default 0,//Set defaults for column values

>primary KEY (ID)

>) engine=innodb//Set table storage engine, generally commonly used InnoDB and myisam;innodb reliable, support transactions; MyISAM efficient does not support full-text indexing

>default Charset=utf8; Set the default encoding to prevent the database from garbled characters

If you have a conditionally created data table you can also use the > createtable if not EXISTS tb_name(...).

2. Copy the table:

>create TABLE tb_name2 SELECT * from Tb_name;

or partial replication:

>create TABLE tb_name2 SELECT id,name from Tb_name;

3. Create a temporary table:

>create temporary table Tb_name (this is the same as creating a normal table);

4. View the tables available in the database:

>show TABLES;

5. View the structure of the table:

>describe Tb_name;

You can also use:

>show COLUMNS in Tb_name; From can also

6. Delete the table:

>drop [temporary] TABLE [IF EXISTS] tb_name[, tb_name2 ...];

Instance:

>drop TABLE IF EXISTS tb_name;

7. Table renaming:

>rename TABLE name_old to Name_new;

You can also use:

>alter TABLE name_old RENAME name_new;

Third, modify the table:

1. Change the table structure:

>alter TABLE tb_name Add[change,rename,drop] ... What to change ...

Instance:

>alter TABLE tb_name ADD COLUMN address varchar (n) not NULL;

>alter TABLE tb_name DROP address;

　　　　>alter TABLE tb_name Change score score SMALLINT (4) is not NULL;

Iv. Inserting data:

1. Insert Data:

>insert into Tb_name (id,name,score) VALUES (null, ' Zhang San ',), (null, ' Zhang Si ', 178), (null, ' five ', 134);

Here the insertion of multiple data directly behind the comma, directly written to the inserted data, the primary key ID is a self-increment column, you can not write.

2. Insert the retrieved data:

>insert to Tb_name (name,score) SELECT name,score from Tb_name2;

Five, update the data:

1. Specify Update data:

>update tb_name SET score=189 WHERE id=2;

>update tablename SET columnname=newvalue [WHERE condition]

Vi. Deletion of data:

1. Delete data:

>delete from Tb_name WHERE id=3;

Vii. Condition Control:

1. WHERE statement:

>select * from Tb_name WHERE id=3;

2. Having a statement:

>select * from Tb_name GROUP by score have count (*) >2

3, the relevant conditions control character:

=, >, <, <>, in (...), between A and B, not

And, or

Linke () Use% to match any, _ match one character (can be kanji)

Is null empty value detection

Viii. regular expressions for MySQL:

1, MySQL support regexp regular expression:

>select * from Tb_name WHERE name REGEXP ' ^[a-d] '//Find the name starting with a-d

2, special characters need to be escaped.

Some functions of MySQL:

1. String link--concat ()

>select CONCAT (name, ' = = ', score) from Tb_name

2. Mathematical functions:

AVG, SUM, MAX, MIN, COUNT;

3. Text Processing function:

TRIM, LOCATE, UPPER, LOWER, SUBSTRING

4. Operators:

+ 、-、 *, \

5. Time function:

DATE (), Curtime (), Day (), year (), now () .....

Ten, group query:

1. Group queries can be grouped by the specified columns:

>select Count (*) from Tb_name GROUP by score have COUNT (*) >1;

2, the conditions of use have;

3. Order BY Sort:

ORDER by desc| ASC = sorted by data descending and ascending

Xi. Union rule--Can execute two statements (can remove duplicate rows)

12. Full-Text search--match and against

1, SELECT MATCH (note_text) against (' Picaso ') from Tb_name;

2, InnoDB engine does not support full-text search, MyISAM can;

13. View

1. Create a View

>create VIEW name as SELECT * from Tb_name WHERE ~ ~ ORDER by ~ ~;

2, the special role of the View:

A, simplify the junction between tables (write the connection in select);

b, reformat the output retrieved data (Trim,concat functions);

C. Filter unwanted data (select part)

D. Use the view to calculate field values, such as summarizing values.

14. Use stored procedures:

Personal understanding, the stored procedure is a custom function, there are local variables parameters, can pass in parameters, you can return the value, but this syntax is sluggish ~ ~ ~

1. Create a stored procedure:

>create PROCEDURE Pro (

>in num int,out Total INT)

>begin

>select SUM (score) into total from Tb_name WHERE id=num;

>END;

Here in (pass a value to the stored procedure), out (a value from the stored procedure), INOUT (incoming, outgoing from the stored procedure), into (save variable)

2. Call the stored procedure:

>call Pro (@total)//The stored procedure here two variables, one is in one is out, here the out also need to write, do not write error

>select @total///Here you can see the results;

3. Other operations of the stored procedure:

>show PROCEDURE STATUS; Show stored procedures for the current period

>drop PROCEDURE Pro; To delete a specified stored procedure

XV, using cursors:

To this understanding is not very understand, a friend a lot of guidance Oh ~ ~ ~

1. Operation of cursors

>create PROCEDURE Pro ()

>begin

>declare Ordername CURSOR for

>select Order_num from Orders;

>END;

　　　　

>open Ordername; Open cursor

>close Ordername; Close Cursors

16. Trigger:

A trigger is a trigger specified within a trigger when a specified action is made;

1, support trigger statements have delete, INSERT, UPDATE, others do not support

2. Create a trigger:

>create TRIGGER trig after INSERT on ORDERS for each ROW SELECT new.orser_name;

>insert statement, triggering statement, returns a value

3. Delete Trigger

>drop TRIGGER trig;

17, grammar collation:

1. ALTER TABLE (Modify tables)

ALTER TABLE table_name

(ADD column datatype [NULL | Not NULL] [CONSTRAINTS]

Change column datatype COLUMNS [NULL | Not NULL] [CONSTRAINTS]

DROP column,

。。。。

)

2. COMMIT (processing Transaction)

>COMMIT;

3. Create INDEX (creates an index on one or more columns)

CREATE INDEX index_name on tb_name (column [ASC | DESC], ...);

4. Create PROCEDURE (creating a stored procedure)

CREATE PROCEDURE Pro ([parameters])

BEGIN

........

END

5. Create table (creating tables)

CREATE TABLE Tb_name (

column_name Datetype [NULL | Not NULL] [condtraints],

column_name Datetype [NULL | Not NULL] [condtraints],

.......

PRIMARY KEY (column_name)

) engine=[InnoDB | MyiSAM]default Charset=utf8 auto_increment=1;

6. Create user (creating users)

CREATE USER user_name [@hostname] [identified by [PASSWORD] ' Pass_word '];

7. Create view (creates views on one or more tables)

CREATE [OR REPLACE] VIEW view_name as SELECT ...

8. Delete (remove one or more rows from the table)

DELETE from table_name [WHERE ...]

9. Drop (Permanently delete database and objects, such as views, indexes, etc.)

DROP Datebase | INDEX | PROCEDURE | TABLE | TRIGGER | USER | VIEW Name

10. INSERT (add row to table)

INSERT into Tb_name [(columns,......)] VALUES (value1,............);

Use the Select value to insert:

INSERT into Tb_name [(columns,......)]

SELECT columns, ..... From Tb_name [WHERE ...];

11, ROLLBACK (undo a transaction block)

ROLLBACK [to Savapointname];

12, SavePoint (set the retention point for rollback)

SavePoint SP1;

13. SELECT (Retrieve data, display information)

SELECT column_name,..... From Tb_name [WHERE] [UNION] [Rroup by] [have] [ORDER by]

14. Start TRANSACTION (the beginning of a new transaction block)

START TRANSACTION

15. Update (updating one or more rows in a table)

UPDATE tb_name SET Column=value,...... [WHERE]

MySQL SQL statements Daquan