数据表操作（三）
一、数据表的操作
1、创建表
create table 表名( 列名 类型 是否可以为空， 列名 类型 是否可以为空 )ENGINE=InnoDB DEFAULT CHARSET=utf8 #查看数据表创建语句 show create table t5 \G descript table create table tb_emp1 ( id int(11), name varchar(25), deptid int(11), salary float );
2、清空表
delete from 表名 truncate table 表名
3、删除表
drop table 【IF EXISTS】 表1,表2....#IF EXISTS表示如果数据表不存在会提示警告，但不会出错
4、修改数据表
修改表名 - alter table <old table> rename [to] <new table>
alter table tb_dept3 rename tb_deptments;
DESCRIBE tb_dept1;
修改字段数据类型 - alter table <tablename> modify <field> <type>
alter table tb_dept1 modify name VARCHAR(30);
DESCRIBE tb_dept1;
修改字段名或修改字段数据类型 - alter table <tablename> change <oldfield> <newfield> <newtype>
alter table tb_dept1 change location loc VARCHAR(50);
DESCRIBE tb_dept1;
添加字段 - alter table <tablename> add <field> <type> [约束条件] [First|After Field] #添加字段,First表示设为第一个字段，After表示插入到指定字段的下一列
alter table tb_dept1 add managerid int(10) First;
alter table tb_dept1 add column2 VARCHAR(12) not null after name;
DESCRIBE tb_dept1;
删除字段 - alter table <tablename> drop <field>
alter table tb_dept1 drop column2;
DESCRIBE tb_dept1;
修改字段排列位置 - alter table <tablename> modify <field1> <type> first|after<field2>
alter table tb_dept1 modify column1 VARCHAR(12) first;
alter table tb_dept1 modify column1 VARCHAR(12) after loc;
DESCRIBE tb_dept1;
修改表的存储引擎 - alter table <tablename> engine <存储引擎名>
alter table tb_deptments engine=myisam; show create table tb_deptments; #查看表结构
删除表外键约束 - alter table <tablename> drop foreign key <foreignname>
create table tb_emp9 ( id int(11) PRIMARY key, name VARCHAR(25), deptid int(11), salary FLOAT, constraint fk_emp_dept foreign key (deptid) REFERENCES tb_dept1(id) ) alter table tb_emp9 drop foreign key fk_emp_dept; show create table tb_emp9;
常用修改表语句
添加列：alter table 表名 add 列名 类型
删除列：alter table 表名 drop column 列名
修改列：
alter table 表名 modify column 列名 类型; -- 类型
alter table 表名 change 原列名 新列名 类型; -- 列名，类型
添加主键：
alter table 表名 add primary key(列名);
删除主键：
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除外键：alter table 表名 drop foreign key 外键名称
修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
5、会话级别 - 表示当前连接
show session variables like 'auto_inc%' 查看会话级别的全局变量 set session auto_increment_increment=2; 设置会话步长 set session auto_increment_offset=2; 设置会话起始值
6、全局级别 - 表示所有连接
show global variables like 'auto_inc%'; set global auto_increment_increment=200 set global auto_increment_offset=10
当全局修改时，所有登录的用户均根据全局设置
二、数据表约束
1、可空约束
not null - 不可空
null - 可空
create table tb_dept1
(
id int(11) primary key,
name VARCHAR(22) not null,
location VARCHAR(50)
);
2、默认值约束
create table tb1( nid int not null defalut 2, num int not null )
3、主键约束
#一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。
create table tb1(
nid int not null auto_increment primary key,
num int null
)
或
create table tb1(
nid int not null,
num int not null,
primary key(nid,num)
)
4、外键约束
#一种特殊的索引，只能是指定内容
creat table color(
nid int not null primary key,
name char(16) not null
)
create table fruit(
nid int not null primary key,
smt char(32) null ,
color_id int not null,
constraint fk_cc foreign key (color_id) references color(nid)
)
show create table fruit;
# fk_cc为约束名（不能重复） - foreign key(外键名) references 表名(列名)
# 当主键为两列时，对应的外键也必须为两列，如constraint fk_t1_t2 foreign key (id1,id2 references t1(nid,pid)
5、唯一约束
#不能重复、加速查找，与主键的区别为可以为空
unique 约束名(列名)
unique 约束名(列名1，列名2) 列名1+列名2 联合唯一
create table tb_dept2
(
id int(11) PRIMARY key,
name VARCHAR(22) UNIQUE,
location VARCHAR(50)
)
#或者直接定义约束名
create table tb_dept3
(
id int(11) PRIMARY KEY,
name VARCHAR(25),
location VARCHAR(50),
constraint STH UNIQUE(name)
);
6、自增列
#一个表中只能有一个自增列且必须为主键
#插入值时忽略自增列
create talbe t3(
id int auto_increment primary key,
name varchar(20)
)
#修改自增列的当前值
alter table t10 auto_increment=20;
三、数据表操作
1、增加数据
insert into 表 (列名1,列名2...) values (值1,值2,值3...)
insert into 表 (列名1,列名2...) values (值1,值2,值3...),(值1,值2,值3...)
insert into 表 (列名1,列名2...) select (列名1,列名2...) from 表
示例:
insert into tb(name,age) values('alex',12),('root',18)
insert into tb12(name,age) select name,age from tb12
2、删除数据
delete from 表 delete from 表 where id＝1 and name＝'alex'
3、修改数据
update 表 set name ＝ 'alex' where id>1 update tbl2 set name='alex' where id>12 name='xx' update tb12 set name='alex',age=19 where id>12 and name='xx'
4、外部导入数据 - 导入文件格式需要与数据表格式一致
--infile后跟文件路径，路径分隔符使用/（非\）<br>--fields terminated by表示分隔符 <br>--ignore 表示忽略多少行
load data local infile 'E:/LiWork/wy/lession/data/orderinfo.csv' into table orderinfo
fields terminated by ','
ignore 1 lines;
四、数据的基本类型
1、数值型 - 整数型
1）tinyint - 微小整数，数据类型用于保存一些范围的整数数值范围，1字节
unsigned - 0 ～ 255
signed - -128 ～ 127
PS: MySQL中无布尔值，使用tinyint(1)构造
2）smllint - 小整数，数据类型用于保存一些范围的整数数值范围，2字节
unsigned - 0 ～ 65535
signed - -32768 ~ 32767
3）mediumint - 中等整数，数据类型用于保存一些范围的整数数值范围，3字节
unsigned - 0 ～ 16777215
signed - -8388608 ～ 8388607
4) int[(m)] - 整数，数据类型用于保存一些范围的整数数值范围，4字节
unsigned - 0 ～ 4294967295
signed - -2147483648 ～ 2147483647
PS：整数类型中的m为显示宽度（实际值小于显示宽度以空格填充，大于显示宽度则显示实际值），对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002
系统会为整型数据添加默认的显示宽度，可使用desc查看
5）bigint[(m)] - 大整数，数据类型用于保存一些范围的整数数值范围，8字节
unsigned - 0 ～ 18446744073709551615
signed - -9223372036854775808～ 9223372036854775807
2、浮点数和定点数 - 都可以使用(m,d)表示，m为精度，表示总位数，d为标度，表示小数位数
1）FLOAT[(M,D)] - 单精度浮点数（非准确小数值），4字节
unsigned - 1.175494351E-38 to 3.402823466E+38
signed - -3.402823466E+38 to -1.175494351E-38
PS：数值越大，越不精准
2）DOUBLE[(M,D)] - 双精度浮点数（非准确小数值），8字节
unsigned - 2.2250738585072014E-308 to 1.7976931348623157E+308
signed - -1.7976931348623157E+308 to -2.2250738585072014E-308
3）decimal[(m[,d])] - 准确的小数值，m最大值为65，d最大值为30，占用m+2字节
PS: 不同于浮点数，实际存储时以字符串存放的，对于精确数值计算时需要用此类型，decaimal能够存储精确值的原因在于其内部按照字符串存储
无论定点数或浮点数，如果实际数值超出精度范围，则四舍五入，Decimal默认精度为(10,0)，浮点数则根据计算机硬件和操作系统而定
查看警告信息 - show warnings;
3、字符串型
1）char (m) - 用于表示固定长度的字符串，可以包含最多达255个字符。其中（1<M<255）m代表字符串的长度，实际存储为M字节
PS: 即使数据小于m长度，也会占用m长度
2）varchar(m) - 用于表示变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中，实际存储
PS：varchar占用空间少，但char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。
因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡，创建数据表时将定长的数据列往前放，先检索定长列执行速度会得到优化
3）text - text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符
4）mediumtext - A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters
5）longtext - A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters
PS：当文件长度超出longtext时，则应保存在服务器硬盘上，数据库中保存指向文件的路径
6）关于字符长度
UTF－8：一个汉字＝3个字节
GBK：一个汉字＝2个字节
varchar(n) 表示 n 个字符，无论汉字和英文，Mysql 都能存入 n 个字符，仅是实际字节长度有所区别，MySQL 检查长度，可用 SQL 语言来查看：
select LENGTH(fieldname) from tablename
4、日期型
1）DATE - YYYY-MM-DD（1000-01-01 ~ 9999-12-31），3字节，仅用于需要显示日期值时
输入格式为：'YYYY-MM-DD'、'YYYYMMDD'、当使用'YY'时，则70~99表示1970~1999、00~69表示2000~2069
2）TIME - HH:MM:SS（'-838:59:59' ~ '838:59:59'），3字节，用于仅需要显示时间信息值时
输入格式为：'D HH:MM:SS'（标准）、'HH:MM:SS'、'HH:MM'、'D HH:MM'、'D HH'、或'SS'，D表示日，取值范围0~34，插入数据库时转换为小时进行保存，'HHMMSS'必须具有时间意义，否则存储为00:00:00
使用冒号 '11:12'表示为11:12:00，不使用冒号 '1112'则表示为00:11:12
3) DATETIME - YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 Y），8字节，同时包含日期和时间信息的值
输入格式为：'YYYY-MM-DD HH:MM:SS'、'YYYYMMDDHHMMSS'、YY为两位时参照1
Mysql允许"不严格"语法 - 如 '98.12.31 11+30+45'、'98/12/31 11*30*45'、'98@12@31 11^30^45'都可以正确的插入数据库
4）TIMESTAMP - YYYYMMDD HHMMSS（1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC），4字节，格式同DATATIME，固定在19个字符，取值范围小于DATATIME，UTC为世界标准时间
输入格式为：'YYYY-MM-DD HH:MM:SS'、'YYYYMMDDHHMMSS'
除了取值范围与DATATIME不同外，DATATIME按实际输入的格式存储，而TIMESTAMP存储时对当前时区进行转换，检索时再转换回当前时区，查询时根据当前的时区不同，显示的时间值不同
set time_zone='+10:00'; #设置时区（中国一般为东8区） select CURRENT_TIME(); #由于设置时区不同，则显示的时间值不同
5）YEAR - YYYY（1901 ~ 2155），1字节
输入格式为：'YYYY'或YYYY
输入2位时，00~69表示2000~2069,70~99表示1970~1999
4、枚举类型
enum - An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
5、集合类型
set - A SET column can have a maximum of 64 distinct members
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
6、二进制类型
bit[(M)] - 二进制位（101001），m表示二进制位的长度（1-64），默认m＝1
TINYBLOB - 不超过 255 个字符的二进制字符串，0-255字节
BLOB - 二进制形式的长文本数据，0-65 535字节
MEDIUMBLOB - 二进制形式的中等长度文本数据，0-16 777 215字节
LONGBLOB - 二进制形式的极大文本数据，0-4 294 967 295字节
