数据库安装

下载地址：https://dev.mysql.com/downloads/mysql/

解压到相应目录并在目录下配置my.ini文件，信息如下：

[client]
# 设置mysql客户端默认字符集
default-character-set=utf8
 
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:\\web\\mysql-8.0.11
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
# datadir=C:\\web\\sqldata
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

启动数据库

使用管理员身份开启cmd，切换目录：
1
cd C:\.....\bin
初始化数据库：
1
mysqld --initialize --console
得到密码：（记得保存）

安装命令：
1
mysql install
启动命令：
1
net start mysql

登陆数据库
1
mysql -h localhost -u username -p

常见命令

查看当前所有的数据库
1
show databases
打开指定的库
1
use 库名
查看当前库的所有表
1
show tables
查看其他库的所有表
1
show tables from 库名

创建表

create table 表名（
 列名 列类型,
 列名 列类型,
 ）

查看表结构
1
desc 表名
查看服务器的版本
- 方式一：登录到mysql服务器
  1
  select version()
- 方式二：没有登陆到mysql服务器
  1
  mysql --version
- 或
  1
  mysql --V

语法规范

不区分大小写，但建议关键字大写，表名，列名小写
每条命令最好用分号结尾
每条命令根据需要换行

注释

1
2
3

单行注释 #注释文字
单行注释 -- 注释文字
多行注释 /* 注释文字 */

快捷键

ctrl + enter 执行当前语句

DQL语言的学习

基础查询

语法

1	select 查询列表 from 表名

特点

查询的结果集是一个虚拟表
select 查询列表类似于system.out.println()
select后面跟的查询列表，可以有多个部分组成，中间用逗号隔开
1
select 字段1,字段2,表达式 from 表

查询常量

1	select 100;

查询表达式

1	select 100%3;

查询单个字段

1	select 'last_name' from 'employees'; 引号注意

查询所有字段

1	select * from 'employees'; 引号注意

查询函数

1
2
3

select databases();
select version();
select user();

起别名

方式一使用as关键字

1
2
3

select user() as 用户名;
select user() as "用户名";
select user() as '用户名';

方式二使用空格

1 2	select user() 用户名; select last_name "姓名" from employee;

需求

查询first_name 和 last_name 拼接成的全名 ==不一致==

1	select first_name + last_name as "姓名" from employees;

或

1	select concat(first_name,last_name) as "姓名" from employees;

==特别注意== concat中的字段有null值后续整个连接的字段都是null

解决办法：使用ifnull

1 2	SELECT IFNULL(commission_pct,'空') FROM employees; # 若该字段为null，则输出“空” 若不为null 原样输出

distinct的使用

查询员工涉及到的部门编号有哪些使用distinct关键词达成去重的效果

1	SELECT DISTINCT department_id FROM employees;

查看表的结构

两种方式：

1 2	DESC employees; SHOW COLUMNS FROM employees;

条件查询

语法

1
2
3

SELECT 查询列表
FROM 表名
WHERE 筛选条件;

执行顺序

from字句
where字句
select字句

特点

按关系表达式筛选 ==注意== 在mysql中使用<>作为不等于号
逻辑运算符
1
2
3
and
or
not
mysql特有的模糊查询
1
2
3
4
like
in
between and
is null

模糊查询

like

功能: 一般和通配符搭配使用，对字符型数据进行部分匹配查询

常见的通配符

1 2	_ 任意单个字符 % 任意多个字符

案例一查询姓名中包含字符a的员工信息
1
SELECT * FROM employees WHERE last_name LIKE '%a%';
案例二查询姓名中包含最后一个字符为e的员工信息
1
SELECT * FROM employees WHERE last_name LIKE '%e';
案例三查询姓名中包含第一个字符为e的员工信息
1
SELECT * FROM employees WHERE last_name LIKE 'e%';

案例四查询姓名中包含第三个字符为x的员工信息

1 2	SELECT * FROM employees WHERE last_name LIKE '__x%'; #此处两个下划线

案例五查询姓名中包含第三个字符为_的员工信息使用转义字符

1
2
3

SELECT * FROM employees WHERE last_name LIKE '__\_%';
SELECT * FROM employees WHERE last_name LIKE '__$_%' ESCAPE '$';
#设置$为转义字符

in

功能: 查询某字段的值是否属于指定的列表之内
语法： in(常量值1，常量值2，常量值3….)

案例一查询部门编号是30/50/90的员工名，部门编号

1
2
3

select last_name,department_id 
from employees
where department_id in(30,50,90);

between and

function: 判断某个字段的值是否介于xx之间
grammar: between and/not between and

case1 查询部门编号是30-90之间的部门编号，员工姓名

1
2
3

select department_id,last_name
from employees
where department_id between 30 and 90

is null

function:判断某个字段的数值是否为空

case1 查询没有奖金的员工信息

1
2
3

select *
from employees
where commission_pct is null

特点：= 能判断普通的内容，is只能判断null值

排序查询

语法

SELECT 查询列表
FROM 表名
WHERE 筛选条件
ORDER BY 排序列表

执行顺序

from
where
select
order by

特点

排序列表可以是单个字段，多个字段，表达式，函数，列数，以及以上的组合
升序通过asc 默认
降序通过 desc
根据执行顺序可以用别名排序

字符函数

LENGTH 获取字节长度
1
ORDER BY LENGTH(last_name);
CHAR_LENGTH 获取字符个数
1
ORDER BY CHAR_LENGTH('');

SUBSTR 拼接字符

1
2
3

SELECT SUBSTR('xxx',起始索引,截取的字符长度);
起始索引从1开始 
可以不写截取的字符长度 代表后续全部

INSTR 获取字符第一次出现的索引
1
SELECT INSTR('AAAAAXCCACACACC','X');

TRIM 去前后指定的字符默认去掉空格

1 2	SELECT TRIM(' a ') AS b SELECT TRIM('X' FORM 'XXXXXXXAXXXXXXXXX') AS a

LPAD/RPAD 左填充/右填充
1
SELECT LPAD('XXX',10,'A');
STRCMP 比较两个字符大小大就返回1
1
SELECT STRCMP('abc','afc');
UPPER/LOWER 变大写/小写

数学函数

ABS 绝对值
CELL 向上取整返回≥该参数的最小整数
FLOOR 向下取整
ROUND 四舍五入
TRUNCATE 截断
MOD 取余

日期函数

NOW() 现在的时间
CURDATE
CURTIME
DATEDIFF 日期差
DATE_FORMAT() 转换日期格式
STR_TO_DATE

流程控制函数

CASE

CASE 表达式
WHEN X THEN 结果1 
WHEN X THEN 结果2
...
ELSE 结果n
END

分组函数

sum
avg
max
min
count
分组前筛选 where
分组后筛选 having

连接查询

内连接 INNER JOIN ON
外连接(左连接，右连接)
图解

自我关联

查询广东省的所有区域
先找出市
select a.id,a.area,a.pid from area as a join area as b on a.pid = b.id where b.area = '广东';
再根据市找出区
select c.id,c.area,c.pid from area as c join () as d on c.pid = d.id

子查询

查询语句中嵌套另一个select

1	select sno,sname from student where sno in(select sno from sc where cno = 'C001')

exists(重点)

返回的是true/false
select 之后加什么都可以
与in的区别是
in 会遍历整个表 
exist 找到就返回

not exists(重点)
1

概念

记录

表中的每一行叫做一个记录，每个记录包含此行中所有信息

字段

表的列称之为字段

实体

表格的字段抽象的是共性的内容，以及共同的属性，这种方式称之为实体
用矩形表示实体（对应表名），椭圆表示属性（对应字段），菱形代表关系

主键

用于添加索引，加快搜索效率

外键

用于引用主键的内容
经常是多的那一方设置外键
设置方法：
1. 在表中添加外键的字段
2. 将该字段设置为外键
删除时更新时

关系型数据库

网页系统

三步骤：
原型设计
数据库建模
编写前后台程序

数据库建模

确定实体和关系
完善整体实体关系图
根据实体关系图构建表格

DDL语言

创建语句create

create table 表名(
	列名1 数据类型 约束,
    name varchar(255) unique,
)

修改语句Alter

alter table 表名(
	add column 列名 类型 #添加新的字段
    change column 旧列名 新列名 新列类型 #更改字段名称
    modify column 列名 类型 #修改字段类型
    drop column 列名 #删除字段
    rename to 新表名 #表格重命名
)

删除语句Drop

1	drop table 表名

清除语句Truncate

1	truncate table 表名

删除表中的所有记录单表结构保留

约束

约束的实质就是表中数据的限制条件，为了报纸表中的记录完整和有效

分为五种约束

非空约束 not null 后面加
唯一性约束 unique 后面加
主键约束 primary key 后面加
外键约束 foreign key 
eg
foregin key(外键字段) references 表名(主键字段) 
默认 default
eg
name varchar(20) default 'zhangsan'

约束分类

表级约束和列级约束非空约束不能用于表级约束

命名规范

DML数据操作语句

增加语句insert

1	insert into 表名 (字段1,字段2) values (值1,值2);

删除语句delete

1	delete from 表名

更新语句update

1	update 表名 set 列名=值

DTL事务控制语句

事务

什么是事务

1	—事务（Transaction）是访问和更新数据库的程序执行单元；事务中可能包含一个或多个sql语句，这些语句要么都执行，要么都不执行。

事务的基本要素ACID

原子性 
一致性
隔离性
持久性

事务控制语句

start transaction开启事务
commit 提交事务
rollback 回滚事务
set transaction 设置事务隔离级别

事务的隔离性

脏读

指读到了其他事务未提交的数据

不可重复读

幻读

隔离级别

1	set global transaction isolation level read uncommitted;

存储过程

创建存储过程

create procedure 函数名(参数)
eg
delimiter //
create procedure proc1()
begin 
select * from user;
end //
delimiter ;

局部变量

declare c int default 0;定义一个局部变量c初始值为0
set c=a+b 赋值
如果是字符串类型要给出长度
declare var1 varchar(32) default "";
且局部变量声明要放在存储过程的最开始否则出错

用户变量

@varname
两种创建方式：
set @num = 1;或 set @num:=1;
select @num:=1; 或 select @num:=字段名 from 表名 where 
用select必须用:=

输出用户变量：select

如果直接写 select @name:=password from user;
则是@name变量的值是最后一条记录的值

存储过程的参数

三种存储类型in out inout

IN 输入参数

delimiter //
create proceduce demo1(IN p_in int)
	BEGIN
	SELECT p_in;
	SET p_in = 2;
	SELECT p_in;
	END //
delimiter ;

OUT 输出参数
INOUT 输入输出参数

调用函数

call
1
call procedure(参数)

删除存储过程

drop
1
drop procedure 存储过程名称

修改存储过程

alter

1	alter procedure 存储过程名称 [特征]

存储过程的控制语句

if-then-else

if condition1 then{}
elseif condition2 then
else
end if;

例子

DELIMITER //
CREATE PROCEDURE IncomeLevel (IN monthly_value INT, OUT income_level VARCHAR(32))
BEGIN
   IF monthly_value <= 4000 THEN
      SET income_level = 'Low Income';
   ELSEIF monthly_value > 4000 AND monthly_value <= 7000 THEN
      SET income_level = 'Avg Income';
   ELSE
      SET income_level = 'High Income';
   END IF;
   SELECT income_level;
END; //
DELIMITER ;

case when

case case_value 
	when xxx then xxx
	when xxx then xxx
	else xxx
end case

例子

DELIMITER //  
CREATE PROCEDURE proc3 (in parameter int)  
  begin 
	case parameter
        when 1 then   
          insert into table1 values(1,'tom');  
        when 2 then   
          insert into table1 values(2,'lili');  
        else   
          insert into table1 values(3,'lucy');  
    end case;  
  end //  
DELIMITER ;

while
1
2
3
while 条件 do
代码
end while;

repeat

repeat 
	code
until 条件 end repeat
条件判断在结尾

关系运算

集合运算

传统的集合运算是二目运算，关系看成元组的集合，其运算关系是从关系的水平方向，即从行的角度进行，有并，差，交，笛卡尔积四种运算
单目运算一个运算符 eg i++
双目运算两个运算符 a+b
三目运算三个运算符 a?b:c

差集

笛卡尔积

R1和R2的笛卡尔积 R3=R1XR2

关系运算

从操作关系个数上说，分为一元关系操作和二元关系操作。
一元关系操作包括对==单个==关系进行==垂直分解的投影运算==和进行==水平分解的选择运算==
二元操作关系则是对==两个==关系进行操作，包括==链接运算和除运算==

一元关系

sql考试

选择题
简答题（七选四）
大题
1. 查询语句
2. 数据库设计( 画er图)
3. 关系代数（一道大题）

sql考试简答

数据库系统体系结构有多少层

分为三层：外部层，概念层和内部层

什么是事务，简单的阐述一下事务的原子性

事务（Transaction）是访问和更新数据库的程序执行单元；

原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做

常见的数据库故障有多少种

5种；事务内部的故障，系统故障，介质故障，计算机病毒故障，用户操作错误

关系代数的基本运算有哪些

五种。并差投影选择笛卡尔积

等值连接和自然连接的区别

自然连接一定是等值连接，但等值连接不一定是自然连接
等值连接要求的分量不一定是公共属性；而自然连接要求相等的分量必须是公共属性
等值连接不把重复的属性除去，而自然连接要把重复的属性除去

为什么外键属性的值有时候可以为空

外键可以为空,一个表的关键的标识是主键,外键是否为空不影响表的结构及数据库设计概念的.外键只起个关联表的作用.

数据库的完整性约束有哪些子句

not null 非空约束x
unique 唯一约束
primary key 主键约束
foreign key 外键约束
default 默认约束