简述Mysql

             MySQL数据库

MySQL数据库... 1

2019-7. 2

前言... 2

1、什么是数据库?... 2

2、常见的数据库有哪些?... 3

3、生活中哪些地方使用到数据库?... 3

一、MySQL数据库简介... 3

二、数据库发展史... 3

三、 数据库登录... 3

四、数据库术语... 4

五、数据库技术... 4

5.1  SQL语句... 4

5.2  SQL语句分类... 4

5.3数据库基础操作... 4

5.4数据类型... 6

5.8插入语句... 8

5.12查询语句... 10

5.12.5限制查询(列名或者*)计算机都是从0开始的... 12

5.12.6排序(order  by) 12

5.12.7条件查询... 12

5.12.8聚合(分组)函数... 16

9.12.9分组查询(group by)选择的列不同,分组不同... 17

5.12.10having语句... 18

5.12.11嵌套查询(子查询) 18

5.12.12 MySQL函数... 20

1)常用数值处理函数... 20

2)常用字符处理函数... 21

六、约束... 22

6.1约束的分类... 23

6.2主键约束(primary key) 23

6.3唯一约束(unique) 25

6.4默认值约束(default) 26

6.5非空约束(not null) 27

七、视图(view) 28

7.1创建视图... 28

7.2修改视图... 28

7.3使用视图实现对表的操作(权限问题)... 29

7.4删除视图... 29

                      2019-7

Where 条件是可变的

前言

1、什么是数据库?

   数据库就是储存数据的仓库

2、常见的数据库有哪些?

     微软--SQL server

        微软---Access

     甲骨文---Oracle

        甲骨文---MySQL

      IBM---DB2

       国产---人大金仓

3、生活中哪些地方使用到数据库?

       银行管理系统--->账户信息和钱

       超市管理系统--->商品信息和价钱

    网上购物商城--->账户信息和商品信息

    12306---->车次信息和账户信息

       ...........

一、MySQL数据库简介

    MySQL是一款关系型数据库管理系统,是Oracle 公司旗下产品,主流数据库管理系统之 一,主要有2个版本,社区版和企业版,主要特点有:体积小、速度快,可移植性强(跨平台)

二、数据库发展史

    2.1 程序管理阶段(20世纪50年代中前期)

    特点:数据不能长期保存

       2.2文件系统阶段(20世纪50年代后期---60年代后期)

    特点:数据缺乏独立性(数据重复)

    2.3数据库系统阶段(20世纪60年代后期---至今)

       特点:数据共享,减少冗余

    三、 数据库登录

      准备工作:

       1)禅道------>禁用

       2)OA----->禁用

       3)wamp----->禁用

          计算机--右键单击--->管理---->服务和应用程序----->服务

   四、数据库术语

              4.1关系

                      一个关系就是一张二维表(Excel)

         4.2属性 (列)

                     二维表中的一列称为属性,或者"字段"一般习惯称为"列"

              4.3 元组(行)

                     二维表中的一行称为元组,或者"记录"一般习惯称为"行"

   五、数据库技术

   5.1  SQL语句

     结构化查询语句,通过SQL语句可以操纵数据库管理系统

   5.2  SQL语句分类

   5.2.1  DDL 数据定义语句,主要是对数据库中的表,进行创建,修改,删除

            create------>创建

                            alter------>修改

                            drop----->删除

   5.2.2  DML数据操纵语句,主要是对表中的数据,进行插入、跟新(修改)、删除

                         insert----->插入

                         update--->更新(修改)

                         delete--->删除

   5.2.3  TCL事务控制语句,主要是对数据库的事务的控制

                      commit--->提交事务(保存)

                      rollback--->回滚事务(撤销)

   5.2.4  DQL数据查询语句,主要学习查询(重点)

                         select--->查询

   5.3数据库基础操作

   5.3.1查看当前所有存在的数据库

        语法格式:  show  databases;           ---查看所有数据库  

   5.3.2 创建数据库

              语法格式: create  database  数据库名称;

               案例:创建一个数据库,名称为tarena

          create   database   tarena;  (一个单词一个空格)

                     show    databases;

     练习:创建一个数据库,名称为testing并验证数据库创建是否成功

                     create  database  testing;    

                     show  databases;  --查看所有数据库

     5.3.3选择数据库

          语法格式: use 数据库名称; (已存在)

     5.3.4 查看已经创建好的数据库信息

                 语法格式: show  create  database  数据库名称;

                案例:查看tarena数据库信息(查看某个数据库)

                         show  create  database  tarena;

                     练习:查看testing数据库信息

            show  create  database  testing;

    5.4.5删除数据库

              语法格式:drop  database  数据库名称;

              案例: 删除tarena数据库并验证

                      drop  database  tarena;

                     show  databases;--查看所有数据库

       练习:

    1、创建一个数据库,名称为tedu;

        create  database   tedu;

    2、查看tedu 数据库的信息

               show  create  database   tedu;

       3、选择tedu数据库

         use  tedu;

       4、 查看当前存在的全部数据库

         show  databases;

     5、删除tedu数据库并验证           

        drop  database  tedu; 

               show  databases;

   小说类型--->武侠   都市   玄幻   穿越

  5.4数据类型

  5.4.1 数值类型

        (1)整数类型

          int(n)表示整数,n表示显示的宽度

        int(4)  1234   0123  没有四位前面自动补0

     (2)小数类型

              double(n,m)表示小数,n表示数字的总位数,m小数的位数

              double(4,1)  123.4    546.2  一共有4位数字,1位小数

  5.4.2字符类型

      (1)char(n)表示固定长度的字符串,n表示长度(个数),当要保存的长度小于n时,在字符           串的右边使用空格补齐

      (2)varchar(n)表示可变长度的字符串,n表示字符串的最大长度(个数),当要保存的长度        小于n时,按照实际长度保存。

  5.4.3 日期类型

   date表示日期,默认日期格式是: yyyy-mm-dd

    y表示年份m表示月份 d表示哪天

5.5查看数据库中全部的表

   语法格式: show  tables;

   案例: 选择数据库testing,查看数据库中是否存在表

          use  testing;

                show  tables;

    练习:选择mysql数据库,查看数据库中是否存在表

          use   mysql;

                show   tables;

  5.6创建表(table)  primary  key--主键,防止重复

      语法格式:  create  table  表名(

       列名1   数据类型   primary  key,

          列名2   数据类型,

          列名3   数据类型,

       .......,

          列名n   数据类型

              );

         案例:选择testing 数据库,创建一张表,表名为db_t01,表中包含的字段有,id  int(4)、 name  varchar(30)、 age  int(3)

                     use   testing;

                     create  table  db_01(

                      id      int(4)      primary  key,

                      name   varchar(30),

                   age     int(3)

                     );

            show   tables;  查看表格是否创建成功

             create table db_t02(

              id   int primary key,

                       name varchar(30),

                       sex char(3),

                        sal double(7,2)

                     );

                     show   tables;

           练习:在testing 数据库中,创建一张表,表名为db_t03,表中包含的字段有:id                                int(4) primary key ,name varchar(30)、address  varchar(50)

                            create   table  db_t03(

                               id  int(4) primary key,

                               name  varchar(30),

                               address  varchar(50)

                            );

                             show  tables;

     5.7查看表结构

              语法格式:  desc   表名;

       案例:查看db_t01表结构

             desc  db_t01;

       练习:向db_t03 表中,插入3条记录并验证

    5.8插入语句

    5.8.1向全部列插入数据

                语法格式: insert  into  表名  values(列值1,列值2,列值3,......列值n);

              说明:values中的列值必须和表结构中的列名是一一对应的(顺序、数量、类型)

         数值类型----->直接填写

                字符类型/日期类型---->''  (英文下的单引号)

                验证:select * from  表名;

        案例:向db_t01表中插入数据

                       1)查看表结构  desc db_t01;

                       2) 根据语法格式插入数据

                            insert  into  db_t01  values(1001,'rose',18);

                     3) 验证 select  *  from  db_t01;

    5.8.2 向指定列插入数据

                语法格式: insert  into  表名(列名1,列名2,...列名n) values (列值1,列值2,...列值n);

说明:表中的列名和values中的列值一一对应的关系(顺序、类型、数量)

验证: select * from 表名;

案例: 向 db_t02 插入数据

 id           name              sex           sal

 1            张三              男

 2            李四                            2000

 3                              女            300.5

 4                                           456.78

  1)查看表结构  desc db_t02;

  2) 根据语法格式插入数据

     insert   into  db_t02(id,name,sex) values(1,'张三','男');

     insert   into  db_t02(id,name,sal)values(2,'李四',2000);

     insert   into  db_t02(id,sex ,sal)values(3,'女',300.5);

     insert   into  db_t02(id,sal)values(4,456.78);

  3)验证:  select * from db_t02;

    5.8.3批量插入数据(MySQL)

    语法格式: insert  into 表名(列名1,列名2,...列名n)values(列值1,列值2,...列值n), (列值1,列值2,...列值n), ...(列值1,列值2,...列值n  );  

案例:向db_t03表中批量插入数据

  1)全部列批量插入数据

    insert  into db_t03 values(104,'李敏','上海'),(107,'李明月','北京'),(108,'程洁','成都'),(109,'吴浩','湖南');

  2)指定列批量插入数据

    insert  into  db_t03(id,name)values(120,'赵柳'),(121,'天琪'),(122,'美棋');

练习: 1、创建一个表,表名为db_t04表中包含的字段有:id int(4) pk,  name varchar(20)、 age int(3)

      2、向表中插入4条记录

       id          name         age

              1001         rose          23

       1002         james        

             1003                      30

       1004         pite          22

       1005         小明           

     1)create table db_t04(

              id int(4) primary key,

              name varchar(20),

              age int(3)

              );

     2)

              insert  into db_t04 values(1001,'rose',23);

              insert  into db_t04 values(1004,'pite',22);

              insert  into db_t04(id,name)values(1002,'james'),(1005,'小明');

       insert  into db_t04(id,age)values(1003,30);

       select * from db_t04;

    5.9更新(修改)语句 set(表中的哪个列名) where(表中哪行)

           update  表名 set  列名1=该列新值,列名2=该列新值,...,列名n=该列新              值 where 条件;

        说明:如果没有where条件,修改表中全部数据。

         验证: select * from 表名;

      案例:修改 db_t01表中,将编号(id)是1001的记录,姓名(name)修改为张三

                     update db_t01 set  name='张三' where id=1001;

       练习:修改db_t04表中,将编号(id)是1002的记录,年龄(age)修改为100                     并验证

                     update  db_t04  set age='100'where id='1002';

                      select * from db_t04;

        练习:修改db_t04表中,将姓名(name)是小明的记录,年龄修改为18

             update  db_t04  set  age='18'  where  name='小明';

                            select * from db_t04;

         练习:修改db_t04表中,将编号(id)是1003的记录,姓名(name)修改为tom,                            年龄(age)修改为20

                            update  db_t04 set name='tom',age='20' where id=1003;

                            select * from db_t04;

              练习: 修改db_t03表中,将全部数据的address修改为北京

                        update db_t03 set address='北京'where id;

                            select * from db_t03;

        5.10删除表中的数据

                       语法格式:delete  from 表名 where 条件;

                       说明:如果没有where 条件,删除表中全部数据

                      案例:删除db_t04表中,编号是(id)是1003的记录

                              delete from db_t04 where id=1003;

                                   select * from db_t04;

                     练习: 删除db_t04表中,姓名(name)是xiaom的记录

                               delete from db_t04 where name='xiaom';

                                   select * from db_t04;

                     练习:删除db_t03表中全部数据

                              delete from db_t03;

                              select * from db_t03;

                     5.11删除表

                            语法格式:drop table  表名;

                            案例:删除db_t04

                                    drop table db_t04;

                                    select * from db_t04;

                            练习:删除表db_t03;

                                    drop talbe db_t03;

                                     show tables;

5.12查询语句

5.12.1查询全部列数据(*代表全部列)

        语法格式:select * from 表名;

       案例:查询emp表中全部数据

                      select * from emp;--12条

              练习:查询dept、student表中的全部列数据

                       select * from dept;--7

                       select * from student;--15

5.12.2查询指定列数据

          语法格式:select 例名1,例名2,...,例名n from 表名;

          案例:查询emp表中,员工编号(empno),姓名(ename),职位(job),工资(sal)

                            select empno,ename,job,sal from emp;

练习:查询 student表中,学员编号(sid)姓名(sname),地址(address)

                     select sid,sname,address from  student;

5.12.3给列起别名

      语法格式: select 列名1 as 别名 1,列名2 as 别名2,......列名n as 别名n               from 表名;

               说明:省略式写法,去掉as

            案例:查询dept 表中,部门编号(deptno),部门名称(dname),部门地址(loc)              并给列起别名

        select deptno as 编号,dname as 部门名称, loc as 部门地址 from dept;

      或 select deptno 编号,dname 部门名称, loc部门地址 from dept;

练习:查询emp表中,员工编号(empno),姓名(ename),职位(job),入职时间(hiredate),工资(sal),部门编号(deptno)并给列起别名

select empno as 员工编号,ename as 姓名, job as 职位,hiredate as 入职时间,sal as工资,deptno as 部门编号 from emp;

或 select empno 员工编号,ename 姓名,job 职位,hiredate 入职时间,sal 工资,deptno 部门编号 from emp;

5.12.4去掉重复的列值(去重)

              语法格式: select distinct 列名 from 表名;

              案例:查询emp表中,员工职位的种类?

                   select distinct  job from emp;

         练习:查询student表中,运动类型(type)的种类?

                     select distinct  type from student;

   5.12.5限制查询(列名或者*)计算机都是从0开始的

         语法格式: select 列名/* from 表名 limit 初始位置,行数;

                     说明:初始位置,默认值为0,表示第一行

               案例:查询emp 表中,员工编号(empno),姓名(ename),职位(job),工资                             (sal),要求查询前5行记录

               select empno,ename,job,sal from emp limit 0,5;

        练习:查询student表中,从第3条开始,查询6条记录, 查询的全部列数据

             select * from student limit 2,6;

 5.12.6排序(order  by)

   语法格式: select 列名n  from 表名order by 列1 asc/desc,列名2 asc/desc;

    说明: asc----表示升序(小到大)

                desc---表示降序(大到小)

         案例:查询emp表中,员工编号(empno),员工姓名(ename),职位(job),工资(sal),根据工资进行降序排列  

         select empno,ename,job ,sal from emp order by sal desc;

     案例:查询emp 表中,员工编号(empno),姓名(ename),职位(job),工资(sal),部门编号(deptno),先根据部门编号降序排列,在根据工资升序排列

       select empno,ename,job,sal,deptno from emp order by deptno desc,sal asc;

说明:先根据第1列进行排序,如果第1列中出现相同的列值,那么在根据第2列进行排序,反之不排序。

练习:查询dept 表中,部门编号(deptno),部门名称(dname),部门地址(loc),根据部门编号降序排列

     select deptno,dname,loc from dept order by deptno desc;

练习:查询student表中,学员编号(sid),姓名(sname),性别(sex),分数(score),先根据分数升序排列,在根据学员编号降序排列。

   select sid,sname,sex,score from student order by score asc,sid desc;

练习:查询emp 表中,倒数后5条记录(面试题)

   select * from emp order by empno desc limit 5;

5.12.7条件查询

语法:select */列名from 表名 where 条件 order by  列名 asc/desc;

说明:条件包含-->

关系运算符(><=>=<=<>/!=(不等于))、

逻辑运算符(与and、或or、非not)、

特殊情况

案例:查询student表中,运动类型(type)是网球的,学员的编号(sid),姓名(sname),性别(sex),分数(score)

    select sid,sname,sex,score,sex,score from student where type='网球';

练习:查询student 表中,分数小于等于75的学员编号(sid),姓名(sname),性别(sex),分数(score),根据分数降序排列

select sid,sname,sex,score from student where score<=75 order by score desc;

案例:查询emp表中,工资(sal)大于2000的,员工的编号(empno),姓名(ename),工资(sal)

  select empno,ename,sal from emp where sal>2000;

练习:查询student表中,性别是男的并且分数大于80的,学员的编号,姓名,性别,分数(score)

select sid,sname,sex,score from student where sex='男'and score>80;

练习:查看emp 表中,工资(sal)不等于1250的,员工的全部信息

select * from emp where sal!=1250;

  and:表示并且,用来连接2个或者多个条件(表达式)

案例:查询emp表中,工资在1000-5000之间的,员工的编号,姓名,职位,工资,根据工资升序排列

select empno,ename,job,sal from emp where sal>1000 and sal<5000 order by sal asc;

练习:查询emp 表中,工资不等于1250的并且部门编号(deptno)是30部门的,员工的编号,姓名,职位,工资,部门编号,根据工资降序排列

 select empno,ename,job,sal,deptno from emp where sal!=1250 and deptno=30 order  by sal desc;

  or  :表示或者,用来简介2个或者多个条件(表达式)

案例:查询emp表中,工资大于1250的或者部门编号是30的部门的,员工的编号,姓名,工资,部门编号

     select empno,ename,sal,deptno from emp where sal>1250 or deptno=30;

练习:查询student 表中,运动类型(type)是羽毛器的或者分数(score)大于90的.学员编号(sid),姓名(sname),分数(score),类型(type),根据分数升序排列

     select sid,sname,score,type from student where type ='羽毛球' or  score>90 order by type asc;

练习:查询student 表中,学员编号是1001,1003,1010,1015的学员信息(面试题)

     select * from student where sid=1001 or sid=1003 or sid=1010 or sid=1015;

  not:表示取反(非)not 加在列名前

  案例:查询emp表中,工资不等于1250的,员工的信息,根据工资进行降序排列

    select * from emp where sal!=1250 order by sal desc;

    select * from emp where not sal=1250 order by sal desc;

   1)列值为空的情况(is null)

   案例:查询emp表中,奖金(comm)为空的,员工的信息

        select * from emp  where  comm is null;

   练习:查询emp表中,没有上级领导编号(mgr)为空的员工信息

        select * from emp where mgr is null;

   2)列值不为空的情况(is not null)

   案例:查询emp表中,有奖金的,员工的信息

           select * from emp where comm  is not null;

   练习:查询emp表中,有上级领导编号的,员工的信息

               select * from emp where mgr is not null;

  3)between....and (在什么到什么之间)

   语法格式: select */列名 from 表名 where 列名 between 初值 and 终值;

   说明:[初值,终值]可以取到2个边界值,相当于and + = 符号写法

   案例:查询emp 表中,工资在1000-3000之间的,员工的编号,姓名,工资,部门编号(deptno)-->(获取2个边界值)

      select empno,ename,sal,deptno from emp where sal>=1000 and sal<=3000;

      select empno,ename,sal, deptno from emp where sal between 1000 and 3000;

练习:查询student表中,分数不在80-100之间的,学员的编号,姓名,分数,根据分数升序排列(面试题)

      select sid,sname,score from student where score<80 or score >100 order by score asc;

     select sid,sname,score from student where not score between 80 and 100 order by score  asc;

4)in表示 一个列中的,几个列值

   语法格式:select */列名 from 表名 where 列名 in(列值1,列值2,...,列值n);

   案例:查询student 表中,学员编号是1002,1004,1006的学员信息

         select * from student where sid=1002 or sid=1004 or sid=1006;

         select *from student where sid in(1002,1004,1006);

 练习:查询student表中,学员地址(address)是北京、杭州、天津的,学员信息

     select * from student where address in ('北京','杭州','天津');

     select * from student where address='北京' or address='杭州' or address='天津';

5)模糊查询(like)------->重点

  语法格式:

        select 列名

        from 表名

        where  列名

        like 条件

        order by  列名    asc/besc;

说明:-->条件包括:

(a)% :表示0个或者多个任意字符

(b)_ :表示任意1个字符

案例:查询emp表中,姓名首字母(第一个字母)是M的,员工的编号,姓名,职位,工资

 分析:Mgf M3423 M= = =>M%

 select empno,ename,job,sal from emp where ename like 'M%'; 

案例:查询emp表中,姓名尾字母(最后1个字母)是k的,员工编号,姓名,职位,工资

 分析:dfdsk   k===>%k

 select empno,ename,job,sal from emp where ename like '%k';

 案例:查询emp表中,姓名中N的,员工的编号,姓名,职位,工资

分析:Nfdfd     frewN   fweNgw  N =====>%N%

select empno,ename,job,sal from emp where ename like '%N%';

练习:

select empno,ename,job,sal from emp where ename like '%E_';

练习:查询student 表中,姓名中含有西红柿的,学员的编码,姓名,性别(sex),地址(address)

   select  sid,sname,sex,address from student where sname like '%西红柿%';

练习:查询emp 表中,姓名中倒数第3个字母N员工的编号,姓名,职位,工资

 select empno,ename,job,sal from emp where ename like '%N__';

练习:查询emp表中,姓名中不包含字母E并且有上级领导编号(mgr)的,员工的编号,姓名,职位,工资,根据工资降序排列

 select empno,ename,job,sal

  from emp

  where not ename like '%E%' and mgr is not null order by sal desc;

练习:查询emp 表中,姓名中不包含字母C并且编号是10或者20号部门的员工的编号,姓名,职位,工资,部门编号(面试题)

 select empno,ename,job,sal,deptno

  from emp

  where not ename like'%C%' and (deptno =10 or deptno =20);

 select empno,ename,job,sal,deptno

  from emp

  where not ename like'%C%' and deptno in(10,20);

5.12.8聚合(分组)函数

count     sum      avg   min   max

 1)count(*/列名)总数量

  *: 表示统计某表中数据的总记录数

  列名:表示统计某列中,列值不为空的,数据的总数量

案例: 统计emp 表中数据的总记录数

      select  count(*)  from emp ;

练习:统计emp 表中,奖金(comm)不为空的数据总数量

     select  count(comm) from emp;

练习:统计emp表中,员工职位种类的总数量(去重)

     select  count(distinct job)from emp;

 2)sum(列名)列值累加之和(求和)

 案例:查询emp表中,工资总和

select sum(sal) from emp;

练习:查询emp 表中, 奖金总和并起别名表示

     select sum(comm) 奖金总和 from emp;

3)avg(列名)求平均数

 案例:查询emp表中,员工的平均工资

 select avg(sal) from emp;

练习:查询student表中,学员的平均分数(score)

     select avg(score) from student;

4)min(列名)求最小值

案例: 查询emp 表中,员工的最底工资

     select min(sal) from emp;

练习:查询emp表中,30号部门的最低工资

    select min(sal) from emp where deptno=30;

5)max(列名)求最大值

案例:查询student表中,学员的最高分数

     select max(score) from student;

练习:查询student表中,学员总数,分数总和,最低分数,最高分数,平均分数,并起别名显示

 select count(*) 学员总数,sum(score) 分数总和, min(score) 最低分数, max(score) 最高分数,avg(score) 平均数 from student;

9.12.9分组查询(group by)选择的列不同,分组不同

就是选择哪列进行分组

分组查询:在一张表中,根据某一列,把数据分成几组(相同的值一组),完成后,对每一组数据使用聚合函数,聚合函数经常和分组查询一起使用。

语法格式:select  列名/聚合函数

                from  表名

                where  条件

            group by 列名

          order by 列名/聚合函数 asc/desc;

案例:查询emp表中,部门的编号,部门人数,部门工资总和

     select deptno,count(*),sum(sal)

         from emp

         group by deptno;

练习:查询student表中,学员性别以及每个性别的人数

   select sex, count(*) from student  group by sex;

案例:查询emp 表中,工资大于1000的部门编码,部门最高工资,部门的平均工资

(标注数字是运行步骤)

    select deptno, max(sal),avg(sal)   --------4

    from emp    -----1

    where  sal > 1000   ----------2

    group by deptno;  -------3

练习:查询emp表中,工资在1000~5000之间的员工的职位,职位最高工资,职位的平均工资

 select job,max(sal),avg(sal)

 from emp

 where sal between 1000 and 5000

 group by job;

练习:查询 emp表中,姓名不包含字母k的,部门的编号,部门的人数,部门的最高工资,根据部门人数降序排列

select  deptno 部门编号,count(*) 部门人数,max(sal)最高工资

from emp

where not ename like '%K%' 

group by deptno

order by count(*) desc;

 5.12.10having语句

说明:在分组查询完成后,对数据再次进行过滤(筛选),使用having语句,having语句必须和group by 一起使用

(标注数字是执行步骤)

语法格式:

select  列名/聚合函数  ---------6

from 表名  ------1

where 条件  ---------2

group by 列名    ---------3

having 条件      ---------4

order by 列名/聚合函数  asc/desc;   --------5

  • where  和 having区别:

    where 是对整张表的数据进行过滤,而且可以单独使用。

    having是对分组后的数据进行过滤,而且必须和group by一起使用。

    where ---->真实的列    having--->虚拟的

说明:where 后面不可以跟聚合函数

案例:查询emp表中,部门的平均工资大于2000的,部门编号,部门的人数,部门的平均工资

  select deptno,count(*),avg(sal)

  from emp

  group by deptno

  having avg(sal)>2000;

练习:练习emp表中,工资大于1500的编号,部门工资总和,部门最高工资,要求部门工资最高工资大于等于3000

select deptno,sum(sal),max(sal)

from emp

where sal>1500

group by deptno

having max(sal)>=3000;

练习:查询student 表中,分数大于70分的,运动类型(type),该类型的人数,要求人数大于3 人

select type, count(*)

from student

where score > 70

group by type

 having count(*) > 3;

5.12.11嵌套查询(子查询)

 说明一条SQL语句内部又包含一条SQL语句

案例:查询emp 表中,工资大于平均工资的,员工的编号,姓名,职位,工资

select empno,ename,job,sal

 from emp

 where sal > 平均工资

1)首先求出表的平均工资  select avg(sal ) from emp;

2)合成

select empno,ename,job,sal

 from emp

 where sal > (select avg(sal ) from emp);

练习:查询emp表中,工资大于姓名的是CLARK的,工资,员工的编号,姓名,职位,工资

 select empno,ename,job,sal

 from emp

 where  sal  > 姓名是CLARK的工资;

1)求出CLARK的工资

select  sal  from emp where ename='CLARK';

2)合成

 select empno,ename,job,sal

 from emp

 where  sal  >  (select  sal  from emp where ename='CLARK');

练习:查询emp表中,和姓名是james是同一个部门的,员工的编号,姓名,职位,工资,部门编号(deptno)

1)james 的部门编号

select  deptno from emp  where ename='james';

2)合成

select empno,ename,job,sal,deptno

from emp

where deptno = (select deptno from emp where ename='james');

练习:查询emp表中,和姓名是james是同一个部门的查询其他,员工的编号,姓名,职位,工资,部门编号(deptno)

理解:

select empno,ename,job,sal,deptno

from emp

where deptno =30 and ename !='james';

理解后:

select empno,ename,job,sal,deptno

from emp

where deptno = (select  deptno from emp where ename='james') and  ename!='james';

5.12.12 MySQL函数

1)常用数值处理函数

  (1)round(数值,位数) 四舍五入函数

如果位数>0,小数点后,就保留几位小数,如果位数=0,不保留小数,

如果位数<0,小数点前几位进行四舍五入

select round(36.628,2);   --36.63

select round(36.628,1);   --36.6

select round(36.628,0);   --37

select round(36.628,-1);   --40

select round(36.628,-2);   ---0

(2)truncate(数值,位数) 截取函数
如果位数>0,小数点后,就保留几位小数,如果位数=0,不保留小数,

如果位数<0,舍弃小数点之前第几位

select  truncate(36.628,2);   --36.62

select  truncate(36.628,1);   --36.6

select  truncate(36.628,0);   --36

select  truncate(36.628,-1);   --30   36-6 =30

select  truncate(36.628,-2);   --0

(3)rand() 随机函数

 select rand();  --无限接近于1;

 select rand()*10;

 select rand(3);  --固定

(4) sqrt(n) 平方根函数

  select sqrt(9);  --3

  select sqrt(2);  --1.414...

  select sqrt(3);  --1.732...

(5)mod(n,m)  求余数

select mod(10,3);  --1 除数10,被除数3  余数1

2)常用字符处理函数 

(1)常用字符处理函数

(1)length(列名/字符串)统计字符串的长度(个数)

select length('大大欢迎您');

 select length('abcdef');

案例:查询emp表中,员工的姓名以及姓名的长度

      select  ename,length(ename) from emp;

 (2)trim(列名/ 字符串)去掉列值/字符串2端的空格 

   用处:字符串拼接

   select trim(' 大内  欢  迎您  ');  结果是:去掉两端空格 中间的' 欢 '没有去空格

(3) --重点 substring(参数1,参数2,参数3) 字符串截取函数

参数1:列名或者字符串 

参数2:从哪里开始截取

如果是正数,表示从正数第几个开始,如果是负数,表示从倒数第几个开始

参数3:截取长度(个数)从左向右数

案例: 截取字符串 helloword

select substring('helloword',5,3);

select substring('helloword',-5,4); 

练习;查询emp表中,员工姓名的以及姓名最后2个字母(面试题 2种)

           select ename,substring(ename, -2,2) from emp;

           倒数第2个位=length(ename)-1

    select ename,substring(ename,length(ename)-1,2)from emp;

(4)reverse(字符串/列名) 字符串逆序函数

   select reverse('上海自来水');

练习:查询emp表中,员工的姓名以及姓名的逆序

select ename ,reverse(ename) from emp;

(5)--重点 concat(列名/字符串,列名/字符串........)字符串拼接

 select  concat('问君能有几多愁',',恰是写完用例改需求',',恰是一江春水向东流');

练习:查询emp表中,员工的姓名,职位,以及姓名和职位的拼接

    select ename,job,concat(ename,job) from emp;

3)常用日期处理函数

   (1)curdate() 获取当前系统日期

           select curdate();

          select current_date();

   (2)curtime() 获取当前系统时间

         select curtime();

          select current_time();

(3)sysdate() 获取当前系统日期时间

  select sysdate();

(4)year(列名/sysdate()/字符串)   获取年份

   select year(sysdate());

   select year('2018-01-01');

练习: 查询emp表中,1981年入职的,员工的编号,姓名,职位,入职时间(hiredate)

     select empno,ename,job, hiredate

     from emp

     where year(hiredate) = '1981';

  练习: 查询emp表中,1981-12-03年入职的,员工的编号,姓名,职位,入职时间(hiredate)

select empno,ename,job, hiredate

     from emp

     where hiredate= '1981-12-03';  

(6)month((列名/sysdate()/字符串) 获取月份

  select  month(sysdate());  --8

 select month('2018-10-08');  --10

练习:查询emp表中,12月份入职的,员工的编号,姓名,职位,入职时间(hiredate)

     select empno,ename,job, hiredate

     from emp

     where month(hiredate)='12';

六、约束   

说明:在创建表的时候,对表设置一下规则,只有满足这些规则,才可以操作数据,我们把这些规则叫做约束。

6.1约束的分类

  • 主键约束(primary  key)
  • 唯一约束(unique)
  • 默认值约束(default)
  • 非空约束(not null)

6.2主键约束(primary key)

说明:主键约束就是用来标识表中的记录,使其具有唯一和非空性,

被主键约束所修饰的列,列值不能重复,且不能为空(必须要有数据),一张表中只能有一个主键约束。

6.2.1创建表时,添加主键约束

create  table  work01(

 id  int(4)  primary key,

name  varchar(20),

age  int(3)

);

正确插入:

insert into work01 values(101,'tom',23);

反例: 唯一性

insert into work01 values(101,'james',25);

反例:非空性

insert into work01 values('rose',18);

6.2.2联合(复合)主键

使用主键约束修饰2列或者多列的组合值

create table work02(

 id int(4),

 name varchar(20),

sex char(3),

age int(3),

primary key (id,name)

);

insert into work02 values(101,'rose','女',23);

insert into work02 values(101,'jack','男',25);

insert into work02 values(102,'rose','女' 25);

反例:

insert into work02 values(101,'rose','女',20); -- 全部相同出错

6.2.3修改表时,添加主键约束

语法格式:

alter talbe 表名 add primary key(列名1,列名2,...列名n);

案例: 创建一张表,表名为work03,表中包含的字段有id int(4),name varchar(20),age int(3),address varchar(50),修改表时,对id列添加主键约束

   create table work03(

     id int(4),

         name varchar(20),

      age int(3),

     address varchar(50)

);

alter table work03 add primary key(id);

insert into work03 (id,name,age)values(101,'tros',25);

反例:

insert into work03 (id,name,age)values(101 ,'lili',25);

练习:创建一张表,表名为work04表中包含的字段有id int(4),name varchar(20),age int(3) ,修改表时,对name主键约束

create table work04(

  id int(4),

 name varchar(20),

age int(3)

);

alter table work04 add primary key(name);

6.2.4删除主键约束

语法格式:

alter talbe 表名 drop primary key;

案例:删除 work04 表中主键约束

alter table work04 drop primary key;

练习:删除work03表中的主键约束

alter table work03 drop primary key;

6.3唯一约束(unique)

使用唯一约束,可以修饰一个列或者多个列的组合值,使其列值有唯一性,一张表中可以有多个唯一约束,被唯一约束所修饰的列,可以为null

create table work05(

id int(4) primary key,

name varchar(20) unique,

age int(3)

);

insert into work05 values(101,'初九',12);

insert into work05 (id,age) values(102,12);

反例:

insert into work05 values(102,'初九',12);

6.3.2修改表时,添加唯一约束

语法格式: alter table 表名 add unique(列名1,列名2,......)

案例:创建一张表,表名为work06 表中包含着字段有id int(4) pk,name varchar(20),sex char(3),address varchar(30),email varchar(30),修改表时,对name和address添加唯一约束

create table work06(

id int(4) primary key,

   name varchar(20),

   sex char(3),

   address varchar(30),

   email varchar(30)

);

alter table work06 add unique(name,address);

insert into work06 values(101,'tom','男','北京','tom@163.com');

insert into work06 values(102,'tom ','中','上海 ','tomm@163.com');

insert into work06 values(103,'tomd','女','北京','to@163.com');   

反例:

insert into work06 values(101,'tom ','女','北京','to@163.com');   

练习:修改work表中,对email列添加唯一约束并验证

      alter table work06 add unique(email);

       desc work06;

6.3.3删除唯一约束

语法格式:alter table 表名 drop  index  key_name;

查看key_name值

show keys from 表名

案例:删除work06表中email列上的唯一约束

    show keys from work06\G; --\G格式

    alter table work06 drop index email;

练习:删除 work05表中,name列上的唯一约束并验证

    alter table work05 drop index name;

6.4默认值约束(default)

默认值约束就是,对表中某列,添加默认值约束,当执行插入操作的时候,如果某列没有插入数据,系统会自动将默认值变为列值。

6.4.1创建表时,添加默认值约束

 create table work07(

   id int(4) primary key,

   name varchar(20) unique,

   age int(3)default 18,

   sex char(3)

);

 insert into work07(id,name,sex)values(1,'赵柳','男');

insert into work07(id,name,sex)values(2,'李敏','女');

  select * from work07;

6.4.2修改表时,添加默认值约束

语法格式:alter table 表名 modify 列名 数据类型 default 默认值;

案例:创建一张表,表名为work08,表中包含的字段有id int(4) pk,name varchar(20)uq,age int(3),address varchar(30),修改表时,对address 添加默认值约束

create  table work08(

 id int(4) primary key,

 name varchar(20) unique,

 age int(3),

address varchar(30)

);

alter table work08 modify address varchar(30) default '北京';

insert  into work08 (id,name,age)values(1,'dfggg',23);

练习:修改work07表中,age 列上的默认值约束,修改为20岁

alter table work07 modify age int(3)  default 20;

desc work07;

6.4.3删除默认值约束

语法格式:

alter table 表名 modify 列名 数据类型;

案例: 删除work07表中age列上的默认值约束

alter  table  work07 modify age int(3);

desc work07;

 练习:删除work08表中address列上的默认值约束

alter table work08 modify address varchar(30);

desc work08;

6.5非空约束(not null)

非空约束就是,被非空约束所修饰的列,当执行插入操作的时候,其列值不能为空

6.5.1创建表时,添加非空约束

     create table work09(

     id int(4) primary key,

     name varchar(20) unique,

     age int(3) default 18,

     address varchar(30) not null
);

insert into work09(id,name,age)values(101,'李三',19);

6.5.2修改表时,添加非空约束

语法格式:

alter table 表名 modify 列名 数据类型 not null;

案例:创建一张表,表名为work10,表中包含的字段有:id int(4) pk,name varchar(20),

age int(3),修改表时对name列添加非空约束

 create table work10(

id int(4) primary key,

name varchar(20),

 age int(3)

);

alter table work10 modify name varchar(20) not null;

练习:修改work10 表,对age列添加非空约束

    alter table work10 modify age int(3) not null;

 6.5.3删除非空约束

     语法格式: alter table 表名 modify 列名 数据类型;

    案例:删除work10表中,name 列上的非空约束

         alter table work10 modify name varchar(20);

                     desc work10;

七、视图(view)

视图就是一张虚拟表,可以通过虚拟表可以对数据进行操作。

7.1创建视图

语法格式:

create  view 视图名称

as

查询语句;

案例:创建一个视图,视图名称为V_emp,查询emp表中的全部数据,作为视图结果。

create view v_emp

as

select * from emp;

练习:创建一个视图,v_dept,查询dept 表中全部数据,作为视图结果

    create view v_dept

    as

    select * from dept;

练习:通过视图v_emp,查询工资在1000~5000之间的,员工的编号,姓名,职位,工资,根据工资降序排列

select empno,ename,job,sal from v_emp where sal>1000 and sal<5000

order by sal desc;

   练习:通过视图v_emp,查询员工编号是7521,7369,7654,7566的员工信息

select * from v_emp where empno in(7521,7369,7654,7566);

7.2修改视图

alter view 视图名称 (已存在)

as

查询语句;

案例:修改视图v_emp,查询emp表中,姓名不包含字母N的,员工的编号,姓名,职位,工资作为视图v_emp的结果

alter  view v_emp

as

select empno,ename,job,sal from emp where not ename like '%N%';

练习:修改视图v_dept,查询dept表中,部门编号是10,20,30,50,70的部门信息作为视图v_dept结果

alter view v_dept

as

select * from dept where deptno in(10,20,30,50,70);

7.3使用视图实现对表的操作(权限问题)

7.3.1 插入语句

      insert into v_dept values(80,'web','北京');

      select * from dept;

7.3.2更新语句

     案例:修改视图v_dept,将部门编号是30号的,部门地址(loc)修改为成都

       update v_dept set  loc='成都' where deptno=30;

        select * from v_dept;

7.3.3 删除语句

案例:删除V_dept,视图中,部门编号是20和70号部门信息

     delete from v_dept where deptno in(20,70);

 select * from dept;

7.4删除视图

语法格式:drop view 视图名称;

案例:删除v_emp视图

drop view v_emp;

drop view v_dept;

练习:创建一个视图,视图名称为v_stu,查询student表中全部数据,作为视图结果

     create view v_stu

      as

     select * from student;

     select * from v_stu

练习:修改视图v_stu,查询student表中,分数不再90到100分之间的,学员的编号(sid),学员姓名(sname),分数(score),地址(address)

   alter view v_stu

   as

   select  sid,sname,score,address from student where not score between 90 and 100;

   练习:删除视图v_stu

       drop view v_stu;

百度已收录
分享