Mysql (1), mysql

Mysql (1), mysql

1. Database Operations

1. Create a database

Create database dbname;

2. view the databases in the system

Show databases;

3. Select the data to use

Use daname

4. view the data tables in a database

Use dbname

Show tables;

5. delete a database

Drop database dbname;

2. operations related to tables in the database

1. Create a table

Create table tablename (

Column_name_1 column_type_1 contrains,

Column_name_2 column_type_2 contrains,

...

Column_name_n column_type_n contrains)

Column_name_n is the column name, column_type_n is the column data type, and contrains are the column constraints.

Eg: Create a table named emp, including: ename (name), hiredate (employment date), sal (salary); field types: varchar (10), date, int (2)

Create table emp (

Ename varchar (10 ),

Hiredate date,

Sal int (2 ));

2. view the created table

Desc tablenema;

3. view the SQL statement used to create a table

Show create table tablename \ G;

\ G enables records to be vertically arranged by field

4. delete a table

Drop table tablename;

5. modify a table

(1) modify the table Type

AlterTable tablenameModify[Column] column_definition [first | after col_name]

Eg: Modify the ename field definition in the emp table and change varchar (10) To varchar (20)

Alter tale emp modify ename varchar (20 );

(2) Add a field

AlterTable tablenameAdd[Column] column_definition [first | after col_name]

Eg: alter table emp add column age int (3 );

(3) Delete table fields

AlterTable tablenameDrop[Column] col_name;

Eg: alter tale emp drop column age;

(4) rename a field

AlterTable tablenameChange[Column] old_col_name column_definition [first | after col_name]

Eg: change the age name to age1 and change the field type to int (4)

Alter table emp change age age1 int (4 );

Note: Both change and modify can modify the table definition. The difference is that the column name must be written twice after change. However, change can modify the column name, But modify cannot.

(5) modify the field arrangement order

The ADD, CHANGE, and MODIFY syntax of the preceding field has an option "first | after column_name". This option can MODIFY the position of a field in the table. "ADD" is added at the end by default, CHANGE, MODIFY does not CHANGE the location by default.

Eg: Add birth date after ename

Alter table emp add birth date after ename;

Modify the age and put it at the beginning

Alter table emp modify age int (3) first;

(6) change the table name

Alter table tablename rename [to] new_name;

Eg: alter table emp rename emp1;