数据库SQL语法实例讲解

语法

创建学生数据库，数据表名为student： student(sno,sname,sex,age,class)。

其中，各列描述如下：

sno:学号，字符串

name:姓名，字符串

sex:性别，字符串

age:年龄，整数

class:班级编号，整数

插入语句：

insert into <数据表名> [列名] values<值列表>

insert into student values('121531010','张三','男',18,10);

insert into student (sno,name,sex,age,class) values('121530808','张丰','男',20,10);

修改语句：

update 数据表名 set 列名=更新值 [列名2=更新值] [where<更新条件>]

update student set name='章三',age=17 where sno='121531010';

update student set age=17 where sno='121531001';

删除语句：

delete from 数据表名 [where<更新条件>]

delete from student where sno='121530806';

查询语句：

select 列名 from 数据表名 [where<更新条件>] [order by <排列的列名>(asc或desc)]

select * from student;

select sno,name,age,class from student where class=10 order by sno;

select * from student order by age;//按照年龄升序排列

select name,age from student;

select name,sex from student order by sno;

select sno,sname,sex form student where classid = 1 order by sno

去掉重复的行：

select distinct 列名 from student;

select distinct name from student;

条件查询：

and

select name from student where sex='男' and class=1;

or

select name,sex from student where class=1 or class=2;

条件查询--确定范围

select name from student where age between 16 and 18;

select name from student where age not between 16 and 18;

条件查询--确定集合

select name from student where class in(1,2);

select name from student where class not in(1,2);

like % 模糊查询(匹配多个字符)

select name from student where name like '张%';

like _ 匹配一个字符

select name from student where name like '张__';

count 统计查询：

select count(*) from student;

select count(*) from student where class=1 or class=2;

select avg(age) from student;

avg 按列计算平均值

sum 按列计算值的总和

count 按列值统计个个数

max 求一列中的最大值

min 求一列中的最小值