详数据库二

一.用结果集创建表

语法: 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;