Mysql笔记

1、SQL基本概念

database 库
table 表
schema 数据模型
column 列
row 行

索引INDEX

primary key
主键(索引)，特点非空和唯一

Foreign key
外键(普通索引)
关联另一张表的主键

唯一(就是唯一索引)

2、SQL类型

类型        长度              说明

(整数类型)
tinyint     1个字节
smallint
mediumint
int         4个字节
bigint      8个字节

(浮点类型)
float       小数点后4个字节   
double      小数点后8个字节

(字符串类型)
char(n) 固定长度
varchar(n) 可变长度
text 可变长度2^16
longtext 可变长度2^32

(日期类型)
date 年月日
time 时分秒
datetime 年月日时分秒

###### mysql获取时间日期的语法
select curnow() 年月日时分秒
select curdate()年月日
select sysdate()
select curtime()时分秒

3、数据库简单入门操作

show databases;

create basebase java;
drop basebase java;

3、数据库基本操作

创建表
CREATE TABLE `NewTable` (
`id`  int NOT NULL ,
`name`  varchar(255) NOT NULL ,
PRIMARY KEY (`id`)
)
;

create TABLE `use1`(
id int primary key auto_increment,
name varchar(12) not null,
password char(32) not null,
card INT not null UNIQUE
)

create table student(
  id int primary,
  name varchar(12) not null,
  password char(32),
  age tinyint default 18,
  card char(18) not null unique
)

create table student(
  id int primary key auto_increment,
  name varchar(12) not null,
  password char(32),
  age tinyint default 18,
  card char(18) not null unique
)



 插入数据

insert into 表名 values(值1,值2)
insert into 表名(列1，列2) values(值1,值2)

更新
update 表名 age=99,name=gpc WHERE id=3

删除
delete FROM 表名 WHERE id=3

4、别名

AS 可以省略
列别名
SELECT name AS myname,age from student;

FROM Products 

AS 可以省略
表别名
SELECT e.name,e.salary,e.sex
FROM employee AS e WHERE

SELECT e.name,e.salary,e.sex
FROM employee e WHERE

4、SELECT 子句顺序

SELECT	    要返回的列或表达式	    是
FROM	      从中检索数据的表	     仅在从表选择数据时使用
WHERE	      行级过滤	              否
GROUP BY	  分组说明	            仅在按组计算聚集时使用
HAVING	    组级过滤	              否
ORDER BY	  输出排序顺序	          否

以此为例

CREATE TABLE Products
(
	prod_id		CHAR(10)			NOT NULL,
	vend_id		CHAR(10)			NOT NULL,
	prod_name	CHAR(254)		NOT NULL,
	prod_price	DECIMAL(8, 2)		NOT NULL,
	prod_desc	VARCHAR(1000)	NULL
);

4、SELECT 检索

SELECT prod_id, prode_name, prod_price 
FROM Products;
从Products表选出prod_id, prode_name, prod_price列


distinct找不同的vendid
SELECT DISTINCT vend_id 
FROM Products;

SELECT * 
FROM Products;
从Products表中 选所有列
# 检索前 5 行 LIMIT方最后
SELECT prod_name 
FROM Products 
LIMIT 5;

# 从第 3 行开始检索 5 行
SELECT prod_name 
FROM Products 
LIMIT 5 OFFSET 3;

# 从第 3 行开始检索 5 行
SELECT prod_name 
FROM Products 
LIMIT 3, 5;

第 0 行：第一个被检索的行是第 0 行，而不是第 1 行。
因此，LIMIT 1 OFFSET 1 会检索第 2 行，而不是第 1 行。

5、排序检索

ORDER BY要放在最后

SELECT prod_name 
FROM Products 
ORDER BY prode_name LIMIT 3,5;


SELECT prod_id, prod_price, prod_name 
FROM Products 
ORDER BY prod_price, prod_name;


# 按升序 降序 排序
SELECT prod_id, prod_price, prod_name 
FROM Products 
ORDER BY prod_price DESC;

//默认是ASC升序
SELECT prod_id, prod_price, prod_name 
FROM Products 
ORDER BY prod_price DESC, prod_name;

6、WHERE条件过滤

=：等于
>：大于
>=：大于等于
<>：不等于
!=：不等于
!>：不大于
!<：不小于
<：小于
<=：小于等于
BETWEEN：在指定的两个值之间
IS NULL：为 NULL 值

SELECT prod_name, prod_price 
FROM Products 
WHERE prod_price = 3.49;

SELECT prod_name, prod_price 
FROM Products 
WHERE vend_id !='DLL01';

SELECT prod_name, prod_price 
FROM Products 
WHERE prod_price BETWEEN 5 AND 10;

SELECT prod_name, prod_price 
FROM Products 
WHERE prod_price IS NULL;

7、组合 WHERE条件高级过滤

SELECT prod_name 
FROM Products 
WHERE age>18 ORDER BY prode_name LIMIT 3,5;;

SELECT prod_id, prod_price, prod_name 
FROM Products 
WHERE vend_id = 'DLL01' AND prod_price <= 4;


# 优先匹配 AND 左右两侧的条件
SELECT prod_id, prod_price, prod_name 
FROM Products 
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >= 10;

# 优先匹配括号内的条件
SELECT prod_id, prod_price, prod_name 
FROM Products 
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;

8、操作符 IN 和 NOT

操作符 IN 和 NOT
IN 操作符
IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。
SELECT prod_name, prod_price 
FROM Products 
WHERE vend_id IN ( 'DLL01', 'BRS01' ) 
ORDER BY prod_name;


WHERE 子句中 NOT 操作符有且只有一个功能，
那就是否定其后所跟的任何条件。
SELECT prod_name 
FROM Products 
WHERE NOT vend_id = 'DLL01' 
ORDER BY prod_name;

9、通配符符

通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间

% 表示任何字符出现任意次数（包括 0 个字符）

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE 'Fish%';

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE '%bean bag%';

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE 'F%y';

下划线（_）通配符
下划线的用途与 % 一样，一个下划线代表一个字符

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE '_inch';

方括号（[]）通配符
它必须匹配指定位置（通配符的位置）的一个字符。
这个有待研究

SELECT cust_contact 
FROM Customers 
WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact;

SELECT cust_contact 
FROM Customers 
WHERE cust_contact LIKE '[^JM]%' ORDER BY cust_contact;

SELECT cust_contact 
FROM Customers 
WHERE NOT cust_contact LIKE '[JM]%' ORDER BY cust_contact;

1、计算

UPDATE 表名 SET salary=salary+100 WHERE id=1;

SELECT AVG(p.prod_price),AVG(p.prod_id) from products AS p;

3-2、聚合函数

统计数据
SQL 聚集函数：

AVG()：返回某列的平均值
COUNT()：返回某列的行数
MAX()：返回某列的最大值
MIN()：返回某列的最小值
SUM()：返回某列值之和

使用 COUNT(*) 对表中行的数目进行计数 包含 NULL 值。
SELECT COUNT(*) AS num_cust 
FROM Products;

使用 COUNT(column) 对特定列中具有值的行进行计数，忽略 NULL 值。
SELECT COUNT(cust_email) AS num_cust 
FROM Customers;

// 求最高工资和最低工资的差值
SELECT SUM(salary)-MIN(salary) FROM student

SELECT AVG(prod_price) AS avg_price 
FROM Products;

// vend_id = 'DLL01'的平均工资
SELECT AVG(prod_price) AS avg_price 
FROM Products 
WHERE vend_id = 'DLL01';


SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min,
 MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg 
FROM Products;

4、 分组

!!! GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前。

选择vend_id列，按照相同vend_id分组,计算相同vend_id出现的次数
SELECT vend_id, COUNT(*) AS num_prods 
FROM Products 
GROUP BY vend_id;


//HAVING 分组之后的过滤，用HAVING，
//where是在分组之前，用and
SELECT vend_id, COUNT(*) AS num_prods 
FROM Products 
GROUP BY vend_id HAVING COUNT(*) >= 2;

SELECT vend_Id, COUNT(*) AS num_prods 
FROM Products 
WHERE prod_price >= 4 
GROUP BY vend_id HAVING COUNT(*) >= 2;


选中 性别，按性别分组，求每组的平均工资 和 每组的数量
SELECT SEX,AVG(salary),count(sex) 
FROM student
GROUP BY sex

SELECT p.vend_id,p.prod_price,count(*)
from products AS p
GROUP BY p.vend_id,p.prod_price;

SELECT p.vend_id,p.prod_price,avg(p.prod_desc)
from products AS p
GROUP BY p.vend_id,p.prod_price;

// 不同名字不同城市分组
SELECT name,city,AVG(salary)
FROM student
GROUP BY name,city
ORDER BY name;

5、 条件匹配case

//出现名字和sex 2列，
//当 p.prod_id=1的时候显示男 p.id=2显示女
SELECT p.prod_name,
CASE p.prod_id
	WHEN 1 THEN
		'男'
  WHEN 2 THEN
		'女'
END as 'sex'
from products AS p;


SELECT p.prod_name,
CASE
	WHEN p.prod_id>4 THEN
		'男'
  WHEN p.prod_id<=4 THEN
		'女'
END as 'sex'
from products AS p;

5、 条件表达式，子查询

SELECT cust_name, cust_contact 
FROM Customers 
WHERE cust_id IN (
  SELECT cust_id 
  FROM Orders 
  WHERE order_num IN (
    SELECT order_num 
    FROM OrderItems 
    WHERE prod_id = 'RGAN01'));



SELECT cust_name, cust_state, (
  SELECT COUNT(*) 
  FROM Orders 
  WHERE Orders.cust_id = Customers.cust_id) AS orders 
FROM Customers 
ORDER BY cust_name;

5、行列转化

SELECT name,course
FROM student
GROUP BY name,course

SELECT name,salary,
  CASE
  WHEN salary<=10000 THEN '普通收入'
  WHEN salary<=20000 THEN '中等收入'
  ELSE '高级视频'
END AS '工资水平'
FROM student

//挺难...
//AS 'Java' 查询到的结果生成 新列 ，命名为Java
SELECT name,
SUM(CASE course
  WHEN 'Java' THEN course ELSE 0
END) AS 'Java'
SUM(CASE course
  WHEN 'Go' THEN course ELSE 0
END) AS 'Go'
FROM student
GROUP BY name

多表及约束

一对一

如果对应的信息也是经常需要一起展示，放一张表即可
否则分2张表
实现方式：再任意一方加入外键，关联另一方主键，
并且设置外键唯一

1对多
多对1
多对多
CREATE TABLE `prod` (
`id`  int NOT NULL AUTO_INCREMENT ,
`name`  varchar(12) NOT NULL ,
`price`  int NOT NULL ,
PRIMARY KEY (`id`)
)
;

CREATE TABLE `prod_info` (
`id`  int NOT NULL AUTO_INCREMENT ,
`desc`  varchar(24) NOT NULL ,
`prod_id`  int UNIQUE NOT NULL ,
PRIMARY KEY (`id`),
CONSTRAINT `prod_id_fk` FOREIGN KEY (`prod_id`) REFERENCES `prod` (`id`)
);

一对多，多对一

多个表的外键，关联一个表的主键

多对多

如商品订单，一个订单包含多个商品，一个商品可以出现在多个订单上
如学生课程，一个学生可以选择多门课程，同门课程可以被多个学生选择

建立第三张中间表，建立两个外键，分别关联两方的主键

CREATE TABLE `NewTable` (
`course_id`  int NOT NULL ,
`student_id`  int NOT NULL ,
PRIMARY KEY (`course_id`, `student_id`),
CONSTRAINT `course_fk` FOREIGN KEY (`course_id`) REFERENCES `prod` (`id`),
CONSTRAINT `student_fk` FOREIGN KEY (`student_id`) REFERENCES `products` (`prod_id`)
);

解除外键关联关系，设置null
工作中多用逻辑删除 

多表关联查询

INNER JOIN、
LEFT JOIN、
RIGHT JOIN 
FULL JOIN 
1. 内连接，相当于查2个表的交集
2.左连接，相当于查询左表所有数据和交集部分数据
3.右连接，相当于查询右表所有数据和交集部分数据

通过使用 "WHERE" 子句，对两个表进行了连接，指定了
 "Vendors" 表和 "Products" 表的 "vend_id" 
 列相等的条件，以确保只返回有关联的供应商和产品信息

SELECT vend_name, prod_name, prod_price 
FROM Vendors, Products 
WHERE Vendors.vend_id = Products.vend_id;


SELECT vend_name, prod_name, prod_price 
FROM Vendors INNER JOIN Products 
ON Vendors.vend_id = Products.vend_id;

SELECT prod_name, vend_name, prod_price, quantity 
FROM OrderItems, Products, Vendors 
WHERE Products.vend_id = Vendors.vend_id 
AND OrderItems.prod_id = Products.prod_id 
AND order_num = 20007;

ON和WHERE

ON 子句：
ON 用于连接两个表，

-- 使用 ON 子句指定连接条件
ON 子句用于指定连接条件，
即连接 "Vendors" 表和 "Products" 表的 "vend_id" 列。
SELECT vend_name, prod_name, prod_price 
FROM Vendors 
INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

WHERE 子句：
 WHERE 用于对连接后的结果进行进一步的筛选和过滤。
WHERE 子句用于筛选整个结果集，
根据特定条件对从多个表中连接后形成的结果集进行过滤。
它可以包含对连接后的结果集中任何列的条件约束，而不局限于连接条件。

-- 使用 WHERE 子句对连接后的结果集进行筛选
ON 子句同样指定了连接条件，
然后 WHERE 子句进一步筛选连接后的结果集
SELECT vend_name, prod_name, prod_price 
FROM Vendors 
INNER JOIN Products ON Vendors.vend_id = Products.vend_id
WHERE Vendors.vend_id = Products.vend_id;

单行子查询

select 什么列 from table名字 where name='gpc'
select id列 from table名字 where name='gpc'

//优先级 先执行圆括号

//SELECT选择 e.name,e.salary,e.sex 3个列，
//从employee表中，
//条件 部门id为，子查询gpc所在部门的id
SELECT e.name,e.salary,e.sex
FROM employee AS e WHERE e.id=
//1. 选中部门的id的列，从employee表中，先查名字=gpc的人
//自然就找到gpc的部门id了
(SELECT e.id
FROM employee e WHERE e.name='gpc')
//过滤排除名字为gpc的
AND e.name!='gpc'

1.从  dept 表中 查 name像java的
2.从 employee 表中查id是(java id)的所有信息
SELECT e.name,e.salary,e.sex
FROM employee AS e WHERE e.id IN
(SELECT d.id FROM dept AS d WHERE d.name LIKE 'Java%') 

视图

重用 SQL 语句
简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
使用表的一部分而不是整个表。
保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图用 CREATE VIEW 语句来创建。删除视图可以用 DROP VIEW 。
利用视图简化复杂的联结

CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;

在以上视图中进行检索：

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

用视图重新格式化检索出的数据

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title
FROM Vendors
ORDER BY vend_name;

把此语句转换为视图：

CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title
FROM Vendors;

再检索数据：
SELECT *
FROM VendorLocations;

用视图过滤不想要的数据
CREATE VIEW CustomerEmailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

再检索数据：
SELECT *
FROM CustomerEMailList;

使用视图和计算字段
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

将以上查询转成视图：
CREATE VIEW OrderItemsExpanded AS 
SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM OrderItems;

再检索数据：
SELECT *
FROM OrderItemsExpanded
WHERE order_num = 20008;

索引概念

什么时候需要创建索引
    1. 频繁作为查询条件的字段
    2. 查询中频繁作为排序的字段
    （索引的区分度高，索引的效率就越高）

什么时候不可以创建索引
    1.频繁更新的字段
    2.写多读少的字段
    3.区分度低的字段，比如 性别 激活 
    4.数据量小的字段



索引INDEX

primary key
主键(索引)，特点非空和唯一

Foreign key
外键(普通索引)
关联另一张表的主键

唯一(就是唯一索引)

联合(组合)索引

索引语法

查看当前表的索引
SHOW INDEX FROM 表名

创建一个普通索引
CREATE INDEX 索引名字 ON 表名(列名)

创建一个唯一普通索引
CREATE UNIQUE INDEX 索引名字 ON 表名(列名)

创建一个主键索引
ALTER TABLE 索引名 ADD PRIMARY KEY(id)

删除索引
DROP INDEX 索引名字 ON 表名

组合索引联合索引
ALTER TABLE 表名 ADD INDEX 索引名字(name列名,city列名)
(先按第一个字段查找，将区分度高德方前面)

如何判断索引是否生效
https://www.bilibili.com/video/BV1jW4y1s7Vv/?p=42&spm_id_from=pageDriver

91、数据库事务

数据库事务的特性，ACID，
原子性：事务是一个原子操作单元，要么全部执行，要么全部不执行
一致性：在事务的开始和完成时，数据都必须保持一致状态
隔离性：事务之间互不影响，事务处理过程中的中间状态都是不可见的
持久性：事务完成之后，他对数据的修改是永久性的，即使出现系统故障也能保持

事务控制语句

开启一个任务
start transaction

提交事务
commit

回滚事务
rollback

设置一个事务保存点
savepoint <保存点名称>

释放事务保存点
release savepoint

将事务撤销到保存点
rollback to <事务保存点>

savepoint step1

savepoint step2
//step2释放了，就回滚不回去了
release savepoint step2

savepoint step3

rollback to step2

sverlvt 处理动态资源
servlet是web服务器中运行的java程序

http://localhost8080/javaweb/myServlet
javaweb 访问那个web项目
myServlet 访问那个servlet

92、并发事务可能带来的问题

1. 更新丢失
    多个事务同时选择一个数据进行修改，最后提交的更新覆盖了
    之前其他事务提交的更新
2.脏的
    一个事务正在修改，但未提交事务，
    另一个事务来读取这个记录，如果没做控制，
    则会读取到尚未提交的事务数据，此数据就是脏数据
3、不可重复读
    一个事务在前后2个时间点读取同样条件的事务，
    却发现读取的结果不一样，
4.幻读
    一个事务按条件查询数据，没有查到对应的数据，
    但是在插入数据的时候，却提示以及有这个数据了

92、MySql的事务隔离级别

事务隔离是指在数据库中，
多个并发执行的事务之间相互隔离的程度。

SET TRANSACTION ISOLATION LEVEL <隔离级别>

读未提交（Read Uncommitted）
    在这个隔离级别下，事务可以读取其他事务尚未提交的修改。
    这意味着可能出现脏读、不可重复读和幻读等问题。
    读未提交是最低的隔离级别，并发性能较高，
    但数据的一致性和可靠性较差，通常不建议使用。

读已提交（Read Committed）
    在这个隔离级别下，事务只能读取其他事务已经提交的修改。
    这解决了脏读的问题，但仍然可能出现不可重复读和幻读。
    读已提交提供了较好的并发性能和一定的数据一致性，
    是许多数据库默认的隔离级别。

可重复读（Repeatable Read）
    在这个隔离级别下，事务在第一次读取数据后会创建一个一致性视图，
    并在事务结束之前始终使用这个视图。
    这意味着事务内部的查询不会受到其他事务的修改影响
    ，解决了不可重复读的问题。但幻读仍然可能发生，
    即在同一查询中，事务可能看到其他事务插入或删除的数据。

串行化（Serializable）
    在这个隔离级别下，事务是完全串行执行的，
    每个事务在执行期间都会对数据进行加锁，
    避免了脏读、不可重复读和幻读等问题。
    但是串行化会对并发性能产生严重影响
    ，因为它需要对数据进行较强的加锁，限制了并发访问。