SQL学习笔记一

1，创建数据库
CREATE DATABASE students;
2,创建表
CREATE TABLE grades
(name VARCHAR(10) NOT NULL,
class CHAR(10) NOT NULL,
gender CHAR(1) NOT NULL,
score INTEGER NOT NULL,
PRIMARY KEY (name));

2,删除表
DROP TABLE grades; 删除操作不可恢复，需十分谨慎
3,更新表
ALTER TABLE grades ADD COLUMN age INTEGER NOT NULL;
ALTER TABLE grades DROP COLUMN age;

4,插入操作
INSERT INTO TABLE students
VALUES
(‘小明’，‘2’，‘M’,89,12);

5,SELECT操作
select name
from students
where score >90’

select distinct class_id
from students
where gender = ‘M’; distinct表示去重

select count(name)
from students
where score >90 上九十分的人数和

select sum(score),avg(score),max(score),min(score)
from students
where gender = ‘M’ 男生的分数总和和平均分

select name,score,class_id
from students
where gender = ‘M’
group by class_id
having class_id = ‘3’; group by 和 having 操作

select name,id
from students
order by socre; 默认为升序，降序为DESC