第一天

1. 什么是数据库？

   1.1  存储数据仓库，数据存储的一种方式
   1.2  数据库就是一个RDBMS(relationship database management system:关系型数据库管理系统)
         关系型数据库  -->  关系模型  --> 基于一个二维表
   1.3  MySQL, Oracle都输RDBMS
   

2. MySQL的安装： 

3. MySQL的登录:

   3.1 mysql -uroot -p
   3.2 mysql -uroot -ppassword  
   3.3 远程登录:  mysql -uroot -p -h<ip> -P3306
   

4. 创建数据库：

语法:
CREATE  DATABASE  [IF NOT EXISTS] db_name    
[create_specification [, create_specification] ...]   


示例：
创建一个名称为mydb1的数据库。
create database mydb1;


创建一个使用utf-8字符集的mydb2数据库。
create database mydb2 character set utf8;


创建一个使用utf-8字符集，并带校对规则的mydb3数据库。
create database mydb3 character set utf8 collate utf8_general_ci;


查看已经创建的数据库
show databases;


查看mydb1创建的语法:
show create database mydb1;


删除前面创建的mydb1数据库
drop database mydb1;


修改数据库:
alter database mydb1 character set gbk;


在Oracle中，使用命令行工具创建数据库： dbca(database configuration assistant)

5. 创建表

CREATE TABLE table_name
(
field1  datatype,
field2  datatype,
field3  datatype,
)character set 字符集 collate 校对规则


5.1 创建员工表
create table employee
(
eid int,
name varchar(20),
sex  varchar(10),
birthday date,
entry_day date,
job  varchar(20),
salary float,
resume text
);


查看已有的表: show tables;
查看表的结构: show create table employee;
              desc employee;




5.2 char和varchar的区别
create table testchar
(
tchar    char(10),
tvarchar varchar(10)
);
插入数据
insert into testchar values('abcd   ','abcd   ');
查询
select concat(tchar,'*'),concat(tvarchar,'*') from testchar;


concat函数 --> 相当于java: + 


5.3 mysql中表示日期和时间的方式：DATE(日期)/DATETIME(日期 + 时间)
    在oracle中: 只有date, 没有datetime


5.4 修改表： 
*. 增加新列
ALTER TABLE table
ADD   (column datatype [DEFAULT expr]
  [, column datatype]...);


*. 修改一个列
ALTER TABLE table
MODIFY   (column datatype [DEFAULT expr]
  [, column datatype]...);


*. 删除一个列
ALTER TABLE table
DROP      (column);


在上面员工表的基本上增加一个image列。
alter table employee add image blob;


修改job列，使其长度为60。
alter table employee modify job varchar(60);


删除sex列。
alter table employee drop sex;


表名改为user。
Rename table 表名 to 新表名
rename table employee to user;


修改表的字符集为utf-8
alter table <表名> character set utf8;


列名name修改为username
alter table user change column name username varchar(20);


5.5 mysql中删除表: drop table <表名字>
    oracle中删除表: drop table <表名字> [purge];  --> 删除到回收站中

6. CRUD操作(java程序)

6.1 SQL语言的类型
    *. DML(Data Manipulation Language 数据操作语言):  insert update delete select
    *. DDL(Data Definition Language 数据定义语言): create table,alter table,drop table
                                              (oracle): create/drop view(视图),sequece(序列),index(索引),synonym(同义词)
    *. DCL(Data Control Language 数据控制语言): grant(授权) ,revoke(撤销权限)


6.2 插入操作 insert
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);


mysql> desc employee;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| sex        | varchar(4)  | YES  |     | NULL    |       |
| job        | varchar(20) | YES  |     | NULL    |       |
| birthday   | date        | YES  |     | NULL    |       |
| salary     | float       | YES  |     | NULL    |       |
| entry_date | date        | YES  |     | NULL    |       |
| resume     | text        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)


注意： 在SQL中，字符串和日期都看成字符串


示例： 使用insert语句向表中插入四个员工的信息
*. 插入所有的列
insert into employee(id,name,sex,job,birthday,salary,entry_date,resume) 
values(1,'Tom','M','Manager','1998-09-08',5000,'2014-06-05','This is my resume');


insert into employee values(2,'Mary','F','Engineer','1998-09-08',3000,'2014-06-05','This is my resume');


*. 插入某些列
insert into employee(id,name,job,salary) values(3,'Mike','Sale',4000);


*. 插入中文
insert into employee(id,name,job,salary) values(4,'张三','Sale',4000);
关于mysql命令行的编码:
mysql> show variables like 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\ |
+--------------------------+---------------------------------------------------------+
修改编码格式:
set character_set_client=gbk;
set character_set_results=gbk;


补充：一次插入多条记录(oracle子查询)
insert into employee select * from employee;

7. 更新update

语法:
UPDATE tbl_name    
SET col_name1=expr1 [, col_name2=expr2 ...]    
[WHERE where_definition]
注意：对于update和delete，一般都有一个where子句；否则就是操作整张表的数据


示例：
将所有员工薪水修改为5000元。
update employee set salary=5000;


将姓名为’Tom’的员工薪水修改为3000元。
update employee set salary=3000 where name='Tom';


将姓名为’Mary’的员工薪水修改为4000元,job改为Manager。
update employee set salary=4000,job='Manager' where name='Mary';


将Tom的薪水在原有基础上增加1000元。(SQL语句支持算数表达式)
update employee set salary=salary+1000 where name='Tom';


补充：更新Tom的薪水，让Tom的薪水跟Mary的薪水一样
下面的语句可以在oracle中执行:
update employee 
set salary=(select distinct salary from employee where name='Mary')
where name='Tom';

8. 删除操作 delete

语法：
delete from <表名>    
[WHERE where_definition] 

示例：
删除表中名称为’Tom’的记录。
delete from employee where name='Tom';


删除表中所有记录。
delete from employee;


使用truncate删除表中记录。
语法： truncate table <表名>;
作用： 清空表
执行脚本：
mysql: source
oracle: @


delete和truncate区别： 
*. delete: 逐条删除
*. truncate: 先摧毁表，再重建表


在oracle中：详细对比delete和truncate

9. 小结：

9.1 概念： 关系模型，ER图
9.2 常用数据库: oracle mysql db2 ....
9.3 mysql安装和配置
9.4 mysql中root密码的重置
9.5 数据库： create, alter, drop
             use 进入数据库
show
9.6 表 table: create table(列的类型),alter table,drop table(oracle回收站 purge)
9.7 insert
    update
delete
跟：子查询

---

第二天

---

1. 卸载mysql

  *. 控制面板中卸载mysql
  *. 删除mysql目录: my.ini
     #Path to installation directory. All paths are usually resolved relative to this.
 basedir="C:/Program Files/MySQL/MySQL Server 5.5/"


#Path to the database root
 datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"


下午的内容： select，约束(constraint)


2. 基本查询select
语法:
SELECT [DISTINCT] *|{column1, column2. column3..}
FROM table;
*: 代表表的所有列
DISTINCT: 去掉重复记录
          distinct作用范围：后面所有的列


示例：
查询表中所有学生的信息。
select * from student;
select id,name,chinese,english,math,deptname from student;
注意：尽量使用列名方式代替*


查询表中所有学生的姓名和对应的英语成绩。(查询某些具体的列)
select name,english from student;


过滤表中重复系名数据。
select distinct deptname from student;


3. 在select中使用表达式
语法：
SELECT *|{column1｜expression, column2｜expression，..}
FROM table;


示例：
显示所有学生英语分数上加10分特长分。
select name,english,english+10 from student;


统计每个学生的总分。
select name,(math+english+chinese) from student;


使用别名表示学生分数和总分(列的别名)
select name as "姓名",math "数学成绩",english 英语成绩,chinese 语文成绩,(math+english+chinese) 总分 from student;
思考问题：别名中是否有双引号的区别??




4. 使用where子句，进行过滤查询  --> 选择正确的记录
语法：
SELECT *|{column1｜expression, column2｜expression，..}
FROM table
WHERE <condition(s)>;


示例：
查询姓名为“王五”的学生成绩
select name,english,math,chinese
from student
where name='王五';


查询英语成绩大于90分的同学
select *
from student
where english>90;


查询总分大于200分的所有同学(在where也可以使用表达式)
select *
from student
where (math+chinese+english) > 200;


查询计算机系总分大于200分的所有同学(在where也可以使用表达式)
select *
from student
where (math+chinese+english) > 200 and deptname='计算机系';


查询数学分>80，语文分>80的同学
select * from student where math>80 and chinese>80;


and : 同时成立，才成立；只要有一个为false，全为false
or  : 只要有一个为true，全为true


5. 在where子句中经常使用的运算符
*. between ... and: 大于**** and 小于****(含有边界)
   查询英语分数在 80－90之间的同学。
   select name,english from student where english>=80 and english<=90;
   select name,english from student where english between 80 and 90;


*. in： 在集合中
   查询数学分数为89,90,91的同学。
   select name,math from student where math in (89,90,91);
   select name,math from student where math=89 or math=90 or math=91;


*. 模糊查询 like 
   %: 任意长度的任意字符串
   _: 任意的一个字符
   
   查询所有姓李的学生成绩。
   select * from student where name like '李%';
   
   查询所有名字是两个字的学生成绩。
   select * from student where name like '__';
   
   关于模糊查询特殊的用法: 转意 --> 取 _ 和 % 本身  --> oracle中
   insert into student values(8,'Tom_AB',60,61,62,'计算机系');
   查询名字中含有下划线的学生成绩
   select * from student where name like '%_%';
   
6. 排序 order by
语法:
SELECT *|{column1｜expression, column2｜expression，..}
FROM table
WHERE <condition(s)>
ORDER BY <列名，表达式> [desc];


默认：升序
降序: desc


示例：
对数学成绩排序后输出。
select * from student order by math;


对总分排序后输出，然后再按从高到低的顺序输出
select name,math+chinese+english from student order by math+chinese+english desc;


对姓李的学生总成绩排序输出
select name,math+chinese+english 
from student 
where name like '李%'
order by math+chinese+english ;


关于order by的规则:
1. order by 后面 +  列名,表达式,别名,序号
2. 多个列的排序: 先按照第一个列排序，如果第一个列相同，再第二个列排序.
                 order by作用于后面所有的列
desc: 只作用于离他最近的一列
   select *
   from student
   order by math desc,english desc;
   
7. SQL中的函数: 合计函数（聚合函数），日期函数，字符函数，数值函数   
7.1 合计函数（聚合函数） (oracle多行函数)
    *. count --> 求个数
统计一个班级共有多少学生？
select count(*),count(id) from student;

统计数学成绩大于90的学生有多少个？
select count(*) from student where math>90;

统计总分大于250的人数有多少？
select count(*) from student where math+chinese+english > 250;
select count(math+chinese+english) from student where math+chinese+english > 250;

*. sum  --> 求和
统计一个班级数学总成绩？
select sum(math) from student;

统计一个班级语文、英语、数学各科的总成绩
select sum(chinese) 语文总分, sum(english) 英语总分, sum(math) 数学总分 from student;

统计一个班级语文、英语、数学的成绩总和
select sum(math+english+chinese) from student;

统计一个班级语文成绩平均分
select sum(chinese)/count(*) 语文平均分1, avg(chinese) 语文平均分2 from student;

*. max/min
求班级总分最高分和最低分（数值范围在统计中特别有用）
    select max(math+chinese+english) 最高分, min(math+chinese+english) 最低分 from student;

    *. avg  --> 平均值


7.2 日期函数
now(): 返回当前时间 select now();
ADDTIME: 一个小时后
         select now() 现在, addtime(now(),'01:00:00') 一个小时后;
date_add: 两年后： select date_add(now(),INTERVAL 2 year);
          两年前： select date_sub(now(),INTERVAL 2 year);
DATEDIFF :select DATEDIFF (now() ,now());  
YEAR\month\day 年月日: select year(now)) 年, month(now()) 月, day(now()) 日;

8. 字符串相关函数
*. concat : select concat('Hello','  World'); 
            在oracle中，  concat和连接符 ||
             select concat('Hello','  World') from dual;
 select 'Hello'||'  World' from dual;
* instr(a,b): 在a中，查找b；如果找到，返回下标；否则返回0
              select instr('Hello World','ll');


*. LENGTH: 返回字符串的长度
           select length('Hello World');


*. SUBSTRING: 求子串
   *. substring(a,b): 从a中，第b位开始取，取右边所有的字符
                      select substring('Hello World',3);   
   *. substring(a,b,c): 从a中，第b位开始取，取c位
                      select substring('Hello World',3,4);   
*. replace(a,b,c): 用c替换a中b  
                  select replace('Hello World','l','*');
*. strcmp(a,b) 比较a和b的值
                select strcmp('abc','abc'),strcmp('abcd','abc'),strcmp('abc','abcd');
   
9. 数值函数
abs 绝对值:  select abs(1), abs(-1);
mod 求余数:  select mod(1600,300);
least 求最小值: select least(2,10,1,20);


10. group by 子句  --> 分组数据
语法：
SELECT *|{column1｜expression, column2｜expression，..}
FROM table
WHERE <condition(s)>
GROUP BY <列名>
ORDER BY <列名，表达式> [desc];


问题： 按系统计学生的总成绩。


select deptname 系的名字,sum(math+english+chinese) 系的学生总成绩
from student
group by deptname;




11. having子句 --> 过滤分组： 是在group by分组的基础上，过滤结果
语法：
SELECT *|{column1｜expression, column2｜expression，..}
FROM table
WHERE <condition(s)>
GROUP BY <列名>
HAVING <condition(s)>
ORDER BY <列名，表达式> [desc];




where和having的异同：
1. 相同点：过滤记录，把正确的记录选择出来
2. 不同点：where 不需要分组
           having 在group by分组的基础上，过滤结果


示例：
按系统计学生的总成绩，并显示总成绩大于250分的记录
select deptname 系的名字,sum(math+english+chinese) 系的学生总成绩
from student
group by deptname
having sum(math+english+chinese) > 250;




12. 约束(constraint)
12.1 什么是约束?  --> 目的： 保证数据的完整性
12.2 约束的类型(SQL99中定义的类型):
     *. 主键: primary key  --> 每个人身份证号
   隐含了非空 且唯一
    create table testconstraint1
(
   tid int primary key,
name varchar(20)
);
 
*. 非空：not null     --> 每个人的姓名
create table testconstraint2
(
tid int primary key,
name varchar(20) not null
);
 
*. 唯一: unique       --> 每个同学的email
create table testconstraint3
(
tid int primary key,
name varchar(20) not null,
email varchar(40) unique 
);  
 
*. 外键: foreign key  --> 部门和员工
   作用： 约束具有父子关系的表
部门表:
create table dept
(
   deptno int primary key,
dname varchar(20) not null
);
 
员工表:
create table emp
(
  empno int primary key,
  ename varchar(20) not null,
  job   varchar(20) not null,
  salary int ,
  deptno int,
  constraint dept_emp_fk foreign key(deptno) references dept(deptno)
);
 
定义外键的语法：
[constraint   ordersid_FK]  foreign key(当前表的某列) references    被引用表 (id)
  关键字     约束的名字       约束的类型(子表中的列)   引用父表的列  父表(列--> 必须是父表的主键)
  
 
*. check约束(检查性约束)  --> oracle讲
 
mysql中:
*. 主键自动增长 auto_increment --> 不需要维护该列的值
create table testautoincrement
(
  id int primary key auto_increment,
  name varchar(20)
);
insert into testautoincrement(name) values('Tom');
insert into testautoincrement(name) values('Mary');
insert into testautoincrement(name) values('Mike');


oracle中：序列  --> mysql auto_increment
          序列 --> 数组 int[20]

---

1. 多表基础
1.1 表之间的关系：一对多、一对一、多对多
1.2 多表查询: 查询员工信息，要求显示员工号，员工姓名，薪水，部门名称
     *. 笛卡尔集
*. 等值连接
*. 不等值连接
*. 外连接
*. 自连接
*. 层次查询


2. MySQL数据的备份和恢复（数据导出和导入）
2.1 数据的导出:   mysqldump
语法：  mysqldump  -uroot -p {数据库的名字}  > {路径}
注意: mysqldump 是一个windows命令


2.2 数据的导入:  source
语法: source **.sql
注意: *. source是一个mysql命令
      *. 在导入之前，要先建库


2.3 Oracle中备份和恢复
2.3.1 数据的导入和导出--> 只能导数据:
      *. 导出: exp
 *. 导入: imp
 注意：导出文件是一个.dmp文件，是一个二进制文件


2.3.2 RMAN(Recovery Manager 恢复管理器)
       *. 是Oracle提供的命令行工具
  *. 登录:  c:\>rman target /
 
3. MySQL图形工具


4. MySQL用户的管理
4.1 目的： 尽量避免使用管理员进行操作,需要建立普通用户
4.2 表:  mysql库---> user表 --> 保存数据库中用户的信息
4.3 用户的管理
    *. 创建用户: 
   语法: CREATE USER 用户名[@地址] IDENTIFIED BY '密码';
示例： 创建一个名叫itcast，密码是password的用户
     create user itcast identified by 'password';
    *. 删除用户: drop user <用户名>;
*. 给用户授权:
   语法： GRANT 权限1, … , 权限n ON 数据库.* TO 用户名[@IP]
示例1： 授予itcast用户访问mydemo数据库中testautoincrement的权限
                grant select on mydemo.testautoincrement to itcast;

        示例2： 授予itcast用户访问mydemo数据库中所有表的权限
                grant select on mydemo.* to itcast;


示例3： 授予itcast用户访问mydb2数据库的所有权限
                grant all on mydb2.* to itcast;
*. 撤销权限: 
  语法: REVOKE 权限1, … , 权限n ON 数据库.* FROM 用户名[@IP]
  示例： 撤销itcast用户操作mydb2数据库的所有权限
         revoke all on mydb2.* from itcast;



5. Oracle用户的管理


5.1  用户管理
5.1.1  创建用户:
       *. 用户名
  *. 验证方式
      *. 密码认证
  *. 主机认证 （外部认证）  --> 登录操作系统的用户，必须是数据库的管理员sys用户
     *. 管理员登录:  sqlplus sys/password as sysdba  --> 密码认证
                 sqlplus / as sysdba             --> 主机认证
 
            注意：主机认证优先于密码认证
  *. 全局认证
*. 对用户加锁和解锁
  alter user <用户名> account lock|unlock;

*.  改密码
  alter user <用户名> identified by 新密码;


5.2  权限
5.2.1  系统权限: 针对数据库的行为,比如:  登录数据库，创建表，创建用户
       示例1：授予itcast用户登录的权限
         grant create session to itcast;


5.2.2  对象权限: 针对的是某一张表
       示例：授予itcast用户查询scott用户下emp表的权限
         grant select on scott.emp to itcast;
 
       示例：授予itcast用户更新scott用户下emp表的权限
              grant update on scott.emp to itcast;   


5.3  角色: 方便对用户的权限进行管理




====================================================
SQL> --创建一个名叫itcast 密码是password的用户
SQL> create user itcast identified by password;


用户已创建。


SQL> --注意: 新创建的用户，不具有任何的权限，甚至不能登录
SQL> --授予itcast用户登录的权限
SQL> grant create session to itcast;


授权成功。


SQL> --授予itcast用户查询scott用户下emp表的权限
SQL> grant select on scott.emp to itcast;


授权成功。


SQL> --创建角色hr_mgr 和  hr_clerk
SQL> create role hr_mgr;


角色已创建。


SQL> create role hr_clerk;


角色已创建。


SQL> --授予hr_clerk 查询和更新的权限
SQL> grant select on scott.emp,update on scott.emp to hr_clerk;
grant select on scott.emp,update on scott.emp to hr_clerk
                         *
第 1 行出现错误: 
ORA-00905: 缺失关键字 




SQL> grant select,update on scott.emp to hr_clerk;


授权成功。


SQL> --授予hr_mgr 插入和删除的权限
SQL> grant insert,delete on scott.emp to hr_clerk;


授权成功。


SQL> grant insert,delete on scott.emp to hr_mgr;


授权成功。


SQL> --授予hr_mgr员工的角色hr_clerk;
SQL> grant hr_clerk to hr_mgr;


授权成功。


SQL> /*
SQL> 将角色授予给用户
SQL> grant hr_mgr to Jenny;
SQL> grant hr_clerk to david,rachel;
SQL> */
SQL> spool off