SQL高级（SQL语言）

数据库 day 4

回顾

• 分页

  • limit 开始 行数
  • 默认值0
  • limit 5

• 内连接

  • inner join xx on xxxxx

• 左连接

  • left join xx on xxxx

• 右连接

  • right join xx on xxxx

• 自关联
  将一张表 看做两张：
  a1 a2

• 子查询

  • 将一个select 语句的查询结果，给另外的select语句去用
  • 条件中 ： 一个值， 多个值
  • 数据源： 多条记录， 看做表，做表连接。

学习目标：

• 掌握命令行中的数据库操作：使用、查看、创建、删除
• 掌握命令行中的数据表操作：查看、创建、删除
• 掌握命令行中的数据备份和恢复操作
• 了解内置字符串函数功能
• 了解内置数学函数功能
• 了解内置日期函数功能
• 了解视图的作用、特点以及如何使用
• 了解存储过程作用
• 了解事务命令：开启事务、提交事务和回滚事务
• 了解索引的目的
• 了解外键的作用

高级

数据库设计

E-R模型

• E ： 实体： 描述实际的对象，具有相同特征。-- 表
• R ： 联系： 实体之间的关系， 表和表之间的关系
• 属性： 某方面的特性 – 字段

– 联系：

• 一对一：
• 一对多 ：多表进行维护， 多这张表某字段来源于 一这张表。
• 多对多 ： 多对多， 找第三张表， 帮助维护他们关系

命令行客户端

- 连接服务端

• 打开dos 窗口
  1. 找到Mysql的安装路径
     1. 安装mysql - progromfiles/mysql
     2. 安装PHPstudy : d：/phpstudy/mysql
  2. 在mysql的bin目录下， 文件夹输入 cmd
• 命令行输入
  • mysql -uroot -p
  • 输入密码
  • 回车

操作数据库

== ---- 命令行结束要用 ; 结束-----==

• 查看所有的数据库信息
  == show databases; ==

- 选择使用数据库
== use 数据库名称; ==
- use test13

• 查看当前你使用的数据库
  == select database(); ==

• 新建数据库
  • create database 数据库名 charset=utf8;
  • charset: 设置编码格式， utf8 gbk

乱码时设置国标
== set names ‘gbk’; ==

- 删除数据库
== drop database 数据库名称; ==
- drop database test1111；

操作数据表

- 查看当前数据库所有表的信息
-== show tables;==

- 查看表结构的信息
- ==desc 表名; ==
- desc students；

- 查看表的建表语句
- ==show create table 表名; ==
- show create table students;

- 数据库备份与恢复

• 数据库备份

  • CMD用管理员权限打开。

  • 到达Mysql的 bin 目录下

    • cd bin目录的路径

      • PHPSTUDEY：
        • d: 回车
        • cd d:/phpstudy/mysql/bin

    • 回车

  • 输入备份命令

    -== mysqldump -uroot -p 数据库名称 > test1111.sql ==

    • mysqldump -uroot -p test13 >test1111.sql

• 数据库恢复

  • 1. 新建一个数据库 （编码格式）。
    2. 执行恢复的命令
       1. == mysql -uroot -p 新数据库名称 < sql文件名称 ==
          mysql -uroot -p test1111 < test1111.sql

内置函数

字符串函数

• 字符串拼接
  concat(str1,str2,str3);

• 截取部分字符

  • select left(‘hellomorning’,4); – 左边四个
  • select right(‘hellomorning’,4); – 右边四个
  • select substring(‘hellomorning’,2,3); – 从第二个字符找3个。

• 去掉空格

  • ltrim 去掉左边空格
  • rtirm 去掉右边空格

数学函数

• 四舍五入 round(数字，位数)

• 求幂次方 pow(x,y)

• 求随机值 rand()

• 求π PI（）；

• 日期时间函数

  • 获取当前日期
    • Select current_date()
  • 获取当前的时间
    • select current_time()
  • 获取当前的时间+日期
    • select now()
  • 更改日期的样式
    • select dateformat( 日期 ， 样式)

流程控制

case语法

根据不同的值， 给出不同的结果.


-- 根据性别不同进行赋值！
select *,case sex
	when '男' then '帅哥'
	when '女' then  '美女' 
	else '保密' end as res
	
from students;

-- 加上一个姓 孙美女  李帅哥！
select *,case sex
	when '男' then concat(left(name,1), ' 帅哥')
	when '女' then  concat(left(name,1),' 美女')
	else '保密' end as res

自定义函数

语法：

create function 函数名称 （参数列表）returns 数据类型
begin
	sql 语句 (retrun )
end

例：

命令行操作：  delimiter $$
create function rl(str varchar(100)) returns varchar(100)
begin
	return rtrim(ltrim(str));
end

delimiter ;

调用

select rl('   sd   ');

存储过程

存储过程：

• 执行一系列的SQL语句， 完成数据的准备工作， 数据的处理工作。

语法：

delimiter //
create procedure 存储过程()
begin
	sql语句
end
//
delimiter ;

例：

-- 命令行要设置  不是分号运行
delimiter $$
-- 创建存储过程
create procedure proc_st2()
begin
	-- 执行sql语句
	select * from students;
	
end

delimiter ;

调用

-- 调用存储过程
call proc_st

重复的 sql 语句的集合，放到存储过程和函数，方便复用
存储过程和函数都存在服务端
减少网络交互,减少网络访问流量

视图

介绍： 将复杂的查询结果存在服务端， 下次直接使用。

语法：

--1 。 创建视图
create view v_视图名称 as
	select 语句

例：

create view v_sd as
	select s.*,d.deptid as did, d.deptname
	from employees s
	inner join departments d on s.deptid=d.deptid

调用

select * from v_sd;

注意：视图中查询语句返回的结果不能重名的字段

查看视图：查看表会将所有的视图也列出来

show tables;

删除视图

drop view v_sd;

视图优点：

1. 重复查询， 可以重用。
2. 放在服务端。
3. 减少了网络的传输，减少了网络的访问

事务

介绍: 执行一系列的操作， 要么成功， 要么失败恢复初始状态。

- 全部成功

1、开启事务

‘begin;’

2、修改数据的操作，大乔减少5岁

update students set age=age-5 where name='大乔';

3、提交事务

commit

- 全部失败

1、开启事务

begin；

2、修改数据的操作

update students set age=age-5 where name='大乔';

3、回滚事务

rollback

索引

语法：

• 查看索引
  show index from 表名

• 创建索引

  • 方式一：建表时创建索引
    create table create_index(
    id int primary key,
    name varchar(10) unique,
    age int,
    key (age)
    );

    注意事项：
    key(字段名) – 创建索引
    主键 不能重复 – 自带索引
    name unique 不能重复 – 带索引

    • 方式二：对于已经存在的表，添加索引
      create index 索引的名称 on 病名（字段(长度)）
      例
      create index age_index on create_index(age);
      create index name_index on create_index(name(10));
      给某个字段创建了索引，查询时按照那个字段写条件，速度才会快

  • 删除索引：
    drop index 索引名称 on 表名;

索引大大提高了查询速度，同时却会降低更新表的速度

外键

语法：

• 设置外键约束

方式一：创建数据表的时候设置外键约束

create table stu2(
name varchar(10),
class_id int unsigned,
foreign key(class_id) references class(id)
);

方式二：对于已经存在的数据表设置外键约束

-- 给stu表创建一个外键  class_id 来源于 class 表的id
alter table stu add foreign key (class_id) references class(id);

• 查看外键

  – 查看建表语句
  show create table stu;

• 删除外键

  – 删除外键
  alter table stu drop foreign key stu_ibfk_1;

缺点：降低表更新的效率