mysql数据库表的基本命令

2018年5月13日 1,828次阅读 1人点赞

mysql数据库中以表来存储数据，一般而言，一张表由多个字段组成，其中有一个或多个字段组成主键（主键就是一张表的中一条数据的身份证号码，拥有唯一性，用来识别数据）。

创建表

创建表的语法如下：

CREATE TABLE 表名(字段名 数据类型 [约束条件] ，字段名 数据类型 [约束条件] ，字段名 数据类型 [约束条件]);

mysql常用数据类型有

数值类型：TINYINT, SMALLINT, MEDIUMINT, INT/INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(M,N)取值范围在M和N之 间

字符类型(有BLOB的都是二进制形式文本数据)：CHAR, VARCHAR, TINYBLOB, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT

日期和时间类型：DATE(YYYY-MM-DD), TIME(HH-mm-ss), DATETIME(YYYY-MM-DD HH-mm-ss), YEAR(YYYY), DATESTAMP(YYYYMMDDHHmmss)

常用约束条件有

条件 解释
PRIMARY KEY（主键） 可以设置复合主键，主键必须唯一
NOT NULL（非空） 字段设置非空约束，必须给值，可以是NULL字
AUTO_INCREMENT(自增长) 自增长初始值默认从1开始，可以通过AUTO_INCREMENT=n设置初始值为n
DEFAULT（默认值） 如果被设置有默认值的字段没有赋值，则会自动给默认值
UNIQUE KEY（唯一约束，唯一字段可以设置多个） 设置唯一约束的字段，值不可以重复

其他参数（可以放在创建表语句后，以，隔开）

参数 解释
AUTO_INCRMENT=100 自动增长的初始值
CHARSET=UTF8 设置字符编码
ENGINE=INNODB 存储引擎,默认INNODB

建表示例

  • 示例1：
select database();  查看当前选中的数据库
create table if not exists student(
     id int auto_increment,
     name varchar(20) not null,
     sex char(2) default '男',
     primary key(id))auto_increment=100,charset=utf8,engine=innodb;

其中primary key(字段1，字段2)可以是联合主键，如果只有一个字段的主键时，直接在定义字段时使用即可
id int primary key auto_increment,

  • 复制表结构建表
create table student like student_t;
show tables;

+---------------------+
| Tables_in_basictest |
+---------------------+
| student             |
| student_t           |
+---------------------+
  • 复制数据：
insert into student select * from student_t;

查询表

  • 查看当前数据库
select database();
  • 查看当前数据库中所有表
show tables;
  • 查看表结构
desc 表名
  • 查询表中列的注释信息
select * from information_schema.columns
where table_schema = 'db' #表所在数据库
and table_name = 'tablename' ;#你要查的表
  • 只查询列名和注释
select column_name, column_comment from information_schema.columns where table_schema ='db' and table_name = 'tablename' ;

alter table student modify names varchar(20) comment '学生名字';

select column_name, column_comment from information_schema.columns where table_schema ='basictest' and table_name = 'student' ;

先给学生名字添加注释，然后查询

+-------------+----------------+
| column_name | column_comment |
+-------------+----------------+
| id          |                |
| names       | 学生名字       |
| sex         |                |
+-------------+----------------+

在创建表时最好给字段添加注释，以便理解

  • 查看表生成的DDL
show create table student_s;

CREATE TABLE `student_t` (
  `id` int(11) NOT NULL auto_increment,
  `names` varchar(20) default NULL,
  `sex` char(2) default '女',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

修改表

语法

ALTER TABLE 表名 ADD|DROP|MODIFY|CHANGE|ALTER|RENAME
  • ADD 增加字段
ALTER TABLE student ADD class_id INT;
desc student; 查看表结构

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20) | NO   |     | NULL    |                |
| sex      | char(2)     | YES  |     | 男      |                |
| class_id | int(11)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
  • DROP 删除字段
ALTER TABLE student DROP class_id;
desc student;

结果

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| sex   | char(2)     | YES  |     | 男      |                |
+-------+-------------+------+-----+---------+----------------+
  • MODIFY和CHANGE 修改字段
    MODIFY和CHANGE都可以修改字段，其区别就在于：

CHANGE必须指定新的字段名而MODIFY则不需要（如果需要修改字段名只能用CHANGE，否则用哪个都可以）
示例1：

ALTER TABLE student MODIFY name  VARCHAR(10);
desc student;

结果：

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| sex   | char(2)     | YES  |     | 男      |                |
+-------+-------------+------+-----+---------+----------------+

示例2：

ALTER TABLE student change name names  VARCHAR(20);
desc student;

“`+——-+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| names | varchar(20) | YES | | NULL | |
| sex | char(2) | YES | | 男 | |
+——-+————-+——+—–+———+—————-+

<pre data-language=><code class=" line-numbers">- ALTER（设置默认值）
</code></pre>

ALTER TABLE student ALTER sex SET DEFAULT '女';
desc student;
“`

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| names | varchar(20) | YES  |     | NULL    |                |
| sex   | char(2)     | YES  |     | 女      |                |
+-------+-------------+------+-----+---------+----------------+

为什么有了MODIFY和CHANGE还要来个ALTER呢？这是因为另外两个在修改的时候会把字段之前旧的属性全部覆盖掉

例如：现在需要修改sex DEFAULT的值，我们需要这么写

ALTER TABLE student MODIFY sex VARCHAR(20) DEFAULT '男';

而采用ALTER则不需要则可以只需要设置默认值,如果一个字段的附加属性很多就会得不偿失

  • RENAME（重命名数据表）
ALTER TABLE student RENAME TO student_t;
show tables;

+---------------------+
| Tables_in_basictest |
+---------------------+
| student_t           |
+---------------------+

如上，basictest数据库中只有student_t一张表，说明已将表student重命名。

  • 修改存储引擎
ALTER TABLE student_t ENGINE=myisam;

删除表

DROP TABLE 表名;
作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议进行许可
点赞
标签：

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注