正确认识数据库

• MySQL是一个小型关系型数据库管理系统，开发者为瑞典MySQL AB公司。
• 在2008年1月16号被Sun公司收购。而2009年,SUN又被Oracle收购.对于Mysql的前途,没有任何人抱乐观的态度.
• 目前 MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网 站总体拥有成本而选择了MySQL作为网站数据库。
• 数据库服务器为我们提供数据存储服务
• 常用的数据库服务器:
  • 商业:oracle, SQLserver,DB2
  • 开源:MySQL,postgreSQL,SQLite

MySQL的连接

MySQL的连接.png

增删改查

• 增insert语法
  $ insert into tableName(列1, ...... 列n) values(值1, ...... 值n); ：其中(列1, ... ... 列n)允许不写，如果不写，则默认插入所有列
• 改update语法
  $ update userTable set age=8, score=89 where name='lilei'：（userTable(表单名) ；age(列1)=8(新值1), score(列2)=89(新值2),）即改哪张表？需要改哪几列的值？分别改成什么值？在哪些行生效？
• 删delete语法
  $ dele from userTable where expr：（user(表单名)）你要删哪张表的数据？你要删掉哪些行？

select简单使用

$ select * from user：
$ select * from userTable where id=2：
$ select * from userTable where id>=2
$ select id,name from userTable where id>=2：查哪张表的数据？你要选择哪些列来查询？要选择哪些行？

select查询模型（重要部分）

• 列表属于变量，并且可以参与计算
• where是表达式，值为真假
  $ select id,name,age+1 from userTable where 1

• select除了简单使用之外，还有5种句子类型需要了解
  • Where条件查询
  • group by分组
  • having筛选
  • order by排序
  • limit限制结果条数

select之where介绍

• where expression
  • 用法：expression为真，则该行取出
  • 运用场景：各种条件查询场合，如按学号查学生，按价格查商品，按发布时间差新闻等等
    select where常用运算符
• 比较运算符

• 逻辑运算符

** select where匹配 **

• 使用like模糊匹配，
• %通配任意字符
• _通配单一字符
  $ select id,userName form userTable where userName like '李%';：查询userTable下，姓“李”的童鞋
  $ select id,userName,hometown form userTable where hometown like 湖南_：查询userTable下，家乡为湖南省的童鞋，"_"几个字符就画几条线

*** 练习题1 ***

• 把number值处于[20, 29]之间，改为20；把number值处于[30, 39]之间的，改为30

$ select id,floor(number/10)*10 from mian;
*** 练习题2 ***

• 把goods表中商品名为“诺基亚xxxxx”的商品，改名“Nokia”；

  
  
  goodsTable.png
• 查询：$ select goods_id, concet('Nokia',substring(goods_name,4)) from goods where goods_name like '诺基亚%';：
• 修改：$ update goods set goods_name=CONCAT('Nokia', substring(goods_name,4)) where goods_name like '诺基亚%';

group by分组与统计函数

• 作用：把行 按字段 分组
• 语法：group by col1, col2, ...colN
• 运用场合：常见于统计场合，如按栏目计算贴子数，统计每个人的平均成绩等

练习题

• 如上表，计算商品库存总金额
  $ select sum(goods_number * shop_price) from goods;
• 分组统计货品的总价格
  $ select cat_id, avg(shop_price) from goods group by cat_id;

having筛选

• having与where异同点
  • having与where类似，可筛选数据where后的表达式怎么写，having就怎么写
  • where针对表中的列发挥作用，查询数据
  • having针对查询结果中的列发挥作用，筛选数据

练习题

• 如上表，查询商品比市场价所节省的价格，超过两百的不统计
  错误写法：$ select goods_id, goods_name, (market_price - shop_price) as v from goods where v > 200
  正确写法：$ select goods_id, goods_name, (market_price - shop_price) as v from goods where 1 having value > 200

where-having-group综合练习题

• 有如下表格即数据

• 要求：查询出2门及2门以上不及格者的平均成绩
  查看每个人挂科情况：$ select name, score < 60 from result;
  计算每个人的挂科数量：$ select name, sum(score < 60) from result by name;
  同时计算每人的平均分：$ select name, sum(score < 60), avg(score) as v from result group by name;
  利用having筛选挂科2门以上的：$ select name, sum(score < 60) as hung, avg(score) as avera from result group by name having hung >= 2;

order by 排序

• 功能：按一个或多个字段队查询结果进行排序
• 用法：order by col1, col2, col3
• 知识点的运行场合描述：各种排序场合，如新闻按点击量排序，商品按价格排序等
• 排序的几种类型
  • 降序：desc
  • 升序：asc [ 默认是asc ]
  • 多列排序：多个列以 “,” 隔开

练习
$ select goods_id, cat_id, goods_name, shop_price from goods order by desc;

limit 限制取出条目

• limit限制条数：limit [ offset ], N,限制结果取N条
• 用法：limit [ 偏移量 ], 取出条目
• 知识点的运用场景描述：分页应用中最为典型，如第1页取1-20条，第2页取21-40条

$ select something from ...(where) order by ...(someline/somevalues) desc limit ...(number);：配合order by使用
取出上表单（goods）价格最高的前三名商品：$ select goods_id, cat_id, goods_name, shop_price from goods order by shop_price desc limit 0, 3;
跳过几行，去取哪几行：$ select goods_id, cat_id, goods_name, shop_price from goods order by shop_price desc limit 2, 3;

子查询

• 子查询就是在原有的查询语句中，嵌入新的查询，来得到我们想要的结果集，一般根据子查询的嵌入位置分为where型子查询和from型子查询
  where型子查询
• where型子查询即：把内层sql语句查询的结果作为外层sql查询的条件
• 典型语法：$ select *from tableName where colName = (select colName from tbName where ...) {where colName in (select colName from tbname where ..)}
• 使用场景：查询最新的一条商品；查询出某个大栏目下的所有商品

练习
取出上表单(goods)中最新添加的产品：
$ select goods_id, goods_name -> from goods -> where goods_id = (select max(goods_id) -> from goods);
取出上表单(goods)中每组产品中最新的产品：
$ select goods_id, cat_id, goods_name -> from goods -> where goods_id in (select max(goods_id) -> from goods group by cat_id);

from型子查询

• from型子查询：把内存sql语句查询的结果作为临时表供外层sql语句再次查询
• 典型语法：$ select *from (select *from tableName where ...) where ...;
• 使用场景：查询出每个栏目下的最新商品；BBS中查询每个栏目下的最新帖子

练习
查询最新一行商品(以商品编号最大为最新，用子查询实现)：
$ select goods_id, goods_name -> from goods -> where goods_id = ->(select max(good_id) from goods);

** exists型子查询 **

• exists：外层sql查询所查到的行代入内层sql查询，要使内层查询能够成立
• 查询可以与in型子查询互换，但效率要高
• 典型语法：$ select *from tableName where exists (select *from tableName where ...)
• 使用场景：查询出某大栏目下的所有商品

练习
查询出所有商品的栏目：
$ select * from category -> where -> exists(select * from goods where goods.cat_id = category.cat_id);

内连接查询

• 已知有两张表分别为：boyTable和girlTable，hid相同的为情侣，找出两张表有多少对情侣

$ select boy.hid, boy.bname, girl.hid, girl.gname -> from -> boy inner join girl -> on boy.hid = girl.hid;

基本语法：$ select xxx from table1 inner join table2 on table1.xx = table2.xx;

左连接及右连接查询

取出所有商品的商品名 栏目 价格：$ select goods_id, cat_name, goods_name, shop_price -> from -> goods left join category -> on goods.cat_id = cat_id;
取出第4个栏目下的上哦的商品名称 栏目名 与品牌名：$ select goods_name, cat_name, shop_price -> from -> goods left join category -> on goods.cat_id = category.cat_id -> where goods.cat_id = 4;

难度练习题

• 根据给出的表结构按要求写出SQL语句
  -- match赛程表 --

-- Team 参赛队伍表 --

• Math的hostTeamID与guestTeamID都与Team中的teamID关联，
  要求：查出2006-6-1到2006-7-1之间举行的所有比赛，并且用以下形式列出：
  拜仁 2 : 0 不来梅 2016-6-21

$ select *from m;

$ select mid.t1.tname as hname, mres, t2.name as gname, matime -> from -> m inner join t as t1 on m.hid = t1.tid -> left join t as t2 -> on m.gid = t2.tid -> where matime between '2006-06-01' and '2006-07-01';
结果：

union查询

• 把2条或多条sql的查询结果，合并成1歌结果集
• 使用场景：两条语句各自的where条件非常复杂，可以简化成简单条件，在Union
• 局限性条件：Union的语句必须满足1个条件，各语句取出的列数相同列名称未必要一致，列名称会使用第1条sql的列名称为准
• 注意点：使用Union时，完全相等的行，将会被合并，而合并是比较耗时的操作，一般不让Union进行合并，使用union all可以避免合并

*** 练习题 ***
已知两个表单A和表单B，要求合并

要求查询出以下效果

解析过程：
$ select id, sum(num) -> from -> (select *from a -> union all -> select *from b) as tmp -> group by id;

以上内容均属于DML知识点——
DML is Data Manipullation Language statements. Some examples：数据操作语言，SQL中处理数据等操作统称为数据操作语言(使用者的角度80%)

DDL is Data Definition Language statements.Some example：数据定义语言，用于定义和管理SQL数据库中所有的对象的语言(建设者的角度，建表 建库 建试图 等等 15%)下一个知识点

DCL is Data Definition Language statements. Some examples：数据控制语言，用来授予或回收访问数据库的某种特权，并控制数据库操作事务发生的时间及效果，对数据库实行监视等(管理员角度 DBA 5%)

创建表 table

• 建表，其实就是一个画表头的过程，从术语上讲，建表的过程，就是一个声明字段的过程
  $ create table 表名( -> 列1 列类型 [ 列属性 默认值 ], -> 列2 列类型 [ 列属性 默认值 ], -> 列3 列类型 [ 列属性 默认值 ], ... ... -> 列n [ 列属性 默认值 ], );
  engine = 存储引擎
  charset = 字符集

数据类型

• MySQL支持所有标准的SQL数值数据类型，这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECIDION)。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词

整型

• unsigned：无符号，列的值为0开始，不为负
• M zerofill：适合用于 学号 编码 等固定宽度的数字，可以用0填充至固定宽度，M 填充至多宽
• 注意：zerofill属性默认决定列卫unsigned
  $ alert table t2 add sn tinyint(M即填充宽度) zerofill;
$ insert into t2 values(4, 4, 9);

浮点型

• float(M, D)：M是精度总位数，D标度，小数点后面的位数
• float／double区别：float是有精度损失的
• decimal：定点型，更精确

字符串类型

• char：定长类型
• char(M)：M代表宽度，0<=M<=255之间，例：char(10)，则能输入10个字符
• varchar：变长类型
• varchar(M)：M代表宽度，0<=M<=65535 (以ascii字符为例，utf8 22000左右)

• char与varchar的相关实验
  $ create table t1 ( -> n1 char(10), -> n2 varchar(10), )
$ insert into t1 values(' hello ', ' hello ');

$ select concat('!', n1, '!'), concat('!', n2, '!') from t1;

• char型，如果不够M个字节，内部用空格补齐，取出时再把右侧空格删掉

• enum('value1', 'value2', ... ...)：检举型，是定义好 值 就在某几个枚举范围内，只能有一个值的字符串，从值列“value1”，“value2”，..., NULL中或特殊错误值中选出。ENUM列最多可以有65.535个截然不同的值。ENUM值在内部用整数表示
• set('value1', 'value2', ... ...)：一个设置，字符串对象可以有零个或多个值，每个值必须来自列值“value1”， “value2”，...SET列最多可以有64个成员，SET值在内部用整形表示

日期时间类型

• Year年(1字节)，可存【1901～2155年】在insert时，可以简写年的后2位，但是不推荐这样
• Date日期 格式：1992-03-25 范围：1000/01/01 9999/12/31
• Time时间 格式：13:56:23 范围：-838:59:59 -> 838:59:59
• datetime 时期时间：1998-12-31 13:56:23 范围：1000/01/01 00:00:00 -> 9999/12/31 23:59:59
• 时间戳 是从1970-01-01 00:00:00 到当前的秒数，一般存注册时间，商品发布时间等，并不是用datatime存储，而是用时间戳，因为datetime虽然直观但计算不便
  $ create table time ( -> yr year, -> dt date, -> tm time, ->dttm datetime)
$ insert into time (yr) values ('1992');
$ insert into time (dt) values ('1994-11-15');
$ insert into time (tm) values ('18:23:56');
$ insert into time (dttm) values ('2016-10-02 10:00:00');
  结果：

• timestamp：记录当前时间戳
  $ create table currenttime ( -> id int, -> ts timestamp);
$ select *from currenttime;
  结果：

注意：建表的默认值，在建表的时候应该尽量避免NULL的出现

• 列表的默认值为NULL，由于NULL查询不方便，并且NULL的索引效果不高，所以实用中，应该避免列的值为NULL
• 避免NULL的出现——声明NOT NULL

列的删除增加与修改

$ alter table 表名 add 列名 列类型 列属性 ...;：默认在表的最后面增加列
$ alter table 表名 add 列明 列类型 列属性 ... after 列名;：将会出现在指定列后面
$ alter table 表名 drop column 列名;：删除列
$ alter table 表名 change 列名 新列名 列类型：将列名修改为xxx
$ alter table 表名 modify 列名 新属性 ...;：
$ desc 表单名;：查看详情

视图

• view：又被称为虚拟表，view是sql的查询结果
• view的作用：
  • 1.权限控制可以用 如：某几个列，允许用户查询，其他列不允许被别人查询（可以通过视图，开放其中的一列或几列）起到权限控制的作用
  • 2.简化复杂的查询 ，如查询每个栏目下商品的平均价格，并按平均价格排序，查出平均价前3高的栏目
    $ create view priceview select cat_id, avg(shop_price) as average from goods group by cat_id;
$ select *from priceview order by average desc limit 0, 3;
  • 视图是否可以更新，增加或添加？
    • 如果视图的每一行 是与物理表一一对应的，则可以
    • 如果view的行是由物理表多行经过计算得到的结果，view不可以更新的

视图的algorithm（算法）

• 对于简单查询形成的view，在对view查询时，如：where, order等等，可以把建视图语句+查视图的语句===合并成 => 查物理的语句，这种视图的算法叫merge(合并)
• 如果视图的语句本身比较复杂，很难再和查询视图的语句合并，mysql可以限制性视图的创建语句，把结果集形成内存中的临时表，再去查询临时表，这种算法叫temptale
  $ create algorithm = marge view v as seletc goods_id, goods_name from goods;

表／视图管理语句

• 查看所有表：$ show tables;
• 查看表结构：$desc 表名／视图名;
• 查看建表／建视图过程：$ show create table/view;
• 查看表信息：$ show table 表名 status;
  • 单独查看某一张表：$ show table status where name = '表名' \G;
• 删除表／视图：$ drop table/view 表/视图名;
• 改表名：$ rename table oldName to newName;
• 清空表数据：$ truncate;

存储引擎

• 数据库对同样的数据，有着不同的存储方式和管理方式，mysql中，称为存储引擎
  ** 存储引擎与其特点 **

主键与自增

• 主键 primary key此列不重复，能够区分每一行！
• 主键的写法
  $ create table t3 ( -> id int primary key, -> name char(2) );
$ creat table t4 ( -> id int, ->name char(2), -> primary key(id) );
• 主键一般与auto_increment一起出现
• 注意：一张表只能有1列 auto_increment，并且列必须加索引（index／key）

索引概念

• 索引是数据的目录，能快速定位行数据的位置
• 索引提高了查询速度，但是降低了增删改的速度
• 一般在查询频率的列上加，而且在重复度低的列上加效果更好
• 索引的几种类型
  • key：普通索引
  • unique key：唯一索引
    $ create table viplist ( -> name char(10), -> email char(20), -> key name(name), -> unique key email(email));
$ desc viplist;

结果：

• primary key：主键索引
  $ create table viplist ( -> id int, -> name char(10), -> email char(20), -> primary key (id), -> key name(name), -> unique key email(email));
$ desc viplist2;

结果

• fulltext：全文索引（中文环境下，全文索引无效，要分词+索引，一般用第三方解决方案，如sphinx）
  $ create table viplist ( -> name char(10), -> email char(20), -> key name(name), -> unique key email(email));
$ desc viplist2;
• 索引长度：建索引时，可以只索引列的前一部分的内容，比如前10个字符
• 多列索引：就是2列或多列的值，看成一个整体然后建索引
• 冗余索引：就是在某个列上，可能存在多个索引，比如
  $ create table viplist3 ( -> xing char(2), -> ming char(10), -> key xm(xing, ming), -> key m(ming) );

索引操作

• 查看：$ show index from 表名 或$ show create table 表名

• 删除：$ alter table 表名 drop index 索引名 或 $ drop index 索引名 on 表名

• 添加索引：$ alter table 表名 add [ index/unique ] 索引名(列名);

• 添加主键索引：$ alter table 表名 add primary key(列名称);

• 删除逐渐索引：$ alter table 表名 drop primary key;