一、数据库基础概念数据库database是表的集合一个数据库中能够有多个表表table由行列组成的二维表格类似于excel行记录record列字段field。二、SQL语法注释单行注释-- 注释内容多行注释/* 注释内容 */语法忽略单词的大小写语句用英文分号进行分隔常用数据类型序号数据类型英文说明1整数int2小整数tinyint有符号范围-128127无符号范围02553小数decimaldecimal(5,2)存5位数小数占2位4字符串varcharvarchar(3)最多存3个字符一个中文或一个字母都占一个字符中文需要用英文单引号括起来5日期时间datetime写SQL基本方法基本的select框架包括连接表》where后面的条件》select需要显示的字段三、常用SQL操作语句表序号操作关键词语法说明1创建表createcreate table 表名 (字段名 字段类型 约束选填,字段名 字段类型 约束选填)1. 多个字段用英文逗号分隔2删除表dropdrop table 表名drop table if exists 表名如果表存在才删除避免报错3插入记录insert1. 插入一组记录insert into 表名 values (值1,值2…)2. 插入一组记录的部分值insert into 表名 (字段名) values (值)值的顺序和字段名的顺序需要一致3. 一条语句插入多个记录insert into 表名 values (值1,值2), (值3, 值4)4查询记录select1. 查询表中所有内容select * from 表名2.条件查询指定字段select 字段1,字段2 from 表名 where 条件3. 查询记录并进行排序select * from 表名 order by 字段1 asc, 字段2 desc…4. 条件查询并排序select * from 表名 where 条件 order by 字段及排序方式1. *表示所有内容2. 通过as给字段起别名as可以省略select 字段1 as 别名1 from 表名3.通过as给表起别名as可以省略select * from 表名2 as 别名24.过滤重复记录distinct示例select distinct * from 表名5.where条件查询规则5.1 如果没有where条件就修改表中所有记录5.2 相关运算符 不等于!或者与and或or非not5.3.like模糊查询规则%任意个数的任意字符_一个任意字符示例select * from 表名 where name like ‘A%’5.4.范围查询规则in在一个非连续的范围内where size in (‘s’,‘m’,‘l’)between…and…在一个连续的范围内范围包含两个边界值where age between 20 and 255.5.空判断注意null表示什么都没有表示长度为0的字符串is null:为空where name is nullis not null不为空6.order by排序规则6.1 asc默认值从小到大排序升序desc从大到小排序降序。6.2 order by能够多个字段排序以字段顺序进行先后排序5修改记录updateupdate 表名 set 字段1值1,字段2值2… where 条件可使用where补充修改条件6删除记录deletedelete from 表名 where 条件可使用where补充删除条件7删除记录truncatetruncate table 表名1. 删除表中所有数据保留表结构2. 不需要添加条件3. 主键的自增长字段恢复从1开始delete会从之前的值继续向后18数据分组group by1. select 字段, count(*) from 表名 group by 字段2. select 聚合函数 from 表名 where 条件 group by 字段3. 注意多种组合的先后顺序【先筛选后分组】select 字段 from 表名where 条件 group by 字段 order by 字段4. 【先分组后筛选】select 字段 from 表名 group by 字段 having 字段1. 以字段进行分组便于配合聚合函数进行计算2.having分组后的筛选规则2.1 having总是出现在group by之后2.2 having能够配合聚合函数使用3. having与where筛选的区别3.1 where是对表的原始数据进行筛选having是对group by后的分过组数据进行筛选3.2 having后面能够使用聚合函数但是where后面不能使用聚合函数9数据分页显示limitselect * from 表名 limit start, count1. start表示开始行省略时采用默认值0从第一行开始count表示显示行数2. limit也能和where查询语句结合使用且总放在最后10子查询select嵌套select * from 表 where (select子查询)1. 子查询嵌套在主查询里面一般用括号括起来2. 子查询作为条件或者数据源来辅助主查询3. 子查询是可以独立存在的一条完整select语句4. 主查询不能独立运行需要依赖子查询的结果5. 特殊子查询定义标量子查询子查询返回结果只有一行一列列子查询子查询返回结果为一列多行表级子查询子查询返回结果为多行多列一个表四、字段的约束——校验记录序号约束关键词说明1主键primary key1. 此字段值不能重复auto_increment表示值自动增长2. 插入值时如果不想插入主键自增长字段可用占位符0或者null2非空not null此字段不允许空值3唯一unique此字段不允许重复4默认值default1. 当不填写此值时会使用默认值2. 示例age int default 30五、聚合函数——简单计算查询结果注意聚合函数能用于select中的显示结果和having后的条件不能在where后面的条件中套用序号函数作用示例1count()统计记录条数1. select count(字段或者*) from 表名2. 去重处理select count(distinct 字段) from 表名2max()查询数值型字段中的最大值select max(字段) from 表名3min()查询数值型字段中的最小值select min(字段) from 表名4sum()求数值型字段数值之和select sum(字段) from 表名5avg()求数值型字段平均值1. select avg(字段) from 表名2. null值不计算在avg的分母中仅计算有值数据的平均值六、多张表的连接方式序号连接方式关键词查询结果示例说明1内连接inner join…on…只显示两个表的交集1. 标准语法select * from 表1 inner join 表2 on 表1.字段 表2.字段 where 条件 order by 排序2. 多表内连接select * from 表1 inner join 表2 on 表1.字段 表2.字段 inner join 表3 on 表1也有可能是表2.字段 表3.字段3. 隐式内连接了解select * from 表1,表2 where 表1.字段 表2.字段1. 着重关注表中映射的字段2. 通过控制select后面的内容来展示部分内容多表中的同名字段需要补充上表名来进行区分表1.字段2左连接left join…on…显示两个表的交集 左表特有的数据标准语法select * from 表1 left join 表2 on 表1.字段 表2.字段 where 条件 order by 排序右表中不存在的数据用null填充3右连接right join…on…显示两个表的交集 右表特有的数据标准语法select * from 表1 right join 表2 on 表1.字段 表2.字段 where 条件 order by 排序左表中不存在的数据用null填充4自关联on 表的别名1.字段1 表的别名2.字段2//自关联定义同一张表做连接查询表中的某一字段关联了这个表中另外的一个字段但是他们的业务逻辑含义不一样七、MySQL常用内置函数内置函数能够在where后面的条件和select的显示中进行套用序号函数关键词示例说明1拼接字符串concatconcat(字符1, 字符2, 字符3…)1. 对多个字符串进行拼接2. 参数可以是数字也可以是汉字2计算字符个数lengthlength(str)计算字符串长度一个汉字长度为33截取字符串left/right/substringleft(str,截取长度)right(str,截取长度)substring(str,起始位置从1开始,截取长度)1. 从左边/右边/中间截取字符串2. 英文和中文都视为1个字符4去除字符串空格ltrim/rtrim/trimltrim(str)rtrim(str)trim(str)去除字符串左侧/右侧/两侧的空格5求四舍五入值roundround(数字, 需要保留的小数位数默认是0对数字进行四舍五入6随机数randrand()实现表的随机排序order by rand()生成一个0~1的随机数7返回当前时间信息current_date/current_time/now返回当前日期current_date()返回系统当前时间current_time()返回系统当前日期时间now()日期和时间通常在插入记录时调用八、一些拓展知识序号函数关键词/解释示例说明1存储过程procedure1. 创建存储过程create procedure 存储过程名称(参数列表)beginsql 语句end2. 调用存储过程call 存储过程(参数列表)3. 删除存储过程drop procedure if exists 存储过程2视图view1. 创建视图create view 视图名称 as select 语句2. 删除视图drop view if exists 视图1. 视图就是对查询进行封装返回结果也是一张表2. 能够对视图进行表的只读操作3事务事务是多条更改数据操作的sql语句集合这些操作要么都执行要么都不执行开启事务begin回滚事务rollback;提交事务commit;1. 数据的更改涉及到事务2. 开启事务执行update或delete语句变更写到缓存中不会立即生效3. 回滚事务放弃修改4. 提交事务对表的修改生效4索引index1. 创建索引create index 索引名称 on 表名(字段)2. 查看索引show index from 表名3. 删除索引drop index 索引名称 on 表名1. 创建索引时如果指定的字段是字符串则还需要指定长度一般长度与定义字段时的长度一致2. 索引能够加快数据查询效率where后面的字段数据库会自动查找是否有索引3. 系统会自动为主键创建索引九、基于命令行的mysql序号操作命令说明1连接数据库并登录mysql -h mysql服务器地址 -u 用户名 -p-p输入密码进入登录2展示数据库/表名show databases/tables3进入数据库use 数据库名4告诉mysql客户端编码方式set names gbk避免数据库查询结果返回乱码5查看表的结构desc 表名6创建数据库create database 数据库名 default charset 字符集字符集默认为utf8十、参考学习资料软件测试工程师所需的MySQL数据库技术mysql系统精讲课后练习B站学习连接https://www.bilibili.com/video/BV1M541147Cn/?spm_id_from333.788.videopod.episodesvd_source7089343738fb1b5a72c1db4d1f9c49ea
