一、SQL一、DDLDDL数据定义语言1、数据库操作#查询所有数据库showdatabases;#查询当前数据库selectdatabase();#创建createdatebase[ifnotexist]数据库名[defaultcharset字符集][collate排序规则];#删除dropdatabase[ifexist]数据库名;#使用use数据库名;2、表操作前提是要进入当前数据库#查询**当前数据库**所有表showtables;#查询表结构desc表名;#查询指定表的建表语句showcreatetable表名;# 创建createtable表名(字段1字段1类型[comment字段1注释],字段2字段2类型[comment字段2注释],......)[comment表注释];#[...] 为可选参数,最后一个字段后面没有,逗号字段类型char(10)固定十位当输入2位后其他用0补齐性能好varchar(10)最长10位根据内容计算占用空间性能差#修改表#添加字段altertable表名add字段名 字段类型(长度)[comment注释][约束];#修改字段#修改数据类型altertable表名modify字段名 新数据类型长度;#修改字段名和字段类型altertable表名 change 旧字段名 新字段名 类型长度[comment注释][约束]#删除字段altertable表名drop字段名;#修改表名altertable表名renameto新表名;#删除表droptable[ifexists]表名;#删除指定表并重新创建truncatetable表名;二、DMLDML:Data Manipulation Language (数据操作语言),对数据库中表的记录进行增删改。1、insert_添加数据#给指定字段添加数据insertinto表名(字段名1,字段名2....)values(值1,值2...);#给所有字段添加数据insertinto表名values(值1,值2....);#批量插入insertinto表名(字段名1,字段名2....)values(值1,值2...),(值1,值2...),(值1,值2...)..;insertinto表名values(值1,值2..),(值1,值2....),...;注意:插入数据时指定的字段顺序需要与值的顺序是一一对应的。字符串和日期型数据应该包含在引号中。插入的数据大小应该在字段的规定范围内2、update_修改数据#没有条件时代表修改整张表的数据update表名set字段名1值1,字段名2表2,...[where条件];3、delete_删除数据deletefrom表名[where条件];#delete的条件可有可无没有时代表删除整张表啊数据#delete不能删除某一个字段的值可以用update三、DQLDQLData Query Language数据查询语言1、基本查询#查询多个字段select字段1,字段2....from表名select*from表名#设置别名select字段1[as别名],字段2[as别名]...from表名;#去重selectdistinct字段列表from表名;2、条件查询select字段列表from表名where条件列表;#占位符 —— %#查询姓名为2个字的员工select*fromempwherenamelike__;#查询身份证号最后一位为X的员工select*fromwmpwhereidcardlike%X条件3、聚合函数将一列数据作为一个整体进行纵向计算注意所有null值不参加聚合运算select聚合函数(字段列表)from表名;4、分组查询select字段列表from表名[where条件]groupby分组字段名[having分组后过滤条件]where和having区别where是分组之前进行过滤不满足where条件不参与分组having是分组之后对结果进行过滤where不能对聚合函数进行判断having可以5、排序查询关键字asc(升序可省略) desc降序select字段列表名from表名orderby字段1排序方式1字段2排序方式2;6、分页查询select字段列表from表名limit起始索引查询记录数;注意起始索引从0开始起始索引(查询页码-1)*每页显示记录数。分页查询是数据库的方言不同的数据库有不同的实现MySQL中是LIMIT。如果查询的是第一页数据起始索引可以省略直接简写为limit10。四、DCLDCL:Data Control Language管理用户控制数据库的访问1、管理用户2、权限控制常用权限二、函数函数是指一段可以直接被另一段程序调用的程序或代码。1、字符串函数select函数名(参数);#企业员工的工号统一为5位数目前不足5位数的全部在前面补0。比如:1号员工的工号应该为00001。updateempsetworknolpad(workno,5,0);2、数值函数#生成随机六位数验证码selectlapd(round(rand()*1000000,0),6,0);3、日期函数4、流程函数三、约束约束是作用于表中字段上的规则用于限制存储在表中的数据保证数据库中数据的正确、有效和完整性。createtableuser(idintprimarykeyauto_incrementcomment主键;namevarchar(10)notnulluniquecomment姓名;ageintcheck(age0age120)comment年龄;statuschar(1)default1comment状态;genderchar(1)comment性别)comment用户表;#添加外键createtable表名(字段名 数据类型,..[constraint][外键名称]foreignkey(外键字段名)references主表主表列名);#或者altertable表名addconstraint外键名称foreignkey(外键字段名)references主表主表列名;#删除外键altertable表名dropforeignkey外键名称;外键删除更新行为altertable表名addconstraint外键名称foreignkey(外键字段名)references主表(主键列表名)onupdate约束ondelete约束;四、多表查询表关系一对一多用于单表拆分,将一张表基础字段放在另一张表中详情字段放在另一张表中在任意一方加入外键关联另外一方的主键并且设置外键为唯一的unique一对多在多的一方建立外键指向另一方的主键多对多需要中间表中间表至少包含两个外键分别关联两方主键。笛卡尔积集合A和集合B的所有组合情况多表查询时需要消除无效的笛卡尔积。一、连接查询1、内连接内连接A、B交集部分数据。# 隐式内连接select字段列表from表1,表2where条件;# 显式内连接select字段列表from表1[inner]join表2on连接条件...;注意内连接两种写法性能、结果完全相同只是语法层面不同新项目一律使用显式 INNER JOIN ON隐式逗号写法属于老旧语法可读性差容易笛卡尔积只在老项目遗留代码会见到ON与WHERE的条件位置仅对外连接生效对内连接没有区别2、外连接#左外连接查询左表所有数据以及两张表交集部分select字段列表from表1left[outer]join表2on条件;#右外连接查询右表所有数据以及两张表交集部分select字段列表from表1right[outer]join表2on条件;注意ON关联阶段过滤是两张表做连接时的条件只影响被连接的次表驱动表的数据不会丢失。WHERE连接完成后过滤是对连接之后的完整结果集做筛选不满足条件的整行直接被删掉会丢失左表数据表和表的关联字段必须写在 ON。外连接想保住左表全部数据右表的筛选条件放 ON。条件放WHERE就代表可以接受左表数据被删掉。只有外连接才有这个差异内连接 ON 和 WHERE 结果完全一致。3、自连接当前表与自身表的连接查询自连接必须使用表别名。自连接查询可以是内连接查询也可以是外连接查询。SELECT字段列表from表A 别名A,表A 别名Bwhere条件;SELECT字段列表from表A 别名Ajoin表A 别名Bon条件;4、联合查询将多次查询的结果合并起来形成一个新的查询结果集。查询语句1union[all]查询语句2;注意对于联合查询的多张表的列数必须保持一致字段类型也需要保持一致。unionall会将全部的数据直接合并在一起union会对合并之后的数据去重。二、子查询SQL语句中嵌套SELECT语句称为嵌套查询又称子查询。子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个select*fromt1wherecolumn1(SELECTcolumn1fromt2)根据查询结果不同可以分为列子查询(子查询结果为一列)行子查询(子查询结果为一行)表子查询(子查询结果为多行多列)标量子查询(子查询结果为单个值)根据子查询的位置分为where 之后、from之后、select之后。1、标量子查询子查询返回的结果是单个值(数字、字符串、日期等)最简单的形式这种子查询成为标量子查询。常用操作符 2、列子查询子查询返回的结果是一列(可以是多行)这种子查询称为列子查询常用操作符3、行子查询子查询返回的结果是一行(可以是多列)这种子查询称为行子查询常用的操作符: IN NOT IN4、表子查询子查询返回的结果是多行多列这种子查询称为表子查询。常出现在from之后常用的操作符:IN五、事务事务是一组操作的集合它是一个不可分割的工作单位事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失败。 默认MysQL的事务是自动提交的也就是说当执行一条DML语句MySQL会立即隐式的提交事务。# 查看设置事务提交方式selectautocommit;#设置为手动提交setautocommit0;#手动提交commit;#回滚rollback;#方式二不修改提交方式#开启事务starttransaction;#或begin;#提交commit;#回滚rollback;事务四大特性原子性(Atomicity):事务是不可分割的最小操作单元要么全部成功要么全部失败。一致性(Consistency):事务完成时必须使所有的数据都保持一致状态。隔离性(Isolation):数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行。持久性(Durability):事务一旦提交或回滚它对数据库中的数据的改变就是永久的。并发事务问题事务隔离级别解决并发事务问题#查看事务隔离级别selecttransaction_isolation;#设置事务隔离级别 session会话级别针对当前客户端窗口有效global 针对所有有效set[session|global]transactionisolationlevel{readuncommited|readcomminted|repeatableread|serializable}