数据库基础

什么是数据库
数据库是指长期存储在计算机内有组织可共享的数据和数据之间关系的集合。数据库中的数据按照一定的数据模型组织、描述和存贮，具有较小的冗余度、较高的数据独立性和易扩展性，并在一定范围内可以被多个用户所共享。
数值和数据。数据=元数据+数值
关系型数据库是建立在关系数据模型基础上的数据库，借助集合代数概念和方法处理数据库中的数据，同时也是一个被组织成拥有正式描述性的表格。该形式的表格实质是装载数据项的收集体，这些表格中的数据能以不同的方式被存取或重新召集而不需要更新组织数据库表格。
主流RDBMS有Oracle、DB2、SQL Server、Sybase、MySQL等。
什么是DBMS
数 据 库 管 理 系 统Database Management System，简称DBMS是位于用户与操作系统之间的一层数据管理软件

DBMS的用途
科学地组织和存储数据、高效地获取和维护数据

DBMS的主要功能
数据定义功能
提供数据定义语言(DDL)
定义数据库中的数据对象
数据操纵功能
提供数据操纵语言(DML)
操纵数据实现对数据库的基本操作(查询、插入、删除和修改)
数据库的运行管理
保证数据的安全性、完整性
多用户对数据的并发使用
发生故障后的系统恢复
数据库的建立和维护功能(实用程序)
数据库数据批量装载、数据库转储
介质故障恢复、数据库的重组织、性能监视等
数据库特点
数据尽可能不重复，以最优方式为多种应用服务
数据结构独立于应用
对数据的CRUD由统一软件进行管理和控制
数据模型
在数据库中用数据模型这个工具来抽象、表示和处理现实世界中的数据和信息。通俗地讲数据模型就是现实世界的模拟

数据模型分成两个不同的层次
1、概念模型也称信息模型，它是按用户的观点来对数据和信息建模，可以使用ER图进行描述。实体E，关系R

矩形—实体 椭圆—属性 菱形—关系

数据模型主要包括层次模型、网状模型、关系模型等，它是按计算机系统的观点对数据建模。下一代是对象模型
ORM对象关系映射:hibernate、mybatis

2、物理数据模型

关系模式是最重要的一种数据模型。也是目前主要采用的数据模型
1970年由美国IBM 公司San Jose研究室的研究员E.F.Codd提出
在用户观点下，关系模型中数据的逻辑结构是一张二维表，它由行和列组成
它通过数据、关系和对数据的约束三者组成的数据模型来存放和管理数据
RDBMS的特点
数据以表格的形式出现 －表在数学中被表示为关系
每行为各种记录名称
每列为记录名称所对应的数据域
许多的行和列组成一张表单
若干的表单组成database
关系模型的优缺点
建立在严格的数学概念的基础上
概念单一。数据结构简单、清晰，用户易懂易用
实体和各类联系都用关系来表示
对数据的检索结果也是关系
关系模型的存取路径对用户透明
具有更高的数据独立性，更好的安全保密性
简化了程序员的工作和数据库开发建立的工作

缺点：存取路径对用户透明导致查询效率往往不如非关系数据模型
为提高性能，必须对用户的查询请求进行优化。增加了开发数据库管理系统的难度
常见的数据库产品
Oracle：运行稳定，可移植性高，功能齐全，性能超群！适用于大型企业领域
DB2：速度快、可靠性好，适于海量数据，恢复性极强。适用于大中型企业领域

SQL Server：全面，效率高，界面友好，操作容易，但是不跨平台[2019支持windows、Linux和Docker]。适用于于中小型企业领域

MySQL：开源，体积小，速度快。适用于中小型企业领域
LAMP–php
SQL
SQL结构化查询语言是一种数据库操作的非过程化编程语言，用于存取数据以及查询、更新和管理关系型数据系统，一般数据库脚本文件的扩展名也是.sql。
允许用户在高层数据结构上工作，不要求用户了解数据的存放方式，对于不同底层结构的数据库系统使用相同的数据输入与管理的接口

1986年10月美国国家标准化协会ANSI首先对SQL进行规范化，1987年国际标准化组织ISO定义了国际标准。但是各种数据库系统都对SQL规范进行了某些编改和扩充，所以不同数据库系统的SQL不能完全相互通用。

一个SQL类型的数据库对于使用者是便于理解和组织结构的，通过数据元与数据元之间的关系可以整理出对系统的数据处理逻辑，并且可以进行人为精简数据，达到最优或者较优解。
SQL的特点
综合统一、高度非过程化 4GL
面向集合的操作方式

以同一种语法结构提供两种使用方法；即可以直接以命令方式交互使用；也可以嵌入主语言中使用

语言简洁，易学易用；在ANSI标准中，只包含了94个英文单词，核心功能只用6个动词，语法接近英语口语

NoSQL
NoSQL泛指非关系型数据库。主要是针对超大规模和高并发的SNS类型动态网站的解决方案（Web2.0）。
常见的NoSQL数据库类型有4种
key-value数据库。使用hash表存储数据，例如Redis。简单易部署，但是对部分值进行查询或更新时效率低下
列存储数据库，用于应对分布式存储的海量数据，键仍旧存在，只是指向了多个列，例如HBase
文档型数据库，该类型的数据模型是版本化的文档，半结构化的文档以JSON格式存储，例如MongoDB
图形数据库，主要用户关系计算Neo4j
NoSQL的适用场景：
数据模型比较简单
需要灵活性很强的IT系统
对数据库性能要求较高
不需要数据高度的一致性
对于给定的key比较容易映射复杂值的环境
SQL vs NoSQL
复杂查询SQL数据库比较擅长
SQL数据库不适合分层次的数据存储，而NoSQL数据库可以很好地存储分层次的数据，更适用于大数据
对于基于大量事务的应用SQL非常适合，更加稳定，能够保证数据的原子性和一致性，而NoSQL对事务的处理能力有限
SQL数据库厂商对其产品有很好的支持，而NoSQL一般只有社区支持
性能对比中NoSQL明显优于SQL数据库。一般NoSQL可以充分利用系统的内存资源。
不用考虑数据关系和格式的NoSQL非常方便开发，范式对运维提出了相当高的要求。
什么是Mysql数据库
MySQL®软件提供了十分快速的多线程、多用户、牢靠的SQL（结构化查询语言）数据库服务器。 MySQL服务器定位于任务关键型、重负荷生产系统，并能嵌入在大量部署的软件中
MySQL数据库软件是一种客户端/服务器系统，由支持不同后端的1个多线程SQL服务器，数种不同的客户端程序和库，众多管理工具和广泛的应用编程接口API组成
Mysql数据库应用
MySQL的官方网站引述MySQL是“世界上最受欢迎的开放源代码数据库”。这不是狂妄之语，数字可以证明它
有些用户将MySQL用于含60000个表和约50亿行的数据库
每个表可支持高达64条索引。每条索引可由1～16个列或列元素组成。最大索引宽度为1000字节。索引可使用具备CHAR、VARCHAR、BLOB或TEXT列类型的列前缀
所有数据均以所选的字符集保存。正常字符串列的比较不区分大小写
MySQL数据库的最大有效表尺寸通常是由操作系统对文件大小的限制决定的，而不是由MySQL内部限制决定的
InnoDB存储引擎将InnoDB表保存在一个表空间内，该表空间可由数个文件创建。这样，表的大小就能超过单独文件的最大容量。表空间可包括原始磁盘分区，从而使得很大的表成为可能。表空间的最大容量为64TB
MySQL 8的新特性
MySQL 8.0 的速度要比 MySQL 5.7 快 2 倍。MySQL 8.0 在以下方面带来了更好的性能：读/写工作负载、IO 密集型工作负载、以及高竞争（"hot spot"热点竞争问题）工作负载
从 MySQL 8 开始，使用 utf8mb4 作为 MySQL 的默认字符集
InnoDB 现在支持表 DDL 的原子性，也就是 InnoDB 表上的 DDL 也可以实现事务完整性了，要么失败回滚，要么成功提交
InnoDB 集群为您的数据库提供集成的原生 HA 解决方案
Windows下MySQL的安装
从Oracle公司的官方网站上，可以下载到MySql的安装版本和解压缩版本。
https://dev.mysql.com/downloads/mysql/
MySql本来是完全免费的产品，被Oracle收购以后，Oracle将MySql修改成了三个不同的版本为：
MySql Enterprice Edition 企业版　－　收费或试用
MySql Community Edition 社区版本　－　免费
MySql Classic Edition 经典版本－免费
1、初始化操作
mysqld --initialize --console

2、将mysql添加到系统服务中
mysqld --install
3、启动服务
net start mysql
4、登录数据库
mysql -uroot -p
输入步骤1中生成的口令，可以采用拷贝粘贴的方式
5、修改口令，因为步骤1生成的口令太难记忆了，如果是云平台上的数据库，则建议不要修改
ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘新密码’;
使用quit退出
停止服务：net stop mysql
卸载服务：mysqld --remove
6、如果忘记口令，则删除data文件夹，重新执行步骤1-5即可

常用mysql服务命令
启动数据库服务器 net start mysql
启动客户端登录mysql数据库：mysql -u username -p
例如 mysql -uroot -p123456 有安全隐患，所以一般建议mysql -uroot -p回车后再输入口令
退出mysql客户端：quit
查看mysql版本：select version(); 在mysql的命令行客户端中执行
关闭数据库服务：net stop mysql
数据库服务器开机自启：mysqld --install

创建数据库: create database 数据库名称 default character set utf8;
create database test default character set utf8;
创建成功后，在对应的安装路径下出现一个data/test文件夹

查看所有的数据库 show databases;

选定默认数据库：use dbname;
例如 use test;

查看当前的数据库：select database();

显示当前数据库中所有表：show tables;

创建新表： create table 表名称(列名称 数据类型 约束规则,…) engine=数据库引擎 default charset utf8;
mysql> create table t_student(
-> id bigint primary key,
-> name varchar(20) not null,
-> sex boolean default 1
-> ) engine=innodb default charset utf8;
创建表结束后则会发现文件夹data/test/下出现一个文件t_student.ibd
如果设置engine=myisam则会创建两个文件
myd数据信息文件，是表的数据文件
myi索引信息文件，是表数据文件中任何索引的数据树

查看表中的列定义
简单方法： desc t_student;
查看具体的列定义
show create table t_student;

放弃正在输入的命令：\c
显示命令清单：\h
查看MySQL服务器状态信息：\s
Mysql已存数据库
information_schema – 系统数据库，这个数据库损坏就会导致DBMS无法正常启动，所以这个数据库不要用户使用
schemata – 可以通过此表获取已经有数据库
tables – 可通过此表获知某数据库的所有表.
mysql-系统数据库，其中包括时区、权限等相关配置。也是属于系统数据库，一般不直接修改，仅供DBMS使用
sys系统数据库，其中保存和锁、统计等相关信息，也是属于系统数据库，不直接修改，仅供DBMS使用
performance_schema系统数据库，不直接修改，仅供DBMS使用。用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况

test – 非系统数据库,测试使用
SELECT * FROM TABLES WHERE TABLE_SCHEMA=‘mysql’ 是查询mysql数据库中的所有表

常见的SQL命令
SQL语言都基于SQL92标准，但各数据库之间又存在差异。目前最新的标准为SQL99，它同样兼容SQL92。
SQL-Structured QueryLanguage结构化查询语言。
Oralce划分为DDL、DML、DCL、TCL
MySQL划分为DDL、DCL、DQL、DML

DDL – 数据定义语言Data Definition Language
建库、建表、设置约束等：create\drop\alter
truncate表的截断
DCL – 数据控制语言　Data Controll Language
数据授权，收回授权。grant\revoke
DQL - 数据查询语言
select
DML－数据操纵语言Data Manipulation Language
主要指数据的增删改。delete\update\insert\call

SQL 对大小写不敏感；例如Insert InSerT没有区别
默认字符串比较也不区分大小写
默认分号是在数据库系统中分隔每条SQL语句的标准方法，可以使用delimiter进行修改

数据库涉及字符规范（不是语法硬性规则）
命名采用26个英文字母和0-9这十个自然数，加上下划线’_'组成,共63个字符.不能出现其他字符(注释除外)
以上命名都不得超过30个字符的系统限制.变量名的长度限制为29(不包括标识字符@)
数据对象、变量的命名都采用英文字符,禁止使用中文命名.绝对不要在对象名的字符之间留空格
如果使用特殊符号，请使用反引号括起来。create table t 1(id int); 注意这里不是单引号，是反引号
小心保留词,要保证你的字段名没有和保留词、数据库系统或者常用访问方法冲突

保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性.假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型

创建数据表的语法规则
CREATE TABLE <表名>
–（<列名> <数据类型> [ <列级完整性约束条件> ]
–[，<列名><数据类型>[ <列级完整性约束条件>] ] …
–[，<表级完整性约束条件> ] ）；
其中:
<表名>：所要定义的基本表的名字
<列名>：组成该表的各个属性（列）
<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

DBMS为了保证数据的有效性，给开发者提供了3种完整性约束，用于定义DBMS在数据入库时进行检查，如果合法则入库，否则报错拒绝
实体完整性—主键约束 primary key ----非空唯一
表定义中不是必须有主键 create table t1(id int)
参照完整性—外键约束 foreign key—表示某个列的取值范围在另外一个列的范围内
用户自定义完整性—4种用于自定义约束 not null/null default check unique
额外：域完整性-----取值范围

删除表: drop table 表名称; 如果存在则删除表，否则报错
drop table if exists t1;

需求：
建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，其中学号不能为空，值是唯一的，并且姓名取值也唯一

ER图

create table student(
sno bigint primary key auto_increment,
sname varchar(20) not null unique,
ssex char(1) default ‘男’,
sdept varchar(32)
)engine=innodb default charset utf8;
MySQL的数据类型
数值列类型
mysql提供了五种整型： tinyint、smallint、mediumint、int和bigint。int为integer的缩写。这些类型在可表示的取值范围上是不同的。
整数列可定义为unsigned从而禁用负值；这使列的取值范围为0以上。各种类型的存储量需求也是不同的。取值范围较大的类型所需的存储量较大。
mysql 提供三种浮点类型： float、double和decimal。

create table t1() 在当前数据库中表名称不允许重复，如果重复则报错
create table if not exists t1 如果t1表不存在才执行创建操作，如果已存在则不执行任何操作
无符号的写法：
create table t13(id tinyint unsigned);
定点的浮点数
create table t14(id numeric(8,2)); 表示总位宽为8位，小数为2位，其中的正负号和小数点不进行计算
小数处理采用的是四舍五入

完整性约束
primary key 用于声明主键，非空唯一。一个表只能定义一个主键，但是主键允许使用多个列构成(复合主键)
create table t17(id int primary key);
create table t18(
id int, name varchar(20),
primary key(id,name)); 表示id和name的组合不允许为空【任何一个都不允许为null】，不允许重复，不是其中某个列不允许为空，不允许重复
4种用户定义约束
null允许为空，默认/not null不允许为空 id int not null表示id列不允许为空，如果插入null值则报错

default设置默认值，如果试图插入null，则加入null，默认值无效；如果不针对当前列进行操作，则默认值生效
create table t1(id int default 1,name varchar(20));
Insert into t1 values(null,‘yan’); 此时id插入null值。如果insert into t1(name) values(‘yan’)则默认值生效

check检查型约束，可以定义，语法create table t1(id int check(id>5)); 要求插入的数据必须满足条件id>5，但是在mysql中无效

unique唯一性约束，表示列中的数据不允许重复，否则报错，拒绝操作
create table t1(id int unique); 允许为null，null表示不确定的值
数值列属性
AUTO_INCREMENT以标识记录。在MySQL中可通过数据列的AUTO_INCREMENT属性来自动生成。MySQL支持多种数据表，每种数据表的自增属性都有差异
create table t16(id int primary key auto_increment); 只有不插入数据或者插入null值时才会自动生成自增的值

字符串列类型
在mysql中使用单引号表示字符串类型，事实上不区分单双引号，但是一般建议使用单引号，允许使用\转义字符
类型名 参数 说明
char(n) 0-255 定长字符串
varchar(n) 0-65535 可变长字符串
create table t1(id char(2)); 表示id为字符串类型，允许最大存储2个字符，不区分中英文
char(2)表示存储的字符个数为2，如果实际长度大于2则报错；如果实际存放的字符数不足，则自动末尾补充空格。
varchar(2)表示存储的字符个数最大为2，存放字符数不足时按照实际长度存放
如果存放字符串末尾是空格时，会自动裁剪到满足长度限制为止，注意不能裁剪开头的和中间的空格
等值判定时空格不参与
char与varchar的区别
1、char是定长数据，如果char(10)但里面只保存了1个字符，则它的实际长度仍然是10。Mysql对查询的结果进行了处理 ，所以使用length无法判断它所占用的长度；varchar(10)时，如果里面只保存了1个字符，则它的长度为1，所以varchar被称了可变长字符
2、char的取值范围为0-255；varchar的取值范围为0-65535。如果需要存储的字符串的长度跟所有值的平均长度相差不大，适合用char，如MD5。 对于经常改变的值，char优于varchar，原因是固定长度的行不容易产生碎片。
3、对于很短的列，char优于varchar，原因是varchar需要额外一个或两个字节存储字符串的长度
4、char和varchar后面如果有空格，char会自动去掉空格后存储，varchar虽然不会去掉空格，但在进行字符串比较时，会去掉空格进行比较
大对象类型

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求
TINYTEXT可变长度最多 255 个字符；TEXT可变长度最多 65535 个字符；MEDIUMTEXT可变长度最多 16M个字符；LONGTEXT可变长度，最多4G个字符
set和枚举

enum枚举类型实际上是当作字符串进行处理，设置当前列中所允许的取值，取值只能是枚举值中的任意一个值
create table t1(id enum(‘a’,’b’,’c’));
set集合类型实际上是当作字符串进行处理，设置当前列中所允许的取值，取值可以是集合中的任意多个值
create table t1(id set(‘a’,’b’,’c’));
日期和时间列类型

date类型只能存放日期，datetime可以存放日期和时间

datetime一般供用户使用，而timestamp可以存放1970-2038年的日期和时间，一般供系统使用

默认值的设置问题

需求：需要当不添加数据时，自动填充服务器的系统当前时

位类型bit
bit 表示1个二进制的位 bit(8) 表示8个二进制的位，其中n的取值范围为1-64

注意boolean类型

总结
定义数据类型就是定义列，数据类型决定数据的特性
数据类型主要分为字符串类型，浮点书类型和定点数类型，日期类型以及二进制类型
数据类型在不同的存储引擎上表现不同
根据所采用的数据类型，需求和数据特性选择数据类型
删除操作drop
删除数据库 DROP DATABASE <数据库名>;
drop database test;
删除数据表 DROP TABLE <表名>;
drop table t_users;
基本表删除数据、表上的索引都删除
表上的视图往往仍然保留，但无法引用
删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述（标准中没有，认为表建立后就永久存在）
show create table t1;
•
修改基本表alter
修改表 ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束] ]
[ DROP <完整性约束名> ]
[ MODIFY <列名> <数据类型> ]；
说明：
<表名>：要修改的基本表
ADD子句：增加新列和新的完整性约束条件
DROP子句：删除指定的完整性约束条件
MODIFY子句：用于修改列名和数据类型

基本练习
1、创建一个数据库test，如果数据库已经存在则删除
drop database if exists test;
create database test default character set utf8;
2、切换当前数据库
select database(); 查看当前数据库
use test;
3、创建一个用于存储学生信息的学生表 student
编号id，姓名name，成绩score
show tables; 查看当前库中的所有表
drop table if exists student; 如果已经存在student表则执行删除操作
create table if not exists student(
id bigint primary key auto_increment,
name varchar(10) not null,
score numeric(4,1) default 0
)engine=innodb default charset utf8;
4、向Student表增加“入学时间”列，其数据类型为日期型
alter table student add scome date;
不论基本表中原来是否已有数据，新增加的列一律为空值。所以如果表中已经有数据，则注意not null约束的问题
5、将年龄的数据类型改为半字长整数
alter table student add age int; 向表中添加一个int类型的列age
desc student; 查看当前的表结构
alter table student modify age smallint;
6、删除学生姓名必须取唯一值的约束
alter table student modify name varchar(10) unique;

alter table student drop name; 删除指定列name
alter table student add name varchar(10); 重新添加列name
对表的基本操作 DML+DQL
增加数据:
Insert into 表名称 values(值的列表)
Insert into tb_users values(1,’yan’,’123’) 注意和表的列定义一一对应，当值不确定时也必须写null
Insert into 表名称(列名称,…) values(对应的值,…) 注意一一对应，注意数据类型。如果没有出现的列则自动插入null

插入数据总结
字符类型和日期类型的数据前后要添加’’单引号
没有指定列时，要给出完整数据，且要对应顺序：
Insert into student values(1,’Tom’,’1’);
优化写入，指定列：
Insert into stud(id,name)values(1,’jack’);

修改数据
update 表名称 set 列名称=值, … 将指定表中指定列的值全部修改为指定值
update stud set name=‘Jack’; 将stud表中的所有行的name列值修改为jack
update 表名称set 列名称=值, … where 条件 只修改满足条件的指定行上列值
update stud set name=‘Jack’where id=1; 将stud表中的id值为1的行的name列值修改为jack

同时修改两个值：update stud setname=‘Rose’,id=22 where id=1;

在原值上面进行累加操作：update product set price=price1.1 －所有商品的价格上涨10%
update product set price=price1.1 where cname=‘食品’－只有列名为cname值为食品的商品，价格上涨10%

删除数据
delete from 表名称; 删除表中的所有数据,表结构仍旧存在,类似truncate table 表名称
delete from 表名称 where 条件; 根据条件删除数据，例如delete from tb_users where id>3则会删除所有id>3的行
delete from stud 全部删除
delete from stud where id=1; 删除指定的行
特殊写法： delete from stud where 1=1 delete from stud where 1=2

也可以使用 TRUNCATE TABLE stud; 它与delete的区别是不记录操作日志，即无法恢复数据
从执行效果上看 truncate table stud和delete from stud一致，都是删除了stud表中的所有数据，但是执行是有区别的。 【面试】
truncate table不是DML语句，没有事务的概念，所以直接执行删除。不能撤销操作
delete from属于DML语句，有事务，所以在删除前需要记录日志，以供事务执行回滚撤销操作。
一般针对大量数据的删除操作，使用truncate执行效率高

查询数据
SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] … FROM <表名或视图名>[，<表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ]；

select * from 表名称 查询指定表中的所有行的所有列数据，用于指代所有列。事实上一般建议写列名称，而不是号
select * from tb_users;
Select 列名称,… from 表名称 查询指定表中的所有行的指定列的数据，没有指定的列不查询
select username,password from tb_users; 只获取username和password两个列的值，而id列的值不获取

select *或者指定列 from 表名称 where 条件; 只查询满足条件的行
条件：
比较 >大于 >=大于等于 <小于 <=小于等于 !=不等于 =等于比较，不是赋值
select * from stud where age>=18; 查询年龄大于等于18岁的学生
select * from stud where age=18; 注意=不是赋值，是等值判断，查询年龄为18岁的学生
特殊比较 =null空值判断
在数据库中null表示不确定的值，所以判断不能使用=号，只能通过is null判空或者is not null非空进行判断
select * from student where name is null;
select * from student where name is not null;

注意：’’空字符串和null不是一样的 is null可以获取id=6，但是不能获取id=7

from语句在mysql中不是必须的【数据库不同有所区别】

字符串的比较
允许进行大小的比较，但是一般使用相等和不等于的判定较多
select * from tb_users where username=’zhangsan’ and password=’123456’

模糊查询 like _和%，例如查询所有姓张的学生
使用通配符_或者%，必须使用like或者not like运算符，不能使用=号
表示一个任意字符
查询所有姓张的同学 select * from stud where name like ‘张%’ 其中%表示任意个数的任意字符
查询所有姓张的同学，并且名字为2个字符 select * from stud where name like '张’ 其中_表示1个任意字符
查询所有名字中有书字的同学 select * from stud where name like ‘%书%’
查询所有名字以书字结尾的同学 select * from stud where name like ‘%书’

多条件查询
and与 or或 not非
查询所有的姓张的男生 select * from stud where name like ‘张%’ and sex=1
查询姓张或者年龄在18岁以下的学生 select * from stud where name like ‘张%’ or age<18
查询姓张或者年龄在18岁以下的男生 select * from stud where (name like ‘张%’ or age<18) and sex=1
查询所有年龄不在18岁以下的学生 select * from stud where not(age<18)

术语
元组是关系数据库中的基本概念，是事物特征的组合，可以描述一个具体的事物。
关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，元组的集合称为关系，每列就是一个属性。 在二维表里，元组也称为行，属性为列。

字段：某一个事物的一个特征或者说是属性
列：字段的另一种称谓

表：记录的属组合，表示同一类事物的组合。
主键：能唯一标识信息的事物
域：属性的取值范围

关系模式：关系的描述，表示为：关系名（属性列表） 例如：学生（学号，姓名，性别，年龄，系别）
关系的约束：实体完整性约束，参照完整性约束，用户自定义完整性约束【域完整性约束】

练习：
学生表：Student(Sno，Sname，Ssex，Sage，Sdept)
课程表：Course(Cno，Cname，Cpno，Ccredit)
学生选课表：SC(Sno，Cno，Grade)
题目：
查询全体学生的学号与姓名
select sno,sname from student
查询全体学生的姓名、学号、所在系
select sname,sno,sdept from student
查询全体学生的详细记录
select * from student
Select sno,sname,ssex,sage,sdept from student
定义别名的方法 as
select id as 编号, name as 姓名, age 年龄 from student;
JDBC编程基础
JDBC英文名为：Java Data Base Connectivity(Java数据库连接)，官方解释它是Java编程语言和广泛的数据库之间独立于数据库的连接标准的Java API，根本上说JDBC是一种规范，它提供的接口，一套完整的，允许便捷式访问底层数据库。
可以用JAVA来写不同类型的可执行文件：JAVA应用程序、JAVA Applets、Java Servlet、JSP等，不同的可执行文件都能通过JDBC访问数据库，又兼备存储的优势。简单说它就是JAVA与数据库的连接的桥梁或者插件，用JAVA代码就能操作数据库的增删改查、存储过程、事务等。
JDBC概述
JDBC(Java DataBase Connectivity)
为Java语言定义的一个SQL调用级的统一界面
为访问关系数据库提供了一个标准的界面
从JDK1.2开始已包括了JDBC2.0的内容
•
JDBC(Java Database Connectivity，Java数据库连接)是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一的访问接口。
JDBC由一组用Java语言编写的类与接口组成，通过调用这些类和接口所提供的方法，用户能够以一致的方式连接多种不同的数据库系统，进而使用标准的SQL语言来存取数据库中的数据

使用JDBC访问MySQL
建立连接
每个JDBC应用程序至少要有一个JDBC驱动程序版本，JDBC驱动程序是Driver接口类的实现
需要将数据库生产厂商提供的驱动jar包添加到classpath中。驱动jar包可以从官网上下载或者从maven之类的仓库中下载
1、在java项目中新建文件夹Folder，名称为libs
2、将下载的jar包拷贝到libs文件夹下
3、将jar包添加到classpath上

Driver类是驱动程序厂家实现的接口，Driver使DriverManager和JDBC应用程序层可以独立于具体的数据库系统
java自身提供了对各类主流数据库系统的支持
通过java.sql 库，提供了统一的接口，使得可以在java环境下不必对程序作大规模的修改，只要更改相应的驱动程序，即可实现对各类数据库的操作，提高软件的生存周期和降低软件的开发成本和维护费用
编程获取数据库连接：
1、加载驱动程序
2、通过驱动管理器获取和数据库的连接

localhost是数据库服务器的IP地址，3306是数据库服务器的端口号[3306\1521\1433]，test是连接的数据库名，？是额外参数，这里的serverTimezone用于设置使用的时区配置
1、加载JDBC驱动程序
在和某一特定数据库建立连接之前，必须首先加载一种可用的JDBC驱动程序。这需要使用java.sql包中的方法来加载JDBC驱动程序：Class.forName(“DriverName”);
DriverName是要加载的JDBC驱动程序名称
实际上还有一种简化写法 new 驱动类名称();
采用的是Access数据库，所以加载Access数据库驱动程序的方法为：Class.forName(“sun.jdbc.odbc. JdbcOdbcDriver”);
JDBC的Driver可分为以下4中类型：JDBC-ODBC Bridge和ODBC Driver、Native-API partly-Java Driver、JDBC-Net All-Java Driver、Native-protocol All-Java Driver
2、创建数据库连接
创建和指定数据库的连接需要使用DriverManager类的getConnection()方法
一般的使用格式：Connectionconn = DriverManager.getConnection (URL,username,password);该方法返回的是一个Connection对象。这里的URL是一个字符串，代表了将要连接的数据源，即具体的数据库位置。不同的JDBC驱动程序其URL是不同的。
Class.forName(“sun.jdbc.ordc.JdbcOdbcDriver”);
Connection Conn = DriverManager.getConnection("jdbc:odbc:yy”);
注意：数据库连接属于稀有资源，所以必须保证及时关闭。采用try/finally接口或者try(resource){}自动关闭

3、执行SQL语句
在与某个特定数据库建立连接之后，连接会话就可以用于发送SQL语句。在发送SQL语句前，必须创建一个类的对象，该对象负责将SQL语句发送给数据库
创建Statement对象：Statement smt =conn.create Statement();
PreparedStatement是Statement的子接口

执行修改操作 insert /update /delete

修改操作的返回值是一个int类型的值，表示所执行的sql语句操作了多少行数据

Statement对象的executeQuery()方法来执行数据库查询语句，executeQuery()方法返回一个ResultSet类的对象，它包含了SQL查询语句执行的结果
ResultSet rs = smt.executeQuery(“SELECT * FROM student”);

注意：获取数据时不建议通过列的索引号来获取对应列的数据
4、接收并处理SQL的返回结果
JDBC接收结果是通过ResultSet类的对象来实现的
一个ResultSet对象包含了执行某个SQL语句后满足条件的所有的行，它还提供了对这些行的访问，用户可以通过一组get方法来访问当前行的不同列
通常结果集的形式是一张带有表头和相应数值的表
5、关闭创建的各个对象
一个Statement对象在同一时间只能打开一个结果集，所以如果在同一个Statement对象中运行下一条SQL语句时，第一条SQL语句生成的ResultSet对象就被自动关闭了
当然也可以通过调用ResultSet接口的close()方法来手工关闭
关闭Statement对象和Connection对象可以分别使用各自的close()方法。

修改操作的完整代码:

注意：针对connection对象必须保证及时关闭，所以一定应该使用try/finally结构。正确的编程
public class Test1 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
int len=0;
try {
Class.forName(“com.mysql.cj.jdbc.Driver”);// 加载字符串对应的类
conn = DriverManager.getConnection(“jdbc:mysql://localhost:3306/test?serverTimezone=UTC”, “root”, “123456”);
stmt = conn.createStatement();
String sql = “insert into student1(name,sex,age,score) values(‘wangwu’,1,19,83.5)”;
len = stmt.executeUpdate(sql);// 提交并执行sql语句
} catch (Exception e) {
//基本上java.sql包中的操作都会引发一个受检型异常SQLException，但是一般针对这种异常没有什么好的处理方案
e.printStackTrace();
} finally {
try {
if (stmt != null)
stmt.close();
} catch (Exception e2) {
e2.printStackTrace();
} finally {
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 需要执行的sql语句
if (len > 0)
System.out.println(“插入成功!”);
else
System.out.println(“插入失败!”);
}
}
public class Test1 {
public static void main(String[] args)throws Exception {
int len=0;
new Driver();
try(
Connection conn = DriverManager.getConnection(“jdbc:mysql://localhost:3306/test?serverTimezone=UTC”, “root”, “123456”);
Statement stmt = conn.createStatement();
){
String sql = “insert into student1(name,sex,age,score) values(‘wangwu’,1,19,83.5)”;
len = stmt.executeUpdate(sql);// 提交并执行sql语句
}
if (len > 0)
System.out.println(“插入成功!”);
else
System.out.println(“插入失败!”);
}
}

PreparedStatement

基本用法

PreparedStatement提供的功能：
1、允许sql语句中使用?占位符，表示参数
2、支持预编译功能
3、在一定程序上可以避免sql注入漏洞

查询所有的姓yan，年龄18岁以上男学生

注意：
Connection接口
Statement createStatement() 创建用于执行SQL语句的Statement对象
PreparedStatement prepareStatement(String sql)创建PrepareStatement对象，用于实现数据库的动态访问
参数：sql —— 是SQL的INSERT，UPDATE和DELETE语句，或是无返回结果的语句
prepareCall(sql)：创建执行存储过程的callableStatement对象
void close() 结束Connection对象对数据库的连接。强调：数据库连接属于稀有资源，必须保证及时关闭
boolean isClose() 测试是否已经关闭Connection对象对数据库的连接
setAutoCommit(boolean autoCommit)：设置事务是否自动提交
commit() ：在链接上提交事务
rollback() ：在此链接上回滚事务

Statement接口
Statement接口用于执行静态的SQL语句，并将SQL语句的执行结果返回

ResultSet executeQuery(String sql) ：执行SQL查询语句，返回满足条件的结果集。注意：ResultSet中并不是存储数据，而是存储一个行指针。所以必须在关闭之前操作ResultSet。如果连接关闭在访问ResultSet则异常
参数：sql —— 是SQL的SELECT语句
int executeUpdate(String sql) ：执行SQL数据更新语句，返回值为结果集中的记录数
参数：sql —— 是SQL的INSERT，UPDATE和DELETE语句，或是无返回结果的语句

boolean execute(String sql) ：执行任意SQL语句。如果第一个结果是ResultSet，则返回true；否则如果是整数型，则返回false。参数：sql —— 可以是任何SQL语句
void close() ：关闭当前的Statement对象
通过Connection接口建立Statement对象，可以使用executeQuery()方法对数据库表中存储的数据进行查询以及使用executeUpdate()方法进行插入、修改和删除记录的操作。必须保证关闭Connection，一般建议关闭Statement

addBatch(String sql) ：把多条sql语句放到一个批处理中
executeBatch()：向数据库发送一批sql语句执行。

三种Statement类
Statement：由createStatement创建，用于发送简单的静态SQL语句（不带参数）

PreparedStatement ：继承自Statement接口，由preparedStatement创建，用于发送含有一个或多个参数的SQL语句。PreparedStatement对象支持预编译，反复执行一个只是参数不同的sql语句时，比Statement对象的效率更高，并且可以防止SQL注入，所以我们一般都使用PreparedStatement

CallableStatement：继承自PreparedStatement接口，由方法prepareCall创建，用于调用存储过程。

Statement和PreparedStatement的区别
Statement只能处理静态SQL;
PreparedStatement既能处理静态sql也能处理动态sql,它继承了Statement的特点
站在预处理角度: PreparedStatement适合做连续多次结构相同的sql语句,有优势.
Statement适合做连续多次不同结构的sql语句,有优势.
Sql注入漏洞，最终的解决方案还是需要通过编码实现

MySQL运算符
算术运算符、比较运算符、逻辑运算符
算术运算符
只能针对数值类型进行计算，如果是非数值类型则自动转换，如果转换失败则0进行处理

比较运算符

like
配合%和_进行模糊查询
In
查询年龄在15\19\21的学生 where age=15 or age=19 or age=21
使用in where age in (15,19,21)
不在某范围内 where age not in (15,19,21)
Between
查询工资在1000到2000之间 where salary>=1000 and salary<=2000
使用between/and的写法 where salaray between 1000 and 2000
不在某个范围内 where salary not between 1000 and 2000
between和in的区别
In用于离散值 between用于连续值
使用正则式进行条件判断

判空处理

逻辑运算符

练习：查姓李学生的姓名及其出生年份
练习：查全体学生的姓名及其出生年份。
select 2021-age birth,age from student;
练习：查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄
练习：查询年龄不在20~23岁之间的学生姓名、系别和年龄
select name,dept,age from student where age between 20 and 23 —连续值
select name,dept,age from student where age in(20,21,22,23) —离散值

消除取值重复的行
在SELECT子句中使用DISTINCT短语
Select distinct Sno fromSC 如果distinct后面有多个列，则表示针对多个列的组合进行去重处理

ALL 与 DISTINCT
查询选修了课程的学生学号。
SELECT Sno FROM SC 或(默认 ALL) SELECT ALL Sno FROMSC;
SELECT DISTINCT SnoFROM SC;
注意 DISTINCT短语的作用范围是所有目标列

例：查询选修课程的各种成绩
错误的写法：SELECT DISTINCT Cno，DISTINCT Grade FROM SC;
正确的写法：SELECT DISTINCT Cno，Grade FROM SC;

limit的用法
Select * from student limit 3; 表示从第0行开始获取前3行数据
Select * from student limit 2,3; 表示从第2行开始获取前3行数据

查询3名男生 select * from student where sex=1 limit 3;
注意顺序

Order by排序
可以按照指定的列值进行排序 正序asc，默认可以省略；逆序desc

按照成绩排序，获取前3个男生
Select * from student order by score 从小到大
Select * from student order by score desc 从大到小

Order by 可以按照一个指定列进行排序，也允许指定多个列进行排序
Select * from student order by score desc, age

select * from student order by age desc,name desc,id desc;

5类主要集合统计函数
是SQL基本函数，聚合函数对一组值执行计算，并返回单个值，也被称为组函数
聚合函数经常与 SELECT 语句的 GROUP BY 子句的HAVING一同使用
计数
统计总行数COUNT（[DISTINCT|ALL] *）
COUNT（[DISTINCT|ALL] <列名>）
select count(distinct age) from student; 取消age重复值后进行统计
select count(age) from student; 不会进行去重的统计
针对null的处理

count（*）

Count(id)返回值和count(*)不一致，即使表中只有一个列id，*和id的含义不一致

null值根本就不参与运算
计算总和
SUM（[DISTINCT|ALL] <列名>）
获取所有学生的成绩总和
Select sum(score) from student;
参与运算的列应该是数值型。
如果不是数值型则自动进行尽可能的转换，转换成功后按照对应的值进行处理，否则按照0处理。 例如select ‘11a’+’b12’返回为11，select ’11a’+’12b’返回23
计算平均值
AVG（[DISTINCT|ALL] <列名> ） null值不参与
求最大值
MAX（[DISTINCT|ALL] <列名>）
求最小值
MIN（[DISTINCT|ALL] <列名>）

group分组操作
select sex,count() from student group by sex; 按照性别统计人数
注意： group by后面跟的列是允许出现在select之后，如果不是group by后面的列只能出现在集合函数之内
select sex,count(),max(age) from student group by sex;

如果group by后面跟多个列，则表示多列相等为一组
select year(now())-age bb,count() from student group by bb;
mysql> select year(now())-age bb,count() from student group by year(now())-age;

针对分组追加条件having
例如查询人数多于3的组

注意sex=null的数据只有2行