mysql知识系列–东西比较全

 
A。结构化查询语言（structured query language)

B。SQL语句分类
  -在MySql数据库中，SQL语句主要可以划分以下几类：
  ——DDL（data definition language):数据定义语言，定义对数据库对象（库、表、列、索引）的操作
  -create,drop,alter,rename,truncate等

  ——DML(data manipulation language):数据操作语言，定义对数据库记录的操作
  -insert,delete,update,select等

  ——DCL(data control language):数据控制语言，定义对数据库，表，字段，用户的访问权限和安全级别
  -grant，revoke等

  ——transaction control:事务控制
  -commit,rollback,savepoint等

1.create database创建一个数据库：
  -语法：create database[if not exists] 数据库名字；
  -例子：create database fkjava;

2.drop database 删除一个数据库
  -语法：-drop database [if not exists] 数据库名；
  -功能：删除数据库中所有的表和数据库
 -小心使用这个命令
  -例子：drop database fkjava;

3.使用use选用数据库(选着一个数据库)
  -语法：use 数据库名；
  -功能：把指定的数据库作为默认的数据库使用，用于后续语句。
  -例子：use fkjava;

4.数据库对象
  -表：基本的数据存储对象，以行和列的形式存在，列就是字段，行就是记录
  -约束：执行数据校验，保证了数据的完整性
  -数据字典：就是系统表，存放数据库相关信息的表
  -视图：一个或多个表数据的逻辑显示
  -索引：用于提高查询的性能

5.数据库对象的命名规则
  -必须以字母开头
  -可包含数字和三个特殊的字符（# _ $）
  -不要使用mysql的保留字
  -同一Schema下的对象不能同名

6.建表的语句
  -标准的建表的语法（列定义之间以英文逗号隔开）
  -数据表的每行称为一条记录（record），每一列称为一个字段（field）
  -主键列：唯一能够标识每条记录的列

7.show
  -show tables [from 数据库名][like wild];
  -功能：显示当前数据库中已有的数据表的信息
  -例子：show tables;

8.用create table 创建数据库表
  -语法：create table table_name(
    列名 列类型,
    列名 列类型,
    列名 列类型
  );
  -功能：在当前数据库中创建一张表
  -例子：
 CREATE TABLE tb_dept(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(18),
  description VARCHAR(100)
 );

9.describe
  -语法：describe|desc table_name[列名]；
  -功能：查看数据表中各列的信息
  -例子：describe tb_dept;
  -用show create table table_name\G 可以查看更全面的表定义信息

10.删除数据表
  -语法：drop table[if exists] table_name;
  -功能：删除指定的表

11.用alter table语句修改表的结构
  ●修改列类型
   -alter table table_name modify 列名 列类型；
   -例子：ALTER TABLE tb_emp MODIFY NAME VARCHAR(18);
   ▲不是任何情况都是可以修改的，当字段只包含空值时，类型，大小都可以修改的，否则修改可能不能成功
  ●增加列
   -alter table table_name add 列名 列类型；
   -例子：ALTER TABLE tb_emp ADD mobile VARCHAR(18);
  ●删除列
   -alter table table_name drop (column) 列名；
   -例子：ALTER TABLE tb_emp DROP mobile;
  ●列改名
   -alter table table_name change 旧列名 新列名 列类型；
   -例子：ALTER TABLE tb_emp CHANGE NAME emp_name VARCHAR(18);
  ●更改表名
   -alter table table_name rename 新表名；
   -rename table table_name to 新表名；  
   -例子：ALTER TABLE tb_emp RENAME emp;
   -例子：RENAME TABLE emp TO tb_emp;

12.删除表
  -语法：drop table table_name;

13.约束
  -约束是在表上强制执行的数据校验规则
  -约束主要的用于保证数据库的完整性
  -当表中的数据有相互依赖性时，可以保护相关的数据不被删除

  >>>大部分数据库支持下面五类完整性约束：
   -not null 非空
   -unique key 唯一键
   -primary key 主键
   -foreign key 外键
   -check 检查
 
 ▲约束作为数据库对象，存放在系统表中，也有自己的名字
 ▲创建约束的时机
  -在建表的同时创建
  -建表后创建（修改表）

 >>可定义列级或表级约束
 >>有单列约束和多列约束

 ★★★★★★★★★★★★★★★★★
 ■定义约束的语法
   -列级约束：在定义列的同时定义约束
   >>语法：列定义 约束类型

   -表级约束：在定义了所有列之后定义的约束
   >>语法：
     列定义….
     [constraint 约束名] 约束类型(列名)
   
    -约束名的取名规则
     >> 推荐采用：表名_列名_约束类型简写

    -约束可以在创建表的同时就定义，也可以在创建完表之后再添加
     >>语法：alter table table_name
      add constraint 约束名 约束类型(要约束的列名);

 ■非空约束(not null)
  -列级约束，只能使用列级约束语法定义
  -确保字段值不允许为空
  -只能在字段级定义
 
  >>NULL值
   -所有数据类型的值都可以是null
   -空字符串不等于NULL
   -0也不等于NULL

 ★例子：
   create table tb_student(
   id int primary key auto_increment,
   name varchar(18) not null
   );
  
 ■唯一约束
  -唯一性约束条件确保所在的字段或者字段组合不出现重复值
  -唯一性约束条件的字段允许出现多个NULL
  -同一张表内可创建多个唯一约束
  -唯一约束可由多列组合而成
  -建唯一约束时MySQL会为之建立对应的索引
  -如果不给唯一约束起名，该唯一约束默认与列名相同

  ★例子：
   create table tb_student(
   id int primary key auto_increment,
   name varchar(18) unique not null
   );

 ■主键约束
  -主键从功能上看相当于非空且唯一
  -一个表中只允许一个主键
  -主键是表中唯一确定一行数据的字段
  -主键字段可以是单字段或者是多个字段的组合
  -当建立主键约束时，MySQL为主键创建对应的索引
  -主键约束名总为primary

 ■外键约束
  -外键是构建于一个表的两个字段或者两个表的两个字段之间的关系
  -外键确保了相关的两个字段的两个关系
  -子（从）表外键列的值必须在主表参照列值的范围内，或者为空（也可以加非空约束，强制不允许为空）
  -当主表的记录被子表参照时，主表记录不允许被删除
  -外键参照的只能是主表主键或者唯一键，保证子表记录可以准确定位到被参照的记录
  -格式foreign key （外键名） references 主表（参考列）

  ★例子：#列级约束
    create table tb_dept(
    dept_id int primary key,
    name varchar(18),
    description varchar(255)
    );

    create table tb_employee(
    employee_id int primary key,
    name varchar(18),
    gender varchar(10),
    dept_id int references tb_dept(dept_id),
    address varchar(255)
    );
  
    #表级约束
     create table tb_dept(
     dept_id int primary key,
     name varchar(18),
     description varchar(255)
     );

     create table tb_employee(
     employee_id int primary key,
     name varchar(18),
     gender varchar(10),
     dept_id int,
     address varchar(255),
     constraint foreign key tb_employee_fk(dept_id) references tb_dept(dept_id)
     );

 ■check约束
  -既可以作为列级约束，也可以作为表级约束
  -定义在字段上的每一条记录都要满足的条件
  -在check中定义检查的条件表达式，数据需要符合设置的条件
  -条件表达式不允许使用
  -参照其他记录的值

   ★例子：
     create table tb_student(
     id int primary key auto_increment,
     name varchar(18),
     age int check(age > 18 and age < 60)
     );
   
  ◎列级约束与表级约束
    -列级约束直接跟在列后定义，不再需要制定列名，与列定义之间用空格分开
    -表级约束通常放在所有的列定义之后定义，要显示制定对哪些列建立列级约束 与列定义之间采用英文逗号给开
    -如果是对多列建联合约束，只能使用表级约束语法

  ◎增加约束
    -可增加或删除约束，但不能直接修改
    -可使约束启用和禁用
    -非空约束必须使用modify字句增加
    -只要是可以使用列级约束语法来定义的约束，都可以通过modify来增加该约束
     -alter table table_name
     -add [CONSTRAINT constraint] type(column);

      ★例子：加FOREIGN KEY 约束到EMP表
         alter table tb_employee
  add constraint
  tb_employee_dept_id_fk
  foreign key(dept_id)
  references tb_dept(dept_id);

*************************************************************
      >>>> #建表的同时建立约束
 CREATE TABLE tb_emp(
 id INT PRIMARY KEY AUTO_INCREMENT,#主键，自动增长
 NAME VARCHAR(18) NOT NULL,#姓名，非空约束，插入记录的时候一定要插入姓名
 sex VARCHAR(2) CHECK(sex='男' OR sex = '女'),#性别
 age INT CHECK(age > 18 AND age < 60),#年龄，检查约束
 address VARCHAR(200),
 email VARCHAR(100) UNIQUE,#唯一约束
 dept_id INT REFERENCES tb_dept(id)
 );

      >>>>#建表的最后建立约束
 CREATE TABLE tb_emp(
 id INT PRIMARY KEY AUTO_INCREMENT,#主键，自动增长
 NAME VARCHAR(18) NOT NULL,#姓名，非空约束，插入记录的时候一定要插入姓名
 sex VARCHAR(2) CHECK(sex='男' OR sex = '女'),#性别
 age INT CHECK(age > 18 AND age < 60),#年龄，检查约束
 address VARCHAR(200),
 email VARCHAR(100) UNIQUE,#唯一约束
 dept_id INT,
 #CONSTRAINT FOREIGN KEY 外键名[表名+约束名](外键) REFERENCES 表名
 CONSTRAINT FOREIGN KEY tb_emp_fk(dept_id) REFERENCES tb_dept(id)
 );

     >>>> #表建立完之后再建约束
 CREATE TABLE tb_emp(
 id INT ,
 NAME VARCHAR(18),
 sex VARCHAR(2) ,
 age INT ,
 address VARCHAR(200),
 email VARCHAR(100) ,
 dept_id INT
 );

 #添加主键
  alter table tb_emp
  add primary key (id);
 
 #添加外键
 ALTER TABLE tb_emp
 ADD CONSTRAINT FOREIGN KEY tb_emp_fk(dept_id) REFERENCES tb_dept(id);

 #添加唯一键
 alter table tb_emp
 add unique (email);

 #添加检查约束
  alter table tb_emp
  add constraint check(age > 18 and age < 60);

  #更改自动增长
   alter table tb_emp
   modify column id int auto_increment;
 

*************************************************************
 ■删除约束
  -约束可被删除，删除约束不会对数据产生影响
  -当删除被外键参照的主键时候，应该采用CASCADE关键字来级联删除外键，否则无法删除主键
  -语法如下：
   alter table talbe_name drop constraint 约束名
 
  ◎删除定义的约束
   -删除NOT NULL
    >>alter table table_name modify 列名 列类型;

   -删除UNIQUE约束
    >>alter table table_name drop index 唯一约束名;

   -删除PRIMARY KEY约束
    >>alter table table_name drop primary key;

   -删除外键FOREIGN KEY约束
    >>alter table table_name drop foreign key 外键名；
==============================================
 ◎自动增长和默认值
  auto_increment:自动增长
   -为新的行产生唯一的表识
   -一个表只能有一个auto_increment属性，且该属性必须为主键的一部分，auto_increment属性可以是任何整数类型
  default默认值：指定默认值
 ★create table depts(
   dept_id int primary key auto_increment,
   name varchar(18),
   description varchar(255) default"how are you now";
   );
————————————————————————-

14.insert语句
 -insert into table_name[(column[column…])]
  values(value[,value…]);
 -默认情况下，一次插入操作只插入一行
 -一次性插入多条记录：
   insert into table_name[(column[,column…])]
   values(value[,value…]),(value[,value…]);
 -如果为每列都指定值，则表名后不需要列出插入的列名
 -如果不想在表名后列出列名，可以为那些无法指定的值插入NUll
 -如果需要插入其他特殊字符，应该采用\转义字符做前缀

 ★1.指明字段进行插入，必须一一对应
   insert into tb_dept(name,loc,description)
   values('开发部','武汉','负责软件开发');
   2.如果插入的values是所有字段，可以不用显示写插入的字段名，强烈不推荐，不会影响到自动增长
    insert into tb_dept values(3,'财务部','武汉','负责财务工作');
    ◆一次性插入多行：
        #可以从一张表中插入数据
 #创建一张表和tb_dept表的结构一样，通过这种方式创建的表，只是复制表结构，不复制约束
 CREATE TABLE tb_dept2
 SELECT * FROM tb_dept

 CREATE TABLE tb_dept2
 SELECT * FROM tb_dept
 WHERE id = 3;

 INSERT INTO tb_dept2(id,NAME,loc,description)
 SELECT id,NAME,loc,description FROM tb_dept;

15.update语句
   -语法：update table_name
  set column = value[,column = value]…
  [where condition];
   -修改可以一次修改多行数据，修改的数据可用where子句限定，where子句是一个条件表达式，只有符合该条件的行才会被修改。没有where子句意味着where子句表达式值为true
   -也可以同时修改多列，多列的修改中采用逗号隔开
   ★例子：
 #where的时候建议使用主键或者是unique，主键最好，因为和业务无关
 UPDATE tb_emp SET age = 13;
 UPDATE tb_emp SET sex = '女',age = 34 WHERE id = 2;

16.delete语句
   -语法：delete [from] table_name
   [where condition];
   -删除不需要指定列名，因为删除总是整行整行删除，where子句是一个条件表达式，只有符合该条件的行才会被删除。没有where子句意味着删除所有数据
   -删除可以一次删除多行，删除哪些行采用where子句限定
   ★例子：
     delete from tb_emp;#删除表所有数据
     delete from tb_emp where id = 3;

17.truncate语句
  -语法：truncate table table_name
  -完全清空一个表
  -DDL语句
  ★例子：
     #截断表，删除所有表数据
     truncate table tb_emp;

18.drop truncate delete
   –delete删除数据，保留表结构，可以回滚，如果数据量大，很慢
   –truncate删除所有数据，保留表结构，不可以回滚，一次删除全部数据，速度相对快
   –drop删除数据和表结构，删除速度最快

19.查询mysql自动提交设置(1自动提交，0手动提交)
   -select @@autocommit;
   修改提交方式
   set autocommit = 0;
 
20.最简单的select语句
    -语法：
 select {*,column[alias],…}
 from table_name;
    -说明：*表示所有列
    -from提供数据源(表名/视图名)
    -默认选着所有行
    #查找 字段,字段… 从 表
    ★例子：
 SELECT * FROM tb_dept;
 SELECT NAME,loc FROM tb_dept;

21.select语句中的算术表达式
   -对数值型数据列，变量，常量可以使用算数操作符创建表达式(+ – * /)
   -对日期型数据列，变量，常量可以使用部分算数操作符创建表达式(+ -)
   -运算符不仅可以在列和常量之间进行运算，也可以在多列之间进行运算

22.运算符的优先级
   -乘法和除法的优先级高于加法和减法
   -同级运算的顺序是从左到右
   -表达式中使用括号可强行改变优先级的运算顺序
   ★例子：
 select ename,sal.sal*12+100
 from tb_emp;
 SELECT ename,sal,sal*(12+100)
 FROM tb_EMP;

23.NULL值的使用
   -空值是指不可用，未分配的值
   -空值不等于零或空格
   -任意类型都可以支持空值
   -包含空值的任何算术表达式都等于空
   -字符串和null进行连接运算，得到也是null

24.定义字段的别名
  -改变列的标题头
  -用于表示计算结果的含义
  -作为列的别名
  -如果别名中使用特殊字符，或者是强制大小写敏感，或者有空格时，都可以通过为别名添加""(英文双引号)实现
  ★例子：（加AS是标准的写法）
 SELECT ename '姓名',sal*12+100 '年薪'
 FROM tb_emp;

 SELECT ename AS '姓名',sal*12+400 AS '年薪'
 FROM tb_emp;

25.重复记录
   -缺省情况下查询显示所有行，包含重复行
    >>select deptno from tb_emp;
   -使用distinct关键字可从查询结果中清除重复的行
    >>SELECT DISTINCT deptno FROM tb_emp;
   -distinct的作用范围是后面所有字段的组合
    >>SELECT DISTINCT deptno,job FROM tb_emp;

26.限制所选择的记录
  -使用where子句限定返回的记录
  -where子句在from子句后
  -语法：
 select [distinct]{*,column[alias],…}
 from table_name
 [where condition(s)];
   ★例子：
 SELECT * FROM tb_emp WHERE deptno = 20;

27.where中的字符串和日期值
  -字符串和日期要用单引号括起来
  -字符串是大小写敏感的，日期值是格式敏感的
 select ename,job,deptno
 from tb_emp
 where ename = 'king';

28.where中的比较运算符
    > >= < <= = <>(不等)
 SELECT * FROM tb_emp WHERE sal >= 1500;
 SELECT * FROM tb_emp WHERE sal >=1500 AND sal <=3000;

   >>>其他比较运算符
    between…and…  在两个值之间(包含)
    in(list)       匹配所有列出的值
    like       匹配一个字符模式
    is        是空值
  ◎使用between运算符
    -使用between运算符显示某一值域范围的记录
    select ename,sal
    from tb_emp
    where sal between 1000 and 1500;

  ◎使用IN运算符
      -使用IN运算符获得匹配列表值的记录
      select empno,ename,sal,mgr
      from tb_emp
      where mgr in(7920,7566,7888);

  ◎使用like运算符
      -使用like运算符执行模糊查询
      -查询条件可包含文字字符或数字
      -(%)可表示零或多个字符
      -(_)可表示一个字符
 SELECT ename
 FROM tb_emp
 WHERE ename LIKE '_德';

  ◎使用is null运算符
    -查询包含空值的记录
    select ename,mgr
    from tb_emp
    where mgr is null;

    SELECT ename,mgr
    FROM tb_emp
    WHERE mgr IS NOT NULL;

 ◎逻辑运算符
   -and
   -or
   -not

 ◎优先级规则
  -括号将跨越所有优先级
  次序  运算符
    1  所有比较运算符
    2  not
    3  and
    4  or

29.对结果集排序
  -查询语句执行的查询结果，数据是按插入顺序排序的
  -实际上需要按某列的值大小排序排列
  -按某列排序采用order by 列名[desc],列名…
  -设定排序列的时候可采用列名，列序号和列别名
  -如果按多列排序，每列的asc，desc必须单独设定

  ★降序
 select * from tb_emp
 order by sal desc;

  ★升序(默认)
 select * from tb_emp
 order by sal asc;

==============================================================
30.常用函数
  -字符串函数
        #concat连接
 SELECT CONCAT(ename,job) FROM tb_emp;
 #uppper转换大写
 SELECT UPPER(ename) FROM tb_emp WHERE EMPNO = 8000;
 #返回字符串长度
 SELECT LENGTH(ename) FROM tb_emp WHERE EMPNO = 8000;
 #返回字符串的子串
 SELECT SUBSTR(ename,2,4) FROM tb_emp WHERE EMPNO = 8000;
  -数值函数
  -日期和时间函数
 #返回当前时间
 SELECT NOW();

 #查询入职时间是1980年的员工
 SELECT * FROM tb_emp
 WHERE YEAR(HIREDATE) = 1980;

 #查询入职时间是1980年2月
 SELECT * FROM tb_emp
 WHERE YEAR(hiredate) = 1981
 AND MONTH(HIREDATE) = 2;

 #插入时间的两种方式
 INSERT INTO tb_emp(empno,ename,job,hiredate,sal)
 VALUES(8001,'刘德华','卖火材的小女孩','2013-9-3',900);

 INSERT INTO tb_emp(empno,ename,job,hiredate,sal)
 VALUES(8002,'王大海','酒吧玉漱',NOW(),1200);

  -流程函数
      函数     功能
    ◎if(expr1,expr2,expr3)  如果expr1是true(expr1<>0 and expr1<>null),
     则if()的返回值为expr2,否则返回值为expr3
    
    ◎ifnull(expr1,expr2)  如果expr1不为null，则ifnull()的返回值为expr1,
     否则其返回值为expr2
 
    ◎case    
       when [value] then result  如果value是真，返回result，否则返回default
       else default
     end

    ◎case [expr]   如果expr等于value1，返回result1，
       when [value1] then result1 如果等于value2，返回result2，否则返回default
       when [value2] then result2
       [else default]
     end
————————————————————
 SELECT empno,ename,job,comm AS '奖金',
 CASE
 WHEN comm IS NULL THEN 100
 ELSE comm
 END AS '奖金2'
 FROM tb_emp;

 SELECT empno,ename,job,comm AS '奖金',
 IF(comm,comm,0) AS '奖金2'
 FROM tb_emp;

 SELECT empno,ename,job,comm AS '奖金',
 IFNULL(comm,50) AS '奖金2'
 FROM tb_emp;
————————————————————-
  -其他常用函数
  -组函数
  ◆聚合函数对一组值进行运算，并返回单个值，也叫组合函数。
    -count(*|列名)  统计行数
    -avg(数值类型列名)  平均值
    -sum(数值类型列名)  求和
    -max(列名)   最大值
    -min(列名)   最小值
   >>除了count()以外，聚合函数都会忽略NULL值

   ************************************************
 SELECT MAX(sal) AS '最大工资',MIN(sal) AS 最低工资,
 SUM(sal) AS '总工资',AVG(sal) AS '平均工资'
 FROM tb_emp;

 SELECT COUNT(*) FROM tb_emp;
 SELECT COUNT(job) FROM tb_emp;
 SELECT COUNT(deptno) FROM tb_emp;
 SELECT COUNT(DISTINCT deptno) FROM tb_emp;

 SELECT AVG(comm) FROM tb_emp;#忽略空值
 SELECT AVG(IFNULL(comm,0)) FROM tb_emp;
   ************************************************
   ◆group by子句
     -group by子句的真正作用在于与各种聚合函数配合使用，它用来对查询出来的数据进行分组
     -分组的含义是：把该列具有相同值的多条记录当成一组记录处理，最后只输出一条记录
     -分组函数忽略空值
     -结果集隐式按升序排列，如果需要改变排序方式可以使用order by子句
     ☆语法：
        select column,group_function
 from table_name
 [where conditon]
 [group by group_by_expression]
 [order by column];
 
    ⊙分组函数的重要规则
      -如果使用了分组函数，或则使用group by的查询：出现在select列表中的字段，要么出现在组合函数里，要么出现在group by子句中
      -group by子句的字段可以不出现在select列表当中
      -使用集合函数可以不适用group by子句，此时所有的查询结果作为一组

*********************************
 #每个部门的平均工资
 SELECT deptno,AVG(sal)
 FROM tb_emp
 GROUP BY deptno;

 #查询每个部门的整体工资情况
 #如果select语句中的列未使用组函数，那么它必须出现在group by子句中
 #而出现在group by子句中的列，不一定要出现在select语句中
 SELECT deptno,MAX(sal),MIN(sal),SUM(sal),AVG(sal),COUNT(*)
 FROM tb_emp
 GROUP BY deptno;

 #每个部门每个职位的平均工资
 SELECT deptno,job,AVG(sal)
 FROM tb_emp
 GROUP BY deptno,job;
*********************************
    ⊙限定组的结果：having子句
      -having子句用来对分组后的结果再进行条件过滤
      -语法：
        select column group_function
 from table_name
 [where condition]
 [group by group_by_expression]
 [having group_condition]
 [order by column];
    ☆having与where的区别
      -where是在分组前进行条件过滤
      -having子句是在分组后进行条件过滤
      -where子句中不能使用聚合函数
      -having子句可以使用聚合函数
 ====================================
 #部门平均工资大于1100
 SELECT deptno,AVG(sal)
 FROM tb_emp
 GROUP BY deptno
 HAVING AVG(sal) >1100;
 ====================================
 ⊙查询结果限定(mysql特有)
   -在select语句最后可以用limit来限定查询结果返回的起始记录和总数量
   -语法：
 select….limit offset_start,row_count;
 -offset_start:第一个返回记录行的偏移量，默认为0
 -row_count:要返回记录行的最大数量

—————————
 SELECT * FROM tb_emp LIMIT 5;

 SELECT * FROM tb_emp LIMIT 4,2;
—————————
31多表连接查询
  -使用单个select语句从多个表中取出相关的数据，通过多表之间的关系，构建相关数据的查询
  -多表连接通常是建立在相互关系的父子表上的
   ☆语法：
 select … from join_table
 join_type join_table
 on join_condition
 where where_condition;
 -join_table 参与连接的表
 -join_type 连接类型：内连接，外连接，交叉连接，自连接
 -join_condition 连接条件
 -where_condition where过滤条件
  
   ☆交叉连接/笛卡尔交集
     -语法：
 select … from join_table cross join join_table2;
 -没有on子句和where子句，它返回连接表中所有数据行的笛卡尔积
 -其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数
 -相当于：select … from table1,table2;

    ★例子：
 SELECT COUNT(*) FROM tb_dept,tb_emp;
 SELECT * FROM tb_emp CROSS JOIN tb_dept;
    -交叉连接：返回两张记录表的乘机
  
    ☆内连接
      -语法：
 select … from join_table
 [inner] join join_table2
 [on join_condition]
 where where_definition;
      -只列出这些连接表中与连接条件相匹配的数据行

      -内连接分类：
        -等值连接：在连接条件中使用等号(=)运算符来比较被连接列的值
 -非等值连接：在连接条件中使用除等号运算符以外的其它比较运算符来比较被连接的列的列值
 -自然连接：在连接条件中使用等于(=)运算符来比较连接列的类值，但它使用选择列表指出查询结果集合中所包含的列，并删除连接表中的重复列

————————————
 内连接，连接条件就是主外键关联
 SELECT * FROM tb_emp e,tb_dept d
 WHERE e.deptno = d.deptno;

 SELECT * FROM tb_emp e INNER JOIN tb_dept d
 ON e.deptno = d.deptno;
————————————
  ☆外连接
   -语法：
 select … from join_table
 (left|right|full) [outer] join join_table2
 on join_condition
 where where_definition;
  -不仅列出与连接条件相匹配的行，还列出左表(左外连接)，右表(右外连接)或两个表(全外连接)中所有符合where过滤条件的数据行
  -分类：
 -左外连接(left [outer] join)
 -右外连接(right [outer] join)
 -全外连接(full [outer] join)

    ●左外连接
      -在外连接中，某些不满足条件的列也会显示出来，也就说，只限制其中一个表的行，而不限制另一个表的行
      -左连接，左边的表为主变，左边的表记录全部显示，如果没找到记录则补NULL
———————————
 SELECT * FROM tb_emp LEFT JOIN tb_dept
 ON tb_dept.deptno = tb_emp.deptno;

 SELECT * FROM tb_dept LEFT JOIN tb_emp
 ON tb_dept.deptno = tb_emp.deptno;
———————————
 ☆自连接
   -参与连接的表都是同一张表(通过给表取别名虚拟出)
   select c.name '类别名',c2.name '父类别名'
   from tb_course c left join tb_course c2
   on c.pid = c2.id;
============================================================
32子查询
  -某些情况下，当进行查询的时候，需要的条件是另外一个select语句的结果，这个时候，就要用到子查询
  -为了给主查询(外部查询)提供数据而首先执行的查询(内部查询)被叫做子查询
  -用于子查询的关键字主要包括in ，not in = <>等
  -一般说子查询的效率低于连接查询，表连接都可以用子查询替换，但反过来却不一定

========================================
 #查询工资比艾伦高的员工
 SELECT * FROM tb_emp
 WHERE sal <(
 SELECT sal FROM tb_emp
 WHERE ename = '艾伦'
 );
 
 #查询工资最高的员工的名字
 SELECT ename,sal FROM tb_emp
 WHERE sal =(
 SELECT MAX(sal) FROM tb_emp
 );
 #单行子查询返回多个结果是 错误
 SELECT * FROM tb_emp
 WHERE sal =(
 SELECT MIN(sal) FROM tb_emp
 GROUP BY deptno
 ); 
 
 #in 与列表中的任一成员相等（等于子查询的任意一个结果）
 SELECT * FROM tb_emp
 WHERE sal IN (
 SELECT sal FROM tb_emp
 WHERE job = '市场营销'
 );
 
 #any 与子查询返回的每一个值比较(小于子查询的任意一个结果 即：小于最大的)
 SELECT * FROM tb_emp
 WHERE sal < ANY(
 SELECT sal FROM tb_emp
 WHERE job = '市场营销'
 );
========================================
33.索引
  -模式(schema)中的一个数据库对象
  -在数据库中用来加速对表的查询
  -通过使用快速路径访问方法快速定位数据，减少了磁盘的IO
  -与表独立存放，但不能独立存在，必须属于某个表
  -由数据库自动维护，表被删除时，该表上的索引自动被删除
  -索引的作用类似于书的目录，几乎没有一本没有目录，因此几乎没有一张表没有索引

   ☆索引的创建
     -自动：当在表上定义一个primary key 或者unique约束条件时，数据库自动创建一个对应的索引
     -手动：用户可以创建索引以加速查询
    ◆创建索引
 -在一列或者多列上创建索引
   create index index_name
   on table_name(column[,column]…);

 create index index_tb_student_name
 on tb_student(stu_name);
 
 ⊙使用索引，在where之后加上索引，提高查询速度：
  select * from tb_student where stu_name = index_tb_student_name;
 
 ⊙删除索引
   drop index index_name;

——————————————-
33视图的好处
  -可以限制对数据的访问
  -可以使复杂的查询变得简单
  -提供了数据的独立性
  -提供了对相同数据的不同显示

  ⊙创建视图
    -在create view 语句后加入子查询
    create [or replace] view view_name
    [(alias[,alias]…)]–为视图字段指定别名
    as subquery
    [with read only];
    子查询中可以是非常复杂的select语句

    ●创建视图，EMP_V_10，包括10号部门的所有雇员信息
 create view emp_v_10
 as select employee_id,name,salary
 from employee
 where deptt_id = 10;
    ●可以使用sql*plus的describe命令描述视图结构
      -describe emp_v_10;

  
 CREATE VIEW view_emp
 AS SELECT ename AS 姓名,job AS 岗位,HIREDATE AS 入职日期,sal AS 工资
 ROM tb_emp WHERE DEPTNO = 30;
 
 使用视图
 SELECT * FROM view_emp;
 删除视图
 DROP VIEW view_emp;
 -删除视图不会导致数据的丢失，因为视图是基于数据库的表之上的一个查询定义

 -创建复杂视图两张表以及两张以上的表查询的视图创建
—————————————————————-
34数据库的设计
  -当数据库比较复杂时我们需要设计数据库
  -良好的数据库设计
    -节约数据的存储空间
    -能够保证数据的完整性
    -方便进行数据库应用系统的开发

 ☆数据库设计规范化
  -仅有好的DBMS并不足以避免数据冗余，必须在数据库的设计中创建好的表结构
  -数据库规范化的三个级别，范式是具有最小冗余的表结构，如下：
    -第一范式：(1stNF first normal formate)
    -第二范式：(2stNF second normal formate)
    -第三范式：(3stNF third normal formate)

    ◆第一范式(1NF)（第一范式必须有主键）
      -在关系模式R中的每一个具体关系r中，必须要有主键，并且每个属性都是不可再分的最小数据单位

    ◆第二范式(2NF)
      -如果关系模式R中的所有非主属性都完全依赖于主关键字，则称关系R是属于第二范式

    ◆第三范式(3NF)
      -关系模式R中的非主关键字不能依赖于其他非主关键，即非主关键字之间不能有函数(传递)依赖关系，则称关系R是属于第三范式

************************************************
一对一，一对多的关系可以用主外键关联
多对多的话，就需要建立中间表来处理关系

#创建一个班级表
CREATE TABLE tb_class(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(18)
);
#创建一个学生表
CREATE TABLE tb_student(
id INT PRIMARY KEY AUTO_INCREMENT,
stu_name VARCHAR(20),
sex VARCHAR(2),
age INT,
class_id INT,
CONSTRAINT FOREIGN KEY tb_student_fk(class_id) REFERENCES tb_class(id)
);
#创建一个课程表
CREATE TABLE tb_course(
id INT PRIMARY KEY AUTO_INCREMENT,
course_name VARCHAR(20),
course_grade INT
);

SELECT * FROM tb_student

#创建一个中间表处理关联关系
CREATE TABLE tb_stu_cour(
id INT PRIMARY KEY AUTO_INCREMENT,
stu_id INT,
cour_id INT,
grade INT,
UNIQUE(stu_id,cour_id),
CONSTRAINT FOREIGN KEY tb_sc_stu_id(stu_id) REFERENCES tb_student(id),
CONSTRAINT FOREIGN KEY tb_sc_cour_id(cour_id) REFERENCES tb_course(id)
);
************************************************

   oracle部分
==============================================================
1.数据模型
  -数据模型就是描述数据的一组概述的定义：
     -层次数据模型
     -网状数据模型
     -关系数据模型(RDBMS)
     -面向对象数据模型
  -关系型数据库
    -关系模型数据库采用表组织数据(表称为“关系”)，一个数据库有许多个表组成
  -oracle数据库的特点
   -支持大数据库，多用户的高性能的事物处理
   -oracle遵循数据存取语言，操作系统，用户接口网络通信的工业标准
   -实施安全性控制和完整性：数据加密，数据校对，第三方认证服务…
   -支持分布式数据库和分布式处理
   -完全向上兼容，具有可移植性，可兼容性和可连接性
   -全球化，跨平台的数据库
   -从两层模式到多层模式

 -oracle10g体系结构
   -数据库的体系结构是指数据库的组成、工作过程与原理，以及数据在数据库中的组织与管理机制
   -oracle服务器提供开放、全面和综合的信息管理，它由oracle数据库和oracle实例实例组成
   -管理数据库的后台进程和内存结构的集合称为oracle实例
   oracle服务器
    ↙   ↘
   oracle数据库      oracle实例
  -oracle实例
   -oracle实例是后台进程和内存结构的集合
 
      (分配)↗系统全局区
 oracle实例
      (启动)↘后台进程
  -oracle数据库
    -oracle数据库由操作系统文件组成，这些文件为数据库信息提供实际物理存储区
    -oracle数据库包含逻辑结构和物理机构
    -物理结构包含数据库中的一组操作系统文件
    -逻辑结构指数据库创建之后形成的逻辑概念之间的关系
   oracle数据库
   ↙ ↘
  物理结构 逻辑结构

 -oracle数据库的物理结构
   -物理组件就是oracle数据库所使用的操作系统物理文件
    物理文件可分为三类：
   物理组件
        ↙    ↓     ↘
   数据文件 控制文件  日志文件
    -日志文件记录对数据库的所有修改信息，用于故障恢复
    -各种文件的后缀 数据文件.dbf  控制文件.ctl 日志文件.log 参数文件.ora
    -数据库文件与存储：
      -数据文件：信息的物理存储位置
      -控制文件：数据库的控制信息
      -日志文件：记录日志信息
      -初始化参数文件：数据库启动时读取的属性
 -会话session
   -会话是用户与oracle服务器的单个连接
   -当用户与服务器建立连接时创建会话
   -当用户与服务器断开连接时关闭会话
   -oracle是多用户、多任务、可分布式管理的数据库，同时可有许多个用户对数据库操作

–查询当前系统时间
select sysdate from dual;
–忘记管理员密码：
  -在命令行下输入sqlplus/nolog 进入sql*plus
  -输入：conn/as sysdba
  -修改密码：alter user 用户名 identified by 新密码;

 -oracle默认用户
  -只有合法的用户账号才能访问oracle数据库
  -oracle有几个默认的数据库用户
  -sys是系统最高权限的管理员
  -system是是默认的系统管理员该用户拥有oracle管理工具使用的内部表和视图，通常通过system用户管理数据库用户、权限和存储等
  -scott用户是oracle数据库的一个示范账户，在数据库安装的时创建
  -数据库中所有数据字典和视图都存在sys模式中。sys用户主要用来维护系统信息和管理实例
  >>>解除scott用户的锁定的方法：
    alter user scott account unlock;
    重新设置密码：
    alter user scott identified by tiger;

    -数据字典：
     -在数据库创建的时候，oracle服务器会在数据文件里创建一些额外的对象结构
       -数据字典表
       -动态性能表

–查询当前系统时间
select sysdate from dual;

select * from dba_tablespaces;

–查新所有的表空间
select distinct tablespace_name from dba_free_space;

–创建表空间myspace
–表空间的数据文件保存路径、大小
–是否自动扩展
create tablespace myspace
datafile 'F:\oracle\product\10.2.0\oradata\orcl\myspace.dbf'
size 15M
autoextend on;

–删除表空间同时删除表空间的所在物理文件
drop tablespace myspace including contents and datafiles;

–创建用户
create user myspace
identified by myspace
default tablespace myspace
temporary tablespace temp;

–查看用户
select * from dba_users;

–删除用户
drop user myspace;

drop user mytest;

–赋予权限
grant connect to MYSPACE;

grant resource to MYSPACE;

create table tb_calss(
id number,
name varchar2(20)
);

create table tb_student(
id number,
name varchar2(20),
sex char(2),
age number,
address varchar(50),
mobile varchar(11)
);
/*
     使用alter table语句增加，修改或者删除行
     假如只是增加或者修改一行，可以不加()
     drop就算只有一列都应该加()

*/
–增加字段
alter table tb_student
add (email varchar2(20),faname varchar2(20));

–删除字段  多了一个column关键字
alter table tb_student
drop column faname;

–修改字段
alter table tb_student
modify email varchar2(300);

select * from tb_student;

/*
       修改列名 如果觉得列名不合适，那么可以使用alter table语句的raname column
       子句改变列名
       语法：
            alter table table_name
            rename column column_name
            to nwe_column_name;
*/
alter table tb_student
rename column address
to addr;

/*     修改表名
     -要想改变表，视图，序列或者同义字的名称，可以执行rename语句
     语法：     
              rename table_name
              to new_table_name;
*/
rename tb_student
to tb_stu;

rename tb_stu
to tb_student;
 
/*
   -增加注释
     -当开发数据库应用程序的时候，为了描述表，列的作用，可以使用comment语句为
     表和列增加注释
     语法：
              comment on table table_name is 'text';
              comment on column table.column is 'text';

*/
comment on table tb_student is '学生表';
comment on column tb_student.name is '学生姓名';

–查看数据字典,可以查看用户所拥有的表
select * from user_tables;
select * from user_tab_comments;
select * from user_tab_comments where comments is not null;
select * from user_col_comments;
select * from user_col_comments where comments is not null;

/*
       删除一张表
        -表中的数据和结构会被删除           语法：
        -任何未决的事务被提交                   drop table table_name;
        -所有的索引被删除
        -不能回滚drop table 语句
       
        drop table table_name [cascade constraints] [purge];
        -cascade constraints用于指定级联删除从表的外键约束
        -purge用于指定彻底删除表（oracle10g新有的特性）
        注意：如果在两张表之间具有主外键关系，那么当删除主表的时候，必须指定cascade constraint子句
       
        恢复被删除的表
        -.在执行drop table 语句的时候，oracle会将被删除的表存放到数据库回收站中，
        从oracle10g开始，使用flashback table语句可以快速恢复被删除的表
        语法：
               flashback table table_name to before drop;
*/

/*
    截断表
     -当表结构必须保留，而表数据不再需要的时候，可以使用truncate table语句
     -删除表中所有的行
     语法：
               truncate table table_name;
    -当你使用truncate 不可以回滚          
*/

–彻底删除一张表
drop table table_name purge;
=======================================================================
–将commit 到 commit 之间的多条sql语句看做一个事务

–建表表的另外一种方式
create table tb_class2
as select * from tb_class;

select * from tb_class2;

–插入数据的另外一种方式
insert into tb_class2(id,name)
select id,name from tb_class;

commit;

–更新数据
update tb_class2
set name = 'class4' where id = 1;
–一次可以修改多列数据
update tb_class2
set id = 5,name = '美好'
where id = '2';

–删除数据
–删除  从 表 条件
delete from tb_class2
where id = 5;

–删除所有数据保留表结构（不加where条件），可以回滚
delete from tb_class2;
rollback;

–截断表，删除所有数据保留表结构，不可以回滚
truncate table tb_class2;

–删除表所有数据和表结构，不可以回滚
drop table tb_class2;
select * from tb_student;
select * from tb_class;

drop table tb_student purge;
drop table tb_class purge;

create table tb_class(
id number primary key,
name varchar2(30) not null,
monitor varchar(30) default 'wangyan'
);

create table tb_student(
id number primary key,
name varchar2(20) not null,
sex char(2) not null check(sex = '男' or sex = '女'),
age number check(age > 18 and age < 60),
email varchar2(50) unique,
class_id number references tb_class(id)
);

insert into tb_class(id,name)
values (1,'network');

insert into tb_class(id,name)
values (2,'application');

insert into tb_class(id,name)
values (3,'boolean');
===================================================================

-对几种关系的解决方式
–一对一 、一对多（多对一）、多对多
—

–一对一  1.主键关联
create table tb_card1(
id number primary key,
code varchar2(19)
);

create table tb_person1(
id number primary key,
name varchar2(20),
constraints tb_person1_fk foreign key (id) references tb_card1(id)
);

–多一字段来做外键关联
create table tb_card2(
id number primary key,
code varchar2(19)
);

create table tb_person2(
id number primary key,
name varchar2(20),
card_id number unique,
constraints tb_person2_fk foreign key (card_id) references tb_card2(id)
);

–学生表、班级表、课程表的关系处理
–学生表 班级表 多对一
–班级表 学生表 一对多
–学生表 课程表 多对多

–班级表
create table tb_class1(
id number primary key,
name varchar2(30) not null
);
–学生表
create table tb_student(
id number primary key,
name varchar2(20) not null,
sex char(2) not null check(sex = '男' or sex = '女'),
age number check(age > 18 and age < 60),
email varchar2(50) unique,
class_id number,
constraints tb_student_fk foreign key (class_id) references tb_class1(id)
);

–课程表
create table tb_course(
id number primary key,
name varchar2(30),
credit number
);

–创建一个中间表来处理多对多的关系
create table tb_student_course(
student_id number,
course_id number,
constraints tb_student_course_pk primary key (student_id,course_id),
constraints tb_st_cour_st_fk foreign key (student_id) references tb_student(id),
constraints tb_st_cour_co_fk foreign key (course_id) references tb_course(id)
);

create table tb_student_course1(
id number primary key,
student_id number,
course_id number,
constraints tb_student_course_unique unique (student_id,course_id),
constraints tb_st_cour_st_fk1 foreign key (student_id) references tb_student(id),
constraints tb_st_cour_co_fk1 foreign key (course_id) references tb_course(id)
);
==================================================================================

–字符串的连接操作
select ename || '的年薪是' || sal*12 from emp;

–null的使用
select * from emp where comm is null;
select * from emp where comm is  not null;

–定义别名 as可以有，也可没有 ""可以有，也可没有 ，但不能使用‘’
select ename as 姓名,sal 月薪,sal*12 "年薪"  from emp;

–distinct
select deptno from emp;
select distinct deptno from emp;

select * from emp where ename like '%A%';

–between…and…
select * from emp where sal between 1200 and 3000;
–in
select * from emp where deptno in(20,30);
–排序
select * from emp  order by sal desc;

–函数的使用
–lower把大写转小写，upper把小写转大写
select * from emp where lower(ename)='allen';

select upper('nijljfdljfld') from dual;

–initcap使字符串中的所有的单词的首字母变为大写
select initcap('tony allen') from dual;

–concat连接两个字符串
select concat('lan','guage') from emp;

–substr截取字符串，返回子串 从1开始 d但0、1效果一样
select substr('zhanghaicheng',6) from dual;
select substr('zhanghaicheng',0,5) from dual;
select substr('zhanghaicheng',1,5) from dual;

–length返回字符串的长度
select length('zhanghaicheng') from dual;

–instr(string,char)在一个字符串中搜索指定的字符，返回发现指定的字符的位置，从1开始
select instr('zhanghaicheng','e') from dual;

–rpad 在列的右边粘贴字符 lpad 在列的左边粘贴字符 9是显示的位数 '$'所粘贴的字符
select rpad(sal,9,'$') from emp;

–trim 删除首尾的空字符串
select trim(' zhanghaicheng  ') from dual;

–trim 删除首尾的h;
select trim('h'from 'helloWordh') from dual;

–trim 删除首的h
select trim(leading 'h' from 'helloWordh') from dual;
select trim(both 'h' from 'helloWordh') from dual;

–trim 删除尾的h
select trim(trailing 'h' from 'helloWordh') from dual;

–replace('string','s1','s2')
–string 希望被替换的字符或变量
–s1 需要被替换的字符串，s2替换的字符串
select replace('zhanghaicheng','hai','iah') from dual;

–数值函数round 进行四舍五入 3 是保留的小数位数
select round(89.3468,3) from dual;

–trunc截断数据 不进行四舍五入 3 是保留的小数位数
select trunc(89.3468,3) from dual;

–mod 两个数取模
select mod(3,2) from dual;

–日期函数
–sysdate 返回系统当前时间
select sysdate from dual;

–转换函数：to_number ,to_char, to_date
–to_number可将数字型字符串转化为number型数据
select to_number('344') from dual;

–to_char可将number型数据转化为字符串
select to_char(344) from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

–to_date 将日期型字符串转换为date
select to_date('2013-3-23','yyyy-mm-dd') from dual;
select to_date('2013-3-23','yyyy-mm-dd') as currdate from dual;

–常用函数
–nvl(expr1,expr2) 如果expr1不为null，返回expr1,为null，返回expr2
select ename,sal,nvl(comm,0) from emp;
select sal,(sal+nvl(comm,0))*12 from emp;
–nvl2(expr1,expr2,expr3) 如果expr1不为null，返回expr2 ，为null ，返回expr3
select sal,(sal+nvl2(comm,comm,0))*12 from emp;
–coalesce(expr1,expr2,…..)返回第一个不为null的表达式

–case表达式功能类似于if… else…
select ename,job,sal,
       case job when 'SALEMAN'then sal*0.4
                when 'MANAGER'then sal*0.7
                when 'CLERK'then sal+100000
       else sal end as salary
from emp;
 
–decode
=====================================================================