MySQL数据库系统原理PPT完整全套教学课件

1Linux与数据库概述63全套PPT课件课程简介课程内容关系型数据库理论MySQL系统管理目的学习关系型数据库基本理论知识学习数据库开发中的数据库部分学习数据库管理的基本知识和技术就业岗位程序员DBA系统集成考核方法平时表现：20%实验报告：20%期中考试：20%期末考试：40%Linux简介1991,LinusTorvalds，LinuxUnixonPC，...世界各地程序员协作开发代码开源多个发行版逐渐占领服务器操作系统市场服务器发行版RedHatSuseRedHat公司1994.10,MarcEwing,RedHatLinuxdistribution1995,BobYoung,MarcEwing,RedHatSoftware2016.5,9300employees2017.11.30，11,400employees2019.7.9,itbecameasubsidiaryofIBMRedHatEnterpriseLinuxdevelopedbyRedHatcommercialnocostfordevelopmentpurposes(2016.3.31)releasehistory2002.3.26,RedHatEnterpriseLinux2.1AS2018.4.10,RedHatEnterpriseLinuxServer7.52019.5.7,RedHatEnterpriseLinuxServer82020.4.24,RedHatEnterpriseLinuxServer8.22020.11,RedHatEnterpriseLinuxServer8.32021.5.18,RedHatEnterpriseLinuxServer8.4RHELclonesCentOS(不再发布redhat的稳定版克隆，不推荐使用)OracleLinux数据库应用的场合及典型产品购物淘宝京东amazon旅行去哪儿携程铁路售票金融银行证券3个基本术语数据库存于计算机内、有组织、有结构、可共享的数据集合。数据按一定模型组织、描述和存储，具有低冗余度、高独立性和易扩展性。DBMS创建及管理数据库的一类系统软件，如Oracle，MySQL，SQLServer。用于科学、有效地组织和存储数据，高效地获取和维护数据。数据库应用系统引入了数据库、服务于特定功能的计算机应用系统。数据库应用系统的构成硬件服务器：专用计算机，配置高端CPU、内存条、磁盘(如果使用云，可以不考虑这些)客户端：PC机，手机软件操作系统：Unix，Linux，WindowsDBMS：Oracle，DB2，SQLServer，Access，MySQL,PostgreSQL客户端软件人员系统分析人员，数据库设计人员，应用程序开发人员数据库管理员终端用户数据库发展网状模型，1964年，通用电气公司，CharlesBachman，IDS层次模型，1968年，IBM，ICS，1969，更名为IMS/360关系模型，1970年，Codd(IBM)层次模型和网状模型的贡献和缺陷贡献IDS是第一个具备通用功能的数据库产品，开创了数据处理的数据库时代。提出了数据库的三层模式、DML与DDL语言等概念。IMS第一个提出了程序与数据分离的思想，使得数据独立性有了很大提高。缺陷数据结构复杂数据独立性存在一定问题数据独立性数据的物理独立性存储数据的文件位置发生改变，应用程序不需要修改。数据的逻辑独立性增加属性，扩充功能，应用程序不需要修改。Codd的建议用一种简单的结构存储数据。用一种更高层次的、面向集合的语言访问数据。select*fromempwhereename='SMITH'不需要说明数据的物理存储方式。关系模型理论的提出1970年，Codd，ARelationalModelofDataforLargeSharedDataBanks因为对关系模型理论的贡献，Codd获得1981年度图灵奖E.F.Codd1923.8.19，生于英格兰在Oxford学习数学和化学二次大战中，RoyalAirForce飞行员1948，去纽约加入IBM1965,CSPhD,Univ.ofMichigan,AnnArbor1970，提出关系数据模型理论1981，获得TuringAward2003.4.18，在佛罗里达家中因心脏病去世关系定义关系是元组的集合元组是由若干列构成的行列值要求为单值关系模型的三要素数据结构：关系，即表操作方式：关系代数选择映射并差连接约束实体完整性约束引用完整性约束几个术语的不同叫法关系和表的区别关系是集合，是数学概念，行之间无顺序，列之间也无顺序。为了方便查看，表的行一般会以某个列排序，列也会按照习惯排序。关系模型的特点严格的理论基础简单的逻辑结构面向集合的操作语言,与此相对的是面向过程语言面向过程的数据处理方式也称为imperativelanguage以C语言读取文件为例(写入文件代码参考本页备注)fp=fopen("e:\\law.dat","rb");fread(s,sizeof(structstu),1,fp);printf("输入要查询的号码：\n");fflush(stdin);scanf("%d",&sn);while(!feof(fp)) { if(sn==s->sno) printf("%d,%s,%d,%d\n",s->sno,s->sname,s->age,s->wgt);}fclose(fp);面向集合语言也称为declarativelanguageselect*fromempwheredeptno=10关系型数据库产品–IBM的SystemRSystemR，1973~1979，80,000行代码，2.2MB1981，SQL/Data1983，DB21979，Oracle2.0关系型数据库产品–UCB的Ingres项目1973~1985,MichaelStonebraker与EugeneWong由Ingres演化而来的产品1984，主要开发者RobertEpstein创立Sybase。1987.5，Sybase系统。1993，微软购买Sybase源代码的许可，即SQLServer。1996.8，PostgreSQLMySQL历史MichaelWidenius,1962年3月3日出生于芬兰赫尔辛基,My是芬兰语，是其大女儿的名字1994,MichaelWidenius,web-basedapplicationsusingUNIREG,rewritingUNIREGforbetterperformance1995,Wideniusandhisfriends,DavidAxmarkandAllanLarsson,foundedMySQLAB2008,SunMicrosystemsboughtMySQLABfor$1billion2010,OracleacquiredSunfor$7.4billion2009，MariaDB(MySQL5.1),community-drivenfork,byMontyWideniushimselfinresponsetotheSunandOracleacquisitions2018.4.19，MySQL8.0.112020.8.31,MySQL8.0.212020.10.19,MySQL8.0.22当前(2021.07.20),MySQL8.0.26

MySQL资源-安装文件及MySQL官方文档2下载与安装MySQL支持MySQL8.0的操作系统OracleLinux8/RedHatEnterpriseLinux8/CentOS8x86_64OracleLinux7/RedHatEnterpriseLinux7/CentOS7x86_64OracleLinux6/RedHatEnterpriseLinux6/CentOS6x86_64/32Ubuntu20.04/18.04/16.04LTSx86_64/32SUSEEnterpriseLinux15/12(12.4+)x86_64DebianGNU/Linux10/9x86_64/32Windows2012ServerR2/2016Server/2019Serverx86_64Windows10 x86_64ApplemacOS10.15/10.14 x86_64安装VMwareWrokstationpro16.1.0网址/cn/products/workstation-pro/workstation-pro-evaluation.html序列号ZF3R0-FHED2-M80TY-8QYGC-NPKYF

YF390-0HF8P-M81RQ-2DXQE-M2UT6

ZF71R-DMX85-08DQY-8YMNC-PPHV8ssh远程管理软件(选择其一即可)PuTTYhttps://the.earth.li/~sgtatham/putty/latest/w64/putty.exeMobaXterm/download-home-edition.html说明：页面中的两个版本均可，portable更方便。Windows10自带的ssh工具和scp工具C:\Users\LAW>sshroot@31root@31'spassword:Lastlogin:SatMar709:43:572020from[root@cent8~]#C:\Users\LAW>scp

people.txt

root@30:/root/下载OracleLinux8.4(大小为9.2GB)/ISOS/OracleLinux/OL8/u4/x86_64/OracleLinux-R8-U4-x86_64-dvd.iso启动VMwareWorkstation-创建新的虚拟机选择“典型”找到已下载的OracleLinux8.3的ISO文件设置虚拟机名称和存放位置设置磁盘虚拟机设置完毕重启虚拟机开始安装OracleLinux选择安装过程使用的语言设置OracleLinux功能设置OracleLinux功能LOCALIZATIONTime&Date部分：选择“Asian/Shanghai”时区，并开启“NetworkTime”。SOFTWARESoftwareSelect部分：选择“MinimalInstall”，即最小安装。SYSTEMInstallationDestination部分：设置分区，默认即可(点开后，直接点击Done按钮)。Network&Hostname部分：开启网络并设置机器名称。USERSETTINGS点击“RootPassword”，设置超级用户root的口令。若不符合口令复杂度要求，需要多次点击“Done”按钮才能返回。以root登录，执行hostname-I查询IP配置root用户shell提示符vi/root/.bash_profile添加一行exportPS1="\e[1;33m[\u\w\D{%F%T}]\n#\e[m"安装vimdnf-yinstallvim配置vimvi/root/.bashrc，添加aliasvi=vimvi/root/.vimrc，添加noremap

<Up>

<Nop>noremap

<Down>

<Nop>noremap

<Left>

<Nop>noremap

<Right>

<Nop>set

nocompatibleset

autoindentset

tabstop=4set

shiftwidth=4syntax

on注意：直接拷贝上面内容，可能因为格式导致.vimrc不可用yum与dnf简介RHEL操作系统的软件库管理器安装，卸载，升级软件YellowDogUpdaterModifier源自YellowDogLinux在RHEL8，yum已被效率更高的dnf代替yum是dnf的软连接dantifiedyum

安装MySQLOracleLinux7/CentOS7/RHEL7#yum-yinstall//mysql80-community-release-el7-3.noarch.rpm#yuminstall-ymysql-serverOracleLinux8/CentOS8/RHEL8由CentOS8的AppStream安装#dnf-yinstall@mysql说明：此方法安装MySQL后，root@localhost的密码默认为空。此方法安装的MySQL版本一般比官方版本低#dnf-yinstallmysql只安装客户端mysql#dnf-yinstallmysql-server客户端和服务器均会安装由MySQL官方repository安装(推荐)#dnfmoduledisablemysql#dnfinstall-y/get/mysql80-community-release-el8-1.noarch.rpm#dnfinstall-ymysql-community-server安装和使用mysql-workbench(可选)#dnf-yinstallmysql-workbench#mysql-workbench卸载升级MySQL#dnf-yremovemysql*##dnf-yupgrademysql-community-server说明：升级前，需执行：dnfmoduledisablemysql各文件安装位置数据库相关文件(datadir目录)/var/lib/mysql可执行文件/usr/bin服务器-mysqld/usr/sbin启动/关闭MySQL服务器使用操作系统工具systemctl管理mysql服务#systemctlstatusmysqld#systemctlstartmysqld#systemctlstopmysqld#systemctlrestartmysqld设置MySQL服务自动启动#systemctlenablemysqld#systemctldisablemysqld说明：配置文件为/etc/systemd/system/multi-user.target.wants/mysqld.service确认root帐号临时密码启动mysqld服务后，临时密码才会生成，存于/var/log/mysqld.log#cd/var/log#greppasswordmysqld.log[Note]Atemporarypasswordisgeneratedforroot@localhost:7hjuVkij<eo&[root@law19log]#catmysqld.log连接MySQL服务器输入密码时，隐藏内容#mysql-uroot-pEnterpassword:输入root的密码即可直接输入密码#mysql-uroot-pYourPassword设置root初始密码mysql>alteruserroot@localhostidentifiedby'Root@1995';简单信息查询查看MySQL版本mysql>selectversion();查看所有数据库mysql>showdatabases;切换数据库mysql>usedb查看当前数据库mysql>selectdatabase();查看数据库中的所有表mysql>show

tables;查询表mysql>select*fromemp;基本操作创建表mysql>createtablet(aint,bint);执行OS命令(system或\!)mysql>systemclearmysql>\!lsanaconda-ks.cfgscott.sql修改执行过的代码mysql>edit忘记root密码后的重置设置mysql启动参数[root@lawbin]#systemctlset-environmentMYSQLD_OPTS="--skip-grant-tables"重启mysqld[root@lawetc]#systemctlrestartmysqld登录mysql，不输入用户和密码直接回车[root@lawetc]#mysql修改密码mysql>flushprivilegesmysql>alteruserroot@localhostidentifiedby'Root@1995';还原启动参数[root@law~]#systemctlunset-environmentMYSQLD_OPTS="--skip-grant-tables"可把密码存在.f，直接登录mysql[root@law~]#cat.f[client]user=rootpassword=Root@1995[root@law~]#chmod400.f[root@law~]#ls-l.f-r.1rootroot2511月522:48.f[root@law~]#mysqlWelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis13Serverversion:8.0.11MySQLCommunityServer-GPL[root@law~]#rm-f.f[root@law~]#mysqlERROR1045(28000):Accessdeniedforuser'root'@'localhost'(usingpassword:NO)远程连接MySQL，报错10060的解决错误表现C:\Windows\system32>mysql-h05-u'law'-pEnterpassword:***********ERROR2003(HY000):Can'tconnecttoMySQLserveron'05'(10060)方法1：关闭Linux端的防火墙[root@lawetc]#systemctlstopfirewalld.service方法2：禁止防火墙自动启动[root@lawetc]#systemctldisablefirewalld.service方法3：开放防火墙的3306端口[root@lawbin]#firewall-cmd--add-port=3306/tcp--permanent#关闭端口：--remove-portsuccess[root@lawbin]#firewall-cmd--reloadsuccess说明：本地连接时，默认-hlocalhost，不使用网络，若-h则使用网络RedHatLinux几个常见网络问题解决PuTTY登录CentOS慢vi/etc/ssh/sshd_config查找GSSAPIAuthentication，设置为no，然后重启sshd查看网卡信息#nmclideviceshow启动网卡#nmcliconnectionupens33关闭网卡#nmcliconnectiondownens33查看IP地址#hostname-I查看网卡状态#nmclidevicestatusDEVICETYPESTATECONNECTIONens33ethernetconnectedens33loloopbackunmanaged--3数据库设计-ER模型与规范化数据库设计在需求分析的基础上顺序进行的三个步骤概念设计逻辑设计物理设计需求分析的主要任务通过对客户的调查，得到数据库应用的下面内容要保存的数据要完成的功能数据库设计的任务概念设计由需求分析结果抽象出应用的实体及联系，得到ER图。逻辑设计把ER图转化为表的结构。物理设计在具体的DBMS上把表实现，并包括创建合适的索引。实体-联系的概念实体是现实世界中可区别于其他对象的一个“事件”或一个“物体”现实世界是由一系列的实体以及这些实体间的联系构成的。ER图的概念ER表示EntityRelationship，即实体－联系。一种可应用于关系型数据库(但不限于关系型)概念设计的建模方法。最早由陈品山(PeterPin-ShanChen)在1976年提出，是数据库概念设计的主要方法。陈品山1947，生于台中市1968，毕业于台湾大学，BS，EE1973，Harvard，PhD1976，发表TheEntity–RelationshipModel–TowardAUnifiedViewofData曾在IBM，Honeywell，DEC短暂工作1974~1978，AssistantProfessor，MIT1978~1983，AssociateProfessor，UCLA1983~2011，Professor，LouisianaStateUniversityER模型的目的甲乙方以一种非技术的模型，交流对数据的理解，保证数据库设计反映了企业对数据的使用方式。以直观的形式开始数据库设计过程。ER图准备抽象出实体抽象出实体间的联系得到实体属性得到联系属性ER图的画法陈方法UMLUML画法用矩形表示实体把矩形分为上下两部分，上半部分是实体名称，下半部分实体属性存在联系的两个实体用直线连接起来，把联系名称放在直线上面联系一般有方向，在其名称左侧或右侧放置一个箭头符号表示方向ER图示例Navicat，Workbench,ERWin,

PowerDesigner联系的映射约束指一个实体集中的实体通过联系可以同另一个实体集中相联系的实体数量映射约束的种类一对一一对多多对多ER图转化为表实体名称即表名实体属性即列名根据联系的映射约束情况把联系转化为表一对一联系转化为表把两个实体及联系的属性合并为一个表，两个实体的主键都可以选作新表的主键一对多联系转化为表把“一”的一方的实体的主键及联系的属性合并到“多”的一方，并作为多的一方的实体的外键指向“一”的一方的主键。也可以把联系转化为一个独立的表，这个新表由两个实体的主键及联系的属性构成，多的一端的主键作为这个新表的主键。多对多联系把联系转化为一个独立的表这个表由联系的属性及两个实体的主键构成这两个实体的主键作为新表的外键各自指向由两个实体转化而成的两个表的主键实例简单的论坛网站范式即NormalForm，数据规范形式目的是减少数据冗余和避免数据异常emp和dept表合并产生的问题空间浪费产生异常Insert异常新添加员工的dept部分的细节要跟其他已有的记录完全一致，否则会造成表中的数据彼此不一致。若一个新部门还没有员工，则此部门的信息不能添加到表中去，此表中就查不到这些部门的信息。Deletion异常如果一个部门的员工都辞职或调到其他部门，其对应员工的记录都会被删除，这个部门的信息在表中也就不存在了。Update异常(适用于普通表格)若一个部门有多个员工，而其部门名称发生改变，则必须更新这个部门的所有记录的部门名称字段，如果遗漏一个，会造成表中数据的彼此不一致。第一范式如果一个表没有多值字段，则我们说这个表是满足第一范式的(1NF)下面book表中的author列有两个值，不满足1NFISBNtitlepub_datepricecategorypublisherauthor7-04-007494-X数据库系统概论2002-1-125.10计算机高等教育出版社萨师煊，王珊如何转化为第一范式把多值列移出，与原表主键一起构成一个新表ISBNtitlePub_datepricecategorypublisher7-04-007494-X数据库系统概论2002-1-125.10计算机高等教育出版社ISBNauthor7-04-007494-X萨师煊7-04-007494-X王珊第二范式如果一个表为第一范式，而且非主键字段完全依赖于主键，则此表称为满足第二范式下面book表的主键由ISBN和author_id列构成，title等列只依赖于ISBN，author_name等列依赖于author_id，不满足第二范式ISBNtitlePub_datepricecategorypublisherauthor_idauthor_nameauthor_mobile7-04-007494-X数据库系统概论2002-1-125.10计算机高等教育出版社1001萨师煊135559990017-04-007494-X数据库系统概论2002-1-125.10计算机高等教育出版社2001王何转化为第二范式把部分依赖的非主键字段移出，与原表所依赖的部分主键构成一个新表ISBNtitlepub_datepricecategorypublisher7-04-007494-X数据库系统概论2002-1-125.10计算机高等教育出版社ISBNauthor_id7-04-007494-X10017-04-007494-X2001author_idauthorauthor_mobile1001萨师煊135559990012001王三范式如果一个表满足第二方式，并且没有非主键字段传递依赖于主键字段，则我们称这个表满足第三范式若把emp表和dept表合并，会出现什么情况？如何转化为第三范式把存在传递依赖的字段从原表移出，与其依赖的字段构成一个新表4SQL语言复习测试-选择题关系模型理论在_______年提出A1879B1970C1983逻辑设计的任务是A得到ER图B得到建表语句C在数据库产品中创建表一个表满足第二范式，而且不存在______依赖，则它满足第三范式A部分依赖B完全依赖C传递依赖在OracleLinux8启动服务的工具是AsystemBsystemctlCmysqld若root帐号的密码为Root@1995，以mysql工具连接服务器的命令为Amysql>-uroot-pRoot@1995Bmysql>-uroot-pRoot@1995Cmysql>-uroot-pRoot@1995Dmysql>-uroot-p'Root@1995'复习测试-选择题在mysql中切换至db数据库的命令为Amysql>usedbBmysql>connectdbCmysql>changedbDmysql>db在db数据库中执行/scripts/scott.sql的命令为

Amysql>source/scripts/scott.sqlBmysql>sourcescott.sqlCmysql>\./scripts/scott.sqlDmysql>/scripts/scott.sql复习测试-简答题如何把不满足第三范式的表转化为满足第三范式简述关系模型的三个要素简述关系模型的特点简述画出ER图之前的准备工作简述如何把一对多的联系转化为表SQL操作关系型数据库的标准语言StructuredQueryLanguageSQL历史1970，Codd提出关系模型1973，IBM启动SystemR项目DonaldD.Chamberlin和RaymondF.Boyce负责操作工具的开发1974，DML与DDL成形称为SEQUEL(StructuredEnglishQueryLanguage)RaymondF.Boyce因为脑动脉瘤去世1980，重命名为SQLSEQUEL已被英国飞机公司HawkerSiddeley用作商标.SQL,StructuredQueryLanguage，结构化查询语言SQL标准SQL-86,SQL-89,SQL-92,SQL:1999,SQL:2003,SQL:2006,SQL:2008,SQL:2011,SQL:2016Chamberlin(1944-)1966,BS,Engineering,HarveyMuddCollege1967,MS,EE,StanfordUniversity1971,PhD,EE,StanfordUniversitySQL分类DMLDataMnaipulationLanguageselect,delete,update,insertDDLDefinitioncreate,drop,alter,truncatetableDCLControlgrant,revokeMySQL的客户端工具SQL执行方式直接执行嵌入至其他编程语言使用API字符界面mysql图形界面MySQLWorkbench第三方工具(Windows)SQLyognavicat使用mysql客户端工具连接至本地服务器[root@law~]#mysql-uroot-p连接服务器时，指定数据库[root@law~]#mysql-uroot-phr简单信息查询查看MySQL版本mysql>selectversion();查看当前用户mysql>selectuser();查看所有数据库mysql>showdatabases;查看当前数据库mysql>selectdatabase();查看当前时间mysql>selectnow();查看当前数据库中的所有表mysql>showtables;查看表结构mysql>desct;基本操作创建数据库mysql>createdatabasedb;切换数据库mysql>usedb执行OS命令(system或\!)mysql>systemclearmysql>\!lsanaconda-ks.cfgscott.sql执行SQL脚本mysql>sourcescott.sql修改执行过的代码mysql>edit执行SQL脚本文件创建测试数据用vi提前在当前目录下准备好scott.sql脚本文件，然后执行下面命令mysql>createdatabasehr;QueryOK,1rowaffected(0.00sec)mysql>usehrDatabasechangedmysql>sourcescott.sql最简单的查询mysql>select*fromdept;++++|deptno|dname|loc|++++|10|ACCOUNTING|NEWYORK||20|RESEARCH|DALLAS||30|SALES|CHICAGO||40|OPERATIONS|BOSTON|++++简单查询-where条件mysql>select*->fromemp->wheresal>2000;mysql>selectename,sal->fromemp->wheresal>2000anddeptno=10;mysql>selectename,sal->fromemp->wheresalbetween1500and3000;mysql>selectename,sal->fromemp->wherecommisnull;简单查询-列别名mysql>selectdeptnoasdno,dnameas"deptname",loccity->fromdept->;++++|dno|deptname|city|++++|10|ACCOUNTING|NEWYORK||20|RESEARCH|DALLAS||30|SALES|CHICAGO||40|OPERATIONS|BOSTON|++++简单查询-orderby与limit子句mysql>selectename,salfromemp->wheredeptno=10->orderbysal->;mysql>selectename,salfromemp->wheredeptno=10->orderbysal->limit2,3->;说明：limitm,

n的用法是略过前m行，然后取出n行table关键字(始自8.0.19)select*fromtable_name可以替换为tabletable_namemysql>tabledept;mysql>tabledeptorderbydnamelimit1,2;使用row关键字，自组表(始自8.0.19)mysql>select*from->(valuesrow(1,2,3),row(4,5,6))ast;++++|column_0|column_1|column_2|++++|1|2|3||4|5|6|++++mysql>select*from->(valuesrow(1,2,3),row(4,5,6))ast(a,b,c)->wherea=1;++++|a|b|c|++++|1|2|3|++++字符串条件查询字符串常量表示方法默认是否区分大小写模糊查询，%,_条件中包括特殊字符：%,_,'复习测试1.update命令属于____。A.

DDLB.DCLC.DML2.select语句的from子句用于指定______。A.列名B.表名C.数据库名D.表名或列名3.下面描述查询语句的顺序中，正确的是______。A.select...from...where...orderby...B.select...from...orderby...where...C.select...orderby...from...where...复习测试mysql>selectdistinctjobfromemp;++|job|++|CLERK||SALESMAN||%RD%||R_D|++selectjobfromempwherejob=CLERK的查询结果为：A.CLERKB.执行时，出现语法错误C.空集2.selectjobfromempwherejoblike'%%%'的查询结果为：A。%RD%B.与上面的查询结果相同C.空集D.以上均错E.执行时，出现语法错误3.selectjobfromempwherejoblike'%_'的查询结果为：A.R_DB.与上面的查询结果相同C.空集D.以上均错E.执行时，出现语法错误4.selectjobfromempwherejob='%\_%'的查询结果为:A。%RD%B.R_DC.空集D.以上均错E.执行时，出现语法错误复习测试mysql>selectempnofromemporderbyempno;++|empno|++|7369||7499||7521||7566||7654||7698||7782||7839|++说出下面查询结果selectempnofromemporderbyempnolimit1,2;selectempnofromemporderbyempnolimit2;selectempnofromemporderbyempnodesclimit2;分类汇总mysql>selectdeptno,max(sal)->fromemp->groupbydeptno->;+++|deptno|max(sal)|+++|10|5000.00||20|3000.00||30|2850.00|+++汇总函数max(),min(),avg(),count(),sum(),group_concatgroup_concat(column_name1orderbycolumn_name2separator‘..’)where子句：限定参与汇总的行groupby子句：指定分组列having子句：过滤分组结果orderby子句：指定排序列MySQL不支持汇总函数的嵌套：max(count(*))简单汇总练习查询emp表的最高工资查询emp表的最低工资查询emp表的记录个数查询emp表中部门号20中的记录个数查询emp表中不重复的部门编号查询emp表中20号部门的最高工资使用groupby执行分类汇总练习查询emp表中每个部门的sal总和。查询emp表中，每个部门工资超过2000的那些员工的工资总和。使用having子句过滤分组汇总的结果查询工资总额超过7000的部门编号及其工资总额，并以部门编号从小到大排序各子句的处理顺序执行where子句，过滤符合条件的记录。执行groupby子句，对以上结果以分组汇总。执行having子句，对分组后的查询结果进行过滤。执行orderby子句，对查询结果以指定列排序。执行select子句，显示查询结果。分组汇总的注意事项where子句置于groupby之前。不能在where子句中使用分组函数。having子句一般会使用分组函数，若不需使用分组函数，则推荐使用where子句。在语法顺序上，having子句可放置于groupby子句之前或之后，推荐放置于groupby子句之后。若未被分组函数作用，select、having和orderby子句中的列只能选用groupby子句中的列。子查询mysql>select*from->(selectename,salfromemp)ast->;mysql>selectename,(selectavg(sal)fromempwheredeptno=e.deptno)dept_avg_sal->fromempe->;mysql>selectename,sal->fromemp->wheresal=(selectmax(sal)fromempwheredeptno=10)->;mysql>selectdnamefromdept->wheredeptnoin->(selectdeptnofromempgroupbydeptnohavingavg(sal)>2000)->;notin查询结果为空查询没有员工的部门名称mysql>selectdnamefromdeptwheredeptnonotin(selectdistinctdeptnofromemp);++|dname|++|OPERATIONS|++1rowinset(0.00sec)mysql>updateempsetdeptno=nullwhereename='MILLER';QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0mysql>selectdnamefromdeptwheredeptnonotin(selectdistinctdeptnofromemp);Emptyset(0.00sec)notexists与exists使用子查询实现汇总函数嵌套调用效果查询emp表中平均工资最高的deptno查询emp表中部门人数最多的deptno,dname单选-查询获得最高工资的员工名称selectenamesalfromempwheresal=max(sal)selectename,salfromempwheresal=(selectmax(sal)fromemp)selectename,max(sal)fromempselectename,(selectmax(sal)fromemp)fromemp单选-查询每个员工名称及其部门名称selectename,(selectdnamefromdept)fromempselectename,(selectdnamefromdeptwheredeptno=deptno)fromempselectename,(selectdnamefromdeptwheredeptno=e.deptno)fromempeselect(selectenamefromempwheredeptno=d.deptno),dnamefromdeptd多选-查询各部门人数的最大值selectmax(count(*))fromempselectmax(cnt)from(selectcount(*)cntfromemp)aselectmax(count(*))fromempgroupbydeptnoselectmax(cnt)from(selectcount(*)cntfromempgroupbydeptno)aselectmax(cnt)from(selectdeptno,count(*)cntfromempgroupbydeptno)aselectmax(cnt)from(selectcount(*)cntfromemp)agroupbydeptno表连接-传统语法交叉连接mysql>select*fromemp,dept;内连接mysql>selecte.ename,d.dname->fromempe,deptd->wheree.deptno=d.deptno->;表连接-SQL-92语法交叉连接mysql>select*fromemp[cross]joindept;内连接mysql>selecte.ename,d.dname->fromempe[inner]joindeptd->one.deptno=d.deptno;mysql>selecte.ename,d.dname->fromempenaturaljoindeptd->;mysql>selecte.ename,d.dname->fromempejoindeptd->using(deptno)->; SQL-92语法-单表条件附加方法mysql>selecte.ename,d.dname->fromempeinnerjoindeptd->one.deptno=d.deptno->wheree.sal>2500->;mysql>selecte.ename,d.dname->fromempeinnerjoindeptd->one.deptno=d.deptno->ande.sal>2500->;外连接leftouterjoinrightouterjoin不支持fullouterjoin可以使用左外连接和右外连接的union得到构造复杂查询#mysqllaw</root/mysql/course.sql查询学生名称，课程名称，分数查询选修English的学生名称及分数查询选课最多的学生的名称selectstunamefromstuwherestunoin( selectstunofromselgroupbystuno havingcount(*)= ( selectmax(cnt)from (selectcount(*)cntfromselgroupbystuno)t ))查询所选课程平均分最高的学生名称分页查询-limit

m,n略过前m行，然后取出n行limitn相当于limit0,nmysql>selectename,sal->fromemp->orderbysallimit3;+++|ename|sal|+++|SMITH|800.00||JAMES|950.00||WARD|1250.00|+++mysql>selectename,salfromemporderbysallimit1,2;+++|ename|sal|+++|JAMES|950.00||WARD|1250.00|+++集合操作union/unionallintersect(至8.0.27，MySQL尚未支持)except(至8.0.27，MySQL尚未支持)增删改insertmysql>insertintoemp(empno,ename,sal)->values(8888,'Clinton',3000),(9999,'Bush',null);mysql>createtableemp_copylikeemp;mysql>insertintoemp_copyselect*fromemp;updatemysql>updateempsetsal=sal+1000,comm=1500->wheredeptno=10;deletemysql>deletefromempwhereempno>8000;增删改replacemysql>select*fromdept;++++|deptno|dname|loc|++++|10|ACCOUNTING|NEWYORK||20|RESEARCH|DALLAS||30|SALES|CHICAGO||40|OPERATIONS|BOSTON|++++4rowsinset(0.00sec)mysql>replacedeptvalues(50,'OPR','PITTS');QueryOK,2rowsaffected(0.11sec)mysql>select*fromdept;++++|deptno|dname|loc|++++|10|ACCOUNTING|NEWYORK||20|RESEARCH|DALLAS||30|SALES|CHICAGO||40|OPR|PITTS|++++4rowsinset(0.00sec)数据类型intdecimal(p,s)或numeric(p,s)1≤p≤65,0≤s≤30,s≤p若省略参数，则表示整数，p默认为10若省略s，则默认为0datetime(p)以8B存储，秒可以精确至小数点后6位，p默认为01000-01-0100:00:00.000000~9999-12-3123:59:59.999999timestamp(p)以4B存储，秒可以精确至小数点后6位，p默认为01970-01-0100:00:01UTC~2038-01-1903:14:07UTCinsert或update记录时，此类型的列可设置自动以当前时间填充存储时，会转换为UTC时间

客户端的当前时区为服务器时区

数据类型-字符串char(n)0≤n≤255varchar(n)0≤n≤65,535blob与text长度<=4G，blob为binary类型，text为non-binary类型长度单位均为字符createtableorders(

ord_idintauto_incrementprimarykey,

ord_namevarchar(255)unique)charsetutf8mb4collateutf8mb4_zh_0900_as_cs;支持中文拼音排序utf8mb4_zh_0900_as_cs创建表创建简单的表mysql>createtablet(aint,bchar(10));

自增列建表时对列附加自增选项mysql>createtablet(aintauto_incrementprimarykey,bint);自增整数类型可以用serial简化mysql>createtablett(aserial,bint);说明：serial等同于BIGINTUNSIGNEDNOTNULLAUTO_INCREMENTUNIQUE系统变量auto_increment_offset和auto_increment_increment分别设置初值和步长，默认均为1

自填充/更新的timestamp/datetime列createtablet1(atimestampdefaultcurrent_timestamponupdatecurrent_timestamp,bint);mysql>insertintot1(b)values(1);mysql>select*fromt1;+++|a|b|+++|2018-05-1509:45:59|1|+++约束种类primarykey8.0.13开始，增加sql_require_primary_key参数，以设置是否强制主键uniqueforeignkeycheck8.0.16开始支持，之前版本语法不报错，但实际不支持notnull建表时附加约束mysql>createtablep(->aint,->bint,->cvarchar(5)notnull,->primarykey(a,),#名称不会生效，其名称总为PRIMARY->constraintck_pcheck(b>100),#8.0.16版本开始生效->constraintuq_punique(c));mysql>createtablec(->xintprimarykey,->yint,->constraintfk_cforeignkey(y)referencesp(a)ONupdateCASCADE->);说明：MySQL的外键只能加在表级，列级references不报错，但无效。外键指向的列不需要附加唯一或主键约束，但指向的列需要附加索引拷贝表只拷贝表结构(连同约束一起拷贝)mysql>createtabletlikedept;表和数据一起拷贝(不会拷贝约束)mysql>createtabletasselect*fromdept;mysql>insertintotselect*fromdept;修改表的结构mysql>altertablepmodifycvarchar(10);mysql>altertabletaddconstraintuq_punique(b);mysql>altertabletaddcolumncintaftera;mysql>altertabletaddcolumncintfirst;mysql>altertabletdropcolumnc;mysql>altertabletmodifybintnotnull;mysql>altertabletrenamecolumnbtobb;mysql>altertabletchangecolumncccint;#数据类型不能省略mysql>altertabletaddcolumndchar(10),addcolumneintafterd,->changecolumncccint;删除约束altertable命令不支持dropconstraint选项(8.0.19已经支持)通过删除索引，删除唯一约束mysql>dropindexuq_enameonemp;删除主键约束mysql>altertabletdropprimarykey;删除指定外键约束mysql>altertabletdrop

foreignkeyfk_t;删除检查约束mysql>altertabletdropcheckck_t;altertabletdropconstraintck_t;删除表droptablet1,t2可附加restrict或cascade，但无实际作用表的系统信息查询数据库中所有的表mysql>showtables;表结构mysql>desct;建表语句mysql>showcreatetablet\G;***************************1.row***************************Table:tCreateTable:CREATETABLE`t`(`a`char(10)DEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=latin11rowinset(0.00sec)查询约束信息mysql>showcreatetableemp查询emp表上的所有约束mysql>selecttable_name,constraint_name,constraint_type->frominformation_schema.table_constraintswheretable_name='emp';++++|table_name|constraint_name|constraint_type|++++|emp|PRIMARY|PRIMARYKEY||emp|fk_deptno|FOREIGNKEY|++++查询外键约束信息mysql>selecttable_name,constraint_name,->referenced_table_name,unique_constraint_name->frominformation_schema.referential_constraintswheretable_name='emp';查询外键信息mysql>selecttable_name,constraint_name,column_name,->referenced_table_nameasref_table,referenced_column_nameasref_column->frominformation_schema.key_column_usage->wheretable_schema='db'andtable_name='emp';++++++|table_name|constraint_name|column_name|ref_table|ref_column|++++++|emp|PRIMARY|empno|NULL|NULL||emp|fk_deptno|deptno|dept|deptno|++++++视图起名称的查询查看定义SHOWCREATEVIEWinformation_schema.views

可更新视图视图中的行与表中的行的关系：一对一不可更新视图，含有以下内容汇总函数distinctgroupbyhavingunin