1.数据库封装类MySQLiteManager
import Foundation
import SQLite3
//数据库封装类
class MySQLiteManager {
private var db: OpaquePointer!
//重写构造函数
init(dbPath: String) {
let isSuccess = openDB(dbPath: dbPath)
if isSuccess {
print("打开数据库成功")
}else {
print("打开数据库失败")
}
}
//打开数据库
func openDB(dbPath: String) -> Bool {
let result = sqlite3_open(dbPath, &db)
if result != SQLITE_OK { return false }
return true
}
//关闭数据库
func closeDB() {
sqlite3_close(db)
}
deinit {
sqlite3_close(db)
}
}
extension MySQLiteManager {
func createTable(sql: String) -> Bool {
guard sql.contains("create") else { return false }
return execSQL(sql: sql)
}
func dropTable(sql: String) -> Bool {
guard sql.contains("drop") else { return false }
return execSQL(sql: sql)
}
}
extension MySQLiteManager {
//MARK: - 插入
func insert(sql: String) -> Bool {
guard sql.contains("insert") else { return false }
return execSQL(sql: sql)
}
//MARK: - 删除
func delete(sq服务器托管网l: String) -> Bool {
guard sql.contains("delete") else { return false }
return execSQL(sql: sql)
}
//MARK: - 修改
func update(sql: String) -> Bool {
guard sql.contains("update") else { return false }
return execSQL(sql: sql)
}
func execSQL(sql: String) -> Bool {
var errMsg: UnsafeMutablePointer? = nil
let cSql = sql.cString(using: String.Encoding.utf8)!
if sqlite3_exec(db, cSql, nil, nil, &errMsg) == SQLITE_OK {
return true
}
let msg = String.init(cString: errMsg!)
print(msg)
return false
}
//MARK: - 查询
func query(sql: String) -> [[String: Any]]? {
guard sql.contains("select") else {
print("sql语句有误")
return nil
}
let cSql = sql.cString(using: String.Encoding.utf8)!
var statement: OpaquePointer? = nil
if sqlite3_prepare_v2(db, cSql, -1, &statement, nil) != SQLITE_OK {
sqlite3_finalize(statement)
print("执行(sql)错误n")
let errmsg = sqlite3_errmsg(db)
if errmsg != nil {
print(errmsg!)
}
return nil
}
var rows = [[String: Any]]()
while sqlite3_step(statement) == SQLITE_ROW {
rows.append(record(statement: statement!))
}
sqlite3_finalize(statement)
return rows
}
private func record(statement: OpaquePointer) -> [String: Any] {
var row = [String: Any]()
for col in 0 .. Data? {
let csql = sql.cString(using: String.Encoding.utf8)!
var statement:OpaquePointer? = nil
if sqlite3_prepare_v2(db, csql, -1, &statement, nil) != SQLITE_OK {
sqlite3_finalize(statement)
print("执行(sql)错误n")
if let errmsg = sqlite3_errmsg(db) {
print(errmsg)
}
return nil
}
while sqlite3_step(statement) == SQLITE_ROW {
if let dataBlob = sqlite3_column_blob(statement, 0) {
let dataBlobLength = sqlite3_column_bytes(statement, 0)
let data = Data(bytes: dataBlob, count: Int(dataBlobLength))
sqlite3_finalize(statement)
return data
}
}
sqlite3_finalize(statement)
return nil
}
}
//MARK: - sql语句
/**
1.DDL
1.创建表
create table if not exists t_student(id integer auto_increment primary key ,name
varchar(3) , age int);
删除表
drop table if exists t_student;
修改表结构
alter table t_student add address varchar(4);
alter table t_student modify address varchar(6) not null;
tip：
简单约束：
name varchar(3) not null
age int default 66
主键约束：
id integer auto_increment primary key
2. DML
1.插入数据
insert into t_student(name,age,address) values ('xiaoM',12,'guangzhou');
2.修改数据
update t_student set name='xiaoHuang' where id=1;
3.删除数据
delete from t_student where id= 1 ;
tip：
条件约束
delete from t_student where id is 1 or name = 'xiaoM';
3. DQL
查询语句
1.查询所有字段信息
select * from t_student;
2.查询部分字段信息
select name,age from t_student;
3.查询符合条件下的部分字段信息
select name,服务器托管网age from t_student where id=2;
4.查询数据个数
select count(*) from t_student;
5.查询age不为空值的个数
select count(age) from t_student;
6.查询age平均值
select avg(age) from t_student;
7.查询age总和
select sum(age) from t_student;
8.查询age最大值
select max(age) from t_student;
9.查询age最小值
select min(age) from t_student;
10.查询按分数升序，按年龄降序
select *from t_student order by score asc, age desc;
*/2.针对表test写的类TestTableManager
import Foundation
class TestTableManager {
private var sqliteManager: MySQLiteManager!
static let shared = TestTableManager()
init() {
let dbPath = NSHomeDirectory() + "/Documents/testDB.sqlite"
sqliteManager = MySQLiteManager(dbPath: dbPath)
createTable()
}
private func createTable() {
let sql = "create table if not exists test (id integer auto_increment primary key ,name char(50) not null default '');"
if sqliteManager.createTable(sql: sql) {
print("表创建成功")
}else {
print("表创建失败")
}
}
func dropTable() {
let sql = "drop table if exists test;"
if sqliteManager.dropTable(sql: sql) {
print("表删除成功")
}else {
print("表删除失败")
}
}
func insert(name: String) {
let sql = "insert into test(name) values ('(name)');"
let isSuccess = sqliteManager.insert(sql: sql)
if isSuccess {
print("插入成功")
}else {
print("插入失败")
}
}
func quary() {
let sql = "select * from test;"
let data = sqliteManager.query(sql: sql)
print(data!)
}
}3.测试代码
import UIKit
class ViewController: UIViewController {
override func viewDidLoad() {
super.viewDidLoad()
//MARK: - sqlite自封装
TestTableManager.shared.insert(name: "aaaaa")
TestTableManager.shared.insert(name: "bbbbb")
TestTableManager.shared.quary()
TestTableManager.shared.dropTable()
}
override func viewDidAppear(_ animated: Bool) {
super.viewDidAppear(animated)
}
}服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
锁 1、MySQL 有哪些锁？ （1）全局锁 加了全局锁之后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞： 对数据的增删改操作，比如 insert、delete、update等语句； 对表结构的更改操作，比如 alter table、dr…
