数据库&MySQL

数据库基础

• 常见的数据库模型
  • 层次(树状)模型
  • 网状模型
  • 关系模型
• 关系型数据库
  • 由相互关联的表通过共享属性构建数据的一种方法
  • 一个数据库由若干张表组成
  • 表中的行称为记录,列称为记录的属性
  • 关系型数据库主要通过SQL去定义,管理及查询数据
• RDBMS 关系型数据库的管理系统
  • 对数据库进行管理,并能够进行相关查询的一套软件系统
  • 常见的:Oracle,Microsoft SQL Server,MySQL…

SQL

• 结构化查询语言,是一种用于和RDBMS软件进行交互的语言,可以定义,管理及查询数据
• SQL包含五个部分:
• 数据查询语言:
  • 保留字:SELECT,WHERE,ORDER BY,GROUP BY,HAVING等
• 数据操作语言:
  • 保留字:INSERT,UPDATE,DELETE
• 事务处理语言:
  • BEDIN TRANSACTION，COMMIT和ROLLBACK
• 数据定义语言
  • 保留字:CREATE,DROP等
• 数据控制语言
  • 保留字:GRANT,REVOKE等
• SQL语句

SELECT name
FROM movies
WHERE year=2000;

• 连接到MySQL
• 本地链接
  • 先进入MySQL所在目录,如:D:\xampp\mysal\bin
  • 键入命令mysql -u username -p
• 原程链接
  • 利用PUTTY或SSH Secure Shell
  • 键入命令mysql -u username -p

SQL语句	含义
SHOW DATABASES;	显示服务器上所有的数据库
USE datebase;	打开一个数据库 (其他操作的签提)
SHOW TABLES;	显示当前数据库的所有数据表
DESCRIBE table;	显示指定表的结构

SELECT语句

• SELECT column1,column2,...,columnN FROM table;

• SQL语法扩展

• *在SELECT子句中的用法

  • SELECT * FROM table

• DISTINCT:使SELECT查询不返回重复值

  • SELECT DISTINCT columns FROM table

• 用WHERE子句过滤结果

  • SELECT columns FROM table WHERE predicates

• SQL的逻辑运算符(通常用于WHERE）

运算符	描述
=
>
<
<=
>=
<>	不等于
AND(&&)	与
BETWEEN min AND MAX	在某一特定范围之内
IN(value1,value2…)	检测是否是集合内的某值
LIKE pattern	在WHERE子句中搜索列中的指定模式
NOT(!)	非
OR(||)	或

• 使用了通配符%的LIKE运算符

用法	描述
LIKE ‘text%’	以text开头的所有记录
LIKE’%text’	以text结尾的所有记录
LIKE’%text%’	包含text的所有记录

%:匹配0个或多个字符
_:仅匹配一个字符

• ORDER BY对查询结果进行排序
• LIMIT NUM返回记录的最大数
• LIMIT NUM1,NUM2:NUM1为偏移量,NUM2为返回的记录的最大数

聚合函数

函数	含义
AVG	返回复合条件记录集中表达式的平均值
COUNT	…的计数值
MAX	…的最大值
MIN	…的最小值
SUM	…的求和值

SELECT AVG(rank)FROM movies WHERE year>=2000;
SELECT COUNT(*)FROM movies WHERE rank>=8.8;

• GROUP BY:根据指定规则进行分组

SELECT column(s) FROM table WHERE predicates GROUP BY column;
SELECT year,AVG(rank) average FROM movies WHERE year>=2000 GROUP BY year;
//average为别名

• HAVING的应用
• 对聚合函数结果进行过滤,功能等同与WHERE子句
• WHERE无法识别聚合函数生成的别名，此时只能用HAVING

SELECT　year，AVG(rank)average
from movies
WHERE year>=2000
GROUP BY year 
HAVING average>=8.0;

增改删语句

• INSERT
• 向数据表中添加一行数据,即一条记录

INSERT INTO table[(column1,column2,...,columnN)]
VALUES(value1,value2,...,valueN);

INSERT INTO actors[(id,first_name,last_name,gender)]
VALUES(4545,'Wda','bob','F');

INSERT INTO actors         //默认全部属性
VALUES(4545,'Wda','bob','F');

• UPDATE
• 修改数据表中的数据

UPDATE table
SET column1=value1,column2=value2,...,columnN=valueN
WHERE predicates

UPDATE table
SET column1=value1,column2=value2,...,columnN=valueN
WHERE id=35161

• DELETE
• 从数据库表中删除数据

DELETE FROM table
WHERE conditions

多表查询

• 迪卡尔积
• 设R,S为集合,用R中元素为第一元素 ,S中元素为第二元素构成有序对,所有的这样的有序对组成的集合叫笛卡尔积,记作RxS
• JOIN的用法

SELECT columns
FROM table1
	JOIN table2 ON conditions
	...
	JOIN tableN ON conditions
WHERE conditions

实例1

SELECT*
FROM movies
	join movies_genres ON id = movie_id
WHERE　name="Bob"

实例2

防止出现二义性

1.
SELECT*
FROM actors
   JOIN directors ON actor.first_name=directors.first_name
WHERE actors.gender='F';
2.别名
SELECT*
FROM actors a
   JOIN directors d ON a.first_name=d.first_name
WHERE actors.gender='F';

查询设计

• key数据库中的某一列,该列的值在每一行中都是唯一的

• 主键 primary key

  • 主键必须唯一
  • 不为空
  • 每个表有且仅有一个主键

• 外键foreign key

  • 外键用于与另一张表的关联.能确定另一张表记录的字段,用于保持数据的一致性.比如A表中的一个字段是B表的主键,就是A表的外键
    使用DESCRIBE发现主键
    
    说明id为主键

• 设计查询

  • 哪个表中包含关键数据(FROM)
  • 结果集中需要哪些列(SELECT)
  • 各表如何连接(JOIN)
  • 哪些需要被过滤(WHERE)