以前写的笔记,现在掏出来放博客上,方便查找。
主流数据库介绍
MySQL
MySQL开源、免费,多操作系统支持。其开发可追溯至1985年,而第一个内部发行版本诞生,已经是1995年。09年Oracle收购了Sun和MySQL。MySQL体积小、速度快、占用资源少,但是安全性、功能性一般。
Oracle
Oracle开发,收费较高。性能、功能性、安全性较强,多操作系统支持。官方提供技术维护。操作难度较高。
sqlServer
易用性较强,性价比较高。开放性、安全性一般。
MySQL入门
安装
基础术语
-
数据库: 数据库是一些关联表的集合。
-
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
-
列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
-
行:一行(=元组,或记录)是一组相关的数据,例如一条用户订阅的数据。
-
冗余:存储两倍数据,冗余降低了性能,但提高了数据的安全性。
-
主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
-
外键:外键用于关联两个表。
-
复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
-
索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
-
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性。
-
表头(header): 每一列的名称;
-
列(col): 具有相同数据类型的数据的集合;
-
行(row): 每一行用来描述某条记录的具体信息;
-
值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
-
键(key): 键的值在当前列中具有唯一性。
登录
-
cmd启动mysql服务:
net start mysql
-
登录:
mysql -u用户名 -p密码 (u,p后不接空格) 或者: mysql -hip地址 -u用户名 -p密码 (本机ip地址127.0.0.1) 或者: mysql --host=ip地址 --user=用户名 --password=密码
-
退出
quit 或者 exit
SQL 语句分类
- Data Definition Language (DDL数据定义语言)如:建库,建表
- Data Manipulation Language(DML数据操纵语言),如:对表中的记录操作增删改
- Data Query Language(DQL数据查询语言),如:对表中的查询操作
- Data Control Language(DCL数据控制语言),如:对用户权限的设置
DDL库/表结构操作
创建数据库
- 创建数据库
CREATE DATABASE 数据库名;
- 判断数据库是否已经存在,不存在则创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;
- 创建数据库并指定字符集
CREATE DATABASE 数据库名CHARACTER SET 字符集;
查看数据库
SHOW DATABASES;
- 查看某个数据库的定义信息
show create database 数据库名;
修改数据库
- 修改字符集
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
删除数据库
DROP DATABASE 数据库名;
使用数据库
- 查看当前正在使用的数据库
SELECT DATABASE(); (单数,接括号)
- 使用/切换数据库
USE 数据库名;
创建表
CREATE TABLE 表名(
字段名1 字段类型1,
字段名2 字段类型2
);
注意:
每行结尾为逗号,最后一个字段类型不加逗号,最后一行是); 字符串类型要加(指定字节数)
- 数据类型(部分)
int | 整型 |
---|---|
float, double | 浮点型 |
char, varchar | 字符串型, |
date | 日期类型, 格式为年月日: yyyy-MM-dd |
- 创建一个表结构相同的表
create table 新表 like 旧表;
查看表
USE 数据库名;
SHOW TABLES;
- 查看表结构
DESC 表名;
- 查看当时创建该表的语句(`是为了避免混淆)
show create table 表名;
删除表
drop table 表名;
- 判断表是否存在, 存在则删除
drop table if exists 表名;
修改表
- 添加新列
alter table 表名 add 字段名 字段类型;
- 修改列类型
alter table 表名 modify 列名 新类型;
- 同时修改列名和列类型
alter table 表名 change 旧列名 新列名 新列类型;
- 删除列
alter table 表名 drop 列名;
- 修改表名
rename table 表名 to 新表名;
- 修改表字符集
alter table 表名 character set 字符集;
DML表数据增删改操作
添加记录
- 添加一行完整记录
insert into 表名 (字段名1,字段名2...字段名n) values (值1,值2...值n);
或者:
insert into 表名 values (值1,值2...值n);
- 插入部分记录
insert into 表名 (字段名1,字段名2) values (值1,值2);
未说明的记录会自动以null代替
注意:
值的类型, 大小范围应该符合要求, 字符和日期型数据应包含在单引号中。MySQL
中也可以使用双引号做为分隔符。
DOS乱码
- 查看MySQL 内部设置的编码
查看包含character开头的全局变量:
show variables like 'character%';
-
修改client、connection、results的编码为GBK,保证和DOS命令行编码保持一致
-
同时设置三项
set names gbk;
注意:退出DOS 命令行就失效了,需要每次都配置
蠕虫复制
将一张表中的数据复制到另一张表中
- 将表2 所有数据复制到表1
insert into 表1 select * from 表1;
- 将表2 部分数据复制到表1
insert into 表1 select 列名1,列名2... from 表1;
更新记录
- 无条件修改,修改所有行
update 表名 set 字段名=值;
- 指定位置修改
update 表名 set 字段名=值 where 字段名=值;
删除记录
- 无条件逐条删除数据
delete from 表名;
- 指定位置删除数据
delete from 表名 where 字段名=值;
- truncate删除全部数据
truncate table 表名;
truncate 和delete区别:
从效果上来看:truncate是删除整个表,然后重构整个表。delete只是删除逐条删除没一条数据。
从空间上来看:delete会产生碎片,并不会释放空间,而truncate不会产生碎片。
从事务的角度:truncate不可以回滚,delete可以回滚。
DQL查询表数据
简单查询
- 查表所有列的数据
select * from 表名;
- 查指定列数据
select 列名1,列名2...列名n from 表名;
指定别名进行查询
以自己想要的别名显示,不会对真实的列名造成影响
- 指定列的别名查询
select 字段名1 as 别名1,字段名2 as 别名2... from 表名;
- 对列和表同时指定别名
select 字段名1 as 别名, 字段名2 as 别名... from 表名 as 表别名;
别名字符不用加引号.
清除重复值
- 查询指定列并要求结果不显示重复值
select distinct 字段名 from 表名;
查询结果参与运算
- 某列数据和固定值运算
SELECT 列名1 + 固定值 FROM 表名;
- 某列数据和其他列数据参与运算
SELECT 列名1 + 列名2 FROM 表名;
注意: 参与运算的必须是数值类型.
条件查询
select 字段名 from 表名 where 条件;
- 参与条件构成的运算符
、<、<=、>=、=、<> | <>在SQL 中表示不等于,在mysql 中也可以使用!=, 没有== |
---|---|
BETWEEN...AND | 在一个范围之内,如:between 100 and 200 相当于条件在100到200之间,包头又包尾 |
IN(集合) | 集合表示多个值,使用逗号分隔 |
LIKE '张%' | 模糊查询 |
IS NULL | 查询某一列为NULL 的值,注:不能写=NULL |
and 或 && | 与,前者使用较多 |
---|---|
or 或 || | 或 |
not 或 ! | 非 |
例如:
select * from student where math >120 and English > 110;
-
in关键字
in后括号作为省略多个or关键字的简略写法, 只要有 字段=某个值 的数据则被查询
select 字段名 from 表名 where 某字段 in(值1,值2...值n);
例如:
select * from student3 where id not in(1,3,5,7);
- 范围查询
where后是查询指定字段的条件
select 字段名 from 表名 where 某字段 between 值1 and 值2;
- 模糊查询 like
SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
通配符字符串:
% : 替代任意多个字符串(包括0个)
_ : 替代一个任意字符串
-
例如:
查询姓名中包含'钟'字的学生
select * from student where name like '%钟%';
查询姓王,且姓名有两个字的学生
select * from student where name like '王_';
DQL查询(续)
SELECT * 字段列表 as别名 FROM表名 WHERE子句 GROUP BY子句 HAVING子句 ORDER BY子句 LIMIT子句;
排序查询
- 单列排序(仅仅对显示结果排序)
select 字段名 from 表名 where 字段=值 order by 字段名 asc/desc
asc: 升序; desc: 降序
例句:
select * from student order by age desc;
- 组合排序(仅仅对显示结果排序)
同时对多个字段进行排序,如果第1个字段相等,则按第2个字段排序,依次类推。
select 字段名 from 表名 order by 字段名1 asc/desc, 字段名2 asc/desc ...
例句:
select * from student order by age desc, grade desc;
聚合函数
之前做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询,
它是对一列的值进行计算,然后返回一个结果值。聚合函数会忽略空值NULL。
- 五个聚合函数:
max, min, avg, count, sum
select 聚合函数(列名) from 表名;
例句:
--统计30岁以上总人数( 注意null值的处理 )
select count( ifnull( id,0 ) ) from student where age>30;
- ifnull函数
IFNULL(字段名,默认值) 如果字段为null则使用默认值替代
分组查询
一般分组会跟聚合函数一起使用
- 语法
select 显示字段名1,2 from 表 group by 分组字段 having 条件;
例句:
select sex, count(*) from student3 group by sex;
select sex, avg(math) from student3 group by sex;
select sex, count(*) from student3 where age > 25 group by sex ;
--先筛选age, 剩下的再分组性别, 再对分组后的数据筛选统计数目大于2的.
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;
- where和having区别
where:
1)对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,即先过滤再分组。
2) where后面不可以使用聚合函数
having:
1) having子句的作用是筛选满足条件的组,即在分组之后过滤数据,即先分组再过滤。
2) having后面可以使用聚合函数
限制查询
LIMIT的作用就是限制查询记录的条数。
LIMIT offset,length;
offset:起始行数,从0开始计数(也就是说第二行开始为则值1),如果省略,默认就是0
length:返回的行数
例句:
select * from student3 limit 5;
数据库备份
可以在图形化工具里备份,也可以使用命令:
dos 未登录时备份:
mysqldump -u 用户名 -p 密码 数据库 > 文件的路径
登录后还原:
USE 数据库;
SOURCE 导入文件的路径;
例句:
-- 备份day21数据库中的数据到d:\day21.sql文件中
mysqldump -uroot -proot day21 > d:/day21.sql
数据库约束
对表中的数据进行限制,保证数据的正确性、有效性和完整性。一个表如果添加了约束,不正确的数据将无
法插入到表中。约束在创建表的时候添加比较合适
- 种类及关键字:
主键: primary
唯一: unique
非空: not null
外键: foreign key
检查约束: check (mysql里没有)
主键
每条记录的唯一标识, 不用业务字段做主键, 另起id列. 仅仅给程序使用, 不要涉及业务.
主键特点: 非空, 唯一性
- 创建主键
- 若在创建表时添加主键:
字段名 字段类型 primary key,
- 给已有的表添加主键
alter table 表名 add primary key(字段名);
- 删除主键
alter table 表名 drop primary key;
- 主键自增
希望在插入新记录时,系统自动增加主键的值,必须是int
create table 表名(
id int primary key auto_increment,
...
);
-
修改起始值
默认地AUTO_INCREMENT的开始值是1,如果希望修改起始值:
create table 表名(
id int primary key auto_increment,
...
)auto_increment=起始值;
或者对已经创建的表修改:
alter table 表名 auto_increment = 初始值;
- delete与truncate对主键自增长影响
delete删除对主键的值没有影响, 原来是几删除后还是几, 没有自动重新生成
truncate后,主键自增长重新开始
唯一约束
创建表时添加
字段名 字段类型 unique,
给已经建好的表添加:
alter table 表名 add unique(字段名);
注意: null不是数据, 可以重复存在
非空约束
某一列不能为null
创建表时:
字段名 字段类型 not null(字段名),
后添加:
alter table 表名 not null(字段名);
默认值
--创建表时使用默认值
create table st9 (
id int,
name varchar(20),
address varchar(20) default '广州'
)
-- 添加一条记录,使用默认地址
insert into st9 values (1, '李四', default);
外键约束
从表中对应每个主键的一列作为外键, 被主表的主键所约束. 主表中主键列对应从表中外键列.
创建从表时:
constraint 外键约束名 foreign key(从表外键名) references 主表(主键名)
后添加:
alter table 从表名 add CONSTRAINT 外键约束名 foreign key(外键名) references 主表(主键名);
其中,constraint 自定义约束名 这部分可以省略
- 删除外键
alter table 表名 drop foreign key 外键名;
- 外键的级联操作
修改或删除主表主键时,同时更新和删除附表外键的操作。
语法:
只能在创建表时建立级联关系:
on update cascade
级联删除:
on delete cascade
例如:
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
-- 创建外键约束
constraint emp_depid_fk foreign key (dep_id) references
department(id) on update cascade on delete cascade
)
表之间的关系
注意不是指表的数量的关系,而是表的字段的包含关系.
- 一对多
例如班级和学生,部门和员工,客户和订单,总类别和个体。
建表原则:
多方(从表)创建字段作外键 指向 单方(主表)的主键。
- 多对多
老师和学生,学生和课本
建表原则:
创建中间表,该表有多个字段(id1, id2...)分别作为外键指向各自对应的表的主键(id1,id2...)
- 一对一
一对一关系可以创成一张表, 多个表的话:
从表只能有一个主键, 该主键同时作为外键指向主表主键.
数据库设计
范式
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式).
并非并列,而是递进关系. 满足更多设计要求则是更高层次范式.
- 第一范式(1NF)
原子性: 每列都是不可再分割的数据项, 不能是集合或数组等非原子数据项.
- 第二范式(2NF)
第二范式就是在第一范式的基础上每一列全部都依赖于主键列。(一表一主键)
每张表应该只描述一件事. 不产生局部依赖.
反例: 如果有两个不同的主键列在同一张表, 则每一列不完全依赖主键列.
- 第三范式(3NF)
在满足第二范式的前提下,表中的每一列都直接依赖于主键,而不是通过其它的列来间接依赖于主键。
不产生传递依赖.
2021年7月16日 下午5:17 1F
好全