MySQL

一、基础知识

基本概念
- 数据(Data)：数据是数据库中存储的基本对象，是描述事物的符号记录
- 数据库（Database,DB）:存储数据的仓库，长期存储在计算机中（永久存储），有组织、可共享的相关数据集合
- 数据库管理系统（Database Management System，DBMS）:专门用于数据库创建和管理的软件
- 数据库系统（Database System DBS）:支持数据库的相关计算机硬件、软件、人员
数据管理技术的发展阶段
- 人工管理阶段
- 文件系统阶段
- 数据库系统阶段：数据结构化、数据共享、数据独立性高、数据统一由DBMS管理和控制
三级模式二级映像与两个独立性
- 外模式
  - 外模式/模式映像->数据的逻辑独立性
- 概念模式
  - 模式/内模式映像->数据的物理独立性
- 内模式
数据模型
- 现实世界
- 信息世界
  - 概念模型
    - E-R图
      - 实体矩形框
      - 属性椭圆形
      - 联系菱形
        
        1:1
        
        1:n
        
        n:m
- 机器世界
  - 逻辑模型
    - 层次模型
    - 网状模型
    - 关系模型
  - 物理模型
SQL
常见的数据库产品
- Oracle：收费的大型数据库。Oracle公司的产品
- MySQL：开源免费功能强大（企业版收费，社区版免费）
- DB2：IBM公司,收费的，常应用于银行系统中
- SQLServer：微软公司收费的中型的数据库
- SQLite：嵌入式的小型数据库，应用在手机端
安装
- 下载安装压缩包
- 解压缩到具体位置（即mysql安装目录）比如C:\mysql-5.5.48-winx64\mysql-5.5.48-winx64
- 管理员身份打开命令提示符
- 切换到MySQL安装目录下的bin目录： cd mysql安装目录\bin
  - cd是change directory（改变目录）的缩写
  - 进入下一级目录，输入命令 "cd 子目录"
  - 返回到当前目录的上一级目录，输入命令“cd..“
- mysqld -install
- 创建MySQL配置文件：my.ini [mysqld] basedir=mysql安装目录 datadir=mysql安装目录\data port=3306
- 初始化数据库（5.7版本及以上执行此步骤）： mysqld --initialize-insecure -user=root
- 启动MySQL服务
  - 方法一：计算机->管理->服务和应用程序->服务
  - 方法二：”命令提示符：cmd->services.msc
  - 方法三：以管理员身份运行命令提示符
    - net start mysql
卸载
- 停止MySQL服务
- 以管理员身份运行命令提示符：cmd
- 切换到MySQL安装目录下的bin目录：cd mysql安装目录\bin
- mysqld -remove
- 删除文件
登录与退出
- 设置系统环境变量
  - 命令提示符如果不在bin目录下，需执行cd命令切换目录，这样操作比较麻烦，可以将bin目录添加到环境变量中
    - 方法一：右键【我的电脑】->【属性】->【高级系统设置】->【高级】->【环境变量】，在系统变量中选择path，增加 bin目录的路径
    - 方法二：执行命令 setx PATH "%PATH%;路径"
- 登录
  - mysql -u root
  - mysql -u root -p密码
  - mysql -u root -p 回车后输入密码
  - -u root表示以root用户的身份登录，-u和root之间的空格可以省略。
- 设置密码
  - 5.7以上版本
    - 设置密码语句：ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
    - 取消密码语句：ALTER USER 'root'@'localhost' IDENTIFIED BY '';
  - 5.7以下版本
    - 设置密码语句：
    - USE mysql; UPDATE user SET password=PASSWORD("123456") WHERE USER='root'; FLUSH PRIVILEGES;
  - 通用
    - SET PASSWORD FOR 'root'@'localhost'=PASSWORD("123456");
- 退出
  - EXIT
SQL
- 定义
  - Structured Query Language：结构化查询语言
  - 是用于访问和处理关系数据库的标准的计算机语言
- 组成
  - DDL
    - Data Definition Language 数据定义语言
    - 用来定义数据库对象：数据库，表，列等，关键字：CREATE, DROP,ALTER等
  - DML
    - Data Manipulation Language 数据操纵语言
    - 用来对数据库中表的数据进行增删改，关键字：INSERT DELETE UPDATE等
  - DQL
    - Data Query Language 数据查询语言
    - 用来查询数据库中表的记录（数据）关键字：SELECT
  - DCL
    - Data Control Language 数据控制语言
    - 用来定义数据库的访问权限和安全级别，及创建用户、关键字：GRANT REVOKE ROLLBACK COMMIT等
- 语法
  - 语句可以单行或多行书写，以分隔符（常用分号）结尾
  - 不区分大小写，但强烈建议遵循规范
    - 关键字与函数名称全部大写
    - 数据库名称、表名称、字段名称等全部小写

测试一、安装Mysql

1.1.在MySQL官网下一个MySQL安装包到C盘目录下，一个是免安装版mysql-5.7.22-winx64.zip

在www.mysql.com 上面下载社区版5.7.22 64位版本的mysql

1.2.进行免安装版mysql-5.7.22-winx64.zip的Mysql安装

切换到MySQL安装目录下的bin目录： cd mysql安装目录\bin
mysqld -install
mysqld -install
.初始化数据库mysqld --initialize-insecure -user=root
启动mysql服务net start mysql

1.3.登录MySQL

mysql -u root
mysql -u root p(pssword)

1.4.运行MYSQL客户端相关命令

设置密码ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

退出 exit

1.5.登录MySQL

使用新密码登录 mysql -u root -p123456

1.6.卸载Mysql

停止服务net stop mysql
卸载mysql mysqld -remove

1.7配置环境变量

统属性-高级-环境变量-系统变量-Path-编辑-新建-mysql的bin目录 -保存-确定

二、基本操作

数据库与数据表的基本操作

登录MYSQL客户端

执行命令：>mysql -u root -p123456
查看MySQL服务器当前有哪些数据库

执行命令：SHOW DATABASES;
创建一个名称为mydb的数据库

执行命令：CREATE DATABASE mydb;
再创建一个名称为mydb1的数据库

CREATE DATABASE mydb1;
再次查看MySQL服务器当前有哪些数据库

截图:
查看创建数据库mydb的信息

截图：
删除数据库mydb1

执行命令：DROP DATABASE mydb1
在mydb数据库中创建名称为goods的数据表，表结构如下：

字段名称	数据类型	说明
id	INT	编号
name	VARCHAR(32)	商品名
price	INT	价格
description	VARCHAR(255)	商品描述

注意：创建表之前，应该使用“USE数据库名”指定是在哪个数据库中进行

截图：

查看mydb数据库中有哪些数据表

执行命令：SHOW TABLES;
查看数据表goods的字段信息

执行命令：SHOW COLUMNS FROM goods;
查看数据表的创建语句

执行命令：SHOW CREATE TABLE goods
修改数据表goods的名称为my_goods

执行命令：ALTER TABLE goods RENAME my_goods;
将表中字段description 修改为des

执行命令：ALTER TABLE my_goods CHANGE description des varchar(255);
查看数据表结构

截图：
将Name字段的数据类型修改为CHAR(16)

执行命令：ALTER TABLE my_goods CHANGE name name CHAR(16);
将my_goods表中的最后一个字段des移动到name字段后

执行命令：ALTER TABLE my_goods MODIFY des varchar(255) AFTER name;
新增如下两个字段

字段名称	数据类型	位置
num	int(11)	字段name后面
brand	VARCHAR(64)	最后

ALTER TABLE my_goods ADD num INT(11) AFTER name;

ALTER TABLE my_goods ADD brand VARCHAR(64);

查看my_goods表中的字段

执行命令：SHOW COLUMNS FROM my_goods;
删除字段brand

执行命令：ALTER TABLE my_goods DROP brand ;
删除数据表my_goods

执行命令：DROP TABLE my_goods;
修改root用户登录密码为admin

截图：

三、数据的基本操作

数据操作

复制表结构和数据
- CREATE TABLE 表名 LIKE 旧表名
- 全部复制：INSERT INTO 数据表名1 [(字段列表)] SELECT [(字段列表)]FROM 数据表名2;
- 指定字段列表复制：
- 蠕虫复制
  - 意义：从已有表拷贝数据到新表中；
  - 可以迅速的让表中的数据膨胀到一定的数量级：测试表的压力以及效率
主键冲突
- 在数据插入的时候，假设主键对应的值已经存在，插入一定会失败
- 当主键存在冲突的时候，可以选择性的进行处理：更新和替换
- 更新操作:
  - INSERT INTO 表名 ( 字段列表：包含主键) VALUES(值列表) on duplicate key update 字段=新值;
- 替换操作：
  - REPLACE INTO 表名 VALUES(值列表)
删除
- 如果表中存在主键自增长，删除数据后，自增长并不会还原
- 思路：数据的删除不会该表表结构，只能删除表后重建表
- Truncate 表名; --先删除该表，后新增该表
排序
- 在很多实际需求中，用户可以根据看到的某些字段数据进行排序操作，那么这个时候必须要进行分组统计吗？
- 分组的目标是统计，如果只是单纯的想对某个字段数据进行排序，不能使用group by分组，可以使用order by 来实现排序。
limit子句
- 思考：每次数据查看的时候，不是条件获取指定记录就是获取全部记录，有的时候想获取一定数量的记录该怎么实现呢？
- 引入：查询数据的量可以在查询出来之后，再通过其他手段来实现数据控制（如PHP控制），但是这种时候会出现带宽浪费，增加服务器负荷，因此，mysql提供了一种可以获取指定数量记录的方式limit子句
GROUP BY 子句
- 思考：如果在进行数据查询的时候，想根据某个数据进行分组，那么这种时候该怎么操作呢？
- 引入：在MySQL中，where条件判断可以用来筛选部分数据，使得满足条件的数据成为一组。但是如果还需要其他效果，如果统计组内记录数，还得依赖group by分组统计
- 定义：GROUP BY即分组的意思，是根据某个具体的字段，将相同的值分到一组中。
- GROUP BY分组原理
  - 按照分组字段，将获取到的记录分成几块
  - 保留每块的第一条记录
- GROUP BY 目的：GROUP BY分组的目的是为了分组统计，因此配合使用聚合函数
- GROUP BY子句与WHERE子句同时存在的时候，必须在where子句之后
- GROUP BY可以实现多字段分组()
- GROUP BY分组后会自动根据分组字段进行升序排序，可以控制排序方式为降序排序DESC(20通信本10.12)
- 已选课学生的选课门数、最高分、最低分、平均分、总分：
- 查询各课号及相应选课人数：SELECT cno,COUNT(sno) 人数 FROM sc GROUP BY cno；
- 查询各个学生的平均分：SELECT sno,AVG(score) AS 平均分 FROM sc GROUP BY sno;
- WITH ROLLUP：对分组计算结果进行汇总级别的计算
- 回溯统计：回溯统计是指当数据被分组后，系统会在最后额外按照分组字段进行一次统计，此时分组字段会被置空
- GROUP BY分组统计可以进行回溯统计，即查询对应字段未分组之前效果
HAVING子句
- 思考：where子句是在group子句之前，但是如果某些结果必须是通过计算之后才能判定结果的，那么这个时候怎么办呢
- 引入：group by 本身是分组统计的，那么统计之后的确有需求可能会进行判定，这个时候就需要在分组统计后再对结果进行条件限定，这个时候可以在group by子句之后使用having子句
- 定义：where是从磁盘读取数据时进行判定，而在数据进行到内存之后where就不能生效，having是完全针对进入内存后的数据进行判定
- having语法：having几乎能做where所能做的所有事情：having 条件判定
- having主要针对group by后的统计结果进行判定
- 注意：having子句中用到的字段，必须在select后出现过，即字段从磁盘读入到内存中
别名
- 定义：对某些较长名字或者冲突名字指定一个新的名字，在最后查看数据的时候就可以通过新的名字来进行查看
- 字段名/表名 [as] 别名
- 别名的目的是为了让名字变得简洁或者更加方便使用，别名只能在查询数据的SQL指令中使用，别名一般是在复杂的SQL指令中用到

创建的数据库名称是： jwgl

创建的三个数据表名称是：student，course，sc

CREATE DATABASE IF NOT EXISTS jwgl DEFAULT CHARACTER SET='utf8';-- 创建数据库jwgl

USE jwgl;                       -- 转到jwgl

CREATE TABLE IF NOT EXISTS student(         -- 创建学生信息表student
sno CHAR(9),                                    -- sno:学号
sname       VARCHAR(10) ,               -- sname:学生姓名
sgender      CHAR,                           -- sgender:学生性别
sage TINYINT UNSIGNED,               -- sage:学生年龄
sdept CHAR(2) COMMENT 'IS 信息系 CS 计算机系 MA数学系'        -- sdept:学生所在系，IS信息系、CS计算机系、MA数学系
)CHARSET utf8;

#插入数据到学生表中
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815121',"李勇","男",23,"CS");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815122',"刘晨","男",20,"IS");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815123',"王敏","男",21,"MA");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815124',"王大海","女",20,"MA");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815125',"张立","男",19,"IS");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815126',"于大宝","女",21,"IS");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815127',"李斌","男",20,"CS");

CREATE TABLE IF NOT EXISTS course(     -- 创建课程信息表course
cno CHAR(2),                    -- cno:课程编号
cname       VARCHAR(20) ,                           -- cname:课程名称
cpno      CHAR(4) COMMENT '先修课程号',       -- cpno:先修课程号
ccredit TINYINT UNSIGNED                                 -- ccredit:学分
)CHARSET utf8;

#插入数据到课程信息表中
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('01',"数据库",NULL,"4");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('02',"数学",NULL,"2");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('03',"信息系统",NULL,"4");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('04',"操作系统",NULL,"3");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('05',"数据结构",NULL,"4");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('06',"数据处理",NULL,"2");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('07',"BASIC语言",NULL,"4");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('08',"DB_Design",NULL,"3");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('09',"DBSDesign",NULL,"3");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('10',"线性代数",NULL,"2");

#修改课程信息表中部分数据
UPDATE course SET cpno='05'  WHERE cno='01';
UPDATE course SET cpno='01'  WHERE cno='03';
UPDATE course SET cpno='06'  WHERE cno='04';
UPDATE course SET cpno='07'  WHERE cno='05';
UPDATE course SET cpno='06'  WHERE cno='07';
UPDATE course SET cpno='01'  WHERE cno='08';

CREATE TABLE IF NOT EXISTS sc(          -- 创建学生选课信息表sc 
sno CHAR(9),                                          -- sno：学号
cno       CHAR(2) ,                  -- cno：课程编号
score TINYINT UNSIGNED       -- score：成绩
)CHARSET utf8;

INSERT INTO sc (sno,cno,score) VALUES ("201080701","01","92");
INSERT INTO sc (sno,cno,score) VALUES ("201080701","02","85");
INSERT INTO sc (sno,cno,score) VALUES ("201080701","03","88");
INSERT INTO sc (sno,cno,score) VALUES ("201080702","02","90");
INSERT INTO sc (sno,cno,score) VALUES ("201080702","03","59");
INSERT INTO sc (sno,cno,score) VALUES ("201080704","07",NULL);
INSERT INTO sc (sno,cno,score) VALUES ("201080706","08",NULL);

查看表student中的数据

截图：)
在表student中插入下表所示的数据，

sno（学号）	sname（姓名）	sgender（性别）	sage（年龄）	sdept（所在系）
201815128	张三	男	22	MA

截图：

修改课程信息表course的一条数据，将课程编号(cno)是10的学分(cpno)设置为01

截图)
删除表student中学号为201815128的学生信息

截图：
查看数据表student中sno列和sname列的数据

截图：)
查询年龄介于19岁到21岁之间的学生信息

截图
询信息系的女生信息

截图)
查询student表中年龄数据，年龄不重复

截图
显示student表中从第4行起的2条记录

截图)
将student表中数据按年龄降序排序

截图

高级查询

1、用模糊查询:LIKE 操作符，查询student表中姓王的学生

2、用正则表达式查询姓名中包含"大"字的学生

截图

四、数据类型与约束

4.1数据类型

数据类型
- 数值
  - 整数
    - TINYINT
    - SMALLINT
    - MEDIUMINT
    - INT
    - BIGINT
  - 小数
    - 浮点数：FLOAT,DOUBLE
      - FLOAT(M,D):M表示数字总位数，D表示小数点后的位数
      - DOUBLE(M,D):M表示数字总位数，0表示小数点后的位数
    - 定点数：DECIMAL
      - DECIMAL(M,D):M表示数字总位数，D表示小数点后的位数
- 时间和日期类型：
  - 查看当前系统时间信息：
    - SELECT NOW();
    - SELECT CURRENT_DATE();
      - SELECT CURDATE();
    - SELECT CURRENT_TIME(0;
      - SELECT CURTIME();
    - YEAR:表示年份，YYYY
    - DATE:表示年月日，YYYY-MM-DD
    - TIME:表示时分秒，hh:mm:ss
    - DATETIME:表示年月日时分秒，YYYY-MM-DD hh:mm:ss
    - TIMESTAMP:时间戳类型，格式与DATETIME相同(2021.10.18物联)
      - 如果不给该字段赋值，或赋值为null，则默认使用当前的系统时间对其自动赋值
- 字符串类型
  - CHAR：固定长度字符串，需指明长度 CHAR(M)
  - VARCHAR:可变长度字符串，需指明最大长度VARCHAR(M)
  - TEXT:大文本数据
  - ENUM：枚举类型
  - SET:字符串对象
字符集和校对集
- 字符集：用来定义MySQL存储字符串的方式
  - 查看字符集信息：SHOW CHARACTER SET;或SHOW CHARSET;
    - 常用到的字符集：
      - latin1 最大长度：1字节支持西欧字符、希腊字符等
      - gbk 最大长度：2字节支持简体和繁体中文、日文、韩文等
      - utf8 最大长度 3字节支持世界上大部分国家的文字
- 校对集：用来定义比较字符串的方式，如是否区分大小写等
  - 查看校对集信息：SHOW COLLATION;
  - 校对集组成：字符集名称_国家名/general_ci/cs/bin
    - ci:Case Insenstive 不区分大小写
    - cs: Case Sensitive 区分大小写
    - bin：binary 以二进制方式比较
- 字符集和校对集的设置
  - 查看字符集相关变量: SHOW VARIABLES LIKE 'character%';
    - Variable_name 变量名说明
    - character_set_client 客户端字符集
    - character_set_connection 客户端与服务器连接用的字符集
    - character_set_database 默认数据库使用的字符集
    - character_set_filesystem 文件系统字符集
    - character_set_results 将查询结果返回给客户端用的字符集
    - character_set_server 服务器默认字符集
    - character_set_system 服务器用来存储标识符的字符集
    - character_set_dir 安装字符集的目录
  - 可在创建数据库、数据表时指明数据库、数据表、字段的字符集与校对集
  - 通过 SET 变量名=值;来更改，但修改只对当前会话有效
    - 例如：SET character set_server =utf8; -将服务器默认字符集设置为utf8
    - 同时更改客户机、链接和返回结果字符集：SET NAMES 字符集名；
  - 在MySQL配置文件中进行设置：

4.2表的约束

默认约束
- 关键字 DEFAULT：给字段设定一个默认值
- 字段名数据类型 DEFAULT 默认值
- 在创建表时，添加默认约束
  - 格式为：字段名类型声明 DEFAULT 默认值;
  - CREATE TABLE student（age INT UNSIGNED DEFAULT 18,......）;
- 创建完表后，添加默认约束
  - ALTER TABLE student MODIFY age INT UNSIGNED DEFAULT 18 ;
- 删除默认约束
  - ALTER TABLE student MODIFY age INT UNSIGNED;
非空约束
- 关键词NOT NULL:限制字段内容不能为空值
- 创建表时添加非空约束
  - 格式：字段名类型声明 NOT NULL
  - CREATE TABLE IF NOT EXISTS student(...... sname VARCHA(10) NOT NULL,......);
- 创建表完后，添加非空约束
  - ALTER TABLE student MODIFY sname VARCHAR(10) NOT NULL;
- 删除非空约束
  - ALTER TABLE stu MODIFY NAME VARCHAR(20);
唯一约束
- 关键词UNIQLE：限定字段值不能有重复（但允许有NULL）
- 创建表时添加唯一约束
  - 格式为：字段名类型声明 UNIQUE
  - CREATE TABLE IF NOT EXISTS student(...... sname VARCHAR(10) UNIQUE,......)
- 创建表后，添加唯一约束
  - ALTER TABLE student MODIFY sname VARCHAR(10) UNIQUE;
  - ALTER TABLE student ADD CONSTRAINT sname UNIQUE(sname); --snam别名可自定义
- 删除唯一约束
  - ALTER TABLE student DROP INDEX sname; --通过查看表创建语句查看唯一约束名称
主键约束
- 关键词PRIMARY KEY：限制字段非空且唯一
- 在创建表时，添加主键约束
  - 格式为：字段名类型声明 PRIMARY KEY
  - ALTER TABLE student（sno CHAR(9) PRIMARY KEY,......）;--针对列的声明
  - CREATE TABLE student（所有字段声明，PRIMARY KEY(sno)）；--针对表的声明
  - 创建联合主键则需要表级完整性约束定义：CREATE TABLE sc(所有字段声明，PRIMARY KEY（）)
- 创建完表后，添加主键
  - ALTER TABLE student MODIFY sno CHAR(9) PRIMARY KEY; -针对列的声明
  - ALTER TABLE student ADD PRIMARY KEY(sno);--针对表的声明
  - ALTER TABLE sc ADD PRIMARY KEY(sno,cno);
- 删除主键
  - ALTER TABLE student DROP PRIMARY KEY;
自动增长
- 关键词AUTO_INCREMENT:实现数值的自动增长
- 自增长：当对应的字段，不给值，或者给默认值或者给NULL的时候，会自动的被系统触发，系统会从当前字段中已有的最大值再进行+1的操作，得到一个新的值
- 自增长通常是跟主键搭配使用
- 格式为字段名数据类型 AUTO_INCREMENT
- 新增自增长
  - 自增长字段必须是一个键
  - 自增长字段必须是数字（整型）
  - 一张表最多只能有一个自增长
- 自增长的使用
  - 当自增长被给定的值为NULL，或者默认值的时候就会触发自增长
  - 自增长如果对应的字段输入了值，那么自增长会暂时失效，但是下一次还是能够正确的自增长
  - 使用SHOW CREATE TABLE查看移动增长值
- 修改自增长
  - ALTER TABLE 表名AUTO_INCREMENT=值
  - 自增长如果是涉及到字段的改变，必须先删除后新增（一张表只能有一个自增长）
  - 修改当前自增长已经存在的值，修改智能比当前已有的自增长的最大值更大，不能改小（小不生效）
- 删除自动增长
  - 自增长是字段的一个属性：可以通过modify来进行修改（保证字段没有auto increment即可）
  - alter table 表名 modify 字段类型：
  - 主键理论上是单独存在的

4.3约束测试

使用CREATE TABLE命令建立表并定义表的约束

表student：

字段名	类型	长度	含义	约束
Sno	Int		学号	主键约束，自增长
Sname	VARCHAR	20	姓名	非空约束
Ssex	Char	2	性别
Sage	Int		年龄	默认约束，默认为19
Sdept	Char	10	所在系

表course：

字段名	类型	长度	含义	约束
Cno	Char	10	课程号	主键约束
Cname	Char	20	课程名	唯一约束
Cpno	Char	2	先行课
Ccredit	Int		学分

先在student表插入表格中数据后，执行两条SQL语句，说明执行失败的原因。

Sno	Sname	Ssex	Sage	Sdept
95001	黎小明	男	16	CS
95003	王小菲	女	26	MA
95004	王小凌	女	20	IS

（1）执行如下SQL语句，

INSERT INTO student (sno,sname,ssex,sage,sdept) VALUES (95002,NULL,'男',19,'CS');

截图并说明执行失败原因：

Sname为非空约束

（2）执行如下SQL语句

INSERT INTO student (sno,sname,ssex,sage,sdept) VALUES (95004,'刘小烨','男',27,'IS');

截图并说明执行失败原因：

95004的字段是主键，不能重复

3.course表插入如下数据，什么原因导致某些数据不能正确插入？

Cno	Cname	Ccredit
1	高等数学	4
2	大学英语	4
3	计算机导论	3
4	计算机网络	3
5	计算机网络	3

5-计算机网络-3这个不能插入因为字段Cname是唯一性约束

4.给student表添加一个约束，该约束限制ssex为非空约束

截图

删除ssex的非空约束

5.验证自增长功能是否生效，执行如下命令

INSERT INTO student (sname,ssex,sage,sdept) VALUES ("李勇","男",23,"CS");

查看student表中的数据，sno是否能自动填值

截图

6.验证默认约束是否有效，执行如下命令

INSERT INTO student (sname,ssex,sdept) VALUES ("张小云","男" ,"CS");

查看表中的数据，sage是否自动赋值

截图

7.删除course表的约束

截图：

五、分组查询和统计查询

使用函数：
- 文本处理函数
  - SELECT cname ,UPPER(cname) AS 大写，LOWER(cname) AS 小写，LENGTH(cname) AS 长度，LEFT (cname,1) AS左起第1位，RIGHT(cname,1) AS 右起第1位，SUBSTRING(cname,2,3)第2位开始取3位，LIRIM(cnam)去左空格，RTRIM(cname) 去右空格 FROM course
- 日期和时间处理函数
  - 返回当前日期和时间：SELECT NOW();
  - 返回当前日期：SELECT CURDATE();
  - 返回当前时间：SELECT CURTIME();
  - 返回一个日期的年份部分：SELECT YEAR(NOW());
- 数值处理函数
  - 绝对值：SELECT ABS(-5)
  - 平方根：SELECT SQRT(9)
聚合函数
- 将表中的数据做一些汇总，得到汇总之后的结果而不需要将表中原始数据检索出来：借助聚集函数可以完成
- 常见的聚集函数
  - COUNT() 计数
    - 确定行的数目或符合特定条件行的数目
    - SELECT COUNT(*) FROM student;
    - 查询总共有多少学生：SELECT COUNT(*) AS 学生总是 FROM student;
    - 查询总共有多少男学生：SELECT COUNT(sno) 男生总数 FROM student WHERE sgender='男' ;
    - 字段选取很重要，一般是选择主键或主属性，不用可以设置成NULL的字段
    - 查询选课记录总数 SELECT COUNT(*) AS 选课记录数 FROM sc;
    - 查询已有成绩的选课记录条数 SELECT COUNT(score) AS 有成绩的选课记录 FROM sc;
    - 查询选课记录条数：SELECT COUNT(sno) AS 选课记录数 FROM sc;
  - SUM() 汇总/总计
    - 用于返回指定列值的和
    - 查询课程表中所有课程的学分总和：SELECT SUM(ccredit) AS 学分总数 FROM course;
  - AVG() 平均值
    - 返回特定列值的平均值
    - 查询所有学生的平均年龄：SELECT AVG(sage) AS 平均年龄 FROM student;
    - 查询课程号为02的课程的平均成绩:SELECT AVG(score) AS 学分总数 FROM SC WHERE cno='02'
  - MAX() 最大值
    - 返回列中的最大值
    - 所有选课记录中的最高成绩：SELECT MAX(score) AS 最高成绩FROM sc;
  - MIN() 最小值
    - 返回列中的最小值
    - 所有选课记录中的最低成绩:SELECT MIN(score) AS 最低成绩 FROM sc;
- DISTINCT 关键字在函数中的作用
  - 查询已选课人次：SELECT COUNT(sno) AS 已选课人次 FROM sc;
  - 查询已选课人数：SELECT COUNT(DISTINCT sno)AS 已选课人次FROM sc;
- 说明：SUM() AVG()仅对数值类型列值计算，MAX()、MIN()可针对任意类型的列
- 组合使用：

插入数据:

DROP DATABASE IF EXISTS jwgl;
CREATE DATABASE IF NOT EXISTS jwgl DEFAULT CHARACTER SET='utf8';-- 创建数据库jwgl

USE jwgl;                       -- 转到jwgl

CREATE TABLE IF NOT EXISTS student(         -- 创建学生信息表student
sno CHAR(9),                                    -- sno:学号
sname       VARCHAR(10),               -- sname:学生姓名
sgender      CHAR(6),                           -- sgender:学生性别
sage TINYINT UNSIGNED,               -- sage:学生年龄
sdept CHAR(2) COMMENT 'IS 信息系 CS 计算机系 MA数学系'        -- sdept:学生所在系，IS信息系、CS计算机系、MA数学系
)CHARSET utf8;

#插入数据到学生表中
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815121',"李勇","男",23,"CS");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815122',"刘晨","男",20,"IS");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815123',"王敏","男",21,"MA");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815124',"王大海","女",20,"MA");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815125',"张立","男",19,"IS");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815126',"于大宝","女",21,"IS");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815127',"李斌","男",20,"CS");

CREATE TABLE IF NOT EXISTS course(     -- 创建课程信息表course
cno CHAR(2),                    -- cno:课程编号
cname       VARCHAR(20) ,                           -- cname:课程名称
cpno      CHAR(4) COMMENT '先修课程号',       -- cpno:先修课程号
ccredit TINYINT UNSIGNED                                 -- ccredit:学分
)CHARSET utf8;

#插入数据到课程信息表中
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('01',"数据库",NULL,"4");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('02',"数学",NULL,"2");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('03',"信息系统",NULL,"4");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('04',"操作系统",NULL,"3");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('05',"数据结构",NULL,"4");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('06',"数据处理",NULL,"2");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('07',"BASIC语言",NULL,"4");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('08',"DB_Design",NULL,"3");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('09',"DBSDesign",NULL,"3");
INSERT INTO course (cno,cname,cpno,ccredit) VALUES ('10',"线性代数",NULL,"2");

#修改课程信息表中部分数据
UPDATE course SET cpno='05'  WHERE cno='01';
UPDATE course SET cpno='01'  WHERE cno='03';
UPDATE course SET cpno='06'  WHERE cno='04';
UPDATE course SET cpno='07'  WHERE cno='05';
UPDATE course SET cpno='06'  WHERE cno='07';
UPDATE course SET cpno='01'  WHERE cno='08';

CREATE TABLE IF NOT EXISTS sc(          -- 创建学生选课信息表sc 
sno CHAR(9),                                          -- sno：学号
cno       CHAR(2) ,                  -- cno：课程编号
score TINYINT UNSIGNED       -- score：成绩

)CHARSET utf8;

INSERT INTO sc (sno,cno,score) VALUES ("201815121","01","92");
INSERT INTO sc (sno,cno,score) VALUES ("201815121","02","85");
INSERT INTO sc (sno,cno,score) VALUES ("201815121","03","88");
INSERT INTO sc (sno,cno,score) VALUES ("201815122","02","90");
INSERT INTO sc (sno,cno,score) VALUES ("201815122","03","59");
INSERT INTO sc (sno,cno,score) VALUES ("201815124","07",NULL);
INSERT INTO sc (sno,cno,score) VALUES ("201815126","08",NULL);

查询选修了课程的学生总数： select count(distinct sno) from sc
查询学校共有多少个系 SELECT COUNT(DISTINCT sdept) FROM student;
查询所有课程的总学分数和平均学分数,以及最高学分和最低学分： SELECT SUM(SCORE), AVG(SCORE), MAX(SCORE), MIN(SCORE) FROM SC;
查询选修03号课程的最高分，最低分 Select max(score),min(score) from sc where cno=03
查询学号为201815121的学生的总分和平均分
求计算机系学生的总数 SELECT COUNT(SDEPT) FROM STUDENT WHERE SDEPT="CS" ;
查询选修了03号课程的学生的学号及其成绩，查询结果按分数降序排列。 select sno,score from sc where cno=03 order by score desc;
按照系别进行分组，查询每个系的人数 select sdept,count(sdept) as 人数 from student group by sdept;
查询每个学生所选课程的平均成绩, 最高分, 最低分和选课门数 SELECT SNO AS 学号 , SUM(SCORE), AVG(SCORE), MAX(SCORE), MIN(SCORE) FROM SC GROUP BY SNO;
求IS系中男女学生的数量分别是多少？ select sgender,count(*) from student where sdept='IS' group by sgender;
查询各系的学生的人数并按人数从多到少排序 select sdept,count() from student group by sdept order by count() desc;
查询选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。

select sno,sum(score)

from sc

where score>60

group by sno having count(cno)>3

order by sum(score) desc
查询选修两门以上课程学生的学号和选课门数 select sno,count(cno) from sc group by sno having count(cno)>2;
查询至少选修了2门课程的学生的平均成绩. select sno avg(score) from sc group by sno having count(cno)>2
查询平均分超过80分的学生的学号和平均分. select sno, avg(score) from sc group by sno having avg(score)>80
求选修“数据库”课程的学生平均成绩。 select sno,avg(score) from course,sc where course.cno=sc.cno and cname='数据库' group by sno )
求每一门课程的学生平均成绩，要求输出课程名及对应的平均成绩，并按平均成绩由大到小排序。

select cno,avg(score)

from sc

group by cno

order by avg(score) desc

六、连接查询、子查询和外键约束

插入数据：

CREATE DATABASE mydb;
USE mydb;
#1.创建fruit表 
CREATE TABLE fruits
(
f_id    char(10)     	NOT NULL,
s_id    INT        	NOT NULL,
f_name  char(255)  	NOT NULL,
f_price decimal(8,2)  	NOT NULL,
PRIMARY KEY(f_id) 
);
#插入数据
INSERT INTO fruits (f_id, s_id, f_name, f_price)
     VALUES('a1', 101,'apple',5.2),
     ('b1',101,'blackberry', 10.2),
     ('bs1',102,'orange', 11.2),
     ('bs2',105,'melon',8.2),
     ('t1',102,'banana', 10.3),
     ('t2',102,'grape', 5.3),
     ('o2',103,'coconut', 9.2),
     ('c0',101,'cherry', 3.2),
     ('a2',103, 'apricot',2.2),
     ('l2',104,'lemon', 6.4),
     ('b2',104,'berry', 7.6),
     ('m1',106,'mango', 15.6),
     ('m2',105,'xbabay', 2.6),
     ('t4',107,'xbababa', 3.6),
     ('m3',105,'xxtt', 11.6),
     ('b5',107,'xxxx', 3.6);
#2.创建数据表suppliers
CREATE TABLE suppliers
(
  s_id      int      NOT NULL AUTO_INCREMENT,
  s_name    char(50) NOT NULL,
  s_city    char(50) NULL,
  s_zip     char(10) NULL,
  s_call    CHAR(50) NOT NULL,
  PRIMARY KEY (s_id)
) ;
#插入数据
INSERT INTO suppliers(s_id, s_name,s_city,  s_zip, s_call)
VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'),
(102,'LT Supplies','Chongqing','400000','44333'),
(103,'ACME','Shanghai','200000','90046'),
(104,'FNK Inc.','Zhongshan','528437','11111'),
(105,'Good Set','Taiyuang','030000', '22222'),
(106,'Just Eat Ours','Beijing','010', '45678'),
(107,'DK Inc.','Zhengzhou','450000', '33332');
#3创建数据表customers
CREATE TABLE customers
(
  c_id      int       NOT NULL AUTO_INCREMENT,
  c_name    char(50)  NOT NULL,
  c_address char(50)  NULL,
  c_city    char(50)  NULL,
  c_zip     char(10)  NULL,
  c_contact char(50)  NULL,
  c_email   char(255) NULL,
  PRIMARY KEY (c_id)
);
#插入以下语句。
INSERT INTO customers(c_id, c_name, c_address, c_city, 
c_zip,  c_contact, c_email) 
VALUES(10001, 'RedHook', '200 Street ', 'Tianjin', 
 '300000',  'LiMing', 'LMing@163.com'),
(10002, 'Stars', '333 Fromage Lane',
 'Dalian', '116000',  'Zhangbo','Jerry@hotmail.com'),
(10003, 'Netbhood', '1 Sunny Place', 'Qingdao',  '266000',
 'LuoCong', NULL),
(10004, 'JOTO', '829 Riverside Drive', 'Haikou', 
 '570000',  'YangShan', 'sam@hotmail.com');
 SELECT COUNT(*) AS cust_num  FROM customers;
#4创建表orders
CREATE TABLE orders
(
  o_num  int      NOT NULL AUTO_INCREMENT,
  o_date datetime NOT NULL,
  c_id   int      NOT NULL,
  PRIMARY KEY (o_num)
) ;
#插入数据：
INSERT INTO orders(o_num, o_date, c_id)
VALUES(30001, '2008-09-01', 10001),
(30002, '2008-09-12', 10003),
(30003, '2008-09-30', 10004),
(30004, '2008-10-03', 10005),
(30005, '2008-10-08', 10001);

6.1连接查询

（一）对两张数据表使用内连接进行查询

fruits表和suppliers表中都有相同数据类型的字段s_id，两个表通过s_id字段建立联系。

在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询。从fruits表中查询f_name、f_price字段，从suppliers表中查询s_id、s_name

截图:
在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询，并对查询结果排序

截图

（二）对两张数据表使用左连接进行查询

用左连接，在customers表和orders表中，查询所有客户，包括没有订单的客户

截图

（三）对两张数据表使用右连接进行查询

在customers表和orders表中，查询所有订单，包括没有客户的订单

截图

6.2子查询

标量子查询

在suppliers表中查询s_city等于Tianjin的供应商s_id，然后在fruit表中查询所有该供应商提供的水果的种类

截图：
带EXSITS关键字的子查询

在suppliers表中查询是否存在s_id=107的供应商s_id，如果存在，就查询fruits表中的记录。

截图：

6.3外键约束

注意：此实验数据有中文，注意字符集的设置，需执行set names gkb;和set character_set_server=gbk;然后执行下面操作

在下面创建表格的语句中增加一条添加外键的语句，使book.pid字段为相对于person的pid字段的外键，然后执行。(CONSTRAINT book_bid_pk PRIMARY KEY(bid))

CREATE TABLE person(
pid      VARCHAR(18)  PRIMARY KEY,
name    VARCHAR(20),
age      TINYINT,
birthday  DATE,
address  VARCHAR(20)
) CHARSET utf8;

CREATE TABLE book(
bid INT,
bname VARCHAR(30) NOT NULL,
bprice DECIMAL(6,2),
pid VARCHAR(18),
CONSTRAINT  book_bid_pk  PRIMARY KEY(bid)
) CHARSET utf8;

截图：

在person表中插入三条记录

数据：
INSERT INTO person(pid,name,age,birthday,address) VALUES('111111111111111111','张三',30,'1980-05-05','中国北京市海淀区');
INSERT INTO person(pid,name,age,birthday,address) VALUES('222222222222222222','李四',35,'1975-05-05','中国上海市浦东区');
INSERT INTO person(pid,name,age,birthday,address) VALUES('333333333333333333','王五',40,'1970-05-05','辽宁省沈阳市铁西区');

向book表中插入记录

INSERT INTO book(bid,bname,bprice,pid)

VALUES(1002,'JAVA SE 项目集锦',108.8,' 00000000000000000');

截图并说明数据不能插入的原因：

Person表中没有pid=00000000000000000的数据

向book表中插入合法数据。

INSERT INTO book(bid,bname,bprice,pid) VALUES(1001,'JAVA SE 基础',98.8,'111111111111111111');

删除person表中pid为’111111111111111111’的记录。

DELETE FROM person WHERE pid='111111111111111111';

截图并说明执行失败的原因：

应该先要删除从表中的数据，然后再删除主表中的数据

删除外键

截图：

6.4多表操作相关知识

联合查询：将多次查询（多条select语句），在记录上进行拼接（字段数不会增加）
- 语法：多条select语句构成，每条select语句后去的字段数必须严格一致（与字段类型无关）
- 意义：联合查询的意义分为两种：查询同一张表但是需求不同；表数据结构一致时多表查询
连接查询：语法：左表 join 右表
- 将多张表（大于2张）进行记录的连接（按照某个指定条件进行数据拼接）
- 意义：在用户查看数据的时候，需要显示的数据来自多张表
- 分类：SQL中将连接查询分成四类：内连接，外连接。自然连接和交叉连接
  - 交叉连接：左表 cross join 右表 ,从一张表中循环取出每一条记录都去另外一张表进行匹配，匹配的结果全部保留（没有条件匹配），而连接本身字段就会增加，最终形成的结果叫笛卡尔积
    - SELECT * FROM student cross join sc
    - 交叉连接存在的价值，保证连接的结构的完整性
  - 内连接：从左表中取出每一条记录，去右表中与所有的记录进行匹配：匹配必须是某个条件在左表中与右表中相同最终才会保留结果，否则不保留
    - 语法：左表[inner] join 右表 on 左表.字段 =右表.字段;
      - on表示连接条件；条件字段就是代表有相同的业务含义
      - select * from student inner join sc on student.sno=sc.sno;
    - 内连接如果没有on,结果就是笛卡尔积
    - 内连接可以使用where代替on（where没有on效率高）
- 外连接：outer join 以某张表为主表，取出里面的所有记录，然后每条与另外一张表进行连接，不管能不能匹配上条件，最终都会保留：能匹配，正确保留，不能匹配，其他字段都置空
  - 外连接分为两种：有主表和副表
  - 语法：左表 left/right join 右表 on 左表.字段 =右表.字段;
    - 左连接left join：以左表为主表
    - 右连接right join：以右表为主表
    - select * from student left join sc on student.sno=sc.sno;
  - 虽然左连接和右连接有主表差异，但是显示的结果：左表的数据在左边，右表的数据在右边.
子查询
- 不相关子查询：作为查询条件，把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句中
  - 查询分数最高的那一位学生的学号
  - 不相关子查询：作为查询条件，把一条SELECT语课程的学生句返回的结果用于另一条SELECT语句的WHERE子句中
    - 查询分数最高的那以为学生的学号
      - SELECT sno FROM sc WHERE SCORE=(SELECT Max(score) FROM sc);
    - 查询已选课学生的学号和姓名
      - SELECT * from student where sno IN(SELECT DISTINCT sno FROM sc)
    - 查询成绩平均分80分以上的学生学号、
    - 查询选修了'数学'和'信息系统'课程的学生姓名
  - 相关子查询：根据一个查询的结果计算另一个查询结果，作为计算字段使用子查询
    - 查询每个学生的学号、姓名、选课门数
    - SELECT sno,sname FROM student;
    - SELECT COUNT(*) FROM sc where sno='201815121'
    - 将上面两句合二为一：
      - SELECT sno,sname,(SELECT COUNT(*) FROM sc WHERE sc.sno=student.sno) 选课门数 FROM student;
- 子查询有两种分类方式：按位置分类；按结果分类
- 按位置分类：子查询语句在外部查询语句中的位置
from子查询：子查询跟在from之后
where子查询：子查询出现在where条件之后
exists子查询：子查询出现在exists里面
按结果分类：根据子查询语句得到的数据进行分类（理论讲任何一个查询得到的结果都可以理解为一个二维表）
标量子查询：子查询得到的结果是一行一列
- 需求：已知班级名为PHP0710,要查询出该班级的所有学生
- 1、确定数据源，获取满足条件的所有学生
  - select * from student where c_id
- 2、获取班级ID:通过班级名字获得
  - select id from class where name='';--id一定只有一个值（一行一列）
- 3、标量子查询实现需求
  - select * from student where c_id=(select id from class where c_name='');
列子查询：子查询得到的结果是一列多行
- 需求：查询所有选课的学生（选课表中存在的数据）
- 1确定数据源：学生的学号
  - select * from student where sno in(?)
- 2、确定学生的学号
  - select sno from sc
- 3、列子查询实现需求
- select * from student where sno in(select sno from sc);
  - 列子查询返回的结果会比较：需要使用in作为条件匹配；
- 在mysql中还有几个类似的条件：all,some,any
- =any <=> in <=> =some
- =all全部匹配
- 肯定结果
  - select * from student where sno =any(select sno from sc where score>60);
  - select * from student where sno =some(select sno from sc where score>60);
- 否定结果
  - select * from student where sno !=any(select sno from sc where score>60);
  - select * from student where sno !=some(select sno from sc where score>60);
行子查询：子查询得到的结果是一行多列
- 需求：要求查询整个学生中，年龄最大，性别是男生的学生
- 确定数据源：学生
  - select * from student where age=? and sgender=?
- 确定最大的年龄和最高的身高
  - select max(age),max(height) from my_student;
  - select * from my_student where(age,height)=(select max(age),max(height) from my_student;)
- 行子查询返回的结果是一行多列（多行多列）
表子查询：子查询得到的结果是多行多列（出现的位置是在from之后）
外键约束
- FOREIGN KEY:保证不引用不存在的数据
- 创建表时添加外键
  - 语法：CREATE TABLE 表名(所有字段声明及主键声明，[constraint 外键名称] FOREIGN KEY(外键列名称) REFERENCES 主表名称（主表列名称）);
  - CREATE TABLE IF NOT EXISTS sc(......,FOREIGN KEY(sno) REFERENCES student(sno));--系统会自动给外键一个名称
  - CREATE TABLE IF NOT EXISTS sc(....,CONSTRAINT fk_stu_sno FOREIGN KEY(sno) REFERENCES student(sno), --fk_stu_sno为外键名称，可自定义)
- 创建表之后，添加外键
  - ALTER TABLE 表名 ADD[CONSTRAINT 外键名称] FOREIGN KEY(外键字段名称) REFERENCES 主表名称(主表列名称);
  - ALTER TABLE sc ADD FOREIGN KEY(cno) REFERENCES course(cno); --系统自动给外键一个别名
  - ALTER TABLE sc ADD CONSTRAINT fk_sc_cno FOREIGN KEY(cno) REFERENCES course(cno); --fk_stu_sno为外键名称，可自定义
- 删除外键
  - ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;--通过查看创建表语句查看外键名称
  - ALTER TABLE sc DROP FOREIGN KEY sc_ibfk_1;
  - ALTER TABLE sc DROP FOREIGN KEY fk_stu_sno;
- 父表中更新、删除数据时外键的应对

七、用户与权限、视图、事务

第一部分：用户与权限

创建用户
- 使用GRANT语句创建用户
  - 创建一个用户名为user1、密码为123，并授予该用户对student表有查询权限
    - GRANT SELECT ON . TO 'user1'@'localhost' IDENTIFIED BY '123';
- 使用CREATE USER语句创建用户
  - 创建一个用户名为user2、密码为123的用户，创建语句如下；
    - CREATE USER 'user2'@'localhost' IDENTIFIED BY '123';
- 使用INSERT语句创建用户（遗留）
  - 创建一个用户名为user3、密码为123的用户，创建语句如下：
    - INSERT INTO mysql.user(Host,User,Password) VALUES('localhost','user3',PASSWORD('123'));
修改密码
- root用户修改root用户密码
  - 使用mysqladmin命令修改
    - mysqladmin –username [–h hostname] –p password new_password
  - 使用UPDATE语句修改
    - UPDATE mysql.user set Password=PASSWORD('new_password')
    - WHEREUser='username' and Host='hostname';
  - 使用SET语句修改
    - SET PASSWORD=PASSWORD('new_password');
  - set password 创建一个for 'use2'@'localhost'='654321';
- root用户修改普通用户密码
  - 使用GRANT USAGE语句修改
    - GRANT USAGE ON . TO 'username'@'localhost'IDENTIFIED BY [PASSWORD]'new_password';
  - 使用UPDATE语句修改
    - UPDATE mysql.user set Password=PASSWORD('new_password')
    - WHERE User='username' and Host='hostname';
  - 使用SET语句修改
    - SETPASSWORD=PASSWORD('new_password');
- 普通用户修改自己的密码
  - SET PASSWORD=PASSWORD('new_password');
删除普通用户
- DROP USER 'user1'@'localhost';
- DELETE FROM mysql.user WHERE HOST='localhost' AND User='user2';
权限管理
- 授予权限
  - GRANT privileges [(columns)][,privileges[(columns)]] ON database.table
  - TO 'username'@'hostname'[IDENTIFIED BY [PASSWORD]'password']
  - [WITH with_option [with_option]…]
  - 使用GRANT语句创建一个用户名为user4、密码为123，且对数据库有INSERT、SELECT、GRANT OPTION权限的用户,并使用SELECT语句查询user4的用户权限
    - GRANT INSERT,SELECT ON . TO 'user7'@'localhost' IDENTIFIED BY '123' with grant option;
    - SELECT Host,User,Password,Insert_priv,Select_priv,Grant_priv FROM mysql.user WHERE user='user4';
- 查看某个用户拥有的权限
  - SHOW GRANTS FOR 'username'@'hostname';（USER3查不到）
  - 查看root用户的权限信息：SHOW GRANTS FOR 'root'@'localhost';
- 收回权限
  - 收回用户指定权限
    - REVOKE privileges [columns][,privileges[(columns)]] ON database.table
    - FROM'username'@'hostname'[,'username'@'hostname'] …
    - 收回user4用户的INSERT权限：REVOKE INSERT ON student FROM 'user3'@'localhost';
  - 收回用户全部权限
    - REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'username'@'hostname' [,'username'@'hostname'] …
    - REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'root'@'localhost';

TEST：

用root超级用户身份登录mysql,创建数据库jwgl和数据表student。

set names gbk;
DROP DATABASE IF EXISTS jwgl;
CREATE DATABASE IF NOT EXISTS jwgl DEFAULT CHARACTER SET='utf8';-- 创建数据库jwgl

USE jwgl;                       -- 转到jwgl

CREATE TABLE IF NOT EXISTS student(         -- 创建学生信息表student
sno CHAR(9),                                    -- sno:学号
sname       VARCHAR(10),               -- sname:学生姓名
sgender      CHAR(6),                           -- sgender:学生性别
sage TINYINT UNSIGNED,               -- sage:学生年龄
sdept CHAR(2) COMMENT 'IS 信息系 CS 计算机系 MA数学系'        -- sdept:学生所在系，IS信息系、CS计算机系、MA数学系
)CHARSET utf8;

#插入数据到学生表中
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815121',"李勇","男",23,"CS");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815122',"刘晨","男",20,"IS");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815123',"王敏","男",21,"MA");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815124',"王大海","女",20,"MA");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815125',"张立","男",19,"IS");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815126',"于大宝","女",21,"IS");
INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815127',"李斌","男",20,"CS");

建立新用户:用户名stu1,密码stu1。

CREATE USER 'stu1'@'localhost' IDENTIFIED BY 'stu1';

授予stu1对student具有SELECT和INSERT权限

GRANT SELECT,INSERT ON student TO 'stu1'@'localhost';

以用户stu1登录数据库jwgl：

mysql –u stu1 –p,

密码输入stu1，

然后use jwgl;

5.向student表中插入一组合法数据，观察运行结果

INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815129',"王红","女",20,"CS");

截图：

删除student表中的一组数据，观察运行结果

DELETE from student where sno='201815126';

截图并说明原因：

原因：因为在创建stu1的时候只给了查询和插入限权，没有删除限权，所以报错

root身份登录数据库jwgl，收回stu1对student表的INSERT权限；

REVOKE INSERT ON student FROM 'stu1'@'localhost';

stu1身份登录jwgl，查询student表中的数据；

SELECT * FROM student;

向student表中插入一组合法数据，观察运行结果

INSERT INTO student (sno,sname,sgender,sage,sdept) VALUES ('201815130',"张丰","男",21,"IS");

截图并说明原因：

原因：因为收回stu1对student表的INSERT权限，所以会报错

root身份登录数据库，删除用户名stu1。

DROP USER 'stu1'@'localhost';

截图：

第二部分：视图

创建数据库和基本表t

CREATE DATABASE ex2;

USE ex2;

CREATE TABLE t (quantity INT, price INT);     /*创建基本表t*/

INSERT INTO t VALUES(3, 50);         /*插入记录*/在表student和表stu_info上创建视图

在表t上创建一个名为view_t的视图

CREATE VIEW view_t AS SELECT quantity, price, quantity *price FROM t;
查看视图

SELECT * FROM view_t;
通过DESC语句查看视图view_t的定义.

截图：
通过SHOW CREATE VIEW查看视图的详细定义

截图：

6.修改视图view_t

CREATE OR REPLACE VIEW view_t AS SELECT * FROM t;

7.更新视图，观察视图更新后，基本表t的内容也更新了

① 视图更新之前，查看基本表和视图的信息

SELECT * FROM view_t; /*查看更新之前的视图*/

SELECT * FROM t; /*查看更新之前的表*/

截图：

② 更新视图view_t

UPDATE view_t SET quantity=5; /*更新视图*/

③ 视图更新之后，查看基本表和视图的信息

SELECT * FROM t; /*查看更新之后的表*/

SELECT * FROM view_t; /*查看更新之后的视图*/

截图：

通过DROP语句删除视图view_t;

截图：

第三部分：事务

事务
- 定义：事务是针对数据库的一组操作，它可以由一条或多条SQL语句组成。
- 事务的特性（ACID）
  - 原子性
    - 事务中的操作要么都发生，要么都不发生
  - 一致性
    - 事务必须使数据库从一个一致性状态变换到另外一个一致性状态
  - 隔离性
    - 一个事务的执行不能被其他事务干扰，一个事务内部的操作及使用的数据对并发的其他事务是给的，并发执行的各个事务之间不能互相干扰
  - 持久性
    - 一个事务一旦提交，数据的改变就是永久性的，接下来的操作和数据库故障都不应该对它造成影响
- 事务的使用
- ① 开启事务事务
  - START TRANSACTION;
- ②SQL语句
  - UPDATE sh_user SET money=money+100 WHERE name='Alex'; /Alex增加100元/
  - UPDATE sh_user SET money=money-100 WHERE name='Bill'; /Bill扣除100元/
- ③ 提交事务
  - COMMIT;
事务回滚
- ① 开启事务
  - START TRANSACTION;
- ② Bill扣除100元
  - UPDATE sh_user SET money = money - 100 WHERE name = 'Bill';
- ④ 回滚事务
  - ROLLBACK;
- ① 开启事务
  - STARTTRANSACTION;
- ②SQL语句
  - UPDATEaccount SET money=money-100 WHERE name='Alex'; /SQL语句/
- ③ 创建保存点s1
  - SAVEPOINT s1;
- 执行SQL语句
将事务回滚到指定保存点
- 关闭当前会话的事务自动提交
  - SET AUTOCOMMIT = 0;
  - show variables like 'autocommit';
- ① 开启事务
  - START TRANSACTION;
- ② Alex扣除100元
  - UPDATE sh_user SET money = money - 100 WHERE name = 'Alex';
- ③ 创建保存点s1
  - SAVEPOINT s1;
- ④ Alex再扣除50元
  - UPDATE sh_user SET money = money - 50 WHERE name = 'Alex';
- ⑤ 回滚到保存点s1
  - ROLLBACK TO SAVEPOINT s1;
- ⑥ 查询Alex的金额
  - SELECT name, money FROM sh_user WHERE name = 'Alex';
- ⑦回滚事务
  - ROLLBACK;
- ⑧ 查看Alex的金额
  - SELECT name, money FROM sh_user WHERE name = 'a';
隔离级别
- REPEATABLEREAD：可重复读
- READ UNCOMMITTED：读取未提交
- READCOMMITTED：读取提交
- SERIALIZABLE：可串行化
- 设置B账户中事务的隔离级别
  - SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
- 查询事务隔离级别
  - SELECT @@tx_isolation;
READ UNCOMMITTED（读取未提交）
- 出现脏读
  - 客户端B设置隔离级别为 READ UNCOMMITTED
    - SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    - SELECT name, money FROM sh_user WHERE name = 'Bill';
  - 客户端A开启事务，并执行转账操作
    - START TRANSACTION;
    - UPDATE sh_user SET money = money - 100 WHERE name = 'Alex';
    - UPDATE sh_user SET money = money + 100 WHERE name = 'Bill';
  - 客户端B查询金额
    - SELECT name, money FROM sh_user WHERE name = 'Bill';
- 解决脏读
  - 客户端B设置隔离级别为 READ COMMITTED
    - SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    - SELECT name, money FROM sh_user WHERE name = 'Bill';
    - 可以看到客户端B读取到了客户端A提交前的结果
  - 客户端A回滚，以免影响后面案例
    - ROLLBACK;
    - START TRANSACTION;
READ COMMITTED（读取提交）
- B不可重复读
  - 客户端B
    - SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    - START TRANSACTION;
    - SELECT name, money FROM sh_user WHERE name = 'Alex';
  - 客户端A
    - UPDATE sh_user SET money = money - 100 WHERE name = 'Alex';
  - 客户端B
    - SELECT name, money FROM sh_user WHERE name = 'Alex';
    - COMMIT;
- 解决B不可重复读
  - 客户端B
    - SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    - START TRANSACTION;
    - SELECT name, money FROM sh_user WHERE name = 'Alex';
  - 客户端A
    - UPDATE sh_user SET money = money + 100 WHERE name = 'Alex';
  - 客户端B
    - SELECT name, money FROM sh_user WHERE name = 'Alex';
    - COMMIT;
REPEATABLE READ（可重复读）
- 客户端B的幻读
  - 客户端B
    - SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    - START TRANSACTION;
    - SELECT SUM(money) FROM sh_user; 2000
  - 客户端A
    - INSERT INTO sh_user (id, name, money) VALUES (3, 'Tom', 1000);
  - 客户端B
    - SELECT SUM(money) FROM sh_user; 3000
    - COMMIT;
- 避免客户端B的幻读
  - 客户端B
    - SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    - START TRANSACTION;
    - SELECT SUM(money) FROM sh_user;
  - 客户端A
    - INSERT INTO sh_user (id, name, money) VALUES (4, 'd', 1000);
  - 客户端B
    - SELECT SUM(money) FROM sh_user;
    - COMMIT;
SERIALIZABLE（可串行化）
- 客户端B
  - SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  - START TRANSACTION;
  - SELECT name, money FROM sh_user WHERE name = 'Alex';
- 客户端A
  - UPDATE sh_user SET money = money + 100 WHERE name = 'Alex';
- 客户端B
  - COMMIT;

TEST：

建库、建表、添加数据

CREATE DATABASE ex2;

USE ex2;

CREATE TABLE account(

    id INT primary key auto_increment,

    name VARCHAR(40),

    money FLOAT

);

INSERT INTO account(name,money) VALUES('a',1000);

INSERT INTO account (name, money) VALUES ('b', 1000);

2．事务提交

① 开启事务

执行SQL语句

并截图：

②A增加100元

UPDATE account SET money=money+100 WHERE name='a';

③ b扣除100元

UPDATE account SET money=money-100 WHERE name='b';

④ 提交事务

执行SQL语句

截图：

事务回滚

使用UPDATE语句实现由b账户向a账户转100元钱的转账功能，

① 开启事务

START TRANSACTION;

② a扣除100元

UPDATE account SET money=money-100 WHERE name='a';

③ 创建保存点s1

执行SQL语句

截图：

	a再扣除50元

UPDATE account SET money=money-50 WHERE name='a';

④查看a的金额

SELECT * FROM account WHERE name='a';

⑤ 回滚到保存点s1

执行SQL

语句并截图：

⑥查询a的金额

SELECT * FROM account WHERE name='a';

执行SQL语句

截图

⑦事务回滚

ROLLBACK;

⑧查看a的金额

SELECT * FROM account WHERE name='a';

执行SQL语句

截图：

事务的隔离级别

① 开启两个命令行窗口A、B，分别模拟a账户和b账户，登录mysql数据库并切换到数据库ex2

② 查看有脏读的情况

命令行窗口B：

\#设置B账户中事务的隔离级别为读未提交

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

\#查询事务隔离级别

SELECT @@tx_isolation;

\#开启事务

START TRANSACTION;

\#在该事务中查询当前账户的余额信息

SELECT * FROM account;

命令行窗口A：

\#开启事务

START TRANSACTION;

\#执行转账功能

UPDATE account SET money=money-100 WHERE name='a';

UPDATE account SET money=money+100 WHERE name='b';

命令行窗口B：

\#查询当前账户的余额信息

SELECT * FROM account;

在B账户中查询到的两次数据是否一样？这种隔离级别有什么影响？

答：查询的两次数据不一样，在该级别下的事务可以读取到其他事务中未提交的数据。

注意：观察完成后，需在a客户端执行ROLLBACK;在B客户端执行COMMIT.

③ 解决脏读的问题

命令行窗口B：

\#防止脏数据读发生，设置B账户中事务的隔离级别为读提交

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

\#开启事务

START TRANSACTION;

\#在该事务中查询当前账户的余额信息

SELECT * FROM account;

命令行窗口A：

\#开启事务

START TRANSACTION;

\#执行转账功能

UPDATE account SET money=money-100 WHERE name='a';

UPDATE account SET money=money+100 WHERE name='b';

命令行窗口B：

\#查询当前账户的余额信息

SELECT * FROM account;

B账户在同一个事务中查询到的两次数据是否一样？

答：B账户在同一个事务中查询到的两次数据一样，在READ COMMITTED隔离级下只能读取其他事务已经提交的数据，避免了脏读数据的现象。

八、索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构，提取句子主干，就可以得到索引的本质：索引是数据结构

8.1、索引的分类

主键索引 RPIMARY KEY
- 唯一的标识，主键不可重复，只能有一列作为主键
唯一索引（UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标志为唯一索引
常规索引（KEY/INDEX）
- 默认的，index ，key关键字来设置
全文索引（FullText）
- 在特定的数据库引擎下才有，MyISAM
- 快速定位数据

8.2、索引原则

索引不是越多越好
不要对进程变动数据加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段上

九、JDBC

需要导入JDBC的java包

9.1JDBC连接数据库

创建测试数据库

CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `jdbcStudy`;

CREATE TABLE `users`(
 `id` INT PRIMARY KEY,
 `NAME` VARCHAR(40),
 `PASSWORD` VARCHAR(40),
 `email` VARCHAR(60),
 birthday DATE
);

 INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES('1','zhangsan','123456','zs@sina.com','1980-12-04'),
('2','lisi','123456','lisi@sina.com','1981-12-04'),
('3','wangwu','123456','wangwu@sina.com','1979-12-04')

创建一个普通项目
导入数据库驱动
编写测试代码

package com.JDBC.Demo01;

import com.mysql.jdbc.Driver;

import java.sql.*;

public class jdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");//固定写法

        //2.用户信息
        String url  = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC";
        String username = "root";
        String password = "admin";

        //3.连接成功,数据库对象 connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //4.执行sql的对象  statement 执行sql的对象
        Statement statement = connection.createStatement();
        //5.执行sql的对象  去执行sql。可能存在结果，查看返回结果
        String sql ="SELECT * FROM `users`";

        ResultSet resultSet = statement.executeQuery(sql);//返回的结果集

        while (resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("NAME"));
            System.out.println("pwd="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birth="+resultSet.getObject("birthday"));
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

加载驱动
用户信息
创建数据库对象Connection connection = DriverManager.getConnection（url, username, password）
执行sql对象tatement statement = connection.createStatement();
执行sql的对象去执行sql。可能存在结果，查看返回结果

String sql ="SELECT * FROM users";

ResultSet resultSet = statement.executeQuery(sql);//返回的结果集(executeUpdate 更新插入删除)
resultSet.beforeFirst() 移动到最前面
resultSet.afterLast()移动到最后面
resultSet.next()移动到下一个数据
resultSet.prevoius()移动到前一行
resultSet absolute（）移动到指定行
resultSet.getObject（）在不知道类型下提取
resultSet.getString（）
resultSet.getInt（）
释放链接

9.2statement

CRUD操作-create

使用executUpdate（string sql）方法完成数据库添加操作，实例操作

  Statement statement = connection.createStatement();
        //5.执行sql的对象  去执行sql。可能存在结果，查看返回结果
        String sql ="insert into user(...) value (..)";
        int num= statement.executeUpdate(sql);//返回的结果集
        if(num>0){
            System.out.println("插入成功");
        }

CRUD操作-delete

使用executUpdate（string sql）方法完成数据库添加操作，实例操作

Statement statement = connection.createStatement();
       //5.执行sql的对象  去执行sql。可能存在结果，查看返回结果
       String sql ="delete from user where id = 1";
       int num= statement.executeUpdate(sql);//返回的结果集
       if(num>0){
           System.out.println(删除成功");
       }

CRUD操作-update

使用executUpdate（string sql）方法完成数据库添加操作，实例操作

Statement statement = connection.createStatement();
      //5.执行sql的对象  去执行sql。可能存在结果，查看返回结果
      String sql ="update user set name ='' where name='';
      int num= statement.executeUpdate(sql);//返回的结果集
      if(num>0){
          System.out.println(更新成功");
      }

CRUD操作-delete

使用executeQuery（string sql）方法完成数据库添加操作，实例操作

String sql ="SELECT * FROM `users`";

       ResultSet resultSet = statement.executeQuery(sql);//返回的结果集

       while (resultSet.next()){
           System.out.println("id="+resultSet.getObject("id"));
           System.out.println("name="+resultSet.getObject("NAME"));
           System.out.println("pwd="+resultSet.getObject("PASSWORD"));
           System.out.println("email="+resultSet.getObject("email"));
           System.out.println("birth="+resultSet.getObject("birthday"));
       }

9.3提取工具类

编写配置文件

driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC
username = root
password = admin

编写工具类

package com.JDBC.Demo02.utils;

import com.mysql.cj.protocol.Resultset;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
    private  static  String driver = null;
    private  static  String url = null;
    private  static  String username = null;
    private  static  String password = null;
    static {
        try{
            InputStream input = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(input);
            driver=properties.getProperty("driver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");


            //1.驱动只用加载一次
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取链接
    public  static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
    //释放资源
    public  static  void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if(resultSet!=null){
            resultSet.close();
        }
        if(statement!=null){
            statement.close();
        }
        if(connection!=null){
            connection.close();
        }
    }
}

测试

编写插入数据

package com.JDBC.Demo02.test;

import com.JDBC.Demo02.utils.JdbcUtils;
import com.mysql.cj.protocol.Resultset;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) throws SQLException {
        Connection connection= null;
        Statement statement = null;
        Resultset resultset = null;
        try {
            connection = JdbcUtils.getConnection();//获取数据库链接
            statement = connection.createStatement();
            String sql  = " INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
                    "VALUES('4','zhangsan','123456','zs@sina.com','1220-12-04');";
            int i = statement.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,null);
        }
    }
}

编写查询数据

package com.JDBC.Demo02.test;

import com.JDBC.Demo02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestQuery {
    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        Statement statement = null;
        ResultSet resultSet=null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "select * from users where id=1";
            resultSet  = statement.executeQuery(sql);
            if(resultSet.next()){
                System.out.println("id="+resultSet.getObject("id"));
                System.out.println("name="+resultSet.getObject("NAME"));
                System.out.println("pwd="+resultSet.getObject("PASSWORD"));
                System.out.println("email="+resultSet.getObject("email"));
                System.out.println("birth="+resultSet.getObject("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

9.4 SQL注入问题

sql存在漏洞会被攻击导致数据泄露 SQL会被拼接

普通常见登录

package com.JDBC.Demo02.test;

import com.JDBC.Demo02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQL注入 {
    public static void main(String[] args) throws SQLException {
        //正常登录
        login("zhangsan","123456");
    }
    // 登录业务
    public  static  void login(String username,String password) throws SQLException {
        Connection connection=null;
        Statement statement = null;
        ResultSet resultSet=null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "select * from users where `NAME` ='"+username+"' AND `PASSWORD`='"+password+"' ";
            resultSet  = statement.executeQuery(sql);
            if(resultSet.next()){
                System.out.println("id="+resultSet.getObject("id"));
                System.out.println("name="+resultSet.getObject("NAME"));
                System.out.println("pwd="+resultSet.getObject("PASSWORD"));
                System.out.println("email="+resultSet.getObject("email"));
                System.out.println("birth="+resultSet.getObject("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

PreparedStatement对象

PreparedStatement可以防止SQL注入，而且效率更好

使用PreparedStatement插入数据：

package com.JDBC.Demo03;

import com.JDBC.Demo02.utils.JdbcUtils;

import java.util.Date;
import java.sql.*;

public class TestInsert {
    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            //区别
            //使用？占位符代替参数
            String sql = " INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
                    "VALUES(?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);//预编译sql，先写sql ，然后不执行

            //手动参数赋值
            preparedStatement.setInt(1,5);
            preparedStatement.setString(2,"xiang");
            preparedStatement.setString(3,"123456");
            preparedStatement.setString(4,"xsadas@dsh.com");
            //注意点  sql.Date 数据库
            //   util.Date  Java  new Date().getTime()获得时间戳
            preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));

            int i = preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

使用PreparedStatement查询数据：

package com.JDBC.Demo03;

import com.JDBC.Demo02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestQuery {
    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        PreparedStatement preparedStatement= null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();

            String sql = "select * from users where id=?";//编写sql

            preparedStatement = connection.prepareStatement(sql);//预编译

            preparedStatement.setInt(1,1);//传递参数

            resultSet=preparedStatement.executeQuery();//执行查询

            if(resultSet.next()){
                System.out.println("id="+resultSet.getObject("id"));
                System.out.println("name="+resultSet.getObject("NAME"));
                System.out.println("pwd="+resultSet.getObject("PASSWORD"));
                System.out.println("email="+resultSet.getObject("email"));
                System.out.println("birth="+resultSet.getObject("birthday"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

优化：

package com.JDBC.Demo03;

import com.JDBC.Demo02.utils.JdbcUtils;

import java.sql.*;
import java.util.Scanner;

public class SQL注入 {
    public static void main(String[] args) throws SQLException {
        System.out.print("输入查询用户ID:");
        try {
            Scanner scanner = new Scanner(System.in);
            int id = scanner.nextInt();
            login(id);
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("数据异常！");
        }
    }
    // 登录业务
    public  static  void login(int id) throws SQLException {   Connection connection=null;
        PreparedStatement preparedStatement= null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();

            String sql = "select * from users where id=?";//编写sql

            preparedStatement = connection.prepareStatement(sql);//预编译

            preparedStatement.setInt(1,id);//传递参数

            resultSet=preparedStatement.executeQuery();//执行查询

            if(resultSet.next()){
                System.out.println("id="+resultSet.getObject("id"));
                System.out.println("name="+resultSet.getObject("NAME"));
                System.out.println("pwd="+resultSet.getObject("PASSWORD"));
                System.out.println("email="+resultSet.getObject("email"));
                System.out.println("birth="+resultSet.getObject("birthday"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

9.5 JDBC事物

开启事务connection.setAutoCommit(false);
提交事务 connection.commit();
可以在catch语句中显示定义回滚语句，但默认失败就会回滚connection.rollback();

package com.JDBC.Demo05;

import com.JDBC.Demo02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestTransaction {
    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            //关闭数据库的自动提交，自动会开启事务
            connection.setAutoCommit(false);//开启事物
            //区别
            //使用？占位符代替参数
            String sql = " INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
                    "VALUES(?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);//预编译sql，先写sql ，然后不执行

            //手动参数赋值
            preparedStatement.setInt(1,6);
            preparedStatement.setString(2,"xiang");
            preparedStatement.setString(3,"123456");
            preparedStatement.setString(4,"xsadas@dsh.com");
            //注意点  sql.Date 数据库
            //   util.Date  Java  new Date().getTime()获得时间戳
            preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));

            int i = preparedStatement.executeUpdate();
            connection.commit();
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            //异常回滚
            connection.rollback();
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

9.6 数据库连接池

数据库连接……执行完毕……释放

连接……释放十分浪费系统资源

池化技术：准备一些预先的资源，过来就链接预先准备好的

开门 -业务员-：等待 —服务

常用链接数

最小连接数

最大连接数

超过最大连接数排队等待

等待超时

编写连接池，实现一个接口DataSource

开源数据源实现

DBCP

C3P0

Druid （阿里巴巴）

使用了这些连接数据库连接池之后，我们在项目开发中就不需要编写数据库的代码了!

示例：BDCP

需要用到的jar包

commons-dbcp commons-logging commons-pool

编写配置文件

driverClassName=com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC
username = root
password = admin

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

编写工具类

package com.JDBC.Demo05.utils;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils_dbcp {
    private  static  BasicDataSource dataSource = null;
    static {
        try{
            InputStream input = JdbcUtils_dbcp.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(input);

            //创建数据源  工厂模式  -->创建对象
            dataSource = BasicDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取链接
    public  static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据中获取连接
    }
    //释放资源
    public  static  void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if(resultSet!=null){
            resultSet.close();
        }
        if(statement!=null){
            statement.close();
        }
        if(connection!=null){
            connection.close();
        }
    }
}

测试

package com.JDBC.Demo05.utils;

import com.JDBC.Demo02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestQuery {
    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        PreparedStatement preparedStatement= null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils_dbcp.getConnection();

            String sql = "select * from users where id=?";//编写sql

            preparedStatement = connection.prepareStatement(sql);//预编译

            preparedStatement.setInt(1,1);//传递参数

            resultSet=preparedStatement.executeQuery();//执行查询

            if(resultSet.next()){
                System.out.println("id="+resultSet.getObject("id"));
                System.out.println("name="+resultSet.getObject("NAME"));
                System.out.println("pwd="+resultSet.getObject("PASSWORD"));
                System.out.println("email="+resultSet.getObject("email"));
                System.out.println("birth="+resultSet.getObject("birthday"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils_dbcp.release(connection,preparedStatement,resultSet);
        }
    }
}

posted @ 2022-01-30 20:40 项sir 阅读(503) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· JAVA JDBC

· JavaWeb

· MySQL

· MySQL 笔记

· MySQL最新通俗易懂

阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！

项sir

博客

MySQL

一、基础知识

测试一、安装Mysql

1.1.在MySQL官网下一个MySQL安装包到C盘目录下，一个是免安装版mysql-5.7.22-winx64.zip

1.2.进行免安装版mysql-5.7.22-winx64.zip的Mysql安装

1.3.登录MySQL

1.4.运行MYSQL客户端相关命令

1.5.登录MySQL

1.6.卸载Mysql

1.7配置环境变量

二、基本操作

数据库与数据表的基本操作

三、数据的基本操作

高级查询

四、数据类型与约束

4.1数据类型

4.2表的约束

4.3约束测试

五、分组查询和统计查询

六、连接查询、子查询和外键约束

6.1连接查询

（一）对两张数据表使用内连接进行查询

（二）对两张数据表使用左连接进行查询

（三）对两张数据表使用右连接进行查询

6.2子查询

6.3外键约束

6.4多表操作相关知识

七、用户与权限、视图、事务

第一部分：用户与权限

TEST：

第二部分：视图

第三部分：事务

TEST：

八、索引

8.1、索引的分类

8.2、索引原则

九、JDBC

9.1JDBC连接数据库

9.2statement

9.3提取工具类

9.4 SQL注入问题

PreparedStatement对象

9.5 JDBC事物

9.6 数据库连接池

示例：BDCP

公告

常用链接

我的标签

联系