MySQL database Add, delete, change, check and other basic commands

Test environment: Windows7 64-bit Mysql.exe, Navicat Lite for MySQL, MySQL 5.0.18

Structure of MySQL database:
The database contains multiple tables (table), a table containing multiple attribute columns (column), a property column containing multiple data, and a property column with four information (Field,type,null,key,default).

We have to do the work is to database, table, column, data to increase, delete, change, check four kinds of operations, altogether 4*4=16 kind of command.

1.1 Add Database --Create a database
Create Database DatabaseName; --Create a database named DatabaseName

Example: Create Database mydb;

1.2 Delete Database --Delete a database
Drop database databaseName; --delete a database named DatabaseName

Drop database if exists databaseName; --If database databasename is present, delete

1.3 Change Database

Unknown

1.4 Search Database
show databases; --Show all databasename of database

Select Database (); --Displays the current database's DatabaseName

2.1 Add Table -Create a table with at least one column when creating the table, otherwise error
　　CREATE TABLE TableName
(
ColumnName1 < properties information;
[, Primary KEY (ColumnName)] --You can also define a primary key (primary key) here, and the contents of [] are optional
[, FOREIGN Key (COLUMNNAME1) reference tableName2 (columnName2)] /* Define the foreign key columnName1 is the current table already defined column,tablename2 is another already defined table name, ColumnName2 is another table's column name */
) [comment[=] ' Description of table "];
--< Property information >:typename[null|not Null][default defaultvalue] [primary key][ Auto_increment][comment ' column description content '][identity (start value, increment)]
-- Null|not null: null or non-empty
--default DefaultValue: Default
Span style= "color: #c0c0c0;" > --auto_increment: Automatically increases by 1 from 1 or the current maximum, as with the identity (*)

Cases:

CREATE TABLE MyTable

(

ID Int (8) NOT NULL auto_increment comment ' table's primary key ' primary key,

Name Char (TEN) NOT null default ' None ',

FOREIGN key (ID) reference Mytable1 (id_1)

) comment= ' My watch ';

2.2 Deletingtable--Deleting tables
drop table tableName1 [, tableName2]--can delete one or more tables, default to the table in the current database
drop table Databasename.tablename--delete a table named TableName in DatabaseName

2.3 Changing Table --Change the name of the table　　
Rename table TableName to Tablename_new [, tableName1 to Tablename_new1]; --Rename table named TableName to Tablename_new
ALTER TABLE TableName Rename to tablename_new; --Ibid .

2.4 Check Table --View the name of the table
Show tables from DatabaseName; --Show all tablename of DatabaseName
Show tables; --Displays all tablename of the current database (use databasename;--into a database)

3.1 Increment column
ALTER TABLE TABLENAME add [column] ColumnName < properties information >; -- Insert ColumnName,< property information > Reference 2.1

Example: ALTER TABLE mytable add age int (2) default 0;

ALTER TABLE TABLENAME add [column] columnName < attribute information > after columnName1; --Insert ColumnName after columnName1

3.2 Delete Column
ALTER TABLE TableName drop [column] columnName; --delete ColumnName

3.3 Change Column
ALTER TABLE TableName Modify ColumnName < properties information >; --Modify the < attribute information of ColumnName >

Example: ALTER TABLE mytable modify age int (4);

ALTER TABLE TableName change ColumnName columnname_new < properties information >; --Modify ColumnName's name to Columnname_new and its < attribute information >

3.4 Search Column --View all column columnname and their information in the table
Describe TableName; --View the ColumnName and its < attribute information for all column names in the table named TableName >
Show columns from TableName; --Ibid .

4.1 Add data

Insert into TableName (Columnname1,columnname2[,columnname3])
VALUES (Value1,value2[,value3]) [, (Value_1,value_2[,value_3])]; --inserting one or more rows of data

Example: INSERT INTO MyTable (name,age) VALUES (' Bob ', +), (' Linda ', 21);

4.2 Delete Data

TRUNCATE TABLE tableName; --Empty the data in the TableName

Delete from TableName; --Ibid .

Delete from TableName where < conditional statements >; --delete data rows that meet < conditional statement >

Example: Delete from mytable where age=20;

4.3 Change data

Update TableName set <columnName=data> where < conditional statements >; --Change the columnname data in rows that meet the < condition statement >

Example: Update mytable set age=22 where name= ' Bob ';

4.4 Search Data

Select Columnname1[,columnname2] from TableName where < conditional statements >; --find data that satisfies the < conditional statement > attribute named Columnname1[,columnname2]

Example: Select Name,age from mytable where id=2;

SELECT * from TableName where < conditional statements >; --find rows of data that meet the < condition statement >

Reference: http://www.w3school.com.cn/sql/index.asp

http://blog.csdn.net/Sunboy_2050/article/details/5131863

MySQL database Add, delete, change, check and other basic commands