Introduction to common command operations in MySQL (code examples)

This article brings you an introduction to common command operations in MySQL (code examples). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

l Create database: CREATE DATABASE table_name;

l Delete database: DROP DATABASE table_name;

l Show database: SHOW DATABASE;

l Select database: USE table_name;

Note: All database names, table names, and table names Field names are case-sensitive. So, we must use appropriate names before giving any SQL command.

l Create table: CREATE TABLE table_name (column_name column_type); //column column

l Delete table: DROP TABLE table_name;

l Rename table: ALTER TABLE table_name RENAME TO new name;

l Create instance:

create table table_tbl(
   table_id INT NOT NULL AUTO_INCREMENT,   //AUTO~自动增加id字段下一个可用编号
table_title VARCHAR(100) NOT NULL,      //字段使用NOT NULL属性，用户创建具
   table_author VARCHAR(40) NOT NULL,          有NULL的记录会报错
   submission_date DATE,
   PRIMARY KEY ( table_id )   //主关键字，一般分配后不再更改
);

l Insert data:

INSERT INTO table_name ( field1, field2,...fieldN )  VALUES ( value1, value2,...valueN );    
//注意对应创建表时对列的定义

l Query display data:

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE Clause] [OFFSET M ][LIMIT N]

SELECT * from table_name;    //返回所有记录
SELECT * from table_name WHERE table_author='wonker'; //返回作者是wonker的记录

l Modify data:

UPDATE table_name SET field1=new-value1, field2=new-value2
         [WHERE Clause]
UPDATE table_name  SET table_title='Learning JAVA'  WHERE table_id=3;

l Delete records:

DELETE FROM table_name [WHERE Clause]
DELETE FROM table_tbl WHERE table_id=3;

l Delete, add or redefine columns: ALTER command

mysql view table structure command, as follows:

l desc Table name;

l show columns from table name;

l describe table name;

l show create table table name;

Due to the database table The additions, deletions, modifications and checks result in discontinuous numbers. You can use the following method to delete the number and then re-establish the number:

ALTER TABLE table_tbl  DROP title_author;         //删除
ALTER TABLE table_tbl ADD num INT;                 //添加
ALTER TABLE table_tbl ALTER num SET DEFAULT 1000;   //更改默认值

create table if not exists table_name(---);

The above is the detailed content of Introduction to common command operations in MySQL (code examples). For more information, please follow other related articles on the PHP Chinese website!