MySQL基础教程（第二版）学习笔记

satomiku

已于 2023-11-15 14:59:13 修改

阅读量92

点赞数

文章标签： mysql 数据库

于 2023-11-14 17:59:05 首次发布

本文链接：https://blog.csdn.net/satomiku/article/details/134404774

版权

-- ---------------------unit1----------------------

show databases;

use shop;

show tables;

-- create table

create table Product(

product_id char(4) not null,

product_name varchar(100) not null,

product_type varchar(32) not null,

sale_price integer,

purchase_price integer,

regist_date date,

primary key (product_id)

);

show columns from product;

alter table Product add column on_sale varchar(1) not null;

alter table Product drop column on_sale;

-- DML:insert data

start transaction;

insert into product values ('0001', 'T-Shirt', 'Clothes', 1000, 500, '2023-10-26');

INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');

INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, null);

INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');

INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');

INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');

INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');

commit;

-- alter table's name

rename table product_1 to Product;

-- ------------------------unit2---------------------------

-- 2.1

-- 输出

select product_id, product_name, purchase_price from product;

-- query for all columns

select * from product;

-- use 'AS' to create another name for column

select product_id as id,

product_name as name,

purchase_price as price

from Product;

select product_id as "商品编号",

product_name as "商品名称",

purchase_price as "进货单价"

from Product;

select "商品" as string,

39 as number,

'2023-03-27' as date,

product_id,

product_name

from product;

-- 使用DISTINCT突出明显元素

select distinct product_type from product;

select distinct regist_date from product; -- null也能合并

select distinct product_type, regist_date from product;

-- where语句：查询特定条件

select * from product where product_type = "衣服"

-- 注释

-- 单行注释

/* 多行

注释 */

-- 2.2 算术运算符和比较运算符

select product_name as name,

sale_price as price,

sale_price * 2 as "double price"

from product;

select product_name, sale_price as price from product

where purchase_price >= 500;

select product_name, sale_price - purchase_price as margin from product

where sale_price - purchase_price > 500;

-- 2.2 字符串的大小比较

create table chars(chr char(3) not null, primary key(chr));

start transaction;

insert into chars values('1');

insert into chars values('2');

insert into chars values('3');

insert into chars values('10');

insert into chars values('11');

insert into chars values('222');

commit;

select chr from chars where chr > '2'; -- answer is '222' '3'.

-- is null, is not null: 取出/不要null的记录

select product_name, purchase_price

from product

where purchase_price is null;

select product_name, purchase_price

from product

where purchase_price is not null;

-- 2.3逻辑运算符

-- not: 取非

select product_name, product_type, sale_price from product

where not sale_price >= 1000;

-- 用and和or进行多条件查询

select product_name from product

where sale_price >= 1000 and purchase_price >= 500;

-- 括号改变优先级

select product_name from product

where product_type = "办公用品"

and (regist_date = "2009-09-11" or regist_date = "2009-09-20");

-- ----------------- unit3 --------------------

-- --3.1 对表进行聚合查询

-- count/sum/avg/max/min

select count(*) from product; -- 8

select count(purchase_price) from product; -- 6

select sum(sale_price) from product;

select sum(sale_price), sum(purchase_price) from product;

select avg(sale_price) from product;

select max(purchase_price), min(purchase_price) from product;

/* 暂时无法将null也纳入计算范围，具体方法详见 unit6 */

-- 用聚合函数distinct 删除重复值

select count(distinct product_type) from product;

select sum(sale_price), sum(distinct sale_price) from product;

-- --3.2 对表进行分组（使用group by分组）

select product_type, count(*)

from product

where product_type = '厨房用具' or product_type = '办公用品'

group by product_type;

/* 注意：①使用group by的时候，select的列名可少不可多（聚合键除外）

②group by中不能使用别名（如把‘product_type’简写成‘pro’）（2023.11：MySQL经测试可以使用别名）

③where中不能使用聚合函数（count、sum、avg、max、min。。。只有select, having, order by可以）

-- --3.3 为聚合结果指定条件（having）

select product_type, count(*)

from product

group by product_type

having count(*) >= 2;

select product_type, avg(sale_price) as avgsp

from product

group by product_type

having avgsp >= 2500;

-- --3.4 排序（order by）

select product_id, product_name, sale_price, purchase_price

from product

order by sale_price;

select product_id, product_name, sale_price, purchase_price

from product

order by sale_price desc;

-- 两个以上的，更详细的排序

select product_id, product_name, sale_price, purchase_price

from product

order by sale_price, product_id ;

-- null默认排前面

select product_id, product_name, sale_price, purchase_price

from product

order by purchase_price;

-- order by 允许使用别名

select product_id as pid,

product_name as pn,

sale_price as sp,

purchase_price as pp

from product

order by pp desc;

-- ----------------- unit4 插入、删除、更新 --------------------

-- 4.1 数据的插入 insert

-- 创建表

create table productins

(

product_id char(4) not null,

product_name varchar(100) not null,

product_type varchar(32) not null,

sale_price integer default 0,

purchase_price integer ,

regist_date date ,

primary key (product_id)

);

-- 从其他表中复制数据

CREATE TABLE ProductCopy

(product_id CHAR(4) NOT NULL,

product_name VARCHAR(100) NOT NULL,

product_type VARCHAR(32) NOT NULL,

sale_price INTEGER ,

purchase_price INTEGER ,

regist_date DATE ,

PRIMARY KEY (product_id));

insert into productcopy

select * from product;

-- insert ... select 也可以使用 where / grope by等语句

create table producttype(

product_type varchar(32) not null,

sum_sale_price integer ,

sum_purchase_price integer ,

primary key (product_type)

);

insert into producttype

select product_type, sum(sale_price), sum(purchase_price)

from product

group by product_type;

select * from producttype;

-- 4.2 数据的删除 delete

-- 清空

delete from productcopy;

-- 删除部分行

delete from productcopy

where sale_price >= 4000;

-- 4.3 数据的更新 update

-- 更改整列数据

update productcopy

set regist_date = '2009-10-10';

-- 指定条件

update productcopy

set sale_price = sale_price * 10

where product_type = '厨房用具';

-- 将有值的记录更新为null （null清空）

update productcopy

set regist_date = null

where product_id = '0008';

-- 同时对多列进行update(法1)

update productcopy

set sale_price = sale_price * 10,

purchase_price = purchase_price / 2

where product_type = '厨房用具';

-- 同时对多列进行update(法2, mysql不能使用，postgreSQL 和 DB2 可以使用)

update productcopy

set (sale_price, purchase_price) = (sale_price * 10, purchase_price / 2)

where product_type = '厨房用具';

-- 把之前的英文名称T-shirt记录改为中文

update product

set product_name = 'T恤衫',

product_type = '衣服',

regist_date = '2009-09-20'

where product_id = '0001';

-- 4.4事务

/* 现在，请大家把自己想象为管理 Product（商品）表的程序员或者

软件工程师。销售部门的领导对你提出了如下要求。

“某某，经会议讨论，我们决定把运动 T 恤的销售单价下调 1000 日元，

同时把 T 恤衫的销售单价上浮 1000 日元，麻烦你去更新一下数据库。” */

start transaction;

update product

set sale_price = sale_price - 1000

where product_name = "运动T恤";

update product

set sale_price = sale_price + 1000

where product_name = "T恤衫";

commit;

-- 事务回滚 rollback

start transaction;

update product

set sale_price = sale_price - 1000

where product_name = '运动T恤';

update product

set sale_price = sale_price + 1000

where product_name = 'T恤衫';

rollback;

实际上，几乎所有的数据库产品的事务都无需开始指令,像这样不使用指令而悄悄开始事务

的情况下，应该如何区分各个事务呢？通常会有如下两种情况:

A 每条SQL语句就是一个事务（自动提交模式）

B 直到用户执行COMMIT或者ROLLBACK为止算作一个事务

默认使用自动提交模式的DBMS 有 SQL Server、PostgreSQL 和 MySQL 等。

在默认使用 B 模式的 Oracle 中，事务都是直到用户自己执行提交或者回滚指令才会结束。

-- ----------------- unit5 复杂查询--------------------

-- 5.1 视图

-- 创建视图

create view productsum (product_type, cnt_product)

select product_type, count(*)

from product

group by product_type ;

-- 使用视图

select product_type, cnt_product

from productsum;

-- 视图上的视图（多重视图）

create view productsumjim(product_type, cnt_product)

select product_type, cnt_product

from productsum

where product_type = '办公用品';

虽然语法上没有错误，但是我们还是应该尽量避免在视图的基础上创建视图。

对多数 DBMS 来说，多重视图会降低 SQL 的性能。因此，希望大家（特别是

刚刚接触视图的读者）能够使用单一视图。

-- 另外，1.视图虽然能使用任何select语句，但唯独不能使用order by。

-- 2.经过聚合得到的视图基本无法更新

-- 非聚合视图的更新！注意！更新视图后，原表也会进行更新。

create view productjim

select *

from product

where product_type = '办公用品';

insert into productjim

values ('0009', '印章', '办公用品', 95, 10, '2009-11-30');

-- 删除视图 drop view (若删除一级视图，则二级视图也会失效)

drop view productsum;

-- 复原原表

delete from product

where product_id = '0009';

-- 5.2 子查询

-- 子查询就是一次性视图（select语句）

-- 正常创建视图，并进行查询（这不叫子查询）

create view productsum(product_type, cnt_product)

select product_type, count(*)

from product

group by product_type;

select * from productsum;

-- 或者使用子查询：

select *

from (select product_type, count(*) as cnt_product

from product

group by product_type)

as productsum_2; -- 此 select创建的视图用完就消失，不会保存，属于子查询。

-- 子查询多层嵌套(不建议)

select *

from (select *

from (select product_type, count(*) as cnt_product

from product

group by product_type) as productsum_1

where cnt_product = 4) as productsum_2;

-- 标量子查询

/* 只返回一行一列的数据，其返回值适用于 = 或者 <> 这样

需要单一值的比较运算符之中 */

-- 在where子句中使用标量子查询（因为where子句不能使用聚合函数）

select product_name, sale_price

from product

where sale_price > (select avg(sale_price)

from product);

-- 练习：查询种类内平均售价高于所有商品的平均售价的数据

select product_type, avg(sale_price) as avgsp

from product

group by product_type

having avgsp > (select avg(sale_price) from product);

-- 5.3 关联子查询

-- 场景：查询高于自己种类平均售价的商品。

select product_type, product_name, sale_price

from product as P1

where sale_price > (select avg(sale_price) from product as P2

where p1.product_type = p2.product_type

group by product_type);

-- ----------------- unit6 函数、谓词、CASE表达式--------------------

-- 6.1 函数

-- 建表

create table SampleMath

(m numeric (10,3),

n integer,

p integer);

start transaction;

insert into samplemath values (500, 0, null);

INSERT INTO SampleMath(m, n, p) VALUES (-180, 0, NULL);

INSERT INTO SampleMath(m, n, p) VALUES (NULL, NULL, NULL);

INSERT INTO SampleMath(m, n, p) VALUES (NULL, 7, 3);

INSERT INTO SampleMath(m, n, p) VALUES (NULL, 5, 2);

INSERT INTO SampleMath(m, n, p) VALUES (NULL, 4, NULL);

INSERT INTO SampleMath(m, n, p) VALUES (8, NULL, 3);

INSERT INTO SampleMath(m, n, p) VALUES (2.27, 1, NULL);

INSERT INTO SampleMath(m, n, p) VALUES (5.555,2, NULL);

INSERT INTO SampleMath(m, n, p) VALUES (NULL, 1, NULL);

INSERT INTO SampleMath(m, n, p) VALUES (8.76, NULL, NULL);

COMMIT;

select * from samplemath;

create table SampleStr

(str1 varchar(40),

str2 varchar(40),

str3 varchar(40));

start transaction;

insert into samplestr values ('opx','rt',null);

INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc' ,'def' ,NULL);

INSERT INTO SampleStr (str1, str2, str3) VALUES ('山田' ,'太郎' ,'是我');

INSERT INTO SampleStr (str1, str2, str3) VALUES ('aaa' ,NULL ,NULL);

INSERT INTO SampleStr (str1, str2, str3) VALUES (NULL ,'xyz',NULL);

INSERT INTO SampleStr (str1, str2, str3) VALUES ('@!#$%' ,NULL ,NULL);

INSERT INTO SampleStr (str1, str2, str3) VALUES ('ABC' ,NULL ,NULL);

INSERT INTO SampleStr (str1, str2, str3) VALUES ('aBC' ,NULL ,NULL);

INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc太郎' ,'abc' ,'ABC');

INSERT INTO SampleStr (str1, str2, str3) VALUES ('abcdefabc' ,'abc' ,'ABC');

INSERT INTO SampleStr (str1, str2, str3) VALUES ('micmic' ,'i' ,'I');

COMMIT;

select * from samplestr;

-- ------算术函数:------

-- abs绝对值

select m, abs(m) as abs_col from samplemath;

-- mod求余

select n, p,

mod(n, p) as mod_n_p

from samplemath ;

-- round(值，保留几位小数) 四舍五入

select m, round(m,2) as '保留两位小数'

from samplemath;

-- ------字符串函数:------

-- concat(字符串1, 字符串2, ...) 拼接

select str1, str2, concat(str1, str2) as str_concat

from samplestr;

select str1, str2, str3, concat(str1, str2, str3) as str_concat

from samplestr;

-- length(str) 、 char_length(str) 计算字符串长度

select str1, length(str1) as len_str1

from samplestr; -- length里，汉字这样的全角字符会占用 2 个以上的字节

select str1, char_length(str1) as charlen_str1

from samplestr;

-- lower(str) upper(str)

select str1, lower(str1) as low_str

from samplestr;

select str1, upper(str1) as upr_str

from samplestr;

-- replace（对象str，要替换的部分，替换内容）把一部分字符串换成另一部分

select str1, str2, str3, replace(str1, str2, str3)

from samplestr;

-- substring(对象str from 起始位置 for 截取数量)

select str1, substring(str1 from 3 for 2) as sub_str

from samplestr;

-- where str in ('', '', '', ...)

-- ------日期函数:------

select current_date; -- 获取当前日期

select current_time; -- 获取当前时间

select current_timestamp -- 当前日期+时间

-- extract(元素 from 日期函数) 提取日期函数内的元素

select current_timestamp,

extract(year from current_timestamp) as year,

extract(month from current_timestamp) as month,

extract(day from current_timestamp) as day,

extract(hour from current_timestamp) as hour,

extract(minute from current_timestamp) as minute,

extract(second from current_timestamp) as second;

-- ------转换函数:------

-- cast（转换前的值 as 想转换的数据类型）数据类型转换

select cast('0001' as signed integer) as int_col; -- 有符号整数

select cast('2009-12-14' as date) as date_col;

-- coalesce(数据1，数据2，数据3……) 返回从左往右第一个不是null的值

select coalesce(null, 1) as col_1,

COALESCE(NULL, 'test', NULL) AS col_2,

COALESCE(NULL, NULL, '2009-11-01') AS col_3;

select coalesce(str2, 'IS NULL') as coa_str2

from samplestr;

-- 6.2 谓词

-- 建表

create table SampleLike

(strcol varchar(10));

start transaction;

insert into samplelike values ('abcddd');

insert into samplelike values ('dddabc');

insert into samplelike values ('abdddc');

insert into samplelike values ('abcdd');

insert into samplelike values ('ddabc');

insert into samplelike values ('abddc');

commit;

-- like % _ 模糊查询

select strcol

from samplelike

where strcol like 'ddd%';

select strcol

from samplelike

where strcol like '%ddd%';

select strcol

from samplelike

where strcol like '%ddd'; -- % 任意字符

select strcol

from samplelike

where strcol like 'abc__'; -- _ 任意1个字符

-- between 范围查询

select product_name, sale_price

from product

where sale_price between 100 and 1000; -- between特点：包含临界值

-- is null, is not null 判断是否为null

select product_name, purchase_price

from product

where purchase_price is null;

select product_name, purchase_price

from product

where purchase_price is not null;

-- in, not in 把多条or变成一条in or……，or…… --》 in （……，……）

select product_name, purchase_price

from product

where purchase_price in (320, 500, 5000);

select product_name, purchase_price

from product

where purchase_price not in (select max(purchase_price)

from product); -- 标量子查询运用：去掉最大值

-- in谓词 + 子查询进行跨表查询 --

CREATE TABLE ShopProduct

(shop_id CHAR(4) NOT NULL,

shop_name VARCHAR(200) NOT NULL,

product_id CHAR(4) NOT NULL,

quantity INTEGER NOT NULL,

PRIMARY KEY (shop_id, product_id));

start TRANSACTION;

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);

COMMIT;

select product_id

from shopproduct

where shop_id = '000C'; -- ①

select product_name, sale_price

from product

where product_id in (select product_id

from shopproduct

where shop_id = '000C' -- ①

);

-- 练习：在product中选取在东京店（000A）以外销售的商品

select product_name, sale_price

from product p

where product_id not in (select product_id

from shopproduct

where shop_id = '000A'

);

-- exists exsits(关联子查询)

select product_name, sale_price

from product p

where exists (select * from shopproduct sp

where sp.shop_id = '000C' and sp.product_id = p.product_id

);

-- 对 exists 中，关联子查询使用 select * 的说明

select 1

from product p ; -- 返回一列列名和内容都为1 的列

select product_name, sale_price

from product p

where exists (select 1 from shopproduct sp

where sp.shop_id = '000C' and sp.product_id = p.product_id

); -- exists 只关心 where语句中的判断，因此即使 select 1 也不会影响结果

-- 可以把在 exists 的子查询中书写 SELECT * 当作 SQL 的一种习惯

-- 练习：用 not exist 在 product 中选取在东京店（000A）以外销售的商品名称、销售价格

select product_name, sale_price

from product p

where not exists (select * from shopproduct sp

where shop_id = '000A' and sp.product_id = p.product_id

);

-- 6.3 case (case表达式也是函数的一种，是SQL中数一数二的重要功能)

-- 搜索case 包含了简单case的所有功能

-- 用case将 A、B、C 和不同商品种类字段合并

select product_name,

case when product_type = '衣服' then concat('A:', product_type)

when product_type = '办公用品' then concat('B:', product_type)

when product_type = '厨房用具' then concat('C:', product_type)

else null -- 含义：上述情况之外时返回 null。

-- ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。

end as abc_product_type -- end不能省略。

from product p;

-- 用 case 输出一个行列转换表(相对于group by)

select product_type , sum(sale_price) as sum_price

from product

group by product_type;

/* sum_price_clothes | sum_price_kitchen | sum_price_office

------------------+-------------------+-----------------

5000 | 11180 | 600 */

select sum(case when product_type = '衣服' then sale_price else 0 end) as sum_price_clothes,

sum(case when product_type = '厨房用具' then sale_price else 0 end) as sum_price_kitchen,

sum(case when product_type = '办公用品' then sale_price else 0 end) as sum_price_office

from product p ;

-- ----------------- unit7 集合运算--------------------

CREATE TABLE Product2

(product_id CHAR(4) NOT NULL,

product_name VARCHAR(100) NOT NULL,

product_type VARCHAR(32) NOT NULL,

sale_price INTEGER ,

purchase_price INTEGER ,

regist_date DATE ,

PRIMARY KEY (product_id));

START TRANSACTION;

INSERT INTO Product2 VALUES ('0001', 'T恤衫' ,'衣服', 1000, 500, '2008-09-20');

INSERT INTO Product2 VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');

INSERT INTO Product2 VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);

INSERT INTO Product2 VALUES ('0009', '手套', '衣服', 800, 500, NULL);

INSERT INTO Product2 VALUES ('0010', '水壶', '厨房用具', 2000, 1700, '2009-09-20');

COMMIT;

-- 7.1 表的加减法

/* 集合运算注意事项

1. 列数要相同，数据类型要相同。

2. order by 只能在最后使用一次，其他select语句无限制。 */

-- union 表的加法