还剩26页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
数据库基本知识和基础sql语句LELEwasfinallyrevisedonthemorningofDecember162020HAVINGcondition/*分组后的行条件*/ORDERBYsorting_columns/火对结果分组*/LIMIToffset_startrow_count/*结果限定火/基础查询查询所有列SELECT*FROMstu;查询指定列SELECTsidsnameageFROMstu;2条件查询条件查询介绍条件查询就是在查询时给出WHERE子句,在WHERE子句中可以使用如下运算符及关键字
二、!
二、<〉、<、<
二、>、>二;BETWEEN...AND;INset;ISNULL;AND;OR;NOT;查询性别为女,并且年龄50的记录SELECT*FROMstuWHEREgender=femaleANDge50;查询学号为SJXXH或者姓名为liSi的记录SELECT*FROMstuWHEREsid=S1001ORsname=liSi;查询学号为S_1001S_1002S_1003的记录SELECT*FROMstuWHEREsidINS_lQQl\S_10Q2\lS_lQQ3y查询学号不是S_1001S_1002S_1003的记录SELECT*FROMtab_studentWHEREs.numberNOTINS_1001;S_1002S_1003;查询年龄为null的记录SELECT*FROMstuWHEREageISNULL;SELECT*FROMstuWHEREage=20ANDage=40;或者SELECT*FROMstuWHEREageBETWEEN20AND40;查询性别非男的学生记录SELECT*FROMstuWHEREgender!二male;或者SELECT*FROMstuWHEREgenderomale;或者SELECT*FROMstuWHERENOTgender=male1;SELECT*FROMstuWHERENOTsnameISNULL;或者SELECT*FROMstuWHEREsnameISNOTNULL;3模糊查询当想查询姓名中包含a字母的学生时就需要使用模糊查询了模糊查询需要使用关键字LIKEO查询姓名由5个字母构成的学生记录SELECT*FROMstuWHEREsnameLIKE模糊查询必须使用LIKE关键字其中匹配任意一个字母,5个表示5个任意字母查询姓名由5个字母构成,并且第5个字母为7的学生记录SELECT*FROMstuWHEREsnameLIKEi:SELECT*FROMstuWHEREsnameLIKEz%;其中“盼匹配〜n个任何字母°查询姓名中第2个字母为7的学生记录SELECT*FROMstuWHEREsnameLIKEJ%1;查询姓名中包含匕”字母的学生记录SELECT*FROMstuWHEREsnameLIKE4字段控制查询去除重复记录去除重复记录(两行或两行以上记录中系列的上的数据都相同),例如emp表中sal字段就存在相同的记录当只查询emp表的sal字段时,那么会出现重复记录,那么想去除重复记录,需要使用DISTINCT SELECTDISTINCTsalFROMemp;查看雇员的月薪与佣金之和因为sal和comm两列的类型都是数值类型,所以可以做加运算如果sal或comm中有一个字段不是数值类型,那么会出错SELECT*sal+commFROMemp;comm列有很多记录的值为NULL因为任何东西与NULL相加结果还是NULL所以结算结果可能会出现NULL下面使用了把NULL转换成数值的函数IFNULL SELECT*sal+IFNULLcomm0FROMemp;给列名添加别名在上面查询中出现列名为sal+IFNULLcomm0这很不美观,现在我们给这一列给出一个别名,为total SELECT*sal+IFNULLcomm0AStotalFROMemp;给列起别名时,是可以省略AS关键字的SELECT*sal+IFNULLcomm0totalFROMemp;5排序查询所有学生记录,按年龄升序排序SELECT*FROMstuORDERBYsageASC;或者SELECT*FROMstuORDERBYsage;查询所有学生记录,按年龄降序排序SELECT*FROMstuORDERBYageDESC;查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序SELECT*FROMempORDERBYsalDESCempnoASC;6聚合函数聚合函数是用来做纵向运算的函数COUNT统计指定列不为NULL的记录行数;MAXQ计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;MIN:计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;SUMO:计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;AVG0计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0/COUNT当需要纵向统计时可以使用COUNTQo查询emp表中记录数SELECTCOUNT*ASentFROMemp;查询emp表中有佣金的人数SELECTCOUNTcommentFROMemp;注意,因为count函数中给出的是comm歹ij那么只统计comm列非NULL的行数查询emp表中月薪大于2500的人数WHEREsal2500;统计月薪与佣金之和大于2500元的人数SELECTCOUNT*ASentFROMempWHEREsal+IFNULLcomm02500;查询有佣金的人数,以及有领导的人数SELECTCOUNTcommCOUNTmgrFROMemp;SUM和AVG当需要纵向求和时使用sum函数查询所有雇员月薪和SELECTSUMsalFROMemp;查询所有雇员月薪和,以及所有雇员佣金和SELECTSUMsalSUMcommFROMemp;查询所有雇员月薪+佣金和SELECTSUMsal+IFNULLcomm0FROMemp;统计所有员工平均工资SELECTSUMsalCOUNTsalFROMemp;或者SELECTAVGsalFROMemp;MAX和MIN查询最高工资和最低工资SELECTMAXsalMINsalFROMemp;分组查询当需要分组查询时需要使用GROUPBY子句,例如查询每个部门的工资和,这说明要使用部分来分组分组查询查询每个部门的部门编号和每个部门的工资和SELECTdeptnoSUMsalFROMempGROUPBYdeptno;查询每个部门的部门编号以及每个部门的人数SELECTdeptnoCOUNT*FROMempGROUPBYdeptno;数据库的发展历程没有数据库,使用磁盘文件存储数据;层次结构模型数据库;网状结构模型数据库;关系结构模型数据库使用二维表格来存储数据;关系-对象模型数据库;理解数据库RDBMS=管理员manager+仓库databasedatabaseN个tabletable表结构定义表的列名和列类型!表记录一行一行的记录!Mysql安装目录bin目录中都是可执行文件;文件是MySQL的配置文件;相关命令启动netstartmysql;关闭netstopmysql;mysql-uroot-p123-hlocalhost;a-u:后面的root是用户名,这里使用的是超级管理员root;A-p后面的123是密码,这是在安装MySQL时就已经指定的密码;•查询每个部门的部门编号以及每个部门工资大于1500的人数SELECTdeptnoCOUNT*FROMempWHEREsal1500GROUPBYdeptno;HAVING子句•查询工资总和大于9000的部门编号以及工资和SELECTdeptnoSUMsalFROMempGROUPBYdeptnoHAVINGSUMsal9000;注意,WHERE是对分组前记录的条件,如果某行记录没有满足WHERE子句的条件,那么这行记录不会参加分组;而HAVING是对分组后数据的约束8LIMITLIMIT用来限定查询结果的起始行,以及总行数查询5行记录,起始行从0开始SELECT*FROMempLIMIT05;注意,起始行从开始,即第一行开始!查询10行记录,起始行从3开始SELECT*FROMempLIMIT310;分页查询如果一页记录为10条,希望查看第3页记录应该怎么查呢第一页记录起始行为0一共查询10行;第二页记录起始行为10一共查询10行;第三页记录起始行为20一共查询10行;多表连接查询连接查询A内连接A外连接<左外连接©右外连接全外连接(MySQL不支持)A自然连接•子查询连接查询连接查询就是求出多个表的乘积,例如tl连接t2那么查询出的结果就是tlt2SELECT*FROMtlt2连接查询会产生笛卡尔积,假设集合A={ab}集合B={0l2}则两个集合的笛卡尔积为{a0a,D,a2b0blb2}可以扩展到多个集合的情况那么多表查询产生这样的结果并不是我们想要的,那么怎么去除重复的不想要的记录呢,当然是通过条件过滤通常要查询的多个表之间都存在关联关系,那么就通过关联关系去除笛卡尔积内连接上面的连接语句就是内连接,但它不是SQL标准中的查询方式,可以理解为方言!SQL标准的内连接为SELECT*FROMempeINNERJOINdeptdON=;内连接的特点查询结果必须满足条件例如我们向emp表中插入一条记录□101591^|保泊员|1009199»-12-31~~
80000.00~
20000.00jS0其中deptno为50而在dept表中只有
10、
20、
30、40部门,那么上面的查询结果中就不会出现“张三”这条记录,因为它不能满足二这个条件外连接(左连接、右连接)外连接的特点查询出的结果存在不满足条件的可能左连接SELECT*FROMempeLEFTOUTERJOINdeptdON二;左连接是先查询出左表(即以左表为主),然后查询右表,右表中满足条件的显示出来,不满足条件的显示NULLo子查询子查询就是嵌套查询,即SELECT中包含SELECT如果一条语句中存在两个,或两个以上SELECT那么就是子查询语句了•子查询出现的位置awhere后,作为条件的一部分;afrom后,作为被查询的一条表;•当子查询出现在where后作为条件时,还可以使用如下关键字:aany»all•子查询结果集的形式A单行单列(用于条件)A单行多列(用于条件)A多行单列(用于条件)»多行多歹(用于表)练习
1.工资高于smith的员工分析查询条件工资〉smith工资,其中smith工资需要一条子查询第一步查询smith的工资SELECTsalFROMempWHEREename=smith第二步查询高于smith工资的员工SELECT*FROMempWHEREsal(${第一步})SELECT*FROMempWHEREsalSELECTsalFROMempWHEREename=smith|•子查询作为条件•子查询形式为单行单列
2.工资高于30部门所有人的员工信息分析查询条件工资高于30部门所有人工资,其中30部门所有人工资是子查询高于所有需要使用all关键字第一步查询30部门所有人工资SELECTsalFROMempWHEREdeptno=30;|第二步查询高于30部门所有人工资的员工信息SELECT*FROMempWHEREsalALL${第一步}|结果SELECT*FROMempWHEREsalALLSELECTsalFROMempWHEREdeptno=30|•子查询作为条件•子查询形式为多行单列当子查询结果集形式为多行单列时可以使用ALL或ANY关键字
3.查询工作和工资与smith完全相同的员工信息分析:查询条件工作和工资与smith完全相同,这是子查询第一步查询出smith的工作和工资SELECTjobsalFROMempWHEREename=smith第二步查询出与smith工作和工资相同的人SELECT*FROMempWHEREjobsalIN${第一步}|结果SELECT*FROMempWHEREjobsalINSELECTjob.salFROMempWHEREename=smith•子查询作为条件•子查询形式为单行多列
4.查询员工编号为1006的员工名称、员工工资、部门名称、部门地址分析查询列员工名称、员工工资、部门名称、部门地址查询表emp和dept分析得出,不需要外连接(外连接的特性某一行(或某些行)记录上会出现一半有值,一半为NULL值)条件员工编号为1006第一步去除多表,只查一张表,这里去除部门表,只查员工表SELECTenamesalFROMempeWHEREempno=1006第二步让第一步与dept做内连接查询,添加主外键条件去除无用笛卡尔积SELECTFROMempedeptdWHERE=ANDempno=1006第二步中的dept表表示所有行所有列的一张完整的表,这里可以把dept替换成所有行,但只有dname和loc列的表,这需要子查询第三步查询dept表中dname和loc两列,因为deptno会被作为条件,用来去除无用笛卡尔积,所以需要查询它|SELECTdnameloc.deptnoFROMdept;第四步替换第二步中的deptSELECTFROMempeSELECTdname.locdeptnoFROMdeptdWHERE=AND=1006•子查询作为表•子查询形式为多行多列退出quit或exit;sql语句语法要求SQL语句可以单行或多行书写,以分号结尾;可以用空格和缩进来来增强语句的可读性;关键字不区别大小写,建议使用大写;分类DDLDataDefinitionLanguage数据定义语言用来定义数据库对象库、表、列等;DMLDataManipulationLanguage数据操作语言,用来定义数据库记录数据;基本操作查看所有数据库名称SHOWDATABASES;切换数据库USEmydbl切换到mydbl数据库;创建数据库CREATEDATABASE[IFNOTEXISTS]mydbl;修改数据库编码ALTERDATABASEmydblCHARACTERSETutf8创建表CREATETABLE表名列名列类型,列名列类型,;查看当前数据库中所有表名称SHOWTABLES;查看指定表的创建语句SHOWCREATETABLEemp查看emp表的创建语句;查看表结构DESCemp查看emp表结构;删除表DROPTABLEemp删除emp表;修改表L修改之添加列给stu表添加classname列ALTERTABLEstuADDclassnamevarcharlOO;.修改之修改列类型:修改stu表的gender列类型为CHAR⑵ALTERTABLEstuMODIFYgenderCHAR2;.修改之修改列名修改stu表的gender列名为sex ALTERTABLEstuchangegendersexCHAR2;.修改之删除列删除stu表的classname列:ALTERTABLEstuDROPclassname;5修改N修改表名称修改stu表名称为student ALTERTABLEstuRENAMETOstudent;其他常用命令mysql基本操作命令
一、数据库操作.新增数据库createdatabase数据库名字[数据库选项];数据库选项规定数据库内部该用什么进行规范字符集charset具体字符集(utf8)校对集collate具体校对集(依赖字符集).查看数据库查看所有的数据库showdatabases;匹配查询showdatabaseslikepattern;#pattern可以使用通配符_下划线匹配,表示匹配单个任意字符,如_s表示任意字符开始,但是以s结尾的数据库%:百分号匹配,表示匹配任意个数的任意字符,如student%表示以student开始的所有数据库查看数据库的创建语句showcreatedatabase数据库名字;.修改数据库数据库名字在mysql高版本中不允许修改,所以只能修改数据库的库选项(字符集和校对集)alterdatabase数据库名字[数据库选项];eg:alterdatabasestucharsetutf8;.删除数据库对于数据库的删除要谨慎考虑,是不可逆的dropdatabase数据库名字;.选择数据库use数据库名字;
二、数据表操作(字段).新增数据表createtable表名(字段名1数据类型comment备注...’,字段名2数据类型comment,备注:....#最后一行不需要逗号)[表选项];表选项1)字符集charset/characterset(可以不写,默认采用数据库的)2)校对集collate3)存储引擎engine=innodb(默认的)存储文件的格式(数据如何存储)^创建数据表的时候,需要指定要在哪个数据库下创建创建方式有隐式创建和显式创建1)显式创建createtable数据库名字.数据表名字2)隐式创建use数据库名字;.查看数据表查看所有的数据表showtables;查看表使用匹配查询Showtableslikepattern;#与数据库的pattern一样一和%两个通配符查看数据表的创建语句showcreatetable数据表名字;查看数据表的结构desc数据表名字;.修改数据表修改表名字renametable旧表名to新表名;修改表选项(存储引擎,字符集和校对集)altertable表名[表选项];修改字段(新增字段,修改字段名字修改西段类型,删除字段)新增字段altertable表名add[column]字段名字数据库类型[位置first/after];位置选项first在第一个字段after在某个字段之后,默认就是在最后一个字段后面修改字段名称altertable表名change旧字段名字新字段名字字段数据类型[位置];eg:altertablestudentnamefullnamevarchar
(30)afterid;修改字段的数据类型:altertable表名modify字段名字数据类型[位置];删除字段altertable表名drop字段名字;.删除数据表droptable表名;
三、数据操作.新增数据inserintotable表名[(字段列表)]values(值列表);.查看数据select*/字段列表from表名[where条件];.修改数据update表名set字段名=值where条件;注意使用update操作最好配合limit1使用避免操作大批量数据更新错误..删除数据deletefrom表名where条件;注意没有where条件就是默认删除全部数据.
四、列属性(字段).删除主键altertable表名dropprimarykey;.增加主键altertable表名addprimarykey(字段列表);#可以是复合主键.删除自增长只能通过修改字段属性的方法操作..删除唯一键altertable表名dropindex索引名字;#默认的唯一键名字就是字段的本身.增加唯一键altertable表名adduniquekey(字段列表);#可以是复合唯一索引
五、外键约束.创建表的时候增加外键constraint外键名字foreignkey(外键字段)references父表(主键字段);eg:--创建父表(班级表)createtableclass(idintprimarykeyauto_incrementnamevarchar(lO)notnullcomment班级名字,roomvarchar(lO)notnullcomment教室号’)charsetutf8;--创建子表(外键表)createtablestudent(idintprimarykeyauto_incrementnumberchar10notnulluniquecomment学号itcast+四位数,namevarcharlOnotnullcomment姓名,c_idintcomment班级ID一增加外键foreignkeyc_idreferencesclassidcharsetutf8;.创建表之后增加外键altertable表名addconstraint外键名字foreignkey外键字段references父表主键字段;eg一增加外键altertablestudentaddconstraintstudent_class_fkforeignkeyc_idreferencesclassid;.删除外键altertable表名dropforeignkey外键名字;#查看外键名字需要通过表创建语句来查询.eg:--删除外键altertablestudentdropforeignkeystudent_ibfk_l;数据查询语法DQLDQL就是数据查询语言,数据库执行DQL语句不会对数据进行改变,而是让数据库发送结果集给客户端SELECTselectionjist/火要查询的列名称*/FROMtablejist/*要查询的表名称火/WHEREcondition/*行条件*/GROUPBYgrouping_columns/*对结果分组火/ab|cd□「11■4d|□|1aSe□1a6gO2b4d|□2bSe□2b€g□3G4d□3C5e□3e69□4d4d1□4d5e□4d6g]。