MySQL相关知识点
MySQL
1.数据库的基本概念
1.数据库的英文单词:DataBase 简称:DB
2.什么数据库?
*用于存储和管理数据的仓库
3.数据库的特点
1.持久化储数据的。其实数据库就是一个文件系统
2.方便存储和管理数据
3.使用了统一的方式操作数据库 ----SQL
-
常见的数据库软件
参见<MySQL基础>
2.MySQL数据库软件
1.安装
- 参见<MySQL基础.pdf>
2.卸载
- 去MySQL的安装目录找到my.ini文件
- 复制 datadir = “C:/ProgramData/MySQL/MySQL Server 5.5/Data/”
- 卸载MYSQL
- 最后删除C:/ProgarmData目录下的MySQL文件夹.
3.配置
-
MySQL服务启动
1.手动.
2.cmd-—>services.msc 打开服务的窗口 3.使用管理员打开cmd net start mysql :启动mysql服务 net stop mysql:关闭mysql服务
-
MySQL的登录
- mysql -uroot -proot
- mysql -uroot -p
- mysql --host=ip --user=root --password=root
-
MySQL退出
1.exit或者quit
-
MySQL目录结构
配置文件 my.ini
- MySQL数据目录
- 几个概念
- 数据库:文件夹
- 表:文件
- 数据
3.SQL
1.什么是SQL
Structured Query Language:结构化查询语言
其实就是定义了操作所有关系型数据库的规则
2.SQL的通用语法
SQL 语法可以单行或者多行书写,以分号结尾
可使用空格和缩进来增强语句的可读性
MySQL数据库的SQL语句不区分大小写,关键字建议使用大写.
3种注释
- 单行注释:-- 注释内容 或 # 注释内容(mysql 特有)
- 多行注释:/* 注释*/
show databases; --注释内容 show databases; #注释内容 show databases;/*多行注释*/
3.SQL分类
- DDL(Data Definition Language)数据定义语言
- 用来定义数据库对象:数据库,表,列等.关键字:create ,drop,alter等
- DML(Data Manipulation Language)数据操作语言
- 用来对数据库中表的数据进行增删改.关键字:insert, delete, update等
- DQL(Data Query Language)数据查询语言
- 用来查询数据库中表的记录(数据).关键字:select, where等
- DCL(Data Control Language)数据控制语言(了解)
- 用来定义数据库的访问权限和安全级别,及创建用户.关键字:GRANT,REVOKE等
1.操作数据库: CRUD
1.C(Create):创建
#创建数据库
create database 数据库名称;
#创建数据库,判断不存在,再创建
create database if not exists 数据库名称;
#创建数据库,并指定字符集
create database 数据库名称 character set 字符集名;
#创建db4数据库,判断是否存在,并制定字符集为gbk
create database if not exists db4 character set gbk;
2.R(Retrieve):查询
#查看所有的数据库 show databases; #查询某个数据库的字符集:查询某个数据库的创建语句 show create database 数据库名称;
3.U(Update):修改
#修改数据库的字符集
alter database 数据库名称 character set 字符集名称; #(utf8 gbk)
4.D(Delete):删除
#删除数据库
drop database 数据库名称;
#判断数据库存在,存在再删除
drop database if exists 数据库名称
5.使用数据库
#查询当前正在使用的数据库名称
select database();
#使用数据库
use 数据库名称;
2.操作表
1.C(Create):创建表
#创建表语法
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
...
列名n 数据类型n
);
###注意最后一列不需要加逗号(,)
数据库类型:
int: 整数类型
- age int,
double:小数类型
*score double(5,2)
date:日期,只包含年月日, yyyy-MM-dd
datatime:日期,包含年月日时分秒 yyyy-MM-dd HH:mm:ss
timesstamp:时间戳类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
- 如果将来不给这个字段赋值,或者赋值为null,则默认使用当前的系统时间,自动赋值
- 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):修改
# 修改表名
alter table 表名 rename to 新的表名;
# 显示表的创建字符集
show create table 表名字;
# 修改表的字符集
alter table 表名 character set 字符集名称;
# 添加一列
alter table 表名 add 列名 数据类型;
# 修改列名称 类型
alter table 表名 change 列名 新列名 新数据类型;
alter table 表名 modify 列名新数据类型;
# 删除列
alter table 表名 drop 列名;
4.D(Delete):删除
# 删除表
drop table 表名;
# 判断如果表存在就进行删除
drop table if exists 表名;
3.DML-增删改表中的数据
1.添加数据:
# 语法 插入数据
insert into 表名(列名1,列名2,列名3...列名n) values(值1,值2...值n);
#注意的事项
1.列名和值要一一对应。
2.如果表名后,不定义列名,则默认给所有列添加值
insert into 表名 values(值1,值2,值3...值n)
3.除了数字类型,其他类型需要使用引号包裹起来(单引号或者双引号都可以)
2.删除数据
# 语法:
delete from 表名 where(条件); #delete from stu where id=1;
注意:
1.如果不加条件,则删除表中所有记录
2.如果要删除所有记录
1.delete from 表名; -- 不推荐使用。 有多少条记录就会执行多少次删除操作
2.TRUNCATE TABLE 表名; -- 先删除表(清空表内容),然后再创建一张一样的表
3.修改数据
# 语法:
update 表名 set 列名1 = 值1, 列名2 = 值2,...[where 条件]; UPDATE stu SET gendex="男" WHERE id = 1;
# 注意:
1.如果不加任何条件,则会将表中所有记录全部修改
4.DQL-查询表中的记录
# 查询表中所有的数据
select * from stu;
# 复制旧表的格式和内容到到新表
CREATE TABLE 新表名字 SELECT * FROM 旧表名字;
1.语法
# 语法
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
2.基础查询
1.多个字段的查询
# 多个字段的查询
select 字段名1,字段名2....from 表名;
# 注意:
*如果查询所有字段,则可以使用*来代替字段列表
2.去除重复
#去除重复
distinct # SELECT DISTINCT address FROM people; 去除相同地址的
3.计算列
# 一般可以使用四则运算计算一些列的值.(一般只会进行数值型的计算)
#计算 math + english 的和
SELECT NAME, math,english,math+english FROM people;
# 计算math + english的和
# IFNULL(表达式1,表达式2):null参与的运算,计算结果都为null
# 表达式1:哪个字段需要判断是否为null 如果该字段为null后的替换值
SELECT NAME, math, english,IFNULL(math,0) + IFNULL(english,0) FROM people;
4.起别名
# as 起别名 当然as也可以省略
SELECT NAME, math, english,IFNULL(math,0) + IFNULL(english,0) AS 总分 FROM people;
SELECT NAME AS 姓名, math 数学, english 英语,IFNULL(math,0) + IFNULL(english,0) AS 总分 FROM people;
3.条件查询
1.where子句后跟条件
2.运算符
>, <, <=, >=, =, <>
-- 查询年龄大于20岁 SELECT * FROM people WHERE age>20; -- 查询年龄大于等于20岁的人 SELECT * FROM people WHERE age>=20; -- 查询年龄等于20岁的人 SELECT * FROM people WHERE age = 20; -- 查询年龄小于20岁的人 SELECT * FROM people WHERE age<20; -- 查询年龄不等于20岁的人 SELECT * FROM people WHERE age != 20; SELECT * FROM people WHERE age <> 20;
BETWEEN...AND
-- 查询年龄大于等于20小于等于30
SELECT * FROM people WHERE age BETWEEN 20 AND 30;
IN(集合)
-- 查询年龄22岁, 19岁, 25岁的信息 SELECT * FROM people WHERE age =25 OR age=18 OR age =22; SELECT * FROM people WHERE age IN(25,18,22);
LIKE
IS NULL
-- 查询英语成绩为null SELECT * FROM people WHERE english = NULL; -- 不对的 null值不能使用 = (!=)判断 -- 查询英语成绩为null 判断null的正确写法 SELECT * FROM people WHERE english IS NULL; -- 查询英文成绩不为NULL SELECT * FROM people WHERE english IS NOT NULL;
and 或 &&
SELECT * FROM people WHERE age>20 && age<=30; SELECT * FROM people WHERE age>=20 AND age<=30;
or 或 ||
-- 查询年龄22岁, 19岁, 25岁的信息 SELECT * FROM people WHERE age =25 OR age=18 OR age =22;
not 或 !
-- 查询年龄不等于20岁的人 SELECT * FROM people WHERE age != 20;
-- 查询英文成绩不为NULL
SELECT * FROM people WHERE english IS NOT NULL;
4.条件模糊查询
-- 模糊查询
SELECT * FROM people WHERE NAME LIKE '马%';
-- 查询第二个字是化的人
SELECT * FROM people WHERE NAME LIKE "_化%";
-- 查询姓名是三个字的人
SELECT * FROM people WHERE NAME LIKE "___";
-- 查询姓名中包含马的人
SELECT * FROM people WHERE NAME LIKE "%德%";
MYSQL约束
1.DQL查询语句
1.排序查询
# 语法:order by 子句
order by 排序字段1 排序方式1, 排序字段2 排序方式2...
# 排序方式:
* ASC升序,默认的
SELECT * FROM people ORDER BY math ASC; -- 默认的排序方式 -升序
* DESC降序
SELECT * FROM people ORDER BY math DESC -- 排序方式 -降序
# 注意:
* 如果有多个排序条件,则当前前边的条件值一样时,才会判断第二个条件.
-- 按照数学成绩排名,如果数学成绩一样, 则按照英语成绩排名
SELECT * FROM people ORDER BY math DESC,english DESC;
2.聚合函数
# 将一列数据作为一个整体,进行纵向的计算。
1. count:计算个数
1.一般选择非空的列: 主键
2.count(*)
-- 计算多少个人
SELECT COUNT(NAME) AS 个数 FROM people;
-- 统计非空的列
SELECT COUNT(IFNULL(english,0)) FROM people;
# 2.max:计算最大值
SELECT MAX(math) FROM people;
# 3.min:计算最小值
SELECT MIN(math) FROM people;
# 4.计算和
SELECT SUM(math) FROM people;
# 5.avg:计算平均值
SELECT AVG(math) FROM people;
聚合函数的计算,排除null值
解决方案:
- 选择不包含非空的列进行计算
- IFNULL函数
3.分组查询
- 语法:group by 分组字段;
- 注意:
1. 分组之后查询的字段:分组字段,聚合函数
2. where 和 having的区别?
1. where 在分组之前进行限定, 如果不满足条件,则不参与分组, having 在分组之后进行限定, 如果不满足结果,则不会被查询出来
2. where后不可以跟聚合函数,having可以进行聚合函数的判断-- 按照性别分组, 分别查询男,女同学的平均分,人数 SELECT sex,AVG(math) 数学平均分,COUNT(id) 人数 FROM people GROUP BY sex; -- 按照性别分组, 分别查询男,女同学的平均分,人数 要求:分数低于70分的人,不参与分组 SELECT sex,AVG(math) 数学平均分,COUNT(id) 人数 FROM people WHERE math > 70 GROUP BY sex; -- 按照性别分组, 分别查询男,女同学的平均分,人数 要求:分数低于70分的人,不参与分组,分组之后,人数大于2个人 SELECT sex,AVG(math) 数学平均分,COUNT(id) 人数 FROM people WHERE math > 70 GROUP BY sex HAVING COUNT(id)>1; SELECT sex,AVG(math) 数学平均分,COUNT(id) 人数 FROM people WHERE math > 70 GROUP BY sex HAVING 数学平均分>1;
4.分页查询
语法:limit 开始的索引,每页查询的条数
公式: 开始的索引 = (当前的页面 - 1) * 每也显示的条数
-- 每页显示3条记录 SELECT * FROM people LIMIT 0,3; -- 第一页 SELECT * FROM people LIMIT 3,3; -- 第二页 SELECT * FROM people LIMIT 6,3; -- 第三页 SELECT * FROM people LIMIT 9,3; -- 第四页
- limit是一个MYSQL“方言”
2.约束
概念: 对表中的数据进行限定, 保证数据的正确性,有效性和完整性。
分类:
- 主键约束: primary key
- 非空约束:not null
- 唯一约束: unique
- 外键约束: foreign key
1.非空约束:not null
创建表时添加约束
-- 创建表添加非空约束 CREATE TABLE st( id INT, NAME VARCHAR(32) NOT NULL -- name为非空 );
创建表完后,添加非空约束
```mysql -- 创建表完后,添加非空约束 ALTER TABLE st MODIFY NAME VARCHAR(20) NOT NULL; ```
- 删除name的非空约束
```mysql -- 删除name的非空约束 ALTER TABLE st MODIFY NAME VARCHAR(20); ```
2.唯一约束: unique,值不能重复
- 创建表, 添加唯一约束
-- 创建表, 添加唯一约束 CREATE TABLE st1( id INT, phone_number VARCHAR(20) UNIQUE -- 添加唯一约束 ); -- 注意mysql中, 唯一约束限定的列的值可以有多个null
- 删除唯一约束
# 删除唯一约束 ALTER TABLE st1 DROP INDEX phone_number;
- 在创建表后,添加唯一约束
-- 在创建表后,添加唯一约束 ALTER TABLE st1 MODIFY phone_number VARCHAR(20) UNIQUE;
-
主键约束:Primary key
-
注意:
- 含义:非空且唯一
- 一张表只能有一个字段为主键
- 主键就是表中记录的唯一标识
-
在创建表时, 添加主键约束
# 添加主键约束 CREATE TABLE st2( id INT PRIMARY KEY, -- 给id添加主键约束 NAME VARCHAR(32) );
-
删除主键约束
-- 删除主键 ALTER TABLE st2 DROP PRIMARY KEY;
-
创建完表后,添加主键约束
-- 创建表后,添加主键 ALTER TABLE st2 MODIFY id INT PRIMARY KEY;
-
自动增长:
# 1.概念: 如果某一列是数值类型的,使用auto_increment可以来完成值得自动增长 # 2.在创建表时,添加主键约束,并且完成主键自增长 CREATE TABLE st3( id INT PRIMARY KEY AUTO_INCREMENT, -- 给id添加主键约束 NAME VARCHAR(20) ); -- 删除自动增长 ALTER TABLE st3 MODIFY id INT; -- 添加自动增长 ALTER TABLE st3 MODIFY id INT AUTO_INCREMENT; SELECT * FROM st3; INSERT INTO st3 VALUES(1,"aaa"); INSERT INTO st3 VALUES(NULL,"bbb"); INSERT INTO st3 VALUES(NULL,"zzz");
-
4.外键约束:foreign key,让表与表产生关系,从而保证数据的正确性。
# 1.在创建表时,可以添加外键 * 语法: create table 表名( 语句1, 语句2, 语句3 .... 外键列 constraint 外键名称 foreign key(外键列名称) reference 主表名称(主表列名称) ); # 2.删除外键 ALTER TABLE 表名 DROP FOREIGN KEY 外键的名称; # 3.创建表之后,添加外键 ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES department(主表列名称); # 4.级联操作 1. 添加级联操作 语法: ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称) REFERENCES department(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE; 2. 分类: 1.级联更新: ON UPDATE CASCADE 2.级联删除: ON DELETE CASCADE
3.多表之间的关系
-
多表之间的关系
- 分类:
- 一对一(了解):
- 如: 人和身份证
- 分析:一个人只有一个身份证,一个身份证只能对应一个人
- 一对多(多对一):
- 如: 部门和员工
- 分析:一个部门有多个员工,一个员工只能对应一个部门
- 多对多:
- 如:学生和课程
- 分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择
- 实现关系:
- 一对多(多对一):
- 如:部门和员工
- 实现方式:在多的一方建立外键,指向有的一方的主键.
- 多对多:
- 如:学生和课程
- 实现方式:多对多关系实现需要借助第三张中间表,中间表至少包含两个字段,这个两个字段作为第三张表的外键,分别指向两张表的主键
- 一对一(了解):
- 如:人和身份证
- 实现方式:一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键.
- 一对多(多对一):
- 分类:
4.范式
5.数据库的备份和还原