MySQL和Oracle

MySQL关系结构模型数据库：使用二维表格来存储数据；基本命令3.1　启动和关闭mysql服务器l  启动：net start mysql；l  关闭：net stop mysql；在启动mysql服务后，打开windows任务管理器，会有一个名为mysqld.exe的进程运行，所以mysqld.exe才是MySQL服务器程序。l  登录：mysql -u root -p 123 -h localhost；Ø  -u：后面的root是用户名，这里使用的是超级管理员root；Ø  -p：后面的123是密码，这是在安装MySQL时就已经指定的密码；Ø  -h：后面给出的localhost是服务器主机名，它是可以省略的，例如：mysql -u root -p 123；l  退出：quit或exit；l  注意，在MySQL中所有的UTF-8编码都不能使用中间的“-”，即UTF-8要书写为UTF8。l  char：固定长度字符串类型；l  varchar：可变长度字符串类型；3.4　操作表l  创建表：CREATE TABLE 表名(  列名 列类型,  列名 列类型,  ......);例如：CREATE TABLE stu(         sid        CHAR(6),         sname     VARCHAR(20),         age           INT,         gender     VARCHAR(10)); 再例如：CREATE TABLE emp(         eid            CHAR(6),         ename     VARCHAR(50),         age           INT,         gender     VARCHAR(6),         birthday  DATE,         hiredate  DATE,         salary       DECIMAL(7,2),         resume    VARCHAR(1000)); l  查看当前数据库中所有表名称：SHOW TABLES；　l  查看指定表的创建语句：SHOW CREATE TABLE emp，查看emp表的创建语句；l  查看表结构：DESC emp，查看emp表结构；l  删除表：DROP TABLE emp，删除emp表；l  修改表：1.      修改之添加列：给stu表添加classname列：ALTER TABLE stuADD (classname varchar(100));2.      修改之修改列类型：修改stu表的gender列类型为CHAR(2)：ALTER TABLE stuMODIFY gender CHAR(2);3.      修改之修改列名：修改stu表的gender列名为sex：ALTER TABLE stuchange gender sex CHAR(2);4.      修改之删除列：删除stu表的classname列：ALTER TABLE stuDROP classname;5.      修改之修改表名称：修改stu表名称为student：ALTER TABLE stuRENAME TO student;DML4.1　插入数据语法：INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)INSERT INTO stu(sid, sname,age,gender) VALUES('s_1001', 'zhangSan', 23, 'male');INSERT INTO stu(sid, sname) VALUES('s_1001', 'zhangSan'); 语法：INSERT INTO 表名 VALUES(值1,值2,…)因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值：INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female'); 　　注意：所有字符串数据必须使用单引用！ 4.2　修改数据语法：UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]UPDATE stu SET sname=’zhangSanSan’, age=’32’, gender=’female’ WHERE sid=’s_1001’;UPDATE stu SET sname=’liSi’, age=’20’ WHERE age>50 AND gender=’male’;UPDATE stu SET sname=’wangWu’, age=’30’ WHERE age>60 OR gender=’female’;UPDATE stu SET gender=’female’ WHERE gender IS NULLUPDATE stu SET age=age+1 WHERE sname=’zhaoLiu’; 4.3　删除数据语法：DELETE FROM 表名 [WHERE 条件]DELETE FROM stu WHERE sid=’s_1001’003BDELETE FROM stu WHERE sname=’chenQi’ OR age > 30;DELETE FROM stu; 语法：TRUNCATE TABLE 表名TRUNCATE TABLE stu; 虽然TRUNCATE和DELETE都可以删除表的所有记录，但有原理不同。DELETE的效率没有TRUNCATE高！TRUNCATE其实属性DDL语句，因为它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的（回滚是事务的知识！）。 5　DCL5.1　创建用户语法：CREATE USER 用户名@地址 IDENTIFIED BY '密码';CREATE USER user1@localhost IDENTIFIED BY ‘123’;CREATE USER user2@’%’ IDENTIFIED BY ‘123’; 5.2　给用户授权　　语法：GRANT 权限1, … , 权限n ON 数据库.* TO 用户名GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;GRANT ALL ON mydb1.* TO user2@localhost; 5.3　撤销授权　　语法：　　REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost; 5.4　查看用户权限语法：SHOW GRANTS FOR用户名SHOW GRANTS FOR user1@localhost; 5.5　删除用户语法：DROP USER 用户名DROP USER user1@localhost; 5.6　修改用户密码语法：USE mysql;UPDATE USER SETPASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’ and Host=’IP’;FLUSHPRIVILEGES;UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE User='user2' and Host=’localhost’;FLUSH PRIVILEGES;数据查询语法（DQL）　　DQL就是数据查询语言，数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。语法：SELECTselection_list /*要查询的列名称*/  FROM table_list /*要查询的表名称*/  WHERE condition /*行条件*/  GROUP BY grouping_columns /*对结果分组*/  HAVING condition /*分组后的行条件*/  ORDER BY sorting_columns /*对结果分组*/  LIMIT offset_start, row_count /*结果限定*/ 创建名：l  学生表：stu字段名称字段类型说明sidchar(6)学生学号snamevarchar(50)学生姓名ageint学生年龄gendervarchar(50)学生性别 CREATE TABLE stu (         sid    CHAR(6),         sname              VARCHAR(50),         age           INT,         gender     VARCHAR(50));l  雇员表：emp字段名称字段类型说明empnoint员工编号enamevarchar(50)员工姓名jobvarchar(50)员工工作mgrint领导编号hiredatedate入职日期saldecimal(7,2)月薪commdecimal(7,2)奖金deptnoint部分编号 CREATE TABLE emp(         empno              INT,         ename              VARCHAR(50),         job            VARCHAR(50),         mgr           INT,         hiredate   DATE,         sal             DECIMAL(7,2),         comm               decimal(7,2),         deptno              INT) ;l  部分表：dept字段名称字段类型说明deptnoint部分编码dnamevarchar(50)部分名称locvarchar(50)部分所在地点 CREATE TABLE dept(         deptno             INT,         dname              varchar(14),         loc            varchar(13));INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');INSERT INTO dept values(30, 'SALES', 'CHICAGO');INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');7　分组查询 当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。 7.1　分组查询l  查询每个部门的部门编号和每个部门的工资和：SELECT deptno, SUM(sal)FROM empGROUP BY deptno; l  查询每个部门的部门编号以及每个部门的人数：SELECT deptno,COUNT(*)FROM empGROUP BY deptno;1　主键当某一列添加了主键约束后，那么这一列的数据就不能重复出现。这样每行记录中其主键列的值就是这一行的唯一标识。例如学生的学号可以用来做唯一标识，而学生的姓名是不能做唯一标识的，因为学习有可能同名。主键列的值不能为NULL，也不能重复！　　指定主键约束使用PRIMARY KEY关键字 l  创建表：定义列时指定主键：CREATETABLE stu(                  sid        CHAR(6) PRIMARY KEY,                  sname     VARCHAR(20),                  age           INT,                  gender     VARCHAR(10));l  创建表：定义列之后独立指定主键： CREATETABLE stu(                  sid        CHAR(6),                  sname     VARCHAR(20),                  age           INT,                  gender     VARCHAR(10),                  PRIMARYKEY(sid)); l  修改表时指定主键：ALTER TABLE stuADD PRIMARY KEY(sid); l  删除主键（只是删除主键约束，而不会删除主键列）：ALTER TABLE stu DROP PRIMARY KEY; 2　主键自增长MySQL提供了主键自动增长的功能！这样用户就不用再为是否有主键是否重复而烦恼了。当主键设置为自动增长后，在没有给出主键值时，主键的值会自动生成，而且是最大主键值+1，也就不会出现重复主键的可能了。l  创建表时设置主键自增长（主键必须是整型才可以自增长）：CREATE TABLE stu(         sid INT PRIMARY KEY AUTO_INCREMENT,                  sname     VARCHAR(20),                  age           INT,                  gender     VARCHAR(10)); l  修改表时设置主键自增长：ALTER TABLE stu CHANGE sid sidINT AUTO_INCREMENT; l  修改表时删除主键自增长：ALTER TABLE stu CHANGE sid sidINT;3　非空指定非空约束的列不能没有值，也就是说在插入记录时，对添加了非空约束的列一定要给值；在修改记录时，不能把非空列的值设置为NULL。l  指定非空约束：CREATE TABLE stu(         sid INT PRIMARY KEY AUTO_INCREMENT,         sname VARCHAR(10) NOT NULL,                  age           INT,                  gender     VARCHAR(10));当为sname字段指定为非空后，在向stu表中插入记录时，必须给sname字段指定值，否则会报错：INSERT INTO stu(sid) VALUES(1); 　　插入的记录中sname没有指定值，所以会报错！ 4　唯一还可以为字段指定唯一约束！当为字段指定唯一约束后，那么字段的值必须是唯一的。这一点与主键相似！例如给stu表的sname字段指定唯一约束：CREATE TABLE tab_ab(         sidINT PRIMARY KEY AUTO_INCREMENT,         snameVARCHAR(10) UNIQUE); 　　INSERT INTO sname(sid, sname) VALUES(1001, 'zs');INSERT INTO sname(sid, sname)VALUES(1002, 'zs');　　当两次插入相同的名字时，MySQL会报错！ 5　外键主外键是构成表与表关联的唯一途径！外键是另一张表的主键！例如员工表与部门表之间就存在关联关系，其中员工表中的部门编号字段就是外键，是相对部门表的外键。2.2　外连接（左连接、右连接）外连接的特点：查询出的结果存在不满足条件的可能。左连接：SELECT * FROM emp eLEFT OUTER JOIN dept dON e.deptno=d.deptno; 左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。这么说你可能不太明白，我们还是用上面的例子来说明。其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。 2.3　右连接右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。SELECT * FROM emp eRIGHT OUTER JOIN dept dON e.deptno=d.deptno;  连接查询心得：连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。1.      查询员工编号为1006的员工名称、员工工资、部门名称、部门地址分析：查询列：员工名称、员工工资、部门名称、部门地址查询表：emp和dept，分析得出，不需要外连接（外连接的特性：某一行（或某些行）记录上会出现一半有值，一半为NULL值）条件：员工编号为1006 第一步：去除多表，只查一张表，这里去除部门表，只查员工表SELECT ename, sal FROM emp e WHERE empno=1006 第二步：让第一步与dept做内连接查询，添加主外键条件去除无用笛卡尔积SELECT e.ename, e.sal, d.dname, d.locFROM emp e, dept dWHERE e.deptno=d.deptno AND empno=1006 第二步中的dept表表示所有行所有列的一张完整的表，这里可以把dept替换成所有行，但只有dname和loc列的表，这需要子查询。第三步：查询dept表中dname和loc两列，因为deptno会被作为条件，用来去除无用笛卡尔积，所以需要查询它。SELECT dname,loc,deptno FROM dept; 第四步：替换第二步中的deptSELECT e.ename, e.sal, d.dname, d.locFROM emp e, (SELECT dname,loc,deptno FROM dept) dWHERE e.deptno=d.deptno AND e.empno=1006 l  子查询作为表l 子查询形式为多行多列Oracle1数据库定义数据库是“按照数据结构来组织、存储和管理数据的仓库”。常用数据MySql,SqlServer,Oracle,DB2...2E-R图  实体(Entity)  [entiti]用矩形表示属性(Attribute)用椭圆形表示关系(Relationship) 用菱形表示实体关系图3三范式建立表的时候要满足的规则第一范式一竖    列级别   原子性（不可在分）和不重复第二范式一横    行级别   能唯一的确认一行   加主键第三范式一撇    多表之间有主外键关系4管理员默认密码   以超级管管理员角色进入  c:/>sqlplus / as sysdba用户名密码syschange_on_installsystemmanagerscotttiger  5数据类型数字Number(5)99999   number(5,2) 999.99字符Varchar2(10) 变长   char(10 )定长时间date大对象[字符、二进制]Clob    blob其他 6数据信息增加信息 insert into table values(...)删除信息 delete from table where...修改信息 update table where...查询信息 select*from table where...7数据对象创建对象 create object objectName删除对象 drop object objectName修改对象 alter object objectName修改表 alter table tablename add columnname typealter table tablename modify columnname typealter table tablename drop column columnname type9约束 五大约束 非空（NOT NULL）约束、 唯一（UNIQUE）约束、主键（PRIMARY KEY）约束、外键（FOREIGN KEY）约束、条件（CHECK）约束约束视图select *  from user_constraints  create table teacher(tid number(8)primary key,tname  varchar2(20)not null，idcard char(18) unique,sex  char(2)check ( sex  in ('男','女')));create table teacher(tid number(8),tname  varchar2(20)not null，idcard char(18),sex  char(2),constraint  teacher_check  check ( sex  in('男','女')),constraint  teacher_pk  primary key(tid),constraint  teacher_unique unique(idcard));  create table student(   sid number(8)primary key,   sname varchar2(20)not null,   gno  number(8)  ,   constraint fk_student foreign key(gno)references grade(gid)   );--外键约束constraint  constraint_name  foreign key( childKey)  references  parentTable ( parentKey )---constraint fk_student foreign key(gno) references grade(gid)--- references grade(gid)  --简写方式--- on delete cascade 级联删除，当父表删除数据的时候，子表对应的外键数据一并删除---constraint fk_student foreign key(gno) references grade(gid) on delete cascade     11序列--序列   sequence ['siːkw(ə)ns]   计数器 create table product( pid  number(8)primary key, pname varchar2(20)not null );--建立序列create sequence PRODUCT_SEQminvalue 0maxvalue 9999999999999999999999999999start with1increment by 1cache 20;----调用序列insert into product values(PRODUCT_SEQ.NEXTVAL,'香蕉');10权限----用户管理--1 管理员有权限创建用户  identified [aɪ'dentɪfaɪd]conn sys/oracle@orcl as sysdba;--create user 用户名 identified by密码(密码必须以字符开头)create user zhangsanidentified by z123; --创建用户alter  user zhangsanidentified by z001; --修改密码drop user zhangsan cascade ;  ---级联删除用户--------  系统授权  grant role to user. grant  connect,resourceto zhangsan; --connect 连接角色   resource赋给用户建表、视图等对象的权限  dba 管理角色 最大的权限集合 ---------数据授权 主要是对表中的数据做增删改查操作  grant privilege on table   to  user  grant select on 表名 to 用户名    grant insert on 表名 to 用户名    grant update on 表名 to 用户名    grant delete on 表名 to 用户名    grant all on 表名 to 用户名  grant  select,updateon godyang.student to zhangsan; --撤消授权 -- 撤销系统权限revoke   role  from user;revoke  connect from zhangsan;--撤销数据权限revoke  privilege  on  table from user;revoke  select on godyang.student from zhangsan;--权限传递 管理员把权限赋值给A的同时附带传递，那么A就可以将权限赋值给B--系统权限传递 with admin option--数据权限传递 with grant optioncreate user wangwuidentified by  w123;create user jialiuidentified by  j123; grant connect,resourceto wangwu  with  admin option;grant select on table  to wangwu with  grant option;11规则---规则  profilecreate profile 规则名称limit failed_login_attempts 次数password_lock_time 天数alter user 用户名profile 规则名称--创建一个规则 规则名字lockAccount当3次登录失败就锁足密码365天create profile  lockAccount limit failed_login_attempts3 password_lock_time365;---将规则加到goydang用户上alter user godyang profile lockAccount;--给godyang用户解锁alter user godyangaccount unlock; 分类DML（数据操纵语言） DDL（数据定义语言）truncateDCL（数据控制语言） TCL（事务控制语言） 1.truncate和不带where子句的delete、以及drop都会删除表内的数据。速度，一般来说: drop> truncate > delete --oracle分页(Pageing Query)   select * from      (select rownum r,e1.*          from (select * from emp order by sal) e1         where rownum <=8         ) where r >=5;  查询在部门的location_id为1700的部门工作的员工的员工号，selectemployee_idfromemployeeswheredepartment_idin (      selectdepartment_id      fromdepartments      wherelocation_id =1700)