☛ 运维之数据库数据库运维最常用的莫过于4大技能增删改查另外才用更高级的操作一、Mysql之查1查数据库:show databases;2) 查表show tabless;3查表结构desc tablesname4单表查询所有;select * from student;5) 单表查询某字段select s_name from student;6单表查询条件限制查询select s_name from student where s_id5;7单表查询自定字段列值后重命令显示一般该字段名字长不便显示selects_name as 名字 from student;81.2、高阶SQL语句1、排序① 按单字段排序asc 升序[默认] desc 降序语法select 显示用字段 from 表名 where 判断用字段Number order by 以哪个字段做排序 desc;举例select id from user where point200 order by point desc;② 多字段排序order by 以后的参数 使用,分割优先是按先后循序而定语法select 字段1 from 表名 order by 字段2 desc,字段3 desc;举例select id from user order by point desc,id desc;注意先根据字段1进行降序排序在进行字段2的降序排序第一个字段只有在出现相同数值的时候第二字段才有意义。2、条件判断① or/andand表示需要同时满足or表示只需要满足其中之一语法select * from 表名 xx 20 and xx10;举例select id from user where point 100 and point 500;② 嵌套/多条件语法select * from 表名 where xx 10 or (xx 15 and xx 50);举例select id from user where point 100 or (point 150 and point 500);3、重复记录① 查询不重复记录使用distinct参数语法select distinct 字段 from 表名;示例select distinct id from user;② 指定多字段去重distinct必须放在最开头distinct只能使用需要去重的字段进行操作指定多个字段去重的话必须要满足这几个字段同时出现重复值时才会被过滤语法select distinct 字段 from 表名 where 字段 in (select distinct 字段 from 表名);举例select id,name from user where point in (select distinct point from user);4、排序③ 对结果进行分组使用group by语句来实现对 SQL 查询出来的结果进行分组group by 通常都是结合聚合函数一起使用的常用的聚合函数包括计数(count) 、求和(sum) 、求平均数(avg) 、最大值(max) 、最小值(min)group by 分组的时候可以按一个或多个字段对结果进行分组处理语法select count(字段) from * where 表名 group by 字段;举例select count(name),point from user where point300 group by point;对user表进行分组筛选范围/条件是point大于等于300的namepoint相同的会默认分在一组④ 结合排序语法语法select count(字段) from * where 表名 group by 字段 order by count(字段) desc;举例select count(id),name from user group by point;selectcount(id),name from user group by point order by count(id)asc;5、有限输出内容⑤ 限制条目结果默认sql’查询的结果集返回的是所有匹配的记录我们使用limit语句就可限制返回结果条目其中位置偏移量第一条是0第二条是1以此类推。语法select * from 表名 limit number;select * from 表名 order by 字段 desc limit number; //结合order by排序举例select * from user limit 2,3; //limit 2 限制结果为前2条记录limit 2, 3 从第2行开始查询3条记录 起始值为第0行limit 2 offset 3 查询 2条记录从第3行开始 起始值为第0行select * from user order by id desc limit 2,3;6、子查询⑥ 子查询子查询也被称作内查询或者嵌套查询是指在一个查询语句里面还嵌套着另一个查询语句子查询语句是先于主查询语句被执行其结果作为主查询的条件返回给主查询进行进一步的查询过滤子语句可以与主语句所查询的表相同也可以是不同的表示例1select name,score from info where id in (select id from info where score80); //子查询不仅可以在select语句中使用在insert、update、delete中也同样使用,支持多次嵌套,还可以使用not in表示对结果集过滤⑦ 子查询exists语法select count(*) as number from 表名 where exists(select id from 表名 where 字段‘值’)// exists 用来进行布尔值判断后面跟内查询语句是否成立where 之后跟条件判断加exists之后的条件是否成立如果成立则正常执行count为计数 sum为求和 使用sum求和结合exists如子查询结果集不成立输出null/⑧ 子查询-别名eg1先查询表字段[看作一个结果集]然后将结果集作为一张表用于主查询进一步筛选语法select 别名.字段 from (select 字段 from 表) 别名;举例select a.id from (select id,name from info)a;select * from 表名此为标准格式而以上的查询语句表名的位置其实是一个结果集mysql并不能识别此时给与结果集设置一个别名并且以select a.id,name from a;的方式查询将此结果集视为一张表就可以正常查询出数据了。eg2当表中有命名过长的字段时可以通过设置别名来缩短语句中书写长名称用例select 字段 AS 别名 from 表名;eg3使用AS后可以用别名代替字段名其中AS语句是可选的其后的别名主要是为表内列或者表提供临时的名称一般在查询过程中使用而库内实际的表名或字段不会改变的多用于对复杂的表或多表联合查询的场景别名可以有效缩短查询语句书写。示例select a.name as 姓名,a.point as 点数 from user as a;⑨ 去加条件查询使用IN语句限定范围语法select 字段 from 表名 where 字段 in (‘值1’,‘值2’…);举例select name from user where money in (100,200);7、模糊查询⑩ 通配符主要用于替换字符串的部分字符通过部分字符的匹配将相关结果查询出来其中%百分号表示零个或以后多个字符_下划线表示单个字符示例select * from info where name like ‘F_i%k’; //查询F后任意单个字符i后零个或多个任意字符k1.3、MySQL集群状态1、主从查询主库show master status\G;备库show slave status\G;2个输出中位置一致Slave_SQL_RunningYesslave的I/O和SQL线程都已经开始运行而且Seconds_Behind_Master不再是NULL。2、确认主从同步故障日志mysql show variables like “log_error”;1.4、多表联合查询MySQL多表连接查询是一种为将多张表中的数据联合起来合并展示的查询方式通常用于要查询的字段分布在多张关联表中需要跨多张表查询的情况。MySQL多表连接查询可以通过多种方式实现例如INNER JOIN、LEFT JOIN、RIGHT JOIN、CROSS JOIN交叉连接等语句。连接类型连接方式说明内连接join等同于inner join用于获取两个表中字段匹配关系的记录即两个表的交集部分外连接left join 或left outer join(等同于left join)获取左表所有记录即使右表没有对应匹配的记录右表中没有匹配的记录则填充NULL,即左表优先原则外连接right join 或right outer join(等同于right join)与LEFT JOIN相反用于获取右表所有记录即使左表没有对应匹配的记录左表中没有匹配的记录则填充NULL。外连接join union或union并集去重外连接union all不会去掉结果集中的重复行交叉连接cross join获取两个表中所有记录的排列组合也就是两个表的笛卡尔积1内连接示例一种场景求交集内连接查询内连接查询是最常见的连接查询它可以查询两张或两张以上的表内连接[inner] join从左表中取出每一条记录,去右表中与所有的记录进行匹配: 匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不保留.基本语法左表 [inner] join 右表 on 左表.字段 右表.字段;其中on表示连接条件: 条件字段就是代表相同的业务含义(如my_student.c_id和my_class.id)当两个表中存在相同意义的字段的时候就可以通过该字段来连接查询这两个表当该字段的值相同时就可以查出该记录。内连接可以没有连接条件: 没有on之后的内容,这个时候系统会保留所有结果。//语法旧select 字段1,字段2... from 表1,表2...[where 条件];如果不加条件直接进行查询:select * from tab_a,tab_b,则会出现结果为 笛卡尔乘积的结论 //笛卡尔乘积公式:A表中数据条数 * B表中数据条数笛卡尔乘积.selecta.*b.* from tab_a a,tab_b b wherea.idb.id;#上述语句使用逗号,来分隔表或列该语句使用了老式的 JOIN 语法这被称为隐式或交叉 JOIN它在没有指定 JOIN 类型时默认为 CROSS JOIN笛卡尔积积会导致大量无用数据。如果 tab_a 和 tab_b 表中的 id 列是用来关联两个表的外键或主键并且想要执行一个内连接INNER JOIN使用显式的 INNER JOIN 语法会更为清晰和现代selecta.*, b.* from tab_a a innerjointab_b b on a.idb.id;#推荐selecta.*, b.* from tab_a ajointab_b b on a.idb.id;#注不指定join类型默认情况下是 INNER JOINselecta.*, b.* from tab_a ajointab_b b where a.idb.id;#[on过滤条件 ] [ where查询条件 on从语法上可省略但如果省略它将查询的是多表的笛卡尔积交叉连接结果#多join嵌套连续使用多个 JOIN 来连接多个表时实际上是在执行一系列嵌套的连接操作即递进交集最小交集多表的共同部分且满足过滤条件SELECT * FROM 表1 JOIN 表2 ON 条件过滤1 JOIN 表3 ON 条件过滤2#得出的结果是 表1 和 表2 的连接结果与 表3 的连接结果的交集。也就是说只有当记录同时存在于所有三个表中符合所有连接条件这些记录才会出现在最终的结果集中。2外连接示例六种场景以某张表为主,取出里面的所有记录, 然后每条与另外一张表进行连接: 不管能不能匹配上条件,最终都会保留: 能匹配,正确保留; 不能匹配,其他表的字段都置空NULL。//1、左外连接 左连接left join: 左外连接(左连接), 以左表为主表左表不管能不能匹配上条件,最终都会保留:能匹配,正确的保留;若不能匹配,右表的字段都置NULL。 基本语法: from 左表 leftjoin右表 on 左表.字段右表.字段;语法select * from 表1 leftjoin表2 on 连接条件[where条件查询]#表1查询结果是所有数据表2查询结果是与表1重合部分的数据没有则填null全显示左表eg1select a.*, b.* from tablea a left[outer]jointableb b on a.idb.id;#得到的是A的所有数据和满足某一条件的B的数据eg2select a.id aid,a.age,b.id bid,b.name from tablea a leftjointableb b on a.idb.id Where b.id is null#表a的数据全部显示匹配表b的数据也显示且同时过滤表b的id为空的数据这种场景下得到的是A中的所有数据减去与B满足同一条件 的数据然后得到的A剩余数据//2、右外连接 right join: 右外连接(右连接), 以右表为主表右表不管能不能匹配上条件,最终都会保留:能匹配,正确的保留;若不能匹配,左表的字段都置NULL。 基本语法: from 左表 rightjoin右表 on 左表.字段右表.字段;语法select * from 表1 rightjoin表2 on连接条件[where条件查询] eg1select a.id aid,a.age,b.id bid,b.name from tablea a rightjointableb b on a.idb.id#表b的数据全部显示表a匹配表b的数据也显示不匹配的填充null即显示的是B的所有数据和满足某一条件的A的数据eg2select a.id aid,a.age,b.id bid,b.name from tablea a rightjointableb b on a.idb.id where a.id is null#将得到的是B中的所有数据减去 与A满足同一条件 的数据“然后得到的B剩余数据//全连接求并集使用 leftjoinunion right joinunion过后重复的记录会合并(select a.id aid,a.age,b.id bid,b.name from tablea a leftjointableb b on a.idb.id)union(select a.id aid,a.age,b.id bid,b.name from tablea a rightjointableb b on a.idb.id)#得到的是满足某一条件的公共记录和各表独有的记录即各表独立部分互相交集部分去重//全连接剥离交集部分leftjoin is null union right joinis null(select a.id aid,a.age,b.id bid,b.name from tablea a leftjointableb b on a.idb.id where b.id is null)#A-Bunion(select a.id aid,a.age,b.id bid,b.name from tablea a rightjointableb b on a.idb.id#B-Awhere a.id is null)#得到的是AB中不满足某一条件的记录之和,即最后得到的是AB-AB//查询id小于3和名字为“英语”的课程select* from course where id3unionselect* from course wherename英语;注.on在内连接中可省略外连接不可省略如上一般内连接都需要加上on限定条件如果不加会被解释为交叉连接on在内连接中执行效果和外连接执行效果不同比如left join…on查询不能过滤掉左表中的数据而内连接on查询可以过滤掉全局数据。外连接中on和where也不同on筛选笛卡尔积过滤条件where筛选具体业务3交叉连接 cross join示例select a.id aid,a.age,b.id bid,b.name from tablea a cross join tableb b;#得到AB记录的排列组合即笛卡儿积加条件select a.id aid,a.age,b.id bid,b.name from tablea across join tableb b where a.id b.id;#此时等同于内连接的效果注一般cross join后面加上where条件但是用cross joinon也是被解释为cross joinwhere4自连接示例select * from 表名 as t1,表名 as t2 where t1.idt2.id [, ...]5联合查询它查询的结果是将多个select语句的查询结果合并到一块因为在某种情况下需要将几个select语句查询的结果合并起来显示。比如现在需要查询两个公司的所有员工的信息这就需要从甲公司查询所有员工信息再从乙公司查询所有的员工信息然后将两次的查询结果进行合并。可以使用union和union all关键字进行操作。联合查询中order by不能直接使用不能出现两次,需要对查询语句使用括号才行#基本语法union选项有两个all表示无论重复都输出distinct: 去重(整个重复)默认的select语句1 union[union 选项]select语句2 union|[union 选项]select语句n //eg1联合查询只要求字段一样, 跟数据类型和查询字段的顺序无关selectid,addrid from addr union allselectid,addrid from student;//order byselect*from student wheresexwomanunionselect*from student wheresexmanorder by score;#order by不能直接出现在union的子句中但是可以出现在子句的子句中select*from(select *from student wheresexwomanorder by score)student unionselect*from(select *from student wheresexmanorder by score)student;联合查询的意义:查询同一张表但是需求不同 如查询学生信息, 男生身高升序, 女生身高降序多表查询: 多张表的结构是完全一样的,保存的数据(结构)也是一样的.6子查询使用in关键字可以将原表中特定列的值与子查询返回的结果集中的值进行比较 如果某行的特定列的值存在则在select语句的查询结果中就包含这一行。#查询成绩大于80的学生的所有信息先在子查询中查出成绩大于80的结果集然后将原成绩表中的成绩与结果集进行比较如果存在就输出这条学生的记录select* from student where score in(select score from student where score80);#带比较运算符的子查询#如果可以确认子查询返回的结果只包含一个单值那么可以直接使用比较运算符连接子查询。#经常使用的比较运算符包括等于、不等于或!、小于、大于、小于等于和大于等于#查询奖学金等级为1的学生信息select* from student where score(select score from scholarship wherehanmei1);#带exists的子查询:exists子查询就是用来判断某些条件是否满足(跨表),exists是接在where之后,exists返回的结果只有0和1.#如果存在成绩大于90的人则列出整个表的记录select* from student where exists(select *from student where score90);#带any关键字的子查询:any关键字表示满足其中的任意一个条件使用any关键字时只要满足内层查询语句结果的的任意一个就可以通过该条件来执行外层查询语句。select* from student where addridany(select addrid from addr);#带all关键字的子查询:all和any刚好是相反的all关键字表示满足所有结果使用all关键字要满足内层查询语句的所有结果才可以通过该条件来执行外层查询语句。select* from student where addridall(select addrid from addr);二、mysql之改1创建表使用 AS作为连接语句参照另一个表语法create table 表名 as select * from 表名举例create table user_new as select * from user;注意1.创建一个新的表 定义了表结构 插入表数据2.但是约束没有复制过来但是如果原表设置了主键那么Defaut默认为02创建视图视图可看作是一个临时结果集的虚拟表是sql的查询结果其内容由查询定义。同真实的表一样视图包含一系列带有名称的列和行数据在使用视图时动态生成。视图的数据变化会影响到基表基表的数据变化也会影响到视图[insert update delete ] ; 创建视图需要create view 权限并且对于查询涉及的列有select权限使用create or replace 或者 alter修改视图那么还需要改视图的drop权限。用法create view v_xxx as select * from 表名 where 字段Number; //Mysql视图的定义在from关键字后面不能包含子查询show create view 视图名; //显示视图创建情况drop view 视图名[,视图名…]; //删除视图rename table 视图名 to 新视图名; //重命名视图3修改视图用法alter view 视图名 as select 语句或alter view 视图名 as select 视图; //修改了视图对基表数据有影响注意如果视图中的每一行都与物理表一一对应就可以对视图进行更新删除添加等操作如view的行是由多个物理表多行经计算得到的结果则view就不可以更新。4修改表用法update 表名 set 字段‘xx’ where 字段‘xx’; //修改原表数据该原数据后就自动映射到对应的视图中NULL可用来表示数据表中字段列中缺失的值即表中该字段没有值如果在创建表时限制某些字段不为空则可以使用NOT NULL关键字来占位在向表内插入记录或者更新记录时如果该字段没有NOT NULL并且没有值这时候新纪录的该字段将被保存为NULL需要注意的是NULL值与数字0或者空白的字段是不同的值为NULL的字段表示没有值在SQL语句中使用IS NULL可以判断表内的某个字段是不是NULL值相反用IS NOT NULL可以判断不是NULL值而0值是0而不是无区别是空值0长度为0不占用空间而null值长度为null占用空间。使用count()计算时NULL会忽略空值会加入计算。update 表名 set 字段‘’ from 字段值; //将某表的某字段值配置为空select * from 表名 where 字段 is null; //查询为null的值select * from 表名 where 字段 is not null; //查询不为null的值三、MySQL之增3.1、创建数据库CREATE TABLE IF NOT EXISTS dept(d_id int not null auto_increment PRIMARY KEY, d_name VARCHAR(50)not null COMMENT部门名称)ENGINEINNODB DEFAULT charset utf8;#添加部门数据INSERT INTOdeptVALUES(1,教学部);INSERT INTOdeptVALUES(2,销售部);INSERT INTOdeptVALUES(3,市场部);INSERT INTOdeptVALUES(4,人事部);INSERT INTOdeptVALUES(5,鼓励部);-- 创建人员 DROP TABLE IF EXISTSperson;CREATE TABLEperson(idint(11)NOT NULL AUTO_INCREMENT,namevarchar(50)NOT NULL,agetinyint(4)DEFAULT0,sexenum(男,女,人妖)NOT NULL DEFAULT人妖,salarydecimal(10,2)NOT NULL DEFAULT250.00,hire_datedateNOT NULL,dept_idint(11)DEFAULT NULL, PRIMARY KEY(id))ENGINEInnoDBAUTO_INCREMENT13DEFAULTCHARSETutf8;-- 添加人员数据 -- 教学部 INSERT INTOpersonVALUES(1,alex,28,人妖,53000.00,2010-06-21,1);INSERT INTOpersonVALUES(2,wupeiqi,23,男,8000.00,2011-02-21,1);INSERT INTOpersonVALUES(3,egon,30,男,6500.00,2015-06-21,1);INSERT INTOpersonVALUES(4,jingnvshen,18,女,6680.00,2014-06-21,1);-- 销售部 INSERT INTOpersonVALUES(5,歪歪,20,女,3000.00,2015-02-21,2);INSERT INTOpersonVALUES(6,星星,20,女,2000.00,2018-01-30,2);INSERT INTOpersonVALUES(7,格格,20,女,2000.00,2018-02-27,2);INSERT INTOpersonVALUES(8,周周,20,女,2000.00,2015-06-21,2);-- 市场部 INSERT INTOpersonVALUES(9,月月,21,女,4000.00,2014-07-21,3);INSERT INTOpersonVALUES(10,安琪,22,女,4000.00,2015-07-15,3);-- 人事部 INSERT INTOpersonVALUES(11,周明月,17,女,5000.00,2014-06-21,4);-- 鼓励部 INSERT INTOpersonVALUES(12,苍老师,33,女,1000000.00,2018-02-21, null);四、MySQL之删五、MySQL之用户管理5.1、创建用户授权GRANT REPLICATION SLAVE,REPLICATION CLIENT on.to slaveuser‘192.168.15.%’ IDENTIFIED BY ‘123456’;GRANT REPLICATION SLAVE,REPLICATION CLIENT on.to slaveuser‘localhost’ IDENTIFIED BY 123456’;六、MySQL之索引七、MySQL之视图八、MySQL之事务九、MySQL之引擎十、MySQL 联合查询…………未完待整理