数据表的基本操作

表的创建基本介绍

基本语法

• field 表示 列的名称，自定义如 name，age等
• datetype 表的列的类型，这里的数据类型指的是mysql数据类型，必须指定
• character set 字符集（也可以charset=utf8）， 如果你没有指定字符集，则以所在数据库的字符集为准。
• collate 校验规则, 如果你没有指定校验规则，则以所在数据库的校验规则为准
• engine 表示表的存储引擎，分为 InnoDB 和 MyISAM，默认是InnoDB，可在my.ini中更改

//filed：指定列名   datatype：指定列类型 (字段类型)
create table table_name(
	field1 datatype,
	field2 datatype,
	field3 datatype
) character set 字符集 collate 校验规则 engine 存储方式

代码示例

• 每个字段必须有数据类型
• comment ‘xxx’ 是注释

//创建表user 存储方式为MyISAM
mysql> create table user(
    -> id int,
    -> name varchar(60),
    -> password char(32) comment '密码是32位的md5值',
    -> birthday date comment '生日'
    -> )character set utf8 engine MyISAM;
Query OK, 0 rows affected (0.03 sec)

//创建表user1 存储方式为InnoDB
mysql> create table user1(
    -> id int,
    -> name varchar(60),
    -> password char(32) comment '密码是32位的md5值',
    -> birthday date comment '生日'
    ->
    -> )character set utf8 engine InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| user            |
| user1           |
+-----------------+
2 rows in set (0.00 sec)

文件结构

类型及约束

常见数据类型

详见 mysql的数据类型

常见约束条件

• auto_increment定义列为自增的属性，一般用于主键，数值会自动加1
• not null 在操作数据库时如果输入该字段的数据为NULL ，就会报错
• primary key 用于定义列为主键
• default 默认值，插入数据时如果不写会使用默认值
• unsigned 只能添加正数，当不设置unsigned时，我们可以添加负数进去
• comment ‘xxx’ 是注释，方便别的程序员了解字段含义，show create table 数据库名 可查看

create table students(
        id int unsigned primary key auto_increment not null comment 'id号',
        name varchar(20) not null comment '姓名',
        age int unsigned not null default 18 comment '年龄',
        height decimal(3,2) not null comment '身高'
)charset utf8;

查看表信息

//test表结构信息
mysql> desc test;
+------------+-----------------+------+-----+---------+-------+
| Field      | Type            | Null | Key | Default | Extra |
+------------+-----------------+------+-----+---------+-------+
| id         | int(11)         | NO   |     | 0       |       |
| name       | varchar(60)     | NO   |     |         |       |
| sex        | enum('男','女') | NO   |     | NULL    |       |
| birthday   | date            | NO   |     | NULL    |       |
| entry_date | date            | NO   |     | NULL    |       |
| job        | varchar(60)     | NO   |     |         |       |
| salary     | float           | NO   |     | 0       |       |
| resume     | text            | NO   |     | NULL    |       |
+------------+-----------------+------+-----+---------+-------+
8 rows in set (0.02 sec)

//test表的其他信息
mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.27, for Win32 (x86)

Connection id:          6
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.5.27 MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 12 days 1 hour 3 min 1 sec

Threads: 1  Questions: 55  Slow queries: 0  Opens: 40  Flush tables: 1  Open tables: 1  Queries per second avg: 0.000
--------------


//test表的创建信息
mysql> show create table test;
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL DEFAULT '0' COMMENT 'id号',
  `name` varchar(60) NOT NULL DEFAULT '' COMMENT '用户名',
  `sex` enum('男','女') NOT NULL COMMENT '性别',
  `birthday` date NOT NULL COMMENT '生日',
  `entry_date` date NOT NULL COMMENT '入职时间',
  `job` varchar(60) NOT NULL DEFAULT '' COMMENT '工作',
  `salary` float NOT NULL DEFAULT '0' COMMENT '薪水',
  `resume` text NOT NULL COMMENT '个人介绍'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)

编码问题

查看数据库编码格式

mysql> show variables like 'character%';
+--------------------------+-------------------------------------+
| Variable_name            | Value                               |
+--------------------------+-------------------------------------+
| character_set_client     | latin1                              |
| character_set_connection | latin1                              |
| character_set_database   | utf8                                |
| character_set_filesystem | binary                              |
| character_set_results    | latin1                              |
| character_set_server     | utf8                                |
| character_set_system     | utf8                                |
| character_sets_dir       | F:\wamp\mysql5.5.27\share\charsets\ |
+--------------------------+-------------------------------------+
8 rows in set (0.00 sec)

查看数据库表的编码

mysql> show create table user1;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| user1 | CREATE TABLE `user1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

一般用 set names gbk 解决

//黑框只认识gbk码 具体原因不懂
set names gbk;

//相当于以下代码
set character_set_client=gbk;
set character_set_results=gbk;
set character_set_connection=gbk;

表结构修改

概述

在项目实际开发中 ，我们会经常的修改某个表的结构，比如字段名称，字段的大小， 字段的类型，表的字符集类型，表的存储引擎等等. 我们还有需要是添加字段，删除字段等等 这时我们就需要使用到修改表的技术

快速入门案例

• 在员工表test上增加一个image列（resume之后）
• 修改job列，使其长度为50
• 删除sex列
• 表名改为user
• 修改表的字符集为utf8
• 列明name修改为username

创建一个表

//创建test表
mysql> create table test(
    -> id int not null default 0 comment 'id号',
    -> name varchar(60) not null default '' comment '用户名',
    -> sex enum('男','女') not null comment '性别',
    -> birthday date not null comment '生日',
    -> entry_date date not null comment '入职时间',
    -> job varchar(60) not null default '' comment '工作',
    -> salary float not null default 0.0 comment '薪水',
    -> resume text not null comment '个人介绍'
    -> )charset=utf8 engine=myisam;
Query OK, 0 rows affected (0.06 sec)


//插入两条数据
mysql> insert into test values(1, '赵信', 1, '2010-11-11', '2018-12-12', '管家', 8000.55, '一点寒芒先到，随后枪出如龙');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(2, '阿卡丽', 2, '2013-03-03', '2019-09-09', '刺客', 5555.55, '我随影而来，随影而去');
Query OK, 1 row affected (0.00 sec)

//查询数据
mysql> select * from test;
+----+--------+-----+------------+------------+------+---------+----------------------------+
| id | name   | sex | birthday   | entry_date | job  | salary  | resume                     |
+----+--------+-----+------------+------------+------+---------+----------------------------+
|  1 | 赵信       | 男    | 2010-11-11 | 2018-12-12 | 管家    | 8000.55 | 一点寒芒先到，随后枪出如龙                     |
|  2 | 阿卡丽       | 女   | 2013-03-03 | 2019-09-09 | 刺客    | 5555.55 | 我随影而来，随影而去                        |
+----+--------+-----+------------+------------+------+---------+----------------------------+
2 rows in set (0.00 sec)

添加字段 add

//alter table 表名 add 新字段名及类型约束 after 字段
//新增列（字段）  在员工表test上增加一个image列,  after resume表示在resume后添加，不写则默认加在最后
mysql> alter table test add image varchar(125) not null default '' comment '图片路径' after resume;;
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+--------+-----+------------+------------+------+---------+----------------------------+-------+
| id | name   | sex | birthday   | entry_date | job  | salary  | resume                     | image |
+----+--------+-----+------------+------------+------+---------+----------------------------+-------+
|  1 | 赵信   | 男  | 2010-11-11 | 2018-12-12 | 管家 | 8000.55 | 一点寒芒先到，随后枪出如龙 |       |
|  2 | 阿卡丽 | 女  | 2013-03-03 | 2019-09-09 | 刺客 | 5555.55 | 我随影而来，随影而去       |       |
+----+--------+-----+------------+------------+------+---------+----------------------------+-------+
2 rows in set (0.00 sec)

修改字段 modify

//alter table 表名 modify 字段名及类型约束
//modify后的字段名必须是已存在的字段
mysql> alter table test modify job varchar(50) not null comment '工作';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+------------+-----------------+------+-----+---------+-------+
| Field      | Type            | Null | Key | Default | Extra |
+------------+-----------------+------+-----+---------+-------+
| id         | int(11)         | NO   |     | 0       |       |
| name       | varchar(60)     | NO   |     |         |       |
| sex        | enum('男','女') | NO   |     | NULL    |       |
| birthday   | date            | NO   |     | NULL    |       |
| entry_date | date            | NO   |     | NULL    |       |
| job        | varchar(50)     | NO   |     | NULL    |       |
| salary     | float           | NO   |     | 0       |       |
| resume     | text            | NO   |     | NULL    |       |
| image      | varchar(125)    | NO   |     |         |       |
+------------+-----------------+------+-----+---------+-------+
9 rows in set (0.02 sec)

修改字段名 change

//alter table 表名 change 旧字段名 新字段名及类型约束
//change的功能可以代替modify，change新旧字段名一样时相当于modify
mysql> alter table users change name user_name varchar(60) not null default '' comment '用户名';
Query OK, 2 rows affected (0.17 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from users;
+----+-----------+------------+------------+------+---------+----------------------------+-------+
| id | user_name | birthday   | entry_date | job  | salary  | resume                     | image |
+----+-----------+------------+------------+------+---------+----------------------------+-------+
|  1 | 赵信      | 2010-11-11 | 2018-12-12 | 管家 | 8000.55 | 一点寒芒先到，随后枪出如龙 |       |
|  2 | 阿卡丽    | 2013-03-03 | 2019-09-09 | 刺客 | 5555.55 | 我随影而来，随影而去       |       |
+----+-----------+------------+------------+------+---------+----------------------------+-------+
2 rows in set (0.00 sec)

mysql> desc users;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | NO   |     | 0       |       |
| user_name  | varchar(60)  | NO   |     |         |       |
| birthday   | date         | NO   |     | NULL    |       |
| entry_date | date         | NO   |     | NULL    |       |
| job        | varchar(50)  | NO   |     | NULL    |       |
| salary     | float        | NO   |     | 0       |       |
| resume     | text         | NO   |     | NULL    |       |
| image      | varchar(125) | NO   |     |         |       |
+------------+--------------+------+-----+---------+-------+
8 rows in set (0.02 sec)

删除字段 drop

//alter table 表名 drop 字段名
mysql> alter table test drop sex;
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+--------+------------+------------+------+---------+----------------------------+-------+
| id | name   | birthday   | entry_date | job  | salary  | resume                     | image |
+----+--------+------------+------------+------+---------+----------------------------+-------+
|  1 | 赵信   | 2010-11-11 | 2018-12-12 | 管家 | 8000.55 | 一点寒芒先到，随后枪出如龙 |       |
|  2 | 阿卡丽 | 2013-03-03 | 2019-09-09 | 刺客 | 5555.55 | 我随影而来，随影而去       |       |
+----+--------+------------+------------+------+---------+----------------------------+-------+
2 rows in set (0.00 sec)

mysql> desc test;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | NO   |     | 0       |       |
| name       | varchar(60)  | NO   |     |         |       |
| birthday   | date         | NO   |     | NULL    |       |
| entry_date | date         | NO   |     | NULL    |       |
| job        | varchar(50)  | NO   |     | NULL    |       |
| salary     | float        | NO   |     | 0       |       |
| resume     | text         | NO   |     | NULL    |       |
| image      | varchar(125) | NO   |     |         |       |
+------------+--------------+------+-----+---------+-------+
8 rows in set (0.02 sec)

修改表名 rename

//alter table 旧表名 rename to 新表名
mysql> alter table test rename to users;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
+----+--------+------------+------------+------+---------+----------------------------+-------+
| id | name   | birthday   | entry_date | job  | salary  | resume                     | image |
+----+--------+------------+------------+------+---------+----------------------------+-------+
|  1 | 赵信   | 2010-11-11 | 2018-12-12 | 管家 | 8000.55 | 一点寒芒先到，随后枪出如龙 |       |
|  2 | 阿卡丽 | 2013-03-03 | 2019-09-09 | 刺客 | 5555.55 | 我随影而来，随影而去       |       |
+----+--------+------------+------------+------+---------+----------------------------+-------+
2 rows in set (0.00 sec)

mysql> desc users;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | NO   |     | 0       |       |
| name       | varchar(60)  | NO   |     |         |       |
| birthday   | date         | NO   |     | NULL    |       |
| entry_date | date         | NO   |     | NULL    |       |
| job        | varchar(50)  | NO   |     | NULL    |       |
| salary     | float        | NO   |     | 0       |       |
| resume     | text         | NO   |     | NULL    |       |
| image      | varchar(125) | NO   |     |         |       |
+------------+--------------+------+-----+---------+-------+
8 rows in set (0.02 sec)

修改表的存储引擎，字符集

mysql> alter table users charset=utf8;
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table users engine=InnoDB;
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0

总结

// 数据表结构修改 非表内数据

-- 查看表结构
desc 表名

-- 修改表-添加字段
-- alter table 表名 add 列名 类型;
alter table students add birthday datetime;

-- 修改表-修改字段：不重命名版
-- alter table 表名 modify 列名 类型及约束;
alter table students modify birthday date;

-- 修改表-修改字段：重命名版
-- alter table 表名 change 原名 新名 类型及约束;
alter table students change birthday birth date default "2000-01-01";

-- 修改表-删除字段
-- alter table 表名 drop 列名;
alter table students drop high;

-- 删除表
-- drop database 数据库;
-- drop table 数据表;
-- drop table xxxxx;