Mysql语法介绍

序言

之前在本科期间，做过一部分的网站开发的工作，当时不可避免的需要使用数据库来存储数据库的数据，当然在本科学过数据库这门学科，个人觉得数据库还是蛮复杂的，不过你们只是用的话，基本上增删改查基本就可以满足我们的日常需要，但是如果你要从事大型数据库的构建工作的话，还是需要好好学习下理论知识的，这里是拿mysql来例子介绍下数据库的相关操作，不过其实虽然不同的数据库类型不用，不过其实sql语言是通用的，不过在这里我只是重温下sql语言，并不做系统的讲解，想仔细了解数据库的同学可以看下《数据库系统概念》以及《SQL必知必会》这两本书，了解下原理。

发展历史及其特点

数据库发展史 ---> 网状数据库--->层次数据库-->关系数据库-->NoSQL数据库
对于Nosql数据库不是很了解，这里主要是介绍关系数据库，NoSQL数据库可以在下次进行介绍

关系型数据库的特点
1.理论基础：集合论和关系代数。
2.具体表象：用二维表（有行和列）组织数据。
3.编程语言：结构化查询语言（SQL

ER模型

其实这个ER模型就是来表征数据库的关系模型的，实现对于数据库设计可是话，其实我觉得这个其实也应该算作是UML建模语言中的一种吧，跟程序建模中的类图很相似，一般的设计步骤就是先设计模型，然后模型ok之后作为概念模型，然后下一步转化为物理模型，然后最后转化为对应的数据库中的表。

ER模型图实例

<annotation>
-实体 - 矩形框
-属性 - 椭圆框
-关系 - 菱形框
-重数 - 1:1（一对一） / 1:N（一对多） / M:N（多对多）

常用命令

#查看服务器版本。

select version();
#查看所有数据库。

show databases;
切换到指定数据库。

use mysql;
#查看数据库下所有表。

show tables;
#获取帮助,即问号+需要知道的函数名称或用法

? contents;
? functions;
? numeric functions;
? round;
? data types;
? longblob;

下面进入增删改查

1.对数据库的操作，这些所有对数据库进行操作的都是SQL语言，在其他的开发类语言里面都是可以调用的,b比如java，php，python，perl等

#首先判断数据库是否存在，
#-- 如果存在名为school的数据库就删除它
drop database if exists school;

#-- 创建名为school的数据库并设置默认的字符集和排序方式
create database school default charset utf8 collate utf8_bin;
#--切换到目标数据库school
use school;

2.对数据库里面的表进行操作
一张数据表里面是必须要索引的，一般而言索引必须是int类型，一般的这个索引会当作是主键，一张表里面必须要有主键，要不这张表是创建不成功的，在此同时，你还可以设置为表设置外键，用来连接其他表，从而使数据库的关系更加明确。示意图如下所示

表之间的联系

下面来介绍下对数据表的操作

#创建一张表
#-- 创建教师表和学生表
create table tb_teacher
(
teaid       int not null comment '工号',#作为主键，其不能是空的，标注不能为空，所以你建立一个表的对象的时候如果出现对应的字段是空的时候，那么就会报错
teaname     varchar(20) not null comment '姓名',
teatitle    varchar(10) default '助教' comment '职称',
collid      int not null comment '所属学院',
primary key (teaid),
foreign key (collid) references tb_college (collid)
);
-- 创建学生表
create table tb_student
(
stuid       int not null comment '学号',
stuname     varchar(20) not null comment '姓名',
stusex      boolean default 1 comment '性别',
stubirth    date not null comment '出生日期',
stuaddr     varchar(255) default '' comment '籍贯',
collid      int not null comment '所属学院',
primary key (stuid),
foreign key (collid) references tb_college (collid)
);
#--向教师表中插入数据（insert into）
insert into tb_teacher (teaid, teaname, teatitle, collid) values 
(1122, '张三丰', '教授', 1),
(1133, '宋远桥', '副教授', 1),
(1144, '杨逍', '副教授', 1),
(2255, '范遥', '副教授', 2),
(3366, '韦一笑', '讲师', 3);
##删除表中的数据
delete from tb_student where teaid=1122;
#查询表中的数据 (select +想要查询显示字段+from+表名称+where+筛选条件)
select * from tb_teacher where teaname like '杨_'; #查询教师里面姓杨，且名字只有两个字的老师的数据
select avg(x) from table# 在select的返回的时候可以用聚合函数进行数据提取
select distinct teanamefrom table; ##在属性前面加distinct表示对数据进行去重
select stusex, count(*) from tb_student group by stusex;#聚合函数+分组，一般的分组会用group by
#-- 查询年龄最大的学生的姓名(子查询/嵌套的查询)，在如果查询组成比较复杂的时候，可以进行嵌套查询
select stuname from tb_student where stubirth=( select min(stubirth) from tb_student );
#左连接进行连表查询，同时也可以进行右连接的，如果不知道左右的话可以直接用join来实现，连表查询的时候这两个表必须要用公共字段，通过on 来就行连接
select stuname, ifnull(total, 0) from tb_student left outer join ( select sid, count(sid) as total from tb_record group by sid ) temp on stuid=sid;

视图

为什么使用视图呢？
使用视图可以获得以下好处：

1.可以将实体数据表隐藏起来，让外部程序无法得知实际的数据结构，让访问者可以使用表的组成部分而不是整个表，降低数据库被攻击的风险。
2.在大多数的情况下视图是只读的（更新视图的操作通常都有诸多的限制），外部程序无法直接透过视图修改数据。
3.重用SQL语句，将高度复杂的查询包装在视图表中，直接访问该视图即可取出需要的数据；也可以将视图视为数据表进行连接查询。
4.视图可以返回与实体数据表不同格式的数据，

创建视图使用视图与删除视图

create view vw_avg_score 
as 
    select stuname, avgscore 
        from 
            tb_student t1, 
            (select sid, round(avg(score), 1) as avgscore from tb_record group by sid) t2 
        where stuid=sid;
####使用视图
select stuname, avgscore from vw_avg_score order by avgscore desc;
####删除视图
drop view vw_avg_score;

最后一个，可以实现将多个sql语句的封装

类似于函数的或类的封装，因为一条sql语句可能智能显示1-2条命令，所以你如果向一次调用很多了sql命令，一条一条的写的话代码冗余性以及时间的复杂度太高了，这时候就需要将查询等操作进行存储，下面是一个存储的例子。

#查询某门课程的最高分、最低分和平均分
delimiter $$

create procedure sp_get_score(courseId int, 
                              out maxScore decimal(4,1), 
                              out minScore decimal(4,1),
                              out avgScore decimal(4,1))
begin
    select max(score) into maxScore from tb_record where cid=courseId;
    select min(score) into minScore from tb_record where cid=courseId;
    select avg(score) into avgScore from tb_record where cid=courseId;
end $$

delimiter ;
##调用存储过程
call sp_get_score(1111, @a, @b, @c);
##删除存储过程
drop procedure sp_get_score;
##获取输出参数的值。
select @a as 最高分, @b as 最低分, @c as 平均分;