Basic MySQL command for interview

Since the operation of the database from the database link to write, including the building, building tables, add and remove the Check field and constraints, delete the library, delete the table of data, the following is mainly my previous interview summary, welcome to add!

One, the database connection

1. Connect the machine (no spaces between p and password 123456)

Mysql-u username-p123456;

2. Remote connection

Mysql-h IP Address to access-u username-p123456;

Second, the operation of the library

Create DATABASE db_name;

Drop database db_name;

show databases;

Use db_name;

Rename database db_name to New_db_name

Mysqldump-u username-p123456 db_name > exported file name; #备份

Third, the operation of the table

1. Build Table/Delete table/Copy table/Backup table

creat table Db_name.tb_nane (

ID smallint unsigned auto_increment,

Username varchar () Not NULL unique key,

Sex enum ("1", "2") default "1")

drop table (if exists) tb_name;

CREATE TABLE tb_name2 select * from Db_name.tb_name;

Mysqldump-u username-p123456 db_name tb_name> the exported file name;

2. Inserting data

Insert into Tb_name (col_name1,col_name2) VALUES (value 1, value 2);

Insert into Tb_name.tb_name (username,sex) VALUES ("Zhang", 1);

INSERT into tb_name.tb_name values (1, "Wang", 1), (2, "Lili", 2), (3, "haha", 2);

3. Delete data

Delete col_name from tb_name where col_name= value; #删除某条记录

TRUNCATE TABLE tb_name; #删除所有记录

4. Add/Remove a single column

ALTER TABLE Tb_name add Col_name col_defination [first |after col_name];

ALTER TABLE tb-name drop col_name;

5. Add/Remove constraints

ALTER TABLE Tb_name add primary key col_name; #主键 (primary key is not NULL automatically; only one primary key per table)

ALTER TABLE tb_name add unique col_name;

ALTER TABLE Tb_name add index index_name col_name;

ALTER TABLE tb_name drop PRIMARY key;

6. Modify the constraint

ALTER TABLE tb_name modify ID int unsigned; #修改列id的类型为int unsigned

ALTER TABLE tb_name change ID sid int unsigned; #修改列id的名字为sid, and modify the property to int unsigned

7. View Table Structure

Show Colums from Tb_name;

Desc Tb_name;

8. Limit query return results

1> Limit (10 to 20 selected) < First recordset is numbered 0>

SELECT * FROM students order by ID limit 9, 10;

2> Top 10 Records selected

Select top ten from students;

9, the external connection in MySQL, divided into the left outer connection and the right connection, that is, in addition to return to meet the connection conditions of the results, but also to return the left table (left connection) or the right table (right connection) does not meet the results of the join conditions, relative to the use of NULL corresponding.

Select Col_name from tb_name1 left join tb_name2 on Tb_name1.col=tb_name2.col;

10, union character or multiple columns (Connect column ID to ":" and column name and "=")

Select Concat (ID, ': ', name, ' = ') from students;

Basic MySQL command for interview