Use of SQL statements

1.mysql Common Statements

1) Display database show databases;

2) Create Database dbname;

3) Delete database drop databases dbname;

4) Enter the database and use databases dbname;

5) Check all tables in the library show tables (from dbname);

6) Creating Table Create tables Tbname (field 1 type, field 2 type, ...., PRIMARY key (primary key name))

7) View the contents of the table select * FROM Tbname (* represents all fields, you can customize the fields to be checked)

8) View the table structure describe tbname;

9) Delete Table drop tables tbname;

operation of data in a table in 2.mysql

1) inserting data

Insert into Tbname (field 1, Field 2,...) VALUES (Value of field 1, Value of field 2 ...)

2) Update data

Update tbname SET Field 1 = "Value of field 1", Field 2 = "Value of field 2" where conditional expression

3) Delete data

Delete from tbname where conditional expression

4) Clear the entire table

TRUNCATE TABLE tbname or delete from Tbname

3.MYSQL User Authorization Action statement

1) Permissions for users in MySQL

* ALTER: Modify tables and indexes.

* Create: Creates databases and tables.

* Delete: Delete the records that are already in the table.

* Drop: Discard (delete) the database and table.

* Index: Create or discard indexes.

* Insert: Inserts a new row into the table.

* REFERENCE: Not used.

* SELECT: Retrieves the records in the table.

* UPDATE: Modify existing Table records.

* File: Read or write files on the server.

* PROCESS: View the thread information that is being executed on the server or kill the thread.

* RELOAD: Overloads the authorization table or empties the log, host cache, or table cache.

* SHUTDOWN: Shut down the server.

* All: All permissions, all privileges synonyms.

* USAGE: Special "No Permissions" permission

Tip: You can perform show privileges in the MySQL database to see more detailed permissions

2) give users specific permissions

Grant privilege on Dbname.tbname to ' [e-mail protected] ' identified by ' password '

Explain:

Privilege: Give the user permissions to assign, multiple permissions separated by commas

Dbname.tbname: Databases and tables authorized for user operations

[Email protected]: User and host, where the host is generally IP or network segment, with% of the host part of the network segment

Password: User's password

3) Revoke user-specific permissions

REVOKE privilege on dbname.tbname from ' [email protected] '

4) View User rights

Show grants view Current user rights

Show grants for [email protected] View specific user rights

Example Operation:

650) this.width=650; "Src=" http://s3.51cto.com/wyfs02/M01/58/1E/ Wkiom1spmy7zkp69aaeis5qmhty104.jpg "/>650) this.width=650;" Src= "http://s3.51cto.com/wyfs02/M02/58/1B/ Wkiol1spmkqwi-k9aacqm0koubc773.jpg "/>650) this.width=650;" Src= "http://s3.51cto.com/wyfs02/M02/58/1E/ Wkiom1spmy7wsotwaaebtotlah0241.jpg "/>650) this.width=650;" Src= "http://s3.51cto.com/wyfs02/M00/58/1B/ Wkiol1spmkrsyy9kaadagrm-j68566.jpg "/>650) this.width=650;" Src= "http://s3.51cto.com/wyfs02/M00/58/1E/ Wkiom1spmy6xd1ozaadhdnrv5ou819.jpg "/>650) this.width=650;" Src= "http://s3.51cto.com/wyfs02/M01/58/1B/ Wkiol1spmkvs-fbaaaden_fyeeo739.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/58/1E/wKiom1SpMY_wXFc6AACDhoh1doY774.jpg "/>650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/58/1E/wKiom1SpMY_iY-xpAACGk5svBHU492.jpg "/>650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/58/1B/wKioL1SpMkvSqJHEAAEbZqpIIhw789.jpg "/>

attached: A common character number type in MySQL table

Char fixed-length string

varchar variable-length string

Tinyblob very small blob (binary large object)

Blob small blob

Mediumblob Medium blob

Longblob Large blob

Tinytext very Small text string

Text Small text string

Mediumtext Medium Text string

Longtext Large text string

enum enum; column can be assigned to an enumeration member

Set set, column can be assigned to multiple collection members

This article is from the "Study-everyday" blog, make sure to keep this source http://studys.blog.51cto.com/9736817/1599110

Use of SQL statements