1
2
3
4
5
6
7
8
9
10
--查询student表中的所有数据
select *from student 

--查询student表中指定字段
select id, name from student 

--查询student表中指定数据
select id,name from student where id=2 ;
select id,name from student where age=18 and sex="famale" ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
1、%    替代一个或多个字符    
2、_    仅替代一个字符    
3、[charlist]    字符列中的任何单一字符    


--查询以住在以'Ne'开始城市的人
select *from persons where City like 'Ne%' ;

--查询居住在包含on城市的人
select *from persons where City like '%on%' ;

--查询城市第一个字符后为'dodon'的城市
select *from persons where City like '_ondon'l;

--查询居住的城市以 "A" 或 "L" 或 "N" 开头的人：

select *from persons where City like '[ALN]%' ;

1
2
3
4
5
--去除age中相同的数据
select distinct age from student;

--去除age和sex都相同的数据
select distinct age,sex from student;

1
2
3
4
5
6
7
--检索前4行数据 显示id=1,2,3，4的数据 
select *from test limit 0,3;
select *from test limit 3;

-- 检索3行数据 显示id=4,5,6的数据 
select *from test limit 3,5;

1
2
3
4
5
6
7
--按sex分组 统计人数并显示sex个数
select count(*) from group by sex;

--按sex分组 统计人数并显示sex个数和具体的sex
select sex,count(*) from group by sex;

1
2
3
4
5
6
7
8
--30100学院的每个专业的学生有多少人。

select majar, count(*) from student where college = 30100 group by majar ;

--查询每个学院的学生有多少人，并且只要学生人数大于3的。

select college,count(*) from student group by college having count(*) >3 ;

1
2
3
4
5
6
7
--student表中id按逆序排列  id=5,4,3,2,1
select *from  student order by id desc
--student表中id按顺序排列  id=1,2,3,4,5
select *from  student order by id asc 
--student表中按字母（是英语非中文）顺序显示名称
select *from  student order by name asc;

1
2
3
4
5
6
7
8
9
10
11
12
--count（*） 统计个数
select count(*) from student;
--和count(*)一样统计个数，但是不同的是它除去age中为null的行数
select count(age) from student;  
--将age中数值相加
select sum(age) from student;
--将age中数值相加求平均值
select avg(age) from student;
--求age中最大值
select MAX(age) from student;
--将age中最小值
select MIN(age) from student;

1
2
3
4
5
--student表全部内容和mark表全部内容生成笛卡儿积
select *from student,mark;

--student表id,name和mark表grade生成笛卡儿积
select student.id ,studen.name ,mark.grade from student ,mark;

1
2
3
4
5
6
7
--student表内连接mark表查看student的id,student的name,mark的grade当满足mark.stu_id=student.id;

select student.id ,student.name ,mark.grade from student inner join mark on mark.stu_id=student.id where ...条件 ;

<=>select student.id ,student.name ,mark.grade from student inner join mark where mark.stu_id=student.id;

<=> select student.id ,student.name ,mark.grade from student ,mark where mark.stu_id=student.id;

1
insert student.id ,student.name,mark.grade from student left join mark on studen.id=mark.stu_id;

1
insert student.id ,student.name,mark.grade from student right join mark on studen.id=mark.stu_id;

1
2
3
4
5
//将name和grade放同一列
select name from student union all select grade from mark;

//将name和grade放同一列，将student的id和mark表中的id放同一列
//select name,id from student union all select grade,id from mark;

1
2
--从mark表中获取id号，根据该id号用来查询student中的数据
select *from student where id in (select stu_id from mark);