MySql

最新推荐文章于 2023-07-29 19:35:13 发布
最新推荐文章于 2023-07-29 19:35:13 发布
阅读量222 收藏 5
点赞数 1
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43838444/article/details/118566033
版权

1.数据库介绍

1.1.数据库概述

数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修改，删除及查询操作。
随着互联网的高速发展，大量的数据在不断的产生，伴随而来的是如何高效安全的存储数据和操作数据，而这一问题成为了信息时代的一个非常大的问题。
使用数据库可以高效的有条理的储存数据：

  1. 可以结构化存储大量的数据，
  2. 可以有效的保持数据的一致性、完整性。

1.2.常见数据库

数据库又分为关系型数据库和非关系型数据库

数据库类型数据库介绍
关系型数据库MYSQL开源免费的数据库，中型的数据库.已经被Oracle收购了.MySQL6.x版本也开始收费。
Oracle收费的大型数据库，Oracle公司的产品。Oracle收购SUN公司，收购MYSQL。
DB2IBM公司的数据库产品,收费的。常应用在银行系统中.
SQLServerMicroSoft 公司收费的中型的数据库。C#、.net等语言常使用。
SQLite嵌入式的小型数据库，应用在手机端。
非关系型数据库Redis是一个小而美的数据库，主要用在key-value 的内存缓存，读写性能极佳
HBaseHBase是列式数据库，目标是高效存储大量数据
MongoDBMongoDB是文档型数据库，非常接近关系型数据库的。

常用数据库：MYSQL，Oracle，Redis，Hbase

2.MySql数据库安装和使用

2.1.MySql安装

mysql安装

2.2.登录MySQL数据库

MySQL是一个需要账户名密码登录的数据库，登陆后使用，它提供了一个默认的root账号，使用安装时设置的密码即可登录，有两种登录方式。
方式1:

mysql -u用户名 -p密码

或者

mysql -u用户名 -p   回车输入密码

方式2:

mysql  --host=ip地址 --user=用户名 --password=密码

3.SQL语句

3.1.SQL概述

结构化查询语言(Structured Query Language)简称SQL，是关系型数据库管理系统都需要遵循的规范，是数据库认识的语句。不同的数据库生产厂商都支持SQL语句，但都有特有内容。
普通话：各数据库厂商都遵循的ISO标准。
方言：数据库特有的关键字。
在这里插入图片描述

3.2.SQL语句介绍

结构化查询语言(Structured Query Language)简称SQL，是关系型数据库管理系统都需要遵循的规范，是数据库认识的语句。不同的数据库生产厂商都支持SQL语句，但都有特有内容。
普通话：各数据库厂商都遵循的ISO标准。
方言：数据库特有的关键字。

3.3.SQL语句分类

  • SQL分类：
    • 数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等
    • 数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字：insert，delete，update等
    • 数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。
    • 数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select，from，where等

3.4.SQL通用语法

  • SQL语句可以单行或多行书写，以分号结尾
  • 可使用空格和缩进来增强语句的可读性
  • MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
    例如：SELECT * FROM user。
  • 同样可以使用/**/的方式完成注释 – #
  • MySQL中的我们常使用的数据类型如下
分类类型名称说明
整数类型tinyInt很小的整数
smallint小的整数
mediumint中等大小的整数
int(integer)普通大小的整数
小数类型float单精度浮点数
double双精度浮点数
decimal（m,d）压缩严格的定点数 decimal(10,2)
日期类型yearYYYY 1901~2155
timeHH:MM:SS -838:59:59~838:59:59
dateYYYY-MM-DD 1000-01-01~9999-12-3
datetimeYYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
timestampYYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC
文本、二进制类型char(m)m为0~255之间的整数定长 char(10) tom
varchar(m)m为0~65535之间的整数变长 varchar(10) tom
tinyblob允许长度0~255字节
blob允许长度0~65535字节
mediumblob允许长度0~167772150字节
longblob允许长度0~4294967295字节
tinytext允许长度0~255字节
text允许长度0~65535字节
mediumtext允许长度0~167772150字节
longtext允许长度0~4294967295字节
varbinary(m)允许长度0~M个字节的变长字节字符串
binary(m)允许长度0~m个字节的定长字节字符串

4.DDL之数据库操作：database

4.1.创建数据库

格式:

create database 数据库名;    #直接创建数据库，如果存在则报错
create database if not exists bigdata_db;  #如果数据库不存在则创建
create database 数据库名 character set 字符集; #创建数据库时设置字符集

实例：

create database bigdata_db;

4.2.查看MySQL服务器中的所有的数据库:

show databases;

4.3.删除数据库

格式：

drop database 数据库名称

实例:

drop database bigdata_db;

4.4.使用数据库

  • 选择数据库
    格式:
 use   数据库名字

实例:

use bigdata_db;
  • 查看正在使用的数据库:
select database();

5.DDL之表操作：table

5.1.创建表

  • 格式：
create table if not exists 表名(
字段名  类型(长度) [约束],
字段名  类型(长度) [约束],
   ...
);

类型：

      varchar(n)字符串
      int   整形
	  double浮点
      date   时间
      timestamp时间戳

约束：
primary key 主键，被主键修饰字段中的数据，不能重复、不能为null。

实例:

###创建分类表
CREATE TABLE category ( 
cid varchar(20) primary key,#分类ID 
cname VARCHAR(100) #分类名称
);

5.2.查看表

  • 查看数据库中的所有表：
    格式:show tables;
  • 查看表结构：
    格式：desc 表名;
    例如：desc sort;

5.3.删除表

  • 格式：drop table 表名;

  • 实例:

drop table category;

5.4.修改表结构格式：

  • alter table 表名 add 列名 类型(长度) [约束];
    作用：修改表添加列.
    例如：
#为分类表添加一个新的字段为分类描述 varchar(20)
ALTER TABLE category ADD `desc` VARCHAR(20);
  • alter table 表名 change 旧列名 新列名 类型(长度) 约束;
    作用：修改表修改列名.
    例如：
#为分类表的分类描述字段更换为description  varchar(30)
ALTER TABLE category CHANGE `desc`description VARCHAR(30);
  • alter table 表名 drop 列名;
    作用：修改表删除列.
    例如：
#删除分类表中description这列
ALTER TABLE category DROP description;
  • rename table 表名 to 新表名;
    作用：修改表名
    例如：
#为分类表category改名成 category2
RENAME TABLE `category` TO category2;

6.DML数据操作语言

6.1.插入表记录：insert

  • 语法：
-- 向表中插入某些字段
insert into 表 (字段1,字段2,字段3..) values(值1,值2,值3..);

-- 向表中插入所有字段,字段的顺序为创建表时的顺序
insert into 表 values(值1,值2,值3..)

  • 注意：

  • 值与字段必须对应，个数相同，类型相同

  • 值的数据大小必须在字段的长度范围内

  • 除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）

  • 如果要插入空值，可以不写字段，或者插入 null。

  • 例如：

INSERT INTO category(cid,cname) VALUES('c001','电器'); 
INSERT INTO category(cid,cname) VALUES('c002','服饰'); 
INSERT INTO category(cid,cname) VALUES('c003','化妆品'); 
INSERT INTO category(cid,cname) VALUES('c004','书籍');
INSERT INTO category(cid) VALUES('c005'); 
insert into category values('06','玩具'),('07','蔬菜');

6.2.更新表记录：update

用来修改指定条件的数据，将满足条件的记录指定列修改为指定值

  • 语法：
-- 更新所有记录的指定字段
update 表名 set 字段名=值,字段名=值,...;
-- 更新符号条件记录的指定字段
update 表名 set 字段名=值,字段名=值,... where 条件;

实例:

update category set cname  = '家电';  #将所有行的cname改为'家电'
update category set cname  = '水果' where cid = 'c001'; #将cid为c001的cname修改为水果

注意：
列名的类型与修改的值要一致.
修改值得时候不能超过最大长度.
除了数值类型外，其它的字段类型的值必须使用引号引起

6.3.删除记录：delete

逻辑删除
磁盘删除

  • 语法：
delete from 表名 [where 条件];
或者
truncate table 表名;

实例:

delete from category where cid = '005'; #删除cid为005的纪录
truncate category;  #清空表数据

注意:
delete 一条一条删除，不清空auto_increment记录数。
truncate 直接将表删除，重新建表，auto_increment将置为零，从新开始。

7.SQL约束

7.1.主键约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。

7.1.1.添加主键约束

  • 方式一：创建表时，在字段描述处，声明指定字段为主键：
CREATE TABLE Persons1
(
 Id int PRIMARY KEY,
 LastName varchar(255),
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255)
)
  • 方式二：创建表时，在constraint约束区域，声明指定字段为主键：
  • 格式：[constraint 名称] primary key (字段列表)
  • 关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用。
  • 字段列表需要使用小括号括住，如果有多字段需要使用逗号分隔。声明两个以上字段为主键，我们称为联合主键。
CREATE TABLE Persons2
(
  FirstName varchar(255),
  LastName  varchar(255),
  Address   varchar(255),
  City      varchar(255),
  CONSTRAINT pk_PersonID PRIMARY KEY (FirstName, LastName)
)

或

CREATE TABLE Persons3
(
  FirstName varchar(255) ,
  LastName  varchar(255),
  Address   varchar(255),
  City      varchar(255)
)

7.1.2.删除主键约束

如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：

ALTER TABLE Persons DROP PRIMARY KEY;

7.1.3.自动增长列

我们通常希望在每次插入新记录时，数据库自动生成字段的值。
我们可以在表中使用 auto_increment（自动增长列）关键字，自动增长列类型必须是整形，自动增长列必须为键(一般是主键)。

下列 SQL 语句把 “Persons” 表中的 “Id” 列定义为 auto_increment 主键

CREATE TABLE Persons4
(
  Id      int PRIMARY KEY AUTO_INCREMENT,
  LastName  varchar(255),
  FirstName varchar(255),
  Address   varchar(255),
  City      varchar(255)
)

向persons添加数据时，可以不为Id字段设置值，也可以设置成null，数据库将自动维护主键值：

INSERT INTO Persons (FirstName,LastName) VALUES ('Bill','Gates')
INSERT INTO  Persons (Id,FirstName,LastName) VALUES (NULL,'Bill','Gates')

扩展：默认AUTO_INCREMENT 的开始值是 1，如果希望修改起始值，请使用下列 SQL 语法：

ALTER TABLE Persons AUTO_INCREMENT=100

7.2.非空约束

NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
下面的 SQL 语句强制 “Id” 列和 “LastName” 列不接受 NULL 值：

CREATE TABLE Persons5
(
  Id      int          NOT NULL,
  LastName  varchar(255) NOT NULL,
  FirstName varchar(255),
  Address   varchar(255),
  City      varchar(255)
)

7.3.唯一约束

UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

添加唯一约束
创建表时，在字段描述处，声明唯一：

CREATE TABLE Persons
(
  Id      int UNIQUE,
  LastName  varchar(255) NOT NULL,
  FirstName varchar(255),
  Address   varchar(255),
  City      varchar(255)
)

7.4.外键约束

FOREIGN KEY 表示外键约束，将在多表中学习。

8.DQL数据查询语言

8.1.准备工作

#创建商品表：
create table product(
 pid int primary key,
 pname varchar(20),
 price double,
 category_id varchar(32)
);

INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');

INSERT INTO product(pid,pname,price,category_id) VALUES(4,'杰克琼斯',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');

INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');

INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'海澜之家',1,'c002');

8.2.语法：

select [distinct]
*| 列名,列名
from 表
where 条件

8.3.简单查询

#1.查询所有的商品.  
select *  from product;
#2.查询商品名和商品价格. 
select pname,price from product;
#3.别名查询.使用的关键字是as（as可以省略的）.  
#3.1表别名: 
select * from product as p;
#3.2列别名：
select pname as pn from product; 
#4.去掉重复值.  
select distinct price from product;
#5.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
select pname,price+10 from product;

8.4.条件查询

比较运算符> < <= >= = <> !=大于、小于、大于(小于)等于、不等于
BETWEEN …AND…显示在某一区间的值(含头含尾)
IN(set)显示在in列表中的值，例：in(100,200)
LIKE ‘张%’LIKE ‘%涛%’模糊查询，Like语句中，%代表零个或多个任意字符，_代表一个字符，例如：first_name like ‘_a%’;
IS NULL IS NOT NULL判断是否为空
逻辑运算符and多个条件同时成立
or多个条件任一成立
not不成立，例：where not(salary>100);

实例:

#查询商品名称为“花花公子”的商品所有信息：
SELECT * FROM product WHERE pname = '花花公子';

#查询价格为800商品
SELECT * FROM product WHERE price = 800;

#查询价格不是800的所有商品
SELECT * FROM product WHERE price != 800;
SELECT * FROM product WHERE price <> 800;
SELECT * FROM product WHERE NOT(price = 800);

#查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price > 60;

#查询商品价格在200到1000之间所有商品
SELECT * FROM product WHERE price >= 200 AND price <=1000;
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;

#查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price = 200 OR price = 800;
SELECT * FROM product WHERE price IN (200,800);

#查询含有'霸'字的所有商品
SELECT * FROM product WHERE pname LIKE '%霸%';

#查询以'香'开头的所有商品
SELECT * FROM product WHERE pname LIKE '香%';

#查询第二个字为'想'的所有商品
SELECT * FROM product WHERE pname LIKE '_想%';

#查询没有分类的商品
SELECT * FROM product WHERE category_id IS NULL;

#查询有分类的商品
SELECT * FROM product WHERE category_id IS NOT NULL;

8.5.排序查询

通过order by语句，可以将查询出的结果进行排序。暂时放置在select语句的最后。
格式:

SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
 ASC 升序 (默认)
 DESC 降序

#1.使用价格排序(降序)
SELECT * FROM product ORDER BY price DESC;
#2.在价格排序(降序)的基础上，以分类排序(降序)
SELECT * FROM product ORDER BY price DESC,category_id DESC;
#3.显示商品的价格(去重复)，并排序(降序)
SELECT DISTINCT price FROM product ORDER BY price DESC;

8.6.聚合查询

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。
今天我们学习如下五个聚合函数：
聚合函数 作用

聚合函数作用
count()统计指定列不为NULL的记录行数；
sum()计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
max()计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
min()计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
avg()计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

实例:

#1 查询商品的总条数
SELECT COUNT(*) FROM product;
#2 查询价格大于200商品的总条数
SELECT COUNT(*) FROM product WHERE price > 200;

#3 查询分类为'c001'的所有商品的总和
SELECT SUM(price) FROM product WHERE category_id = 'c001';
#4 查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id = 'c002';
#5 查询商品的最大价格和最小价格
SELECT MAX(price),MIN(price) FROM product;

8.7.分组查询

  • 分组查询是指使用group by字句对查询信息进行分组。
    格式：
SELECT 字段1,字段2… FROM 表名 GROUP BY分组字段 HAVING 分组条件;

分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。
having与where的区别:

  1. having是在分组后对数据进行过滤.,where是在分组前对数据进行过滤
  2. having后面可以使用分组函数(统计函数),where后面不可以使用分组函数。
    实例:
#1 统计各个分类商品的个数
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;
#2 统计各个分类商品的个数,且只显示个数大于1的信息
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;

8.8.分页查询

分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。

  • 格式：
SELECT 字段1，字段2... FROM 表明 LIMIT M,N

M: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数
N: 整数，表示查询多少条数据
SELECT 字段1，字段2… FROM 表明 LIMIT 0,5
SELECT 字段1，字段2… FROM 表明 LIMIT 5,5

#查询product表的前5条记录
SELECT *  FROM product LIMIT 0,5

8.9.insert into select语句

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。
基本语法:

INSERT INTO table2
SELECT column_name(s)
FROM table1;

实例:

create table product2(
 pid int primary key,
 pname varchar(20),
 price double
);

insert into product2 select pid,pname,price from product where category_id = 'c001';

9.多表操作

实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来我们将在单表的基础上，一起学习多表方面的知识。

9.1.表与表之间的关系

  • 一对多关系：
  • 常见实例：客户和订单，分类和商品，部门和员工.
  • 一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键.

9.2.外键约束

  • 现在我们有两张表“分类表”和“商品表”，为了表明商品属于哪个分类，通常情况下，我们将在商品表上添加一列，用于存放分类cid的信息，此列称为：外键

  • 此时“分类表category”称为：主表，“cid”我们称为主键。“商品表products”称为：从表，category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多关系。

外键特点

  • 从表外键的值是对主表主键的引用。
  • 从表外键类型，必须与主表主键类型一致。

声明外键约束

语法：

alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);

[外键名称] 用于删除外键约束的，一般建议“_fk”结尾

alter table 从表 drop foreign key 外键名称

使用外键目的：

  • 保证数据完整性

9.3.一对多操作

9.3.1.分析

  • category分类表，为一方，也就是主表，必须提供主键cid
  • products商品表，为多方，也就是从表，必须提供外键category_id

9.3.2.实现：分类和商品

###创建分类表
create table category(
  cid varchar(32) PRIMARY KEY ,
  cname varchar(100)  #分类名称
);

# 商品表
CREATE TABLE products (
  pid varchar(32) PRIMARY KEY  ,
  name VARCHAR(40) ,
  price DOUBLE ,
  category_id varchar(32)
);

#添加约束
alter table products add constraint product_fk foreign key (category_id) references category (cid);

9.3.3.操作

#1 向分类表中添加数据
INSERT INTO category (cid ,cname) VALUES('c001','服装');

#2 向商品表添加普通数据,没有外键数据，默认为null
INSERT INTO products (pid,pname) VALUES('p001','商品名称');

#3 向商品表添加普通数据，含有外键信息(category表中存在这条数据)
INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名称2','c001');

#4 向商品表添加普通数据，含有外键信息(category表中不存在这条数据) -- 失败,异常
INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名称2','c999');

#5 删除指定分类(分类被商品使用) -- 执行异常
DELETE FROM category WHERE cid = 'c001';

10.多表查询

CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50)
);
CREATE TABLE products(
  pid VARCHAR(32) PRIMARY KEY ,
  pname VARCHAR(50),
  price INT,
  flag VARCHAR(2),    #是否上架标记为：1表示上架、0表示下架
  category_id VARCHAR(32),
  CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);

10.1.初始化数据

#分类
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');

10.2.多表查询

  1. 交叉连接查询(基本不会使用-得到的是两个表的乘积) [了解]
语法：select * from A,B;
  1. 内连接查询(使用的关键字 inner join – inner可以省略)
隐式内连接：select * from A,B where 条件;
显示内连接：select * from A inner join B on 条件;
  1. 外连接查询(使用的关键字 outer join – outer可以省略)
左外连接：left outer join
select * from A left outer join B on 条件;
右外连接：right outer join
select * from A right outer join B on 条件;

#1.查询哪些分类的商品已经上架
#隐式内连接
SELECT DISTINCT c.cname FROM category c , products p 
 WHERE c.cid = p.category_id AND p.flag = '1';

#内连接
SELECT DISTINCT c.cname FROM category c 
 INNER JOIN products p ON c.cid = p.category_id 
 WHERE p.flag = '1';

#2.查询所有分类商品的个数
#左外连接
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');
SELECT cname,COUNT(category_id) FROM category c 
 LEFT OUTER JOIN products p 
  ON c.cid = p.category_id 
 GROUP BY cname;

下面通过一张图说明连接的区别:

在这里插入图片描述

10.3.子查询

子查询：一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。

select ....查询字段 ... from ... 表.. where ... 查询条件

#3 子查询, 查询“化妆品”分类上架商品详情
#隐式内连接
SELECT p.* FROM products p , category c 
 WHERE p.category_id=c.cid AND c.cname = '化妆品';

#子查询
##作为查询条件
SELECT * FROM products p 
 WHERE p.category_id = 
 ( 
  SELECT c.cid FROM category c 
   WHERE c.cname='化妆品'
 );
 ##作为另一张表
 SELECT * FROM products p , 
   (SELECT * FROM category WHERE cname='化妆品') c 
  WHERE p.category_id = c.cid;

 

#查询“化妆品”和“家电”两个分类上架商品详情
SELECT * FROM products p 
 WHERE p.category_id in 
 ( 
  SELECT c.cid FROM category c 
   WHERE c.cname='化妆品' or c.name='家电'
 );

11.MySql索引(书本目录)

11.1.概述

索引是 MySQL 中一种十分重要的数据库对象。它是数据库性能调优技术的基础，常用于实现数据的快速检索。
索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

  1. 顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。这种方式实现比较简单，但是当表中有大量数据的时候，效率非常低下。

  1. 索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

例如，在学生基本信息表 students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表，当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

11.2.索引的分类

根据存储方式的不同，MySQL 中常用的索引在物理上分为以下两类。

B-树索引

B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。B-树索引是一个典型的数据结构，基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

哈希索引

哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。
HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。

根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 3 类：

普通索引

普通索引是最基本的索引类型，唯一任务是加快对数据的访问速度，没有任何限制。创建普通索引时，通常使用的关键字是 INDEX 或 KEY。

唯一性索引

唯一性索引是不允许索引列具有相同索引值的索引。如果能确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一性索引。
创建唯一性索引的目的往往不是为了提高访问速度，而是为了避免数据出现重复。

主键索引

主键索引是一种唯一性索引，即不允许值重复或者值为空，并且每个表只能有一个主键。主键可以在创建表的时候指定，也可以通过修改表的方式添加，必须指定关键字 PRIMARY KEY。

11.3.索引的操作

11.3.1.普通索引：

11.3.1.1.创建索引
# 方式1-直接创建
CREATE INDEX indexName ON mytable(username([length])); 
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

# 方式2-修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)

# 方式3-创建表的时候直接指定
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX indexName(username(length))  
);
11.3.1.2.查询索引
#1、查看表中所有索引
SHOW INDEX FROM table_name;
#2、查看数据库所有索引
SELECT * FROM mysql.`innodb_index_stats` a WHERE a.`database_name` = '数据库名';

#2、查看某一表索引
SELECT * FROM mysql.`innodb_index_stats` a WHERE a.`database_name` = '数据库名' and a.table_name like '%表名%';
11.3.1.3.删除索引
DROP INDEX [indexName] ON mytable;
alter table mytable drop index indexName;

11.3.2.唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

11.3.2.1.创建索引：
方式1-直接创建
CREATE UNIQUE INDEX indexName ON mytable(username(length)) 
方式2-修改表结构(添加索引)
ALTER table mytable ADD UNIQUE [indexName] (username(length))
方式3-创建表的时候直接指定
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
);
11.3.2.2.删除索引
DROP INDEX [indexName] ON mytable;
alter table mytable drop index indexName;

11.3.3.主键索引

主键索引的操作就是主键约束的操作，之前已经讲过，在这里不做描述。

11.4.索引的使用原则和注意事项

虽然索引可以加快查询速度，提高 MySQL 的处理性能，但是过多地使用索引也会造成以下弊端：

  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
  • 除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
  • 对于那些在查询中很少使用或参考的列不应该创建索引。因为这些列很少使用到，所以有索引或者无索引并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度，并增大了空间要求。

12.MySql开窗函数

12.1.概述

MySql在8.0的版本增加了对开窗函数的支持，终于可以在MySql使用开窗函数了。
开窗函数的语法结构：

 #Key word :Partiton by & order by
<开窗函数> over ([PARTITION by <列清单>]
                     Order by <排序用列清单>）

Mysql中支持的开窗函数有很多，这里重点给大家介绍三个：row_number(),rank(),dense_rank()

12.2.开窗函数介绍

row_number(),rank(),dense_rank()这三个函数都是用于返回结果集的分组内每行的排名
三者区别:
row_number:不管排名是否有相同的，都按照顺序1，2，3……n
rank:排名相同的名次一样，同一排名有几个，后面排名就会跳过几次
dense_rank:排名相同的名次一样，且后面名次不跳跃

12.3.案例

# 数据准备：
create table employee (empid int,ename varchar(20) ,deptid int ,salary decimal(10,2));

insert into employee values(1,'刘备',10,5500.00);
insert into employee values(2,'赵云',10,4500.00);
insert into employee values(2,'张飞',10,3500.00);
insert into employee values(2,'关羽',10,4500.00);

insert into employee values(3,'曹操',20,1900.00);
insert into employee values(4,'许褚',20,4800.00);
insert into employee values(5,'张辽',20,6500.00);
insert into employee values(6,'徐晃',20,14500.00);

insert into employee values(7,'孙权',30,44500.00);
insert into employee values(8,'周瑜',30,6500.00);
insert into employee values(9,'陆逊',30,7500.00);

# 对employee表中按照deptid进行分组，并对每一组的员工按照薪资进行排名：
SELECT
  empid,
  ename,
  deptid,
  salary,
  row_number() over (PARTITION BY deptid ORDER BY salary DESC) AS row_number1,
  rank() OVER (PARTITION BY deptid ORDER BY salary desc) AS rank2,
  dense_rank() OVER (PARTITION BY deptid ORDER BY salary desc) AS dense_rank3
FROM
 employee;

在这里插入图片描述

过去的曾经^_^
关注
专栏目录
Docker安装MySQL
帝上无天的博客
11-13 9万+
默认的鉴权方式，我们修改为mysql_native_password，不然的话连接会报错。虽然 docker 安装 mysql 不是一个很好的方案，但是为了个人使用方便，使用 docker 安装 mysql 还是没什么问题的。所以，我们在安装之前需要电脑可以联网，不然我们这种方式是安装不了的。当然，你也可以自行下载mysql镜像，然后再通过 docker 安装，不过这不在本文的讨论范围。此时我们使用mysql客户端连接服务器是失败的，因为默认的root不具有远程连接的权限。此时我们需要执行第六步。
MySQL导入sql文件的三种方法
Elliseaon的博客
06-27 24万+
文章目录一、使用工具Navicat for MySQL导入1.打开localhost_3306,选中右击“新建数据库”3.指定数据库名和字符集（可根据sql文件的字符集类型自行选择）3.选中数据库下的表运行SQL文件4.选中路径导入二、使用MySQL Workbench导入（MySQL的官方工具）1、第一种方法①.新建一个数据库demo(名字任取)，点击指示图标(或者File栏里面的Open SQL Script...)②.选中路径导入SQL文件③.添加指定库名的命令 ，并点击运行注意：大概在15、16行
MySQL
weixin_47401619的博客
01-18 3183
密码：1234 name：MySQL80 MySQL 3306 33060 6446 6447 6448 6449 获取mysql服务器地址的方法 1.首先，使用组合键“win+R”运行cmd，进入DOS窗口； 2.进入DOS窗口后，在DOS窗口中，启动MySQL服务； service mysql start　　　 2.MySQL服务启动后，输入用户名和密码，登录mysql数据库； mysql -u root -p 3.登录到MySQL数据库后，使用以下命令即可获取到mysql数据库的地址; selec.
Ubuntu deb文件 安装 MySQL
桃李不言下自成蹊
11-15 7万+
sudo apt -y install vim net-tools wget gcc make cmake lrzsz sudo apt -y install libmecab2 libjson-perl
Mysql | 查看表的索引
Web3 & Basketball
02-07 11万+
因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。
MySQL之mysql dump的使用
gong_wejoy的专栏
12-07 2万+
MySQL之mysql dump的使用
MySQL安装（详细，适合初学者）
在下是小白的博客
01-26 14万+
MySQL安装一、mysql安装包下载二、配置my.ini文件三、初始化MySQL四、可能遇到的错误操作 一、mysql安装包下载 官网下载地址：mysql安装包下载 如图所示： 二、配置my.ini文件 解压后的文件尽量不要放在C盘（内存小），解压后如下图所示 在上图所示梗目录下配置my.ini文件 [mysqld] # 设置3306端口 port=3306 [mysqld] # 设置3306端口 port=3306 # 设置mysql的安装目录（存放地址可以更改） basedir=E:\My
MySQL | 常用命令示例
✨ 欢迎来到【Seal ^_^ 的CSDN博客】！这里是我记录技术心得、分享经验的地方。✨
07-29 1万+
🟢 在使用MySQL时，我们经常需要编写一些常用脚本来进行数据操作和管理。本文将介绍一些常用的MySQL脚本，帮助更好地使用MySQL。
MySQL启动失败——MySQL服务无法启动原因及解决办法
weixin_44698389的博客
04-07 19万+
MySQL启动失败——MySQL服务无法启动原因及解决办法 一般情况下，通过压缩的安装包安装MySQL可能会出现MySQL无法启动的情况，如下图： 在安装初期，出现此情况的原因有两种： 1.缺乏my.ini文件 由于my.ini有特定的格式。所以此处可以通过寻找MySQL server 路径下的my.ini文件，将其复制到 my.ini文件的创建可以参考 2.缺乏data文件 在缺乏data文件...
MySQL 在线练习
qq_45571138的博客
03-02 4214
MySQL在线练习网站
mysql jdbc连接驱动jar包（适用于mysql5.7版本，亲测可用）
08-17
MySQL JDBC连接驱动是Java应用程序与MySQL数据库之间通信的桥梁，使得开发者可以使用Java语言来执行SQL语句，处理数据库中的数据。本驱动适用于MySQL 5.7版本，并且经过实际测试，确认可用。这个jar包是完全免费的，...
mysql-connector-java-8.0.27
12-24
MySQL Connector/J 8.0.27 是MySQL数据库与Java应用程序之间的重要桥梁，它是一个实现了JDBC（Java Database Connectivity）接口的驱动程序，允许Java开发者在应用中无缝地访问MySQL数据库。MySQL Connector/J使得...
mysql驱动jar 文件适用MySQL5.7
01-18
MySQL驱动jar文件是Java应用程序与MySQL数据库之间通信的关键组件，主要功能是提供Java Database Connectivity (JDBC) API，使得Java开发者能够通过编写Java代码来执行SQL语句，从而操作MySQL数据库。标题提到的...
MySQL安装配置教程（超级详细、保姆级）
热门推荐
Soloversion的博客
03-26 42万+
一、 下载MySQL Mysql官网下载地址https://downloads.mysql.com/archives/installer/ 1. 选择想要安装的版本，本篇文章选择的是5.7.31版本，下面的那个文件，点击Download下载 二、 安装MySQL 1. 选择设置类型 双击运行mysql-installer-community-5.7.31.0.msi 这里选择是自定义安装，所以直接选择“Custom”，点击“Next” “Developer Default”是开发者默认
Java连接MySQL数据库的超级详细步骤（Windows）
firstlt0217的博客
05-19 11万+
1. 数据准备 1. 下载JDK 下载Java开发工具包JDK，下载地址：https://www.oracle.com/java/technologies/javase-jdk14-downloads.html，下载待用。 2. 下载Mysql Java 连接 MySQL 需要驱动包，下载地址为：http://dev.mysql.com/downloads/connector/j/。 解压后得到 jar 库文件，待用，最终会将红框文件导入相应项目文件中。 3. 下载MySQL 下载地址：https:/
javaweb项目进销存管理系统springboot+vue+redis idea导入 mysql数据库-java课程设计毕业设
10-18
该系统采用Spring Boot作为后端框架，Vue.js作为前端技术，集成Redis进行缓存管理，并使用MySQL数据库进行数据存储。此项目旨在为在校大学生的Java课程设计和毕业设计提供全面的学习参考与实践指导，同时为Java技术爱好者提供丰富的学习资料。帮助用户深入理解进销存管理系统的设计思路与实现方法。通过该源码，开发者可以掌握Spring Boot、Vue.js、Redis和MySQL的结合使用，提升全栈开发能力，是学习Java开发的重要实践材料，适合于进行个人项目或课程作业参考。
毕业设计论文SpringBoot助学兼职系统.docx
10-18
毕业设计论文
毕业设计论文SpringBoot智能菜谱推荐系统.docx
10-18
毕业设计论文
毕业设计论文SSM创新型产品提前购平台.docx
最新发布
10-18
毕业设计论文
MySQL 5.1.58 安装教程
2. 创建用户组和用户：`groupadd mysql` 创建名为mysql的用户组，`useradd -g mysql mysql -s /sbin/nologin` 添加一个名为mysql的用户并将其添加到mysql组，且指定不登录 shell。 3. 解压MySQL源码包：`tar xzf ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值