mysql 数据库的一些操作

一些基本查询：
show databases;
use test;
show tables;
desc student;
单引号' 转->
1模式定义：
2基本表定义
create table s (si char(4) not null unique,
sn char(20)  not null,
sa int,
se char(2),
sd char(20),
primary key (si),
check (sa between 15 and 25));

create table c (ci char(4) not null,
cn char(10) not null,
pi  char(4),
primary key(ci));

create table sc (si char(4) not null,
ci char(4) not null,
g int ,
primary key (si,ci));

3基本表更新
增加列： (增加的列不允许出现not null)
alter table s  add address varchar(30);
删除列：
alter table s drop address ;
修改列：
alter table s modify si char(6);
删除主键：(删除不成功？)
alter table s drop primary key (si);
补充定义主键：// 主键要满足非空和唯一性要求
alter table s add primary key (si);

4 删除基本表

drop table s;

5创建索引
按升序排列的索引
create unique index s_xsno on s(si);
create  index sc_xsc on sc(si,ci);
聚簇索引
6 删除索引
drop index sc_xsc; //删除失败

7 单表查询
select * from s;
select si, sa,sd from s;  // 显示感兴趣的列
select * from sc;
select distinct si from sc; // 去重复
select si, ci ,10+g from sc; //成绩参与运算
select * from sc where g=60;
select * from sc where g>=60; //找出考试及格的学生
select * from sc where g<>60; // 不等于
select * from sc where  g between 50 and 60; // between
select * from sc where  g not between 50 and 60; // not between
select * from sc where  si in ('s36','si36'); // in /not in
select * from sc  where  g is  null;// 涉及空，使用is
select * from sc  where si in('s36') and g>60;// 多重条件用and
select * from sc order by g desc; // 按成绩降序排列
select * from s  where sn like '李__';// 一个汉字连个通配符
select * from s where sn like '刘%';// % 而不是*

8连接查询 (多表查询）
 
9嵌套查询（多表查询） (in比较容易理解）
//方法1：使用in实现嵌套。 （多重嵌套) 多表联查
//查询成绩小于60分的学生的姓名（不及格记录）
select sn from s  where si in  // 表s中查询，表s,sc通过si关联
(select si from sc where g<60);// 表sc中查询
//查询C++课程成绩不及格的学生的姓名 (三表联查）
select sn from s where si in
(select si from sc where g<60 and ci in
  (select ci from c where cn='c++'));
//方法2：使用exists实现嵌套 （跨表联查）
//查询成绩小于60分的学生的姓名（不及格记录）
select sn from s where exists
(select * from sc where s.si=sc.si and sc.g<60); // 两表交叉
//查询C++课程成绩不及格的学生的姓名 (三表联查）
select sn from s where exists
(select * from sc where s.si=sc.si and sc.g<60  and exists
(select * from c where cn='c++'));

10集合查询 contains
并集：union

11 函数
+-*/ ，integer(取整数）,sqrt , sin ,cos ,substring,upper,months_between
count ,sum, avg, max, min 等

select count(*) from s; // 行数
select avg(sa) from s;// 平均年龄
select * from s where sa in (select min(sa) from s );//年龄最小的记录

12分组与筛选(非常重要）
select * from sc; // 选课表中就有很多重复
// 求每个学生的平均成绩
select si,avg(g) from sc group by si; // 执行上,先分组，再求平均
//求每个学生的选课数目
select si,count(ci) from sc group by si;
// 筛选出 超过两个学生选修的课程的学生数。
（第一步按照课程分组，课程大于2的组被选中，然后计算学生数）
select ci,count(si) from sc group by ci  having count(*)>2;
//having 和where是有区别的。having 是对分组进行选择，where是对基表进行选择。
// 按照总平均值降序给出 所有课程都及格的学生的总平均成绩，不包括学生si36
// 第一步 是按照学生进行分组，然后选出成绩没有不及格的分组（学生）
// 计算分组平均值。按照降序排列。
select  si ,avg(g) from sc
where si<>'si36'
group by si
having min(g) >60
order by avg(g) desc;

 

13 插入数据
insert into s values ( 's35','liu','20','se','cs');
insert into sc(si,ci)  values( 's35','c10');
insert into c(ci,cn) values('c10','c语言');
14 修改数据
update s set sd='css' where si='s35';
update s set sa=sa+1 where si='s35';
update s set g='100' where 'css'=(select sd from s where s.si=sc.si);//无法识别s.si
15删除数据
delete from c where ci='c10';

16定义数据视图
create view cs_s (si, sn ,sd,sa,g)
as select  from s where sd='css';  // 失败

17 查询数据视图
select * from cs_s;
18 删除数据视图
drop view cs_s;
19 授予权限
grant all privileges on s to user1;// 找不到user1
grant select  on table s to public;// 找不到public
20 收回权限
revoke select on table s from use1;

 

//完善数据库数据
// 添加学生信息
insert into s(si,sn,sa,se,sd) values('s36','lina', '22','se','dz');
update s set sn='李娜' where si='s36';
insert into s(si,sn,sa,se,sd) values('si36','momo', '22','se','dz');
update s set sn='摸摸' where si='si36';
//添加学生选课信息
insert into sc(si,ci) values('si36','c11');
insert into sc(si,ci) values('s36','c11');
insert into sc(si,ci) values('s35','c11');
//添加课程信息
insert into c(ci,cn,pi) values('c11','C++','c');
insert into c(ci,cn,pi) values('c12','英语','c');
//更改学生的成绩,所有学生的成绩都为60
update sc set g='60' ;
update sc set g='100' where si='s36';
update sc set g='50' where si='si36';