Mysql (1)

I. Related operations of the database

1. Create a database

Create Database dbname;

2. See what databases are in the system

show databases;

3. Choose which data to use

Use Daname

4. See what data tables are in a database

Use dbname

Show tables;

5. Deleting a database

Drop Database dbname;

Two. Related operations for 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)

Where Column_name_n is the column name, Column_type_n is the column data type, and Contrains is the constraint of the column

Eg: Create a table named EMP, including: ename (name), HireDate (hire date), Sal (Salary), field type: varchar, date,int (2)

CREATE TABLE EMP (

ename varchar (10),

HireDate Date,

Sal Int (2));

2. View the tables you have created

Desc Tablenema;

3. View the SQL statement that created the table

Show CREATE TABLE TableName \g;

Where \g is to allow records to be vertically arranged in fields

4. Delete a table

drop TABLE TableName;

5. Modify the table

(1) Modifying table types

alter table tablename modify[column] Column_definition[first|after col_name]

Eg: Modify the ename field definition in the EMP table to change varchar (10) to varchar (20)

Alter tale EMP Modify ename varchar (20);

(2) Add field

alter table tablename add[column] Column_definition[first|after col_name]

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

(3) Delete table field

alter table tablename drop[column] col_name;

Eg:alter Tale EMP Drop column age;

(4) Renaming of fields

alter table tablename change [column] Old_col_name Column_definition[first|after col_name]

Eg: Change the age name to Age1 while modifying the field type to int (4)

ALTER TABLE EMP change age Age1 int (4);

Note: Both change and modify can modify the definition of a table, except that the change is followed by a two-time column name, but the advantage of changing is that the column name can be modified, modify not.

(5) Modifying the order of the fields

The previous field addition modification (add,change,modify) syntax has an optional first|after column_name, which modifies the position of the field in the table, add defaults to the last addition, and change,modify does not change the position by default.

Eg: add birth date back to Ename

ALTER TABLE EMP Add birth date after ename;

Modify age, and then put it in the front

ALTER TABLE EMP Modify age int (3) first;

(6) Change the name of the table

ALTER TABLE TableName Rename[to] new_name;

Eg:alter table emp Rename emp1;

Mysql (1)