数据库基础语法

sql（Structured Query Language 结构化查询语言）

SQL语法

• use DataTableName; 命令用于选择数据库。
• set names utf8; 命令用于设置使用的字符集。
• SELECT * FROM Websites; 读取数据表的信息。
• 上面的表包含五条记录（每一条对应一个网站信息）和5个列（id、name、url、alexa 和country）。

重要的SQL命令

• SELECT - 从数据库中提取数据
• UPDATE - 更新数据库中的数据
• DELETE - 从数据库中删除数据
• INSERT INTO - 向数据库中插入新数据
• CREATE DATABASE - 创建新数据库
• ALTER DATABASE - 修改数据库
• CREATE TABLE - 创建新表
• ALTER TABLE - 变更（改变）数据库表
• DROP TABLE - 删除表
• CREATE INDEX - 创建索引（搜索键）
• DROP INDEX - 删除索引

1. select xx,xx from table 从表中查找xx，xx
2. select distinct xx ，xx from table 从表中查找xx，xx（不同的值，去掉重复值）
3. select xx，xx from table where xxxxxs（某个条件） 限定条件

4. and or 运算符、

   • 如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
   • 如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。
   • where country=‘CN’ and alexa>50(两个条件成立)
   • where country=‘CN’ or alexa>50 （一个符合就可以）
   • WHERE alexa > 15 AND (country=‘CN’ OR country=‘USA’);（双条件）

5. SELECT xxxx, xxx, ...
   FROM table_name
   ORDER BY xxxx, xxx, ... ASC|DESC;
   

   • asc 升序排序
   • desc 降序排序
   • order by 根据xxx进行排序，用，分开可以用多个来排序

6. insert into 向表中插入新纪录

   • INSERT INTO table_name (column1,column2,column3,...)
     VALUES (value1,value2,value3,...);
     
     INSERT INTO Websites (name, url, alexa, country)
     VALUES ('百度','https://www.baidu.com/','4','CN');
     空会自动补0/null
     table_name：需要插入新记录的表名。
     column1, column2, ...：需要插入的字段名。
     value1, value2, ...：需要插入的字段值。
     

7. updata

   • UPDATE Websites 
     SET alexa='5000', country='USA' 
     WHERE name='菜鸟教程';//where很重要
     把 "菜鸟教程" 的 alexa 排名更新为 5000，country 改为 USA。
     

8. delete

   • DELETE FROM table_name
     WHERE condition;
     从某表删除  where条件下的值
     

9. top、limit、rownum（**注意:**并非所有的数据库系统都支持 SELECT TOP 语句。 MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。）

   • select top num from table
   • select * from table limit num
   • select * from table where rownum<=num

10. like(指定模式)、

    • SELECT * FROM Websites
      WHERE name LIKE ‘G%’;//%通配符，以G开头的

    • 通配符	描述
      %	替代 0 个或多个字符
      _	替代一个字符
      [charlist]	字符列中的任何单一字符
      [^charlist] 或 [!charlist]	不在字符列中的任何单一字符

11. in（where 子句中规定多个值）

    • SELECT * FROM Websites
      WHERE name IN (‘Google’,‘菜鸟教程’);

12. between（取两个值之间的数）

    • not between WHERE alexa NOT BETWEEN 1 AND 20;
    • not in
      • WHERE (alexa BETWEEN 1 AND 20)
        AND country NOT IN (‘USA’, ‘IND’);
    • 文本 WHERE name BETWEEN ‘A’ AND ‘H’;

    SELECT column1, column2, ...
    FROM table_name
    WHERE column BETWEEN value1 AND value2;
    

    • column1, column2, …：要选择的字段名称，可以为多个字段。如果不指定字段名称，则会选择所有字段。
    • table_name：要查询的表名称。
    • column：要查询的字段名称。
    • value1：范围的起始值。
    • value2：范围的结束值。

高级sql

别名

xx，xx AS xx

链接 （join）

inner join 交集

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

• columns：要显示的列名。
• table1：表1的名称。
• table2：表2的名称。
• column_name：表中用于连接的列名。

left jion(右表没有也会记录) A全部 B没有对应null

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

right join 右连 B全部，A没有的对应null

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

full outer join 并集

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

union

默认不允许重复

union all（可重复）

合并两个或多个select每个select语句需要拥有相同数量的列，列也必须拥有相同数据类型

select into

从一个表复制数据，然后把数据插入另一个新表中

用于拷贝表结构和数据
CREATE TABLE 新表
AS
SELECT * FROM 旧表

SELECT * 或某一列
INTO newtable [IN externaldb]
FROM table1;

insert into select

从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。

else

• create database
• create table

constraints （约束）

• 添加 add 撤销 drop/alter

• column_name3 data_type(size) constraint_name,

NOT NULL

• 指示某列不能存储 NULL 值。
• 添加 modify xx int not null
• 删除 modify xx int null

UNIQUE

• 保证某列的每行必须有唯一的值。
• 添加 add
• 撤销 drop

PRIMARY KEY

• NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• 添加add 撤销 drop

FOREIGN KEY

• 保证一个表中的数据匹配另一个表中的值的参照完整性。
• 添加 add 撤销 drop

CHECK

• 保证列中的值符合指定的条件。check（a>0 and/or b=‘a’）
• 添加 add 撤销 drop

DEFAULT

• 规定没有给列赋值时的默认值。
• 每个添加方式不同
  • mysql alter
  • sql/ms add
  • oracle modify
• 撤销
  • mysql alter
  • sql alter

index

在不读取整表时，可快速查找数据，常用的加索引就好

• CREATE INDEX index_name
  ON table_name (column_name)

• CREATE UNIQUE INDEX index_name
  ON table_name (column_name)

• drop撤销

alter table

在已有表中添加或删除修改列v（语法与环境有关）

auto increment

在新记录插入表中时生成唯一的数字（语法与环境有关）

视图 views

可视化表格

Date 函数SQL Server 和 MySQL 中的 Date 函数 | 菜鸟教程 (runoob.com)

null 函数

• is null null值
• is not null 非null值
• null ≠ 0
• SQL ISNULL()、NVL()、IFNULL() 和 COALESCE() 函数 | 菜鸟教程 (runoob.com)

sql通用数据类型

sql函数

Aggregate 函数

从列中取得的值，返回一个单一的值

• AVG() - 返回平均值
• COUNT() - 返回这一列中符合count（内容）内容的行数
• FIRST() - 返回第一个记录的值（仅ms可用）
  • sql top1 order by asc
  • mysql order by asc limit1
  • oraclr order by asc where xx<=1
• LAST() - 返回最后一个记录的值（仅ms可用）
  • sql top1 order by decs
  • mysql order by desc limit1
  • oraclr order by desc where xx<=1
• MAX() - 返回指定列最大值
• MIN() - 返回指定列最小值
• SUM() - 返回指定列总和

group by

可结合聚合函数使用，根据一个或多个列对结果集进行分组

having

where 无法和聚合一起使用，HAVING 子句可以让我们筛选分组后的各组数据。

exists运算符

判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

SELECT Websites.name, Websites.url 
FROM Websites 
WHERE EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);

Scalar函数

基于输入的值，返回一个单一的值

• UCASE() - 将某个字段转换为大写

• LCASE() - 将某个字段转换为小写

• MID() - 从某个文本字段提取字符，MySql 中使用

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

  • 参数	描述
    column_name	必需。要提取字符的字段。
    start	必需。规定开始位置（起始值是 1）。
    length	可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。

• SubString(字段，1，end) - 从某个文本字段提取字符

• LEN() - 返回某个文本字段的长度

  • mysql
    SELECT LENGTH(column_name) FROM table_name;
    

  • sql
    SELECT LEN(column_name) FROM table_name;
    

• ROUND() - 对某个数值字段进行指定小数位数的四舍五入

  • mysql> SELECT ROUND(1.298, 1);
    -> 1.3
  • mysql> SELECT ROUND(1.298, 0);
    -> 1

• NOW() - 返回当前的系统日期和时间

• FORMAT() - 格式化某个字段的显示方式