SQL笔记

多对多？三张表，关系表两个外键。

            t_student学生表
            sno(pk)        sname
            -------------------
            1                张三
            2                李四
            3                王五

            t_teacher 讲师表
            tno(pk)        tname
            ---------------------
            1                王老师
            2                张老师
            3                李老师

            t_student_teacher_relation 学生讲师关系表
            id(pk)        sno(fk)        tno(fk)
            ----------------------------------
            1                1                3
            2                1                1
            3                2                2
            4                2                3
            5                3                1
            6                3                3

一对多？两张表，多的表加外键。

            班级t_class
            cno(pk)            cname
            --------------------------
            1                    班级1
            2                    班级2

            学生t_student
            sno(pk)            sname                classno(fk)
            ---------------------------------------------
            101                张1                        1
            102                张2                        1
            103                张3                        2
            104                张4                        2
            105                张5                        2

一对一设计有两种方案：主键共享
            t_user_login  用户登录表
            id(pk)        username            password
            --------------------------------------
            1                zs                    123
            2                ls                    456

            t_user_detail 用户详细信息表
            id(pk+fk)    realname            tel            ....
            ------------------------------------------------
            1                张三                1111111111
            2                李四                1111415621

        一对一设计有两种方案：外键唯一。
            t_user_login  用户登录表
            id(pk)        username            password
            --------------------------------------
            1                zs                    123
            2                ls                    456

            t_user_detail 用户详细信息表
            id(pk)       realname            tel                 userid(fk+unique)....
            -----------------------------------------------------------
            1                张三                1111111111          2
            2                李四                1111415621        1
            

登录：mysql -localhost -uroot -p

展示所有的数据库：show databases;

创建数据库：create database   数据库名；

删除数据库：drop database  数据库名；

使用（进入）数据库：use 数据库名；

\c 结束当前sql语句,\q 退出数据库；

is null      is not null          where comm is not null;

in    not  in  select * from 数据库名 where sal in(800, 5000); // in后面的值不是区间，是具体的值。

order by 字段名 asc; // 升序    order by sal desc; // 降序

越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候，才会启用后面的字段。

select ename,sal from emp order by sal desc , ename asc;

like模糊查询    %匹配多个字符    _匹配一个字符  where ename like '%O%   \转义字符        like  '%\_%'

分组函数count 计数sum 求和avg 平均值max 最大值min 最小值   会自动忽略NULL。其他和NULL计算会变成NULL

分组函数必须在group by 后面才能使用，分组函数不可直接使用在where子句当中 

不加group by  的自成一组       group by 后面可以接多个字段用来联合查询

select     分组函数，按照字段分组的字段 A   from       group by   字段A

当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。

select        5
                    ..            
                from            1
                    ..
                where            2
                    ..
                group by        3
                    ..
                having        4
                    ..
                order by        6

select distinct job from emp; // distinct关键字去除重复记录。         distinct   adj.不同的

distinct只能出现在所有字段的最前面。     字段联合去重     select distinct deptno,job from emp

select count(distinct job) from emp;

内连接：    得匹配上，匹配不上就不查（显示）了

select                 条件是等量关系。
        e.ename,d.dname
    from
        emp e
    inner join
        dept d
    on                                on后面是（连接条件）
        e.deptno = d.deptno;                       

select 
    e.ename,e.sal,s.grade
from
    emp e
join
    salgrade s                       连接条件中的关系是非等量关系
on
    e.sal between s.losal and s.hisal;

自连接：最大的特点是：一张表看做两张表。自己连接自己。

select 
    a.ename   '员工名',b.ename   '领导名'
from
    emp a
join
    emp b
on
    a.mgr = b.empno;

外连接：特点（主表的数据无条件的全部查询出来。）
        假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

左连接表示左边的这张表是主表。右连接表示右边的这张表是主表

select 
    d.*
from
    emp e
right join
    dept d       （这里d作为主表，显示的就是d表，而e表中有则对应，没有则为NULL)
on
    e.deptno = d.deptno
where
    e.empno is null;     (这里再做完上述步骤后，再次进行筛选判断，此时筛选的已经建立外连接后的表）

三张表的连接查询

    A
    join
        B

    on

       ...
    join
        C
    on
        ...
    
    表示：A表和B表先进行表连接，连接之后A表继续和C表进行连接。

select 
        e.ename,d.dname,s.grade
    from
        emp e
    join
        dept d
    on
        e.deptno = d.deptno
    join
        salgrade s
    on
        e.sal between s.losal and s.hisal;
    

 可加入外连接

select 
        e.ename '员工',d.dname,s.grade,e1.ename '领导'
    from
        emp e
    join
        dept d
    on
        e.deptno = d.deptno
    join
        salgrade s
    on
        e.sal between s.losal and s.hisal
    left join
        emp e1
    on
        e.mgr = e1.empno;

子查询

select语句当中嵌套select语句，被嵌套的select语句是子查询。

子查询可以出现在
        select
            ..(select).
        from
            ..(select).
        where
            ..(select).

①select * from emp where sal > (select avg(sal) from emp);

②elect 
    t.*,s.grade
from
    (select deptno,avg(sal) as avgsal from emp group by deptno) t      将查询结果当作临时表 t
join
    salgrade s
on
    t.avgsal between s.losal and s.hisal;

③select 
    e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname

（会查出ename和dname)
  from 
    emp e;

union    可以将查询结果集相加     两张无关联的表中数据也可以拼在一起显示，但是要求列数相同

select ename from emp
union
select dname from dept;

limit:limit取结果集中的部分数据，   limit startIndex, length    startIndex表示起始位置，从0开始 length表示取几个                        limit是sql语句最后执行的一个环节： 
    select        5
        ...
    from            1
        ...        
    where            2
        ...    
    group by        3
        ...
    having        4
        ...
    order by        6
        ...
    limit            7
        ...;

select ename,sal from emp order by sal desc limit 3,6;       找出工资排名在第4到第9名的员工

创建表：       表名在数据库当中一般建议以：t_或者tbl_开始。

建表语句的语法格式：
        create table 表名(
            字段名1 数据类型,
            字段名2 数据类型,
            字段名3 数据类型,
            ....

           字段名n 数据类型
        );

在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。

创建学生表：
        学生信息包括：
            学号、姓名、性别、班级编号、生日
            学号：bigint
            姓名：varchar
            性别：char
            班级编号：int
            生日：char
        
        create table t_student(
            no bigint,
            name varchar(255),
            sex char(1) default 1,      此时这里加了default 默认值是1
            classno varchar(255),     不加的花默认值为NULL
            birth char(10)
        );

inserr语句插入数据

insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....)
        要求：字段的数量和值的数量相同，并且数据类型要对应相同。

insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban', '1950-10-12');

// 一次插入多行数据
    insert into t_student
        (no,name,sex,classno,birth) 
    values
        (3,'rose','1','gaosi2ban','1952-12-14'),(4,'laotie','1','gaosi2ban','1955-12-14');

表的复制
    语法：
        create table 表名 as select语句;
        将查询结果当做表创建出来。

将查询结果插入到一张表中
        insert into dept1 select * from dept;

修改数据：update

    语法格式：
        update 表名 set 字段名1=值1,字段名2=值2... where 条件;

    注意：没有条件整张表数据全部更新。

update dept1 set loc = 'SHANGHAI', dname = 'RENSHIBU' where deptno = 10;

删除数据？
    语法格式：
        delete from 表名 where 条件;
    
    注意：没有条件全部删除。

delete from dept1 where deptno = 10;

约束(Constraint)：NULL不是一个值，两个NULL是不相等的

        非空约束(not null)：约束的字段不能为NULL
        唯一约束(unique)：约束的字段不能重复
        主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）
        外键约束(foreign key)：...（简称FK）

非空约束 not null                                 默认insert不写具体时插入的是NULL                  not null约束只有列级约束。没有表级约束。

create table t_user(
        id int,
        username varchar(255) not null,
        password varchar(255)
    );

    insert into t_user(id,password) values(1,'123');
    ERROR 1364 (HY000): Field 'username' doesn't have a default value

    insert into t_user(id,username,password) values(1,'lisi','123');

唯一性约束（unique）  唯一约束修饰的字段具有唯一性，不能重复。但可以为NULL。

create table t_user(
            id int,
            username varchar(255) unique  // 列级约束                一夫一妻
        );

给两个列或者多个列添加unique

create table t_user(
            id int, 
            usercode varchar(255),
            username varchar(255),
            unique(usercode,username) // 多个字段联合起来添加1个约束unique 表级约束 多夫多妻
        );                                                 只有所有字段都为相同时才不可以insert

主键约束(Primary Key)       主键的特点：不能为NULL，也不能重复。一张表的主键约束只能有1个。（必须记住）

create table t_user(
            id int primary key,  // 列级约束
            username varchar(255),
            email varchar(255)
        );

insert into t_user(username,email) values('jack','jack@123.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

mysql提供主键值自增：

create table t_user(
            id int primary key auto_increment, // id字段自动维护一个自增的数字，从1开始，以1递增。
            username varchar(255)
        );

外键约束

create table t_class(
            cno int,
            cname varchar(255),
            primary key(cno)
        ); 

        create table t_student(
            sno int,
            sname varchar(255),
            classno int,
            primary key(sno),
            foreign key(classno) references t_class(cno)//被引用的字段不一定是主键，但至少具有unique约束。

        );

索引底层采用的数据结构是：B + Tree

创建索引对象:create index 索引名称 on 表名(字段名);

create index emp_sal_index on emp(sal);

删除索引对象：drop index 索引名称 on 表名; 

索引什么时候失效？
        select ename from emp where ename like '%A%';
        模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

将数据库当中的数据导出
        在windows的dos命令窗口中执行：（导出整个库）
            mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p333
        
        在windows的dos命令窗口中执行：（导出指定数据库当中的指定表）
            mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot –p123
导入数据
        create database bjpowernode;
        use bjpowernode;
        source D:\bjpowernode.sql

三范式都是哪些？    目的：不会出现数据冗余。 在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。

        第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。（列不可再分，也即属性不可再分）
        第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。（即一张表只表达一层含义，只描述一件事）

        第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。（表中的每一列和主键都是直接依赖关系，而不是间接依赖）（间接依赖：可通过连表查询查到相关属性）

数据库设计的范式和数据库的查询性能很多时候是相悖的，我们需要根据实际的业务情况做一个选择：
  - 查询频次不高的情况下，我们更倾向于提高数据库的设计范式，从而提高存储效率（比如说通过连表去查询，不设计新的属性)
  - 查询频次较高的情形，我们更倾向于牺牲数据库的规范度，降低数据库设计的范式，允许特定的冗余，从而提高查询的性能（在可以通过连表查询查询数据的情况下设计新的属性来代替连表查询得到的属性，即一个属性对应了两张表）