Common command set, Common commands in linux
Database Server (many databases)-database (many tables)-table
// Display all databases
Mysql> show databases;
// Create a new database
Mysql> create database name;
// Enter the database
Mysql> use Database Name; (display Database changed)
// Display all tables in the database (tables in the database)
Mysql> show tableS; (display Empty set (0.00 sec ))
// Create a new table (new in the database)
Mysql> create table PersonList (table name)
-> (
-> Id int not null (column name + Type + others)
-> Name char (8) not null,
-> Title char (8) not null
-> );
// Display the table structure
Mysql> DESCRIBE mytable;
Add:
// Insert a single row of data (sequential insertion)
Mysql> insert into PersonList
-> Values
-> (1, "Lu Bu", "Feng Xian ");
// Insert multiple rows of data (sequential insertion)
Mysql> insert into PersonList
-> Values
-> (5, "Guan Yu", "Yun Chang"), (6, "Zhang Fei", "Yi de ");
// Insert part of data in a row (insert in sequence)
Mysql> insert into students (id, name, title) values (4, "Sun ce", "Uncle ");
// Insert a column (at the end)
Mysql> alter table PersonList add sex (inserted column name) char (8 );
// Insert the tel column after the name column (remember to write the Data Type of the newly inserted column, which I found after writing for a long time)
Mysql> alter table PersonList add tel char (8) after name;
Query:
// Query all data in the table
Mysql> select * from PersonList; (table name)
// Query by condition (only data under this condition is displayed. For example, only the name of each person is displayed)
Mysql> select name (condition, which can be separated by commas) from PersonList (Table name );
// Query all row data of id> 2 in the table
Mysql> select * from PersonList where id> 2;
// Query the data with the name "Lu" in the table
Mysql> select * from PersonList where name like "% lu % ";
// Query the data with id <6 and id> 1 in the table (you can use and, or, = ,! =,> =, Etc)
Mysql> select * from PersonList where id <6 and id> 1;
Change:
// Subtract 1 from all IDs in the table
Mysql> update PersonList set id = id-1;
// Change the id of the table named Lu Bu to-1 (set is to be changed, and where is the condition)
Update PersonList set id =-1 where name = "Lu Bu ";
// Change the table name to lub, and the data id is set to 0 (multiple conditions or data to be modified)
Mysql> update PersonList set id = 0 where title = "Feng Xian" and name = "Lu Bu ";
// Change the name of the column in the table to tit (if the data in the title is empty, an error will always be reported)
Mysql> alter table PersonList change title tit char (5) not null;
// Change the table name PersonList to Person.
Mysql> alter table PersonList rename Person;
Delete:
// Delete the positive row of the table whose id is 1
Mysql> delete from PersonList where id = 1;
// Delete all data in the table
Mysql> delete from PersonList;
// Delete the tel Column
Mysql> alter table PersonList drop tel;
// Delete the Person table
Mysql> drop table Person;
// Delete the database threekinggenerallist
Mysql> drop database threekinggenerallist;
Finally: