sql对（数据库、表、数据）的操作总结&练习

sql对（数据库、表、数据）的操作总结&练习

1.对数据库的操作

• 查看数据库：show databases;
• 增：create database dbname;
  • demo:

  create database if not exists mydb1 charset utf8;
  

• 删：drop database if exists dbname;
• 进入数据库：use dbname;

2.对表的操作

• 增：create table tbname(字段1 数据格式 约束条件,字段2 数据格式 约束条件…);
  • demo：创建学生表

  CREATE TABLE student(
  sno int NOT NULL PRIMARY KEY,
  sname varchar(20) NOT NULL,
  sage datetime NOT NULL,
  ssex char(2) NOT NULL
  );
  

• 删：drop table tbname;
• 改：alter table tbname add/drop…;
  • 1.添加字段：alter table 表名 add 列名 类型
  • 2.删除字段：alter table 表名 drop column 列名
  • 3.修改字段类型：alter table 表名 alter column 列名 类型
  • 4.加主键：alter table 表名 add constraint 约束名 primate key 列名
  • 5.添加唯一约束：alter table 表名 add constraint 约束名 unique （列名1， 列名2…）
• 查：show tables;------查看所有表 、desc tbname;------查看表结构

3.对数据的操作

• 增：insert into 表名 （字段名）values（字段名）;
• 删：delete from 表名 where 条件;
• 改：update 表名 set <列名=更新值> [where <更新条件>];
• 查：select * from 表名…

4.练习

• 4.1用一条SQL 语句查询出每门课都大于80 分的学生姓名？
  // 第一种方式：
  select name from table where name not in ( select name from table where score < 80 );
  // 第二种方式：
  select name from table group by name having min(score) >= 80;
• 4.2删除除了自动编号id不同, 其他都相同的学生冗余信息？ 字段分别是：id，stunum，name，course，score
  delete table where id not in ( select min(id) from table group by stunum,name,course,score );
• 4.3一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合?// 所有组合：ab,ac,ad, bc,bd, cd 所有左侧字母都小于右侧字母
  select a.name,b.name from team a,team b where a.name < b.name order by a.name,b.name ;
• 4.4复制表( 只复制结构, 源表名：a新表名：b)
  select * into b from a where 1<>1; // where1=1，拷贝表结构和数据内容
  Oracle: create table b as select * from a where 1=2;
• 4.5拷贝表( 拷贝数据, 源表名：a目标表名：b)
  insert into b(a, b, c) select d,e,f from a;
• 4.6日程安排提前五分钟提醒
  select * from 日程安排 where timestampdiff(‘minute’,开始时间,curtime())>5；
• 4.7两张关联表，删除主表中已经在副表中没有的信息
  delete from info where not exists (select * from infobz where info.infid=infobz.infid )；
• 4.8有两个表A 和B ，均有key 和value 两个字段，如果B 的key 在A 中也有，就把B 的value 换为A 中对应的value？
  update b set b.value=(select a.value from a where a.key=b.key) where b.id in(select b.id from b,a where b.key=a.key);
• 4.9已知有如下4张表：学生表：STUDENT(SNO,SNAME,SAGE,SSEX)课程表：COURSE(CNO,CNAME,TNO)成绩表：SC(SNO,CNO,SCORE)教师表：TEACHER(TNO,TNAME)
  • （1）查询课程编号为“001”的课程比“002”的课程成绩高的所有学生的学号？
    select x.sno,x.score,y.score from sc x,sc y where x.cno=001 and y.cno=002 and x.sno=y.sno and x.score > y.score；
  • （2）查询平均成绩大于60分的学生的学号和平均成绩？
    select sno,avg(score) from sc group by sno having avg(score)>60；
  • （3）查询姓“孙”的老师的个数？
    select count(Tname) from teacher where Tname like ‘悟%’；
  • （4）查询没学过“悟空”老师课的学生的学号、姓名？
    select sno,sname from student where sno not in(select sno from SC where cno in(select cno from course where tno in(select tno from teacher where tname=‘悟空’)))；