Add, delete, modify, and query databases and tables based on mysql operations

Add, delete, modify, and query databases and tables based on mysql operations

I. Database Management
-- 1. log on to the database

1 mysql -u root -p;

-- 2. view all databases on the database server

1 SHOW DATABASES;

-- 3. Create a database

CREATE DATABASE MyDATA;

-- 4. Create a database with the set Character Set

CREATE DATABASE MYDATA DEFAULT CHARACTER SET UTF8;

-- 5. delete a database

DROP DATABASE MYDATA;

-- 6. view the default Character Set

SHOW CREATE DATABASE MYDATA;

-- 7. Modify the database Character Set

ALTER DATABASE MYDATA DEFAULT CHARACTER SET GBK;

2. Manage data tables
-- 1. Select a database

USE MYDATA;

-- 2. view the table (it must be after the database is selected)

SHOW TABLES;

-- 3. Create a table and specify fields. Note that there is no comma after the last field.

CREATE TABLE STUDENT(SID INT,SNAME VARCHAR(20),SAGE INT);

 

-- 4. view the structure of a table

DESC STUDENT;

-- 5. delete a specified table

DROP TABLE STUDENT;

-- 6. Add a specified field

ALTER TABLE STUDENT ADD COLUMN SGENDER VARCHAR(2);

-- 7. delete a specified field

ALTER TABLE STUDENT DROP COLUMN SGENDER;

-- 8. Modify the table Field Type

ALTER TABLE STUDENT MODIFY COLUMN SNAME VARCHAR(100);

-- 9. Modify the field name

ALTER TABLE STUDENT CHANGE COLUMN SNAME USERNAME VARCHAR(2);

-- 10. Modify the table name

ALTER TABLE STUDENT RENAME TO TEACHER;