MySQL 学习笔记(动力节点课程)

146 阅读21分钟

视频链接: www.bilibili.com/video/BV1fx…

Mysql 安装

  1. dev.mysql.com/downloads/m… 此地址可以下载对应的mysql进行安装

  2. 配置环境变量 sudo vim ~/.zshrc export PATH=$PATH:/usr/local/mysql/bin

SQL, DB , DBMS 之间的关系

DB: DataBase(数据库,数据库实际上还是以文件形式存在)

DBMS: DataBase Manager System(数据库管理系统,常见的有Mysql,Oracle,SqlServer...)

SQL: 结构化查询语言,是一门标准通用的语言。标准的sql适合所有的数据库产品。 SQL属于高级语言。SQL语句在执行的时候,实际内部也会进行编译,然后再执行sql。(SQL语句的编译由数据库管理系统完成)

DBMS -(执行) ->SQL -(操作) ->DB

什么是表

表:table是数据库的基本组成单元,所有数据都以表格的形式组织,目的是可读性强

一个行包括行和列

行:被称为数据/记录(data)

列: 被称为字段(column)

每个字段包括哪些属性:字段名,数据类型,相关约束

SQL语句分类

DQL(数据查询语言):查询语句,凡是select语句都是DQL

DML(数据操作语言):insert delete update 对表中的数据进行增删改

DDL(数据定义语言):create drop alter 对表结构的增删改

TCL(事务控制语言):commit 提交事务 rollback 回滚事务

DCL(数据控制语言):grant 授权,revoke 撤销授权

导入数据库

  1. 登录mysql数据库管理系统

    命令: mysql -uroot -p

  2. 查看数据库命令

    show databases;(这个不是SQL语句,是mysql特有的命令)

  3. 创建数据库

    create database test;(这个不是SQL语句,是mysql特有的命令)

  4. 使用数据库

    use test;(这个不是SQL语句,是mysql特有的命令)

  5. 查看当前数据库有哪些表

    show tables;(这个不是SQL语句,是mysql特有的命令)

  6. 初始化数据

    source xxx.sql

  7. 删除数据库

    drop database 数据库名称;

  8. 查看表结构

    desc table名称;

  9. 查看当前使用的数据库

    select database();

  10. 查看当前使用的数据库版本号;

    select version();

  11. 结束sql语句命令

    \c

  12. 退出mysql

    \q 或者 exit

  13. 查看创建表的创建语句

    show create table 表名;

DQL

简单查询

给结果列重命名(使用 as 并且as可省略,如果别名需要中文使用单引号,因为标准sql使用单引号,使用双引号会导致其他类型数据库错误)

select ename,sal*12 as ‘年薪’ from emp;

条件查询

语法格式:(执行顺序,先from 然后where 最后select)

    select 
            字段,字段...
    from 
            表名
    where 
            条件;
            
            
   mysql> select ename from emp where sal=5000;

    +-------+

    | ename |

    +-------+

    | KING  |

    +-------+

    1 row in set (0.00 sec)

    \

    
    mysql> select sal from emp where ename='SMITH';

    +--------+

    | sal    |

    +--------+

    | 800.00 |

    +--------+

    1 row in set (0.00 sec)     
    
    找出工资高于3000的员工
    
    找出工资不等于3000的员工
    select ename from emp where sal<>3000;
    select ename from emp where sal!=3000;
    

between..and(闭区间)

 找出工资在10003000 的员工名称和薪资?
 方法1: select ename,sal from emp where sal>=1000 and sal <=3000;
 方法2: select ename,sal from emp where sal between 1000 and 3000;
 

is null 和 is not null

  null在数据库中不是一个值,不能用等号衡量,代表什么都没有,为空。必须使用 is null 或者 is not null
  
  
  找出津贴不为null的用户?
  select ename,comm from emp where comm is not null;
  
  找出津贴为null或者津贴为0的用户?
  select ename,comm from emp where comm is null or comm=0;
  
     

当AND 和 OR 同时出现的时候 AND 优先级大于OR,当不确定优先级时,加小括号提升优先级

or 和 and

 找出工作岗位是MANAGER和SALESMAN的员工?
 select ename,job from emp where job='MANAGER' or job ='SALESMAN'; 
 
 找出薪资大于3000的并且部门编号是20或者30部门的员工?
 

in(not in) 等同于or,in后面每一个值都不是区间

   找出工作岗位是MANAGER和SALESMAN的员工?
   select ename,job from emp where job in ('MANAGER','SALESMAN');
   
   找出工作岗位不是MANAGER和SALESMAN的员工?
   select ename,job from emp where job not in ('MANAGER','SALESMAN');

like(模糊查询)

在模糊查询中,% 代表任意多个字符 _代表任意一个字符

找出名字当中含有o的用户
 
select ename from emp where ename like '%o%';

找出名字中第二个字母是A的用户

select ename from emp where ename like '_o%';

找出名字中有下划线的用户
 
select name from t_user where name like '%\_%';

找出最后一个字母为T的用户

select ename from emp where ename like '%T';

order by desc / order by asc(默认升序)

  根据工资升序降序排列
  select ename,sal from emp order by sal(升序);
  select ename,sal from emp order by sal asc;(升序)
  select ename,sal from emp order by sal desc(降序);
  
  根据工资降序排列,当工资相同在按照名字升序排列
  
  select ename,sal from emp order by sal desc ,ename asc;
  
  总结: order by 多个条件用,隔开,条件越靠前作用越大,后面的条件有可能压根用不到
  
  根据第一列字段进行排序
  select ename,sal from emp order by 1;
  
  找出工作岗位时SALESMAN的员工,并且要求薪资的将序排列(先执行 from,再执行where ,再执行select,最后执行order by)
  
  select ename,job, sal from emp where job='SALESMAN' order by sal desc;
  
 

分组函数(自动忽略null)

count 计数 sum 求和 avg 平均值 max 最大值 min 最小值 所有的分组函数都是对某一组 数据进行操作的

   找出工资总和
   select sum(sal) from emp;
   找出最高工资
   select max(sal) from emp;
   找出最低工资
  select min(sal) from emp;
   找出平均工资
   select avg(sal) from emp;
   找出总人数
   select count(*) from emp;
   找出工资高于平均工资的员工
   select ename,sal from emp where sal>(select avg(sal) from emp);
   找出每个部门最高薪资,要求显示薪资大雨2500的数据
   方案一:不太好
   select max(sal) maxSal,deptno from emp  group by deptno having maxSal>2500;
   最佳写法:
    select max(sal) maxSal,deptno from emp  where sal>2000 group by deptno ;
   找出每个部门不同工作岗位的最高薪资
   select deptno,job,max(sal) from emp group by job,deptno order by deptno ;

+--------+-----------+----------+

| deptno | job       | max(sal) |

+--------+-----------+----------+

|     10 | MANAGER   |  2450.00 |

|     10 | PRESIDENT |  5000.00 |

|     10 | CLERK     |  1300.00 |

|     20 | CLERK     |  1100.00 |

|     20 | MANAGER   |  2975.00 |

|     20 | ANALYST   |  3000.00 |

|     30 | SALESMAN  |  1600.00 |

|     30 | MANAGER   |  2850.00 |

|     30 | CLERK     |   950.00 |

+--------+-----------+----------+

9 rows in set (0.00 sec)


   计算每个员工的年薪
   select ename,(sal+comm) salary from emp ;

 
   
+--------+---------+

| ename  | salary  |

+--------+---------+

| SMITH  |    NULL |

| ALLEN  | 1900.00 |

| WARD   | 1750.00 |

| JONES  |    NULL |

| MARTIN | 2650.00 |

| BLAKE  |    NULL |

| CLARK  |    NULL |

| SCOTT  |    NULL |

| KING   |    NULL |

| TURNER | 1500.00 |

| ADAMS  |    NULL |

| JAMES  |    NULL |

| FORD   |    NULL |

| MILLER |    NULL |

+--------+---------+


   

ifnull() 空处理函数,对null进行预处理

  计算每个员工的年薪
  select ename,(sal+ifnull(comm,0)) yearsal from emp ;
  

count(*) 和 count(具体某个字段)

count(*)一定是返回记录总条数

count(具体某个字段)返回的是具体字段中不为null的总条数

group by 和 having

分组函数不能直接用在where子句中是因为,group by 在where后执行,分组函数是在group by后面执行的

group by: 按照某个或者某些字段进行分组

having: 对分组后的数据进行再次过滤

分组函数一般都和group by 一起使用,所以被称为分组函数 当一条sql语句没有group by的时候,一张表中的数据自成一组

当一个sql中有group by 的时候,select后只能有分组函数和执行分组的字段。否则会报错。

如果能使用where 过滤的优先使用 where ,不能使用where再使用having

select job, max(sal) from emp group by job;

# 执行模板

   select 5(查询)
   ..
   from 1(从哪里查询)
   ..
   where 2 (一次过滤)
   ..
   group by 3(分组)
   ..
   having 4(再次过滤)
   ..
   order by 6(进行排序)
   ..

distinct(去重,只能出现在所有字段的最前面,如果后面有多个字段,则是字段联合去重)

不使用distinct
mysql> select job,ename from emp;

+-----------+--------+

| job       | ename  |

+-----------+--------+

| CLERK     | SMITH  |

| SALESMAN  | ALLEN  |

| SALESMAN  | WARD   |

| MANAGER   | JONES  |

| SALESMAN  | MARTIN |

| MANAGER   | BLAKE  |

| MANAGER   | CLARK  |

| ANALYST   | SCOTT  |

| PRESIDENT | KING   |

| SALESMAN  | TURNER |

| CLERK     | ADAMS  |

| CLERK     | JAMES  |

| ANALYST   | FORD   |

| CLERK     | MILLER |

+-----------+--------+


使用distinct

select distinct job from emp;

+-----------+

| job       |

+-----------+

| CLERK     |

| SALESMAN  |

| MANAGER   |

| ANALYST   |

| PRESIDENT |

+-----------+

统计岗位的数量?
mysql> select count(distinct job) from emp;

+---------------------+

| count(distinct job) |

+---------------------+

|                   5 |

+---------------------+

1 row in set (0.01 sec)

连接查询

什么是连接查询 在实际开发中,大部分情况下都不是从单表中查询数据,一般是多张表联合查询取出最终的结果。在实际开发中,一般一个业务都会对应多张表。

     根据表的连接方式来划分,包括:
 内连接:
     等值连接
     非等值连接
     自连接
  外连接:
      左外连接(左连接)
      右外连接(右连接)
      
  笛卡尔积现象:当两张表进行连接查询的时候,如果没有任何条件限制,查询的记录条数是两张表记录条数的乘积
  
  表的别名:
  select e.ename,d.dname from emp e,dept d;
  表的别名有什么好处?
  1 执行效率高
  2 可读性好
  
  怎么避免笛卡尔积现象? 添加条件进行过滤
  思考:避免了笛卡尔积现象,会减少记录的匹配次数吗?
  不会,次数还是一样的,只不过显示的是有效记录.
  
   select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno ;
SQL92 的老语法
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno ;
+--------+------------+

| ename  | dname      |

+--------+------------+

| SMITH  | RESEARCH   |

| ALLEN  | SALES      |

| WARD   | SALES      |

| JONES  | RESEARCH   |

| MARTIN | SALES      |

| BLAKE  | SALES      |

| CLARK  | ACCOUNTING |

| SCOTT  | RESEARCH   |

| KING   | ACCOUNTING |

| TURNER | SALES      |

| ADAMS  | RESEARCH   |

| JAMES  | SALES      |

| FORD   | RESEARCH   |

| MILLER | ACCOUNTING |

+--------+------------+

14 rows in set (0.00 sec)

\

内连接

等值连接

最大的特点是:条件是等量关系; 注意:内连接如果条件匹配不上就不查出来了

    SQL99 语法
...
    A
(inner) join
    B
on
    连接条件
where
    ...;
    
案例:查询每个员工的部门名称,要求显示员工名和部门名
SQL92
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno ;
 
select  
    e.ename,d.dname 
from 
    emp e 
join 
    dept d 
on 
    e.deptno=d.deptno ;

SQL99(inner 可省略)

select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;

+--------+------------+

| ename  | dname      |

+--------+------------+

| SMITH  | RESEARCH   |

| ALLEN  | SALES      |

| WARD   | SALES      |

| JONES  | RESEARCH   |

| MARTIN | SALES      |

| BLAKE  | SALES      |

| CLARK  | ACCOUNTING |

| SCOTT  | RESEARCH   |

| KING   | ACCOUNTING |

| TURNER | SALES      |

| ADAMS  | RESEARCH   |

| JAMES  | SALES      |

| FORD   | RESEARCH   |

| MILLER | ACCOUNTING |

+--------+------------+



非等值连接

最大的特点是:条件不是等量关系

案例:找出每个员工的工资等级,要求显示员工名,工资,工资等级

select 
    e.ename,e.sal,s.grade 
from 
    emp e 
inner join 
    salgrade s 
on 
    e.sal 
between 
    s.losal 
and 
    s.hisal;

自连接

特点: 一张表看作两张表,自己连接自己

找到每个员工的上级领导,要求显示员工名和自己对应的领导名(这个问题应该使用外连接,使用内连接会导致KING丢失)
select 
    e.ename,m.ename 
from 
    emp e 
inner join
    emp m 
on 
    e.mgr=m.empno;

+--------+-------+

| ename  | ename |

+--------+-------+

| SMITH  | FORD  |

| ALLEN  | BLAKE |

| WARD   | BLAKE |

| JONES  | KING  |

| MARTIN | BLAKE |

| BLAKE  | KING  |

| CLARK  | KING  |

| SCOTT  | JONES |

| TURNER | BLAKE |

| ADAMS  | SCOTT |

| JAMES  | BLAKE |

| FORD   | JONES |

| MILLER | CLARK |

+--------+-------+

13 rows in set (0.00 sec)

外连接写法:(outer 可以省略)

select e.ename,m.ename managerName from emp e left outer join emp m on e.mgr=m.empno;

+--------+-------------+

| ename  | managerName |

+--------+-------------+

| SMITH  | FORD        |

| ALLEN  | BLAKE       |

| WARD   | BLAKE       |

| JONES  | KING        |

| MARTIN | BLAKE       |

| BLAKE  | KING        |

| CLARK  | KING        |

| SCOTT  | JONES       |

| KING   | NULL        |

| TURNER | BLAKE       |

| ADAMS  | SCOTT       |

| JAMES  | BLAKE       |

| FORD   | JONES       |

| MILLER | CLARK       |

+--------+-------------+

14 rows in set (0.00 sec)

外连接

外连接的特点: 主表中的数据无条件查询出来

什么是外连接,和内连接有什么区别? 内连接: 假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接。AB两张表没有主副之分,两张表是平等的。

外连接: 假设A和B两张表进行连接,使用外连接的话,AB两张表中有一张表是主表,一张表是副表就,主要查询主表中的数据,捎带着查询副表,当副表中的数据没有和猪表中的数据匹配上,副表自动模拟出NULL与之匹配。

外连接的分类: 左外连接(左连接):表示左边的这张表是主表 右外连接(右连接):表示右边的这张表是主表

左连接有有连接的写法,右连接有左连接的写法。

   案例:找出那个部门没有员工
 select d.* from dept d left join emp e  on d.deptno=e.deptno where e.deptno is null;

+--------+------------+--------+

| DEPTNO | DNAME      | LOC    |

+--------+------------+--------+

|     40 | OPERATIONS | BOSTON |

+--------+------------+--------+

1 row in set (0.00 sec)

多张表连接

语法:
 ...
    A
 join 
    B
 on
 ...
 join 
    C
 on 
    ...

案例:找出每一个员工的部门名称以及工资等级
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;

+--------+------------+-------+

| ename  | dname      | grade |

+--------+------------+-------+

| SMITH  | RESEARCH   |     1 |

| ALLEN  | SALES      |     3 |

| WARD   | SALES      |     2 |

| JONES  | RESEARCH   |     4 |

| MARTIN | SALES      |     2 |

| BLAKE  | SALES      |     4 |

| CLARK  | ACCOUNTING |     4 |

| SCOTT  | RESEARCH   |     4 |

| KING   | ACCOUNTING |     5 |

| TURNER | SALES      |     3 |

| ADAMS  | RESEARCH   |     1 |

| JAMES  | SALES      |     1 |

| FORD   | RESEARCH   |     4 |

| MILLER | ACCOUNTING |     2 |

+--------+------------+-------+

14 rows in set (0.00 sec)

案例:找出每一个员工的部门名称以及工资等级以及上级领导

select     e.ename,d.dname,s.grade,m.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 m on m.empno=e.mgr;

+--------+------------+-------+-------+

| ename  | dname      | grade | ename |

+--------+------------+-------+-------+

| SMITH  | RESEARCH   |     1 | FORD  |

| ALLEN  | SALES      |     3 | BLAKE |

| WARD   | SALES      |     2 | BLAKE |

| JONES  | RESEARCH   |     4 | KING  |

| MARTIN | SALES      |     2 | BLAKE |

| BLAKE  | SALES      |     4 | KING  |

| CLARK  | ACCOUNTING |     4 | KING  |

| SCOTT  | RESEARCH   |     4 | JONES |

| KING   | ACCOUNTING |     5 | NULL  |

| TURNER | SALES      |     3 | BLAKE |

| ADAMS  | RESEARCH   |     1 | SCOTT |

| JAMES  | SALES      |     1 | BLAKE |

| FORD   | RESEARCH   |     4 | JONES |

| MILLER | ACCOUNTING |     2 | CLARK |

+--------+------------+-------+-------+

子查询

什么是子查询?子查询可以在哪里出现 select 语句中嵌套select语句,被嵌套的select语句是子查询 子查询可以出现在哪里? select ..(select) from ..(select) where ..(select)

where 中使用子查询

select * from emp where sal>(select avg(sal) from emp);
+-------+-------+-----------+------+------------+---------+------+--------+

| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |

+-------+-------+-----------+------+------------+---------+------+--------+

|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |

|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |

|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |

|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |

|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |

|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |

+-------+-------+-----------+------+------------+---------+------+--------+

6 rows in set (0.00 sec)


from 中使用子查询

案例:找出每个部门平均薪水的薪资等级

mysql> select t.*,s.grade from (select deptno,avg(sal) avgsal from emp group by deptno) t join salgrade on t.avgsal between s.losal and s.hisal;

ERROR 1054 (42S22): Unknown column 's.grade' in 'field list'

mysql> select t.*,s.grade from (select deptno,avg(sal) avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;

+--------+-------------+-------+

| deptno | avgsal      | grade |

+--------+-------------+-------+

|     20 | 2175.000000 |     4 |

|     30 | 1566.666667 |     3 |

|     10 | 2916.666667 |     4 |

+--------+-------------+-------+


案例 :找出每个部门平均的薪资等级

select e.deptno,avg(s.grade) from emp e join  salgrade s on e.sal between s.losal and s.hisal group by e.deptno ;

+--------+--------------+

| deptno | avg(s.grade) |

+--------+--------------+

|     20 |       2.8000 |

|     30 |       2.5000 |

|     10 |       3.6667 |

+--------+--------------+

3 rows in set (0.00 sec)

select 后使用子查询

   案例:找出每个员工所在部门的部门名称,要求显示部门员工名和部门名?
   
   select e.ename ,(select d.dname from dept d where e.deptno=d.deptno) dname from emp e;

+--------+------------+

| ename  | dname      |

+--------+------------+

| SMITH  | RESEARCH   |

| ALLEN  | SALES      |

| WARD   | SALES      |

| JONES  | RESEARCH   |

| MARTIN | SALES      |

| BLAKE  | SALES      |

| CLARK  | ACCOUNTING |

| SCOTT  | RESEARCH   |

| KING   | ACCOUNTING |

| TURNER | SALES      |

| ADAMS  | RESEARCH   |

| JAMES  | SALES      |

| FORD   | RESEARCH   |

| MILLER | ACCOUNTING |

+--------+------------+

14 rows in set (0.00 sec)

union(可以将查询结果集相加)

可以解决任意两张无关的表的结果进行相加(需要union 前后列数一样)

找出工作岗位是SALESMAN和MANAGER的员工?


select ename,job from emp where job in ('SALESMAN','MANAGER');

+--------+----------+

| ename  | job      |

+--------+----------+

| ALLEN  | SALESMAN |

| WARD   | SALESMAN |

| JONES  | MANAGER  |

| MARTIN | SALESMAN |

| BLAKE  | MANAGER  |

| CLARK  | MANAGER  |

| TURNER | SALESMAN |

+--------+----------+

7 rows in set (0.00 sec)

\


mysql> select ename,job from emp where job='SALESMAN' or job='MANAGER';

+--------+----------+

| ename  | job      |

+--------+----------+

| ALLEN  | SALESMAN |

| WARD   | SALESMAN |

| JONES  | MANAGER  |

| MARTIN | SALESMAN |

| BLAKE  | MANAGER  |

| CLARK  | MANAGER  |

| TURNER | SALESMAN |

+--------+----------+

7 rows in set (0.00 sec)


\


mysql> select ename,job from emp where job ='SALESMAN' union select ename,job  from emp where job='MANAGER';

+--------+----------+

| ename  | job      |

+--------+----------+

| ALLEN  | SALESMAN |

| WARD   | SALESMAN |

| MARTIN | SALESMAN |

| TURNER | SALESMAN |

| JONES  | MANAGER  |

| BLAKE  | MANAGER  |

| CLARK  | MANAGER  |

+--------+----------+

7 rows in set (0.00 sec)

limit是 Mysql特有的,其他数据库没有

limit 取结果集中的部分数据。

语法机制。limit startIndex,length

    
    
    startIndex 表示起始位置。从0开始,降序2
    length 表示取几个
    
    案例:取出工资前5名的员工
    select ename,sal from emp order by sal desc limit 0,5;
    select ename,sal from emp order by sal desc limit 5;
    
    

通用的标准分页sql

每页显示3条记录 第一页: 0,3, 第二页: 3,3, 第三页: 6,3, 第四页: 9,3, 第五页:12,3,

第n页: (pageNum-1)*pageSize,pageSize pageNum显示第几页,pageSize 每页显示条数

DDL

创建表

```
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
字段名4 数据类型,
)

MySQL 中数据类型

int 整数型(intbigint 长整型(long)
float 浮点型(floatdoublechar 定长字符串(String)
varchar 可变长字符串(StringBuffer/StringBuilder)
date  日期类型(对象java中java.sql.Date类型)
blob  二进制大对象(存储图片,视频等流媒体信息对应java Object)
CLOB  字符串大对象(存储加大文本,比如,可以存储4G字符串,对应java Object)

charvarchar 怎么选择
在实际的开发中,当某个字段的长度不发生改变的时候,是定长的。例如:性别,生日等都是采用char。
当一个字段的长度不确定,例如:简介,名称都是采用varcharblobclob类型的使用?

```

表名在数据库中一般都是以t_或者tbl_开始

创建学生表: 学生信息表: 学号,姓名,性别,班级编号,生日 学号:bigint 姓名:varchar 性别:char 班级编号:int 生日:char

create t_student table( no bigint, name varchar(255), sex char(1), classno varchar(255), birth char(10) );

DML

insert语句插入数据

 语法格式:
     insert into 表名(字段名1,字段名2,字段名3,字段名4values(值1,值2,值3,值4)
     

删除表

drop table 表名 if  exists   表名 // 当这个表存在的话删除

create t_student table( no bigint, name varchar(255), sex char(1) default 1, classno varchar(255), birth char(10) )

插入单行数据

  insert into t_student(name,sex) values('李四','0');

Query OK, 1 row affected (0.00 sec)

插入多行数据

  insert into t_student (name,sex) values ('王五','0'),('赵六','0');

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

表的复制

create table 表名 as select 语句  将查询结果当作表创建出来
insert into dept1 select * from dept; 将dept的查询结果插入到dept1中

修改数据 UPDATE

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

删除数据 DELETE

语法格式: delete from 表名 where 条件 ; 没有条件全部删除

delete from 不会释放真实数据,可以进行恢复

怎么删除大表?(重点)

truncate table 表名; 不可回滚,数据永久丢失,但是删除速度快

增删改查:CRUD Create Retrieve Update Delete

约束(Constraint)

什么事约束?常见的约束有哪些呢?

      非空约束(not null) 约束的字段不能为NULL
      唯一性约束(unique)   约束的字段不能重复
      主键约束(primary key)约束的字段既不能为NULL也不能重复
      外键约束(foreign key)

在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中数据的合法性,有效性,完整性。

非空约束

create table t_user(

    -> id int,

    -> username varchar(255) not null,

    -> password varchar(255)

    -> );

唯一约束,但是可以为NULL

// 单个字段unique (列级约束)

create table t_user(

    -> id int,

    -> username varchar(255) unique,

    -> password varchar(255) unique

    -> );
 
 // 多个字段联合 unique(表级约束)
  create table t_user(

    -> id int,

    -> username varchar(255) ,

    -> password varchar(255),
 
 -> unique(username,password)

    -> );
 
 

主键约束

怎么给一张表添加主键约束呢?

create table t_user(
id int primary key,
username varchar(255),
email varchar(255)
);
主键有什么作用?
    表的设计三范式有要求,第一范式要求任何表中都应该有一个主键
    主键的作用:主键值是这行记录在这张表中的唯一标识(就像人的身份证号码一样)
主键不能为NULL 不能重复
主键约束:primary key
主键字段:  id
主键值:id 字段的值

主键的分类

根据主键字段的字段数量来划分:
    单一主键(推荐的,常用的)
    复合主键(多个字段联合起来添加一个主键约束)(复合主键不建议使用,因为复合主键违背三范式)
    
根据主键性质来划分:
    自然主键:主键值最好就是一个和业务没有任何关系的自然数。
    业务主键:主键值和系统的业务挂钩,例如:拿着银行卡的卡号做主键,拿着身份证号码作为主键(不推荐使用)。最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候,主键值可能也需要随着发生变化,但有的时候没有办法变化,因为变化可能会导致主键值重复。
  

一张表只能有一个主键约束

使用表级约束方式定义主键

create table t_user(

    -> id int,

    -> username varchar(255),

    -> email varchar(255),

    -> primary key(id)

    -> );

Query OK, 0 rows affected (0.00 sec)

复合主键

primary key(id,username)

create table t_user(

    -> id int,

    -> username varchar(255),

    -> email varchar(255),

    -> primary key(id,username)

    -> );

Query OK, 0 rows affected (0.00 sec)

Mysql提供主键值自增

create table  t_user(

    -> id int primary key auto_increment,

    -> username varchar(255)

    -> );

外键约束(可以为NULL)

外键约束: foreign key 外键字段: 添加有外键约束的字段 外键值: 外键字段中的每一个值

业务背景

请设计数据库表,用来维护学生和班级的信息? 第一种方案 no(pk) name classno classname

1 zs1. 101. 高三1班 2 zs2。 102。 高三2班 3。 zs3。 102 高三2班

第二种方案

t_class 班级表

cno(pk) cname 101. 高三1班 102 高三2班

t_student 学生表 sno(pk) sname。 classno(该字段添加外键约束fk) 1 zs1 101 2 zs2 101 3 zs3 102

t_class为父表 ,t_student 为子表。创建表的时候先创建父表,再创建子表。删除表的时候,先删除子表,再删除父表。添加数据的时候先添加父表,在创建子表。删除数据的时候先删除父表,再删除子表。

外键约束再引用其他表中的字段,被引用的字段不一定一定是主键,但是至少要有unique约束

存储引擎(了解)

完整的键表语句 CREATE TABLE t_x( id int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

练习题

   1 取得每个部门最高薪水的人员名称
select e.ename,t.* from (select deptno,max(sal) maxsal from emp  group by  deptno) t join emp e on ( e.sal=t.maxsal and e.deptno=t.deptno) ;

+-------+--------+---------+

| ename | deptno | maxsal  |

+-------+--------+---------+

| BLAKE |     30 | 2850.00 |

| SCOTT |     20 | 3000.00 |

| KING  |     10 | 5000.00 |

| FORD  |     20 | 3000.00 |

+-------+--------+---------+

4 rows in set (0.00 sec)

事务(Transaction)

什么是事务? 一个事务是一个完整的业务逻辑单元,不可再分 比如银行账户转账,从A账户向B账户转账 1000,需要执行两条update语句: update t_act set balance=balance -1000 where actno='act01';

update t_act set balance=balance +1000 where actno='act02';

和事务相关的语句只有DML (insert ,delete,update) 因为这三个语句都是和数据库表中的数据相关的. 事务的存在就是为了保证数据的完整性和安全性。

假设所有的业务都能够使用一条DML搞定,就不需要使用事务。 但实际情况不是这样的,通常一个业务都需要多条DML语句共同联合完成。

索引(index)

   什么是索引?有什么用?
   索引就相当于一本书的目录,通过目录可以快速查找到对应的资源。
   在数据库方面,查询一张表的时候有两种检索方式:
       第一种方式:全表扫描
       第二种方式:根据索引检索(效率很高)
   
   索引为什么可以提高检索效率呢?
       其实最根本的原理就是缩小扫描范围。
       
    索引虽然可以提高检索效率,但是不能随意的添加索引,因为索引也是数据库当中的对象,也需要数据库不断维护。是有维护成本的。比如,表中的数据经常被修改,这样就不适合添加索引,因为数据一旦发生修改,索引需要重新排序,进行维护。
    添加索引是给某一个字段就,或者说某些字段添加索引。
    select ename,sal from emp where  ename = 'SMITH';
    当ename字段上没有添加索引的时候,以上sql语句会进行全表扫描,扫描ename字段中所有的数据。
    当ename字段上添加了索引的时候,以上sql语句会根据索引进行扫描,快速定位。
   怎么创建索引?怎么删除索引?
   create index 索引名称 on 表名(字段名);
   删除索引
   drop index 索引名称 on 表名; 
   什么时候考虑给字段添加索引?(满足什么条件)
   1数据量庞大
   2该字段中很少有DML操作
   3该字段经常出现在where子句中
   
  查看sal语句的执行计划
  explain select ename,sal from emp where sal=5000;
  

主键 和有unique 约束 的字段会自动添加索引


mysql> create index emp_sal_index on emp(sal);

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select ename,sal from emp where sal=5000;

+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

|  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 |   100.00 | NULL  |

+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.01 sec)

\


mysql> select ename,sal from emp where sal=5000;

+-------+---------+

| ename | sal     |

+-------+---------+

| KING  | 5000.00 |

+-------+---------+

1 row in set (0.00 sec)

\


mysql> drop index emp_sal_index on emp;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

\


mysql> explain select ename,sal from emp where sal=5000;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

\


mysql> select ename,sal from emp where sal=5000;

+-------+---------+

| ename | sal     |

+-------+---------+

| KING  | 5000.00 |

+-------+---------+

1 row in set (0.00 sec)

\



   索引的分类?
   单一索引:给单个字段添加索引
   复合索引:给多个字段联合起来添加一个索引
   主键索引:主键上会自动添加索引
   唯一索引:有unique约束的字段上会自动添加索引


  索引什么时候失效?
  select ename from  emp where ename like '%A%';
  模糊查询的时候,如果第一个通配符是% 索引会失效

视图

站在不同的角度去看数据(同一张表的数据,通过不同的角度去看待)
创建视图:create view myview as select empno,ename from emp;
删除试图:drop view myview
注意:只有DQL语句才能以视图对象创建出来

对试图执行CRUD会影响原表的数据。

试图的作用:
隐藏表的实现细节 。保密级别较高的系统,数据库值对外提供相关试图,java程序员只对视图对象进行CRUD。

DBA命令

将数据库中的数据导出和导入
导出整个库
mysqldump bjpowernode>/路径/bjpowernode.sql -uroot -p
导出库中某个表
mysqldump bjpowernode emp>/路径/bjpowernode.sql -uroot -p
导入数据
create database bjpowernode;
use bjpowernode;
source ~/bjpowernode.sql;

三范式

第一范式:所有的表都要有主键,并且每个字段要保持原子性,不可再分 第二范式:建立在第一范式的基础之上,所有非主键字段完全依赖主键,不能产生部分依赖。 多对多?三张表,关系表两个外键 第三范式:建立在第二范式基础之上,所有非主键字段直接依赖主键,不能产生传递依赖。 一对多?两张表,多的表有外键

一对一设计? 两种方案

主键共享

image.png

外键唯一 image.png

练习题

1 取出每个部门最高薪水的人员名称

select e.ename ,t.* from emp e join  (select deptno,max(sal) as maxsal from emp group by deptno) t on (t.deptno=e.deptno and t.maxsal=e.sal);

+-------+--------+---------+

| ename | deptno | maxsal  |

+-------+--------+---------+

| BLAKE |     30 | 2850.00 |

| SCOTT |     20 | 3000.00 |

| KING  |     10 | 5000.00 |

| FORD  |     20 | 3000.00 |

+-------+--------+---------+

4 rows in set (0.00 sec)
2 哪些人的薪水在部门平均薪水之上
mysql> select e.ename,t.* from emp e join (select deptno, avg(sal) avgsal from emp group by deptno) t on e.sal>t.avgsal and e.deptno=t.deptno;

+-------+--------+-------------+

| ename | deptno | avgsal      |

+-------+--------+-------------+

| ALLEN |     30 | 1566.666667 |

| JONES |     20 | 2175.000000 |

| BLAKE |     30 | 1566.666667 |

| SCOTT |     20 | 2175.000000 |

| KING  |     10 | 2916.666667 |

| FORD  |     20 | 2175.000000 |

+-------+--------+-------------+

6 rows in set (0.00 sec)

3 取得部门中所有人的平均薪水等级

select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;

+--------+--------------+

| deptno | avg(s.grade) |

+--------+--------------+

|     20 |       2.8000 |

|     30 |       2.5000 |

|     10 |       3.6667 |

+--------+--------------+

3 rows in set (0.00 sec)

4 不能用组函数max,取的最高薪水
第一种方案

select ename,sal from emp order by sal desc limit 1 ;

+-------+---------+

| ename | sal     |

+-------+---------+

| KING  | 5000.00 |

+-------+---------+

1 row in set (0.00 sec)

第二种方案 表的自连接

select sal from emp where sal not in (select  e.sal from emp e join emp max on max.sal>e.sal);

+---------+

| sal     |

+---------+

| 5000.00 |

+---------+

1 row in set (0.01 sec)

4 取得平均薪水最高的部门的部门编号

方案一:
select deptno from emp group by deptno order by avg(sal) desc limit 1;

+--------+

| deptno |

+--------+

|     10 |

+--------+

1 row in set (0.00 sec)

方案二 使用max