数据库基础mysql

来源：互联网发布：centos openstack 编辑：程序博客网时间：2024/06/06 14:08

类型

大小

用途

CHAR

0-255字节

定长字符串

VARCHAR

0-255字节

变长字符串

TINYBLOB

0-255字节

不超过255个字节的二进制字符串

TINYTEXT

0-255字节

短文本字符串

BLOB

0-65535字节

二进制形式的长文本数据

TEXR

0-65535字节

长文本数据

MEDIUMBLOB

0-16777215字节

二进制形式的中等长度文本数据

MEDIUMTEXT

0-16777215字节

中等长度文本数据

LONGBLOB

0-4294967295字节

二进制形式的极大文本数据

LONGTEXT

0-4294967295字节

极大文本数据

类型

大小（字节）

范围

格式

用途

DATE

1000-01-01/9999-12-31

YYYY-MM-DD

日期值

TIME

-838:59:59'/838:59:59'

HH:MM:SS

时间值或持续时间

YEAR

1901/2155

YYYY

年份值

DATETIME

1000-01-01 00:00:00/9999-12-31 23:59:59

YYYY-MM-DD

HH:MM:SS

混合日期和时间值

TIMESTAMP

1970-01-01 00:00:00/2037

YYYYMMDD

HHMMSS

混合日期和时间值，时间戳

对应数据库：

表：记录的集合。

字段：记录中的一个数据项称为字段。

数据项：数据项也称为分量，是数据库中可以命名的最小逻辑数据单位，指某个元组对应列的属性值，用来描述属性的数据。

主键：即为主码。

记录：指的是关系中的一行数据，用它描述实体。它是数据项的有序集，即一个记录是由若干个数据项组成。

SQL是结构化查询语言（StructuredQuery Language）的缩写，其功能包括数据查询、数据操纵、数据定义和数据控制四个部分。

SQL 语言简洁、方便实用、功能齐全，已成为目前应用最广的关系数据库语言。

SQL功能

动词

数据查询

SELECT

数据定义

CREATE,DROP,ALTER

数据操纵

INSERT,UPDATE,DELETE

数据控制

GRANT,REVOKE

操作对象

创建

删除

修改

模式

CREATE SCHEMA

DROP SCHEMA

表

CREATE TABLE

DROP TABLE

ALTER TABLE

视图

CREATE VIEW

DROP VIEW

索引

CREATE INDEX

DROPINDEX

CREATE TABLE <表名>

（<列名> <数据类型>[ <列级完整性约束条件> ]

[，<列名> <数据类型>[ <列级完整性约束条件>] ] …

[，<表级完整性约束条件> ] ）；

字段的长度：指字段所能容纳的最大数据量，但对不同的数据类型来说，长度对字段的意义可能有些不同。

对字符串与UNICODE数据类型而言，长度代表字段所能容纳的字符的数目，因此它会限制用户所能输入的文本长度。

对数值类的数据类型而言，长度则代表字段使用多少个字节来存放数字。

对BINARY、VARBINARY、IMAGE数据类型而言，长度代表字段所能容纳的字节数。

create table player(

name varchar(10),

age int(5) not null,

ssex char(2) default "男",

id int (2) primary key);

INSERT INTO

player(name,age,ssex,id)

VALUES ('zycld',20,'男',0);

数据操控 + from 表名+where 条件

查询：select * from player where name ='zyc';

更新:update player set name = 'ld' where id=0;

如删除：delete from player where name ='zyc';

查询条件

谓词

比较

=，>，<，>=，<=，!=，<>，!>，!<；NOT+上述比较运算符

确定范围

BETWEEN AND，NOT BETWEEN AND

确定集合

IN，NOT IN

字符匹配

LIKE，NOT LIKE

空值

IS NULL，IS NOT NULL

多重条件（逻辑运算）

AND，OR，NOT

select name from player wherename='zyc';

select name from player whereatk>130;

%:替代任意个数的任意字符。

_:替代一个任意字符。

select * form player where atk>100 anddef>100;

select * from player where atk>100 and def>100;

多表查询

select * from player as a,account as bwhere a.acc_id=b.id;

select查询嵌套语句

动词+要查询的数据项+from+表名+where+数据项=（select语句）;

select+......;

select *from player where acc_id=( select

id from account where account='zyc');

动词+要查询的数据项+from+表名+where+数据项=（select语句）;

select+......;

select*from player where acc_id=( select

id from account where account='zyc');

update player set name='ld' whereacc_id=(select id from account where account ='zyc' );

delete from player where name ='zyc';

select unionid,avg(atk) from player group byunionid;

select unionid,sum(atk) from player groupby unionid;

数据项数值分组后的某项值的求和，平均数.......

using UnityEngine;using System;using System.Collections;using Mono.Data.Sqlite;public class DbAccess{private SqliteConnection dbConnection;private SqliteCommand dbCommand;private SqliteDataReader reader;public DbAccess (string connectionString){OpenDB (connectionString);}public DbAccess (){}public void OpenDB (string connectionString){try{dbConnection = new SqliteConnection (connectionString);dbConnection.Open ();Debug.Log ("Connected to db,连接数据库成功！");}catch(Exception e){string temp1 = e.ToString();Debug.Log(temp1);}}public void CloseSqlConnection (){if (dbCommand != null) {dbCommand.Dispose ();}dbCommand = null;if (reader != null) {reader.Dispose ();}reader = null;if (dbConnection != null) {dbConnection.Close ();}dbConnection = null;Debug.Log ("Disconnected from db.关闭数据库！");}public SqliteDataReader ExecuteQuery (string sqlQuery){dbCommand = dbConnection.CreateCommand ();dbCommand.CommandText = sqlQuery;reader = dbCommand.ExecuteReader ();return reader;}public SqliteDataReader ReadFullTable (string tableName){string query = "SELECT * FROM " + tableName;return ExecuteQuery (query);}/// <summary>/// 插入数据 param tableName=表名 values=数据内容/// </summary>public SqliteDataReader InsertInto (string tableName, string[] values){string query = "INSERT INTO " + tableName + " VALUES (" + values[0];for (int i = 1; i < values.Length; ++i) {query += ", " + values[i];}query += ")";return ExecuteQuery (query);}/// <summary>/// 插入数据 param tableName=表名 cols=更新字段 colsvalues=更新内容 selectkey=查找字段（主键) selectvalue=查找内容/// </summary>public SqliteDataReader UpdateInto (string tableName, string []cols,string []colsvalues,string selectkey,string selectvalue){string query = "UPDATE "+tableName+" SET "+cols[0]+" = "+colsvalues[0];for (int i = 1; i < colsvalues.Length; ++i) {query += ", " +cols[i]+" ="+ colsvalues[i];}query += " WHERE "+selectkey+" = "+selectvalue+" ";return ExecuteQuery (query);}/// <summary>/// 删除数据 param tableName=表名 cols=字段 colsvalues=内容/// </summary>public SqliteDataReader Delete(string tableName,string []cols,string []colsvalues){string query = "DELETE FROM "+tableName + " WHERE " +cols[0] +" = " + colsvalues[0];for (int i = 1; i < colsvalues.Length; ++i) {query += " or " +cols[i]+" = "+ colsvalues[i];}return ExecuteQuery (query);}public SqliteDataReader InsertIntoSpecific (string tableName, string[] cols, string[] values){if (cols.Length != values.Length) {throw new SqliteException ("columns.Length != values.Length");}string query = "INSERT INTO " + tableName + "(" + cols[0];for (int i = 1; i < cols.Length; ++i) {query += ", " + cols[i];}query += ") VALUES (" + values[0];for (int i = 1; i < values.Length; ++i) {query += ", " + values[i];}query += ")";return ExecuteQuery (query);}/// <summary>/// 删除表中全部数据/// </summary>public SqliteDataReader DeleteContents (string tableName){string query = "DELETE FROM " + tableName;return ExecuteQuery (query);}/// <summary>/// 创建表 param name=表名 col=字段名 colType=字段类型/// </summary>public SqliteDataReader CreateTable (string name, string[] col, string[] colType){if (col.Length != colType.Length) {throw new SqliteException ("columns.Length != colType.Length");}string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0];for (int i = 1; i < col.Length; ++i) {query += ", " + col[i] + " " + colType[i];}query += ")";return ExecuteQuery (query);}/// <summary>/// 插入数据 param tableName=表名 items=结果字段 col=查找字段 operation=运算符 values=内容/// </summary>public SqliteDataReader SelectWhere (string tableName, string[] items, string[] col, string[] operation, string[] values){if (col.Length != operation.Length || operation.Length != values.Length) {throw new SqliteException ("col.Length != operation.Length != values.Length");}string query = "SELECT " + items[0];for (int i = 1; i < items.Length; ++i) {query += ", " + items[i];}query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";for (int i = 1; i < col.Length; ++i) {query += " AND " + col[i] + operation[i] + "'" + values[0] + "' ";}return ExecuteQuery (query);}/// <summary>/// 查询表/// </summary>public SqliteDataReader Select(string tableName, string col, string values){string query = "SELECT * FROM " + tableName  + " WHERE " + col + " = " + values;return ExecuteQuery (query);}public SqliteDataReader Select(string tableName, string col,string operation, string values){string query = "SELECT * FROM " + tableName  + " WHERE " + col + operation + values;return ExecuteQuery (query);}/// <summary>/// 升序查询/// </summary>public SqliteDataReader SelectOrderASC (string tableName,string col){string query = "SELECT * FROM " + tableName  + " ORDER BY " + col + " ASC";return ExecuteQuery (query);}/// <summary>/// 降序查询/// </summary>public SqliteDataReader SelectOrderDESC (string tableName,string col){string query = "SELECT * FROM " + tableName  + " ORDER BY " + col + " DESC";return ExecuteQuery (query);}/// <summary>/// 查询表行数/// </summary>public SqliteDataReader SelectCount(string tableName){string query = "SELECT COUNT(*) FROM " + tableName;return ExecuteQuery (query);}}

1 0