Shulou(Shulou.com)06/01 Report--

User Management:

1. Create a user:

> CREATE USER name IDENTIFIED BY 'ssapdrow'

2. Change the password:

> SET PASSWORD FOR name=PASSWORD ('fdddfd')

3. Rights management

> SHOW GRANTS FOR name; / / View name user permissions

> GRANT SELECT ON db_name.* TO name; / / give name users all permissions on the db_name database

> reverse operation of REVOKE SELECT ON db_name.* TO name; / / GRANT to remove permissions

1. Database operation:

1. View the database:

> SHOW DATABASES

2. Create a database:

> CREATE DATABASE db_name; / / db_name is the database name

3. Use the database:

> USE db_name

4. Delete the database:

> DROP DATABASE db_name

Second, create a table:

1. Create tables:

> CREATE TABLE table_name (

> id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, / / id value, unsigned, non-empty, incremental-unique, can be used as a primary key.

> name VARCHAR (60) NOT NULL

> score TINYINT UNSIGNED NOT NULL DEFAULT 0, / / set the default column value

> PRIMARY KEY (id)

>) ENGINE=InnoDB / / sets the storage engine for tables. Generally, InnoDB and MyISAM;InnoDB are reliable and support transactions. MyISAM does not support full-text retrieval.

> DEFAULT charset=utf8; / / sets the default encoding to prevent garbled codes in the database

If you can conditionally create a data table, you can also use > CREATE TABLE IF NOT EXISTS tb_name (.

2. Copy the table:

> CREATE TABLE tb_name2 SELECT * FROM tb_name

Or partial copy:

> 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 available tables 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 is also fine

6. Delete the table:

> DROP [TEMPORARY] TABLE [IF EXISTS] tb_name [, tb_name2.]

Example:

> DROP TABLE IF EXISTS tb_name

7. Rename the table:

> RENAME TABLE name_old TO name_new

You can also use:

> ALTER TABLE name_old RENAME name_new

3. Modify the table:

1. Change the table structure:

> ALTER TABLE tb_name ADD [CHANGE,RENAME,DROP]... What you want to change.

Example:

> ALTER TABLE tb_name ADD COLUMN address varchar (80) NOT NULL

> ALTER TABLE tb_name DROP address

> ALTER TABLE tb_name CHANGE score score SMALLINT (4) NOT NULL

4. Insert data:

1. Insert data:

> INSERT INTO tb_name (id,name,score) VALUES (NULL,' Zhang San', 140), (NULL,' Zhang Si', 178), (NULL,' Zhang Wu', 134)

Here, insert multiple pieces of data directly after the comma, directly write to the inserted data; the primary key id is a self-increasing column, you don't have to write.

2. Insert the retrieved data:

INSERT INTO tb_name (name,score) SELECT name,score FROM tb_name2

5. Update data:

1. Specify update data:

> UPDATE tb_name SET score=189 WHERE id=2

> UPDATE tablename SET columnName=NewValue [WHERE condition]

6. Delete data:

1. Delete data:

> DELETE FROM tb_name WHERE id=3

7. Condition control:

1. WHERE statement:

> SELECT * FROM tb_name WHERE id=3

2. HAVING statement:

> SELECT * FROM tb_name GROUP BY score HAVING count (*) > 2

3. Related condition control characters:

=, >, SELECT * FROM tb_name WHERE name REGEXP'^ [Amurd]'/ / find the name that starts with Amurd

2. special characters need to be escaped.

9. Some functions of MySQL:

1. String link-CONCAT ()

> SELECT CONCAT (name,'= >', score) FROM tb_name

2. Mathematical function:

AVG 、 SUM 、 MAX 、 MIN 、 COUNT

3. Text processing function:

TRIM 、 LOCATE 、 UPPER 、 LOWER 、 SUBSTRING

4. Operator:

+, -, *,\

5. Time function:

DATE (), CURTIME (), DAY (), YEAR (), NOW ().

10. Group query:

1. Grouping queries can be grouped according to the specified columns:

> SELECT COUNT (*) FROM tb_name GROUP BY score HAVING COUNT (*) > 1

2. Conditional use of Having

3. ORDER BY sorting:

ORDER BY DESC | ASC = > sort by data in descending and ascending order

11. UNION rules-two statements can be executed (duplicate lines can be removed)

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

XIII. View

1. Create a view

> CREATE VIEW name AS SELECT * FROM tb_name WHERE ~ ~ ORDER BY ~ ~

2. The special function of view:

A. Simplify joins between tables (write joins in select)

B, reformat output retrieved data (TRIM,CONCAT and other functions)

C. Filter unwanted data (select section)

D. use the view to calculate field values, such as summarization.

14. Use stored procedures:

A stored procedure is a custom function

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 (passing a value to the stored procedure), OUT (passing a value from the stored procedure), INOUT (incoming and outgoing to the stored procedure), INTO (saving variables)

2. Call the stored procedure:

> CALL pro (13remarked total) / / there are two variables in the stored procedure here, one is IN and the other is OUT, and the OUT here also needs to be written. If you don't write it, you will make an error.

> SELECT @ total / / you can see the result here.

3. Other operations of stored procedures:

> SHOW PROCEDURE STATUS; / / displays the current stored procedures

> DROP PROCEDURE pro; / / delete the specified stored procedure

Use cursors:

I don't understand this very well, so my friends give me a lot of advice.

1. Operation of cursors

> CREATE PROCEDURE pro ()

> BEGIN

> DECLARE ordername CURSOR FOR

> SELECT order_num FROM orders

> END

> OPEN ordername; / / Open cursors

> CLOSE ordername; / / close cursors

XVI. Triggers:

A trigger is a trigger that triggers a specified action within a trigger when a specified operation is performed

1. The statements that support triggers include DELETE, INSERT and UPDATE, but none of them are supported.

2. Create a trigger:

> CREATE TRIGGER trig AFTER INSERT ON ORDERS FOR EACH ROW SELECT NEW.orser_name

The INSERT statement, which triggers the statement and returns a value

3. Delete trigger

> DROP TRIGGER trig

XVII. Grammatical arrangement:

1. ALTER TABLE (modify table)

ALTER TABLE table_name

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

CHANGE column datatype COLUMNS [NULL | NOT NULL] [CONSTRAINTS]

DROP column

.

)

2. COMMIT (transaction processing)

> COMMIT

3. CREATE INDEX (create an index on one or more columns)

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

4. CREATE PROCEDURE (create stored procedure)

CREATE PROCEDURE pro ([parameters])

BEGIN

.

END

5. CREATE TABLE (create table)

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

CREATE USER user_name [@ hostname] [IDENTIFIED BY [PASSWORD] 'pass_word']

7. CREATE VIEW (create views on one or more tables)

CREATE [OR REPLACE] VIEW view_name AS SELECT .

8. DELETE (delete one or more rows from the table)

DELETE FROM table_name [WHERE.]

9. DROP (permanently delete databases and objects, such as views, indexes, etc.)

DROP DATEBASE | INDEX | PROCEDURE | TABLE | TRIGGER | USER | VIEW name

10. INSERT (add rows to the table)

INSERT INTO tb_name [(columns,.) )] VALUES (value1,.)

Insert using the select value:

INSERT INTO tb_name [(columns,.) )]

SELECT columns,. FROM tb_name [WHERE. ]

11. ROLLBACK (undo a transaction block)

ROLLBACK [TO savapointname]

12. SAVEPOINT (set reservation point for ROLLBACK)

SAVEPOINT sp1

13. SELECT (retrieving data, displaying information)

SELECT column_name,.FROM tb_name [WHERE] [UNION] [RROUP BY] [HAVING] [ORDER BY]

14. START TRANSACTION (the start of a new transaction block)

START TRANSACTION

15. UPDATE (update one or more rows in a table)

UPDATE tb_name SET column=value,. [where]

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.