关于数据库其实我们可以简单的理解为存储货物的一个厂库,里面分别是按照一定的分类存放的物品,然后人们有时会从厂库中拿走或存储一些物品,有时也会更改或增加一些分类

这些物品都分门别类的存放在厂库中,方便人们的查询和存储。

MySQL是一个关系型数据库管理系统,开发者为瑞典MySQL AB公司。目前MySQL被广泛地应用在互联网行业。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多互联网公司选择了MySQL作为后端数据库。2008年MySQL被Sun公司收购,2010年甲骨文成功收购Sun公司。
MySQL数据库的优点:
1、多语言支持:Mysql为C、C++、Python、Java、Perl、PHP、Ruby等多种编程语言提供了API,访问和使用方便。
2、可以移植性好:MySQL是跨平台的。
3、免费开源。
4、高效:MySql的核心程序采用完全的多线程编程。
5、支持大量数据查询和存储:Mysql可以承受大量的并发访问。

由于本人最先接触到的数据库是oracle,在此谈论mysql说的有些命令或知识可能是oracle 上的,mysql可能不适用,见谅

 

首先先介绍下mysql的常用命令:

显示所有数据库:show databases;
•选定默认数据库:use dbname;
•显示默认数据库中所有表:show tables;
•放弃正在输入的命令:\c
•显示命令清单:\h
•退出mysql程序:\q
•查看mysql服务器状态信息:\s

 

mysql目前用的最多的存储引擎就是InnoDB存储引擎

InnoDB给MySQL的表提供了事务、回滚、崩溃修复能力、多版本并发控制的事务安全。在 MySQL从 3.23.34a 开始包含 InnoDB 存储引擎。InnoDB 是 MySQL 上第一个提供外键约束的表引擎。而且InnoDB对事务处理的能力,也是MySQL其他存储引擎所无法与之比拟的。
InnoDB存储引擎的特点
1)支持外键(Foreign Key)
2)支持事务(Transaction):如果某张表主要提供OLTP支持,需要执行大量的增、删、改操作(insert、delete、update语句),出于事务安全方面的考虑,InnoDB存储引擎是更好的选择。

 

SQL(Structured Query Language)语言的全称是结构化查询语言。数据库管理系统通过SQL语言来管理数据库中的数据。
SQL语言分为三个部分:数据定义语言(Data Definition Language,简称为DDL)、数据操作语言(Data Manipulation Language,简称为DML)和数据控制语言(Data Control Language,简称为DCL)。
DDL语句:CREATE、ALTER、DROP
DML语句:update、insert、delete、select
DCL语句:是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句

 

每个数据库建立是来后都会有一个数据库实例,所谓数据库实例就是一个数据库的服务器,在该实例中会有用户,刚刚创建的用户是没有任何权限的,因此,需要dba给该用户授权

当一个用户创建了任意一个数据对象,dbms就会创建一个相应的方案与之对应,且该方案名与应户名相同

用户权限分系统权限和对象权限,系统权限是和数据库管理相关的权限,如create table、create view等,而对象权限是和用户操作数据库相关的权限,如数据库的增删改查。

说到权限就不得不说角色了,角色是一组权限的集合,目的是为了简化对权限的管理,从而达到简单对用户的管理。

 

角色可以包含系统权限,也可以包含对象权限。

oracle中有严格的权限之分,至于mysql暂不清楚故不做深讨~~~

 

下面简单介绍下mysql一些基本命令:

一、数据库增删改查

1、增:有2种方法

  1).使用insert插入单行数据:

         语法:insert [into] <表名> [列名] values <列值>

   例:insert into strdents (姓名,性别,出生日期) values ('张三','男','1990/1/1')

   注意:如果省略表名,将依次插入所有列

2).使用insert,select语句将现有表中的 数据添加到已有的新表中

        语法:insert into <已有的新表> <列名> select <原表列名> from <原表名>

  例:insert into address ('姓名','地址','电子邮件')select name,address,email  from  Strdents 

      注意:查询得到的数据个数、顺序、数据类型等,必须与插入的项保持一致

2、删:有2中方法

1).使用delete删除数据某些数据

    语法:delete from <表名> [where <删除条件>]    

    例:delete from a where name='张三'(删除表a中列值为张三的行)  

        注意:删除整行不是删除单个字段,所以在delete后面不能出现字段名

2).使用truncate table 删除整个表的数据

        语法:truncate table <表名>

    例:truncate table addressList

   注意:删除表的所有行,但表的结构、列、约束、索引等不会被删除;不能用于有外建约束引用的表

 truncate与delete 的区别:

1)delete数据DML语句,其事物可以回归,而truncate是DDL语句,不能回滚;

2)delete只是删除表中数据,如表中有自增长列,不会改变自增长的数值,仍按原数据增长,而truncate会连自增长数值一并删除,重新开始自增长;

3)truncate会释放表的存储空间,而delete 不会,高水位标记(HWM)不会降低。

注:

Drop :删除数据表或数据库,或删除数据表字段,此方法不可逆

 

语法:drop database 数据库名称

三、改  使用update更新修改数据         

    语法:update <表名> set <列名=更新值> [where <更新条件>] 

   例:update addressList set 年龄=18 where 姓名='张三'

   注意:set后面可以紧随多个数据列的更新值(非数字要引号);where子句是可选的(非数字要引号),用来限制条件,如果不选则整个表的所有行都被更新

四、查

1.普通查询

    语法:select <列名> from <表名> [where <查询条件表达试>] [order by <排序的列  

          名>[asc或desc]]

   1).查询所有数据行和列

    例:select * from a

    说明:查询a表中所有行和

   2).查询部分行列--条件查询

    例:select i,j,k   from  a   where f=5

    说明:查询表a中f=5的所有行,并显示i,j,k3列

   3).在查询中使用AS更改列名

    例:select name as 姓名 from a where  gender='男'

    说明:查询a表中性别为男的所有行,显示name列,并将name列改名为(姓名)显示

   4).查询空行

    例:select name from a where email is null

    说明:查询表a中email为空的所有行,并显示name列;SQL语句中用is null或者is not null来判断是否为空行

   5).在查询中使用常量

    例:select name '北京' as 地址 from a

    说明:查询表a,显示name列,并添加地址列,其列值都为'北京'

   6).查询返回限制行数(关键字:limit)

表名 limit 从第几条取,取几条

    例1:select * from students limit 3,5

    说明:查询表students ,显示列表的3-5行,limit 为关键字(oracle 中没有top关键字用rownum替代)

                          select   *   from   a where   rownum<6  

   7).查询排序(关键字:order by , asc , desc)

    例:select * from students  where grade>=60 order by desc

    说明:查询表中成绩大于等于60的所有行,并按降序显示;默认为ASC升序

2.模糊查询

   1).使用like进行模糊查询

    注意:like运算副只用语字符串,

    例:select * from students where name like '赵%'

    说明:查询显示表students中,name字段第一个字为赵的记录

   2).使用between在某个范围内进行查询

    例:select * from students where age between 18 and 20

    说明:查询显示表a中年龄在18到20之间的记录

    3).使用in在列举值内进行查询(in后是多个的数据)

        例:select name from students where address in ('北京','上海','唐山')

   说明:查询表students中address值为北京或者上海或者唐山的记录,显示name字段

3.分组查询

   1).使用group by进行分组查询

    例:select studentID as 学员编号, AVG(score) as 平均成绩  (注释:这里的score是列名)

      from score (注释:这里的score是表名) 

      group by studentID

           2).使用having子句进行分组筛选

    例:select studentID as 学员编号, AVG from score group by studentID having count(score)>1

    说明:接上面例子,显示分组后count(score)>1的行,由于where只能在没有分组时使用,分组后只能使用having来限制条件,

4.多表联接查询

   1).等号联接

    ①在where子句中指定联接条件

    例:select a.name,b.mark from a,b where a.name=b.name

    说明:查询表a和表b中name字段相等的记录,并显示表a中的name字段和表b中的mark字段

    注:上述sql中where后条件a.name=b.name一定要有,否则会出现笛卡尔结果

     2)Left join:左连接, 连接两张表,以左边表的数据匹配右边表中的数据,如果左边表中的数据在右边表中没有,会显示左边表中的数据。

 

  3)Right join:右连接,连接两张表,以右边表的数据匹配左边表中的数据,如果左边表中的数据在左边边表中没有,会显示右边表中的数据。

    select a.name 学生姓名,b.score 学生成绩 from students a right join score b on a.id=b.student_id;

  4)Inner join:内连接,连接两张表,匹配两张表中的数据,和前面两个不同的是只会显示匹配的数据。

    select a.name 学生姓名,b.score 学生成绩 from students a INNER join score b on a.id=b.student_id;

 

二、合并查询

合并多个select的查询结果,可使用集合操作符UNION,UNION ALL,intersect,minus 

1)union查询结果会自动去掉重复项,而union all不会去掉重复项

2)intersect取交集

3)minus 取差集

a1 minus a2 结果取a1中去掉与 a2相同的部分,如没有相同部分则不去掉。

注:mysql中仅支持union与union all查询,不知最新版mysql是否支持其他的合并查询

Select avg(sal),deptno,job from emp group by cube(deptno,job);deptno--先按deptno分组,再按job分组

 

 三、序列

在mysql的一张表中,我们希望有列,假设该列是整形,我们希望当我们添加一条记录的时候,该列值能够自动的增长(比如从1开始增长,每次增长1)

 下面分别说下不同数据库建序列方式:

1)oracle

例:创建一个序列

  create sequence myseq

  Start with 1

  Increment by 1

  minvalue 1

  maxvalue 30000

表示当前序列增加30000,从1开始,每次增加1

  nocache

 

使用:创建一张表

Create table aa (id number primary key,name varchar2(32));

使用:

序列细节说明:

一旦定义了某个序列,你就可以用currval,nextval

Currval:返回sequence的当前值

Nextval:增加sequence的值,然后返回sequence值

比如:

序列名.currval

序列名.nextval

 

什么时候使用序列:

1)不包含子查询、snapshot、view 的select语句

2)Insert语句的子查询中

3)Insert语句的values中

4)Update的set中

序列注意事项:

1)currval总是返回当前sequence的值,但是在第一次nextval初始化之后才能使用currval,否则会出错。一次nextval会增加一次sequence的值,所以如果同一个语句里面使用多个nextval,其值就是不一样的。

2)第一次nextval返回的是初始值,随后的nextval会自动增加你定义的increment by值,然后返回增加后的值

3)如果指定cache值,oracle就可以预先在内存里面放置一些sequence,这样存取的快些,cache里面的取完后,oracle自动再取一组到cache。使用cache或许会跳号,比如数据库突然不正常down掉(shutdown abort),cache中的sequence就会丢失,所以可以在create sequence的时候用nocache防止这种情况

2)

在sql server和MySQL中都是可以在定义表的时候,直接指定自增长

Sqlserver

 Crete table temp1(

Id int primary key identity(1,1),

Name varchar(36));

 

Mysql:

Create table temp1(

Id int primary key c,

Name varchar(36)); 

 

 四、索引

所谓索引可以简单的理解为一本书的目录,通过目录我们能快速的找到我们所要查找的内容

索引分类

1)单列索引

索引名 on 表名(列名)

2)复合索引

索引名 on 表名(列名1,列名2);

创建索引原则:

在大表上建立索引才有意义

在where子句或是连接条件上经常引用的列上建立索引

索引的层次不要超过4层

在逻辑类型字段上,或者值就是固定几种的列上也不要建立索引

mysql编程

使用pl/sql编程的优缺点

使用纯sql语句来操作数据库,有技术缺陷

1、不能模块化编程,为了完成下订单,可能要发出几条sql

2、执行速度低

3、安全性问题

4、浪费带宽

使用pl/sql来编写过程,可以提高效率

缺点:移植性不高

快速入门案例:

  Create procedure prol is

  Begin

  Insert into emp (empno,ename) values(1234,’1234’);

  End;

  \

 

1、在控制台调用

Exec 过程名(参数1,...,参数n)

Sql编程规范:

1、当定义变量时,建议用v_作为前缀v_sal

2、当定义常量时,建议用c_作为前缀c_rate

3、当定义游标时,建议用_cursor作为后缀emp_cursor;

4、当定义例外时,建议用e_做为前缀e_error;

pl/sql块

块(block)是pl/sql的基本程序单元,编写pl/sql程序实际上就是编写pl/sql块。要完成相对简单的应用功能,可能只需编写一个pl/sql块,但如果要想实现复杂的功能,可能需要在一个pl/sql中镶嵌其他的pl/sql块。

pl/sql块有三个部分构成:定义部分、执行部分、例外处理部分:

  Declare

定义部分----定义常量、变量、游标、复杂数据类型*/

  Begin

执行部分----要执行的pl/sql语句和sql语句*/

  Exception

例外处理部分----处理运行的各种错误*/ 

  End;

定义部分是从declare开始的,该部分是可选的,执行部分是从begin开始的,该部分是必须的,例外处理部分是从exception开始的,该部分是可选的

 

异常处理作用:

1、可以捕获异常,并给出明确提示

2、有时可以利用异常,进行业务处理 

过程

过程用于执行特定的操作,当建立过程时,既可以指定输入参数(in),也可以指定输出参数(out)。通过在过程中使用输入参数,可以将数据传递到执行部分;通过使用输出参数,可以将执行部分的数据传递到应用环境,在sql/plus中可以使用create procedure命令来建立过程。

Oracle 的过程可以指定参数是输入的参数还是输出的参数,默认是输入参数。

基本语法:

过程名(变量 in  变量类型……,变量 out 变量类型....)is(as)

定义变量

  Begin

  //执行语句

  End;

 

调用过程的两种方法:

过程名(参数值...)

过程名(参数值...)

函数

函数用于返回特定的数据,当建立函数时,在函数头部必须包含return子句,而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数 

注:这里所说的函数和sum、max等函数是相同概念。 

Create function 函数名(参数1...)

Return 数据类型 is

定义变量;

Begin

执行语句;

End;

 

在sql/plus中调用函数

变量名 变量类型

函数名(参数值...)into :变量名;

变量名

函数和过程的区别

1)函数必须有返回值,而过程可以没有

2)函数和过程在java中调用方式不一样

函数:select 自己的函数名(列) from 表

过程:使用CallableStatement去完成调用

包用于在逻辑上组合过程和函数,它由包规范和包体两部分组成。

1、我们可以使用create package命令来建包

包名 is

过程名(变量名 变量类型...);

函数名(变量名 变量类型...)return 数据类型

  End;

包的规范只包含了过程和函数的说明,但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。

在不同包中可以建立相同名字的过程、函数

 

包体 

建立包体可以使用create package body 命令

基本语法:

Create or replace package body 包名 is

--实现过程

Procedure 过程名(变量名 变量类型...) is 

//定义变量

Begin

//执行语句;

End;

 

--实现函数

Function 函数名(变量名 变量类型...)

Return 数据类型 is

//定义变量;

Begin

//执行语句;

End;

End;

包名必须先定义

注:

在包中声明的变量必须在包体中实现!

在包体中使用的变量必须先在包中定义!

调用包的过程或函数

当调用包的过程或是函数时,在过程和函数前需要带有包名,如果要访问其他方案的包,还需要在包名前加方案名。

Exec 方案名.包名.过程名(参数值...);

Call 方案名.包名.函数名(参数值...);

触发器

触发器是指隐含的执行的存储过程。当定义触发器时,必须要指定触发器的事件和触发器的操作,常用的触发器事件包括insert、update、delete语句,而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器

触发器是非常有用的,可维护数据库的安全和一致性。

mysql数据库的备份

使用mysqldump命令备份

mysqldump命令可以将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。
mysqldump命令的工作原理很简单。它先查出需要备份的表的结构,再在文本文件中生成一个CREATE语句。然后,将表中的所有记录转换成一条INSERT语句。这些CREATE语句和INSERT语句都是还原时使用的。还原数据时就可以使用其中的CREATE语句来创建表。使用其中的INSERT语句来还原数据

Mysqldump常用参数:
•--all-databases , -A 导出全部数据库
•--add-drop-database 每个数据库创建之前添加drop数据库语句
•--no-data, -d 不导出任何数据,只导出数据库表结构。
•--no-create-db, -n只导出数据,而不添加CREATE DATABASE 语句。
•--no-create-info, -t 只导出数据,而不添加CREATE TABLE 语句。

mysql视图

视图可以理解为是一张虚拟的表,它是基于表而存在的,且不占用存储空间,视图中的数据存放在原来的表中。使用视图查询数据时,数据库系统会从原来的表中取出对应的数据。

视图的作用归纳为如下几点:
1.使操作简单化
2.增加数据的安全性
3.提高表的逻辑独立性