淘先锋技术网

首页 1 2 3 4 5 6 7

数据库

1. 前言

1.1 什么是数据库?

  1. 怎么理解数据库呢? 可以把数据库比作 “账本”+“仓库” 的结合体。 它的作用就是: “管理和存储” 数据。
  2. 数据库(DataBase),一般成为DB是一个专业的管理数据的软件系统(DataBase Management System,我们简称为DBMS)
  3. 相对人工记录数据和管理文件保存数据而言,数据库优势就很明显,更安全,更可靠,更快捷。

1.2 为什么要学习数据库?

  1. 搞明白一个问题? 对于我们程序员来说,就需要知道它的用处。想想我们生活中已经是 “互联网2.0时代” 了,数据更有效的管理和存储就是重中之重。
  • 日常操作app,或者 浏览网站,都会产生数据,这些数据就是宝贵的财富。不管是对于用户而言还是公司而且都有不同的价值。 这些“财富” 就会存储在数据库中,然后再从数据库中使用这些数据创造价值。 比如: 用户画像。

    扩展知识: 数据库分类
    关系型数据库,非关系型数据库(占时不讨论)。

1.3 什么是关系型数据库?

  • MySql 就是关系型数据库的一种,其实就是底层是以 “二维表” 的形式保存数据,属于关系型。

学生表:

编号姓名班级
1路飞1
2索隆2

班级表:

班级编号班级名称
1三年二班
2三年三班

扩展知识:
常见的关系型数据库,有哪些?

1.SQL Server
	微软提供,适用于中型、大型项目,收费,在java 中的使用占比不高。在.NET语言中使用较多。
2.Oracle
	甲骨文公司提供,适用于大型、超大型项目,功能强大,性能优异,收费,在] ava中使用占比很高。"
3. mysql
	瑞典 MySQLAB 公司提供,适用于小型、中型项目,免费开源,小巧轻量,性能也不差。在Java中使用占比较高。
	它还有一个亲兄弟,MariaDB,是有mysql创始人开发的,完全兼容mysql。
4. DB2
     IBM公司提供,适用于大型项目,收费,在 Java 中使用占比不高。。 Sqlite:迷你,嵌入式设备,智能家居,手机,ipad等。
 5. Sqlite
    迷你,嵌入式设备,智能家居,手机,ipad等。

2. 数据库的安装与卸载

  1. 请参考初学者 MySQL 数据库安装与卸载使用手册,即可。
  2. 包括:如何启动,关闭,连接数据库。
  3. 扩展知识: 退出操作,在命令窗口下,quit 或者 exit。 当输出错误,无法回删除时,使用反斜杠c,\c
    在这里插入图片描述

3. SQL语句概念和类型

  • 操作数据库就需要使用相应的规则,即使用sql语句操作。

3.1 什么是Sql语句?

  • 官方的回答: Structure Query Language(结构化查询语言)简称SQL,它被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准,后被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。数据库管理系统可以通过SQL管理数据库;定义和操作数据,维护数据的完整性和安全性。
  • 白话就是: 使用者与数据库可以交流的语言,就是sql语句。主要分为以下几类。

3.2 SQL语言的分类

  1. DDL(Data Definition Language) 数据定义语言,用来操作数据库、表、列等; 常用语句:CREATE、 ALTER、DROP
  2. DML(Data Manipulation Language) 数据操作语言,用来操作数据库中表里的数据;常用语句:INSERT、 UPDATE、 DELETE
  3. DCL(Data Control Language) 数据控制语言,用来操作访问权限和安全级别; 常用语句:GRANT、DENY
  4. DQL(Data Query Language) 数据查询语言,用来查询数据 常用语句:SELECT

3.3 SQL语句规范说明

  • 通过数据库的 sql语言分类 来操作使用数据库。

    1. 注意:Sql语句对大小写不敏感,推荐关键字用大写,自定义小写(数据库名,表名,列名,别名)。
    说明:
    	12 在本质上是一致的,都可以使用。因为数据库不区分大小写。
    		但是,推荐第3种写法。一个字“规范”。
    		1. 	create database mybase;
    		2.  CREATE DATABASE MYBASE;
    		3.  CREATE DATABASE mybase;
    
    1. 在数据库自定义表中 不推荐java的驼峰命名法, 而是使用下划线去连接,如: tab_name 而不是tabName;

3.4 MySQL中的数据类型

  1. 整数类型
    • 根据数值取值范围的不同MySQL 中的整数类型可分为5种,分别是TINYINT、SMALUNT、MEDIUMINT、INT和 BIGINT。
    • 使用场景,以及如何取值,记住的一点就是系统故障成本远远超过内存空间成本。
      在这里插入图片描述
  1. 浮点类型
    • 浮点数和定点数 类型的特点是都可以处理小数 。
    • 定点数(Decimal)类型取值范围相对小,但是精准,没有误差,适合于对精度要求极高的场景 (设计到金额计算),M被称为精度,D被称为标度。
    • 举个栗子: M表示的是数据的长度,D表示的是小数点后的长度。将数据类型为DECIMAL(5,2)的数据3.1415 插人数据库后显示的结果为3.14

在这里插入图片描述

  1. 字符串类型
    • 在MySQL中常用CHARVARCHAR 表示字符串。区别在于:VARCHAR存储可变长度的字符串。CHAR 是固定长度。啥意思?
    • VARCHAR 定义时必须指定长度(M),否则会报错。
字符串类型长度长度范围
CHAR固定长度0~255
VARCHAR可变长度0~65535
  1. 大文本数据类型
    • 适合存储大文本数据,例如,文章内容、简历、小说等等。LONGTEXT 相当于能存储4GB,MEDIUMTEXT 16M
数据类型长度范围
TINYTEXT0~255
TEXT0~65535
MEDIUMTEXT0~16777215
LONGTEXT0~4294967295
  1. 日期类型
    • MySQL有多种表示日期和时间的数据类型,日期和时间类型主要有:YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型。
    • 注意: TIMESTAMP( 时间戳 )底层存储的是毫秒值,距离1970-1-1 0:0:0 0毫秒的毫秒值。两个日期比较大小或日期计算时,TIMESTAMP更方便、更快。
    • (时间戳)当使用插入时,或者更新,它可以自动更新为当前系统时间。 例如:创建时间。
      在这里插入图片描述
  • 开发中的建议: 在定义数据类型时,如果确定是 整数 ,就用 int; 如 小数 ,一定用定点数类型DECIMAL(M,D) ; 如果是日期与时间,就用DATETIME

4 数据库的基本操作

4.1 显示数据库

  1. 查看服务器中所有的数据库,列出所有库的名称。
	1.显示数据库
		show databases; 

在这里插入图片描述

4.2 进入数据库

  1. 进入 某一个数据库如果想在指定的数据库操作表, 就需要先进入数据库,之后才能操作表和表数据。

    • 如果操作表,但是没有先进入数据库会有错误提示,No database selected
 1.进入数据库
	use  数据库名称;
	use mysql;

在这里插入图片描述

4.3 显示当前数据库名称

  • 想知道当前使用的数据库的名称。
 1.显示当前数据库名称
	select databases();

在这里插入图片描述

4.4 创建数据库

  • 语法格式:create database 数据库名称 charset 编码格式 创建新的数据库,在服务器上。
 1. 创建数据库
   create database mydb charset utf8;

在这里插入图片描述

4.5 查看数据库的基本信息

  • 语法格式 : show create database 数据库名称;
  1.显示数据库的基本信息,并查看编码格式
   show create database mydb; 

在这里插入图片描述

4.6 删除数据库

  1. 删除语法格式: drop database 数据库名称;
    • 注意:数据库有表的情况下,是无法删除数据库的。
 1.删除数据库
	drop database mydb;

在这里插入图片描述

5. 数据库表的基本操作

5.1 创建表,查看表结构

  1. 在数据库中创建表操作,列名会对象相应的数据类型。中间用 逗号 分割。
    • 如:创建学生表编号 (整型),姓名(字符串),性别(字符串),出生年月(日期型), 成绩(浮点型))。
 1.创建语法格式:
	create table 表名(
		列名 数据类型,
		列名 数据类型,
		... 
	);

1.1 例子:
	create table stu(
		id int, 
		name varchar(50),
		gender varchar(10),
		birthday date,
		score decimal    //不指定,默认 (10.0),无精度。
	);
  1. 显示数据库中的表,并且查看其结构。
 1.显示数据库中的表
		show tables;

 2.查看表结构 
		desc 表名称;
		desc stu;

在这里插入图片描述

5.2 删除表

  1. 删除表,即使表里有数据也会直接删除一定要谨慎操作。
	1,删除表操作
		drop table 表名;
		
		drop table stu; 

在这里插入图片描述

5.3 新增,查询,修改,删除表记录

  • 语法就是针对表中数据,添加,修改,删除,相应记录,相当于使用DML数据库操作语言。
  1. 新增(插入):往表中的新增数据,相当插入数据 。
    • 注意插入时列名可以省略,但是顺序要一致,并且涉及到日期类型和字符串类型 需要加单引号。

	1.往表中插入数据,语法格式:
	 	insert into 表名 (列名1,列名2,列名3 ...) values(1,值2,值3 ...); //列名可以省略。但是顺序一致。
	2.例子:
		1.	insert into stu values(1,'路飞','男','2020-12-20',98.2);
		2.	insert into stu(id,name,gender,birthday,score) values(2,'山治','男','2021-12-20',100.2);

在这里插入图片描述

  1. 查询: 查询表中的数据,即:显示表中的记录。
    • 可以按照列名查询,查询指定列名数据。也可以省略使用" * "号代替,相当于全部列名查询。
 	1.根据列名显示表中的记录,语法格式:
		select 列名1,列名2,列名3, ... from 表名;
		select * from 表名;
	2.例子:
		1. select id ,score from stu;
		2. select * from stu;

在这里插入图片描述

  1. 修改:更改表中的数据,即:修改表中的记录。修改有两种情况:如下
    • 第一种:指定修改表中的,某一条记录,加上where 控制范围。
    • 第二种:修改表中相应字段的全部数据。不加where控制范围。
提示: where 相当于过滤条件关键字。
  1 修改表中数据的语法格式:
		 update 表名 set  列名=值,列名=where 子语句(条件);

  1.1 修改stu表中的分数,将所有分数加10分。
		 update stu set score = score+10;
  1.2 修改指定的记录数据,如name = 路飞的记录 score 减10;
		 update stu set score = score-10 where name ="路飞";

在这里插入图片描述

  1. 删除:同更新操作一样,可以全部删除,也可以删除指定的记录。
    • 需要使用 条件关键字 where 过滤选项。
    • 提示 语法格式: delete from 表名 where 子语句;
  1.删除成绩大于100分的。
		delete from stu where scroe>=100;
  2. 全部删除表中记录;
		delete from stu;

在这里插入图片描述

  • 提示: 在操作表数据的时候一定要注意,操作大量数据时,应该先备份表,然后再操作。 或者有什么有什么更好的办法!? 答案: 开启事物操作,提交和回滚控制

6. MySQL 字段约束

  • 字段约束,又叫列约束,约束相当于规则,字段约束就是即:列的规则。

6.1 主键约束

  1. 一个列如果添加了主键(primary key)约束,那么这个列就是主键,主键的特点是,唯一且不为空,且不能重复
    • 如:身份证,每一个人独立一个,且不重复。
    • 没有加主键约束的字段id值,如下图:
      在这里插入图片描述
  1.语法格式
		creaete table stu(
				列名 数据类型 primary key,
				...
		);
  • 主键自增策略: 当id为数值类型时,可以让数据库维护id的序号,每次自增1,这样可以省去了开发人员的底层维护,需要关键字AUTO_INCREMENT 设置到需要维护的id上即可。
    在这里插入图片描述

6.2 非空约束

  1. 如果一个列加了非空约束(not null)约束,那么这个列的值就不允许为空,但是可以重复。
    • 如:性别,密码等。不允许为空,但是可以重复。
    • 如果不设置not null 即允许为空,效果如下:
      在这里插入图片描述
 1.非空约束语法格式:
		create table stu(
			gender varchar(10) not null,
			password varchar(30) not null,
			...
	    );

6.3 唯一约束

  1. 一个列添加了唯一约束(unique),那么这个列就是唯一,且不能重复,但是可以为空
    • 如:用户名 username。
  1.非空约束语法格式:
		create table stu(
			id int primary key auto_increment,
			username varchar(13) unique,
			phone varchar(11) unique not null, //可以和非空一起使用。
			...
	    );

在这里插入图片描述

7. 数据库查询表记录

  • DQL 数据库查询语言,也是数据库大量使用的操作,接下来就是针对各种情况的查询操作介绍。
  • 提示:操作之前,先将准备数据导入 创建数据库,创建表结构,并插入数据。

7.1 SELECT 基础查询

  1. SELECT 语句用于从表中选取数据。查询的结果被存在一个表中,该表称为 结果集。

    • 语法格式: select 列名称 | * from 表名;
    • 提示: * 星号为通配符,相当于所有列。 如果输入列名,只查询指定列数据。
  2. 请先导入数据,在操作。
    在这里插入图片描述

	1. 查询employ表中, 所有的列的数据信息。
		 	 select * from employ; 
		  
	2. 查询指定列名称,部门 职位,的数据信息。
		 	select department ,job from employ; 

在这里插入图片描述

  1. 如果查询的记录存在大量的 重复数据 应该怎么处理!?
1. 使用去重复关键字 distinct ,在列之前加入即可。 
	
	 select distinct department ,job from employ;

在这里插入图片描述

7.2 WHERE 子句查询

  • WHERE 子语句查询关键字,相当于设定,满足筛选的条件,后面加运算符或者关键字
    • 语法格 select 列名称 from 表名 where 列 运算符; 下面运算符可以在where中使用。
操作符作用
=等于
<> 或 !=不等于
>大于
<小于
>=大于等于
<=小于等于
BETWEEN…and…在某个范围内
in在一个区间里
LIKE搜索某种模式
  1. 练习:查询薪资表 ,中大于等于2500的所有员工,并 显示员工姓名

在这里插入图片描述

  1. 练习:查询薪资表,中(薪资和奖金)大于等于2500的所有员工,并 显示员工姓名
	1. 需要结合运算符查完成查询。
		select emp_name ,(salary+bonus)from employ where (salary+bonus)>=2500; 

在这里插入图片描述

  • 思考一个问题01: 查询结果,作者也是3000,为什么没有统计!?什么原因!?
    在这里插入图片描述2.1 . ifnull(列,值) 函数::判断指定列是否包含null值,如果有null值,第二个值就需要用null值替换。

    	1. 解决列中存在null值情况。
    	
    	 select emp_name, (salary+ifnull(bonus,0)) from employ where (salary+ifnull(bonus,0))>=2500;
    

    在这里插入图片描述

  • 思考一个问题02: 上述表头看起来不容易识别,怎么能把上面表头编程“总薪资”?

	2. 解决表头可容易识别。起一个别名,白话意思,起名字 关键字 AS

	 select emp_name AS 姓名, (salary+ifnull(bonus,0)) AS 总薪资 from employ where (salary+ifnull(bonus,0))>=2500;

在这里插入图片描述

  1. 练习:查询薪资表中,员工薪资 在1500到3000之间的人员, 并 显示姓名,和 薪资。。

    • 提示使用运算符between... and...
    	1.使用运算符,显示区间数据。
    		1.1 select emp_name,salary  from employ where salary>=1500 and salary <=3000;
    
    		1.2 select emp_name ,salary from employ where salary between 1500 and 3000;
    

在这里插入图片描述

  1. 练习:查询薪资表中,员工薪资 是1800 ,3000,2500 的人员, 并 显示姓名,和 薪资。

    • 提示: 使用运算符 in
    	1. 查询指定的人员数据,使用and可以么!? 测试结果:不可以,需要使用 or1.1 select emp_name,salary from employ where salary=1800 or salary =3000 or salary=2500;
    		
    		-- 使用where 列名 in(值,值,值); in相当于一个范围
    		1.2 select emp_name,salary from employ where salary in(1800,3000,2500);
    
    

在这里插入图片描述

  1. 练习:查询薪资表中,员工薪资 不是 1800 ,3000,2500 的人员, 并 显示姓名,和 薪资。

    • 提示: 使用运算符 not
    	1.相当于排除这三个选择 。
    		1.1 select emp_name,salary from employ where salary not in(1800,3000,2500);
    
    		1.2 select emp_name,salary from employ where not (salary=1800 or salary =3000 or salary=2500);
    

    在这里插入图片描述

  2. 练习:查询薪资表中,员工薪资 大于4000和薪资小于2000 的人员, 并 显示姓名,和 薪资。

    • 提示: 使用运算符 or
    	1. 这是一个范围取数,但是属于相反的范围。
    		 select emp_name,salary from employ where salary>4000 or salary <2000;
    

    在这里插入图片描述

  3. 练习:查询薪资表中。属于 空值查询

    • 一,没有部门的员工(即,部门为null)
    • 二,部门不是null的员工信息,并显示姓名。
    
      1 .提示null值比较特殊,不能使用 = 号去判断,需要使用  is
    	           select * from employ where department is null; 
    
    		--部门不是null的员工信息,并显示姓名。相当于取反。
    	     	select * from employ where department is not null; 
    	
    

    在这里插入图片描述

7.2.1 课堂测试where案例

  • 测试时间3分钟: 查询薪资表中,薪资大于2999,并且 奖金小于600的员工,显示员工 姓名,薪资,奖金。

7.3 模糊查询

  • LIKE操作符 用于WHERE子语句中搜索列中的指定模式。可以和通配符(%,_)配合使用。
    • 语法结构select 列 | * from 表名 where 列名 like 值;
操作符作用
%表示0个或者多个任意字符
_表示一个任意字符
  1. 练习:查询薪资表中 ,包含 “张” 字的员工,并显示员工姓名。

    • 提示:使用运算符%
	1.思考一下,名字中包含张字,可能以它开头也可能以它结尾。
	
		1.1 select emp_name from employ where emp_name like "%张%";

在这里插入图片描述

  1. 练习:查询薪资表中 ,以 “冯” 字开头的员工,并显示员工姓名。

    • 提示:注意是以 字开始。。

    在这里插入图片描述

  2. 练习:查询薪资表中 ,以 “徐” 字开头的员工,并显示姓名为两个字的员工,显示姓名。

    • 提示:注意是以 开头且 两个字的员工。
    1. 相当于规定了字数的限定 ,使用 运算符 “_”带表一个任意字;
    	select  emp_name from employ where  emp_name like "徐_";
    
    		
    

    在这里插入图片描述

7.4 多行函数查询

  • 多行查询也叫 聚合函数,根据某一列或所有列进行统计。
    常见的多行函数有:
操作符作用
COUNT(列名 | * ) :统计结果集中,某一列或者记录的行数。
MAX (列)统计结果集中,某一列值中的最大值。
MIN (列)统计结果集中,某一列值中的最小值。
SUM (列)统计结果集中,某一列所有值中的和 。
AVG (列)统计结果集中,某一列值得平均值。
  • 提示: 多行语句不能在 where 子句中使用。
  1. 练习:统计薪资表中 ,员工薪资大于等于 3000的个数

    • 提示:中间count (*)可以使用星号代替一样。
    	1.select count(salary) from employ where  salary >=3000;
    	2.select count(*) from employ where  salary >=3000;
    

    在这里插入图片描述

  2. 练习:求薪资表中 ,分别求出 ,薪资最高 和 薪资最低。

    • 提示:可以合并求取。
    	1.薪资最高
    		select max(salary) from employ;
    	2.薪资最低
    		select min(salary) from employ;
    
    	3.合并 起别名 薪资最高 和薪资最低。
    		select max(salary) as 最高薪资, min(salary) as 最低薪资 from employ;
    

    在这里插入图片描述

  3. 练习:统计薪资表中,员工薪资的总额(薪资和奖金)。

    • 提示:思考怎么连接?

    在这里插入图片描述

  4. 练习:统计薪资表中,平均薪资(不包含奖金)。

    • 提示:啥是平均薪资!? 总薪资/总人数
    	--用总薪资/总人数
    	1. select sum(salary)/count(*) from employ;
    	
    	-- 使用avg平均函数
    	2. select avg(salary) from employ;
    

    在这里插入图片描述

7.5 分组查询

  • GROUP BY 语句根据一个或者多个列对结果集进行分组。就像切蛋糕一样,将表中的数据进行分组。
    在分组上可以使用 多行函数COUNT , SUM, AVG ,MAX ,MIN 等函数。
    • 语法格式:select 列 |* from 表名 [where 子语句] group by 列;
  1. 练习:对薪资表中,按照部门对员工进行分组,查看分组效果。

    	-- 相当于对部门进行分组
    	1.select department from employ  group by department;
    	
    	-- 扩展:结合多行函数  相当于统计分组个数。
    	1.1 select count(*) ,department from employ group by department;
    

    在这里插入图片描述

  2. 练习:对薪资表中,按照职位进行分组,并统计每个职位的人数,显示职位和对应人数。

  3. 练习:对薪资表中,按照部门进行分组,求每个部门的最高薪资(不包含奖金),显示部门名称和最高薪资。

    在这里插入图片描述

7.6 排序查询

  • 从表中査询出来的数据可能是无序的,所以使用ORDER BY 子句将结果集,根据指定的列排序后在返回。
    • 语法格式:select 列名 from 表名 order by 列名 [asc|desc]
    • asc(默认) 升序,从低到高。 desc 降序,从高到低。
  1. 练习:对薪资表中

    • 一,员工的薪资从低到高进行排序,显示员工姓名,薪资。提示:相当于升序 asc(默认可以不写)。
    • 二,员工的奖金进行降序,显示员工姓名,薪资。
    --升序, 
    	1. select emp_name,salary from employ order by salary asc;
    --降序
    	2. select emp_name,bonus from employ  order by bonus desc;
    

    在这里插入图片描述

7.7 分页查询

  • 在mysql中,通过 limit 进行分页查询,相当于,网址有上百页,不可能一次都显示,每次显示几条,或几十条。
    • 语法格式:limit(第几页码-1)*每页显示记录数,每页显示记录数。
  1. 练习:查询薪资表中分页记录,分页显示:每页2条记录,返回第一页。
    • 返回第二页。
	--返回第一页 每页显示2条数据 
		select * from employ limit 0,2;  
	-- 返回第二页  limit(2-1)*2 2;
		select * from employ limit 2,2;
	-- 返回第三页 limit(3-1)*2 2;
		select * from employ limit 4,2;
	-- 返回第四页   limit(4-1)*2 2
		select * from  employ limit  6,2;

在这里插入图片描述

7.8 其他函数

  • 函数,如 java中的方法一样,有着特殊的功能,sql中也有其相应的方法。
    -函数很多,就不能全部列举出来,先混一个脸熟。
函数名作用
curdate()获取当前日期, 年月日
curtime()获取当前时间,时分秒
sysdate()获取当前日期+时间 年月日 时分秒
year(date)返回date中的年份
month(date)返回date中的月份
day(date)返回 日
now()返回年月日 时分秒
hour(date)返回date中的小时
minute(date)返回date中的分钟
second(date)返回date中的秒
concat(s1 ,s2)将s1 s2等多个字符串合并成一个字符串
  1. 练习:查询薪资表中 员工在1993与1995之间出生的员工,显示姓名,出生日期。
    • 提示:日期与数值不能相比较。
	-- 思路,将日期转换为数值。year(date)	
		select emp_name,birthday from employ where year(birthday) >=1993 and year(birthday)<=1995;

	提问: 还有没有别的思路? 

在这里插入图片描述

  1. 练习:查询薪资表中 本月过生日的所有员工。

    • 提示:获取系统中本月的月份同表中的所有月份员工进行比较。
    	-- 获取系统中当前月份
    	select * from employ where month(now()) = month(birthday);
    

    在这里插入图片描述

  2. 练习:查询薪资表中 显示姓名和薪资 显示结果为 “xxx(元)”。

    • 提示:字符串拼接 concat();

    在这里插入图片描述