Loading

MySQL基础（一）

MySQL

JavaEE：企业级java开发 Web
前端（页面：展示，数据！）

后台（连接点：连接数据库JDBC，连接前端【控制视图跳转，和前端传递数据】）

数据库（存数据库，Text，Excel，Word）

只会写代码，学好数据库，基本混饭吃
操作系统，数据结构与算法！ 当一个不错的程序员！
高散数据，数学电路，体系结构，编译原理+实战经验，高级程序员～优秀程序员～

为什么学习数据库

  1. 岗位需求
  2. 现在的世界，大数据时代，得数据者得天下
  3. 被迫需求：存数据
  4. 数据库是所有软件体系中最核心的存在

什么是数据库

数据库（DB，DataBase）

  • 概念：数据仓库，软件，安装在操作系统（window，linux，mac）之上，可以存储大量的数据。500万
  • 作用：存数据，管理数据

数据库分类

关系型数据库 （SQL）

  • MySQL、Oracle，Sql Server，DB2，SQLite
  • 通过表和表之间，行和列之间的关系进行数据库的存储，学员信息表，考勤表，......

非关系型数据库 （NoSQL）Not Only SQL

  • Redis，MongDB
  • 非关系型数据库，对象存储，通过对象的自身的属性来决定

DBMS(数据库管理系统)

  • 数据库的管理软件，科学有效地管理我们的数据。维护和获取数据；
  • MySQL，数据库管理系统

MySQL简介

MySQL是一个关系型数据库管理系统

前世：瑞典MySQL AB公司
今生：属于Oracle旗下产品

MySQL是最好RDBMS（RelationalDatabaseManagement System，关系型数据库管理系统）应用软件之一。

开源的数据库软件

体积小、速度快、总体拥有成本低，招人成本比较低，所有人必须会

总小型网站、或者大型网站，集群～

官网：https://www.mysql.com/

常用MySQL版本

  • 5.7
  • 8.0

安装MySQL

  1. 解压
  2. 把这个包放到自己的电脑环境目录下
  3. 配置环境变量
  4. 新建mysql配置文件ini
[mysql]
# 目录一定要换成自己的
basedir=[目录]\
datadir=[目录]\data\
port=3306
skip-grant-tables
  1. 启动管理员模式下的CMD，运行所有的命令

连接数据库

命令行连接

mysql -uroot -p123456  -- 连接数据库

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; -- 修改用户密码

flush privileges; -- 刷新权限

-----------------------------------------------------------

-- 所有的语句都用分号结尾：结尾

show databases; -- 查看所有数据库

use school; -- 切换数据库 use 数据库名

show tables; -- 查看所有数据库中所有的表

describe student; -- 显示数据库中所有表信息

create database westos; -- 创建一个数据库

exit; 退出数据库连接

注释

-- 单行注释（SQL的本来的注释）

/* （SQL的多行注释）
hello
asds
dasdas
*/

操作数据库

操作数据库

创建数据库

mysql关键字不区分大小写

CREATE DATABASE westos;

-- 一般会加一个判断
CREATE DATABASE IF NOT EXISTS westos;

使用数据库

USE `school`;

删除数据库

DROP DATABASE IF EXISTS `hello`;

查看数据库

SHOW DATABASES; -- 查看所有数据库

数据库的列类型

数值

  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 中等大小的数据 3个字节
  • int 标准整数 4个字节
  • bigint 较大的数据 8个字节
  • float 浮点数 4个字节
  • double 浮点数 8个字节
  • decimal 字符串形式的浮点数 金融计算的时候一般使用decimal

字符串

  • char 字符串固定大小 0～255
  • varchar 可变字符串 0～65535 常用的变量
  • tinytext 微型文本 2^8 - 1
  • text 文本串 2^16-1 保存大文本

时间日期

  • date YYYY-MM-DD 日期
  • time HH:mm:ss 时间格式
  • datetime YYYY-MM-DD HH:mm:ss 常用的时间格式
  • timestamp 时间戳，1970.1.1 到现在的秒数 较为常用
  • year 年份

null

  • 没有值，未知
  • 注意，不要使用NULL进行运算个，结果一定为NULL

数据库的字段属性（重点）

Unsigned

  • 无符号的整数
  • 声明了该列不能声明为负数

zerofill

  • 0填充的
  • 不足的位数，使用0来填充，int(3)

自增

  • 通常理解为自增，自动在上一条记录的基础上 +1 （默认）
  • 通常用来设计唯一的主键 index，必须是整数类型
  • 可以自定义设计主键自增的起始值和步长

非空 NOT NULL

  • 假设设置为 not null，如果不给它赋值，就会报错
  • null，如果不填写值，默认就是null

默认

  • 设置默认的值
  • sex，默认为男，如果不填值，默认为男

创建数据库表

-- 目标：创建一个student表（列，字段） 使用SQL创建
-- 学号int 登陆密码varchar(20) 姓名，性别varchar(2),出生日期(datetime)，家庭住址，email

-- 注意点，使用英文（），表的名称 和字段尽量用 ``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用 单引号括起来
-- 所有语句后面加英文逗号
-- PRIMARY KEY 主键，一般一个表只有一个主键！
CREATE TABLE IF NOT EXISTS `student` (
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
		`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
		`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
		`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
		`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
		`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
		`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
		PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式

CREATE TABLE [IF NOT EXIST] `表名`(
    `字段名1` 列类型 [属性] [索引] [注释],
    `字段名2` 列类型 [属性] [索引] [注释],
    ......
    `字段名n` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]

SHOW语句

SHOW CREATE DATABASE `school`; -- 查看创建数据库的语句
SHOW CREATE TABLE `student`; -- 查看创建表的语句

显示表结构

DESC `student`;

数据表的类型

-- 关于数据库引擎
/*
INNO默认使用
MYISAM早些年使用
*/
MYISAM INNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大，约为2倍

常规使用操作：

  • MYISAM 节约空间，速度较快
  • INNODB 安全性高，事务的处理，多表用户的操作

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹对应一个数据库

本质还是文件的存储

MySQL引擎在物理文件上的区别

  • InnoDB 在数据库表中只有一个 *.frm文件，以及上级目录下的ibdata1文件
  • MYISAM 对应文件
    • *.frm 表结构的定义文件
    • *.MYD 数据文件（data）
    • *.MYI 索引文件（index）

设置数据库表的字符集编码

CHARSET=utf8

不设置的话，会是mysql默认的字符集编码（不支持中文！）

MySQL的默认编码时Latin1，不支持中文

在my.ini中配置默认的编码

character-set-server=utf8

修改删除表

修改

-- 修改表名
ALTER TABLE `student` RENAME AS `teacher`;

-- 增加表字段
ALTER TABLE `teacher` ADD `age` INT(11);

-- 修改表的字段 （重命名、修改约束）
ALTER TABLE `teacher` MODIFY `age` VARCHAR(11);
ALTER TABLE `teacher` CHANGE `age` `age1` INT(1); -- change 用来重命名

-- 删除表的字段
ALTER TABLE `teacher` DROP `age1`;

删除

-- 删除表
DROP TABLE IF EXISTS `teacher`;

所有的创建和删除操作尽量加上判断，以免报错

MySQL数据管理

外键（了解即可）

-- 创建grade表
CREATE TABLE IF NOT EXISTS `grade`(
		`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
		`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
		PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 创建student表
-- 学生表的 干扰的ID字段，要去引用年级表的 gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） references 引用
CREATE TABLE IF NOT EXISTS `student` (
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
		`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
		`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
		`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
		`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
		`gradeid` INT(10) DEFAULT 1 NOT NULL COMMENT '学生的年级',
		`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
		`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
		PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 创建表的时候没有外键关系
ALTER TABLE `student` 
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

-- ALERT TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 哪个表(字段)

删除所有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

以上的操作都是物理外键，数据库级别的外键，我们不建议使用！（避免数据库过多造成困扰）

最佳实践

  • 数据库就是存粹的表，只用来存数据，只有行（数据）和列（字段）
  • 我们想使用多张表的数据，想使用外键（程序去实现）

DML语言（全部记住）

DML语音：数据操作语音

  • insert
  • update
  • delete

增加

insert

-- 插入语句（添加）
-- INSERT INTO 表名(字段1,字段2,字段3) VALUES(值1,值2,值3)
INSERT INTO `grade`(`gradename`) VALUES('大四');

-- 由于主键自增我们可以省略（如果不写表的字段，他们会一一匹配）
INSERT INTO `grade` VALUES('大四');

-- 一般写插入语句，我们一定要数据和字段一一对应！

-- 插入多个字段
INSERT INTO `grade`(`gradename`) 
VALUES('大二'),('大一');

INSERT INTO `student`(`name`) VALUES("张三");

INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES("李四","5tgb^YHN","男"),("王五","1qaz@WSX","男");

注意事项

  1. 字段和字段之间使用英文逗号隔开
  2. 字段是可以省略的，但是后面的值必须一一对应

修改

update 修改 (条件) set 原来的值=新值

-- 修改
update `student` SET `name` = "冰渣渣" WHERE id = 1;

-- 语法 ：
-- UPDATE 表名 SET COLUMN_name = VALUE WHERE 条件[]

-- 修改多个属性，逗号隔开
update `student` SET `name` = "逗比",`email`= "475377482@qq.com" WHERE id = 2;

条件：where 子句，运算符 id等于某个值，在某个区间内修改......

删除

delete

-- 删除
-- DELETE FROM 表名 WHERE 条件

-- 删除数据（避免这样写，会全部删除）
DELETE FROM `student`

-- 删除指定数据
DELETE FROM `student` WHERE `id` = 1;

TRUNCATE

作用：完全清空一个数据库表，表的结构和索引约束不会变

-- 清空student表
TRUNCATE `student`;

delete 和 TRUNCATE区别

  • 相同点 都能删除数据，都不会删除表结构
  • 不同点
    • TRUNCATE 重新设置自增列 计数器会归零
    • TRUNCATE 不会影响事务
-- 测试delete 和 TRUNCATE的区别
CREATE TABLE `test`(
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`coll` VARCHAR(20) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3');

DELETE FROM `test`; -- 不会影响自增

TRUNCATE `test`; -- 自增会归零

了解即可：DELETE删除的问题，重启数据库现象

  • InnoDB 自增列会从1开始（存在内存当中的，断电即失）
  • MyISAM 继续从上一个自增开始（存在文件中的，不会丢失）

DQL查询数据（最重点）

DQL

（Data Query Language: 数据查询预语言）

  • 所有的查询操作都用它 select
  • 简单的查询，复杂的查询都能做
  • 数据库中最核心的语言，最重要的语言
  • 使用频率最高的语句

准备表及数据

--创建表
CREATE DATABASE IF NOT EXISTS `library`;

--查看当前表有哪些
SHOW TABLES;

--查看表字段详情
DESC `bookinfo`;
DESC `readerinfo`;
DESC `borrowinfo`;

--删除表
--删除方式有两种：有关联关系、无关联关系的表
DROP TABLE `bookinfo`;
DROP TABLE `bookcategory`;
DROP TABLE `readerinfo`;
DROP TABLE `borrowinfo`;

--多个表一起删除
DROP TABLE `borrowinfo`,`readerinfo`;

--创建图书类别表bookcategory
CREATE TABLE `bookcategory`(
 `category_id` INT PRIMARY KEY AUTO_INCREMENT,
 `category` VARCHAR(20) NOT NULL UNIQUE,
 `parent_id` INT NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--创建图书信息表bookinfo
CREATE TABLE `bookinfo`(
 `book_id` INT PRIMARY KEY,
 `book_category_id` INT,
 `book_name` VARCHAR(100) NOT NULL UNIQUE,
 `author` VARCHAR(100) NOT NULL,
 `price` FLOAT(5,2) NOT NULL,
 `press` VARCHAR(40) DEFAULT '机械工业出版社',
 `pubdate` DATE NOT NULL,
 `store` INT NOT NULL,
 CONSTRAINT `fk_bcid` FOREIGN KEY(book_category_id) REFERENCES bookcategory(category_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--创建读者信息表readerinfo
CREATE TABLE `readerinfo`(
 `card_id` CHAR(18) PRIMARY KEY,
 `name` VARCHAR(20) NOT NULL,
 `sex` ENUM('男','女','保密') DEFAULT '保密',
 `age` TINYINT NOT NULL,
 `tel` CHAR(11) NOT NULL,
 `balance` DECIMAL(7,3) DEFAULT 200
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--创建借阅信息表borrowinfo
CREATE TABLE `borrowinfo`(
 `book_id` INT,
 `card_id` CHAR(18),
 `borrow_date` DATE NOT NULL,
 `return_date` DATE NOT NULL,
 `status` CHAR(1) NOT NULL,
 PRIMARY KEY(`book_id`,`card_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--查看已创建表
SHOW TABLES;

-- 插入数据
insert into `bookcategory`(`category`,`parent_id`)values('计算机',0),('医学',0),('文学',0),('编程语言',1),('数据库',1),('儿科学',2),('国内文学',3),('国外文学',3);
/*图书信息表*/
insert into `bookinfo`(`book_id`,`book_category_id`,`book_name`,`author`,`price`,`press`,`pubdate`,`store`)
values
(20150201,4, 'Java编程思想', '(美)埃克尔', 79.8, default, '2007-04-01', 5),
(20150202,5, 'PHP和MySQL Web开发', 'Luke Welling等',95, default, '2009-04-01', 2),
(20150301,4, 'Spring源码深度解析', '郝佳',69, '人民邮电出版社', '2013-09-01', 3),
(20160801,6, '中医儿科学', '汪受传', 136, '人民卫生出版社', '2011-04-01', 1),
(20170401,6, '小儿推拿秘笈', '李德修', 24.5, '人民卫生出版社', '2011-04-01',4),
(20150802,4, 'Python编程思想', '(英)斯特林', 79.8, default, '2005-04-01', 6),
(20150203,5, 'Drango Web开发', 'Duk 等',95, default, '2010-04-01', 3),
(20150304,4, 'SpringCloud源码深度解析', '袁媛',69, '人民工程出版社', '2014-09-01', 4),
(20160805,6, '西医儿科学', '赵传', 136, '人民卫生出版社', '2012-04-01', 2),
(20170406,6, '小儿健康宝典', '孔莉', 24.5, '人民卫生出版社', '2012-04-01',5),
(20170201,4, 'C++编程思想', '(美德)隆美尔', 69.8, default, '2017-04-01', 5),
(20170702,5, 'PHP和Oracle Web开发', '威廉姆斯',85, default, '2012-04-01', 5),
(20170301,4, 'HTML源码深度解析', '邵佳一',59, '人民邮电出版社', '2015-09-01', 6),
(20170801,6, '中医幼儿科学', '王向平', 126, '人民卫生出版社', '2013-04-01', 4),
(20190401,6, '小儿推拿手册', '胡凯', 14.5, '人民卫生出版社', '2014-04-01',7),
(20170202,4, 'Python基础入门', '(日)山本一木', 69.8, default, '2005-07-01', 9),
(20170203,5, 'Vue Web开发', '新街',85, default, '2010-05-01', 5),
(20170304,4, 'Spring深度解析', '贾斯丁',59, '人民工程出版社', '2014-10-01', 6),
(20190805,6, '西医小儿科学', '艾薇儿', 126, '人民卫生出版社', '2012-06-01', 4),
(20190406,6, '小儿健康手册', '泡芙dog', 24, '人民卫生出版社', '2012-05-01',7),
(20200101,7, '中国文学史', '空的都', 54, '中国教育出版社', '2007-01-01',27),
(20200103,7, '菜根谭', '洪应明', 44, '中国友谊出版社', '2022-04-01',203),
(20200104,7, '太平广记', '李昉等', 49, '中华书局', '2020-04-01',801),
(20200105,7, '文献中的百年党史', '李颖', 49, '学林出版社', '2020-11-01',1002),
(20200106,7, '李太白全集', '李白', 29, '中华书局', '2011-03-01',702),
(20200107,7, '李银河：性学入门', '李银河', 49, '上海三联书店', '2021-01-01',12),
(20200109,7, '极限正义：刑案之道', '高艳东', 38, '北京大学出版社', '2021-04-01',32),
(20200110,7, '监狱琐记', '王学泰', 57, '生活·读书·新知三联书店', '2021-03-01',73),
(20200111,7, '汉武时代漫谈', '草说木言', 40, '海天出版社', '2022-04-01',103),
(20200201,8, '生活与命运', '[俄] 瓦西里·格罗斯曼', 62, '四川人民出版社', '2020-03-01',9),
(20200202,8, '堂吉诃德', '[西班牙] 塞万提斯 ', 42, '人民文学出版社', '2019-05-01',27),
(20200203,8, '卡拉马佐夫兄弟', '[俄] 陀思妥耶夫斯基', 38, '人民文学出版社', '2020-11-01',7),
(20200204,8, '一生', '[法] 莫泊桑', 42, '人民文学出版社', '2020-01-01',57),
(20200205,8, '傲慢与偏见', '[英] 简·奥斯丁', 32, '人民文学出版社', '2020-03-01',2037),
(20200206,8, '巴黎圣母院', '[法] 雨果', 32, '人民文学出版社', '2019-05-01',3037),
(20200207,8, '永别了武器', '[美] 欧内斯特·海明威', 62, '人民文学出版社', '2020-04-01',631),
(20200208,8, '瓦尔登湖', '[美] 亨利·戴维·梭罗', 63, '人民文学出版社', '2019-05-01',32),
(20200209,8, '帝国游戏', '(智利)罗贝托·波拉尼奥', 37, '上海人民出版社', '2020-05-10',332),
(20200210,8, '神秘岛', '凡尔纳', 67, '西安交通大学出版社', '2017-08-01',3),
(20200301,4, 'Python编程快速上手 让繁琐工作自动化', '[美] 阿尔·斯维加特', 67, '人民邮电出版社', '2021-03-01',29),
(20200302,4, 'Python编程练习与解答', '[加拿大] 本·斯蒂芬森', 47, '清华大学出版社', '2021-01-01',32),
(20200303,4, 'Python编程入门与算法进阶', '中国电子学会', 47, '人民邮电出版社', '2022-04-01',106),
(20200304,4, 'Spring微服务实战', '[美] 约翰·卡内尔', 54, '人民邮电出版社', '2022-05-01',86),
(20200305,4, 'Spring Boot实战派', '龙中华', 34, '电子工业出版社', '2020-01-01',6),
(20200306,5, '高性能MySQL', 'Baron Schwartz，Peter Zaitsev，Vadim Tkachenko', 34, '电子工业出版社', '2013-04-01',2),
(20200307,5, 'MySQL基础教程', '[日] 西泽梦路', 24, '人民邮电出版社', '2020-01-01',22),
(20200401,6, '张素芳小儿推拿技法图谱', '周奕琼，刘晓峰', 54, '江苏凤凰科学技术出版社', '2018-07-01',22);

/*读者信息表*/
insert into `readerinfo`(`card_id`,`name`,`sex`,`age`,`tel`,`balance`)
values
(20210199801010500,'张飞','女',18,'18217054300',300),
(20210199801010501,'李月','女',19,'18217054301',200),
(20210199801010502,'王鹏','男',20,'18217054302',300),
(20210199801010503,'刘鑫','男',21,'18217054303',400),
(20210199801010504,'杨磊','男',22,'18217054304',500),
(20210199801010505,'李飞','女',18,'18217054305',300),
(20210199801010506,'李月气','女',29,'18217054306',200),
(20210199801010507,'王鹏站','男',30,'18217054307',300),
(20210199801010508,'刘鑫饿','男',41,'18217054308',400),
(20210199801010509,'杨磊从','男',42,'18217054309',500),
(20210199801010510,'张飞册','女',38,'18217054310',300),
(20210199801010511,'刘月','女',29,'18217054100',200),
(20210199801010512,'钱鹏','男',40,'18217054200',300),
(20210199801010513,'牛鑫','男',51,'18217054330',400),
(20210199801010514,'陈磊','男',32,'18217054400',500),
(20210199801010515,'胡飞','女',48,'18217054500',200),
(20210199801010516,'孔月','女',19,'18217054600',200),
(20210199801010517,'孙鹏','男',10,'18217054700',200),
(20210199801010518,'徐鑫','男',31,'18217054800',200),
(20210199801010519,'潘磊','男',22,'18217054900',200),
(20210199801010520,'庞飞','女',48,'18217054010',100),
(20210199801010521,'施月','女',39,'18217051300',100),
(20210199801010522,'金鹏','男',30,'18217052300',100),
(20210199801010523,'管鑫','男',41,'18217053300',100),
(20210199801010524,'藏磊','男',42,'18217054340',100),
(20210199801010525,'李飞','女',18,'18217055300',300),
(20210199801010526,'王月','女',19,'18217056300',200),
(20210199801010527,'张鹏','男',20,'18217057300',300),
(20210199801010528,'刘子','男',21,'18217058300',400),
(20210199801010529,'陈磊','男',22,'18217059300',500),
(20210199801010530,'徐飞','女',18,'18217050380',300),
(20210199801010531,'钱月气','女',29,'18211054300',200),
(20210199801010532,'金鹏站','男',30,'18212054300',300),
(20210199801010533,'卢鑫饿','男',41,'18213054300',400),
(20210199801010534,'潘磊从','男',42,'18214054300',500),
(20210199801010535,'周飞册','女',38,'18215554350',300),
(20210199801010536,'梁月','女',29,'18217064300',200),
(20210199801010537,'林鹏','男',40,'18217074300',300),
(20210199801010538,'季鑫','男',51,'18217084300',400),
(20210199801010539,'邹磊','男',32,'18217094300',500),
(20210199801010540,'许飞','女',48,'18217004900',200),
(20210199801010541,'孙月','女',19,'18217154300',200),
(20210199801010542,'孔鹏','男',10,'18217254300',200),
(20210199801010543,'陆鑫','男',31,'18217354300',200),
(20210199801010544,'黎磊','男',22,'18217454300',200),
(20210199801010545,'冯飞','女',48,'18217554300',100),
(20210199801010546,'葛月','女',39,'18217654300',100),
(20210199801010547,'胡鹏','男',30,'18217754300',100),
(20210199801010548,'江鑫','男',41,'18217854300',100),
(20210199801010549,'黄磊','男',42,'18217954300',100),
(20210199801010550,'张的飞','女',18,'18217005300',300),
(20210199801010551,'李吃月','女',19,'18211054300',200),
(20210199801010552,'王充鹏','男',20,'18212054300',300),
(20210199801010553,'刘强鑫','男',21,'18213054300',400),
(20210199801010554,'杨票磊','男',22,'18214054300',500),
(20210199801010555,'李龙飞','女',18,'18215054300',300),
(20210199801010556,'李学气','女',29,'18216054300',200),
(20210199801010557,'王额站','男',30,'18217774300',300),
(20210199801010558,'刘三饿','男',41,'18218054300',400),
(20210199801010559,'杨额从','男',42,'18219054300',500),
(20210199801010560,'张票册','女',38,'18210884300',300),
(20210199801010561,'刘月三','女',29,'18227054300',200),
(20210199801010562,'钱鹏婆','男',40,'18237054300',300),
(20210199801010563,'牛鑫城','男',51,'18247054300',400),
(20210199801010564,'陈磊钱','男',32,'18257054300',500),
(20210199801010565,'胡飞车','女',48,'18267054300',200),
(20210199801010566,'孔月才','女',19,'18277054300',200),
(20210199801010567,'孙鹏中','男',10,'18287054300',200),
(20210199801010568,'徐鑫人','男',31,'18297054300',200),
(20210199801010569,'潘磊林','男',22,'18299054300',200),
(20210199801010570,'庞飞新','女',48,'18117054300',100),
(20210199801010571,'施月饿','女',39,'18222054300',100),
(20210199801010572,'金鹏牌','男',30,'18317054300',100),
(20210199801010573,'管鑫客','男',41,'18417054300',100),
(20210199801010574,'藏磊排','男',42,'18517054300',100),
(20210199801010575,'李次飞','女',18,'18617054300',300),
(20210199801010576,'王月如','女',19,'18717054300',200),
(20210199801010577,'张鹏粗','男',20,'18817054300',300),
(20210199801010578,'刘子我','男',21,'18917054300',400),
(20210199801010579,'陈磊类','男',22,'18017054300',500),
(20210199801010580,'徐长飞','女',18,'18222054300',300),
(20210199801010581,'钱次气','女',29,'18333054300',200),
(20210199801010582,'金苦站','男',30,'18444054300',300),
(20210199801010583,'卢裁饿','男',41,'18555054300',400),
(20210199801010584,'潘磊从','男',42,'18666054300',500),
(20210199801010585,'周葱册','女',38,'18777054300',300),
(20210199801010586,'梁囱月','女',29,'18888054300',200),
(20210199801010587,'林鹏都','男',40,'18999054300',300),
(20210199801010588,'季春','男',51,'18210004300',400),
(20210199801010589,'邹兰','男',32,'18909054300',500),
(20210199801010590,'许飞琮','女',48,'18808054300',200),
(20210199801010591,'孙思月','女',19,'18707054300',200),
(20210199801010592,'孔鹏从','男',10,'18606054300',200),
(20210199801010593,'陆鑫看','男',31,'18505054300',200),
(20210199801010594,'黎磊犯','男',22,'18404054300',200),
(20210199801010595,'冯龙飞','女',48,'18303054300',100),
(20210199801010596,'葛粗','女',39,'18220254300',100),
(20210199801010597,'胡鄂','男',30,'18210154300',100),
(20210199801010598,'江搭','男',41,'18201054300',100),
(20210199801010599,'裘磊','男',42,'18202054300',100);

-- borrowinfo 数据
INSERT INTO `borrowinfo`(`book_id`, `card_id`, `borrow_date`, `return_date`, `status`) 
VALUES
(20150201,20210199801010500,'2012-07-06','2021-10-21',1),
(20150202,20210199801010501,'2011-07-01','2021-10-01',1),
(20150301,20210199801010502,'2010-07-02','2021-10-11',1),
(20160801,20210199801010503,'2009-07-03','2021-10-31',1),
(20170401,20210199801010504,'2009-07-04','2021-10-01',1),
(20150802,20210199801010505,'2009-07-05','2021-11-11',1),
(20150203,20210199801010506,'2009-07-06','2021-09-21',1),
(20150304,20210199801010507,'2009-07-07','2021-07-11',1),
(20160805,20210199801010508,'2016-07-08','2017-06-14',1),
(20170406,20210199801010509,'2019-07-09','2021-10-13',1),
(20170201,20210199801010510,'2018-07-10','2019-06-12',1),
(20170702,20210199801010511,'2019-07-11','2019-08-11',1),
(20170301,20210199801010512,'2017-07-12','2019-03-11',1),
(20170801,20210199801010513,'2019-07-13','2019-10-08',1),
(20190401,20210199801010514,'2019-07-14','2021-10-07',0),
(20170202,20210199801010515,'2019-07-15','2021-10-06',1),
(20170203,20210199801010516,'2019-07-16','2021-10-05',1),
(20170304,20210199801010517,'2019-07-17','2021-10-04',1),
(20190805,20210199801010518,'2019-07-18','2020-10-03',1),
(20190406,20210199801010519,'2019-07-19','2020-10-02',1),
(20200101,20210199801010520,'2019-07-20','2021-11-30',1),
(20200103,20210199801010521,'2019-07-21','2021-10-30',1),
(20200104,20210199801010522,'2019-07-22','2021-10-29',1),
(20200105,20210199801010523,'2019-07-23','2021-10-28',1),
(20200106,20210199801010524,'2019-07-24','2021-10-27',1),
(20200107,20210199801010525,'2019-07-25','2021-10-26',1),
(20200109,20210199801010526,'2019-07-26','2021-10-25',1),
(20200110,20210199801010527,'2019-07-27','2021-10-24',1),
(20200111,20210199801010528,'2019-07-28','2021-10-23',1),
(20200201,20210199801010529,'2019-07-29','2021-10-22',1),
(20200202,20210199801010530,'2019-07-30','2021-10-21',1),
(20200203,20210199801010531,'2019-08-06','2021-10-20',1),
(20200204,20210199801010532,'2019-09-06','2021-10-09',1),
(20200205,20210199801010533,'2019-01-06','2021-10-08',0),
(20200206,20210199801010534,'2019-02-06','2021-10-07',1),
(20200207,20210199801010535,'2019-03-06','2021-10-06',1),
(20200208,20210199801010536,'2019-04-06','2021-10-05',1),
(20200209,20210199801010537,'2019-05-06','2021-10-04',1),
(20200210,20210199801010538,'2019-06-06','2021-10-03',1),
(20200301,20210199801010539,'2019-07-06','2021-10-02',1),
(20200302,20210199801010540,'2001-07-06','2021-10-01',0),
(20200303,20210199801010541,'2002-07-06','2021-10-31',1),
(20200304,20210199801010542,'2003-07-06','2021-10-21',1),
(20200305,20210199801010543,'2004-07-06','2021-10-16',1),
(20200306,20210199801010544,'2005-07-06','2021-10-11',1),
(20200307,20210199801010545,'2006-07-06','2021-10-17',1),
(20200401,20210199801010546,'2007-07-06','2021-10-18',1),
(20150201,20210199801010547,'2008-07-06','2021-10-19',0),
(20150202,20210199801010548,'2009-07-06','2021-09-11',1),
(20150301,20210199801010549,'2010-07-06','2021-11-11',1),
(20160801,20210199801010550,'2011-07-06','2021-10-11',1),
(20160805,20210199801010551,'2012-07-06','2020-10-11',1);

指定查询字段

-- 查询全部字段 SELECT * FROM 表
SELECT * FROM `bookcategory`;
SELECT * FROM `bookinfo`;
SELECT * FROM `readerinfo`;

-- 指定查询字段
SELECT `card_id` FROM `readerinfo`;
SELECT `card_id`,`name`,`balance` FROM `readerinfo`;

-- 别名，给结果起一个名字 AS,可以给字段、表起别名
SELECT `card_id` AS "身份id",`name` AS "姓名",`balance` AS "余额" FROM `readerinfo` AS s;

-- 函数 Concat (a,b)
SELECT CONCAT("书名：",`book_name`) AS "新书名" FROM `bookinfo`;

语法：==SELECT 字段,... FROM 表==

去重 distinct

-- 查询用户余额都有哪些 , 重复数据多，需要去重
SELECT DISTINCT`balance` AS "余额" FROM `readerinfo`;

作用：去除select查询出来的结果中重复的数据，只显示一条

数据库的列 （表达式）

-- 查看系统的版本 (函数)
SELECT VERSION();

-- 用来计算 （表达式）
SELECT 100*3-1 AS '计算结果';

-- 查询自增的步长 （变量）
SELECT @@auto_increment_increment;

-- 图书库存 +1 查看
SELECT `book_name` AS '图书名称', `store`+1 AS '库存' FROM `bookinfo`;

数据库中的表达式：文本值、列、Null、函数、计算表达式、系统变量

select 表达式 from 表

where条件子句

作用：检索数据中符合条件的值

搜索结果由一个或多个结果表达式表示

逻辑运算符

运算符 语法 描述
and && a and b a&&b 逻辑与，两个为真，结果为真
or || a or b a||b 逻辑或，其中一个为真，则结果为真
not ! not a !a 逻辑非 
-- 查询性别为男，余额大于等于300的用户姓名，性别，余额
SELECT `name` AS "性别",`sex` AS "性别",`balance` AS "余额" 
FROM `readerinfo` 
WHERE `sex` = "男" AND `balance`>=300;

-- 模糊查询（区间）
-- 查询价格在50到100之间的图书，作者，价钱
SELECT `book_name` AS "书名",`author` AS "作者",`price` AS "价钱"
FROM `bookinfo`
WHERE `price` BETWEEN 50 AND 100;

-- !=   not
-- 查询出版社不是“机械工业出版社”的所有书籍名称，出版社
SELECT `book_name` AS "书名",`author` AS "作者",`press` AS "出版社"
FROM `bookinfo`
WHERE `press` != "机械工业出版社";

模糊查询

比较运算符

运算符 语法 描述
IS NULL a is null 如果操作符为NULL，结果为真
IS NOT NULL a is not null 如果操作符为not null，结果为真
BETWEEN a between b and c 若a在b和c之间，则结果为真
LIKE a like b SQL匹配，如果a匹配b，则结果为真
IN a in(a1,a2,a3...) 假设a在a1，或者a2...其中的某一个值中，结果为真 
-- 查询2017年的图书
-- like结合 %(代表0到任意个字符串) 
SELECT `book_id` AS "书号",`book_name` AS "书名",`author` AS "作者"
FROM `bookinfo`
WHERE `book_id` LIKE "2017%";

-- 查询图书中带有“web”的图书
SELECT `book_name` AS "书名",`author` AS "作者"
FROM `bookinfo`
WHERE `book_name` LIKE "%Web%";

-- in (具体的一个或者多个值)
SELECT `book_name` AS "书名",`author` AS "作者"
FROM `bookinfo`
WHERE `book_name` IN ("Java编程思想","Python基础入门","Spring深度解析");

-- NULL  not NULL
-- 查询地址为空的学生 NULL
SELECT `name` AS "姓名",`sex` AS "性别",`tel` AS "电话"
FROM `readerinfo`
WHERE `name`= "" OR `tel` IS NULL;

联表查询

JOIN 对比

-- 联表查询

/*
1、分析需求，分析查询的字段来自哪些表，（连接查询）
2、确定使用哪种连接查询？ 7种
3、确定交叉点（这两个表中哪个数据是相同的）

判断的条件：bookcategory表的category_id = bookinfo表的book_category_id
*/

-- 查询图书名称，作者，出版社以及图书类别
SELECT bc.category AS "图书类型",bi.book_name AS "图书名称",bi.author AS "作者",bi.press AS "出版社"
FROM `bookinfo` AS bi
	INNER JOIN `bookcategory` AS bc
	ON bi.book_category_id = bc.category_id;

-- RIGHT JOIN
SELECT bc.category AS "图书类型",bi.book_name AS "图书名称",bi.author AS "作者",bi.press AS "出版社"
FROM `bookinfo` AS bi
	RIGHT JOIN `bookcategory` AS bc
	ON bi.book_category_id = bc.category_id;

-- LEFT JOIN
SELECT bc.category AS "图书类型",bi.book_name AS "图书名称",bi.author AS "作者",bi.press AS "出版社"
FROM `bookinfo` AS bi
	LEFT JOIN `bookcategory` AS bc
	ON bi.book_category_id = bc.category_id;

操作 描述
INNER JOIN 如果一个表中至少有一个匹配，就返回行
LEFT JOIN 会从左表中返回值，即使右表中没有匹配
RIGHT JOIN 会从右表中返回值，即使左表中没有匹配

案例

-- 案例：查询价钱大于50的图书名称，作者，出版社以及图书类别
SELECT bc.category AS "图书类型",bi.book_name AS "图书名称",bi.author AS "作者",bi.press AS "出版社",`price` AS "价钱"
FROM `bookinfo` AS bi
	LEFT JOIN `bookcategory` AS bc
	ON bi.book_category_id = bc.category_id
WHERE bi.price > 50;


-- 思路
/*
1、我要查询哪些数据 select ...
2、从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件
3、假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加

-- FROM a LEFT JOIN b  以a表为基准查
-- FROM a RIGHT JOIN b  以b表为基准查
*/

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表

案例

根据category表查询复制信息

category_id category parent_id
1 计算机 0
2 医学 0
3 编程语言 1
4 数据库 1
5 儿科学 2 
-- 查询父子信息
SELECT bc.category AS "图书子类",bc2.category AS "图书父类"
FROM `bookcategory` AS bc
	INNER JOIN `bookcategory` bc2
	ON bc.category_id = bc2.parent_id;

联表查询

案例

-- borrowinfo表插入数据
INSERT INTO `borrowinfo`(`book_id`, `card_id`, `borrow_date`, `return_date`, `status`) VALUES (20150201, '210210199901011111', '2022-09-22', '2022-09-30', '1');
INSERT INTO `borrowinfo`(`book_id`, `card_id`, `borrow_date`, `return_date`, `status`) VALUES (20150202, '210210199901012222', '2022-09-21', '2023-02-24', '1');
INSERT INTO `borrowinfo`(`book_id`, `card_id`, `borrow_date`, `return_date`, `status`) VALUES (20170301, '210220199901015555', '2022-09-01', '2022-09-20', '0');
INSERT INTO `borrowinfo`(`book_id`, `card_id`, `borrow_date`, `return_date`, `status`) VALUES (20170406, '210250199901011111', '2022-08-02', '2022-10-28', '1');

-- 查询正在借阅的书籍，书名，借阅人
SELECT bi.book_name AS "书名",ri.`name` AS "借阅人"
FROM `borrowinfo` AS br
	INNER JOIN `bookinfo` AS bi
	ON bi.book_id = br.book_id
	INNER JOIN `readerinfo` AS ri
	ON br.card_id = ri.card_id
WHERE br.`status` = 1;

分页和排序

分页

为什么要分页

  • 为什么要分页？
  • 缓解数据库压力，给人的体验更好，瀑布流
-- 分页
-- 读者信息，每页显示5条数据
-- 语法：limit 当前页，页面的大小
-- 第一页 LIMIT 0,5  (1-1) * 5
-- 第二页 LIMIT 5,5  (2-1) * 5
-- 第三页 LIMIT 10,5  (3-1) * 5
-- 第n页 LIMIT 0,5  (n-1) * pagesize,pagesize 
-- pagesize代表页面大小，n代表当前页
-- (n-1) * pagesize: 起始页
-- n 当前页
-- 数据总数/页面大小 = 当前页
SELECT *
FROM `readerinfo`
LIMIT 1,5;

语法：limit(查询起始下标，pagesize)

排序

-- 排序：升序ASC , 降序：DESC
-- ORDER BY 通过哪个字段排序，怎么排
-- 查询读者信息的结果根据 年龄 降序 排序
SELECT *
FROM `readerinfo`
ORDER BY `age` DESC;

-- 查询借阅信息，书名、借阅人、借阅时间，根据借阅时间倒序排列
SELECT bi.book_name AS "书名",ri.`name` AS "借阅人",br.borrow_date AS "借阅时间"
FROM `borrowinfo` AS br
	INNER JOIN `bookinfo` AS bi
	ON bi.book_id = br.book_id
	INNER JOIN `readerinfo` AS ri
	ON br.card_id = ri.card_id
ORDER BY br.borrow_date DESC;


案例

-- 查询已借书籍，最近借阅的前十本书（书名，作者，借阅人，库存，借阅时间）
SELECT bi.book_name AS "书名",bi.author AS "作者",ri.`name` AS "借阅人",bi.store AS "剩余库存",br.borrow_date AS "借阅时间"
FROM `borrowinfo` AS br
	INNER JOIN `bookinfo`AS bi
	ON br.book_id = bi.book_id
	INNER JOIN `readerinfo` AS ri
	ON br.card_id = ri.card_id
WHERE br.`status`=1
ORDER BY br.borrow_date DESC
LIMIT 10;

子查询

where (这个值是计算出来的)

本质： 在where语句中嵌套一个子查询语句

-- 查询王鹏的借书情况（书名，作者，借阅人姓名，电话）
-- 方式一：使用联表查询
SELECT bi.book_name,bi.author,ri.`name`,ri.tel
FROM `borrowinfo` bri
INNER JOIN `readerinfo` ri
ON bri.card_id = ri.card_id
INNER JOIN `bookinfo` bi
ON bri.book_id = bi.book_id
WHERE ri.`name` = "王鹏";

-- 方式二 子查询
SELECT bi.book_name,bi.author,ri.`name`,ri.tel
FROM `borrowinfo` bri
INNER JOIN `readerinfo` ri
ON bri.card_id = ri.card_id
INNER JOIN `bookinfo` bi
ON bri.book_id = bi.book_id
WHERE bri.card_id = (
	SELECT card_id 
	FROM `readerinfo`
	WHERE `name` = "王鹏"
);

分组和过滤

-- 查询不同类别书籍的评价书价，最高书架，最低书价，平均价钱大于50
-- 核心（根据不同类型书籍分组）
SELECT bc.category AS "书籍类别",AVG(bi.price) AS "平均价钱",MAX(bi.price) AS "最高价",MIN(bi.price) AS "最低价"
FROM `bookinfo` AS bi
INNER JOIN `bookcategory` AS bc
ON bi.book_category_id=bc.category_id
GROUP BY bi.book_category_id;  -- 通过平均字段来分组
HAVING bi.price > 50

posted @ 2022-10-17 22:54  Binzichen  阅读(47)  评论(0)  编辑  收藏  举报