您的位置:新葡亰496net > 网络数据库 > JavaWeb前端笔记,多表的询问

JavaWeb前端笔记,多表的询问

发布时间:2019-08-02 13:14编辑:网络数据库浏览(55)

    SQL server基础知识

    数据库的概述

    数据库操作

    day06

      回顾:

       bootstrap:

          css框架,html/css/js集于一身,ie 6/7/8兼容有问题

          开发响应式页面,使用于不同的上网设备

          使用步骤:

            1.导入bootstrap.css

            2.导入jquery.js

            3.导入boostrap.js

            4.bixu设置一个meat标签

            5.必须将内容放入一个布局容器

              .container或者。container-fluid

            前提:

              一行分为12分

            栅格系统:

              分辨率>1200px             使用col-lg-n样式

              1200px>分辨率>992px      使用col-md-n样式

              992px>分辨率>768px           使用col-sm-n样式

              768px>分辨率          使用col-xs-n样式

            隐藏:

              hidden-xs|sm|md|lg        在什么屏幕下隐藏

            组成:

              css样式

              组件  导航条  下拉菜单

              js插件  轮播图   选项卡

    ////////////////////////////////////////////

            validate插件:校验表单

              jquery 的插件

              使用步骤:

                1.导入JQUERY.JS

                2.导入validate.js

                3.在页面加载成功之后,对表单进行校验

                 $(function(){
                   $("选择器").validate();

                 })

                4.在validate方法中编写校验规则

                $(function(){

                  $("选择器").validate({

                     rules:{

                        //格式1: name值:“校验器”

                        //格式2:name值:{校验值:值,校验器:值}

                     },

                     messages:{

                        //格式1: name值:“提示信息”

                        //格式2:name值:{校验值:"提示信息"校验器:"提示信息"}

                     }

                   });

                 })             

                常用的校验器:  

                  required:必须的

                  min:最小值 minlength:最小长度 range:取值区间  rengelength:长度区间

                  equalTo:jquery表达式 判断是否相等

     //////////////////////////////////  

    数据库:

      本质上就是一个文件系统.通过标准的sql对数据进行curd操作

    安装  数据库管理系统:

      数据库管理系统就是一个额软件

    常见的关系型数据库:

      存放实体与实体之间的关系的数据库(二维表)

      实体:

        用户  订单  商品

      关系:

        用户拥有订单

        订单包含商品

      非关系型数据库:存放的是对象(redis)no-sql(not only sql)

      软件名    厂商       特点

      mysql    oracle        开源的数据库 

      oracle    oracle       大型的收费的数据库

      DB2     IBM       大型的收费的数据库

      sqlserver   微软      中大型的收费的数据库  

      sybase    sybase(powerdesigner)

    安装了数据库管理系统的计算机称之为数据库服务器

      服务器:给别人提供服务(软件服务器)

    我们可以通过标准的sql在服务器创建数据库(database)

    有了数据库之后,就在数据库上创表

    有了表之后,就可以往里面存放数据了

    /////////////////////

    SQL:

      结构化查询语句

      作用:

        管理数据库

    sql的分类:

      DDL:数据定义语言

        操作对象:数据库和表

        关键词:create alter drop

      DML:数据操作语言

        操作对象:记录,数据

      DQL:数据查询语言(非官方)   

      DCL:数据控制语言

        操作对象:用户 事物 权限

    ////////////////////////

      DDL:数据定义语言

        操作对象:数据库和表

        关键词:create alter drop

        操作数据库:

          创建:

            格式:

                create database 数据库名;

          删除:

            格式:

                drop database 数据库名称;

          常用的命令:

            查看所有的数据库 show databases;

          操作表:

             创建表:

                格式:

                    create table 表名(

                    字段名 字段类型 [约束],

                    ......

                    );

                 例如:

                   create table user(

                     id int primary key auto_increment,

                     username varchar(20)

                  );

               修改表

                格式:

                  alter table 表名 。。。

                 修改表名:

                   alter table 旧表名 rename to 新表名;

                 添加字段:

                  alter table 表名 add [column] 字段描述;

                  eg:alter table user add password varchar(20);

                 修改字段名:

                  alter table 表名 change 字段名称 新字段描述;

                  eg:alter table user change password pwd varchar(20);

                 修改字段描述:

                  alter table 表名 modify 字段名称 字段类型 [约束];

                  eg:alter table user modify pwd int;

                 删除字段:

                  alter table 表名 drop 字段名;

                  eg:alter table user drop pwd;

            常用命令:

              切换或者进入数据库:use 数据库名称;

              查看当前数据库下的所有表;show table;

              查看表结构:desc 表名;

              查看建表语句:show create table 表名;

    //////////////////////////////////////////////////

    DML:数据操作语言

      操作对象:记录(数据行)

      关键词:insert update delete

      插入:

        格式:  

         insert into 表名 values(字段值1,字段值2...);

         注意:

          默认插入全部字段

          必须保证values后面的内容的类型和顺序和表结构中的一致

          若字段类型为数字,可以省略引号

         eg:insert into user values(1,'tom');

         格式2:

          insert into 表名(字段名1,字段名2.。。。) values (字段值1,字段值2...);

         修改:

          格式:

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

           例如:

            update user set username='jerry' where username ='jack';

          删除:  

            格式:   

              delete from 表名 [where 条件];

           eg:delete from user where id = '2'; 

    //////////////////////////////////

    DQL:数据查询语言

      关键词主要是:select

      格式:

        select ...... from 表名 where 条件 group by 分组字段 having 条件 order by 排序字段 ase|desc

      通配符:*

    初始化环境:
      -- 创建商品表
      create table products(
      pid int primary key auto_increment,
      pname varchar(20),
      price double,
      pnum int,
      cno int,
      pdate timestamp
    );

    insert into products values (null,'泰国大榴莲',98,12,1,null);
    insert into products values (null,'新疆大枣',38,123,1,null);
    insert into products values (null,'新疆切糕',68,50,2,null);
    insert into products values (null,'十三香',10,200,3,null);
    insert into products values (null,'老干妈',20,180,3,null);
    insert into products values (null,'豌豆黄',20,120,2,null);

      eg:

      1.查询所有商品

       select * from products;

      2.查询商品名和商品价格

       select pname,price from products;

      3.查询所有商品都有那些价格.
        去重操作 distinct
         格式:

        select distinct 字段名,字段名2 from 表名
        select price from products;
        select distinct price from products;
      4.将所有商品的价格 10元进行显示.(别名)
             可以在查询的结果之上进行运算,不影响数据库中的值
          给列起别名 格式:

        字段名 [as] 别名
          select price 10 from products;
          select price 10 新价格 from products;
          select price 10 '新价格' from products;
          select price 10 新 价 格 from products;-- 错误
          select price 10 '新 价 格' from products;
          select price 10 `新 价 格` from products;

    //////////////////////////////////

    数据类型

    java              mysql

    byte              tinyint

    short              smallint

    int               int(★)

    long              bigint

    char|String           varchar(★)|char

                    varchar:可变长度 mysql的方言

    boolean              tinyinty|int 代替

    float|double            float|double

                     注意:

                     double(5,2):该小数长度为五,小数占2个最大值:999.99  

     

    Date-java.sql.Date         date存放日期

    java.sql.Time                                         time 时间

    java.sql.Timestamp                               timestamp时间戳 若给定值为null,数据库会把当前的系统时间存放到数据库中

     

    java.sql.Clob(存放字符长文本——小说)   mysql方言(text-4个G内容)

    java.sql.Blob(二进制——放电影)    blob

    /////////////////////////////////////////

    约束

      作用:

        为了保证数据的有效性和完整性

      mysql中常用的约束:

        主键约束(primary key)

        唯一约束(unique)

        非空约束(not null)

        外键约束(foreign key)

       主键约束:被修饰过的字段唯一非空

          注意:一张表只能有一个主键,这个主键可以包含多个字段  

          方式1:创建表的同时添加约束  格式:字段名称 字段类型 primary key

          方式2:建表的同时在约束区域添加约束  格式:primary key

              所有的字段声明完成之后,就是i约束区域了

             格式:primary key(字段1,字段2)

             eg :create table pk01(

              id int,

              username varchar(20),

              primary key(id)

              );

           方式3:建表之后,通过修改表结果后添加约束

           

             eg :create table pk02(

              id int,

              username varchar(20),

              );

              alter table pk02 add primary key(字段名1,字段名2);

              alter table pk02 add primary key(id,username);

              

              insert into pk02 values(1,'tom');--成功

              insert into pk02 values(1,'tomcat');--成功

              insert into pk02 values(1,'tomcat');--失败--插入完全一样的

      唯一约束:

        被修改过的字段唯一,对null不起作用   

          方式1:创建表的同时添加约束  格式:字段名称 字段类型 unique

         

          方式2:建表的同时在约束区域添加约束  格式:unique

              unique(字段1,字段2....)     

           方式3:建表之后,通过修改表结构

             alter table 表名 add unique(字段1,字段2);--添加的联合唯一

       非空约束(not null)

         特点:被修饰过的字段非空

    ////////////////////////////

    truncate 清空表

        格式:

          truncate 表名;干掉表,重新创建一张空表

        和delete from 区别

          delete属于DML语句 truncate属于DDl语句

          delete逐条删除  truncate干掉表,重新创建一张空表

    auto_increment 自增

        要求:

          1.被修饰的字段类型支持自增-int

          2.被修饰的字段必须是一个key 一般是primary key

     

        create table ai01(

        id varchar(10) auro_increment

     );--错误 Incorrect column specifier for column 'id'

     外键约束:

      为了保证数据的有效性和完整性,添加约束(外键约束)

      在多表的一方添加外键约束

        格式:

          alter table 多表名字 add foreign key (外键名称) references 一表名称(主键)

         例如

           alter table orders add foreign key (user_id)  references user(id);

        添加了外键约束后有如下特点:★

          1.主表中不能删除从表中已引用的数据

          2.从表中不能添加主表中不存在的数据

     

        开发中处理一对多:★

          在多表中添加一个外键,名称一般为主表的名称_id,字段类型一般和主表的逐渐类型保持一致

          为了保证数据的有效性和完整性,在主表的外键上添加外键约束即可

    //////////////////

    多表查询:  

        内连接:

          显示的内连接

            select a.*,b.*  from a join b on 连接条件

          隐式外连接

            select a.*,b.* from a,b where 连接条件

         外连接:

           左外连接:(显示内连接加left)

            select a.*,b.* from a left join b on 连接条件

            展示a表所有数据,根据条件关联查询b表,满足条件展示,不满足以null展示

        给表起别名:

        表 【as】 别名

    一、基础知识

    1.数据库的作用:仓库,存储数据。 

    下面介绍一些关于从数据库创建到使用的一些简单的方法;

      (1)、存储结构:数据库->表->数据

    2.关系型的数据库,保存实体与实体之间的关系。 

    创建一个名字为mydatabase数据库: create database mydatabase ;

      (2)、管理数据库

    3.常见的数据库 

    可以用以下地命令来查看创建的数据库是否成功: show databases ;

        增加:create database 数据库名称

    * Oracle MySQL SQLServer DB2 

    更改数据库名字 : alter databases  Hdatabase ;

        删除:drop database 数据库名称

    MySQL数据库的安装和卸载

    更改数据库mydatabase的字符集 : alter database mydatabase charset GBK ;

        查询:select name from master..sysdatabases

    1.看笔记 

    进入数据库: use mydatabase ;

        修改:alter database 数据库名称(在 sql server 中修改与数据库关联的文件和文件组。在数据库中添加或删除文件和文件组、更改数据库或其文件和文件组的属性)

    2.MySQL密码重置。文档 

    用下面的命令来查看该数据库中的表: show tables ;

      (3)、管理表

    MySQL数据库的概念

                                    表操作

        选择数据库:use 数据库

    1.数据库的服务器 – 多个数据库 – 每一个数据库中存在的多个表结构 – 创建表结构的时候需要指定字段 – JavaBean中的属性是吻合的 – 表结构中的一行数据对应的是一个JavaBean实体 

    用下面的命令来创建表:create table student (

        增加:create table 表名(字段1 字段类型,字段2 字段类型,......);

    SQL语言的分类

                                          name varchar(10) , 

        删除:drop table 表名

    1.SQL语言的概述 

                                         gendervarchar(10) ,

        修改:

    2.非过程性的语言:不依赖上一条或者上几条语句,一条语句就会有执行的结果。 

                                         sno    int    primary key(id)

          添加字段:alter table 表名 add 字段名 字段类型

    3.SQL的分类: 

                                        )charset utf8;

          删除字段:alter table 表名 drop column 字段名

    * DDL 数据定义语言 create alter drop 

    用下面的命令来检查表的创建是否成功: show tables;

          修改字段类型:alter table 表名 alter column 字段名 字段类型

    * DML 数据操作语言 delete insert update 

    查看该表结构,表中字段的信息: desc 表名 ;        describe 表名;       show columns from  表名;

          修改字段名称:exec sp_rename '表.原字段名称','新字段名称','column'

    * DCL 数据控制语言 

    修改表名: rename命令用于修改表名。  rename命令格式:rename table 原表名 to 新表名;

          修改表的名称:exec sp_rename '原表名称','新表名称'

    * DQL 数据查询语言 select 

                       alter  table  旧表名  rename  新表名;         alter table student  rename  my_student;

        查询:select name from sysobjects where xtype = 'u'

    数据库的CURD

    修改字段,字段操作很多:新增(add)、修改(modify)、重名(change)、删除(drop)

      (4)、管理数据

    1.创建数据库 create database 名称; 

    --给学生表增加ID放到第一个位置。                          altertable  表名  add  字段名  数据类型[位置];

        增加数据:insert into 表(字段1,字段2,...) values (值1,值2,...)

    2.修改数据库 alter database 名称 character set ‘utf8’ collate ”; 

    alter  table my_student add  id int first;

        删除数据:delete from 表名 where 条件

    3.删除数据库 drop database 名称; 

    ---将学生表中的number学号字段变成固定长度,且放到第二位。   alter table 表名  modify  字段名  数据类型  [位置]; 

        修改数据:update 表名 set 字段1=值1,字段2=值2,... where 条件

    4.查看数据库 show databases; show create database 名称; use 名称; 

    alter  table  my_student  modify  number  char(10)  after id;

        查询数据:

    表结构的CURD

    ---修改学生表中的gender字段为sex。alter table 表名  change  旧字段名  新字段名  数据类型 ;alter table my_student  change  gender  sex  varchar(10);

          所有字段:select * from 表

    1.创建表 

    ---删除学生表中的字段age。                                alter  table  表名   drop   字段名;    

          指定字段:select 字段1,字段2,... from 表

    create table 名称( 

    alter  table  my_student  drop  age;                                       

          指定别名:select 字段1 as 别名1,字段2 as 别名1,... from 表

    字段 类型(长度) 约束, 

    删除数据表: drop  table  表名1,表名2,……;         一次性删除多个表

          去重:select distinct 字段 from 表

    字段 类型(长度) 约束 

    数据操作

          条件查询:

    ); 

    ---新增数据(插入全表字段数据) insert into my_student values('Jim','female',1106101);

            逻辑条件:and(与)or(或)

    2.类型 

    ---新增数据(指定字段列表)   insert into  my_student(number,sex)  values(1106,'male');

              select * from 表 where ... and/or ...

    char 固定长度 varchar 可变长度 

    ---查看所有的数据  select  * 

            比较条件:>,<,>=,<=,=,!=,<>(不等于),between and

    int bigint float double 

                      from  表名

            判空条件:

    bit 

                      [where 条件];

              判断null:is null/is not null

    date time datetime 传Null 就是null timestamp 取当前的时间

    ---更新数据    alter是操作数据库,表,字段。 update更新数据

              判断空字符串:='',!='',<>''

    3.查看和删除表结构 

    --更新名字叫Jim的性别。      update  表名  set  字段 = 值[where 条件];

              模糊条件:like

    * show tables; 

    update my_student set sex = 'female' where name = 'Jim';

                '%':替换任意个字符

    * show create tables 名称; 

    ---删除数据 

                '_':替换一个字符

    * desc 名称; 

    --删除表中性别男的      delete  from  表名  [where  条件];

                  select *JavaWeb前端笔记,多表的询问。 from 表 where 字段 like '%查询字符%'

    * drop table 表名;

    delete  from my_student  where  sex = 'male';

              分页查询:      

    4.修改 

    中文数据问题本质是字符集问题,set names  gbk;   一定要设置字符集。

                @p_pagesize int,//页面数量

    * alter table 表名 add 字段 类型(长度); 

    外键:如果一张表中有一个字键段(非主键)指向另外一张表的主键,那么将该字段称之为外键。

                @p_currentPage int//当前页

    * alter table 表名 drop 字段; 

    主键问题

                declare @string varchar(3000)

    * alter table 表名 modify 字段 类型(长度); 

    主键直接在字段之后,可以有多个字段作为主键,这样的主键称为复合主键。

                if(@p_currentPage=1)//第一页

    * alter table 表名 change 旧字段 新字段 类型(长度) 

    没有主键,追加主键    alter  table  my_database  modify  course  char(10)  primary key;

                begin

    * rename table 表名 to 新表名; 

    没有主键,追加复合主键  alter table  my_student  add  primary key (sno,cno);

                  set @string = 'Select top ' cast(@p_pagesize as char(20)) '* from 表名 where 条件

    数据的CURD

    更新主键 & 删除主键  alter  table  表名 drop primary  key;

                end

    1.添加数据 

    范式:终极目标为了减少数据的冗余,凡是通过关系寻找出来的数据,坚决不再重复存储。

                else//不是第一页

    * insert into 表名 (字段1,字段2 ) values (值1,值2) 

    第一范式:在设计表存储数据时,数据具有原子性。

                begin

    * insert into 表名 values (值1,值2,,值3)

    如表(姓名,性别,教课时间(开始,结束))  此表的教课时间仍可拆分,不具备原子性。

                  set  @string = 'Select top ' cast(@p_pagesize as varchar(20)) '* from 表名where 条件and 表.字段 not in (select top '                    cast(((@p_currentPage-1)*@p_pagesize) as varchar(20)) '字段 from 表名where 条件

    2.修改数据 

    解决办法(姓名,性别,开始,结束)

                end

    * update 表名 set 字段=值 , 字段=值 where 条件;

    第二范式:解决表设计出现的部分依赖,部分依赖就是存在字段依赖主键中某个字段(主键的部分)。只要不存在复合主键,表的设计就一定满足2NF

                exec(@string);

    3.删除数据 

    如一张表中有(讲师,性别,班级,教室,带课时间,开始时间,结束时间)   因为讲师没办法作为主键,需要结合讲师班级才能作为主键(一个老师在一个班只带一个阶段的课,性别并不依赖班级,只依赖讲师;教室不依赖讲师,只依赖班级,因此出现了性别和教室依赖主键中的一部分)

              排序查询:order by asc/desc

    * delete from 表名 where 条件; 

    解决方案: 可以将性别与讲师单独成表,班级与教室也单独成表,以此取消复合主键。

                asc:正序;顺序

    * truncate 表名; 

    有人也认为可以使用逻辑主键,例如增加IDP。这样真的可以吗?  不可以,如下,会出现传递依赖。

                desc:反序;倒序

    数据的查询

    第三范式:理论上讲,应该一张表中的所有字段都直接依赖主键,如果表设计中存在一个字段,并不直接依赖主键,而是通过某个非主键字段依赖,最终实现依赖主键,如一张表中有(主键IDP,讲师,性别,班级,教室,带课时间,开始时间,结束时间)  性别依赖于讲师,讲师依赖于主键IDP;教室依赖班级,班级依赖主键IDP。性别和教师都存在传递依赖。

              分组查询:group by 字段

    1.语法: 

    解决方案:将存在传递依赖的字段,以及依赖的字段本身单独取出,形成一个单独的表,然后在需要对应的信息的时候,使用对应的实体表的主键加进来。一句话就是实体单独建表。

              分组后筛选:having

    * select * | 字段1,字段2,,字段3 from 表 where 条件 order by 字段 asc/desc; 

    查询数据完整语法

    二、数据约束

    * 查询的字段支持运算 

    select 字段名/*

    JavaWeb前端笔记,多表的询问。数据约束:对用户表操作进行约束

    * where条件的符号 

    from  数据源

      (1)默认值:

    * <> 不等于 

    [where 条件子句]

        作用:当用户对使用默认值的字段不插入值的时候,就会使用默认值

    * in 范围 

    [group by 子句]

        注意:

    * like 模糊查询 like ‘张_’ ‘%张%’ 

    [having 子句]

          (a)对于默认值字段插入null是可以的

    * and or not 

    [order by 子句]

          (b)对于默认值字段可以插入非null

    * between and

    [limit 子句];

        创建:

    2.聚集函数:都是操作列的。 

    select *  from my_student; 

          (a)建表:

    * count – 计算 

    ---去重

             create table temp_table

    * sum – 求和 

    select distinct *  from  my_student;

             (id int,

    * avg – 平均值 

    --插入数据

             name varchar(10),

    * max – 最大值 

    insert into my_student values(null,'itcast01','张三','男'),(null,'itcast02','李四','男'),(null,'itcast03','王五','女'),(null,'itcast03','男');

             address varchar(50) default '吉林长春')

    * min – 最小值 

    --字段别名

          (b)语句:

    今天内容:MySQL数据库(多表的查询)

    select id,number as 学号,name as 姓名, sex 性别 

             alert table 表名 add constraint 默认名字(任意) default('默认值') for 字段

    分组查询

    from my_student;

      (2)非空:

    1.分组查询: 

    where子句

          作用:限制字段,必须赋值

    * select * from stu; – 查询所有的学生的信息。默认分成一组。 

    原理:where是唯一一个直接从磁盘获取数据的时候就开始判断的条件:从磁盘取出一条记录,开始进行where判断,判断的结果如果成立保存到内存,反之放弃。

          注意:

    * 可以使用group by sex 来进行分组。 

    ---找学生id为1,3,5的学生

            (a)非空字段必须赋值

    * 条件过滤的关键字 having 一般都使用在分组语句的条件过滤。

    select  *  from  my_student  where id =1 || id =3 || id =5;

            (b)非空字段不能赋null

    2.练习 

    或者select * from my_student where id in(1,3,5);

          创建:

    create database day16; 

    group by  字段 [asc/desc];   分组  根据某个字段进行分组,相同的放一组,不同的分到不同的组。统计的是数据,不是记录

            (a)建表:

    use day16; 

    --按性别分组

              create table temp_table

    create table orders( 

    select * from my_student group by sex; 这是绝对错误的,分组是为了统计数据,按分组字段进行数据统计

              (id int not null,

    id int, 

    SQL提供了一系列统计函数

              name varchar(10),

    product varchar(20), 

    Count();  统计分组后的记录数,每一相同的组有多少行记录

              address varchar(50) default '吉林长春')

    price float 

    Max();   统计每组最大的值

            (b)语句:

    );

    Min();   统计每组最小的值

              alert table 表 alert column 字段名称 字段类型 not null

    insert into orders(id,product,price) values(1,'电视',900);

    Avg();   统计每组的平均值

      (3)唯一:

    insert into orders(id,product,price) values(2,'洗衣机',100);

    Sum();   统计每组的和

          唯一键分为主键(主键索引)和唯一索引(unique索引)

    insert into orders(id,product,price) values(3,'洗衣粉',90);

    ---身高高矮,平均年龄,和总年龄

          作用:对该字段的值不能重复

    insert into orders(id,product,price) values(4,'洗衣机',100);

    select sex,count(*),max(height),min(height),avg(age),sun(age)   from my_student  group by sex;

          注意:

    insert into orders(id,product,price) values(5,'洗衣粉',90);

    注意使用group by却不使用统计函数,没意义!

            (a)唯一字段可以插入null

    insert into orders(id,product,price) values(6,'洗衣粉',90);

    多字段分组:先根据一个字段进行分组,然后对分组后的结果再次按照其他字段分组。

            (b)唯一字段只能插入一个null

    ------ --------- -------

    having子句

          创建:

    | id  | product | price |

    与where子句一样进行条件判断,思考:为什么是group by……having…… 而不是group by……where???

            (a)建表:

    ------ --------- -------

    进行数据统计时,是数据进入内存之后,会进行分组-》统计-》having条件判断。而where是针对从磁盘读取数据时进行判断,此刻还没分组,进行统计就判断了。所以错误!!!

              create table student

    |    1 | 电视    |  900 |

    ---求出所有班级人数大于等于2的学生人数

              (id int unique,

    |    2 | 洗衣机  |  100 |

    order by 子句

              name varchar(10))

    |    3 | 洗衣粉  |    90 |

    order by根据某个字段进行升序或降序排序,依赖校对集

            (b)语句

    |    4 | 洗衣机  |  100 |

    基本语法: order by 字段名 [asc/desc];  asc升序 可不写      desc降序

              create unique clustered index 约束名 on 表名(字段名)

    |    5 | 洗衣粉  |    90 |

     

      (4)主键:

    |    6 | 洗衣粉  |    90 |

    limit子句是一种限制结果的语句

        作用:非空,唯一

    ------ --------- -------

    1)用来限制数量

        注意:

    * 对订单表中商品归类后,显示每一类商品的总价(按着商品的名称进行分组,查询的是每一组的总价)

    --查询学生前两个   select * from my_student limit 2;

          (a)通常情况下,每张表都会设置一个主键字段用于标记表中的每条记录的唯一性。

        select product,sum(price) from orders group by product;   

    2)限制起始位置  limit 起始位置,长度

          (b)建议不要选择表的包含业务含义的字段作为主键,建议给每张表独立设计一个非业务含义的id字段。

        select product,sum(price),count(*) from orders group by product;       

    --查从编号4开始,询找两个学生

        创建:

    * 查询购买了几类商品,并且每类总价大于200的商品

    select * from my_student limit 4,2;

          (a)建表:

        select product,sum(price) from orders group by product having sum(price) >200;

    内连接,外连接,自然连接,交叉连接

              create table student

        select product,sum(price) from orders where price > 95 group by product having sum(price) > 300;

    内连接,从左表中取出每一条记录,和右表中所有的记录进行匹配,匹配必须是左表中与右表中某个条件相同,最终会保留结果,否则不保留。

              (id int primary key,--主键

    查询语句的顺序

    基本语法:  左表 [inner] join 右表  on 左表.字段 = 右表.字段;

              name varchar(10))

    select … from … where … group by … having … order by 

    select * from my_student inner join my_class on my_student.C_id = mu_class.id;

          (b)语句:

    数据库的备份和还原

    左外连接:左表为主,然后每条记录与右表进行连接,不管能不能匹配的上,左表都会保留。能匹配,右表某条记录保留,不能匹配,某条记录置为NULL,最终记录数至少不少于左表已有的记录。

              第一:先设置字段为null: alter table 表名 alter column 字段名 字段类型 not null

    1.如果使用工具来进行备份。 

    基本语法:  左表  left join  右表  on  左表.字段 = 右表.字段;

              第二:设置主键:alter table 表名 alter constrant 主键名(任意) promary key(主键字段)

    单表的约束(主键)

    a表     id   name      b表     id   job   parent_id

      (5)自增长

    1.约束:保证数据的完整性。 

    1   张3                   1     23     1

        作用:自动递增

    2.主键约束(重要) 

    2   李四                 2     34     2

        注意:一张表中不能有两个或者多个标识列

    * 如果把某个字段声明成主键了,有如下的特点: 

    3   王武                 3     34     4

        创建:

    * 唯一 

    a.id同parent_id   存在关系

          (a)建表:

    * 非空 


              create table student

    * 被引用 

     1) 内连接 

              (id int identity(1,1) primary key,--主键,自增长,从1开始,每次增加1

    * 声明主键,使用关键字 primary key 

    select   a.*,b.*   from   a   inner   join   b     on   a.id=b.parent_id

              name varchar(10))

    3.对主键进行测试 

    结果是

          (b)语句:

    create table person( 

    1   张3                   1     23     1

              alter table 表名 add 列名 int identity(1,1) NOT NULL--增加一列自增

    id int primary key, 

    2   李四                  2     34     2

      (6)主键:   

    name varchar(50) 

     2)左连接  

        作用:约束两张表的数据

    ); 

    select   a.*,b.*   from   a   left   join   b     on   a.id=b.parent_id

        出现两种表的情况:解决数据冗余高问题, 独立出一张表。

    insert into person values (1,’美美’); 

    结果是

        注意:

    insert into person values (2,’小凤’); 

    1   张3                   1     23     1

          (a)被约束的表称为副表,约束别人的表称为主表,外键设置在副表上的!!!

    insert into person values (3,’如花’);

    2   李四                  2     34     2

          (b)主表的参考字段通用为主键!

    4.主键自动增长 

    3   王武                  null

          (c)添加数据: 先添加主表,再添加副表

    * 把主键的值交给数据库进行维护了。不用咱们去管理了。 

    3) 右连接   

    新葡亰496net,      (d)修改数据: 先修改副表,再修改主表

    * 使用关键字:auto_increment 

    select   a.*,b.*   from   a   right   join   b     on   a.id=b.parent_id

          (e)删除数据: 先删除副表,再删除主表

    * 主键的类型:int bigint

    结果是

        创建:

    drop table person;

    1   张3                   1     23     1

          (a)建表:

    create table person(

    2   李四                  2     34     2

            -- 部门表(主表)

        id int primary key auto_increment,

    null                        3     34     4

            CREATE TABLE dept(

        name varchar(50)

    4) 完全连接 

            id INT PRIMARY KEY,

    );

    select   a.*,b.*   from   a   full   join   b     on   a.id=b.parent_id

            deptName VARCHAR(20)

    insert into person values (null,'美美'); 

    结果是

            )

    insert into person values (null,'小凤');

    1   张3                  1     23     1

            -- 修改员工表

    insert into person values (null,'如花');

    2   李四                 2     34     2

            CREATE TABLE employee(

    delete from person where id = 3;

    null                   3     34     4

            id INT PRIMARY KEY,

    insert into person values (null,'如花');

    3   王武                 null

            empName VARCHAR(20),

    唯一和非空约束

            deptId INT,-- 把部门名称改为部门ID,

    1.唯一约束:声明值是唯一的值。但是允许为空。 

              -- 声明一个外键约束

    * unique 

            CONSTRAINT 外键名称 FOREIGN KEY(外键) REFERENCES 主键表(主键id)

    2.非空约束:声明值不能是空的。 

            )

    * not null 

          (b)语句:

    外键约束(多表)

            alter table 表 add foreign key(表外键) references主键表(主键id)

    1.作用:保证数据的完整性。 

    三、多表链接查询

    2.模拟例子 

      (1)外链接

    create table dept( 

        外连接分为:左链接、右链接、完全外链接

    did int primary key auto_increment, 

        (a)左链接:left join 或 left outer join

    dname varchar(50) 

           用法:最少两张表,A表和B表

    );

            Select A.字段 from A left join B on A.id = B.id

    create table emp(

           左外连接包含left join左表所有行,如果左表中某行在右表没有匹配,则结果中对应行右表的部分全部为空(NULL).

        eid int primary key auto_increment,

        (b)右链接 right join 或right outer join

        ename varchar(50),

           用法:最少两张表,A表和B表

        sal double,

            Select A.字段 from A right join B on A.id = B.id

        dno int

           右外连接包含right join右表所有行,如果左表中某行在右表没有匹配,则结果中对应左表的部分全部为空(NULL)。

    );

        (c)完全外连接 full join 或 full outer join

    insert into dept values (1,'研发部');

           用法:最少两张表,A表和B表

    insert into dept values (2,'人事部');

            Select A.字段 from A full join B on A.id = B.id

    insert into emp values (null,'大鹏',15000,1);

            完全外连接包含full join左右两表中所有的行

    insert into emp values (null,'熊大',5000,1);

            如果右表中某行在左表中没有匹配,则结果中对应行右表的部分全部为空(NULL)

    insert into emp values (null,'小凤',5000,2);

            如果左表中某行在右表中没有匹配,则结果中对应行左表的部分全部为空(NULL)。

    insert into emp values (null,'美美',6000,2);

      (2)内连接

    * 如果直接删除到研发部门

         内连接 join 或 inner join

        delete from dept where did = 1;

         用法:最少两张表,A表和B表

    3.就可以使用外键进行约束,保存数据库的完整性。 

         Select A.字段 from A full join B on A.id = B.id

    * 声明外键,使用foreign key声明外键。 

         inner join 是比较运算符,只返回符合条件的行。
         此时相当于:select * from A,B where A.ID=B.ID

    * 修改表,给emp表添加的外键(把dno的字段添加成外键) 

    * alter table emp add foreign key (dno) references dept (did);

    4.如果已经包含外键的关联了,就不能删除部门了。 

    delete from dept where did = 1;

    5.在创建表结构的时候,可以声明外键 

    create table emp( 

    eid int primary key auto_increment, 

    ename varchar(50), 

    sal double, 

    dno int, 

    foreign key (dno) references dept (did) 

    ); 

    多表的设计

    1.一对多的建表原则:在多方的表中,新添加一个字段,作为该表的外键。指向一方表的主键。(外键的值只能从一方表的主键中获取)

    2.多对多的建表原则:创建一个中间表,中间表中至少包含两个字段,这两个字段作为外键,指向多对多原表的主键。

    3.一对一的建表原则:例子:一个公司都对应一个地址。 

    * 创建一个公司的表,创建一个地址的表。 

    * 主键对应:公司的主键值和地址表的主键值是相同的。

    4.JavaWeb课程最后4天,有一个项目。购物的网站。 

    * 用户 订单 商品 分类 

    多表的查询

    1.了解笛卡尔积。查询数据是不准确的。笛卡尔积:两个结果的乘积。 

    A B 

    aid aname bid bname aid 

    a1 a11 b1 b11 a1 

    a2 a22 b2 b22 a2 

    b3 b33 a1

    select * from A,B; 结果:就是笛卡尔积。 

    a1 a11 b1 b11 

    a1 a11 b2 b22 

    a1 a11 b3 b33 

    a2 a22 b1 b11 

    a2 a22 b2 b22 

    a2 a22 b3 b33 

    多表查询(准确的数据)之内连接

    0.前提条件:部门和员工表例子,两张表需要存在关系,就是外键的关系。 

    1.普通内链接 

    * 语法:inner join … on 条件 

    * 例子: select * from dept inner join emp on dept.did = emp.dno;

    2.隐式内连接(开发中使用最多的) 

    * 语法:去掉inner join关键字 

    * 例子:select * from dept,emp where dept.did = emp.dno; 

    * 使用别名:select d.dname,e.ename,e.sal from dept d,emp e where d.did = e.dno; 

    多表查询之外链接

    0.前提条件:部门和员工表例子,两张表需要存在关系,就是外键的关系。 

    1.左外链接 

    * 语法:关键字:left outer join … on 条件; 

    * 例子:select * from dept left outer join emp on dept.did = emp.dno; 

    2.右外链接 

    * 语法:关键字:right outer join … on 条件; 

    * 例子:select * from dept right outer join emp on dept.did = emp.dno; 

    内连接和外链接的数据的区别

    1.向部门中添加一些数据 

    insert into dept values (3,’牛宝宝部’); 

    insert into dept values (4,’扯淡部’); 

    2.向员工表中添加一些数据 

    insert into emp values (null,’陈冠希’,16000,null); 

    insert into emp values (null,’张柏芝’,26000,null);

    3.进行测试 

    * 内连接:select * from dept,emp where dept.did = emp.dno; 

    * 左链接:select * from dept left join emp on dept.did = emp.dno; 

    * 向链接:select * from dept right join emp on dept.did = emp.dno;

    4.总结:内连接查询的结果没有变化,查询的是有关联的数据。左链接,看谁是左表,把左表中所有的数据全部都查询出来和有关联的数据。右链接,看谁是右表,把右表中所有的数据全部都查询出来和关联数据。 

    5.看图 

    子查询

    1.一条SQL语句不能查询出结果,需要依赖几条SQL语句的查询。 

    2.查询出英语成绩大于英语平均分的同学? 

    * 先查英语的平均分 

    * 再查英语的成绩(大于平均分)

    use day15;

    select name,english from stu where english > (select avg(english) from stu);

    3.子查询的符号 

    < 小于 > 大于 

    <= 小于等于 >=大于等于 

    = 等于 <>或!= 不等于 in 范围

    any :select * from stu where english >any (5,6,7) 大于范围最小的值 

    all :select * from stu where english >all (5,6,7) 大于范围最大的值 

    练习

    查看熊大所属的部门名称和员工名称? 

    * 查询:部门名称和员工的名称 

    * 表:部门表和员工表 

    * 条件:熊大的名称 where ename=’熊大’ 内连接 where d.did = e.dno

    * 语句:select d.dname,e.ename from dept d,emp e where d.did = e.dno and e.ename='熊大';

    统计每个部门的人数(按照部门名称统计) 

    * 查询:部门的人数,部门名称 

    * 表:部门表和员工表 

    * 条件:内连接 where d.did = e.dno 分组:group by d.dname

    * 语句:select d.dname,count(*) from dept d,emp e where d.did = e.dno group by d.dname;

    统计部门的平均工资(按部门名称统计) 

    * 查询:部门的平均工资,部门名称 

    * 表:部门和员工表 

    * 条件:内连接 where d.did = e.dno 分组:group by d.dname

    * 语句:select d.dname,avg(e.sal) from dept d,emp e where d.did = e.dno group by d.dname;

    统计部门的平均工资大于公司平均工资的部门 

    * 查询:部门 

    * 表:员工表和部门表 

    * 条件:内连接 where d.did = e.dno 分组:group by d.dname 条件:部门的平均 > 公司的平均工资

    * 语句:select d.dname,avg(e.sal) from dept d,emp e where d.did = e.dno group by d.dname having avg(e.sal) > (select avg(sal) from emp);

    本文由新葡亰496net发布于网络数据库,转载请注明出处:JavaWeb前端笔记,多表的询问

    关键词: