MySQL Database (2) _MYSQL database operation statement

I. SQL statements about database operations

--1Create a database (create a corresponding folder on disk) Create databases [ifNOT EXISTS] db_name [characterSetXXX]--2. View the database show databases; View all databases show create database db_name; View how the database was created--3. Modify database Alter DB_NAME [characterSetXXX]--4. Delete the database drop databases [ifexists] db_name; --5. Use database to switch database using Db_name;--Note: There is no way to return to a previous state after entering a database, but you can switch to view the currently used database by using theSelectDatabase ();

Ii. SQL statements about data table operations

Create a database table

-- Syntax create TABLE tab_name (            field1 type[integrity constraint],            field2 type,            ...            FIELDN type        set xxx];

Integrity constraints:

/* constraint:       primary KEY (non-null and unique)  : The field that uniquely distinguishes the current record is called the primary key!       unique not                null       auto_increment: self-increment field, for primary key field, primary key field must be number  type * /

Example:

--Create an Employee table Employee CREATE TABLE employee (IDintprimary key auto_increment, name varchar ( -), gender bitdefault 1,--genderChar(1)default 1-----or TINYINT (1) Birthday date, job varchar ( -), SalaryDouble(4,2) unsigned, resume text--Note that this is the last field without a comma);

To view table information:

desc tab_name                         from tab_name          #查看表结构show tables                         #查看当前数据库中的所有的表show CREATE table tab_name          #查看当前数据库表建表语句         

To modify table information:

-- (1Add column (field) ALTER TABLE tab_name add [column] column name type [integrity constraint][first|after field name]; ALTER TABLE user add addr varchar ( -) notNULLUnique first/After username; #添加多个字段 ALTER TABLE users2 add addr varchar ( -), add AgeintFirst , add birth varchar ( -) after name; -- (2Modify a column type ALTER TABLE tab_name modify column name type [integrity constraint][first|after field name]; ALTER TABLE USERS2 modify age tinyintdefault  -; ALTER TABLE USERS2 Modify AgeintAfter ID; -- (3Modify the column names ALTER TABLE TAB_NAME change [column] column name new column name type [integrity constraint][first|after field name]; ALTER TABLE USERS2 change ageint default  -First ; -- (4Delete a list of ALTER TABLE Tab_name drop [column] names; --think: Delete multiple columns?      Delete one and fill in one? ALTER TABLE USERS2 Add salaryfloat(6,2) Unsigned notNULLAfter name, drop addr; -- (5) Modify table name rename table name to a new name; -- (6) The character set used by the table ALTER TABLE student characterSetUTF8;

To delete a table:

drop table tab_name;

MySQL Database (2) _MYSQL database operation statement