mysql数据库CURD
mysql> create database if not existsmydb2 character set utf8;
mysql> create table employee(id int,
-> name varchar(20),
-> sex varchar(20),
-> birthday date,
-> entry_date date,
-> job varchar(20),
-> salsay float(5,1),
-> resume text);
Query OK, 0 rows affected (1.05 sec)
Insert
语法L
INSERT INTO table[(column[, column...])]
VALUES (value[,value...]);
l 插入的数据应与字段的数据类型相同。
l 数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
l 在values中列出的数据位置必须与被加入的列的排列位置相对应。
l 字符和日期型数据应包含在单引号中。
l 插入空值，不指定或insert into table value(null)
l 注意：字符和日期要包含在单引号中。
mysql> insertinto employee values(1,'java','male','1990-04-23','1990-05-08','pm
',5000.1,'hello');
Query OK, 1 row affected (0.11 sec)
mysql> insertinto employee values(2,'mysql','male','1990-04-23','1990-05-08','pm',5000.1,'hello');
Query OK, 1 row affected(0.07 sec)
mysql> select *from employee;
+------+-------+------+------------+------------+------+--------+--------+
| id | name | sex | birthday | entry_date | job | salsay | resume|
+------+-------+------+------------+------------+------+--------+--------+
| 1 | java | male | 1990-04-23| 1990-05-08 | pm | 5000.1 | hello |
| 2 | mysql | male | 1990-04-23 | 1990-05-08| pm | 5000.1 | hello |
+------+-------+------+------------+------------+------+--------+--------+
2 rows in set (0.00 sec)
--删除
语法:
delete from tbl_name
[WHEREwhere_definition]
l 如果不使用where子句，将删除表中所有数据。
l delete语句不能删除某一列的值（可使用update）
l 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。用于删除数据量不大的数据
l 同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，
l 删除表中数据也可使用TRUNCATE TABLE语句，它和delete有所不同。TRUNCATE(复制表结构->一次性删除整表->自动恢复表结构，适合删除数据量较大的数据，不能按条件进行删除
l DELETE(逐行删除记录),按行删除表的记录，但会保留表，适合删除数据量不大的数据，可按条件删除
drop table:删除表的本身
删除记录时，一定要留意表间的关联关系
mysql> deletefrom employee where id=1;
Query OK, 1 row affected (0.10 sec)
mysql> select * from employee;
+------+-------+------+------------+------------+------+--------+--------+
| id | name | sex | birthday | entry_date | job | salsay |resume |
+------+-------+------+------------+------------+------+--------+--------+
| 2 | mysql | male | 1990-04-23 | 1990-05-08| pm | 5000.1 | hello |
+------+-------+------+------------+------------+------+--------+--------+
1 row in set (0.00 sec)
--更新
l 使用 update语句修改表中数据。
语法:
UPDATE tbl_name
SETcol_name1=expr1[, col_name2=expr2 ...]
[WHEREwhere_definition]
l UPDATE语法可以用新值更新原有表行中的各列。
l SET子句指示要修改哪些列和要给予哪些值。
l WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行
--先再插入几条数据
mysql> insert into employee values(3,'c#','female','1991-04-23','1995-05-08','pm',6000.1,'how');
Query OK, 1 row affected (0.09 sec)
mysql> insert into employee values(4,'oracle','male','1950-04-23','1980-05-08','pm',1000.1,'are');
Query OK, 1 row affected (0.13 sec)
mysql> insert into employee values(5,'sqlserver','female','1970-04-23','1980-05-08','crm',4000.1,'you');
Query OK, 1 row affected (0.19 sec)
--将所人员的工资修改为5000
mysql> select * from employee;
+------+-----------+--------+------------+------------+------+--------+--------+
| id | name | sex | birthday | entry_date | job | salsay |resume |
+------+-----------+--------+------------+------------+------+--------+--------+
| 2 | mysql | male | 1990-04-23| 1990-05-08 | pm | 5000.1 | hello |
| 3 | c# | female | 1991-04-23| 1995-05-08 | pm | 6000.1 | how |
| 4 | oracle | male | 1950-04-23| 1980-05-08 | pm | 1000.1 | are |
| 5 | sqlserver | female | 1970-04-23| 1980-05-08 | crm | 4000.1 | you |
+------+-----------+--------+------------+------------+------+--------+--------+
4 rows in set (0.00 sec)
mysql> updateemployee set salsay=5000;
Query OK, 4 rows affected (0.05 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from employee;
+------+-----------+--------+------------+------------+------+--------+--------+
| id | name | sex | birthday | entry_date | job | salsay | resume|
+------+-----------+--------+------------+------------+------+--------+--------+
| 2 | mysql | male | 1990-04-23| 1990-05-08 | pm | 5000.0 | hello |
| 3 | c# | female | 1991-04-23| 1995-05-08 | pm | 5000.0 | how |
| 4| oracle | male | 1950-04-23| 1980-05-08 | pm | 5000.0 | are |
| 5 | sqlserver | female | 1970-04-23| 1980-05-08 | crm | 5000.0 | you |
+------+-----------+--------+------------+------------+------+--------+--------+
4 rows in set (0.00 sec)
--修改id为2的员工的工资为8000
mysql> updateemployee set salsay=8000 where id=2;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from employee whereid=2;
+------+-------+------+------------+------------+------+--------+--------+
| id | name | sex | birthday | entry_date | job | salsay |resume |
+------+-------+------+------------+------------+------+--------+--------+
| 2 | mysql | male | 1990-04-23 | 1990-05-08| pm | 8000.0 | hello |
+------+-------+------+------------+------------+------+--------+--------+
1 row in set (0.00 sec)
=========================select=======================
mysql> create table student(
-> id int,
-> name varchar(20),
-> chinese float,
-> english float,
-> math float
-> );
Query OK, 0 rows affected (1.37 sec)
mysql>
mysql> insert intostudent(id,name,chinese,english,math) values(1,'张小明',89,78,90);
Query OK, 1 row affected (0.12 sec)
mysql> insert intostudent(id,name,chinese,english,math) values(2,'李进',67,98,56);
Query OK, 1 row affected (0.06 sec)
mysql> insert intostudent(id,name,chinese,english,math) values(3,'王五',87,78,77);
Query OK, 1 row affected (0.37 sec)
mysql> insert intostudent(id,name,chinese,english,math) values(4,'李一',88,98,90);
Query OK, 1 row affected (0.06 sec)
mysql> insert intostudent(id,name,chinese,english,math) values(5,'李来财',82,84,67);
Query OK, 1 row affected (0.04 sec)
mysql> insert intostudent(id,name,chinese,english,math) values(6,'张进宝',55,85,45);
Query OK, 1 row affected (0.09 sec)
mysql> insert intostudent(id,name,chinese,english,math) values(7,'黄蓉',75,65,30);
Query OK, 1 row affected (0.05 sec)
mysql> insert intostudent(id,name,chinese,english,math) values(8,'张一李',75,65,30);
Query OK, 1 row affected (0.25 sec)
mysql> insert intostudent(id,name,chinese,english,math) values(9,'何李',75,65,30);
Query OK, 1 row affected (0.06 sec)
mysql> insert intostudent(id,name,chinese,english,math) values(10,'单',75,65,30);
Query OK, 1 row affected (0.14 sec)
mysql> insert intostudent(id,name,chinese,english,math) values(11,'李',75,65,NULL);
Query OK, 1 row affected (0.11 sec)
mysql> insert intostudent(id,name,chinese,english,math) values(12,'jack',75,65,40);
Query OK, 1 row affected (0.08 sec)
mysql> insert into student(id,name,chinese,english,math)values(13,'marry',75,65,60);
Query OK, 1 row affected (0.12 sec)
mysql>
mysql> select * from student;
+------+-----------+---------+---------+------+
| id | name | chinese | english |math |
+------+-----------+---------+---------+------+
| 1 | 张小明 | 89 | 78 | 90 |
| 2 | 李进 | 67 | 98 | 56 |
| 3 | 王五 | 87 | 78 | 77 |
| 4 | 李一 | 88 | 98 | 90 |
| 5 | 李来财 | 82 | 84 | 67 |
| 6 | 张进宝 | 55 | 85 | 45 |
| 7 | 黄蓉 | 75 | 65 | 30 |
| 8 | 张一李 | 75 | 65 | 30 |
| 9 | 何李 | 75 | 65 | 30 |
| 10 | 单 | 75 | 65 | 30 |
| 11 | 李 | 75 | 65 | NULL |
| 12 | jack | 75 | 65 | 40 |
| 13 | marry | 75 | 65 | 60 |
+------+-----------+---------+---------+------+
13 rows in set (0.00 sec)
l 基本select语句
语法:
SELECT [DISTINCT] *|{column1,column2. column3..}
FROM table;
l Select指定查询哪些列的数据。
l column指定列名。
l *号代表查询所有列。
l From指定查询哪张表。
l DISTINCT可选，指显示结果时，是否剔除重复数据
查询表中所有学生的信息
select * from student;
select id,name,match,chinese,english from student;
select name,id,match,english,chinese from student;
--查询表中所有学生的姓名和英语成绩
mysql> select name,english from student;
+-----------+---------+
| name | english |
+-----------+---------+
| 张小明 | 78 |
| 李进 | 98 |
| 王五 | 78 |
| 李一 | 98 |
| 李来财 | 84 |
| 张进宝 | 85 |
| 黄蓉 | 65 |
| 张一李 | 65 |
| 何李 | 65 |
| 单 | 65 |
| 李 | 65 |
| jack | 65 |
| marry | 65 |
+-----------+---------+
13 rows in set(0.00 sec)
--select distinct/*/列名 from表名
--使用distinct过滤重复数据
mysql> select distinct english from student;
+---------+
| english |
+---------+
| 78 |
| 98 |
| 84 |
| 85 |
| 65 |
+---------+
5 rows in set(0.00 sec)
-- select 表达式/对列名加别名 from 表名 NULL+X=NULL
语法:
SELECT *|{column1｜expression,column2｜expression，..}
FROM table;
--在所有学生数学分数上加10分的特长分
--SELECT column as别名 from 表名;使用as别名
mysql> selectname,math+10 as数学from student;
+-----------+--------+
| name | 数学 |
+-----------+--------+
| 张小明 | 100 |
| 李进 | 66 |
| 王五 | 87 |
| 李一 | 100 |
| 李来财 | 77 |
| 张进宝 | 55 |
| 黄蓉 | 40 |
| 张一李 | 40 |
| 何李 | 40 |
| 单 | 40 |
| 李 | NULL |
| jack | 50 |
| marry | 70 |
+-----------+--------+
13 rows in set(0.00 sec)| marry | 75 |
+-----------+------------+
13 rows in set(0.00 sec)
--统计每个学生的总分:
mysql> select name,math+english+chinese as 总分 from student;
+-----------+--------+
| name | 总分 |
+-----------+--------+
| 张小明 | 257 |
| 李进 | 221 |
| 王五 | 242 |
| 李一 | 276 |
| 李来财 | 233 |
| 张进宝 | 185 |
| 黄蓉 | 170 |
| 张一李 | 170 |
| 何李 | 170 |
| 单 | 170 |
| 李 | NULL |
| jack | 180 |
| marry | 200 |
+-----------+--------+
13 rows in set(0.00 sec)
--where子句，出现在from后面，where是按行筛选
|
比较运算符 | > < <= >= = <> | 大于、小于、大于(小于)等于、不等于 |
| BETWEEN ...AND... | 显示在某一区间的值 | |
| IN(set) | 显示在in列表中的值，例：in(100,200) | |
| LIKE ‘张pattern’ | 模糊查询(重点) | |
| IS NULL/IS NOT NULL | 判断是否为空 | |
|
逻辑运算符 | and && | 多个条件同时成立 |
| or || | 多个条件任一成立 | |
| not ! | 不成立，例：where not(salary>100); |
Like语句中，%代表零个或多个任意字符，_代表一个字符，例first_name like ‘_a%’;
--查询姓名为张小明的记录
mysql> select * from student where name='张小明';
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 1 | 张小明 | 89 | 78 | 90 |
+------+-----------+---------+---------+------+
1 row in set(0.00 sec)
--查询英语大于90的学生
mysql> select * from student where english>90;
+------+--------+---------+---------+------+
| id | name | chinese | english | math |
+------+--------+---------+---------+------+
| 2 | 李进 | 67 | 98 | 56 |
| 4 | 李一 | 88 | 98 | 90 |
+------+--------+---------+---------+------+
2 rows in set(0.00 sec)
--查询总分大于200的
mysql> select name, (chinese+english+math) as总分from student where (chinese+english+math)>200;
+-----------+--------+
| name | 总分 |
+-----------+--------+
| 张小明 | 257 |
| 李进 | 221 |
| 王五 | 242 |
| 李一 | 276 |
| 李来财 | 233 |
+-----------+--------+
5 rows in set(0.00 sec)
--查询英语分数在80－90之间的同学。
mysql> select *
-> from student
-> whereenglish>=80 and english<=90;
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 5 | 李来财 | 82 | 84 | 67 |
| 6 | 张进宝 | 55 | 85 | 45 |
+------+-----------+---------+---------+------+
2 rows in set(0.00 sec)
或 (推荐下面的方式)
mysql> select *
-> from student
-> whereenglish between 80 and 90;
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 5 | 李来财 | 82 | 84 | 67 |
| 6 | 张进宝 | 55 | 85 | 45 |
+------+-----------+---------+---------+------+
2 rows in set (0.00 sec)
--查询数学分数为89,90,91的同学。
--方式一
mysql> select *
-> from student
-> wheremath=89 or math= 90 or math=91;
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 1 | 张小明 | 89 | 78 | 90 |
| 4 | 李一 | 88 | 98 | 90 |
+------+-----------+---------+---------+------+
2 rows in set(0.00 sec)
--方式二
mysql> select * from student where math in(89,90,91);
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 1 | 张小明 | 89 | 78 | 90 |
| 4 | 李一 | 88 | 98 | 90 |
+------+-----------+---------+---------+------+
2 rows in set(0.00 sec)
--查询数学分数不为89,90,91的同学。
mysql> select * from student where math not in(89,90,91);
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 2 | 李进 | 67 | 98 | 56 |
| 3 | 王五 | 87 | 78 | 77 |
| 5 | 李来财 | 82 | 84 | 67 |
| 6 | 张进宝 | 55| 85 | 45 |
| 7 | 黄蓉 | 75 | 65 | 30 |
| 8 | 张一李 | 75 | 65 | 30 |
| 9 | 何李 | 75 | 65 | 30 |
| 10 | 单 | 75 | 65 | 30 |
| 12 | jack | 75 | 65 | 40 |
| 13 | marry | 75 | 65 | 60 |
+------+-----------+---------+---------+------+
10 rows in set(0.00 sec)
--查询所有姓'李'的学生
mysql> select * from student where name like '李%';
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 2 | 李进 | 67 | 98 | 56 |
| 4 | 李一 | 88 | 98 | 90 |
| 5 | 李来财 | 82 | 84 | 67 |
| 11 | 李 | 75 | 65 | NULL |
+------+-----------+---------+---------+------+
4 rows in set(0.00 sec)
--查询李_(_代表一个字符，%代表多个字符)
mysql> select * from student where name like '李_';
+------+--------+---------+---------+------+
| id | name | chinese | english | math |
+------+--------+---------+---------+------+
| 2 | 李进 | 67 | 98 | 56 |
| 4 | 李一 | 88 | 98 | 90 |
+------+--------+---------+---------+------+
2 rows in set(0.00 sec)
--模糊查询，查询有姓名中有'李'的字
mysql> select * from student where name like '%李%';
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 2 | 李进 | 67 | 98 | 56 |
| 4 | 李一 | 88 | 98 | 90 |
| 5 | 李来财 | 82| 84 | 67 |
| 8 | 张一李 | 75 | 65 | 30 |
| 9 | 何李 | 75 | 65 | 30 |
| 11 | 李 | 75 | 65 | NULL |
+------+-----------+---------+---------+------+
6 rows in set(0.00 sec)
--查询数学成绩为空的记录
mysql> select * from student where math is null;
+------+------+---------+---------+------+
| id | name | chinese | english | math |
+------+------+---------+---------+------+
| 11 | 李 | 75 | 65 | NULL |
+------+------+---------+---------+------+
1 row in set(0.00 sec)
--查询数学成绩不为空的记录
mysql> select * from student where math is not null;
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 1 | 张小明 | 89 | 78 | 90 |
| 2 | 李进 | 67 | 98 | 56 |
| 3 | 王五 | 87 | 78 | 77 |
| 4 | 李一 | 88 | 98 | 90 |
| 5 | 李来财 | 82 | 84 | 67 |
| 6 | 张进宝 | 55 | 85 | 45 |
| 7 | 黄蓉 | 75 | 65 | 30 |
| 8 | 张一李 | 75 | 65 | 30 |
| 9 | 何李 | 75 | 65 | 30 |
| 10 | 单 | 75 | 65 | 30 |
| 12 | jack | 75 | 65 | 40 |
| 13 | marry | 75 | 65 | 60 |
+------+-----------+---------+---------+------+
12 rows in set(0.00 sec)
--查询数学和语法大于80的记录
mysql> select * from student where math>80 &&chinese>80;
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 1 | 张小明 | 89 | 78 | 90 |
| 4 | 李一 | 88 | 98 | 90 |
+------+-----------+---------+---------+------+
2 rows in set(0.00 sec)
mysql> select * from student where math>80 and chinese>80;
+------+-----------+---------+---------+------+
| id | name | chinese | english | math |
+------+-----------+---------+---------+------+
| 1 | 张小明 | 89 | 78 | 90 |
| 4 | 李一 | 88 | 98 | 90 |
+------+-----------+---------+---------+------+
2 rows in set(0.00 sec)
l 使用order by 子句排序查询结果。
语法:
SELECT column1,column2. column3..
FROM table;
order by columnasc|desc
l Order by指定排序的列，排序的列即可是表中的列名，也可以是select语句后指定的列名。
l Asc升序、Desc 降序
l ORDER BY子句应位于SELECT语句的结尾。
--按数学成绩排序输出desc降序
asc(默认升序)
mysql> select name,math from student order by math desc;
+-----------+------+
| name | math |
+-----------+------+
| 张小明 | 90 |
| 李一 | 90 |
| 王五 | 77 |
| 李来财 | 67 |
| marry | 60 |
| 李进 | 56 |
| 张进宝 | 45 |
| jack | 40 |
| 张一李 | 30 |
| 何李 | 30 |
| 单 | 30 |
| 黄蓉 | 30 |
| 李 | NULL |
+-----------+------+
13 rows in set(0.00 sec)
mysql> select name,math from student order by math;
+-----------+------+
| name | math |
+-----------+------+
| 李 | NULL |
| 黄蓉 | 30 |
| 单 | 30 |
| 何李 | 30 |
| 张一李 | 30 |
| jack | 40 |
| 张进宝 | 45 |
| 李进 | 56 |
| marry | 60 |
| 李来财 | 67|
| 王五 | 77 |
| 李一 | 90 |
| 张小明 | 90 |
+-----------+------+
13 rows in set(0.00 sec)
mysql> select name,math from student order by math asc;
+-----------+------+
| name | math |
+-----------+------+
| 李 | NULL |
| 黄蓉 | 30 |
| 单 | 30 |
| 何李 | 30 |
| 张一李 | 30 |
| jack | 40 |
| 张进宝 | 45 |
| 李进 | 56 |
| marry | 60 |
| 李来财 | 67 |
| 王五 | 77 |
| 李一 | 90 |
| 张小明 | 90 |
+-----------+------+
13 rows in set(0.00 sec)
--对总分降序输出
mysql> selectname,(chinese+math+english) as总分 from student orderby (chinese+math+english) desc;
+-----------+--------+
| name | 总分 |
+-----------+--------+
| 李一 | 276 |
| 张小明 | 257 |
| 王五 | 242 |
| 李来财 | 233 |
| 李进 | 221 |
| marry | 200 |
| 张进宝 | 185 |
| jack | 180 |
| 张一李 | 170 |
| 何李 | 170 |
| 单 | 170 |
| 黄蓉 | 170 |
| 李 | NULL |
+-----------+--------+
13 rows in set (0.00 sec)
--对姓名的学生按总分进行排序
mysql> select name,math+chinese+englishas总分
-> from student
-> where name LIKE '李%'
-> order bymath+chinese+english desc;
+-----------+--------+
| name | 总分 |
+-----------+--------+
| 李一 | 276 |
| 李来财 | 233 |
| 李进 | 221 |
| 李 | NULL |
+-----------+--------+
4 rows in set (0.00 sec)
===================函数==================
l Count(列名)返回某一列，行的总数，除null外
语法:
Select count(*)|count(列名) fromtablename[WHERE where_definition]
--统计学生人数
mysql>select count(*) as学生人数 from student;
+--------------+
| 学生人数 |
+--------------+
| 13 |
+--------------+
1 row in set (0.00 sec)
--统计数学成绩大于80的人数
mysql>select count(*) as数学大于80的人数from student where math>80
+-------------------------+
| 数学大于80的人数 |
+-------------------------+
| 2 |
+-------------------------+
1 row in set (0.00 sec)
--统计数学成绩的人数,为12,因为有一个null,count不进行统计
mysql> selectcount(math) from student;
+-------------+
| count(math) |
+-------------+
| 12 |
+-------------+
1 row in set (0.00 sec)
--统计学生数学的总分
l Sum函数返回满足where条件的行的和
Select sum(列名)｛,sum(列名)…｝ fromtablename [WHERE where_definition]
mysql> selectsum(math) as '数学总分' from student;
+--------------+
| 数学总分 |
+--------------+
| 645 |
+--------------+
1 row in set (0.00 sec)
mysql> selectsum(math)+ sum(chinese)+ sum(english)as '语数外总分' from student;
+-----------------+
| 语数外总分 |
+-----------------+
| 2614 |
+-----------------+
1 row in set (0.00 sec)
AVG函数返回满足where条件的一列的平均值
Select sum(列名)｛,sum(列名)…｝ from tablename
[WHERE where_definition]
--统计语文平均分
mysql> selectsum(chinese)/count(*) as '语文平均分'from student;
+-------------------+
| 语文平均分 |
+-------------------+
| 76.38461538461539 |
+-------------------+
1 row in set (0.00 sec)
mysql> selectavg(chinese) as '语文平均分'from student;
+-------------------+
| 语文平均分 |
+-------------------+
| 76.38461538461539 |
+-------------------+
1 row in set (0.00 sec)
--求班级总分平均数
mysql> select (sum(math)+sum(english)+sum(chinese))/count(*)as '总分平均数' from student;
+--------------------+
| 总分平均数 |
+--------------------+
| 201.07692307692307 |
+--------------------+
1 row in set (0.00 sec)
l Max/min函数返回满足where条件的一列的最大/最小值
语法:
Select max(列名)from tablename [WHERE where_definition]
Max/min函数返回满足where条件的一列的最大/最小值求班级最高分和最低分。（数值范围在统计中特别有用）
max(),min()，当max()和min()函数位于日期类型时，分别取得最近日期和最早日期,用于varchar是按码表的顺序进行查询
mysql> select min(chinese) as语文最低分,max(chinese) as 语文最高分 from student;
+-----------------+-----------------+
| 语文最低分 | 语文最高分 |
+-----------------+-----------------+
| 55 | 89 |
+-----------------+-----------------+
1 row in set (0.00sec)
mysql> droptable if exists teacher;
Query OK, 0 rowsaffected, 1 warning (0.00 sec)
mysql> createtable teacher(
-> id int,
-> name varchar(20),
-> birthday date
-> );
Query OK, 0 rowsaffected (0.61 sec)
mysql> insertinto teacher(id,name,birthday) values(1,'jack','2011-1-1');
Query OK, 1 rowaffected (0.14 sec)
mysql> insertinto teacher(id,name,birthday) values(2,'marry','2011-2-2');
Query OK, 1 rowaffected (0.08 sec)
mysql> insertinto teacher(id,name,birthday) values(3,'sisi','2011-3-3');
Query OK, 1 rowaffected (0.53 sec)
mysql>
mysql> selectmax(birthday),min(birthday)
-> from teacher;
+---------------+---------------+
| max(birthday) |min(birthday) |
+---------------+---------------+
| 2011-03-03 | 2011-01-01 |
+---------------+---------------+
1 row in set (0.00sec)
mysql>
--order表
mysql> createtable orders(
-> id int,
-> product varchar(20),
-> price float
-> );
Query OK, 0 rowsaffected (0.90 sec)
mysql>
mysql> insertinto orders(id,product,price) values(1,'电视',900);
Query OK, 1 rowaffected (0.05 sec)
mysql> insert intoorders(id,product,price) values(2,'洗衣机',100);
Query OK, 1 rowaffected (0.06 sec)
mysql> insertinto orders(id,product,price) values(3,'洗衣粉',90);
Query OK, 1 rowaffected (0.44 sec)
mysql> insertinto orders(id,product,price) values(4,'桔子',10);
Query OK, 1 rowaffected (0.07 sec)
mysql> insertinto orders(id,product,price) values(5,'洗衣粉',80);
Query OK, 1 rowaffected (0.06 sec)
mysql>
mysql> select *from orders;
+------+-----------+-------+
| id | product | price |
+------+-----------+-------+
| 1 | 电视 | 900 |
| 2 | 洗衣机 | 100 |
| 3 | 洗衣粉 | 90 |
| 4 | 桔子 | 10 |
| 5 | 洗衣粉 | 80 |
+------+-----------+-------+
5 rows in set(0.00 sec)
l 使用group by 子句对列进行分组
语法:
SELECT column1,column2. column3.. FROM table;
group bycolumn
--对订单表中商品归类后，显示每一类商品的总价
l 使用group by 子句对列进行分组
mysql> select product as类别名,sum(price) as商品类别总价
-> from orders
-> group by product;
+-----------+--------------------+
| 类别名 | 商品类别总价 |
+-----------+--------------------+
| 桔子 | 10 |
| 洗衣机 | 100 |
| 洗衣粉 | 170 |
| 电视 | 900 |
+-----------+--------------------+
4 rows in set(0.00 sec)
--查询购买了几类商品，并且每类总价大于100的商品
l 使用having 子句组过滤
mysql> select product as类别名,sum(price) as商品类别总价
-> from orders
-> group by product
-> having sum(price) > 100;
+-----------+--------------------+
| 类别名 | 商品类别总价 |
+-----------+--------------------+
| 洗衣粉 | 170 |
| 电视 | 900 |
+-----------+--------------------+
2 rows inset (0.00 sec)
l Having和where均可实现过滤，但在having可以使用合计函数,having通常跟在group by后，它作用于组。
where主要用于行过滤器
having主要用于类别过滤器，通常有having就一定出现group by,但有group by的地方，不一定出现having。
扫一扫
71
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
