Book-MySQL-Operate

创建数据库

CREATE DATABASE db_name

 

查看数据库

SHOW DATABASES

 

选择数据库

USE db_name

　　

删除数据库

DROP DATABASE db_name

 

列主键

CREATE TABLE [IF NOT EXISTS] tbl_name(col_name type PRIMARY KEY,.........)
CREATE TABLE [IF NOT EXISTS] tbl_name(..........,PRIMARY KEY(col_name[,....]))

 

表命令

CREATE TABLE [IF NOT EXISTS] tbl_name(......)COMMENT=comment [DEFAULT] CHARSET [=] charSet

 

查看表

SHOW TABLES

 

表列定义

DESCRIBE tbl_name
DESC tbl_name

 

改变当前控制台输出格式(临时)

SET NAMES charset

 

删除表

DROP TABLE [IF NOT EXISTS] tbl_name

 

查询当前引擎

SHOW VARIABLES LIKE 'storage_angine%'

 

指定表引擎

CREATE TABLE [IF NOT EXISTS] tbl_name(.........)ENGINE=engine

 

查看帮助

HELP content

 

修改表名

ALTER TABLE ori_tbl_name RENAME [TO] new_tbl_name

 

添加字段

ALTER TABLE tbl_name ADD col_name type [attr]

 

修改字段

ALTER TABLE tbl_name CHANGE ori_col_name new_col_name type [attr]

 

删除字段

ALTER TABLE tbl_name DROP col_name

 

添加主键

ALTER TABLE tbl_name ADD CONSTRAINT pk_name PRIMARY KEY tbl_name(col_name)

 

添加外键

ALTER TABLE tbl_name ADD CONSTRAINT fk_name FOREIGN KEY(col_name) REFERENCES tbl_name(col_name)

 

插入数据

INSERT INTO tnl_name[(col_name,......)] VALUE(......)[,(....).....]

 

查询结果放入新表(必须存在)

INSERT INTO new_tbl_name(col_name,......) SELECT col_name,....... FROM tbl_name

 

查询结果放入新表(自动创建)

CREATE TABLE new_tbl_name(SELECT col_name,...... FROM tbl_name)

 

更新数据

UPDATE tbl_name SET col_name=value[,......]  [WHERE where_condition]

 

删除数据

1): DELETE FROM tbl_name [WHERE where_condition]
2): TRUNCATE TABLE tbl_name
说明: 区别就是第二种是清空数据

 

查询语句

SELECT col_name,.....
FROM tbl_name
[WHERE where_condtion]
[GROUP BY col_name,...]
[HAVING having_condtion]
[ORDER BY col_name [ASC|DESC],...]
[LIMIT startIndex,len]

 

别名: AS

 

IN与NOT IN: 查询指定的枚举值范围

 

EXISTS与NOT EXISTS: 检测是否有值,返回值为TRUE,否则FALSE.