wordpress二次元动漫,网站不同时期的优化工作该怎么做,阿里巴巴国际贸易网站官网,赣州企业网一、在Ubuntu系统下安装MySQL数据库
1、更新软件源#xff0c;在确保ubuntu系统能正常上网的情况下执行以下命令
sudo apt-get update
2、安装MySQL数据库及相关软件包
# 安装过程中设置root用户的密码 123456
sudo apt-get install mysql-server
# 安装访问数据库的客…一、在Ubuntu系统下安装MySQL数据库
1、更新软件源在确保ubuntu系统能正常上网的情况下执行以下命令
sudo apt-get update
2、安装MySQL数据库及相关软件包
# 安装过程中设置root用户的密码 123456
sudo apt-get install mysql-server
# 安装访问数据库的客户端
sudo apt-get install mysql-client
# 安装访问数据库的代码库
sudo apt-get install libmysqlclient-dev
4、配置数据库的字符集、开启网络连接
1、打开MySQL数据库的配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
2、开启mysql的网络连接注释掉以下配置
# bind-address 127.0.0.1
3、在文件38行后面添加以下配置
lc-messages-dir /usr/share/mysql
character_set_serverutf8
skip-external-locking
4、保存退出并重启数据库
sudo service mysql restart
5、创建数据库、创建新用户
1、在终端登录MySQL的root用户
mysql -uroot -p123456
2、进入MySQL的root用户创建数据库
mysql create database testDB;
3、创建test用户设置访问权限设置用户密码为123456
mysql grant all privileges on testDB.* to test% identified by 123456;
4、刷新保存设置
mysql flush privileges;
5、退出登录
mysql exit
6、测试MySQL数据库
1、在终端登录MySQL的test新用户
mysql -utest -p123456
2、选择要使用的数据库
mysql use testDB;
3、创建一张Student数据表
mysql create table Student(name char(20),sex char,age int,addr varchar(100));
4、向Student数据表中插入一条数据
mysql insert into Student values(hehe,w,18,杭州指针信息技术有限公司);
5、查询Student数据表中的所有数据
mysql select * from Student;
二、数据库介绍
1、为什么需要数据库
1、计算机的资源有限不可能把数据全部存储在内存中且内存掉电后数据会丢失为了能让程序在关机重启后继续使用必须把数据保存到磁盘的文件中。
2、随着程序的功能越来越复杂、数据量越来越大从文件中读写数据需要大量的重复性操作从文件中读取出指定的数据需要复杂的逻辑。
3、不同的程序它的访问文件的操作不同就意味着读写文件的代码无法复用。
4、所以程序员非常需要一个统一的快速的访问磁盘数据的工具。
5、使用数据库程序员不需要自己管理数据而是通过数据库提供的接口进行读写操作至于数据在文件中是如何保存、查找与程序员无关。
2、什么是数据库
数据库指的管理数据的软件DBMS而不是存储数据的仓库。
3、数据库的类型
关系型
关系型数据库是指采用了关系模型来组织数据的数据库其以行和列的形式存储数据以便于用户理解关系型数据库这一系列的行和列被称为表一组表组成了数据库。用户通过查询来检索数据库中的数据而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。
非关系型redis
非关系型数据库严格上不止一种数据库应该是一种数据结构化存储方法的集合可以是文档或者键值对等。
NoSQL泛指非关系型的数据库NoSQL最常见的解释是“non-relational” “Not Only SQL”也被很多人接受。NoSQL仅仅是一个概念泛指非关系型的数据库区别于关系数据库。
NoSQL是一项全新的数据库革命性运动其拥护者们提倡运用非关系型的数据存储相对于铺天盖地的关系型数据库运用这一概念无疑是一种全新的思维的注入。
4、目前主流关系型数据库
商用数据库OceanBaseOracleSQL ServerDB2GaussDB
开源数据库MySQLMariaDB
桌面数据库Access
嵌入式数据库SQLite 三、SQL语言介绍
1、什么是SQL
SQL是结构化查询语言的缩写是数据库的标准委员会用来访问和操作数据库的统一语言所有的数据库都支持SQL语言也就是我们只需要学习SQL语言就可以操作所有的关系型数据库。
虽然ANSI组织定义了统一的SQL语言标准但不同的数据库厂商对SQL的支持程度不同有的还添加了新的语法如果只使用标准的SQL语句理论上可以操作所有的数据库然后把每种数据库特有的SQL语法称为SQL的方言。
SQL语句不区分大小写但标识符(表名、字段名)是区别的。
2、SQL语句有功能分类
数据控制语句用于权限的赋予和回收。
数据定义语句用于建立、修改、删除数据库对象(表、视图等)。
数据操作语句用于改变表中的数据(增、删、改)。
数据查询语句根据不同的条件来查询数据。
事务控制语句用于维护数据库的一致性。
3、SQL语句中的数据类型 MySQL 支持多种类型大致可以分为三类数值、日期/时间和字符串(字符)类型。 数值类型
1、MySQL 支持所有标准 SQL 数值数据类型。
2、这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。
3、关键字INT是INTEGER的同义词关键字DEC是DECIMAL的同义词。
4、BIT数据类型保存位字段值并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。
5、作为 SQL 标准的扩展MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
类型byte范围有符号无符号最大值TINYINT1-128,127255SMALLINT2-327683276765535MEDIUMINT3-8388608838860716777215INT4-214748364821474836474294967295BIGINT8-9,223372036854775808922337203685477580718446744073709551615FLOAT4(-3.402823466E38-1.175494351E-38) (1.175494351E-383.402823466351E38)1.175494 351E-383.402 823 466 E38DOUBLE8(-1.7976931348623157E308-2.225073858507 2014E-308) (2.2250738585072014 E-3081.797 6931348623157 E308)2.225073 858507201 4 E-3081.797693134 862 3157E308 日期和时间类型
1、表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
2、每个时间类型有一个有效值范围和一个零值当指定不合法的MySQL不能表示的值时使用零值。
3、TIMESTAMP类型有专有的自动更新特性将在后面描述。
类型byte范围格式用途DATE31000-01-01/9999-12-31YYYY-MM-DD日期值TIME3-838:59:59/838:59:59HH:MM:SS时间值或持续时间YEAR11901/2155YYYY年份值DATETIME81000-01-01 00:00:00 到 9999-12-31 23:59:59YYYY-MM-DD hh:mm:ss混合日期和时间值TIMESTAMP41970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07YYYY-MM-DD hh:mm:ss混合日期和时间值时间戳
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型byte用途CHAR0-255定长字符串VARCHAR0-65535变长字符串TINYBLOB0-255不超过 255 个字符的二进制字符串TINYTEXT0-255短文本字符串BLOB0-65535二进制形式的长文本数据TEXT0-65535长文本数据MEDIUMTEXT0-16777215中等长度文本数据MEDIUMBLOB0-1677215二进制形式的中等长度文本数据LONGBLOB0-4294967295二进制形式的极大文本数据LONGTEXT0-4294967295极大文本数据
注意
1、char(n) 和 varchar(n) 中括号中 n 代表字符的个数并不代表字节个数比如 CHAR(30) 就可以存储 30 个字符。
2、CHAR 和 VARCHAR 类型类似但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
3、BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR不同的是它们包含二进制字符串而不要非二进制字符串。也就是说它们包含字节字符串而不是字符字符串。这说明它们没有字符集并且排序和比较基于列值字节的数值值。
4、BLOB 是一个二进制大对象可以容纳可变数量的数据。有 4 种 BLOB 类型TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
5、有 4 种 TEXT 类型TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型可存储的最大长度不同可根据实际情况选择。
tinyint
smallint
int
bigint
float
double
date
timestamp
char
varchar 三、MySQL数据库的访问方式
1、本地访问
# 方便但有泄露密码的风险
mysql -u用户名 -p密码
# 建议这样登录
mysql -u用户名 -p
2、远程访问
1、先使用 ssh 或 telnet 远程登录MySQL服务器ssh 用户名ip 输入密码
2、mysql -u用户名 -p
3、图形客户端登录
sudo apt install mysql-workbench
1、创建连接 2、填写连接参数 四、数据定义语句
1、创建表
create table 表名(字段名 字段类型,...);
-- 创建一个聊天室的用户表
create table chat_user(user_name char(20),passwd char(8),email char(32)
);
-- 查看表结构
desc 表名;
注意如何把结构转换成数据表
2、修改表
-- 修改表名
rename table 旧表名 to 新表名;
rename table User to chat_user;
-- 增加列如果表已有数据新添加的字段具有非空的要求则添加失败
alter table 表名 add(字段名 字段类型);
-- 删除列如果表中已有数据则该字段的数据会一起删除
alter table 表名 drop 字段名;
-- 修改列如果表中已有数据新的字段如果能兼容之前的数据则修改成功否则修改失败
alter table 表名 modify 字段名 新类型;
alter table 表名 change 旧字段名 新字段名 新类型;
总结1、把表结构设计完善后再添加数据。2、尽量不要修改旧表的结构而设计一张新的表让他们建立联系。
3、删除表
-- 删除表数据保留表结构清空表
truncate 表名;
truncate Student;
-- 删除表数据和结构都删除
drop table 表名;
drop table Student; 五、数据操作语言
1、插入数据
-- 按全字段顺序插入
insert into 表名 values(数据);
-- 指定字段插入
insert into 表名(字段名,...) values(数据,...);
注意在设计表时某些字段为设置一些约束条件如果插入的数据不满足这些条件(非空、唯一)则插入失败。
2、删除数据
delete from 表名 where 条件;
delete from chat_user;
delete from chat_user where nick二师兄;
delete from chat_user where level 2;
-- 注意如果不写where则整张表全部删除所以为了安全MySQL数据库默认不支持该操作
-- 查看当前连接是否开启数据保护
show variables like sql_safe%;
-- 开启安全保护禁止使用非主键字段作为删除条件
set sql_safe_updateson;
-- 关闭数据安全保护任何条件都可以作为删除条件
set sql_safe_updatesoff;
3、修改数据
-- 注意如果不写where则整张表的字段数据全被修改。
update 表名 set 字段数据,... where 条件; 六、数据查询语句
select 字段1,字段2,... from 表名;
-- *在SQL中也是通配符代表所有字段
select * from chat_user;
select nick,level from chat_user; 七、事务控制语句
1、commit 提交
一个数据库会被若干个客户端同时访问数据库的底层为了保护数据的完整性修改数据时会加锁保护。
理论上每个客户端修改一次数据都要加一次锁但频繁的加锁会降低数据库的运行速度所以数据设计一种确认修改的动作。
客户端对数据库进行若干次修改了数据库不会立即修改硬盘上的数据而是把修改过的数据暂存客户端直接客户端执行了确认修改的命令此时数据库才会加锁然后把修改后的数据更新到硬盘上。
在数据库一个用户插入一条数据时只有它自己能查询到其它用户并不能立即看到只有执行了commit语句后其它用户才能看到。
2、rollback
当用户对数据进行修改后如果发现操作错误可以使用rollback语句返回到上一次commit;
使用commit的优点
1、让数据库批量执行写操作提高了数据库的操作效率。
2、降低硬盘的读写次数提高硬盘的寿命。
3、MySQL中的自动提交
-- 查询当前登录的自动提交是否开启
show variables like autocommit;
set session autocommit 0|1; 关闭或开启当前登录的自动提交。
set global autocommit 0|1; 关闭或开启所有登录的自动提交需要root用户才能执行。
4、设置保存点
使用rollback取消操作时会取消所有的操作直接回到上次commit的时刻但这样可以会浪费一部分有意义的操作可以在一个的阶段设置在保存点让rollback返回到指定的位置。
insert into chat_user values(二师兄,123123,13388666688,1);
savepoint s1;
insert into chat_user values(二师兄,123123,13388666687,2);
savepoint s2;
insert into chat_user values(二师兄,123123,13388666686,3);
savepoint s3;
insert into chat_user values(二师兄,123123,13388666685,4);
savepoint s4;
insert into chat_user values(二师兄,123123,13388666684,5);
select * from chat_user;
rollback to savepoint s3;
select * from chat_user; 八、使用C连接并操作数据库 所有MySQL数据库的C语言接口就声明在mysql/mysql.h头文件中但前提是安装libmysqlclient-dev库。 1、初始化MYSQL对象
MYSQL *mysql_init(MYSQL *mysql)
功能分配或初始化与mysql_real_connect()相适应的MYSQL对象。
mysql1、参数是NULL指针该函数将分配、初始化、并返回新对象2、参数是MYSQL对象地址将初始化对象并返回对象的地址。
返回值成功初始化的MYSQL*句柄。如果无足够内存以分配新的对象返回NULL。错误在内存不足的情况下返回NULL。
注意如果mysql_init分配了新的对象当调用mysql_close来关闭连接时将释放该对象。
2、连接数据库
MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag);
mysql已有MYSQL结构的地址调用mysql_real_connect()之前必须调用mysql_init()来初始化MYSQL结构。
host主机名或IP地址。如果“host”是NULL或字符串localhost连接将被视为与本地主机的连接。如果操作系统支持套接字Unix或命名管道Windows将使用它们而不是TCP/IP连接到服务器。
user用户名
passwd用户的密码
db是数据库名称
port如果不是0其值将用作TCP/IP连接的端口号host参数决定了连接的类型。
unix_socket该字符串描述了应使用的套接字或命名管道注意“host”参数决定了连接的类型。
client_flag值通常为0但是也能将其设置为下述标志的组合。
返回值如果连接成功返回MYSQL*连接句柄,与第1个参数的值相同。如果连接失败返回NULL。
2、设置字符集、获得当前连接的字符集
int mysql_set_character_set(MYSQL *mysql, char *csname);
如果查询成功返回0。如果出现错误返回非0值。
const char *mysql_character_set_name(MYSQL *mysql);
3、发送SQL指令
int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length)
如果查询成功返回0。如果出现错误返回非0值。
4、获取查询结果
MYSQL_RES *mysql_store_result(MYSQL *mysql)
功能将查询的全部结果读取到客户端分配1个MYSQL_RES结构并将结果置于该结构中。
返回值结果为NULL语句执行出现错误或执行的是没有结果的SQL语句如INSERT语句。通过检查mysql_error()是否返回非空字符串mysql_errno()是否返回非0值或mysql_field_count()是否返回0可以检查是否出现了错误。
返回值结果不为NULL则需要调用以下函数
my_ulonglong mysql_num_rows(MYSQL_RES *result)
功能返回结果集中的行数。unsigned int mysql_num_fields(MYSQL_RES *result)
功能返回结果集中的列数MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)
功能从结果集中读取一行数据void mysql_free_result(MYSQL_RES *result)
功能释放结果集
5、受影响或检索的记录行数
unsigned long long mysql_affected_rows(MYSQL *mysql);
功能返回上次UPDATE更改的行数上次DELETE删除的行数或上次INSERT语句插入的行数。对于UPDATE、DELETE或INSERT语句可在mysql_query()后立刻调用。对于SELECT语句mysql_affected_rows()的工作方式与mysql_num_rows()类似。
返回值大于0的整数表明受影响或检索的行数。0表示UPDATE语句未更新记录在查询中没有与WHERE匹配的行或未执行查询。-1表示查询返回错误或者对于SELECT查询在调用mysql_store_result()之前调用了mysql_affected_rows。由于mysql_affected_rows返回无符号值通过比较返回值和(my_ulonglong)-1或等效的(my_ulonglong)~0检查是否为“-1”。
6、commit提交
my_bool mysql_autocommit(MYSQL *mysql, my_bool mode)
mode:为真启用autocommit模式为假禁止autocommit模式。
返回值如果成功返回0如果出现错误返回非0值。
my_bool mysql_commit(MYSQL *mysql)
功能提交当前事务。
返回值如果成功返回0如果出现错误返回非0值。
7、错误编号、原因
unsigned int mysql_errno(MYSQL *mysql)
功能获取错误编号如果没有错误则返回0const char *mysql_error(MYSQL *mysql)
功能获取错误原因如果没有错误则返回NULL
8、编译时需要添加参数
# 用的库是libmysqlclient-dev所以编译时需要添加参数
gcc xxx.c -lmysqlclient
任务1使用以上API实现一个mysql命令。
任务2使用数据库实现通讯录项目。
增、删、改、查、列出直接针对数据库进行操作。 九、高级查询
1、排重 distinct
-- 在数据表中一个字段的值可能有多个重复的distinct 可以排除重复数据如果多个字段查询那么所有查询的字段值都一样才算重复
select distinct 字段,... from 表名;
select distinct nick from chat_user; 2、算术运算符
-- select 语句查询时可以对表中的数值字段直接进行算术运算如果想改变运算的优先级可以使用小括号。
select 字段 - * / % from 表名;
select level/3 from chat_user; 3、where子句
-- 在where字句中可以使用关系运算符和逻辑运算符只有条件为真的数据才会显示
select 字段 from 表名 where 条件;
关系运算符 !
逻辑运算符and or not || ! 可以继续使用特殊条件between a and b 判断一个范围使用判断运算符加逻辑运算符也能达到同样的效果[a,b]。is null、is not null 判断字段值是不是为空在数据表中空值是一种状态而不是一个特定的值。select * from chat_user where phone is null;in (a,b,c,...) 当字段的值出现在in的范围列表中则为真。select * from chat_user where level in (1,3,5,7,9);like %str_ 模糊查询Linux系统命令行中使用的通配符字符型字段使用才合适select * from chat_user where phone like 183%;% 匹配任意多个字符_ 匹配一个字符
注意判断字段值是不为空在数据表中空值是一种状态而不是一个特定的值。 4、排序
select 字段 from 表名 order by 字段 [asc|desc];asc 升序从小到大默认desc 降低从大到小 select * from chat_user order by phone;
-- 注意MySQL数据库在排序时把空值当作最小值Oracle数据库把空值当作了最大值。
-- 排序与where子句配合
select 字段 from 表名 where 条件 order by 字段 [asc|desc];
select * from chat_user where phone is not null order by phone desc; 5、分页查询
使用SELECT查询时如果结果集数据量很大比如几万行数据放在一个页面显示的话数据量太大不如分页显示每次显示100条。
要实现分页功能实际上就是从结果集中显示第1~100条记录作为第1页显示第101~200条记录作为第2页以此类推。因此分页实际上就是从结果集中“截取”出第M~N条记录。
select 字段 from 表名 limit 一页的记录数 offset 要跳过的记录数;
select * from chat_user limit 10 offset 0;
假设一页m条数据
select * from chat_user limit m offset (页数-1)*m; 6、连接查询(多表查询)
当需要的数据分布在不同的表中就需要多张表连接查询无条件的连接会产生笛卡乐积有海量的无效数据需要配合where子句进行连接。
-- 班级表
create table class(class_id int,class_name char(20),teacher_id int,room_id int
);
-- 教师表
create table teacher(work_id int,name char(20),sex char(1),phone char(11)
);
-- 学生表
create table student(id int,name char(20), sex char(1),class_id int
);
select 字段 from 表1,表2 where 表1.key 表2.key;
-- 表中的字段可能会重名需要 表名.字段进行区分;
select 表名.字段 from 表1,表2 where 表1.key 表2.key;
根据where条件和连接结果连接查询分为以下几种 -- 内连接
查询出每班级的教师叫什么名字
select class_name,name from class,teacher where work_id teacher_id;
-- 左内连接
查询出每班级的教师叫什么名字,没有分配教师的班级也显示
select class_name,name from class left join teacher on work_id teacher_id;
-- 右内连接
查询出每班级的教师叫什么名字,没有教学任务的教师也显示
select class_name,name from class right join teacher on work_id teacher_id;
-- 左外连接
查询出没有分配教师的班级
select class_name,name from class left join teacher on work_id teacher_id where teacher_id is null;
-- 右外连接
查询出没有分配教学任务的教师
select class_name,name from class right join teacher on work_id teacher_id where class_name is null;
-- 全连接
select class_name,name from class full outer join teacher on work_id teacher_id;
select class_name,name from class left join teacher on work_id teacher_id union
select class_name,name from class right join teacher on work_id teacher_id where class_name is null;
-- 外连接
select class_name,name from class left join teacher on work_id teacher_id where teacher_id is null union
select class_name,name from class right join teacher on work_id teacher_id where class_name is null;
全连接、外连接MySQL不支持该语法可以使用union关键字把左外连接、右外连接联合在一起实现外连接、全连接。左外连接 union 右外连接 外连接;左连接 union 右连接 全连接; 7、取别名
在多表查询时由于表名在访问重名字段时语句过长可以给表名取一个简单的别名也可以解决一张表自连接的查询。
select 别名.字段 from 表名 as 别名;
问题查询出每个学生的上课时的教室。
select name,room_id from class as c,student as s where c.class_id s.class_id;
select name,room_id from class as c,student as s where class.class_id student.class_id; 8、子查询
把一个查询结果作为另一个查询语句的基础这种查询叫子查询或嵌套查询。
-- 在SQL中可以把select的查询结果当作一张内存表。
查询出没有分配教学任务的教师并显示它的ID
select work_id from class right join teacher on teacher_id work_id where teacher_id is null;
查询出未分配教学任务的教师姓名、性别、手机号
select name,sex,phone from teacher where work_id in (select work_id from class right join teacher on teacher_id work_id where teacher_id is null); 9、MySQL中的函数
-- 普通函数一条记录就会产生一个结果而组函数一次查询只产生一个结果这两种函数不能混用
count 计数
max 求最大
min 求最小
sum 求和
avg 求平均
MySQL 函数 | 菜鸟教程
注意MySQL中有丰富的数据处理函数但程序员也可以先把查询到的结果转换成相关的数据类型再使用编程语言中的数据处理函数。 10、分组查询
把表中的数据按照标准分为不同的组。
select 分组标准或组函数处理过的数据 from 表 group by 字段;
查询出每个班级的学生数量
select class_id,count(id),max(id),min(id) from student group by class_id;
查询出每个班级的班级名授课教师名学生数量
select class_name,teacher.name,count(id) from
class,teacher,student where work_idteacher_id student.class_idclass.class_id
group by class_name,teacher.name;
查询出每个班级的班级ID、班级名授课教师名学生数量
select class.class_id,min(class_name),min(teacher.name),count(student.id) from class,teacher,student where work_idteacher_id student.class_idclass.class_id group by class.class_id;
select class_name,teacher.name,count(id) from
class
left join teacher
on work_id teacher_id
left join student on student.class_idclass.class_id
group by class_name,teacher.name;
过虑分组后的数据:
select 分组标准或组函数处理过的数据 from 表 group by 字段 having 条件;
注意having的条件字段必须分组标准或组函数处理过的字段。
查询出每个1004班级的班级名授课教师名学生数量
select class.class_id,class_name,teacher.name,count(id) from class left join teacher on work_id teacher_id left join student on student.class_idclass.class_id group by class_name,teacher.name,class.class_id having class.class_id 1004; 复杂语句的执行顺序
select 组函数(字段)|或分组标准from 表名where 条件 | 连接标准group by 分组标准having 过虑条件order by 排序标准; 查询出班级人数在3人以上的班级显示班级名班级ID班级人数并且对按班级人数进行排序。
select student.class_id,max(class_name),count(name) from student,class where student.class_idclass.class_idgroup by student.class_idhaving count(name) 3order by count(name);
注意在MySQL客户端里使用CtrlB 可以格式化SQL语句。 十、设计表
1、数据库设计的三 大范式
在进行数据库设计时制定的一些规则称为数据库设计范式 遵守这些规则将创建出良好的数据库如经常听到的数据库三大范式。
第一范式Normal From,1NF确保每列的原子性
如果每列都是不可再分的最小数据单元则满足第一范式。
例如顾客表(姓名、编号、地址、……)其中地址列还可以细分为国家、省、市、区等所以按照第一次范围地址更改为地区编号(例如身份证号的前6位)。
第二范式Normal From,2NF在第一范式的基础上更进一层目标是确保表中的每列都和主键相关
如果一个关系满足第一范式并且除了主键以外的其它列都依赖于该主键则满足第二范式.
例如订单表(订单编号、产品编号、产品数量、定购日期、产品价格、……)订单编号为主键产品价格和主键列没有直接的关系即产品价格列不依赖于主键列应删除然后连接产品表根据产品编号获得该项数据。
第三范式Normal From,3NF在第二范式的基础上更进一层目标是确保每列都和主键列直接相关而不是间接相关
如果一个关系满足第二范式并且除了主键以外的其他列都只能依赖主键列列与列之间不存在相互依赖关系则满足第三范式。
例如订单表(订单编号定购日期顾客编号顾客姓名……)初看该表没有问题满足第二范式每列都和主键列订单编号相关再细看你会发现顾客姓名和顾客编号相关为了满足第三范式应去掉顾客姓名列放入客户表中。
总结字段不可再分字段跟主键都有关系字段与主键有强直接关系遵循这三范式能让数据库中的表更灵活、强大、节约存储空间但并不能保证查询速度最快所以在实际开发过程中会突破三范式牺牲存储空间以达到速度最优。
2、约束
约束是对数据和表的限制可以提高表中数据的准确性和可靠性一般在创建表、修改表时使用在已有数据的情况下修改表的约束不一定能成功。
约束的分类
not null 非空字段的数据不能为空
unique 唯一字段的数据不能重复
primary key 主键(非空且唯一)
foreign key 外键依赖B表某个字段值必须在A表中的某个字段出现过
default 默认值给字段设置完默认值后当插入数据不提供该字段的数据值数据库自动填充默认值
check 检查设置一个条件判断当数据不满足条件时插入失败但MySQL数据库不支持
drop table teacher;
create table teacher(work_id int primary key,name char(20) not null,sex char NULL default w,enter_time timestamp NULL default CURRENT_TIMESTAMP
);
drop table class;
create table class(class_id int primary key,class_name char(20) not null,enter_time timestamp NULL default CURRENT_TIMESTAMPteacher_id int,room_id int unique not null
);
-- 一般在创建表的时候设置外键先建父表再建子表
create table 父表(字段名 类型 primary key,
);
create table 子表(...foreign key(字段) REFERENCES 父表(字段)
);
drop table student;
约束的设置方式
-- 创建表时设置
create table 表名(字段名 类型 约束,
);
-- 修改表时设置
alter table 表名 modify 字段 类型 约束;
外键约束
一张表子表的值引用自另一张表父表被引用的字段必须具备唯一性子表中的外键字段的值必须来自父表或者是null值。
-- 一般在创建表的时候设置外键先建父表再建子表
create table 父表(字段名 类型 primary key,
);
create table 子表(...foreign key(字段) REFERENCES 父表(字段)
);
-- 也可以后期添加一般不建议使用
alter table 子表 add foreign key(子表字段) REFERENCES 父表(父表字段);
-- 班级表 父表
drop table class;
create table class(class_id int primary key,name char(20) unique not null);-- 学生表 子表
drop table student;
create table student(student_id int primary key,name char(20) unique not null,class_id int, foreign key(class_id) REFERENCES class(class_id))
插入数据
先插入父表数据再插入子表数据。
更新或删除数据
默认情况下
删除父表数据时先删除子表数据再删除父表数据。
更新父表数据时父表插入新数据修改子表数据再删除父表中数据。
设置级联删除级联更新
on delete cascade 级联删除删除父表时子表中的数据会一起删除
on update cascade 级联更新更新父表时子表中的数据会一起删除
注意级联删除、更新必须在创建子表时设置才有效。
create table student(student_id int primary key,name char(20) unique not null,class_id int, foreign key(class_id) REFERENCES class(class_id) on delete cascade on update cascade
); 3、自动增长的字段
这种字段可以不用手动插入值由系统提供默认的序列值但必须满足以下要求
1、只有主键才能设置
2、必须是数值型字段
3、一张表最多只能设置一个
设置方法
-- 创建表时设置
create table 表名(字段 类型 primary key auto_increment,
);
-- 修改表时设置
alter table 表名 modify 字段 类型 primary key auto_increment;
alter table student modify id int primary key auto_increment;
初始值和步长
alter table 表名 auto_increment 初始值;
set auto_increment_increment value 设置步长;
-- 也可以通过第一次手动插入数据设置初始值 4、索引
索引是一种提高查询速度的技术如果把数据库看作字典那么索引就是字典的目录。
创建索引
create table 表名(...index [索引名] (字段,)
);
-- 注意创建的索引字段是经常在SQL语句的where字句条件上
添加索引
alter table 表名 add index [索引名] (字段名);
查看索引
show index from 表名;
-- 注意MySQL数据库会自动对表进行优化主键、非空且唯一的字段会自动优化成索引。
删除索引
drop index 索引名 on 表名;
索引的优点和缺点
1、能大大提高数据库的查询速度。
2、但索引的本质其实也是硬盘地址的表里面存储着字段数据所在的硬盘位置创建索引需要额外的存储空间是典型用空间换取时间。
3、而且使用索引虽然提高了查询速度但会降低插入、更新、删除数据的速度。
4、MySQL数据库会自动为主键创建索引所有在MySQL数据库中不建议主动创建索引。 5、视图
视图是一张虚拟的表它本身并不包含数据而是作为一个select语句保存在数据库中。
如果设计表时遵循了三大范式我们的数据库中会有很多张表(零散)查询数据时会有很多连接查询SQL语句就需要写的非常长非常麻烦。
视图就是把常用的连接查询语句存储到数据库中。
创建视图
create view 视图名 [字段名] as select语句;
create view class_view as select * from class,student where class.class_id student.class_id;
查看视图
desc 视图名;
删除视图
drop view 视图名;
使用视图的优、缺点
1、可以只展现基本表的部分数据不用关心基本表的结构;
2、使用视图的用户只能访问被允许访问的数据对数据库权限的管理只能精细到某张表但使用视图可以管理某些列的某些行可以大大提高数据的安全性。
3、视图创建完成后可以删除、添加列而视图不受影响。
4、速度慢无法修改视图中的数据只能读。 十、扩展
1、导出数据(备份)
mysqldump -uroot -p123456 -hlocalhost testDB testDB.sql
2、导入数据(恢复)
-- 登录数据库
mysql -utest -p
mysql use testDB;
-- 把备份的数据导入到当前数据库
mysql source /home/sunll/testDB.sql
3、SQL 注入
4、存储引擎
