MYSQL基础
2019-06-03 本文已影响0人
So_ProbuING
MySQL基础
今日目标
1. 数据库介绍
2. MySQL安装和使用
3. sql介绍
sql分类
4.
sql操作库
sql操作表
sql操作数据增删改
sql操作数据查询
5. DQL高级查询
6. 数据库约束
主键约束
7. 数据库备份与还原
一 数据库介绍【了解】
1.1 什么是数据库？
存储数据的仓库,本质上就是存储数据的==文件系统==，方便我们管理数据。
1.2 数据库管理系统
数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件。
数据库管理系统（广泛名词）--> MySQL（具体软件）--> 多个仓库 --> 多张表
1573778868926.png
1.3 实体（类）和表关系
一个实体对应一张表
一个对象（实例）对应一条记录
[图片上传失败...(image-e58b9d-1573810513162)]
1.4 常见关系型数据库
1. MYSQL：开源免费的数据库，小型的数据库.已经被Oracle收购了.MySQL6.x版本也开始收费。
2. Oracle：收费的大型数据库，Oracle公司的产品。Oracle收购SUN公司，收购MYSQL。
3. DB2：IBM公司的数据库产品,收费的。常应用在银行系统中。
4. SQLServer：MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用。
5. SyBase：已经淡出历史舞台。提供了一个非常专业数据建模的工具PowerDesigner。
6. SQLite: 嵌入式的小型数据库，应用在手机端。
三 SQL
3.1 概述
3.1.1 什么是SQL？
结构化查询语言（Structured Query Language）
sql语句操作数据库，实现数据的增删改查
【CRUD】：create、retrieve（检索）、update、delete
3.1.2 SQL方言
SQL是一套标准，所有的数据库厂商都实现了此标准；但是各自厂商在此标准上增加了特有的语句，这部分内容我们称为方言。
举例：MySQL注释 #
3.1.3 SQL书写规范
1. sql语句可以单行或多行书写，最后以分号结尾
2. sql语句（windows环境）不区分大小写，建议关键字大写
3. sql注释
单行
-- 单行注释
# mysql的单行注释
多行
/*
注释内容
*/
3.1.4 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等
5. TCL(Transaction Control Language) 事务控制语言
用于控制数据库的事务操作，关键字; commit,savepoint,rollback等
四 SQL基础操作【重点】
4.1 DDL
1）操作数据库
C：创建
1. 直接创建数据库
语法：
create database 数据库名;
实例：
create database web12;
2. 创建库并指定字符集
语法：
create database 数据库名 charset=字符集;
实例：
create database web12_1 charset=gbk;
R：查询
1. 查看所有数据库
语法：
show databases;
2. 查看建库语句
语法：
show create database 数据库名;
实例：
show create database web12;
U：修改
1. 修改数据库的字符集
语法：
alter database 数据库名 charset=字符集;
实例：
alter database web12_1 charset=utf8;
D：删除
1. 直接删除数据库
语法：
drop database 数据库名;
实例：
drop database web12_1;
使用数据库
1. 查看我在哪个数据库中？
语法：
select database();
2. 使用（切换）数据库
语法：
use 数据库名;
实例：
use web12;
2）操作表
C：创建
1. 创建表
语法：
create table 表名(
列名 数据类型,
列名 数据类型,
列名 数据类型
....
);
实例：
create table student(
id int,
name varchar(32),
birthday date
);
2. 常用数据类型
1）整型：
int
2）浮点型：
double
3）字符串：
varchar(长度)
长度范围：1~65535（中英文及特殊符号）
4）日期：
date（yyyy-MM-dd）
3. 快速创建（复制表）
语法：
create table 新表 like 旧表;
实例：
create table teacher like student;
R：查询
1. 查看库中所有表
语法：
show tables;
2. 查看建表语句
语法：
show create table 表名;
实例：
show create table teacher;
3. 查看表结构
语法：
desc 表名;
实体：
desc teacher;
U：修改【了解】
1. 添加一列
语法：
alter table 表名 add 列名 数据类型;
实例：
alter table teacher add jieshao varchar(66);
2. 修改列的数据类型
语法：
alter table 表名 modify 列名 新数据类型;
实例：
alter table teacher modify jieshao varchar(370);
3. 修改列名
语法：
alter table 表名 change 旧列名 新列名 新数据类型;
实例：
alter table teacher change jieshao intro varchar(888);
4. 删除一列
语法：
alter table 表名 drop 列名;
实例：
alter table teacher drop intro;
5. 修改表名
语法：
rename table 旧表名 to 新表名;
实例：
rename table teacher to tch;
D：删除
1. 直接删除表
语法：
drop table 表名;
实例：
drop table tch;
4.2 DML
1）添加记录
1. 语法：
insert into 表名(列1,列2....) values(值1,值2);
insert into 表名 values(所有的值);
2. 注意
要求列名与值一一对应
字符类型可以使用单双引，推荐单引号
字符串可以设置所有类型，mysql底层进行隐式转换
3. 练习
insert into student(id,name,birthday) values(1,'刘强东','1964-7-1');
insert into student(id,name) values('2','杰克马');
insert into student values(3,'王健林','1960-8-3');
insert into student values(4,'撒贝宁',null);
4. 补充
insert into student values(5,'tom',null),(6,'jerry',null);
蠕虫复制
1. 将一张表的数据快速复制到另一张表，要求：二张表结构相同
2. 步骤
1）克隆表
create table stu like student;
2）蠕虫复制
insert into stu select * from student;
2）修改记录
1. 语法：
update 表名 set 列名1=新值1,列名2=新值2 .... [where 条件];
2. 注意
[] 中括号的内容可以省略
3. 练习
update stu set birthday='1967-1-1';
update student set birthday='1967-1-1' where id = 2;
3）删除记录
1. 语法：
delete from 表名 [where 条件];
2. 注意
[] 中括号的内容可以省略
3. 练习
delete from stu;
delete from student where id = 4;
4. 摧毁表【了解】
特点：将已有表删除，在重建一个新表
语法：
truncate table 表名;
实例：
truncate table student;
4.3 DQL简单查询【重点】
基础数据
-- 创建表
create table student1(
id int,
name varchar(20),
chinese double,
english double,
math double
);
-- 插入记录sss
insert into student1(id,name,chinese,english,math) values(1,'tom',89,78,90);
insert into student1(id,name,chinese,english,math) values(2,'jack',67,98,56);
insert into student1(id,name,chinese,english,math) values(3,'jerry',87,78,77);
insert into student1(id,name,chinese,english,math) values(4,'lucy',88,NULL,90);
insert into student1(id,name,chinese,english,math) values(5,'james',82,84,77);
insert into student1(id,name,chinese,english,math) values(6,'jack',55,85,45);
insert into student1(id,name,chinese,english,math) values(7,'tom',89,65,30);
笔记
1. 语法：
select * from 表名;
2. 对查询结果去重
select distinct 列名 from 表名;
注意：多字段去重要求内容完全一致
3. 在查询结果使用运算符，都会不影响表中原有的数据
4. null与其他数值进行运算，结果还是NULL
ifnull(列名,默认值) 函数
功能：判断如果该列有值就直接返回，如果为null就返回默认值
5. 别名
select 列名 [as] 列别名,.... from 表名 [as] 表别名;
练习
-- 查询表中所有学生的信息
select * from student1;
-- 查询表中所有学生的姓名和对应的语文成绩
select name,chinese from student1;
-- 查询表中学生姓名（去重）
select distinct name from student1;
select distinct name,chinese from student1;
-- 在所有学生数学分数上加10分特长分
select name,math+10 from student1;
-- 统计每个学生的总分
select name,chinese+english+math from student1;
select ifnull(english,0) from student1;
select name,chinese+ifnull(english,0)+math from student1;
-- 使用别名表示学生总分
select name as 姓名,(chinese+ifnull(english,0)+math) as 总分 from student1;
-- 表别名
select stu1.name from student1 as stu1;
五 DQL单表高级查询【重点】
基础数据
-- 建库语句
create database web12_1;
use web12_1;
-- DQL语句 单表查询
-- 创建表
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);
5.1 条件查询
1. 语法：
select ... from 表名 where 条件;
2. 比较（关系）运算符
> < >= <= = !=
3. 逻辑运算符
and：条件同时满足
or：条件满足一个
not：条件取反
4. in关键字（集合）
select ... from 表名 where id in(1,3,5,7,9);
5. between关键字（范围）
select ... from 表名 where 列名 between 较小的值 and 较大的值;
6. null这哥们六亲不认
select ... from 表名 where 列名 is null; 查询为null的
select ... from 表名 where 列名 is not null;查询不为null的
7. like关键字（模糊）
select ... from 表名 where 列名 like '通配符字符串'
_ 单个任意字符
% 多个任意字符
# 关系运算符
-- 查询math分数大于80分的学生
SELECT * FROM student WHERE math > 80;
-- 查询english分数小于或等于80分的学生
SELECT * FROM student WHERE english <=80;
-- 查询age等于20岁的学生
SELECT * FROM student WHERE age = 20;
-- 查询age不等于20岁的学生
SELECT * FROM student WHERE age != 20;
# 逻辑运算符
-- 查询age大于35且性别为男的学生(两个条件同时满足)
SELECT * FROM student WHERE age > 35 AND sex = '男';
-- 查询age大于35或性别为男的学生(两个条件其中一个满足)
SELECT * FROM student WHERE age >35 OR sex = '男';
-- 查询id是1或3或5的学生
SELECT * FROM student WHERE id = 1 OR id = 3 OR id = 5;
-- in关键字
-- 再次查询id是1或3或5的学生
SELECT * FROM student WHERE id IN (1,3,5,7,9);
-- 查询id不是1或3或5的学生
SELECT * FROM student WHERE id NOT IN (1,3,5,7,9);
-- 查询english成绩大于等于77，且小于等于87的学生
SELECT * FROM student WHERE english >=77 AND english <=87;
SELECT * FROM student WHERE english BETWEEN 77 AND 87;
-- 查询英语成绩为null的学生
SELECT * FROM student WHERE english IS NULL;
SELECT * FROM student WHERE english IS NOT NULL;
# like模糊匹配
-- 查询姓马的学生
SELECT * FROM student WHERE `name` LIKE '马%';
-- 查询姓名中包含'德'字的学生
SELECT * FROM student WHERE `name` LIKE '%德%';
-- 查询姓马，且姓名有三个字的学生
SELECT * FROM student WHERE `name` LIKE '马__';
5.2 排序
1. 语法：
select ... from 表名 order by 列名 [asc|desc],列名 [asc|desc];
asc：升序 （默认）
desc：降序
2. 注意：
后面排序字段在前面排序完成基础上在进行排序
# 排序
-- 查询所有数据,使用年龄降序排序
SELECT * FROM student ORDER BY age DESC;
-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序
SELECT * FROM student ORDER BY age DESC,math DESC;
5.3 聚合函数
功能：对一列进行计算，返回一个结果，忽略null值
# 常用聚合函数
count(列名)：统计个数
补充：对一列统计推荐使用 count(*)
sum(列名)： 求和
avg(列名)： 平均值
max(列名)： 最大值
min(列名)： 最小值
# 聚合函数
-- 查询学生总数（null值处理）
SELECT COUNT(id) FROM student;
SELECT COUNT(IFNULL(english,0)) FROM student;
SELECT COUNT(*) FROM student;
-- 查询年龄大于40的总数
SELECT * FROM student WHERE age > 40;
SELECT COUNT(*) FROM student WHERE age > 40;
-- 查询数学成绩总分
SELECT SUM(math) FROM student;
-- 查询数学成绩平均分
SELECT AVG(math) FROM student;
-- 查询数学成绩最高分
SELECT MAX(math) FROM student;
-- 查询数学成绩最低分
SELECT MIN(math) FROM student;
5.4 分组
功能：对一列进行分组，把相同的内容分为一组，通常与聚合函数一起使用，完成统计工作
1. 语法:
select 分组列名 from 表名 group by 分组列名 having 分组后条件;
2. 区别：
where：在分组前进行条件过滤，不能使用聚合函数
having：在分组后进行条件过滤，可以使用聚合函数
-- 查询男生和女各多少人
SELECT COUNT(*) FROM student WHERE sex = '男';
SELECT COUNT(*) FROM student WHERE sex = '女';
SELECT sex,COUNT(sex) FROM student GROUP BY sex;
-- 作业：按照地址分组，统计各个地区人数
SELECT address,COUNT(address) FROM student GROUP BY address;
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数
-- 1.查询年龄大于25岁的人
SELECT * FROM student WHERE age > 25;
-- 2.按性别分组
SELECT sex FROM student WHERE age > 25 GROUP BY sex;
-- 3.统计每组的人数
SELECT sex,COUNT(sex) FROM student WHERE age > 25 GROUP BY sex;
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
-- 错误 Invalid use of group function
SELECT sex,COUNT(sex) FROM student WHERE age > 25 AND COUNT(sex)>2 GROUP BY sex;
-- 正确
SELECT sex,COUNT(sex) FROM student WHERE age > 25 GROUP BY sex HAVING COUNT(sex)>2;
5.5 分页
补充基础数据
# 分页
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);
1. 语法：
select ... from 表名 limit 开始索引,每页个数
2. 注意：
索引是从0开始的，默认值也为0
3. 索引公式：
索引= (当前页-1) × 每页个数
-- 查询学生表中数据，从第三条开始显示，显示6条
SELECT * FROM student LIMIT 2,6;
-- 查询学生表中数据，显示前6条
SELECT * FROM student LIMIT 0,6;
SELECT * FROM student LIMIT 6;
-- 模拟百度分页，一页显示5条
-- 第一页
SELECT * FROM student LIMIT 0,5;
-- 第二页
SELECT * FROM student LIMIT 5,5;
-- 第三页
SELECT * FROM student LIMIT 10,5;
5.6 知识小结
执行顺序
select * from 表名 where 条件 group by 分组 having 条件 order by 排序 limit 分页;
六 数据库约束【理解】
6.1 概述
作用
对表中的数据进行限定，保证数据的正确性、有效性和完整性。
分类
1. 主键约束：primary key【重点】
特点：唯一 + 非空 = 主键 ，通常使用id作为主键
2. 唯一约束：unique
3. 非空约束：not null
4. 默认值约束：default
-------------------------------------------------------
5. 外键约束：foreign key
6.2 实现
6.2.1 主键约束
作用：限定某一列的值非空且唯一， 主键就是表中记录的唯一标识。
1. 添加主键约束
1）创建表【掌握】
create table 表名(
id int primary key,
...
...
...
);
2）修改表【了解】
alter table 表名 add primary key(id);
2. 主键自增器
1）创建表【掌握】
create table 表名(
id int primary key auto_increment,
...
...
...
);
3. truncate 和 delete 区别
4. 删除主键：【了解】
1）先删除自增器
alter table 表名 modify id int;
2）再删除主键
alter table 表名 drop primary key;
5. 注意：一张表只能有一个主键，但是我们可以设置联合主键
-- 修改表添加主键
ALTER TABLE student ADD PRIMARY KEY(id);
-- 创建表添加主键
CREATE TABLE stu1(
id INT PRIMARY KEY,
`name` VARCHAR(32)
);
INSERT INTO stu1 VALUES(1,'jack');
-- 唯一 Duplicate entry '1' for key 'PRIMARY'
INSERT INTO stu1 VALUES(1,'tom');
-- 非空 Column 'id' cannot be null
INSERT INTO stu1 VALUES(NULL,'tom');
-- 创建表添加主键和自增器
CREATE TABLE stu2(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32)
);
INSERT INTO stu2 VALUES(1,'lucy');
INSERT INTO stu2 VALUES(NULL,'jerry');
-- delete 删除
DELETE FROM stu2;
INSERT INTO stu2 VALUES(NULL,'jerry');
-- truncate 删除
TRUNCATE TABLE stu2;
INSERT INTO stu2 VALUES(NULL,'jerry');
-- 主键删除
ALTER TABLE stu2 MODIFY id INT;
ALTER TABLE stu2 DROP PRIMARY KEY;
-- 错误 Multiple primary key defined
CREATE TABLE stu3(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32) PRIMARY KEY
);
-- 联合主键
CREATE TABLE stu3(
id INT ,
`name` VARCHAR(32),
PRIMARY KEY(id,`name`)
);
INSERT INTO stu3 VALUES(1,'jack');
INSERT INTO stu3 VALUES(1,'lucy');
-- 唯一 Duplicate entry '1-lucy' for key 'PRIMARY'
INSERT INTO stu3 VALUES(1,'lucy');
6.2.2 唯一约束
作用：限定某一列的值不能重复
1. 创建表
create table 表名(
列名 数据类型 unique,
...
..
);
2. 注意：可以出现多个null
6.2.3 非空约束
作用：限定某一列的值不能为null
1. 创建表
create table 表名(
列名 数据类型 not null,
...
..
);
-- 创建唯一约束
CREATE TABLE stu4(
id INT,
`name` VARCHAR(32) UNIQUE
);
INSERT INTO stu4 VALUES(1,'lucy');
-- 唯一 Duplicate entry 'lucy' for key 'name'
INSERT INTO stu4 VALUES(2,'lucy');
INSERT INTO stu4 VALUES(3,NULL);
INSERT INTO stu4 VALUES(4,NULL);
-- 创建唯一和非空
CREATE TABLE stu5(
id INT,
`name` VARCHAR(32) UNIQUE NOT NULL
);
INSERT INTO stu5 VALUES(1,'james');
-- 非空 Column 'name' cannot be null
INSERT INTO stu5 VALUES(2,NULL);
6.2.4 默认值
作用：限定某一列的默认值，再没有指定的情况下所有列的默认值为null
1. 创建表
create table 表名(
列名 数据类型 default 默认值,
...
);
-- 创建默认值约束
CREATE TABLE stu6(
id INT DEFAULT NULL,
`name` VARCHAR(32) DEFAULT NULL,
sex VARCHAR(5) DEFAULT '男'
);
INSERT INTO stu6(id,`name`) VALUES(1,'李圣');
INSERT INTO stu6 VALUES (2,'陈丽琼','女');
INSERT INTO stu6 VALUES(3,'高哲',NULL);
七 数据库备份与还原【了解】
1. dos命令行
1）备份
mysqldump -u用户名 -p 需要备份的数据库名 > 指定输出路径(*.sql)
输入密码
注意：只有建表和数据，没有库的信息，需要大家手动添加...
2）还原
mysql -uroot -p < 指定输入路径(*.sql)
输入密码
2. 图形化工具【推荐】
Navicat
傻瓜式操作
