一、数据库的基本概念
1.数据库的英文单词:DataBase DB
2.什么是数据库：
*用于储存和管理数据的仓库
3.数据库的特点
*可以持久化存储数据，相当于一个文件系统
*方便存储和管理数据
*使用统一的管理方式--SQL
MySql数据库软件
1.配置
* MySQL服务启动
1. 手动。
2. cmd--> services.msc 打开服务的窗口
3. 使用管理员打开cmd
* net start mysql : 启动mysql的服务
* net stop mysql:关闭mysql服务
*MySQL登陆
1.mysql -uroot -p密码
*MYSQL退出
1.exit
2.quit
- MySQL目录结构
1. MySQL安装目录：basedir="D:/develop/MySQL/"
* 配置文件 my.ini
2. MySQL数据目录：datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
* 几个概念
* 数据库：文件夹
* 表：文件
* 数据：数据
什么是SQL
1) DDL(Data Definition Language)数据定义语言
用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
2) DML(Data Manipulation Language)数据操作语言
用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
3) DQL(Data Query Language)数据查询语言
用来查询数据库中表的记录(数据)。关键字：select, where 等
4) DCL(Data Control Language)数据控制语言(了解)
用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等
DDL：操作数据库、表
1.操作数据库：CRUD
1. C(Create):创建
*创建数据库:
**create Database 数据库名称；
* 创建数据库，判断不存在，再创建：
**create Database if not exists 数据库名称;
* 创建数据库，并指定字符集
**create DDatabase 数据库名称 character set 字符集名称;
* 练习： 创建db4数据库，判断是否存在，并制定字符集为gbk
**create Database if not exists 数据库名称 character set gbk;
2. R(Retrieve)：查询
* 查询所有数据库的名称:
**show databases;
* 查询某个数据库的字符集:查询某个数据库的创建语句
**show create database 数据库名称;
3. U(Update):修改
* 修改数据库的字符集
**alter database 数据库名称 character set 字符集名称
4. D(Delete):删除
* 删除数据库
**drop database 数据库名称
* 判断数据库存在，存在再删除
* drop database if exists 数据库名称;
5. 使用数据库
* 查询当前正在使用的数据库名称
* select database();
* 使用数据库
* use 数据库名称;
2.操作表
1. C(Create):创建
1. 语法：
create Table 表名{
列名1 数据类型1,
列名2 数据类型2,
......
列名n 数据类型n
}
* 数据库类型：
1. int：整数类型
* age int,
2. double:小数类型
* score double(5,2)
3. date:日期，只包含年月日，yyyy-MM-dd
4. datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
5. timestamp:时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
* 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
6. varchar：字符串
* name varchar(20):姓名最大20个字符
* zhangsan 8个字符 张三 2个字符
* 创建表
create table student(
id int,
name varchar(32),
age int ,
score double(4,1),
birthday date,
insert_time timestamp
);
* 复制表：
create table 表名 like 被赋值的表名
2. R(Retrieve)：查询
* 查询某个数据库中所有的表名称
show tables;
* 查询表结构
desc 表名;
3. U(Update):修改
1. 修改表名
alter table 表名 rename to 新的表名;
2. 修改表的字符集
alter table 表名 character set 字符集名;
3. 添加一列
alter table 表名 add 列名 数据类型;
4. 修改列名称 类型
alter table 表名 change 列名 新列名 数据类型;
alter table 表名 modify 列名 数据类型;
5. 删除列
alter table 表名 drop 列名;
4. D(Delete):删除
* drop table 表名;
* drop table if exists 表名 ;
DML:增删改表中数据
1. 添加数据：
* 语法：insert into 表名 (列名,列名2,列名3) values(值1,值2,值3);
* 注意：
1. 列名和值要一一对应。
2. 如果表名后，不定义列名，则默认给所有列添加值
insert into 表名 values(值1,值2,...值n);
3. 除了数字类型，其他类型需要使用引号(单双都可以)引起来
2. 删除数据：
* 语法： delete from 表名 [where 条件]
2. 如果要删除所有记录
*delete from 表名;不推荐，删除了所有数据
*TRUNCATE TABLE 表名;推荐，效率高，输出表，还会创建一张一样的表
3. 修改数据：
*语法：
*update 表名 set 列名1=值1,列名2=值2,列名n=值n.....[where条件];
* 注意：
1. 如果不加任何条件，则会将表中所有记录全部修改。
DQL：查询表中的数据
* select * from 表名;
1.语法:
select
字段列表
from
表格字段
where
条件字段
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
- 基础查询
- 多个字段的查询
select 字段1，字段2... from 表名;
* 注意：
* 如果查询所有字段，则可以使用来替代字段列表。
2. 去除重复：- distinct
- 计算列
- 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）
- ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null
- 表达式1：哪个字段需要判断是否为null
- 如果该字段为null后的替换值。
- 起别名：
- as：as也可以省略
- 多个字段的查询
3.条件查询
1. where子句后跟条件
2. 运算符
* > 、< 、<= 、>= 、= 、<>
* BETWEEN...AND
* IN( 集合)
* LIKE：模糊查询
* 占位符：
* _:单个任意字符
* %：多个任意字符
* IS NULL
* and 或 &&
* or 或 ||
* not 或 !
4.条件查询练习的sql代码
create table student(
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math int, -- 数学
english int -- 英语
);
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','
杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩
',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港
',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);
练习
-- 查询年龄大于20岁
select * from student where age >20;
-- 查询年龄等于20岁
select * from student where age =20;
-- 查询年龄不等于20岁
select * from student where age !=20;
select * from student where age <>20;
-- 查询年龄大于等于20 小于等于30
select * from student where age >=20 and age <=30;
select * from student where age >=20 && age <=30;
select * from student where age between 20 and 30;
-- 查询年龄22岁，18岁，25岁的信息
select * from student where age=22 or age=18 or age=25;
select * from student IN(22,18,25);
-- 查询英语成绩为null
select * from student where english is Null;
select * from student where english=null;
-- 查询英语成绩不为null
slect * from student where english is NOT Null;
-- 查询姓马的有哪些？ like
select * from student where name like '马%';
-- 查询姓名第二个字是化的人
select * from student where name like '化'%;
-- 查询姓名是3个字的人
select * from student where name like '__';
-- 查询姓名中包含德的人
select * from student where name like '%德%';
约束
-
概念： 对表中的数据进行限定，保证数据的正确性、有效性和完整性。
-
分类：
1.主键约束:primary key
2.非空约束:not null
3.唯一约束:unique
4.外键约束:foreign key -
非空约束：not null，值不能为null
- 创建表时添加约束
create table stu(
int id,
Name varchar(20) not null
);
2. 创建表完后，添加非空约束
alter Table stu modify name varchar(20) not null;
3. 删除name的非空约束
alter Table stu modify name varchar(20);
- 创建表时添加约束
-
唯一约束：unique，值不能重复
-
创建表时，添加唯一约束
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束);
- 注意mysql中，唯一约束限定的列的值可以有多个null
- 在创建表后，添加唯一约束
alter Table stu modify phone_number varchar(20) unique; - 删除唯一约束
alter Table stu INDEX drop phone_numbe;
-
-
主键约束：primary key。
1. 注意：
1. 含义：非空且唯一
2. 一张表只能有一个字段为主键
3. 主键就是表中记录的唯一标识
2. 在创建表时，添加主键约束
create table stu(
id int primary key,-- 给id添加主键约束
name varchar(20)
);
3. 删除主键
-- 错误 alter table stu modify id int ;
Alter table stu drop primary key;
4. 创建完表后，添加主键
alter table stu modify id int primary key;
5. 自动增长：
1. 概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长
2. 在创建表时，添加主键约束，并且完成主键自增长
create table stu(
id int primary key auto_increment,
name varchar(20)
);
3. 删除自动增长
ALTER TABLE stu MODIFY id INT;
4. 添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT; -
外键约束：foreign key,让表于表产生关系，从而保证数据的正确性。
1. 在创建表时，可以添加外键
* 语法：
create table 表名(
....
外键列
constraint foreign key references 主表名称(主表列名称)
);
2. 删除外键
alter Table stu drop foreign key (外键名称);3. 创建表之后，添加外键 alter table 表名 add constraint 外键名称 foreign key (外键字段名称) REFERENCES 主表名称(主表列名称); 4. 级联操作 1. 添加级联操作 语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ; 2. 分类： 1. 级联更新：ON UPDATE CASCADE 2. 级联删除：ON DELETE CASCADE
数据的备份和还原
1. 命令行：
* 语法：
* 备份： mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
* 还原：
1. 登录数据库
2. 创建数据库
3. 使用数据库
4. 执行文件。source 文件路径
2. 图形化工具：
多表查询
* 查询语法：
select
列名列表
from
表名列表
where....
创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
# 创建员工表
CREATE TABLE emp(
id int primary key auto_increment,
name varchar(20) not null,
gender char(1).
salary double,
join_date date,
dept_id int ,
foreingn key(dept_id) references dept(id)
);
笛卡尔积
* 有两个集合A,B .取这两个集合的所有组成情况。
* 要完成多表查询，需要消除无用的数据
-
多表查询的分类：
- 内连接查询：
- 隐式内连接：使用where条件消除无用数据
例子：
-- 查询所有员工信息和对应的部门信息
select * from dept,emp where dept.'id'=emp.'dept_id';
-- 查询员工表的名称，性别。部门表的名称
select emp.name,emp.gender,dept.name where dept.'id'=emp.'dept.id';SELECT t1.name, -- 员工表的姓名 t1.gender,-- 员工表的性别 t2.name -- 部门表的名称 FROM emp t1, dept t2 WHERE t1.`dept_id` = t2.`id`; 2. 显式内连接： * 语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件 * 例如： * SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`; * SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`; 3. 内连接查询： 1. 从哪些表中查询数据 2. 条件是什么 3. 查询哪些字段 2. 外链接查询： 1. 左外连接： *语法:select 字段列表 from 表1 left [outer] join 表2 on 条件字段; *查询左表的所有数据以及交集部分 * 例子： -- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称 select emp.*,dept.name from emp [outer] join dept on emp.'dept_id'=dept.'id'; 2. 右外连接： * 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件； * 查询的是右表所有数据以及其交集部分。 * 例子： SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`; 3. 子查询： * 概念：查询中嵌套查询，称嵌套查询为子查询。 -- 1 查询最高的工资是多少 9000 select MAX(salary) from emp; -- 2 查询员工信息，并且工资等于9000的 select * from emp where emp.salary=9000; --3 查询最高工资的员工信息 select * from emp where emp.'salary' =(select Max(salary) from emp); 子查询不同情况 1. 子查询的结果是单行单列的： * 子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= = -- 查询员工工资小于平均工资的人 select * from emp where emp.salary < (select AVG(salary) from emp); 2. 子查询的结果是多行单列的： * 子查询可以作为条件，使用运算符in来判断 -- 查询'财务部'和'市场部'所有的员工信息 select id from dept where dept.name='财务部' and dept.name='市场部'; select * from emp where dept_id=2 or dept_id=3; --子查询 select * from emp where dept_id IN (select id from dept where dept.name='财务部' and dept.name='市场部'); 3. 子查询的结果是多行多列的： * 子查询可以作为一张虚拟表参与查询 -- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息 -- 子查询 select * from dept t1,(select * from emp where emp.join_date>'2011-11-11') t2 where t1.id=t2.dept_id; - 内连接查询：
事务
1. 概念：
* 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
2. 操作：
1. 开启事务： start transaction;
2. 回滚：rollback;
3. 提交：commit;
4. MySQL数据库中事务默认自动提交
* 事务提交的两种方式：
* 自动提交：
* mysql就是自动提交的
* 一条DML(增删改)语句会自动提交一次事务。
* 手动提交：
* Oracle 数据库默认是手动提交事务
* 需要先开启事务，再提交
* 修改事务的默认提交方式：
* 查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
* 修改默认提交方式： set @@autocommit = 0;
2. 事务的四大特征：
1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
3. 隔离性：多个事务之间。相互独立。
4. 一致性：事务操作前后，数据总量不变
3. 事务的隔离级别（了解）
* 概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
* 存在问题：
1. 脏读：一个事务，读取到另一个事务中没有提交的数据
2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
* 隔离级别：
1. read uncommitted：读未提交
* 产生的问题：脏读、不可重复读、幻读
2. read committed：读已提交 （Oracle）
* 产生的问题：不可重复读、幻读
3. repeatable read：可重复读 （MySQL默认）
* 产生的问题：幻读
4. serializable：串行化
* 可以解决所有的问题