MySQL Database (2) _MYSQL database operation statement

Source: Internet
Author: User

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 * /


--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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.