ToB企服应用市场:ToB评测及商务社交产业平台

标题: 数据库（头歌） [打印本页]

作者: 不到断气不罢休 时间: 2024-6-13 22:14
标题: 数据库（头歌）
目录
一. MySQL数据库 - 初始MySQL
第1关：创建数据库
第2关：创建表
第3关：使用主键束缚
第4关：外键束缚
第5关：添加常用束缚
二. MySQL开发技巧 - 索引
平凡索引：
唯一索引：
主键索引：
组合索引：
删除索引
查询表中索引
三. MySQL数据库 - 单表查询（一）
第1关：基本查询语句
第2关：带 IN 关键字的查询
第3关：带 BETWEEN AND 的范围查询
四. MySQL数据库 - 单表查询（二）
第1关：带 LIKE 的字符匹配查询
第2关：查询空值与去除重复结果
第3关：带 AND 与 OR 的多条件查询
五. MySQL数据库 - 单表查询（三）
第1关：对查询结果进行排序
第2关：分组查询
第3关：使用 LIMIT 限定查询结果的数量
六. MySQL数据库 - 使用聚合函数查询
第1关：COUNT( )函数
第2关：SUM( )函数
第3关：AVG( )函数
第4关：MAX( )函数
第5关：MIN( )函数
七. 数据查询（一）
第1关：按条件查询单表的所有字段
第2关：查询唯一值
第3关：统计查询
第4关：分组查询
第5关：数据排序
八.数据查询（二）
第1关：多表查询
第2关：多表查询及统计分组
第3关：子查询
第4关：多表子查询
九. MySQL数据库 - 毗连查询
第1关：内毗连查询
第2关：外毗连查询
第3关：复合条件毗连查询
十. MySQL数据库 - 子查询
第1关：带比力运算符的子查询
第2关：关键字子查询
十一. MySQL数据库 - 分组选择数据
第1关：GROUP BY 与聚合函数
第2关：使用 HAVING 与 ORDER BY
十二. MySQL数据库 - 数据库和表的基本操纵（一）
第1关：检察表结构与修改表名
第2关：修改字段名与字段数据类型
第3关：添加与删除字段
第4关：修改字段的排列位置
第5关：删除表的外键束缚
十三. MySQL数据库 - 数据库和表的基本操纵（二）
第1关：插入数据
第2关：更新数据
第3关：删除数据
十四. MySQL开发技巧 - 视图
视图的定义
创建视图
操纵视图
删除视图
十五. MySQL开发技巧 - 分页和索引
第1关：MySQL 分页查询
第2关：索引（单列索引）
第3关：索引（组合索引）
十六. MySQL数据库 - 其他函数的使用
第1关：字符函数
第2关：数学函数
第3关：日期时间函数和流程控制类函数
十七. 数据库E-R图

一. MySQL数据库 - 初始MySQL

第1关：创建数据库

毗连数据库

          mysql -u用户名 -p密码
          mysql -uroot -p123123 -h127.0.0.1
    （在平台上毗连数据库还需要加上一句-h127.0.0.1）

检察已存在的数据库

          show databases;

创建数据库

          create database MyDb(数据库名);

删除数据库

drop database MyDb(数据库名);

第2关：创建表

选择数据库

          USE MyDb;

创建表

          CREATE TABLE 表名
        (
          字段名数据类型,
          字段名数据类型,
          字段名数据类型
        );

检察表的基本结构

          DESCRIBE 表名;
          DESC 表名; （简写）

删除表

          drop table t_user(表名);

第3关：使用主键束缚

            表的主键就可以看做是人的身份证

添加主键束缚

          1. 在定义列的同时指定主键

          2. 在定义完所有列之后指定主键

多字段联合主键

          CREATE TABLE t_emp2
        (
          name VARCHAR(32),
          deptId INT,
          salary FLOAT,
          PRIMARY KEY(name,deptId)
        )

第4关：外键束缚

外键就是另一张表中的主键,外键的主要作用就是保持数据的同等性，完整性

添加外部关键字

CONSTRAINT 外键名 FOREIGN KEY 字段名 REFERENCES 主表名(主键名)

第5关：添加常用束缚

添加唯一束缚

          唯一束缚（Unique Constraint）要求该列唯一，允许为空，但是只能有一个空值。唯一束缚可以确保一列或者几列不出现重复值。

          CREATE TABLE t_dept (
          id INT PRIMARY KEY,
          name VARCHAR(22) UNIQUE,
          location VARCHAR(50)
        )

添加非空束缚

          CREATE TABLE t_dept(
          id INT PRIMARY KEY,
          name VARCHAR(22) NOT NULL,
          location VARCHAR(50)
        )

默认束缚

          给字段一个默认值

          CREATE TABLE t_emp (
                id INT PRIMARY KEY,
                name VARCHAR(22),
                sex VARCHAR(2) DEFAULT '男'
        ) DEFAULT CHARSET=utf8;

  注：1.假如是添加字符串型默认值要使用单引号，假如是整型则不需要加任何符号；
          2.假如要添加的是中文默认值，则需要加上DEFAULT CHARSET=utf8，使用英文字符则不需要

自动增加属性值

          经常有个需求就是，在每次插入新纪录时，系统自动生成字段的主键值，默认情况下初始值和增量都为1。

          CREATE TABLE t_tmp
        (
          id int PRIMARY KEY AUTO_INCREMENT,
          name VARCHAR(32)
        )
  在数据库MyDb中创建表t_user，表结构如下：
  字段名称数据类型备注idINT用户ID，主键，自动增长usernamevarchar(32)用户名，非空，唯一sexvarchar(4)性别，默认“男”      CREATE DATABASE MyDb;
      use MyDb;
      create table t_user
      (id int PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(32) NOT NULL UNIQUE,
    sex varchar(4) DEFAULT '男'
      )DEFAULT CHARSET =utf8;


二. MySQL开发技巧 - 索引

        数据库索引是一种进步数据库系统性能的方法。索引能让数据库服务器更快地查找和获取表中指定的行，但是索引也给数据库系统带来了一定的开销，所以我们应该谨慎地使用它们。

平凡索引：

          仅加速查询，最基本的索引，没有任何限定，是我们大多数情况下使用到的索引

          1. 创表时创建平凡索引

                  CREATE table mytable(
                        id INT NOT NULL,
                        username VARCHAR(16) NOT NULL,
                        INDEX [indexName] (username)
                );

          2. 建表后创建平凡索引

       create INDEX 索引名称 on 表名(字段名);
       #或者
       ALTER TABLE 表名 ADD INDEX 索引名称 (字段名);


                  创建名为idx_age的平凡索引：
                  create index idx_age on student(age);

唯一索引：

          索引列中的值必须是唯一的，但允许为空值

       CREATE UNIQUE INDEX 索引名称 ON 表名(字段名);
       #或者
       ALTER TABLE 表名 ADD UNIQUE (字段名);

                  创建名为uniq_classes的唯一索引：
                  CREATE UNIQUE INDEX uniq_classes ON student(classes);

主键索引：

          主键索引一般在建表时创建，会设为 int 而且是 AUTO_INCREMENT自增类型的，例如一般表的id字段。


      CREATE TABLE mytable (
      id int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (id)
      );

          创建名为pk_student的主键索引：
          create table student(
              stu_id int not null ,
              name varchar(25) not null,
            age int not null,
            sex char(2) not null,
            classes int not null,
            grade int not null,
              primary key (stu_id)
          );

组合索引：

         组合索引就是在多个字段上创建一个索引。（应用场景：当表的行数远宏大于索引键的数目时，使用这种方式可以显着加快表的查询速度）


     CREATE INDEX 索引名称 ON 表名(字段1,字段2，字段3);
     #或者
     ALTER TABLE 表名 ADD INDEX 索引名称(字段1，字段2，字段3);


          创建名为idx_group的组合索引：
          CREATE INDEX idx_group ON student(name,sex,grade);

删除索引

     #使用drop删除索引
     drop index index_name on table_name ;

     #使用alter删除索引
     alter table table_name drop index index_name ;
     alter table table_name drop primary key ; #删除主键索引

查询表中索引

       show index from 表名;


三. MySQL数据库 - 单表查询（一）

第1关：基本查询语句

查询数据表中指定字段的内容:

  SELECT 字段名 FROM 表名;
  SELECT 字段名1, 字段名2 FROM 表名;
          提示： MySQL 中的 SQL 语句对关键字的大小写不敏感， SELECT 和 select 是一样的。但是许多开发者更喜好将关键字大写，表名和字段名使用小写，养成这种风俗，以后写出来的脚本就更容易阅读和维护了。

  查询数据表中的所有内容:

  SELECT * FROM 表名;
           提示：虽然使用星号 * 看起来是一件很方便的事情，但是发起在不想获取整张表的情况下，使用字段名来进行查询。很显然，它会降低查询的服从。

查询字段Name和Salary的内容；

          SELECT Name, Salary
          FROM tb_emp;

查询整张表的内容。

          SELECT *
          FROM tb_emp;

第2关：带 IN 关键字的查询

带IN关键字的查询
    IN关键字被用在WHERE语句的后边，用来过滤所需要查询的内容。更形象的说，IN关键字的使用情况就像点名，点到谁谁就要站出来。

  SELECT 字段名 FROM 表名 WHERE 字段名 IN (n1,n2,n3,...);

  带NOT IN关键字的查询
    查询的是除了括号中出现内容外的所有内容。

  SELECT 字段名 FROM 表名 WHERE 字段名 NOT IN (n1,n2,n3,...);

  查询当Id不等于1时，字段Name和Salary的内容:

  SELECT Name ,Salary
  FROM tb_emp
  WHERE Id not IN (1);

第3关：带 BETWEEN AND 的范围查询

带BETWEEN AND关键字的查询
          某一范围内符合条件的数据, BETWEEN AND需要两个参数支持，一个是范围的开始值，另一个是竣事值。假如字段值满意指定的范围查询条件，就返回这些满意条件的数据内容。

  SELECT 字段名 FROM 表名 WHERE 字段名 BETWEEN n1 AND n2;

  带NOT BETWEEN AND关键字的查询
          表示查询指定范围之外的值。

  SELECT 字段名 FROM 表名 WHERE 字段名 NOT BETWEEN n1 AND n2;

  查询当字段Salary范围在3000~5000时，字段Name和Salary的内容。

  SELECT Name,Salary
  FROM tb_emp
  WHERE Salary BETWEEN 3000 AND 5000;


四. MySQL数据库 - 单表查询（二）

第1关：带 LIKE 的字符匹配查询

使用通配符%模糊匹配数据内容
  百分号通配符%可以匹配任意长度的字符，以致包括零字符。

  SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '字符%';
  其中 % 的位置可以根据需要在字符间变革。

  使用通配符_模糊匹配数据内容
  下划线通配符_只能模糊匹配1个字符。

   SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '字符_';
  其中`_`的位置可以根据需要在字符间变革。

  查询所有Name以字母C为起始的员工的Name和Salary的内容

  SELECT Name,Salary
  FROM tb_emp
  WHERE Name LIKE 'C%';


第2关：查询空值与去除重复结果

查询空值
          这个NULL既不代表0，也不代表空字符，而是代表一种未知的状态，比如不实用或者放着等将来有符合数据了再添加进去。

  SELECT 字段名 FROM 表名 WHERE 字段名 IS NULL;

  去除重复结果
          消除数据表中重复的数据,DISTINCT

  SELECT DISTINCT 字段名 FROM 表名;

使用关键字IS NULL返回数据表中字段DeptId为NULL的所有字段的内容，注意，返回的是指定行所有字段的内容；

          SELECT *
          FROM tb_emp
          WHERE DeptId IS NULL;

使用关键字DISTINCT返回数据表中字段Name不重复的内容，注意，只需返回字段Name的内容。

          SELECT DISTINCT Name
          FROM tb_emp;


第3关：带 AND 与 OR 的多条件查询

带AND关键字的多条件查询
    满意所有条件的查询才会被返回。

   SELECT 字段名 FROM 表名 WHERE 表达式1 AND 表达式2;

  带OR关键字的多条件查询
    满意两个条件中的其中一个条件即可返回结果。

  SELECT 字段名 FROM 表名 WHERE 表达式1 OR 表达式2;

  提示：
          1. OR 可以和 AND 一起使用。但是 AND 的优先级要高于 OR 的优先级。
    2. 关键字IN，它能实现与OR相同的功能，虽然两种写法功能一样，但是更保举使用 IN 关键字。因为它不但逻辑清晰，执行的速度也会快于 OR 关键字。更紧张的是，使用 IN 关键字，以后可以执行更加复杂的嵌套语句。

使用关键字AND返回数据表中字段DeptId为301并且薪水大于3000的所有字段的内容，其中DeptId的倒数第二个字母为i的大写；

          SELECT *
          FROM tb_emp
          WHERE DeptId = 301 AND Salary > 3000;

使用关键字IN返回数据表中字段DeptId为301和303的所有字段的内容。

          SELECT *
          FROM tb_emp
          WHERE DeptId IN('301', '303');


五. MySQL数据库 - 单表查询（三）

第1关：对查询结果进行排序

对查询结果排序
    对读取的语句进行排序，使用Order By子句来设定

  SELECT 字段名 FROM 表名 ORDER BY 字段名 [ASC[DESC]];

  指定排序方向

ASC 升序关键字
DESC 降序关键字

  查询1班同学的所有信息以成绩降序的方式显示结果
          SELECT *
          FROM tb_score
          WHERE class_id IN(1)
          ORDER BY score DESC;

第2关：分组查询

分组查询的单独使用
    查询的是每个分组中初次出现的一条记录。

  SELECT 字段名 FROM 表名GROUP BY 字段名;

  对班级名称进行分组查询
          SELECT *
          FROM tb_class
          GROUP BY class_id ;


第3关：使用 LIMIT 限定查询结果的数量

LIMIT的使用
    限定查询结果的数量。

  SELECT 字段名 FROM 表名 LIMIT [OFFSET,] 记录数;

  参数阐明：

第一个参数，OFFSET，可选参数，表示偏移量，假如不指定默认值为0，表示从查询结果的第一条记录开始，若偏移量为1，则从查询结果中的第二条记录开始，以此类推。
第二个参数，记录数，表示返回查询结果的条数

  查询班级中第2名到第5名的学生信息，并根据学生成绩进行降序排序。
          SELECT *
          FROM tb_score
          WHERE score
          ORDER BY score DESC LIMIT 1,4;



六. MySQL数据库 - 使用聚合函数查询

第1关：COUNT( )函数

COUNT()函数基本使用
    用来统计记录的总条数

    select count(*/字段名) from 数据表;

  查询该表中一共有多少条数据
          select count(*)
          from tb_class;

  查询此表中367 班有多少位学生,以班级id和对应人数格式输出
          select classid,count(*)
          from tb_class
          where classid=367;


第2关：SUM( )函数

SUM()函数基本使用
    对数据表的某列进行求和操纵

    select sum(字段名) from 数据表;

  查询该表中学生的总成绩
          select sum(score)
          from tb_class;

  查询学生语文课程的总分数
          select course, sum(score)
          from tb_class
          where course='语文';


第3关：AVG( )函数

AVG()函数基本使用
          对数据表的某列进行求平均值操纵

    select avg(字段名) from 数据表;

  查询表中该班级三位同学语文的平均分数以及对应的课程名
          select course, avg(score)
          from tb_class
          where course='语文';

  查询表中该班级三位同学英语的平均分数以及对应的课程名
          select course, avg(score)
          from tb_class
          where course='英语';


第4关：MAX( )函数

MAX()函数基本使用
    MAX()函数是求某列的最大数值

    selectmax(字段名) from 数据表;

  查询语文课程中的最高分数
          select course ,max(score)
          from tb_class
          where course='语文';

  查询英语课程中的最高分数
          select course ,max(score)
          from tb_class
          where course='英语';


第5关：MIN( )函数

MIN()函数基本使用
          求某列的最小数值

    select min(字段名) from 数据表

  查询语文课程中的最低分数
          select course,min(score)
          from tb_class
          where course='语文';

  查询英语课程中的最低分数
          select course,min(score)
          from tb_class
          where course='英语';



七. 数据查询（一）

第1关：按条件查询单表的所有字段

select * from 数据表 where 查询条件

  1.查询街道信息(jdxx)数据表的开福区(qxmc)的所有字段
        select * from jdxx where qxmc="开福区" ;

  2.查询街道信息(jdxx)数据表的开福区和岳麓区(qxmc)的所有字段
          select * from jdxx where qxmc="开福区" or qxmc="岳麓区";

  3.查询街道信息(jdxx)数据表的长沙市(cs)的西湖街道（name)所有字段
          select * from jdxx where name="西湖街道" and cs="长沙市";

第2关：查询唯一值

distinct <字段名>去掉重复的查询结果

  根据jdxx数据表

  查询湖南省(sf)所有的区县名称(qxmc)，每个区县只出现一次
    select distinct qxmc from jdxx where sf="湖南省";

  查询长沙市(cs)所有的区县名称(qxmc)，每个区县只出现一次
    select distinct qxmc from jdxx where cs="长沙市";

第3关：统计查询

统计查询函数:
   SUM 盘算数值列的和
AVG 盘算数值列的平均值
MAX 盘算列（数值、日期、字符）的最大值
MIN 盘算列（数值、日期、字符）的最小值
COUNT 盘算查询结果的数目

  对jdxx统计查询

  查询湖南省的街道个数
  select count (name) from jdxx where sf="湖南省" ;

  查询长沙市的街道个数
  select count (name) from jdxx where cs="长沙市" ;

  查询湖南省的区县个数
  select count (distinct qxmc) from jdxx where sf="湖南省" ;

  查询长沙市的区县个数
  select count (distinct qxmc) from jdxx where cs="长沙市" ;


第4关：分组查询

select 表达式列表 from 数据表 where 条件 group by 分组字段 having 分组条件

  统计每个省的名称和街道数目
  select sf , count(*) from jdxx
  group by sf;

  显示街道数目大于200个的都会和街道数目
  select cs , count(*) from jdxx
  group by cs having count(name)>200;

  显示长沙市每个区县的街道数目
  select qxmc , count(*) from jdxx
  where cs='长沙市'
  group by qxmc;

第5关：数据排序

排序数据

  Order by <字段名1> ASC | DESC, <字段名2> ASC | DESC……

首先按照<字段名1>的顺序排列记录，若多条记录的<字段名1>值相同，则按<字段名2>的顺序排列 ASC升序 DESC降序（默认升序）。
使用limit 位置偏移量 <行数> 指定查询结果的数量，位置偏移量指示从第几行开始显示，0表示从第1行开始，若省略默认为0，<行数>指定返回的记录条数。

  显示 jdtj 数据表中街道个数最多的10个的所有字段
          select * from jdtj
          order by jdgs desc
          limit 10;

  显示 jdtj 数据表中街道个数最少的10个的所有字段
          select * from jdtj
          order by jdgs asc
          limit 10;

  按街道个数从高到底，街道个数相同的按省份升序排序显示 jdtj 数据表中街道个数大于35的所有字段
          select * from jdtj
          where jdgs>35
          order by jdgs desc,sf asc;



八.数据查询（二）

第1关：多表查询

         在毗连查询中引用两个表的公共字段时，必须在字段前添加表名作为前缀，否则系统会提示堕落。
  二表查询:
  select * from 数据表1，数据表2 where 毗连条件
  或
  from 数据表1 join 数据表2 on 毗连条件

  三表查询:
  from 数据表1 ,数据表2 ,数据表3 where 毗连条件1 and 毗连条件2

  根据读者(reader)和借阅(borrow)数据表，查询王颖珊的借阅记录，包括条形码txm、借阅日期jyrq、还书日期hsrq
          select txm , jyrq , hsrq
          from borrow , reader
          where reader.dzzh = borrow.dzzh and xm='王颖珊';

  根据图书(book)和借阅(borrow)数据表，查询李白全集被借阅的情况：包括读者证号dzzh、借阅日期jyrq、还书日期hsrq
          select dzzh , jyrq , hsrq
          from borrow , book
          where borrow.txm = book.txm and book.sm='李白全集';

  根据读者(reader)、图书(book)和借阅(borrow)数据表查询没有被归还的借阅信息：包括读者证号dzzh、姓名xm、电话dhhm、条形码txm、书名sm、借阅日期jyrq
  提示：通过isnull(表达式)可以判断表达式是否NULL值
          select reader.dzzh , xm , dhhm , book.txm , sm , borrow.jyrq
          from reader,book,borrow
          where reader.dzzh=borrow.dzzh and book.txm = borrow.txm and isnull(hsrq);

第2关：多表查询及统计分组

注意：order by <表达式1>,<表达式2> 表示首先按第一个表达式的值排序，第一个表达式的值相同的再按第二个表达式的值排序

  第一题：统计每本书借阅的次数,显示书名和借阅次数（借阅次数定名为jycs),按借阅次数降序排列,借阅次数相同的按书名降序排列（提示：borrow数据表的一条数据对应一次借阅）
          select sm,count(*)jycs
          from borrow left join book on book.txm=borrow.txm
          group by sm
          order by count(*) desc,sm desc;

  第二题：统计借阅次数在2次以上的图书的借阅的次数,显示书名和借阅次数，按借阅次数降序排列,借阅次数相同的按书名降序排列

          select sm,count(*)jycs
          from borrow left join book on book.txm=borrow.txm
          group by sm having(count(*)>=2)
          order by count(*) desc,sm desc;

  第三题统计每个出书社的图书的借阅次数,显示出书社的名称和借阅次数，按借阅次数降序排列,借阅次数相同的按出书社降序排列
          select cbs,count(*)jycs
          from borrow left join book on book.txm=borrow.txm
          group by cbs
          order by count(*) desc,cbs desc;

  第四题：统计每位读者借阅的次数,显示姓名和借阅次数,按借阅次数降序排列，借阅次数相同的按姓名降序排列
          select xm,count(*)jycs
          from borrow left join reader on borrow.dzzh=reader.dzzh
          group by xm
          order by count(*) desc,xm desc;

  第五题：统计研究生读者借阅的次数,显示姓名和借阅次数，按借阅次数降序排列，借阅次数相同的按姓名降序排列
          select xm,count(*)jycs
          from borrow left join reader on borrow.dzzh=reader.dzzh
          where sf='研究生'
          group by xm
          order by count(*) desc,xm desc;


第3关：子查询

         在select语句中，一个查询语句完全嵌套在另一个查询语句的where或having的条件短语中，称为子查询或嵌套查询。通常把条件短语中的查询成为子查询，父查询则使用子查询的查询结果作为查询条件。

  查询与李白全集同一个出书社的图书的书名（不包括李白全集）
          select sm
          from book
          where cbs = (
                  select cbs
                  from book
                  where sm ='李白全集'
                  ) and sm !='李白全集';

  查询高于图书的平均售价(sj)的图书的书名和售价
          select sm, sj
          from book
          where sj > (
                  select avg(sj)
                  from book);

  查询售价最高的图书的条形码、书名和售价
          select txm, sm, sj
          from book
          where sj = (
                  select max(sj)
                  from book);

  查询售价最低的图书的条形码、书名和售价
          select txm, sm, sj
          from book
          where sj = (
                  select min(sj)
                  from book);


第4关：多表子查询

查询曾经借过图书的读者的读者证号和姓名
  select distinct reader.dzzh,xm
  from reader,borrow
  where reader.dzzh = borrow.dzzh
  order by reader.dzzh asc;

  查询曾经没有被借阅的图书的条形码和书名
  select book.txm, sm
  from book left join borrow on book.txm = borrow.txm
  where jyrq is null;

  查询与孙思旺借过相同图书的读者的读者证号和姓名，按读者证号升序排列
  select distinct reader.dzzh, xm
  from reader,borrow
  where reader.dzzh = borrow.dzzh and txm in(
          select borrow.txm
          from reader,borrow,book
          where reader.dzzh = borrow.dzzh and borrow.txm = book.txm and xm = '孙思旺'
          ) and xm !='孙思旺'
  order by reader.dzzh asc;

  查询借阅过李白全集的读者所借过的其他图书的书名按书名升序排列
   select distinct sm
   from borrow,book
   where borrow.txm = book.txm and dzzh in(
          select dzzh
          from borrow,book
          where borrow.txm = book.txm and sm ='李白全集'
          )and sm !='李白全集'
  order by sm asc;



九. MySQL数据库 - 毗连查询

第1关：内毗连查询

内毗连查询

仅将两个表中满意毗连条件的行组合起来作为结果集，称为内毗连；
关键字：[inner] join ... on

   表1[inner] join 表2on 表1.字段=表2.字段

          从表1中取出每一条记录，去表2中与所有的记录进行匹配，匹配必须是某个条件在表1中与表2中相同，最终才会保留结果，否则不保留。inner 关键字可省略不写；on 表示毗连条件：条件字段就是代表相同的业务含义，大多数情况下为两张表中的主外键关系

  查询数据表tb_student，tb_class中学生姓名（studentName）和对应的班级名称（className）
          select tb_student.name as studentName , tb_class.name as className
          from tb_student join tb_class on tb_class.id = tb_student.class_id ;


第2关：外毗连查询

外毗连查询

以某张表为主，取出里面的所有记录，然后每条与别的一张表进行毗连，不管能不能匹配上条件，最终都会保留。能匹配，精确保留；不能匹配，别的表的字段都置空（null），称为外毗连。
外毗连查询分为左外毗连查询和右外毗连查询
关键字：left/right [outer] join ... on

    表1 left/right [outer] join 表2 on 表1.字段=表2.字段

左外毗连：在内毗连的基础上，还包含表1中所有不符合条件的数据行，并在其中的表2列填写 NULL；
右外毗连：在内毗连的基础上，还包含表2中所有不符合条件的数据行，并在其中的表1列填写 NULL。

  使用左外毗连查询所有学生姓名和对应的班级

          select  tb_student.name as studentName , tb_class.name as className
          from  tb_class right join tb_student on tb_class.id=tb_student.class_id;

  使用右外毗连查询所有学生姓名和对应的班级

          select  tb_student.name as studentName , tb_class.name as className
          from  tb_class left join tb_student on tb_class.id=tb_student.class_id;


第3关：复合条件毗连查询

复合条件毗连查询
          复合条件毗连查询，就是在毗连查询的过程中，通过添加过滤条件来限定查询结果，使查询结果更加精确。

  查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级

          select s1.name as studentName,score,s2.name as className
          from tb_student as s1 ,tb_class as s2
          where s1.class_id=s2.id and s1.score>90 order by score desc;


十. MySQL数据库 - 子查询

第1关：带比力运算符的子查询

子查询
  嵌套在查询内部，且必须始终出现在圆括号内

  分为四类：

标量子查询：返回单一值的标量，最简朴的情势；
列子查询：返回的结果集是 N 行一列；
行子查询：返回的结果集是一行 N 列；
表子查询：返回的结果集是 N 行 N 列。

    eg: SELECT * FROM t1 WHERE col1=(SELECT col2 FROM t2);

  带比力运算符的子查询

带有比力运算符的子查询是指父查询和子查询使用比力运算符毗连的嵌套查询
使用场景：当用户能够确切的知道内层查询返回的是单个值时,可以使用比力运算符

  运算符阐明>大于>=大于或等于=等于!= 或 <>不等于<小于<=小于或等于
  查询大于所有平均年事的员工姓名与年事

          select name,age
          from tb_emp
          where age > (
                  select avg(age)
                  from tb_emp ) ;

第2关：关键字子查询

ALL关键字
    ALL必须接在一个比力运算符的后面，表示与子查询返回的所有值比力都为 TRUE则返回TRUE。
          eg:  SELECT col1 FROM table1 WHERE col1 > ALL (SELECT col2 FROM table2)

  ANY和SOME关键字
    ANY与比力操纵符联合使用，表示与子查询返回的任何值比力为TRUE ，则返回TRUE。 SOME是ANY的别名，一般用的比力少。

  IN关键字

IN的意思就是指定的一个值是否在这个聚集中，假如在就返回TRUE；否则就返回FALSE了，同IN(项1，项2，...)
IN 是 = ANY 的别名，二者相同，但 NOT IN 的别名却不是 <> ANY 而是 <> SOME

  使用 ALL 关键字进行查询

    select position,salary
    from tb_salary
    where salary > ALL (
          select salary
          from tb_salary
          where position="java");

  使用 ANY 关键字进行查询

    select position,salary
    from tb_salary
    where salary > ANY (
    select salary
    from tb_salary
     where position="java");

  使用 IN 关键字进行查询

    select position,salary
    from tb_salary
    where salary IN (
    select salary
    from tb_salary
    where position="java");



十一. MySQL数据库 - 分组选择数据

第1关：GROUP BY 与聚合函数

GROUP BY与聚合函数的使用

  select [聚合函数] 字段名 from 表名 [where 查询条件]　[group by 字段名]

          注: select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。

          查询表中2,3,4年级中分别男女的总人数

    select gradeId,sex,count(*)
    from student
    where gradeId in(2,3,4)
    group by gradeId,sex;

第2关：使用 HAVING 与 ORDER BY

使用having子句进行分组筛选

    having子句用来对分组后的数据进行筛选,常与Group by连用

  select [聚合函数] 字段名 from 表名 [where 查询条件] [group by 字段名] [having 字段名筛选条件]


    select语句中，where、group by、having子句和聚合函数的执行次序如下：

where子句从数据源中去除不符合条件的数据；
然后group by子句搜集数据行到各个组中；
接着统计函数为各个组盘算统计值；
最后having子句去掉不符合其组搜索条件的各组数据行。

  Having与Where的区别

    where子句都可以用having代替，区别在于where过滤行，having过滤分组

where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，where条件中不能包含聚组函数，使用where条件过滤出特定的行
having子句筛选满意条件的组，在分组之后过滤数据，条件中经常包含聚组函数，使用having条件过滤出特定的组，也可以使用多个分组标准进行分组

  Group By 和 Order By


    select [聚合函数] 字段名 from 表名 [where 查询条件]　[group by 字段名] [order by 字段名排序方向]

  查询表中至少有两门课程在90分以上的学生信息
    select sno,count(*)
    from tb_grade
    where score>=90
    group by sno having count(*)>=2;

  查询表中平均成绩大于90分且语文课在95分以上的学生信息
    select sno,avg(score)
    from tb_grade
    where sno in (
    select sno
    from tb_grade
    where score>=95 and pno='语文')
    group by sno having avg(score)>=90;


十二. MySQL数据库 - 数据库和表的基本操纵（一）

第1关：检察表结构与修改表名

检察数据表基本结构
  describe表名；

  检察数据表详细结构
  show create table表名;

  修改表名
  alter table旧表名 rename新表名;

把数据表tb_emp改名为jd_emp；

    ALTER TABLE tb_emp RENAME jd_emp;

检察该数据库下数据表的列表；

    SHOW TABLES;

检察数据表jd_emp的基本结构。

    DESCRIBE jd_emp;

第2关：修改字段名与字段数据类型

修改字段名

    alter table表名 change旧字段名新字段名新数据类型;

          提示：假如不需要修改字段的数据类型，可以把新字段的数据类型设置为和原来一样，不要空着它

  修改字段数据类型

    alter table表名 modify字段名数据类型;

把数据表tb_emp的字段Id改名为prod_id，数据类型稳定

    alter table tb_emp change Id prod_id int(11);

把数据表tb_emp字段Name的数据类型改为varchar(30)

    alter table tb_emp modify Name varchar(30);


第3关：添加与删除字段

添加字段

    ALTER TABLE 表名 ADD 新字段名数据类型 [束缚条件] [FIRST|AFTER] 已存在字段名;

          在表的最后一列添加字段

    不做[FIRST|AFTER]的位置阐明，在添加字段时MySQL会默认把新字段加入到表的最后一列。

    eg: 字段prod_country添加到表Mall_products的最后一列

    alter table Mall_productsadd prod_countryvarchar(30);

          在表的第一列添加字段

    做FIRST的位置阐明

          eg:  字段prod_country添加到表Mall_products的第一列

          alter table Mall_products add prod_country varchar(30) frist;

          在表的指定列后添加字段

    做AFTER的位置阐明，注明添加在哪个字段后面

    eg:prod_country添加到表Mall_products的 prod_name字段的后面。

    alter table Mall_products add prod_country varchar(30) after prod_name;

  删除字段

          alter table表名 drop字段名;

          eg: 字段prod_price从表Mall_products中删除

                 alter table Mall_productsdrop prod_price;

在数据表tb_emp的Name字段后添加字段Country，数据格式为varchar(20)

    alter table tb_emp add Country varchar(20) after Name;

删除数据表tb_emp中的字段Salary

    alter table tb_emp drop Salary;


第4关：修改字段的排列位置

修改字段的排列位置

          ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST|AFTER 字段2;

    字段1指要修改位置的字段，FIRST与AFTER 字段2为可选参数


          修改字段为表的第一个字段

          做FIRST的位置阐明

    eg: 把字段prod_price调整到表Mall_products的第一列

                  alter table Mall_products modify prod_price float first;

          修改字段到表的指定列之后

          做AFTER 字段2的位置阐明

    eg: 字段prod_price调整到字段prod_country的后面

                  alter table Mall_products modify prod_price float after prod_country;

将数据表tb_emp的Name字段移至第一列，数据格式稳定

         alter table tb_emp modify Name varchar(25) first;

将DeptId字段移至Salary字段的后边，数据格式稳定

          alter table tb_emp modify DeptId INT(11) after Salary;


第5关：删除表的外键束缚

删除表的外键束缚

  ALTER TABLE 表名 DROP FOREIGN KEY 外键束缚名;

          删除数据表tb_emp的外键束缚emp_dept

          alter table tb_emp drop foreign key emp_dept;



十三. MySQL数据库 - 数据库和表的基本操纵（二）

第1关：插入数据

为表的所有字段插入数据

          INSERT INTO 表名 (字段名) VALUES (内容);

    insert into MyUser(name,age) values('zhnagsan',18);

  为表的指定字段插入数据

    insert into MyUser(name) values('lisi'),('fawaikuangtu'),('zhangsan');

为空数据表tb_emp同时添加3条数据内容

          insert into tb_emp (Id,Name,Deptid,Salary)
          values (1,'Nancy',301,2300.00),(2,'Tod',303,5600.00),(3,'Carly',301,3200.00);


第2关：更新数据

更新表中指定的内容

  UPDATE 表名 SET 字段名1 = 内容1, 字段名2 = 内容2, 字段名3 = 内容3 WHERE 过滤条件;

    eg: 表Mall_products2 中 Span 换成 Pakistan,地区代码换为 92 。
    UPDATE Mall_products2
    SET country_name = "akistan", country_id = 92
    WHERE id = 2;

数据表tb_emp 中 Carly改为Tracy，相应的，301改为302，3200.00改为4300.00

          update tb_emp
          set Name = "Tracy", Deptid = 302, Salary = 4300.00
          where Id=3;

第3关：删除数据

删除表中的指定行

          DELETE FROM 表名 WHERE 条件语句;

          eg:  表Mall_products2同时删除包含Span和Italy的两行数据内容

    delete from Mall_products2
    where id=2 or id=3;


  删除表中的所有行

          DELETE FROM 表名;

          eg: 同时删除表Mall_products2的所有数据内容

                  delete from Mall_products2;

    提示： TRUNCATE TABLE 语句也可以用来删除表中的所有记录。但是与 DELETE 不同的是，TRUNCATE TABLE 语句直接删除的是表，而不是表中的内容，删除竣事后还会重新创建一个表。所以它的执行速度会比 DELETE 语句快。语法为：truncate table表名;

将数据表tb_emp中Salary大于3000的数据行删除。

                  delete from tb_emp where Salary>3000;



十四. MySQL开发技巧 - 视图

视图的定义

          视图（view）是一种虚拟存在的表，是一个逻辑表，自己并不包含数据。通过视图，可以展现基表（用来创建视图的表）的部分数据；视图数据来自定义视图的查询表，使用视图的大部分情况是为了保障数据安全性，进步查询服从。
          优点：
          ①. 简朴：使用视图的用户完全不需要关心后面临应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集
          ②. 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限定到某个行或列，但是通过视图就可以简朴的实现
          ③. 数据独立：一旦视图的结构确定了，可以屏蔽表结构变革对用户的影响，源表增加列队视图没有影响；源表修改列名，则可以通过修改视图来办理，不会造成对访问者的影响

创建视图

    CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

          参数阐明：

OR REPLACE：表示替换已有视图；
ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)： MySQL 自动选择要使用的算法；merge合并；temptable临时表；
column_list：可选参数，指定视图中各个属性的名词，默认情况下与select语句中查询的属性相同；
select_statement：表示select语句；
[WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内；cascade是默认值，表示更新视图的时候，要满意视图和表的相干条件；local表示更新视图的时候，要满意该视图定义的一个条件即可。

操纵视图

视图是逻辑表，也就是说视图不是真实的表，但操纵视图和操纵平凡表的语法是一样的。用户可以在视图中无条件地使用select语句查询数据。但使用insert、update和delete操纵需要在创建视图时满意以下条件（满意以下条件的视图称为可更新视图）：

from子句中只能引用有1个表（真实表或可更新视图）；
不能包含 with、distinct、group by、having、limit等子句；
不能使用复合查询，即不能使用union、intersect、except等聚集操纵；
select子句的字段列表不能包含聚合、窗口函数、聚集返回函数。

删除视图

          若视图不再被需要，我们可以将其删除，且视图的删除并不影响源表中的数据

    DROP VIEW view_name;

在student表上创建视图stu_view，查询math、chinese字段并定义一个math+chinese成绩之和字段，值为两者之和；

                  CREATE VIEW stu_view
                  AS select math , chinese , math+chinese
                  FROM student;

在student表和stu_info表上，创建stu_classes视图，查询出stu_id、姓名和班级，查询条件为两表中的stu_id字段相同。

                  CREATE VIEW stu_classes
                  AS  select student.stu_id , student.name , stu_info.classes
                  FROM student , stu_info
                  WHERE student . stu_id = stu_info . stu_id;


十五. MySQL开发技巧 - 分页和索引

第1关：MySQL 分页查询

limit 和 offset 实现的分页查询

eg: 检索记录行符合条件的10条数据
        select * from table where xxx="xxx" limit 10;

    eg: 检索记录行符合条件的11-20条数据
          select * from table where xxx="xxx" limit 10 offset 10;
          或

          select * from table where xxx="xxx" limit 10,10;

    提示：limit之后的数字代表偏移量，offset代表返回记录的最大值，可以直接把offset直接省略掉

  大数据量下分页查询优化

          select * from table where xxx="xxx" limit 100,100;
          select * from table where xxx="xxx" limit 1000,100;
          select * from table where xxx="xxx" limit 10000,100;
          select * from table where xxx="xxx" limit 100000,100;
          select * from table where xxx="xxx" limit 1000000,100;

          优化

          直接定位到偏移量所在记录，先查询到偏移量位置，再进行分页：
          select *
          from table
          where xxx="xxx" and id>=(
                  select id
                  from table
                  where xxx="xxx"limit 100000,1)
          limit 100;

按prod_id升序查询表products中第6-10行数据,只要求prod_id字段即可（prod_id从1开始）；

          select prod_id
          from products
          limit 5,5;

利用子查询优化查询出按prod_id升序的第10-15行数据，只要求prod_id字段即可。

          select prod_id
          from products
          where prod_id>=(
                  select prod_id
                  from products
                  limit 10,1)
          limit 5;

第2关：索引（单列索引）

索引是一张特殊的表，该表生存了主键与索引字段，并指向实体表的记录。

  单列索引分类和创建

          1.主键索引
                  主键索引一般在建表时创建，不允许有空值并且值唯一，一般会设为 int 而且是 AUTO_INCREMENT 自增类型的

                  create table `student` (
                  `ID` int(11) not null auto_increment,
                  primary key(`ID`)
                  ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

          2.平凡索引

    create index name_indexon `student`(`name`);
    或
    alter table student add index name_index(`name`);
                  注：这里的 ` 不是单引号，而是键盘 1 数字左边的符

          3.唯一索引
                  和主键索引类似，要求字段唯一，但是可以允许字段为空

    create unique index name_index on `student`(`name`);
    唯一索引可以用来对数据进行强制性要求，可以禁止某表的某个字段出现重复数据

创建student表结构并且设置id为主键索引

                  create table student (
                    id int(11) not null auto_increment,
                    primary key (`id`),
                    name varchar(20) not null,
                    score int(10)
                  );

对name建立唯一索引 (索引名name_index)

                  create unique indexname_index on`student`(`name`);

对score建立平凡索引 (索引名score_index)

                  create index score_index on `student`(`score`);
                  SHOW INDEX FROM student;


第3关：索引（组合索引）

创建组合索引

    eg: 创建三个索引：name,name city,name city score

    alter tablestudent add index name_city_score (name,city,score);

在表person 中的 name、age、address字段上创建组合索引，索引名为name_city_score

          alter table personadd index name_city_score ( name, age, address );



十六. MySQL数据库 - 其他函数的使用

第1关：字符函数

字符串的常用函数：
  函数作用CONCAT()字符串的拼接CONCAT_WS()使用指定的字符将目的字符串进行拼接FORMAT()数字格式化LOWER()字符串全部转换为小写UPPER()字符串全部转换为大写LEFT()获取左侧的字符串RIGHT()获取右侧的字符串LENGTH()盘算字符串的长度LTRIM()删除前导的空格RTRIM()删除后续的空格TRIM()删除前导和后续的空格SUBSTRING()字符串截取SUBSTRING_INDEX()分隔字符串REPLACE()字符串替换[NOT] LIKE模式匹配
  1. CONCAT()与CONCAT_WS()
  将字符进行毗连

concat()

concat_ws()

                  使用指定字符进行毗连，最少需要三个参数，第一个参数为指定毗连符：

  2. left()与right()

          左右侧的字符获取，需要两个参数，第一个参数是从哪个字符串中获取，第二个参数是获取的长度

  3. LTRIM()、RTRIM() 与 TRIM()
          去掉字符串中含有的空格

ltrim()

          去掉其中字符前导的空格

rtrim()

          去掉其中字符后导的空格

trim()

          去掉其中字符全部的空格，删除字符串中自定字符

                  删除前导 ? : leading
                  删除后导的 ? : trailing
                  删除全部 ? ：both

  4.SUBSTRING()、SUBSTRING_INDEX()与REPLACE()

substring()

          用于字符串的截取

                          从第一位（注意第一位为1而非0）开始截取MySQL的二位字符

substring_index()

          通过特殊符号将字符串分隔

                          将字符串www.educoder.net通过.进行分隔，可从前后取字符串

replace()

          替换字符串中间自定字符

          以首字母大写，其他字母小写的方式显示所有员工的姓名。

    select concat (upper ( left ( Name, 1 ) ) , lower ( substr ( Name from 2 ) ) )
    as 'Name'
    from employee;


第2关：数学函数

数值运算符

    常与数值结合使用的函数：
  函数阐明CEIL()进一（向上）取整FLOOR()舍一（向下）取整POWER()幂运算SQRT()平方根运算ROUND()四舍五入ABS()绝对值盘算RAND()生产0-1的随机数TRUNCATE()数字截取DIV整数除法MOD取余数(取模)  1. CEIL()和FLOOR()

CEIL()函数 : 向上取整
FLOOR(): 向下取整

  2. DIV和MOD

div 整数除法函数

mod 取模函数

  3. ROUND()、POWER()、SQRT()和TRUNCATE()

ROUND()函数：四舍五入

POWER()函数：幂运算

2^3=8

SQRT()函数：平方根运算

TRUNCATE()函数：数字截取，只做截取，不发生四舍五入，下面示例为：

  比力运算符
函数
阐明[NOT] BETWEEN...AND...[不]在范围内[NOT] IN()[不]在列出值范围内IS [NOT] NULL[不]为空

BETWEEN...AND...：指定一个范围，只要选择的数字在这个范围内则为1否则为0

IN()：指定列出值，只要选择的数字存在其中则为1否则为0

IS NULL：判断字段是否为空

                  根据下面表达式修改学生的最终成绩，结果使用四舍五入保留两位小数：

  update Score set s_score=TRUNCATE(s_score - ROUND(SQRT((POWER(4,4)-POWER(3,3))/POWER(2,2)),2),2);


第3关：日期时间函数和流程控制类函数

日期函数

    常用的函数：
  函数阐明NOW()当前日期和时间CURDATE()当前日期CURTIME()当前时间DATE_ADD()日期变革DATEDIFF()日期差值DATE_FORMAT()日期格式化YEAR()返回日期的年份MONTH()返回日期的月份WEEK()返回日期的周号

  1. NOW()、CURDATE()和CURTIME()

now():返回当前日期和时间

curdate(): 单独返回当前日期
curtime(): 返回当前时间

  2. DATE_ADD()、DATEDIFF()和DATE_FORMAT()

date_add(): 日期的变革，第一个参数为指定时间，第二个参数为增加（淘汰）的单位值（YEAR、MONTHS、WEEK、DAY）

datediff(): 返回二个日期之间的差值

DATE_FORMAT(): 日期格式化, 日期格式转换成另一种格式

    以下是在format字符串中常用的阐明符:
  格式形貌%d月的天，数值（00-31）%H小时（00-23）%h小时（01-12）%m月，数值（00-12）%pAM 或 PM%s秒（00-59）%T时间，24-小时（hh:mm:ss）%Y年，4 位

  3. YEAR()和MONTH()

  流程控制类函数
  函数阐明IF()判断，流程控制IFNULL()字段值是否为空CASE搜索语句

IF()函数：IF(a,b,c) 假如a为真返回b，否则返回c

IFNULL()函数：IFNULL(a,b) 假如a不为null返回a，否则返回b

CASE：CASE WHEN a then b end 判断假如a为真返回b。

          查询所有学生的出生年份及至2019-01-01时的年事

                  select year(s_birth) year,'2019-01-01'-s_birth '年事'from Student;

          查询所有课程中的最高分、最低分、平均分和及格率，按课程id升序输出

                  select c.c_id 课程id,
                  c_name 课程名,
                  max(s_score) 最高分,
                  min(s_score) '最低分',
                  round(avg(s_score),2) '平均分',
                  round((count(s_score>=60 or null)/count(s_score))*100,2) '及格率'
                  from Score s,Course c
                  where s.c_id=c.c_id
                  group by s.c_id;


十七. 数据库E-R图

编程要求
  绘制六个模块的 E-R 图，分别为课程、选课、学生、班级、学院、教师。
  课程包含以下字段名：课程编号、课程名称、学时、学分、人数上限、上课时间、上课教室、开课学期、开始选课时间、选课竣事时间、课程编号。
  选课包含以下字段名：选课时间、成绩。
  学生包含以下字段名：学号、姓名、密码、性别、出生年代、电话、家庭住址、学生简介。
  班级包含以下字段名：班级编号、班级名称、年度。
  学院包含以下字段名：学院编号、学院名称。
  教师包含以下字段名：教师编号、教师姓名、密码、性别、专业、职称、教师简介。
  各个模块之间的关系如下所示：
  课程与学生之间的关系为选课：学生与课程的关系为多对多。
  学生与班级的关系为所在：学生与班级的关系为多对 1。
  学生与学院的关系为所属：学生与学院的关系为多对 1。
  班级与学院直接的关系为所属：班级与学院的关系为多对 1。
  教师与学院直接的关系为所属：教师与学院的关系为多对 1。
  教师与课程的关系为负责：教师与课程的关系为 1 对多。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)