大数据WEB阶段 （六）MySql详解（一）

MySql（二）

一、概述

1. 什么是数据库
   
   1. 数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，简而言之就是存储数据的仓库。
2. 数据库的分类
   
   1. 层次式数据库、网络式数据库、关系型数据库
   2. 数据库可以按照一定的数据结构存储管理大量的数据及数据与数据之间的关系，它本质上是一种信息管理系统。数据库根据存储采用的数据结构的不同可以分为许多种，其中常见的有层次式数据库、网络式数据库、关系型数据库。其中关系型数据库占据着市场的主流
3. 关系型数据库
   
   1. 关系型数据库是建立在关系模型基础上的数据库。这种定义听起来十分抽象，这里我们不深入讨论什么叫做“关系模型”–大学计算机专业专门有一门课叫“离散数学”专门讨论过关系模型 –，只是简单的表述为 利用表来存储数据，用表和表之间的关系保存数据之间的关系的数据库称为关系型数据库，这个定义不太严谨，但是更好理解。
4. 常见的关系型数据库
   
   1. SqlServer
   2. Oracle
   3. MySql
   4. DB2
   5. 。。。
5. MySql介绍
   
   1. MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL的特点是体积小、速度快、总体拥有成本低，并且开源。
6. MySql数据库的下载、安装、卸载与配置
   
   1. mysql下载地址：https://www.mysql.com/downloads/， 安装和配置步骤详见mysql安装文档。
   2. 安装路径中不要有中文和空格
   3. 默认的端口3306保持默认即可 。
7. MySql相关命令
   
   1. 登录mysql客户端命令： mysql -uroot -proot -hlocalhost;
      
      1. -u 后面的root是用户名 ， 这里使用的是超级管理员root。
      2. -p 后面的root是密码 ， 这是在安装时指定的密码。
      3. -h 后面给出的localhost是服务器主机名 ， 他是可以省略的 ， 例如： mysql -uroot -proot
   2. 退出客户端命令： quit 或者 exit
   3. 扩展： 拖过命令启动 、 停止、 删除服务（必须在c://Windows//System32下以管理员身份运行cmd.exe）
      
      1. 启动： net start 服务名称
      2. 关闭： net stop 服务名称
      3. 删除: sc delete 服务名称
      4. 注意：如果服务名中有空格 ， 则需要在前后加 引号 。
8. MySql常见概念
   
   1. 数据库服务器： 安装的mysql软件就是mysql服务器。
   2. 数据库： 是数据库表的集合，数据库服务器中可以创建多个数据库。
   3. 表： 类似于一个excel表格，用于存储数据。
   4. 表记录： 即表中的一行数据。

二、SQL语言

1. SQL概述
   
   1. SQL，指结构化查询语言，全称是 Structured Query Language。
   2. SQL是操作关系型数据库通用的语言
   3. SQL是非过程性语言
   4. SQL是一个标准，各大数据库厂商都根据该标准提供了实现。
   5. 数据库厂商为了增强数据库的功能，添加了一些非标准的SQL，称之为数据库的“方言”。
2. SQL能干什么？
   
   1. SQL 可以创建、修改、删除数据库，包括查询服务器中所有数据库。
   2. SQL 可以创建、修改、删除数据库表，包括查询数据库中所有的表
   3. SQL 可以在数据库中插入新的记录，或修改、删除已有的记录
   4. SQL可以查询数据库中的数据……

三、利用sql语句来操作数据库

1. 创建数据库

   1. 语法

      create database [if not  exists] db_name [character set charset_name][collate collation_name];
      

   2. 其中charset_name是为 数据库指定默认的字符集
   3. 在不加 if not exists 时 ， 如果被创建的表已经存在则会报错。
   4. Collate是为数据库指定默认的校对规则（校对规则是指在字符集 内用于比较字符的一套规则 ， 可以控制select查询时where条件大小写是否敏感的规则）。

   5. 练习：

      1. 创建一个名称为mydb1的数据库

         create database  mydb1;
         

      2. 创建一个字符集为gbk的数据库mydb2

         create database mydb2 character set gbk;
         

2. 查看数据库

   1. 语法：

      查看所有的数据库
      show databases;
      显示数据库创建语句
      show create datebase mydb2;
      

   2. 练习：

      1. 查看当前数据库服务器中的所有数据库

         show datebases;
         

      2. 查看mydb2的建表语句

         show create datebase mydb2;
         

3. 修改数据库

   1. 注意： 数据路一旦创建成功 ， 名字无法修改 。

   2. 语法：

      alter datebase db_name [character set charset_name][Collate collationn_name];
      

   3. 练习：

      1. 查看服务器中的数据库 ， 并把mydb2的编码改为utf8

         alter datebase mydb2  character set utf8;
         

4. 删除数据库

   1. 语法：

      drop datebase [if exists] db_name
      

   2. 练习：

      1. 删除前面创建的mydb2数据库

         drop datebase mydb2;
         

5. 选择数据库

   1. 语法：

      选择数据库：
      use db_name;
      查询当前是那个数据库：
      select datebase();
      

   2. 在mysql中没有退出数据库的命令 ， 如果想退出当前数据库 ， 那可以进入另一个数据库 ， 直接用use切换到另一个数据库就可以了 。

四、 利用sql语句操作数据库表

1. MySql中常见的数据类型
   
   1. 字符串类型
      
      1. char(n) 定长字符串
         
         1. 无论内容多大， 这个字段都占n个字符空间 。
         2. n的值最大为255 ， 表示最大能存储255个字符 。
         3. 一般用于存储长度固定的字符串 ， 比如：手机号 、 身份证号等效率比较高
      2. varchar(n) 不定长字符串
         
         1. n表示该字段的最大存储量 ， 但该字段实际大小有具体的数据而定 。
         2. n的最大值为65535 （5.0以后的版本）
         3. 一般用于不定长的数据 ， 比如： 用户名 。
         4. 节省空间 ， 但效率较低
   2. 数值类型
      
      1. TINYINT：占用一个字节 ， 相当于java中的byte
      2. SMALLINT： 占用两个字节 ， 相当于java中的short
      3. INT： 占用4个字节 ， 相当于java中的int
      4. BIGINT： 占用8个字节 ， 相当于java中对的long
      5. FLOAT： 占用4个字节 ， 相当于java中的float
      6. DOUBLE： 占用8个字节 ， 相对于java中的double
   3. 大数据类型
      
      1. BLOB： 大二进制类型 ， 可以存进二进制类型的数据 ， 通过这个字段 ， 可以将视频、音频、音乐等数据以二进制的形式存入数据库 。 最大为4GB。
      2. TEXT： 大文本 。 被声明为这种 类型的数据可以保存大量的字符数据 ， 最大4GB 。 注意： TEXT属于mysql的方言， 在其他的数据库中为clob类型 。
   4. 日期类型：
      
      1. DATE： 日期 2015-09-09
      2. TIME ： 时间格式 ‘HH:MM:SS’ 19:19:19
      3. DATETIME ： 日期时间 2016-09-09 09:09:09 注意：年份的范围在1000~9999
      4. TIMESTAMP： 时间戳 2016-09-09 09:09:09 注意： 年份范围 1970~2037
   5. 逻辑型
      
      1. BIT：只能 存储0或1
2. 字段的约束
   
   1. 主键约束： 保证约束列中的值唯一且不能为空 。
      
      1. 添加主键约束（创建表时）： id int primary key ,
      2. 表已经存在时 ， 添加主键 ： alter table tb_name add primary key(col_name);
      3. 设置主键自增： col_name dataType primary key auto_increment;
   2. 唯一约束 ： unique
      
      1. 保证约束列的值必须唯一 ， 即不能重复 。
   3. 非空约束： not null
      
      1. 保证约束列的值不能为空 。
   4. 外键约束（后面详讲）

3. 新增表

   1. 语法：

      create table tb_name(
          field1 datatype,
          field2 datatype,
          field2 datatype,
      )[character set charset_name][collatc 校对规则];
      //field指定列名 ， datatype指定数据类型  []中的内容可选可不选
      

   2. 注意：
      
      1. 创建表时 ， 要根据需要保存的数据创建相应的列， 并根据数据的类型定义相应的列类型 。
      2. 创建表时 ， 一般不需要指定字符集编码和校对规则 ， 和数据库保持一致即可 。

   3. 练习：

      1. 创建 emlopyee表 ， 表中字段如下：

      字段	属性
      id	整型（主键 自增）
      name	字符型
      gender	字符型
      brithday	日期型
      entry_date	日期型
      job	字符型
      salary	浮点型
      resume	大文本型

      建表语句：

          create table emlopyee(
              id int primary key auto_increment,
              name varchar(50)  unipue,
              gender char(1) not null,
              brithday date,
              entry_date date,
              job varchar(50),
              salary double,
              resume text
          );
      

4. 查看表

   1. 语法：

      查看表结构：
      desc tb_name;
      查看当前数据库的所有表
      show tables;
      查看当前表的建表语句
      show  create table tb_name ;
      

5. 修改表

   1. 语法：

      1. 增加列
      alter table tbname add col_name datatype[DEFAULT expr][,add col_name datatype ...];
      2. 修改列
      alter table modify (col_name datatype [DEFAULT  expr][, modify col_name datatype...]);
      3. 删除列
      alter table tb_name drop [COLUMN] col_name;
      4. 修改表名
      alter table tb_name rename to  new_tb_name;
      或
      rename table old_tb_name to  new_tb_name;
      5. 修改列名称
      alter table tb_name  change [column] old_col_name new_col_name datatype ;
      6. 修改列的顺序
      alter table tb_name modify col_name1 datatype after col_name2;
      7. 修改表的字符集
      alter tbale tb_name character set charset_name;
      

   2. 练习：

      在emlopyee的基础上添加一个image列
      alter tbale emlipyee  add image blob;
      修改job列 ， 使其长度为60
      alter table emlopyee modify job varchar(60);
      删除gender列
      alter table emlopyee drop gender;
      表名改为user
      alter table emlopyee rename to  user;
      或    rename table emlopyee to user;
      name列名改为username 
      alter tbale emlopyee change name username varchar(50);
      将image列放在id后面
      alter table emlopyee modify image blob after  id;
      修改表的字符集为GBK
      alter table emlopyee cahracter set gbk;
      

6. 删除表

   1. 语法：

      drop table tb_name;
      

   2. 练习：

      删除emlopyee表
      drop table emlopyee;
      

五、利用sql语句操作数据库表记录

1. Insert:

   1. 语法：

      insert into tb_name[col_name1,col_name1,col_name1...]values(value1[value2...]);
      

   2. 注意：
      
      1. 插入的数据必须 与字段的数据类型相同 。
      2. 数据的大小应该在列的规定范围内
      3. 在values中列出的数据位置必须与被对应列的 排列位置相同 。
      4. 字符串和日期格式的数据要用‘’引起来 。
      5. 如果插入 所有的数据， 可以不写列列表 ， 直接按照表中字段的顺序写值列表 。

   3. 练习：

      向emlopy表中插入三条数据
      insert into employee (id, name, gender, birthday, entry_date, job, salary, resume) values(null, '刘备', '男', '1876-1-1', '1902-1-1', 'CEO', 100000, '很牛!!!');
      
      insert into employee values(null, '张飞', '男' , '1879-1-1', '1905-1-1', '大数据工程师', 20000, '还行吧!');
      
      insert into employee values(null, '小乔', '女' , '1882-1-1', '1906-1-1', '程序员鼓励师', 40000, '美女!');
      

   4. 在mysql中插入中文数据 ， 或查询中文数据时的乱码问题：

      1. 
      2. 乱码原因：客户端发送的数据时GBK格式 ， 而服务端使用的是utf-8编码处理客户端发来的数据 ， 两端使用的编码不一致导致乱码。
      3. 解决方案一：在客户端通过set names gbk；通知服务端使用指定的码表来处理客户端的数据。 这种方式只在当前cmd命令窗口中有效 ， 每次新打开窗口都要设置一次。
      4. 解决方案二：可以通过修改 MySql安装目录下的 my.ini文件（第57行）中 的配置 ， 来制定服务器端使用的码表 。 这种方式一劳永逸 。 注意：修改配置后 ，需要重启mysql服务

      5. 查询数据库的编码和安装目录

         show variables like '%char%';
         

         

2. UPDATE

   1. 语法：

      update tb_name set col_name1=expr1[, col_name2 = expr2....][where where_definition];
      

      1. update语句可用新值更新原有表行中的各列。
      2. set子句指示要修改哪些列和要给予哪些值 。
      3. where子句指定应该更新哪些行 。 如果没有where子句 ， 则默认 更新整个 表 。

   2. 练习：

       ~将所有员工薪水修改为30000元。
      update employee set salary=30000;
      ~将姓名为’貂蝉’的员工薪水修改为11000元。
      update employee set salary=11000 where name='貂蝉';
      ~将张飞的薪水在原有基础上增加1000元。
      update employee set salary=salary+1000 where name=’张飞’;
      注意：mysql不支持+=符号
      

3. DELETE

   1. 语法

      detele from tb_name [where where_definition];
      

      1. where用于筛选要删除的记录 ， 如果没有where子句 ， 则会将所有的表记录删除。
      2. delete语句不能删除某一列的值 ， 只能删除某一行的值。
      3. delete只能 删除表记录 ，而不能删除表本身。

   2. 练习：

      ~删除表中名称为’张飞’的记录。
      delete from employee where name='张飞';
      ~删除表中所有记录。
      delete from employee;
      

4. SELECT

   1. 基本查询

      1. 语法：

         select [DISTINCT]*|{col_name1,col_name2,col_name3,...}  from tb_name;
         

         1. select 指定要查询哪些列的数据 。
         2. column指定列名
         3. *号代表查询所有列
         4. from 指定查询那张表
         5. DISTINCT可选 ， 值显示结果时 ， 是否剔除 重复数据 。

      2. 练习：

         执行下面的SQL，创建exam表并插入数据
         create table exam(
             id int primary key auto_increment,
             name varchar(20) not null,
             chinese double,
             math double,
             english double
         );
         
         insert into exam values(null,'关羽',85,76,70);
         insert into exam values(null,'张飞',70,75,70);
         insert into exam values(null,'赵云',90,65,95);
         insert into exam values(null,'张三丰',85,79,null);
         
         ~查询表中所有学生的信息。
             select * from exam;
         
         ~查询表中所有学生的姓名和对应的语文成绩。
             select name, chinese from exam;
         
         ~过滤表中重复数据。
             select distinct chinese from exam;
         
         ~在所有学生分数上加10分特长分显示。
         select name,chinese+10,math+10,english+10 from exam;
         或者
         select name,chinese+10 as 语文, math+10 as 数学, english+10 英语 from exam;###通过as关键词指定别名, as可以省略不写!
         或者
         select name,chinese+10 '语文', math+10 数学, english+10 英语 from exam;###通过as关键词指定别名, as可以省略不写!
         
         ~统计每个学生的总分。(ifnull(colname, 0))
         select name, chinese+math+english 总分 from exam;
         
         select name, chinese+math+ifnull(english, 0) 总分 from exam;  
         

   2. 使用 where子句的查询

      1. 语法：

         select *|{列名} from  tb_name [where where_definition];
         

      2. 练习：

         ~查询姓名为关羽的学生成绩
             select * from exam where name='关羽';
         
         ~查询英语成绩大于90分的同学
             select * from exam where english>90;
         
         ~查询总分大于230分的所有同学
             select name, chinese+math+ifnull(english, 0) 总分 from exam where chinese+math+ifnull(english, 0)>230;###在where子句中不能使用列别名!!!
         
         ~查询语文分数在 80~100之间的同学。
             select * from exam where chinese>80 and chinese <100;
         或者
             select * from exam where chinese between 80 and 100;#包括80和100
         
         ~查询数学分数为75,76,77的同学。再查询分数不在这个范围内的同学
             select * from exam where math=75 or math=76 or math=77;
             select * from exam where !(math=75 or math=76 or math=77);
             select * from exam where not(math=75 or math=76 or math=77);
         或者
             select * from exam where math in(75, 76, 77);
             select * from exam where math not in(75, 76, 77);
         
         ~查询数学分>70，语文分>80的同学。
             select * from exam where math>70 and chinese>80;   
         

   3. like模糊查询：

      1. 百分号（%）：匹配0个或多个字符 。
      2. 下划线（_）： 只匹配一个字符 。
      3. 示例：

      语句	描述
      where salary like ‘200%’	找出任何以200开头的值 。
      where salary like ‘%200%’	找出任何存在200的值 。
      where salary like ‘_00%’	找出任何第二个位置和第三个位置都为 0的值 。
      where salary like ‘2_%_%’	找出任何以2开头 ， 并且长度 至少为3的值 。
      where salary like ‘%2’	找出任何以2结尾的值 。
      where salary like ‘_2%3’	找出第二个位置为2并且以3结尾的值 。
      where salary like ‘2___3’	找出任何以2开始， 以3结尾的五位数 。

   4. 排序查询

      1. 语法：

         select col_name1,col_name2... from tb_name order by col_name asc|desc;
         

         1. order by 指定排序的列 ， 排序的列既可以是表中的列名 ， 也可以是select中指定的列名 。
         2. Asc ： 升序排序（默认）
         3. Desc ： 降序排列
         4. order by ： 应当位于select语句的结尾 。

      2. 练习：

         ~对数学成绩排序后输出。
                 select math from exam order by math;
         
             ~对总分排序按从高到低的顺序输出
         select name, chinese+math+ifnull(english, 0) 总分 from exam order by 总分 desc;
         
             ~对姓张的学生总分排序输出
         select name, chinese+math+ifnull(english, 0) 总分 from exam where name like '张%' order by 总分;
         

   5. 聚合函数

      1. 语法：

         求否和条件的记录中指定列的记录数
         select count(列名)...  from tb_name [where where_definition];
         求否和条件的记录中指定列的和
         select sum(col_name1,col_nmae,...) from tb_name [where where_definition];
         求否和条件的记录中指定列的平均值
         select avg(col_name1,col_nmae,...) from tb_name [where where_definition];
         求否和条件的记录中指定列的最大值
         select max(col_name1,col_nmae,...) from tb_name [where where_definition];
         求否和条件的记录中指定列的最小值
         select min(col_name1,col_nmae,...) from tb_name [where where_definition];
         

      2. 练习：

         ~统计一个班级共有多少学生？
             select count(*) from exam;
         
         ~统计数学成绩大于75的学生有多少个？
             select count(*) from exam where math>75;
         
         ~统计总分大于230的人数有多少？
             select count(*) from exam where chinese+math+ifnull(english, 0)>230;
         
         ~统计一个班级数学总成绩？
             select sum(math) 数学总成绩 from exam;
         
         ~统计一个班级语文、英语、数学三科成绩的总和
             select sum(math)+sum(chinese)+sum(ifnull(english, 0)) 总成绩 from exam;
         或者
             select sum(chinese+math+ifnull(english, 0)) from exam;
         
         ~统计一个班级英语成绩平均分
             select avg(english) from exam;
         
         ~求一个班级总分平均分？
             select avg(chinese+math+ifnull(english, 0)) from exam;
         
         ~求班级总分最高分和最低分
             select max(chinese+math+ifnull(english, 0)) from exam;
             select min(chinese+math+ifnull(english, 0)) from exam;