MySQL笔记2_数据表

字段数据类型(3类：数值、日期/时间和字符串)：

1、数值类型

MySql支持所有标准的SQ数值数据类型。

关键字int是integer的同义词，关键字dec是decimal的同义词

bit数据类型保存字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表

类型	大小(字节)	范围（有符号）	范围（无符号）	用途
tinyint	1	(-128, 127)	(0, 255)	小整数值
smallint	2	(-32768,32767)	(0, 65535)	大整数值
mediumint	3	(-8388608, 8388607)	(0, 16777215)	大整数值
int或integer	4	(-2147483648, 2147483647)	(0, 4294967295)	大整数值
bigint	8	(-9223372036854775808, 9223372036854775807)	(0，18 446 744 073 709 551 615)	极大整数值
float	4字节	……	……	单精度浮点数值
double	8字节	……	……	双精度浮点数值
decimal	对decimal(M,D)如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

2、日期和时间类型

表示时间值的日期和事件类型为datatime、date、timestamp、time和year

每一个时间类型有一个有效范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

timestamp类型有专有的自动更新的特性

类型	大小(字节)	范围	格式	用途
date	3	1000-01-01~ 9999-12-31	YYYY-MM-DD	日期值
time	3	'-838:59:59'~ '838:59:59'	HH:MM:SS	时间值或持续的时间
year	1	1901~2155	YYYY	年份值
datetime	8	1000-01-01 00:00:00~ 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	4	1970-01-01 00:00:00~2038 结束时间是第2147483647秒，北京时间2038-1-19 11:14:07，格林威治时间2039年1月19日凌晨03:14:07	YYYYMMDD HHMMSS	混合时间和时间值，时间戳

3、字符串类型

char和varchar类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

binary和varbinary类似于char和varchar，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字节字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值

blob是一个二进制大对象，可以容纳可变数量的数据。有4种bolb类型：tinyblob、blob、medumblob和longbolb。它们区别在于可容纳存储范围不同。

有4中text类型：tinytext、text、mediumtext和longtext。对应着4中blob类型，可存储的最大长度不同，可根据实际情况选择

类型	大小（字节）	用途
char	0~255	定长字符串
varchar	0~65535	定长字符串
tinyblob	0~255	不超过200个字节的二进制字符串
tinytext	0~255	短文本字符串
blob	0~65535	二进制形式的文本字符数据
text	0~65535	长文本数据
mediumblob	0~16777215	二进制形式的中等长度文本数据
mediumtext	0~16777215	中等长度文本数据
longblob	0~4294967295	二进制形式的极大文本数据
longtext	0~4294967295	极大文本数据

1、创建数据表

create table <数据表的名称> ([表定义选项]……)[表选项][分区选项]
其中[表定义选项]的格式为：
<列名1> <数据类型1> ....
mysql> create table employee(
    -> _id int,
    -> name varchar(20),
    -> age int,
    -> salary double,
    -> sex varchar(5),
    -> department varchar(25)
    -> );
Query OK, 0 rows affected (0.04 sec)

2、查看所有数据表

show tables;


mysql> show tables;
+-------------------------+
| Tables_in_personal_info |
+-------------------------+
| employee                |
+-------------------------+
1 row in set (0.00 sec)

3、查看数据表的编码(charset:字符集/编码集)

show create table <数据表名>


mysql> show create table employee;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                           |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
  `_id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `salary` double DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `department` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4、修改数据表的编码

alter table <数据表名> character set 编码格式


mysql> alter table employee character set gbk;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

5、查看数据表的结构【desc（description：描述，形容;类型）】

Field：字段 Null：是否可以为空 Key：约束 Defaul：默认值 Extra：扩展，备注的意思

desc <数据表的名称>


mysql> desc employee;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| _id        | int(11)     | YES  |     | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| age        | int(11)     | YES  |     | NULL    |       |
| salary     | double      | YES  |     | NULL    |       |
| sex        | varchar(5)  | YES  |     | NULL    |       |
| department | varchar(25) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

6、修改表的结构

1、修改表名

alter table <旧表名> rename <新表名>


mysql> alter table employee rename info_employee;
Query OK, 0 rows affected (0.01 sec)

2、修改字段的名字

alter table <表名> change <旧字段名> <新字段名> <类型>(长度);
 
mysql> alter table employee change name username varchar(40);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

3、增加字段

alter table <数据表名> add column <字段名> <字段类型> [约束条件] [first|after 已存在的字段名]
first:（可选）作用是将新添加的字段设置为表的第一个字段
alter:（可选）作用是将新添加的字段添加到指定的已存在的字段名的后面
实例：使用alter table 修改表info_employee的结构，
在表的第一列添加一个int类型的字段coll，输入的SQL语句和运结果如下所示：


mysql> alter table info_employee
    -> add column coll int first;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table info_employee
    -> add column name varchar(32) after _id;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

4、删除列(字段)

alter table <数据表名称> drop <字段名称>


mysql> alter table employee drop address;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

”First或after已存在的字段名“用于指定新增字段在表中的位置，如果SQL语句中没有这两个参数，则默认将新添加的字段添加到最后

5、修改字段的长度/类型

alter table <数据表的名称> modify column <字段名称> <新的类型(新的长度)>


mysql> alter table employee modify column department varchar(200);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0