数据库基础知识笔记（数据库基础学习）

存储大量数据，方便检索和访问
保持数据信息的一致、完整
共享和安全
通过组合分析，产生新的有用信息

只要是在客观世界存在的、可以被描述出来的都是实体

数据库就是数据的仓库，可以存放结构化的数据

是一种系统软件，提供操作数据库的环境，可以通过数据库管理系统对数据进行插入、修改、删除和查询等操作。

结构化查询语言专门用来和数据库进行交流的语言,几乎所有的 DBMS 都支持 SQL

SQL 语句不区分大小写，建议 SQL 关键字大写，表名和列表小写
命令用分号结尾
命令可以缩进和换行，一种类型的关键字放在一行
可以写单行和多行注释 , #和--是单行注释，/***/多行注释

表是数据库中包含所有数据的数据库对象，也是其它对象的基础。
表定义是一个列的集合，数据在表中是按行和列的格式组织的，用来存放数据
行也称为记录用来存放一个个实体，列称为字段用来描述实体的某一个属性学生管理系统

开源免费
性能高
安装使用都简单

mysql 下载
安装 MYSQL

C:Program FilesMySQLMySQL Server 5.5my.ini

port 端口号
basedir 安装目录
datadir 数据存放访目录
character_set_server 字符集
default-storage-engine 存储引擎
sql-mode 语法模式
max-connections 最大连接数

为了实现数据完整性，需要检验数据库表中的每行和每列数据是否符合要求
在创建表的时候，应该保证以后的数据输入是正确的，错误的数据不允许输入

不同的字段需要设置为各种合适的类型，比如年龄就是整数类型

默认值是指如果用户没有指定值的情况下会记录的此字段指定一个提供一个预先设定的值

可以把居住地默认值设置为北京

我们可以指定某个字段不能不输入，必须提供一个非空的值

姓名字段不能为空

表中一列或者几列组合的值能用来唯一标识表中的每一行，这样的列或者列组合称为表的主键，主键表的数据不同重复。
如果两列或者多列组合起来唯一标识表中的每一行，则该主键又称为"组合键"

主键的选择标准

最少性尽量选择单个键作为主键
稳定性，由于主键是用来在两个表间建立联接的，所以不能经常更新，最好就不更新

成绩表中的学生 ID 应该在学生表中是存在的我们应该让成绩表中的 ID 只能引用学生表中的 ID，它们的值应该是一一对应的，也就是说成绩表中的 ID 是成绩表中的外键，对应学生表的主键，这样就可以保证数据的引用完整性

唯一约束是指某个字段值是唯一的，在所有的记录中不能有重复的值.

学生的身份证号可以设置为唯一约束

当表中没有合适的列作为主键时可以考虑增加标识列，标识列是一个无实际业务含义的列，仅仅用来区分每条记录。
标识列的值是自动生成的，不能在该列上输入数据

一个表的外键必须引用另一个表的主键,比如成绩表中的学生 ID 会引用学生表的主键，课程 ID 会引用成绩表的主键

主表没有记录，子表中不能添加相应的记录
修改和删除主表记录不能让子表记录孤立，必须相应修改和删除

Structured Query Language：结构化查询语言

使用界面操作数据库不方便
我们需要通过应用程序去操作数据库

9.3.1 DDL（data definition language）是数据定义语言

主要的命令有 CREATE、ALTER、DROP 等，DDL 主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用

9.3.2 DML（data manipulation language）是数据操纵语言

它们是 SELECT、UPDATE、INSERT、DELETE`，就象它的名字一样，这 4 条命令是用来对数据库里的数据进行操作的语言

9.3.3 DCL（DataControlLanguage）是数据库控制语言

是用来设置或更改数据库用户或角色权限的语句，包括（grant,revoke 等）语句

是一种符号，它是用来进行列间或者变量之间的比较和数学运算的

9.4.1 算术运算符

运算符说明 + 加运算，求两个数或表达式相加的和，如 1+1 * 减少减运算，求两个数或表达式相减的差，如 4-1 - 乘运算，求两个数或表达式相乘的积，如 2*2 / 除运算，求两个数或表达式相除的商，如 6/4 的值为 1 % 取模运算，求两个数或表达式相除的余数，如：6%4 的值为 2

查询姓名全称

9.4.2 逻辑运算符

运算符说明 AND 当且仅当两个布尔表达式都为 true 时，返回 TRUE OR 当且仅当两个布尔表达式都为 false，返回 FALSE NOT 布尔表达式的值取反

9.4.3 比较运算符

运算符说明 = 等于 > 大于 < 小于 <> 不等于 = 大于等于 <= 小于等于 != 不等于

9.6.1 插入数据行

9.6.1.1 语法

9.6.1.2 插入记录

向学生表插入一条记录, 姓名张三,身份证号 123456,年龄 30,城市北京

9.6.1.3 注意事项

每次插入一行数据，不能只插入一部分数据，插入的数据是否有效将按照整行的完整性要求来检验
每个数据值的数据类型、精度、位数必须与要应的列名精确匹配
不能为标识符指定值
如果某字段设置为不能为空，则必须插入数据
插入数据时还要符合检查性约束的要求
有缺省值的列，可以使用 DEFAULT 关键字来代替插入实际的值

9.6.2 更新数据行

9.6.2.1 语法

9.6.2.2 更新记录

更新 ID 等于 7 的学生年龄改为 40，城市改为上海

9.6.2.3 注意

多列时用逗号隔开，一定要加更新条件以免错误更新
多个联合条件使用 AND id=7 and idcard='410787'
判断某字段是否为空 email is null or email = ''

9.6.3 删除数据

9.6.3.1 语法

9.6.3.2 删除记录

删除 ID=7 的学生记录

9.6.3.3 注意

Delete 语句是对整行进行操作，因此不需要提供列名
如果要删除主表数据，则要先删除子表记录

9.6.4 TRUNCATE 截断表

截断整个表中的数据

9.6.4.1 语法

9.6.4.2 截断学生表

数据全部清空,但表结构、列、约束等不被改动不能用于有外键约束引用的表标识列重新开始编号因为要删除的数据不会写入日志，数据也不能恢复，所以工作中请尽量不要使用此命令

查询就是从客户端发出查询请求数据库服务器，并从数据库返回查询结果的过程
每次执行查询只是从数据表中提取数据，并按表的方式呈现出来
查询产生的是虚拟表,并不会保存起来

9.7.1 查询

9.7.1.1 语法

9.7.1.2 排序

查询北京的学生信息，并按 ID 正序排列

9.7.1.3 别名

9.7.1.4 查询空行

9.7.1.5 常量列

9.7.1.6 限制返回的行数

9.7.1.7 DISTINCT

查询同学们一共来自哪些不同的城市

9.7.1.8 +

在 MYSQL 中+号只能用作于数字

9.7.1.7 练习

9.7.1.7.1 准备数据

9.7.1.7.2 查询山东省的同学全部信息

9.7.1.7.3 查询山东省的男同学的全部信息

9.7.1.7.4 查询没有填写电子邮箱的同学的姓名和身份证号

9.7.1.8 排序

9.7.1.8.1 把学生的分数按升序排列

9.7.1.8.2 把学生的分数按降序排列

9.7.1.8.3 课程编号按升序，成绩按降序

9.8.1 字符函数

函数名称描述 CONCAT 字符串连接 CONCAT_WS 使用指定的分隔符进行字符连接 FORMAT 数字格式化 LOWER 转小写字母 UPPER 转大写字母 LEFT 返回字符串 s 开始的最左边 n 个字符 RIGHT 返回字符串 s 开始的最左边 n 个字符

9.8.1.1 LENGTH

9.8.1.2 拼接字符串 [CONCAT]

9.8.1.3 拼接字符串 [UPPER]

9.8.1.4 拼接字符串 [LOWER]

9.8.1.5 截取字符串 [SUBSTR]

9.8.1.6 姓名的首字符大写，其它字符小写然后用_拼接

9.8.1.7 返回子串在原始字符串的起始索引

9.8.1.8 去掉左右空格[TRIM]

9.8.1.9 用空格补齐[LPAD]

9.8.1.10 替换

9.8.1.11 FORMAT

9.8.1.12 LEFT RIGHT

9.8.2 数学函数

函数名称描述 CEIL 向上取整 FLOOR 向下取整数 DIV 整数取 MOD 取余(取模) POWER 幂运算 ROUND 四舍五入 TRUNCATE 数字截取

9.8.2.1 四舍五入

9.8.2.2 向上取整

9.8.2.3 向下取整

9.8.2.4 截断

9.8.2.5 取余

9.8.3 日期函数

函数名称描述 NOW 当前日期和时间 CURDATE 当前日期 CURTIME 当前时间 DATE_ADD 日期变化 DATEDIFF 计算日期差 DATE_FORMAT 日期格式化

9.8.3.1 返回日期时间

9.8.3.2 返回日期

9.8.3.3 返回日期

9.8.3.4 返回日期中指定的部分

年月日小时分钟秒

9.8.3.5 str_to_date

将日期格式的字符串转成指定格式的日期

序号格式符功能 1 %Y 4 位的年份 2 %y 2 位的年份 3 %m 月份(01,02) 4 %c 月份(1,2) 5 %d 日(01,02) 6 %H 小时(24 小时制) 7 %h 小时(12 小时制) 8 %i 分钟(00,01) 9 %s 秒(00,01)

9.8.3.6

查询入职日期为 1984-8-4 的员工信息

9.8.3.7 DATE_FORMAT

将日期转换成指定字符串

9.8.3.8 DATE_ADD

9.8.3.9 DATEDIFF

9.8.4 其它函数

9.8.5 流程控制函数

9.8.5.1 IF

9.8.5.2 CASE 函数

自定义函数是对 MySQL 的扩展，使用方式和内置函数相同
函数必须要有参数和返回值
函数可以接收任意类型的值，也可以接收这些类型的参数
函数体由合法的 SQL 语句组成
函数体可以是简单的 SELECT 语句或 INSERT 语句，如果是复合结构要用 BEGIN...END
函数体也可以包含声明，循环和流程控制
返回值只能有一个

9.9.1 语法

9.9.2 znow

注意函数名一定要跟着小括号表示参数

9.9.3 zadd

9.9.4 addUser

就是查询的条件是模糊的，不是特别明确的

9.10.1 通配符

代替一个或多个真正的字符，与 LIKE 关键字一起使用

_表示一个任意字符
%表示任意长度的字符串

9.10.2 BETWEEN AND

查询某一列在指定的规范内的记录，包括两个边界

9.10.3 IN

查询某一列中的值在列出的内容列表中

9.10.4 IS NUL

查询没有邮箱的 IS NUL
查询有邮箱的 IS NOT NULL

大多数 SQL 语句都是针对一个或多个表的单条语句。但是并不是所有的操作都是可以用一条语句来完成的，经常有一些操作是需要多条语句配合才能完成。我们引入的存储过程（Stored Procedure）是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它

因为存储过程实际上是一种函数,所以存储过程名后需要有 () 符号(即使不传递参数也需要)

存储过程并不显示结果，而是把结果返回给你指定的变量

索引可以提高数据库的查询速度

-- 构建一个 8388608 条记录的数据

-- 创建用户表

-- 创建生成随机字符串的函数

-- 产生随机数字

-- 创建存储过程，向用户表添加海量数据

比较频繁作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合作创建索引
不会出现在 where 子句中的字段不该创建索引