329
2021年4月30日22:54:44

阅读模式

以前写的笔记，现在掏出来放博客上，方便查找。

主流数据库介绍

MySQL

MySQL开源、免费，多操作系统支持。其开发可追溯至1985年，而第一个内部发行版本诞生，已经是1995年。09年Oracle收购了Sun和MySQL。MySQL体积小、速度快、占用资源少，但是安全性、功能性一般。

Oracle

Oracle开发，收费较高。性能、功能性、安全性较强，多操作系统支持。官方提供技术维护。操作难度较高。

sqlServer

易用性较强，性价比较高。开放性、安全性一般。

MySQL入门

安装

基础术语

数据库: 数据库是一些关联表的集合。
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
表头(header): 每一列的名称;
列(col): 具有相同数据类型的数据的集合;
行(row): 每一行用来描述某条记录的具体信息;
值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
键(key): 键的值在当前列中具有唯一性。

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
)

表之间的关系

注意不是指表的数量的关系,而是表的字段的包含关系.

一对多

例如班级和学生，部门和员工，客户和订单，总类别和个体。

建表原则：

多方（从表）创建字段作外键指向单方（主表）的主键。

多对多

老师和学生，学生和课本