数据库笔记

 -- Mysql
 
 -- Oracle
 
第三章  创建和管理数据库，表及索引
-- 创建数据库：
 -- sql语句：
 create database 数据库名;

-- 连接到数据库
 -- Mysql
 use 数据库名;
 -- Oracle ,有需要还可跟个权限链接到数据库
 connect 用户名/密码@服务名 [as sysopera | as sysdba]
 -- 在sql*plus中执行 ：disconect可以关闭连接
-- 删除数据库：
 -- Mysql
 drop database 数据库名;
 -- Oracle
 Oracle中不支持此语句：drop database 数据库名；
 一般使用Oracle配置助手删除数据库 

-- 数据类型
 sql-2003规范中定义了字符串，数字，布尔，日期时间，时间间隔5中通用的类型；其具体实现跟具体数据库有关。
-- 创建和删除表
 create table 表名称;
 drop table 表名称;
-- 创建临时表，临时表比游标性能好
 -- sql*plus:
 create global temporary tmp as select name,phoneNo from friend;
 -- Mysql:
 create temporary table tmp(name varchar(20),age int); 在Mysql中创建临时表与create table语法一样，
 因为Mysql对子查询支持不好，所以临时表在Mysql中特别有用。
-- 复制表
 -- Oracle
 create table myfriend as select * from friend where 1=0;
 -- Mysql
 create table myfriend as select * from friend where 1=0;
 但是注意，通过复制表而创建新表，并不能把表的约定复制过来，可通过alter table来修改表结构，
 如果你只想复制表的结构而不需要内容，就像上面那样加一个条件永远不能为真的条件就可以了。
-- 字段约束
 1.非空约束：name varchar(20) not null;
 2.指定默认值：name varchar(20) default '无名氏' not null; 当插入数据而没有给name赋值时就插入默认值
 3.设置主键约束：(关系型数据库中的每个表都必须有一个主键)，可以在创建表的最后一行加上主键声明：
 create table person
 (
  name varchar(20) not null,
  password varchar(10) not null default '530',
  primary key (name)  //可以创建一个名字：constraint MyPKey primary key (name,password)
 )
 更简单的就是：name varchar(20) primary key,因为Oracle或Mysql中不允许有null值，所以这里就不必有not null 了
 当一个主键被约束后，一个唯一索引自动就被创建了。
 4.设置唯一约束：(unique constraint) 即是被设置的列的所有行值都必须唯一不能重复，但是允许null值，而主键
   不允许null值。
 create table person
 (
  name varchar(20) ,
  password varchar(10) not null default '530' unique,
  primary key (name)
 )
 5.如果是Oracle，则可以使用序列：允许我们生成连续的唯一的整数
   1）create sequence mysequ;
   2）create esequence mysequ increment by 1 start with 1000; 即是初始值为1000，每次增量为1.
 向Oracle中插入数据：
   insert into person(id,name,age) values(mysequ,'wjr',24); 
   insert into person (id,name,age) values(mysequ,'xiaxia',22);
 6.使用自动编号字段(只有Mysql才有，Oracle中没有)
 create table person(
  id int auto_increment primary key;
  name varchar(20) not null,
  age int dufault 100
 )
 当我们插入数据时就应该这样了：
 insert into person (name,age) values ('wjr',26);
 7.外键约束
 create table note
 (
  nname varchar(20) primary key,
  password varchar(10) not null default '530' unique,
  constraint for_key foreign key(nname) references person(pname)
  或：foreign key(nname) references person(pname)
 );
 注意，使用这中外关键约束连接的两个烈必须是相同的（或至少是相似的）数据类型。
 但是但Mysql数据库中有多种类型的表，其中只有InnoDB表类型的支持外键，也就是在Mysql中引用的和被引用的表都必须是该类型
 所以如果我们要在Mysql中使用外键约束，就必须在create table 语句之后加一句：type="InnoDB"
 create table note
 (
  nname varchar(20) primary key,
  password varchar(10) not null default '530' unique,
  foreign key(nname) references person(pname)
 ) type ="InnoDB";

-- 创建和管理索引
     索引可以降低insert，update，delete的操作性能。因为每次执行该类操作时索引结构就需要更新才行。但是索引可以提
 高select语句速度。索引可以唯一也可非唯一。
 
 为Person表中的name列创建一个非唯一索引：
  create index nameIndex on person(name);
 为Person表中的name列创建一个唯一索引：
  create unique index nameIndex on person(name);
 注意唯一索引的列不允许有重复值。
 现在执行诸如下面查询，将使得查询更快：
 select id, name,age from person where name="wzwjr";
 select id, name,age from person where name like "_z%";
 -- 删除索引
 --Oracle中只需指定要删除的索引名即可：
  drop index nameIndex;
 -- mysql中需要指定表明和索引名：
  drop index nameIndex on person;
-- 更改数据库表
 -- 为表添加一个新列：
 alter table person add phone int;  或
 alter table person add (phone int,email varchar(20));
 -- 修改列属性
 alter table person modify email default '没有邮箱'；
 -- 删除列
 alter table person drop phone;

第四章  更改数据

 insert into values  (mysql中into可选)
 update set
 delete from where
 
 在插入数据时，insert可在使用select子句替代values：比如需要把student1中的一些数据插入到student表中去，就可这样：
 insert into person(id,name) select id,name from student1;
 更新：update person set name='xiaxia' where name='wjr';

第五章  获取数据：查询
 
 -- 如何获取当前连接的数据库之外的数据库中的数据呢？？

    mysql中：
  select * from 数据库名.表名称;
    orcale中：
  select * from scott.emp;
  select * from db2admin.employee;
 oracle中使用双引号表示表名或字段名大小写敏感。
 查询时可使用别名：
 select name as 姓名,password as 密码 from person as 账户表;

 -- 查询时可连接字符串

 mysql默认模式是+号作为连接运算符，ANSI模式时与Oracle使用一样的连接运算符：||
 如果连接的列为数字类型的话，就不要使用+号作为连接运算符了，而要使用concat()函数
 select firstname+' '+lastname as name from person;
 select concat('room',roomid) as roomname from room;
 而Oracle中使用标准的ANSI连接运算符“||”,用他不用单行数据类型，他们会自动转换。当mysql也使用ANSI模式运行时，
 任然采用此运算符||。
 
 -- 查询排序

 排序使用order by 列; order by 列 asc;   order by 列 desc;

 -- 查询排除重复数据：使用distinct
 select distinct name from person;
 select distinct name, address from person;
 上句查询有多个字段，但是只要使用了distinct关键字，就是对多个字段组合唯一，而不是某一个字段唯一。
 
 -- 条件查询
 
 where
 where like 百分号 % 和下划线 _
 where and
 where or
 
 where between and
 where not between and
 select id,name,age from person where not between '2003-06-12' and '2003-06-28';

 where in(value1,value2,value3...)
 select * from person where age in(21,22,23,24);
 
 当使用where like语句匹配条件时，mysql中大小写不敏感，而Oracle中大小写敏感：'%man%','%Man%'

 如何匹配一个%号呢？可使用转义字符，但是sql中没有提供转义字符，所以使用时必须使用escape临时定义一个：
 select id,comments from exam where comments like '50p%%' escape 'p'; 即把p定义成转义字符。
 

第六章  数据汇总和分组

 数据汇总大部分与聚合函数有关,而常用的聚合函数有：
  count(), sun(),max(),min(),avg()
 数据分组：1.过滤分组  2.使用having子句

第八章  组合查询

 1.使用子查询：
   子查询就是在其他查询中嵌套查询就叫子查询。也称为内部查询。而包含该子查询的语句叫外部查询。

例如一个客户表Customers和一个订单表Orders，现查询每个客户有多少订单：
select CustomerId,CustomerName,(select count(*) from Orders where Orders.Id=Customer.Id) as NumberOfOrders from Customers.

  上面子查询是在不同表中进行的，因此使用了.号语法。如果子查询和外部查询使用同一张表，这时子查询还是需要加上前缀，只是
 这时一般是加上别名：
select e1.studentId,e1.classId,(select count(*) from enro e2 where e1.classId=e2.class.Id) as othersInclass
 from enro e1 where studentId=5.
 子查询也可用于where语句：
 select name from Course where CourseId=(select courseId from exam where examTime='2003-02-16');
但是子查询结果如果返回多行，就会出错，那如何处理返回多个结果的子查询呢？？
  当子查询需要返回多个结果时可以使用in关键字：例如上面可改为如下：
select name from course where courseid in (select courseid from exam where examtime='2005-08-14');

第九章  数据分组和汇总
 
 使用链接查询：子查询也可以完成不同表之间的查询，但是效率不及链接查询，链接也更为简单方便。
  select table1.column,table2.column from table1,table2;
 上面这种连接效果被称为交叉链接，或者笛卡尔链接。但是SQL-92标准笛卡尔链接是使用 cross join:
  select table1.column,table2.column from table1 cross join table2;
 
 链接查询(join)分类：
 
 ·内链接：（根据每个表中共有的列的值匹配两个表或多个表中的行）
  1.两表相等链接：基于两表相同列值进行比较
  2.多表相等链接：
  3.不等链接：    就是不使用等号=,而使用<,>,<>,!=,>=等比较符
  4.自然链接：
 ·外链接：
  1.左外链接left outer  join  或left join  返回左表的所有行
  2.右外链接right outer join  或right join 返回右表的所有行
  3.全外链接full outer join   或full join  返回左表和右表的所有行
 
 两表相等链接：
  select class.classId,class.time,Room.roomId from Room join class on Room.roomid=Class.classid;
  但是一般为了在不同数据库中能正常执行，一般都使用 inner join，即内链接：
  select class.classId,class.time,Room.roomId from Room inner join class on Room.roomid = class.classid;
 多表相等链接：
  Join on并不是说只能连接2个表，多个表需要连接就在后边再加上Join on就ok了：
  select se.examid,sm.mark,ex.sus,st.name from join student as st on se.stid=st.stid
  join exam as ex on se.exid = ex.exid order by st.name desc;

--  第十一章  存储过程
 存储过程存储在数据库内，应用程序可以调用执行，而且允许声明变量等，比使用一般sql语句功能更强大，更灵活。
 -- 创建存储过程
 create [or replace] procedure 存储过程名
 as
 begin
  存储过程体；
 end;
 replace选项在Oracle中经常用到，如果有相同名称，则替换。如果不指定，将会抛出错误，新存储过程不会被创建。例：
 create or replace procedure InsertStudent(i_studentID in int,i_studentName in varchar)
 as
 begin
  insert into student(studentID,name) values((i_studentID ,int,i_studentName );
 end;
     //in 或者out 或者in out 表示参数类型：输入参数，输出参数或者输入输出参数
 
 --如何使用存储过程
   使用call关键字：
  call InsertStudent(12003,'王江荣');

 -- 删除和修改存储过程
  drop procedure procedureName; alter procedure procedureName;

 --如何使用存储过程变量和参数
 declare
  myvar varchar(30);
  myothervar int;
 begin
 --给变量赋值和使用变量
   myvar :='woshiyike';  //Oracle中使用这种赋值语法，不同数据库赋值语法略有不同。
 end;
 
 -- 在创建过程时，可以为参数设置默认值：
 create or replace procedure InsertStudent(i_studentID in int,i_studentName in varchar default '无名氏')
 as
 begin
  insert into student(studentID,name) values((i_studentID ,int,i_studentName );
 end;

 -- 如何使用存储过程的输出参数呢？在sql*plus中输入如下语句：
 create or replace procedure GetStudentName(i_studentID in int,o_studentName out varchar)
 as
 begin
  select name into o_studentName from student where studentID = i_studentID;
 end;

 -- 条件执行和循环执行，因为不同数据库语法略有不同，所以这里就不详解了。

 -- 游标cursor：有时我们需要循环遍历结果集中的每一行，这时就需要使用游标了。但一般不是用它，因为游标性能很差。

 -- 错误异常处理

-- 第十二章  事务

 原子性
 一致性
 隔离型  事务提交之前，事务中的操作是独立或者独立于其他事务执行的，互不影响。
 持久性

 保证数据一致性，一个事务要么全部成功执行commit，要么全部失败rollback。

-- 第十三章  触发器trigger

 触发器是一个特殊的存储过程，它与指定的表格想关联，并且在表中的数据发生改变时能自动执行，或者是某事件激发触发器执行。

 

 

 

SQL语言：

 标准SQL语言分为如下3类：
 ·DML数据操作语言：检索或修改数据
 ·DDL数据定义语言：定义数据结构
 ·DCL数据控制语言：定义数据库权限

-- 下面内容都是针对Oracle数据库的：
 -- Oracle中如何查看当前用户建立了多少张表：
  select * from tab； //tab表就保存了相关信息
 -- 如何查看表结构呢？？
  describe emp; //该命令也可简写：desc emp;

 -- 如何查看表数据呢？？
  select * from 表名;  // emp表为雇员表
 -- 如何设置行宽和每页显示的记录数呢？？
  set linesize 200;
  set pagesize 10;
 -- SQL支持基本算数运算：+ - * /加减乘除
  //注意别名没有使用''号哈，而且as可不写出来，效果一样。
  select ename as 姓名, sal as 工资,sal*12 as 年薪 from emp;
 -- 如何使用连接字符？或者如何把相关列连在一起作为一个列呢？
  select ename||'工作是：'||job as 雇员信息 from emp;
 -- 如何消除重复行？？使用distinct关键字
  select distinct ename from emp;
 -- 怎样处理null空值？例如：查询出所有有奖金的雇员姓名：
  select ename from emp where comm is not null;
   像上面这种is not null也可替换成其他比较运算符：
   =,  >,  <,  >=,  <=,  !=或<>,  between and,and,or,not, in,  like等等 
  
       但是注意，Oracle中查询数据是区分大小写的哦！！！

  select ename,sal from emp where sal between 1000 and 1500;
  select ename sal from emp where ename like '李%';  // 下划线_表示一个字符
  select ename,job from emp where job not in('clear','manager','analysl');
  
 -- 查询排序
  order by 子句必须放在select语句最后，默认asc升序排列，可降序排列：desc
  select ename,sal*12 as ysal from emp order by ename,ysal; // 可以使用别名

 -- 如何进行多表链接查询呢？

   将dept表与emp表连接：
 select empno,ename,job,dname,Loc from emp,demt;      //笛卡尔积连接
   因为上面查询没加条件限制，所以查询结果有m*n条数据。
 select empno,ename,job,dname,loc from emp,dept where emp.empno=dept.deptno; //这种才叫表关联查询
   表名过长可起别名：
 select empno,ename,job,dname,loc from emp as e ,dept as d where emp.empno=dept.deptno;
 -- 当然也可以自身连接，左连接，右连接，自然连接等
 
 -- 交叉连接 cross join：产生笛卡尔积
 select empno,ename,job,dname,Loc from emp cross join demt; //跟之前的简单连接效果基本上是一样的，如下：
 select empno,ename,job,dname,Loc from emp,demt;

 -- 左链接
 select * from emp e,dept d where e.deptno=d.deptno(+);   //这种叫左链接，跟不写+号没什么区别。
 -- 右链接
 select * from emp e,dept d where e.deptno(+)=d.deptno;   //(+)在左边就表示以右边为准，叫右链接。

 其实上面是Oracle中的写法，左右连接标准写法如下：
 select * from emp e left join dept d on e.deptno=d.deptno;
 select * from emp e right join dept d on e.deptno=d.deptno;

 -- 自然链接
 select emp.empno,emo.ename,emp.sal,dept.loc from emp natural join dept;
 使用自然连接就省略了where语句，自然连接会自动进行等值匹配，所以就不用使用where语句了，不然会出现笛卡尔积。
 自然连接使用所有名称和数据类型想匹配的列，而Using子句可以用于指定产生连接的列，也就是指定某个列出来用于关联。
 select emp.empno,emo.ename,emp.sal,dept.loc from emp join dept using(deptno);
 使用using子句就不能与where和group by语句同时使用了。

-- 下面内容都是针对Mysql数据库的：

 -- 启动服务：net start mysql;    net stop mysql;
 -- 登录数据库：mysql -uroot -pmysql;
 -- 如何查看当前建立了多少数据库？？
  show databases;
 -- 如何创建或删除一个数据库
  create database huan;  drop database huan;
 -- 选择你要使用的数据库
  use huan;
 -- 查看正在使用的数据库中的表
  show tables; 
 -- 如何查看表结构呢？？
  describe emp; //跟Oracle中一样这点。

 -- 如何导入或执行已写好的sql脚本文件呢？使用source关键字：
 source f:/my.sql;
 -- 删除表： drop table 表名称；
 -- 如何删除表中数据，而不删除表呢？
  delete from 表名称；

 -- 查看服务器版本和当前日期：
 select version(),current_date,now();
 
 -- 查看用户
 select user();

 

 

--  结束  --