Mysql Common Statements

First, the database operation
1. Create a database:
>create DATABASE db_name; Db_name for Database name
>create DATABASE IF not EXISTS db_name default character set UTF8 COLLATE utf8_general_ci; Conditions to create a database
2. Delete the database:
>drop DATABASE db_name;
>drop DATABASE IF EXISTS db_name;
3. View the database:
>show DATABASES;
4. Select the database:
>use db_name;
5. Modify the Database
>alter DATABASE my_db CHARACTER SET latin1; Modify Database character encoding

second, create the table:
1. Create a table:
>create TABLE IF not EXISTS tb_name (
FID Int (one) not NULL default ' 0 ',//fid INT type display 11 bit, non null, default value is 0
FName VARCHAR (+) not NULL,
Fage Int (one) not NULL,//Set default column value
faddress varchar (+) not NULL,
Fcontace varchar () not NULL,
PRIMARY key (ID)//PRIMARY key
Engine=innodb default Charset=utf8//Set table storage engine, generally commonly used InnoDB and myisam;innodb reliable, support transactions; MyISAM efficient does not support full-text indexing
Set the default encoding to prevent the database from garbled characters
2. Copy table structure and data:
>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 (this is the same as creating a normal table);
4. View the tables available in the database:
>show TABLES;
5. View the structure of the table:
>desc Tb_name;
You can also use:
>show COLUMNS in Tb_name; From can also
6. Delete the table:
>drop [temporary] TABLE [IF EXISTS] tb_name[, tb_name2 ...];
Instance:
>drop TABLE IF EXISTS tb_name;
7. Table renaming:
>rename TABLE name_old to Name_new;
You can also use:
>alter TABLE name_old RENAME name_new;

8. Change the table structure:
>alter TABLE tb_name Add[change,rename,drop] ... What to change ...
Instance:
>alter TABLE tb_name ADD COLUMN address varchar (+) not NULL after ' column_name ';
>alter TABLE tb_name DROP address;
>alter TABLE tb_name Change scoer score SMALLINT (4) is not NULL; Change the column name or column field type (note: There is no inconsistency between the field type and table fields type to modify the table fields type)
9. Add Delete primary key
>alter TABLE tb_name ADD primary key (ID);
>alter TABLE tb_name DROP primary key;

Third, the table additions and deletions to check and change
1. Insert Data:
>insert into Tb_name (id,name,score) VALUES (null, ' Zhang San ',), (null, ' Zhang Si ', 178), (null, ' five ', 134); Here the insertion of multiple data directly behind the comma, the primary key ID is a self-increment column, you can not write.
>insert to Tb_name (name,score) SELECT name,score from Tb_name2;

2. Update data:
>update tb_name SET score=189 WHERE id=2;
>update tablename SET columnname=newvalue [WHERE condition]

3. Delete data:
>delete from Tb_name WHERE id=3;
>delete from Tb_name; Delete the entire table data without a condition

4. Condition Control:
(1). WHERE statement:
>select * from Tb_name WHERE id=3;
(2). Having statement:
>select * from Tb_name GROUP by score have count (*) >2
(3). Related condition Control:
=, >, <, <>, in (...), between A and B, not
And, or
Like () Usage%: matches any character _: matches one character (can be kanji)
Is null empty value detection

Some functions of 5.MySQL

(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), Operator: + 、-、 *, \
(5), Time function: Date (), Curtime (), Day (), year (), now () ....

Mysql Common Statements