JavaWeb之SQL语句

最新推荐文章于 2022-03-24 19:23:26 发布
最新推荐文章于 2022-03-24 19:23:26 发布
阅读量1.7k 收藏 2
点赞数 1
分类专栏： 学习总结 文章标签： sql mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44528963/article/details/109693468
版权

JavaWeb之SQL语句

文章目录

一、数据库

1.数据存储

存储方式优点缺点
数组、集合速度快不能永久保存，数据是临时数据
数据库数据可以永久保存、查询速度快、方便管理数据占用资源，需要购买

  (1)集合、数组:容器,使用完毕就不存在了集合对象/数组被回收。

  (2)IO流:字节流/字符流,将数据存储到配置文件/xxx.txt文件中 ,效率低。

  (3)数据库软件:
    关系型数据库
      SQLServer,Oracle,db2,Mysql(开源,小型化)
    非关型数据库
      典型代表:mangodb/redis(key-value形式)—(缓存一些数据)

2.MySql数据库

  (1)验证是否安装完毕(登录成功,说明mysql的环境变量也配置了)
在这里插入图片描述
  (2)四个:mysql默认带的四个库
    mysql> show databases;
在这里插入图片描述
    information_schema：相关配置信息库
    mysql：存储相关表:user 用户表(管理员)
    performance_schema：mysql性能相关的
    test：测试的库

3.数据库的备份和还原

  (1)备份
    1)命令行的方式:进入dos
      备份的语法格式：
      mysqldump -u用户名 -p密码 数据库的名称 > 保存的路径

				举例：备份day31 数据库中的数据到 d:\day31.sql文件中。
				mysqldump -uroot -proot day31 > d:/day31.sql

    2)使用图形界面化工具的方式来进行数据库的备份
      进入到sqlyog工具中，在数据库的上面右键，选择备份。

  (2)还原数据库
    1)进入dos之后:登录msyql数据库，删除当前day31这个数据库。
      还原格式：source 之前备份的sql文件路径,回车

				举例：source d:/day31.sql;

    2)图形界面化的方式
      在sqlyog中删除当前day31数据库,然后右键选择以sql的转存文 件来导入。

4.表与表之间的关系

  (1)三种关系
    一对一：相对使用比较少，员工表–简历表，公民表–护照表。
    一对多：最常用的关系，部门和员工。
    多对多：学生选课表和学生表，一门课程可以有多个学生选择，一个学 生选择多门课程。

  (2)一对一 (1:1)
    1)一对一:在实际的开发中应用不多.因为一对一可以创建成一张表。
    2)两种建表原则：
      外键唯一:主表的主键和从表的外键（唯一），形成主外键关系，外 键唯一 UNIQUE 。
      外键是主键:主表的主键和从表的主键，形成主外键关系。

  (3)一对多(1:n)
    1)例如：班级和学生，部门和员工，客户和订单，分类和商品
    2)一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主 表(一方)的主键。

  (4)多对多(m:n)
    1)例如：老师和学生，学生和课程，用户和角色。
    2)多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段， 这两个字段分别作为外键指向各自一方的主键。

  (5)总结
    一对多：主外键的关系。
    多对多：中间表，两个一对多 。
    一对一：1) 特殊一对多，从表中的外键设置为唯一 。
        2) 从表中的主键又是外键。

5.数据规范化

  (1)范式：好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。
    建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。

  (2)三大范式：目前关系型数据库有六种范式
    第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、 第四范式(4NF）和第五范式（5NF，又称完美范式）。

    满足最低要求的范式是第一范式（1NF）。
    在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。
    一般说来，数据库只需满足第三范式(3NF）就行了。

  (3)第一范式:1NF
    1)概念：数据表的每一列都是不可再分割的原子数据项，不能是集合、 数组等非原子数据项。
      表中的某个列有多个值时，必须拆分为不同的列。
      简而言之，第一范式每一列不可再拆分，称为原子性。

    2)示例：班级表
在这里插入图片描述
  (4)第二范式：2NF
    1)概念： 在满足第一范式的前提下，表中的每一个字段都完全依赖于主 键。
      所谓完全依赖是指不能存在仅依赖主键一部分的列。
      简而言之，第二范式就是在第一范式的基础上所有列完全依赖于主键列。
      当存在一个复合主键包含多个主键列的时候，才会发生不符合第二范式的情况。
      比如有一个主键有两个列，不能存在这样的属性，它只依赖于其中一个列，这就是不符合第二范式。

    2)特点：
      一张表只描述一件事情。
      表中的每一列都完全依赖于主键。

    3)示例：
      借书证表(学生证号、学生证名称、学生证办理时间、借书证号、借 书证名称、借书证办理时间)
      分成两张表
      学生证表(学生证号、学生证名称、学生证办理时间)
      借书证表(借书证号、借书证名称、借书证办理时间)
  (5)第三范式：3NF：
    1)概念：在满足第二范式的前提下，表中的每一列都直接依赖于主键， 而不是通过其它的列来间接依赖于主键。
      第三范式就是所有列不依赖于其它非主键列，也就是在满足2NF的基础上，任何非主列不得传递依赖于主键。
    2)传递依赖:指的是如果存在"A → B → C"的决定关系，则 C 传递依赖于A。
      因此，满足第三范式的数据库表应该不存在如下依赖关系：主 键列 → 非主键列 x → 非主键列 y。
    3)示例：
      学生信息表(学号、姓名、年龄、所在学院、学院地点)
        存在传递的决定关系：学号—>所在学院—>学院地点。
      拆分成两张表
        学生信息表(学号、姓名、年龄、所在学院的编号(外键))
        学院信息表(学院编号、所在学院、学院地点)

  (6)三大范式总结：
    1NF：原子性，表中每列不可再拆分。
    2NF：不产生局部依赖，一张表只描述一件事情。
    3NF：不产生传递依赖，表中每一列都直接依赖于主键，而不是通过其它 列间接依赖于主键。

二、SQL语句之DDL、DML语句

1.SQL

  (1)SQL:Structured Query Language 结构化查询语言。

  (2)SQL作用
    1) 是一种所有关系型数据库的查询规范，不同的数据库都支持。
    2) 通用的数据库操作语言，可以用在不同的数据库中。
    3) 不同的数据库 SQL 语句有一些区别

  (3)SQL语句分类
    1)Data Definition Language (DDL 数据定义语言),如：建库，建表
    2)Data Manipulation Language(DML 数据操纵语言)，如：对表中的记 录操作(增删改查)。
    3)Data Query Language(DQL 数据查询语言)，如：对表中数据的查询操 作 。
    4)Data Control Language(DCL 数据控制语言)，如：对用户权限的设置

  (4)MySQL的语法
    1)每条语句以”;”结尾，如果在 SQLyog 中不是必须加的。
    2)SQL中不区分大小写，关键字中认为大写和小写是一样的。
    3)3种注释：
      单行注释：–空格
      多行注释：/* */
      # ：这是 mysql 特有的注释方式

2.DDL操作数据库

  (1)创建数据库
    方式1：create database 数据库名;
在这里插入图片描述
    方式2: create database if not exists 数据库名;
      如果不存在这个库,创建一个新的。
在这里插入图片描述
    方式3：创建数据库的时候直接指定库的字符集
       create database if not exists 数据库名 default(不写) characterset 字符集 ;
在这里插入图片描述
  (2)查看数据库
    1)查看所有数据库
      show databases;
在这里插入图片描述
    2)查看数据库的定义信息 (数据库名,以及数据库的默认的编码格式)
      show create database 数据库名;
在这里插入图片描述
  (3)修改数据库的编码格式为gbk
    alter databse 数据库名 default(省略不写) character set 字符集;
在这里插入图片描述
  (4)删除数据库
    方式1：drop database 数据库名;
在这里插入图片描述
    方式2：drop database if exists 数据库名;
      如果存在,删除该库
在这里插入图片描述
  (5)使用数据库
    use 数据库名;
在这里插入图片描述
    select database();
    查看正在使用的数据库
在这里插入图片描述

3.DDL语句操作表

  (1)常见字段类型:
    1)varchar(m):m多少个字符,mysql字符串类型。
    2)int:默认int(11)，11位(记录实际字符)，int(num)参数指定长度， mysql整数类型。
    3)double(5,2)：mysql的小数类型，表示小数5位,小数点后保留2位。
    4)date:mysql的日期时间,仅仅是日期(年月日),不包含具体时间。
    5)datetime：mysql的日期时间,是日期+具体时间(年月日 时分秒)
    6)timestamp(时间戳):具体哪个时间操作的表。
      例如：2020//11/15 16:16这个时间修改了表的字段。

  (2)创建表
    1)格式：
      create table 表名(
         字段名称1 字段类型1,
         字段名称2 字段类型2,
         字段名称3 字段类型3,
        …
        字段名称n 字段端类型n
      );

    2)在创建表之前,先使用这个库

			use 数据库名
			mysql> use mydb_01;
			Database changed

    3)在mydb_01创建一个学生表

			学生表:		字段		数据类型
						姓名 		varchar(3)
						年龄		int
						性别		varchar(2)
						分数		double
						出生日期 	date

在这里插入图片描述
    4)快速创建一个表结构相同的表。
在这里插入图片描述
  (3)查看表
    1)查询该库中的所有表
      show tables;
在这里插入图片描述
    2)查询表的结构
      desc 表名;
在这里插入图片描述
    3)查看创建表的sql语句
在这里插入图片描述
  (4)修改表
    1)给表中新增一列 (加入一个新的字段)
      alter table 表名add 字段名 字段数据类型;
在这里插入图片描述
    2)修改表中字段的数据类型
      alter table 表名 modify 字段名 字段数据类型;
在这里插入图片描述
    3)修改表表中字段的名称 change
      alter table 表名 change 旧字段名 新字段名 新字段数据类型;
在这里插入图片描述
    4)删除列 DROP
      alter table 表名 drop 列名;
在这里插入图片描述
    5)修改表的名称
      alter table 表名 rename to 新表名;
在这里插入图片描述
    6)修改字符集 character set
      alter table 表名 character set 字符集;
在这里插入图片描述
  (5)删除表
    1)drop table 表名;
在这里插入图片描述
    2)drop table if exists 表名;
      如果这个表存在，就删除
在这里插入图片描述

4.DML语句:操作数据语句

  (1)插入数据
    1)方式1：:给表中的每一个字段赋值
      insert into 表名 values(值1,值2,值3,…)
在这里插入图片描述
    2)给部分字段赋值
      insert into 表名(id,name,age,gender) values(值1,…) ;
在这里插入图片描述
    3)注意事项:
      ①插入数据的时候,插入字段顺序必须要和表的字段对应上;
      ②如果是给全部字段插入数据,中间逗号隔开,依次插入多条语句!
在这里插入图片描述
      ③没有赋值的字段都是NULL(空值)
在这里插入图片描述
      ④数据的大小应在列的规定范围内。
      ⑤插入的数据应与字段的数据类型相同。
      ⑥字符和日期型数据应包含在单引号中。

  (2)蠕虫复制
    将一张已经存在的表中的数据复制到另一张表中。
    1)将表名 2 中的所有的列复制到表名 1 中
      INSERT  INTO  表名1  SELECT  *  FROM  表 名 2;
在这里插入图片描述
    2)只复制部分列
      INSERT  INTO  表名1( 列 1,列 2)  SELECT 列 1,列 2  FROM  表名2;
在这里插入图片描述
  (2)删除表的记录
    1)带条件删除
      delete from 表名 where 字段名=值;
在这里插入图片描述
    2)删除全表数据:
      truncate table 表名 和 delete from 表名的区别
      ①delete from 表名:删除全表数据
在这里插入图片描述
      TRUNCATE table 表名 :删除表以及表中的数据，再创建一张一模一样的表。

      ②是否对自增长的主键影响
      delete from 表名：不会影响自增长的主键，只删除全表的数据，不会清除自增长主键。
      TRUNCATE TABLE 表名:直接删除了表，并且同时创建一张一模一样的表，自增长主键会清除掉。

  (3)修改表中数据
    1)带条件修改单个字段数据
      update 表名 set 字段名 = 赋值 where 字段 = 值;

      注意:update语句都需要带上where条件,否则就是"批量修改"

在这里插入图片描述
    2)修改多个字段的数据
      将id=4的这个人 name=‘高阳’ 并将年龄改成30
在这里插入图片描述

三、DQL语句：数据查询语句

1.基本的查询语句

  (1)查询全表数据
    select 全部字段(*) from 表名;
在这里插入图片描述
  (2)查询指定的字段
    select 字段名,字段名… from 表名;
    需求:查询id,name,age字段
在这里插入图片描述
  (3)去重
    将字段去重查询
    select distinct 字段名称 from表名
在这里插入图片描述
  (4)查询结果参与运算
    1)某列数据和固定值运算
      SELECT 列名 1 + 固定值 FROM 表名;
在这里插入图片描述
    2)某列数据和其他列数据参与运算
      SELECT 列名 1 + 列名 2 FROM 表名;
      需求:查询name,总分成绩
在这里插入图片描述

    3)注意事项:对两个字段进行求和，两个字段类型必须一致:int类型 (math+english)。

  (4)起别名查询
    1)给字段起别名
      select 字段名称 as(可省略不写) ‘名称’ from 表名
在这里插入图片描述
    2)给表起别名
      select 表别名.字段名称 from 表名 别名;
在这里插入图片描述

2.基本条件查询:where语句

  (1)基本的条件关系符号
    比较:使用表达式符号<,>,<=,<=,<>(不等于,mysql的语法,!=也可以使 用)。
    并列关系:and、&&、between and。
    或的关系:or、||
    集合:in(18,25,19)
    是否是空: is null :xxx是null值
    is not null:不是null
    非:!、not

  (2)等于(=)
    select * from 表名 where 字段名=字段值;
    举例：查询年龄是30的人
在这里插入图片描述
  (3)查询在某个字段一定范围的数据
    1)大于等于某个值的范围(>=)
      举例：查询年龄大于20的人
在这里插入图片描述
    2)在某两个值之间的范围(>= + && + <=)
      需求:查询年龄在20岁和30岁之间的人(包含两端)
在这里插入图片描述
    3)and用法(>= + and + <=)
在这里插入图片描述
    4)between…and…(等价于>= + and + <= )
在这里插入图片描述
  (4)是否为空
    1)is null(为空)
      需求:查询英语成绩为null的学生信息。
在这里插入图片描述
    2)is not null(不为空)
      查询英语成绩不为空的学生信息。
在这里插入图片描述
  (5)不等于
    1)java语法:!=
      需求:查询年龄不等于20岁的学生信息。
在这里插入图片描述
    2)推荐mysql语法:<> 不等于
在这里插入图片描述
  (6)或
    1)||连接
      需求:查询年龄是25岁或者18岁或者20岁的人。
在这里插入图片描述
    2)or连接
在这里插入图片描述
    3)in(集合数据):可以填多个数据
在这里插入图片描述

3.模糊条件查询

  (1)关键字:like
    两个符号:
    %:表示多个任意字符
    _:代表一个字符

  (2)格式:select 字段名称 from 表名 where 某个字段 like ‘模糊符号’;

  (3)查询某个字段的第一个字符是指定字符的数据
    举例：查询姓名第一个字符是’小’的学生信息
在这里插入图片描述
  (4)查询某个字段的第二个字符是指定字符的数据
    举例：查询学生姓名第二个字符是’化’的学生信息
在这里插入图片描述
  (5)查询某个字段是指定字符个数的数据
    举例：查询学生姓名是三个字符的人
在这里插入图片描述
  (6)查询某个字段中有指定字符的数据
    举例：查询学生姓名中包含’小’字的人
在这里插入图片描述
  (7)查询某个字段中没有指定字符的数据
    举例：查询学生姓名中不包含’小’字的人
在这里插入图片描述
  (8)如果dos中出现中文乱码,查询所有包含’character’的变量
    SHOW VARIABLES LIKE ‘%character%’ ;

4.查询排序

  (1)关键字:order by
    asc :升序排序
    desc:降序排序

  (2)格式：
    select 字段名称列表 from 表名 order by 排序的字段 排序规则;

  (3)如果执行排序的时候,某个字段后面没有跟排序规则:默认升序排序(整数类型)。

  (4)升序排序
    举例：按照身高升序排序
在这里插入图片描述
  (5)降序排序
    举例：按照体重降序排序
在这里插入图片描述
  (6)针对多个字段同时排序
    需求:查询全表数据,体重升序,身高降序。
在这里插入图片描述
  (7)多个字段同时排序:
    优先按照第一个字段的排序规则排序,
    第一个字段排序值如果相同,
    这个时候再按照第二个字段排序规则排序。

5.聚合函数

  (1)常用函数：
    count函数：统计记录数
    max函数：最大值
    min函数：最小值
    avg函数：平均数
    sum函数：求和

  (2)count(非业务字段)
    count的使用:一般都是用表的非业务字段，如id。
    需求:统计当前表中总记录数有多少条
在这里插入图片描述
  (3)max(数值型字段)
    需求:查询数学成绩的的最高分的学生信息
在这里插入图片描述
  (4)avg(数字型字段)
    举例：查询英语平均分
在这里插入图片描述
  (5)sum(任意个数数值型字段)
    举例：查询数学与英语的总分
在这里插入图片描述
  (6)min(数字型字段)
    需求:查询数学成绩的的最低分的学生信息。
在这里插入图片描述
  (7)函数：ifnull(字段名,默认值)
    ifnull(字段名,默认值):如果当前字段名称是null值,使用默认值给它 赋值。
    例如：当前何瑾的英语成绩为null
    在获取英语成绩的时候提供一个函数ifnull(english,0)。
在这里插入图片描述

6.分组查询

  (1)分组:分组查询是指使用group by语句对查询信息进行分组，相同数据作为一组，进行数据统计。

  (2)格式：group by 分组字段

  (3)group by分组查询,如果带有条件进行分组查询,要先去判断条件,再进行分组,即先where,后group by。

  (4)查询的字段:要有分组字段,还有聚合函数。

  (5)按照某个字段分组
    需求:查询数学平均分并且按照性别进行分组
在这里插入图片描述
  (6)按照指定条件，进行某个字段分组
    需求:查询数学的平均分，并且按照性别分组。
    条件：数学成绩小于70分的学生不参与分组。
在这里插入图片描述

7.筛选

  (1)关键字：having

  (2)格式：having 筛选的字段(字段是查询结果中存在的字段)

  (3)需求:查询数学的平均分 并且按照性别分组。
    条件：数学成绩小于70分的学生不参与分组。
    帅选条件:总人数大于2等于个人的
在这里插入图片描述
  (4)变形

		SELECT  sex '性别',AVG(math) '数学平局分',COUNT(id) ‘人数’
		FROM student3  
		WHERE  math > 70  
		GROUP BY  sex
		HAVING  ＇人数＇>= 2;

  (5)having和group by的区别
    1)group by是在where条件后面用,不能使用聚合函数的,但是查询的 时候可以查询聚合函数。
    2)having是在group by 后面使用,后面可以跟聚合函数。
    3)having：是需要放在group by 后面,不能放置在group by 之前。

  (6) having 与 where 的区别
    where 子句 :
      1)对查询结果进行分组前，将不符合 where 条件的行去掉，在分组之前过滤数据，即先过滤再分组。
      2)where 后面不可以使用聚合函数 。

    having子句:
      1)having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。
      2) having后面可以使用聚合函数。

8.分页查询

  (1)关键字:limit是限制的意思，所以 LIMIT 的作用就是限制查询记录的条数。

  (2)语法格式:limit 起始行数,每页显示的条数。
    limit 起始行数=(当前页码数-1)*每页显示的条数,每页显示条数;

  (3)举例：起始行数:从0开始，每页展示3条数。
    查询第一页的数据
在这里插入图片描述
    查询第二页的数据:每页2条
在这里插入图片描述
    查询第一页:从0开始，每页4条
在这里插入图片描述

四、数据库约束

1.数据库约束

  (1)约束:通过一些特定的关键字 保证数据的安全性,正确性和有效性。

  (2)常见约束
    默认约束：字段名称 类型 default ‘值’
    非空约束：not null
    唯一约束：unique
    主键约束：primary key
    自增长约束：auto_increment
    外键约束：foreign key

2.默认约束:default

  (1)默认约束:是当某个字段没有赋值的时候起作用,如果赋值了,那么按照实际值赋值即可。
  (2)为某个字段添加默认约束
    alter table 表名 modify 字段名 字段数据类型 default ‘默认值’;
在这里插入图片描述
  (3)创建表时添加默认约束
    字段名称 类型 default ‘默认值’;
在这里插入图片描述
  (4)举例：向teacher表中插入两条数据，做对比
在这里插入图片描述
  (5)数据不安全,默认约束可以插入null。
    为了防止插入的字段不是null,可以给表加入非空约束。
在这里插入图片描述
  (6)删除默认约束(修改表字段类型)
    alter table 表名modify 有默认约束的字段 字段类型;
在这里插入图片描述

3.非空约束:not null

  (1)非空约束：加入非空约束的字段其值不能是null。

  (2)创建表时给某个字段添加非空约束。
    字段 字段数据类型 not null;
在这里插入图片描述
  (3)给表中某个字段添加非空约束。
    alter table 表名moify 字段名 字段数据类型 not null;
在这里插入图片描述
  (4)举例：向表中添加一条name为空的数据;
在这里插入图片描述
    错误：列“name”不能为空。
在这里插入图片描述
    非空约束不能添加null，但可以添加’ ’。

  (5)删除非空约束(修改表字段类型)
    alter table 表名 modify 有非空约束的字段 字段数据类型;
在这里插入图片描述

4.唯一约束:unique

  (1)唯一约束：字段值不能重复。

  (2)创建表时为某字段添加唯一约束
    字段名 字段类型 unique
在这里插入图片描述
  (3)为表中某个字段添加唯一约束
    alter table 表名 modify 字段名 字段类型 unique;
在这里插入图片描述
  (4)举例:向表中添加两条phone值相同的数据
在这里插入图片描述
    错误：phone项重复
  (5)删除唯一约束
    alter table 表名drop index 有唯一约束的字段;
在这里插入图片描述

5.主键约束：primary key

  (1)主键约束:非空且唯一,主键都应用在非业务字段上。

  (2)创建表时添加主键约束
    非业务字段名 字段类型 primary key
在这里插入图片描述
  (3)添加主键
     给表中的已有id字段添加主键
     alter table 表名 add primary key(id);
在这里插入图片描述
     添加id字段并添加主键
     alter table 表名 add id int primary key;
在这里插入图片描述
     修改id的数据类型,并添加主键
     alter table 表名 modify id int primary key;
在这里插入图片描述
  (4)一个表中的字段有:非业务字段(id)和业务字段(name,age等)。
     业务字段可能随着需求的变化不断变化，所以不会在业务字段上加入 primary key，都是给每一张表的id字段设置主键和自增长。

  (5)举例：添加两条id相同的数据
    错误：id值11重复
在这里插入图片描述
    添加一条id为null的数据
在这里插入图片描述
    添加一条id为0的数据
在这里插入图片描述
    添加一条没有id值的数据
在这里插入图片描述
    添加两条id值为0的数据
在这里插入图片描述
    结论：添加主键约束的非业务字段，其值不能为null,可以为0，如果不写，默认添加0值，0值只能添加一次。

  (6)删除主键约束
    alter table 表名 drop primary key;
在这里插入图片描述

6.自增长约束：auto_increment

  (1)自增长约束：id值自增从起始索引0一直++。

  (2)创建表时为id添加自增长约束
    id int auto_increment
在这里插入图片描述
  (3)为表中的id字段添加自增长约束
     alter table 表名 modify id int primary key auto_increment;
在这里插入图片描述
    如果表中存在id为0的数据，添加自增长会与主键的唯一性冲突。

  (4)修改自增长的起始值
    在创建表时修改
      CREATE TABLE 表名( 列 名 int primary key AUTO_INCREMENT )
      AUTO_INCREMENT=起始值;
    表创建好之后修改
      ALTER TABLE 表名 AUTO_INCREMENT=起始值;

  (4)添加一条没有id值的数据：id值为上一条数据的id值加1.
在这里插入图片描述
  (5)删除自增长约束(修改id的类型)
     alter table 表名 modify id int;
在这里插入图片描述

7.外键约束

  (1)外键约束:让多个表之间产生一种关系,来保证数据的安全性和有效性。
    主表:没有外键的表。
    从表：表中有外键声明。

  (2)引入：
    部门名称:字段值大量冗余(重复名称)
    1)解决字段的冗余问题
      方案:单独的在创建一张表:部门表 dept表，存储者三个部门:研发 部,测试部, 销售部。
      将原来的部门名称换为部门表中对应部门的id。

    2)问题:往员工表中插入一条不存在的部门数据,依然可以插入成功(存 在非法数据)。
      解决：为了防止表中有非法数据,加入一种潜在关系，外键约束
      给某个字段设置外键约束
      constraint(声明) 外键名称
      foreign key 员工表的dept_id
      reference(关联) 部门表(主键id)

  (3)创建表时为某个字段添加外键约束
    constraint 外键名称 foreign key (表中要添加外键的字段)
    reference 主表(主表的主键)
在这里插入图片描述
    表中要添加外键的字段的命名：主表名_id。
    外键名称：主表名_从表名_fk;

  (4)如果要给从表中添加主表没有的数据，需要先在主表添加。
    不能直接修改/删除当前字段,除非当前这个表字段和主表的id字段没 有关联关系,才能修改。

  (5)给已有表中的某个字段添加外键
    ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称]
    FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
在这里插入图片描述
    [CONSTRAINT] [外键约束名称]:声明外键约束名称，可以不写，为默认 名字。

  (6)删除外键
    ALTER TABLE 从表 drop foreign key 外键名称;
在这里插入图片描述
  (7)什么是级联操作：
    在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联 操作。

  (8)级联操作语法
    ON UPDATE CASCADE：级联更新。
    ON DELETE CASCADE： 级联删除。
    注意：只能是在添加外键的时候添加级联关系。
      更新主表中的主键，从表中的外键列也自动同步更新。

  (9)添加外键约束，同时添加级联更新和删除。
    constraint 外键约束名称 foreign key (外键字段名)
    references 主表(主表主键) on update cascade on delete cascade;
在这里插入图片描述

五、多表查询

1.多表查询

  (1)多表查询：通过查询两张或者两张以上的表时使用的sql语句。

  (2)多表查询分类：
    内连接：隐式内连接、显式内连接。
    外连接：左外连接、右外连接。

  (3)直接查询两张表或多张表存在问题:产生笛卡尔乘积。
    A表中有n条数据,B表中有m条数据
    select * from A,B;n*m=总条数

  (4)解决上面笛卡尔积的方案
    满足条件:
    1)查询哪个：表员工表和部门表。
    2)查询指定表中的哪些字段：查询所有
    3)这些表的连接接条件：员工表的dept_id部门编号依赖于部门表的主键id，即员工表.dept_id=部门表.id。

2.多表查询之内连接

  (1)隐式内连接:条件使用WHERE指定。

  (2)格式：select 字段名列表 from 表名列表 where 连接条件;
  (3)使用
    1)多表查询所有字段
在这里插入图片描述
    2)多表查询,查询指定字段
在这里插入图片描述
    3)给表起别名
在这里插入图片描述
  (4)显示内连接:使用 INNER JOIN … ON 语句, 可以省略 INNER

  (5)格式：select 字段名列表 from 表名1 inner join 表名2 on 连接条件；

  (6)使用
    1)查询部分字段，给表起别名，不省略inner
在这里插入图片描述
    2)查询所有关键字，省略inner
在这里插入图片描述

3.多表查询之外连接

  (1)左外连接:将左表的数据全部查询,并查询交集部分(连接条件)。

  (2)格式：select 字段列表
    from 左表(表名1)
    left outer join 右表(表名2)
    on 连接条件

  (3)使用
    查询所有学生的信息以及所在班级
在这里插入图片描述
  (4)右外连接查询:将右边全部显示,以及多个表的交集部分数据(连接条件)。

  (5)格式：select 字段列表
    from 左表(表名1)
    right outer join 右表(表名2)
    on 连接条件;

  (6)使用
    查询所有班级的学生信息。
在这里插入图片描述
  (7)特点：
    1)左外连接：用左边表的记录去匹配右边表的记录，如果符合条件的则 显示；否则，显示 NULL 。
      可以理解为：在内连接的基础上保证左表的数据全部显示。

    2)右外连接：用右边表的记录去匹配左边表的记录，如果符合条件的则 显示；否则，显示 NULL 。
      可以理解为：在内连接的基础上保证右表的数据全部显示。

4.多表查询之子查询

  (1)子查询：
    1)一个查询的结果做为另一个查询的条件;
    2)有查询的嵌套(select 嵌套select语句)，内部的查询称为子查询;
    3) 子查询要使用括号;

  (2)子查询结果的三种情况：
    1)子查询的结果是单行单列
    2)子查询的结果是多行单列
    3)子查询的结果是多行多列

  (3)子查询结果是一个值
    1)格式：SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;
      select 字段列表
       from 表名
       where 字段名(<,<=,>=,>,=)
       (select 字段列表
      from 表名…)

    2)举例：查询最大年龄的学生信息。
在这里插入图片描述
    3)举例：查询大于平均年龄的学生信息
在这里插入图片描述
  (4)子查询结果是一个字段的多个值(多行单列)
    1)格式：SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
      select 字段列表
       from 表名
      where 字段名 in(select 字段列表
      from 表名…)

    2)举例：查询指定班级的学生信息
在这里插入图片描述
    3)in(多个值)实现
在这里插入图片描述
  (5)子查询的结果是多个字段的多个值(多行多列,即参与子查询的表的子表)
    1)多行多列:将一个表的查询结果作为一个虚表来进行二次查询。

    2)格式：SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;

    3)注意：子查询结果只要是多列，肯定在 FROM 后面作为表(虚表，不是真 实存在的表)。
      子查询结果作为表需要取别名，否则这张表没有名称则无法访问表中的字段。

    4)举例：查询年龄大于20的学生信息，并查看所在班级
在这里插入图片描述
    5)优化
在这里插入图片描述

六.事务

1.事务

  (1)事务：将整个业务操作看成一个整体; 这些事务的执行要么同时成功,要么同时失败。

  (2)事务原理
    事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有 在得到 commit命令才会同步到数据表中，其他任何情况都会清空事务 日志(rollback，断开连接)。
  (3)原理图：
在这里插入图片描述
  (4)事务的步骤：
    1)客户端连接数据库服务器，创建连接时创建此用户临时日志文件。
    2)开启事务以后，所有的操作都会先写入到临时日志文件中。
    3)所有的查询操作从表中查询，但会经过日志文件加工后才返回。
    4)如果事务提交则将日志文件中的数据写到表中，否则清空日志文件。

2.回滚点

  (1)回滚点
    在某些成功的操作完成之后，后续的操作有可能成功有可能失败，
    但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。
    可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

  (2)回滚点的操作语句
    设置回滚点：savepoint 回滚点名字
    回到回滚点：rollback to 回滚点名字

  (3)操作案例：
    1)开启事务;
    2)让张三账号减3次钱，每次10块;
    3)设置回滚点：savepoint three_times;
    4)让张三账号减4次钱，每次10块;
    5)回到回滚点：rollback to three_times。

  (4)总结：设置回滚点可以在事务操作失败的时候回到回滚点，而不是回到事务开启的时候。

3.事务提交方式

  (1)事务的两种提交方式：
    1)手动提交事务
    2)自动提交事务

  (2)当前提交方式的查看和修改:
    查看：SELECT @@autocommit ;
    修改：set @@autocommit = 0 ;
      1:默认自动提交
      0:非自动提交(关闭自动提交，需手动提交)
    针对DML语句:增删改，数据自动提交的。

  (3)手动提交事务
    1)用到的语句：
      start transaction ;开启事务
      rollback ; 回滚事务,回滚到操作语句之前。
      commit; 提交数据
    2)如果开启事务:针对数据进行增删改，这些语句执行完毕之后必须 commit(手动提交)。

  (5)手动提交案例
    开启事务
    START TRANSACTION ;

    – 张三给李四转账500
    UPDATE account SET balance = balance - 500 WHERE id = 1 ;
    UPDATE account SET balance = balance + 500 WHERE id = 2 ;

    – 回滚事务 :如果执行sql语句时出现异常，回滚到操作语句之前的状 态(两个账户都没有转钱)。
    ROLLBACK;

    – 提交事务：如果执行sql没有问题就提交，提交之后再修改数据表数 据。
    COMMIT ;

  (6)自动提交事务
    MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，
    每条语句都会自动开启一个事务，语句执行完毕自动提交事务，
    MySQL 默认开始自动提交事务。

4.事务的特性：ACID

  (1)原子性(Atomicity):事务是不可分割的,要么sql同时执行成功,要么同时执行失败。

  (2)一致性(Consistency):事务在执行前数据库的状态与执行后数据库的状态保持一致，即操作的数据总量不发生变化。

  (3)隔离性(Isolation):事务和事务之间是独立的,比如:修改账户/ 添加/删除,每一个具体业务都是独立的事务。

  (4)持久性(Durability):一旦事务执行成功,对数据库的修改是持久的。就算关机，也是保存下来的。

5.数据库的并发访问问题

  (1)事务在操作时的理想状态：所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。

  (2)可能引发的并发访问问题：
    1)脏读:是最危险的，一个事务读取到了另一个没有提交的事务。

    2)不可重复读：一个事务中两次读取的数据内容不一致，
          要求的是一个事务中多次读取时数据是一致的，
          这是事务update时引发的问题。

    3)幻读：一个事务中两次读取的数据的数量不一致，
        要求在一个事务多次读取的数据的数量是一致的，
        这是insert 或 delete 时引发的问题。

  (3)解决方式：逐级提升隔离级别，可以逐个解决，serializable级别可以解决所有问题。

6.事务的隔离级别

  (1)mysql的隔离级别默认第三种:repeatable-read:可重复读。

  (2)四个隔离级别
    1)read uncommitted :可以读到未提交的事务,安全性最差，出现脏读，不可重复读，幻读问题。
    2)read committed:只能读到已提交的事务，但是两次读取的结果不 一 致。
    3)repeatable read: 可重复读，多次读取的结果一致。
    4)serializable :串行化，隔离级别最高。

  (3)隔离级别越高,事务执行越安全，性能越低。

  (4)查看事务的隔离级别:
    select @@tx_isolation;

  (5)设置隔离级别：
    set global transaction isolation level 隔离级别名称;

敐
关注
专栏目录
JavaWeb——SQL语言的学习
anrui0的博客
07-06 218
SQL介绍 1.SQL的概念 SQL(Structured Query Language):结构化查询语言，其实就是定义了操作所有关系数据库的规则。每一种数据库的操作方式处在不一样的地方称为“方言”。 2.SQL语言的书写格式 (1).SQL语言可以单行或多行书写，以分号结尾。 (2).可以通过空格或者缩进来增强语句的可读性。 (3).MYSQL数据库的SQL语句不区分大小写，关键字建议使用大写。 (4).三种注释 *单行注释；--（...
JavaWeb点餐系统前后台及其sql语句
12-18
【JavaWeb点餐系统前后台及其sql语句】是一个典型的Web开发项目，它结合了Java技术和SQL数据库操作，用于实现在线餐饮订购的功能。这个系统包括前端用户界面和后端服务器逻辑，通过HTTP协议进行通信，为用户提供浏览...
记录我的javaweb学习路1 ：SQL语句
low666666的博客
03-24 676
记录我的javaweb学习路1 ：SQL语句
Java web学习-SQL通用语句
感谢关注
09-08 243
Java web学习-SQL通用语句 文章目录Java web学习-SQL通用语句SQL通用语句SQL分类DDL：操作数据库、表DML：增删改表中数据DQL：查询表中的记录约束数据库的设计 SQL通用语句 SQL语句可以单行或多行书写，以分号结尾 可使用空格和缩进来增强语句的可读性 MySQL数据库的SQL语句不区分大小写，关键字建议使用大写 3种注释 单行注释：-- 注释内容 或 # 注释内容(mysql 特有) 多行注释：/* 注释 */ SQL分类 DDL：操作数据库、表 DML：增删改表
JavaWeb项目开发SQL语句总结
weixin_43881282的博客
05-09 525
1、查询语句 select * from 表名 where 条件 n、limit用法 查询数据时，需选择查出哪些行的数据，那么limit语句就派上用场了。 语法： select * from 表名 limit 起始行（整型，从0开始），检索总行数（整型） 例如： select * from user limit 0,10 //检索1-10行 特别： 当检索总行数为-1时，指起始行到结束行 当只给起始行一个参数n时，表示返回前n行 ...
java sql 结果_Java 获取SQL查询语句结果
weixin_35363322的博客
02-12 6033
step1:构造连接Class.forName("com.mysql.jdbc.Driver");Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db","root","123");step2:构造语句String sql = "select username,password from t_use...
Javaweb用sql语句渲染表单
最新发布
06-02
在Javaweb中，可以使用SQL语句获取数据库中的数据，然后将数据渲染到表单中。以下是一个示例： 假设我们有一个名为“students”的表，其中包含“id”、“name”和“age”字段。我们可以使用以下SQL语句获取所有学生...
SQL语句SQL语句.zip
04-20
在JavaWeb开发中，SQL语句常用于后端与数据库交互，例如通过JDBC（Java Database Connectivity）来执行SQL查询。开发者需要熟悉SQL，以便有效地与数据库进行通信，存储和检索数据，从而实现Web应用程序的功能。在...
客户管理系统（javaweb，sqlserver）
10-04
2. SQL查询：通过SQL语句，开发者可以进行数据的增删改查操作，实现对客户数据的动态管理。 3. 数据安全性：SQLServer提供了多种安全机制，如用户权限控制、备份恢复策略，确保客户数据的安全。 三、客户管理系统...
综合供求信息网（javaweb,sqlserver）
10-04
使用SQL语句进行数据的增删改查操作，如INSERT插入新记录，DELETE删除记录，UPDATE更新记录，以及SELECT查询记录。此外，SQLServer还提供了事务处理、存储过程、视图、索引等高级功能，以优化数据操作性能和安全性。...
Java开发的sql生成器
02-06
java写的sql生成器，可以自动生成sql语句。
java web代码生成器
03-27
java web开发后台代码自动生成，支持SSH和SSM2大框架，数据库支持MySQL和sqlserver，根据数据库自动生成entity,vo,service,impl,action或者controller,dao,mapper等类，包含常用的增删改查方法，集成了pageheper分页插件，swagger，redis。减少重复性的代码编写，大大提高开发效率。
java+js+sql网页
02-11
java+js+sql做的个人网页，内容很好
过滤出java中的sql，js中的javascript
07-03
帮你找出java中的sql以及jsp中的javascript代码行
JavaWeb网上商城项目中sql语句问题的解决
weixin_30845171的博客
12-11 258
出现的问题 解决方法 对ProductDaoImpl建立Junit测试得到问题所在 学到了 Junit case测试验证和debug分步执行 mysql是关系型数据库。Redis是非关系型数据库且比mysql访问效率高，可以装在虚拟机上运行 转载于:https://www.cnblogs.com/heamin/p/10106144.html...
java sql语句两个,Java中调用两个SQL语句,该怎么解决
weixin_34327476的博客
03-13 674
当前位置:我的异常网» Java Web开发»Java中调用两个SQL语句,该怎么解决Java中调用两个SQL语句,该怎么解决www.myexceptions.net网友分享于：2013-03-31浏览：26次Java中调用两个SQL语句我的想法是：先用一条查询语句查询数据库中属否存在该数据(也就是订单号：id)，如果存在则通过该id对任务表(rw)进行添加，而我没有用查询条件进行向r...
Javaweb基础-SQL查询
weixin_46899412的博客
12-30 1062
SQL查询select Student表
javaweb ssm sql 语句报错
weixin_43871059的博客
11-29 338
周一学习了ssm框架，执行的时候一个SQL语句报错，但其他都可以执行 这个是我的sql代码 &lt;update id="update" parameterType="lms.entity.Book"&gt; UPDATE book SET name=#{name}, author=#{author}, publisher=#{publisher}, is...
java/jsp执行sql语句的方式
cout0
06-16 3622
首先给出sql驱动包(CSDN无法设置免CB下载，介意的请大家另辟途径获取) 引入sql包 import java.sql.*;//java <%@ page import="java.sql.*"%>//jsp 连接mysql数据库 String DBDRIVER= "com.mysql.jdbc.Driver";//驱动程序 String DBURL="jdbc:...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值