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

Source: Internet
Author: User
Tags mathematical functions

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]


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.