sql语句

--创建表
create table 表名(字段)
创建表，存储学生信息（学号，姓名，年龄）
create table student(id integer primary key autoincrement,name

varchar(20),age integer);

字段：列
字段名称 字段类型
记录：行

**主键：**primary key，区分表中的每一条记录，是唯一标识
autoincrement，自动增长

删除表：drop table student;

修改表：
增加字段：alter table 表名 add 字段
alter table student add sex varchar(4);

–插入语句：
insert into 表名（字段名） values（值）

insert into student(name,age,sex) values(‘tom’,20,’male’);
insert into student(name) values(‘jack’);
insert into student(age,name,sex) values(21,’rose’,’female’);

–查询语句：select 字段名 from 表 where 条件

查询学生表中所有的记录
select * from student;

查询学生表中姓名和年龄
select name,age from student ;

–修改语句
update 表名 set 字段名=值，字段名=值…… where 条件
–将表中所有的年龄修改为18
update student set age=18 ;

–将年龄修改为19，性别修改为male
update student set age=19 ,sex=’male’ where id =5;

– 将学号为3的学生性别修改为female
update student set sex=’female’ where id=3;

–删除语句（删除记录）
delete from 表名 where 条件

–删除表中所有的学生
delete from student;

–删除性别为男的学生
delete from student where sex=’male’;

select * from student;

–条件:where +条件

–>,>= ,=,< ,<=
–查询年龄大于20的学生
select * from student where age > 20;

–查询年龄大于20的女同学
select * from student where age > 20 and sex=’female’;

–查询年龄在18 到21之间的学生，[18,21]
select * from student where age >=18 and age <= 21;

–between and ,两者之间，等于边界
select * from student where age between 20 and 21;

–查询年龄是19 或 20 的学生
select * from student where age =19 or age =20;

–in(值，值……)
select * from student where age in (19,20,21);

– 查询年龄为19的男学生 或者 年龄为21 的女同学
select * from student where (age=19 and sex=’male’) or (age=21 and sex=’female’);

–模糊查询 like
–通配符
%:代表0个或多个字符
_:代表任意一个字符

update student set name=’zhangsan’ where id=5;
–查询姓‘zhang’的学生
select * from student where name like ‘zhang%’;

–查询姓名的第二个字符为o的学生
select * from student where name like ‘_o%’;

–查询姓名中含有s的学生
select * from student where name like ‘%s%’;

–查询内容去掉重复项:distinct
select distinct sex from student

–排序：默认升序，升序（asc） ，降序（desc）
order by 字段

–按年龄升序
select * from student order by age asc;

–按年龄降序
select * from student order by age desc;

–按姓名升序
select * from student order by name desc;

– 先按年龄升序，年龄相同，按姓名降序
select * from student order by age asc,name desc;

–起别名
字段 as 别名，函数 as 别名 ，表 as 别名
select name as 姓名 ,age as 年龄 from student;

–函数
max()
min
sum
avg

select max(age) as max,
min(age) as min,
sum(age) as sum,
avg(age) as avg from student;

count(*):统计记录数
count（字段）：统计该字段的记录数
count(distinct 字段）：去掉重复项，统计记录数

select count(*) from student;
select count(sex) from student;
select count(distinct sex) from student;

–limit 索引，个数，用于数据库的分页
select * from student limit 1,2;(一次显示两条数据 )

–分组：group by 字段
–分组之后再过滤：having 条件

–根据性别分组
select sex from student group by sex;

–根据年龄分组
select age from student group by age;

– 根据年龄，性别分组
select age ,sex from student group by age,sex;

– 根据年龄分组，查询年龄大于20的
select age from student group by age having age >=20;