Table表操作、常用列类型和DML(增删改)操作

一、Table表操作

1、创建表:

语法:
create table 表名(
    字段名 数据类型 [(数据的长度) 约束],
    字段名 数据类型 [(数据的长度) 约束],
    字段名 数据类型 [(数据的长度) 约束],
    ......     #最后一个不需要逗号

);

2、表的列字段修改:

    1）修改字段名     

ALTER TABLE 表名 CHANGE 原列名 新列名 数据类型(长度);

    2）修改字段的数据类型或约束

ALTER TABLE 表名 MODIFY 列名 新类型(长度);

     3）添加新字段   

 ALTER TABLE 表名 ADD 列名 类型(长度);

    或者同时添加多列   

ALTER TABLE 表名
ADD(
    列名 类型(长度),
    ...
    列名 类型(长度)    # 最后一个不需要逗号
);

    4）删除字段

ALTER TABLE 表名 DROP COLUMN 列名;

     5）修改表名     

ALTER TABLE 表名 RENAME TO 新表名;

3、表的删除

drop table if exists 表名; 

4、表的复制

    1）仅复制表的结构     

create table 新建表名 like 要被复制的表名;

    2）复制表的结构和数据     

create table 新建表名 select * from 要被复制的表名;

    3）只复制表结构和部分数据     

create table 新建表名 select * from 要被复制的表名 where 条件;

    4）只复制部分字段,且不要数据     

create table 新建表名 select 需要的列 from 要被复制的表名 where 0;

二、常用列类型

1、常用列类型

    1）数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值，高精度类型，金额货币优先选择	小数值

    取值范围如果加了 unsigned，则最大值翻倍，如 tinyint unsigned 的取值范围为(0~255)。

    int(m) 里的 m 是表示 SELECT 查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度，不知道这个 m 有什么用。

    小数：float(M,D) double(M,D) decimal(M,D);

           M - 表示整数部分和小数部分合起来的总位数

           D - 表示小数点后保留位数

          不设置后面的M和D值,float和double会根据插入的实际值来确定精度，decimal默认(10,0)表示总体10位数，小数位是0;       

    2）日期和时间类型

类型	大小 (字节)	范围	格式	用途
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，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

       若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

    3）字符串类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

char 和 varchar的区别：

 （1）char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以 char 类型存储的字符串末尾不能有空格，varchar 不限于此。

（2）char(n) 固定长度，char(4) 不管是存入几个字符，都将占用 4 个字节，varchar 是存入的实际字符数 +1 个字节（n<=255）或2个字节(n>255)，所以 varchar(4),存入 3 个字符将占用 4 个字节。

（3）char 类型的字符串检索速度要比 varchar 类型的快。

更多具体信息参考网站：https://zixuephp.net/manual-mysql-1320.html

数据类型的属性：

MySQL关键字	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

2、Java与MySql数据类型对应表

      参考文章：java与mysql对应数据类型

MySQL Type Name	Return value of GetColumnTypeName	Return value of GetColumnClassName
BIT(1) (new in MySQL-5.0)	BIT	java.lang.Boolean
BIT( > 1) (new in MySQL-5.0)	BIT	byte[]
TINYINT	TINYINT	java.lang.Boolean if the configuration property tinyInt1isBit is set to true (the default) and the storage size is 1, or java.lang.Integer if not.
BOOL, BOOLEAN	TINYINT	See TINYINT, above as these are aliases for TINYINT(1), currently.
SMALLINT[(M)] [UNSIGNED]	SMALLINT [UNSIGNED]	java.lang.Integer (regardless of whether it is UNSIGNED or not)
MEDIUMINT[(M)] [UNSIGNED]	MEDIUMINT [UNSIGNED]	java.lang.Integer (regardless of whether it is UNSIGNED or not)
INT,INTEGER[(M)] [UNSIGNED]	INTEGER [UNSIGNED]	java.lang.Integer, if UNSIGNED java.lang.Long
BIGINT[(M)] [UNSIGNED]	BIGINT [UNSIGNED]	java.lang.Long, if UNSIGNED java.math.BigInteger
FLOAT[(M,D)]	FLOAT	java.lang.Float
DOUBLE[(M,B)]	DOUBLE	java.lang.Double
DECIMAL[(M[,D])]	DECIMAL	java.math.BigDecimal
DATE	DATE	java.sql.Date 开发中都是用java.util.Date
DATETIME	DATETIME	java.sql.Timestamp
TIMESTAMP[(M)]	TIMESTAMP	java.sql.Timestamp
TIME	TIME	java.sql.Time
YEAR[(2|4)]	YEAR	If yearIsDateType configuration property is set to false, then the returned object type is java.sql.Short. If set to true (the default), then the returned object is of type java.sql.Date with the date set to January 1st, at midnight.
CHAR(M)	CHAR	java.lang.String (unless the character set for the column is BINARY, then byte[] is returned.
VARCHAR(M) [BINARY]	VARCHAR	java.lang.String (unless the character set for the column is BINARY, then byte[] is returned.
BINARY(M)	BINARY	byte[]
VARBINARY(M)	VARBINARY	byte[]
TINYBLOB	TINYBLOB	byte[]
TINYTEXT	VARCHAR	java.lang.String
BLOB	BLOB	byte[]
TEXT	VARCHAR	java.lang.String
MEDIUMBLOB	MEDIUMBLOB	byte[]
MEDIUMTEXT	VARCHAR	java.lang.String
LONGBLOB	LONGBLOB	byte[]
LONGTEXT	VARCHAR	java.lang.String
ENUM('value1','value2',...)	CHAR	java.lang.String
SET('value1','value2',...)	CHAR	java.lang.String

   

1、bolb类型

     一般用于对图片的数据库存储，原理是把图片打成二进制，然后进行的一种存储方式，在java中对应 byte[]数组。

2、boolen类型

     在mysql数据库中，个人认为用int类型代替较好，对bit操作不是很方便，尤其是在具有web页面开发的项目中，表示0/1，对应java类型的Integer较好。

3、decimal列的声明语法是decimal(m,d)。

    在mysql5.1中，参数的取值范围：

    1）M是数字的最大数（精度）。其范围为1～65（在较旧的MySQL版本中，允许的范围是1～254）。

    2）D是小数点右侧数字的数目（标度）。其范围是0～30，但不得超过M。

    说明：float占4个字节，double占8个字节，decimail(M,D)占M+2个字节。

     如DECIMAL(5, 2) 的最大值为9 9 9 9 . 9 9，因为有7 个字节可用。

 参考文章：https://www.cnblogs.com/jerrylz/p/5814460.html

三、DML(增删改)操作

    数据操作语言（DML：Data Manipulation Language）：其语句包括动词INSERT、UPDATE和DELETE操作。

1、INSERT语句

    1）插入一条记录：

语法：insert into 表名(字段1,字段2,...) values (对应字段1的值,对应字段2点值,...) ;

注意：

（1）insert记录时候要保持插入的记录值类型和表结构设计的类型一致，除了数字类型外，字符串型varchar和日期类型datetime等都需要用单引号包裹起来

（2）不可为空的字段必须插入记录值，可以为null的列在插入的时候可以不写

（3）字段列表和values后面的值必须前后一一对应(内容一致，数量一致，数据类型一致)

    2）插入多条记录：

语法一：

insert into
     表名(字段1,字段2,...)
values
     (对应字段1的值,对应字段2点值,...)，
     (对应字段1的值,对应字段2点值,...)，
     (...) ;

语法二：MySQL特有，两者列字段保持一致

insert into 表1(字段1,字段2,...)  select (字段1,字段2,...) from 表2；

2、UPDATE语句

    1）修改单表记录

语法：update 表名 set 字段名1=新的值, 字段名2=新值 , ... where 条件 ;

    2）修改多表记录

语法一：

update 表1 别名,表2 别名
set 字段1=值 , ...
where 连接条件 and 筛选条件;

语法二：

update 表1 别名
[连接类型] join 表2 别名
on 连接条件
set 字段1=值 , ...
where 筛选条件;

案例：修改没有年龄记录的同学的对应年龄的编号都为3

update students s
left join ages a
on s.age_id=a.id
set s.age_id=3
where a.age is null;

3、DELETE语句

    1）单表的删除

语法：delete from 表名 where 要删除的记录筛选条件;

    2）多表的删除

语法一：

delete 别名1,别名2
from 表1 别名1,表2 别名2
where 连接条件 and 筛选条件 ;

语法二：

delete 别名1,别名2
from 表1 别名1
[连接类型] join 表2 别名2
on 连接条件
where 筛选条件 ;

注意：如果只删表1的就只写别名1，如果两个表的记录都删就别名1,别名2

    3）删除整个表的全部记录，保留表的结构

delete from 表名;
或者
truncate table 表名;

delete和truncate区别：

（1）delete可以加where条件

（2）truncate删除没有返回值，delete删除有返回值

（3）truncate删除不能回滚，delete删除可以回滚

（4）假如要删除的表上有自增长字段，如果用delete删除，再插入数据，自增长从断点值开始；用truncate删除，再插入数据，从1开始;

（5）truncate效率高

案例：删除李斯的年龄信息

delete a from students s
inner join ages a
on s.age_id = a.id
where s.name='李斯';

    知识点整理

ends~