Database Build Table

First, the database operation:

1. View the database:

>show DATABASES;

2. Create a database:

>create DATABASE db_name; Db_name for Database name

3. Use the database:

>use db_name;

4. Delete the database:

>drop DATABASE db_name;

Second, create the table:

1. Create a table:

>create TABLE table_name (

>id TINYINT UNSIGNED not NULL auto_increment,//id value, unsigned, non-null, incremented-uniqueness, can be the master key.

>name VARCHAR () not NULL

>score TINYINT UNSIGNED not NULL default 0,//Set defaults for column values

>primary KEY (ID)

>) engine=innodb//Set table storage engine, generally commonly used InnoDB and myisam;innodb reliable, support transactions; MyISAM efficient does not support full-text indexing

>default Charset=utf8; Set the default encoding to prevent the database from garbled characters

If you have a conditionally created data table you can also use the > createtable if not EXISTS tb_name(...).

2. Copy the 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 (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:

>describe 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;

Third, modify the table:

1. Change the table structure:

>alter TABLE tb_name Add[change,rename,drop] ... What to change ...

Instance:

>alter TABLE tb_name ADD COLUMN address varchar (n) not NULL;

>alter TABLE tb_name DROP address;

　　　　>alter TABLE tb_name Change score score SMALLINT (4) is not NULL;

Iv. Inserting data:

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, directly written to the inserted data, the primary key ID is a self-increment column, you can not write.

2. Insert the retrieved data:

>insert to Tb_name (name,score) SELECT name,score from Tb_name2;

Five, update the data:

1. Specify Update data:

>update tb_name SET score=189 WHERE id=2;

>update tablename SET columnname=newvalue [WHERE condition]

Vi. Deletion of 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 a statement:

>select * from Tb_name GROUP by score have count (*) >2

3, the relevant conditions control character:

=, >, <, <>, in (...), between A and B, not

And, or

Linke () Use% to match any, _ match one character (can be kanji)

Is null empty value detection

Viii. regular expressions for MySQL:

1, MySQL support regexp regular expression:

>select * from Tb_name WHERE name REGEXP ' ^[a-d] '//Find the name starting with a-d

2, special characters need to be escaped.

Some functions of MySQL:

1. String link--concat ()

>select CONCAT (name, ' = = ', score) from Tb_name

2. Mathematical functions:

AVG, SUM, MAX, MIN, COUNT;

3. Text Processing function:

TRIM, LOCATE, UPPER, LOWER, SUBSTRING

4. Operators:

+ 、-、 *, \

5. Time function:

DATE (), Curtime (), Day (), year (), now () .....

Ten, group query:

1. Group queries can be grouped by the specified columns:

>select Count (*) from Tb_name GROUP by score have COUNT (*) >1;

2, the conditions of use have;

3. Order BY Sort:

ORDER by desc| ASC = sorted by data descending and ascending

Database Build Table