数据库开发(1)

day01-数据库基础

sql的通用语法

​ /**/ 多行注释 #单行注释 – 单行注释

DDL数据库操作

创建数据库

create database 数据库名 charset utf8

删除数据库

drop database 数据库名

创建表

	create table 数据库表名(列名 数据类型(长度) [约束],                    
			列名 数据类型(长度) [约束]);

​ 注意
​ 当写数据库名,表名,列名时如果关键字冲突,那名字要用``进行包裹

删除表

drop table 表名
		修改表结构(一般不用)
			alter table 表名 add 列名 类型(长度) [约束];

DML数据操作

插入数据

insert into 表名(列名,列名) values (具体的值,具体的值);
	注意
	如果列是varchar,在插入数据时,值用的是单引号,不能用双引号

删除数据

	delete from 表名 where 条件
				根据条件进行删除
	delete from 表名
				删除全部数据

修改数据

	update 表名 set 列名=值
			指定具体的列进行全部修改
	update 表名 set 列名 = 值 where 条件
			根据条件进行修改

约束

主键约束

primary key

添加方式

​ 1.可在创建表时,在字段后进行使用
​ 2.在constraint约束区域,指定主键约束
​ 什么是constraint区域

​ 格式
​ primary key (字段名)
​ 3.通过修改表结构的方式
​ 格式
​ ALTER TABLE 表名 ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)
​ 注意
​ 数据唯一,不能重复
​ 不能为null
​ 每张表都应该有一个主键列,代表一条数据

删除主键约束

​ ALTER TABLE 表名 DROP PRIMARY KEY->删除主键约束

联合主键

​ 多个列结合为一个主键
​ 数据不能完全一样,且不能为null

自增长约束

​ auto_increment
​ 通常和primary key进行使用
​ 注意
​ 自增长列,自动维护该列的数据,可自动编号,不能代表一条数据
​ 0和null可以进行自动维护,其他的值会根据给的值添加数据

truncate和delete的区别

​ 相同点
​ 都可以进行数据的删除
​ 不同点
​ 如果删除的是自增长的数据,用delete删除之后,添加数据还会按照之前的继续编号
​ 如果是truncate,会把表中和所有的数据进行删除,所以可以进行重新编号

非空约束和唯一约束

​ 非空约束
​ NOT NULL
​ 此列数据不能为null
​ 唯一约束
​ UNIQUE
​ 此列中的数据不可重复
​ 唯一约束和主键约束的区别
​ 相同点
​ 列中的数据都是唯一的
​ 不同点
​ 唯一约束:不能代表一条数据,而且表中可以都多个列都是唯一约束
​ 主键约束:能代表一条数据,而且一个表中必须有一个主键,只能有一个主键

查询

简单查询

​ a.select * from 表名 -> 查询所有列的数据
​ b.select 列名,列名 from 表名-> 查询数据,指定只展示哪几列
​ 注意
​ 查询出来的结果也是呈表格的形式展现,但是查询出来的表是一张伪表,这个表不能修改的,是只读的
​ as用来给表,列起别名
​ 例子
​ SELECT pname ‘商品名’,price ‘价格’ FROM product;
​ distinct(列名) 给指定列的值去重复
​ 例子
​ SELECT DISTINCT(price) FROM product;

条件查询

运算符

between…and…

​ 显示在某一区间的值(含头含尾)

in(set)

​ 显示在in列表的值
​ 注意,这个不是区间
​ 查询id为1,3,7的商品: id in(1,3,7)

like

​ 模糊查询,%代表零个或多个任意字符 代表一个字符
​ 查询姓张的人:name like ‘张%’
​ 查询商品名中带香的商品: pname like ‘%香%’
​ 查询第二个字为想的商品: like '想%’
​ 查询商品名为四个字的商品:like '__’

IS NULL

​ 判断是否为空

排序查询

select 列名 from 表名 order by 排序字段 asc|desc
				asc升序,默认
				desc降序

SQL语句执行顺序

书写sql语句关键字的顺序

​ select
​ from
​ where
​ group by
​ having
​ order by
执行顺序:
​ from
​ where
​ group by
​ having
​ select
​ order by
​ 先定位到要查询哪个表,然后根据什么条件去查,表确定好了,条件也确定好了,开始利用select查询
查询得出一个结果,在针对这个结果进行一个排序

day02-多表查询

课前回顾:
   1.数据库操作:
     a.创建库:create database 数据库名称 charset utf8
     b.切换库:use 数据库名
     c.删除库:drop database 数据库名字
   2.表操作:
     a.创建表:
       create table 表名(
         列名 数据类型 [约束],
         列名 数据类型 [约束],
         列名 数据类型 [约束]
       );
     b.删除表:
       drop table 表名
     c.修改表:
       alter table 表名 add/modify
   3.数据操作:
     a.插入数据:insert into 表名 (列名,列名) values (值,值)
               insert into 表名 (列名,列名) values (值,值),(值,值),(值,值)
               insert into 表名 values (值,值),(值,值),(值,值)->不指定列名,那么赋值时需要都写
     b.删除数据:
       delete from 表名 where 条件
     c.修改数据
       update 表名 set 列名 = 新值 where 条件
   4.约束:
     主键约束:primary key
     自增长约束:auto_increment
     非空约束:not null
     唯一约束:unique
   5.简单查询:
     select * from 表名  -> 查询所有数据,展示所有列
     select 列名 from 表名 -> 查询所有数据,展示指定的列
     select 列名 from 表名 where 条件->按照指定条件查询数据
   6.条件:
     a.> < >= <= !=(<>)
     b.列名 like '模糊条件'
     c.between... and...
     d.列名 in(数据)
     e.is null  is not null
     f.and  or 
   7.排序查询:
     order by 列名 排序规则(asc|desc)
         
     先查询后排序
         
今日重点:
  1.会单表查询
  2.会数据库的备份和还原(命令和点击)
  3.知道表和表之间的三种关系
  4.会多表查询(内连接,外连接,子查询)

第一章.单表查询

#创建商品表：
create table product(
	pid int primary key,
	pname varchar(20),
	price double
);
INSERT INTO product(pid,pname,price) VALUES(1,'联想',5000);
INSERT INTO product(pid,pname,price) VALUES(2,'海尔',3000);
INSERT INTO product(pid,pname,price) VALUES(3,'雷神',5000);
INSERT INTO product(pid,pname,price) VALUES(4,'JACK JONES',800);
INSERT INTO product(pid,pname,price) VALUES(5,'真维斯',200);
INSERT INTO product(pid,pname,price) VALUES(6,'花花公子',440);
INSERT INTO product(pid,pname,price) VALUES(7,'劲霸',2000);
INSERT INTO product(pid,pname,price) VALUES(8,'香奈儿',800);
INSERT INTO product(pid,pname,price) VALUES(9,'相宜本草',200);
INSERT INTO product(pid,pname,price) VALUES(10,'面霸',5);
INSERT INTO product(pid,pname,price) VALUES(11,'好想你枣',56);
INSERT INTO product(pid,pname,price) VALUES(12,'香飘飘奶茶',1);
INSERT INTO product(pid,pname,price) VALUES(13,'果9',1);

1.聚合查询

1.注意:纵向查询,结果是单值
2.关键字:聚合函数
  count(列名):统计表中有多少条数据
  sum(列名):对指定列求和
  avg(列名):对指定列做平均值
  max(列名):求指定列中的最大值
  min(列名):求指定列中的最小值
3.语法:
  select 聚合函数(列名) from 表名 [where 条件]

/*
  count(列名):统计表中有多少条数据
  
  统计product表中的总记录数
*/
SELECT COUNT(*) FROM product;
SELECT COUNT(pid) FROM product;
SELECT COUNT(0) FROM product;
SELECT COUNT(1) FROM product;

-- 查询所有商品的价格总和
SELECT SUM(price) FROM product;

-- 查询pid为1,3,7 商品的价格平均值
SELECT AVG(price) FROM product WHERE pid IN(1,3,7);

-- 查询商品的最高价格以及最低价格
SELECT MAX(price),MIN(price) FROM product;

/*
  创建student表
*/
CREATE TABLE student(
  sid INT,
  sname VARCHAR(10)
);

SELECT COUNT(sid) FROM student;-- 不包含null,根据某一列统计,如果此列中有null,是统计不出来的
SELECT COUNT(*) FROM student; -- 包含null
SELECT COUNT(0) FROM student; -- 包含null
SELECT COUNT(1) FROM student; -- 包含null

2.分组查询

1.关键字: group by 列名
2.语法:select 列名,列名... from 表名 group by 分组字段 having 分组条件
3.分组小技巧:
  相同的为一组
  不同的单独为一组
4.注意:分组查询都是和聚合函数结合使用

书写sql语句关键字的顺序
select 
from 
where 
group by 
having 
order by

执行顺序:
from 
where 
group by 
having 
select 
order by

先定位到要查询哪个表,然后根据什么条件去查,表确定好了,条件也确定好了,开始利用select查询
查询得出一个结果,在针对这个结果进行一个排序

-- 查询相同商品的价格总和->查询结果有问题
SELECT pname,SUM(price) FROM product;


-- 查询相同商品的价格总和-> 正确查询结果
SELECT pname,SUM(price) FROM product GROUP BY pname;

-- 查询相同商品的价格总和并排序

/*
  先执行查询,后执行排序
  当我们执行完查询之后,伪表中的价格列名叫 SUM(price)
  所以我们排序,也应该根据SUM(price)排序
*/
SELECT pname,SUM(price) FROM product GROUP BY pname ORDER BY SUM(price) DESC;

SELECT pname,SUM(price) 'newprice' FROM product GROUP BY pname ORDER BY newprice DESC;


-- 查询相同商品的价格总和,再展示出价格总和大于等于2000的商品

-- 根据sql关键字书写顺序来看,where 不能 放到 group by 后面,应该放到前面
SELECT pname,SUM(price) 'newprice' FROM product GROUP BY pname WHERE newprice>=2000;

-- 先走的where,当走where的时候newprice新名还没定义呢,所以报错
SELECT pname,SUM(price) 'newprice' FROM product WHERE newprice>=2000 GROUP BY pname;

-- 语法没问题,但是结果不对,因为先走where条件,当执行where的时候,"果9"还没价格相加呢
SELECT pname,SUM(price) 'newprice' FROM product WHERE price>=2000 GROUP BY pname;

/*
  根据上面需求发现
  我们应该执行完分组查询之后,在这个伪表的基础上进行条件筛选
  having 条件  -> 用在分组之后的条件筛选
*/

SELECT pname,SUM(price) 'newprice' FROM product GROUP BY pname HAVING newprice>=2000;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dJlj3T4S-1659014506244)(D:\Typora\bin\document\img\1656638501837.png)]

3.分页查询

1.语法: select * from 表名 limit m,n
       m:每页的起始位置  -> 表中的第一条数据看做是0位置
       n:每页显示条数
       
2.如何快速计算每页的起始位置  
 (当前页-1)*每页显示条数
 
3.分页其他参数计算

-- 后台计算出页码、页数(页大小)
-- 分页需要的相关数据结果分析如下,
-- 注意:下面是伪代码不用于执行
int curPage = 2; -- 当前页数
int pageSize = 5; -- 每页显示数量
int startRow = (curPage - 1) * pageSize; -- 当前页, 记录开始的位置(行数)计算

int totalSize = select count(*) from products; -- 记录总数量
int totalPage = Math.ceil(totalSize * 1.0 / pageSize); -- 总页数
                总页数 = (总记录数/每页显示条数)向上取整

SELECT * FROM product LIMIT 0,5;  -- 第一页

SELECT * FROM product LIMIT 5,5;  -- 第二页

SELECT * FROM product LIMIT 10,5;  -- 第三页

SELECT * FROM product LIMIT 15,5;  -- 第四页

第二章.数据库的备份与还原

1.用命令去操作数据库的备份与还原

1.1.命令操作备份

mysqldump  -u用户名 -p密码 数据库名>生成的脚本文件路径

生成的脚本文件路径:指定备份的路径,写路径时最后要指明备份的sql文件名,命令后不要加;

1.2.命令操作还原

mysql  -uroot  -p密码 数据库名 < 文件路径

注意:我们利用命令备份出来的sql文件中没有单独创建数据库的语句,所以如果利用命令去还原的话,需要我们自己手动先创建对应的库
    命令后不要加;

2.利用点击去操作数据库的备份与还原

2.1.利用点击去备份

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iVdtrkJy-1659014506246)(D:\Typora\bin\document\img\1606698676769.png)]

2.2.利用点击去还原

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HRgs09v1-1659014506246)(D:\Typora\bin\document\img\1606698901671.png)]

第三章.数据库三范式

好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。

1第一范式: 确保每列保持原子性

第一范（1NF）式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-spOsnwEq-1659014506247)(D:\Typora\bin\document\img\tu_11.png)]

如果不遵守第一范式，查询出数据还需要进一步处理（查询不方便）。遵守第一范式，需要什么字段的数据就查询什么数据（方便查询）

列名:详细地址手机号
    
    北京市昌平区北七家镇宏福苑小区19号楼1501087xxxx  -> 不行,因为数据可以拆分,不符合第一范式原子性

2 第二范式: 确保表中的每行都能唯一区分

第二范式（2NF)第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一的区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。

3 第三范式: 3NF:非主键字段不能相互依赖

比如student表，班级编号受人员编号的影响，如果在这个表中再插入班级的班主任、数学老师等信息，你们觉得这样合适吗？肯定不合适，因为学生有多个，这样就会造成班级有多个，那么每个班级的班主任、数学老师都会出现多条数据，而我们理想中的效果应该是一条班级信息对应一个班主任和数学老师，这样更易于我们理解，

第四章.多表之间的关系

1.一对一
  比如:人和身份证 -> 一个人对应一个身份证号;  

2.一对多:  分类表和商品表
  主表:分类表  -> 主表中的数据约束着商品表
  从表:商品表
  
  关系:从分类表出发,往商品表看  ->  一个分类对应多个商品   ->  一对多
      从商品往分类表看  ->  多个商品属于一个分类   -> 多对一
      
  结论:从主表往从表看是一对多,反过来是多对一,结果就是一对多关系
  
3.多对多: 商品表和订单表
  从商品表往订单表看:一个商品可以在多个订单中  -> 一对多
  从订单表往商品表看:一个订到中可以包含多个商品  -> 一对多
  
  正着看,反着看都是一对多,结果就是多对多

第五章.创建外键约束

格式:alter table 从表 add [constraint 外键名称(自定义)] foreign key 从表(外键列名) references 主表(主键列名)

1.一对多的表创建外键约束

    # 分类表->主表
    CREATE TABLE category (
      cid VARCHAR(32) PRIMARY KEY ,
      cname VARCHAR(50)
    );

    #商品表->从表
    CREATE TABLE products(
      pid VARCHAR(32) PRIMARY KEY ,
      pname VARCHAR(50),
      price DOUBLE,
      category_id VARCHAR(32)-- 外键  存储的是主表的主键内容
    );
    
        /*
     格式:alter table 从表 add [constraint 外键名称(自定义)] foreign key 从表(外键列名) references 主表(主键列名)    
    */
    
    ALTER TABLE products ADD CONSTRAINT cp1 FOREIGN KEY products(category_id) REFERENCES category(cid);

2.多对多的表创建外键约束

CREATE DATABASE 210906_java3 CHARSET utf8;    
    #商品表
    CREATE TABLE products(
      pid VARCHAR(32) PRIMARY KEY ,
      pname VARCHAR(50),
      price DOUBLE
    );
    
   
   # 订单表 
  CREATE TABLE `orders`(
  `oid` VARCHAR(32) PRIMARY KEY ,
  `totalprice` DOUBLE 	#总计
  );
   
  #订单项表->中间表
CREATE TABLE orderitem(
  pid VARCHAR(50),-- 商品id->外键
  oid VARCHAR(50)-- 订单id ->外键
);

/*
  商品表:主表
  订单项表:从表
*/

    /*
     格式:alter table 从表 add [constraint 外键名称(自定义)] foreign key 从表(外键列名) references 主表(主键列名)    
    */
    
ALTER TABLE orderitem ADD CONSTRAINT cp1 FOREIGN KEY orderitem(pid) REFERENCES products(pid);


/*
  主表:订单表
  从表:订单项表

*/
ALTER TABLE orderitem ADD CONSTRAINT cp2 FOREIGN KEY orderitem(oid) REFERENCES orders(oid);

第六章.多表查询

    # 分类表
    CREATE TABLE category (
      cid VARCHAR(32) PRIMARY KEY ,
      cname VARCHAR(50)
    );

    #商品表
    CREATE TABLE products(
      pid VARCHAR(32) PRIMARY KEY ,
      pname VARCHAR(50),
      price DOUBLE,
      flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
      category_id VARCHAR(32), -- 外键
      CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
    );
    #分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
#商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');

1.交叉查询

1.语法:
  select * from 表A,表B
2.注意:
  交叉查询查询的结果是有"笛卡尔乘积"现象

-- 交叉查询->结果有问题
SELECT * FROM category,products;

SELECT * FROM category,products WHERE category.cid = products.category_id;

SELECT * FROM category c,products p WHERE c.cid = p.category_id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iJwgCj0p-1659014506249)(D:\Typora\bin\document\img\1656656163181.png)]

2.内连接查询

1.关键字:inner join on -> inner可以省略
2.语法:
  显示内连接:select * from 表名A inner join 表B on 条件
  隐式内连接:select 列名 from 表A,表B where 条件

-- 查询具体的商品信息->隐式内连接
SELECT * FROM category c,products p WHERE c.`cid`=p.`category_id`;

-- 查询具体的商品信息->显示内连接
SELECT * FROM category c INNER JOIN products p ON c.`cid` = p.`category_id`;

-- 用显示内连接的方式查询"化妆品"的商品信息

-- on 条件 and 条件   -> 相当于on后面的是一个大条件
SELECT * FROM category c INNER JOIN products p ON c.`cid` = p.`category_id` AND cname = '化妆品';


-- on 条件 where 条件  -> 先根据on的条件查询,然后在根据where的条件过滤
SELECT * FROM category c INNER JOIN products p ON c.`cid` = p.`category_id` WHERE cname = '化妆品';

3.外连接

1.关键字:  outer join on -> outer可以省略
2.分类:
  左外连接:left join on
  右外连接:right join on
3.语法:
  select * from 左表 left/right join 右表 on 条件
4.如何分清谁是左表,谁是右表
  join左边的是左表
  join右表的是右表
  
5.左外连接,右外连接,内连接区别:
  a.左外连接:查询的是和右表的交集,以及左表的全部
  b.右外连接:查询的是和左表的交集,以及右表的全部
  c.内连接:只查询交集

-- 查询所有的商品信息 左外连接

/*
  左表:category
  右表:products
  
  a.左外连接:查询的是和右表的交集,以及左表的全部
*/
SELECT * FROM category c LEFT JOIN products p ON c.`cid` = p.`category_id`;



-- 查询所有的商品信息 右外连接
/*
  左表:category
  右表:products
  
  右外连接:查询的是和左表的交集,以及右表的全部
*/
SELECT * FROM category c RIGHT JOIN products p ON c.`cid` = p.`category_id`;

-- 查询所有的商品信息 内连接
SELECT * FROM category c,products p WHERE c.`cid` = p.`category_id`;

4.union联合查询实现全外连接查询（了解）

首先要明确，联合查询不是多表连接查询的一种方式。联合查询是将多条查询语句的查询结果合并成一个结果并去掉重复数据。
全外连接查询的意思就是将左表和右表的数据都查询出来，然后按照连接条件连接
    
只要将两个结果一连接,左表和右表没有交叉的部分也就都查出来了

1.union的语法:
  查询语句1 union 查询语句2 union 查询语句3 ...

-- 联合查询
SELECT * FROM category c LEFT JOIN products p ON c.`cid` = p.`category_id`
UNION
SELECT * FROM category c RIGHT JOIN products p ON c.`cid` = p.`category_id`;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0byN28hY-1659014506250)(D:\Typora\bin\document\img\1656658321369.png)]

5.子查询

1.概述:一条查询语句作为另外一条查询语句的条件使用

-- 查询products表中'化妆品'的商品信息
SELECT * FROM products WHERE category_id = 'c003';

/*
  1.单纯的看products这个表,我们不确定c003就是化妆品
  2.c003其实是从category中拿来的
  3.分析:
    光看c003不确定是化妆品
    但是"化妆品"这个三个字是固定的吧
    
  4.所以:我们可以先根据"化妆品"这三个字查询出对应的id
         然后将查询出来的id当做条件使用
*/

-- 先根据"化妆品"查询对应的id
SELECT cid FROM category WHERE cname = '化妆品';

SELECT * FROM products WHERE category_id = (SELECT cid FROM category WHERE cname = '化妆品');

-- 查询products表中化妆品和家电的商品信息
SELECT * FROM products WHERE category_id IN ('c001','c003');

-- 我们单纯的看products表也不知道c001 和 c003 具体代表啥,所以我们可以先根据化妆品和家电查询出对应的id
SELECT cid FROM category WHERE cname IN ('家电','化妆品');

SELECT * FROM products WHERE category_id IN (SELECT cid FROM category WHERE cname IN ('家电','化妆品'));

6.子查询作为伪表使用

1.查询出来的结果是以表的形式呈现,这个表是只读权限,这个查询出来的表就是一张伪表

-- 查询化妆品的所有商品信息
SELECT * FROM category c,products p WHERE c.`cid`=p.`category_id` AND cname = '化妆品';

-- 先将化妆品从category中查询出来
SELECT * FROM category WHERE cname = '化妆品';

SELECT * FROM (SELECT * FROM category WHERE cname = '化妆品') c,products p WHERE c.`cid`=p.`category_id`


-- -- 查询所有化妆品和家电的商品信息
SELECT * FROM category c,products p WHERE c.`cid`=p.`category_id` AND cname IN ('家电','化妆品');


-- 先从category中查询出化妆品和家电
SELECT * FROM category WHERE cname IN('家电','化妆品');

SELECT * FROM (SELECT * FROM category WHERE cname IN('家电','化妆品')) c,products p WHERE c.`cid`=p.`category_id`;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vdoS7Plu-1659014506252)(D:\Typora\bin\document\img\1656661128432.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mc5LeSPl-1659014506253)(D:\Typora\bin\document\img\1656661279621.png)]

第七章.mysql函数

mysql中的函数都是针对指定列中的数据进行操作

1.字符串函数

1.1.1 字符串函数列表概览

函数	用法
CONCAT(S1,S2,…,Sn)	连接S1,S2,…,Sn为一个字符串
CONCAT_WS(separator, S1,S2,…,Sn)	连接S1一直到Sn，并且中间以separator作为分隔符
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母
TRIM(s)	去掉字符串s开始与结尾的空格
SUBSTRING(s,index,len)	返回从字符串s的index位置其len个字符

1.1.2 环境准备

-- 用户表
CREATE TABLE t_user (
  id int(11) NOT NULL AUTO_INCREMENT,
  uname varchar(40) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  sex int(11) DEFAULT NULL,
  PRIMARY KEY (id)
);

insert  into t_user values (null,'zs',18,1);
insert  into t_user values (null,'ls',20,0);
insert  into t_user values (null,'ww',23,1);
insert  into t_user values (null,'zl',24,1);
insert  into t_user values (null,'lq',15,0);
insert  into t_user values (null,'hh',12,0);
insert  into t_user values (null,'wzx',60,null);
insert  into t_user values (null,'lb',null,null);

1.1.3 字符串连接函数

字符串连接函数主要有2个：

函数或操作符	描述
concat(str1, str2, …)	字符串连接函数，可以将多个字符串进行连接
concat_ws(separator, str1, str2, …)	可以指定间隔符将多个字符串进行连接；

练习1：使用concat函数显示出 你好uname 的结果

/*
  concat(str1, str2, ...)
  字符串连接函数，可以将多个字符串进行连接
*/
SELECT CONCAT('a','b');

-- 拼接字符串练习 练习1：使用concat函数显示出 你好uname 的结果

SELECT CONCAT('你好',uname),age FROM t_user;

练习2：使用concat_ws函数显示出 你好,uname 的结果

SELECT CONCAT_WS(',','你好',uname),age FROM t_user;

1.1.4 字符串大小写处理函数

字符串大小写处理函数主要有2个：

函数或操作符	描述
upper(str)	得到str的大写形式
lower(str)	得到str的小写形式

练习1： 将字符串 hello 转换为大写显示

-- 将hello转成大写
SELECT UPPER('hello');
-- 查询t_user,uname变成大写
SELECT UPPER(uname),age FROM t_user;

练习2：将字符串 heLLo 转换为小写显示

-- 将HELLO转成小写
SELECT LOWER('HELLO');
-- 查询t_user,uname变成小写
SELECT LOWER(uname),age FROM t_user;

1.1.5 移除空格函数

可以对字符串进行按长度填充满、也可以移除空格符

函数或操作符	描述
trim(str)	将str两边的空白符移除

练习1： 将用户id为8的用户的姓名的两边空白符移除

SELECT id,TRIM(uname),age,sex FROM t_user WHERE id = 8;

1.1.6 子串函数

字符串也可以按条件进行截取，主要有以下可以截取子串的函数;

函数或操作符	描述
substr()、substring()	获取子串： 1：substr(str, pos) 、substring(str, pos)； 2：substr(str, pos, len)、substring(str, pos, len)

/*
  功能一样:
    substr(str, pos)
    substring(str, pos)
    
    str:要截取的字符串
    pos:从第几个字符开始截取
    
  功能一样  
  substr(str, pos, len)
  substring(str, pos, len)  
  
  str:要截取的字符串
  pos:从第几个字符开始截
  len:截多少个
*/

SELECT SUBSTR('abcdefg',2);

SELECT SUBSTR('abcdefg',1,3);

练习1：获取 hello,world 从第二个字符开始的完整子串

SELECT SUBSTR('hello,world',2);

练习2：获取 hello,world 从第二个字符开始但是长度为4的子串

SELECT SUBSTR('hello,world',2,4);

2 数值函数

2.1. 数值函数列表

函数	用法
ABS(x)	返回x的绝对值
CEIL(x)	返回大于x的最小整数值
FLOOR(x)	返回小于x的最大整数值
RAND()	返回0~1的随机值
POW(x,y)	返回x的y次方

2.2. 常用数值函数练习

练习1： 获取 -12 的绝对值

SELECT ABS(-12);

练习2： 将 -11.2 向上取整

SELECT CEIL(-11.2);

练习3： 将 1.6 向下取整

SELECT FLOOR(1.6);

练习4： 获得2的2次幂的值

SELECT POW(2,2);

练习5： 获得一个在0-100之间的随机数

SELECT RAND()*100;

3.日期函数

3.1 日期函数列表

函数	用法
CURDATE() 或 CURRENT_DATE()	返回当前日期 年月日
CURTIME() 或 CURRENT_TIME()	返回当前时间 时分秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	返回当前系统日期时间
DATEDIFF(date1,date2) / TIMEDIFF(time1, time2)	返回date1 - date2的日期间隔 / 返回time1 - time2的时间间隔

3.2 常用日期函数的练习

练习1：获取当前的日期

SELECT CURDATE();

练习2： 获取当前的时间（仅仅需要时分秒）

SELECT CURTIME();

练习3： 获取当前日期时间（包含年月日时分秒）

SELECT NOW();

练习4: 获取到五一还有多少天

SELECT DATEDIFF('2022-05-01',CURDATE());

第八章.sql练习

1.创建数据库

CREATE DATABASE mytest01;
USE mytest01;

2.创建表以及添加数据

# 创建部门表dept  部门表中包含 部门id 部门名称  
CREATE TABLE dept(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
)

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');  

# 创建员工表
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),   -- 性别
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)
)  

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('小松松','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('鱼小鱼','女',3600,'2015-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('小霈霈','男',8000,'2013-12-02',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('亮仔','男',5000,'2017-11-11',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('坤仔','男',8000,'2012-02-02',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('福姐','女',6500,'2011-09-12',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('熊姐','女',10500,'2018-12-02',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猛哥','男',9500,'2016-07-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('栋栋','男',8500,'2018-06-28',2);

3.练习

-- 1.查询员工和部门的名字
SELECT emp.`name`, dept.`name` FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

-- 2.查询鱼小鱼的信息，显示员工id，姓名，性别，工资和所在的部门名称(使用显式内连接)
SELECT * FROM emp e INNER JOIN dept d ON e.`dept_id` = d.`id` WHERE e.`name`='鱼小鱼';

-- 3.将上面查到的内容 表头使用别名的形式展示 比如显示id为员工id  name为姓名 等
SELECT e.id 编号,e.name 姓名,e.gender 性别,e.salary 工资,d.name 部门名字 FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE e.name='鱼小鱼';

-- 4.在部门表中增加一个销售部 
INSERT INTO dept (NAME) VALUES ('销售部');
SELECT * FROM dept;

-- 5.查询所有的部门信息关联查询出该部门中的所有员工信息 
SELECT * FROM dept d LEFT JOIN emp e ON d.`id` = e.`dept_id`;

-- 6.查询所有的部门信息关联查询出该部门中的所有员工的名字  部门 以及 工资 
SELECT e.name 姓名,d.name 部门, e.salary 工资 FROM dept d LEFT JOIN emp e ON d.id = e.dept_id;

-- 7.统计出 每个部门的员工人数   查询显示 部门名称 人数 
SELECT d.name 部门,COUNT(e.name) 人数 FROM dept d LEFT JOIN emp e ON d.id = e.dept_id GROUP BY d.name;

-- 8.统计出 每个部门员工 平均薪资 按照 薪资排序 查询显示 部门名称 平均薪资 
SELECT d.name 部门,AVG(e.salary) 人数 FROM dept d LEFT JOIN emp e ON d.id = e.dept_id GROUP BY d.name ORDER BY salary;

-- 9.统计出，每个部门的平均薪资 按照薪资排序 并且筛选出平均薪资>7000的部门
SELECT d.name 部门,AVG(e.salary) 人数 FROM dept d LEFT JOIN emp e ON d.id = e.dept_id 
GROUP BY d.name HAVING AVG(e.salary)>7000 ORDER BY salary;

-- 10.查询最高工资是多少
SELECT MAX(salary) FROM emp;

-- 11.根据最高工资到员工表查询到对应的员工信息
SELECT * FROM emp WHERE salary=(SELECT MAX(salary) FROM emp)

-- 12.查询工资小于平均工资的员工有哪些
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

-- 13.查询工资大于5000的员工，来自于哪些部门的名字  
 SELECT dept.name FROM dept WHERE dept.id IN (SELECT dept_id FROM emp WHERE salary > 5000

-- 14.查询开发部与财务部所有的员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME IN('开发部','财务部'));

-- 15.查询出2011年以后入职的员工信息，包括部门名称
SELECT * FROM dept d, (SELECT * FROM emp WHERE join_date > '2011-1-1') e WHERE e.dept_id = d.id;

day03.函数_jdbc

课前回顾:
 1.条件查询
   a.聚合函数查询:结果为单值,纵向查询
     count  sum  max  min avg
   b.分组查询:要和聚合函数结合使用
     group by 分组列名  having 条件
   c.分页查询:
     select * from 表名 limit m,n
     a.m:代表每页的起始位置
     b.n:每页显示条数
     c.总记录数:count统计
     d.总页数: Math.ceil(总记录数/每页显示条数)
     e.起始位置:(当前页-1)*每页显示条数
 2.三范式:
   a.原子性
   b.唯一性
   c.不可依赖性
 3.多表关系:
   一对一
   一对多
   多对多
 4.多表查询:
   a.交叉查询:select * from 表A,表B->有笛卡尔乘积
   b.内连接:inner join on 条件 -> inner可省略
   c.外连接:outer join on 条件-> outer可以省略
     左外连接:left join
     右外连接:right join
     怎么区分左表和右表:看join
     左外连接和右外连接区别还有内连接:
        左:查询的是和右表的交集,以及左表的全部
        右:查询的是和左表的交集,以及右表的全部
        内:只查交集
   d.子查询:一条查询语句作为另外一条查询语句的条件使用
  
  5.强调一点:给列或者表起别名的时候,不推荐用单引号 

今日重点:
  从第三章开始都是重点

第一章.MySQL的常用函数

-- 用户表
CREATE TABLE t_user (
  id INT(11) NOT NULL AUTO_INCREMENT,
  uname VARCHAR(40) DEFAULT NULL,
  age INT(11) DEFAULT NULL,
  sex INT(11) DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT  INTO t_user VALUES (NULL,'zs',18,1);
INSERT  INTO t_user VALUES (NULL,'ls',20,0);
INSERT  INTO t_user VALUES (NULL,'ww',23,1);
INSERT  INTO t_user VALUES (NULL,'zl',24,1);
INSERT  INTO t_user VALUES (NULL,'lq',15,0);
INSERT  INTO t_user VALUES (NULL,'hh',12,0);
INSERT  INTO t_user VALUES (NULL,'wzx',60,NULL);
INSERT  INTO t_user VALUES (NULL,'lb',NULL,NULL);

1.流程函数_判断

函数	用法
IF(比较,t ,f)	如果比较是真，返回t，否则返回f
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN 条件1 THEN result1 WHEN 条件2 THEN result2 … [ELSE resultn] END	相当于Java的if…else if…else…

• 练习1：获取用户的姓名、性别，如果性别为1则显示1，否则显示0；要求使用if函数查询：

  SELECT uname,age,IF(sex=1,1,0) FROM t_user;
  
  
  

• 练习2：获取用户的姓名、性别，如果性别为null则显示为0；要求使用ifnull函数查询：

  SELECT uname,age,IFNULL(sex,0)  FROM t_user;
  
  

• 练习3：如果age<=12,显示儿童,如果age<=18,显示少年,如果age<=40,显示中年,否则显示老年

  SELECT uname,
  CASE WHEN age<=12 THEN '儿童' 
  WHEN age<=18 THEN '少年' 
  WHEN age<=40 THEN '中年' 
  ELSE '老年' END `年龄段`,IF(sex=1,1,0) `性别` FROM t_user;
  
  

第二章 DCL语句

我们现在默认使用的都是root用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Bt78Mc3W-1659014506255)(D:\Typora\bin\document\img\1656724085173.png)]

2.1 创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

关键字说明：

1.用户名:创建的用户名
2.主机名:指定该用户在哪个主机上可以登录,如果是本地用户,可以用'localhost',如果想让该用户可以任意远程主机登录,可以使用通配符%
3.密码:该用户登录的密码,密码可以为空,如果为空,该用户可以不输入密码就可以登录mysql

具体操作：

-- user1用户只能在localhost这个IP登录mysql服务器
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
-- user2用户可以在任何电脑上登录mysql服务器
CREATE USER 'user2'@'%' IDENTIFIED BY '123';

2.2 授权用户

用户创建之后，基本没什么权限！需要给用户授权
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lTsRKVoe-1659014506256)(D:\Typora\bin\document\img\DCL02.png)]

授权格式：

GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';

关键字说明：

a.GRANT:授权关键字
b.授予用户的权限,比如  'select' 'insert' 'update'等,如果要授予所有的权限,使用 'ALL'
c.数据库名.表名:该用户操作哪个数据库的哪些表,如果要授予该用户对所有数据库和表的相关操作权限,就可以用*表示: *.*
d.'用户名'@'主机名':给哪个用户分配权限

具体操作：

1. 给user1用户分配对test这个数据库操作的权限

   GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON test.* TO 'user1'@'localhost';
   
   
   

   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fdumv7cZ-1659014506257)(D:\Typora\bin\document\img\DCL03.png)]

2. 给user2用户分配对所有数据库操作的权限

   GRANT ALL ON *.* TO 'user2'@'%';
   
   
   

   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oO8siGkG-1659014506258)(D:\Typora\bin\document\img\DCL04.png)]

2.3 撤销授权

REVOKE  权限1, 权限2... ON 数据库.表名 FROM '用户名'@'主机名';

具体操作：

• 撤销user1用户对test操作的权限

  REVOKE ALL ON test.* FROM 'user1'@'localhost';
  
  
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ciO9CKAu-1659014506259)(D:\Typora\bin\document\img\DCL05.png)]

2.4 查看权限

SHOW GRANTS FOR '用户名'@'主机名';

具体操作：

• 查看user1用户的权限

  SHOW GRANTS FOR 'user1'@'localhost';
  
  
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-a8KSpdbn-1659014506261)(D:\Typora\bin\document\img\DCL06.png)]

2.5 删除用户

DROP USER '用户名'@'主机名';

具体操作：

• 删除user2

   DROP USER 'user2'@'%';
  
  
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pNJat4TQ-1659014506270)(D:\Typora\bin\document\img\DCL07.png)]

/*
  1.创建新用户->分配一个用户名和密码进行mysql登录 
    CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
    
  a.用户名:创建的用户名
  b.主机名:指定该用户在哪个主机上可以登录,如果是本地用户,可以用'localhost',如果想让该用户可以任意远程主机登录,可以使用通配符%
  c.密码:该用户登录的密码,密码可以为空,如果为空,该用户可以不输入密码就可以登录mysql
  
*/
-- user1用户只能在localhost这个IP登录mysql服务器
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
-- user2用户可以在任何电脑上登录mysql服务器
CREATE USER 'user2'@'%' IDENTIFIED BY '123';

/*
  2.给新分配的用户分配权限->让用户能操作哪些库,以及对数据可以进行哪些操作
    GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';
    
  a.GRANT:授权关键字
  b.授予用户的权限,比如  'select' 'insert' 'update'等,如果要授予所有的权限,使用 'ALL'
  c.数据库名.表名:该用户操作哪个数据库的哪些表,如果要授予该用户对所有数据库和表的相关操作权限,就可以用*表示: *.*
  d.'用户名'@'主机名':给哪个用户分配权限
*/
-- 给用户1分配权限
GRANT SELECT ON `220526_java_03`.* TO 'user1'@'localhost';

-- 给用户2分配权限
GRANT ALL ON *.* TO 'user2'@'%';

/*
  3.删除用户
    DROP USER '用户名'@'主机名';
    
    
    用户名和主机名要和创建时的保持一致
*/
DROP USER 'user1'@'localhost';

2.6 修改用户密码

2.6.1 修改管理员密码

mysqladmin -uroot -p password 新密码  -- 新密码不需要加上引号

注意：需要在未登陆MySQL的情况下操作。

具体操作：

mysqladmin -uroot -p password root
输入老密码

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9JU0owvw-1659014506272)(D:\Typora\bin\document\img\DCL08.png)]

2.6.2 修改普通用户密码

set password for '用户名'@'主机名' = password('新密码');

注意：需要在登陆MySQL的情况下操作。

具体操作：

set password for 'user1'@'localhost' = password('666666');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7TDgSNOw-1659014506273)(D:\Typora\bin\document\img\DCL09.png)]