Sort the add, delete, modify, and query statements involved in mysql.

Sort the add, delete, modify, and query statements involved in mysql.

1. Create a user:

> Create user name identified by 'ssapdrow ';

2. Change the password:

> Set password for name = PASSWORD ('fdddfd ');

3. permission management

> Show grants for name; // view the name user permission

> Grant select on db_name. * TO name; // GRANT all permissions TO the name user db_name Database

> Revoke select on db_name. * TO name; // GRANT the permission TO be removed;

I. database operations:

1. view the database:

> Show databases;

2. Create a database:

> Create database db_name; // db_name indicates the DATABASE name.

3. database usage:

> USE db_name;

4. delete a database:

> Drop database db_name;

Ii. Create a table:

1. Create a table:

> Create table table_name (

> Id tinyint unsigned not null AUTO_INCREMENT, // id value, UNSIGNED, non-NULL, incrementing -- Unique, can be used as the primary key.

> Name VARCHAR (60) NOT NULL

> Score tinyint unsigned not null default 0, // you can specify the DEFAULT column value.

> Primary key (id)

>) ENGINE = InnoDB // specifies the storage ENGINE for tables. Generally, InnoDB and MyISAM are used. InnoDB is reliable and supports transactions. MyISAM does not support full-text retrieval efficiently.

> DEFAULT charset = utf8; // sets the DEFAULT encoding to prevent Chinese garbled characters in the database.

If you have conditions to create a data table, you can also use>Create table if not exists tb_name(........

2. Copy a 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 (the same as creating a common TABLE );

4. view available tables in the database:

> Show tables;

5. view the table structure:

> DESCRIBE tb_name;

You can also use:

> Show columns in tb_name; // from is optional.

6. delete a table:

> DROP [TEMPORARY] TABLE [if exists] tb_name [, tb_name2 ......];

Instance:

> Drop table if exists tb_name;

7. rename a table:

> Rename table name_old TO name_new;

You can also use:

> Alter table name_old RENAME name_new;

3. modify a table:

1. Change the table structure:

> Alter table tb_name ADD [CHANGE, RENAME, DROP]... content to be changed...

Instance:

> Alter table tb_name add column address varchar (80) not null;

> Alter table tb_name DROP address;

> Alter table tb_name CHANGE score SMALLINT (4) not null;

4. insert data:

1. Insert data:

> Insert into tb_name (id, name, score) VALUES (NULL, 'zhangsan', 140), (NULL, 'zhangsi', 178), (NULL, 'zhangwu ', 134 );

Insert multiple data entries with commas (,) to the backend and directly write the inserted data. The primary key id is an auto-incrementing column and does not need to be written.

2. Insert the Retrieved Data:

> Insert into tb_name (name, score) SELECT name, score FROM tb_name2;

5. Update Data:

1. Specify to 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;

VII. 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 controllers:

=,>, <, <>, IN (1, 2, 3 ......), BETWEEN a AND B, NOT

AND, OR

In Linke () usage, % matches any character and _ matches a character (can be Chinese characters)

Is null value detection

8,MySQLRegular Expression:

1. Mysql supports REGEXP Regular Expressions:

> SELECT * FROM tb_name WHERE name REGEXP '^ [A-D]' // find the name starting with the A-D

2. Escape special characters.

IX,MySQLSome functions:

1. String link -- CONCAT ()

> Select concat (name, '=>', score) FROM tb_name

2. mathematical functions:

AVG, SUM, MAX, MIN, COUNT;

3. Text processing functions:

TRIM, LOCATE, UPPER, LOWER, SUBSTRING

4. operators:

+ ,-,*,\

5. Time functions:

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

10. group query:

1. Grouping queries can be grouped by 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 data in descending and ascending ORDER

XI,UNIONRule -- two statements can be executed (repeated rows can be removed)

12. Full-text retrieval --MATCHAndAGAINST

1. select match (note_text) AGAINST ('picaso') FROM tb_name;

2. the InnoDB engine does not support full-text retrieval. MyISAM is fine;

XIII. View

1. Create a view

> Create view name as select * FROM tb_name WHERE ~~ Order ~~;

2. Special Functions of a view:

A. Simplify the join between tables (write the join in select );

B. reformat the output data (TRIM, CONCAT, and other functions );

C. Filter unwanted data (select part)

D. Use the view to calculate the field value, such as the sum.

14. Use stored procedures:

In my personal understanding, stored procedures are a user-defined function with local variable parameters that can be passed in and can return values. However, this syntax is not enough ~~~

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, the IN (pass a value to the stored procedure), OUT (transfer a value from the stored procedure), INOUT (pass IN and OUT the stored procedure), and INTO (Save the variable)

2. Call the stored procedure:

> CALL pro (13, @ total) // The Stored Procedure contains two variables: IN and OUT, which also needs to be written. If you do not write, an error will occur.

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

3. Other operations in the stored procedure:

> Show procedure status; // displays the current Stored PROCEDURE

> Drop procedure pro; // delete a specified Stored PROCEDURE

15. Use a cursor:

I don't understand this very well.~~~

1. cursor operations

> Create procedure pro ()

> BEGIN

> DECLARE ordername CURSOR

> SELECT order_num FROM orders;

> END;

> OPEN ordername; // OPEN the cursor

 

> CLOSE ordername; // CLOSE the cursor

16. Trigger:

A trigger triggers a specified action in a trigger;

1. The statements supporting triggers include DELETE, INSERT, and UPDATE. None of the other statements are supported.

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 a trigger

> Drop trigger trig;

17. Syntax 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 a stored procedure)

Create procedure pro ([parameters])

BEGIN

........

END

 5,Create table (Create a 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 a view on one or more tables)

CREATE [or replace] VIEW view_name as select ......

 8,DELETE (Delete one or more rows from a table)

Delete from table_name [WHERE...]

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

Drop datebase | INDEX | PROCEDURE | TABLE | TRIGGER | USER | VIEW name

 10,INSERT(Add rows to the table)

Insert into tb_name [(columns,...)] VALUES (value1 ,............);

UseSELECTInsert value:

Insert into tb_name [(columns,...)]

SELECT columns,... FROM tb_name [WHERE...];

11,ROLLBACK(Revoking a transaction processing block)

ROLLBACK [TO savapointname];

12,SELECT (Retrieve Data and display information)

SELECT column_name,... FROM tb_name [WHERE] [UNION] [rroup by] [HAVING] [order by]

 13,Start transaction (Start of a new transaction processing block)

START TRANSACTION

14,UPDATE (Update one or more rows in a table)

UPDATE tb_name SET column = value,... [where]