数据类型
数据类型主要给列上添加完整性约束。开发者设置列的数据类型后，DBMS会对添加的数据进行合法性验证，如果不合法则报错，拒绝添加
数值型
-
5中整型 tinyint、smallint、mediumint、int和bigint，主要区别就是取值范围不停，还可以在类型前添加一个限制词unsigned，不允许添加负数
-
三种浮点型：不能精确存放float和double，可以精确存放decimal和numeric
| 类型名称 | 说明 |
|---|---|
| tinyint | 1B，取值范围-128-127 |
| smallint | 2B，取值范围为-32768-32767 |
| mediumint | 3B，取值范围为-838608-8388607 |
| int | 4B，取值范围为2e9 |
| bigint | 8B,取值范围为9e18 |
| float | 4B，单精度浮点型，取值范围为3.4e38 |
| double | 8B，双精度浮点型，取值范围为1.7e308 |
| decimal和numeric | 采用顶点存放浮点数，具体字节数取决于定义时设置的参数 |
unsigned设置列为无符号型，只能存放大于等于0的数据，没有附属。当使用无符号类型时取值范围由于没有复数部分，从而导致上限扩大一倍
create table t(id int unsigned);
mysql> insert into tt value(-10); -- 不允许存放负数
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into tt value(255); -- 存储数据的上限扩大一倍
Query OK, 1 row affected (0.01 sec)可以再类型后添加括号，其中包含一个正整数，例如int(5)，这里的含义并不是要求只能存放5位长度的整数；含义是当进行查询时自动使用空格填充到5个长，如果真实数据长度大于5，则按实际输出
mysql> create table t3(id int(2));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t3 values(9999);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t3 values(9);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t3;
+------+
| id |
+------+
| 9999 |
| 9 |
+------+
2 rows in set (0.00 sec)decimal和numeric作为字符串存储浮点数，可以实现浮点数的精确存放，并不是float和double中使用二进制浮点数存储。使用方法numeric(总位宽，小数位数)，小数位数必须小于等于总位宽；小数位数最大值30，总位宽最大值65，注意可能存储的数据会超出范围，其中符号位和小数点不占位宽
mysql> create table t1(id numeric (5,3)); -- 总共5位，小数位3位
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values(99.999);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(9.9999); -- 小数位数多余则自动四舍五入
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into t1 values(-99.999); -- 位宽统计符号位不算
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(100); -- 整数超出范围报错
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into t1 values(9.12346); -- 按小数位第三位紧邻的第四位四舍五入
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t1;
+---------+
| id |
+---------+
| 99.999 |
| 10.000 |
| -99.999 |
| 9.123 |
+---------+
4 rows in set (0.00 sec)int和numeric
-
int类型不能保存小数位，存储小数时会自动进行四舍五入
mysql> create table t2(id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t2 values(10.5);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t2;
+------+
| id |
+------+
| 11 |
+------+
1 row in set (0.00 sec)-
numeric 可以保存小数位，如果小数位为0，可以模拟得到int类型的存储效果。采用实际存储方式为字符串，查询效率远低于int
mysql> create table t3(id numeric);
Query OK, 0 rows affected (0.01 sec)
mysql> desc t3;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | decimal(10,0) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t3 values(11);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t3;
+------+
| id |
+------+
| 11 |
+------+
1 row in set (0.00 sec)
mysql> insert into t3 values(6.0);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t3;
+------+
| id |
+------+
| 11 |
| 6 |
+------+
2 rows in set (0.00 sec)数值列的扩展属性
-
auto_incerment一般用于主键，可以实现该列的自动生成连续整数值
mysql> create table t4(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t4 values(null,'王五'); -- 插入数据时不指定插入的值或者设置插入值为null则会自动添加一个自增值
Query OK, 1 row affected (0.01 sec)
mysql> insert into t4 values(10,'张三'); -- 如果插入数据时不指定对应的值，则自增长max(id+1);如果指定对应的值，则自增效果失效
Query OK, 1 row affected (0.01 sec)
mysql> insert into t4(name) values('李四');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t4;
+----+------+
| id | name |
+----+------+
| 1 | 王五 |
| 10 | 张三 |
| 11 | 李四 |
+----+------+
3 rows in set (0.01 sec)-
zerofill如果查询显示时，实际数据小于指定位宽，则自动添加0值
mysql> create table t5(id int(5) zerofill);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t5 values(20);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t5 values(14567);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t5;
+-------+
| id |
+-------+
| 00020 |
| 14567 |
+-------+
2 rows in set (0.00 sec)-
default用于设置默认值
mysql> create table t6(id int default 0,name varchar(10));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t6 values(02,'王五'); -- 指定对应的数据，则default无效
Query OK, 1 row affected (0.01 sec)
mysql> insert into t6 values(null,'李四'); -- 指定对应的数据，则default无效，即使设置的值为null
Query OK, 1 row affected (0.01 sec)
mysql> insert into t6(name) values('张三'); -- 只有不针对这个列进行数据插入时default才能生效
Query OK, 1 row affected (0.01 sec)
mysql> select * from t6;
+------+------+
| id | name |
+------+------+
| 2 | 王五 |
| NULL | 李四 |
| 0 | 张三 |
+------+------+
3 rows in set (0.00 sec)nul的意思为未知的数据，它既不是空字符段，不是任何一个具体的值；不能把任何值于一个null值进行比较。因为null的意思就是不确定的值。真正的null值比较必须使用特殊的运算符is null 或者is not null
mysql> select null = null; +-------------+ | null = null | +-------------+ | NULL | +-------------+ 1 row in set (0.00 sec) mysql> select null != null; +--------------+ | null != null | +--------------+ | NULL | +--------------+ 1 row in set (0.00 sec) mysql> select null is not null; +------------------+ | null is not null | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec) mysql> select null is null; +--------------+ | null is null | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)
完整性约束
DBMS为了保证存储的数据都是完整有效的，避免存放垃圾数据，所以提供了针对插入的数据进行检查
-
开发人员指定对应的规则，由DBMS负责检查，如果试图插入不合法数据，则会自动报错，拒绝插入
-
实际有六种约束，可以分为3大类完整性约束。
-
实体完整性，由主键约束实现
-
参照完整性，由外界约束实现
-
用户自定义完整性，由其他四种约束实现
-
-
实际开发中除了主键约束外，其他约束一概不添加。其他的数据检查一般依赖应用程序实现，为了代码测试方便
1.非空约束，指定列值不允许为空
create table t1(id int not null); -- 不与怒id列值为null
create table t2(id int null); -- 允许id列值为null
create table t3(id int); -- 允许id列值为null2.默认约束default
一般默认约束经常和非空约束一起使用，当不插入数据时，默认值生效
create table t1(id int not null default 0);3.主键约束，又叫做实体完整性约束，不允许数据重复存储
-
主键可以唯一标识一行数据
-
主键约束含义是非空、唯一
create table t1 (id int primary key, .....);
create table t2(
id int not null, ....
primary key(id) -- 注意这里的主键约束允许使用复合主键，多个列构成的主键
);4.唯一约束
5.外键约束，又叫做参照完整性
6.检查约束
DDL表操作
创建操作
create table tb_student(
id bigint primary key auto_increment,
name varchar(10) not null comment '姓名',
birth date,
sex boolean default 1,
salary numeric(8,2)
)engine=innodb default charset utf8;快速创建一个表和另外一个表的相同结构，只能克隆表结构，不能克隆数据
create table 新表名称 like 已经存在的旧表名称;l修改表的结构，语法规则为alter table 表名称 add/drop/modify 列名称【类型 约束】
alter table tb_student add class_name varchar(20) default '软件19'; -- 新增列
alter table tb_student drop column salary; -- 删除指定列
alter table tb_student modify class_name int; -- 修改列定义时，要求已经存在的数据必须符合规则，否则修改失败。modify 一般用于修改的数据类型个长度，但是如果没有数据必须和目标类型匹配，否则修改失败结果如下
mysql> desc tb_student;
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| birth | date | YES | | NULL | |
| sex | tinyint(1) | YES | | 1 | |
| salary | decimal(8,2) | YES | | NULL | |
+--------+--------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)
mysql> alter table tb_student add class_name varchar(20) default '软件19';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table tb_student drop column salary;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_student;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| birth | date | YES | | NULL | |
| sex | tinyint(1) | YES | | 1 | |
| class_name | varchar(20) | YES | | 软件19 | |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> alter table tb_student modify class_name int;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_student;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| birth | date | YES | | NULL | |
| sex | tinyint(1) | YES | | 1 | |
| class_name | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)修改列名称
alter table 表名称 change 旧有列名称 新列名称 类型 约束删除表drop table 表名称
drop table tb_student;
drop table if exists tb_student;查看当前数据库中的所有表
show tables;查看表结构
desc 表名称;查看创建表的sql语句
mysql> show create table tb_student;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_student | CREATE TABLE `tb_student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL COMMENT '姓名',
`birth` date DEFAULT NULL,
`sex` tinyint(1) DEFAULT '1',
`class_name` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)练习题1：创建一个学生表，要求学号字符型，长度为5，不能为空；姓名字符型长度20，不允许重复，性别字符串型长度为1，邮政编码为6，年龄整数，系别字符串型长度15
-- 一般针对一个应用创建一个数据库
create database if not exists stu_manager default character set utf8;
-- 切换数据库
use stu_manager;
-- 创建表
create table if not exists tb_student(
son varchar(5) not null,
sname varchar(20) unique,
sex char(1) default '男',
postcode char(6),
age int,
dept varchar(15)
)engine=innodb default charset utf8;向tb_student表中添加一个入学时间的列
-
注意修改表之前，表中已经有数据，则新增列的值为null，除非设置了default默认约束
alter table tb_student add come date;将年龄字段修改为小整形
-
注意：修改表之前如果表中已经存有数据，修改不能破坏已有数据
alter table tb_student modify age tinyint(3);删除姓名字段
alter table tb_student drop column name;练习题2：
创建一个学生选课表，由学号、课程号、成绩组成，请合理定义主键
-
主键=非空+唯一
-
一个表中只能有一个主键，因为主键会影响数据的具体存放位置【索引问题】
-
一个主键可以是由多个列构成。复合主键是由多个列构成的一个主键
create table if not exists tb_choice(
sno char(5) comment '学号',
con int comment '课程号',
score int,
primary key(son,con) -- 定义复合主键，con和son列上并没有添加not null约束，但是con和son都不允许为null;但是唯一是指两个属性组合的唯一，并不是其中列的唯一
)engine=innodb default charset uft8;表操作DML/DQL
-
DML数据操纵语言，用于对数据库表中的记录进行更新操作，插入inset、修改update、删除delete
-
DQL数据查询语言，用于对数据库表中的记录进行查询操作，select
样例表
cr
eate table tb_users(
id bigint primary key auto_increment,
username varchar(20) not null,
password varchar(24) not null,
birth date,
sex boolean default 1,
score numeric(8,2)
)engine=innodb default charset utf8;增加数据
语法1：insert into 表名称 values(值1,值2,...)用于针对表中的每个列指定对应的值，例如insert into tb_users values(null,'yan','123456','2001-03-03',1,1234.56);注意values后面的括号的值和列一一对应，注意数据类型
语法二：insert into 表名称(列名1,列名2,...) values(值1,值2,...) 人为指定列插入数据，注意两 个括号一一对应，一般用于不是每个列都需要插入数据时使用。例如 insert into tb_users(username,password,birth) values('yanjun','123456','1989-2-3') 。如果没有出现 的列而且列上没有default约束，则自动插入值null；如果有default约束则插入默认值
注意：
-
值于字段必须对应，包括个数和数据类型
-
值必须再合理的字段类型范围内
-
varchar、char和日期类型必须使用单引号包裹
-
如果没有任何限制，控制允许插入
-
-
没有指定列名称时，必须给出完整数据，而且对应顺序
-
优化写法：指定列名称。无需遵守定义表时列的顺序
-
批量插入查询出的数据
-
insert into table(列名1，列名2,...) select 列1，列2,... from table2;
-
-
将一个表的查询出的内容插入创建的新表中。不仅克隆表结构，同时克隆表中的数据。单数约束会有丢失问题
-
create table newTable as select * from oslTable;
-
修改数据
语法1： update 表名称 set 列名1=值1,列名2=值2,... 。修改表中所有行的指定列对应的值，等号后面 可以是一个表达式进行计算。例如score新增10%写法 update tb_users set score=score*1.1 允许同时修改多个列： update tb_users set score=500, sex=0 语法2： update 表名称 set 列名1=值1,列名2=值2,... where 条件用于修改表中满足条件的行指定列 的值；例如1989年之前的用户积分新增10%， update tb_users set score=score*1.1 where year(birth)<1989 ，year是系统函数，用于获取指定列的年份值
删除数据
语法1： delete from 表名称; 删除表中所有数据。例如delete from tb_users;
语法2：delete from 表名称 where 条件;删除表中满足条件的所有行。例如删除1990年的所有女 生， delete from tb_users where sex=0 and year(birth)=1990
整表删除操作
方法1： delete from tb_student;可以删除表中的所有数据，属于DML语句，所以支持事务，支持撤 销回滚操作，但是大量的删除数据时需要记录日志，所以执行效率很低。优势在于可以反悔，缺点在于 记录日志需要时空代价
方法2： truncate table tb_student; 表的截断操作，可以删除表中的所有数据，属于DDL语句，没 有事务，不会记录操作日志，所以大量删除数据时执行效率高。优势是执行效率，缺点在于没有反悔机会。底层原理就是创建一个相同的空表
逻辑删除和物理删除
所谓的逻辑删除就是从表中直接删除数据；逻辑删除类似于回收站，只是给某行数据上添加一个删除标志，表示该行数据已经删除了
逻辑删除的具体实现：
1.修改表tb_users新增一个额外列，用于表示该行数据是否已经删除
alter table tb_users add deleted boolean default 0; -- 0表示没有删除2.逻辑删除，并没有真实数据删除，只是修改一个表示列的值
update tb_users set deleted =1 where id = 5;-- 删除五号用户信息 delete from tb_users where id = 5;物理删除3.真正查询时为了避免查到已经删除的数据，所以需要额外的查询条件
select * from tb_users where deleted=0; -- 只查询deleted值为0的行，不要已经标识删除的函数deleted=1
3211
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
