第四章 SQL与关系数据库基本操作（上）

第一节 SQL概述

1.SQL是一个综合的，功能强大的且简洁易学的语言
2.SQL的特点：
（1）SQL不是某个特定数据库的专用语言
（2）SQL简单易学
（3）简单，灵活
（4）不区分大小写
3.SQL的组成
sql集数据查询，数据定义，数据操纵和数据控制四大功能与一体。
（1）数据定义语言
（1.1）create 用于创建数据库或数据库对象
（1.2）alter 对数据库或数据库对象进行修改
（2）数据操纵语言
（2.1）select 查询
（2.2）insert 插入
（2.3）update 更新
（2.4）delete 删除
（3）数据控制语言
（3.1）grant : 用于授权
（3.2）revoke：用于收回权限

第二节 MySQL预备知识

1.MySQL使用基础
（1）目前主要的两种架构方式
（1.1）LAMP：linux+apache+mysql+php
（1.2）WAMP：windows+apache+mysql+php
2.Mysql中的SQL
Mysql作为关系型数据库管理系统，遵循SQL标准，提供了数据定义、操纵和控制语言
MySQL的扩展语言
（1）常量
常量是指程序运行过程中值不变化的量，也称为字面值或标量值
（2）变量
变量用于临时存储数据，变量的值可以跟随程序随时变化
（3）运算符
（3.1）算数运算符：加减乘除求模(±*/%)
（3.2）位运算符：& | 等
（3.3）比较运算符：> >= = < <=

第三节 数据定义

关系数据库支持三级模式，其模式，内模式和外模式的基本对象有数据库模式、表、索引、视图等。
1.数据库模式定义
数据库模式的定义包括数据库的创建、选择、修改、删除和查看等。
（1）创建数据库

create databse [db_name]

（2）选择数据库
实现从这个数据到另外一个数据库的跳转

use db_name

（3）修改数据库
用于修改数据库的其他基本属性信息，例如编码方式等

alter database [db_name] ....

（4）删除数据库

drop database [db_name]

（5）查看数据库
查看可用数据库，like关键字用于筛选

show DATABASES { like '%key%'}

2.表定义
（1）创建表

create table [tb_name]
(
  field1 [type] [not null] 
)

创建表的语法和参数众多，不一一介绍
（1.1）临时表与持久表
在某些应用场景的中，用户需要临时存放数据的需求，又不希望永久保存。 这时候就可以创建一个临时表
首选项添加关键字temporary即可

create temporary table [tb_name]

临时表只针对创建它的用户可见，当断开连接时会被自动删除。这意味着两个不同的连接可以使用相同的临时表名称。
（1.2）数据类型
数据类型是指系统中所允许的数据类型。 数据库中每个列均有自己适当的类型，用于限制或允许该列中存储的数据。
MySQL中常用的数据类型有int,varchar,date,time，char等
（3）关键字字auto_increment
创建表时，可以使用auto_increment让一个类型为int的列实现自增。
（4）指定默认值
创建数据库时使用defalt可以为指定列赋予一个默认值
（5）NULL值
NULL值指的是没有值或缺值
3.索引定义
所谓索引，就是表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，因而索引实质上是一张描述索引列与原表中记录行一一对应关系的有序表。
索引是提高数据文件访问效率的有效方法
（1）索引是以文件的形式存储的
（2）索引在提高查询速度的同时，却会降低更新表的速度
索引的类别列表
（1）普通索引：index
（2）唯一性索引：unique
（3）主键：主键是一种唯一性索引
索引的创建
（1）create index
使用create index 在一个已有表上创建索引，但不能创建主键
用法：

create [unique] index [index_name] on [tb_name(col_name1,col_name2,)] 

语法中，使用unique创建唯一性索引

create index test on userinfo(name,age)
create unique index test2 on userinfo(name(20) asc,age)  -- 唯一性约束

其中，col_name有如下用法,这称为索引列的描述
col_name(length) [ asc | desc]
length参数，用于指定使用列的前length个字符来创建索引,使用列的部分字符作为索引，可以减少索引文件的大小，节省磁盘空间。
asc | desc 参数，用于指定索引是按照降序还是升序来排列，默认为asc。
索引创建成功后，可使用 show index from tb_name 命令查看索引

show index from userinfo

当然，索引也可以在创建表时一起被创建

create table test(
t_id varchar(20) not null,
create index index_id(t_id(5) asc)
)

（2）索引的删除
语法： delete index index_name on tb_name

delete index test2 on userinfo

第四节 数据更新

数据更新有3种操作，即插入(insert)，修改(update)和删除(delete)
1.insert
（1）insert…values
Mysql中，使用insert [into] tb_name(col_name1,col_name2,…) values(value1,value2,…),…可以插入单条或多条数据
其中，tb_name为被插入数据的表名，col_name指需要插入数据的列名，如果要向表中全部列插入数据，则可以省略。
尽管省略列名的操作很简单，可是却过度依赖于列的定义次序，如果表结构发生变化则会变得不安全。

insert into userinfo values(3,'shawn',21,'北京市')

（2）insert…set
使用insert …set可以直接为部分列赋值
insert into tb_name set col_name1=value1,col_name2=value2

insert into userinfo set id=6,name='shawn'

（3）insert…select
使用insert…select语句插入子查询数据
insert into tb_name(col_name1,col_name2,…) select col_name1,col_name2,… from tb_name2 where …
例：将表2中id大于6的数据插入到表1中

insert into userinfo(id,name,age) select id,name,age from userinfo2 where id>6

2.delete
Mysql中，使用delete删除一条或多条数据
delet from tb_name where … order by [ asc | desc ] limit count
省略where，则删除表中的全部数据。order by 用于限制删除数据的顺序，limit后表示删除的数据最大量。

delete from userinfo  order by id limit 2

3.update
Mysql中，使用 update tb_name set col1=val1,col2=val2 where …来更新数据

update userinfo set col1=val1,ccol2=val2,...where ... order by col3 desc limit 10

第五节 数据查询

数据查询是SQL语言的核心功能
1.select …from tb_name where …
select基本用法
此语法结构中，select用于输出指定列的数据，from用于指定数据的来源，where用于设置查询条件。
group by 用于分组筛选，having用于指定组的筛选条件，order by用于对数据结果排序。limit用于确定要筛选的行数

select [ all | distinct | distinctrow] from tb_name where …
三个可选参数用于指定是否返回重复行
未指定则默认为all,返回所有数据
若指定 distinct 或者distinctrow,则会消除返回中的重复行

select DISTINCTROW name from userinfo
select DISTINCT name from userinfo

DISTINCT 与 DISTINCTROW 为同义词

2.列的选择与指定
（1）选择指定的列
选择指定表的一个列或多个列作为查询列，多个列使用逗号（,）分割。若查询全部的列，则使用通配符（*）。

select * from userinfo  -- 使用通配符
select id,name,age from userinfo -- 指定多个列

（2）定义并使用列的别名
使用 as 关键字为查询列命名别名。

select id as ids,name as newname from userinfo

（3）替换查询结果集中的数据
在 select 语法中使用 case when… then… else…结构替换原始表的内容

select 
   name,
   age,
   (case sex when 1 then '男' when 0 then '女' else '未知' end ) as sex_str  -- 使用as为该列设置别名
from userinfo

（4）计算列值
可以输出对列值计算后的值

select id,id+100 from userinfo

（5）聚合函数
聚合函数是数据库系统中一类系统内置函数
常用聚合函数如下
（5.1）count 求个数
（5.2）max 求最大值
（5.3）min 求最小值
（5.4）sum 求和
（5.5）avg 求平均值
（5.6）std 求标准值

3.from子句与多表连接查询
若一个查询同时涉及两个或两个以上的表，则称之为多表连接查询
（1）交叉连接
交叉连接，又称笛卡尔积，使用 cross join 来连接两张表
以下两种写法，返回结果一致

SELECT * FROM userinfo cross join power
SELECT * FROM userinfo,power

交叉连接返回的查询结果集的行数，等于两张表中的数据的乘积
（2）内连接
内连接是利用条件表达式的比较运算符来组合两张表中的数据，消除了交叉连接中的某些行。
使用 inner join tb_name on …实现内连接
（3）外连接
内连接只返回满足条件的记录
外连接是首先将两张表分为基表和参考表，然后以基表为依据返回满足和不满足条件的数据
外连接分为左连接和右连接
左连接将左边的表作为基表，右连接将右边的表作为基表。不符合条件的将以 null 显示
语法分别为 left join 和 right join
（4）where 子句与条件查询
（4.1）比较运算符

（4.2）判定范围
MySQl中，判定范围的关键字有 between on 和 in
当条件被限定在某个范围之内时，使用between and

select * from userinfo where age between 5 and 30  -- 查询页5岁到30岁的用户

关键字 in 可以指定一个值得枚举值

select * from userinfo where age in (10,15,20)  -- 查询10岁，15岁和20岁的用户

（4.3）判定范围
使用 is null 或 is not null

select * from userinfo where address is null  -- 查询地址为空的用户
select * from userinfo where address is not null  -- 查询地址不为空的用户

（4.4）子查询
使用select 子句创建子查询，可嵌套在其他select查询中。
（4.4.1）子表查询，子查询返回一个表
（4.4.2）行子查询，子查询返回一行数据
（4.4.3）列子查询，子查询返回一列数据
（4.4.4）标量子查询，仅返回一个值