DDL语句

DDL Data Definition Language 数据定义语句

*dbname*若显示为*dbname*并非表示如此写，而是表示该值为自定义文字

SHOW DATABASES; 展示所有数据库

CREATE DATABASE dbname; 创建dbname数据库

SELECT DATABASE(); 查询当前所在数据库

USE dbname; 使用dbname数据库

DROP DATABASE dbname; 删除dbname数据库

SHOW TABLES 展示该数据库的表

CREATE TABLE player(
    id INT,
    name VARCHAR(100),
    level INT,
    exp INT,
    gold DECIMAL(10,2)
);

create table  表名(
    字段1  字段1类型 [约束]  [comment  字段1注释 ],
    字段2  字段2类型 [约束]  [comment  字段2注释 ],
    ......
    字段n  字段n类型 [约束]  [comment  字段n注释 ] 
) [ comment  表注释 ] ;

在数据库内创建表

DESC dbtable; 查看数据库中某个表的结构

SHOW CREATE DATABASE dbname; 查看数据库创建时的结构定义

ALTER TABLE<表名>[改变方式]

ALTER TABLE player ADD COLUMN last_login DATETIME;

[alter table 表名 add 字段名类型（长度） [comment 注释] [约束];] # 添加字段(column可省略)

ALTER TABLE player MODIFY COLUMN name VARCHAR(200);

[alter table 表名 modify 字段名新数据类型(长度);] # 改变字段数据类型

[alter table 表名 change 旧字段名新字段名类型(长度) [comment 注释] [约束];] # 改变字段名和类型

ALTER TABLE player RENAME COLUMN name TO nick_name;

[alter table 原来的表名 rename to 新表名;] # 修改表名

ALTER TABLE player DROP COLUMN last_login;

[alter table 表名drop 字段名;] # 删除字段

ALTER TABLE player MODIFY level INT DEFAULT 1; # 添加level的默认值

DROP TABLE player; # 删除表

[drop table [ if exists ] 表名;]

[truncate table 表名;] # 删除指定表并创建该表

DML 增删改语句

INSERT INTO player (id, name, level, exp, gold) VALUES (1, '张三', 1,1,1);

INSERT INTO player (id, name) VALUES (2, '张四'),(3,'王五');

insert into <table_name> (column_list) values (value_list)

UPDATE player SET level =1 WHERE name = '张四';

DELETE FROM player WHERE gold = 0;

DQL 查询语句

SELECT * FROM player;

SELECT [all|distinct] <目标表达式> [别名] [,<目标列表表达式>]
FROM <表名或视图名> [别名] [,<表名或视图名>[别名]|(<SELECT语句> [as] <别名>)]
[WHERE <条件>]
[GROUP BY <列名1> [HAVING <条件表达式>]]   # 分组
[ORDER BY <列名2> [asc|desc]]              # 排序

select * from player where name regexp '^王.';

regexp 使用正则表达

liunx 数据库操作

mysql -u root -p 进入mysql

mysqldump -u root -p game > game.sql 数据库导出

mysql -u root -p game.sql < game 数据库导入

sqlite3 (python接口)

步骤：

导入sqlite库
python与数据库获取连接connect
打开游标cursor，在游标内使用执行execute语句执行sql语句
(可以使用fetchall获取全部内容)
进行提交commit
关闭close连接释放资源

利用cur.fetchall()返回所有搜索到的内容。

也可以cur.fetchone()返回第一条内容。

'insert into scores(stu_name, math_scores, chinese_scores) values(?, ?, ?)' # 往表score的第（列1，列2，列3...）中添加insert数值values(?,?,?...) 问号内的是将要被替换的内容也就是占位符
'delete from scores where id = ?' # 从表scores中当列(id,可自行修改)等于?(占位符)时where,删除此行
'update scores set math_scores = ?, chinese_scores = ? where id = 5' # 修改update表scores当where 列(id) = 5时设置set列1(math_scores)... 为= ?,列2...

举例

# 连接数据库
conn = sqlite3.connect(db_faile)
# 编写sql语句
sql = 'select * from scores'     # 查询select所有的*内容，从score中
# 执行sql语句
cur = conn.cursor()     # 一个游标，输入命令是通过cursor交互的
cur.execute(sql)        # 执行execute sql语句
# 打印结果
print(cur.fetchall())       # fetchall，取出所有数据
# 关闭链接
cur.close()
conn.close()

'''一个用于表示对数据库进行增添操作的函数例子 '''
conn = sqlite3.connect(db_faile)
sql = 'insert into scores (stu_name, math_scores, chinese_scores) values(?, ?, ?)'
data = ('王五', 89.9, 102.5)      # 传入sql中的数据一定要是元组格式
cur = conn.cursor()
cur.execute(sql, data)
conn.commit()       # 插入完后一定要显示提交数据，否则双方无法同步数据
cur.close()
conn.close()

'''一个用于表示对数据库进行删除操作的函数例子 '''
conn = sqlite3.connect(db_faile)
sql = 'delete from scores where id = ?'
id = (3, )  # 单个元素的元组通过逗号加空表示,不能直接一个括号内加东西
cur = conn.cursor()
cur.execute(sql, id)
conn.commit()
cur.close()
conn.close()

'''一个用于表示对数据库进行修改操作的函数例子 '''
conn = sqlite3.connect(db_faile)
cur = conn.cursor()
sql = 'update scores set math_scores = ? , chinese_scores = ? where id = 5'
date = (99, 100)
cur.execute(sql, date)
conn.commit()
cur.close()
conn.close()

'''一个用于表示对数据库进行查询操作的函数例子 '''
conn = sqlite3.connect(db_faile)
cur = conn.cursor()
sql = 'select * from scores'
cur.execute(sql)        # 查询操作不需要commit()
print(cur.fetchall())
cur.close()
conn.commit()

'''一个用于表示对数据库进行多行添加操作的函数例子 '''
date_list = [('王二麻子', 60, 30), ('张麻子', 80, 90)]
conn = sqlite3.connect(db_faile)
cur = conn.cursor()
sql = 'insert into scores (stu_name, math_scores, chinese_scores) values (?, ?, ?)'
cur.executemany(sql, date_list)     # 插入多条语句应该用executemany()
conn.commit()
cur.close()
conn.close()
return cur.rowcount     # rowcount 用于显示本次操作操作了几行

"""一个从头开始研究如何创建-使用数据库的例子"""
import sqlite3
from pathlib import Path

curFileDir = Path(__file__).parent

db_file = curFileDir / "test.db"

if not db_file.exists():
    db_file.open("w")
else:
    db_file.unlink()

conn = sqlite3.connect(db_file)
cur = conn.cursor()
cur.execute('''create table "sheet1"(
                "number" INTEGER NOT NULL UNIQUE,
                "name" TEXT NOT NULL,
                "age" INTEGER NOT NULL,
                PRIMARY KEY ("number" AUTOINCREMENT)
                );''')      # 小写也是可以的
conn.commit()

date_list = [("张三", 12), ("李四", 16)]

sql = 'insert into sheet1 (name, age) values (?, ?)'

cur.executemany(sql, date_list)
conn.commit()

a = "select name from sheet1"
cur.execute(a)

return_ = cur.fetchall()
conn.commit()

conn.close()

print(return_)

sql转db

可以使用 db browser for sqlite 进行sql与db的互转

但sqlite与mysql对语法定义有些许差别，其中主要差别为：

sqlite没有：

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
LOCK TABLES `player` WRITE;
UNLOCK TABLES;

等

mysql没有：

BEGIN TRANSACTION;
INSERT INTO "AB" VALUES (620,4,125);
COMMIT;

等