Mysql Database Operations

User management:

1. Create a new User:

>create user name identified by ' RedPen ';

2. Change your password:

>set Password for Name=password (' ddd ');

3. Rights Management:

>show grants for username; 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 nam; Grant's anti-operation, remove permissions

A database operation:

1. View the database:

>show databases; Note that you must add a semicolon

2. Create a database:

>create database userinfo; To create a database named UserInfo

3. Use the database:

>use db_name;

4. Delete the database:

Drop database db_name;

Two Create tables:

1. Create a table:

>CREATE TABLE tb_name (name varchar (TEN) not null,score TINY UNSIGNED not null Deffault 0)

2. Copy the table:

>create table Tb_name2 select*form db_name; Copy all

>create table Tb_name2 Select Id,name from db_name//partial copy

3. Create a temporary table:

>create temporary tb_name;

4. View the tables available in the database:

>show tables;

5. View the structure of the table:

>describe Tb_name; (First way)

>show columns in Tb_name; In and from can also (second way)

6. Delete the table:

>drop table if exists tb_name; (or drop table Tb_name)

7. Table renaming:

>rename table Oldname to newname;

>alter table oldname rename newname;

Three Modification tables:

1. Change the table structure:

>alter table Tb_name Add[change,rename,drop]

Example:

>alter table tb_name Add column address varchar nou NULL;

>alter table Tb_name drop address;

>alter table Tb_name Change score score smallint (4) is not null;

Four Insert data:

1. Insert Data:

>insert into Tb_name (id,name,score) VALUES (null, "Hu elder sister"), (null, "Miss Hu", 120);

2. Insert the retrieved data:

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

Five Update data:

Specifying Update data

>update Tb_name set score=190 where id=2;

>update tb_name set columnname=new value[where condition]

Six Delete data:

>delete from Tb_name where id=2;

Seven conditional controls:

1.where statements

>select*from Tb_name where id=2;

2.having statement:

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

3. Related condition control character

= < > In (...) Between A and B not

and OR

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

is NULL (Control detection)

Eight regular expressions for MySQL:

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

Some of the nine 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 () .....

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 Database Operations