Mysql基础

SELECT查询篇

• 使用某个数据库 USE sql_store;
• 查询某个表(SELECT) SELECT * FROM customers; SELECT first_name, city FROM customers;
• 条件查询(WHERE) SELECT * FROM customers WHERE customer_id = 1;
• 排序(ORDER BY) SELECT * FROM customers ORDER BY customer_id；
• 查询时数学运算(+ - * /) SELECT first_name, city, points + 10 * 100 / 2 -30 FROM customers
• 添加别名(AS) SELECT first_name, city, points + 10 * 100 / 2 -30 AS discount_factor FROM customers
• 去重(DISTINCT) SELECT DISTINCT state FROM customers；
• 相反条件(NOT) SELECT * FROM customers WHERE NOT points > 1000 or brith_date > '1990-01-01' 就相当于 SELECT * FROM customers WHERE points <= 1000 AND brith_date <= '1990-01-01'
• 查询范围内数据(IN) SELECT * FROM customers WHERE state = 'VA' OR state = 'LA' OR state = 'NA' 使用IN 两种方法是等效的 SELECT * FROM customers WHERE state IN('VA','NA','LA') 还可以这么使用查询不在这个范围内的 SELECT * FROM customers WHERE NOT state IN('VA','NA','LA')
• 范围数据查询(BETWEEN) SELECT * FROM customers WHERE brith_date BETWEEN '1990-01-01' AND '2000-01-01
• 模糊查询(LIKE) SELECT * FROM customers WHERE last_name LIKE '%c%' —- % 标识任意字符串 —- _表示单个字符 常用方式为:
  1. %c% 所有包含c的
  2. c% 开头是c后面为任意字符
  3. %c 任意开头但结尾是c
  4. _y 只有俩个字符第一个字符随机第二个为y
  5. b____y 开头是b中间三个字符随意结尾是y
• 正则表达式(REGEXP) SELECT * FROM customers WHERE last_name REGEXP '[a-h]e'

1. ^表示以当前值开头
2. $表示以当前值结尾
3. | 表示or
4. [a-h]e 匹配ae、be、ce、de、ee、fe、ge、he

• 查询字段是否为空 select * from customers WHERE phone IS NULL
• 查询字段是否不为空 select * from customers WHERE phone IS NOT NULL
• 限制数量(LIMIT) 5代表偏移量，3代表限制返回数量 SELECT * FROM orders LIMIT 5,3
• 表链接(JOIN) 俩个表链接 SELECT * FROM orders_items oi JOIN customers c ON oi.customer_id = c.customer_id 单表链接 SELECT * FROM employees e JOIN employees m ON e.reports_to = m.employee_id 左连接or右链接 SELECT * FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id 不管是否成立都会返回左边表所有数据。右链接同理

ON c.customer_id = o.customer_id的替代方法

• 联合运算符(UNION)

SELECT customer_id, first_name, points ,'青铜' AS type FROM customers where points < 1000
UNION
SELECT customer_id, first_name, points ,'白银' AS type FROM customers where points BETWEEN 1000 AND 2000
UNION
SELECT customer_id, first_name, points ,'黄金' AS type FROM customers where points > 2000 ORDER BY first_name

新增

• 新增一行

INSERT INTO table (key,key) VALUES (val,val);

• 新增多条记录

INSERT INTO table (key,key) VALUES (val1,val1),(val2,val2);

• 多表增加数据耦合情况

INSERT INTO table1 (key,key) VALUES (val,val);

INSERT INTO table2 VALUES (LAST_INSERT_ID(),val,val);

• 创建表副本

CREATE TABLE name AS SELECT * FROM table;

指定数据或存在多表联查字段和条件

CREATE TABLE name AS SELECT o.id,o.name,t.name as tname FROM table1 o JOIN table2 t USING(id) WHERE date > ‘2022-08-14’;

更新

• 更新数据(条件更新)

UPDATE table SET key1=val1,key2=val2,key3 = key4 WHERE id = ?;

• 具体实际应用

生日在1990年前出生的用户积分+50

UPDATE user SET points = points + 50 WHERE birth_date < ‘1990-01-01’;

• 子查询更新语句

UPDATE user SET points = points + 50 WHERE id = (SELECT user_id from vip WHERE name = ‘john’);

删除

• 删除语句

DELETE FROM table where id = 1;

• 删除语句中使用子查询

DELETE FROM table where id = (SELECT id FROM user WHERE name = ‘john’);

函数

聚合函数

• MAX()最大值
• MIN()最小值
• AVG()平均值
• SUM()累计值
• COUNT()总数 作用于非空字段

count使用DISTINCT进行去重查询(COUNT(DISTINCT client_id))

• 练习聚合数据

select 
 'First half of 2019' as date_range,
    SUM(invoice_total) as total_sales,
    SUM(payment_total) as total_payments,
    SUM(invoice_total - payment_total) as what_we_expect 
from invoices where invoice_date BETWEEN '2019-01-01' AND '2019-06-30'
union
select 
 'Second half of 2019' as date_range,
    SUM(invoice_total) as total_sales,
    SUM(payment_total) as total_payments,
    SUM(invoice_total - payment_total) as what_we_expect 
from invoices where invoice_date BETWEEN '2019-07-01' AND '2019-12-31'
union
select 
 'Total' as date_range,
    SUM(invoice_total) as total_sales,
    SUM(payment_total) as total_payments,
    SUM(invoice_total - payment_total) as what_we_expect 
from invoices  where invoice_date BETWEEN '2019-01-01' AND '2019-12-31';

• 子句写入顺序

1. select
2. from
3. where
4. group by
5. order by

• 单字段分组查询

select 
 client_id,
 SUM(invoice_total) as total_sales
from invoices 
GROUP BY client_id
order by total_sales DESC;

• 多字段分组

select 
 state,
    city,
 SUM(invoice_total) as total_sales
from invoices 
join clients using(client_id)
GROUP BY client_id;

SELECT 
 date,
    pm.name AS payment_method,
    SUM(amount) AS total_payments
FROM payments p
JOIN payment_methods pm
 ON p.payment_method = pm.payment_method_id
GROUP BY date, payment_method
ORDER BY date

• HAVING 分组后筛选数据

select 
 state,
    city,
 SUM(invoice_total) as total_sales
from invoices 
join clients using(client_id)
GROUP BY client_id
HAVING total_sales > 500;

• ROLLUP操作符

!! 使用后不能再分组中使用别名

SELECT 
 pm.name as payment_method,
    SUM(p.amount) as total 
FROM payments p
JOIN payment_methods pm ON p.payment_method = pm.payment_method_id
GROUP BY pm.name WITH ROLLUP;

复杂查询

• ALL

下面使用ALL的意思为invoices表中的invoice_total与子查询中返回的invoice_total列表结果作对比如果全部大于子查询的invoice_total就加入最终结果集(< > ≤ ≥ =)都一样。

SELECT * 
FROM invoices  
WHERE invoice_total > ALL(
 SELECT invoice_total FROM invoices 
 WHERE client_id = 3
);

• ANY or SOME

查询表数据等于子查询中任意值就会加入到最终结果集 与IN 操作符是等价的

SELECT * 
FROM clients  
WHERE client_id = ANY(
 SELECT client_id FROM invoices
    group by client_id
 HAVING count(*) >= 2
);
------------------------------------
SELECT * 
FROM clients  
WHERE client_id IN (
 SELECT client_id FROM invoices
    group by client_id
 HAVING count(*) >= 2
);

• EXISTS

与IN的区别在于 IN是子查询全部完成后返回结果给主查询再去和结果集比对SELECT * FROM table WHERE id IN (1,2,3,….)而EXISTS是只给出子查询是否有满足条件的记录,如果为true就会加入到最终结果集,如果数据量大就使用EXISTS

SELECT * 
FROM products p
WHERE NOT EXISTS (
 SELECT distinct product_id FROM order_items 
    where product_id = p.product_id
)

• SELECT子句中使用子查询

SELECT 
 client_id,
 name,
 (SELECT SUM(invoice_total) FROM invoices where client_id = c.client_id) as total_sales,
 (SELECT AVG(invoice_total) FROM invoices ) as average,
 (select total_sales - average) as diff 
 FROM clients c;

查询语句多种用法查询构成不存表结构的数据后可嵌套SELECT进行其他操作 看例子将统计出来的数据在进行过滤，还可以继续进行JOIN操作 一切皆有可能~

SELECT * FROM (
SELECT 
 client_id,
 name,
 (SELECT SUM(invoice_total) FROM invoices where client_id = c.client_id) as total_sales,
 (SELECT AVG(invoice_total) FROM invoices ) as average,
 (select total_sales - average) as diff 
 FROM clients c) AS sales_summary
 WHERE total_sales IS NOT NULL

内建函数

数学函数(numeric func)

• ROUND(传入数值,可选参数保留几位) 四舍五入函数

ROUND(6.66) = 7

• TRUNCATE(传入数值,保留几位) 截取函数

TRUNCATE(3.1415926,3) = 3.141

• CEILING()返回传入值最大整数

CEILING(5.2) = 6

• FLOOR()返回传入值最小整数

FLOOR(5.2) = 5

• ABS()计算绝对值
• RAND创建随机数

字符函数(string func)

• LENGTH()获取字符长度
• LOWER()转为小写

LOWER(’Sky’) = sky

• LTRIM()删除左侧多余空格
• RTRIM()删除右侧多余空格
• TRIM()删除两侧多余空格
• LEFT(’’,NUM)从左侧获取指定长度字符

LEFT(’abcdef’,3) = abc

• RIGHT()从右侧获取指定长度字符

RIGHT(’abcdef’,3) = def

• SUBSTRING(字符串,起始位置,结束位置)截取当前字符串

SUBSTRING('abcdef',1,3) = abc

• LOCATE(查询的字符,字符串)获取字符在字符串中第一次出现的位置不存在返回0 不区分大小写

SELECT LOCATE('c','abcdef') = 3

SELECT LOCATE('C','abcdef') = 3

• REPLACE(字符串,替换的字符)替换函数

SELECT REPLACE('abcdfeg','fe','ef') = abcdefg

• CONCAT()合并俩个字符串

CONCAT('FIRST','LAST') = FIRSTLAST

CONCAT('FIRST',' ','LAST') = FIRST LAST

日期函数(date func)

• NOW()获取当前时间对象

'2022-08-15 16:16:08'

• CURDATE()获取当前日期

'2022-08-15'

• CURTIME()获取当前时间

'16:16:08’

• YEAR(date)获取年

YEAR(NOW()) = 2022

• MONTH()获取月

MONTH(NOW()) = 8

• DAY()获取天

DAY(NOW()) = 15

• HOUR()获取小时

HOUR(NOW()) = 16

• MINUTE()获取分钟

MINUTE(NOW()) = 20

• SECOND()获取秒钟

SECOND(NOW()) = 35

• MONTHNAME()获取月份字符串名称

MONTHNAME(NOW()) = ’August‘

• DAYNAME()获取天字符串名称

DAYNAME(NOW()) = Monday

• DATE_FORMAT(date,’‘)日期格式化

DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') = 2022-08-15 16:29:36

• TIME_FORMAT(date,’’)时间格式化
• DATE_ADD()为日期加天数 如果传入值为负数代表去年上个月…

DATE_ADD(NOW(),INTERVAL 1 DAY) 加一天

DATE_ADD(NOW(),INTERVAL 1 YEAR) 加一年

• DATE_SUB()时间回溯到什么时候

NOW() = 2022-08-15

DATE_SUB(NOW(),INTERVAL 1 DAY) - 2022-08-14

• DATEDIFF()比较俩个时间的值返回相差多少天

DATEDIFF(NOW(),'2019-01-01') = 1322

• TIME_TO_SEC()返回0:00到指定时间的秒数

TIME_TO_SEC('1:00') = 3600

TIME_TO_SEC('08:00') - TIME_TO_SEC('02:00') = 21600 可以做运算

• IFNULL(表字段名,’替换的内容’)如果为空替换为自己设定的内容

select order_id,IFNULL(shipper_id, 'Not assigned') as shipper from orders

• COALESCE(返回的表字段,替换的表字段,替换表字段为空时填写的内容) 当返回表字段为null时使用替换表字段的内容如果也为空使用自定义内容

select order_id,coalesce(shipper_id, comments ,'Not assigned') as shipper from orders

• IF用法 用于一种判断

SELECT 
 product_id,
    name,
    COUNT(*) AS orders,
    IF(COUNT(*) > 1 ,'Many times','Once') AS frequency
FROM products 
JOIN order_items 
USING(product_id)
GROUP BY product_id,name;

• CASE用法 用于需要多种判断

SELECT 
 CONCAT(first_name,' ',last_name) AS customer,
    points,
    CASE 
  WHEN points > 3000 THEN 'Gold'
  WHEN points >= 2000 THEN 'Silver'
        ELSE 'Bronze'
 END AS category
FROM customers
ORDER BY points DESC;

• CREATE VIEW 创建视图

CREATE VIEW clients_balance AS
SELECT SUM(invoice_total - payment_total) AS balance,client_id,name FROM clients c
JOIN invoices i USING(client_id)
WHERE client_id = c.client_id
GROUP BY client_id

• 使用视图

SELECT * from clients_balance;

• OR REPLACE 修改视图内容

CREATE OR REPLACE VIEW clients_balance AS
SELECT SUM(invoice_total - payment_total) AS balance,client_id,name FROM clients c
JOIN invoices i USING(client_id)
WHERE client_id = c.client_id
GROUP BY client_id

事务

报错或异常关闭没有走commit会自动回滚。

START TRANSACTION;

INSERT INTO...

INSERT INTO...

COMMIT;

• 事务隔离级别(隔离等级越高越需要牺牲性能和体验，默认等级为重复读)

• READ UNCOMMITTEN(不可提交读)

  可以在不需要特别精准数据的场景shi'y

  这种等级没有解决任何问题，因为事务之间没有隔离可以互相做出未提交修改

• READ COMMITTEN(提交读)

  只能够读取提交后的数据，可以避免脏读问题，但是可能或出现读取俩次数据不一致效果 需要重复读来解决

• REPEATABLE READ (重复读)

  可以保证大部分的查询都返回一致的结果就算数据库被改了

  举例: 在事务中第一次A取到10积分B进行了对积分的修改A第二次取到的积分为0 这时俩次取到的结果不同 使用可重复读取后在当前事务中就算是数据被改。

• SERIALIZABLE(序列化) 保证事务会知道其他事务正在对数据进行的修改

  可以解决所有并发问题！会等待其他事务完成后在进行。会影响性能与时间！

  幻读情况下可使用，在查询时其他事务添加了数据 导致符合条件的数据查询不到

  	丢失数据	脏读	不可重复读取(俩次读取的内容可能不同)	幻读(丢失符合查询条件的某些行)
  (READ UNCOMMITTEN)不可提交读
  (READ COMMITTEN)提交读		√
  (REPEATABLE READ)可重复读	√	√	√
  (SERIALIZABLE)序列化	√	√	√	√

• 死锁

发生在不同的事务都无法完成的情况下

俩个事务在互相等待导致永远无法完成

数据类型

• String Types (字符串)

  CHAR(x) 用来存放固定长度字符

  VARCHAR(x-65,535) 用来存放可变长度

  MEDIUMTEXT(x-16000000)中型文字

  LONGTEXT (4GB) 长文字 保存书籍、以年为单位的日志

  • ENMUS
    1. enum
    2. set

• Numeric Types (数字、数学)

  • 整数类

    1. TINYINT (-128,127)短整数
    2. UNSIGNED TINYINT(0,255) 无符号短整数,可预防负值
    3. SMALLINT(-32k,32k) 小整数
    4. MEDIUMINT(-8M,8M) 中整数
    5. INT(-2B,2B)
    6. BIGINT(-9Z,9Z)

    类型	存储（字节）	签名的最小值	最小值无符号	签署的最大值	最大值无符号
    TINYINT	1	-128	0	127	255
    SMALLINT	2	-32768	0	32767	65535
    MEDIUMINT	3	-8388608	0	8388607	16777215
    INT	4	-2147483648	0	2147483647	4294967295
    BIGINT	8	-263	0	263-1	264-1

  • 小数

    1. DECIMAL(总位数,小数位数) 小数点后位数是固定的 DECIMAL(9,2) ⇒ 1234567,89 代表总共九位数小数位俩位

  • BOOLEAN

    实际为整数: 1为true 0为false

• Date and Time Types (日期时间)

  1. DATE(保存日期没有时间部分)
  2. TIME(保存时间)
  3. DATETIME(保存日期时间)
  4. TIMESTAMP (只能保存到2038年)
  5. YEAR()

• Blob Types(存放二进制) 一般不存存在本地 ，本地永远比库中快

  1. TINYBLOB 255b
  2. BLOB 65KB
  3. MEDIUMBLOB 16MB
  4. LONGBLOB

• Spatial Types (地理数据)

• JSON Types

  1. JSON_OBJECT(’weight’,10,’dimensions’,JSON_ARRAY(1,2,3),’manufacturer’,JSON_OBJECT(’name’,’sony’))
  2. 查询JSON中对象值

  
  SELECT product_id,JSON_EXTRACT(properties, '$.weight') FROM products WHERE product_id = 1;
  
  -- 使用列路径操作符
  
  SELECT product_id,properties ->'$.weight' FROM products WHERE product_id = 1;
  
  -- 使用列路径操作符去掉引号
  SELECT product_id,properties ->> '$.weight' FROM products WHERE product_id = 1;
  
  

  3.修改或添加JSON中单个值

  
  UPDATE products SET properties = JSON_SET(properties,'$.weight',20,'$.age',10)
  

  1. 删除JSON某个值

  UPDATE products SET properties = JSON_REMOVE(properties,'$.age')
  

数据库设计

1. 数据建模

(1). 理解分析需求

(2). 概念性建模

(3).数据结构

(4). 创建数据实体模型

2. 遵循的原则(范式)

1. 每个单元格都应该是单一值，并且列不重复
2. 必须满足第一原则,没有依赖任何关系的其他子集的非主键字段
3. 必须满足第二原则，一个表中字段不应该依赖同表其他字段推导而来

3. 创建数据库语句

--创建数据库--
CREATE DATABASE IF NOT EXISTS name; 
-- 创建表 --
CREATE TABLE IF NOT EXISTS name 
(
 customer_id INT PRIMARY KEY AUTO_INCRMENT,
 first_name VARCHAR(50) NOT NULL,
 points INT NOT NULL DEFAULT 0,
 email VARCHAR(255) NOT NULL UNIQUE

); 
--修改表--

--添加一列且在firstname后面一行--
ALTER TABLE name
 ADD last_name VARCHAR(50) NOT NULL AFTER first_name;

--修改列--
ALTER TABLE name
 MODIFY COLUMN last_name VARCHAR(55) NOT NULL;

--删除列--
ALTER TABLE name
 DROP last_name;

创建关联关系

--新建表时关联字段--
CREATE TABLE orders 
(
 order_id INT PRIMARY KEY,
 customer_id INT NOT NULL,
 FOREIGN KEY fk_orders_customers (customer_id) 创建外键名称fk_orders_customers并且关联到customer_id字段
   REFERENCES customers (customer_id) 需要关联的表和对应字段
   ON UPDATE CASCADE 
   ON DELETE NO ACTION
)

--修改表中字段为关联字段--
ALTER TABLE orders 
  ADD FOREIGN KEY fk_orders_customers (customer_id)
  REFERENCES customers (customer_id) 需要关联的表和对应字段
  ON UPDATE CASCADE 
  ON DELETE NO ACTION

--删除关联外键--
ALTER TABLE orders 
  DROP  FOREIGN KEY fk_orders_customers

索引

创建索引

主键默认创建索引(聚合索引)，后续添加的索引为从属索引会记录索引字段以及主键字段。

CREATE INDEX idx_name ON tablename (column);

EXPLAIN SELECT * FROM table; 检查查询了多少rows

SHOW INDEXES IN table; 查看当前表所有索引

字符串索引

使用COUNT来测试保存几个字符合适

CREATED INDEX idx_name ON table (last_name(保存多少字符));

SELECT 
  COUNT(DISTINCT LEFT(last_name,1)), 检查一个字符的唯一值与总数count(*)的差距
  COUNT(DISTINCT LEFT(last_name,5)), 逐渐递增 查到与总数最接近最合适的值
FROM table;

全文索引

用来完成复杂和有弹性的检索。

有俩种模式一种是自然语言模式(默认),一种是布尔模式(可以包含或者排除某些词)

--创建全文索引--
CREATE FULLTEXT INDEX idx_name ON posts (title,body);

--使用全文索引函数查询,其中MATCH(title, body) AGAINST('查询的文字')可以计算出关联度float类型0-1 --
SELECT *, MATCH(title, body) AGAINST('查询的文字') FROM posts WHERE MATCH(title, body) AGAINST('查询的文字');

--布尔模式 减号代表需要过滤的 加号代表需要包含的 双引号代表精确包含这个词--

SELECT *, MATCH(title, body) AGAINST('查询的文字') FROM posts WHERE MATCH(title, body) AGAINST('查询的文字 -文字 +文字 “文字”' IN BOOLEAN MODE);

组合索引 最多包含16个字段

表中出现多索引的时候 最好是使用组合索引 单独创建多个索引会浪费磁盘空间

--创建组合索引--
CREATE INDEX idx_name1_name2 ON table (name1,name2); 字段顺序也很重要

SELECT id FROM table WHERE name1 = '' AND name2 > 100;

组合索引中顺序问题

原则

1. 将最常用的列放到前面
2. 将高唯一性的列放到前面 会收窄检索范围提升性能
3. 去考虑每个查询所有到的索引看那个合适

--可以在查询语句中自选使用那种索引--
SELECT id FROM table USE INDEX (idx_name) WHERE name1 = '' AND name2 > 100;

索引会出现的问题优化

1.OR的情况还是会扫描表里所有列

--优化前--
EXPLAIN SELECT customer_id FROM table WHERE points > 2000 OR state = 'CA';

--优化后--
EXPLAIN SELECT customer_id FROM table WHERE points > 2000 
    UNION
    SELECT customer_id FROM table WHERE state = 'CA';

2.表达式的情况也会扫描表里所有列

--优化前--
EXPLAIN SELECT customer_id FROM table WHERE points + 10 > 2010;

--优化后--
EXPLAIN SELECT customer_id FROM table WHERE points > 2000 

想利用索引必须单独写查询条件

索引排序

索引字段排序比非索引字段排序服务器上下查询性能消耗少10倍

多字段排序

SELECT * FROM table ORDER BY points DESC, state DESC;

防止出现重复索引

1.首先查看SHOW INDEXES IN table; 查看当前表所有索引

2.如果有了(A,B)索引还有A索引那么A索引就是重复索引

3.如有(B,A)还有A那么就是正常的

删除索引

DROP INDEX idx_name ON table

数据库账号与权限

创建账号

--只能在这个地址下访问--

CREATE USER john@'%.dwstack.top'; 

--创建无限制账号并设置密码--
CREATE USER john IDENTIFIED BY '1234'; 要使用强密码 越长越好

查看账号

SELECT * FROM mysql.user;

删除账号

DROP USER john@'%.dwstack.top';

修改密码

SET PASSWORD FOR john = '1234';

设置权限

1.设置语句与表的权限

--创建用户--
CREATE USER Bob IDENTIFIED BY '1234;

--为用户设置操作权限--
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON sql_store.* --为用户设置可操作表--
TO Bob; --选择那个用户，如果有IP限制需要加上--

2.设置管理员权限

GRANT ALL
ON *.* --为用户设置可操作表--
TO Bob; --选择那个用户，如果有IP限制需要加上--

3.查看权限

show grants for john;

4.回收权限

REVOKE CREATE VIEW ON sql_store.* FROM moon_app;

group by权限解决

在my.cnf中加入