一、数据更新操作
1. 添加数据(insert插入语句)
插入的值和列要保持一一对应的关系
-- 方式一:使用多条insert插入语句
-- insert into 表名(列名1,列名2,列名3.....) value(值1,值2,值3...);
insert into students (id,name) values (0,'张飞');
insert into students (id,name) values (1,'赵云');
-- 方式二:通过一条insert插入多条数据
-- insert into 表名 value(值1,值2,值3...);
insert into heros value(0,'孙尚香','女',20),(1,'露娜','女',18);
-- 方式三:insert into 表名(列名1,列名2,列名3.....) value(值1,值2,值3...),(值1,值2,值3...);
insert into heros(id,name,sex,age) value(0,'孙尚香','女',20),(1,'露娜','女',18);
2. 删除数据(delete删除语句)
delete和drop的区别:
delete仅限于把表中数据给删除,表还在留着;drop是删除整个表
2-1. 物理删除
-- 格式一:把表中整个数据删除
-- delete from 表名;
delet from students;
-- 格式二:根据条件删除数据
delete from 表名 where 条件;
and 条件是同时满足
例:delete from 表名 where 条件1 and 条件2 and 条件3....
or 多个条件只需要满足其中一个
例:delete from 表名 where 条件1 or 条件2 or 条件3....
2-2. 逻辑删除
指通过设定一个字段来标识当前记录已经被删除
is_delete字段来标识,1表示删除,0表示未删除
update students set is_delete=1 where id=8;
2-3. 其他删除数据方式
-- truncate table 表名(清除表里面所有的数据,但是表结构会保留,自增长字段的值会从1开始。)
truncate tablestudens;
-- drop table 表名(删除数据表,包括数据和表结构)
drop table students;
3. 修改数据
-- 格式一:
update 表名 set 列名=值;
-- 格式二:
update 表名 set 列名1=值1,列名2=值2...;
-- 格式三:附带条件的更新
update 表名 set 列名=值 where 条件;
update 员工表 set 工资=12000 where 姓名=小张";
二、数据查询操作
1. 查询基本语法结构
查询部分字段的值
select 字段名1,字段名2 .... from 表名 (查询的为一部分的字段的信息)
-- 查询学生表中的姓名、性别、年龄的数据
select name,sex,age from students;
取别名(as 关键字)
-- 给表取别名
-- select别名.字段名1,别名.字段名2 ..... from 表名 as 别名
例:select s.name,s.sex,s.age from students as s;
--给字段取别名
-- select字段名1 as 别名1,字段名2 as 别名2 .... from表名;
例:select name as 姓名, sex as 性别,age as 年龄 from students;
去重(distinct 关键字)
select distinct 字段名1,字段名2 .... from 表名;
--根据学生的年龄去重
select distinct age from students;
2. 条件查询
2-1. 逻辑运算符
and (同时满足条件)
or (满足其中一个条件)
not (不符合该条件)
-- 语法结构
select 列名 from 表名 where 条件1 逻辑运算符 条件2 逻辑运算符 条件3....;
-- 示例
-- 1.查询上海或北京的天气
select * from weather where city='上海' or city='北京';
-- 2.查询除了上海以外的天气
select * from weather where not city='上海';
select * from weather where city!='上海';
2-2. 比较运算符
比较运算符:大于(>)、等于(=)、小于(<)、大于等于(>=)、小于等于(<=)、不等于(<>或者!=)
-- 1.查询年龄小于18岁的女生
select * from students where age < 18;
-- 2.查询成绩大于且等于85分以上的学生
select * from students where grade >= 85;
-- 3.查询所有性别为女生的姓名
select * from students where sex <> '男';
select * from students where sex != '男';
2-3. 范围查询
between ....and....:针对查询结果满足某个范围内的数据在某个范围之内
select 列名 from 表名 where 列名 between 开始值 and 结束值;
-- 查询分数在85-90之间的学生
select * from students where grade between 85 and 90;
select * from students where grade >= 85 and grade <= 90;
not between .... and....:不在某个范围之内的数据
select 列名 from 表名 where 列名 not between 开始值 and 结束值;
-- 查询分数不在85-90之间的学生
select * from students where grade not between 85 and 90;
in:查询非连续范围内的数据 只要满足其中一个就会有查询结果
-- 查询城市为深圳或者广州或者上海的天气
select * from weather where city in('深圳','广州','上海');
select * from weather where city = '深圳' or city = '广州' or city = '上海';
2-4. 模糊查询
模糊查询:like 关键字
%代表的是 零个或多个
字符
_代表的是 一个字符
-- 1.查询姓王的学生
select * from students where name like '王%';
-- 2.查询姓王且名字是一个的学生
select * from students where name like '王_';
-- 3.查询姓名中包含'雨'的学生
select * from students where name like '%雨%';
-- 4.查询名字为两个字的学生
select * from students where name like '__';
-- 5.查询编号以5结尾的学生
select * from students where name like '%5';
2-5. 空值查询
null和''空字符不一样
-- 判断为空:select 列名 from 表名 where 列名 is null;
-- 查询学生科目为空的信息
select * from students where subject is null;
-- 判断不为空:select 列名 from 表名 where 列名 is not null;
-- 查询学生科目不为空的信息
select * from students where subject is not null;
排序(order by)
默认为升序排列 (从小到大)
asc为升序排列 (从小到大)
desc为降序排列 (从大到小)
-- 格式:select 列名 from 表名 order by 列名1 asc,列名2 desc;
-- 例子∶查询成绩表中所有学生的记录,要求按照成绩升序排列,成绩相同时,按照学号进行降序排列
select * from 成绩表 order by 成绩 asc,学号 desc;
分组和聚合函数
聚合函数
sum(列名) 对某个列进行求和
avg (列名) 对某个列进行求平均值
max(列名) 对某个列求最大值
min (列名) 对某个列求最小值
count(*) 统计元组的个数(看一个表有多少条记录)
count(列名) 统计某个列有多少个值
除了count(*)外,其他函数再做操作时,均忽略空值(null)
-- 查询'1班'学生的分数总和
select sum(grade) from students where class='1班';
-- 查询'1班'学生的分数平均分
select avg(grade) as 平均分 from students where class='1班';
-- 查询'1班'学生的分数最高分
select max(grade) as 最高分 from students where class='1班';
-- 查询'1班'学生最小年龄
select min(age) as 最小年龄 from students where class='1班';
-- 统计name下有多少个数
select count(name) from students;
-- 统计元组的个数
select count(*) from students;
分组查询
针对查询出来的结果进行分组
格式:select 列名,聚合函数 from 表名 group by 列名;
-- 查询不同性别的人数
select sex,count(*) from students group by sex;
-- 查询各个班级的人数
select class,count(*) from students group by class;
-- 查询各个班级中不同性别的人数
select class,sex,count(*) from students group by class,sex;
分组后的数据筛选
- 将分组之后的数据当成是一个表数据,然后再通过having 条件来对当前的表数据进行筛选。
- select 列名 from 表名 group by 列名 having 条件(一般情况下都是聚合函数当做条件);
注意: 1.having必须和group by固定搭配;
2.having后面可以写聚合函数,但是where后面不能写。
-- 查询女生的总数
select count(*) from students where sex='女';
select sex,count(*) from students group by sex having sex='女';
-- 查询每个班级女生的总数
select class,sex,count(*) from students group by class,sex having sex='女';
-- 查询所有班级中不同性别的记录数大于1的信息
select class,sex,count(*) from students group by class,sex having count(*)>1;
分页查询
数据库中从0开始索引
select 列名 from 表名 limit [指定开始查询的行,]查询的总行数;
-- 例如:查询学生表中学号从第3行开始,查询7行
select sno from students limit 2,7;
Comments | 12 条评论
I was more than happy to discover this site. I wanted to thank you for your time just for this fantastic read!! I definitely really liked every little bit of it and I have you book-marked to look at new information in your website.
Good post. I learn something totally new and challenging on blogs I stumbleupon on a daily basis. Its always useful to read content from other authors and practice something from their websites.
An interesting discussion is definitely worth comment. I believe that you ought to write more on this topic, it may not be a taboo matter but usually people dont speak about such subjects. To the next! Cheers!!
You ought to be a part of a contest for one of the best sites on the internet. Im going to highly recommend this site!
Thanks a lot for sharing this with all people you actually recognize
what you’re speaking about! Bookmarked. Kindly also discuss with my web site =).
We could have a link exchange arrangement
among us
Its like you learn my thoughts! You seem
to understand a lot about this, such as you wrote
the ebook in it or something. I think that you just could do with a few p.c.
to force the message house a little bit, but instead of that, this is magnificent blog.
A great read. I’ll definitely be back.
I like the helpful info you provide in your articles.
I’ll bookmark your blog and check again here frequently.
I am quite sure I’ll learn plenty of new stuff right here!
Good luck for the next!
Wonderful work! This is the kind of information that should be shared across the web.
Shame on Google for not positioning this submit higher!
Come on over and discuss with my website .
Thanks =)
With havin so much content and articles do you ever run into any problems of plagorism or copyright violation? My blog has a lot
of unique content I’ve either created myself or outsourced but it
seems a lot of it is popping it up all over the internet without my authorization. Do you
know any solutions to help protect against content
from being ripped off? I’d certainly appreciate it.
Howdy, i read your blog occasionally and i own a similar one and i was just wondering if you get a lot of spam feedback?
If so how do you prevent it, any plugin or anything you can advise?
I get so much lately it’s driving me mad so any assistance is
very much appreciated.
Yes! Finally something about buy instagram followers uk.
Thanks , I’ve recently been looking for information about this subject for a while and yours is the greatest
I have discovered till now. But, what concerning the conclusion? Are you positive in regards to the supply?
powered by GoToTop.ee