数据库的基本概念

1. 数据库的基本概念
   1. 数据库

数据库是长期储存在计算机内、有组织的、可共享的数据集合。数据库中的数据指的是以一定的数据模型组织、描述和储存在一起、具有尽可能小的冗余度、较高的数据独立性和易扩展性的特点并可在一定范围内为多个用户共享。

1. 1. 数据库管理系统

数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。

1. 1. 注意

通常情况下，经常会用数据库来表示他们使用的数据库软件，这经常会引起混淆，确切的说，数据库软件应该为数据库管理系统，数据库是通过数据库管理系统创建和操作的。

1. 常见数据库管理系统

1. Oracle：目前比较成功的关系型数据库管理系统。运行稳定、功能齐全、性能超群、技术领先。主要应用在大型的企业数据库领域。
2. DB2：IBM的产品。
3. SQL Server：Microsoft的产品。软件界面友好、易学易用，在操作性和交互性方面独树一帜。
4. PostgreSQL：加州大学伯克利分校以教学目的开发的数据库系统，支持关系和面向对象的数据库，属于自由数据库管理系统。
5. MySQL：免费的数据库系统。被广泛用于中小型应用系统。体积小、速度快、总体拥有成本低，开放源代码。2008年被SUN收购，2009年SUN被Oracle收购。

1. Mysql安装

 

 

 

 

 

 

 

 

 

1. 1. 运行配置助手

 

 

 

 

 

 

Deeloper Machine：开发者模式，供开发人员选择。因为开发者的电脑既是客户端又是服务器端，比较耗费内存，所以这个模式下的Mysq会占用相对较少的内存。

 

 ​​​​​​​

 

 

 

Server Machine:服务器模式，客户的服务器就只作为服务器，可以相对的占用较多的内存

Dedicated Mysql Server Machine:这个模式下，所有的内存都供Mysql来使用。

 

Multifunctional Database:多用途数据库

Transactional Database only:仅支持事务

Non-Transactionl Database only:连事务也不支持

 

 

 

 

 

 

 

 

注意：如果在最后一步点击execute以后长时间没反应，那么直接使用任务管理器结束配置助手即可。

 

在安装目录下，双击配置助手，重新执行一次即可。

1. 1. 校验安装

以管理员身份运行cmd

登录MySQL：mysql -uroot -proot

 

查看数据库：show databases;

 

退出MySQL：exit | quit

 

1. SQL简介
   1. 结构化查询语言

结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ "S-Q-L")，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

1986年10月，美国国家标准协会对SQL进行规范后，以此作为关系式数据库管理系统的标准语言（ANSI X3. 135-1986），1987年得到国际标准组织【ISO】的支持下成为国际标准。

注意：各种通行的数据库系统在其实践过程中都对SQL规范作了某些编改和扩充。所以，实际上不同数据库系统之间的SQL不能完全相互通用。我们可以将各个数据库厂商都支持ISO的SQL标准成为“普通话”，而各厂商在标准上扩充的部分称为“方言”

• 1. SQL语句的分类
• DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等
• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）
• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别
• DQL（Data Query Language）：数据查询语言，用来查询记录（数据）

注意：所有的SQL语句都是以英文状态的分号【;】作为结尾。

1. 1. SQL的优点

不是某个特定数据库供应商专有的语言。几乎所有重要的数据库管理系统都支持SQL。

简单易学，该语言的语句都是由描述性很强的英语单词组成，且这些单词的数目不多。

高度非过程化，即用SQL操作数据库，只需指出“做什么”，无须指明“怎么做”，存取路径的选择和操作的执行由DBMS自动完成。

1. DDL（二）
   1. MySQL常用数据类型

数据类型	备注
int	整型
double	浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99
char	固定长度字符串类型； char(10)  'abc       '
varchar	可变长度字符串类型；varchar(10) 'abc'
text	字符串类型
Blob	字节类型
date	日期类型，格式为：yyyy-MM-dd
time	时间类型，格式为：hh:mm:ss
timestamp	时间戳类型 yyyy-MM-dd hh:mm:ss  会自动赋值
datetime	日期时间类型 yyyy-MM-dd hh:mm:ss

注意：VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度。

1. 1. 表的常见操作

创建表

Create table 表名称(字段名称(列名称) 数据类型,字段名称(列名称) 数据类型,…);

1. DML操作
   1. INSERT

使用 INSERT 语句向表中插入数据。

INSERT INTO table [(column [, column...])] VALUES (value [, value...]);

批量插入：

INSERT INTO 表名称VALUES(),(),()…();

注意：

1. 插入的数据应与字段的数据类型相同。
2. 数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
3. 在values中列出的数据位置必须与被加入的列的排列位置相对应。
4. 字符和日期型数据应包含在单引号中。
5. 插入空值，不指定或insert into table value(null)
   1. UPDATE

使用 update语句修改表中数据。

UPDATE tbl_name  SET col_name1=expr1 [, col_name2=expr2 ...]     [WHERE where_definition]

1. UPDATE语法可以用新值更新原有表行中的各列。
2. SET子句指示要修改哪些列和要给予哪些值。
3. WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。
   1. DELETE

使用 delete语句删除表中数据。

DELETE FROM tbl_name  [WHERE where_definition]  

1. 如果不使用where子句，将删除表中所有数据。
2. Delete语句不能删除某一列的值（可使用update）
3. 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。
4. 同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。

DELTE和TRUNCATE的区别

1. 删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，参看mysql文档。
2. DELETE 删除表中的数据，表结构还在;删除后的数据可以找回
3. TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。
4. 删除的数据不能找回。执行速度比DELETE快。

1. DQL操作
   1. 基本查询
      1. 查询所有列

SELECT * FROM table;

1. 1. 1. 查询指定列

SELECT column1,column2… FROM table;

1. 1. 1. 去除重复数据

SELECT DISTINCT column1,column2… FROM table;

1. 1. 1. 查询中的别名

SELECT column1 AS 别名1,column2 AS 别名2… FROM table;

注意：AS可以省略

1. 1. 1. 查询中的四则运算

查询中直接支持＋，－，×，/，运算

1. 1. 限定查询

绝大部分的查询都带限定条件。在SQL中使用WHERE子句来指定查询的限定条件。

语法：SELECT * FROM table WHERE condition;

在WHERE子句中经常使用的运算符

比较运算符	>   <   <=   >=   =    <>	大于、小于、大于(小于)等于、不等于
	BETWEEN … AND …	显示在某一区间的值(包含)
	IN(VALUE1,VALUE2…)	匹配in中的值
	LIKE	模糊查询 %匹配任意个字符 _匹配一个字符
	IS NULL	判断是否为空
逻辑运算符	AND	多个条件同时成立
	OR	多个条件满足一个即可
	NOT	取反

1. 1. 查询排序

在很多场景下都需要对查询结果进行排序，使用ORDER BY对结果进行排序。

SELECT * FROM table WHERE condition ORDER BY column1 ASC|DESC column2 ASC|DESC

1. 函数
   1. 字符函数

CHARSET(str)	返回字串字符集
CONCAT (string2  [,... ])	连接字串
INSTR (string ,substring )	返回substring在string中出现的位置,没有返回0
UCASE (string2 )	转换成大写
LCASE (string2 )	转换成小写
LEFT (string2 ,length )	从string2中的左边起取length个字符
LENGTH (string )	string长度
REPLACE (str ,search_str ,replace_str )	在str中用replace_str替换search_str
STRCMP (string1 ,string2 )	逐字符比较两字串大小
SUBSTRING (str , position  [,length ])	从str的position开始,取length个字符
LTRIM (string2 ) RTRIM (string2 )  trim	去除前端空格或后端空格

1. 1. 数学函数

ABS (number2 )	绝对值
BIN (decimal_number )	十进制转二进制
CEILING (number2 )	向上取整
CONV(number2,from_base,to_base)	进制转换
FLOOR (number2 )	向下取整
FORMAT (number,decimal_places )	转十六进制
LEAST (number , number2  [,..])	求最小值
MOD (numerator ,denominator )	求余
RAND([seed])	RAND([seed])

1. 1. 日期函数

ADDTIME (date2 ,time_interval )	将time_interval加到date2
CURRENT_DATE (  )	当前日期
CURRENT_TIME (  ) CURRENT_TIMESTAMP (  )	当前时间 当前时间戳
DATE (datetime )	返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type )	在date2中加上日期或时间
DATE_SUB (date2 , INTERVAL d_value d_type )	在date2上减去一个时间
DATEDIFF (date1 ,date2 )	两个日期差
NOW ( )	当前时间
YEAR|Month|DATE (datetime )	年月日

示例：select addtime(‘02:30:30’,‘01:01:01’);           注意：字符串、时间日期的引号问题

  select date_add(entry_date,INTERVAL 2 year) from student;／／增加两年

select addtime(time,‘1 1-1 10:09:09’) from student;　／／时间戳上增加，注意年后没有

1. 分组函数

分组函数，又叫聚合函数，是将数据进行分组运算。

1. 1. COUNT()

该函数用来统计不为null的总共记录数

1. 1. MAX()

计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

1. 1. MIN()

计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

1. 1. SUM()

计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

1. 1. AVG()

计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

1. 分组查询
   1. 分组查询

分组查询是将查询的结果以指定条件进行分组。使用GROUP BY 子句完成。

SELECT * FROM table WHERE condition GROUP BY column1,column2 ORDER BY column1…

注意：凡是GROUP BY后出现的列，在查询中必须使用聚合函数进行处理

1. 1. HAVING子句

HAVING子句用来对聚合函数进行条件约束。

SELECT * FROM table WHERE condition GROUP BY col1,col2 HAVING condition ORDER BY col1 …

1. 分页查询

在Mysql中使用LIMIT来实现分页查询

SQL语句书写顺序

SELECT –FROM-WHERE-GROUP BY-HAVING-ORDER BY-LIMIT

LIMIT语法

LIMIT START,PAGESIZE

示例：limit 3,5 查询5条记录，从第三条开始。

1. 数据完整性

数据完整性，是用来保证用户保存到数据库中的数据是正确的。

完整性的分类：实体完整性，域完整性，引用完整性

为了保证数据完整性，需要使用约束来完成。

1. 1. 实体完整性

实体：即表中的一行(一条记录)代表一个实体（entity）

实体完整性的作用：标识每一行数据不重复。

约束类型： 主键约束（primary key）  唯一约束(unique)  自动增长列(auto_increment)

1. 1. 1. 主键约束

注：每个表中要有一个主键。

特点：数据唯一，且不能为null

第一种添加方式：

CREATE TABLE student(

id int primary key,

name varchar(50)

);

第二种添加方式：此种方式优势在于，可以创建联合主键

CREATE TABLE student(

id int,

name varchar(50),

primary key(id)

);

CREATE TABLE student(

classid int,

stuid int,

name varchar(50),

primary key(classid，stuid)

);

第三种添加方式：

CREATE TABLE student(

id int,

name varchar(50)

);

ALTER TABLE student  ADD  PRIMARY  KEY (id);

1. 1. 1. 唯一约束

特点：数据不能重复。

CREATE TABLE student(

Id int primary key,

Name varchar(50) unique

);

1. 1. 1. 自动增长

sqlserver数据库 (identity)  oracle数据库( sequence)

CREATE TABLE student(

Id int primary key auto_increment,

Name varchar(50)

);

1. 1. 域完整性

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较

域代表当前单元格

域完整性约束：数据类型 非空约束（not null） 默认值约束(default)  

check约束（mysql不支持，使用枚举类型变相实现）check(sex='男' or  sex='女')

1. 1. 1. 数据类型

常用数据类型有数值类型，日期类型，字符串类型

1. 1. 1. 非空约束

CREATE TABLE student(

Id int pirmary key,

Name varchar(50) not null,

Sex varchar(10)

);

1. 1. 1. 默认值约束

CREATE TABLE student(

Id int pirmary key,

Name varchar(50) not null,

Sex varchar(10) default ‘男’

);

1. 1. 引用完整性(参照完整性)

外键约束：用来进行两张表之间的约束

CREATE TABLE student(

sid int pirmary key,

name varchar(50) not null,

sex varchar(10) default ‘男’

);

CREATE TABLE SCORE(

id int,

score int,

sid int , -- 外键列的数据类型一定要与主键的类型一致

CONSTRAINT fk_score_sid foreign key (sid) references student(id)

);

第二种添加外键方式。

ALTER TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);

1. 多表查询

使用一张以上的表做查询就是多表查询

语法： SELECT {DISTINCT} *|列名.. FROM 表名 别名，表名1 别名

{WHERE 限制条件 ORDER BY 排序字段 ASC|DESC...}

注意:多表连接查询为产生笛卡儿积。

1. 1. 多表连接基本查询

查询员工表和部门表

查询出雇员的编号，姓名，部门的编号和名称，地址

1. 1. 连接查询
      1. 内连接

内连接查询的特点是必须满足条件才可以被查询出来，也就是说在多表连接基本查询中我们使用的就是内连接查询。

SQL99内连接语法

SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

Mysql默认使用内连接，其中ON相当于WHERE后边用来跟条件，INNER可以省略。

1. 1. 1. 外连接

外连接的特点则是查询出来的结果可能不满足条件。分为左外连接，右外连接，全外连接

1. 1. 1. 1. 左外连接

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

SQL99语法

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

其中OUTER可以省略。

1. 1. 1. 1. 右外连接

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL

SQL99语法

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

1. 1. 1. 1. 全外连接

全外连接是将所有的数据都查询出来，不满足条件的则会null。Mysql不支持全外连接查询。

SQL99语法

SELECT * FROM emp e FULL JOIN dept d ON e.deptno=d.deptno;

1. 1. 1. 自连接

自连接就是自己连接自己。

查询7369员工编号、姓名、经理编号和经理姓名

1. 1. 1. 自然连接【了解】

连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式

语法

SELECT * FROM emp NATURAL JOIN dept;

SELECT * FROM emp NATURAL LEFT JOIN dept;

SELECT * FROM emp NATURAL RIGHT JOIN dept;

注意：自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！

1. 1. 合并结果集
      1. UNION

去除重复记录,合并两条结果集。

1. 1. 1. UNION ALL

不去除重复记录,合并两条结果集。

1. 1. 1. MINUS

获得两条结果集合的差

1. 子查询【重要】

子查询：查询当中有查询，即：一个select语句中包含另一个完整的select语句.

1. 子查询出现的位置：

• where后，作为条为被查询的一条件的一部分；
• from后，作表；

1. 当子查询出现在where后作为条件时，还可以使用如下关键字：

• any
• all

1. 子查询结果集的形式：

• 单行单列（用于条件）
• 单行多列（用于条件）
• 多行单列（用于条件）
• 多行多列（用于表）

练习

查询和SCOTT工资一样的员工

查询比SCOTT工资高的员工

工资高于10号部门所有人的员工信息

查询工作和工资与MARTIN完全相同的员工信息

有2个以上直接下属的员工信息

SELECT * FROM emp WHERE empno IN(

SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2);

1. 附录
   1. 修改登陆密码

• 停止mysql服务
• 在cmd输入mysqld --skip-grant-tables【不要关闭该窗口】
• 另外打开一个cmd

1. 输入mysql -u root –p【不要输密码】
2. use mysql;
3. update user set password=password('abc') WHERE User='root';

• 关闭所有的cmd
• 重启mysql服务
  1. 重启成功，正常登陆即可
  2. 重启失败
     1. 在任务管理器结束mysql开头的进程
     2. 重新启动mysql服务即可

1. 1. MySQL卸载

• 停止MySQL服务

停止mysql服务 net stop mysql       

启动mysql服务   net start mysql

• 卸载MySQL
• 到安装目录删除MySQL

删除：C:\Documents and Settings\All Users\Application Data\MySQL

删除C:\ProgramData\MySQL

• 找到mysql 安装目录下的 my.ini   datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
• 删除注册表：

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services

搜索mysql，全部删除

• 重新安装
  1. MySQL数据类型

分类	数据类型	说明
数值类型	BIT(M) TINYINT [UNSIGNED] [ZEROFILL] BOOL，BOOLEAN SMALLINT [UNSIGNED] [ZEROFILL] INT [UNSIGNED] [ZEROFILL] BIGINT [UNSIGNED] [ZEROFILL] FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]	位类型。M指定位数，默认值1，范围1-64 带符号的范围是-128到127。无符号0到255。 使用0或1表示真或假 2的16次方 2的32次方 2的64次方 M指定显示长度，d指定小数位数 表示比float精度更大的小数
文本、二进制类型	CHAR(size) char(20) VARCHAR(size)  varchar(20) BLOB   LONGBLOB TEXT(clob)          LONGTEXT(longclob)	固定长度字符串 可变长度字符串 二进制数据 大文本
时间日期	DATE/DATETIME/TimeStamp	日期类型(YYYY-MM-DD)  (YYYY-MM-DD HH:MM:SS)， TimeStamp表示时间戳， 它可用于自动记录insert、update操作的时间

• 1. MySQL中的乱码问题
• mysql有六处使用了字符集，分别为：client 、connection、database、results、server 、system。

1. 1. 表与表之间的关系

• 一对一：例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方案：

Ø 在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；

Ø 给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。

• 一对多（多对一）：最为常见的就是一对多！一对多和多对一，这是从哪个角度去看得出来的。t_user和t_section的关系，从t_user来看就是一对多，而从t_section的角度来看就是多对一！这种情况都是在多方创建外键！
• 多对多：例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。

1. 1. Mysql数据库的备份与恢复

1. 1. 1. 生成SQL脚本，导出数据

在控制台使用mysqldump命令可以用来生成指定数据库的脚本文本，但要注意，脚本文本中只包含数据库的内容，而不会存在创建数据库的语句！所以在恢复数据时，还需要自已手动创建一个数据库之后再去恢复数据。

命令

mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径

 

现在可以在C盘下找到mydb1.sql文件了！

注意，mysqldump命令是在Windows控制台下执行，无需登录mysql！！！

1. 1. 1. 执行sql脚本，恢复数据

前提：必须先创建数据库名

执行SQL脚本需要登录mysql，然后进入指定数据库，才可以执行SQL脚本！！！

执行SQL脚本不只是用来恢复数据库，也可以在平时编写SQL脚本，然后使用执行SQL 脚本来操作数据库！大家都知道，在黑屏下编写SQL语句时，就算发现了错误，可能也不能修改了。所以我建议大家使用脚本文件来编写SQL代码，然后执行之！

命令

SOURCE C:\mydb1.sql

 

注意，在执行脚本时需要先行核查当前数据库中的表是否与脚本文件中的语句有冲突！例如在脚本文件中存在create table a的语句，而当前数据库中已经存在了a表，那么就会出错！

还可以通过下面的方式来执行脚本文件：

mysql –u用户名 –p密码 数据库<要执行脚本文件路径

mysql -uroot -p123 mydb1<c:\mydb1.sql

 

这种方式无需登录mysql！

注意：在CMD下 命令不能加;