SQL语句简单例子-笔记

--------建表-------------
create table student(
       _id integer primary key  autoincrement  ,       
       stu_no varchar(10) not null,       
       class_name varchar(20) not null,       
       stuname varchar(20) not null,       
       sex char(4) ,       
       age integer,   
       height real    
);
---------查询表中的数据------------
select * from student;
------------删除表---------------------
drop table student;
--------往表中增加5条数据---------------
--------insert into 表名(列名)  values (值)------------------------
insert into student(stu_no,class_name,stuname) values
('and02','android1412','张三');
insert into student(stu_no,class_name,stuname,sex,age,height)
values('and03','android1412','张三','男',21,178.5);
insert into student(stu_no,class_name,stuname,sex,age,height) values
('and04','android1412','张三','男',22,178.5);
insert into student(stu_no,class_name,stuname,sex) values
('and05','android1412','李四','男');
insert into student(stu_no,class_name,stuname,sex,age) values
('and06','android1412','徐士杰','男',23);
---------------将学号为3的学生的性别修改为女------------------------
update student set sex='女' where stu_no='and03';
-------------------将学号为2的学生的名字修改为"小李"---------
update student set stuname='小李' where stu_no='and02';
--------将高度为160cm以上的高度修改为162---------
update student set height=162 where height>160;
----将学号为3的学生年龄修改为21岁--------
update student set age=21 where stu_no='and03';
update student set age=20 where stuname='张三';
---将名字为张三并且年龄为20岁的男的年龄修改为23-----------
update student set age=23
 where stuname='张三' and age=20 and sex='男'; 
------将名字里有一个'刘'的年龄设为22-------------
update student set stuname='刘三姐' where _id=2;
update student set age=22 where stuname like '%刘%';
--将名字只有2个字,并且最后一个字为四的高度设为170---
update student set height=170 where stuname like '_四';
------修改编号为1的性别，年龄，高度-------
update student set sex='女',age=18,height=162 where _id=1;
--将高度为162的年龄都增加2岁---
update student set age=age+2 where height=162;
--将班级为android1412班并且性别为女的高度统一设为160---
update student set height=160 
where class_name='android1412' and sex='女';
--将班级为android1412的,或者性别为女的高度设为159----
update student set class_name='android1413',sex='女' where _id=4;
update student set height=159
 where class_name='android1412' or sex='女'; 
---修改名字为张三或者李四的年龄为23-----
update student set age=23 where stuname='张三' or stuname='李四';
update student set age=20 where stuname in('张三','李四');
------删除id为5的学生信息--------
delete  from student where _id=5;
-------查询所有记录--------------
select * from student;
---查询班级名称为and1412的学生记录-----
select * from student where class_name='android1412';
----查询班级名称为and1413的姓名和年龄------
select stuname,age from student where class_name='android1413';
----在查询结果中增加一列,列明为school,值为北大-------
select stuname,age,'北大' as school from student;
---查询年龄在20岁以及以上的学生记录----
select * from student where age>=20;
------查询学生为张三或者李四的记录----------
select * from student where stuname in('张三','李四');
---统计每个班的人数,按班级分组------
select class_name,count(*) from student group by class_name;
----统计每个班年龄大于22岁的总人数,按班级分组-------
select class_name,count(*) from student
 where age>22 
 group by class_name;
------统计班级人数,年龄大于20,并且人数在3人以上的记录--------------
select class_name,count(*) from student
where age>20
group by class_name
having  count(*)>3;
-----查询表中年龄最大的记录-------
select max(age) from student;
select  * ,max(age) from student ;
-----查询表中年龄最小的记录------
select *,min(age) from student;
---查询1412班的平均年龄---
select class_name,avg(age) from student
 where class_name='android1412';
 --查询所有班级的平局年龄----- 
select class_name,avg(age) from student
group by class_name;
---统计班上的人数的总年龄,将列名为总年龄---
select class_name,sum(age) as '总年龄' from student
group by class_name;
----将表中的记录按照年龄升序--order by 列名,desc 降序，asc:升序,默认---
select * from student order by age asc;
------将表中的记录按照年龄降序----
select * from student order by age desc;
---统计班级年龄大于20的人数,按降序排序---
select class_name,count(*) from student
where age>20
group by class_name
order by count(*) desc;
----统计班级年龄大于20的并且年龄大于20的总人数大于2的记录,按降序-------
-----先条件，再分组，再筛选，再排序---------
select class_name,count(*) from student
where age>20
group by class_name
having count(*)>2
order by count(*) desc;
---查询年龄最大的前3个记录----
select * from student 
order by age desc
limit 0,3;








------------建表------------
create table student(
       _id  integer primary key not null,       
       stu_no varchar(10) not null,       
       class_name varchar(20) not null,       
       stuname varchar(20) not null,       
       sex char(4),       
       age integer,       
       height real
);


------------建表------------
create table newstud(
       _id  integer primary key autoincrement,       
       stu_no varchar(10) not null,       
       class_name varchar(20) not null,       
       stuname varchar(20) not null,       
       sex char(4),       
       age integer,       
       height real
);


-----------查询表中数据---------------
select * from student


select * from teacher


------------删除表-------------------
drop table student

-----------表与表关联例子------------

SELECT h.title,u.cellphone FROM broker_request_kanrec t
INNER JOIN broker_request_main m ON t.tranid=m.tranid
INNER JOIN useinfo u ON m.userid = u.idd 
INNER JOIN houseinfo h ON h.newCode = t.newcode
-- WHERE t.newcode = '2810836906' AND u.cellphone = '13424195061'