一.SQL的简介:
1.1 简介
数据库(Data Base,简称DB):长期保存在计算机的存储设备上,数据按照一定的规则组织起来,可以被各种用户、应用共享的数据集合。
数据库管理系统(Database Management System, 简称DBMS):指的是一种用来管理和操作数据的大型软件,用于建立、使用、维护数据,对数据库进行统一的管理和控制,以保证数据的完整性和安全性。用户可以通过数据库管理系统访问数据库中的数据。
数据库:存储、维护和管理数据的集合。
数据库管理系统:数据库软件,数据库是通过这些软件进行创建和操作的。
1.2 常见的数据库管理系统
-
Oracle:被认为是目前业界比较成功的关系型数据库的管理系统。Oracle可以运行在Windows、UNIX等主流的操作系统平台,完全支持所有的工业标准,并获得了最高级别的ISO标准安全性认证。
-
MySQL:是一种关系型数据库管理系统。由瑞典MySQL AB公司开发,目前属于Oracle旗下产品。MySQL是目前最流行的关系型数据库管理系统(RDBMS:Relational DBMS)之一。
-
SQLServer:是由微软推出的关系型数据库管理系统。
-
DB2:是IBM的
1.3 关系型数据库、非关系型数据库
-
非关系型数据库:
-
采用了没有特定关系模型来组织数据。
-
NOSQL基于键值对: not only sql
-
代表: HBase, MongoDB,Redis, Oracle NOSQL
-
-
关系型数据库:
-
依据所有存储数据的模型之间的关系建立的数据库。
-
所谓关系模型,指的是“一对一、一对多、多对多”等关系模型
-
1.4 客户端连接数据库
1.4.1 第一种方式:使用mysql自带的命令行客户端
在开始菜单中–>mysql 8.0 command line client–> input password
1.4.2 第二种方式:使用window的命令行界面
1)需要配置mysql的环境变量
将mysql的服务端的bin文件夹,比如C:\Program Files\MySQL\MySQL Server 8.0\bin, 配置到电脑的环境变量里的path变量里
2)打开命令行,输入以下操作:
方式1: 后输入密码
方式2: 直接输入密码
mysql -u root -p'your password'
1.4.3 使用第三方客户端软件
很多公司都提供了可以连接数据库服务器的用户可视化操作界面(客户端),比如,在国内用的最多的就是navicat。
参考下图: 输入连接参数即可
如果是第一次连接,可能会出现以下错误提示:
原因: 8.0版本使用的是caching_sha2_password密码认证策略,而navicat不支持这个策略,所以报错。 此时我们需要使用另外一种方式先登录mysql,降低mysql的密码认证策略,使用navicat可以连接。操作如下:
alter user root@'localhost' identified with mysql_native_password by '123456';
将密码策略改成:mysql_native_password 。
扩展: 远程授权,即 客户端可以不和服务端在一台机器上,也就是在其他的任何机器上使用root账号登录
> use mysql <--切换库 > update user set host='%' where user='root'; > flush privileges;
1.4.4 使用开发工具idea
1.5 MySQL密码操作
当我们进入数据库后,修改mysql密码的方式有以下四种:
第一种:mysql> alter user root@localhost identified by‘新密码'; 第二种:mysql> set password for root@localhost=password('mmforu'); 第三种: mysql> use mysql mysql> update user set authentication_string=password('123456') where user='root' and host='localhost'; mysql> flush privileges; 第四种:C:\Users\Michael> mysqladmin -uroot –p旧密码 password 新密码
如果忘记了密码,怎么办
1. 停止MySQL80服务 2. 找到C:\ProgramData\MySQL\MySQL Server 8.0\目录下的my.ini文件,在77行下面添加skip-grant-tables 3. 启动MySQL80服务 4. 打开命令提示符界面,直接输入mysql指令,进入mysql内 5. 利用上一页的第三种方式,修改密码 6. 然后再修改my.ini文件,去掉skip-grant-tables,重启服务即可
1.6 SQL简介
SQL: Structure Query Language(结构化查询语言),SQL最早是被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准。后来被国际化标准组织(ISO)采纳为关系型数据库语言的国际标准。
各种数据库厂商都支持ISO标准的SQL,类似于普通话。
各个数据库厂商在标准的基础上,定义了若干自己的扩展,类似于方言。
SQL是一种标准化的语言,允许你在数据库上进行操作,如:创建项目、查询内容、更新内容和删除内容等操作。
这些操作:Create、Read、Update、Delete,通常被称为 CRUD操作
二.数据库的基本操作:
2.1 DDL语言的学习
2.1.1介绍
-
用于定义数据库的结构,包括创建(CREATE)、修改(ALTER)和删除(DROP)数据库对象,如表、视图、索引等。
-
比如,“CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR (50), salary DECIMAL (10,2));” 用于创建一个名为 “employees” 的表。
2.1.2 针对于库的DDL操作
本质:就是对DB对应的文件夹进行操作。
1)创建数据库
创建一个库
create database 库名;
创建一个库,同时指定编码集
create database 库名 character set 编码集
查看一下C:/ProgramData/MySQL/MySQL server 8.0/Data下是否多出了几个文件夹
2)查看数据库:
# 查看当前数据库服务器中的所有的数据库
show databases;
# 查看创建数据库mydb1定义的信息
show create database 库名;
3)切库操作(使用哪一个库)
# 切换当前使用的数据库
use 库名;
# 查询当前正在使用的数据库
select database();
4)修改数据库
# 将数据库mydb1的字符集修改为GBK
alter database 库名 character set 新编码集;
5)删除库
drop database 库名;
2.1.3 针对于表的DDL操作
1)表的概念
表:数据在数据库中的存储是以表的形式存在的。一个表中有若干个字段,将数据按照这些字段进行存储。
1、数据表(table),是关系型数据库的基本存储结构。一个关系型数据库是由多个表组成的。
2、数据表是二维的,它由纵向的列和横向的行组成。
3、数据表的行(Row)是横排数据,也被称之为记录(Recond)。
4、数据表的列(Column)是竖排数据,也被称之为字段(Field)。
5、表与表之间也可能存在着关系。
表与表之间的关系
1. 一对一
在实际开发中应用不多。因为一对一的关系,完全可以放到一个表中
2. 一对多
一对多建表原则:在多的一方,创建一个字段,作为外键指向另外那一方的主键。
3. 多对多
多对多的建表原则:需要创建第三张表,在中间表中至少需要有两个字段。这两个字段分别作为外键指向各自一方的主键。
2)数据类型
每一个字段在设计表的时候都要去指定类型:
int:整型
double:浮点型,例如double(5,2):表示最多有5位,其中必须有两位是小数,即最大值是 999.99
char:固定长度的字符串,例如char(5) : 'aa' 占5位
varchar:可变长度的字符串,例如varchar(5):'aa' 占2位
text:字符串类型
blob:字节类型
date:日期类型,格式是:yyyy-MM-dd
time:时间类型,格式为:hh:mm:ss
timestamp:时间戳类型,yyyy-MM-dd hh:mm:ss,会自动赋值
datetime:时间类型,yyyy-MM-dd hh:mm:ss
3)DDL操作表
# DDL操作(表)
# 创建表格
CREATE TABLE t_users(name varchar(50), age INT, gender VARCHAR(10), height INT, weight INT);
# 删除表格
DROP TABLE t_users;
# 查看当前数据库中所有的表
SHOW TABLES;
# 查看创建一个表的信息
SHOW CREATE TABLE t_users;
# 查看一张表的字段信息
DESC t_users;
# 给一张表添加一个字段
ALTER TABLE t_users ADD score DOUBLE(5,2);
# 修改一张表的字段类型
ALTER TABLE t_users MODIFY score INT;
# 修改一张表的字段名
ALTER TABLE t_users CHANGE name uname VARCHAR(50);
# 修改一张表的字符集
ALTER TABLE t_users CHARACTER SET GBK;
# 删除一张表中的字段
ALTER TABLE t_users DROP score;
2.2 DML语言的学习:
2.2.1介绍
主要用于对数据库中的数据进行操作,包括插入(INSERT)、更新(UPDATE)、删除(DELETE)以及查询(SELECT)数据等操作。
例如,使用 “INSERT INTO students (name, age) VALUES (' 张三 ', 20);” 可以向名为 “students” 的表中插入一条学生记录。
2.2.2 增加数据
就是使用insert into 向表中添加记录,可以添加一条,也可以添加多条
添加一条的写法:
写法1: 给表中的所有字段赋值,从左到右,按照类型进行赋值,不能多,不能少
insert into tableName values (val1,val2,val3,...);
写法2: 指定某些字段赋值, 从左到右,按照类型进行赋值
insert into tableName (colName1,colName2,colName3,...) values (val1,val2,val3,...);
添加多条的写法:
写法1: 给表中的所有字段赋值,从左到右,按照类型进行赋值,不能多,不能少
insert into tableName values (val1,val2,val3,...),(val1,val2,val3,...),..,(val1,val2,val3,...);
写法2: 指定某些字段赋值, 从左到右,按照类型进行赋值
insert into tableName (colName1,colName2,colName3,...) values
(val1,val2,val3,...),(val1,val2,val3,...),..,(val1,val2,val3,...);
练习:
表名: teacher
字段名及其类型
id int name varchar(10)
age int gender char(1)
salary double(6,2) subject varchar(10)
hiredate date
表字符集: utf8;
插入数据:
1001 张三 23 男 2000 语文 2010-03-03
1002 李四 24 女 2000 数学 2010-03-04
1003 王五 23 女 2000 英语 2010-03-05
1004 赵六 25 男 2000 生物 2010-03-06
create table teacher(
id int,
name varchar(10),
age int,
gender char(1),
salary double(6,2),
subject varchar(10),
hiredate date
)default character set utf8;
insert into teacher values
(1001, '张三', 23, '男', 2000, '语文', '2010-03-03'),
(1002, '李四', 24, '女', 2000, '数学', '2010-03-04'),
(1003, '王五', 23, '女', 2000, '英语', '2010-03-05'),
(1004, '赵六', 25, '男', 2000, '生物', '2010-03-06');
2.2.3 修改数据
关键字使用update,表示对表中的记录的部分字段进行更新操作。 可以使用where 子句用来进行条件筛选(过滤)
where的用法:
在进行数据的删除、修改、查询的时候,可以使用where对数据进行一个过滤。
= :相等比较(类似于java中的==)
!= <> : 表示不相等
> < >= <= : 大小比较
BETWEEN...AND... : 在xxx和xxx之间
IN(set) :在括号中所有值之间取
IS [not] NULL
AND、OR
==注意==:如果没有使用where子句进行过滤筛选,则是对表中的所有记录进行更新 (==要小心了==)
语法如下:
update tableName set colName1 = value1, colName2 = value2,...... [where conditions];
练习:
1. 修改所有人的工资为2500
update teacher set salary = 2500;
2. 将所有人的工资增加500
update teacher set salary=salary+500;
3. 修改年龄为23岁的教师的工资为5000,入职日期为'2010-10-10'
update teacher set salary = 5000,hiredate = '2010-10-10' where age = 23;
4. 修改名字为张三的教师的入职日期为空,工资为空
update teacher set hiredate = null, salary = null where name = '张三';
5. 将在'2010-10-10'之前入职的员工工资上调1000元
update teacher set salary = salary+1000 where hiredate < '2010-10-10';
6. 将教语文、英语、生物的老师的年龄改为null
update teacher set age = null where subject = '语文' or subject = '英语' or subject = '生物';
update teacher set age = null where subject in ('语文','英语','生物');
7. 将没有课的老师工资下调1000元。
update teacher set salary = salary -1000 where subject is null;
2.2.3 删除数据
关键字是delete. 指的是删除表中的符合条件的所有记录(行数据)
注意: 如果不指定条件,表中的所有行都被删除了。
语法:
delete from tableName [where conditions];
练习:
1. 删除工资是4000,年龄不是null的教师信息
delete from teacher where salary = 4000 and age is not null;
2. 删除剩下的所有记录
delete from teacher;
delete 与 truncate的区别
delete删除表中的数据,表结构还在;删除的数据可以恢复。
truncate是直接将表DROP掉,然后再按照原来的结构重新创建一张表。数据不可恢复。
truncate删除效率比delete高。
truncate table tableName
三 DQL的基本操作
DQL: SQL分类的一种,是Data Query language的简称。 用于对表中的记录进行查询操作。可以查询表中的所有记录,也可以通过条件筛选过滤,查询表中的部门记录,还可以对表中的数据进行排序操作,分页操作等。当然,还可以进行多表关联查询(因为需求的数据,可能不只是在一张表中,而是在多张表中都有)
3.1 基本查询语言的结构:
DQL的整体语法,是可以分成多个子句的。 最简单的子句是select…子句和from…子句。 这两个子句必须存在。
from…子句: 用于指定要查询的表
select…子句:选择表中的哪些字段进行查询/显示
语法:
select...from....
含有其他子句的语法如下:
select [distinct].....from....[where....][group by .....][having.....]
[order by.....][limit.....]
3.2 查询语句的执行顺序
了解mysql的查询语句的执行顺序,会对编写sql语句有一定的帮助。
1. 先执行from子句:基于表进行查询操作
2. 再执行where子句:进行条件筛选或者条件过滤
3. 再执行group by子句:对剩下的数据进行分组查询。
4. 再执行having子句:分组后,再次条件筛选或过滤
5. 然后执行select子句:目的是选择业务需求的字段进行显示
6. 再执行order by子句:对选择后的字段进行排序
7. 最后执行limit子句:进行分页查询,或者是查询前n条记录
3.3 别名的用法
有的时候,表名或者列名过长,或者在查询过程中涉及到的 虚拟表 或者是虚拟字段,我们就需要给他们起一个别称,也就是别名。
别名的命名:
-
虽然可以使用汉字,但是尽量还是要使用英文字母。
-
表别名,不能使用单双引号
-
列别名,可以加单双引号,也可不加。
reg:
select name 姓名,
(year(now())-year(birth)) 年龄
from test1 t;
reg:
select e.empno 员工编号,e.ename,e.job,mgr '领导编号',hiredate,sal,comm,deptno from emp e;
3.4 where子句的使用
如果想要对表中的数据进行条件筛选或过滤,需要使用where子句。where关键字后,用于书写筛选的条件。
条件如下:
1)关系表达式: >,>=,<,<=,=,!=,<>
2)多条件连接符: and,or, [not] between ..and..
3)集合操作: [not] in (set),
>all(set), <all(set), >any(set), <any(set)
注意:mysql不支持简单的集合查询操作,但是子查询里支持,是针对于all和any集合操作来说的。
4)模糊查询: like
_:占位符,表示匹配任意一个字符
%:表示匹配任意N个字符,大于等于0.
例1:查询工资大于1600的所有员工的编号,姓名,职位,工资
select empno,ename,job,sal from emp where sal>1600
例2:查询工资大于1600并且小于2500的所有员工的编号,姓名,职位,工资
select empno,ename,job,sal from emp where sal>1600 and sal<2500;
select empno,ename,job,sal from emp where sal between 1600 and 2500;
例3:查询10,20号部门的所有员工的信息。
select * from emp where deptno not in (10,20);
例4:查询不是10号部门中姓名第二个字符是m的员工信息。
select * from emp where deptno<>10 and ename like '_m%';
3.5 group by子句
1.需求:
有的时候,需要分组统计一些,最大值,最小值,平均值,和,总数之类的这样的信息,此时需要分组查询。
2.聚合函数:也叫分组函数
- count(): 统计每组满足的记录总数
- max():统计每组满足条件的最大值
- min():统计每组满足条件的最小值
- avg():统计每组满足条件的平均值
- sum():统计每组满足条件的总和。
注意:
- 所有的聚合函数,都会忽略字段为null的那条记录。
- count(*),不会忽略null值所在的行记录,即通常用于统计总行数。
3. 在分组查询时,只有分组字段可以写在select子句中,其他不是分组的字段,不应该写在select子句中,无意义多字段进行分组:
A B 字段组合情况下:组的数组最多为 m*n1 a 1001
1 b 1002
2 b
2 c
3 a
3 b
1 a 1003
1 b 1004
4. 聚合函数处理null值,可以使用ifnull(colName, value).
- ifnull(colName,value): 如果colName对应的值不为空,就使用本身的值,如果为null,使用value.
# 案例:查询每个部门中的每种职位的最高工资,最低工资,工资之和
select deptno,job ,max(sal),min(sal),sum(sal) from emp group by job,deptno;
# 案例:查询所有员工的平均工资,平均奖金 使用ifnull函数
select avg(ifnull(sal,0)),avg(ifnull(comm,0)) from emp;
3.6 having子句
当使用了group by子句后,如果想再次对数据进行筛选和过滤,就需要使用having子句了。
注意: having子句 只能跟在groub by子句后面
# 查询部门平均工资大于1000部门号,平均工资。
select deptno,avg(ifnull(sal,0)),max(sal) avg_sal from emp group by deptno having avg(ifnull(sal,0)) >1000;
# 查询每种职位的最高工资大于1500的职位、最高工资,平均工资,平均奖金。
select job,max(sal),avg(ifnull(sal,0)),avg(ifnull(comm,0)) from emp group by job having max(sal)>1500;
3.7 order by子句
用于查询排序的,通常放置在一个查询语句的最后部分。
语法: order by colName [asc|desc] [,colName [asc|desc]]
asc:升序, 默认情况就是升序
desc:降序
reg:查询员工表中的所有员工信息,按照工资降序排序
select * from emp order by sal desc;
reg:查询员工表中的所有员工信息,按照工资降序排序,如果相同,再按照奖金升序排序
select * from emp order by sal desc,comm asc;
3.8 去重查询
有的时候,我们需要查询表中有那些不同的数据。不需要重复出现,此时可以使用distinct关键字进行去重处理
注意:distinct关键字只能放在select关键字之后。
比如: 查询有那些部门号
3.9 分页查询
- 需求:当一页的数据量过大时,我们可以进行分页显示操作。注意:分页查询时,一般都要进行先排序,再分页。
- 关键字limit.
- 语法:limit m[,n];
m 表示从第几条记录开始查询,
n表示要查询的记录数目。
注意:mysql的记录index从0开始。
一个参数的含义:limit n
表示从0开始查询n条记录
案例1:每页5条记录,查询第二页的数据。
select empno,ename from emp limit 5,5;
案例2:查询第page页的数据, 每页大小为pageSize。limit的写法如下:
limit (page-1)*pageSize , pageSize