MySQL数据库完整笔记

MySQL

基本逻辑结构

Mysql

1.通过（用户）建立连接

2.选择数据库

3.数据库内部由一些表和其他数据库对象组成

用户->数据库->表

Oracle

1.选择数据库

2.数据库里包含很多个用户，选择某个用户进行连接

3.用户有一些表和其他数据库对象组成

数据库->用户->表

服务命令：

net stop mysql 停服务

net start mysql 起服务

sc delete mysql 删除服务

关系型数据库的操作语言：SQL

SQL语句： 结构化查询语言

DQL：数据查询语言：mysql数据库本身对多表友好，对子查询支持不太好

DML: 数据操作语言（增加数据，修改数据，删除数据）

DDL: 数据定义语言

DCL: 数据控制语言（数据使用的权限）

TPL: 事务处理语言

Sql：不区分大小写（Oracle支持部分不区分大小写；MYSQL不一定，默认真正的不区分大小写；有一个配置选项）

'mysql'linux 可以调整，windows完全不可以调整

存储引擎：

MyISAM  InnorDB

优点    性能快          全面

缺点    不支持事务      性能不快

        不支持外键

DQL：

查询：

    简单查询：查询全表数据

    语法：

select [distinct] {*|列名|表达式 [as] [列别名] [,...]}

[from 表名];

关键字/子句：

select：

from：数据源

[]:可有可无可选项

{}:括号内自动为一组

*: 一个表的所有列

|:或者的意思

列名：列的名字

表达式：

1.字符表达式：

2.计算表达式： DIV ：整除

  支持几乎所有的java运算符

  null值：

    没有或者不确定的值

    null值和任意数字进行算数运算的结果一定为null

,...:前面的对象重复出现中间用，隔开

    条件查询：查询表的一部分数据

    默认对于字符数据不区分大小写

    语法：

    select [distinct] {*|列名|表达式 [as] [列别名] [,...]}

[from 表名]

where (列名 |表达式  比较运算符  列名 |表达式)[逻辑运算符]

order by {列名|表达式|列别名 [asc(默认执行asc升序)][desc] } [field()]

limit 子句;

条件表达式 =  关系表达式 [逻辑运算符...]

关系表达式 =  列名 |表达式  比较运算符  列名 |表达式 

81年之前入职的员工信息

select ename,sal,hiredate from emp  where hiredate<'1981'

逻辑运算符：

and  与  &&

or  或  ||

not  非    ！

xor  异或

特殊比较运算符：

is null ：专门判断空值

like ：模糊查询

  通配符：

  _:一个长度的任意字符

  查询名字中倒数第二个字母是T的人员信息

  select * from emp where ename  like '%T_'

  %:任意长度的任意字符

  查询名字以S开头的人员信息

  select * from emp where ename  like 'S%'

  escape：

  声明转义字符@

  select * from emp where ename  like '%@%%' ESCAPE '@'

in ：代替 or 运算符

between  and ：必须从小到大（大于等于 || 小于等于）

自定义排序：

select ename ,sal ,job from emp  order by FIELD(job,'PRESIDENT','ANALYST','MANAGER','CLERK','SALESMAN')

limit:分页问题

select * from emp

LIMIT 10,5

  位置从0开始

  长度

    多表查询：

    内连接：

    查询的数据不仅在同一个表内

    --查询所有人的部门名称和员工编号

    select dname ,empno

    from dept,emp

oracle:笛卡尔积：两组集合中元素组成组合最多的可能

    99:交叉连接：cross join

    oracle:去笛卡尔积 ：等值连接

    select ename,empno ,emp.deptno ,dept.deptno ,dname from emp ,dept

    where  dept.deptno = emp.deptno;

    99:1.自然连接 ，字段名和数据类型都相等

    select dname ,empno from emp NATURAL join dept ;

      2. using连接 （自然连接2.0）：字段名相等并制定

        select ename ,dname

from emp join dept  using(deptno)

oracle：去笛卡尔积：不等值连接

-- 查询员工的姓名和工资等级

select *

from  emp ,salgrade

where sal between losal and hisal

99：on连接：

select *

from emp join salgrade

on sal between losal and hisal;

自连接：查询的数据在同一个表内，但是不在同一行上，现在需要在同一行显示

查询员工的姓名和领导的姓名

select a.ename ,b.ename

from emp a ,emp b

where a.mgr = b.empno;

查询smith的工资和比smith工资高的人的工资

多个表连接：

select  a.empno ,a.ename ,a.sal ,grade,loc ,b.ename

from emp a, dept , salgrade,emp b

where a.deptno = dept.deptno

and a.sal between losal and hisal

and a.mgr = b.empno;

select  a.empno ,a.ename ,a.sal ,grade,loc ,b.ename

from emp a join dept

on(emp.deptno = dept.deptno)

join salgrade

on(a.sal between losal and hisal )

join emp b

on(a.mgr = b.empno)

外连接：

oracle：

左外连接：

select a.ename ,b.ename

from emp a ,emp b

where  a.mgr = b.empno(+);

右外连接：

select * from emp , dept

on emp.deptno(+) = dept.deptno;

99:

左外连接：

select a.ename ,b.ename

from emp a  left outer join emp b

on a.mgr = b.empno;

右外连接：

select * from emp right outer join dept

on emp.deptno = dept.deptno;

union:集合并集可以做全外连接（mysql没有full outer join）

    带函数的查询：

      函数：API

      系统自带函数，单行函数，多行函数（聚合函数，分组函数）

      单行函数和多行函数的区别：单行函数数据源几行，结果有几行；多行函数的数据源无论多少行结果永远只有一行。

      单行函数：

      字符：

      concat  多个字符串拼接

      concat_ws  select CONCAT_WS('@','aliwangwang','qq.com');

      lpad     select lpad('abc',10,'*');

      rpad

      lower

      upper

      left 从左边截取几位字符串

      right

      substring    select SUBSTRING('abcdefg',3,4);

      instr        select instr('abcdef','c') 返回一个字符串在另一个字符串中的位置

      trim 去两头空格

      reverse      select reverse('abc'); 反转

      repeat      select repeat('abc',3);

      space space(1) 返回几个空格

      length      字节数

      char_length  字符数

      format      数字格式化 ： select format(1234567.9876,2);

      数字：

      mod 

      abs

      sign        判断俩数大小

      floor

      ceil

      round        默认保留到个位，可以指定

      truncate    截取，截到哪位，其他的都不要，没有进位

      power     求数字的次幂

      rand 求随机数 ，也有种子

      日期：

      now              系统当前时间

      date_add        select DATE_ADD('2018-1-1',INTERVAL 1 hour);

      timestampdiff    求两个时间点中间差多长时间select timestampdiff(month,'2018-1-2','2019-1-1');

      date_format      select DATE_FORMAT('1981-4-5 1:2:3','%Y@%m@%d %h@%i@%s')

      str_to_date      select str_to_date('1981@04@05 01@02@03','%Y@%m@%d %h@%i@%s')

      unix_timestamp  1970到现在的毫秒数

      FROM_DAYS    通过天数差额算年月日

      通用型： 

          if              select IF(1=3,1,2);

          ifnull          select sal+ifnull(comm,0) from emp;

          case

          SELECT

ename,

CASE deptno

WHEN 10 THEN

'ACCOUNTING'

WHEN 20 THEN

'RESEARCH'

WHEN 30 THEN

'SALES'

ELSE

'OPERATIONS'

END

FROM

emp;

简单写法：

select ename,

      case

        when sal < 1000 then

          '穷'

        when sal between 1000 and 2000 then

          '贫'

        when sal > 2000 and sal <= 3000 then

          '勉强能活'

        else

          '高富帅'

      end

  from emp;

      其他函数：

          Inet_aton()

          Inet_ntoa()

          select Inet_aton('192.168.0.1');

select Inet_ntoa(3232235521);

多行（聚合）函数：进行统计分析

sum:求和,avg：平均数,max ：最大,min：最小,count：计数

多行函数的计算：自动忽略空值

where 中一定不能使用多行函数

计算中可以选择去重

sum，avg必须跟数字类型的字段，其他的无所谓

永远准确的得知当前表多少条数据：

select count(*) from emp;

select count(1) from emp;

分组语句：更精准的使用统计信息说明数据的实际情况（避免无谓的干扰）

select [distinct] {*|列名|表达式 [as] [列别名] [,...]}

[from 表名]

[where (列名 |表达式  比较运算符  列名 |表达式)[逻辑运算符]]

[group by 列名|表达式  [with rollup] [,...]  ]

[having 条件表达式（聚合）]

[order by {列名|表达式|列别名 [asc(默认执行asc升序)][desc] } [field()]]

[limit 子句];

对于分组的细节体现：

-- 求每个部门的工资总和人员构成

select deptno, sum(sal),GROUP_CONCAT(ename)

from emp

group by deptno

--对分组再统计 with rollup

select deptno, sum(sal),GROUP_CONCAT(sal)

from emp

group by deptno with ROLLUP

子查询： 把多个简单查询组合成一个查询语句

where子查询：复杂的条件，什么不知道就查什么

select avg(sal )

from emp

where deptno =

(select deptno  from  emp  where empno in

(select mgr from emp  where sal>

(select sal from emp where ename  = 'smith')

and job =

(select job from emp where ename ='jones')

)

);

from 子查询：自创数据结构，调整子句的执行顺序，（必须起别名，无论是否使用别名）

--mysql

1.聚合函数嵌套问题？

select max(avgsal)  from (select avg(sal) avgsal ,deptno from emp group by deptno) a;

2.嵌套子查询问题

-- 查询出比本部门平均工资高的人员名字和部门平均工资

select ename,avg(sal)

from emp ,(select avg(sal) avgsal ,deptno from emp group by deptno) a

where sal>avgsal and emp.deptno = a.deptno

子查询分类：

where 子查询返回的结果类型：

单行，单列子查询  90%

select *

from emp

where sal> (select sal from emp where ename = 'jones')

多行子查询

-- 查询smith和jones的部门名称

select dname

from dept

where deptno in (select deptno from emp where ename in('smith','king'))

-- 查询比10部门所有人工资高的人员信息

select *

from emp

where sal>ALL(select sal from emp where deptno = 20)

-- 查询比10部门任意人员工资高的人员信息

select *

from emp

where sal>any(select sal from emp where deptno = 20)

多列子查询

-- 查询和smith部门相同工资等于smith部门最高工资的人员信息

select *

from emp

where  (sal,deptno) = (select max(sal),deptno from emp where deptno = (select deptno from emp where ename ='smith'))

DML:增删改 （一切皆是查询）

mysql在用之前，需要查一下当前系统的配置项目，系统默认会直接修改数据

autocommit on  1

          off  0

set global autocommit = 0  (native 连接的命令窗口)

删除：

delete 子句： 删除某一行数据

删除某行数据前线查询到这行数据，把from之前换成delete

Mysql:

多表删除：

delete emp,dept  from emp join dept  on emp.deptno = dept.deptno;

insert:插入数据（行插入）

基础数据插入：

标准：insert into tabname (cloumname[,...] ) values (value[,...]);

mysql：insert tabname set (columname =  value [,...])

业务数据插入：

标准：insert into  testa (haha) select haha from testb;

update：修改

注意难点在：

1.完成改多少？

2.完成改了谁？

update tablename

set columnName  = value [,...]

[where]

[...]

mysql：多表更新

update emp ,(select deptno from emp where ename ='smith' ) a,(select max(sal)maxsal from emp )b

set sal = b.maxsal ,a.deptno =

where emp.deptno = a.deptno

TPL:事务处理语言 （处理事务）

什么叫事务？

commit：事务提交

rollback：事务回滚

事务用来解决数据操作过程中的数据一致性问题

事务通过当前会话的第一个dml语句开始，到第一个tpl语句结束

事务特点：

原子性：一个事务不可被分割

一致性：事务要不然全部成功，要不然全部失败

隔离性：事务之间互相不影响

持久性：事务提交永远改变，事务回滚永远失效

DDL:数据定义语言（用来创建数据库对象：表，索引，约束，序列，视图....）

数据类型:dataType

    （只管显示的长度，跟数据存储无关），必须配合不够补零才有用

字符：char：不支持大小写 

  varchar：（ 可变）不支持大小写

  binary：支持大小写

  varbinary：可变的支持大小写

  blob: 非文本大文件

  text: 文本大文件

  char  和varchar

  都保存字符，一个是定长，一个是可变，定长速度快，浪费空间；可变速度慢，节省空间。

数字：

  int：正常整数

  bigint：大整数

  double：浮点型

  decimal:支持科学计算

  (Oracle：number)

日期：date :不支持时间 ，只支持日期

  timestamp:支持年~秒 ,支持时区 ，空间小 ，只支持用到2038年

  datetime:支持年~秒，空间大，9999

create ：创建 

创建哪种数据库对象

create  table  tabname(

columnName  dataType [constraint conname conType] [,...] 

)

约束：创建时间一般同建表一起，或者在表之后创建，用来保证数据完整性的一种技术手段

约束类型：唯一 ，检查， 非空，主键，外键

Mysql:唯一，非空，主键，外键

create table f1(

        f1p  number constraint f1_f1p_pk primary key,

        ff  number constraint  f2_f1p_fk references f1(f1p)

)

create table f2 (

        ff number constraint f2_ff_pk primary key,

        f1p number constraint  f2_f1p_fk references f1(f1p)

)

alter table f1 add constraint f1_ff_fk  foreign key (ff) references f2(ff);

通过查询建表：

create table emp_dept

as

select emp.*,dname ,loc from emp ,dept where 1=2;

drop：删除

drop table tabname;

alter：修改

删除某列 alter table emp_dept drop  COLUMN loc

增加某列 alter table emp_dept add  COLUMN loc char(10)

修改某列 alter table emp_dept modify  COLUMN loc char(15)

修改列的全部信息：alter table hehe change  COLUMN ename  namess varchar(15);

truncate ：截断 （删除全表的数据，不可选）

truncate table tabname;

truncate 和delete  和drop

只删除数据，不删除表

truncate 全表删除，不可还原 ，速度快

delete  部分可选，可以还原，效率慢

drop： 表和数据都删除，drop自动commit ，但是可以还原

auto_increment :自增列，如果不给赋值，则自动增长值，从1开始...

自增列的前提是主键，一个表只能用一次

这个功能在其他数据库叫sequence

视图：本质是一个保存了的查询语句

CREATE  or replace  VIEW empvu10

    AS SELECT empno, ename, job

    FROM emp

  WHERE deptno = 10;

or replace :利用新的sql语句替换老的视图

索引：

create index hahahaha on emp (ename)

什么时候适合使用索引：

1.数据量较大

2.查询的内容占总数据20%

3.主要做查询

4.不要经常增删改

5.主键和唯一键

DCL:数据控制语言

思考题：

-- 求每个部门的工资总和（不用group_concat如何实现？）

select deptno, sum(sal),GROUP_CONCAT(sal)

from emp

group by deptno

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 194,088评论 5赞 459
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 81,715评论 2赞 371
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 141,361评论 0赞 319
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,099评论 1赞 263
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 60,987评论 4赞 355
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,063评论 1赞 272
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,486评论 3赞 381
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,175评论 0赞 253
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,440评论 1赞 290
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,518评论 2赞 309
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,305评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,190评论 3赞 312
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,550评论 3赞 298
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,880评论 0赞 17
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,152评论 1赞 250
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,451评论 2赞 341
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,637评论 2赞 335

推荐阅读更多精彩内容

  • Oracle数据库学习二
    5.多表查询 多表查询 目的：从多张表获取数据 前提：进行连接的多张表中有共同的列 等连接 通过两个表具有相同意义...
    乔震阅读 1,170评论 0赞 0
  • Oracle数据库学习一
    1.简介 数据存储有哪些方式？电子表格，纸质文件，数据库。 那么究竟什么是关系型数据库？ 目前对数据库的分类主要是...
    乔震阅读 1,679评论 0赞 2
  • mysql——2-18数据库
    mysql数据库中 :database : 文件夹table : 数据表(数据文件) 进入mysqlmysql -...
    赋闲阅读 554评论 0赞 0
  • 游苏州定慧寺、寒山寺（一）
    读书学习之余，总得出门溜达溜达，一来活动活动久坐积劳的筋骨，二来呼吸下外面的新鲜空气，增长些对本地的见闻。 上周末...
    坚冰至_Monsol阅读 1,065评论 0赞 51
  • 写给出门在婆婆家女儿的话
    女儿： 爸爸给你写这些话时，正在医院陪你妈妈。也知晓你在和你婆婆外公去赶会了，心里念想着你要听话，注意安全...
    心语我言阅读 401评论 0赞 1