SQL备忘

-- 建表
create table score(
sc_name char(10),
-- 指定主键
id int primary key,
score int
);


-- 修改表结构：增加字段
alter table score add sc_rank char(1);


-- 修改表结构：删除字段
alter table score drop sc_rank;


-- 删除表
drop table score;


-- 插入数据
insert into score values('xiaoqiang',1,90);
insert into score values('wangcai',2,87);
insert into score values('zhangsan',3,95);
insert into score values('zhaoliu',6,88);
insert into score values('aabb',7,88);
insert into score values('ccdd',8,88);
insert into score values('wangwu',5,82);


-- 插入多条数据的方式二
insert into score values
('xiaoqiang',4,90),
('xiaoqiang',9,90),
('wangwu',13,82);


-- 更新数据
update score set score=85 where id=5;


-- 查询
select * from score;
select * from score order by id;
select * from score order by score;


-- 使用聚合函数
select min(score) as minv from score; 
select max(score) as maxv from score;
select avg(score) as avgv from score;
select count(score) as totalcount from score;
select sum(score) as totalsum from score;


-- 去重
select distinct(score) from score ;




-- 分页查找 
select * from score limit 7,3;