MYSQL笔记及练习
一、服务的登录和退出方法
登录:
cmd语句:mysql 【-h 主机名 -p 端口号】 -u 用户名 -p 密码 【】中内容可省略
退出:
exit 或 ctrl + C
二、基础知识
基础的基础:
- 查看当前所有数据库:
show databases;
- 打开指定库:
use <库名>;
- 查看当前库的所有表:
show tables;
- 查看其他库的所有表:
show tables from <库名>;
- 查看表的结构:
desc 表名;
- 注释:
#单行注释方法1:用“#”表示
-- 单行注释方法2:用“-- ”表示(--后面别忘了加空格)
/*
多行注释用“斜杠+星号and星号+斜杠”表示
多行注释用“斜杠+星号and星号+斜杠”表示
多行注释用“斜杠+星号and星号+斜杠”表示
*/
表的创建
- 创建表
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));
- 插入数据
#方法1:
START TRANSACTION;
INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服',1000, 500, '2009-09-20');
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;
#方法2:
INSERT INTO Product VALUES
('0001', 'T恤衫', '衣服',1000, 500, '2009-09-20'),
('0002', '打孔器', '办公用品',500, 320, '2009-09-11'),
('0003', '运动T恤', '衣服',4000, 2800, NULL),
('0004', '菜刀', '厨房用具',3000, 2800, '2009-09-20'),
('0005', '高压锅', '厨房用具',6800, 5000, '2009-01-15'),
('0006', '叉子', '厨房用具',500, NULL, '2009-09-20'),
('0007', '擦菜板', '厨房用具',880, 790, '2008-04-28'),
('0008', '圆珠笔', '办公用品',100, NULL,'2009-11-11');
-
删除
——表的删除:
drop <表名>
,将表完全删除
——数据删除:delete from <表名>
,留下表(容器),而删除表中数据,可搭配where语句
——数据删除:truncate <表名>
,只能删除表中全部数据 -
表定义的更新
表创建完后发现少了几列,无需把表删除再重新创建,只需使用变更表定义的ALTER TABLE 语句即可。
添加列:
ALTER TABLE <表名> ADD COLUMN <列的定义>;
#在 Product 表中添加product_name_pinyin,该列可以存储 100 位的可变长字符串。
ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);
删除列:
ALTER TABLE <表名> DROP COLUMN <列名>;
#删除上述添加的product_name_pinyin列
ALTER TABLE Product DROP COLUMN product_name_pinyin;
三、查询基础
select语句
- select语句基础:
SELECT <列名>,…… FROM <表名>;
- 为列设定别名:
SELECT product_id AS "商品编号",
product_name AS "商品名称",
purchase_price AS "进货单价"
FROM Product;`
商品编号 | 商品名称 | 进货单价
--------+--------+--------
0001 | T恤衫 | 500
0002 | 打孔器 | 320
0003 | 运动T恤 | 2800
0004 | 菜刀 | 2800
0005 | 高压锅 | 5000
0006 | 叉子 |
0007 | 擦菜板 | 790
0008 | 圆珠笔 |
- 常数的查询
SELECT '商品' AS string, 38 AS number, '2009-02-24' AS date,
product_id, product_name
FROM Product;
string | number | date | product_id | product_name
---------+-----------+--------------+-------------+--------------
商品 | 38 | 2009-02-24 | 0001 | T恤衫
商品 | 38 | 2009-02-24 | 0002 | 打孔器
商品 | 38 | 2009-02-24 | 0003 | 运动T恤
商品 | 38 | 2009-02-24 | 0004 | 菜刀
商品 | 38 | 2009-02-24 | 0005 | 高压锅
商品 | 38 | 2009-02-24 | 0006 | 叉子
商品 | 38 | 2009-02-24 | 0007 | 擦菜板
商品 | 38 | 2009-02-24 | 0008 | 圆珠笔
- 从结果中删除重复行–distinct
#删除product_type的重复行
SELECT DISTINCT product_type
FROM Product;
product_type
---------------
厨房用具
衣服
办公用品
- where语句指定查询
#可以不选取作为查询条件的列,即select语句中可以不包含where中所查询的列
SELECT product_name
FROM Product
WHERE product_type = '衣服';
product_name
---------------
T恤衫
运动T恤
算数运算符(+ - * /)
SELECT product_name, sale_price,sale_price * 2 AS "sale_price_x2"
FROM Product;
product_name | sale_price | sale_price_x2
---------------+-------------+----------------
T恤衫 | 1000 | 2000
打孔器 | 500 | 1000
运动T恤 | 4000 | 8000
菜刀 | 3000 | 6000
高压锅 | 6800 | 13600
叉子 | 500 | 1000
擦菜板 | 880 | 1760
圆珠笔 | 100 | 200
特别注意
5 + NULL
10 - NULL
1 * NULL
4 / NULL
NULL / 9
NULL / 0
以上结果均为 NULL,所有包含 NULL 的计算,结果肯定是 NULL
比较运算符(< > =)
# WHERE子句的条件表达式中可以使用计算表达式
SELECT product_name, sale_price, purchase_price
FROM Product
WHERE sale_price - purchase_price >= 500;
特别注意
-
对字符串使用不等号时:
1<10<11<2<222<3 -
关于NULL:
不能对NULL使用比较运算符
判断是否为 NULL:IS (NOT) NULL
# 选取NULL的记录
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IS NULL;
product_name | purchase_price
---------------+---------------
叉子 |
圆珠笔 |
逻辑运算符(not、and、or)
例:查询“商品种类为办公用品”且“登记日期是 2009 年 9 月 11 日或者 2009 年 9 月 20 日”
SELECT product_name, product_type, regist_date
FROM Product
WHERE product_type = '办公用品'
AND ( regist_date = '2009-09-11'
OR regist_date = '2009-09-20'); #使用()使or运算符优先于and运算符
# 正确结果
product_name | product_type | regist_date
---------------+--------------+------------
打孔器 | 办公用品 | 2009-09-11
# 若不加(),结果为
product_name | product_type | regist_date
---------------+--------------+------------
T恤衫 | 衣服 | 2009-09-20
打孔器 | 办公用品 | 2009-09-11
菜刀 | 厨房用具 | 2009-09-20
叉子 | 厨房用具 | 2009-09-20
特别注意
- and运算符优先于or运算符
- 含有null时的真值结果为unknown
四、聚合与排序
数据聚合:count、sum、avg、max、min
- count:参数列不同计算结果也会不同
SELECT COUNT(*), COUNT(col_1)
FROM NullTbl;
count | count
-------+------
3 | 0
特别注意
COUNT(*)会得到包含null的数据行数,而COUNT(<列名>)会得到NULL之外的数据行数。
- sum、avg:会事先删除null再进行计算
SELECT AVG(sale_price), AVG(purchase_price)
FROM Product;
avg | avg
----------------------+---------------------
2097.5000000000000000 | 2035.0000000000000000
# 此时均值计算的分母是值的个数,即去除null之后的个数
分组:group by
- null会单独被分为一组
SELECT purchase_price, COUNT(*)
FROM Product
GROUP BY purchase_price;
purchase_price | count
----------------+-------
| 2
320 | 1
500 | 1
5000 | 1
2800 | 2
790 | 1
- group by 和where
SELECT purchase_price, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY purchase_price;
purchase_price | count
----------------+------
500 | 1
2800 | 1
为聚结果指定条件:having
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING COUNT(*) = 2;
product_type | count
--------------+------
衣服 | 2
办公用品 | 2
特别注意
where只能指定记录(行)的条件,不能用来指定组的条件
having集合指定条件(必须包含在聚合函数中,即group by 语句下)
顺序:SELECT → FROM → WHERE → GROUP BY → HAVING
排序:order by
- 子句顺序:
1.SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 →5.HAVING 子句 → 6. ORDER BY 子句 - 默认升序,降序加desc
- 可以使用SELECT子句中未使用的列和聚合函数
- 指定多个排序,优先使用左侧
五、数据的更新
数据插入:insert
- insert 语句
INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);
- 单行插入和多行插入
# 单行INSERT
INSERT INTO ProductIns VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO ProductIns VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO ProductIns VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
# 多行INSERT
INSERT INTO ProductIns VALUES
('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
('0003', '运动T恤', '衣服', 4000, 2800, NULL),
('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
- 插入默认值:default
# 创建表时 按default< 默认值 >设置初始默认值
CREATE TABLE ProductIns
(product_id CHAR(4) NOT NULL,
(略)
sale_price INTEGER DEFAULT 0, # 销售单价的默认值设定为0;
(略)
PRIMARY KEY (product_id));
# 插入数据default表示默认值,也可省略含有默认值的列
INSERT INTO ProductIns
(product_id, product_name, product_type, sale_price,purchase_price, regist_date)
VALUES ('0007', '擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28');
- 从其他表中复制数据:insert… select 语句
# 从其他表中复制数据
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 bdm6823267_db.product;
select * from productcopy;
数据删除: delete
- drop (table) 语句可以将表完全删除
- delete 语句会留下表(容器),而删除表中数据,可搭配where语句
delete from <表名>; - truncate<表名>;只能删除表中全部数据
数据更新: update
- update语句
UPDATE <表名>
SET <列名> = <表达式>
WHERE <条件>;
例:
UPDATE Product
SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';
SELECT * FROM Product ORDER BY product_id;
product_id | product_name | product_type | sale_price | purchase_price | regist_date
-----------+--------------+--------------+-----------+---------------+-----------
0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-10-10
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-10-10
0004 | 菜刀 | 厨房用具 | 30000 | 2800 | 2009-10-10
0006 | 叉子 | 厨房用具 | 5000 | | 2009-10-10
0007 | 擦菜板 | 厨房用具 | 8800 | 790 | 2009-10-10
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-10-10
- 多列更新
# 方法1:使用逗号对列进行分隔排列
UPDATE Product
SET sale_price = sale_price * 10,
purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';
# 方法2:将列用()括起来
UPDATE Product
SET (sale_price, purchase_price) = (sale_price * 10, purchase_price / 2)
WHERE product_type = '厨房用具'
事务
- commit:提交
- rollback:回滚
六、复杂查询
视图
- 创建视图:
CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……) AS <SELECT语句>
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
- 使用视图
SELECT product_type, cnt_product
FROM ProductSum;
product_type | cnt_product
--------------+------------
衣服 | 2
办公用品 | 2
厨房用具 | 4
子查询:一次性查询
创建子查询
# 在FROM子句中直接书写定义视图的SELECT语句
SELECT product_type, cnt_product
FROM ( SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type ) AS ProductSum;
标量子查询
- 标量子查询必须而且只能返回 1 行 1列的结果,不能返回多行结果
- 创建标量子查询
(where子句中不可以使用聚合函数)
# 选取出售价>全部商品平均售价的商品
SELECT AVG(sale_price)
FROM Product; #商品平均售价 2097
SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product);
product_id | product_name | sale_price
------------+-------------+-----------
0003 | 运动T恤 | 4000
0004 | 菜刀 | 3000
0005 | 高压锅 | 6800
关联子查询
创建关联子查询
select product_type,avg(sale_price)
from productcopy
group by product_type; #各商品种类平均售价
# 选取各商品售价>该商品种类平均售价的商品
SELECT product_id, product_name, sale_price
FROM productcopy as p1
WHERE sale_price > (select avg(sale_price)
from productcopy as p2
where p1.product_type = p2.product_type
group by product_type);
七、函数、谓词、CASE表达式
函数
算数函数 P199
- 绝对值:
abs(数值) - 求余:
mod(被除数,除数) - 四舍五入:
round(对象数值,保留小数的位数)
字符串函数 P202
- 拼接:
字符串1 II 字符串2 - 字符串长度:
length(字符串) - 小写转换:
lower(字符串) - 大写转换:
upper(字符串) - 替换:
replace(对象字符串,替换前字符串,替换后字符串)
----将对象的一部分替换成其他
SELECT str1, str2, str3,
REPLACE(str1, str2, str3) AS rep_str
FROM SampleStr;
str1 | str2 | str3 | rep_str
----------+------+------+---------
opx | rt | |
abc | def | |
山田 | 太郎 | 是我 | 山田
aaa | | |
| xyz | |
@!#$% | | |
ABC | | |
aBC | | |
abc太郎 | abc | ABC | ABC太郎
abcdefabc | abc | ABC | ABCdefABC
micmic | i | I | mIcmIc
- 截取:
substring(对象字符串 from 截取的起始位置 for 截取的字符数)
eg:substring(abcdrf,3,2) 结果:cd
日期函数 P190
- 当前日期:
current_date - 当前时间:
current_time - 当前日期和时间:
current_timestamp - 截取日期元素:
extract(日期元素 from 日期)
#当前日期和时间:
select current_timestamp();
#截取当前日期元素:
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 想要转换的数据类型) - 将null转换为其他值:
coalesce(数据1,数据2,数据3…)
返回参数中第一个非NULL的字段值,如果都为空值,则返回NULL