语句数据行操作
今日内容
SQL 语句数据行操作补充
- create table tb12(
- id int auto_increment primary key,
- name varchar(32),
- age int
- )engine=innodb default charset=utf8;
增
- insert into tb11(name,age) values('alex',12);
- insert into tb11(name,age) values('alex',12),('root',18);
- insert into tb12(name,age) select name,age from tb11;
删
- delete from tb12;
- delete from tb12 where id !=2
- delete from tb12 where id =2
- delete from tb12 where id> 2
- delete from tb12 where id>=2
- delete from tb12 where id>=2 or name='alex'
改
- update tb12 set name='alex' where id>12 and name='xx'
- update tb12 set name='alex',age=19 where id>12 and name='xx'
查
- select * from tb12;
- select id,name from tb12;
- select id,name from tb12 where id> 10 or name ='xxx';
- select id,name as cname from tb12 where id> 10 or name ='xxx';
- select name,age,11 from tb12;
其他:
- select * from tb12 where id != 1
- select * from tb12 where id in (1,5,12);
- select * from tb12 where id not in (1,5,12);
- select * from tb12 where id in (select id from tb11)
- select * from tb12 where id between 5 and 12;
通配符:
- select * from tb12 where name like "a%"
- select * from tb12 where name like "a_"
分页:
- select * from tb12 limit 10;
- select * from tb12 limit 0,10;
- select * from tb12 limit 10,10;
- select * from tb12 limit 20,10;
- select * from tb12 limit 10 offset 20;
- # page = input('请输入要查看的页码')
- # page = int(page)
- # (page-1) * 10
- # select * from tb12 limit 0,10; 1
- # select * from tb12 limit 10,10;2
排序:
select * from tb12 order by id desc; 大到小
select * from tb12 order by id asc; 小到大
select * from tb12 order by age desc,id desc;
取后 10 条数据
select * from tb12 order by id desc limit 10;
分组:
- select count(id),max(id),part_id from userinfo5 group by part_id;
- count
- max
- min
- sum
- avg
**** 如果对于聚合函数结果进行二次筛选时? 必须使用 having ****
- select count(id),part_id from userinfo5 group by part_id having count(id)> 1;
- select count(id),part_id from userinfo5 where id> 0 group by part_id having count(id)> 1;
连表操作:
- select * from userinfo5,department5
- select * from userinfo5,department5 where userinfo5.part_id = department5.id
- select * from userinfo5 left join department5 on userinfo5.part_id = department5.id
- select * from department5 left join userinfo5 on userinfo5.part_id = department5.id
- # userinfo5 左边全部显示
- # select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
- # department5 右边全部显示
- select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id
将出现 null 时一行隐藏
- select * from
- department5
- left join userinfo5 on userinfo5.part_id = department5.id
- left join userinfo6 on userinfo5.part_id = department5.id
- select
- score.sid,
- student.sid
- from
- score
- left join student on score.student_id = student.sid
- left join course on score.course_id = course.cid
- left join class on student.class_id = class.cid
- left join teacher on course.teacher_id=teacher.tid
- select count(id) from userinfo5;
先生成两张表 tb11\tb12
- create table tb12(
- id int auto_increment primary key,
- name varchar(32),
- age int
- )engine=innodb default charset=utf8;
as 别名不影响数据, 只影响表头
增
增 1 行
insert into tb11(name,age) values('alex',12);
增多行
insert into tb11(name,age) values('alex',12);
将一个表的所有数据, 复制到另一张表中
insert into tb12(name,age) select name,age from tb11;
删
改
查
其他
通配符:
分页:
排序
分组
作业练习:
http://www.cnblogs.com/wupeiqi/articles/5729934.html
10-15 个完成
来源: http://www.bubuko.com/infodetail-2608942.html