Common MySQL database operation statement Daquan

0. User management:

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]

"PostScript" MySQL database is a very lightweight database management system, compared to large-scale database management systems such as Oracle, Ms-sql, MySQL has a light, flexible, fast development features, more suitable for small and medium-sized data storage and architecture. This is why MySQL can be used by tens of thousands of websites.

I used to study the database is not good, a tutorial today, I feel that open source lightweight database also has large content, since the 5 version, has been supporting the cursors, triggers, transactions, stored procedures and other advanced applications, which also give MySQL ease of use and enterprise service development added important weights.

Database is very basic, but the performance of the database optimization is the most important, so much optimization, it will be beneficial.

Common MySQL database operation statement Daquan