数据库及数据库编程

一.数据库编程1.什么是数据库(DataBase)?数据库是用来存储和管理数据的软件在数据存储的同时给用户提供方便的数据访问操作接口。2. 数据库的作用?存储和管理数据3. 数据库的核心?数据库管理系统(DBMS)原则用户访问数据库中的数据是要通过 DBMS 来完成的。4. 应用程序(用户)与 DBMS 的交互方式?SQL(结构化查询语言)也被称为数据库语言。5. DBMS(SQL)提供的功能有哪些?5.1 数据定义语言 DDL5.2 数据操作语言 DML5.3 数据查询语言 DQL5.4 数据控制语言 DCL6. 数据库对数据的管理方式(层级)库(DataBase) -- 数据表(Table) --- 数据(Data)7. 数据库的基础操作预备操作1. 登录MYSQLterryubuntu16:~$ mysql [-h 远程主机IP] -u root -p省略 -h 选择表明登录本地MYSQL2. 退出MYSQLmysql quit / exit7.1 库的操作1.1 查看库mysql show databases;1.2 创建库mysql create database [if not exists] 库名;1.3 删除库mysql drop database [if exists] 库名;1.4 选择库mysql use 库名;7.2 表的操作预备知识1. MYSQL支持的数据类型1) 数值型 (tinyint int float ...)2) 字符串型 (varchar(20) blob text ... )3) 时间日期型 (date time datetime ... )4) 空值类型 NULL2. MYSQL的完整性约束1) 非空约束(not null) 作用: 设置某字段取值不能为 NULL2) 唯一约束(unique key) 作用: 设置某字段取值不能重复3) 主键约束(primary key) 作用: 唯一区分一条记录相当于唯一约束和非空约束4) 默认约束(default) 作用被默认约束约束的字段如果未插入数据则系统将默认值插入5) 自增约束(auto_increment) 作用设置某字段取值自动增1只能修饰整型类型的字段且与主键连用6) 外键约束(foreign key) 作用往往是用来约束两张表如何避免垃圾数据产生。引入概念 1. 记录: 数据表中一行数据被称为一条记录2. 字段: 数据表中一列数据被称为一个字段7.2.1 查看表mysql show tables;7.2.2 创建表mysql create table [if not exists] 表名 (字段1 字段类型 [约束],字段2 字段类型 [约束],...);7.2.3 删除表mysql drop table 表名;7.2.4 查看表结构mysql describe/desc 表名;mysql show create tabl 表名;7.2.6 修改表结构SQL修改表结构的一般格式alter table 表名 动作 参数1) 数据表的重命名mysql alter table 原表名 rename 新表名;2) 字段2.1 增加字段mysql alter table 表名 add 字段名 字段类型 [约束] [位置];位置 缺省 代表增加到最末尾first 代表增加到所有字段头部after 字段 代表增加到指定字段之后2.2 删除字段mysql alter table 表名 drop 字段名;2.3 修改字段2.3.1 修改字段名称mysql alter table 表名 change 旧字段名 新字段名 字段类型;2.3.2 修改字段类型mysql alter table 表名 modify 字段名 字段新类型;2.3.3 调整字段顺序mysql alter table 表名 modify 字段名 字段类型 新位置;新位置: first 代表调整到所有字段头部after 字段 代表调整到指定字段之后3) 字符集(charset)3.1 创建表的同时指定默认字符集mysql create table 表名 (字段1 字段类型 [约束],...)default charsetutf8;3.2 表创建后修改默认字符集mysql alter table 表名 convert to character set utf8;4) 约束4.1 创建表的同时给相关字段指定约束4.2 表创建后如何操作约束1) 设置约束1.1 设置 主键约束 和 唯一约束mysql alter table 表名 add primary key / unique key (字段名);1.2 设置 非空约束 和 自增约束mysql alter table 表名 modify 字段名 字段类型 not null / auto_increment;1.3 设置默认约束mysql alter table 表名 alter 字段名 set default 值;1.4 设置外键约束mysql alter table 表名 add constraint 外键名 foreign key (字段名)references 主表名(主键字段) [on delete / on update ...]2) 取消约束2.1 取消 主键约束 和 外键约束mysql alter table 表名 drop primary key / foreign key 外键名2.2 取消 非空约束 和 自增约束mysql alter table 表名 modify 字段名 字段类型;2.3 取消默认约束mysql alter table 表名 alter 字段名 drop default;2.4 取消唯一约束mysql alter table 表名 drop index 字段名 ;7.3 数据的操作1) DML1.1 数据增加 (insert into 表名....)mysql insert into 表名 [(字段1,字段2,...)] values (值1值2,...);mysql insert into 表名 [(字段1,字段2,...)] values (值11值12,...),(值21值22,...)...;1.2 数据删除 (delete from 表名....)mysql delete from 表名; //删除所有数据, 危险(****************)mysql delete from 表名 where 条件; //有条件删除数据实际上操作规范引出问题SQL语句中如何构建条件?SQL提供了比较运算符和逻辑运算符用于条件的构建比较运算符 !逻辑运算符: || ! xor例子 字段 值1.3 数据更新 (update 表名 set ....)mysql update 表名 set 字段1值1,字段2值2...; //更新字段所有数据,mysql update 表名 set 字段1值1,字段2值2...where 条件; //有条件更新数据2) DQL (select ... from 表)2.1 单表基础查询1) 查询所有数据mysql select * from 表;2) 查询指定字段数据mysql select 字段1,字段2,... from 表;3) 避免重复查询mysql select distinct 字段1,字段2,... from 表;4) 四则运算查询mysql select 字段1 算术运算符 操作数 ,字段2 ... from 表;5) 带条件的查询mysql select 字段1,字段2,... from 表 where 条件;mysql select 字段1,字段2,... from 表 where 条件1 条件2;6) 范围查询mysql select 字段1,字段2,... from 表 where 字段 between 值1 and 值2;mysql select 字段1,字段2,... from 表 where 字段 not between 值1 and 值2;7) 集合查询mysql select 字段1,字段2,... from 表 where 字段 in (值1,值2,值3,....);mysql select 字段1,字段2,... from 表 where 字段 not in (值1,值2,值3,....);8) 空值查询mysql select 字段1,字段2,... from 表 where 字段 is null;mysql select 字段1,字段2,... from 表 where 字段 is not null;9) 模糊查询mysql select 字段1,字段2,... from 表 where 字段 like 值说明 模糊查询需要借助通配符:支持的通配符%: 通配01多个字符_ 通配单个字符例子select name,salary from employee where name like _王%;10) 排序查询mysql select 字段1,字段2,... from 表 order by 字段1 [排序方式],字段2 [排序方式]...;排序方式: asc 升序 (缺省)desc 降序11) 分组查询mysql select 字段1,字段2,... from 表 [where 条件] group by 字段 [having 条件]注意 1. 分组查询的目的适用于数据统计2. 如果想对分组后的结果进行条件过滤要使用having,不能使用where子句12) 分页查询mysql select 字段1,字段2,... from 表 limit [start,] num;2.2 多表连接查询核心思想 将多张数据表连接生成一张临时的 大表大表中集成多个表中的相关数据将多表连接查询转换为单表基础查询。2.2.1 内连接将多张表同时满足连接条件的记录组成一条新纪录mysql select ... from 表1 [inner] join 表2 on 条件1[inner] join 表3 on 条件3 ...;]特点 内连接会筛掉不满足连接条件的记录无法保证某个表所有的记录数据都出现在连接后的大表中;如果在连接后的大表中要存在某个表的所有记录表又该如何连接?2.2.2 外连接1) 左外连接在连接后的大表中确保存在左表的所有记录不满足连接条件的记录在大表右边填充NULL。mysql select ... from 表1 left join 表2 on 条件1left join 表3 on 条件3 ...;2) 右外连接在连接后的大表中确保存在右表的所有记录不满足连接条件的记录在大表左边填充NULL。mysql select ... from 表1 right join 表2 on 条件1right join 表3 on 条件3 ...;3) 全外连接 (注意MYSQL不支持全外连接)在连接后的大表中确保既存在左表的所有记录又存在右表的所有记录不满足连接条件的记录在大表响应位置填充NULL。mysql select ... from 表1 full join 表2 on 条件1full join 表3 on 条件3 ...;2.3 子查询(嵌套查询)子查询 在一个查询语句的内部又存在一个完整的查询语句子查询(内部查询)的结果往往是作为外部查询的条件。子查询可以针对单表也可以针对多表。子查询的形式2.3.1 带有 any/some 关键字的子查询往往针对子查询的结果不唯一借助查询结果(利用 || or)用来构建多条件mysql select ... from 表1 where 字段 运算符 any (select ... from 表...);2.3.2 带有 all 关键字的子查询往往针对子查询的结果不唯一借助查询结果(利用 and)用来构建多条件mysql select ... from 表1 where 字段 运算符 all (select ... from 表...);2.3.3 带有 in 关键字的子查询往往针对子查询的结果不唯一借助查询结果(利用 and)用来构建多条件mysql select ... from 表1 where 字段 in (select ... from 表...);等价于mysql select ... from 表1 where 字段 any (select ... from 表...);2.3.4 带有 exists 关键字的子查询子查询往往不产生结果数据仅仅用来测试子查询是否存在结果如果存在结果执行外部查询否则外部查询不执行。mysql select ... from 表 where exists (select ... from 表...);2.3.5 利用运算符形成的子查询子查询往往针对查询结果是唯一的。mysql select ... from 表1 where 字段 运算符 (select ... from 表...);8. 数据库的备份与加载MYSQL备份工具 mysqldump8.1. 备份方法terryubuntu16:~$ mysqldump -u root -p 待备份的库名 目标文件8.2 数据库文件加载:1) 创建一个新的库mysql create database [if not exists] 库名;2) 向库中导入数据2.1 在系统命令行执行如下指令terryubuntu16:~$ mysql -u root -p 库名 备份文件2.2 在mysql命令行执行如下指令mysql use 库名;mysql source 备份文件路径;9. MYSQL编程操作原则利用 MYSQL 提供的 API 来访问数据库涉及头文件 #include mysql/mysql.h待链接的库文件 -lmysqlclient涉及的数据类型:1. MYSQL : mysql的连接对象/句柄类型2. MYSQL_RES : 结果集合的类型3. MYSQL_FIELD: 字段信息类型4. MYSQL_ROW : 记录类型5. my_ulonglong 记录的数量类型9.1 MYSQL 编程操作的流程步骤1) 初始化连接对象mysql_init函数头文件 #include mysql/mysql.h函数原型: MYSQL* mysql_init(MYSQL* mysql);函数功能 创建或初始化连接对象/句柄函数参数 mysql: [Out] 接收待初始化的连接对象函数返回值 MYSQL*句柄。如果无足够内存以分配新的对象返回NULL。实际应用1. MYSQL mysql;mysql_init(mysql);2. MYSQL *mysql mysql_init(NULL);2) 利用连接对象连接数据库mysql_real_connect头文件: #include mysql/mysql.h函数原型 MYSQL* mysql_real_connect(MYSQL* mysql,const char* host,const char* user,const char* passwd,const char* db,uint16_t port,const char* unix_socket,uint64_t client_flag);函数功能 连接数据库函数参数 mysql: 已经成功初始化的连接句柄host MYSQL主机名NULL 或者 localhost 代表连接本地数据库user MYSQL登录名passwdMYSQL登录密码db 连接库名NULL代表连接MYSQL默认库port MYSQL服务端端口0代表默认端口unix_socket 连接套接字文件名一般写NULLclient_flag 客户端连接标记一般写0若指定为 CLIENT_MULTI_STATMENTS 代表连接对象可支持多语句同时执行但也需要逐一处理结果集。函数返回值: 成功返回第一个参数值失败返回NULL。3) 执行SQL语句完成数据库实际操作mysql_real_query头文件: #include mysql/mysql.h函数原型 int mysql_real_query(MYSQL* mysql,const char* sql,size_t sqllen);函数功能 执行sql语句完成数据库相应操作函数参数 mysql: 已经成功连接连接句柄sql: 待执行的sql语句sqllen: sql语句的长度函数返回值: 0 成功非0 失败如果操作是一个查询操作3.1 存储结果集mysql_store_result头文件: #include mysql/mysql.h函数原型 MYSQL_RES* mysql_store_result(MYSQL* mysql);函数功能 存储结果集函数参数 mysql: 已经成功连接连接句柄函数返回值: 成功 返回指向结果集合的指针失败 NULL3.2 获取结果数据1) 获取记录数据my_ulonglong mysql_num_rows (MYSQL_RES* result);MYSQL_ROW mysql_fetch_row (MYSQL_RES* result);unsigned int mysql_num_fields(MYSQL_RES* result);MYSQL_FIELDS mysql_fetch_fields(MYSQL_RES* result);3.3 释放结果集mysql_free_result头文件: #include mysql/mysql.h函数原型 void mysql_free_result(MYSQL_RES* result);函数功能 存储结果集函数参数 result:待释放的结果集函数返回值: 无4) 关闭连接释放连接对象mysql_close函数头文件 #include mysql/mysql.h函数原型: void mysql_close(MYSQL* mysql);函数功能 关闭连接释放连接对象函数参数 mysql: 待关闭连接对象地址函数返回值 无