数据库

Mysql基础知识总结

2016-08-16  本文已影响81人  那页阳光

检索数据####

代码： select columns from tableName

未排序的数据

没有明确排序查询结果，则返回的数据的顺序没有特殊意义。返回的数据可能是数据被添加到表中的顺序，也可能不是。

代码： select distinct columns from tableName

检索唯一的值（不同的行，没有重复行）

distinct 应用于所有列，而不仅仅是前置它的列。所查询的结果是针对于记录（行）而不是字段（列）。确保查询出来的记录不会出现两条以上一模一样的数据。

代码：select columns from tableName order by columns asc|desc

数据结果集排序

  1. 排序的字段并不仅仅限于查询的字段
  2. 多个字段排序，当前面的字段值相等的时候再按照第二个字段排序，以此类推。
  3. 如果order by搭配limit使用。limit 必须位于order by之后（容易忽略的语句顺序，导致错误警告）。

指定排序字段时可以选择字段的列值进行排序。如：order by id desc 其中id在第一列，则可写成order by 1 desc。不管选择哪种写法，自己开心就好，能实现功能就行。通过列值排序的方式官方已不赞成使用，不再属于标准SQL的一部分

过滤数据集

代码：select columns from tableName where columns<100 order by id limit 10

  1. 使用where过滤数据时，where应该跟随在from子句后，order by之前。
  2. 在字段进行过滤比较时，尽量不要让mysql去进行类型转换然后再对比。 如: where user_id='1' 在数据库中user_id类型为整形,where进行比较的时候，首先把数据库的数据类型转换为字符串型再与'1'进行比较。 自动类型转换对数据库服务器来说本身就是一种消耗，还有可能阻止索引的使用。
  3. between 操作符的范围为大于等于左值小于等于右值
  4. 检测null值:is null 检测非null值：is not null
  5. where里面and的运算优先级比or更高 where user_id=1 or user_id=2 and age=18与where (user_id=1 or user_id=2) and age=18 结果是不同的。

使用通配符进行过滤

代码：select columns from tableName where columns like '%silence.sir%'

常见通配符：- % 表示任何字符出现任意次数（包括0次，如:where user_name like jie%jie 字符'jiejie'也是能匹配的）。默认不区分大小写，可配置。不能匹配null。- _ 只匹配单个字符，与%能匹配0个字符不一样，_总是匹配一个字符，不能多也不能少。

注意：

  1. 不能依赖于like去查询数据集，如果其他操作符能达到相同目的，应该使用其它操作符。
  2. 尽量不要在like模式的开始处使用通配符，在开始处使用通配符的搜索模式不支持使用索引
  3. 如果需要在搜索模式中实际使用某个通配符，需要加反斜线转义。如：user_name like 'jiejie\_yh%'

使用正则表达式过滤数据

代码：select columns from tableName where columns regexp '.iejie'

这里使用了正则表达式:'.iejie'，.在正则表达式中用于匹配任意一个字符。所以字段值为任意一个字符开始,以'iejie'结束的文本都能被匹配出来。

like与regexp的区别：

  `like`匹配的是整个列的内容，如果被匹配字符串在列值中出现，`like`将不会找到它，相应的行不会被返回。        如搜索模式为：`where user_name like 'jiejieyh'`(注意：该例子不带任何通配符)        列值为：'www.jiejieyh.cn'。like搜索模式将不会返回相应的行    `regexp`是在列值内进行匹配，如果被匹配的文本在列值内出现，则相应的行会被返回。        在上面的例子中搜索模式为：`where user_name regexp 'jiejieyh'`，列值不变。        相应的行能被返回。    那么问题就来了：能不能让`regexp`达到与`like`相同的效果（匹配整个列值）？        答案是可以的，在正则表达式中^用于匹配字符串的开始，$符用于匹配字符串的结尾        那么就这么写吧：`where user_name regexp '^jiejieyh$'`

正则表达式检索数据比较慢，能不用就尽量少用。

比如下面的例子，我新建了一个表。表有一个字段，字段名为id。用的是主键索引。总共有14行数据，分别是1-14。对比一下下面的查询

图中上面的例子只是在找一条id为13的记录，进行了全表扫描。再来看看图中下面的例子，找到一条id为13的记录，只对test表扫面了一行。图上面的例子几乎没用到索引。

常用的Mysql函数

代码：select avg(column) as avg_number from tableName where id<100

avg函数用于求一个字段的平均值

代码：select count(*) as num_count from tableName

count函数用于确定表中行的数目或符合特定条件的行的数目

count函数有两种使用方式：

  1. 使用count(*)对表中行的数目进行计数，包含表列中的null值
  2. 使用count(column)对特定列中具有值得行进行统计，忽略null值

代码：select max(column) as max_number from tableName

max函数一般用于找出最大的数值或日期值，也可以用于文本列。max会函数忽略列值为null的行。

min函数与max函数唯一不同点是min用于找出最小的数值

代码：select sum(column) as sum_number from tableName
sum函数用于返回指定列值的总和。sum函数也可以用来合计计算。如：sum(column1*column2) as total from tableName。sum同样会忽略列值为null的行。

使用distinct指定列名，如：sum(distinct column) distinct 不能用于count(*)因此不允许使用count(distinct)。类似的，distinct必须使用列名，不能用于计算或表达式。

使用group by 分组

代码：select column,count(*) as count from tableName group by column order by count desc

group by用于根据某个列，对结果集进行分类。mysql会默认根据group by子句里的数据列对查询结果进行排序。如果分组列中有null值，则null将作为一个分组返回。列中有多个null值，将分为一组。group by 列出的每个列都必须是检索列或者有效的表达式，不能是聚集函数。group by 子句可以包含任意数目的列。

代码：select column,count(*) as num from tableName group by column with rollup

使用with rollup可以得到每个分组以及每个分组的汇总级别（针对每个分组的值）。with rollup 不能与order by 同时使用。

如果需要自定义排序，可以使用如下的方式：

select * from (select column,count(*) as num from tableName group by column with rollup) as data order by data.column

代码:select age,name,count(*) as num from tableName group by age having num > 1

having与where 不同点在where作用于行（记录），having作用与组。where关键字无法与合计函数一起使用

插入数据####

代码：insert into tableName(column1,column2) values(value1,value2),(value3,value4)

在insert操作中省略的列必须满足以下条件：

  1. 该列定义允许为null值 。
  2. 表定义该字段给出默认值。

在insert中，单条insert语句处理比多条insert语句处理更快。尤其是当需要更新表中的索引时。如果在当前表中，数据检索相对于数据插入更重要，可以在insert和into之间添加关键字LOW_PRIORITY,降低INSERT语句的优先级（在之后的mysql高级特性的博文中会详细介绍）。

插入检索出来的数据

代码：insert into tableName(column1,column2) select column1,column2 from tableName1

需要注意列之间的对应关系，即使表tableName1为空，也不会报错。因为操作仍是合法的。

更新数据

代码：update tableName set column1='jiejie',column2='yh' where uid=1

如果需要使用update更新多行，并且在更新这些行中出现一个错误，则整个update操作将被取消（错误发生前更新的所有列被恢复到它们原来的值）。如果想得到即使update在更新多行的时候发生错误，也要继续进行更新，可使用IGNORE关键字。这种情况很少遇到，但存在即合理吧。了解下就好。代码：update ignore tableName ...。

代码：update tableName set column=null where id=1

为了删除列中的某个值时（不是整条记录）可使用update将该列设置为null。

删除数据

代码：delete from tableName where id=1

delete有两个作用：

如果想删除整张表的内容，去除掉语句中where语句就好了。更好的替代方案是使用truncate table语句删除整张表中的所有行。truncate比delete在删除整张表中的内容时更快，因为truncate实际是删除原来的表并重新新建一张表，而不是逐行删除表中的数据。另一个区别是当表中有主键自增时，truncate能使主键从1开始递增。想想也是，人家是把你表干掉了，重新给你一个新的表。

总结

到此为止，mysql的基础知识就更新完毕了，涉及到mysql的增删改查。更高级的运用，比如说子查询、联结查询、事务、视图、存储过程、触发器等等，将会另外介绍。谢谢浏览，欢迎指正。如果你觉得该博文，对你有帮助，请用支付宝扫描二维码打赏。支持原创，感谢鼓励。

上一篇 下一篇

猜你喜欢

热点阅读