关于sql语句的一些梳理学习

distinct关键字   用于返回 不重复的值   例如表中有两个张三     那么distinct name 则只会返回一个张三

 

order by 关键字  以xx的规则来排序     asc 则为升序   desc为降序

例: select * from xxx  order by xx       就会以xx为规则来排   默认降序排列

order by 可以多列  先按照第一个column name排序，再按照第二个column name排序

 

添加  insert into    在值与列完全匹配的时候可以不写列名   例   insert into xxx values (x,xx,xxx,xxx);

但是要在指定列插入的时候就需要标明列名  与值对应

 

修改    update  (table name) set (columName1)='xxx',(columName2)='xx'  where ....

在执行没有where 的修改操作时  一定要慎重再慎重     会将所对应列的值全部修改  一定要注意是否符合场景.  

 

删除   DELETE FROM table_name WHERE some_column=some_value;   从指定列删除值

 

返回指定条数据 可以使用limit num 来实现    可以提前用orderby 或者 group by 来排序

 

模糊查询   select * from columName WHERE xxx like '%xx%';   也可加NOT关键字  来筛选出不符合该表达式的结果集

除了%号通配符外  模糊查询还支持另一种表达式  _a_  表示含有a的三位的结果 与%类似 但是会限制位数

值得注意的是  当指定列上有索引时    在xx前加%会使索引失效  依然进行全表查询   但是在后面加%索引依然有效

当然 在实际应用中应避免 select* 的存在   可以增加查询效率

 

in 关键字  select * from tableName where columName in (a,b,c);

 该句可翻译成 select * from tableName where columName=a or columName=b or columName=c;

 

between and 关键字  SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

查找出值在value1 和value2 中间值的列  这个范围的大小包括value1 的值和value2的值.(mysql是两个都包含,亲测   别的库还没有试过  有试过的大佬可以私我)

 

join 

inner join       SELECT Websites.id, Websites.name, access_log.count, access_log.date FROM Websites INNER JOIN access_log
ON Websites.id=access_log.site_id;

将website 和access 两个表在 websites.id 和aceess_log.site_id处连接.

 

• INNER JOIN：如果表中有至少一个匹配，则返回行(内链接)
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行(左连接)
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行(右连接)
• FULL JOIN：只要其中一个表中存在匹配，则返回行(全连接)

 

union 关键字   用于合并两个查询的结果集    例 SELECT column_name(s) FROM table1 UNION  SELECT column_name(s) FROM table2;     会将两个查询结果进行合并  

注意:UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值

使用UNION命令时需要注意，只能在最后使用一个ORDER BY命令，是将两个查询结果合在一起之后，再进行排序！绝对不能写两个ORDER BY命令。

另外，在使用ORDER BY排序时，注意两个结果的别名保持一致，使用别名排序很方便。当然也可以使用列数。

 

insert into select     可将select查询出的语句放入新表中     

INSERT INTO table2 SELECT * FROM table1;

 

关于建表的DDL就不多复习了    实际开发中一般会有DB或者架构组来创建设计   我们暂时只负责对表的增删改查就好   到时用到再进行系统的回顾,下面是一些数据库使用过程中的经验,包括约束 索引 一些常用的函数 以及在数据库使用过程中遇到过的问题.

 

约束   创建约束是为了保证数据库中值的规范性   约束可以在创建表的时候就进行添加(create table) 也可以在创建后再进行修改(alter table )   例如  ALTER TABLE Persons ADD UNIQUE (P_Id)   撤销约束 ALTER TABLE Persons DROP INDEX uc_PersonID

常见的约束有 主键 唯一 非空 default 等  其中主键约束就是非空加唯一   并且一个表中只能有一个primary key  

最容易遗忘的点是 外键约束 

check 约束 可以自定义规则

 

索引   索引是一种无法在表结构中实际看到的东西  但是他确实是存在的 而且会大幅度的加快查询速度.  之前在写毕业论文的时候看到过  当mySql数据库中的值超过300w条的时候   其性能会远不如oracle  但是现阶段企业级开发中 mysql还是占了很重要的地位. 我在公司的表中用过一些没有优化的sql语句  有的甚至可以跑一分钟还出不来结果  这时候就能体现出索引的优势了. 

另外 更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

索引是基于数据结构中的B+ tree 来实现的    也分主键索引   唯一索引  非空索引  并行索引等    具体的不再赘述  度娘会予以解释

创建索引     CREATE INDEX index_name ON table_name (column_name)    

删除索引     ALTER TABLE table_name DROP INDEX index_name  ( 不同数据库的语法略有不同  这里以mySql为例)

 

auto_increment  我们在建表的时候  常常会把ID作为自增的主键来保存   这时候  我们就要在建表时设定其为auto_increament 

我们在向一个 以自增列为主键的表插入数据时   可以不对该列进行赋值 数据库会自动为其分配    同时 我们也可以为其任意赋值.

 

Date: mySql中 存放日期类型的格式有四种   

• DATE - 格式：YYYY-MM-DD
• DATETIME - 格式：YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
• YEAR - 格式：YYYY 或 YY

timestamp 和datetime 的对比    

datetime 以''YYYY-MM-DD HH:MM:SS''格式检索和显示DATETIME值。支持的范围为''1000-01-01 00:00:00''到''9999-12-31 23:59:59''TIMESTAMP值不能早于1970或晚于2037

TIMESTAMP      1.4个字节储存(Time stamp value is stored in 4 bytes)  2.值以UTC格式保存( it stores the number of milliseconds)  3.时区转化 ，存储时对当前的时区进行转换，检索时再转换回当前的时区。

datetime    1.8个字节储存(8 bytes storage)  2.实际格式储存(Just stores what you have stored and retrieves the same thing which you have stored.)    3.与时区无关(It has nothing to deal with the TIMEZONE and Conversion.)    基本原样输入输出

另外  如果查询时没有指定 时间部分  而表中存储的带有时间部分的话  那么会按照默认 00:00:00来查询   这时将很难获得想要的结果    例如  SELECT * from CR_APPLY_TAB  where CREAT_TIME ='2018-09-04' ;    那么如果没有九月四日 00:00:00  则查询结果集为空   针对这种情况  在实际的开发中  我们可以善于运用> <号  那么如果我们查询  CREAT_TIME>'2018-09-04'; 那么则会返回2018-09-04 00:00:00到查询时间的结果  至少包含了我们想要的值  如果想要更加精确   可以再添加限定条件.

 

经验:查询表中某一字段为null 值的时候   只能用is null 或者is not null 来作为限定条件   如果使用 xxx=null的话 这样的限定条件是无效的  还是会查询全表    另外要注意  null值是无法进行运算和比较的    这也是为什么不能使用xxx=null的一个原因   < > 以及各种运算都不可以.    

常用的关于null 的函数  ISNULL()  判断是否为null 值

                                     IF NULL()    该函数中传的是两个参数  前一个是列名  标明要进行运算的列  后一个值是如果这个列的该值为null  则以什么值进行计算    类似于COALESCE() 函数

 

mysql中常用的函数:   avg()  返回平均值   SELECT AVG(column_name) FROM table_name

                                   count() 对指定列进行计数  SELECT COUNT(column_name) FROM table_name;

                                   max()    min() sum()     对指定列求最大 最小  以及 相加的和

                                group by   分组    SELECT site_id, SUM(access_log.count) AS num FROM access_log GROUP BY site_id;

                                 因为WHERE 无法和聚合函数一起使用  所以sql中添加了 Having 关键字  

                                 ucase()  lcase()    将查询的指定列的值进行大小写的转换    length() 返回指定的长度

                                 round() 四舍五入取整    floor()  向下取整    ceil 向上取整     now()   返回系统当前时间   format() 格式化时间格式

 

公司数据组的大佬前些天刚教了一个函数   group concat     可以把结果集结串列出来 方便对结果集进行操作