第六章 数据库(以mysql为例)
-
什么是数据库?为什么要使用数据库?
-
数据库是一个实体,是保存和管理数据的“仓库”
-
数据库是数据管理的技术和方法,它能更合适的组织数据,更方便的维护数据,更严密的控制数据,更有效的利用数据。
-
-
论文 “r系统:数据库关系理论” 标志着关系型数据库的诞生
-
oracle:老板 拉里埃里森
-
数据库注释有三种:# 、-- (空格)、/* */
-
数据库的语言
DDL:数据定义语言
DML:数据操作语言
DQL:数据查询语言
DCL:数据控制语言
TPL:事务处理语言
CCL:指针控制语言
-
DDL
常用关键字:CREATE ALTER DROP(删除) TRUNCATE(清空表的相关信息)
#进入数据库的方式 sudo mysql -u root -p #查看数据库 show databases; #创建数据库 create database 39th; #数据库操作每一条语句都要以;号结尾 #操作数据成功,只会报Query OK, #查看创建的数据库属性 show create database 39th; #修改数据库的属性 alter database myDB 属性名 目标; #如修改数据库字符集为utf8 alter database 39th CHARACTER SET utf8; #切换、使用数据库 use + 数据库名 #删除数据库 drop database 39th;
数据的类型:
char(size),提高效率
VARCHAR(size):降低存储容量,节约空间
-
表的操作
#创建member表 create table member (id int, name char(20), age int, math float); #查询当前数据库所有的表 show tables; #查看表的结构信息 show create table member; desc member; #数据库中 列 = 域 = 字段 #修改表, 表的字段 alter table member modify math int; alter table member change name mingzi varchar(20); #change可以改变列名,同样可以改变类型, 但是不能单独去修改类型, modify只可以修改类型,不能修改名字 #修改表名 rename table member to chengyuan; alter table chengyuan rename to member; #插入字段 alter table member add english float; alter table member add chinese float first; /*插入到最前面*/ alter table member add cs float after id; /* 插入数据到id后面*/ #删除表中字段 alter table member drop cs; #删除表 drop member;
-
DML语言
常用关键字:INSERT UPDATE DELETE
#向表中插入数据 insert into member (id, name , age , math) values (1, 'zhansan', 27, 59); #查询刚才插入的结果 select * from member; #可以向表中插入部分数据 insert into member (id, name) values (2, 'lisi'); #不写列名插入数据,必须把所有列都赋值 insert into member values (3, 'wangwu', 21, 86); #快速的插入多条数据 insert into member values (4, 'zhouliu', 21, 89), (5, 'wuqi', 19, 99); #修改表中元素值,更新数据 update member set age = 31 where id = 2; update member set math = 77 where name = 'lisi'; #一次更新多个数据 update member set age = 18, math = 89 where name = 'wuqi'; #删除表中数据 delete from member where id = 5; #可以对表中插入空值 insert into member (id) values (NULL); #删除表中空值 delete from member where id is NULL;
-
DQL
#查询表中单个数据的信息 select id from member; #查询表中部分数据的情况 select name, math from member; #对查询结果去重distinct select distinct age from member; #对查询结果做表达式运算 select name, math + 10 from member; +---------+-----------+ | name | math + 10 | +---------+-----------+ | zhansan | 69 | | lisi | 87 | | wangwu | 96 | | zhouliu | 99 | +---------+-----------+ 4 rows in set (0.00 sec) #对表达式计算取别名 select name, math + 10 + 19*21 -29+18 as total from member; +---------+-------+ | name | total | +---------+-------+ | zhansan | 457 | | lisi | 475 | | wangwu | 484 | | zhouliu | 487 | +---------+-------+ #查询某些特定的列 select * from member where math > 80; +------+---------+------+------+ | id | name | age | math | +------+---------+------+------+ | 3 | wangwu | 21 | 86 | | 4 | zhouliu | 21 | 89 | +------+---------+------+------+ 2 rows in set (0.00 sec) select * from member where math <> 89; +------+---------+------+------+ | id | name | age | math | +------+---------+------+------+ | 1 | zhansan | 27 | 59 | | 2 | lisi | 31 | 77 | | 3 | wangwu | 21 | 86 | +------+---------+------+------+ 3 rows in set (0.01 sec) select * from member where math != 89; +------+---------+------+------+ | id | name | age | math | +------+---------+------+------+ | 1 | zhansan | 27 | 59 | | 2 | lisi | 31 | 77 | | 3 | wangwu | 21 | 86 | +------+---------+------+------+ 3 rows in set (0.00 sec) #查询某范围的数据between ... and ... 查询范围是闭闭区间 select * from member where math between 60 and 89; +------+---------+------+------+ | id | name | age | math | +------+---------+------+------+ | 2 | lisi | 31 | 77 | | 3 | wangwu | 21 | 86 | | 4 | zhouliu | 21 | 89 | +------+---------+------+------+ 3 rows in set (0.00 sec) #查询集合中的数据 in(set) select * from member where math in (10, 29, 59, 77, 100); +------+---------+------+------+ | id | name | age | math | +------+---------+------+------+ | 1 | zhansan | 27 | 59 | | 2 | lisi | 31 | 77 | +------+---------+------+------+ 2 rows in set (0.00 sec) #模糊查询like #%代表一个或者多个任意字符,_代表一个字符 select * from member where name like '%san'; +------+---------+------+------+ | id | name | age | math | +------+---------+------+------+ | 1 | zhansan | 27 | 59 | +------+---------+------+------+ 1 row in set (0.00 sec) select * from member where name like '%u'; +------+---------+------+------+ | id | name | age | math | +------+---------+------+------+ | 3 | wangwu | 21 | 86 | | 4 | zhouliu | 21 | 89 | +------+---------+------+------+ 2 rows in set (0.00 sec) select * from member where name like '%i_'; +------+---------+------+------+ | id | name | age | math | +------+---------+------+------+ | 4 | zhouliu | 21 | 89 | +------+---------+------+------+ 1 row in set (0.00 sec) mysql> select * from member where name like '%i__'; +------+------+------+------+ | id | name | age | math | +------+------+------+------+ | 2 | lisi | 31 | 77 | +------+------+------+------+ 1 row in set (0.00 sec) #数据库的退出方式 1.quit; 2.exit; 3.ctrl + d #查询多个条件同时成立 select * from member where age =21 and id =3; +------+--------+------+------+ | id | name | age | math | +------+--------+------+------+ | 3 | wangwu | 21 | 86 | +------+--------+------+------+ 1 row in set (0.00 sec) #查询的结果中任一条件成立or select * from member where age =27 or id =3; +------+---------+------+------+ | id | name | age | math | +------+---------+------+------+ | 1 | zhansan | 27 | 59 | | 3 | wangwu | 21 | 86 | +------+---------+------+------+ 2 rows in set (0.00 sec) #查询条件不成立的 select * from member where not(math = 59); select * from member where not math = 59; +------+---------+------+------+ | id | name | age | math | +------+---------+------+------+ | 2 | lisi | 31 | 77 | | 3 | wangwu | 21 | 86 | | 4 | zhouliu | 21 | 89 | +------+---------+------+------+ 3 rows in set (0.00 sec) #对查询结果结果排序 (默认是升序的方式) select * from member order by age; +------+---------+------+------+ | id | name | age | math | +------+---------+------+------+ | 3 | wangwu | 21 | 86 | | 4 | zhouliu | 21 | 89 | | 1 | zhansan | 27 | 59 | | 2 | lisi | 31 | 77 | +------+---------+------+------+ 4 rows in set (0.01 sec) select * from member order by age desc ; +------+---------+------+------+ | id | name | age | math | +------+---------+------+------+ | 2 | lisi | 31 | 77 | | 1 | zhansan | 27 | 59 | | 3 | wangwu | 21 | 86 | | 4 | zhouliu | 21 | 89 | +------+---------+------+------+ 4 rows in set (0.00 sec) #显示固定范围的数据limit select * from member order by math desc limit 3; +------+---------+------+------+ | id | name | age | math | +------+---------+------+------+ | 4 | zhouliu | 21 | 89 | | 3 | wangwu | 21 | 86 | | 2 | lisi | 31 | 77 | +------+---------+------+------+ 3 rows in set (0.00 sec) #对查询结果偏移offset select * from member order by math desc limit 1 offset 1; +------+--------+------+------+ | id | name | age | math | +------+--------+------+------+ | 3 | wangwu | 21 | 86 | +------+--------+------+------+ 1 row in set (0.00 sec)
-
数据完整性
-
实体完整性:通过主键(类似于身份证号)保证插入的数据(一行)是表中唯一的实体,防止重复插入数据,浪费空间;
-
域完整性:保证列的数据类型和约束;
-
参照完整性:保证一个表外键与另一个表的主键相关联,要求关系中不允许引用不存在的实体。
数据完整性只能尽量保证插入的数据是正确的, 不能百分百保证数据正确
-
-
多表设计
一对多:避免数据的冗余,节约空间
多对多:避免数据的冗余,节约空间
一对一:为了方便扩展,可以扩展成一对多,也可以扩展成多对多
-
复杂查询
#复杂查询 create table hero ( -> id int auto_increment, -> name char(20), -> age int, -> primary key(id) -> ); #把某字段设置为主键后, 默认不为NULL,设置主键的列,不能有重复值 insert into hero (name, age) values('yasuo', 5), ('jie', 7), ('nuoshou', 7); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from hero; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | timo | 21 | | 2 | dema | 29 | | 3 | yasuo | 5 | | 4 | jie | 7 | | 5 | nuoshou | 7 | +----+---------+------+ 5 rows in set (0.00 sec) #某字段设置为自动增长后,不赋值该字段,那么它会接着之前表中已有最大的值继续增加。 #创建装备表,并且把hero_id设置为外键,关联hero表的id create table equipment ( -> id int auto_increment primary key, -> name char(20) not NULL, -> price float, -> hero_id int, -> foreign key(hero_id) references hero(id) -> ); #删除含有外键约束的表中数据,如果不解除外键,无法删除 delete from hero where name = 'timo'; delete from hero; truncate table hero; drop table hero; #以上方法都不能删除 #只复制表数据,不复制表结构 create table hero1 select * from hero; #只复制表结构,不复制表中数据 create table hero2 like hero; #对一个没有设置主键的表设置主键 alter table hero1 modify id int primary key; #对一个没有外键的表设置外键 alter table equipment add foreign key(hero_id) references hero1(id); #设置外键的时候,关联的必须是另一个表的主键,而且类型需要相同, #可以主动设置外键名 alter table equipment1 add CONSTRAINT equipment1_foreign_key FOREIGN KEY(hero_id) REFERENCES hero1(id); #删除外键 alter table equipment1 drop FOREIGN KEY equipment1_ibfk_1; #查看当前是在哪个数据工作 select database(); #删除主键 alter table hero3 drop primary key; #如果有外键关联该表的主键,是不能删除该表的主键 #交叉查询 select * from hero cross join equipment ; #内连接查询 select * from hero inner join equipment on hero.id = equipment.hero_id; #隐式内连接不加inner join select * from hero, equipment where hero.id = equipment.hero_id; #可以对查询的表名起别名as select * from hero as h inner join equipment as e on h.id = e.hero_id; #左外连接(以左表为基准) select * from hero left outer join equipment on hero.id = equipment.hero_id; #右外连接(以右表为基准) select * from hero right outer join equipment on hero.id = equipment.hero_id; #子查询 select *from hero where id =(select id from hero where name like '%suo'); #联合查询(结果会去重) select age from hero where id > 3 union select age from hero where age < 10; #对联合查询结果不去重union all select age from hero where id > 3 union all select age from hero where age < 10; +------+ | age | +------+ | 7 | | 7 | | 5 | | 7 | | 7 | +------+ 5 rows in set (0.00 sec) mysql> select *from hero; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | timo | 21 | | 2 | dema | 29 | | 3 | yasuo | 5 | | 5 | nuoshou | 7 | | 6 | jie | 7 | +----+---------+------+ 5 rows in set (0.00 sec) #报表查询 select age from hero group by age; #统计函数和报表查询联合使用 select count(age),age from hero group by age; #group by后面的组要是同样的, 不然查不到 #查询表中最大的年纪 select max(age) from hero; #同时查询最大值和最小值 select min(age), max(age) from hero; +----------+----------+ | min(age) | max(age) | +----------+----------+ | 5 | 29 | +----------+----------+ #对报表查询做条件查询group by ... having ... select count(age), age from hero group by age having age < 10; +------------+------+ | count(age) | age | +------------+------+ | 1 | 5 | | 2 | 7 | +------------+------+ 2 rows in set (0.00 sec) #对查询出来的临时表再次查询 select count(age), age from hero group by age having count(age) > 1; +------------+------+ | count(age) | age | +------------+------+ | 2 | 7 | +------------+------+ 1 row in set (0.00 sec)
-
数据库的备份:
在命令行输入sudo mysqldump -u root -p 39th > 39th.sql
数据库的恢复:
1.进入数据库,创建该库(create database + 库名)
2.退出数据库,在命令行输入sudo mysql -u root -p 39th < 39th.sql
-
Linux和Windows数据库的传输:
Linux下安装:sudo apt install lrzsz
发送到Windows:sz myDataBase.sql
删除备份库:rm myDataBase.sql
从Windows恢复:rz
-
数据库编程的接口在https://www.mysql.com查找,c运行数据库代码加动态库-lmysqlclient
-
例子
#include <mysql/mysql.h> #include <string.h> #include <stdio.h> #include <head.h> int main(int argc,char* argv[]) { MYSQL *conn; MYSQL_RES *res; MYSQL_ROW row; char *server = "localhost"; char *user = "root"; char *password = "wz2021"; char *database = "39th"; //要访问的数据库名称 char query[300] = "select * from hero where name = '"; unsigned int queryRet; sprintf(query, "%s%s%s", query, argv[1], "'"); puts(query); /* strcpy(query,"select * from hero"); */ //在输出前先打印查询语句 //初始化 conn = mysql_init(NULL); if(!conn) { printf("MySQL init failed\n"); return -1; } //连接数据库,看连接是否成功,只有成功才能进行后面的操作 if(!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) { printf("Error connecting to database: %s\n", mysql_error(conn)); return -1; }else{ printf("MySQL Connected...\n"); } //把SQL语句传递给MySQL queryRet = mysql_query(conn, query); if(queryRet) { printf("Error making query: %s\n", mysql_error(conn)); } else { //用mysql_num_rows可以得到查询的结果集有几行 //要配合mysql_store_result使用 //第一种判断方式 res = mysql_store_result(conn); printf("mysql_num_rows = %lu\n", (unsigned long)mysql_num_rows(res)); //第二种判断方式,两种方式不能一起使用 /* res = mysql_use_result(conn); */ row = mysql_fetch_row(res); if(NULL == row) { printf("Don't find any data\n"); } else { do { /* printf("num=%d\n",mysql_num_fields(res));//列数 */ //每次for循环打印一整行的内容 for(queryRet = 0; queryRet < mysql_num_fields(res); ++queryRet) { printf("%8s ", row[queryRet]); } printf("\n"); }while(NULL != (row = mysql_fetch_row(res))); } mysql_free_result(res); } mysql_close(conn); return 0; } else{ do{ /* printf("num=%d\n",mysql_num_fields(res));//列数 */ //每次for循环打印一整行的内容 for(queryRet = 0; queryRet < mysql_num_fields(res); ++queryRet) { printf("%8s ", row[queryRet]); } printf("\n"); }while(NULL != (row = mysql_fetch_row(res))); } mysql_free_result(res); } mysql_close(conn); return 0; }