Review MySQL Databases

I. Database Operations

A) Check the number of databases currently in showdatabases;

B) Create the createdatabase database name. You can also set the encoding method and calibration rules followed by the characterset encoding method collate correction rules;

C) Check if the created database show createdatabase database name l

D) Delete the database dropdatabase database name l

E) fuzzy query showcollation like "gb2312 _ *". After adding *, You can query the display clooation like "Latin &" and display the correction rules applicable to the Latin1 character set.

F) show Character Set displays the encoding method and correction rules of the system.

Ii. Table operations

A) first specify the database use Database Name

B) show tables; view the table name show CREATE TABLE in the current database; view the table structure if the created table DESC table name is used

C) create temporary table name (field name type ,...); Create a temporary table. When disconnected from the server, the system automatically clears the temporary table.

D) Create a table create table table name (ID int, namechar (6); two columns of the table. And define the type

E) The alter table table name Rename to the new table name or rename employee to the new table name;

F) The field type char () varchar () int double float text tinyint is added with unsigned to indicate that there cannot be negative or signed numbers.

G) insert data inert into table name values (value, value ,....); If the insert into Table Name (field name, field name, field name) values (value, value, value) is used to insert data to a fixed column );

H) selet * from table name; view data of all fields

I) altert table name Add column score int (8) zerofill; adding a new column followed by 8 is the display width. Display 8 bytes. zerofill shows that the display effect is filled with spaces without zerofill, and 0.

J) floate (8, 3) set the type when creating a table. It indicates that the table occupies 8 digits, excluding the decimal point. Three digits are retained after the decimal point.

K) decimal (total width, decimal point reserved digits) Numeric (total width, decimal point reserved digits) indicates currency

L) the default bit type is one digit with an integer. The default value is four digits.

M) Boolean is represented by 0 and 1. True or false. 1 byte

N) Char (maximum length) fixed-length string varchar (maximum length) variable-length Character Type Binary binary string varbinary variable-length string

O) date type '2017-9-8' is input in a certain format. You can use/@ to separate time '10: 38: 20' to indicate the time type.

P) datetime indicates the format of the date and time is '2017-8-8 10:53:20'

Q) The Enum type indicates that a single value of up to 65535 member sets can be obtained from an enumeration set. Up to 65 members

R) Auto generate ID int auto_increment primary key auto generate ID

S) Specify the Rule Set create tablet2 (ID int, name char (10) chatacter setgb2313 collate correction rule for the table;

T) blob type. Large binary type. Pictures can be stored.

U) alter table user Modify column namechar (30); used to modify the column type in the table

V) altere table user change column name usernamechar (30); change the name of the specified column to keep up with its type.

W) alter table user drop column photo; delete a specified column. Column keyword is optional

X) alter table user alter column salary set default2000.0; modify the default value to delete the default value. Replace set with drop

Iii. operation data (add, delete, modify, and query) insert into insert update modify delete SELECT query

A) insert statement insert into Table Name (column name, column name .....) Values (value, value .....); It must be matched in a one-to-one order. The column name can be omitted. If the column name is omitted, all values of the column must be input. Insertinto table name values (null, default ....);

I. insert values T1 (name, age) values ('wangwu', 20),. (zhaoliu, 30), ('dd', 21) in batches; insert three rows of data at a time

Ii. insert into T1 (name, age) Select username, Id fromuser; insert all query results into the table. (Subquery)

Iii. insert into T1 Set ID = 18, name = 'hangsan ', age = 17; Set inserts are implemented using set.

Iv. environment variable operation show variables like 'character % '; view the system environment variable set character_set_client = 'gbk'; modify and accept the environment variable set character_set_results = 'gbk'; modify the output environment variable

V. Delete

A) delete from table name where + condition;

B) truncate table name; delete all data in the table (FAST) Transaction log records. Few resources are used.