复习一下基础的数据库语句,随笔记录一下哈,未完待续。参考资料在小手的超链接里。
目录
基础SQL语句
1. 创建表,数据库
CREATE DATABASE database_name
CREATE TABLE Persons
(
Id_P int,
LastName varchar(255), // varchar可变长度字符串,括号内为字符串最大长度
City varchar(255), // char固定长度字符串
Numbers int(8), // 前面是用逗号,最后一行没有标点,括号内的表示数字最大位数
PRIMARY KEY (Id_P) // 主键!
)
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) // MySQL外键写法
// Id_P int FOREIGN KEY REFERENCES Persons(Id_P) //SQL Server / Oracle / MS Access的外键写法
)
2. 查找
- 简单查找
SELECT * FROM 表名称 // 获取所有列
SELECT TOP 2 * FROM Persons // 获取头两行所有列
SELECT 列名称 FROM 表名称 // 获取名称为xxx的列
- 有条件查找
// 有条件地从表中选择数据
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
SELECT * FROM Persons WHERE City='Beijing' // 例子
// 满足多个条件
SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'
SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'
// like用法:用于在 WHERE 子句中搜索列中的指定模式
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
SELECT * FROM Persons WHERE City LIKE 'N%' // 从表中选取居住在以 "N" 开始的城市里的人
SELECT * FROM Persons WHERE City LIKE '%g' // 从表中选取居住在以 "g" 结尾的城市里的人
SELECT * FROM Persons WHERE City LIKE '%lon%' // 从表中选取居住在包含'lon'的城市里的人
SELECT * FROM Persons WHERE City NOT LIKE '%lon%' // 选择城市不包含'lon'的人
注:对于有些复杂的条件,可以用not in 补集的形式
- 比如:存着s_name、subject、score三列的表格table_name,选出所有成绩都大于80分的学生姓名
- 就可以选择not in 有任意一科<80的学生。
SELECT s_name FROM table_name WHERE s_name not in(
SELECT s_name FROM table_name WHERE score < 80)
- 对结果排序处理
// 对结果排序:按照Company降序,按照OrderNumber升序
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
例题:统计总成绩前十的学生
- 数学操作
// 计数
SELECT COUNT(列名) FROM 表名 WHERE 条件 // 指定列的满足条件的值的数目,NULL不计入
// 若在列名前加个distinct,则返回指定列不重复的记录数目
SELECT COUNT(*) FROM 表名 // 返回表中的记录数
// AS xxx 是将结果存到新的列
SELECT SUM(OrderPrice) AS OrderTotal FROM Orders // 对一列的数值求和
// MAX 函数返回一列中的最大值。NULL 值不包括在计算中。
// MIN 和 MAX 也可用于文本列,以获得按字母顺序排列的最高或最低值。
// AVG 函数返回数值列的平均值。NULL 值不包括在计算中。
- 联表查询,多表查询
按照department_id查询employees员工表和departments部门表的信息:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, department d
WHERE e.department_id = d.department_id
列出所有人的定购:我们可以通过引用两个表的方式,从两个表中获取数据:谁订购了产品,并且他们订购了什么产品?
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P // Orders表里面有一列Id_P保存的是对应的Persons主键
ORDER BY Persons.LastName
- left join(左联接): 返回包括左表中的所有记录和右表中联结字段相等的记录。
- right join(右联接): 返回包括右表中的所有记录和左表中联结字段相等的记录。
- inner join(等值连接): 只返回两个表中联结字段相等的行。(默认)
- full join:只要其中某个表存在匹配,FULL JOIN 关键字就会返回行。
3. 去重和找出重复
去重
select distinct 列名称 from 表名称 // 获取的结果无重复值,对该列去重
delete * from 表名 limit 9 // 某表格中有10个一模一样的数据,删掉其中9个
[ID,email] 表格中找出重复的email,两种写法:
select distinct a.Email from Person a,Person b where a.Email = b.Email and a.Id != b.Id;
select Email from Person group by Email having count(Email)>1;
4. 添加:
ALTER TABLE 表名
ADD 待添加列名 数据类型 // ADD Birthday year
5. 插入:
// 向指定的列插入对应的值,没被提及的列为null
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
// 向表插入完整的一行
INSERT INTO 表名称 VALUES (值1, 值2,....)
6. 删除:
DELETE FROM 表名称 WHERE 列名称 = 值 // 删除表中指定的行
DELETE FROM table_name // 删除所有行,不删除表
DELETE * FROM table_name // 删除所有行,不删除表
ALTER TABLE 表名
DROP COLUMN 待删除列名
truncate table 表名 // 删除表中所有字段
drop table 表名 // 删除表
drop database 数据库名 // 删除数据库
7. 修改、更新:
// 更新:某一行的某一列:
UPDATE 表名称 SET 列名称1 = 新值 WHERE 列名称2 = 某值 // 列名称1这一列中=某值的这一行,对应的列名称1的值,将其设为新值
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
// 更新某一行中的若干列
UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
WHERE LastName = 'Wilson' // 对这一行,更改👆这俩列的值
// 更改表中列的数据类型
ALTER TABLE 表名
ALTER COLUMN 列名 数据类型
8. 子查询方式,怎样创建临时表
SELECT * INTO #临时表名 FROM
(
SELECT xxxxxx //你的查询语句
)AS table_source //这个别名是必须的
WHERE xxxxxxxx //你需要的where判断;
COMMIT或ROLLBACK后可自动删除该临时表
// 形成的结果表名称为 tmp
// 表内的列为SELECT的查询结果,字段名称可以根据自己需要使用AS重命名
CREATE TABLE tmp AS
(SELECT column1 AS field1, column2 AS field2...);
9. 用自己熟悉的语言写从数据库读取内容(比如有一个学生的类,里面有学号姓名这些信息)显示出来
C++ 👉 通过C++ API方式调用 or 通过mysql的Connector C++
C# 👉 待续
Java 👉
package com.exp1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class InsertTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1. 导入外部的驱动包
//2. 加载驱动
Class.forName("com.mysql.jdbc.Driver");
//3. 建立连接 url:全球资源定位
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/lmy", "root", "123456");
// SQL语句
String sql = "填入SQL语句"; // insert into student values(?, ?, ?),
//4. 创建PreparedStatement对象
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, 6); ps.setString(2, "dd"); //5. 参数设置 // 略
int i = ps.executeUpdate();
// 关闭数据库连接
ps.close();
conn.close();
}
}
总结SQL语句 快速参考👉
语句 | 语法 |
---|---|
AND / OR | 从表格中选择列名为xx的且满足这俩条件的列 |
ALTER TABLE (add column) |
|
ALTER TABLE (drop column) |
|
AS (alias for column) |
|
AS (alias for table) |
|
BETWEEN | 选取介于俩数值之间的行 |
CREATE DATABASE | CREATE DATABASE database_name |
CREATE INDEX | CREATE INDEX index_name ON table_name (column_name) |
CREATE TABLE |
|
CREATE UNIQUE INDEX | CREATE UNIQUE INDEX index_name ON table_name (column_name) |
CREATE VIEW | CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition |
DELETE FROM | DELETE FROM table_name (Note: Deletes the entire table!!) or DELETE FROM table_name |
DROP DATABASE | DROP DATABASE database_name 删除数据库 |
DROP INDEX | DROP INDEX table_name.index_name |
DROP TABLE | DROP TABLE table_name |
GROUP BY | SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 |
HAVING | SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 HAVING SUM(column_name2) condition value |
IN | SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..) |
INSERT INTO | INSERT INTO table_name VALUES (value1, value2,....) or INSERT INTO table_name |
LIKE | SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern |
ORDER BY | SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC] |
SELECT | SELECT column_name(s) FROM table_name |
SELECT * | SELECT * FROM table_name |
SELECT DISTINCT | SELECT DISTINCT column_name(s) FROM table_name |
SELECT INTO (used to create backup copies of tables) | SELECT * INTO new_table_name FROM original_table_name or SELECT column_name(s) |
TRUNCATE TABLE (deletes only the data inside the table) | TRUNCATE TABLE table_name 删除表中所有字段 |
UPDATE |
|
WHERE |
|
数据库理论知识
数据库的事务
1. 事务的概念
- 数据库事务:是数据库管理系统执行过程中的一个逻辑单位,由一个有限的数据库操作序列构成(构成单一逻辑工作单元的操作集合)。
- 数据库事务可以包含一个或多个数据库操作,但这些操作构成一个逻辑上的整体。
- 构成事务的所有操作,要么全都对数据库产生影响,要么全都不产生影响,
- 即不管事务是否执行成功,数据库总能保持一致性状态。
- 构成逻辑整体的这些数据库操作,要么全部执行成功,要么全部不执行。
- 以上即使在数据库出现故障以及并发事务存在的情况下依然成立。
2. 事务的作用
(以A给B转账为例,将A金额减少和B金额增加捆在一个事务“转账”里:)
- 当数据库操作失败或者系统出现崩溃,系统能够以事务为边界进行恢复,不会出现A账户金额减少而B账户未增加的情况。
- 当有多个用户同时操作数据库时,数据库能够以事务为单位进行并发控制,使多个用户对B账户的转账操作相互隔离。
注:
- 事务使系统能够更方便的进行故障恢复以及并发控制,从而保证数据库状态的一致性。
- 当事务提交给DBMS,管理系统需要确保事务中的所有操作全部完成且结果永久保存在DB。若出现没有成功完成的,则该事务的全部操作都被回滚,回到执行前的状态。而这对其他事务和数据库无影响。
3. 事务的特性:ACID
具体指什么,举例说明:
- 原子性(Atomicity):事务中的所有操作作为一个整体像原子一样不可分割,要么全部成功,要么全部失败。
- 一致性(Consistency):事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态。
- 一致性状态是指:
- 系统的状态满足数据的完整性约束 (主码,参照完整性,check约束等)
- 系统的状态反应数据库本应描述的现实世界的真实状态,比如转账前后两个账户的金额总和应该保持不变。
- 一致性状态是指:
- 隔离性(Isolation):并发执行的事务不会相互影响,其对数据库的影响和它们串行执行时一样。
- 比如多个用户同时往一个账户转账,最后账户的结果应该和他们按先后次序转账的结果一样。
- 持久性(Durability):事务一旦提交,其对数据库的更新就是持久的。任何事务或系统故障都不会导致数据丢失。
数据库连接泄漏
- 在某次使用或某段程序中没有正确地关闭Connection、Statement、ResultSet资源,每次执行都会留下没有关闭的连接。
- 这些连接失去引用,而得不到重新使用,因此造成数据库连接的泄漏。
- 坏处:数据库连接的资源是有限的,若在使用频度较高的代码中,出现这种情况,资源将被耗尽,影响系统正常运转。
索引
数据库的几种连接方式
(左右连接,内连接和外连接)以及左连接和右连接的区别
数据库用的什么,不同引擎的区别和用途
当数据量很大时,除了用索引还可以用什么加快数据查找速度
数据库怎么处理过期机制
数据库优化
1. 我们的数据库当中如何做的优化?
- 对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
- 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描
- 应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。
- 应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描
- in 和 not in 也要慎用,否则会导致全表扫描
- like模糊全匹配也将导致全表扫描