--创建表

CREATE TABLE userinfo3 (

    ID INT PRIMARY KEY NOT NULL, --ID 整数类型，设置为主键，并且不能为空

    name VARCHAR ( 10 ) NOT NULL, --name 可变长度字符类型，不能为空

age INT NULL --age 整数类型，可以为空

)

--in子查询 把子查询select的结果当作主查询的in 条件使用即可

SELECT * FROM userinfo3 WHERE age in (select age from userinfo2 WHERE isV = 1)

SELECT * FROM userinfo3 WHERE age not in (select age from userinfo2 WHERE isV = 1) --not in 反向

--查询某一条件的区间条件

select * from userinfo3 where age BETWEEN 22 and 25

-- 查询a表和b表中age字段中相同的值，存在则显示

SELECT a.id,a.name,a.age,a.sex from userinfo3 as a

where exists(select * from userinfo2 as b where a.age = b.age);

-- 反,不存在显示

SELECT a.id,a.name,a.age,a.sex from userinfo3 as a

where not exists(select * from userinfo2 as b where a.age = b.age);

--查询返回结果排序

--age倒序, id升序

select * from userinfo3 ORDER BY age DESC,id

--默认是=升序

select * from userinfo3 ORDER BY age,id ASC

--函数

select count(name) from userinfo3 WHERE age >= 23

select sum(age) from userinfo3