数据表操作

从最简单的表操作开始

创建班级表:

ID 名称 班主任

101 六年级一班 马老师

102 六年级二班 潘老师

mysql> create database school;   #创建数据库school

mysql> use school;   #选择数据库school 
mysql> create table class(class_id int, class_name varchar(128), class_teacher varchar(64));         #创建表class
mysql> insert into class values (101, '六年级一班', '马老师');  #往表中插入101班记录
mysql>insert into class values (102, '六年级二班', '潘老师');   #往表中插入102班记录
mysql>select * from class;  #查询表class 中的全部记录
mysql>select * from class where class_id=101;  #查询表class 中的全部记录
mysql>select class_id,class_name from class; #只选择班级id和姓名展示

表的创建

基本语法:
CREATE TABLE <表名> ([表定义选项][表选项][分区选项])

默认情况下,表是创建在当前数据库中的,若表已存在,没有当前数据库或者数据库不存在,则会出现错误

-CREATE TABLE:用于创建给定名称的表,必须要有CREATE权限

<表定义选项>:表创建定义,由列名,列的定义(类型定义),以及可能的空值说明,完整性约束或表索引组成

mysql> use school  #选择数据库school
mysql> create table class4(class_id int PRIMARY KEY,class_name varchar(128)NOT NULL,class_teacher varchar(64) UNIQUE);#创建表class3

列的类型定义

整型类型:

整数类型的附带属性:
1) 类型名称后面可以带小括号
指定显示宽度(并不占用该类型的字节数),如果不设置,显示宽度则默认为tinyint(3),smallint(5),mediumint(8),int(11)和bigint(20). 一般配合zerofill 使用,(就是不够的用0填充);

mysql> use school;
mysql> create table class(id int(5) zerofill,name varchar(128),teacher varchar(64));

varchar(128) 类似一个会根据输入的内容自动增加内存的数组,最大增加到128

2)UNSIGNED(无符号)

如果需要在字段里面保存非负数或者需要较大的上限值时，可以用此选项，它的取值范围是正常值的下限取 0，上限取原值的 2 倍，例如，tinyint 有符号范围是-128～+127，而无符号范围是 0～255。如果一个列指定为 zerofill，则 MySQL 自动为该列添加 UNSIGNED 属性。

mysql> use school;   #选择数据库school 
mysql> create table class6(id integer unsigned , name varchar(128), teacher varchar(64) );         #创建表class6 
mysql> create table class7(id integer zerofill , name varchar(128), teacher varchar(64) );         #创建表class7, id类型为 int unsigned

3)AUTO_INCREMENT

在需要产生唯一标识符或顺序值时，可利用此属性，这个属性只用于整数类型。AUTO_INCREMENT 值一般从 1 开始，每行增加 1。 一个表中最多只能有一个 AUTO_INCREMENT列 。对于任何想要使用 AUTO_INCREMENT 的列，应该定义为 NOT NULL，并定义为 PRIMARY KEY 或定义为 UNIQUE 键。 例如，可按下列任何一种方式定义 AUTO_INCREMENT 列：

mysql> use school;   #选择数据库school 
mysql> create table class8(id integer auto_increment PRIMARY KEY , name varchar(128), teacher varchar(64) );         #创建表class8， id 具有自增长属性
mysql> create table class9(id integer auto_increment UNIQUE , name varchar(128), teacher varchar(64) );         #创建表class9， id 具有自增长属性

也就是加了上面那俩个就可以自增长,上一个数 ++

浮点型和定点数型

float和double类型都不必说了,小数点后面的范围不同,定点数型就是多少位都可以精确到

decimal就是定点数型

mysql> use school;   #选择数据库school 
mysql> create table class10 (f1 float, do1 double, de1 decimal);  #创建表class10

MySQL 的浮点型和定点型后面可以加(M,D),M表示该值的总长度,D表示小数点后面的长度(一般来说浮点型不需要加)

• MySQL 浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度，如float(7,4)的 可显示为-999.9999，MySQL保存值时进行四舍五入，如果插入999.00009，则结果为999.0001。对于decimal,M是最大位数（精度），范围是1到65。可不指定，默认值是10。D是小数点右边的位数（小数位）。范围是0到30，并且不能大于M，可不指定，默认值是0。
• FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0,即(10, 0)。
• 忠告: 当我们需要存储小数，并且有精度要求，比如存储金额时，通常会考虑使用DECIMAL字段类型!!!

日期和时间类型

YEAR 表示 年,DATE表示日期(年月日),TIME表示时间(时分秒),DATETIME表示日期+时间(年月日时分秒),TIMESTAMP和前者一样,但是取值范围,字节数不同

每种类型都有取值范围,如果超过了这个范围,系统就会报错,插入零值

插入日期时,日期年月日和时间时分秒可以使用 “: - _ / ”中的任意字符来分隔,如果单独插入时间，则会报错！！！

日期与时间之间要空格

mysql> use test;   #选择数据库test 
mysql> create table date_example (e_date date, e_datetime datetime, e_timestamp
 timestamp, e_time time, e_year year);  #创建表dt_example
mysql> insert into date_example values('2020-5-9', '2020-5-9 15:01:00', '2020-05-09 15:01:00', '15:56:01', 2011);
mysql> insert into date_example values('2020_5/9', '2020:5-9 15/01-00', '2020:05/09 15-01_00',  '15:56:01', '2011');   #效果同上面插入语句

同时也可以通过MySQL的本身提供的时间函数获取时间
常用函数:
CURDATE() - 获得当前的DATE, 可直接插入DATE 类型中.
NOW() - 获得当前的DATETIME, 可直接插入DATETIME 和TIMESTAMP类型中.
TIME() - 获取参数给定时间串中的时分秒，可直接插入TIME 类型中.
YEAR() - 获取参数给定时间串中的年份，可直接插入YEAR类型中.
MONTH() 、DAY()、HOUR()、MINUTE()、SECOND() 获取参数给定时间串中的月、日、时、分、秒值.

一些函数需要给定参数才能使用,可以 YEAR(NOW()) 这样使用

mysql> use test;   #选择数据库test 
mysql> insert into date_example values(CURDATE(), NOW(), NOW(), time(NOW()), YEAR(NOW()) );

char和varchar型

char类型的长度时固定的,在创建表时就指定了。其长度可以是0~255的任意值。例如，CHAR(100)就是指定CHAR类型的长度为100。CHAR 存储值时，它们会用空格右填充到指定的长度。

varchar类型的长度时不固定的,根据输入的内容来定,但是,它的最大长度是在定义的时候就确定好的,定义时，其最大值可以取0~65535之间的任意值。

例如，VARCHAR(100)的最大长度是100，但是不是每条记录都要占用100个字节，而是在这个最大值范围内使用多少就分配多少。VARCHAR类型实际占用的空间为字符串的实际长度加1或2，这样即可有效节约系统的空间。

mysql> use test;   #选择数据库test 
mysql> create table char_example(e_char char(5), v_char varchar(5)); #创建数据库表
mysql> insert into char_example values('12345','12345');  #正常插入数据
mysql> insert into char_example values('1 2  ','1 2  ');   #char类型会屏蔽后面隐藏的空格，varchar 不会
mysql>select concat('(',e_char, ')'), concat('(',v_char, ')')  from char_example ; #让char 后面屏蔽的空格原型毕露 

总结

• 1、 char一定会使用指定的空间，varchar是根据数据来定空间
• 2、 char的数据查询效率比varchar高：varchar是需要通过后面的记录数来计算
• 3、 如果确定数据一定是占指定长度，那么使用char类型；
• 4、 如果不确定数据到底有多少，那么使用varchar类型；
• 5、 如果数据长度超过255个字符而在65535之内，直接使用varchar
• 6、 如果字符串尾部要保留空格，必须选择varchar

TEXT类型

TEXT 类型是一种特殊的字符串类型,包括 TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT

各种TEXT类型的区别在于允许的长度和存储空间不同。因此，在这几种TEXT类型中，根据需求选取既能满足需要又节省空间的类型即可。

特别注意:
1,以上类型无需加指定长度(text(10))

2,以上表示的是字节数,而不是实际的字符个数.比如一个中文字符占3个字节,那么TEXT类型的存储 65535/3 个中文字符,而 varchar(100) 可以存储100个中文字符,实际占300个字节,但varchar(65535)并不能存储65535个中文字符,因为已超出表达范围.

mysql> use test;   #选择数据库test 
mysql> create table text_example(e_text tinytext, v_char varchar(255)); #创建数据库表，e_text 可存储255个字节，v_char可存储255个字符   

mysql> insert into char_example values(90个中文字符,90个中文字符);  #插入失败，utfmb4 用3个字节表示一个中文汉字,会超出tinytext 保存范围

mysql> insert into char_example values(80个中文字符,100个中文字符);  #插入成功    

实战建议

• 1、 char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定。超过255字 节的只能用varchar或者text；
• 2、 varchar可变长度，可以设置最大长度；适合用在长度可变的属性。
• 3、 text不设置长度， 当不知道属性的最大长度时，适合用text， 能用varchar的地方不用text；
• 4、 如果都可以选择，按照查询速度： char最快， varchar次之，text最慢。

二进制类型

ENUM和SET类型

这也就是我们熟悉的枚举型
基本形式是 [字段名称] ENUM(值1,值2,…);
[字段名称] SET(值1,值2,…);

其中俩者最大的差别就是,enum类型只能选择一个,而set类型可以多选
ENUM
最多可以有65535个值,其中的值再255以内,那么只需要1个字节,如果大于255小于65535,系统将采用俩个字节保存,列表中的每一个值都有单独的编号,MySQL存入的是这个编号,而不是这个值,默认编号从1开始

如果 ENUM类型加上了 NOT NULL 属性,其默认值为取值列表的第一个元素,如果没有加上 NOT NULL 属性,ENUM 允许插入 NULL,而NULL将是默认值

mysql> use test;   #选择数据库test 
mysql> create table enum_example (e_enum enum('男','女','选择保密') ); #创建表 
mysql> insert into enum_example values('男');  #插入记录,必须是enum 选项中的值
mysql> insert into enum_example values(1);  #插入记录可以用数值表示
mysql>select e_enum + 0  from enum_example; #查询enum 选项对应的整数值

SET
SET 类型和ENUM类型的基本形式基本相同,SET类型最多有64个元素组成(也就是用8个字节,一个字节8位)

mysql> use test;   #选择数据库test 
mysql> create table set_example (interest set('足球','追剧','篮球','撩妹') ); #创建表 
mysql> insert into set_example values( '足球,撩妹' ); #插入记录,必须是enum 选项中的值
mysql> insert into enum_example values(9);  #插入相应位效果等同，9 =>1001 选择1,4
mysql>select interest+0 from set_example;  #以整数的方式查询

二进制类型

二进制数据和文本数据在mysql 中的最大区别在于：

1. 二进制类型存储原始的二进制数据(如图片，视频，exe文件等)。文本类型(TEXT)用来存储字符字符串(如由英文字符、中文字符或其它语言字符组成的字符串)。

2. 二进制类型没有字符集，并且排序和比较基于列值字节的数值。而TEXT类型有字符集，并且根据字符集的校对规则对值进行排序和比较。