sqlite数据库语法与实践1

高力士等十万人

已于 2025-02-21 21:36:52 修改

阅读量729

点赞数 22

分类专栏：数据库文章标签：数据库 sqlite jvm

于 2025-01-27 10:36:44 首次发布

本文链接：https://blog.youkuaiyun.com/MEABUG/article/details/145377759

版权

数据库专栏收录该内容

7 篇文章

订阅专栏

1.1. sqlite的常用数据类型

INTEGER：用于存储整数值，根据值的大小储存在1、2、3、4、6或8字节中，储存的值只有整数选择这个。

- 存储效率：INTEGER类型的数据存储效率高，因为它直接存储整数值，没有额外的存储开销。
- 性能：在进行数学运算和比较操作时，INTEGER类型的性能通常优于其他数值类型。
- 适用场景：适用于需要存储整数值的场景，如计数器、ID、枚举值等。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    age INTEGER
);

REAL：用于存储浮点数。储存的值是小数的情况。

- 存储效率：REAL类型的数据存储为双精度浮点数，适合存储小数。
- 适用场景：适用于需要存储小数的场景，如价格、测量值等。

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    price REAL
);

TEXT：用于存储文本数据。

- 可以存储任意长度的字符串。

BLOB：用于存储二进制数据。

- 可以存储任意长度的二进制数据，例如图片、文件

CREATE TABLE images (
    id INTEGER PRIMARY KEY,
    image_name TEXT,
    image_data BLOB
);

NULL：表示字段的值可以为空，默认都是null，一般某个字段不允许为NULL的时候才会在字段后面专门写 NOT NULL。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT NULL
);

DATE：通过 TEXT 类型存储日期。
TIME：通过 TEXT 类型存储时间。
DATETIME：通过 TEXT 类型存储日期和时间。

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    hire_date TEXT,--DATE 特定格式 YYYY-MM-DD
    log_time TEXT, -- TIME 特定格式 HH:MM:SS
    event_time TEXT --DATETIME YYYY-MM-DD HH:MM:SS
);

INSERT INTO employees (name, hire_date,log_time,event_time) VALUES ('Alice', '2022-01-15','14:30:00','2022-01-15 14:30:00');

BOOLEAN：通过 INTEGER 类型存储布尔值。0 1来储存

CREATE TABLE settings (
    id INTEGER PRIMARY KEY,
    name TEXT,
    is_active INTEGER
);
INSERT INTO settings (name, is_active) VALUES ('AutoSave', 1);

NUMERIC：用于存储数值数据，自动选择存储方式。适合储存的值可能是整数也可能是浮点数

- 灵活性：NUMERIC类型可以存储整数或浮点数，具有较高的灵活性。
- 自动选择存储方式：SQLite会根据实际存储的值自动选择最合适的存储方式，例如，如果存储的是整数，则以INTEGER方式存储；如果存储的是浮点数，则以REAL方式存储。
- 适用场景：适用于需要存储精确数值的场景，如财务数据、测量值等。

CREATE TABLE measurements (
    id INTEGER PRIMARY KEY,
    value NUMERIC
);
INSERT INTO measurements(id,value)VALUES(1,2);
INSERT INTO measurements(id,value)VALUES(2,2.32);
INSERT INTO measurements(id,value)VALUES(3,2.23);
--主键会自动递增
INSERT INTO measurements(value)
VALUES(2),
VALUES(2.32),
VALUES(2.23);

1.2. 数据库基本语法（sqlite）

创建并检查数据库

E:\precticePro\sqliteTest>sqlite3 good_luck.db
SQLite version 3.48.0 2025-01-14 11:05:00
Enter ".help" for usage hints.
sqlite> .databases
main: E:\precticePro\sqliteTest\good_luck.db r/w

sqliet3 databaseName.db-创建数据库，直接在cmd命令上中使用
.databases然后使用检查存在的数据库

.tables 查询table是否创建；

sqlite> .tables

CREATE TABLE - 创建数据库表

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    hire_date TEXT,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

SELECT - 从数据库中提取数据

SELECT * FROM employees;--查询所有的数据
--使用JOIN查询多个表 查询 Employees 表和 Departments 表，获取员工的姓名、邮箱和所属部门名称。
SELECT e.name, e.email, d.department_name
FROM Employees e
JOIN Departments d ON e.department_id = d.department_id;

UPDATE - 更新数据库中的数据

UPDATE employees SET email = 'new_email@example.com' WHERE id = 1;

DELETE - 从数据库中删除数据

DELETE FROM employees WHERE id = 1;

INSERT INTO - 向数据库中插入新数据

INSERT INTO employees (name, email, hire_date, department_id) VALUES ('John Doe', 'john.doe@example.com', '2023-01-01', 1);

CREATE DATABASE - 创建新数据库

sqlite3 testDB.db
E:\precticePro\sqliteTest>sqlite3 good_luck.db 
SQLite version 3.48.0 2025-01-14 11:05:00
Enter ".help" for usage hints.
sqlite> .databases
main: E:\precticePro\sqliteTest\good_luck.db r/w

ALTER TABLE - 变更（改变）数据库表

ALTER TABLE employees ADD COLUMN age INTEGER;

DROP TABLE - 删除表

DROP TABLE employees;

CREATE INDEX - 创建索引（搜索键）

CREATE INDEX idx_email ON employees (email);

DROP INDEX - 删除索引

DROP INDEX idx_email;

使用事务处理

BEGIN TRANSACTION;

INSERT INTO Departments (department_name) VALUES ('Marketing');
INSERT INTO Employees (name, email, hire_date, department_id) VALUES ('David', 'david@example.com', '2022-04-20', 4);

COMMIT;

使用视图简化查询

CREATE VIEW EmployeeDetails AS
SELECT e.name, e.email, d.department_name
FROM Employees e
JOIN Departments d ON e.department_id = d.department_id;

SELECT * FROM EmployeeDetails;

使用触发器自动更新数据

CREATE TRIGGER UpdateEmailTrigger
AFTER UPDATE OF name ON Employees
FOR EACH ROW
BEGIN
    UPDATE Employees SET email = LOWER(NEW.name) || '@example.com' WHERE employee_id = NEW.employee_id;
END;

UPDATE Employees SET name = 'Alice Smith' WHERE employee_id = 1;

1.3. 数据库实操

1.3.1. 在cmd中直接运行SQL语句

在命令行中新建数据库

sqlite3 good_luck.db

5.3.2. 快速编辑，统一运行，使用.sql文件

使用vscode在数据库所在文件夹同级目录新建.sql文件

该文件内容如下


 
 PRAGMA foreign_keys = ON; --允许多个表使用同一个外键
CREATE TABLE Departments (
    department_id INTEGER PRIMARY KEY,
    department_name TEXT NOT NULL,
    parent_department_id INTEGER,
    FOREIGN KEY (parent_department_id) REFERENCES Departments(department_id)
);
CREATE TABLE Employees(
    employee_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    hire_date TEXT,
    department_id INTEGER,
    FOREIGN KEY (department_id)REFERENCES Departments(department_id)
);
CREATE TABLE Salaries(
    salary_id INTEGER PRAGMA KEY,
    employee_id INTEGER,
    basic_salary REAL,
    bonus REAL,
    deduction REAL,
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
);
CREATE TABLE AwardsPunishments (
    record_id INTEGER PRIMARY KEY,
    employee_id INTEGER,
    description TEXT,
    date TEXT,
    type TEXT,  -- 'Award' 或 'Punishment'
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
);
CREATE TABLE Commutes (
    commute_id INTEGER PRIMARY KEY,
    employee_id INTEGER,
    commute_date TEXT,
    check_in_time TEXT,
    check_out_time TEXT,
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
);
CREATE TABLE Leaves (
    leave_id INTEGER PRIMARY KEY,
    employee_id INTEGER,
    leave_start_date TEXT,
    leave_end_date TEXT,
    leave_reason TEXT,
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
);
CREATE TABLE CompensatoryLeaves (
    leave_id INTEGER PRIMARY KEY,
    employee_id INTEGER,
    leave_date TEXT,
    leave_hours REAL,
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
);
CREATE TABLE CompensatoryLeaveBalances (
    balance_id INTEGER PRIMARY KEY,
    employee_id INTEGER,
    monthly_hours REAL DEFAULT 6,  -- 每月调休时长
    quarterly_hours REAL DEFAULT 10,  -- 每季度调休时长
    remaining_hours REAL DEFAULT 0,  -- 剩余调休时长
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
);
INSERT INTO Departments (department_name, parent_department_id) VALUES
('xxxxx股份有限公司', NULL), --1
('一级部门1', 1), --2
('一级部门2', 1),--3
('二级部门1', 2),--4
('二级部门2', 3),--5
('三级部门1', 4),--6
('三级部门2', 5);--7

INSERT INTO Employees (name, email, hire_date, department_id) VALUES
('YJW', 'YJW@example.com', '2010-01-15', 2),
('ZDY', 'ZDY@example.com', '2005-05-18', 1),
('HWJ', 'HWJ@example.com', '2016-02-20', 6),
('LJD', 'LJ@example.com', '2021-02-20', 6),
('FJW', 'FJ@example.com', '2015-02-20', 6),
('TFD', 'TF@example.com', '2023-03-20', 6),
('JDS', 'JD@example.com', '2023-07-20', 6),
('ZJG', 'ZJG@example.com', '2022-02-20', 5),
('WWR', 'WW@example.com', '2022-02-20', 3),
('FXL', 'FXL@example.com', '2022-02-20', 7),
('YZW', 'YZ@example.com', '2023-07-20', 7),
('SXY', 'SXY@example.com', '2023-07-20', 7),
('JDR', 'JDR@example.com', '2023-07-20', 7),
('CBS', 'CB@example.com', '2023-10-20', 7),
('CCE', 'CC@example.com', '2024-012-20', 7);

-- 插入工资数据
INSERT INTO Salaries (employee_id, basic_salary, bonus, deduction) VALUES
(1, 200000, 0, 0),  -- YJW
(2, 200000, 0, 0),  -- ZDY
(3, 195000, 0, 0),  -- HWJ
(4, 195000, 0, 0),  -- LJ
(5, 195000, 0, 0),  -- FJ
(6, 195000, 0, 0),  -- TF
(7, 195000, 0, 0),  -- JD
(8, 190000, 0, 0),  -- ZJG
(9, 190000, 0, 0),  -- WW
(10, 185000, 0, 0), -- FXL
(11, 185000, 0, 0), -- YZ
(12, 185000, 0, 0), -- SXY
(13, 185000, 0, 0), -- JDR
(14, 185000, 0, 0), -- CB
(15, 185000, 0, 0); -- CC

-- 插入2024年优秀员工
INSERT INTO AwardsPunishments (employee_id, description, date, type) VALUES
(11, '优秀员工', '2024-01-01', 'Award'),  -- 
(12, '优秀员工', '2024-01-01', 'Award');  -- 

-- 插入2023年优秀员工
INSERT INTO AwardsPunishments (employee_id, description, date, type) VALUES
(14, '优秀员工', '2023-01-01', 'Award'),  -- 
(15, '优秀员工', '2023-01-01', 'Award');  -- 

-- 插入2022年优秀员工
INSERT INTO AwardsPunishments (employee_id, description, date, type) VALUES
(3, '优秀员工', '2022-01-01', 'Award'),  -- 
(5, '优秀员工', '2022-01-01', 'Award');  -- 

--插入通勤记录 使用SQL方法

-- 插入的请假记录
INSERT INTO Leaves (employee_id, leave_start_date, leave_end_date, leave_reason) VALUES
(11, '2024-05-01', '2024-05-05', '家庭事务');

-- 插入的请假记录
INSERT INTO Leaves (employee_id, leave_start_date, leave_end_date, leave_reason) VALUES
(12, '2024-10-31', '2024-11-08', '生病');

-- 插入调休记录 使用SQL语句实现
INSERT INTO Commutes(employee_id,commute_date,check_in_time,check_out_time)
SELECT employee_id,'2024-01-21', '09:00', '18:00' FROM Employees UNION ALL
SELECT employee_id, '2024-01-22', '09:00', '18:00' FROM Employees
UNION ALL
SELECT employee_id, '2024-01-23', '09:00', '18:00' FROM Employees
UNION ALL
SELECT employee_id, '2024-01-24', '09:00', '18:00' FROM Employees
UNION ALL
SELECT employee_id, '2024-01-25', '09:00', '18:00' FROM Employees;

--初始化调休余额 (1, 6 * 12 + 10 * 4)

SELECT * FROM Departments;
SELECT * FROM Employees;
SELECT * FROM Salaries;
SELECT * FROM AwardsPunishments;
SELECT * FROM Commutes;
SELECT * FROM Leaves;
SELECT * FROM CompensatoryLeaves;
SELECT * FROM CompensatoryLeaveBalances;

使用 .read 命令运行 .sql 文件
.tables 查询现有的表

E:\precticePro\sqliteTest>sqlite3 good_luck.db
SQLite version 3.48.0 2025-01-14 11:05:00
Enter ".help" for usage hints.
sqlite> .databases
main: E:\precticePro\sqliteTest\good_luck.db r/w
sqlite> .read E:\precticePro\sqliteTest\EmployeesMS.sql