python--MySql 表记录的操作
表记录的增删改查
---插入表记录
全列插入：insert into 表名 values(...)
缺省插入：insert into 表名(列1,...) values(值1,...)
-- 插入一条数据 INSERT
INSERT [INTO] table_name (字段名称,...) VALUES(值,...);
---插入多条数据 INSERT
INSERT [INTO] table_name (字段名称,...) VALUES(值,...),
(值,...),
...
(值,...);
--SET插入
INSERT [INTO] table_name SET 字段名=值
---修改表记录 UPDATE
UPDATE table_name SET 字段=值,字段=值... WHERE 字句；
/* UPDATE语法可以用新值更新原有表行中的各列。 SET子句指示要修改哪些列和要给予哪些值。 WHERE子句指定应更新哪些行。 如没有WHERE子句，则更新所有的行。*/
---删除表记录
DELETE FROM table_name [WHERE 字句]；
/*
如果不跟where语句则删除整张表中的数据
delete只能用来删除一行记录
delete语句只能删除表中的内容，不能删除表本身，想要删除表，用drop
TRUNCATE TABLE也可以删除表中的所有数据，此语句首先摧毁表，再新建表。此种方式删除的数据不能在
事务中恢复。*/
TRUNCATE TABLE table_name;
查询表记录（重点）
准备表和记录：
CREATE TABLE table_name (
字段名称 字段数据类型 [约束]，
字段名称 字段数据类型 [约束]，
...
字段名称 字段数据类型 [约束]，
);
INSERT [INTO] table_name (字段名称，...) VALUES (值，...),
(值，...),
...
(值，...);
-- 查询语法：
SELECT *|field1,filed2 ... FROM table_name
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
对比where与having
-
-
- where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
- having是对group by的结果进行筛选
-
简单查询
-- （1）select [distinct] *|field1，field2，...... from table_name
-- 其中from指定从哪张表筛选，*表示查找所有列，也可以指定一个列
-- 表明确指定要查找的列，distinct用来剔除重复行。
-- （2）select 也可以使用表达式，并且可以使用: 字段 as 别名或者:字段 别名
注意：不加逗号的后果：
select name，JS from ExamResult;（√）
select name JS from ExamResult; -- what will happen?---->记得加逗号
使用where子句，进行过滤查询
where子句中可以使用：
-- 比较运算符：
> < >= <= <> != （后面两条都是表示不等于）
between 80 and 100 值在80到100之间
in(80,90,100) 值是80或90或100
like 'yuan%'
like '李___'
/* %表示任意多字符 _表示一个字符，两个_则表示两个字符：__ */
-- 逻辑运算符
在多个条件之间可以使用逻辑运算符 and or not
优先级
-
- 小括号，not，比较运算符，逻辑运算符
- and比or先运算，如果同时出现并希望先算or，需要结合()使用
order by排序
指定排序的列，排序的列既可是表中的列名，也可以是select 语句后指定的别名。
select *|field1,field2... from tab_name order by field [Asc|Desc]
-- Asc 升序、Desc 降序，其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。
group by分组查询
--按照字段分组，表示此字段相同的数据会被放到一个组中
--分组后，只能查询出相同的数据列，对于有差异的数据列无法出现在结果集中
--可以对分组后的数据进行统计，做聚合运算
-- 注意,按分组条件分组后每一组只会显示第一条记录
-- group by字句，其后可以接多个列名，也可以跟having子句,对group by 的结果进行筛选。
/*
having 和 where两者都可以对查询结果进行进一步的过滤，差别有：
<1>where语句只能用在分组之前的筛选，having可以用在分组之后的筛选；
<2>使用where语句的地方都可以用having进行替换
<3>having中可以用聚合函数，where中就不行。
*/
-- GROUP_CONCAT() 函数
SELECT id,GROUP_CONCAT(name),GROUP_CONCAT(JS) from ExamResult GROUP BY id;
聚合函数
--<1> 统计表中所有记录
-- COUNT(列名)：统计行的个数
-- SUM(列名)：统计该列的和
-- AVG(列名)：统计该列平均值
-- Max（列名）、Min（列名）：统计该列的最大值和最小值
limit记录条数限制
SELECT * from ExamResult limit 1; SELECT * from ExamResult limit 2,5; -- 跳过前两条显示接下来的五条纪录 SELECT * from ExamResult limit 2,2;
正则表达式
SELECT * FROM employee WHERE emp_name REGEXP '^yu';
SELECT * FROM employee WHERE emp_name REGEXP 'yun$';
SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';
练习
1、创建成绩表，字段包括：学生姓名，语文成绩，数学成绩，英语成绩
向表中插入多条数据；
查询：
（1） 查询所有学生的数学成绩和总成绩
(2) 查询所有学生的语文和数学成绩和，按从高到低排序
（2） 查询班级总成绩最高的学生姓名
（3） 查询班里所有姓李学生的总成绩最高的姓名
2、创建一张某超市的购物表，字段包括：商品名，购物价格，商品生茶日期，商品分类；
向该表中插入多条数据；
查询：（1）每一类商品花的总价格
（2）统计每类商品各有多少件
（2）统计水果花了多少钱（两种方式实现）
（3）统计购买的2017-01-12日生产的商品中价格最贵的商品（插入的数据中包括2017-01-12生产的商品）
（4）统一购买商品的总价格
1.
CREATE TABLE score_form (
name VARCHAR(20),
Chinese_score int,
Math_score int,
English_score int);
INSERT INTO score_form (name,Chinese_score,Math_score,English_score)
VALUES ('alex',98,90,78),
('egon',90,78,90),
('yuanhao',90,89,78),
('wusir',98,96,89),
('李红',94,85,78),
('张三',90,99,68),
('李小龙',99,100,100),
('王小花',99,86,72),
('李佳',72,90,88),
('猪猪',67,60,58),
('李青',40,98,95),
('赵敏',70,89,91);
mysql> select * from score_form; +---------+---------------+------------+---------------+ | name | Chinese_score | Math_score | English_score | +---------+---------------+------------+---------------+ | alex | 98 | 90 | 78 | | egon | 90 | 78 | 90 | | yuanhao | 90 | 89 | 78 | | wusir | 98 | 96 | 89 | | 李红 | 94 | 85 | 78 | | 张三 | 90 | 99 | 68 | | 李小龙 | 99 | 100 | 100 | | 王小花 | 99 | 86 | 72 | | 李佳 | 72 | 90 | 88 | | 猪猪 | 67 | 60 | 58 | | 李青 | 40 | 98 | 95 | | 赵敏 | 70 | 89 | 91 | +---------+---------------+------------+---------------+ 12 rows in set mysql> SELECT name,Math_score,Chinese_score+Math_score+English_score 总分 FROM score_form; +---------+------------+------+ | name | Math_score | 总分 | +---------+------------+------+ | alex | 90 | 266 | | egon | 78 | 258 | | yuanhao | 89 | 257 | | wusir | 96 | 283 | | 李红 | 85 | 257 | | 张三 | 99 | 257 | | 李小龙 | 100 | 299 | | 王小花 | 86 | 257 | | 李佳 | 90 | 250 | | 猪猪 | 60 | 185 | | 李青 | 98 | 233 | | 赵敏 | 89 | 250 | +---------+------------+------+ 12 rows in set mysql> SELECT name,Chinese_score+Math_score 语文和数学成绩总分 FROM score_form ORDER BY Chinese_score+Math_score DESC; +---------+--------------------+ | name | 语文和数学成绩总分 | +---------+--------------------+ | 李小龙 | 199 | | wusir | 194 | | 张三 | 189 | | alex | 188 | | 王小花 | 185 | | yuanhao | 179 | | 李红 | 179 | | egon | 168 | | 李佳 | 162 | | 赵敏 | 159 | | 李青 | 138 | | 猪猪 | 127 | +---------+--------------------+ 12 rows in set mysql> SELECT name,Chinese_score+Math_score+English_score 总分 FROM score_form ORDER BY Chinese_score+Math_score+English_score DESC LIMIT 1; +--------+------+ | name | 总分 | +--------+------+ | 李小龙 | 299 | +--------+------+ 1 row in set mysql> SELECT name,Chinese_score+Math_score+English_score 总分 FROM score_form WHERE name LIKE '李%' ORDER BY Chinese_score+Math_score+English_score DESC LIMIT 1; +--------+------+ | name | 总分 | +--------+------+ | 李小龙 | 299 | +--------+------+ 1 row in set
（2） 查询班级总成绩最高的学生姓名 两种方式： 1. mysql> SELECT name,Chinese_score+Math_score+English_score 总分 FROM score_form ORDER BY Chinese_score+Math_score+English_score DESC LIMIT 1; +--------+------+ | name | 总分 | +--------+------+ | 李小龙 | 299 | +--------+------+ 1 row in set 2. mysql> SELECT name,Chinese_score+Math_score+English_score 总分 FROM score_form WHERE Chinese_score+Math_score+English_score =(SELECT MAX(Chinese_score+Math_score+English_score) FROM score_form); +--------+------+ | name | 总分 | +--------+------+ | 李小龙 | 299 | +--------+------+ 1 row in set
2.
CREATE TABLE product_info ( name VARCHAR(20), price FLOAT(6,2), born_date DATE, class VARCHAR(20) ); INSERT INTO product_info (name,price,born_date,class) VALUES('apple',5.6,'2017-07-25','fruit'), ('sheet',30,'2017-05-23','bed'), ('cherry',8,'2017-07-20','fruit'), ('kettle',66,'2017-01-20','electric'), ('quilt',38.8,'2016-02-08','bed'), ('banana',3.2,'2017-05-18','fruit'), ('lamp',5.6,'2017-03-09','electric'), ('fridge',4888,'2016-12-12','electric'), ('lemon',9.9,'2017-07-25','fruit'), ('pillow',28,'2017-05-23','bed'), ('TV',3000,'2017-07-25','electric'); mysql> select * from product_info; +--------+-------+------------+----------+ | name | price | born_date | class | +--------+-------+------------+----------+ | apple | 5.6 | 2017-07-25 | fruit | | sheet | 30 | 2017-05-23 | bed | | cherry | 8 | 2017-07-20 | fruit | | kettle | 66 | 2017-01-20 | electric | | quilt | 38.8 | 2016-02-08 | bed | | banana | 3.2 | 2017-05-18 | fruit | | lamp | 5.6 | 2017-03-09 | electric | | fridge | 4888 | 2016-12-12 | electric | | lemon | 9.9 | 2017-07-25 | fruit | | pillow | 28 | 2017-05-23 | bed | | TV | 3000 | 2017-07-25 | electric | +--------+-------+------------+----------+ 11 rows in set mysql> SELECT class,SUM(price) FROM product_info GROUP BY class; +----------+------------+ | class | SUM(price) | +----------+------------+ | bed | 96.80 | | electric | 7959.60 | | fruit | 26.70 | +----------+------------+ 3 rows in set mysql> SELECT class,COUNT(name) FROM product_info GROUP BY class; +----------+-------------+ | class | COUNT(name) | +----------+-------------+ | bed | 3 | | electric | 4 | | fruit | 4 | +----------+-------------+ 3 rows in set mysql> SELECT class,SUM(price) FROM product_info WHERE class ='fruit' GROUP BY class; +-------+------------+ | class | SUM(price) | +-------+------------+ | fruit | 26.70 | +-------+------------+ 1 row in set mysql> SELECT class,SUM(price) FROM product_info GROUP BY class HAVING class ='fruit'; +-------+------------+ | class | SUM(price) | +-------+------------+ | fruit | 26.70 | +-------+------------+ 1 row in set mysql> SELECT name,price FROM product_info WHERE born_date LIKE '2017-07-25' order BY price DESC LIMIT 1; +------+-------+ | name | price | +------+-------+ | TV | 3000 | +------+-------+ 1 row in set mysql> SELECT SUM(price) FROM product_info; +------------+ | SUM(price) | +------------+ | 8083.10 | +------------+ 1 row in set mysql>
