CDA数据分析师 - SQL数据库基础 数据类型&表操作语句

•  SQL 基础概念

【领会】

关系型数据库基本概念

表的基本概念（字段、记录）

表的约束条件（主键、外键、非空、唯一、自增、默认值）

实体关系图（E-R 图）

ANSI-SQL 以及不同的数据库实现的关系

【熟知】

关系型数据库的数据类型

表操作（创建表、修改表、删除表、查看表）

【解析】

1. 表字段的约束条件主要包括主键、外键、非空、唯一、自增和默认值等

2. 主键 非空、不能重复；外键 可重复 可为空

3. 关系数据库的数据类型有（以MySQL为例）：

数值类型 | 整数类型：tinyint、smallint、mediumint、int、bigint | 浮点数类型：float、double | 定点小数类型：decima

-- 创建默认数值型表
DROP TABLE
IF EXISTS numerical_value_default_info;
CREATE TABLE numerical_value_default_info ( 
tinyi TINYINT COMMENT'tinyint',
smalli SMALLINT COMMENT'smallint',
mediumi MEDIUMINT COMMENT'mediumint',
i INT COMMENT'int',
bigi BIGINT COMMENT'bigint',
flo FLOAT COMMENT'float', 
dou DOUBLE COMMENT'double', 
deci DECIMAL COMMENT'decimal'
)COMMENT'默认数值型表';

运行以上建表语句后，创建的默认数值型表信息如下：

MySQL常用数值型数据类型

数据类型	说明	占用字节数	取值范围		默认显示宽度
			无符号	有符号
int	整型	4	[ 0，  ]	[ , ]	11
bigint	长整型	8	[ 0,  ]	[ ,  ]	20
float（T, D）	单精度浮点型	4	T：数据总位数；D:数据中小数点后保留几位
double（T, D）	高精度浮点型	8
decimal（T, D）	定点型	T+2

备注：

• 1个字节 = 8位二进制数
• 显示宽度和数据类型的取值范围无关

日期/时间类型：year、time、date、datetime、timestamp

数据类型	数据格式（说明）	占用字节数	取值范围
year	YYYY（年）	1	1900 ~ 2155
time	HH：MM：SS（时：分：秒）	3	-838：59：59 ~ 838：59：59
date	YYYY-MM-DD（年-月-日）	3	1000-01-01 ~ 9999-12-3
datetime	YYYY-MM-DD HH:MM:SS （年-月-日 时：分：秒）	8	1000-01-01 00：00：00 ~ 9999-12-3 23：59：59
timestamp	YYYY-MM-DD HH:MM:SS（年-月-日 时：分：秒）	4	1970-01-01 00：00：01 UTC ~ 2038-01-19 03:14:17 UTC

备注：datetime和timestamp数据格式一致，但timestamp与时区相关，不同时区查询结果不同。

字符串类型：char、varchar、tinytext、text、mediumtext、longtext、enum、set

二进制类型：bit、binary、varbinary、blob、mediumblob、longblob

char&varchar | binary&varbinary对比

数据类型	char(A)	varchar(A)	binary(A)	varbinary(A)
占用字节数	A [1, 255]	B+1 B<=A A [1, 21585]	A[1,255]	B+1 B<=A A [1, 65265]
区分大小写	不区分	不区分	区分	区分

备注：
1.varchar和varbinary的占用字节最大值
VARCHAR(2222222) //试错值大点

结果：
> 1074 - Column length too big for column 'vachar' (max = 21845); use BLOB or TEXT instead
> 时间: 0s

VARCHAR(21845)
结果：
> 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
> 时间: 0.001s

最后试出的结果是：VARCHAR - 21585 & varbinary - 65265

2.char&binary 固定长度 | varchar&varbinary可变长度 

3.char&varchar 不区分大小写 | binary&varbinary 区分大小写

-- 创建字符串型表
DROP TABLE
IF EXISTS str_char_info;
CREATE TABLE str_char_info(
c CHAR(255) COMMENT'char',
vc VARCHAR(21585) COMMENT'varchar',
t TEXT COMMENT'text',
e ENUM('a','b') COMMENT'enum',
s SET('1','2') COMMENT'set'
)COMMENT'字符串型表';

-- 插入数据
INSERT INTO test.str_char_info
VALUES('a', 'aa', 'aaaaaaaaaa', 'a', '1');
INSERT INTO test.str_char_info
VALUES('A', 'AA', 'AAAAAAAAAAA', 'A', '1');

-- 查询
SELECT
	* 
FROM
	test.str_char_info 
WHERE
	c = 'A'
-- 	vc = "AA"
	;

-- 创建二进制型表
DROP TABLE
IF EXISTS binary_info;
CREATE TABLE binary_info(
b BIT(10) COMMENT'bit',
bi BINARY(4) COMMENT'binary',
vbi VARBINARY(65265) COMMENT'varbinary',
bl BLOB COMMENT'BLOB'
)COMMENT'二进制型表';

-- 插入数据
INSERT INTO test.binary_info
VALUES(1023, 'a', 'aa', 'aaaa');

INSERT INTO test.binary_info
VALUES(111, 'A', 'AA', 'AAAA');

-- 查询
SELECT
	* 
FROM
	test.binary_info 
WHERE
-- 	bi = 'a\0\0\0'
	vbi = 'aa'
	;

4.表操作语句

-- 创建学生信息表
DROP TABLE 
IF EXISTS`student_info`;
CREATE TABLE `student_info` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
  `name` varchar(255) NOT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `address` varchar(255) DEFAULT NULL COMMENT '地址',
  `phone` varchar(255) DEFAULT NULL COMMENT '联系电话',
  `remarks` varchar(255) DEFAULT NULL COMMENT '备注'
)COMMENT'学生信息表';

-- 创建学科表
DROP TABLE
IF EXISTS `subject_info`;
CREATE TABLE `subject_info`(
`id` INT PRIMARY KEY AUTO_INCREMENT COMMENT'学科编号',
`name` VARCHAR(20) UNIQUE COMMENT'学科名称',
`remarks` TEXT COMMENT'备注'
)COMMENT'学科表';

-- 创建成绩表
DROP TABLE 
IF EXISTS `score_info`;
CREATE TABLE `score_info`(
`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT'id',
`stu_id` INT COMMENT '学号',
`sub_id` INT COMMENT '学科',
`score` DOUBLE COMMENT '成绩',
`remarks` VARCHAR(255) COMMENT '备注' 
) COMMENT '成绩表';

-- 修改表名
ALTER TABLE `students_info` RENAME `student_info`;

-- 修改字段数据型类型
ALTER TABLE `student_info` MODIFY `address` TEXT;

-- 修改字段名
ALTER TABLE `student_info` CHANGE `name` `studentName` VARCHAR(10);

-- 增加字段
ALTER TABLE `student_info` ADD `add` BIT(10) UNIQUE; 

-- 删除字段
ALTER TABLE `student_info` DROP `add`;

-- 修改字段顺序
ALTER TABLE `student_info` MODIFY `add` BIT(20) FIRST;
ALTER TABLE `student_info` MODIFY `add` BIT(20) AFTER `age`;

-- 添加数据 学生信息表
INSERT INTO `student_info` ( `id`, `name`, `age`, `address`, `phone`, `remarks` )
VALUES
	( NULL, '小强', 28, '北京·大兴', '17710578257', 'boy' );
	
INSERT INTO `student_info` ( `id`, `name`, `age`, `address`, `phone`, `remarks` )
VALUES
	( NULL, '张三', 27, '上海·嘉定', '18810881088', 'boy' );
	
INSERT INTO `student_info` ( `id`, `name`, `age`, `address`, `phone`, `remarks` )
VALUES
	( NULL, '李四', 27, '天津·红桥', '19999999999', 'girl' );
	
-- 添加数据 成绩表
INSERT INTO `score_info` (`stu_id`, `sub_id`, `score` )
VALUES
	(1, 1, 90.5 ), (1, 2, 99), (1, 3, 96.6),
	(2, 1, 60.5 ), (2, 2, 55), (2, 3, 78.6),
	(3, 1, 77.5 ), (3, 2, 88), (3, 3, 99),
	(4, 1, 88 ), (4, 2, 66), (4, 3, 95.4),
	(5, 1, 99.5 ), (5, 2, 100), (5, 3, 88),
	(50, 1, 99.5 ), (50, 2, 100), (50, 3, 88)
	;
	
-- 导入数据FROM CSV文件
LOAD DATA LOCAL INFILE 'C:/Users/xuqiang_duan/Desktop/subject.csv'
INTO TABLE `subject_info`
FIELDS TERMINATED BY ','
IGNORE 1 LINES
;

-- 导入数据FROM TXT文件
LOAD DATA LOCAL INFILE 'C:/Users/xuqiang_duan/Desktop/subject.txt'
INTO TABLE `subject_info`
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n'
;

-- 查看表结构
DESC `subject_info`;