数据库笔记

数据库学习笔记

MacOS通过控制台登入登出MySQL

#登入
/usr/local/MySQL/bin/mysql -u root -p
#登出
exit#或者输入quit

配置环境变量

1. 打开zsh终端输入命令行 cd ~/ 进入根目录

2. 看看有没有.bash_profile文件，没有的话 输入 touch .bash_profile

3. 输入 open .bash_profile打开文件

4. 在打开的文本框中输入以下代码：

    export PATH=${PATH}:/usr/local/mysql/bin
   

   添加配置变量，Command+S 保存

5. 按esc键退出文本框，在终端输入source .bash_profile，然后关闭终端，重新打开，在终端输入vim ~/.zshrc打开文本框，在新打开的文本框输入i键进入插入模式，然后插入source ~/.bash_profile后关闭文本框。

   此时回到控制台就已经可以直接使用如下简短命令登入数据库了：

mysql -u root -p

一、概念概述

1. 数据

**数据的解释：**数据是有结构的，记录是计算机存储数据的一种格式或一种方法，如以下一条记录：

• 语意：学生姓名、性别、出生年份
• 解释：李明是大学生、男、1995年5月20日出生

**数据结构化：**数据结构化是数据库的主要特征之一

• 不再仅仅针对某一个应用，而是面向整个企业或组织

• 不仅数据内部结构化，整体式结构化的，数据之间具有联系

• 数据记录可以变长

• 数据的最小存取单位是数据项

• 数据用数据模型描述，无需应用程序定义

  

• 数据面向整个系统，可以被多个用户、多个应用共享使用

• 数据共享的好处：

  1. 减少数据冗余，节约存储空间
  2. 避免数据之间的不相容性与不一致性
  3. 使系统易于扩充

  

数据独立性高：

• 物理独立性

  指用户的应用程序与数据库中数据的物理存储是相互独立的。当数据库的物理存储改变了，应用程序不用改变

• 逻辑独立性

  指用户的应用程序与数据库的逻辑结构是相互独立的。数据的逻辑结构改变了，应用程序不用改变

数据独立性由数据库管理系统的二级映像功能来保证。

2. DB、DBMS、sql

• DB：DataBase 数据库，是长期存储在计算机内、有组织的、可共享的大量数据的集合。数据库实际上在硬盘上以文件的形式存在。

  收集并抽取一个应用所需要的大量数据，将其保存，以供进一步加工处理，抽取有用信息，转换为有价值的知识。

  数据库的基本特征：

  1. 数据按一定的数据模型组织、描述和存储
  2. 可以为各种用户共享、冗余度较小、易扩展
  3. 数据独立性高

• DBMS：DataBase Management System 数据库管理系统，常见的有：MySQL Oracle DB2 Sybase Sqlserver…

  位于用户应用与操作系统之间的一层数据管理软件，是基础软件，是一个大型复杂的软件系统

  数据库管理系统的用途：科学地组织和存储数据、高效地获取和维护数据

  数据库管理系统的主要功能：

  1. 数据定义功能

     • 提供数据定义语言（DDL）
     • 定义数据库中的数据对象

  2. 数据组织、存储和管理

     • 分类组织、存储和管理各种数据
     • 确定数据在存储级别上的结构和存取方式
     • 实现数据之间的联系
     • 提供多种存取方法提高存取效率

  3. 数据操纵功能

     • 提供数据操纵语言（DML）
     • 实现对数据库的基本操作（查询、插入、删除和修改）

  4. 数据库的事务管理和运行管理（数据控制功能）

     • 数据的安全性（Security）保护：保护数据以防止不合法的使用造成数据的泄密和破坏。

     • 数据的完整性（Integrity）检查：保证数据的正确性、有效性和相容性。

     • 并发控制（Concurrency Control）：对多用户的并发操作加以控制和协调，防止相互干扰而得到错误的结果。

     • 数据库恢复（Recovery）：将数据库从错误状态恢复到某一已知的正确状态。

     数据由数据库管理系统统一管理和控制，保证事务正确运行

  5. 数据库的建立和维护功能

     • 提供实用程序/工具，完成数据库数据批量装载，数据库转储，介质故障恢复，数据库的重组织和性能监视等

  6. 其他功能

     • 数据库管理系统与网络中其他软件系统的通信
     • 数据库管理系统系统之间的数据转换
     • 异构数据库之间的互访和操作

• **DBS：**Database System 数据库系统

  指在计算机系统中引入数据库之后的系统构成

  在不引起混淆的情况下，常常把数据库系统简称为数据库

  数据库系统的构成：

  1. 数据库
  2. 数据库管理系统（及其应用开发工具）
  3. 应用程序
  4. 数据库管理员（DataBase Administrator , DBA）

  数据库系统的特点：

  1. 数据结构化
  2. 数据的共享性高，冗余度低且易扩充
  3. 数据独立性高
  4. 数据由数据库管理系统统一管理和控制

• SQL：结构化查询语言，是一门标准通用的高级语言。标准的SQL适合于所有的数据库产品。

  SQL语句执行时，内部也会先编译，然后再执行。（sql语句的编译由DBMS完成）

• 三者之间的关系：DBMS负责执行sql语句，通过执行sql语句来操作DB当中的数据。

  DBMS --（执行）-> SQL --（操作）-> DB

3. 表

如下：

学号（int）	姓名（varchar）	年龄（int）
110	张三	20
120	李四	21

表：table是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是增强可读性。

​ 一个表包括行和列：

​ 行：被称为数据/记录（data）

​ 列：被称为字段（column）

每一个字段应该包括的属性：字段名、数据类型、相关的约束。

4. SQL的分类

DQL（Data Query Language 数据查询语言）：查询语句，凡是select语句都是DQL

DML（Data Manipulation Language 数据操作语言）：insert delete update，对表当中的数据进行增删改

DDL（Data Definition Language 数据定义语言）：create drop alter，对表结构的增删改

TCL（Transaction Control Language 事务控制语言）：commit提交事务，rollback回滚事务

DCL（Data Control Language 数据控制语言）：grant授权，revoke撤销权限

5. 导入数据

第一步：登入数据库

mysql -u root -p

第二步：查看数据库列表

show databases;#这个不是sql语句，是mysql命令，只能在mysql上可以使用

​ 查询结果（mac版mysql默认的数据库）：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

第三步：创建数据库

create database 数据库名;#mysql命令
#创建成功后显示：Query OK, 1 row affected (0.00 sec)

第四步：使用新创建的数据库

use 数据库名;#mysql命令
#使用成功后显示：Database changed

第五步：查看当前数据库中有哪些表

show tables;#mysql命令
#数据库中无表：Empty set

第六步：初始化数据（导入sql文件中的数据）

source sql文件的路径

6. .sql文件

以**.sql**后缀结尾的文件成为“sql脚本”文件。

sql脚本：文件中编写了大量的sql语句的文件。使用source命令可以直接执行sql脚本

7. 查看表的结构

使用以desc命令可以查看指定的表，示例中先导入了一个sql脚本：testDB，但是在学习过程中，testDB会不断增删数据或者改变结构，务必注意。

#desc 表名; 如：
desc course;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| cid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| cname | varchar(20) | YES  |     | NULL    |                |
| gid   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

8. 查看表中的数据

select * from 表名;

mysql> select * from course;
+-----+----------+------+
| cid | cname    | gid  |
+-----+----------+------+
|   1 | CoreJava | NULL |
|   2 | JavaWeb  | NULL |
|   3 | RIA      | NULL |
|   4 | MyBatis  | NULL |
|   5 | SSM      | NULL |
+-----+----------+------+
5 rows in set (0.01 sec)

二、 MySQL常用命令

• 查看mysql版本：mysql --version 或者 mysql -V
• 创建数据库：create database 数据库名;
• 使用数据库：use 数据库名;
• 查询当前使用的数据库：select database();
• 查询数据库版本：select version();
• 终止一条正在编写的语句：\c
• 退出mysql：exit 或者 quit 或者 \q
• 查看和指定现有的数据库：show databases;
• 查看其他库中的表：show tables from 其他的数据库名;
• 查看创建表的语句：show create table 表名;

三、查询

1. 简单的查询语句（DQL）

查询一个字段：select 字段名 from 字段所属的表名;

mysql> select cname from course;
+----------+
| cname    |
+----------+
| CoreJava |
| JavaWeb  |
| RIA      |
| MyBatis  |
| SSM      |
+----------+
5 rows in set (0.00 sec)

查询多个字段：select 字段名1,字段名2,字段名3… from 字段所属的表名;

mysql> select cid,cname from course;
+-----+----------+
| cid | cname    |
+-----+----------+
|   1 | CoreJava |
|   2 | JavaWeb  |
|   3 | RIA      |
|   4 | MyBatis  |
|   5 | SSM      |
+-----+----------+
5 rows in set (0.00 sec)

字段可以参与数学运算，如将id*10

mysql> select cid * 10,cname from course;
+----------+----------+
| cid * 10 | cname    |
+----------+----------+
|       10 | CoreJava |
|       20 | JavaWeb  |
|       30 | RIA      |
|       40 | MyBatis  |
|       50 | SSM      |
+----------+----------+
5 rows in set (0.01 sec)

查询结果的字段名可以在输入查询语句时重命名：如将cid * 10 更改为 newCID

重命名的好处：可读性强；可以避免多表连接查询时因重名而混淆

ysql> select cid * 10 as newCID,cname from course;
+--------+----------+
| newCID | cname    |
+--------+----------+
|     10 | CoreJava |
|     20 | JavaWeb  |
|     30 | RIA      |
|     40 | MyBatis  |
|     50 | SSM      |
+--------+----------+
5 rows in set (0.00 sec)

注意：更改的只是查询结果，并没有影响原始数据。重命名字段名时不能直接使用中文名，要使用单引号括起来，as关键字可以省略。

查询全部字段：select * from 表名;

mysql> select * from course;
+-----+----------+------+
| cid | cname    | gid  |
+-----+----------+------+
|   1 | CoreJava | NULL |
|   2 | JavaWeb  | NULL |
|   3 | RIA      | NULL |
|   4 | MyBatis  | NULL |
|   5 | SSM      | NULL |
+-----+----------+------+
5 rows in set (0.00 sec)

注意：实际开发中不建议使用星号查询所有字段，因为如果字段量很庞大，会造成查询时间非常长，极大影响程序效率。

2. 条件查询

条件查询要用到where语句，且where必须放在from语句表的后面。where支持的运算符参照下表

运算符	说明
=	等于
<>或!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between…and	两个值之间，等同于>= and <=
Is null	为null（is not null 不为空,mysql中null表示为空，什么都没有，不是一个值，不能用=衡量）
and	并且，表示既是a又是b的数据，and的优先级比or的优先级高
or	或者，表示是a和b的数据，和and联合使用需要注意优先级的情况，加括号的情况
in	包含，相当于多个or（not in不在这个范围内），in后面小括号里不是表示区间，而是具体的值
not	not可以取非，主要用在is或者in中
Like	like成为模糊查询，支持%或者下划线匹配

例：查询cid为2-4之间即2，3，4所对应的cname

Between…and也可以应用在字符方面，如：between ‘a’ and ‘c’但是与数字不同，这里不包括’c’

mysql> select cname from course where cid between 2 and 4;
+---------+
| cname   |
+---------+
| JavaWeb |
| RIA     |
| MyBatis |
+---------+
3 rows in set (0.00 sec)

例：查询cname ：JavaWeb 对应的cid

mysql> select cid from course where cname = 'JavaWeb';
+-----+
| cid |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

例：模糊查询cname中有Java的

在模糊查询中必须掌握的两个特殊符号：’ % ‘和’ _ ’

’ % ‘：代表任意多个字符，’ _ '：代表任意一个字符

使用转义字符‘ \ ’后‘_’和‘%’就只表示一个普通字符了。

#查询字段中有Java的
mysql> select cid,cname from course where cname like '%Java%';
+-----+----------+
| cid | cname    |
+-----+----------+
|   1 | CoreJava |
|   2 | JavaWeb  |
+-----+----------+
2 rows in set (0.00 sec)
#查询cname中第二个字符是‘a’的
mysql> select cid,cname from course where cname like '_a%';
+-----+---------+
| cid | cname   |
+-----+---------+
|   2 | JavaWeb |
+-----+---------+
1 row in set (0.00 sec)

3. 排序（升序、降序）

order by 字段/数字（数字表示表中第几列）;//默认为升序排列

降序排列：order by 字段 desc;

升序排列：order by 字段 asc;

在字段一升序排列的情况下，如果字段一相同，按字段二降序排列：order by 字段1 desc , 字段2 asc;

多个排序条件时，越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序时，后面的字段才会起作用。

#查找学生id和姓名，并按照id降序排列
mysql> select id,stuName from student order by id desc;
+----+---------+
| id | stuName |
+----+---------+
| 12 | 田七    |
| 11 | 田七    |
| 10 | 田七    |
|  9 | 宁辉    |
|  8 | 宁辉    |
|  7 | 王丽    |
|  6 | 李丽    |
|  5 | 刘星    |
|  4 | 赵六    |
|  3 | 王五    |
|  2 | 李四    |
+----+---------+
11 rows in set (0.00 sec)

#查找学生id和姓名，并按照id升序排列
mysql> select id,stuName from student order by id asc;
+----+---------+
| id | stuName |
+----+---------+
|  2 | 李四    |
|  3 | 王五    |
|  4 | 赵六    |
|  5 | 刘星    |
|  6 | 李丽    |
|  7 | 王丽    |
|  8 | 宁辉    |
|  9 | 宁辉    |
| 10 | 田七    |
| 11 | 田七    |
| 12 | 田七    |
+----+---------+
11 rows in set (0.00 sec)

#执行顺序：
select/*-------------3.*/
	字段1,字段2,字段3
from/*---------------1.先执行找到目标表*/
	表
where/*--------------2.*/
	条件
order by/*-----------4.最后执行排序*/
	字段 asc;

4.分组函数

函数	作用
count	计数
sum	求和
avg	平均值
max	最大值
min	最小值

#查找所有学生数目
#count(*)统计的是总记录条数而不是某个字段中的数据个数
#count(字段)表示统计字段中不为null的数据总数量
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)
#找出年龄最大的学生
mysql> select max(age) from student;
+----------+
| max(age) |
+----------+
|       33 |
+----------+
1 row in set (0.01 sec)
#找出年龄最小的学生
mysql> select min(age) from student;
+----------+
| min(age) |
+----------+
|       20 |
+----------+
1 row in set (0.01 sec)
#对所有学生的年龄求和
mysql> select sum(age) from student;
+----------+
| sum(age) |
+----------+
|      277 |
+----------+
1 row in set (0.00 sec)
#分组函数可以组合使用
mysql> select count(*) , max(age) , min(age) , sum(age) from student;
+----------+----------+----------+----------+
| count(*) | max(age) | min(age) | sum(age) |
+----------+----------+----------+----------+
|       11 |       33 |       20 |      277 |
+----------+----------+----------+----------+
1 row in set (0.00 sec)

注意：分组函数输入多行，但只输出一行，且自动忽略空(null)值，不需要手动写入条件排除；所有的分组函数都是对“某一组”数据进行操作的；分组函数一般会与group by联合使用。分组函数的别名：多行处理函数。

单行处理函数：输入一行就输出一行，输入两行就输出两行,运算中如果有null参与，结果必定是null。

#分组函数不能直接出现在where语句中
#分组函数必须在分完组之后才能使用，而group by在where执行之后才能执行，所以以下代码出错
#查询学号高于学号平均值的学生
mysql> select id,stuName from student where id > avg(id);#where执行时还没分组，所以分组函数avg不能使用
ERROR 1111 (HY000): Invalid use of group function
#error:错误得使用了分组函数
#正确写法：先查出平均值，再使用条件查询，可以分两步查询，也可以将两部联合起来，如下：
mysql> select id,stuName from student where id > (select avg(id) from student);
+----+---------+
| id | stuName |
+----+---------+
|  8 | 宁辉    |
|  9 | 宁辉    |
| 10 | 田七    |
| 11 | 田七    |
| 12 | 田七    |
+----+---------+
5 rows in set (0.00 sec)

5. group by 和 having

group by：按照某个字段或者某些字段进行分组。任何一个分组函数都是在group by执行结束后才会执行。当一个表没有group by时，整张表会自成一组。

#案例：找出student表中不同性别的最大年龄
#注意：参加分组的字段可以一起查询，未参加分组的字段不能一起查询，即使在mysql中有查询结果，但是结果是随机取的，并无意义。因此，当一条语句之中如果有group by时，select后面只能跟分组函数和参与分组的字段。
mysql> select max(age) ,sex from student group by sex;
+----------+------+
| max(age) | sex  |
+----------+------+
|       33 | 女   |
|       30 | 男   |
+----------+------+
2 rows in set (0.00 sec)
#可以多个字段联合分组
#案例：找出不同地区不同性别的学生的最大年龄
mysql> select max(age),sex,address from student group by sex,address;
+----------+------+-----------------------------+
| max(age) | sex  | address                     |
+----------+------+-----------------------------+
|       32 | 女   | 北京市朝阳区                |
|       33 | 女   | 北京市海淀区                |
|       20 | 男   | 北京市昌平区沙河镇          |
|       30 | 男   | 北京市海淀区                |
|       23 | 男   | 山西省运城市                |
+----------+------+-----------------------------+
5 rows in set (0.00 sec)

having：对分组后的数据进行再次过滤

#找出不同地区学生的最大年龄，要求年龄大于30
#查出每个地区最大年龄。然后使用having过滤掉不满足条件的
mysql> select max(age),address from student group by address having max(age) > 30;
+----------+--------------------+
| max(age) | address            |
+----------+--------------------+
|       32 | 北京市朝阳区       |
|       33 | 北京市海淀区       |
+----------+--------------------+
2 rows in set (0.01 sec)
#优化后的写法：使用where直接过滤掉30以下的数据，再查询，效率更高，注意，不是所有having都能改为where优化
mysql> select max(age),address from student where age > 30 group by address;
+----------+--------------------+
| max(age) | address            |
+----------+--------------------+
|       32 | 北京市朝阳区       |
|       33 | 北京市海淀区       |
+----------+--------------------+
2 rows in set (0.00 sec)

6. DQL总结

#一个完整的DQL语句的写法和执行顺序：
select/*-------5-*/
	字段
from/*---------1-*/
	表
where/*--------2-*/
	条件
group by/*-----3-*/
	字段
having/*-------4-*/
	条件
order by/*-----6-*/
	字段

关于查询结果集的去重：distinct关键字

distinct只能出现在所有字段的最前方。后面有多个字段的话，表示多个字段联合去重。

#不使用distinct去重
mysql> select address from student;
+-----------------------------+
| address                     |
+-----------------------------+
| 北京市海淀区                |
| 北京市海淀区                |
| 北京市朝阳区                |
| 北京市海淀区                |
| 北京市朝阳区                |
| 北京市海淀区                |
| 山西省运城市                |
| 山西省运城市                |
| 北京市昌平区沙河镇          |
| 北京市昌平区沙河镇          |
| 北京市昌平区沙河镇          |
+-----------------------------+
11 rows in set (0.01 sec)
#使用distinct去重
mysql> select distinct address from student;
+-----------------------------+
| address                     |
+-----------------------------+
| 北京市海淀区                |
| 北京市朝阳区                |
| 山西省运城市                |
| 北京市昌平区沙河镇          |
+-----------------------------+
4 rows in set (0.00 sec)

7. 连接查询

连接查询：在实际开发中，大部分情况下都不是从单张表中查询数据，一般都是多张表联合查询取出最终结果。

在实际开发中，一般一个业务会对应多张表，比如：学生和班级，起码两张表。

连接查询的分类：

• 根据语法出现的年代划分包括：SQL92 和 SQL99
• 根据表的连接方式划分包括：
  1. 内连接：
     1. 等值连接
     2. 非等值连接
     3. 自连接
  2. 外连接
     1. 左外连接（左连接）
     2. 右外连接（右链接）
  3. 全连接（很少用）

笛卡尔乘积现象：当两张表进行连接查询时没有任何条件限制，那么查询的最终结果的条数是两张表条数的乘积，这种现象叫做笛卡尔乘积现象