SQL语句

  2018-10-11 


SQL 分为数据操作语言 (DML) 和 数据定义语言 (DDL)。

DML是用于执行查询的语法:select,update,delete,insert into

DDL部分使我们有能力创建或删除表格,定义索引(键),规定表之间的链接,以及施加表间的约束:create database,alter database,create table,alter table,drop table,create index,drop index

*是通配符

sql的字符串是varchar(size),用字符串用单引号表示

  1. select 列名 from 表名

    列出列

    列名可为多个列,返回列数据

    检索列名,行名无需单引号

    select distinct 列名 from 表名:只列出列中不重复元素

  2. select 列名 from 表名 where 列 运算符 值

  3. select 列名 from 表名 where 列 运算符 值 and 列 运算符 值

    select 列名 from 表名 where 列 运算符 值 or 列 运算符 值

  4. select 列名 from 表名 order by 列名

    排序

    逆序:order by 列名 DESC

    null 值默认为最小值

  5. insert into 表名 values (值1,值2,…)

    insert into 表名(列1,列2,…) values (值1,值2,…)

    不能插null,如果不插就是null

  6. update 表名 set 列名=新值 where 列名 运算符 值

    修改数据:通过where定位行,再通过set修改行中某列属性

  7. delete from 表名 where 列名 运算符 值

  8. where 列名 运算符 值:定位行

    一般select,update,delete都是主定位列,所以需要where辅助

  9. 运算符

    =,<>/!=,>,<,>=,<=,BETWEEN(在某个范围内),LIKE(搜索某种模式,即匹配)

    between 0 and 150

    like '%CG',%是通配符

    in:where字句中规定多个值,如where name in (value1,value2)

    is nullis not null

  10. top:规定返回记录的数目

    在mysql中等价于select from limit 数目(省略select和from的值)

    select top 百分比 percent * from 返回一定百分比

  11. 通配符

    %:替代一个或多个字符

    _:替代一个字符

    [charlist]:字符列中的任何单一字符

    charlist或[!charlist]:不在字符列中的单一字符

  12. select 列名 from 表名 as 别名

    指定别名

  13. table.列名:点语法

    点语法,可以助力多表查询

    于是就可以配合出多表查询比如:select * from table1,table2 where table1.NAME=table2.NAME;

    我们也可以用专业的join,见下

  14. join:多表查询

    从两个或更多的表中获取结果

    select * from table1 join table2 on tb1.name=tb2.name;

    同时有inner join,left join,right join,full join

    join=inner join是返回两张表中完全匹配的结果

    left join,right join返回左/右表所有行

    full join是返回左表和右表的所有行

    join条件语句使用on而不是where!

  15. union:合并查询结果

    写在两个select from中间(是一个语句!)

    eg:select * from tb1 **union** select * from tb2;

    union选取不同的值,union all可以选取相同的值

  16. insert select into:从一个表中选取数据插入另一个表,复制表,常用来备份

    insert into table_backup select * from table1;

    from后面可以加上①where子句,②还可以配合join连接两个表一起复制

    要求目标表存在

  17. create :创建表/table

    eg:create database myDB

    create table table1

    {

    NAME varchar(255),
    MALE varchar(10),
    HEIGHT int(200)

    }

  18. SQL约束(constraints)

    ①添加约束:

    1. 创建表的时候在变量后面规定约束,比如在17中第一个列定义改为:NAME varchar(255) primary key ,

    2. 在表定义中加单独行如unique(MALE)

    3. alter table语句增加约束:alter table table1 add unique(MALE)

    ②约束类型

    1. not null:非null

    2. unique:保证该数据在该列唯一

    3. primary key【主键】,唯一标识数据库表中的每条记录,一个表只能有一个主键,不能为null

      主键最好是完全业务无关的字段,比如NAME等就不太好当主键,一般把主键叫做id

    4. foreign key:【外键】一个表中的 foreign key 指向另一个表中的 primary key

      外键约束会降低数据库的性能

      添加方法:一样的三种方法,不过后面语句特殊要指定references: foreign key (fk) references table2(NAME)

      注意:必须保证该表外键和被连接表的主键是完全一样的类型定义!

      当两张表存在关联字段的时候,利用外键可以保证主表和从表的一致性和完整性,我们可以定义主从表on update,on delete关联规则

      NULL、RESTRICT、NO ACTION(默认)
      删除:从表记录不存在时,主表才可以删除。
      更新:从表记录不存在时,主表才可以更新。

      CASCADE
      删除:删除主表时自动删除从表。
      更新:更新主表时自动更新从表。

      SET NULL
      删除:删除主表时自动更新从表值为NULL。
      更新:更新主表时自动更新从表值为NULL。
      这些规则的使用方法:举例

      alter table table1 add foreign key (fk) references table2(NAME) on update cascade

      也可以在创建的时候定义,跟在定义句子后面即可

    5. check:限制列中的值的范围

      举例:SCORE int check(SCORE>0)

    6. default:默认值

    ③撤销约束:

    都是先通过alter table table1 来指定哪个表,再加上不同的drop操作

    删除外键直接删掉外键列

  19. Index—索引

    create index 索引名 on 表名(列名)

    索引值允许重复,默认升序,如果要建立唯一索引(索引值不可相同):create unique index 索引名 on 表名(列名)

    可以索引多列:create index 索引名 on 表名(列1,列2,…)

    可以降序索引:create index 索引名 on 表名(列名 DESC)

    关于索引:

    在不读取整个表的情况下,索引使数据库应用程序可以更快地查找数据。索引可以高效地查询数据,是一种速度优化!用户无法看到索引,它们只是被用来加速搜索,加速查询。由于更新一个包含索引的表也需要更新索引,所以通常在经常被搜索的列和表上建立索引

  20. drop:删除索引、表、数据库、视图

    drop index 索引名 on 表名

    drop table 表名

    drop database 数据库名

  21. alter:为【表】中【添加,删除】【列、约束】

    可以做到很多事情

    alter table 表名 add

    alter table 表名 drop

    见前面

  22. auto-increment

    属性,和约束一样的添加删除方法。每有新记录插入的时候主动创建字段,如果已创建就自动+1

  23. show

    查看数据库相关信息

  24. view 视图

    可以从表中选择一些数据建立视图,视图中的数据是虚拟的,还是属于原来的表

    创建视图:create view view_tb1 select from where

    更新视图(略)

  25. mysql数据类型:

    常用的

    Text类型:

    char(size):定长字符串,最长255

    varchar(size):变成字符串,最长255,大于则自动转为TEXT

    TEXT:最大长度65535

    MEDIUMTEXT:最大长度16777215

    LONGTEXT:最大长度4,294,967,295

    还有诸如Number,Date中的一些类型

SQL Function

select function(列) from 表

mysql的常用函数:avg(),max(),min(),sum(),lcase(),ucase(),count(),first(),last(),len(),round(),format()等等

mid(列名,int a,int b),可以从列中提取该列元素中a~b区间的字符

主键与索引浅层区别

没有主键会创建默认主键

主键索引主键就是一种索引,此即主键索引。使用:设置primary key

非主键索引:非主键索引是要先链接到主键索引上的,只要建立的不是主键就是非主键索引。使用:create index on(由于默认建立主键,所以非主键索引可以不用担心没有链接到主键索引)

非主键索引可以是不唯一的,但主键索引一定是唯一的

一些注意点

  1. SQL中where 和 on的区别

    在join中,条件必须先用on,但后面的子条件可以接在on后面,也可以用where

    对于inner join没有区别

    但在left join时,on后面的条件只对右表有效(right join就只对左表有效)

    而where是对两个表都有效

    //这两个结果是不一样的
    select * from person p left join account a on p.id=a.id and p.id!=4 and a.id!=4;

    select * from person p left join account a on p.id=a.id where p.id!=4 and a.id!=4;

    https://blog.csdn.net/u013468917/article/details/61933994

  2. sql having(前面出现的)和where(可以前面未出现)

    where筛选的是数据库表里面本来就有的字段,而having筛选的字段是从前筛选的字段筛选的。

    https://www.cnblogs.com/ljf-Sky/p/9024683.html

  3. group by和order by 的使用


且听风吟