sql基本语法学习笔记

SQL DML和 DDL

• DML:数据库操作语言 
  • select  - 数据库查询数据
    • select 列名 from 表名
  • update - 更新数据库数据 
    • update 表名  set 列名 = 新值 where 列名 =  某值
  • delete - 数据库中删除数据
    • delete from 表名 where 列名 =  值
  • insert into - 数据库插入数据
    • insert into 表名 values （值1，值2 ，。。。。）
    •  指定插入数据列 ： insert into table_name  (列1 ， 列2 ，...）values （值1，值2,...）
• DDL：数据库定义语言　　
  • create database - 创建数据库 
  • alter database - 修改数据库
  • create table - 创建表
  • drop table - 输出表
  • create index 创建索引
  • drop index - 删除索引

　　SQL关键字

• 关键字：where ，and & or  ， like  ， in ，between...and... 等
  • where子句  有条件查询 可使用运算符表示条件的范围
  • order by  按照指定列队结果集进行排序
    
    • 升序    例：select name from student  order by age ASC  或  select name from student  order by age  ASC 为升序关键字，可省略
    • 降序    例 ：select name from student  order by age desc  降序排列中 desc  不可省略
  • and & or 
    
    • 多条件查询 可同 where 联合使用
    • and  需要双边条件同时满足才能进行查询
    • or 只需要一遍条件满足即可
  • distinct  去重关键字  语法:  select distinct 列名 from 表名
  • like 用于where 子句中搜索指定列中的指定模式   select *  from 表 where  条件 like  某值
  • in 该操作符允许 where 子句中规定多个值，用法同 like ， in （value1 ， value2 ，...）
  • between... and... 两值之间的数据范围 语法：select column_name(s) from table_name  where column_name between value1 and value2 

　　SQL通配符

• % 替代一个或者多个字符
  • select * from table_name where City_name like 'N%'
• _ 仅代替一个字符

• • select  * from table_name where City_name  like 'N_'  或例  select  * from table_name where City_name  like '_N_'  
• [!charlist]   /  [^charlist]不在字符列中的任何一个单一字符
  • select  * from table_name where City_name  like '[^ABC]%'   或  select  * from table_name where City_name  like '[！ABC]%'  不已ABC任意一个字母开头的城市名
• [charlist]  字符列中的任何一个单一字符
  • select  * from table_name where City_name  like '[ABC]%'   ABC任意一个字母开头的城市名  

　　SQL键、连表、索引

• sql中的键
  • primary key 主键约束，必须包含唯一值且主键中不能包含null值 ，每一个表中都应该有一个主键且只能有一个主键 

    #Mysql中写法
    CREATE TABLE Persons
    (
    Id_P int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    PRIMARY KEY (Id_P)
    )
    #sql server / oracle 中写法
    CREATE TABLE Persons
    (
    Id_P int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
    )

    • 如果需要命名primary key约束，以及多个列定义 primary key约束

      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)

  • foreign key 外键约束
    • 外键约束用于预防破坏表之间的连接动作，也可防止非法数据插入外键列，因为它必须是指向那个表中的值之一

      #Mysql
      CREATE TABLE Orders
      (
      Id_O int NOT NULL,
      OrderNo int NOT NULL,
      Id_P int,
      PRIMARY KEY (Id_O),
      FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
      )
      #SQL Server / Oracle 
      CREATE TABLE Orders
      (
      Id_O int NOT NULL PRIMARY KEY,
      OrderNo int NOT NULL,
      Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
      )

       

    • 若需要命名 外键约束，以及为多个列定义外键约束，则可以

      CREATE TABLE Orders
      (
      Id_O int NOT NULL,
      OrderNo int NOT NULL,
      Id_P int,
      PRIMARY KEY (Id_O),
      CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
      REFERENCES Persons(Id_P)
      )

      表已存在，需要添加外键时

      ALTER TABLE Orders
      ADD FOREIGN KEY (Id_P)
      REFERENCES Persons(Id_P)

      或者

      ALTER TABLE Orders
      ADD CONSTRAINT fk_PerOrders
      FOREIGN KEY (Id_P)
      REFERENCES Persons(Id_P)

       

  • 删除主键或者外键

    #外键删除
    #Mysql
    ALTER TABLE Orders
    DROP FOREIGN KEY fk_PerOrders
    #SQL Server / Oracle
    ALTER TABLE Orders
    DROP CONSTRAINT fk_PerOrders
    #主键删除 Mysql
    ALTER TABLE Persons
    DROP PRIMARY KEY
    #SQL Server / Oracle
    ALTER TABLE Persons
    DROP CONSTRAINT pk_PersonID

     

• sql连表查询
  • join 用于两表或者多表查询，通过表中的唯一主键把多表中的数据交叉捆绑在一起

    SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
    FROM Persons, Orders
    WHERE Persons.Id_P = Orders.Id_P 

     

  • left join 左连表，某些数据库中，left join 又称为 left outer join ，从左表中返回所有行（以左表为主返回左表中的所有行）

    SELECT column_name(s)
    FROM table_name1
    LEFT JOIN table_name2 
    ON table_name1.column_name=table_name2.column_name

     

  • right join 右连表，left join 又称为 left outer join ，从右表中返回所有行（以右表为主返回右表所有行）

    SELECT column_name(s)
    FROM table_name1
    RIGHT JOIN table_name2 
    ON table_name1.column_name=table_name2.column_name

     

  • full join 只要表中存在的就会返回 full outer join 

    SELECT column_name(s)
    FROM table_name1
    FULL JOIN table_name2 
    ON table_name1.column_name=table_name2.column_name

     

• sql索引

数据库中的索引是为了更快速更有效的查询，更新一个包含索引的表需要比一个没有索引的表画的时间更多，这是由于索引也要被更新，通常只需要在常常被搜索的列（以及表）上面创建索引

#column_name 为索引列
CREATE INDEX index_name
ON table_name (column_name)
#若 想要降序索引某列中值可以直接在后面加上DESC
CREATE INDEX PersonIndex
ON Person (LastName DESC) 

SQL中常用函数

• avg（）求平均值
• count（） 统计行
• max（）最大值
• sum（）列求和
• first（）返回列的第一行
• group by 用于结合合计函数，根据单列或者多列结果集进行分组

  SELECT column_name, aggregate_function(column_name)
  FROM table_name
  WHERE column_name operator value
  GROUP BY column_name

   

• mid（）用于从文本字段中提取字符

  SELECT MID(column_name,start[,length]) FROM table_name

   

• having 子句 

  SELECT column_name, aggregate_function(column_name)
  FROM table_name
  WHERE column_name operator value
  GROUP BY column_name
  HAVING aggregate_function(column_name) operator value

   

• now（）返回当前日期和时间（SQL Server 数据库中则使用 getedate（）实现获取当前的时间和日期）
• format（） 对字段显示进行格式化 

  SELECT FORMAT(column_name,format) FROM table_name