MySQL——基础操作

Author：二滑大魔王
发布时间：February 16, 2020
1110views
No comments
12840字数
Categories： Mysql DataBase

操作数据表

创建数据表

语句

-- 字段声明
col_nmae type[NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT][PREIMARY_KEY][reefernece_definition]

参数说明

参数	说明
col_name	字段名
type	字段类型
NOT NULL or NULL	指出该列是否允许是空值,系统统一一般默认允许为空值,所以当不允许为空值时,必须使用NOT NULL
DEFAULT default_value	表示默认值
AUTO_INCREMENT	表示是否自动标号,每个表只能有一个AUTO_INCREMENT列,必须被索引
PRIMARY_KEY	表示是否为主键,一个表只能有一个PRIMARY KEY。如果表中没有一个PRIMARY KEY,而某些程序需要PRIMARY KEY,MySQL将返回一个没有任何NULL列的UNIQUE键,座位PRIMARY KEY
reference_definition	为字段添加注释

示例

MySQL [(none)]> use angel
MySQL [angel]> create table angel_users(
    -> id int auto_increment primary key,
    -> user varchar(30) not null,
    -> created_time datetime);

查看表结构

-- 使用SHOW [FULL] COLUMENS FROM 表名 [FROM 数据库名];
MySQL [angel]> show columns from angel_users;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| user         | varchar(30) | NO   |     | NULL    |                |
| created_time | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
-- 使用DSESCRIBE 表名;
MySQL [angel]> describe angel_users;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| user         | varchar(30) | NO   |     | NULL    |                |
| created_time | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

修改表结构

语句

ALTER [IGNORE] TABLE table_name alter_spec[alter_spec]..

参数说明

参数	说明
[IGNORE]	可选项,标识如果出现重复关键的行,则只执行一次,其他重复的行被删除
table_name	指定要修改的数据表的名称
alter_spe	用于定义要修改的内容,其语法格式如下

添加字段

语句

ADD [COLUMN] create_definition [FIRST | AFTER column_name]

示例

-- 添加sex字段,类型为tinyint,默认值为1,备注'...',添加在user字段后.
MySQL [angel]> alter table angel_users add sex tinyint(1) not null default 1 comment '性别:0.女,1.男' after user;
-- 添加phone字段,类型varchar(11)
MySQL [angel]> alter table angel_users add phone varchar(11) not null after user;
-- 验证结果
MySQL [angel]> describe angel_users;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| user         | varchar(30) | NO   |     | NULL    |                |
| phone        | varchar(11) | NO   |     | NULL    |                |
| sex          | tinyint(1)  | NO   |     | 1       |                |
| created_time | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

删除字段

语句

DROP [COLUMN] col_name

示例

-- 删除phone字段
MySQL [angel]> alter table angel_users drop sex;
-- 验证结果
MySQL [angel]> describe angel_users;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| user         | varchar(30) | NO   |     | NULL    |                |
| phone        | varchar(11) | NO   |     | NULL    |                |
| created_time | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

添加索引

语句

-- index_col_name:设置的字段
-- 创建普通索引
ADD INDEX [INDEX_NAME](index_col_name,...)
-- 创建主键
ADD PRIMARY KEY(index_col_name)
-- 创建唯一索引
ADD UNIQUE [index_name] (index_col_name)

示例

-- 为user字段创建普通索引,索引名为user_index
MySQL [angel]> alter table angel_users add index user_index(user);
-- 为phone创建唯一索引
MySQL [angel]> alter table angel_users add unique unique_phone(phone);
-- 验证结果
MySQL [angel]> describe angel_users;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| user         | varchar(30) | NO   | MUL | NULL    |                |
| phone        | varchar(11) | NO   | UNI | NULL    |                |
| created_time | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

删除索引

语句

-- 删除主键索引(在不是自增情况下可直接使用)
DROP PRIMARY KEY
-- 删除(普通/唯一)索引
DROP INDEX index_name

示例

-- 删除主键索引,先重定义字段取消自增再删除
MySQL [angel]> alter table angel_users modify id int;
MySQL [angel]> alter table angel_users drop primary key;
-- 删除普通索引
MySQL [angel]> alter table angel_users drop index user_index;
-- 删除唯一索引
MySQL [angel]> alter table angel_users drop index unique_phone;
-- 验证
MySQL [angel]> describe angel_users;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | NO   |     | NULL    |       |
| user         | varchar(30) | NO   |     | NULL    |       |
| phone        | varchar(11) | NO   |     | NULL    |       |
| created_time | datetime    | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

修改默认值

语句

ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

示例

-- (添加|修改)默认值
MySQL [angel]> alter table angel_users alter user set default 'QvBiLam';
-- 删除默认值
MySQL [angel]> alter table angel_users alter phone drop default;
-- 验证结果
MySQL [angel]> describe angel_users;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | NO   |     | NULL    |       |
| user         | varchar(30) | NO   |     | QvBiLam |       |
| phone        | varchar(11) | NO   |     | NULL    |       |
| created_time | datetime    | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

修改字段

语句

CHANGE [COLUMN] old_col_name creaet_definition

示例

-- 将user varchar(30)字段修改成name varchar(32)
MySQL [angel]> alter table angel_users change user name varchar(32);
-- 验证结果
MySQL [angel]> describe angel_users;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | NO   |     | NULL    |       |
| name         | varchar(32) | YES  |     | NULL    |       |
| phone        | varchar(11) | NO   |     | NULL    |       |
| created_time | datetime    | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

重定义字段

语句

MODIFY [COLUMN] create_definition

示例

-- 修改子句
MySQL [angel]> alter table angel_users modify name varchar(24) not null default 'Miss you';
-- 验证结果
MySQL [angel]> describe angel_users;
+--------------+-------------+------+-----+----------+-------+
| Field        | Type        | Null | Key | Default  | Extra |
+--------------+-------------+------+-----+----------+-------+
| id           | int(11)     | NO   |     | NULL     |       |
| name         | varchar(24) | NO   |     | Miss you |       |
| phone        | varchar(11) | NO   |     | NULL     |       |
| created_time | datetime    | YES  |     | NULL     |       |
+--------------+-------------+------+-----+----------+-------+

更改表名

语句

RENAME [AS] new_tbl_name

示例

-- 修改angel_users表名为angel_name
MySQL [angel]> alter table angel_users rename angel_name;
-- 验证结果
MySQL [angel]> show tables;
+-----------------+
| Tables_in_angel |
+-----------------+
| angel_name      |
| test            |
+-----------------+

复制表

语句

-- 只复制表结构
CREATE TABLE [IF NOT EXISTS] 表名 LIKE 表名
-- 复制表结构和数据
CREATE TABLE 新表名 AS SELECT * FROM 源表名

示例

-- 创建test1结构为angel_name表的结构
MySQL [angel]> create table test1 like angel_name;
-- 验证表结构
MySQL [angel]> describe test1;
+--------------+-------------+------+-----+----------+-------+
| Field        | Type        | Null | Key | Default  | Extra |
+--------------+-------------+------+-----+----------+-------+
| id           | int(11)     | NO   |     | NULL     |       |
| name         | varchar(24) | NO   |     | Miss you |       |
| phone        | varchar(11) | NO   |     | NULL     |       |
| created_time | datetime    | YES  |     | NULL     |       |
+--------------+-------------+------+-----+----------+-------+
-- 验证数据
MySQL [angel]> select * from test1;
Empty set (0.00 sec)

-- 创建test2复制angel_name表结构和数据
MySQL [angel]> create table test2 as select * from angel_name;
-- 验证表结构
MySQL [angel]> describe test2;
+--------------+-------------+------+-----+----------+-------+
| Field        | Type        | Null | Key | Default  | Extra |
+--------------+-------------+------+-----+----------+-------+
| id           | int(11)     | NO   |     | NULL     |       |
| name         | varchar(24) | NO   |     | Miss you |       |
| phone        | varchar(11) | NO   |     | NULL     |       |
| created_time | datetime    | YES  |     | NULL     |       |
+--------------+-------------+------+-----+----------+-------+
4 rows in set (0.00 sec)
-- 验证数据
MySQL [angel]> select * from test2;
+----+---------+-------------+--------------+
| id | name    | phone       | created_time |
+----+---------+-------------+--------------+
|  1 | QvBiLam | 12312341234 | NULL         |
|  2 | sbzcx   | 12312344321 | NULL         |
+----+---------+-------------+--------------+

删除表

语句

DROP TABLE [IF EXISTS] 表名;

示例

-- 删除test1,test2表
MySQL [angel]> drop table test1,test;
-- 验证结果
MySQL [angel]> show tables;
+-----------------+
| Tables_in_angel |
+-----------------+
| angel_name      |
+-----------------+
1 row in set (0.00 sec)

增删改查

插入数据

-- values插入
insert into angel_name (id,name,phone) values (3,'qvbilam',''),(4,'zcxSB','');
-- set插入
insert into angel_name set id=5,name='xx',phone='666';
-- select插入
insert into angel_name (id,name,phone) select id,name,phone from angel_name where id < 2;

修改数据

参数	说明
LOW_PRIORITY:	可选项,表示在多用户访问数据的情况下可用于延迟update操作,直到没有用户读取数据库为止,仅适用于表级所的存储引擎(如 IyISAM、MEMORY、MERGE).
IGNORE	通过IGNORE语句更新表中多行数据时,如果出现错误,那么整个update语句都会被取消
ORDER BY	用于限定表中的被修改的次序
LIMIT	限定被修改的行数

-- 语句
update [LOW_PRIORITY] [IGNORE] table_name
set feild = value [feild2 = value]
[WHERE] [ORDER BY] [LIMIT]
-- 示例
update ignore angel_name set name='QvBiLam' where id < 5 order by id desc limit 5;

删除数据

-- delete删除(类似修改)
delete from angel_name where id < 5 order by id desc limit 1;
-- truncate table_name 删除
truncate angel_name;

Last modification：February 18th, 2020 at 10:11 pm

MySQL——基础操作

二滑大魔王 • 2020 年 02 月 16 日

操作数据表

创建数据表

语句

-- 字段声明
col_nmae type[NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT][PREIMARY_KEY][reefernece_definition]

参数说明

参数	说明
col_name	字段名
type	字段类型
NOT NULL or NULL	指出该列是否允许是空值,系统统一一般默认允许为空值,所以当不允许为空值时,必须使用NOT NULL
DEFAULT default_value	表示默认值
AUTO_INCREMENT	表示是否自动标号,每个表只能有一个AUTO_INCREMENT列,必须被索引
PRIMARY_KEY	表示是否为主键,一个表只能有一个PRIMARY KEY。如果表中没有一个PRIMARY KEY,而某些程序需要PRIMARY KEY,MySQL将返回一个没有任何NULL列的UNIQUE键,座位PRIMARY KEY
reference_definition	为字段添加注释

示例

MySQL [(none)]> use angel
MySQL [angel]> create table angel_users(
    -> id int auto_increment primary key,
    -> user varchar(30) not null,
    -> created_time datetime);

查看表结构

-- 使用SHOW [FULL] COLUMENS FROM 表名 [FROM 数据库名];
MySQL [angel]> show columns from angel_users;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| user         | varchar(30) | NO   |     | NULL    |                |
| created_time | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
-- 使用DSESCRIBE 表名;
MySQL [angel]> describe angel_users;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| user         | varchar(30) | NO   |     | NULL    |                |
| created_time | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

修改表结构

语句

ALTER [IGNORE] TABLE table_name alter_spec[alter_spec]..

参数说明

参数	说明
[IGNORE]	可选项,标识如果出现重复关键的行,则只执行一次,其他重复的行被删除
table_name	指定要修改的数据表的名称
alter_spe	用于定义要修改的内容,其语法格式如下

添加字段

语句

ADD [COLUMN] create_definition [FIRST | AFTER column_name]

示例

-- 添加sex字段,类型为tinyint,默认值为1,备注'...',添加在user字段后.
MySQL [angel]> alter table angel_users add sex tinyint(1) not null default 1 comment '性别:0.女,1.男' after user;
-- 添加phone字段,类型varchar(11)
MySQL [angel]> alter table angel_users add phone varchar(11) not null after user;
-- 验证结果
MySQL [angel]> describe angel_users;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| user         | varchar(30) | NO   |     | NULL    |                |
| phone        | varchar(11) | NO   |     | NULL    |                |
| sex          | tinyint(1)  | NO   |     | 1       |                |
| created_time | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

删除字段

语句

DROP [COLUMN] col_name

示例

-- 删除phone字段
MySQL [angel]> alter table angel_users drop sex;
-- 验证结果
MySQL [angel]> describe angel_users;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| user         | varchar(30) | NO   |     | NULL    |                |
| phone        | varchar(11) | NO   |     | NULL    |                |
| created_time | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

添加索引

语句

-- index_col_name:设置的字段
-- 创建普通索引
ADD INDEX [INDEX_NAME](index_col_name,...)
-- 创建主键
ADD PRIMARY KEY(index_col_name)
-- 创建唯一索引
ADD UNIQUE [index_name] (index_col_name)

示例

-- 为user字段创建普通索引,索引名为user_index
MySQL [angel]> alter table angel_users add index user_index(user);
-- 为phone创建唯一索引
MySQL [angel]> alter table angel_users add unique unique_phone(phone);
-- 验证结果
MySQL [angel]> describe angel_users;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| user         | varchar(30) | NO   | MUL | NULL    |                |
| phone        | varchar(11) | NO   | UNI | NULL    |                |
| created_time | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

删除索引

语句

-- 删除主键索引(在不是自增情况下可直接使用)
DROP PRIMARY KEY
-- 删除(普通/唯一)索引
DROP INDEX index_name

示例

-- 删除主键索引,先重定义字段取消自增再删除
MySQL [angel]> alter table angel_users modify id int;
MySQL [angel]> alter table angel_users drop primary key;
-- 删除普通索引
MySQL [angel]> alter table angel_users drop index user_index;
-- 删除唯一索引
MySQL [angel]> alter table angel_users drop index unique_phone;
-- 验证
MySQL [angel]> describe angel_users;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | NO   |     | NULL    |       |
| user         | varchar(30) | NO   |     | NULL    |       |
| phone        | varchar(11) | NO   |     | NULL    |       |
| created_time | datetime    | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

修改默认值

语句

ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

示例

-- (添加|修改)默认值
MySQL [angel]> alter table angel_users alter user set default 'QvBiLam';
-- 删除默认值
MySQL [angel]> alter table angel_users alter phone drop default;
-- 验证结果
MySQL [angel]> describe angel_users;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | NO   |     | NULL    |       |
| user         | varchar(30) | NO   |     | QvBiLam |       |
| phone        | varchar(11) | NO   |     | NULL    |       |
| created_time | datetime    | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

修改字段

语句

CHANGE [COLUMN] old_col_name creaet_definition

示例

-- 将user varchar(30)字段修改成name varchar(32)
MySQL [angel]> alter table angel_users change user name varchar(32);
-- 验证结果
MySQL [angel]> describe angel_users;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | NO   |     | NULL    |       |
| name         | varchar(32) | YES  |     | NULL    |       |
| phone        | varchar(11) | NO   |     | NULL    |       |
| created_time | datetime    | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

重定义字段

语句

MODIFY [COLUMN] create_definition

示例

-- 修改子句
MySQL [angel]> alter table angel_users modify name varchar(24) not null default 'Miss you';
-- 验证结果
MySQL [angel]> describe angel_users;
+--------------+-------------+------+-----+----------+-------+
| Field        | Type        | Null | Key | Default  | Extra |
+--------------+-------------+------+-----+----------+-------+
| id           | int(11)     | NO   |     | NULL     |       |
| name         | varchar(24) | NO   |     | Miss you |       |
| phone        | varchar(11) | NO   |     | NULL     |       |
| created_time | datetime    | YES  |     | NULL     |       |
+--------------+-------------+------+-----+----------+-------+

更改表名

语句

RENAME [AS] new_tbl_name

示例

-- 修改angel_users表名为angel_name
MySQL [angel]> alter table angel_users rename angel_name;
-- 验证结果
MySQL [angel]> show tables;
+-----------------+
| Tables_in_angel |
+-----------------+
| angel_name      |
| test            |
+-----------------+

复制表

语句

-- 只复制表结构
CREATE TABLE [IF NOT EXISTS] 表名 LIKE 表名
-- 复制表结构和数据
CREATE TABLE 新表名 AS SELECT * FROM 源表名

示例

-- 创建test1结构为angel_name表的结构
MySQL [angel]> create table test1 like angel_name;
-- 验证表结构
MySQL [angel]> describe test1;
+--------------+-------------+------+-----+----------+-------+
| Field        | Type        | Null | Key | Default  | Extra |
+--------------+-------------+------+-----+----------+-------+
| id           | int(11)     | NO   |     | NULL     |       |
| name         | varchar(24) | NO   |     | Miss you |       |
| phone        | varchar(11) | NO   |     | NULL     |       |
| created_time | datetime    | YES  |     | NULL     |       |
+--------------+-------------+------+-----+----------+-------+
-- 验证数据
MySQL [angel]> select * from test1;
Empty set (0.00 sec)

-- 创建test2复制angel_name表结构和数据
MySQL [angel]> create table test2 as select * from angel_name;
-- 验证表结构
MySQL [angel]> describe test2;
+--------------+-------------+------+-----+----------+-------+
| Field        | Type        | Null | Key | Default  | Extra |
+--------------+-------------+------+-----+----------+-------+
| id           | int(11)     | NO   |     | NULL     |       |
| name         | varchar(24) | NO   |     | Miss you |       |
| phone        | varchar(11) | NO   |     | NULL     |       |
| created_time | datetime    | YES  |     | NULL     |       |
+--------------+-------------+------+-----+----------+-------+
4 rows in set (0.00 sec)
-- 验证数据
MySQL [angel]> select * from test2;
+----+---------+-------------+--------------+
| id | name    | phone       | created_time |
+----+---------+-------------+--------------+
|  1 | QvBiLam | 12312341234 | NULL         |
|  2 | sbzcx   | 12312344321 | NULL         |
+----+---------+-------------+--------------+

删除表

语句

DROP TABLE [IF EXISTS] 表名;

示例

-- 删除test1,test2表
MySQL [angel]> drop table test1,test;
-- 验证结果
MySQL [angel]> show tables;
+-----------------+
| Tables_in_angel |
+-----------------+
| angel_name      |
+-----------------+
1 row in set (0.00 sec)

增删改查

插入数据

-- values插入
insert into angel_name (id,name,phone) values (3,'qvbilam',''),(4,'zcxSB','');
-- set插入
insert into angel_name set id=5,name='xx',phone='666';
-- select插入
insert into angel_name (id,name,phone) select id,name,phone from angel_name where id < 2;

修改数据

参数	说明
LOW_PRIORITY:	可选项,表示在多用户访问数据的情况下可用于延迟update操作,直到没有用户读取数据库为止,仅适用于表级所的存储引擎(如 IyISAM、MEMORY、MERGE).
IGNORE	通过IGNORE语句更新表中多行数据时,如果出现错误,那么整个update语句都会被取消
ORDER BY	用于限定表中的被修改的次序
LIMIT	限定被修改的行数

-- 语句
update [LOW_PRIORITY] [IGNORE] table_name
set feild = value [feild2 = value]
[WHERE] [ORDER BY] [LIMIT]
-- 示例
update ignore angel_name set name='QvBiLam' where id < 5 order by id desc limit 5;

删除数据

-- delete删除(类似修改)
delete from angel_name where id < 5 order by id desc limit 1;
-- truncate table_name 删除
truncate angel_name;

操作数据表

创建数据表

查看表结构

修改表结构

添加字段

删除字段

添加索引

删除索引

修改默认值

修改字段

重定义字段

更改表名

复制表

删除表

增删改查

插入数据

修改数据

删除数据