JAVA---MySQL学习

MySQL

1. 数据库 DB

database. 存储不同类型的数据，

数据库的重要性?
 web项目:
   模拟用户注册       
   用户---> 页面(视图层) ---> 服务器(tomcat)
       ---->控制层controller(servlet/action)   负责页面和后台交互  servlet/springMVC
       ---> service(业务逻辑处理层 权限管理/日志管理/事务管理)
       ---->dao(数据持久层 jdbc/mybatis/hibernate)
       ---> DB(永久存储数据 mysql oracle sqlserver  redis )

数据库的分类:

1. 从存储位置
  1.1 基于磁盘(文件) mysql sqlserver oracle  IO  效率偏慢  保证数据持久化
  1.2 基于缓存   redis key:value    mogodb    hbase    效率很快 有可能会丢失数据
      
2. 从关系上划分
   2.1 关系型数据库   mysql sqlserver oracle  库与库 表与表  字段与字段之间有关系的
   2.2 非关系型数据库  redis  mogodb    hbase  里面没有表  只有数据  
      key:value

2. DBMS

数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS.
    
关系数据库管理系统（Relational Database Management System：RDBMS）是指包括相互联系的逻辑组织和存取这些数据的一套程序 (数据库管理系统软件)。关系数据库管理系统就是管理关系数据库，并将数据逻辑组织的系统。  
    
 MYSQL： 学习的是mysql的服务。   安装的是mysql服务端程序。----> 数据都在服务端 




## 1. 操作Mysql

### 1.1 常用指令

> 数据库管理系统软件----> 很多数据库---> 每个库里面都有很多表----> 字段(列) 类型 约束  数据

```mysql
-- 客户端连接服务端  Socket---> ip 端口 用户名 密码
-- C:\Users\DELL>mysql -h127.0.0.1 -uroot -p
-- C:\Users\DELL>mysql -uroot -p  连接 本机

mysql> show databases;  -- 展示mysql里面所有的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

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

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

mysql> show tables; -- 查看指定库里面所有的表
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event

mysql> desc user;  -- 查看指定表的结构
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                              |       |

mysql> show create database mydb; -- 查看创建库的基本信息  (编码)
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| mydb     | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+

2. SQL

structured query language 结构化查询语言。

1. DDL  data defination language 数据定义语言  create drop truncate alter 
-- 2. DML  数据操作语言   insert  update  delete  
-- 3. DQL  数据查询语言  select 
4. DCL  数据控制语言   grant commit rollback  begin

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

3. DDL

mysql> create database mydb;  -- 创建数据库  一旦创建 名称不可更改

mysql> drop database mydb;-- 删除指定的库    
Query OK, 0 rows affected (0.00 sec)

-- 修改表结构  alter
mysql> alter table tb_userinfo add address varchar(30); -- 新增新的字段
Query OK, 0 rows affected (0.03 sec)

mysql> alter table tb_userinfo drop address; -- 删除指定的列
mysql> alter table tb_userinfo change id id bigint(2); -- 修改字段的数据类型
mysql> alter table tb_userinfo modify id int(3); -- 修改字段的数据类型

mysql> alter table tb_userinfo change name username varchar(20); -- 修改字段的名称

mysql> alter table tb_userinfo rename userinfo; -- 修改表名

创建表 ---> 字段(列/属性) 类型 约束

映射操作:  mybatis  ORM  对象关系映射
  表与类的映射。 一张表就是一个实体类  表字段就是类的属性  表字段的数据类型 就是类属性的数据类型

create table 表名( -- tb_userinfo   t_ userinfo   userinfo  
  字段名称1 字段类型  [约束],
  字段名称1 字段类型  [约束],
  字段名称1 字段类型  [约束],
    ....
  字段名称n 字段类型  [约束]  
);

-- 创建一张用户信息表
create table tb_userinfo(
  id int(2),
  name varchar(20),
  age tinyint(2) unsigned,
  gender char(1),
  birthday date,
  balance decimal(20,3),
  create_time datetime,
  update_time datetime 
);

4. 数据类型

一切采取从小的原则。

1. 整数类型
tinyint(m)==>byte  -128-127   unsigned 0-255  age
int(m)==>int  id 
bigint(m)==>long  id  时间的毫秒数

m: 限定列宽的宽度。 默认值11
unsigned 无符号的数字 
zerofill  以0填充   int(5) 00100  10000000

tinyint(1)  0false  1true---> boolean

2. 小数类型
float(m,n)  m: 总位数   n:小数点后的位数  float
double(m,n) double

decimal(m,n) 定点数  BigDecimal  小数运算 不会丢失精度

3. 字符类型
char(m)  m: 限定字符的个数  定长  姓别  手机号码  身份证号码  255
varchar(m)  可变长  姓名  
  255个字符  65535
char(2): ‘a_’  查询的时候 trim操作 再展示数据
varchar(2) ‘a’ 存储1个字符    

4. 日期类型
年月日 date--> java.util.Date  mysql 5.7     mysql 8  LocalDate/LocalDateTime
年月日 时分秒 
datetime
timestamp

year(2)/year(4)  2021
time  时分秒 

bigint(m) 时间的毫秒数

5. DML

  1. insert 新增（一次新增一行）
1. insert into 表名 values (,'','',);   -- 对表里面所有的列都要赋值  不推荐

mysql> insert into userinfo values (1,'jim',20,'m','2020-01-01',64354.8266,'2020-01-01 12:00:00','2020-01-01 12:00:00');

2. insert into 表名 (字段1,字段2) values ();-- 指定列新增
mysql> insert into userinfo (id,username,age,create_time) values (2,'tom',-20,now());
ERROR 1264 (22003): Out of range value for column 'age' at row 1


-- 只有1行记录受影响

3. insert into 表名 (字段1,字段2) values (),(),();-- 一次新增多行记录


-- 不能添加中文?
mysql> insert into userinfo (id,username,age,create_time) values (2,'张三',20,now());
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'username' at row 1

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 */ |
+----------+---------------------------------------------------------------+

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\ |
+--------------------------+---------------------------------------------------------+

统一全局配置:
66 default-character-set=utf8
100 character-set-server=utf8
  1. delete 删除 （多行受影响的）
1. delete from 表名;
delete from userinfo;-- 清空表数据  慎重

2. delete from 表名 [条件];
 delete form 表名 where 条件1 and/or 条件2;
 
 mysql> delete from userinfo where id=1;
 mysql> delete from userinfo where age>=18;
 
 mysql> delete from userinfo where id in (20,1,2);
 delete from userinfo where id=20 or id=1 or id=2;
  1. update 修改 (一次修改一个 一行记录受影响)
update 表名 set 字段=新数据, 字段=新数据 ;  -- 修改全部的所有的列
update 表名 set 字段=新数据, 字段=新数据 where 条件1 and/or 条件2;
mysql> update userinfo set birthday='2021-01-01',balance=24356,update_time=now() where id=2;

6. 约束

可视化客户端工具。(软件) sqlyog navicat mysqlfront

约束: 操作表字段的数据的时候 遵循一些规则(约束)。 限制insert update delete功能。

行级约束: 非空约束  唯一性约束  默认 (限定一行记录/限定某个字段数据)
表级约束: 主键约束  外键约束 (涉及多个字段  涉及到多张表)

1. 非空约束 not null

限定字段数据 不能为null。

-- 1. not null
-- 创建一张表  不加任何约束  字段都是默认可以为null
-- desc tb_userinfo;
-- 修改tb_userinfo  name 不能为null
-- ALTER TABLE tb_userinfo change name name varchar(20) not null;
-- desc tb_userinfo;
--  Field 'name' doesn't have a default value
-- insert into tb_userinfo (name,age) values (1,'aaa',20);
-- SELECT * FROM tb_userinfo;

create TABLE a(
id int,
`name` varchar(20) not null,
age int null
);

2. 唯一性约束 unique

代表这个字段数据唯一。 不能重复。 除了null 又称为: 唯一性索引 unique+notnull==>primary key

有的字段有索引的话 基于这些列查询操作 效率较快。

-- 2. 唯一性约束

-- CREATE TABLE b(
--    id int not null,
-- 	 `name` varchar(20) UNIQUE,
-- 	 age int not null
-- );
-- > 1062 - Duplicate entry 'a' for key 'name'
insert into b (id,name,age) VALUES  (3,'a',20);

SELECT * from b;

3. 默认约束 default

限定列默认数据。

-- 3. 默认约束
CREATE TABLE b(
   id int not null,
	 `name` varchar(20)  UNIQUE default '无名氏',
	 age int not null,
	 gender char(1) DEFAULT 'M'
);
insert into b (id,age) VALUES  (4,20);
SELECT * from b;

4. 主键约束 primary key

限定列的数据: 非空 且 唯一。 一张表里面只有一个主键列。(只能对一个字段加上主键约束)

任意类型的列都可以充当主键列。实际开发中，一般都是id充当主键列。

id一般都是 int/bigint varchar() 主键约束 自带索引 。 操作id

CREATE TABLE c(
  id int,
	name varchar(20) not null UNIQUE,
	age int,
	PRIMARY key(id)  -- 表级约束   一般是服务于联合 主键的
);

CREATE TABLE c(
  id int PRIMARY key,
	name varchar(20) not null UNIQUE,
	age int
);

id是整型。int/bigint 满足主键列。一般都会自增维护id的数据。 auto_increment 默认1 每次自增+1

-- 4. 主键约束
-- CREATE TABLE c(
--   id int PRIMARY key,
-- 	name varchar(20) not null UNIQUE,
-- 	age int
-- );

-- insert into c (id,name) VALUES (2,'aaa1');
-- insert into c (id,name) VALUES (3,'aaa2');
-- insert into c (id,name) VALUES (4,'aaa3');
-- alter TABLE c change id id int auto_increment;
-- 自动维护  不需要给id赋值了
-- insert into c (name,age) values ('hd7',20);
-- -- 修改id初始值
-- alter TABLE c auto_increment 2000; 
-- 修改全局的步长 +5
set GLOBAL auto_increment_increment=1;
SELECT * from c;

主键列: 字符串类型的时候 维护id的唯一性呢?

-- CREATE TABLE d(
--  id varchar(255) PRIMARY key,
--   age int 
-- );
-- 自动维护  uuid()
-- SELECT UUID(),UUID();
-- 代码传过来传过来一个唯一的数据
INSERT into d (id,age) values (UUID(),20);
SELECT * FROM d;

数据库的表设计:
  遵循三大范式
  1. 第一范式  列不可再分  保证列的原子性。  address---> 河南郑州高新区   可以再分的
  2. 第二范式  满足第一范式的基础之上  保证行记录的唯一性。 添加主键
  3. 第三范式  满足第一，2范式的基础之上  尽量避免(字段)数据冗余   排除外键列的数据。
    总金额: 每个商品单价*数量

5. 外键约束 foreign key

表级约束。 foreign key

一对一:
 用户和身份证
 一夫一妻制
 用户和购物车
 购物车----> 购物项  id  buynum  total
 通过用户查看用户购物车信息:  在 用户表里面  新增新的列 外键列---> 严格参照主表里面的主键列的数据的 
  用户表: 是从表 (外键列都在从表里面)
  购物车表: 主表 
把用户表里面外键列(cid) 添加外键约束。 
-- 自动维护  uuid()
-- SELECT UUID(),UUID();
-- 代码传过来传过来一个唯一的数据
-- INSERT into d (id,age) values (UUID(),20);
-- SELECT * FROM d;
-- 外键约束---> 外键列----> 从表----> 用户表
-- 也自带索引
-- ALTER TABLE tb_userinfo ADD CONSTRAINT FOREIGN key (cid) REFERENCES tb_cart(id);

-- 推荐使用navicat添加外键约束。

![1622777866603](pic\foieign key .png)

-- 操作主表和从表
-- 1.1 外键约束特征: restrict
-- 1. 操作从表  tb_userinfo
-- > 1452 - Cannot add or update a child row: a foreign key constraint fails (`mydb`.`tb_userinfo`, CONSTRAINT `fk_cid` FOREIGN KEY (`cid`) REFERENCES `tb_cart` (`id`))

-- SELECT * from tb_cart;
-- insert into tb_userinfo (name,age,gender,birthday,balance,create_time,cid) 
-- values ('lisi',20,'m','2020-01-01',28726,now(),2);

-- update tb_userinfo SET cid = 1 where id=1;
-- delete from tb_userinfo where id=1;  
-- 删除/查询子表  不需要 考虑任何问题
-- 新增/修改子表的数据  考虑主表

-- 2. 操作主表 cart
-- > 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`mydb`.`tb_userinfo`, CONSTRAINT `fk_cid` FOREIGN KEY (`cid`) REFERENCES `tb_cart` (`id`))
-- delete FROM tb_cart where id=1;
-- 主表的数据  子表有在使用  无法删除   否则 可以删除主表的记录
-- 主表查询/修改/新增不需要 考虑任何问题


-- 1.2 外键约束特征: set  null  前提: 外键列 允许为null
-- 删除主表
-- delete FROM tb_cart where id=2;

-- 1.3 外键约束特征: cascade  级联操作   不能使用
-- delete FROM tb_cart where id=2;
-- 
-- select * from tb_userinfo;
-- select * from tb_cart;

-- 在开发中  不推荐使用外键约束  不允许在外键列上加外键约束  
-- 子表里面该有外键列 还是存在的 只是一个普通列 ----> 维护表与表关系? 代码层面  业务功能
-- 弱化外键  伪外键的思想

一对多:
 用户和手机号码
 购物车和购物项 : 一个购物车里面有很多购物项  
 用户和收货地址
--  需要在多的一方: 加一个外键列 

多对多:
 老师和学生:  一个老师有多个学生  一个学生有多个老师 
 --  使用一个中间表维护2者的关系
 购物车和商品:

7. DQL

最简单的查询: select * from 表名; 指定列查询

查询语句是最难写的。 单表查询 难的多表的关联查询。

-- 语法:
select distinct 列的集合(*) from 表的集合
[ where 条件1 or/and  条件2   -- 条件过滤
  group by 字段  -- 分组查询
  having 条件1 or/and  条件2  -- 对分组之后的数据进行过滤
  order by desc/asc 字段  -- 根据字段数据进行升序或者降序排列
  limit ?/?,?   -- 限定结果 (分页查询)   
];

环境准备: 建库建表

-- 学生信息表
CREATE TABLE stu (
	sid	CHAR(6),
	sname		VARCHAR(50),
	age		INT,
	gender	VARCHAR(50)
);
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

-- 员工信息表
CREATE TABLE emp(
	empno		INT,
	ename		VARCHAR(50),
	job		VARCHAR(50),
	mgr		INT,
	hiredate	DATE,
	sal		DECIMAL(7,2),
	comm		decimal(7,2),
	deptno		INT
) ;
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

-- 部门信息表
CREATE TABLE dept(
	deptno		INT,
	dname		varchar(14),
	loc		varchar(13)
);
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

-- 薪资级别表
CREATE TABLE `salgrade` (
  `GRADE` int(11) NOT NULL DEFAULT '0' COMMENT '工资的等级',
  `LowSAL` decimal(7,2) DEFAULT NULL COMMENT '此等级的最低工资',
  `HISAL` decimal(7,2) DEFAULT NULL COMMENT '此等级的最高工资'
) ;
INSERT INTO `salgrade` VALUES 
(1,700.00,1200.00),(2,1201.00,1400.00),
(3,1401.00,2000.00),(4,2001.00,3000.00),(5,3001.00,9999.00);

1. 条件查询 where

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

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

-- 查询学号不是S_1001，S_1002，S_1003的记录
-- SELECT * FROM stu WHERE  sid not in ('s_1001','s_1002','s_1003');
-- SELECT * FROM stu WHERE sid!= 's_1001' AND sid!='s_1002' AND sid!='s_1003';

-- 查询年龄为null的记录
-- 查询姓名不为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);

2. 模糊查询 like

-- SHOW VARIABLES LIKE '%character%';
-- 查询姓名由5个字母构成的学生记录 *: 只能通配列 _: 一个下划线 数字 字母 字符
-- SELECT * FROM stu WHERE sname LIKE '_____';

-- 查询姓名以“z”开头的学生记录 %:通配任意量的字符
-- SELECT * FROM stu WHERE sname LIKE 'z%';

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

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

3. 字段控制查询

对某个的字段执行特殊操作。

  1. 去重 distinct
-- 3.1 去重  DISTINCT
-- 查询学生表里面所有的性别 
-- SELECT DISTINCT sname, gender FROM stu;
  1. ifnull
-- 3.2 ifnull(字段,默认值) 当字段为null 使用默认值替换 
-- 某些列而言 数据为null  执行相关的算术运算  最终为null
-- 查询: 学生年龄+1之后的结果
SELECT sid,sname,age,IFNULL(age,18)+1 ,gender FROM stu;
-- 发工资
SELECT empno,ename,sal,comm,sal+IFNULL(comm,0) FROM emp;
  1. 别名 AS
-- 一般查询 列名比较复杂的情况  一般都是别名查询  [AS]  表关联查询
SELECT e.empno,ename,sal,comm,sal+IFNULL(comm,0) AS total FROM emp AS e;

4. 排序 order by

-- 4. 排序 order by  asc/desc 默认升序 asc
-- Arrays.sort()  集合
-- 查询所有员工  按照薪资进行升序或者降序排列
-- SELECT * FROM emp WHERE sal>1500 ORDER BY sal DESC;
-- 查询所有员工，按月薪降序排序，如果月薪相同时，按编号降序排序
SELECT * FROM emp ORDER BY sal DESC, empno DESC;

5. 聚合/分组函数

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

-- 只要使用了分组函数  结果有且只有1个
-- 1. 查询emp表里面的总记录数
-- SELECT COUNT(empno), count(*),count(1),COUNT(comm) FROM emp;
-- 2. 统计emp最高薪资 最低薪资  薪资总和 平均薪资
-- SELECT max(sal),min(sal),SUM(sal), sum(sal)/count(*),avg(sal) FROM emp;

6. 分组查询 group by

将很多记录 分成很多组进行统计。

查询每个部门的部门编号和每个部门的工资和：
查询每个部门的部门编号以及每个部门的人数：
查询每个部门的部门编号以及每个部门员工工资大于1500的人数：

-- 查询每个部门的部门编号和每个部门的工资和： sum(sal)
-- 查询每个部门的部门编号以及每个部门的人数：
-- SELECT deptno,count(*),sum(sal) AS total FROM emp GROUP BY deptno ORDER BY total DESC ;

SELECT gender,COUNT(*) FROM stu GROUP BY gender;

7. having

-- 查询工资总和大于9000的部门编号以及工资和：
-- > 1054 - Unknown column 'total' in 'where clause'
-- where 与 having区别

-- where 不能与分组函数结合使用  在group by 之前
-- having 可以与分组函数结合使用 在group by 之后
SELECT 
 deptno,count(*),sum(sal) AS total 
FROM emp 
GROUP BY deptno HAVING sum(sal)>9000 ORDER BY total DESC;

8. 关联查询

表与表之间的查询。涉及到多张表。

  1. 等值连接
-- 查询员工信息，要求显示员工号，姓名，月薪，部门名称
-- 14*4 笛卡尔集的数据
-- 过滤掉笛卡尔集的数据 WHERE
-- 关联2张表 至少带1个条件  3-->2  4-->3
-- > 1052 - Column 'deptno' in where clause is ambiguous
SELECT 
e.empno,e.ename,e.sal,d.dname
FROM emp AS e,dept AS d WHERE e.deptno=d.deptno;
  1. 不等值连接
-- 查询员工信息，要求显示：员工号，姓名，月薪，薪水的级别
SELECT
 e.empno,e.ename,e.sal,s.GRADE
FROM
emp e,salgrade 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
FROM
emp e,salgrade s,dept d
WHERE e.deptno = d.deptno AND e.sal BETWEEN s.LowSAL AND s.HISAL ORDER BY e.sal;
  1. 外连接
-- 2. 外连接
-- 案例:按部门统计员工数，部门号，部门名称，人数 count() emp
-- 40?  基准表是dept
-- SELECT
--  d.deptno,d.dname,count(*)
-- FROM
-- emp e,dept d
-- WHERE e.deptno=d.deptno GROUP BY d.deptno;
-- 1. 内连接 inner join on/where 等价于关联查询
-- SELECT
--  d.deptno,d.dname,count(*)
-- FROM  
--  emp e INNER JOIN dept d
-- ON e.deptno=d.deptno WHERE 1=1 GROUP BY d.deptno;

-- 2. 左外连接 left join  on  以左表为基准 右表没有的数据使用null/0进行填充
-- 3. 右外连接 right join on  以右表为基准  左表没有的数据使用null/0进行填充
SELECT
 d.deptno,d.dname,count(e.empno)
FROM  
 dept d LEFT JOIN emp e ON e.deptno=d.deptno 
GROUP BY d.deptno;
  1. 自连接

本表和本表关联: 通过别名，将同一张表视为多张表

-- 3. 自连接
-- 查询员工姓名和员工的老板的名称
-- emp: 看成2张表  一张员工表  一张老板表  要看条件
-- SELECT
--  e1.*, e2.ename,e2.empno
-- FROM 
--  emp e1, emp e2  WHERE e1.mgr=e2.empno;
--  SELECT
--  e1.*, e2.ename,e2.empno
-- FROM 
--  emp e1 LEFT JOIN emp e2  ON e1.mgr=e2.empno; 
 
-- 查询员工信息: 展示员工基本信息 emp,部门名称 dept 薪资级别 salgrade 上级领导的名称 emp
-- SELECT
--  e.*,d.dname,s.GRADE,e1.ename
-- FROM.
-- emp e,dept d,salgrade s,emp e1
-- WHERE e.deptno=d.deptno AND e.sal BETWEEN s.LowSAL AND s.HISAL AND e.mgr = e1.empno;

SELECT
 e.*,d.dname,s.GRADE,e1.ename
FROM
emp e LEFT JOIN emp e1 ON e.mgr =e1.empno
,dept d,salgrade s
WHERE e.deptno=d.deptno AND e.sal BETWEEN s.LowSAL AND s.HISAL ;

-- 查询用户购买的商品的信息
-- 用户--->购物车---->购物项---->商品
SELECT 
 u.*,g.good_name,g.good_price
FROM 
 mydb.tb_userinfo u,mydb.tb_cart c,mydb.tb_cartitem ct,mydb.tb_good g
WHERE
 u.cid=c.id AND c.id=ct.cid AND ct.gid=g.id;
  1. 子查询

查询的结果的是未知的。

-- 1. 子查询(嵌套查询)
-- 查询薪资=5000的员工的信息
-- SELECT * FROM emp HAVING sal=5000;
-- 查询薪资是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);
  1. 集合查询
union  VS  union all
-- mysql 里面去重的方式? distinct  group by  union
-- 一条sql 查询所有的用户信息
-- 每条查询都有各自的一个结果集---> 合并成1个结果集
-- 集合运算
-- 去除重复行记录
SELECT DISTINCT * from 
(SELECT  * FROM user0
UNION ALL
SELECT  * FROM user1
UNION ALL
SELECT  * FROM user2
UNION ALL
SELECT  * FROM user3) AS temp;


SELECT * FROM (SELECT  * FROM user0
UNION ALL
SELECT  * FROM user1
UNION ALL
SELECT  * FROM user2
UNION ALL
SELECT  * FROM user3) AS temp  GROUP BY id;

-- 查看行记录 
SELECT  * FROM user0
UNION
SELECT  * FROM user1
UNION
SELECT  * FROM user2
UNION
SELECT  * FROM user3;

9. 分页查询 limit

限定结果集。

网页上:
  上一页  当前页   下一页   最后一页  跳转到[]
  
-- 淘宝店铺:  每页展示商品的数量 pageSize=50 
-- 总商品的数量: 商品表的总记录数  select count(*) from 商品表; totalCount
-- 总页数:  int result =  totalCount/pageSize;
  int totalPage = (totalCount%pageSize)?result:(result+1);
  
-- 展示每页数据:
select * from 表 limit pageSize;  默认从第一条记录开始  查询pageSize条
-- 记录有索引: 0
select * from 表 limit start,pageSize; 从指定的记录开始查询 查pageSize条
-- 查询第一页的数据: select * from 商品表  limit 50;
-- 查询第2页的数据： select * from 商品表  limit 50,50;
-- 查询第3页的数据： select * from 商品表  limit 100,50;

-- 从第几条开始查询 取决去用户想看第几页的数据 page=用户提交过来的
-- 查询第n页的数据: select * from 商品表 limit  (page-1)*pageSize,pageSize;

-- 分页查询员工表的信息
-- 每页展示5条
SELECT COUNT(empno) from emp;
-- 3页

SELECT * from emp ORDER BY sal DESC LIMIT 0,5;
SELECT * from emp ORDER BY sal DESC LIMIT 5,5;
SELECT * from emp ORDER BY sal DESC LIMIT 10,5;

8. 函数

操作字符数据

-- 1. 字符串的函数
-- SELECT CONCAT('abc','-','d','f');
-- SELECT CONCAT(ename,'123'), UPPER(LOWER(ename)),length('字符串'),CHAR_LENGTH('字符串') FROM emp;
-- SELECT TRIM(' d  d   '), REPLACE(' d  d   ',' ',''),REPLACE('abca','a','d');
-- SELECT REPEAT('abc',3), SUBSTR('hello',2),SUBSTRING('hello',2,3);

-- SELECT INSERT('abcd',1,3,'aaaaa');
-- SELECT LPAD('hello',9,'abc');
-- SELECT rpad('hello',9,'abc');

操作数值数据

-- SELECT ABS(-10),round(RAND()*10) '随机数', ROUND(12.567),ROUND(12.567,2),truncate(12.567,2);
-- ceil不小于 >=最小整数
-- floor 不大于<=最大整数
-- SELECT ceil(-1.2),CEIL(1.2),FLOOR(-1.2),FLOOR(1.2) ,MOD(10,3);

操作日期数据

-- 3. 日期函数
-- 获得当前系统的时间
-- SELECT now(),SYSDATE(),CURRENT_TIMESTAMP();
-- SELECT CURRENT_DATE(),CURDATE();
-- SELECT CURRENT_TIME(),CURTIME();
-- YEAR(date) MONTH(date) DATE(expr)
-- 查询1981年入职的员工的信息
-- SELECT * FROM emp WHERE YEAR(hiredate) = 1981;

-- INSERT INTO aaa (name,create_time) VALUES ('张三',now());
-- INSERT INTO aaa (name,create_time) VALUES ('张三',UNIX_TIMESTAMP());

-- 格式化
-- SimpleDateFormat yyyy-MM-dd HH:mm:ss
-- SELECT FROM_UNIXTIME(1622864987,'%Y-%m-%d %H:%i:%S');

-- SELECT id,name,create_time, FROM_UNIXTIME(create_time,'%Y-%m-%d %H:%i:%S'), FROM_UNIXTIME(update_time,'%Y-%m-%d %H:%i:%S') FROM aaa;

-- IFNULL(expr1,expr2)
-- uuid()

-- id自增  上一次基础之上+1
-- SELECT LAST_INSERT_ID(); -- 上一次新增的id值
-- SELECT MD5('1234');

show VARIABLES LIKE '%STORAGE%';

9. 数据备份

1. 物理备份 C:\ProgramData\MySQL\MySQL Server 5.7\Data
2. 命令行备份
导出:
>mysqldump -h127.0.0.1 -uroot -proot test > D:\a.sql
C:\Users\DELL>mysqldump -h127.0.0.1 -uroot -proot test tb_userinfo > D:\b.sql

导入：
 C:\Users\DELL>mysql -uroot -p
Enter password: ****
 选中数据库:
   mysql> source D:\a.sql
   
3. 直接操作可视化客户端工具 navicat

10. 数据库引擎

有以下4种引擎:
可以通过: show variables like ‘%storage%’; 查看当前服务的默认引擎
也可以通过my.ini文件查询 默认的引擎:103  default-storage-engine=INNODB
MYISAM: 新增  
INNODB: 5+的版本都是使用的INNODB  数据  更加安全的 
memory: 基于内存的数据库 查询 
archive:
INNODB MYISAM
事务支持 支持 不支持
数据行锁定 支持(行锁) 不支持(表锁)
外键约束 支持 不支持
全文索引 支持(5.6 必须是英文) 支持
表空间大小 较小 较大，约2倍

MYISAM存储引擎:不支持事务机制，安全性低

​ 可以转换为压缩，只读表来节省空间

MYISAM存储引擎:它管理表有以下特征: 可以转换为压缩，只读表来节省空间
使用三个文件表示每个表：
格式文件->存储表结构的定义 mytable.frm
数据文件->存储表行的内容 mytable.MYD
索引文件->存储表上索引 mytable.MYT :索引相当于一本书的目录，缩小搜索范围，提高查询效率
对于一张表来说，主要是主键或者加了UNIQUE约束的字段上会自动创建索引

INNODB存储引擎：

​ -->最大特点：支持事务，保证数据安全，

​ -->效率不是很高，不能压缩不能转换为只读文件，不能很好节省存储空间

是mysql默认的存储引擎，同时也是一个重量级存储引擎
innodb支持事务，支持数据库崩溃后自动恢复机制
特征:
->每个innodb表在数据库目录中一.frm格式文件表示
->innodb表空间 tablespace被用于存储标的内容 表空间存储数据和索引
->提供一组用来提交记录事务性活动的日志文件
->用COMMIT提交、SAVEPOINT以及ROLLBACK回滚支持事务处理
->提供全ACID兼容 原子性 一致性 隔离性 持久性
->在MYSQL服务器崩溃后提供自动回复
->多版本(MVCC)和行级锁定
->支持外键以及引用的完整性，包括级联删除和更新

MEMORY存储引擎:

->数据和索引存储在内存中,查询效率高
->但不安全，关机之后数据消失，因为数据和索引是在内存当中

事务4层隔离级别

提交事务: commit 
回滚事务: rollback
事务对应的单词: transaction
mysql默认情况下是支持自动提交事务的-->每执行一条DML语句，就提交一次
如何关闭自动提交机制:先执行  start transaction ; 开启事务
				  执行DML语句
 				  rollback;之后之前执行DML就白写了
 				  commit;提交 就不能 回滚了 
事务特性：ACID ：
原子性：说明事务是最小工作单元，不可再分
一致性：要求同一个事务，操作要么全部成功，要么全部失败
隔离性：事务和事务之间具有隔离性
持久性：事务结束的保障，事务提交就是将没有保存在硬盘上的数据保存在硬盘上	

！！！隔离性 mysql默认隔离级别，可重复读
隔离级别: 1.读未提交:read uncommitted（最低隔离界别） 没提交也能读到
		 -->事务A可以读取到事务B未提交的数据  
----->		问题现象：出现脏读现象(读到了脏数据)
		 大多数都是二档起步
		 2.读已提交:read committed  真实的，提交才能读到
		 -->事务A只能读取到事务B提交之后的数据 ->解决了脏读现象      		 ----->      存在现象：不可重复读取数据
什么是不可重复读取数据：在事务开启之后，第一次读到的数据是3条，当前事务还没结束，可能第二次在读取的时候，读到的数据是4条，3不等于4称为不可重复读取
		 这种隔离级别是比较真实的数据，第一次读到的数据是绝对真实
		 oracle数据库默认：读已提交
		 3.可重复读:repeatable read   提交之后也读不到，永远读取得都是事务 								 开启时的数据，只要事务不结束，事务永远不变
		 事务A开启之后，不管是多久，每一次在事务A中读取的数据都是一致的，即使事务B将数据已经修改并且提交，事务A读取到的数据还是没发生改变，称为可重复读
		 解决了：不可重复读问题
----->   存在现象:幻影读
		 4.序列化/串行化:serializable(最高隔离级别)
		 每一次读取的数据都是最真实的，效率也是最慢的
	  最高隔离级别，效率最低，解决了所有问题，这种隔离级别表示事务排队，不能并发

索引

1.在任何数据库当中主键都会自动添加索引对象，id字段上自动有索引，因为id是主键，另外在mysql中，一个字段上有unique约束，也会自动创建索引对象。
2.在任何数据库当中，任何一张表的任何记录在硬盘存储上都有一个硬盘的物理存储编号
3.在mysql中，索引是一个单独的对象，在不同的存储引擎以不同的形式存在。

1. 索引介绍-优缺点

创建索引：create index 索引名 on 表名(列名1,...列名n)

1.索引是在数据库表的字段上添加的，是一种提高查询效率的机制。类似于书的目录，是为了缩小扫描范围而存在的机制。 
2.如果字段上没添加索引，MySQL会进行全扫描，会将对应字段的值全部比对一遍，效率低
3.MySQL查询两种方式：① 全表扫描  ② 根据索引检索


索引的优缺点:
索引虽好，但是不要滥用！
优点：
提高了数据查询的效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗！
缺点：
虽然索引大大提高了查询效率，但是同时会降低更新表的速度。如对表进行INSERT、DELETE、UPDATE操作的时候。因为索引也是一张表，该表保存了主键和索引字段，并指向实体表记录。所以MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件！

【1】什么是索引？
数组中有索引！根据索引来查询的时候，速度是非常快！

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

在数据库表中添加索引，类似于给表单独添加了一个目录！查询的时候先检索目录，根据目录快速定位！ 提高查询的速度！！！
【2】索引分类：
①、根据物理存储来划分：聚集索引和非聚集索引！
1.1：聚集索引：索引和实际数据存储的有按照顺序的存储！
--> 类似于新华字典按照拼音查询！
--> 每张表中只能有一个聚集索引！
--> 推荐给主键添加聚集索引！

1.2：非聚集索引：索引和实际数据存储是没有顺序！
--> 类似于新华字典按照偏旁查询！
--> 在表中可以多个非聚集索引！

②、根据实际应用来划分：
2.1：单值索引：只能给单个列添加索引！
2.2：组合索引：给多个列联合添加索引！
2.3：唯一索引：唯一单值索引 或者 唯一组合索引！  --> 创建唯一索引，必须保证的值是唯一的！
2.4：主键索引：给主键添加索引！

【3】比较添加了索引和未添加索引区别：根据查询次数！
       
【4】添加索引：
//创建单值索引
create index 索引名 on  table(列名);

//创建组合索引
create index 索引名 on  table(列名1，列名2);

//创建唯一索引  --> 前提：保证列的数据是惟一的
create unique index 索引名 on  table(列名);
create unique index 索引名 on  table(列名1，列名2);
【5】索引的优缺点？
//优点
①、提高查找的速度！   --> 降低检索的次数！

//缺点：
①、占用物理存储位置！
②、添加索引之后降低新增、修改和删除的执行效率！ 在新增、删除和修改的同时还要管理索引表！


哪些列适合添加索引？
①、根据某些列来搜索数据，适合添加索引！
②、表的数据量比较大！
③、用于排序的列比较添加索引！
④、搜索框！

哪些列不适合添加索引？
①、表的数量很小！
②、列的数据的重复量比较大！  比如：性别
③、经常性的添加、删除和修改的时候！  不怎么查询的时候！
【6】索引会失效：能不用就不要用！
-- ①、判断不等于的时候索引会失效！  <>  或者 !=
select * from tb_emp where emp_name != 'cat';
-- ②、使用模糊查询，如果以占位符来开头！索引也会失效！
EXPLAIN select * from tb_emp where emp_name like '%三%';
-- ③、没有使用添加索引的列！

视图

view：站在不同的角度去看待同一份数据

创建视图
create view 视图名字 as select * from 表名;
删除视图:
drop view 视图名字;
只有DQL语句（查询语句）才能以view的形式创建 select
创建完成后可以对、视图对象进行CRUD
作用:可以面向视图对象进行增删改查 ----> 对视图的操作会影响原表的数据
优点:可以简化开发，并且有利于后期维护,修改时候只需修改视图对象所映射的sql语句。
某条sql语句很长很麻烦，又需要在其他地方使用，就可以把这条sql语句以视图对象的形式创建。视图不是在内存当中，是存储在硬盘上的，不会消失。

数据导入和导出

数据导出(要在windows的dos命令窗口中):

导出数据库: mysqldump 数据库名 >D:文件名.sql -u root -p密码

导出某张表: mysqldump 数据库 名 表名 >D:文件名.sql -u root -p密码

数据导入:需要登录mysql数据服务器上，然后创建数据库和使用数据库最后

source D:文件名.sql

posted @   学JAVA的旅行者  阅读(21)  评论(0)  编辑  收藏  举报
相关博文：
· JAVA--面向对象
· JAVA----面向对象（1）概念，类与对象的创建，构造器，创建对象和内存分析，以及小总结
· JavaWeb--MySQL数据库
· MySQL数据库（重制版）
· 一文带你吃透MySQL数据库！
阅读排行：
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
点击右上角即可分享
微信分享提示