sql语句数据行操作－虽然一般不用

 

创建表

create table tb12(
				id int auto_increment primary key,
				name varchar(32),
				age int
			)engine=innodb default charset=utf8;

　　

增：insert into ... values()

增
			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 ... where id = 

删
			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 tbname set where

改
			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

查

			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;

　　

where/between and/in/not in/name like

where:

				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;


				通配符:数字用where id＝；where id not in,文字用where name like "张％"；where name="李平"

				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

　　

 

排序：asc/desc

排序：
					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;

　　

聚合函数：注意使用having

分组：聚合函数

					select count(id),max(id),part_id from userinfo5 group by part_id;

					count
					max
					min
					sum
					avg

					**** 如果对于聚合函数结果进行二次筛选时？必须使用having ****

                    select part_id from userinfo5 where part_id>10;
					select count(id),part_id from userinfo5 group by part_id having count(id) > 1;

					select count(id),part_id from userinfo5 where id > 10 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 inner 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.teach_id=teacher.tid




			select count(id) from userinfo5;获得表条数