数据库总结

程序员文章站 2022-05-07 14:22:49

...

1.参考资料

MySQL教程™

MySQL 教程

CyC2018/CS-Notes

2.数据库设计步骤

先根据文字描述找出主体与他们之间的关系，画出ER图
将ER图转化为数据模型，标出主码外码

1:n:将1的主键添加到n的表中作为外键

n:m :将两个的关系新建成表，再将两个实体的主键都插入到新表
规范每一张数据模型表：使它们都满足第三范式
为每个属性规划好字段，设置主键、是否为空、自增长
使用sql语句建表

数据库的三级模式结构

数据库总结

范式

函数依赖: X–>Y

解释: Y依赖X，对于每个X都只能有唯一的Y与之对应

部分函数依赖: X–>Y

解释: X的任何一个真子集都能函数依赖于Y，那么Y部分依赖X

第一范式(1NF):每个属性都不能再分

第二范式(2NF):没有部分函数依赖

第三范式(3NF):没有传递函数依赖

3.SQL语句分类

数据定义(DDL)

定义、删除、修改数据库
定义、删除、修改表(Table)、视图（View）
定义、删除、修改索引（Index）
关键字: CREAT ALTER DROP

数据操纵(DML)

数据增、删、改、查
关键字: INSERT UPDATA DELETE SELECT

数据控制(DCL)

用户访问权限的授予、收回
关键字: REVOKE GRANT

4.数据库完整性约束

[添加约束参考]

如果某字段设置为not null，则设置默认约束没有任何意义

一个属性如果是主键，not null没有必要设置

主码: PRIMARY KEY
唯一: UNIQUE
外码: foreign key···references···
非空: NOT NULL
缺省值: DEFAULT
核查: CHECK
自增: auto_increment

建外键约束

[资料参考]

语法: FOREIGN KEY (外键约束的列名) REFERENCES 主键表 (主键表中的主键列名)

级联更新、删除: ALTER TABLE <表名> ADD [CONSTRAINT <约束名>] Foreign Key(列名[,…n]) references 主表表名(主表的列名) [on update cascade][ on delete cascade];

create table sc(
sno char(7)          //注意:建立这个sno时名字可以与students表中sno名字不一样，但是字段一定要相同            
cno	char(7)             
score	decimal(4,1),
point	decimal(2,1),
primary key(sno,cno),   //设置组合属性为主码
foreign key (sno) references students(sno),     //外码建立
foreign key (cno) references course(cno)
);

5.Mysql常用字段

数值形字段

INT - 范围是从-2147483648到2147483647
TINYINT - 范围是从-128到127
Decimal（p,q) - 小数型

日期形字段

DATE - 以YYYY-MM-DD格式的日期，在1000-01-01和9999-12-31之间。例如，1973年12月30日将被存储为1973-12-30
DATETIME - 日期和时间组合以YYYY-MM-DD HH:MM:SS格式，在1000-01-01 00:00:00 到9999-12-31 23:59:59之间。例如，1973年12月30日下午3:30，会被存储为1973-12-30 15:30:00
TIME - 存储时间在HH:MM:SS格式
YEAR(M) - 以2位或4位数字格式来存储年份。如果长度指定为2(例如YEAR(2))，年份就可以为1970至2069(70〜69)。如果长度指定为4，年份范围是1901-2155，默认长度为4

字符型字段

CHAR(M) - 固定长度的字符串是以长度为1到255之间个字符长度(例如：CHAR(5))，存储右空格填充到指定的长度。限定长度不是必需的，它会默认为1
VARCHAR(M) - 可变长度的字符串是以长度为1到255之间字符数(高版本的MySQL超过255); 例如： VARCHAR(25). 创建VARCHAR类型字段时，必须定义长度

6.SQL语句语法&实例

语句结束后要加封号

一个数据库在打开的时候是不能删除的

CREAT

创建库: CREATE DATABASE <数据库名>;

创建表：CREATE TABLE IF NOT EXISTS 表名();

打开数据库: USE <数据库名>;

复制表结构: CREATE TABLE 新表名 LIKE 已有表名;

复制表结构&数据: CREATE TABLE 新表名 AS SELECT 语句;

//打开数据库
USE XSCJ;/*最好在开头加上这行代码，明确操作哪一个数据库*/

//创建表
CREATE TABLE IF NOT EXISTS students(  /*如果这个表不存在才建立这个表*/
	sno  char(7)   not  null,     //设置字段和约束。设置约束的第一种方式。
	sname   nvarchar(8)  not  null,       
	ssex    nchar(2)  not  null DEFAULT ‘男’,   
	score  decimal(4,1) ,
	class  nvarchar(10)  not null ,
	SID  char(18)  not  null ,
	PHOTO  varchar(50),

	PRIMARY KEY (task_id) //设置约束的第二种方式
);

//复制表结构
Create table student1 like students;

//复制表结构&数据
Create table student2 
             as select * from students;

LATER

修改表名: ALTER TABLE 旧表名 RENAME [TO] 新表名;

增加新列: ALTER TABLE 表名 ADD 属性名数据类型 [列的完整性约束];

修改表—修改字段名: ALTER TABLE 表名 CHANGE 旧属性名新属性名数据类型;

修改表—修改字段数据类型: ALTER TABLE 表名 MODIFY 属性名新数据类型;

修改表—删除字段: ALTER TABLE 表名 DROP 属性名;

在已创建的表中设置主键: Alter table 表名 Add primary key(字段名);

在已创建的表中设置唯一键: Alter table 表名 Add unique(字段名);

添加、修改的默认值: Alter table 表名 alter 列名 set default 默认值;

修改列的非空约束: Alter table 表名 Modify 列名数据类型 not null;

删除表的主键: ALTER TABLE 表名 DROP PRIMARY KEY;

删除表的唯一约束: ALTER TABLE 表名 DROP INDEX 唯一约束自动生成的索引名;

删除表的外键: ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

删除表: DROP TABLE <表名>;

//修改表名
ALTER  TABLE  studnet  RENAME  students;

 //增加新列。注意不要一行写到底
ALTER TABLE students      
       ADD phone VARCHAR(20) NOT NULL;

ALTER TABLE students 
			ADD num INT PRIMARY KEY FIRST; //在表的第一个位置增加字段

ALTER TABLE students 
			ADD address VARCHAR(30) NOT NULL AFTER phone;//表的指定位置之后增加字段

//修改表—修改字段名:
ALTER  TABLE  students  
       CHANGE  grade  score  int;//将grade字段改名为score

//修改表—修改字段数据类型
ALTER TABLE students
     MODIFY phone  char(13);//将phone字段的数据类型改为char（13）

//修改表—删除字段
ALTER  TABLE  students  
      DROP  sage;

//在已创建的表中设置主键
Alter table user
			Add primary key(userid);  //方式一

//在已创建的表中设置唯一键: 
Alter table user
			Add unique(email);

Alter table user
			Add primary key(userid),
			Add  unique(email);     //一次添加多个约束的方式

//添加、修改的默认值
Alter table user
			alter column password set default '12345';

//修改列的非空约束
Alter table user
			MODIFY password varchar(20) not null;

//删除表的主键
ALTER TABLE user DROP PRIMARY KEY;

//删除user表email列的唯一约束
ALTER TABLE user DROP INDEX email;

//删除user表userid列的外键
ALTER TABLE user 
			 DROP FOREIGN KEY userid;

//删除表
drop table book,reader,borrow; //一次删除多个表

DORP

删除数据库: DROP DATABASE <数据库名>;

DROP DATABASE XSCJ;

INSTER

INSERT INTO<表名> [(<列名表>)] VALUES （对应列的值）

//插入一行
INSERT INTO tasks(subject,start_date,end_date,description)
VALUES('Learn MySQL INSERT','2017-07-21','2017-07-22','Start learning..');

//插入多行
INSERT INTO tasks(subject,start_date,end_date,description)
VALUES ('任务-1','2017-01-01','2017-01-02','Description 1'),
       ('任务-2','2017-01-01','2017-01-02','Description 2'),
       ('任务-3','2017-01-01','2017-01-02','Description 3');

//如果为表中的所有列指定相应列的值，则可以忽略INSERT语句中的列列表
INSERT INTO table
VALUES (value1,value2,...),
       (value1,value2,...),
...;

DELETE

如果省略WHERE子句，DELETE语句将删除表中的所有行

DELETE FROM table_name
WHERE condition;

//使用LIMIT子句时，应始终使用ORDER BY子句
DELETE FROM customers
WHERE country = 'France'
ORDER BY creditLimit
LIMIT 5;

SELECT

1.单表查询

SELECT <目标列名序列>

FROM <数据源>

[WHERE <检索条件表达式>]

[ORDER BY <排序依据列>]

//根据生日计算年龄
(YEAR(CURDATE())-YEAR(bday))


//使用limit关键字
select  *  form  Books limit 10

//对price进行一个四则运算后
，price所形成的表达式将成为查询表的显示字段
SELECT bookID,bookname,price*0.9 FROM Books


//常量列，生日为常量。字段需要用加上单引号
SELECT rname,'生日',birthday FROM Readers

//as起别名
SELECT bno as 编号,bname 书名,author FROM Books

//where使用
SELECT * FROM Books
WHERE price BETWEEN 25 AND 50;

SELECT * FROM Books 
WHERE publish IN('上海人民出版社','清华大学出版社','高等教育出版社')

//模糊查询  '_'代表任意一个字符    '%'代表任意多个字符     Escape定义转义符
SELECT * FROM Readers
WHERE rname LIKE '/%王%' ESCAPE '/'     //搜索以 %王 开头的所有值

//IS NULL  查询藏书量为空的图书信息
SELECT * FROM Books 
WHERE csl IS NULL

//ORDER BY子句
SELECT * FROM Readers
ORDER BY birthday ASC   //默认是升序，可省略

SELECT * FROM Readers 
ORDER BY bday DESC  //降序排序

//多关键字排序:首先按第一关键字排序，当第一关键字相同的时候，会按照第二关键字排序
SELECT * FROM Books 
ORDER BY classid ASC,price DESC

//聚集函数
SELECT AVG(price) AS 平均价格 
FROM Books

//分组查询(GROUP BY 子句)    GROUP BY <列名>进行分组
SELECT spet 专业, COUNT(*)  //统计每个专业的读者的人数
FROM Readers 
GROUP BY spet

SELECT publish, COUNT(*)   //统计每个出版社的出版图书的数目
FROM Books 
GROUP BY publish

//分组查询(Having子句)   HAVING 子句可以对分组统计后的结果进行筛选
SELECT rno,COUNT(bno)
FROM borrow
GROUP BY rno
having COUNT(bno)>2    //having一定只能与GROUP BY出现

2.多表查询

第一种写法

Select 显示的列

FROM 表1 JOIN 表2 ON <连接条件>

Where 查询条件

Group by分组列

Having 组筛选条件

Order by 排序列

第二种写法

Select 显示的列

FROM 表1 , 表2

Where <连接条件> and <查询条件>

三表连接

Select 列名,……

FROM 表1 JOIN 表2 ON <连接条件>join 表3 on <连接条件>

3.多表连接类型

内连接: inner join on

select * from a_table a inner join b_table bon a.a_id = b.b_id;

外连接

左连接: left join on
右连接: right join on

自连接

SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

嵌套查询

SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2 FROM mytable2);

UPDATA

WHERE子句非常重要。少写WHERE会导致意外更新表中的所有行

UPDATE <表名>

SET <列名＝表达式> [,… n]

[FROM <更新条件所在的表>]

[WHERE <更新条件>]

UPDATE [LOW_PRIORITY] [IGNORE] table_name 
SET 
    column_name1 = expr1,
    column_name2 = expr2,
    ...
WHERE
    condition;


//一次修改多列
Update students 
set sname='李凯',bday='1994-10-11'   
Where sno='0801102'

聚集函数

COUNT(列名): 统计表中元组个数
COUNT(列名): 统计本列列值个数
SUM(列名）:计算列值总和（必须是数值型列）
AVG(列名）:计算列值平均值（必须是数值型列）
MAX(列名）: 求列值最大值
MIN(列名）: 求列值最小值

7.视图

视图查询

CREATE VIEW <视图名> [(视图列名表)]

AS

SELECT 查询语句

[WITH CHECK OPTION]

视图取自一个基本表一般可看可改

视图取自多个表一般可看不可改

子查询中含GROUP BY子句数据只可看不可改

表达式最好起一个列名

//单表查询
CREATE VIEW VBook1
AS
SELECT *
FROM Books
WHERE price>30

//多表查询
CREATE VIEW  R_B_Book2 (读者编号,姓名,图书编号,
书名,出版社,价格,借阅日期)
AS 
SELECT R.rno, R.rname, B.bno, B.bname, publish, 
price, lenddate
FROM readers as R JOIN borrow as W
ON R.rno = W.rno  JOIN Books as B
ON B.bno=W.bno

视图修改

修改视图是指修改数据库中已存在的表的定义(修改查询语句)

ALTER VIEW 视图名[(列名1[,…n])]
AS SELECT查询语句
[WITH CHECK OPTION]

//修改视图
ALTER VIEW v_sc_stat
AS
SELECT cno,COUNT(cno),COUNT(cno)
FROM sc
GROUP BY cno;

视图删除

DROP VIEW <视图名>

视图的作用

简化数据查询语句
使用户能从多角度看待同一数据
提高了数据的安全性
提供了一定程度的逻辑独立性

8.索引

索引类型

普通索引
唯一索引
全文索引（char、varchar、text型字段）

索引创建

使用ALTER TABLE创建

普通索引:ALTER TABLE table_name ADD INDEX index_name (column_list)

UNIQUE索引:ALTER TABLE table_name ADD UNIQUE (column_list)
使用CREATE INDEX创建

CREATE [UNIQUE | FULLTEXT ] {INDEX| KEY} 索引名

ON 表名 (列名 [ASC|DESC] [,…n])
创建联合索引:CREATE INDEX| 索引名 ON 表名(字段名1，字段名2)

//使用`ALTER TABLE`创建
ALTER TABLE course ADD INDEX ix_cname (cname);


//使用`CREATE INDEX`创建
//例：在表book的bookname字段上建立名为BkNameIdx的索引。
CREATE	INDEX	BkNameIdx   
ON	book (bookname);
//在book表的bookId字段上建立唯一索引
CREATE	UNIQUE	INDEX	UniqueIdx    
ON	book	(bookId);


//创建联合索引
CREATE INDEX ix_scs
ON students(sname,class,sdept);

查看索引

show index from <表名>

删除索引

方法一

DROP INDEX 索引名 ON 表名;
方法二

ALTER TABLE 表名 DROP INDEX 索引名;

注意：主键索引的删除有所不同

ALTER TABLE 表名 DROP PRIMARY KEY;

例：alter table book drop PRIMARY KEY;

9.存储过程

CREATE PROCEDURE 存储过程名 ([ IN | OUT | INOUT ] 参数名参数的数据类型)
routine_body

IN: 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回
OUT: 输出参数:该值可在存储过程内部被改变，并可返回
INOUT: 输入输出参数:调用时指定，并且可被改变和返回

存储过程实现

//创建存储过程
CREATE  PROCEDURE  sp_price(in pb char(30),out  avg  decimal(6,2) )
begin
    select  avg(price)  into  avg
    from  books
    where  publish=pb;
end

//  调用存储过程
call sp_price('清华大学出版社',@avgprice);		//avgprice是定义的一个局部变量
select @avgprice;

//删除存储过程
Drop  procedure  sp_name;

10. 数据库安全权限管理

1. 使用create user创建用户账号:

create user ‘user’@’localhost’ identified by [password] 'password'

[,user identified by [password] 'password'] …

2. 使用Insert语句建用户

实质就是在mysql.user表中插入一条语句

3. Grant语句

8.0版本后语句发生了改变[参考]

Grant 权限 on 对象 to 用户 indentified by ‘密码’

[with grant option]

4. 修改用户账号

要使用rename user语句，必须拥有mysql数据库的update权限或全局create user权限

rename user old_user to new_user [，old_user to new_user]

5. 修改用户口令

在命令行运行

mysqladmin修改

mysqladmin –u username p password

使用set语句来修改密码

set password [for 'username'@'hostname'] =password('new_password');

Update mysql.user表修改

Update mysql.user set password=‘新密码’

Where host=? and user=?;

6. 删除用户

用drop user语句来删除普通用户

drop user user[,user]…

使用delete语句来删除普通用户

delete from mysql.user where user='xiaoming' and host='localhost'

7. 权限授予

Grant 权限名[(列名)] on 对象 To 用户

[With grant option]

8. 收回权限

Revoke 权限 on 对象 from 用户

9. 查看权限

show grants FOR 'user'@'host';

//创建用户账号
create user 'xixixi'@'localhost' identified by 'xingkong22'

//Grant语句
grant select on jxgl.* to 'newuser'@'localhost' 
IDENTIFIED BY '111';

//修改用户账号
RENAME USER 'xixixi'@'localhost' to 'hahaha'@'localhost';

//修改用户口令
mysqladmin -uhahaha -p password

//使用set语句来修改密码
set password for 'xixixi'@'localhost' = password('123')

//授予用户newuser对jxgl数据库students表上sno列和sname列的查询权限
Grant select(sno,sname) on jxgl.students 
to ‘newuser’@’localhost’

//授予用户newuser对jxgl数据库students表上sno列和sname列的查询权限，并允许其将权限授予其他用户
Grant select(sno,sname) on jxgl.students 
to ‘newuser’@’localhost’ 
with grant option

//授予所有权限
Grant all on 对象 to 用户 [with grant option]
//mysql 8.0之后
GRANT ALL ON *.* TO 'Testuser1'@'localhost';

收回用户jack的所有的删除权限
//revoke delete on *.* from 'jack'@'localhost';

11. 备份恢复

Reference

mysql导入导出设置

备份类型

冷备（cold backup）：需要关mysql服务，读写请求均不允许状态下进行；
温备（warm backup）：服务在线，但仅支持读请求，不允许写请求；
热备（hot backup）：备份的同时，业务不受影响。

使用mysqldump备份表

mysqldump [options] 数据库名称 [表名]>文件名

其中，options：此命令的选项，如-h主机名，-u用户名，-p密码

使用mysqldump备份库

mysqldump [options] –databases [options]数据库名1 [数据库名2…]>文件名

备份所有数据库语

mysqldump [options] –all-databases [options] 数据库名1 [数据库名2…]>文件名

备份还原

mysql -u root -p 表名|数据库名<文件名.sql

使用select into…outfile备份

使用select into…outfile语句把表数据导出到一个文本文件中进行备份，并可使用load data…infile语句来恢复先前备份的数据

select * from sc into outfile 'D:/mysqlbak/sc.txt'fields terminated by ','enclosed by '"'lines terminated by '\r\n';

使用Load data … infile备份

LOAD DATA INFILE 'D:/mysqlbak/scbak.txt' INTO TABLE sc

fields terminated by ','

enclosed by '"'

lines terminated by '\r\n';

//备份本地数据库jxgl中students表
Mysqldump –hlocalhost –uroot –p1234 jxgl students > d:\backup\studentsbak.sql

//备份数据库jxgl和readbook
Mysqldump –hlocalhost –uroot –p1234 –databases jxgl readbook>文件名

//备份本地服务器上的所有数据库
Mysqldump –uroot –p1234 --all-databases > backup_151211.sql

//对数据jxgl进行还原
mysql -u username -p test_db < jxgl.sql

12 .事务及并发控制

事务并发的问题

丢失修改
读“脏”数据
不可重复读

数据库总结

13.建表代码实例

/*
确定使用的库
*/
USE readbook;

/*
Navicat MySQL Data Transfer

Source Server         : mysql
Source Server Version : 50624
Source Host           : localhost:3306
Source Database       : ss

Target Server Type    : MYSQL
Target Server Version : 50624
File Encoding         : 65001

Date: 2019-09-03 20:11:49
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for books
-- ----------------------------
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
  `bno` varchar(8) NOT NULL DEFAULT '',
  `classid` char(5) DEFAULT NULL,
  `bname` varchar(30) DEFAULT NULL,
  `author` varchar(30) DEFAULT NULL,
  `price` decimal(8,1) DEFAULT NULL,
  `publish` varchar(30) DEFAULT NULL,
  `csl` int(11) DEFAULT NULL,
  PRIMARY KEY (`bno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of books
-- ----------------------------
INSERT INTO `books` VALUES ('sn9001  ', 'jsj', 'SQL SERVER2005教程', '龚波', '10.5', '北京希望出版社', '5');
INSERT INTO `books` VALUES ('sn9002  ', 'jsj', 'Delphi数据库开发指南', '万科', '26.4', '清华大学出版社', '5');

-- ----------------------------
-- Table structure for borrow
-- ----------------------------
DROP TABLE IF EXISTS `borrow`;
CREATE TABLE `borrow` (
  `rno` varchar(7) NOT NULL DEFAULT '',
  `bno` varchar(8) NOT NULL DEFAULT '',
  `lenddate` datetime NOT NULL,
  `limitdate` datetime DEFAULT NULL,
  `returndate` datetime DEFAULT NULL,
  `fine` decimal(10,2) DEFAULT NULL,
  `renew` int(2) DEFAULT NULL,
  PRIMARY KEY (`rno`,`bno`,`lenddate`),
  KEY `fk_br_bno_idx` (`bno`),
  CONSTRAINT `borrow_ibfk_1` FOREIGN KEY (`rno`) REFERENCES `readers` (`rno`),
  CONSTRAINT `borrow_ibfk_2` FOREIGN KEY (`bno`) REFERENCES `books` (`bno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of borrow
-- ----------------------------
INSERT INTO `borrow` VALUES ('1022203', 'sn9010', '2013-02-26 00:00:00', '2013-08-26 00:00:00', '2013-07-01 00:00:00', '0.00', null);
INSERT INTO `borrow` VALUES ('1022203', 'sn9013', '2013-02-26 00:00:00', '2013-08-26 00:00:00', '2013-07-01 00:00:00', '0.00', null);

-- ----------------------------
-- Table structure for readers
-- ----------------------------
DROP TABLE IF EXISTS `readers`;
CREATE TABLE `readers` (
  `rno` varchar(7) NOT NULL,
  `rname` char(15) NOT NULL,
  `rsex` varchar(2) DEFAULT NULL,
  `spet` char(20) DEFAULT NULL,
  `dept` char(20) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  `tel` char(11) DEFAULT NULL COMMENT '电话',
  PRIMARY KEY (`rno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of readers
-- ----------------------------
INSERT INTO `readers` VALUES ('1022102', '王铮亮', '男', '国贸', '经济与管理', '1992-07-15 00:00:00', '674576');
INSERT INTO `readers` VALUES ('1022201', '佟大为', '男', '国贸', '经济与管理', '1992-10-19 00:00:00', '674334');

数据库总结

文章目录

1.参考资料

2.数据库设计步骤

数据库的三级模式结构

范式

3.SQL语句分类

数据定义(DDL)

数据操纵(DML)

数据控制(DCL)

4.数据库完整性约束

建外键约束

5.Mysql常用字段

6.SQL语句语法&实例

CREAT

LATER

DORP

INSTER

DELETE

SELECT

UPDATA

聚集函数

7.视图

视图查询

视图修改

视图删除

视图的作用

8.索引

索引类型

索引创建

查看索引

删除索引

9.存储过程

10. 数据库安全权限管理

1. 使用create user创建用户账号:

2. 使用Insert语句建用户

3. Grant语句

4. 修改用户账号

5. 修改用户口令

6. 删除用户

7. 权限授予

8. 收回权限

9. 查看权限

11. 备份恢复

12 .事务及并发控制

13.建表代码实例

Vue ES6箭头函数使用总结

MySQL面试总结

radio的值怎么写入数据库

用PHP实现XML备份Mysql数据库

浅析常用数据库的自增字段创建方法汇总

Php部分常见问题总结

Code First Migrations更新数据库结构（数据迁移）

总结对JS面对对象理解

Java——重写(Override)总结

数据库原理 - 序列7 - Binlog与主从复制