Ubantu MySQL database operation

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]

Ubantu MySQL database operation