SQL基础

2019-02-13

1 对数据库操作

  • 查看当前数据库服务器上的数据库:show databases
  • 创建数据库:create database + 数据库名
  • 删除数据库:drop database + 数据库名
  • 使用数据库:use + 数据库名
  • 查看数据库中所有表:show tables

2 对表操作

  • 创建表
      create table 表名 (
      字段名称 字段类型 字段特征(是否为null,默认值,标识列,主键,唯一键,外键,check约束),
      字段名称 字段类型 字段特征(是否为null,默认值,标识列,主键,唯一键,外键,check约束)
      )
    
    • 主键约束:primary key
    • 外键约束:foreign key
    • 自增:auto_increment
    • 默认值约束:default
    • 唯一键约束:unique
    • check约束:check(用于限制列中的值的范围,MySQL不支持
  • 删除表:drop table + 表名
  • 查看表结构:desc + 表名
  • 查询
      select * from 表名 where ……
    
    • select语句中子句的顺序:select,from,where,group by,having,order by,limit
    • group by:除了聚集函数,select中的每列必须出现在group by子句中
    • order by:排序,asc:升序desc:降序,默认按升序
  • 插入:insert into 表名(‘字段名称’,‘字段名称’) values(value1,value2)
  • 删除:delete from 表名 where ……
  • 修改:update 表名 set 字段1=值,字段2=值 …… where ……
  • 删除表字段:alter table 表名 drop 字段 (注:如果数据表中只剩余一个字段则无法使用DROP来删除字段)
  • 添加表字段:alter table 表名 add 字段 字段类型
  • 修改表字段:alter table 表名 change 修改的字段名 修改后的字段名 修改后的字段类型
  • 模糊查询:like 子句(‘%’:匹配任何字符出现的任意次数,‘_’ : 匹配单个字符)
  • 聚集函数:avg():平均;count():行数;max():最大;min():最小;sum():求和

3 约束(constraint)详解

参见MySQL——约束(constraint)详解

  • 非空约束(not null)
  • 唯一性约束(unique)
  • 主键约束(primary key) PK
  • 外键约束(foreign key) FK
  • 检查约束(目前MySQL不支持、Oracle支持)
-- 创建
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
)
ALTER TABLE Persons ADD PRIMARY KEY (Id_P)
ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

-- 删除
ALTER TABLE Persons DROP PRIMARY KEY

4 内连接、外连接和半连接

参见:

4.1 内连接

显示两个表中有联系的所有数据;

  • 隐式内连接select * from a,b where a.id=b.id
  • 显示内连接select * from a inner join b on a.id=b.id

4.2 外连接

外联接可以是左向外联接、右向外联接或完整外部联接

  • 左链接:以左表为参照,显示所有数据;
    select * from a left join b on a.id=b.id
  • 右链接:以右表为参照显示数据;
    select * from a right join b on a.id=b.id
  • 全连接:左右表的数据都返回(MySQL不支持)
    select * from a full join b on a.id=b.id

4.3 半连接

半连接通常使用IN 或 EXISTS 作为连接条件。

5 Limit子句

参见深入分析Mysql中limit的用法
limit子句可以被用于强制select语句返回指定的记录数。limit接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)。

**SELECT * FROM table LIMIT [offset,] rows rows OFFSET offset**
select * from book limit 5,10;  /*取得记录行6-15的记录*/
select * from book limit 5,-1;  /*取得记录行6-末尾的记录*/
select * from book limit 5;  /*取得记录行前5个的记录,等价于limit 0,5*/
select * from book limit 5,1;  /*取得第5行的后1行的记录*/

性能分析:取90000条后100条记录。

-- 方法1
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
Select ID From cyclopedia Order By ID limit 90001
) As tmp
) limit 100;

-- 方法2
Select * From cyclopedia Where ID>=(
Select ID From cyclopedia limit 90000,1
)limit 100;

第1句是先取了前90001条记录,取其中最大一个ID值作为起始标识,然后利用它可以快速定位下100条记录。
第2句则是仅仅取90000条记录后1条,然后取ID值作起始标识定位下100条记录
第1句执行结果.100 rows in set (0.23) sec
第2句执行结果.100 rows in set (0.19) sec
明显第2种执行效率更高

总结

  • offset比较小的时候,直接使用limit较优。(SQL 1更优)

      -- SQL 1
      select * from yanxue8_visit limit 10,10;
      -- SQL 2
      Select * From yanxue8_visit Where vid >=(
      Select vid From yanxue8_visit Order By vid limit 10,1
      ) limit 10;
    
  • offset大的时候,使用offset更优。(SQL 2更优)

      -- SQL 1
      select * from yanxue8_visit limit 10000,10;
      -- SQL 2
      Select * From yanxue8_visit Where vid >=(
      Select vid From yanxue8_visit Order By vid limit 10000,1
      ) limit 10;
    

6 总结

6.1 mysql中insert into和replace into以及insert ignore用法区别

  • insert into表示插入数据数据库会检查主键,如果出现重复会报错;
  • replace into表示插入替换数据,需求表中有PrimaryKey,或者unique索引,如果数据库已经存在数据,则用新数据替换,如果没有数据效果则和insert into一样;
  • insert ignore表示如果表中已经存在相同的记录(根据主键primary或者唯一索引unique判断),则忽略当前新数据

参考文献

Interview-Notebook——SQL