多种网站建设,文字转图片生成器在线制作,公众号 wordpress 结合,东阳房产网是记录#xff0c;我不会记录的特别详细 第10章 创建和管理表
标识符命名规则
数据库名、表名不得超过30个字符#xff0c;变量名限制为29个只能包含 A–Z, a–z, 0–9, _共63个字符数据库名、表名、字段名等对象名中间不要包含空格同一个MySQL软件中#xff0c;数据库不能… 是记录我不会记录的特别详细 第10章 创建和管理表
标识符命名规则
数据库名、表名不得超过30个字符变量名限制为29个只能包含 A–Z, a–z, 0–9, _共63个字符数据库名、表名、字段名等对象名中间不要包含空格同一个MySQL软件中数据库不能同名同一个库中表不能重名同一个表中字段不能重名 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用请在SQL语句中使 用着重号引起来保持字段名和类型的一致性在命名字段并为其指定数据类型的时候一定要保证一致性假如数据 类型在一个表里是整数那在另一个表里可就别变成字符型了
数据类型
类型类型举例整数类型TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT浮点类型FLOAT,DOUBLE定点数类型DECIMAL位类型BIT日期时间类型YEAR、TIME、DATE、DATETIME、TIMESTAMP文本字符串类型CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT枚举类型ENUM集合类型SET二进制字符串类型BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOBJSON类型JSON对象、JSON数组空间数据类型单值GEOMETRY、POINT、LINESTRING、POLYGON空间数据类型集合MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION
常用的
数据类型描述INT从-231到231-1的整型数据。存储大小为 4个字节CHAR(size)定长字符数据。若未指定默认为1个字符最大长度255VARCHAR(size)可变长字符数据根据字符串实际长度保存必须指定长度FLOAT(M,D)单精度占用4个字节M整数位小数位D小数位。 DM255,0D30默认MD6DOUBLE(M,D)双精度占用8个字节,DM255,0D30默认MD15DECIMAL(M,D)高精度小数占用M2个字节DM650D30最大取值范围与DOUBLE相同。DATE日期型数据格式’YYYY-MM-DD’BLOB二进制形式的长文本数据最大可达4GTEXT长文本数据最大可达4G
创建和管理数据库
创建数据库
//方式1:创建数据库
CREATE DATABASE 数据库名;
//方式2:创建数据库并指定字符集合
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
//方式3:判断数据库是否已经存在不存在则创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;tips:数据库不能改名一些工具可以改名它是先建库再复制所有表到新库删除旧库.
使用数据库
查看当前所有数据库
SHOW DATABASES;查看当前正在使用的数据库
SELECT DATABASE();查看指定库下所有的表
SHOW TABLES FROM 数据库名;查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
SHOW CREATE DATABASE 数据库名\G;//这种在不使用可视化工具的前提下能好看点使用/切换数据库
USE 数据库名;修改数据库
更改数据库字符集 乱码的时候需要用到这条命令
ALTER DATABASE 数据库名 CHARACTER SET 字符集; //字符集就比如utf8,gbk等等删除数据库
DROP DATABASE 数据库名;
DROP DATABASE IF EXISTS 数据库名;//存在就删不存在就不执行操作创建表
方式1
CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);
//IF NOT EXISTS:若存在该表那么就不创建反之就创建CREATE TABLE emp (
-- int类型
emp_id INT(8) AUTO_INCREMENT primary key,
-- 最多保存20个中英文字符
emp_name VARCHAR(20),
-- 总位数不超过15位
salary DOUBLE,
-- 日期类型
birthday DATE
);方式2 使用AS subquery选项将创建表和插入数据结合起来
CREATE TABLE table [(column1,column2,...)] AS subquery;CREATE TABLE e1 AS SELECT * FROM employees;
CREATE TABLE e2 AS SELECT * FROM employeees WHERE 12;//创建的emp2是空表
CREATE TABLE dept1
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id 80;查看数据表结构
SHOW CREATE TABLE 表名\G修改表
这里是指修改表的结构比如添加/删除/重命名列
添加列
ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 [FIRST | AFTER 字段名];修改列
修改数据类型长度默认值和位置
ALTER TABLE 表名 MODIFY [COLUMN] 字段名1 字段类型 [DEFAULT 默认值] [FIRST | AFTER 字段名2]ALTER TABLE dept MODIFY last_name VARCHAR(30);//把last_name列的数据类型改为varchar(30)
ALTER TABLE dept MODIFY salary double(6,2) default 1000;//把salary的默认值改为1000
--重命名列
ALTER TABLE 表名 CHANGE [COLUMN] 列名 新列名 新数据类型;ALTER TABLE dept CHANGE department_name dept_name varchar(15);//把department_name列改为 dept_name varchar(15)ALTER TABLE 表名 DROP [COLUMN] 字段名;
ALTER TABLE dept DROP COLUMN job_id;重命名表
RENAME
RENAME TABLE emp TO emp1;ALTER TABLE
ALTER TABLE dept RENAME [TO] dept1;删除表
DROP TABLE [IF EXISTS] 数据表1 [,数据表2,..,数据表n];这玩意不能回滚
清空表
删除表中的所有数据释放表的存储空间
TRUNCATE TABLE test;不能回滚
扩展
阿里巴巴 《Java开发手册》之MySQL字段命名 表名字段名必须使用小写字母或数字。 如:xiyi_user,xiyi_config,xiyi3_username表必备字段:id,gmt_create,gmt_modified id自增bigint unsignedgmt_create表示主动式创建或者过去分词表示被动更新表的命名最好遵循“业务名称_表的作用” 如admin_config库名尽量与应用名称保持一致 合适的字符存储长度不但节约数据库表空间、节约索引存储更重要的是提升检索速度。 正例无符号值可以避免误存负数且扩大了表示范围。
如何理解清空表、删除表等操作需要谨慎? 因为MySQL在执行清空表、删除表操作时候不会有任何的确认提示万一是误删除怎么办所以在删除之前需要做一个备份 ALTER TABLE同理表结构是无法撤销的如果你添加了一个无关紧要的字段那删除了也不碍事但是如果删除了一个需要的列那该列的数据全部丢失数据无价啊 MySQL8新特性—DDL的原子化 就是DDL操作要么成功要么回滚并且回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log(这玩意还是隐藏的,可以通过设置参数将DDL操作日志打印输出到MySQL错误日志中)
DROP TABLE a,b;//b不存在然后a,b两个表都没有删除.
第11章 表数据操作之增删改 这个我不会记录的详细 插入数据
VALUES的方式添加
INSERT INTO 表名 [(COLUMN1,COLUMN2,...,COLUMNN)]VALUES (VALUE1,VALUE2,...,VALUEN)[,(VALUE1,VALUE2,...,VALUEN)];
INSERT INTO 表名
SET 列名 值,列名 值 ....插入多条记录的时候最好选择使用单条INSERT语句的方式插入如我有一张用户表username,password,若是大量用户注册会产生如下语句
INSERT INTO User VALUES(ABC,ABC),(ABCD,ABCD),(ABCDE,ABCED);//这样效率高
INSERT INTO User VALUES(ABC,ABC);INSERT INSERT INTO User VALUES (ABCD,ABCD);
INSERT INTO User VALUES (ABCDE,ABCED);
将查询结果插入到表中 把表A的查询结果插入到表B中,前提是结果集的列与表B的列保持一致(列名数据类型相同) 结果集的意思是: source表有a,b,c,d,e,f,g列target表有a,b,c列现在把source表的a,b,c列插入到target表的a,b,c列 从source表中查出来a,b,c三列就是结果集 INSERT INTO target
SELECT a,b,c
FROM source;语法格式
INSERT INTO TargetTableName
(tar_column1 [,tar_column2,...,tar_columnn])
SELECT
(src_column1 [,src_column2,...,src_columnn])
FROM SourceTableName
[WHERE condition];INSERT INTO emp3
SELECT *
FROM employees
WHERE department_id 100;INSERT INTO emp4(id,name,salary,commission_pct)
SELECT employee_id,last_name,salary,commission_pct
FROM employees
WHERE job_id LIKE %REP%;更新数据
语法
UPDATE table_name
SET column1 value1 [,column2 value2,...,columnn valuen ] [WHERE condition];这里记录一下更新多表数据
//下面的是SQL92语法
UPDATE table1 alias1,table2 alias2 SET COLUMN1 VALUE1 [,COLUMN2VALUE2,...,COLUMNNVALUEN]
WHERE JoinCondition;
//下面的是SQL99语法
UPDATE table1 alias1 [INNER | LEFT |RIGHT] JOIN table2 alias2 ON JoinCondition SET COLUMN1 VALUE1 [,COLUMN2VALUE2,...,COLUMNNVALUEN];删除数据
语法
DELETE FROM TableName [WHERE condition];删除多表记录
//SQL92语法
DELETE Table1_alias,Table2_alias FROM Table1 alias1,Table2 alias2 WHERE JoinCondition;//SQL99
DELETE Table1_alias,Table2_alias FROM Table1 alias1 [INNER | LEFT | RIGHT] JOIN Table2 alias2 ON JoinCondition;
MySQL8新特性:计算列
某一列是通过别的列计算得出的 如创建a表,d,e列,f列是由d列e列得出的
CREATE TABLE a(id int,d,int,e,int,f int GENERATED ALWAYS AS (de) VIRTUAL);第12章 MySQL数据类型 数据类型这东西记住几个常用的,不知道选用什么类型的时候去查,查多了就记住了我是这么干的 MySQL的数据类型
类型类型举例整数类型TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT浮点类型FLOAT、DOUBLE定点数类型DECIMAL位类型BIT日期时间类型YEAR、TIME、DATE、DATETIME、TIMESTAMP文本字符串类型CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT枚举类型ENUM集合类型SET二进制字符串类型BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB,JSON类型 JSON对象、JSON数组空间数据类型单值类型GEOMETRY、POINT、LINESTRING、POLYGON空间数据类型集合类型MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION
常见数据类型的属性:
MySQL关键字含义NULL数据列可包含NULL值NOT NULL数据列不允许包含NULL值DEFAULT默认值PRIMARY KEY主键AUTO_INCREMENT自动递增适用于整数类型UNSIGNED无符号CHARACTER SET name指定一个字符集
整数类型
类型介绍
整数类型字节有符号数取值范围无符号数取值范围TINYINT1-128~1270~255SMALLINT2-32768~327670~65535MEDIUMINT3-8388608~83886070~16777215INT、INTEGER4-2147483648~21474836470~4294967295BIGINT8-9223372036854775808~92233720368547758070~18446744073709551615
可选属性
M 显示宽度,M的取值范围是(0,255),一般需要配合ZEROFILL来使用,表示用0填满宽度,否则指定显示宽度无效,只对数据宽度小于显示宽度的生效.并且显示宽度与类型可以存储的值范围无关但是MySQL8以后的整数数据类型不推荐使用显示宽度属性 CREATE TABLE test_int2(
f1 INT,
f2 INT(5),
f3 INT(5) ZEROFILL
);UNSIGNED 无符号类型就是非负适合存储非负整数值比如年龄ZEROFILL 用0填充如果某列是ZEROFILL那么MySQL自动为当前列添加UNSIGNED属性,填充是在左边填充的(比如显示宽度为3数据是1在右边填充0会影响数据)必须和UNSIGNED ZEROFILL一起使用才有意义
适用场景
TINYINT 一般用于枚举数据比如系统设定取值范围很小且固定的场景。SMALLINT 可以用于较小范围的统计数据比如统计工厂的固定资产库存数量等。MEDIUMINT 用于较大整数的计算比如车站每日的客流量等。INT、INTEGER 取值范围足够大一般情况下不用考虑超限问题用得最多。比如商品编号。BIGINT 只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。
如何选择
主要考虑存储空间和可靠性的问题,但必须要确保数据不会超过取值范围在这个基础上再去考虑如何节省存储空间因为系统故障产生的成本远远超过增加几个字段存储空间所产生的成本.
浮点类型
可以处理小数应用范围比整数大
FLOAT:单精度浮点数DOUBLE双精度浮点数 REAL:默认是DOUBLE,如果启用REAL_AS_FLOAT那么就是FLOAT可以通过下面的语句来启用
SET sql_mode REAL_AS_FLOAT;Q1:FLOAT 和 DOUBLE 这两种数据类型的区别是啥呢 所占的字节数不同,一般情况下所占的字节数越大那么取值范围也大 Q2:为什么浮点数类型的无符号数取值范围只相当于有符号数取值范围的一半也就是只相当于 有符号数取值范围大于等于零的部分呢 MySQL 存储浮点数的格式为 符号(S) 、 尾数(M) 和 阶码(E) 。无论有没有符号,MySQL的浮点数都会存储符号位,浮点数的无符号数的取值范围就是把负数的部分丢掉即保留大于等于0的部分 精度说明
MySQL8.0.17之后的FLOAT(M,D)和DOUBLE(M,D)的用法官方文档不推荐使用且FLOAT和DOUBLE的UNSIGNED也不推荐使用了 DOUBLE(M,D) M:精度,整数位小数位D:小数位 取值范围: DM255,0D30 不指定的时候按照实际的精度(由硬件和操作系统决定) 超出了怎么办? 整数超过了范围就报错小数超出了范围分情况 情况1:若四舍五入后整数部分没有超出范围就只是警告,但能成功操作并四舍五入删除多余的小数位后保存情况二:若四舍五入后整数部分超出了范围MySQL报错,并拒绝存储 精度误差
他不够精准是浮点数都有这个问题
CREATE TABLE A(t double(5,2));
INSERT INTO A VALUES(1.12),(2.13),(3.1415);
# 6.3915
SELECT SUM(t)6.3915 FROM A; //这个的结果是0在对精度要求特别高的时候用定点数decimal不要用浮点数
定点类型
就只有一种decimal 存储方式以字符串的方式进行存储,不指定精度和标度的时候默认是DECIMAL(10,0),当精度超过了定点数类型的精度范围时MySQL同样会进行四舍五入处理。
位类型:BIT
存储的是二进制比如1010010
二进制字符串类型长度长度范围占用空间BIT(M)M1M64约为(M7)/8个字节
没有指定M就是一位,这里的M是指二进制的位数,使用SELECT命令查询位字段的时候可以用BIN()或HEX()函数进行读取BIN()以二进制的方式展现,HEX()以十六进制的方式展现。b0查询数据的时候直接查询存储的十进制数据的值
SELECT f10 FROM test;
--------
| f1 0 |
--------
| NULL |
| 23 |
--------
2 rows in set (0.00 sec)日期与时间类型
YEAR 类型通常用来表示年DATE 类型通常用来表示年、月、日TIME 类型通常用来表示时、分、秒DATETIME 类型通常用来表示年、月、日、时、分、秒TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒
类型名称字节日期格式最小值最大值YEAR年1YYYY或YY19012155TIME时间3HH:MM:SS-838:59:59838:59:59DATE日期3YYYY-MM-DD1000-01-019999-12-03DATETIME日期时间8YYYY-MM-DD HH:MM:SS1000-01-01 00:00:009999-12-31 23:59:59TIMESTAMP日期时间4YYYY-MM-DD HH:MM:SS1970-01-01 00:00:00 UTC2038-01-19 03:14:07UTC
TIME可以表示一个时间间隔且可以超过24个小时
YEAR类型
存储格式:
以4位字符串或数字格式表示YEAR类型格式为YYYY,最小值为1901最大值为2155以2为字符串格式表示YEAR类型最小值为00最大值为99 当取值为01~69时表示2001到2069 当取值为70~99时表示1970~1999 当取值整数的0或00添加时是0000年 取值是日期/字符串的’0’添加是2000年
但是MySQL5.5.27开始2位格式的YEAR不推荐使用YEAR的默认格式就是YYYY 从MySQL 8.0.19开始不推荐使用指定显示宽度的YEAR(4)数据类型。
CREATE TABLE test(
f1 YEAR,
f2 YEAR(4)
);
mysql DESC test;
-------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------
| f1 | year(4) | YES | | NULL | |
| f2 | year(4) | YES | | NULL | |
-------------------------------------------
2 rows in set (0.00 sec)
INSERT INTO test
VALUES(2020,2021);
mysql SELECT * FROM test;
------------
| f1 | f2 |
------------
| 2020 | 2021 |
------------
1 rows in set (0.00 sec)
INSERT INTO test
VALUES(45,71);
INSERT INTO test
VALUES(0,0);
mysql SELECT * FROM test;
------------
| f1 | f2 |
------------
| 2020 | 2021 |
| 2045 | 1971 |
| 0000 | 2000 |
------------
3 rows in set (0.00 sec)DATE类型
只存储日期没有时间部分格式位YYYY-MM-DD,需要三个字节的存储空间,在向DATE类型的字段插入数据时同样需要满足一定的格式条件。
以 YYYY-MM-DD格式或者 YYYYMMDD 格式表示的字符串日期其最小取值为1000-01-01最大取值为9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式。以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期此格式中年份为两位数值或字符串满足YEAR类型的格式条件为当年份取值为00到69时会被转化为2000到2069当年份取值为70到99时会被转化为1970到1999。使用 CURRENT_DATE() 或者 NOW() 函数会插入当前系统的日期。
CREATE TABLE test_date1(
f1 DATE
);
Query OK, 0 rows affected (0.13 sec)
INSERT INTO test_date1
VALUES (2020-10-01), (20201001),(20201001);
INSERT INTO test_date1
VALUES (00-01-01), (000101), (69-10-01), (691001), (70-01-01), (700101),
(99-01-01), (990101);INSERT INTO test_date1
VALUES (000301), (690301), (700301), (990301);
INSERT INTO test_date1
VALUES (CURRENT_DATE()), (NOW());
SELECT *
FROM test_date1;TIME类型
只表示时间需要3个字节的存储空间来存储TIME类型的数据可以使用HH:MM:SS格式来表示TIME类型,其中,HH表示小时MM表示分钟SS表示秒.
几种不同的格式
使用带有冒号的字符串,比如D HH:MM:SS,HH:MM:SS,HH:MM,D HH:MM,D HH,SS格式都可以,D表示天范围是0~34,若使用带D的字符串插入到TIME字段中D会转换为小时,计算格式:D*24HH;反之就是表示当天的时间比如12:10表示12:10:00不带有冒号的字符串或者数字格式为HHMMSS或者HHMMSS,插入一个非法的字符串或者数字的时候MySQL会自动转换为00:00:00进行存储1210把最右边的解析成秒表示00:12:10使用CURRENT_TIME()或者NOW()会插入当前系统时间
举例:
CREATE TABLE test_time1(
f1 TIME
);
Query OK, 0 rows affected (0.02 sec)
INSERT INTO test_time1
VALUES(2 12:30:29), (12:35:29), (12:40), (2 12:40),(1 05), (45);
INSERT INTO test_time1
VALUES (123520), (124011),(1210);
INSERT INTO test_time1
VALUES (NOW()), (CURRENT_TIME());
SELECT * FROM test_time1;DATETIME类型
在日期时间类型中所占的字节最大需要8个格式上就是DATE和TIME的组合表示为YYYY-MM-DD HH:MM:SS 满足如下格式条件:
以YYYY-MM-DD HH:MM:SS格式或者YYYYMMDDHHMMSS最小值为1000-01-01 00:00:00,最大值为9999-12-03 23:59:59 以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时会被转化为YYYY-MM-DD HH:MM:SS格式 以 YY-MM-DD HH:MM:SS 格式或者YYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时两位数的年份规则符合YEAR类型的规则00到69表示2000到206970到99表示1970到1999。使用函数 CURRENT_TIMESTAMP() 和 NOW() 可以向DATETIME类型的字段插入系统的当前日期和时间
CREATE TABLE test_datetime1(dt DATETIME);
INSERT INTO test_datetime1
VALUES (2021-01-01 06:50:30), (20210101065030);
INSERT INTO test_datetime1
VALUES (99-01-01 00:00:00), (990101000000), (20-01-01 00:00:00),
(200101000000);
INSERT INTO test_datetime1
VALUES (20200101000000), (200101000000), (19990101000000), (990101000000);
INSERT INTO test_datetime1
VALUES (CURRENT_TIMESTAMP()), (NOW());TIMESTAMP类型
和DATETIME类型相同都是YYYY-MM-DD HH:MM:SS4个字节的存储空间只能存储1970-01-01 00:00:00~2038-01-19 03:14:07 UTC之间的时间UTC表示世界统一时间
CREATE TABLE test_timestamp(ts TIMESTAMP);
INSERT INTO test_timestamp1
VALUES (1999-01-01 03:04:50), (19990101030405), (99-01-01 03:04:05),
(990101030405);
INSERT INTO test_timestamp1
VALUES (20200101000000), (200101000000);
INSERT INTO test_timestamp1
VALUES (CURRENT_TIMESTAMP()), (NOW());
#Incorrect datetime value
INSERT INTO test_timestamp1
VALUES (2038-01-20 03:14:07);TIMESTAMP和DATETIME的区别
TIMESTAMP的存储空间比较小表示的范围也比较小TIMESTAMP底层存储的是毫秒值距离1970-1-1 0:0:0毫秒的毫秒值两个日期比较大小或日期计算时TIMESTAMP更方便、更快TIMESTAMP和时区有关
CREATE TABLE temp_time(
d1 DATETIME,
d2 TIMESTAMP
);
INSERT INTO temp_time VALUES(2021-9-2 14:45:52,2021-9-2 14:45:52);
INSERT INTO temp_time VALUES(NOW(),NOW());
mysql SELECT * FROM temp_time;
------------------------------------------
| d1 | d2 |
------------------------------------------
| 2021-09-02 14:45:52 | 2021-09-02 14:45:52 |
| 2021-11-03 17:38:17 | 2021-11-03 17:38:17 |
------------------------------------------
2 rows in set (0.00 sec)
#修改当前的时区
SET time_zone 9:00;
mysql SELECT * FROM temp_time;
------------------------------------------
| d1 | d2 |
------------------------------------------
| 2021-09-02 14:45:52 | 2021-09-02 15:45:52 |
| 2021-11-03 17:38:17 | 2021-11-03 18:38:17 |
------------------------------------------
2 rows in set (0.00 sec)开发中经验
用的最多的是DATETIME。注册时间、商品发布时间用时间戳来存储的
SELECT UNIX_TIMESTAMP();文本字符串类型
MySQL中文本字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、LONGTEXT 、 ENUM 、 SET 等类型。
CHAR与VARCHAR类型
字符串(文本)类型特点长度长度范围占用存储空间CHAR(M)固定长度M0M255M个字节VARCHAR(M)可变长度M0M65535(实际长度1)个字节
CHAR类型
一般需要指定字符串长度不指定(M)就是1个字符实际长度小于CHAR类型声明的长度在右侧填充空格MySQL检索CHAR类型的数据CHAR类型的字段会去除尾部的空格定义CHAR类型字段时声明的字段长度即为CHAR类型字段所占的存储空间的字节数
CREATE TABLE test_char1(
c1 CHAR,//默认是CHAR(1)
c2 CHAR(5)
);
DESC test_char1;
INSERT INTO test_char1
VALUES(a,Tom);
SELECT c1,CONCAT(c2,***) FROM test_char1;
INSERT INTO test_char1(c2)
VALUES(a );
SELECT CHAR_LENGTH(c2)
FROM test_char1;VARCHAR类型
这个必须要指定长度检索该类型的数据的时候会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。
CREATE TABLE test_varchar1(
NAME VARCHAR #错误
);
#Column length too big for column NAME (max 21845);
CREATE TABLE test_varchar2(
NAME VARCHAR(65535) #错误
);
CREATE TABLE test_varchar3(
NAME VARCHAR(5)
);
INSERT INTO test_varchar3
VALUES(尚硅谷),(尚硅谷教育);
#Data too long for column NAME at row 1
INSERT INTO test_varchar3
VALUES(尚硅谷IT教育);如何选择?
类型特点空间上时间上使用场景CHAR(M)长度固定浪费存储空间效率高存储不大,速度要求高VARCHAR(M)长度可变节省存储空间效率低非CHAR的情况
存储比较短的数据,长度固定的数据用CHAR存储长度变化幅度比较大的数据的时候用VARCHAR具体存储引擎中的情况
MyISAM 数据存储引擎和数据列MyISAM数据表最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化从而使数据检索更快用空间换时间。MEMORY 存储引擎和数据列MEMORY数据表目前都使用固定长度的数据行存储因此无论使用CHAR或VARCHAR列都没有关系两者都是作为CHAR类型处理的。 InnoDB 存储引擎建议使用VARCHAR类型。因为对于InnoDB数据表内部的行存储格式并没有区分固定长度和可变长度列所有数据行都使用指向数据列值的头指针而且主要影响性能的因素 是数据行使用的存储总量由于char平均占用的空间多于varchar所以除了简短并且固定长度的 其他考虑varchar。这样节省空间对磁盘I/O和数据存储总量比较好。
TEXT类型
保存和查询数据的时候并没有删除TEXT类型的数据尾部的空格 四种类型TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT
文本字符串类型特点长度长度范围占用的存储空间TINYTEXT小文本、可变长度L0 L 255 L 2 个字节TEXT文本、可变长度L0 L 65535L 2 个字节MEDIUMTEXT中等文本、可变长度L0 L 16777215L 3 个字节LONGTEXT大文本、可变长度L0 L 4294967295相当于4GBL 4 个字节
这玩意不能作为主键只能采用CHAR(M)或者VARCHAR(M)
开发中经验
主要用来存储比较大的文本端
ENUM类型
设置字段值的时候ENUM类型只允许从成员中选取单个值所需要的存储空间由定义ENUM类型时指定的成员个数决定
文本字符串类型长度长度范围占用的存储空间ENUML1L655351或2个字节
当ENUM类型包含1~255个成员需要1个字节的存储空间当ENUM类型包含256~65535个成员时需要两个字节的存储空间
INSERT INTO test_enum
VALUES(春),(秋);
# 忽略大小写
INSERT INTO test_enum
VALUES(UNKNOW);
# 允许按照角标的方式获取指定索引位置的枚举值
INSERT INTO test_enum
VALUES(1),(3);
# Data truncated for column season at row 1
INSERT INTO test_enum
VALUES(ab);
# 当ENUM类型的字段没有声明为NOT NULL时插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);SET类型
SET表示一个字符串对象可以包含0个或多个成员成员个数的上限为64设置字段值时可以取取值范围内的 0 个或多个值。 SET类型包含的成员个数不同时占用的存储空间也是不同,
CREATE TABLE test_set(
s SET (A, B, C)
);
INSERT INTO test_set (s) VALUES (A), (A,B);
#插入重复的SET类型成员时MySQL会自动删除重复的成员
INSERT INTO test_set (s) VALUES (A,B,C,A);
#向SET类型的字段插入SET成员中不存在的值时MySQL会抛出错误。
INSERT INTO test_set (s) VALUES (A,B,C,D);
SELECT *
FROM test_set;CREATE TABLE temp_mul(
gender ENUM(男,女),
hobby SET(吃饭,睡觉,打豆豆,写代码);
INSERT INTO temp_mul VALUES(男,睡觉,打豆豆); #成功
# Data truncated for column gender at row 1
INSERT INTO temp_mul VALUES(男,女,睡觉,写代码); #失败
# Data truncated for column gender at row 1
INSERT INTO temp_mul VALUES(妖,睡觉,写代码);#失败
INSERT INTO temp_mul VALUES(男,睡觉,写代码,吃饭); #成功
);二进制字符串类型
主要是用来存储一些二进制数据比如图片的二进制数据MySQL中支持的二进制字符串类型主要包括BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB类型。
BINARY与VARBINARY类型
存储的都是二进制字符串 BINARY(M)为固定长度的二进制字符串,M表示最多能存储的字节数取值范围是0~255个字符未指定(M)就是1个字节不足数据宽度的时候在右边填充’\0’补齐指定长度 VARBINARY(M)可变还需要1或2个字节来存储数据的字节数总字节数不能超过行的字节长度限制65535.
CREATE TABLE test_binary1(
f1 BINARY,
f2 BINARY(3),
# f3 VARBINARY,
f4 VARBINARY(10)
);
INSERT INTO test_binary1(f1,f2)
VALUES(a,a);
INSERT INTO test_binary1(f1,f2)
VALUES(尚,尚);#失败
INSERT INTO test_binary1(f2,f4)
VALUES(ab,ab);
mysql SELECT LENGTH(f2),LENGTH(f4)
- FROM test_binary1;
------------------------
| LENGTH(f2) | LENGTH(f4) |
------------------------
| 3 | NULL |
| 3 | 2 |
------------------------
2 rows in set (0.00 sec)BLOB
二进制大对象容纳可变数量的数据BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB一般情况下会将图片、音频和视频文件存储到服务器的磁盘上,将路径存储到MySQL当中
TEXT和BLOB的注意事项
在执行大量的更新或删除操作的时候会在数据表中留下很大的空洞所以为了提高性能建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理对大文本字段进行模糊查询MySQL提供了前缀索引。把BLOB或TEXT列分离到单独的表中会减少主表中的碎片
JSON类型
JSONJavaScript Object Notation是一种轻量级的 数据交换格式。
CREATE TABLE test_json(
js json
);
INSERT INTO test_json (js)
VALUES ({name:songhk, age:18, address:{province:beijing,
city:beijing}});
mysql SELECT *
- FROM test_json;当需要检索JSON类型的字段中数据的某个具体值时可以使用-或-符号
SELECT js - $.name AS NAME,js - $.age AS age ,js - $.address.province
AS province, js - $.address.city AS city FROM test_json;空间类型 这个我实在是记录不下去了先水一下 空间类型扩展支持地理特征的生成、存储和分析,存储世界上具有位置的任何东西。MySQL使用Geometry几何表示所有地理特征
第13章 约束
一些概念 数据完整性(Data Integriry)是指数据的精确性(Accuracy)和可靠性(Reliability)。 约束主要是为了防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的主要是从四个方面考虑
实体完整性Entity Integrity 同一个表中确保记录的唯一性域完整性Domain Integrity 对于一些字段取值应该在值域内如性别只能在男女两者之间选。引用完整性Referential Integrity 例如员工所在部门在部门表中要能找到这个部门用户自定义完整性User-defined Integrity 例如密码不能为空邮箱不能为空
什么是约束
这个了解一下 约束是表级的强制规定。 可以在创建表时规定约束通过 CREATE TABLE 语句或者在表创建之后通过 ALTER TABLE 语句规定 约束。
分类
看导图可能会直接点图后面有文字版本
根据约束数据列的限制约束可分为
单列约束每个约束只约束一列多列约束每个约束可约束多列数据
根据约束的作用范围约束可分为
列级约束只能作用在一个列上跟在列的定义后面表级约束可以作用在多个列上不与列一起而是单独定义
根据约束起的作用约束可分为
NOT NULL 非空约束规定某个字段不能为空UNIQUE 唯一约束规定某个字段在整个表中是唯一的PRIMARY KEY 主键(非空且唯一)约束FOREIGN KEY 外键约束CHECK 检查约束DEFAULT 默认值约束
查看某个表已有的约束
# information_schema数据库名
# table_constraint 专门存储各个表的约束
SELECT * FROM information_schema.table_constraints
WHERE table_name 表名字;NOT NULL 非空约束
限制某个字段的值不能为空,
特点
所有的数据类型都可以是NULL只能出现在列上不能组合非空可以多列限定非空 注意空字符串和0都不等于NULL
添加非空约束
建表的时候
CREATE TABLE table_name(字段名 数据类型,字段名 数据类型 NOT NULL
);建表后
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;删除非空约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NULL;
# 去掉NOT NULL等同于NULL
# 或者
ALTER TABLE 表名称 MODIFY 字段名 数据类型;//默认就是NULL唯一性约束 确保某个字段的值不能重复,但是可以出现多个NULL 特点
可以有多个唯一约束可以是单列唯一也可以是组合唯一唯一约束允许列值为空若不指定约束名就是列名1MySQL会给唯一约束的列上默认创建一个唯一索引
添加唯一约束
建表时
CREATE TABLE 表名(字段名 数据类型,字段名 数据类型 UNIQUE,字段名 数据类型 UNIQUE KEY,
);
CREATE TABLE 表名(字段名 数据类型,[CONSTRAINT 约束名] UNIQUE KEY(字段名)
);建表后
# 方式1:
ALTER TABLE 表名称 ADD UNIQUE KEY(字段列表);
# 方式2:
ALTER TANLE 表名称 MODIFY 字段名 字段类型 UNIQUE;删除唯一约束
添加唯一性约束的列上会自动创建唯一索引删除唯一约束只能通过删除唯一索引名的方式删除若未指定索引名称若是单列就是列名组合列则默认和排在第一个的列名相同可自定义约束名字
ALTER TABLE table_name DROP INDEX index_name;
# 查看表的索引
SHOW INDEX FROM table_name;PRIMARY KEY约束
用来标识表中的一行记录
特点
等同于 UNIQUENOT NULL主键名总是为PRIMARY主键约束对应着表中的一列或多列若是多列则这些列不允许为空值,组合的值不允许重复一个表只能有一个主键约束创建主键约束时默认会在所在的列或列组合上建立对应的主键索引若是删除主键约束主键约束对应的索引就自动删除别修改主键字段的值可能会破坏数据的完整性
添加
建表时指定主键约束
create table 表名称(
字段名 数据类型 primary key, #列级模式
字段名 数据类型,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] primary key(字段列表) #表级模式
);建表后添加主键约束
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); #字段列表可以是一个字段也可以是多个字段如果是多个字段的话是复合主键删除
一般情况下不会这么干
ALTER TABLE 表名称 DROP PRIMARY KEY;自增列 AUTO_INCREMENT
让指定字段的值自增
特点
最多有一个可顺序增长可指定初始值自增列约束的列必须为键列主键列唯一键列并且列的数据类型必须是整数类型若不指定值就是在最大值的基础上加一若指定就按指定的来
添加
建表时
create table 表名称(
字段名 数据类型 primary key auto_increment,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 unique key auto_increment,
字段名 数据类型 not null default 默认值,,
primary key(字段名)
);建表后
ALTER TABLE 表名 MODIFY 字段名 数据类型 AUTO_INCREMENT;删除
ALTER TABLE 表名 MODIFY 字段名 数据类型;#去掉了auto_increment了MySQL8的新特性 自增变量持久化
这个举例说明比较好点 下面的专业点 在MySQL 5.7系统中对于自增主键的分配规则是由InnoDB数据字典 内部一个 计数器 来决定的而该计数器只在 内存中维护 并不会持久化到磁盘中。当数据库重启时该计数器会被初始化。 这个是我自己复述的,有点啰嗦 MySQL8以前的版本是把当前自增列的最大值放在内存的一块区域每次自增的时候去读取那块区域的值在此基础上加一若MySQL服务重启内存被操作系统回收掉了那么再次插入记录自增列的值就是插入之前的最大值1MySQL8以后的版本是把自增列的最大值好像是放在文件里这样重启服务也不碍事 看例子吧:
CREATE TABLE test(
id INT PRIMARY KEY AUTO_INCREMENT;
);
INSERT INTO test VALUES(0),(0),(0);
# 查询数据
/*
| id |
----
| 1 |
| 2 |
| 3 |
*/
#删除id3的记录
DELETE FROM test WHERE id 3;
# 再次插入空值
INSERT INTO test VALUES (0);
# 再次查询
/*
| id |
----
| 1 |
| 2 |
| 4 |
*/
# 删除id3的记录再次插入的时候并不是在现有id列的最大值的基础上实现1
# 删除id 4的
DELETE FROM test WHERE id 4;
#重启服务,插入空值
#Windows 是net restart MySQL80
#linux是 systemctl restart mysql
INSERT INTO test VALUES(0);
# 再次查询
/*
| id |
----
| 1 |
| 2 |
| 3 |
*/MySQL8以后的不一样还是按照以上的操作但是最后一步的结果是
| id |
----
| 1 |
| 2 |
| 3 |
| 5 |自增变量持久化了 MySQL 8.0将自增主键的计数器持久化到 重做日志 中。每次计数器发生改变都会将其写入重做日志 中。如果数据库重启InnoDB会根据重做日志中的信息来初始化计数器的内存值。
FOREIGN KEY 约束 这个阿里巴巴的文档中写的是不要使用这个但是你要从应用的层面去完成FOREIGN KEY的效果 限定某个表的字段的引用完整性这个举个例子比较好点,在学校填一些信息的时候会收集你是那个班的你不能填一个不存在的班级。
主表和从表/父表和子表
主表(父表):被引用的表被参考的表就是上面例子的班级名称 从表(子表):引用别人的表参考的表就是上面例子中的存放收集信息的表
特点
从表的外键列必须引用主表的主键或唯一约束的列若不指定约束名会自动产生一个外键名(如:user_ibfk_1)可自定义约束名创建表时指定外键约束先主后从先创建主表再创建从表删表时先删除从表再删除主表主表的记录被从表引用的时候主表的记录不允许删除若要删除先删除从表中依赖该记录的数据再删除主表从表中指定外键约束并且一个表可以建立多个外键约束从表的外键列和主表被参照的列的名字可以不相同但是数据类型必须一样。创建外检约束时系统默认会在所在的列上建立对应的普通索引删除外键约束后必须手动删除对应的索引
添加
建表的时候
create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型
);
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT 外键约束名称] FOREIGN KEY从表的某个字段) references 主表名(被参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列建表后 通常表与表之间的关联都是提前设计好的若创建表后要修改表的设计按照如下来
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用
字段) [on update xx][on delete xx];常见问题
失败不是键列
create table dept(
did int , #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did)
);
#ERROR 1215 (HY000): Cannot add foreign key constraint 原因是dept的did不是键列失败数据类型不一致
create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid char, #员工所在的部门
foreign key (deptid) references dept(did)
);
#ERROR 1215 (HY000): Cannot add foreign key constraint 原因是从表的deptid字段和主表的did字段的数据类型不一致并且要它俩的逻辑意义一致约束等级
Cascade方式 在父表上update/delete记录时同步update/delete掉子表的匹配记录就是级联Set null方式 在父表上update/delete记录时将子表上匹配记录的列设为null但是要注意子表的外键列不能为not nullNo action方式 如果子表中有匹配的记录则不允许对父表对应候选键进行update/delete操作Restrict方式 同no action 都是立即检查外键约束Set default方式 在可视化工具SQLyog中可能显示空白父表有变更时子表将外键列设置成一个默认的值但Innodb不能识别 默认是Restrict方式对于外键约束最好采用ON UPDATE CASCADE ON DELETE RESTRICT的方式,ON UPDATE CASCADE ON DELETE RESTRICT表示更新的时候采用Cascade方式删除的时候采用Restrict方式。 可以把ON UPDATE CASECADE的CASCADE换成上面的CascadeSet nullNo actionRestrictSet default. 这里我不会写的很详细
删除外键约束
检查约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name 表名称
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;查看索引名和删除索引。这里只能手动删除
SHOW INDEX FROM 表名称; //查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;开发场景
若两个表之间有关系一定要建立外键约束吗 不是建不建外键约束有何区别 建外键约束操作会在语法层受到限制这里的操作主要是指创建表、删除表、添加、修改、删除下面的操作指的意思和这个一样 不建外键约束操作不受限制但要保证数据的引用完整性这个只能依靠程序员的自觉了或者用程序来实现限定的效果。建不建外键约束和查询有何关系 没有
阿里开发规范
不得用外键约束一切外键概念必须在应用层解决。 说明学生表中的 student_id 是主键那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id同时触发成绩表中的 student_id 更新即为级联更新。外键与级联更新适用于 单 机低并发 不适合 分布式 、 高并发集群 级联更新是强阻塞存在数据库 更新风暴 的风险外键影数据库的 插入速度 。 强阻塞就是更新的太多了要排队这是我的理解 CHECK约束
检查指定字段值是否符合某某要求 MySQL5.7不支持但是你写CHECK关键字不报错但是没效果。MySQL8支持 举例
CREATE TABLE user(
id int primary key auto_increment,
username char(10) not null,
gender char(1) not null check (男 or 女)
# gender char(1) not null check ( gender in (男 or 女))这样也可以
);DEFAULT约束
给指定字段指定默认值
添加
建表时
create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
create table 表名称(
再举例
字段名 数据类型 default 默认值 ,
字段名 数据类型 not null default 默认值,
字段名 数据类型 not null default 默认值,
primary key(字段名),
unique key(字段名)
);
# 说明默认值约束一般不在唯一键和主键列上加建表后
alter table 表名称 modify 字段名 数据类型 default 默认值;
#如果这个字段原来有非空约束你还保留非空约束那么在加默认值约束时还得保留非空约束否则非空约束就被
删除了
#同理在给某个字段加非空约束也一样如果这个字段原来有默认值约束你想保留也要在modify语句中保留默
认值约束否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;删除
alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束也不保留非空约束
alter table 表名称 modify 字段名 数据类型 not null; #删除默认值约束保留非空约束一些题目
为何建表的时加NOT NULL DEFAULT 或DEFAULT 0 不想让表中出现null值为何不想要null值
不好比较null参与运算结果都是null只能用is null和is not null效率不高影响索引效果
带AUTP_INCREMENT约束的字段值是从1开始的 默认初始值是从1开始的但是也可以指定每个表都可以任意选择存储引擎吗 不能FOREIGN KEY不能跨引擎使用主表采用的是Innodb,从表采用的是MyISAM那就不行
