单表查询
where
1. 比较操作符，">" "<" "=" ">=" "<=" "!="
select * from student where id != 1;
2. BETWEEN ，操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、日期。
select * from student where id between4 and 6;
3. like，模糊搜索，"%"匹配0个或者多个字符，"_"匹配一个字符
select * from student where name like '%周%';
4. and和or，可以写成&&和||，可以通过括号调整优先级
select * from student where name='张三' and sex='男';
5. in和not in
select * from student where id in (2,3,4,5);
6. 排序，order by [asc | desc]，不写默认是asc
select * from student [条件] order by id asc;
select * from student [条件] order by id asc，age desc;
7. is null
select * from student where name is null;
8. 分组 group by
select * from student group by type;
9. having
select * from goods group by type having price > 50;
10. limit分页，从第3行开始往后面查4行数据
select * from student where id limit 3,4;
11. distinct去掉重复字段
select distinct type from goods;
聚集函数
查询数据的总和select sum(age) from student;查询最大值select max(age) from student;查询最小值select min(age) from student;平均年龄select avg(age) from student;统计查询数量select count(*) from student;
复制表
复制表结构
CREATE TABLE 新表 LIKE 旧表;
复制表结构以及数据
CREATE TABLE 新表 SELECT * FROM 旧表 [条件];
赋值表结构数据
INSERT INTO 表1(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 表2;
作业
#学生表drop table If EXISTS student;create table IF NOT EXISTS student( s_id int primary key, c_id int, s_name varchar(20), s_sex varchar(20), s_age int, s_addr varchar(20), s_money int);insert into student values (11,16,"李贵贵","男",22,"广西省",20);insert into student values (12,17,"张三贵","女",22,"广东省",50);insert into student values (13,17,"赵六贵","男",18,"广东省",200);insert into student values (14,18,"李贵","男",25,"广西省",30);insert into student values (15,17,"刘有贵","女",18,"广东省",1);insert into student values (16,16,"赵贵贵","男",20,"广西省",70);insert into student values (17,19,"王贵贵","男",20,"广西省",55);insert into student values (18,19,"王云飞","男",20,"广西省",500);insert into student values (19,16,"李贵贵","男",21,"广西省",80);insert into student values (110,16,"李贵贵","男",24,"广西省",3);insert into student values (111,17,"王云飞","男",24,"湖南省",54);insert into student values (112,16,"周海华","男",24,"湖南省",14);insert into student values (113,17,"赵小三","男",24,"湖南省",33);insert into student values (114,17,"王云飞",NULL,28,"广西省",45);insert into student (s_id,c_id,s_name) values (115,16,"肖明");查询年龄大于20岁的同学查询年龄小于等于22岁的同学查询年龄不等于20岁的同学查询年龄为20岁到22岁之间的所有男同学查询16班男同学的所有信息查询17班同学的姓名和性别和班级查询16班户籍在广西省的所有学生信息查询姓李的所有学生信息查询姓赵的男学生信息查询赵?贵的性别查询名字中带贵字的女学生查询16班姓李的男同学查询湖南或者广东省的男同学查询年龄为20,22,25岁的同学查询每个省有多少个人查询名字为2个字的同学查询地址为广西的同学信息，姓名进行升序，年龄进行降序排序查询该班级的学生主要来自哪些省份查询年龄最大的学生查询17班年龄最大的学生查询17班年龄最小的男学生查询16班有多少女生查询16班有多少钱查询16班每人平均有多少钱查询16班有多少钱和平均有多少钱查询地址为湖南的男生有多少人查询性别未填写的同学姓名查询前10条记录查询从第五行开始后4行的记录查询金额在50到100的男同学 查询班上名字不相同的学生姓名