# 1.修改之添加列：给stu表添加classname列： ALTERTABLE stu ADD (classname varchar(100)); # 2.修改之修改列类型：修改stu表的gender列类型为CHAR(2)： ALTERTABLE stu MODIFY gender CHAR(2); # 3.修改之修改列名：修改stu表的gender列名为sex： ALTERTABLE stu change gender sex CHAR(2); # 4.修改之删除列：删除stu表的classname列： ALTERTABLE stu DROP classname; # 5.修改之修改表名称：修改stu表名称为student： ALTERTABLE stu RENAME TO student;
DML 数据操作语言
insert
语句
作用
insert into student values (1,’bai’,’history’，50);
普通添加
insert into student values (2,’black’,NULL,100);
添加可以加入 null 空元素
insert into student values (3,’green’,’enlish’,20);
insert into student (name,major,student_id,score) values (‘red’,’enlish’,4,60);
可以按照自定顺序添加
updete
语句
作用
update student set major=’chemistry’ where student_id= ‘4’;
在 id 为 4 的行的 major 改为 chemistry。
update student set major = ‘japan’ where student_id = ‘3’ or student_id=2 ;
在 id 为 2 或 3 的地方改为 japan（and 同理）
UPDATE student SET ‘name’=‘zero’
不安全会报错
update student set name=’purple’ , score=10000 where student_id=5;
在 id 为 5 下 name 改为 purple 和 score10000
delete
语句
作用
DELETE FROM ‘student’ WHERE ‘name’=‘小灰’ AND ‘major’=‘物理’；
# 修改数据 UPDATE stu SET sname=’liSi’, age=’20’ WHERE age>50AND gender=’male’;
# 删除数据 DELETEFROM stu WHERE sname=’chenQi’ OR age >30; DELETEFROM stu; # truncate 是先DROPTABLE，再CREATETABLE。而且TRUNCATE删除的记录是无 法回滚的，但DELETE删除的记录是可以回滚的 TRUNCATETABLE stu;
# 方言版 SELECT e.ename,e.sal,e.comm,d.dname FROM emp AS e,dept AS d WHERE e.deptno=d.deptno; # 标准版 SELECT* FROM emp e INNERJOIN dept d ON e.deptno=d.deptno;
左连接
1 2 3
SELECT*FROM emp e LEFTOUTERJOIN dept d ON e.deptno=d.deptno;
右连接
1 2 3
SELECT*FROM emp e RIGHTOUTERJOIN dept d ON e.deptno=d.deptno;
自然连接
1 2 3
SELECT * FROM emp NATURAL JOIN dept; SELECT * FROM emp NATURAL LEFT JOIN dept; SELECT * FROM emp NATURAL RIGHT JOIN dept;
// Start is called before the first frame update voidStart() { connectionString = "URI=file:" + Application.dataPath + "/PlayerData.db"; CreateTable(); }
// 创建表 voidCreateTable() { using (var conn = new SqliteConnection(connectionString)) { conn.Open();
using (var cmd = conn.CreateCommand()) { cmd.CommandText = "CREATE TABLE IF NOT EXISTS Player (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, level INTEGER)"; cmd.ExecuteNonQuery(); } } } }
using System.Collections; using System.Collections.Generic; using UnityEngine; using Mono.Data.Sqlite;
publicclassDatabaseManager : MonoBehaviour { privatestring connectionString; // Start is called before the first frame update voidStart() { connectionString = "URI=file:" + Application.dataPath + "/PlayerData.db"; CreateTable();//创建表 InsertData("Jack", 10);//向"Player"表中插入一条玩家角色信息 QueryData();//查询所有的角色信息 }
// 创建表 voidCreateTable() { using (var conn = new SqliteConnection(connectionString)) { conn.Open();
using (var cmd = conn.CreateCommand()) { cmd.CommandText = "CREATE TABLE IF NOT EXISTS Player (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, level INTEGER)"; cmd.ExecuteNonQuery(); } } }
// 插入数据 voidInsertData(string name, int level) { using (var conn = new SqliteConnection(connectionString)) { conn.Open();
using (var cmd = conn.CreateCommand()) { cmd.CommandText = "INSERT INTO Player (name, level) VALUES (@name, @level)"; cmd.Parameters.AddWithValue("@name", name); cmd.Parameters.AddWithValue("@level", level); cmd.ExecuteNonQuery(); } } }
// 查询数据 voidQueryData() { using (var conn = new SqliteConnection(connectionString)) { conn.Open();
using (var cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT * FROM Player";
using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { var id = reader.GetInt32(0); var name = reader.GetString(1); var level = reader.GetInt32(2);
