java第一阶段（day14）MySQL

原创已于 2022-09-03 10:15:44 修改 · 944 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #java #数据库

于 2022-03-08 19:04:43 首次发布

第一阶段专栏收录该内容

17 篇文章

订阅专栏

本文深入探讨了MySQL数据库管理系统，包括其安装、基本指令、数据类型、约束、DML操作、DQL查询以及数据库设计。通过实例展示了如何创建数据库、表，以及进行数据的插入、更新和删除操作。此外，还讲解了如何使用各种条件查询、分组查询和子查询，以及数据库的备份与恢复。最后，提到了数据库的存储引擎和数据持久化的重要性。

1.DBMS

为什么学习io？
  数据持久化。  io不太好用。逻辑操作比较复杂。
  
换另外一种技术实现数据的持久化?
     将数据存储到库里面----->数据库  Database
     
听说过的数据库:  Mysql  oracle  sqlserver sqllite
               Mogodb  redis Elastic
              
存储不同数据类型的数据。

数据库的分类:
   1.从存储位置分析:
      基于磁盘: Mysql Oracle Sqlserver  肯定可以保证数据持久化。IO 性能低
      基于缓存: Redis Mysql  数据不能够完全的保证持久化  快 
      
   2.从关系划分:
      关系型数据库: Mysql Oracle Sqlserver    字段与字段表直接都是有关系的
      非关系型数据库: Redis  NOSQL  NOT ONLY SQL 
           key:value

为什么要学习数据库?
    数据持久化.

DBMS:
   database management system  数据库管理系统。
   Mysql: 就是一个软件， 是一个数据库管理系统软件。 RDBMS. 

数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。用户通过DBMS访问数据库中的数据，数据库管理员也通过DBMS进行数据库的维护工作

2. MySQL

2.1 安装mysql的服务

下载安装Mysql的服务端程序。MySQL :: Download MySQL Installer

mysql服务端程序的端口号：  3306
mysql的协议:  TCP/IP   客户端连接服务器  保证服务器是开启的。

mysql的用户名都是:  root
mysql的密码都是自定义: root

mysql的核心配置文件:  C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

max_connections=151
tomcat: 200

2.2 客户端连接服务器

客户端要连接哪一台主机的mysql的服务器?
    ip  3306 用户名  密码

配置mysql的环境变量。

所有的指令+sql语句都是不区分大小写。每一行的结束必须带 ;

-- 连接mysql指令
-- mysql -hip地址  -u用户名 -p
-- C:\Users\JAVASM>mysql -h 127.0.0.1 -uroot -p
-- C:\Users\JAVASM>mysql -h localhost -uroot -p

-- 如果是连接本机的服务  -hip可以省略的
C:\Users\JAVASM>mysql -uroot -p

2.3 mysql基本指令

mysql软件里面都有啥?
  有很多数据库:  db
  db的组成部分:
      1. table 表
          1.1 字段(属性) 列
          1.2 数据类型
          1.3 约束
          
      2. 查询
      3. 函数
      5. 索引
      6. 存储过程
      7. 触发器
      8. 备份

-- mysql> show databases;  查看所有的数据库

mysql> select database(); -- 查看当前正在使用的数据库
+------------+
| database() |
+------------+
| NULL       |
+------------+

mysql> use mysql;-- 选择使用的数据库
Database changed

mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+

mysql> show tables; -- 查看指定库里面所有的表

mysql> desc user; -- 查看指定表的表结构

mysql> select * from user; -- 查看表里面所有的记录  *:通配符 表里面所有的字段

mysql> select host, User from user; -- 指定列查询 查询部分字段

3. SQL

使用sql操作数据库的表。 SQL： structured query language 结构查询语言

-- 1. DDL data definitation language 数据定义语言
       创建库 创建表  删除库  删除表  create /drop/ truncate
-- 2. DML 数据操作语言(更新操作)
      insert  update  delete

-- 3. DQL 数据查询语言  select

-- 4. DCL 数据控制语言   事务   权限

3.1 DDL

1. 操作库

mysql> create database mydb ; -- 创建数据库  数据库名称一旦定义  无法修改
Query OK, 1 row affected (0.00 sec)

mysql> drop database mydb; -- 删除数据库(连着库里面的表都全部删除  数据无法回滚的)


mysql> show create database mydb;-- 展示创建数据库的相关的信息
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| mydb     | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+

2.操作表

-- 创建表格(数据 列(字段)  数据类型  约束)
create table 表名(
   列名1 数据类型  [约束],
   列名2 数据类型  [约束],
   .....
   列名n 数据类型  [约束]

);


-- 需求: 创建表 存储所有的用户信息
create table userinfo(
  id int,
  username varchar(20),
  gender char(1),
  phone char(11),
  age tinyint(2) unsigned,
  balance decimal(15,4),
  password varchar(70),
  birthday date,
  create_time datetime,
  update_time datetime,
  last_login_time datetime  
);

映射关系:
   代码操作数据。
   一张表就是一个类。
   表里面的字段就是类里面属性，
   表里面字段的数据类型 就是类里面属性的数据类型。

操作表结构: alter(了解)

mysql> drop table a;  -- 删除表 (数据无法回滚)
Query OK, 0 rows affected (0.14 sec)

mysql> alter table userinfo add address varchar(30);
-- 新增新的列

mysql> alter table userinfo drop address;  -- 删除指定的列

-- 可以修改列名  也可以修改类的数据类型
mysql> alter table userinfo change username username varchar(25);
mysql> alter table userinfo change username name varchar(25);

-- 修改列的数据类型
mysql> alter table userinfo modify name varchar(20);

-- 修改指定的表的名称
mysql> alter table userinfo rename tb_userinfo;
Query OK, 0 rows affected (0.10 sec)

3.2. 数据类型

java数据类型: 引用+基本基本: 4类8种

整数类型 byte short int long
小数类型 float double
布尔类型 boolean
字符类型 char

1 整数类型

-- 1. tinyint(n) 类似byte -128-127  人的年龄  unsigned  0-255
      tinyint(1) 类似boolean   0false   1true
      
-- 2. int(n)  类似int  id
-- 3. bigint(n) 类似long  id  时间的毫秒数

n: 限定列宽的宽度。 
-- unsigned 无符号数字
-- zerofill  以0填充  int(5)  00100  1000000

-- 格式化类: NumberFormat   0 #

2 小数类型

-- float(m,n)  学生的成绩  商品单价
-- double(m,n)

m: 规定小数的总位数
n:限定小数点后面有几位数据
double(5,2)  111.23

-- 与钱相关的一些数据 
-- BigDecimal
-- decimal(m,n) 定点数  优先

3 字符类型

-- char(m)  定长  性别char(1) 手机号码  身份证号码
-- varchar(m) 可变长度  用户名 简介
m: 限定存储字符个数。
char(5)  'abc' 存储的时候还是存储5个字符'abc__'  查询的时候 先trim 再展示数据
varchar(5) 'abc' 存储就是3个字符 
    
-- text
-- longtext  

String

4 日期类型

-- 年月日  date
-- 年月日 时分秒 datetime  timestamp
-- 时分秒 time
-- 年 year(2/4) 2022 22

3.3 DML

insert delete update

1. insert

对于insert 有几行记录受影响? >=1

-- 1. 对所有的列都新增新的数据
insert into 表名 values (数据1,....数据n); -- 有几个字段 就新增几个数据即可


mysql> insert into tb_userinfo values (
    -> 1,'jackMa','m','111111',40,6666666,
    -> '1234','1977-01-01','2020-01-01 12:00:00',null,null
    -> );

mysql> select * from tb_userinfo;
+------+--------+--------+--------+------+--------------+----------+------------+---------------------+-------------+-----------------+
| id   | name   | gender | phone  | age  | balance      | password | birthday   | create_time         | update_time | last_login_time |
+------+--------+--------+--------+------+--------------+----------+------------+---------------------+-------------+-----------------+
|    1 | jackMa | m      | 111111 |   40 | 6666666.0000 | 1234     | 1977-01-01 | 2020-01-01 12:00:00 | NULL        | NULL            |
+------+--------+--------+--------+------+--------------+----------+------------+---------------------+-------------+-----------------+

-- 在很多情况下  不需要对所有的列赋值  指定列新增记录
-- 2. insert into 表名 （列名1,...列名n） values (数据1,....数据n);

mysql> insert into tb_userinfo (id,name,password,create_time) values (2,'ponyMa','1234',now());
Query OK, 1 row affected (0.05 sec)


mysql> insert into tb_userinfo (id,name,create_time) values (3,'马云',now());
ERROR 1366 (HY000): Incorrect string value: '\xC2\xED\xD4\xC6' for column 'name' at row 1

-- 不能存储汉字数据?
mysql> show create database mydb;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| mydb     | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter database mydb CHARACTER SET UTF8;  -- 修改指定数据库的编码格式
Query OK, 1 row affected (0.00 sec)

mysql> show create database mydb;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| mydb     | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+


-- 修改完数据库的编码格式  还是会有可能出现不能存储中文问题
-- 有可能是因为这张表  他是在修改编码格式之前创建好的。
-- 对于cmd这个客户端而言  没法修改  只能先删除 再创建新的一张表

-- 局部修改指定的一个数据库的编码格式  。

mysql> show variables like '%character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+

-- 全局修改mysql的服务器的编码格式:
-- my.ini
default-character-set=utf8
character-set-server=utf8

-- 修改了核心配置文件 重新启动mysql的服务。

-- 一次新增多条记录  性能比较高
-- 3. insert into 表名 （列1....列n） values (数据1,....数据n),(数据1,....数据n),(数据1,....数据n);

2. update

一次更新一行记录。带条件修改。条件语句 where

-- 1. update 表名 set 列1=新的数据,... 列n=新的数据;

-- 修改表里面所有的记录
mysql> update tb_userinfo set age=35,gender='m';


-- 2. update 表名 set 列1=新的数据,... 列n=新的数据  where 条件1 and/or 条件2;
mysql> update tb_userinfo set balance=7000,birthday=now() where id=4;

3. delete

删除. 删除一个删除多个。

-- delete  from  表名;  --  删除表里面的所有的数据(清空表) 可以回滚

-- delete from 表名 where 条件1 and/or 条件2;

mysql> delete from tb_userinfo where id=3 or id=4;
mysql> delete from tb_userinfo where id in (1,2);

4. 客户端工具

// 1. navicat 
// 2. sqlyog
// 3. mysqlFront

5.约束

限定字段数据。数据需要满足一些规则。

表级约束 primary key
行级约束： not null default unique primary key

5.1 not null

非空约束。限定列的数据不能为null。

-- INSERT INTO tb_product (id,prod_price) VALUES (5,10); 
-- prod_name必须赋值  不能为null  not  null
-- 修改这个列的特征
DESC tb_product;
ALTER TABLE tb_product MODIFY prod_name VARCHAR(20) NOT NULL;


CREATE TABLE a1(
 id int not null,
 name varchar(20) not NULL
);

5.2 default

默认约束。给指定列设置默认值。

-- CREATE TABLE a1(
--  id int not null,
--  name varchar(20) NOT NULL DEFAULT '无名氏',
--  age int
-- );

-- ALTER TABLE a1 modify name varchar(20) not null DEFAULT '无名氏';
-- DESC a1;

-- INSERT INTO a1 (id,`name`,age) VALUES (1,null,20);

5.3 unique

unique。保证列的数据唯一性。也称为唯一性索引。

NOT NULL+ UNIQUE ===> PRI

索引----> 目录  效率比较快的。   底层是结合一些算法维护索引。
创建唯一性约束 自带索引的。
有索引的字段在查询的语句中  比没有索引列 性能更快。

推荐在sql中大量创建索引。 第4阶段。

-- CREATE TABLE a1(
--  id int not null,
--  name varchar(20) NOT NULL UNIQUE DEFAULT '无名氏',
--  age int NOT null
-- );

-- 在NOT NULL UNIQUE 下 默认约束就没有意义
-- NOT NULL+ UNIQUE ===> PRI
-- ALTER TABLE a1 modify name varchar(20) UNIQUE;

5.4 primary key

主键约束。数据特征: not null+unique

也可以称为主键索引。自带索引。

--  1. 一张表里面只有1个主键约束。
--  2. 任意类型的列都可以充当主键列。-----> 整型(自增)+字符类型(uuid)
--  3. 可以有多个列同时充当一个主键列。(联合主键)---->必须使用表级约束

表设计: 理论上遵循3大范式
第1范式:  保证列的原子性。 列不可再分。  河南省郑州市高新  没有保证原子性。
 provice  city  area
第2范式:  在遵循第1范式的基础之上  保证行记录的唯一性。 id唯一。 一般都是主键约束。
第3范式:  在遵循第2范式的基础之上. 
    尽可能减少/不能出现数据/列的过度冗余。 
    主键列或者外键列的数据除外。可以允许冗余。
    
具体的表设计看具体的需求。

-- DROP TABLE a1;
-- 
-- CREATE TABLE a1(
--  id int PRIMARY KEY auto_increment,
--  name varchar(20) NOT NULL UNIQUE,
--  age int NOT NULL
-- );
-- 
-- DESC a1;

-- id: PRIMARY KEY 必须要唯一   手动维护唯一性???
-- mysql自己维护主键列id的数据  自己累增+1
-- 主键列是整型的话 一般都会与auto_increment 联合使用。
-- ALTER TABLE a1 MODIFY id int auto_increment;
-- id的数据自增了 在insert的时候 就不需要对id赋值 了
-- 自定义自增的初始值  默认1  每次自增+1
-- ALTER TABLE a1 auto_increment 1001;
-- SET GLOBAL auto_increment_increment=5;
-- 可以获得上一次新增的id的数据
-- SELECT LAST_INSERT_ID();
-- SELECT * FROM a1;

INSERT INTO a1 (`name`,age) VALUES ('张翠翠4',20);
SELECT * FROM a1;


-- create TABLE b(
--  id VARCHAR(100),
--  name VARCHAR(20),
--  PRIMARY KEY(id) -- 表级约束
-- );

-- DESC b;
-- 1.要么在代码里面 UUID 传过来一个唯一的字符串的数据
-- 2. 交给mysql服务器维护 id值  UUID()
INSERT INTO b (id,name) VALUES (UUID(),'tom');

-- CREATE TABLE c(
--  name VARCHAR(20),
--  pass VARCHAR(100),
--  age int,
--  PRIMARY key(name,pass)
-- );

-- desc c;
-- 场景: 一般经常出现在中间表里面、(维护其他多张表的关系)
INSERT INTO c(name,pass) VALUES ('tom','1234');

5.5 foreign key

外键约束。使用外键约束修饰的列称为外键列。

1. 一张表里面可以有多个外键列
2. 外键约束维护多表关系的。
3. 一张表里面如果有外键列的话  这张表作为从表或者子表。
4. 外键列的数据要严格操作主表/基表里面的主键列的数据。

表与表之间的关系:
  1. 用户与购物车  1对1关系
  2. 购物车与购物项  一对多
      购物车里面有很多购物项  还有总价
      
  3. 购物项与商品 一对一
  
 得知:  
  4. 用户与商品 多对多

1. 一对一

-- 新增外键约束
-- tb_cart.uid  将这个列变为外键列
-- ALTER TABLE tb_cart ADD CONSTRAINT FOREIGN KEY (uid) REFERENCES tb_userinfo(id);

在外键约束下操作主表+子表数据:

-- 新增外键约束
-- tb_cart.uid  将这个列变为外键列
-- ALTER TABLE tb_cart ADD CONSTRAINT FOREIGN KEY (uid) REFERENCES tb_userinfo(id);

-- 一：有外键约束   删除/更新 RESTRICT
-- 新增用户信息
-- 1. 新增主表
-- INSERT INTO tb_userinfo (name) VALUES ('王五');
-- 2.删除主表
-- DELETE FROM tb_userinfo WHERE id =11; -- 子表没有参考主表的信息
-- DELETE FROM tb_userinfo WHERE id =1; 
   -- 不能删除  子表在使用这个数据 
	 -- 根本原因: 在外键约束里面   删除/更新 RESTRICT
-- 3. 修改主表信息
-- UPDATE tb_userinfo SET gender = 'n' WHERE id =1;

-- 操作子表
-- 1. 新增子表的数据
-- INSERT INTO tb_cart (total_money,uid) VALUES (1000,6); -- 这个数据必须存在于主表里面
-- 2. 删除子表的数据
-- DELETE FROM tb_cart WHERE id=1011;
-- 3. 修改子表的数据
-- UPDATE tb_cart SET total_money = 2000,uid=10 WHERE id = 1001;

-- 二：有外键约束   删除/更新 SET NULL
-- 删除主表
-- DELETE FROM tb_userinfo WHERE id =1;
-- 子表里面没有记录关联数据  直接删除
-- 有 会先将子表里面记录update为null  然后再删除主表的数据

-- 二：有外键约束   删除/更新 CASCADE  级联
-- 删除主表
-- DELETE FROM tb_userinfo WHERE id =6;

-- SELECT * FROM tb_userinfo;
-- SELECT * FROM tb_cart;

-- 总结: 外键约束可以维护多表数据。
-- 弊端: 性能低。不太推荐使用外键约束的。 外键列还正常使用。
-- 只需要将一个普通列看成外键列即可。 弱化外键思想。伪外键

2.一对多

一个购物车有多个购物项

3. 多对多

学生+老师学生+课程

角色+权限

6. DQL

数据查询语言。 select

6.1.语法

SELECT [DISTINCT] 列的集合
FROM 表的集合 
[
WHERE 条件的集合 -- 过滤记录
GROUP BY 列的集合 -- 分组查询   使用指定的列进行分组
HAVING   条件的集合 -- 类似的where 过滤的  对分组之后的数据进行过滤
ORDER BY 列 ASC/DESC  -- 根据指定的列进行升序或者降序排列 默认升序 ASC
LIMIT ?/?,? -- 限定最后记录 分页查询       
]

6.2 基础查询

SELECT * FROM A; -- 表里面列很少的时候  *
SELECT ID ,NAME ,AGE FROM A; -- 列很多  指定列查询  提高sql的查询的性能

6.3 条件查询 where

	条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
	=、!=、<>、<、<=、>、>=；
	BETWEEN…AND；是否满足一个区间范围  >=  <=
	IN(set)；条件的集合
	IS NULL；
	AND； 连接多个条件的查询
	OR；or  满足其中一个条件就可以
	NOT；

-- 1. 条件查询
-- 	查询学生性别为女，并且年龄50的记录
-- SELECT * FROM stu WHERE gender='female' OR age=15;
-- 	查询学号为S_1001，S_1002，S_1003的记录
-- SELECT * FROM stu WHERE sid='s_1001' OR sid='s_1001'  OR sid='s_1003' ;
-- SELECT * FROM stu WHERE sid IN ('s_1001','s_1002','s_1003');
-- 	查询学号不是S_1001，S_1002，S_1003的记录
-- SELECT * FROM stu WHERE NOT sid  IN ('s_1001','s_1002','s_1003');
-- SELECT * FROM stu WHERE sid!='s_1001' AND sid!='s_1002'  AND sid!='s_1003' ;

-- 	查询年龄为null的记录
-- SELECT * FROM stu WHERE age IS NulL;
-- SELECT * FROM stu WHERE age IS NOT  NulL;

-- 	查询年龄在20到40之间的学生记录
-- SELECT * FROM stu WHERE age>= 20 AND age<=40;
-- SELECT * FROM stu WHERE age BETWEEN 20 AND 40;

-- 	查询性别非男的学生记录
-- SELECT * FROM stu WHERE gender!='male' OR gender is null;

6.4 模糊查询 like

与where一起使用。模糊匹配

-- 	查询姓名由5个字母构成的学生记录
-- 使用通配符:  统配任意一个字符  _ 
-- SELECT * FROM stu WHERE sname LIKE '_____';
-- 	查询姓名以“z”开头的学生记录
-- %: 统配的是任意数量的字符数据
-- SELECT * FROM stu WHERE sname LIKE 'z%';
-- SHOW VARIABLES LIKE '%character%';

-- 	查询姓名中第2个字母为“i”的学生记录
-- SELECT * FROM stu WHERE sname LIKE '_i%';
-- 	查询姓名中包含“a”字母的学生记录
-- SELECT * FROM stu WHERE sname LIKE '%a%';

6.5 字段控制查询

1. distinct 去重

只是对指定的列去重。

-- 3. DISTINCT  去重  看DISTINCT有哪些列
-- SELECT DISTINCT gender FROM stu;
-- 可以去重重复的行记录

2. ifnull

-- 计算员工的工资
-- 只要是查询语句  都会出现一张临时表
-- comm有值得记录 运算是ok
-- 列与null进行任意算术运算  最后的结果都是null
-- IFNULL(列名,新的数据) 列为null  使用指定的数据进行运算  否则还是自身的数据
-- SELECT empno,ename,sal,comm, (sal+IFNULL(comm,0)) FROM emp;

6.6 排序 order by

在代码里面排序:
 Arrays.sort()
 List.sort(Comparator c)
 Collections.sort(List l)    
 Collections.sort(List l,Comparator c)    
     
 List<Integer> list;      Integer implements Comparable 
 包装类型+String  
 
  List<UserInfo> list;

order by 字段1 ASC/DESC ，字段2 ASC/DESC

-- 只要是查询一般都会排序 一般都是将最新的数据  展示在最前面
-- ORDER BY id/create_time(毫秒数) DESC
-- 查询学生的信息  根据年龄进行升序或者降序排列
-- SELECT * FROM stu ORDER BY age ASC, sid DESC;

6.7 分组函数

也可以称为 “聚合函数”。------> 有且只有1个记录(只有1个结果)

	COUNT(列)：统计指定列不为NULL的记录行数；
	MAX(列)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
	MIN(列)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
	SUM(列)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
	AVG(列)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；


-- 	查询emp表中记录数：
-- SELECT COUNT(empno),COUNT(comm),COUNT(*),COUNT(1) FROM emp;
-- 	查询emp表中月薪大于2500的人数：
-- 	统计月薪与佣金之和大于2500元的人数：
-- SELECT COUNT(*) FROM emp WHERE (sal+IFNULL(comm,0))>2500;

-- 在查询的时候 会出现的很多新的字段 这些的名称 有可能很复杂
-- 一般进行别名处理  [AS] 
-- 对表起别名: 多表关联的时候  
-- SELECT MAX(sal),MIN(e.sal),SUM(sal),AVG(sal),SUM(sal)/COUNT(*) AS avgSal  FROM emp AS e;

-- SELECT * from emp where sal = (SELECT MAX(sal) FROM emp);

6.8 分组查询 group by

根据指定的列进行分组查询。

-- 分组查询
-- 	查询每个部门的部门编号和每个部门的工资和：
-- 先分组  再进行分组运算
-- -- 	查询每个部门的部门编号以及每个部门的人数：
-- SELECT deptno,sum(sal) '每个部门工资和',COUNT(*) '每个部门的员工数量'  FROM emp  GROUP BY deptno;
-- 	查询每个部门的部门编号以及每个部门员工工资大于1500的人数：
-- SELECT deptno,count(*) FROM emp WHERE sal>1500 GROUP BY deptno;

6.9 having

作用与where一致。

-- 查询薪资>1500 的员工信息
-- SELECT * FROM emp WHERE sal>1500;
-- SELECT * FROM emp HAVING sal>1500;
-- 与分组相关
-- 查询工资总和大于9000的部门编号以及工资和：
-- > 1111 - Invalid use of group function  where 不能与组函数一起使用
-- 对比where vs having  : 与条件总和使用  过滤数据
-- 1. 从位置 where在GROUP BY之前  having在GROUP BY之后
-- 2. where 不能与组函数一起使用  having就是对分组之后的数据进行过滤 因此可以一起使用
-- SELECT deptno,sum(sal) AS sum FROM emp  GROUP BY deptno HAVING sum(sal)>9000 ;

6.10 多表关联查询

1. 等值连接

连接的条件是一个 =

-- 多表关联查询
-- 1. 查询员工信息，要求显示员工号，姓名，月薪，部门名称 
-- 等值关联查询: 查询的条件是=
SELECT
  e.empno,e.ename,e.sal,d.dname
FROM emp AS e,dept AS d   -- 查询多张表 使用 , 
WHERE e.deptno=d.deptno;   -- 多表关联的时候 分析表与表的关系(外键列+主键列)

-- 多表关联的时候 有可能多张表里面出现很多重名列 
-- 1. 使用表名限定
-- 2. 使用别名 表的别名
-- 关联2张表查询: 条件至少1个   关联n张表查询: n-1

-- 查询指定用户的购物车信息
-- SELECT 
--  u.id,u.`name`,c.total_money
-- FROM 
--  demo.tb_userinfo AS u, demo.tb_cart AS c
-- WHERE
--  u.id =1  AND u.id = c.uid; 
-- 查询用户购买的购物项(小计)
-- SELECT
--  u.id,u.`name`,c.total_money,ci.buy_num,ci.money
-- FROM
--  demo.tb_userinfo AS u, demo.tb_cart AS c,demo.tb_cart_item AS ci 
-- WHERE
--  u.id =1 AND u.id=c.uid AND c.id=ci.cid;
-- 查询用户购买的商品信息
-- SELECT
--  u.id,u.`name`,c.total_money,p.prod_name,p.prod_price,p.prod_image,ci.buy_num,ci.money
-- FROM
--  demo.tb_userinfo AS u, demo.tb_cart AS c,demo.tb_cart_item AS ci ,demo.tb_product AS p
-- WHERE
--  u.id =1 AND u.id=c.uid AND c.id=ci.cid AND ci.pid=p.id;

2. 不等值连接

连接条件不是一个 =

-- 查询员工信息，要求显示：员工号，姓名，月薪，部门名称,薪水的级别
-- SELECT
--   e.empno,e.ename,e.sal,s.GRADE
-- FROM
--   emp AS e, salgrade AS s
-- WHERE
--   e.sal BETWEEN s.LowSAL AND s.HISAL
-- ORDER BY e.sal;

SELECT
  e.empno,e.ename,e.sal,s.GRADE,d.dname,d.loc
FROM
  emp AS e, salgrade AS s,dept AS d
WHERE
  e.sal BETWEEN s.LowSAL AND s.HISAL AND e.deptno=d.deptno
ORDER BY e.sal;

3. 自连接

本表与本表关联查询。

-- 自连接
-- 查询员工姓名和员工的老板的名称
-- 本表与本表有关系: 需要将一张表  看成多张表  别名  使用条件分析
SELECT
 e1.*,   e2.ename AS boss_name
FROM
emp AS e1, emp AS e2
WHERE e1.mgr=e2.empno;

4. 外连接

-- 查询的所有的员工信息并展示上级信息
-- 基准表是: 员工表
-- 外连接
-- 1. 内外连接  ---->普通的关联查询  INNER OUTER JOIN  INNER JOIN...ON/WHERE
-- 2. 左外连接 LEFT JOIN...ON  以左表为基准 右表里面没有的数据使用null或者0填充
-- 3. 右外连接 RIGHT JOIN...ON 以右表为基准 左表里面没有的数据使用null或者0填充
-- SELECT
--  e1.*,   e2.ename AS boss_name
-- FROM
-- emp AS e1 INNER JOIN  emp AS e2
-- ON e1.mgr=e2.empno
-- WHERE 1=1;

-- SELECT
--  e1.*,   e2.ename AS boss_name
-- FROM
-- emp AS e1 LEFT JOIN  emp AS e2
-- ON e1.mgr=e2.empno;

-- 按部门统计员工数，部门号，部门名称，人数 count()
-- 统计每个部门的员工数
-- SELECT
--   d.*,count(*) '部门人数'
-- FROM 
--  dept AS d, emp AS e
-- WHERE 
--  d.deptno=e.deptno 
-- GROUP BY d.deptno ;

-- SELECT
--   d.*, count(e.empno) '部门人数'
-- FROM 
--  emp AS e RIGHT JOIN  dept AS d  ON  d.deptno=e.deptno 
-- GROUP BY d.deptno ;
--

-- 查询员工信息: 展示员工所在的部门名称 员工的薪资等级  还有员工的上级领导名称
SELECT
 e1.*,d.dname,s.GRADE,e2.ename
FROM
emp AS e1 LEFT JOIN emp AS e2 ON e1.mgr=e2.empno
LEFT JOIN dept AS d ON e1.deptno=d.deptno,salgrade AS s 
WHERE
  e1.sal BETWEEN s.LowSAL AND s.HISAL;

6.11 子查询

-- 查询员工信息  他的薪资是20号部门平均薪资
-- SELECT avg(sal) FROM emp WHERE deptno=20;
-- SELECT * FROM emp WHERE sal=2175;
-- 子查询: 查询的条件是未知
SELECT * FROM emp WHERE sal=(SELECT avg(sal) FROM emp WHERE deptno=20);

6.12. 集合查询

将多个结果集转换成1个结果集。

-- 查找所有的用户信息
-- SELECT * FROM user0;
-- SELECT * FROM user1;
-- SELECT * FROM user2;
-- 集合运算   UNION  vs  UNION ALL
-- 将多个结果集  合成1个
-- UNION ALL: 不去重  UNION: 去除重复的行记录

-- SELECT * FROM user0
-- UNION
-- SELECT * FROM user1
-- UNION
-- SELECT * FROM user2;

-- SELECT id,name FROM USER0
-- UNION
-- SELECT id,name FROM USER1
-- UNION
-- SELECT id,name FROM USER2;

-- 在mysql里面 有几种去重的操作?
-- 1. DISTINCT
-- 2. UNION
-- 3. GROUP BY

6.13 分页查询

-- 分页查询员工信息
-- size=4
-- page=1
-- SELECT count(*) FROM emp;  15/4=3
-- LIMIT size; 从第1条开始查询 查size条数据  只能查询第1页的数据
-- LIMIT startIndex,size; 从第startIndex+1条开始查询 查size条
-- 第1页
SELECT * FROM emp LIMIT 0,4;

-- 第2页
SELECT * FROM emp LIMIT 4,4;

-- 第3页
SELECT * FROM emp LIMIT 8,4;

-- 第4页
SELECT * FROM emp LIMIT 12,4;

-- SELECT * FROM emp LIMIT (page-1)*size,size;

7.函数

自学

8. 数据库存储引擎

9. 数据备份

1. 物理备份  C:\ProgramData\MySQL\MySQL Server 5.7\Data
2. 命令行备份
    C:\Users\JAVASM>mysqldump -uroot -proot demo >> D:\\a.sql
    -- 将demo库里面的所有的表都进行了备份
C:\Users\JAVASM>mysqldump -uroot -proot demo tb_userinfo tb_cart >> D:\\b.sql
    -- 将demo库里面的指定的表的结构+数据进行备份
    
    在连接成功前提下  source 文件路径;   source d:\\a.sql
    
3. 使用图形化的工具
    导入/导出sql文件。
    
4. 使用图形化的工具---->备分

10.课堂练习

1.MySQL之常用函数

1. 字符串函数

函数	功能
concat(str1,str2,…)	连接字符串
insert(str,pos,len,newstr)	字符串str从第pos位置开始的len个字符替换为新字符串newstr
lower(str)	转成小写
upper(str)	转成大写
length(str)	返回字符串str的长度
char_length(str)	返回字符串str的长度
lpad(str,ien,padstr)	返回字符串str，其左边由字符串padstr填补到len字符串长度
rpad(str,len,padstr)	返回字符串str，其右边由字符串padstr填补到len字符串长度
trim(str)	去掉字符串str前缀和后缀的空格
repeat(str,count)	返回str重复count次的结果
replace(str,from_str,to_str)	用字符串to_str替换字符串str中所有的字符串from_str
substring(str,pos,len)	从字符串str的pos位置起len个字符长度的子串

insert(str,pos,len,newstr)：

INSERT('Football'，2，4，'Play') 将“Football”从第 2 个字符开始长度为 4 的字符串替换为 Play，结果为“FPlayall”；
INSERT('Football'，-1，4，'Play') 中的起始位置 -1 超出了字符串长度，直接返回原字符串；
INSERT('Football'，3，20，'Play') 替换长度超出了原字符串长度，则从第 3 个字符开始，截取后面所有的字符，并替换为指定字符 Play，结果为“FoPlay”。

2. 数值函数

函数	功能
ABS（X）	返回X的绝对值
CEIL(X)	返回不小于X的最小整数(向上取整)
FLOOR(X)	返回不大于x的最大整数（向下取整），返回值类型bigint
MOD(X,Y)	返回x/y的模
RAND()	返回一个0~1之间的随机浮点数
ROUND(X,Y)	返回x的四舍五入的有y位小数的值
TRUNCATE(X,Y)	返回数字x截断位y位小数的结果

TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）

SELECT TRUNCATE(1.23456,3);
---- 返回1.234

3. 日期和时间函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前的日期和时间
WEEK(date)	返回指定日期为一年中的第几周
YEAR(date)	返回日期的年份
HOUR(time)	返回time的小时值
MINUTE(time)	返回time的分钟值
MONTHNAME(date)	返回date的月份名
DATEDIFF(expr,expr2)	返回起始时间expr和结束时间exrp2之间的天数
DATE_FORMAT(date,fmt)	返回按字符串fmt格式化日期date值
from_unixtime(unix_timestamp,”%Y-%m-%d %H:%i:%S”)常用来将毫秒数转换为时间格式

%M 月名字(January……December)

%W 星期名字(Sunday……Saturday)

%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）

%Y 年, 数字, 4 位

%y 年, 数字, 2 位 20

%a 缩写的星期名字(Sun……Sat)

%d 月份中的天数, 数字(00……31)

%e 月份中的天数, 数字(0……31)

%m 月, 数字(01……12)

%c 月, 数字(1……12)

%b 缩写的月份名字(Jan……Dec)

%j 一年中的天数(001……366)

%H 小时(00……23)

%k 小时(0……23)

%h 小时(01……12)

%I 小时(01……12)

%l 小时(1……12)

%i 分钟, 数字(00……59)

%r 时间,12 小时(hh:mm:ss [AP]M)

%T 时间,24 小时(hh:mm:ss)

%S 秒(00……59)

%s 秒(00……59)

%p AM或PM

%w 一个星期中的天数(0=Sunday ……6=Saturday ）

%U 星期(0……52), 这里星期天是星期的第一天

%u 星期(0……52), 这里星期一是星期的第一天

%% 一个文字“%”。

4. 高级函数

case见mysql作业

11.课后作业

1.作业1

1.创建student和score表

CREATE TABLE student (
id INT(10) NOT NULL PRIMARY KEY , #学生ID 自增
name VARCHAR(20) NOT NULL , #学生姓名
sex VARCHAR(4) , #学生性别
birth YEAR, #学生出生年份
department VARCHAR(20) , #所在院系
address VARCHAR(50) #家庭住址
);

创建score表。SQL代码如下：
CREATE TABLE score (
id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT , #自增ID
stu_id INT(10) NOT NULL , #学生ID ，外键创建可有可无
c_name VARCHAR(20) , #学科名称
grade INT(10) #分数
);

2.添加记录

向student表插入记录的INSERT语句如下：
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
向score表插入记录的INSERT语句如下：
INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);

3.查询student表的所有记录

select * from student;

4.查询student表的第2条到4条记录

select * from student limit 1,3;

5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

select id,name,department from student;

6.从student表中查询计算机系和英语系的学生的信息

select * from student where department='计算机系' or department='英语系';

7.从student表中查询年龄1985~1990年份的学生信息

select * from student where birth between 1985 and 1990;

8.从student表中查询每个院系有多少人

select count(*) as '人数' from student group by department;

9.从score表中查询每个科目的最高分

select max(garde) as '最高分' from score group by c_name;

10.查询李四的考试科目（c_name）和考试成绩（grade）

select

st.name,sc.c_name,sc.garde

from student as st ,score as sc

where

st.name='李四' and st.id=sc.stu_id;

11.用连接的方式查询所有学生的信息和考试信息

select

st.*,sc.*

from

student as st, score as sc

where

st.id = sc.stu_id;

结果：六个人展示出10行记录，希望只展示6行记录

-- GROUP_CONCAT 一个人有多行记录，且值不一样，可拼到一起前提：必须有分组
SELECT
s.`name`,GROUP_CONCAT(sc.c_name),GROUP_CONCAT(sc.garde)
FROM
student AS s, score sc WHERE s.id=sc.stu_id
GROUP BY s.id;

升级版，用case when 行转列（做数据报表和面试题）

语法一：简单函数，做等值判断 case字段 when数据 then数据 else数据 end

语法二：搜索函数，做条件判断 case when条件 then数据 .....（多个when） else数据 end

SELECT
s.`name`,
max(CASE sc.c_name WHEN '计算机' THEN sc.garde ELSE null END) AS '计算机成绩',
max(CASE sc.c_name WHEN '中文' THEN sc.garde ELSE null END) AS '中文成绩',
max(CASE sc.c_name WHEN '英语' THEN sc.garde ELSE null END) AS '英语成绩'
FROM
student AS s, score sc WHERE s.id=sc.stu_id GROUP BY s.id;

若求每个学生年龄处于人生的什么阶段少年 12-18 青年 19-30 中年31-50 老年51+

SELECT
id,name,birth, (YEAR(NOW())-birth) AS '年龄',
CASE
WHEN (YEAR(NOW())-birth) BETWEEN 12 AND 18 THEN '少年'
WHEN (YEAR(NOW())-birth) BETWEEN 19 AND 30 THEN '青年'
WHEN (YEAR(NOW())-birth) BETWEEN 31 AND 50 THEN '中年'
ELSE '老年'
END AS '人生阶段'
FROM student;

-- 涨工资 <10000 1.5 10000-15000 1.3 15000-30000 1.1
-- CREATE table temp select * from emp; 创建临时表，复制粘贴emp表

SELECT
empno,ename,sal,
CASE
WHEN sal <10000 THEN sal*1.5
WHEN sal BETWEEN 10001 AND 15000 THEN sal*1.3
WHEN sal BETWEEN 15001 AND 30000 THEN sal*1.1
ELSE sal
END AS '涨薪之后的薪资'
FROM temp;

12.计算每个学生的总成绩

select

st.name,sum(sc.garde)

from

student as st ,score as sc

where

st.id=sc.stu_id

group by sc.stu_id;

13.计算每个考试科目的平均成绩

select

sc.c_name '考试科目',avg(sc.garde) '平均成绩'

from score as sc

group by sc.c_name;

14.查询计算机成绩低于95的学生信息

SELECT
s.*,c.c_name,c.garde
FROM
student AS s,score AS c
WHERE
s.id = c.stu_id AND c.garde<95 AND c.c_name='计算机';

15.将计算机考试成绩按从高到低进行排序

SELECT * FROM score WHERE c_name='计算机' ORDER BY greade DESC ;

16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

SELECT s.*,c.c_name,c.greade FROM student AS s,score AS c WHERE s.id = c.stu_id AND (s.name like '张%' OR s.name like '王%');

17.查询都是北京的学生的姓名、年龄、院系和考试科目及成绩

SELECT s.*,c.c_name,c.greade FROM student AS s,score AS c WHERE s.id = c.stu_id AND s.address like '北京%';

2.作业2

建立如图所示的四张表，按照图示数据添加到对应的表中

表一 emp

表二 dept

表三 salgrade

表四年度利润表

1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

-- SELECT
-- d.*,COUNT(e.empno)
-- FROM emp e,dept d
-- WHERE e.deptno=d.deptno GROUP BY e.deptno;
-- SELECT
-- d.*,COUNT(e.empno)
-- FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno
-- GROUP BY e.deptno HAVING COUNT(e.empno)>=2;

2. 列出所有员工的姓名及其直接上级的姓名。

-- SELECT
-- e1.*,e2.ename
-- FROM emp e1 LEFT JOIN emp e2 ON e1.mgr=e2.empno;

3. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。

-- SELECT
-- e1.*,e2.ename,d.dname
-- FROM emp e1 LEFT JOIN emp e2 ON e1.mgr=e2.empno
-- LEFT JOIN dept d ON e1.deptno=d.deptno
-- WHERE e1.hiredate<e2.hiredate;

4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

-- SELECT * FROM dept d LEFT JOIN emp e ON d.deptno=e.deptno ORDER BY d.deptno;

5. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。

-- SELECT
-- job,count(*)
-- FROM emp GROUP BY job HAVING min(sal)>15000 ;

6. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

-- SELECT * FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='销售部');

7. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

-- SELECT
-- e1.*,e2.ename,d.dname,s.GRADE
-- FROM emp AS e1, emp AS e2,dept AS d,salgrade AS s
-- WHERE e1.mgr=e2.empno AND e1.deptno=d.deptno AND e1.sal BETWEEN s.lowsal AND s.HISAL ;

-- SELECT
-- e1.*,e2.ename,d.dname,s.GRADE
-- FROM emp AS e1
-- LEFT JOIN emp AS e2 ON e1.mgr=e2.empno
-- LEFT JOIN dept AS d ON e1.deptno=d.deptno,salgrade AS s
-- WHERE e1.sal BETWEEN s.lowsal AND s.HISAL AND e1.sal >(SELECT avg(sal) FROM emp)
-- ORDER BY s.GRADE ;

8.列出与庞统从事相同工作的所有员工及部门名称。

-- SELECT
-- *
-- FROM
-- emp e,dept d
-- WHERE e.deptno=d.deptno AND e.ename!='庞统' AND e.job=(SELECT job FROM emp WHERE ename='庞统');

9.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。

-- SELECT
-- *
-- FROM
-- emp e LEFT JOIN dept d ON e.deptno=d.deptno WHERE e.sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);

10.查出年份、利润、年度增长比。

-- SELECT
-- p1.year,p1.zz, CONCAT(ROUND(((p1.zz-p2.zz)/p2.zz)*100,2),'%') AS '年度增长比'
-- FROM profit p1 LEFT JOIN profit p2 ON p1.year=p2.year+1 ORDER BY p1.`year`;

参考：

SQL语句的增删改查（详细）_宝宝要努力的博客-CSDN博客_增删改查https://blog.csdn.net/a88055517/article/details/6736284