Mysql的表操作:
一.表的创建:(create)
语法:
INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...
创建一张学生表:
create table students(
id int unsigned primary key auto_increment,
sn int not null unique,
name varchar(20) not null,
qq varchar(20)
);
二.表的插入:
1.单行数据全列插入:
insert into students values(100,10000,"小明",NULL);
insert into students values(101,10001,"张三",11111);
2.多行数据指定列插入:
insert into students (id,sn,name) values (102,10002,"孙悟空"),(103,10003,"猪八戒");
2.表的数据查找:
创建表:create table exam_result;
1.全列查询:
select *from exam_result;
2.指定列查询:
select id,name,yuwen from exam_result;
3.查询字段为表达式:
1.表达式不包含字段:
select id,name 10 from exam_result;
2.表达式包含一个字段:
select id,name,yuwen+10 from exam_result;
3.表达式包含多个字段:
select id,name,yunwen+shuxue from exam_result;
4.查询结果指定别名:
select id,name,yuwen+shuxue+yingyu 总分 from exam_result;
5.结果去重:
select distinct shuxue from exam_result;
6.where条件:
比较运算符:
逻辑运算符:
举例:
(1)英语不及格的同学:
select name,yingyu from exam_result where yingyu<60;
(2)语文成绩在[80,90)的同学:
select name,yuwen from exap_result where yuwen >= 80 and yuwen < 90;
(3)数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩;
select name,shuxue from exam_result where shuxue = 58 or shuxue = 59 or shuxue = 98 or shuxue = 99;
(4)语文成绩在 [80, 90] 分的同学及语文成绩;
select name,yuwen from exam_result where yunwen between 80 and 90;
(5)姓孙的同学:
select name from exam_result where name like '孙%';
select name from exam_result where name like '孙_';
(6)NULL查询:
查询qq已知的同学:
7.结果排序:
语法:
-- ASC 为升序(从小到大)
-- DESC 为降序(从大到小) -- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];
(1) 同学及数学成绩,按数学成绩升序显示;
select name, shuxue from exam_result order by shuxue;
(2) 查询同学各门成绩,依次按 数学降序,英语升序,语文升序的方式显示 ;
select name, shuxue, yingyu, yuwen from exam_result order by shuxue desc, yingyu, yuwen;
三.表的更新:(update…set…)
语法:
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]
1.将孙悟空同学的数学成绩变更为 80 分;
update exam_result set shuxue = 80 where name = '孙悟空';
//查看更新后的结果:
select name, shuxue from exam_result where name = '孙悟空';
2.将曹孟德同学的数学成绩变更为 60 分,语文成绩变更为 70 分;
update exam_result set shuxue = 60, yuwen = 70 where name = '曹孟德';
//查看更新结果:
select name, shuxue, yuwen from exam_result where name = '曹孟德';
四.表的删除:(delete)
语法:
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
1.删除孙悟空的考试成绩:
delete from exam_result where name = '孙悟空';
//查询结果:
select * from exam_result where name = '孙悟空';
2.删除整张表的数据:
delete from exam_result;
聚合函数:
聚合函数的常见函数形式:
(1)统计班级一共有多少学生:
select count(*) from exam_result;
(2)统计本次考试的数学成绩分数个数 ;
select count(shuxue) from exam_result;
//统计去重成绩:
select count(distinct shuxue) from exam_result;
(3)统计平均总分;
select avg(yuwen + shuxue + yingyu) 平均总分 from exam_result;
(4)统计英语最高分;
select max(yingyu) from exam_result;