数据库基础

Mysql表的增删改查

1.CRUD

'-- '为注释，包括空格‘

CRUD： Create,Retrieve,Update,Delete

2.新增

drop table student if exists 
create table student(
    id int ,
    sn int comment '学号'
    name varchar(20) comment '姓名'
    qq_email varchar(20) comment '邮箱'
);

单行数据，全列插入

INSERT INTO student VALUES (100,1001,'唐三藏'，NULL);
INSERT INTO student VALUES (101,1002,'孙悟空'，'1111');

多行数据，指定列插入

INSERT INTO student (id,sn,name) VALUES
    (103,'2001','曹孟德'),
    (104,'2002','孙仲谋');

3.查询

1.全列查询

select * from exam_result;

2.指定列查询

select id,name,english from exam_result;

3.查询字段为表达式

-- 不包含字段
select id,name from exam_result;
-- 包含一个字段
select id,name,english+10 from exam_result;
-- 包含多个字段
select id,name,english+math+chinese from exam_result;

4.别名

select id,name,english+math+chinese 总分 from exam_result;

5.去重 distinct

select distinct math from exam_result; 

6.排序 order by

-- ASC为升序，默认
-- DESC为降序
select name,email from exam_result order by qq_mail;
select anme,email from exam_result order by qq_mail desc;
-- NULL数据排序，视为比任何值都小

使用表达式或者别名排序

select name,english+chinese+math from exam_result order by english+chinese+math desc;
select name,english+math total from exam_result order by total desc;

可以对多个字段进行排序，优先级为书写顺序

select name,english,chinese,math from exam_result order by math desc,english,chinese;

7.条件查询 where

基本查询

-- 查询英语不及格的同学及成绩
select name,english from exam_result where english<60;
-- 查询语文成绩比英语好的同学
select name,chinese,english from exam_result where chinese>english
-- 查询总分在200以下的同学
select name,chinese+english+math 总分 from exam_result where chinese+english+math<200; 

AND与OR

-- 查询语文大于80同时英语成绩大于80的同学
select * from exam_result where chinese>80 and english>80;
-- 查询语文大于80或者英语成绩大于80的同学
select * from exam_result where chinese>80 or english>80;
-- 观察and和or的优先级
select * from exam_result where chinese>80 or english>70 and math>70;
select * from exam_result where (chinese>80 or english>70) and math>70;

范围查询

select name,chinese from exam_result where chinese between 80 and 90;
select name,chinese from exam_result where chinese >= 80 and cinese <= 90;

in

-- 查询数学成绩是58,59,98,99的同学
select name,math from exam_result where math in(58,59,98,99); 
select name,math from exam_result where math=58 or math=59 or math=98 or math=99;

like

-- %匹配任意多个字符 ,匹配到孙悟空，孙权等
select name,math from exam_result where name like '孙%';
-- _匹配任意一个人字符，匹配到孙权等
select name,math form exam_result where name like '孙_';

NULL的查询

-- 查询邮箱不为空的同学姓名及邮箱
select name,qq_email where qq_email is not NULL;
-- 查询邮箱为空的同学姓名及邮箱
select name,qq_email where qq_email is NULL;

分页查询

-- 从0开始查询n条结果
select * from exam_result limit n;
-- 从s开始查询n条结果
select * from exam_result limit s,n;
-- 从s开始查询n条结果
select * form exam_result limit n offset s;

4修改

-- 将孙悟空同学的数学成绩改成80分
update exam_result set math = 80 where name='孙悟空'；
-- 将曹孟德同学的数学成绩变更为60，英语成绩变更为70
update exam_result set math = 80 ,english = 70 where name='曹孟德';
-- 将总成绩倒数前三的同学的数学成绩增加30分
update exam_result set math = math+30 where order by english+chinese+math limit 3;
-- 将所有同学的语文成绩×2
update exam_result set chinese = chinese *2 ;

5删除

-- 删除孙悟空同学的成绩
delete from exam_result where name = '孙悟空'；
-- 删除整张表数据
delete from exam_result;