MySQL（十三）—— 表

MySQL数据库系列内容的学习目录$\to$老杜带你学MySQL学习系列内容汇总。

13. 表

13.1 表的创建（建表）

  建表的语法格式：(建表属于DDL语句，DDL包括：create drop alter)

create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型);
或者

create table 表名(
	字段名1 数据类型, 
	字段名2 数据类型, 
	字段名3 数据类型
);

  表名： 建议以 t_ 或者 tbl_ 开始，可读性强，见名知意。
  字段名： 见名知意。
  表名和字段名都属于标识符。

  创建表的时候，表中有字段，每一个字段有：
   $\diamond$ 字段名
   $\diamond$ 字段数据类型
   $\diamond$ 字段长度限制
   $\diamond$ 字段约束

13.2 关于MySQL中的数据类型

13.2.1 varchar

  varchar(最长255)：可变长度的字符串。
            比较智能，节省空间。
            会根据实际的数据长度动态分配空间。

  优点： 节省空间。
  缺点： 需要动态分配空间，速度慢。

13.2.2 char

   char(最长255)：定长字符串。
          不管实际的数据长度是多少，分配固定长度的空间去存储数据。
          使用不恰当的时候，可能会导致空间的浪费。

  优点： 不需要动态分配空间，速度快。
  缺点： 使用不当可能会导致空间的浪费。

  varchar和char应该怎么选择？
  性别字段选什么？因为性别是固定长度的字符串，所以选择char。
  姓名字段选什么？每一个人的名字长度不同，所以选择varchar。

13.2.3 int

  int(最长11)：数字中的整数型。等同于java的int。

13.2.4 bigint

  bigint：数字中的长整型。等同于java中的long。

13.2.5 float

  float：单精度浮点型数据。

13.2.6 double

  double：双精度浮点型数据。

13.2.7 date

  date：短日期类型。

13.2.8 datetime

  datetime：长日期类型。

13.2.9 clob

  clob：字符大对象（Character Large OBject）。
       最多可以存储4G的字符串。比如：存储一篇文章，存储一个说明。
       超过255个字符的都要采用CLOB字符大对象来存储。

13.2.10 blob

  blob：二进制大对象（Binary Large OBject）。
       专门用来存储图片、声音、视频等流媒体数据。
       往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，需要使用IO流才行。

  t_movie 电影表（专门存储电影信息的）

编号			名字				故事情节		     上映日期		      时长	       海报		     类型
no(bigint)	name(varchar)	history(clob)	 playtime(date)	  time(double)	image(blob)    type(char)
------------------------------------------------------------------------------------------------------------------
10000		哪吒				...........		 2019-10-11		       2.5		    ....	      '1'
10001		林正英之娘娘      ...........		 2019-11-11		       1.5			....		  '2'
....

13.3 创建一个学生表

  学号、姓名、年龄、性别、邮箱地址

create table t_student(
	no int,
	name varchar(32),
	sex char(1),
	age int(3),
	email varchar(255)
);

  上述创建表的语句报错，已存在！
  删除表：drop table t_student;当这张表不存在的时候会报错！
  如果这张表存在的话，删除：drop table if exists t_student;。

13.4 插入数据 insert （DML）

  语法格式： insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);
  注意：字段名和值要一一对应。什么是一一对应？数量要对应，数据类型要对应。

  插入no为"1"、name为"zhangsan"、sex为"m"、age为"20"、email为"zhangsan@123.com"的数据：insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');

  插入email为"lisi@123.com"、name为"lisi"、sex为"f"、age为"20"、no为"2"的数据：insert into t_student(email,name,sex,age,no) values('lisi@123.com','lisi','f',20,2);

  插入no为3的数据：insert into t_student(no) values(3);

  插入name为“wangwu”的数据：insert into t_student(name) values('wangwu');

  注意： 1. insert语句但凡是执行成功了，那么必然会多一条记录。
      2. 没有给其它字段指定值的话，默认值是NULL。

  删除之前创建的表：drop table if exists t_student;

  创建新表，设置sex的默认值为"m"。

create table t_student(
	no int,
	name varchar(32),
	sex char(1) default 'm',
	age int(3),
	email varchar(255)
);

  插入编号为1的数据：insert into t_student(no) values(1);

  insert语句中的“字段名”可以省略吗？可以。
  但是insert into t_student values(2); 是错误的，因为前面的字段名省略的话，等于都写上了，所以值也要都写上！

  insert into t_student values(2, 'lisi', 'f', 20, 'lisi@123.com');是正确的。

13.5 insert 插入日期

  数字格式化：format
  从员工信息表中查询员工名和工资：select ename,sal from emp;

  格式化数字：format(数字, '格式')
  select ename,format(sal, '$999,999') as sal from emp;

  str_to_date：将字符串varchar类型转换成date类型。
  date_format：将date类型转换成具有一定格式的varchar字符串类型。

  drop table if exists t_user;

  创建新表 t_user。

• 生日也可以使用date日期类型。

create table t_user(
	id int,
	name varchar(32),
	birth date 
);

• 生日可以使用字符串生日可以使用字符串，例如1990-10-11 （10个字符）。

create table t_user(
	id int,
	name varchar(32),
	birth char(10) 
);

  注意： 数据库中的有一条命名规范：所有的标识符都是全部小写，单词和单词之间使用下划线进行衔接。

  desc t_user;

  插入数据？
  插入id为1，name为"zhangsan"，birth为1990年10月1日的数据：insert into t_user(id,name,birth) values(1, 'zhangsan', '01-10-1990');

  上述语句出问题了：原因是类型不匹配。数据库birth是date类型，这里给了一个字符串varchar。
  怎么办？可以使用str_to_date函数进行类型转换。str_to_date函数可以将字符串varchar转换成日期类型date，语法格式：str_to_date('字符串日期', '日期格式')。

  MySQL的日期格式：%Y 年
            %m 月
            %d 日
            %h 时
            %i 分
            %s 秒

  插入id为1，name为"zhangsan"，birth为1990年10月1日的数据语句改为：insert into t_user(id,name,birth) values(1, 'zhangsan', str_to_date('01-10-1990','%d-%m-%Y'));

  str_to_date函数可以把字符串varchar转换成日期date类型数据，通常使用在插入insert方面，因为插入的时候需要一个日期类型的数据，需要通过该函数将字符串转换成date。

  如果提供的日期字符串是%Y-%m-%d这个格式，str_to_date函数就不需要了！例如，insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01');。

  查询的时候可以以某个特定的日期格式展示吗？
  date_format 这个函数可以将日期类型转换成特定格式的字符串。
  date_format 函数的用法：date_format(日期类型数据, '日期格式')，这个函数通常使用在查询日期方面，设置展示的日期格式。例如，select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user;。

  不进行格式化：select id,name,birth from t_user;

  以上的SQL语句实际上是进行了默认的日期格式化，自动将数据库中的date类型转换成varchar类型，并且采用的格式是MySQL默认的日期格式：’%Y-%m-%d’。

13.6 date 和 datetime 两个类型的区别

  date是短日期：只包括年月日信息。
  datetime是长日期：包括年月日时分秒信息。

  删除之前创建的表 t_user：drop table if exists t_user;
  创建新的表 t_user：

create table t_user(
	id int,
	name varchar(32),
	birth date,
	create_time datetime
);

  其中，id是整数；name是字符串，birth是短日期，create_time是这条记录的创建时间：长日期类型。

  MySQL短日期默认格式：%Y-%m-%d。
  MySQL长日期默认格式：%Y-%m-%d %h:%i:%s。

  插入数据：insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2020-03-18 15:49:50');

  在MySQL当中怎么获取系统当前时间？
  now() 函数，并且获取的时间带有时分秒信息，是datetime类型的。例如，insert into t_user(id,name,birth,create_time) values(2,'lisi','1991-10-01',now());。

13.7 修改 update（DML）

  语法格式：update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件;
  注意： 没有条件限制会导致所有数据全部更新。

  更新id为2的name、birth信息：update t_user set name = 'jack', birth = '2000-10-11' where id = 2;

  更新id为2的create_time信息：update t_user set name = 'jack', birth = '2000-10-11', create_time = now() where id = 2;

  更新所有：update t_user set name = 'abc';

13.8 删除数据 delete （DML）

  语法格式：delete from 表名 where 条件;
  注意： 没有条件，整张表的数据会全部删除！

  删除表中id为2的信息：delete from t_user where id = 2;

  插入id为2的数据：insert into t_user(id) values(2);

  删除所有：delete from t_user;