Mysql database addition, deletion, modification, query and other basic commands, mysql database

Mysql database addition, deletion, modification, query and other basic commands, mysql database

Test environment: Windows 7 64-bit mysql.exe, Navicat Lite for MySQL, mysql 5.0.18

Basic Structure of the mysql database:
Databases contain multiple tables, tables contain multiple attribute columns, attribute columns contain multiple data, and attribute columns have four fields, type, null, key, default ).

The job we need to do is to add, delete, modify, and query the database, table, column, and data. There are 4*4 = 16 commands in total.

 

1.1 add database-- Create a database
Create database databaseName;-- Create a database named databaseName

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 exists, delete

 

1.3 Change database

Unknown

 

1.4 query database
Show databases;-- Display databaseName of all databases

Select database ();-- Display the databaseName of the current database

 

2.1 Add table　　　　-- Create a table with at least one column when creating the table; otherwise, an error is reported.
Create table tableName
(
ColumnName1 <attribute information>
[, Primary key (columnName)]-- You can also define a primary key here ),[] Content is optional
[, Foreign key (columnName1) reference tableName2 (columnName2)]/* Define that the foreign key columnName1 is the column defined in the current table, tableName2 is another defined table name, And columnName2 is the column name of another table */
) [Comment [=] 'table description content'];
-- <Attribute information>: typeName [null | not null] [default defaultValue] [primary key] [auto_increment] [comment 'column description '] [identity (start value, incremental)]
-- Null | not null: null or not empty
-- Default defaultValue: default Value
-- Auto_increment: automatically increases by 1 from 1 or the current maximum value, the same as identity ().

 

Create table mytable (id int (8) not null auto_increment comment 'table's primary key 'Primary key, name char (10) not null default 'none', foreign key (id) reference mytable1 (id_1) comment = 'my table ';

 

2.2 Delete table-- Delete a table
Drop table tableName1 [, tableName2]-- One or more tables can be deleted. The default value is the table in the current database.
Drop table databaseName. tableName-- Delete the table named tableName in databaseName

 

2.3 change table　　-- Change the table name
Rename table tableName to tableName_new [, tableName1 to tableName_new1];-- Rename a table named tableName to tableName_new
Alter table tableName rename to tableName_new;-- Same as above

 

2.4 query table-- ViewTable Name
Show tables from databaseName;-- Display all tableName of databaseName
Show tables;-- Display all tableName (use databaseName; -- enter a database) of the current database)

 

3.1 add column
Alter table tableName add [column] columnName <attribute information>;--Insert columnName,<Property Information> see 2.1

 

alter table mytable add age int(2) default 0;

Alter table tableName add [column] columnName <Property 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 <attribute information>;-- Modify <attribute information> of columnName

 

alter table mytable modify age int(4); 

Alter table tableName change columnName columnName_new <attribute information>;-- Modify columnName to columnName_new and its <attribute information>

 

3.4 check column -- View the columnName and information of all columns in the table
Describe tableName;-- View the columnName and <attribute information> of all columns in the table named tableName.
Show columns from tableName;-- Same as above

 

4.1 increase data

　　Insert into tableName (columnName1, columnName2 [, columnName3])
　　Values (value1, value2 [, value3]) [, (value_1, value_2 [, value_3])];-- Insert one or more rows of data

insert into mytable (name,age) values('Bob',20),('Linda',21);

Load data local infile 'path/filename' into table tableName-- Load the data in the file into the table. The data in the file is separated by '\ t', and the data rows are separated by' \ n '.

load data local infile 'F:/test.txt' into table mytable;

 

 

 

4.2 Delete data

Truncate table tableName;-- Clear data in tableName

Delete from tableName;-- Same as above

Delete from tableName where <Condition Statement>;-- Delete data rows that meet the <Condition Statement>

 

delete from mytable where age=20; 

 

4.3 Change data

Update tableName set <columnName = data> where <Condition Statement>;-- Change the data of columnName in the row that meets the <Condition Statement> to data

 

update mytable set age=22 where name='Bob'; 

 

4.4 query data

Select columnName1 [, columnName2] from tableName where <Condition Statement>;-- Search for data with the attribute columnName1 [, columnName2] meeting the <Condition Statement>

 

select name,age from mytable where id=2; 

Select * from tableName where <Condition Statement>;-- Search for data rows that meet the <Condition Statement>

 

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

Summary of common MySQL database commands

Mysql official documentation