数据库学习笔记
文章目录
MacOS通过控制台登入登出MySQL
#登入
/usr/local/MySQL/bin/mysql -u root -p
#登出
exit#或者输入quit
配置环境变量
-
打开zsh终端输入命令行 cd ~/ 进入根目录
-
看看有没有.bash_profile文件,没有的话 输入 touch .bash_profile
-
输入 open .bash_profile打开文件
-
在打开的文本框中输入以下代码:
export PATH=${PATH}:/usr/local/mysql/bin
添加配置变量,Command+S 保存
-
按esc键退出文本框,在终端输入source .bash_profile,然后关闭终端,重新打开,在终端输入vim ~/.zshrc打开文本框,在新打开的文本框输入i键进入插入模式,然后插入source ~/.bash_profile后关闭文本框。
此时回到控制台就已经可以直接使用如下简短命令登入数据库了:
mysql -u root -p
一、概念概述
1. 数据
**数据的解释:**数据是有结构的,记录是计算机存储数据的一种格式或一种方法,如以下一条记录:
- 语意:学生姓名、性别、出生年份
- 解释:李明是大学生、男、1995年5月20日出生
**数据结构化:**数据结构化是数据库的主要特征之一
-
不再仅仅针对某一个应用,而是面向整个企业或组织
-
不仅数据内部结构化,整体式结构化的,数据之间具有联系
-
数据记录可以变长
-
数据的最小存取单位是数据项
-
数据用数据模型描述,无需应用程序定义
-
数据面向整个系统,可以被多个用户、多个应用共享使用
-
数据共享的好处:
- 减少数据冗余,节约存储空间
- 避免数据之间的不相容性与不一致性
- 使系统易于扩充
数据独立性高:
-
物理独立性
指用户的应用程序与数据库中数据的物理存储是相互独立的。当数据库的物理存储改变了,应用程序不用改变
-
逻辑独立性
指用户的应用程序与数据库的逻辑结构是相互独立的。数据的逻辑结构改变了,应用程序不用改变
数据独立性由数据库管理系统的二级映像功能来保证。
2. DB、DBMS、sql
-
DB:DataBase 数据库,是长期存储在计算机内、有组织的、可共享的大量数据的集合。数据库实际上在硬盘上以文件的形式存在。
收集并抽取一个应用所需要的大量数据,将其保存,以供进一步加工处理,抽取有用信息,转换为有价值的知识。
数据库的基本特征:
- 数据按一定的数据模型组织、描述和存储
- 可以为各种用户共享、冗余度较小、易扩展
- 数据独立性高
-
DBMS:DataBase Management System 数据库管理系统,常见的有:MySQL Oracle DB2 Sybase Sqlserver…
位于用户应用与操作系统之间的一层数据管理软件,是基础软件,是一个大型复杂的软件系统
数据库管理系统的用途:科学地组织和存储数据、高效地获取和维护数据
数据库管理系统的主要功能:
-
数据定义功能
- 提供数据定义语言(DDL)
- 定义数据库中的数据对象
-
数据组织、存储和管理
- 分类组织、存储和管理各种数据
- 确定数据在存储级别上的结构和存取方式
- 实现数据之间的联系
- 提供多种存取方法提高存取效率
-
数据操纵功能
- 提供数据操纵语言(DML)
- 实现对数据库的基本操作(查询、插入、删除和修改)
-
数据库的事务管理和运行管理(数据控制功能)
-
数据的安全性(Security)保护:保护数据以防止不合法的使用造成数据的泄密和破坏。
-
数据的完整性(Integrity)检查:保证数据的正确性、有效性和相容性。
-
并发控制(Concurrency Control):对多用户的并发操作加以控制和协调,防止相互干扰而得到错误的结果。
-
数据库恢复(Recovery):将数据库从错误状态恢复到某一已知的正确状态。
数据由数据库管理系统统一管理和控制,保证事务正确运行
-
-
数据库的建立和维护功能
- 提供实用程序/工具,完成数据库数据批量装载,数据库转储,介质故障恢复,数据库的重组织和性能监视等
-
其他功能
- 数据库管理系统与网络中其他软件系统的通信
- 数据库管理系统系统之间的数据转换
- 异构数据库之间的互访和操作
-
-
**DBS:**Database System 数据库系统
指在计算机系统中引入数据库之后的系统构成
在不引起混淆的情况下,常常把数据库系统简称为数据库
数据库系统的构成:
- 数据库
- 数据库管理系统(及其应用开发工具)
- 应用程序
- 数据库管理员(DataBase Administrator , DBA)
数据库系统的特点:
- 数据结构化
- 数据的共享性高,冗余度低且易扩充
- 数据独立性高
- 数据由数据库管理系统统一管理和控制
-
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
- 根据表的连接方式划分包括:
- 内连接:
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左外连接(左连接)
- 右外连接(右链接)
- 全连接(很少用)
- 内连接:
笛卡尔乘积现象:当两张表进行连接查询时没有任何条件限制,那么查询的最终结果的条数是两张表条数的乘积,这种现象叫做笛卡尔乘积现象