1.SQL命令
SELECT
- 从数据库中提取数据UPDATE
- 更新数据库中的数据DELETE
- 从数据库中删除数据INSERT INTO
- 将新数据插入数据库CREATE DATABASE
- 创建一个新数据库ALTER DATABASE
- 修改数据库CREATE TABLE
- 创建一个新表ALTER TABLE
- 修改表格DROP TABLE
- 删除表CREATE INDEX
- 创建索引(搜索键)DROP INDEX
- 删除索引
2.SELECT FROM
SELECT column1, column2, ...
FROM table_name;
选择所有列就是SELECT *
3.SELECT DISTINCT
SELECT DISTINCT column1, column2, ...
FROM table_name;
从表中的列中选择所有不同的值。Country
Customers
eg.
select distinct Country FROM Customers;
distinct 可以去重
通过在名为 的函数中使用关键字,我们可以返回不同国家/地区的数量。DISTINCT
COUNT
SELECT COUNT(DISTINCT Country) FROM Customers;
4.WHERE
SELECT column1, column2, ...
FROM table_name
WHERE condition;
SQL 要求文本值两边使用单引号(大多数数据库系统会 也允许双引号)。
但是,数值字段不应用引号括起来
子句中可以使用以下运算符:WHERE
Operator | Description | Example |
---|---|---|
= | Equal | Try it |
> | Greater than | Try it |
< | Less than | Try it |
>= | Greater than or equal | Try it |
<= | Less than or equal | Try it |
<> | Not equal. Note: In some versions of SQL this operator may be written as != | Try it |
BETWEEN | Between a certain range | Try it |
LIKE | Search for a pattern | Try it |
IN | To specify multiple possible values for a column |
5.ORDER BY
按升序或者降序排序(默认升序)
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
降序用DESC
eg.按最高价格到最低价格对产品进行排序:
SELECT * FROM Products
ORDER BY Price DESC;
倒序DESC 正序ASC
6.AND 运算符
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
AND OR
如果所有条件都为 TRUE,则运算符将显示一条记录。AND
如果任何条件为 TRUE,运算符将显示一条记录。OR
7.OR 运算符
该子句可以包含一个或多个运算符。WHERE
OR
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
8.NOT 运算符
运算符与其他运算符给出相反的结果,也称为负结果。WHERE NOT
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
9.INSERT INTO
可以通过两种方式编写语句:INSERT INTO
(1). 指定要插入的列名和值:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
(2).如果要为表的所有列添加值,则不需要 在 SQL 查询中指定列名。但是,请确保 values 的顺序与表中的列的顺序相同。在这里,语法 如下所示: INSERT INTO
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
eg.
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
插入多行后要加逗号
10.NULL 值
具有 NULL 值的字段是没有值的字段。
如果表中的字段是可选的,则可以插入新记录或在不向此字段添加值的情况下更新记录。然后,该字段将是 使用 NULL 值保存。
NULL 值不同于零值或字段 包含空格。具有 NULL 值的字段是留空的字段
IS NULL 语
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
IS NOT NULL 语法
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
11.UPDATE 更新
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
12.DELETE 删除
DELETE FROM table_name WHERE condition;
可以在不删除表的情况下删除表中的所有行。这 表示表结构、属性和索引将保持不变
DELETE FROM Customers;
要完全删除该表,请使用以下语句:DROP TABLE
DROP TABLE Customers;