数据库基础

-- 后面有更新社区版mysql的使用

第一部分：Oracle的安装与卸载

==========安装==========
9i 10g 11g 12c i-网络技术 g-网格技术 c-云计算
-XP3兼容模式、管理员
高级安装
企业版
一般用途
字符集：al32-utf-8
统一口令:oracle
口令管理：解锁scott，默认密码tiger
测试1：CMD命令行输入sqlplus sys/oracle@orcl as sysdba
测试2：http://localhost:5560/isqlplus
==========卸载==========
关闭服务
使用Oracle产品自带工具（Universal Installer）卸载-XP3兼容模式
、管理员
卸装产品
选择内容-方框内打勾-删除-是，等待删除完成即可
打开注册表regedit
删除HKEY_CLASSES_ROOT目录下所有以Ora、Oracle、Orcl或EnumOra为
前缀的键
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE -win10没有
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services -win10没
有
删除自己安装Oracle的目录下的文件后重启即可
卸载可参考：
http://blog.csdn.net/machinecat0898/article/details/77924711
http://www.jb51.net/article/30612.htm
第三方工具：Navicat P
忘记sys密码的处理方法：
\oracle\NETWORK\ADMIN\sqlnet.ora 编辑
SQLNET.AUTHENTICATION_SERVICES= (NTS)
中的NTS为NONE即可用任意密码conn数据库as sysdba;

第二部分：SQL语句的语法规则

==========1 sqlplus的基本命令==========
1 sqlplus 进入sqlplus工具去操作数据库
sqlplus /nolog
sqlplus scott
sqlplus scott/tiger
sqlplus scott/tiger@orcl
sqlplus scott/tiger@192.168.1.254/orcl
sqlplus sys/oracle@orcl as sysdba
...
2 conn 完整格式 connect
conn scott
conn scott/tiger
conn scott/tiger@orcl
conn scott/tiger@192.168.1.254/orcl
conn sys/oracle@orcl as sysdba
3 ? 显示帮助信息
? conn
? desc
4 desc 描述表信息
desc emp
5 disc 断开连接
disc
6 / 加载上一条命令
/
7 set
set auto on/off
set linesize 300
set pagesize 33
8 ed 编辑sql文件内容
ed d:/sql
9 @ 执行sql文件内容
@ d:/sql
10 sho 显示信息
sho auto
sho user
11 netca 配置监听程序
12 host cls 清理屏幕
13 exit 退出
14 -- 注释

==========2 DQL==========数据查询
1 select..from..where..group by..having..order by..desc;
解析顺序：from..where..group by..having..select..order by
dual 虚表
not like % 任意 _一个 escape '\'取消字符转义功能
两个''就是’本身原样"aa"字符串'a'
别名可以用在select\from\order后不能用在where\group后
单行函数 nvl（字段，新值） nvl2(mgr,'有领导'--不为空,'boss'--
为空) concat(a,b)
组函数 max,min,avg,count,sum 用在select、having后，不能在where
后
字符串和数字、日期可以隐式互转显式格式:
to_char (number_value,'L99,999.99')
to_char (date_value,'YYYY-MM-DD') HH12,MI,SS
to_number (char_value,'$99,990.00')
to_number (char_value,'000.00')
to_date (char_value,'yyyy-mm-dd') from dual;
2 || 连接运算符
select empno||emp
3 rownum 伪列
显式结果的行号 <=
4 rowid 伪列
删除重复数据
5 数字、字母（字符串形式'abc'）伪列
select empno,'abc' from emp;
6 where 不能使用分组函数
运算符 /*-+ ><!= not between..and..,in,some,any,all,is not
null,exists,or,and
子查询
7 等值连接
cross join
select * from emp,deptn;//笛卡尔积
natural jon
select * from emp natural join dept;//自动寻找相同字段99
select * from emp,dept where emp.deptno=dept.deptno;//92
left join
select * from emp left join dept using(deptno);//左边为主表
right join
select * from dept right join emp on emp.deptno=dept.deptno;//
右边为主表
//全连接 select * from emp full join dept using(deptno);
8 having 后可以用组函数和子查询来过滤分组***************
select deptno,sum(sal),count(1) from emp group by dempno
having count(1)=
(select min(count(1)) from emp group by deptno);
9 非等值连接
select empno,ename,sal,grade from emp e,salgrade s where e.sal
between losal and hisal;
10 decode(字段，值，新值，【null】) 行列转换题型
理解decode: if 字段=值，执行新值处的表达式，如：
decode(deptno,10,sal=sal+100)
解读：如果在10号部门，则工资加100元

例2：decode(course,'语文',score,null)

解读：如果课程名是语文，则该列值为score，否则为空。（三目运算）
==========3 DML=========数据操纵
1 insert into
//insert into table_name subquery;
insert into table_name select * from emp where empno=30;//表结
构一致，没有as
//insert into table_name (fields_name) values
(fileds_value);//部分字段
insert into emp (empno,empname) values (8899,'小仙');
//insert into table_name values (all_field_value);//全部字段
2 update table_name set field=new_field_value where
update emp set sal=sal*1.5 where deptno=30;
3 delete
delete from table_name;
delete from table_name where ..;

==========4 DDL==========数据定义
1 alter
alter table emp add address varchar(20);
alter table emp drop address ;
alter table emp modify empno(number(20));
alter user scott account unlock;
alter user scott identified by new_password;

alter table emp disable/enable constraint constaint_name;

alter table emp add constraint c_empno check(empno<8000);
alter table emp drop constraint c_empno;

alter table book modify bname varchar2(10) constraint c_bname not null;//modify
alter table book drop constraint c_bname;

select constraint_name from user_constraints;

2 drop
drop table table_name;
drop table emp cascade constraints;
drop view view_name;
drop index index_name;
drop sequence sequence_name;
3 truncate
truncate table table_name;
4 create
create table table_name (field_name field_type(size) 5大约束,
...);
create table table_name as subquery;
create or replace view view_name as subquery with read only;
create index index_name on table_name (fields_name) ;
create sequence sequence_name 增量 n start with s max_n min_n
cycle|nocycle cache n|nocache
5 rename .. to ..
rename table_name to new_table_name;
6 5大约束 constraint
select constraint_name/type from user_constraints where
table_name=emp or where owner='SCOTT';
约束一般在表建立的时候指定完成，尽可能不要去修改表的约束。
primary key
CONSTRAINT "身份号重复了!" PRIMARY KEY(pid),
unique
CONSTRAINT person_name_UK UNIQUE(name),
not null
constraint person_name_nvl not null,
cherk
CONSTRAINT person_age_CK CHECK( age between 0 and 150
),
CONSTRAINT person_sex_CK CHECK( sex in('男','女') )
foreign key
constraint a foreign key(this) references person(pid)
on delete cascade
CONSTRAINT person_book_pid_fk FOREIGN KEY(pid)
References person(pid) ON DELETE CASCADE
==========5 DCL==========数据控制
1 grant
grant 权限 to user_name;
2 revoke
revoke 权限 from user_name;
==========6 事务==========
1 commit
commit;
2 rollback
rollback;
rollback to s1;
3 savapoint
savepoint s1;
==========数据库对象==========
查看当前用记下的所有表、视图、序列及解发器
select table_name from user_tables;

select view_name from user_views;

select sequence_name from user_sequences;
select * from dba_sequences where
sequence_owner='SCOTT';
select trigger_name from user_triggers;
==========视图

create or replace view v_emp as select * from emp ;
drop view v_emp;

==========索引
create index i_emp_name_date on emp(ename, hiredate);
==========序列
CREATE SEQUENCE seq_Name //创建序列名称
[INCREMENT BY n] //增量 n ；若n是负数就递减默
认是1
[START WITH n] //起始值,递增默认是minvalue 递
减是maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}]; //是否分配并存入到内存
中

Create sequence seq_AutoNum
Increment by 1
Start with 10
Maxvalue 999999
Minvalue 1
Nocycle
nocache ;
==========过程
==========触发器

==========3大范式==========
规范的样式
越高的范式冗余越小
6个范式，常用3个：BCNF、1NF、2、3、4、5范式
1NF 字段具有原子性，即不可再分地址（省、市、县...）不符合,主
键合理与业务无关
2NF 字段没有部分依赖（学生学号姓名教师编号教师姓名），分
解表（多对多变一对多）
3NF 字段没有传递依赖（学号姓名班级标号班级名称班级信息）
提取表

////////////MYSQL5.5:统一密码123456
安装过程：
典型安装、OLTP、解除防火墙-打勾、UTF-8、Windows path-打勾
--------------
根目录下 my 文件里可以修改端口、字符集2个地方，bin下有工具
mysql -uroot -p123456
端口：3306
show databases;
use database_name;
show tables;
desc table_name;
////////////JDBC
复制包jar 到项目下，构建Buildpath
mysql.jar下载：http://dev.mysql.com/downloads/connector/j/
oracle.jar路径\oracle\jdbc\lib\ojdbc14.jar
////////////JDBC编程,代码如下：

import java.sql.Driver;//驱动管理接口
import java.sql.DriverManager;//驱动管理类
import java.sql.Connection;//连接接口
import java.sql.Statement;//会话接口
import java.sql.ResultSet;//返回结果集 接口
import java.sql.SQLException;//异常处理类

public class JDBCTest {
    // url 格式 协议：//主机：端口/资源
    //private static String url = "jdbc:oracle:thin:@192.168.1.254:1521:orcl";
     private static String url = "jdbc:mysql://localhost:3306/studb";
    // private static String url =
    // "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=数据库名";

//    private static String user = "scott";
//    private static String password = "tiger";
//    private static String sql = "select * from emp";
    private static String user = "root";
    private static String password = "123456";
    private static String sql = "select * from stuinfo";

    public static void main(String[] args) {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 1 加载 驱动 需要jar包已经配置好
             Class.forName("com.mysql.jdbc.Driver");
            // Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            //Class.forName("oracle.jdbc.driver.OracleDriver");
            System.out.println("加载驱动成功");
            // Class.forName()将对应的驱动类加载到内存中，然后执行内存中的static静态代码段，
            // 代码段中，会创建一个驱动Driver的实例，放入DriverManager中，供DriverManager使用
            // 2 获取 连接
            con = DriverManager.getConnection(url, user, password);
            System.out.println("连接成功");
            // 3 建立会话
            stmt = con.createStatement();
            // 4 发送sql,获取结果
            rs = stmt.executeQuery(sql);
            // 5遍历
            while (rs.next()) {// rs指针 指向第一条记录
                //System.out.println("员工编号：" + rs.getString("empno") + "\t员工姓名" + rs.getString("ename"));
                System.out.println("学生编号：" + rs.getString("id") + "\t学生姓名:" + rs.getString("name")+"\t考试成绩："+rs.getString("score"));
            }
        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动失败");
            e.printStackTrace();
            // 加载失败：1写错了2没有配置BuildPath
        } catch (SQLException e) {
            System.out.println("连接失败");
            e.printStackTrace();
        } finally {// 回收资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

附件1：函数
///////////////函数///////////////////
用来完成某个特定任务的一段代码的集合
内置+自定义
内置：单行+多行（组）函数
单行：字符+数值+日期+转换+其它函数（通用函数）
单行：一行记录被单行处理后得到一个自己的结果
组：多行数据被组函数处理后得到一个结果
////字符函数
nvl
chr
concat
initcap 首字母大写
lower 小写
upper 大写
lpad(A,6,' ') 按6位显示，不够的用空格(可替换)补齐；会失真
rpad 同上，右边加字符
trim 去空格 ltrim rtrim
substr(A，3,5-个数可取负数) 截取，String索引从1或0开始
instr(A,B) B在A 中出现的位置
length 求长度
////数字函数count(empno);
round 四舍五入，取整
round (A,2) 保留2位小数
round (A,-1) 不保留个位数-2 十位数
trunc 不四舍五入截取，其它同上
mod() 取模
ceil 上取整，不小于
floor 下取整
////日期函数
sysdata 系统日期
current_date 当前日期
add_months(A,b) 加b个月
last_days(d) 当月最后一天
months_between（a,b）相差月数
////转换函数,隐式，显示
to_char
//to_char(sysdate,'YYYY"年"-MM-DD HH24:MI:SS') ''可以"原样"显
示
//to_char(sal,'$99,999,99') L本地 0占位

to_date
//to_date('2016年5月24日','yyyy-mm-dd') 字符A转成B的格式的日期
//next_day(sysdate,'星期三') 结果是下周的星期三、、sysdate超过
本周的二
//反之，显示本周的三的日子
to_number
//to_number('1111','格式9999')

////函数可以嵌套使用/////

////分组函数
max() --
min() --任何类型
avg()
sum()
count() //count(*)
//伪列：表中不存在的列
select 1（或者字母） from emp; //1 s是伪列
//使用count时常常使用伪列
///因为字段值为null时，不计数
having 分组过滤
where不能使用分组函数
///空值参与运算
avg(comm) 有津贴的人的平均数
avg(nvl(comm,0)) 所有人的津贴的平均数
除组函数外，select语句中的每个列都要出现在group中

附件2：
并集、大并集、交集、差集

小并集过滤重复 14条结果
select * from emp where deptno in (10,20)
union
select * from emp where deptno in(20,30);
大并集不过滤重复 19条结果
select * from emp where deptno in (10,20)
union all
select * from emp where deptno in(20,30);
交集 5条结果
select * from emp where deptno in (10,20)
intersect
select * from emp where deptno in(20,30);
差集在第一条语句中不在第二条语句中 3条结果
select * from emp where deptno in (10,20)
minus
select * from emp where deptno in(20,30);

EXISTS 是判断是否存在,和in类似,但效率要比in高 -- 这个语法关键是内表和外表是否进行了等值连接

SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')

SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')

这两句效果一样

查询部门名称为 'ACCOUNTING'或 'SALES'的部门编号的员工姓名和部门编号
要求必须使用 exists ( 子查询 ) 实现　
select ename,deptno
FROM EMP
WHERE EXISTS (SELECT * FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND (dname='ACCOUNTING' or dname='SALES'));

================社区版/解压版 mysql的使用

mysql -uroot -p123456

安装解压版MySQL
1、将mysql压缩包解压到盘符的根目录
2、打开cmd命令行，cd到mysql解压的目录中的bin目录
3、将mysql根目录下的my-default.ini文件拷贝一份，重命名为my.ini
并在其中添加下列内容：
[client]
port=3306
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
character_set_server=utf8
4、在cmd中执行一句话：
mysqld --install 会在windows的服务列表中添加MySQL服务
mysqld --install bjsxtmysql 指定服务名称
mysqld --remove 会将windows的服务列表中的MySQL服务删除
mysqld --remove bjsxtmysql 删除指定名称的服务

我们执行mysqld --install就可以了
5、初始化MySQL数据库
mysqld --initialize-insecure --user=mysql
6、启动MySQL数据库
net start mysql
7、登录mysql
mysql -uroot
此时mysql的root账户还没有密码
8、查看数据库字符集编码
show variables like '%character%'

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 | F:\mysql-5.7.12-winx64\share\charsets\ |
+--------------------------+----------------------------------------+
8 rows in set, 1 warning (0.01 sec)

mysql>

9、给root账户设置密码"123456"
登录MySQL之后，执行：
set password for 'root'@'localhost'=password("123456");
10、（1）查看所有数据库信息
show databases;
（2）创建数据库：
create database 数据库名称;
（3）指定使用哪个数据库：
use 数据库名称;
（4）查看数据库中的表：
show tables;
（5）创建数据库表；
mysql> create table tb_user (
-> user_id int primary key auto_increment,
-> user_name varchar(30) not null,
-> user_pwd varchar(64) not null
-> );
Query OK, 0 rows affected (0.09 sec)

mysql>
（7）查看创建表的语句
mysql> show create table tb_user;
+---------+-----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
----------------+
| Table | Create Table

|
+---------+-----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
----------------+
| tb_user | CREATE TABLE `tb_user` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(30) NOT NULL,
`user_pwd` varchar(64) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
----------------+
1 row in set (0.00 sec)

mysql>

（8）添加和删除外键操作
mysql> create table student(
-> stu_id int primary key auto_increment,
-> stu_name varchar(20),
-> class_id int
-> );
Query OK, 0 rows affected (0.08 sec)

mysql> create table tb_class(
-> class_id int primary key auto_increment,
-> class_name varchar(20)
-> );
Query OK, 0 rows affected (0.07 sec)

mysql> alter table student add constraint fk_class foreign key(class_id) references tb_class(class_i
d);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table student;
+---------+-----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
---------------------------------+
| Table | Create Table

|
+---------+-----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
---------------------------------+
| student | CREATE TABLE `student` (
`stu_id` int(11) NOT NULL AUTO_INCREMENT,
`stu_name` varchar(20) DEFAULT NULL,
`class_id` int(11) DEFAULT NULL,
PRIMARY KEY (`stu_id`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `tb_class` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
---------------------------------+
1 row in set (0.00 sec)

mysql> alter table student drop foreign key fk_class;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

|
+---------+-----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
------------------------------------------------+
| student | CREATE TABLE `student` (
`stu_id` int(11) NOT NULL AUTO_INCREMENT,
`stu_name` varchar(20) DEFAULT NULL,
`class_id` int(11) DEFAULT NULL,
PRIMARY KEY (`stu_id`),
KEY `fk_class` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
------------------------------------------------+
1 row in set (0.00 sec)

mysql>

11、将mysql的bin目录添加到path中

posted @ 2016-05-26 20:30 已注销账户阅读(526) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

岐下闻唐

数据库基础

公告

岐下闻唐

数据库 基础

公告

数据库基础