数据库的基本操作

数据库内操作

对于数据库
1,增：create database 库名;
2,删：drop database 库名;

对于表
1.增
    creat table 表名(
    名称  类型  约束,
    name varchar(8)  primary key//主键 auto_increment//递增  ,
    age int not null//不为空
    )
2.删
    drop table 表名

对于数据
1.增
    insert into 表名 (名称1，名称2) values (数值1，数值2);
    例：insert into 表名 (name,age) values ("张三",19),("李四",20);
2.删
    删除所有数据
    delete from 表名;
    删除指定同一特性的数据
    delete from 表名 where age = 20;
3.改
    update 表名 set 字段 = 某个值 where 条件
    例：update 表名 set age = 21 where name = "张三";
    update 表名 set 字段1 = 值1 字段2 = 值2 where 条件;

3.查
    1.全查
    select * from 表名
    2.通过某项属性查其内容
    select * from 表名 where name = "张三";
    3.通过某项属性查指定内容
    select age from 表名 where name = "张三";

    4.查询时去除重复
    select distinct age from stu

    5.查询前5条数据
    select * from stu limit 5
    6.查询第6条第7条数据
    select * from stu limit (5,2);

idea连接数据库

        //1.导入驱动jar包
        复制粘贴后右键--->Add as Library
//        2.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
//        3.获取数据库的连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/kuaidi","root","123");
        //4.定义sql语句
        String sql = "update kuaidi set qujianma = 12138 where danhao = 12138 ";
        //5.获取执行sql的对象 Statement
        Statement stmt = conn.createStatement();
        //6.执行sql
        int count = stmt.executeUpdate(sql);
        //7.处理结果
        System.out.println(count);
        //8.释放资源
        stmt.close();
        conn.close();

连接用的各关键字的意思

    1.DriverManager : 驱动管理对象
        功能：
        1.注册驱动：
            static void registerDriver(Driver driver:注册与给定的驱动程序 DriverManager.
            写代码使用：Class.forName("com.mysql.jdbc.Driver");
            通过查看源码
        2.获取数据库连接：
            方法：static Connection getConnection(String url , String user , String password)
            参数：
                *url:指定连接路径
                    *语法：jadb:mysql://ip地址（域名）：端口号/数据库名称
                    *列子：jdbc:mysql://localhost:3306/kuaidi","root","123"
                    *细节：如果连接的是本机的MySQL服务器，并且MySQL服务默认端口是3306，则url可以简写为：jdbc:///数据库名
                *user：用户名
                *password：密码

    2.Connection : 数据库连接对象
        功能：
        1.获取执行sql的对象
        2.管理事务：
            *开启事务：setAutoCommit(boolean autoCommit)；调用该方法设置参数为false，即开启事务。
            *回滚事务：rollback()
            *提交事务：commit()
    3.Statement : 执行sql的对象
        功能：
        1.boolean execute (String sql):可以执行任意的sql  （了解）
        2.int executeUpdate （String sql）:执行DML（itsert,update,delete）语句
            *返回值：影响的行数，可以判断这个影响的行数判断DML执行是否成功 返回值>0则执行成功
        3.ResultSet executeQuery(String sql): 执行DQL（select）语句
    4.ResultSet : 结果集对象
    5.PreparedStatement : 执行sql的对象

部分习题

1. grade 表增加一个阶段，“就业期”
   alter table grade add “就业期” varchar(20)
   2.将第三阶段的学生的 gradeid 改为就业期的 id
   alter table change gradeid id int
   3.查询所有得了 100 分的学号
   select id from stu where score = 100
   4.查询所有 1989 年出生的学生（1989-1-1~1990-1-1）
   select name from stu where birthday>1989-1-1&&birthdayt<1990-1-1
   5.查询学生姓名为“金蝶”的全部信息
   select * from stu name =“金蝶”
   6.查询 subjectid 为 8 的科目考试未及格（60 分）的学号和成绩
   select score id from subjectid where (subjectid = 8)<60
   7.查询第 3 阶段课时大于 50 的课程全部信息
   select * from stu where keshi > 50
   8.查询 S1101001 学生的考试信息
   select * from stu where id = S1101001
   9.查询所有第二阶段的女生信息
   select * from stu wehre grade = 2 and sex = girl
   10.“基于.NET 平台的软件系统分层开发”需要多少课时
   select
   11.查询“设计 MySchool 数据库”和“面向对象程序设计”的课时(使用 in) 12 查询所有地址在山东的学生信息
   select
   13 查询所有姓凌的单名同学
   select name from stu where name like ‘凌%’
   14.查询 gradeid 为 1 的学生信息，按出生日期升序排序
   select * from stu where gradeid=1 order by birthday
   15.查询 subjectid 为 3 的考试的成绩信息，用降序排序
   select * from stu where subjectid=3 order by desc birthday
   16.查询 gradeid 为 2 的课程中课时最多的课程信息
   select * from stu where gradeid = 2 and max(time)
   17.查询北京的学生有多少个
   select count() from stu where address = “北京”
   18.查询有多少个科目学时小于 50
   select count () from stu where time < 50
   19.查询 gradeid 为 2 的阶段总课时是多少
   select sum(time) from grade where gradeid=2
   20.查询 subjectid 为 8 的课程学生平均分
   select avg(grade) from stu where subjectid=8
   21.查询 gradeid 为 3 的课程中最多的学时和最少的学时
   select max(time),min(time) from grade where gradeid=3
   22.查询每个科目有多少人次考试
   select class_id , cout() as nums from stu group by class_id
   23.每个阶段课程的平均课时
   select avg(time) from grade group by jieduan
   24.查询每个阶段的男生和女生个数（group by 两列）
   select sex , count() from stu group by sex

1.	查询" 01 "课程⽐" 02 "课程成绩⾼的学⽣的信息及课程分数
    1.分开表达查询两个表的数据
    select sid,score from sc where cid = '01';
    select sid,score from sc where cid = '02';

    2.合并，找到学生id
    select s1.sid,s1.score from
    (select sid,score from sc where cid = '01') as s1
    join
    (select sid,score from sc where cid = '02') as s2
    on s1.sid = s2.sid
    where s1.score > s2.score;

    3.通过学生id查询出学生信息
    select stu.sid,stu.name,s.score
    from student as stu
    join
    (
    select s1.sid,s1.score from
    (select sid,score from sc where cid = '01') as s1
    join
    (select sid,score from sc where cid = '02') as s2
    on s1.sid = s2.sid
    where s1.score > s2.score
    ) as S
    in stu.sid = S.sid;

2. 查询同时存在" 01 "课程和" 02 "课程的情况

    select s1.* from
    (select sid.score from sc where cid = '01') as s1
    join
    (selcet sid.score from sc where cid = '02') as s2
    on s1.sid = s2.sid;

3. 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

    select s1.*,s2.* from
    (select sid,score from sc where cid = '01') as s1
    left join
    (select sid,score from sc where cid = '02') as s2
    on s1.sid = s2.sid;

4. 查询不存在" 01 "课程但存在" 02 "课程的情况

    select * from sc
    where sid not in (select sid from sc where cid= '01')
    and cid = '02';

5.查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩

    select sc.sid,sname,round(avg(score),2) as avg_score
    from sc,student
    where sc.sid = student.sid
    group by sc.sid,sname having avg_score >=60;

6.查询在 SC 表存在成绩的学⽣信息

    select distinct stu.*
    from student as stu join sc on sc.sid = stu.sid
    where name(select name from )

7.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

    select stu.sid,stu.sname,count(sc.cid)as num , sum(sc.score) as total_score
    from student as stu
    left join sc on stu.sid = sc.sid
    group by stu.sid,stu.sname;

8.查询「李」姓⽼师的数量

    select count(*) from teacher where name like '李%';

9.查询学过「张三」⽼师授课的同学的信息



10.查询没有学全所有课程的同学的信息

11.查询⾄少有⼀⻔课与学号为" 01 "的同学所学相同的同学的信息

    select distinct stu.*
    from student as stu
    left join sc on sc.sid = stu.sid
    where sc.sid in (select cid from sc where sid = '01');

12.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息

    select s1.sid,student.sname
    from sc as s1
    join sc as s2
    on s1.cid = s2.cid and s1.sid = '01' and s2.sid != '01'
    join student on s2.sid = student.sid
    group by s2.sid,student.sname
    having count(s2.cid) = (select count(*) from sc where sid = '01');

13.查询没学过"张三"⽼师讲授的任⼀⻔课程的学⽣姓名

14.查询两⻔及其以上不及格课程的同学的学号，姓名及其平均成绩

    select stu.sid,stu.sname,round(avg(stu.score),2) as avg_score
    from student as stu
    join sc on stu.sid = sc.sid
    where

15.检索" 01 "课程分数⼩于 60，按分数降序排列的学⽣信息

16.按平均成绩从⾼到低显示所有学⽣的所有课程的成绩以及平均成绩

方案一
    select sc.*,s2.avg_score
    from sc
    join (select sid,avg(score) as avg_score from sc group by sid) as s2
    on sc.sid = s2.sid
    order by s2.avg_score desc,sc.sid;

方案二
    select
    stu.sname,
    a.score as '语文',
    b.score as '数学',
    c.score as '英语',
    avg(d.score) as '平均成绩'
    from student as stu
    left join sc as a on stu.sid = a.sid and a.cid = '01'
    left join sc as b on stu.sid = b.sid and b.cid = '02'
    left join sc as c on stu.sid = c.sid and c.cid = '03'
    left join sc as d on stu.sid = d.sid
    group by stu.sname,语文,数学,英语
    order by 平均成绩 desc;


17.查询各科成绩最⾼分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最⾼分，最低
分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀
为：>=90 要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若⼈数相同，按课程号升序排列

    select sc.cid,c.cname,
    max(sc.score) as '最高分',
    min(sc.score) as '最低分',
    avg(sc.score) as '平均分',
    count(sc.cid) as '选修人数',
    sum(case when sc.score >=60 then 1 else 0 end)/count(sc.cid) as '及格率',
    sum(case when sc.score >=60 and sc.score<80 then 1 else 0 end)/count(sc.cid) as '中等率',
    sum(case when sc.score >=80 and sc.score<90 then 1 else 0 end)/count(sc.cid) as '优良率',
    sum(case when sc.score >=90 then 1 else 0 end)/count(sc.cid) as '优秀率'

    from sc join course as c on sc.cid = c. cid
    group by sc.cid,c.cname
    order by '选修人数' desc,sc.cid;


18.按各科平均成绩进⾏排序，并显示排名， Score 重复时保留名次空缺

    1.按各学科进行分组，计算平均成绩
    select cid, round(avg(score),2) as avg_sc from sc group by cid;

    2.按照各学科的平均成绩，做自联结，进行比较
    select s1.*,s2.*
    from
    (select cid , round(avg(score),2) as avg_sc from sc group by cid) as s1
    join
    (select cid , round(avg(score),2) as avg_sc from sc group by cid) as s2