Common MySQL database operation statement Daquan

First, 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;

Second, 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;

Third, 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

> CREATE TABLE 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;

Iv. Modification of 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;

V. Inserting data:

1. Insert Data:

>insert into Tb_name (id,name,score) VALUES (null, ' Zhang San ', 178), (null, ' Zhang Si ', ' a "), (null, ' Zhang 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;

VI. Update Data:

1. Specify Update data:

>update tb_name SET score=189 WHERE id=2;
>update tablename SET columnname=newvalue [WHERE condition]

Vii. Deletion of data:

1. Delete data:

>delete from Tb_name WHERE id=3;

Viii. 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
Like () Use% to match any, _ match one character (can be kanji)
Is null empty value detection

Ix. 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 of MySQL's functions:

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 ()

.....

Xi. 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

12, Union rule--Can execute two statements (can remove duplicate rows)

13. 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;

14. 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.

XV, using 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

16. 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

17. 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;

18, 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]

Common MySQL database operation statement Daquan