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

CDA 同时被 3 个专栏收录
7 篇文章 1 订阅
6 篇文章 0 订阅
  •  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, 2^{32}-1 ][ -2^{31}, 2^{31}-1]11

bigint

长整型

[ 0, 2^{64}-1 ]-2^{63}, 2^{63}-1 ]20
float(T, D)单精度浮点型4T:数据总位数;D:数据中小数点后保留几位
double(T, D)高精度浮点型8
decimal(T, D)定点型T+2

备注:

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

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

数据类型数据格式(说明)占用字节数取值范围
yearYYYY(年)11900 ~ 2155
timeHH:MM:SS(时:分:秒)3-838:59:59 ~ 838:59:59
dateYYYY-MM-DD(年-月-日)31000-01-01 ~ 9999-12-3
datetimeYYYY-MM-DD HH:MM:SS (年-月-日 时:分:秒)81000-01-01 00:00:00 ~ 9999-12-3 23:59:59
timestampYYYY-MM-DD HH:MM:SS(年-月-日 时:分:秒)41970-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`;

  • 2
    点赞
  • 2
    评论
  • 7
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值