详数据库二

一.用结果集创建表

语法: create table 新表名 selsect语句

实例:create stu2 select *from stu1;

复制表结构及其表数据,新表中不包含源表中的主键,唯一.外键等约束,自增长字段变成默认值为0的字段

二.数据操作语句

2.1 向表中写入数据

 2.1.1 insert语句

   1) 作用

       向指定中插入一行数据

    2) 语法:insert into 表名[(字段列表)] values(值列表);

       注意: 字段列表和值列表必须是一一对应的

  2.1.2 批量插入多行数据

    1)语法

      insert into 表名[(字段列表)] values

       (值列表1),

       (值列表2),

        ......

       (值列表n),

     2)示例

      SQL语句如下:

       insert  into  student  values ('2020','张三','12333333','1'),('2021','类三','123332344','1'),('2023','李四','132433333','2'),

  2.1.3 使用insert...select语句插入结果集

    1) 语法: insert into 目标表名 [(字段列表1)] select 字段列表2 from 源表名 [where子句....];

    2) 示例 :insert into  stul select *from student;

 2.2 update语句

   2.2.1 作用

     根据条件修改 表中的数据

   2.2.2 语法

     update 表名 set 字段=新值[,字段=新值....] [where 条件表达式];

   2.2.3 示例

     创建一个成绩表

     create table exam(stu_on int auto_increment primary key,score tinyint unsigned);

     插入数据

     insert into exam values(null,68);

     insert into exam  values(null,99);

    1) 每人加5分(无条件更新)

    update exam set score = score+5;

    2) 分数高于100的 改为100分

     update exam set score =100 where score>100;

    3) 如果成绩处于55-60之间,改为60(有两个条件)

      update exam set score=60 where score >=55 and score>=60;

    3.truncate

     1)作用

      截断表

     2) 语法

       truncate table 表名;

     3) 和delete语句的区别

       a. truncate语句不能应用于主表(即使从表没有数据)

       truncate table student ; #错误\从表choose中没有数据

       delete from student ; #正确/

      b.对于自增长字段,使用truncate语句截断表后,值恢复初始值

        insert  into exam values(null,80); --stu no 8

       truncate table exam ;

       insert into exam values(null,80);---stu no 1;

      c. truncate语句不能回滚

    3. select 语句

     3.1select 语句概述

      3.1.1 语句 

        select  字段列表

          from 数据库

             [where 条件表达式]

              [group by 分组字段 [having 条件表达式]]

               [order by 排序字段 {[asc] |desc}];

   其中;

    字段列表:            指定检索字段

     数据源 :              检索的表或试图

     where 子句 :       指定数据行的过滤条件

     group by 子句 :   根据分组字段,把数据行分成若干个组,并进行汇总统计

     having 子句:        对分组后的数据进行筛选

     order  by 子句 :   对结果集进行排序,默认升序

  3.1.2 使用select子句指定字段列表

     字段列表的指定方式:

     *:                            代表数据源中的全部字段

     字段列表:               逗号隔开的字段列表,指定需要检索的若干个字段

     表名.*  :                 多表查询时,指定某个表中的全部字段

     表名. 字段:            多表查询是,指定某个表中的某个字段

     表达式 :                 表达式中可以包含算术运算,函数等

    2)   示例

     ---- 检索MySQL的版本号,服务器时间

     select version (), now ();

 

命名别名
字段或 表达式 [ as ] 别名
select version() as 版本号 ,now() 服务器当前时间;
基本查询语句
语法:

select 字段列表 from 表名;

实例:

列出表中的所有字段

SELECT * FROM student;
列出表中的部分字段

select student_no,student_name,student_contact from student;
字段去命名别名

select student_no as 学号,student_name 姓名,student_contact 联系方式 from student;
使用表达式

select stu_no 学号,score 卷面成绩,score*0.7+30 综合成绩 from exam;
特殊的关键字
distinct 去掉重复的行
语法:

select distinct 字段列表 from 表名;
示例:

select class_no from student;
单列排序

select distinct class_no from student;
多列排序

use information schema;
 
show tables; --显示当前数据库中所有表的名字
desc tables; -- 查看表tables的表结构
select table schema 数据库名,table name 表名,table type 表类型from infomation schea table;
select distinct table schema数据库名from information schema . tables;
select distinct table_ type from information schema. tables;
select distinct table_ schema 数据库名,table_ type 表的类型
information schema . tables;
使用 limit 限制显示行数
分页查询
语法:

select 字段列表 from 表名  limit [ start , ] length;
其中

start : 表示从第几行开始检索,缺省时为0, 表示为第一行

length : 表示要检索多少行

示例

列出 information_schema.tables 表中的前10行 显示 table_schema,table_name;

select table_schema 数据库名,table_name 表名 from information_schema.tables limit 10;
练习: 每页显示 10行,列出 tables表中的第7页

select table_schema 数据库名,table_name 表名 from information_schema.tables limit 60,10;
 
多表查询
需求: 列出学生及其所在班级的信息,包括学号,姓名和班级名称

学号,姓名: student

班级名称: classes

关联字段: student.class_no,classes.class_no,

关联条件: student.class_no=classes.class_no,

多表查询的类型
内连接 : 符合关联条件的数据行被检索出来,不符合关联条件的被过滤掉
外连接: 外连接的结果集 = 内连接的结果集 + 匹配不上的数据行
 

内连接
语法

select 字符安列表 from 表1 [ inner ] join 表2 on 关联条件;
需求: 列出学生及其所在班级的信息,包括学号,姓名和班级名称

学号,姓名: student

班级名称: classes

关联字段: student.class_no,classes.class_no,

关联条件: student.class_no=classes.class_no;

select student.student_no,student.student_name,classes.class_no 
    from student join classes on student.class_no=classes.class_no;
向学生表中插入一行数据 没有班级

insert into student values('2018006','小明','20000000',null);
班级表中也插入一行数据

insert into classes(null,'2018机械自动化2班','机电工程');
内连接匹配不上数据不会显示

select student.student_no 学号,student.student_name 姓名,classes.class_no 班级名称 
    from student join classes on student.class_no=classes.class_no;
表 的别名
表名  [as]  别名
select s.student_no 学号,s.student_name 姓名,c.class_no 班级名称
    from student s join classes c on s.class_no=c.class_no;
错误演示

给 表一旦命名别名,在该语句中 就只能 使用别名 ,原表名就失效了
select student.student_no 学号,s.student_name 姓名,c.class_no 班级名称 
    from student s join classes c on s.class_no=c.class_no;
如果连接的多张表中,没有重名的字段,可以省略 字段前的表名 或 别名的修饰 缺点 会 降低SQL 语句的查询效率

select student no 学号,student name 姓名,class name 班级 
    from student s join classes c on s.class_no=c.class_no;
三表内连接
向选课表choose 中 插入测试数据

student_no course_no score( ) choose_time

SQL 语句如下

insert into choose values( ... );

语法

select 字段列表
from 表1 [ inner ] join 表2 on 关联条件1 [ inner ] join 表3 on 关联条件2;
示例;

select  s.student_on 学号,s.student_name 姓名, cs.course_name 课程 ,ch.score 成绩
​    from student s join choose ch on s.student_no = ch.student_on join course cs on cs.course_on = ch.course_on;
练习:

列出教师及其所授课程的信息,包括教师的工号,姓名,课程名称,上限人数

select t.teacher_on 教师工号,t.teacher_name 姓名,c.course_name 课程,c.up_limit 人数
    from teacher t join course c on t.teacher_on=c.teacher_on;
教师的工号,姓名,课程名称,和选修该课程的学生的学号

select t.teacher_on 教师工号,t.teacher_name 姓名,c.course_name 课程,c.up_limit 人数
     from teacher t join course c on t.teacher_on=c.teacher_on join choose ch on c.course_on = ch.coure_on;
外连接
左外连接 :
左外连接结果集 = 内连接的结果集 + 左表中匹配不上的数据
语法

select 字段列表
from 左表 left [ outer ] join 右表 on 关联条件;
示例:

列出所有学生及其所在班级信息

select s.student_on 学号,s.student_name 姓名,c.calss_name 班级
    from student s left join classes c on s.class_on = c.class_on;  
右外连接 :
右外连接结果集 = 内连接的结果集 + 右表中匹配不上的数据
语法:

select 字段列表
from 左表 right [ outer ] join 右表 on 关联条件;
示例:

列出所有学生及其所在班级信息

select s.student_on 学号,s.student_name 姓名,c.calss_name 班级
    from classes c right join student s on s.class_on = c.class_on;  
 

如果想在结果集中显示两张表的所有内容
全外连接--- MySQL 不支持语法
全外连接结果集 = 内连接的结果集 + 两表中匹配不上的数据
语法 :

select 字段列表
from 左表 full [ outer ] join 右表 on 关联条件;
MySQL 支持语法 union 语句 union all 语句
作用:
将两个select的结果作为一个整体显示出来。

满足条件： 1、两个select查询的列的数量必须相同；

2、每个列的数据类型需要相似；

区别:
union all是将两个select语句的结果求并集。 union是将union all的结果下再去除重复数据

union
使用 union 实现以上功能

select s.student_on 学号,s.student_name 姓名,c.calss_name 班级
    from student s left join classes c on s.class_on = c.class_on  
union   
select s.student_on 学号,s.student_name 姓名,c.calss_name 班级
    from student s right join classes c on s.class_on = c.class_on; 

 
如果union 连接的两张表的字段列表 数量不一致,可以给字段列表少的表赋值 null, 不赋值会报错

select * from student union select *,null from teacher;
union all
select s.student_on 学号,s.student_name 姓名,c.calss_name 班级
    from student s left join classes c on s.class_on = c.class_on  
union all
select s.student_on 学号,s.student_name 姓名,c.calss_name 班级
    from student s right join classes c on s.class_on = c.class_on;