数据库知识小结

       文章用于个人学习，是对自己学习的简单小结，以便以后返回来学习。

1. 数据库基本操作

cd Users\mysql\bin
// cd到 mysql 数据库的 bin 文件中

mysql -hlocalhost -uroot -p
// 打开数据库
{
    mysql    应用
    -h       后跟的是主机域名
    -u       后跟的是 mysql 账户
    -p       后跟的是 mysql 密码
}

\q 
// 退出数据库

create database text default character set utf8 collate utf8_general_cli;
// 创建名为 text 的数据库，编码格式为 utf8，数据校对规则为 utf8_bin

show database;
// 查看数据库
 
use text;
// 选择 text 数据库

describe Student;
// 显示 Student 表的结构

create database text;
// 创建名为 text 的数据库

drop database text;
// 删除名为 text 的数据库

delete from Student;
// 清空 Student 表中的记录

set names utf8;
// 设置编码

update mysql.user set password=PASSWORD('123456') where User='root';
flush privileges;
// 修改 root 中的用户密码为 123456

2. 示例

create table `Student`
(
  `Snumber` int,
  `Sname` varchar(32),
  `Sage` int,
  `Ssex` varchar(8)
);
// 创建表 Student 

create table `Course`
(
  `Cnumber` int,
  `Cname` varchar(32),
  `Tnumber` int
);
// 创建表 Course 

create table `Score`
(
  `Snumber` int,
  `Cnumber` int,
  `Score` int
);
// 创建表 Score 

create table `Teacher`
(
  `Tnumber` int,
  `Tname` varchar(16)
);
// 创建表 Teacher 

desc Student;
// 查看表 Student

insert into Student select 1,'刘一',18,'男' union all
select 2,'钱二',19,'女' union all
select 3,'张三',17,'男' union all
select 4,'李四',18,'女' union all
select 5,'王五',17,'男' union all
select 6,'赵六',19,'女';
// Student 中插入 6 条数据

insert into Teacher select 1,'叶平' union all
select 2,'贺高' union all
select 3,'杨艳' union all
select 4,'周磊' union all
select 5,'杨哈哈';
// Teacher 中插入 5 条数据

insert into Score select 1,1,56 union all
select 1,2,78 union all
select 1,3,67 union all
select 1,4,58 union all
select 2,1,79 union all
select 2,2,81 union all
select 2,3,92 union all
select 2,4,68 union all
select 3,1,91 union all
select 3,2,47 union all
select 3,3,88 union all
select 3,4,56 union all
select 4,2,88 union all
select 4,3,90 union all
select 4,4,93 union all
select 5,1,46 union all
select 5,3,78 union all
select 5,4,53 union all
select 6,1,67 union all
select 6,2,67 union all
select 6,4,67;
// Score 中插入 21 条数据

select * from Student;
// 查看 Student 表

3. SQL 语法

一 SELECT 查询

--------------------------------------------------------------------------------

SELECT FROM
{
   SELECT column1, column2, ...
   FROM table_name;
}

SELECT * FROM table_name
提取 table_name 中所有数据

SELECT CustomerName, City FORM Customers
从 Customers 表中提取 CustomerName 和 City 列数据

SELECT Country FROM Customers
从 Customers 表中提取 Country 列数据

-------------------------------------------------------------------------------

SELECT DISTINCT FROM
{
   SELECT DISTINCT column1, column2, ...
   FROM table_name;
}

SELECT DISTINCT Company FROM Orders
从 Orders 表中提取 Company 列唯一不同的值
{
   表
     Company             Orderin
     IBM                 121
     W3C                 4564
     Apple               565
     W3C                 45

   SQL语句执行结果为
     Company
     IBM
     W3C
     Apple
}

--------------------------------------------------------------------------------

SELECT FROM WHERE
{
   SELECT column1, column2, ...
   FROM table_name
   WHERE condition;
}

SELECT * FROM Customers WHERE Country='Mexico';
从表 Customers 中提取所有 Country 属性为 Mexico 的元素的所有属性

SELECT * FROM Customers WHERE CustomerID=1;
从表 Customers 中提取所有 CustomerID 属性为 1 的元素的所有属性

--------------------------------------------------------------------------------

AND 与
{
   SELECT column1, column2, ...
   FROM table_name
   WHERE condition1 AND condition2 AND condition3 ...;
}

SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin';
从表 Customers 中提取所有 Country 属性为 Germany 同时 City 属性为 Berlin 的元素的所有属性

--------------------------------------------------------------------------------

OR 或
{
   SELECT column1, column2, ...
   FROM table_name
   WHERE condition1 OR condition2 OR condition3 ...;
}

SELECT * FROM Customers WHERE City='Berlin' OR City='Munchen';
从表 Customers 中提取 City 属性为 Berlin 或 Munchen 的元素的所有属性

--------------------------------------------------------------------------------

NOT 非
{
   SELECT column1, column2, ...
   FROM table_name
   WHERE NOT condition;
}

SELECT * FROM Customers WHERE NOT Country='Germany';
从表 Customers 中提取所有 Country 属性不是 Germany 的元素的所有属性

--------------------------------------------------------------------------------

AND & OR
SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='Munchen');
从表 Customers 表中提取所有 Country 属性为 Germany 同时 City 属性为 Berlin 或 Munchen 的元素的所有属性

--------------------------------------------------------------------------------

结合 AND，OR 和 NOT
SELECT * FROM  Customers WHERE NOT Country='Germany' AND NOT Country='USA';
从表 Customers 中提取所有 Country 属性不为 Germany 也不为 USA 的元素的所有属性

--------------------------------------------------------------------------------

SQL ORDER BY 排序
{
   SELECT column1, column2, ...
   FROM table_name
   ORDER BY column1, column2, ... ASC|DESC;
}

SELECT * FROM Customers ORDER BY Country;
从表 Customers 中提取所有元素的属性，并根据 Country 按照升序排序

SELECT * FROM Customers ORDER BY Country DESC;
从表 Customers 中提取所有元素的属性，并根据 Country 按照降序排序

SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;
从表 Customers 中提取所有元素的属性，先根据 Country 按照升序排序，如果 Country 相同，则按照 CustomerName 降序排序

--------------------------------------------------------------------------------

二 INSERT 插入

--------------------------------------------------------------------------------

INSERT INTO (两种形式)
1.
{
   INSERT INTO table_name (column1, column2, column3, ...)
   VALUES (value1, value2, value3, ...);
}

2.
{
   INSERT INTO table_name
   VALUES (value1, value2, value3, ...);
}

前提条件：假设表中有 CustomerName, ContactName, Address, City, PotalCode, Country 这些行

INSERT INTO Customers (CustomerName, ContactName, Address, City, PotalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
在表中插入新的一行

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
在表中插入新的一行，但新的一行只有 CustomerName, City, Country 这三行插入数据

第 2 种方式没有列名，需要按列表种列的顺序，写入对应的值。

--------------------------------------------------------------------------------

三 空值

--------------------------------------------------------------------------------

IS NULL 为空
{
  SELECT column_names
  FROM table_name
  WHERE column_name IS NULL;
}

SELECT LastName, FirstName, Address FROM Persons WHERE Address IS NULL;
从表 Persons 中提取出 Address 属性值没有的元素的 LastName, FirstName, Address 的值

IS NOT NULL 不为空
{
  SELECT column_names
  FROM table_name
  WHERE column_name IS NOT NULL;
}

SELECT LastName, FirstName, Address FROM Persons WHERE Address IS NOT NULL;
从表 Persons 中提取出 Address 属性值存在的元素的 LastName, FirstName, Address 的值

创建表时
SQL> CREATE TABLE CUSTOMERS{
   ID  INT             NOT NULL,
   NAME  VARCHAR (20)  NOT NULL,
   AGT  INT            NOT NULL,
   ADDRESS  CHAR (25),
   SALARY  DECIMAL (18, 2),
   PRIMARY  KEY (ID)
};
该表中前三列的数据必须填写，不能为空，后三列的数据可以为空

--------------------------------------------------------------------------------

四 UPDATE 更新

--------------------------------------------------------------------------------

SQL UPDATE
{
   UPDATE table_name
   SET column1 = value1, column2 = value2, ...
   WHERE condition;
}

UPDATE Customers SET ContactName = 'Alfred Schmidt', City = 'Frankfurt' WHERE CustomerID = 1;
更新表 Customers 中所有 CustomerID 为 1 的元素的属性，将 ContactName 改为 Alfred Schmidt, City 改为 Frankfurt

UPDATE Customers SET ContactName = 'Juan';
省略了 WHERE 语句则将表中所有元素的 ContactName 改为 Juan

--------------------------------------------------------------------------------

五 DELETE 删除

--------------------------------------------------------------------------------

SQL DELETE
{
  DELETE FROM table_name
  WHERE condition;
}

DELETE FROM Customers WHERE CustomerName = 'Alfreds Futterkiste';
删除表中 CustomerName 为 Alfreds Futterkiste 的元素

DELETE FROM table_name; 或者 DELETE * FROM table_name;
删除表中所有元素，但表的结构任然保存

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

限制提取数量在 MyAQL 中是 LIMIT, Oracle 中用 ROWNUM

LIMIT
{
   SELECT column_name(s)
   FROM table_name
   WHERE condition
   LIMIT number
}

SELECT * FROM Persons LIMIT 5;
提取表中前五条元素的数据

SELECT * FROM Customers WHERE Country='Germany' LIMIT 3;
提取表中所有 Country 为 Germany 的前三条数据

SELECT * FROM table LIMIT 5, 10;
提取表中 6~15 行的所有数据

SELECT * FROM table LIMIT 95, -1;
提取表中第 94 行数据

Oracle
{
   SELECT column_name(s)
   FROM table_name
   WHERE ROWNUM <= number;
}

SELECT * FROM Persons WHERE ROWNUM <= 5;
提取表中前五条元素的数据

SELECT * FROM Customers WHERE Country='Germany' AND ROWNUM <= 3;
提取表中所有 Country 为 Germany 的前三条数据

SELECT * FROM table WHERE ROWNUM = 3;
提取表中第 3 条数据

--------------------------------------------------------------------------------

SQL LIKE(模糊查询)
{
   SELECT column1, column2, ...
   FROM table_name
   WHERE columnN LIKE pattern;
}
LIKE 不区分大小写

SELECT * FROM Customers WHERE CustomerName LIKE 'a%';
提取所有 CustomerName 属性中以 a 开头的一行数据

SELECT * FROM Customers WHERE CustomerName LIKE '%a';
提取所有 CustomerName 属性中以 a 结尾的一行数据

SELECT * FROM Customers WHERE CustomerName LIKE '%or%';
提取所有 CustomerName 属性中包含有 or 的一行数据

SELECT * FROM Customers WHERE CustomerName LIKE '_r%';
提取所有 CustomerName 属性中第二个字符为 r 的一行数据

SELECT * FROM Customers WHERE CustomerName LIKE 'a_%_%';
提取所有 CustomerName 属性中以 a 开头且至少三个字符长度的一行数据

SELECT * FROM Customers HWERE CustomerName LIKE 'a%o';
提取所有 CustomerName 属性中以 a 开头 o 结尾的一行数据

SELECT * FROM Customers WHERE CustomerName NOT LIKE 'a%';
提取所有 CustomerName 属性中不以 a 开头的一行数据

--------------------------------------------------------------------------------