数据库初级在这里!!https://blog.csdn.net/m0_60563771/article/details/123638612?spm=1001.2014.3001.5501
目录
数据库的完整性
为了保证存放到数据库中的数据是有效的,即数据的有效性和准确性,我们需要在创建表时给数据约束。
完整性的分类:
- 实体完整性(行完整性):
- 域完整性(列完整性):
- 引用完整性(关联表完整性):
主键约束:primary key
唯一约束:unique [key]
非空约束:not null
默认约束:default
自动增长:auto_increment
外键约束: foreign key
这些约束应该在创建表的时候设置 ,多个约束之间用空格隔开
eg:studentno int primary key auto_increment,(其中primary key 是主键约束,auto_increament 是自动增长约束)
一、实体完整性
实体:即表中的一行(一条记录)代表一个实体(entity)
实体完整性的作用:标识每一行数据不重复。
约束类型:
*主键约束(primary key)*
*唯一约束(unique)*
*自动增长列(auto_increment)*
一、1:主键约束(primary key)
注:每个表中要有一个主键。
特点:数据唯一,且不能为null(比如说,不能用姓名作为主键,因为姓名可能重复,但是可以用学号,学号不会重复)
添加方式1:create table 表名(列名1 列数据类型1 主键约束 , 列名2 数据类型2等);(常用)
添加方式2: create table 表名(列名1 列数据类型1,列名2 列数据类型2,列名n 列数据类型n,primary key(主键1,主键2) );(这种方式优势在于,可以创建联合主键:在一个表中有多个主键约束)
添加方式3:创建好表后用alter去添加主键:(不常用)
eg:CREATE TABLE student( id int, name varchar(50) );
ALTER TABLE student ADD PRIMARY KEY (id);
二、唯一约束(unique)
特点:数据不能重复,和主键约束很像。不同的是中间约束不允许为空,唯一约束允许一次为空。
create table 表名(列名1 列数据类型1 约束1 , 列名2 数据类型2 unique );unique 为唯一约束
三、自动增长列(auto_increment)(只能用于整数数据类型)
给某列加自增长约束,此列的值会自动增加,不用手动设置。
eg:给主键添加自增长的数值:
create table student2(id int primary key auto_increment,Name varchar(50));
查询结果:自动加了1、2、3、4(abc那一条我执行了两次) ,自己递增。
二、域完整性
限制此单元格的数据准确,比如,邮箱的格式是必须正确的,人类的性别只能是男和女。
域完整性约束:数据类型 非空约束(not null) 默认值约束(default)
补充:check约束(mysql不支持)check(sex='男' or sex='女'
一、数据类型来约束
数值类型(了解)
| 类型 | 大小 | 范围(有符号) | 范围(无符号 ) | 用途 |
| ------------- | ---------------- | ----------------------- | ---------------------- | ---------- |
| tinyint | 1 字节 | (-128,127) | (0,255) | 小整数值 |
| smallint | 2 字节 | (-32 768,32 767) | (0,65 535) | 大整数值 |
| mediumint | 3 字节 | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
| INT| 4 字节 | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
| bigint | 8 字节 | (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) | (0,18 446 744
073 709 551 615) | 极大整数值 |
| float | 4 字节 | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402
823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度浮点数值 |
| double | 8 字节 | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,
(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4
E-308,1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |
日期类型:
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性
| 类型 | 大小(字节) | 范围 | 格式 | 用途 |
| --------- | ------ | ---------------------------------------- | ------------------- | ------------ |
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日
期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 *2147483647* 秒,北京时间 *2038-1-
19 11:14:07*,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和
时间值,时间戳 ,当更新数据的时候自动添加更新时间
字符串类型:
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET
| 类型 | 大小 | 用途 |
| ---------- | ----------------- | ------------------ |
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
CHAR和VARCHAR类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY和VARBINARY类类似于CHAR和VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
BLOB(0、1用来存音频视频等)是一个二进制大对象,可以容纳可变数量的数据。有4种BLOB类型:TINYBLOB、BLOB、
MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
有4种TEXT类型:TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型,有相同的
最大长度和存储需求。
二、非空约束 not null(重点)
如果我们给的数据,没有添加name则会报错:
添加后,就不会报错:
三、默认值约束 default(重点)
如果用户不赋值,那么系统给一个默认值:
student4 id为主键约束与自增长约束,姓名为非空约束,性别为默认值约束,默认为女
下面我们添加数据时,只添加了姓名,id自增长自动赋值,性别默认为女。
三、引用完整性
前面写到的实体完整性和域完整性都是关注单表存储的完整性。而引用完整性是关注表之间的完整性。
我们在开发中一般也会用到多张有相互关联的表。比如现有两张表:
表1为学生信息表,表中有:学生姓名,性别,年龄,学号(设为主键列)。
表2为学生成绩表,表中有:分数id值,学生id,科目,分数。
显然,这两张表中的数据是一一对应的关系。我们现在要将两张表建立关联。为了保证一一对应,我们应将表1的主键值引用成表2的列值。于是,我们让表2中的学生id这一列存入表1的学号,这样一个学号就对应表二中的一组数据。例如表2的stuid,第一行为2,对应表1就是“小红”的成绩!
但有一个问题:如果我们把表2的stuid改为5 或者 6 其实发现也不会报错:
其实这就是出现了引用完整性的问题。那么为了保证引用完整性,落实上面的操作,就要用到外键约束: foreign key
外键列:存其他表的主键值的列
添加方式1:
创建完表后,修改添加:
alter table 被修改的表名 add constraint 自定义的外键约束名称 foreign key(外键列)references 关联表名(关联表的主键列)
此时,如果再在score的 stuid中,将数据改为student5的主键列中没有的数据时,就会报错:
四、navicat添加约束
1、新建一张表,填入列名
2、添加主键:点击最后一列,有一个钥匙,则添加为了主键列,注意主键列不能为空,所以将“允许空值”选掉!
3、添加自增长约束:点击“id”列名,点击下方自动递增
4、添加唯一约束:点击索引,自己编写唯一约束的名,点击栏位,选择要添加的列,索引类型选择unique即可。
5、添加默认值:点击要添加默认值的列,然后点击下面“默认”输入即可:
6、添加外键列:现有两张表,表1为“哈哈哈”,内容如下:
表2为“哈哈的关联表”,内容如下:
添加外键列的步骤如下:
1、右键单击要添加外键列的表1,点设计表
2、在要添加外键列的表1中添加一个新的列:fkid
3、点击外键,填写相应的数据,保存即可(后两列,是保存后自动添加的,不用手动写)
此时,再点开表1,外键列就有可以选择的数据了,表示已有外键约束。
五、多表查询
多表查询依然依靠外键约束来维护多表之间的关系。
多表的关系:
一对多/多对一(常见):一个部门可以有多个员工(一对多),多个员工属于一个部门(多对一)。
此种情况的建表原则是:在多的一方创建一个字段,字段作为外键指向一的一方的主键。eg:上面的“哈哈哈”就可以看做是多的一方,而“哈哈的关联表”就是一的一方。
多对多:老师可以有多个学生,学生也可以有多个老师。
多对多的建表原则:需要创建第三张表,中间表至少包含两个字段,这两个字段分别作为外键指向其余两张表的主键。
eg:现有一张学生表:
一张老师表:
为了关联上面两张表的多对多关系,我们需要创建第三张表,取名为middle:
接下来我们再向表中添加数据:stuid代表学生,teaid代表老师。
所以此表意思为:学生1小白,学了老师1、2、3的课,学生2小红学了老师1、3的课,以此类推。
那么反过来,也代表老师2魏老师有两个学生,分别为1小白和4小溪。
我们在此案例中并没有创建外键约束,因为外键约束的优点是保证了数据的有效性,但是缺点是性能会降低。而开发过程中,数据量较大,使用外键列性能会有很大程度的降低。所以一般不建议使用外键约束,并且由于外键列的值是由我们程序员给定的,所以我们很清楚哪些值是正确的,哪些是错误的。所以可以不用创建外键列。
六、一对一
一对一关系就像夫妻关系,一个丈夫只能配对一个妻子,那么就让双方的主键建立关系,将丈夫的主键设为妻子的外键,反之也可。或者是在一方中另外添加另一方的外键列,并加unique唯一约束。两种方法都可以。操作方法都和上面一样,我就不在此赘述了~
七、多表查询
多表查询有如下几种:
1. 合并结果集:UNION 、 UNION ALL
2. 连接查询(重点)
2.1内连接 [INNER] JOIN ON
2.2外连接 OUTER JOIN ON
-左外连接 LEFT [OUTER] JOIN
- 右外连接 RIGHT [OUTER] JOIN
- 全外连接(MySQL不支持)FULL JOIN
2.3 自然连接 NATURAL JOIN
3.子查询(重点)
一、合并结果集:(注意:被合并的两个结果:列数和列的类型必须相同)
现查询两张表:
其中重复数据有“小白”。
方法一:UNION:去除重复记录
eg:小白 只显示一次
方法二:UNION ALL:不去除重复记录
eg:小白两次都显示
二、连接查询
连接查询就是求出多个表的乘积,表1连接表2,查询结果就是:表1*表2。
连接查询会产生笛卡尔积,假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。两个表的数据一一匹配。
我们先创建两个表:emp1: dept1:
我们所谓的连接查询就是:select *from emp1,dept1;
得到的结果:
可是很明显,我们的程序员的部门等数据都是错乱的,结果不合理,不是我们想要的。这时,我们就需要添加条件来过滤掉不想要的数据。
我们可以看到在雇员表中,是存在与部门表的关系列的,所以这就是我们要添加的限定条件!
语法:select *from 表1,表2 where 限定条件:如下图蓝色部分
这样查询出来的结果,就是正确的了!
查询部分信息:
语法:select 表1.列名,表2.列名 from 表1,表2 where 限定条件;
比如,我们要查询雇员姓名和所属部门名称。
如图,显示的就是要查询的部分了。
说完去除无用信息之后,我们来介绍一下具体的连接方式:
一、内连接(内联查询):
语法:select 列名 from 表1 inner join 表2 on 表1.列名=表2.列名 //外键列的关系 where.....
我们发现跟上面写的查询方法查询出来的结果相同,其实这个语法也只是上面那个写法的标准化而已。~其实内连接就是通过主外键来进行条件筛选,那么如果我们还想添加其他的条件怎么办?很简单,只要在后面加上where就可以继续添加条件啦!!
注:1、表1和表2的顺序可以互换
2、找两张表的等值关系时,找表示相同含义的列作为等值关系。
3、有多表时,就是继续在inner join on 的后面继续inner join 表3 on 表1/表2.列名=表三.列名
二、外连接(外联查询)
包括左外连接和右外连接,外连接的特点:查询出的结果存在不满足条件的可能。
现在,我们把刚才的部门表添加一个数据:
deptno 50是没有匹配的员工的,此时我们再使用内联查询是无法查询到部门aa的,因为内联查询只显示两者有关系的共同部分。那么如果我们想要显示所有的部门,此时就要用到外联查询。
左外联:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名
1.主表数据全部显示,次表数据匹配显示,能匹配到的显示数据,匹配不成功的显示null
2.主表和次表不能随意调换位置
此时,部门aa依然显示,只不过因为没有匹配数据,所以后面全为null。
右外联:select 列名 from 次表 right join 主表 on 主表.列名=次表.列名
右外联查询的结果与左外联一致,只不过位置会发生改变:
三、自然连接(了解)
自然连接是一种特殊的等值连接,他要求两个关系表中进行连接的必须是相同的属性列(名字相同),无须添加连接条件,并且在结果中消除重复的属性列。
我们可以看到自然连接查询到的结果将一个deptno删除了,这也就是它的特点:在结果中消除重复的属性列。
三、子查询(重要!)
子查询就是:一个查询语句中包含另一个完整的查询语句。
子查询出现的位置:
1、 where后,作为条件为被查询的条件的一部分;
2、 from后;(我们知道from后面应该是表格,但是其实查询语句的结果就是一个虚拟表,所以子查询也会跟在from后面)
当子查询出现在where后作为条件时,还可以使用如下关键字:
1、 any
2、all
我们举个例子来理解子查询的应用:
例如刚刚的emp1表,我们要查询工资高于赵六的人的姓名。
此时,我们就应该把赵六的工资作为限定条件,但是得到赵六的工资要先查询到赵六的工资,此时这个查询语句就是“子查询”语句。 (括号中就是子查询语句)
SELECT emp1.` ename` from emp1 where emp1.` sal`>(SELECT emp1.` sal` from emp1 where emp1.` ename`='赵六');
结果:
子查询没有什么书写顺序,按照需求,一步一步分析一步一步加条件即可。
八、扩展
一、多行新增:
insert into 表名(列名) values (列值1,列值2,、、),(列值1,列值2,、、),(列值1,列值2,、、);
二、多表更新:
(1)update 表1,表2 set 列名 //(表1的表2的都可以)=列值 //(表1的表2的都可以) where 表1.列名=表2.列名 //两表关系 and 其他限定条件
(2)update 表1 inner join 表2 on 表1.列名=表2.列名 //两表关系 set 列名=列值
where 限定条件
eg:update student s,class c
set name='赵四',classip=1000
where s.stuid=c.claip and name='李四'
将李四的姓名改为赵四,课堂编号改为1000.
三、多表删除
语法:delete 被删除数据的表 from 删除操作中使用的表 where 限定条件
注:多张表之间使用逗号间隔
eg: //删除人事部的信息
delete d,e,s from department d,employee e,salary s where d.depid=e.depid and s.empid=e.empid and depname='人事部'
四、日期运算函数
now() 获得当前系统时间
year(日期值) 获得日期值中的年份
date_add(日期,interval 计算值 计算的字段);
注:计算值大于0表示往后推日期,小于0表示往前推日期
eg:date_add(now(),interval -40 year);//40年前的日期
九、数据库优化(重点,面试爱问)
1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引(约束)
2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫
描,如:
select id from t where num is null
最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。
3.应尽量避免在 where 子句中使用 != 或 <> 操作符,否则引擎将放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致
引擎放弃使用索引而进行全表扫描,如:select id from t where num=10 or Name = 'admin'
可以这样查询:
select id from t where num = 10
union all
select id from t where Name = 'admin'
5.in 和 not in 也要慎用,否则会导致全表扫描,如:
select id from t where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择
十、数据库的导入导出
选中表后,选择转存sql文件或者导出向导都可以。
导入的话,直接将文件拖拽到数据库中即可,刷新后即可看到。