目录
1.数据库的介绍
1.1数据库概述
1.1.1数据的存储方式
Java 中创建对象: Student s = new Student(1, "张三") 存在内存中。
学习了 Java IO 流:把数据保存到文件中。
1.1.2什么是数据库
1.存储数据的仓库
2.本质上是一个文件系统,还是以文件的方式存在服务器的电脑上。
3.所有的关系型数据库都可以使用通用的SQL语句进行管理DBMS DataBase Management System。
1.2常见的数据库
1.MySQL:开源免费的数据库,小型的数据库,已经被Oracle收购了,MySQL6.x版本也开始收费,后来Sun公司收购了MySQL,而Sun公司又被Oracle收购。
2.Oracle:收费的大型数据库,Oracle公司的测评。
3.DB2:IBM公司的数据库产品,收费的。常应用在银行系统中。
4.SQL Server:Microsoft公司收费的中型的数据库。C#,.net等语言常使用。
5.SQLite:嵌入式的小型数据库,应用在手机端,如:Android。
1.2.1为什么选择MySQL
1.免费
2.功能强大
1.3MySQL目录结构
1.4数据库管理系统
数据库管理系统(DataBase Management System,DBMS):指一种操作和管理数据库的大型软件,用于建立、使用和维护数据库,对数据库进行统一管理和控制,以保证数据库的安全性和完整性。用户通过数据库管理 系统访问数据库中表内的数据
1.5数据库管理系统,数据库和表的关系
数据库管理程序(DBMS)可以管理多个数据库,一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据,一般会在数据库创建多个表,以保存程序中实体 User 的数据。
数据库管理系统、数据库和表的关系如图所示:
总结:
1.一个数据库服务器包含多个库
2.一个数据库包含多张表
3.一张表包含多条记录
2.SQL的 概念
2.1什么是SQL
Structured Query Language 结构化查询语言
2.2SQL的作用
1.是一种所有关系型数据库的查询规范,不同的数据库都支持。
2.通用的数据库操作语言,可以用在不同的数据库中。
3.不同的数据库 SQL 语句有一些区别,例如:
2.3SQL语句分类
- Data Definition Language (DDL 数据定义语言) 如:建库,建表
- Data Manipulation Language(DML 数据操纵语言),如:对表中的记录操作增删改
- Data Query Language(DQL 数据查询语言),如:对表中的查询操作
- Data Control Language(DCL 数据控制语言),如:对用户权限的设置
2.4MySQL的语法
- 每条语句以分号结尾,如果在 SQLyog 中不是必须加的。
- SQL 中不区分大小写,关键字中认为大写和小写是一样的
- 3 种注释:
3.DDL操作数据库
3.1创建数据库
3.1.1创建数据库的几种方式
创建数据库
CREATE DATABASE 数据库名;
判断数据库是否已经存在,不存在则创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;
创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
3.1.2具体操作:
-- 直接创建数据库
db1 create database db1;
-- 判断是否存在,如果不存在则创建数据库
db2 create database if not exists db2;
-- 创建数据库并指定字符集为 gbk
create database db3 default character set gbk;
3.2查看数据库
-- 查看所有的数据库
show databases;
-- 查看某个数据库的定义信息
show create database db3;
show create database db1;
3.3修改数据库
3.3.1修改数据库默认的字符集
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;;
3.3.2具体操作
将 db3 数据库的字符集改成 utf8
alter database db3 character set utf8;
3.4删除数据库
3.4.1删除数据库的语法
DROP DATABASE 数据库名;
3.4.2具体操作:
drop database db2;
3.5使用数据库
3.5.1查看正在使用的数据库
SELECT DATABASE(); 使用的一个 mysql 中的全局函数
3.5.2使用/切换数据库
USE 数据库名;
3.5.3具体操作:
-- 查看正在使用的数据库
select database();
-- 改变要使用的数据库
use db4;
4.DDL操作表结构
前提先使用某个数据库
4.1创建表
4.1.1创建表的格式
CREATE TABLE 表 名 (
字段名 1 字段类型 1,
字段名 2 字段类型 2
);
4.1.2关键字说明:
4.2MySQL数据库类型
4.2.1常使用的数据类型如下:
4.2.2详细的数据类型如下:
4.2.3具体操作:
创建student表包含id,name,birthday字段
create table student (
id int, -- 整 数
name varchar(20), -- 字符串
birthday date -- 生日,最后没有逗号
);
4.3查看表
4.3.1查看某个数据库中的所有表
SHOW TABLES;
4.3.2查看表的结构
DESC 表名;
4.3.3查看创建表的SQL语句
SHOW CREATE TABLE 表名;
4.3.4具体操作
查看 day21 数据库中的所有表
use day21;
show tables;
查看student表的结构
desc student;
执行结果:
查看student的创建表的SQL语句
show create table student;
执行结果:
` 存在的目的是为了避免关键字的冲突
CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
4.4快速创建一个表结构相同的表
4.4.1语法
CREATE TABLE 新表名 LIKE 旧表名;
4.4.2具体操作
创建 s1 表,s1 表结构和 student 表结构相同
-- 创建一个 s1 的表与 student 结构相同
create table s1 like student;
desc s1;
4.5删除表
4.5.1直接删除表
DROP TABLE 表名;
4.5.2判断表是否存在,如果存在则删除表
DROP TABLE IF EXISTS 表名;
4.5.3具体操作
-- 直接删除表 s1 表
drop table s1;
-- 判断表是否存在并删除 s1 表
drop table if exists `create`;
4.6修改表结构
4.6.1添加表列ADD
ALTER TABLE 表名 ADD 列名 类型;
为学生表添加一个新的字段remark,类型为varchar(20)
alter table student add remark varchar(20);
执行结果:
4.6.2修改列类型MODIFY
ALTER TABLE 表名 MODIFY 列名 新的类型;
将student表中的remark字段的数据类型改成varchar(100)
alter table student modify remark varchar(100);
执行结果:
4.6.3修改列名CHANGE
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
将 student 表中的 remark 字段名改成 intro,类型 varchar(30)
alter table student change remark intro varchar(30);
执行结果:
4.6.4删除列DROP
ALTER TABLE 表名 DROP 列名;
删除student表中的字段intro
alter table student drop intro;
执行结果:
4.6.5修改表名
REBANE TABLE 表名 TO 新表名;
将学生表student改名成student2;
rename table student to student2;
4.6.6修改字符集
ALTER TABLE 表名 CHARACTER SET 字符集;
alter table student2 character set gbk;
5.DML操作表中的数据
用于对表中的记录进行增删改操作
5.1插入记录
5.1.1插入全部字段
所有字段名都写出来
INSERT INTO 表名 (字段名 1, 字段名 2, 字段名 3…) VALUES (值 1, 值 2, 值 3);
编写字段名
INSERT INTO 表名 VALUES (值 1, 值 2, 值 3…);
5.1.2插入部分数据
INSERT INTO 表名 (字段名 1, 字段名 2, ...) VALUES (值 1, 值 2, ...);
注意:没有添加数据的字段会使用NULL
5.1.3具体操作
插入所有的列,向学生表中
insert into student (id,name,age,sex) values (1, '孙悟空', 20, '男');
insert into student (id,name,age,sex) values (2, '孙悟天', 16, '男');
执行效果:
向表中插入所有字段
-- 插入所有列
insert into student values (3, '孙悟饭', 18, '男', '龟仙人洞中');
-- 如果只插入部分列,必须写列名
insert into student values (3, '孙悟饭', 18, '男');
select * from student;
执行效果:
5.2更新表记录
5.2.1不带条件修改数据
UPDATE 表名 SET 字段名=值; -- 修改所有的行
5.2.2带条件修改数据
UPDATE 表名 SET 字段名=值 WHERE 字段名=值;
5.2.3具体操作:
-- 不带条件修改数据,将所有的性别改成女
update student set sex = '女';
-- 带条件修改数据,将 id 号为 2 的学生性别改成男
update student set sex='男' where id=2;
-- 一次修改多个列,把 id 为 3 的学生,年龄改成 26 岁,address 改成北京
update student set age=26, address='北京' where id=3;
5.3删除表记录
5.3.1不带条件删除数据
DELETE FROM 表名;
5.3.2带条件删除数据
DELETE FROM 表名 WHERE 字段名=值;
5.3.3使用truncate删除表中所有记录
TRUNCATE TABLE 表名;
5.3.4truncate和delete的区别:
truncate相当于删除表的结构,再创建一张空表。
5.3.5具体操作:
-- 带条件删除数据,删除 id 为 1 的记录
delete from student where id=1;
-- 不带条件删除数据,删除表中的所有数据
delete from student;
6.DQL查询表中的数据
查询不会对数据库中的数据进行修改,只是一种显示数据的方式
6.1简单查询
6.1.1查询表所有行和列的数据
使用*表示所有列
SELECT * FROM 表名;
查询所有的学生:
select * from student;
6.1.2查询指定列
查询指定列的数据,多个列之间以逗号分隔
SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名;
查询 student 表中的 name 和 age 列
select name,age from student;
6.2指定列的别名进行查询
使用别名的好出:显示的时候使用新的名字,并不修改表的结构
6.2.2语法:
对列指定别名
SELECT 字段名1 AS 别名,字段名2 AS 别名... FROM 表名;
对列和表同时指定别名
SELECT 字段名1 AS 别名,字段名2 AS 别名... FROM 表名 AS表别名;
6.2.3具体操作:
-- 使用别名
select name as 姓名,age as 年龄 from student;
-- 表使用别名
select st.name as 姓名,age as 年龄 from student as st
6.3清除重复值
6.3.1查询指定列并且结果不出现重复数据
SELECT DISTINCT 字段名 FROM 表名;
6.3.2具体操作:
查询学生来自那些地方
-- 查询学生来自于哪些地方
select address from student;
-- 去掉重复的记录
select distinct address from student;
6.4查询结果参与运算
6.4.1某列数据和固定值运算
SELECT 列名1 + 固定值 FROM 表名;
6.4.2某列数据和其他列数据参与运算
SELECT 列名1 + 列名2 FROM 表名;
注意:参与运算的必须是数值类型
6.4.3需求
准备数据:添加数学,英语成绩列,给每条记录添加对应的数学和英语成绩,查询的时候将数学和英语的成绩相加。
6.4.4实现:
select * from student;
-- 给所有的数学加 5 分
select math+5 from student;
-- 查询 math + english 的和
select * from student;
select *,(math+english) as 总成绩 from student;
-- as 可以省略
select *,(math+english) 总成绩 from student;
6.5条件查询
6.5.1为什么要条件查询
如果没有查询条件,则每次查询所有的行。实际应用中,一般要指定查询的条件。对记录进行过滤。
6.5.2条件查询的语法
准备数据
创建一个学生表,包含如下列:
CREATE TABLE student3 (
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math int, -- 数学
english int -- 英语
);
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','
杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港
',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);
运算符
具体操作:
-- 查询 math 分数大于 80 分的学生
select * from student3 where math>80;
-- 查询 english 分数小于或等于 80 分的学生
select * from student3 where english <=80;
-- 查询 age 等于 20 岁的学生
select * from student3 where age = 20;
-- 查询 age 不等于 20 岁的学生,注:不等于有两种写法
select * from student3 where age <> 20; select * from student3 where age != 20;
逻辑运算符
具体操作:
-- 查询 age 大于 35 且性别为男的学生(两个条件同时满足)
select * from student3 where age>35 and sex='男';
-- 查询 age 大于 35 或性别为男的学生(两个条件其中一个满足)
select * from student3 where age>35 or sex='男';
-- 查询 id 是 1 或 3 或 5 的学生
select * from student3 where id=1 or id=3 or id=5;
in关键字
具体操作:
-- 查询 id 是 1 或 3 或 5 的学生
select * from student3 where id in(1,3,5);
-- 查询 id 不是 1 或 3 或 5 的学生
select * from student3 where id not in(1,3,5);
范围查询
查询 english 成绩大于等于 75,且小于等于 90 的学生
select * from student3 where english between 75 and 90;
like 关键字
MySQL 通配符
-- 查询姓马的学生
select * from student3 where name like "马%";
-- 查询姓名中包含'德'字的学生
select * from student3 where name like '%德%';
-- 查询姓马,且姓名有两个字的学生
select * from student3 where name like '马_';
7.1排序
7.1.1单列排序
什么是单列排序:
只按某一个字段进行排序,单列排序。
-- 查询所有数据,使用年龄降序排序
select * from student order by age desc;
7.1.2组合排序
什么是组合排序?
同时对多个字段进行排序,如果第 1 个字段相等,则按第 2 个字段排序,依次类推。组合排序的语法:
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];
-- 查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩升序排序
select * from student order by age desc, math asc;
7.2聚合函数
之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询, 它是对一列的值进行计算,然后返回一个结果值。聚合函数会忽略空值 NULL。
7.2.1五个聚合函数
7.2.2语法
SELECT 聚合函数(列名) FROM 表名;
-- 查询学生总数
select count(id) as 总人数 from student;
select count(*) as 总人数 from student;
我们发现对于 NULL 的记录不会统计,建议如果统计个数则不要使用有可能为 null 的列,但如果需要把 NULL也统计进去呢?
IFNULL(列名,默认值)
如果列名不为空,返回这列的值。如果为NULL,则返回默认值。
-- 查询 id 字段,如果为 null,则使用 0 代替
select ifnull(id,0) from student;
我们可以利用 IFNULL()函数,如果记录为 NULL,给个默认值,这样统计的数据就不会遗漏。
select count(ifnull(id,0)) from student;
另外四个聚合函数的用法如下:
-- 查询年龄大于 20 的总数
select count(*) from student where age>20;
-- 查询数学成绩总分
select sum(math) 总分 from student;
-- 查询数学成绩平均分
select avg(math) 平均分 from student;
-- 查询数学成绩最高分
select max(math) 最高分 from student;
-- 查询数学成绩最低分
select min(math) 最低分 from student;
7.3分组
GROUP BY怎么分组的?
将分组字段结果中相同内容作为一组,如按性别将学生分成 2 组。
GROUP BY 将分组字段结果中相同内容作为一组,并且返回每组的第一条数据,所以单独分组没什么用处。分组的目的就是为了统计,一般分组会跟聚合函数一起使用。
-- 按性别进行分组,求男Th和女Th数学的平均分
select sex, avg(math) from student3 group by sex;
效果如下:
注意:当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的
select sex, count(id) from student3 group by sex;
查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
-- 对分组查询的结果再进行过滤
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;
7.3.1having与where的区别
7.3.2面试题:有如下订单表
Orders 表数据如下所示,执行如下 SQL 语句,运行结果是?
运行有误,group by 后面不能出现 where,使用 having
7.4limit语句
7.4.1准备数据
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果ft',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);
7.4.2limit的作用
LIMIT是限制的意思,使用LIMIT的作用计算限制查询记录的条数。
7.4.3LIMIT语法格式:
-- 查询学生表中数据,从第 3 条开始显示,显示 6 条。
select * from student3 limit 2,6;
7.4.4LIMIT的使用场景
分页:比如我们登录京东,淘宝,返回的商品信息可能有几万条,不是一次全部显示出来。是一页显示固定的条数。 假设我们每页显示 5 条记录的方式来分页。
-- 如果第一个参数是 0 可以省略写:
select * from student3 limit 5;
-- 最后如果不够 5 条,有多少显示多少
select * from student3 limit 10,5;