一.数据库介绍
二.MySQL数据库安装和使用
三.SQL语句
3.3. SQL语句分类
- 数据定义语言:简称DDL(Data Definition Language),用来定义数据库对象:数据库,表,列等。关键字:creat,alter,drop等。
- 数据操作语言:简称DML(Data Manipulation Language),用来对数据库中表的记录进行更新。关键字:insert,delete,update等。
- 数据控制语言:简称DCL(Data Control Language),用来定义数据库的访问权限和安全级别,及创建用户。
- 数据查询语言:简称DQL(Data Query Language),用来查询数据库中表的记录。关键字:select,from,where等。
3.4. SQL通用语法
- SQL语句可以单行或多行书写,以分号结尾。
- 可使用空格和缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。例如:SELECT * FROM user。
- 同样可以使用/**/的方式完成注释 – #
- MySQL中的我们常使用的数据类型如下
分类 | 类型名称 | 说明 |
---|---|---|
整数类型 | tinyInt | 很小的整数 |
整数类型 | smallint | 小的整数 |
整数类型 | mediumint | 中等大小的整数 |
整数类型 | int(integer) | 普通大小的整数 |
小数类型 | float | 单精度浮点数 |
小数类型 | double | 双精度浮点数 |
小数类型 | decimal(m,d) | 压缩严格的定点数 decimal(10,2) |
日期类型 | year | YYYY 1901~2155 |
日期类型 | time | HH:MM:SS -838:59:59~838:59:59 |
日期类型 | date | YYYY-MM-DD 1000-01-01~9999-12-3 |
日期类型 | datetime | YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59 |
日期类型 | timestamp | YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC |
文本、二进制类型 | char(m) | m为0~255之间的整数定长 char(10) tom |
文本、二进制类型 | varchar(m) | m为0~65535之间的整数变长 varchar(10) tom |
文本、二进制类型 | tinyblob | 允许长度0~255字节 |
文本、二进制类型 | blob | 允许长度0~65535字节 |
文本、二进制类型 | mediumblob | 允许长度0~167772150字节 |
文本、二进制类型 | longblob | 允许长度0~4294967295字节 |
文本、二进制类型 | tinytext | 允许长度0~255字节 |
文本、二进制类型 | text | 允许长度0~65535字节 |
文本、二进制类型 | mediumtext | 允许长度0~167772150字节 |
文本、二进制类型 | longtext | 允许长度0~4294967295字节 |
文本、二进制类型 | varbinary(m) | 允许长度0~M个字节的变长字节字符串 |
文本、二进制类型 | binary(m) | 允许长度0~m个字节的定长字节字符串 |
四.DDL之数据库操作
- DDL之数据库定义
- 语法:
create database bigdata; ##创建数据库
create database if not exists bigdata; ##创建数据库的时候检查数据库是否存在
show databases; ##显示当前MySQL下面有哪些数据库,一定要加s,否则报错
use bigdata; ##使用数据库bigdata,使用表的时候一定要指出是哪个数据库下面的表
select database(); ##查看当前正在使用的是哪个数据库
drop database bigdata; ##删除数据库
mysql -uroot -p123456; ##进入mysql
五.DDL之表操作
- DDL之表操作
- 语法:
use bigdata; ##使用数据库
create table student(
name varchar(2000),
age int,
sex char(10)
); ##创建一张表student
show tables; ##查看使用的数据库里面有哪些表
desc student; ##查看表结构
drop table student; ##删除表
- DDL之表字段的操作
- 语法:
##1.添加字段
alter table student add address varchar(200); 给表添加字段,如果添加的字段为MySQL关键字就一定要加反引号
##2.删除字段
alter table student drop address;
##3.修改表字段
alter table student change sex gender varchar(20);
##4.修改表名称
rename table student to stu;
六.DML数据库操作语言
- DML之数据操作语言
- 插入:insert
insert into stu value('张三',21,'男');
insert into stu(name) values('李四');
insert into stu(name,age,gender) values('王五',30,'女');
insert into stu(name,gender,age) values('赵六','女',18);
insert into stu values('王五',22,'女'),('王五',23,'女'),('王五',24,'女');
/*注意:
1.字段的个数要与给的值的个数匹配
2.字段的类型要与给的值的类型对应上
3.字段的顺序要与给的值的顺序对应上*/
- 插入:update
update teacher set name='zhangsan' where id=11112; ##加where条件修改部分数据
update teacher set name='李四' where id=9876 and subject='计算机科学'; ##多条件更新数据
/*注意:
1.默认是整表的修改数据,一定要加上where条件
2.update teacher set name='learnit'; 全表修改数据 */
- 删除数据:delete
delete from stu2; 删除整个表的数据
delete from stu2 id=3; ##删除表里面的部分数据,根据where条件判断
create table stu2(
id int primary key auto_increment,
name varchar(20)
);
truncate stu2; ##先删除表,然后新建一张一模一样的表
七.SQL约束
- 主键约束,primary key
- not null,不能为空
- 被主键修饰的字段不能为null
- unique,唯一性
- 被主键修饰的字段的值必须唯一
- 删除主键
create table stu3(
id int primary key,
name varchar(255),
gender varchar(2)
)
alter table stu3 drop primary key; ##删除主键约束
create table human(
first_name varchar(255),
last_name varchar(255),
address varchar(255),
city varchar(255)
constraint pk_humanid primary key(first_name,last_name)
) ##联合主键:将first_name,last_name形成联合主键名字叫做pk_humanid
- 自动增长列,auto increment
- 只能修饰在int 类型的字段上
- 一般结合主键使用
- 修饰字段的默认值是从1开始,但是可以设置初始值
create table stu4(
id int primary key auto_increment,
name varchar(255),
city varchar(255)
) ##自动增长列
alter table stu4 auto_increment=100 ##设置初始值,默认自动增长初始值为1
- 非空约束
not null
create table stu5(
id int not null,
name varchar(255) not null,
address varchar(255)
) ##NOT NULL 约束强制字段始终包含值。这意味着,如果不向字段添加值,就无法插入新记录或者更新记录。
- 唯一约束
unique
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意,每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。
create table stu6(
id int unique,
name varchar(255) not null,
address varchar(255)
)
- 外键约束
foreign key
FOREIGN KEY 表示外键约束
八.DQL操作
- 数据查询语言:
select * from product;
select pname,price from product; ## 只取表里面的部分数据
select distinct category_id from product; ## 对分类id进行去重拿到去重之后的结果
select distinct price,category_id from product; ## distinct一次对多个字段进行去重的时候,就是将多个字段的值先拼接在一起,再去重,且distinct只能紧跟select,不能在字段之间
select pname as lalala,pname aaa from product t1; ## 对字段和表取别名,as可以省略不写
select t1.pname,price from product t1; ## 取别名之后可以使用原表名也可以使用别名引用表里面的字段
select price*0.7+13 from product; ## select后面可以进行四则运算
- 条件查询:
select * from product where category_id='c002';
select * from product where price<=2000 and price >=200;
select * from product where price between 200 and 2000;
select * from product where category_id != 'c002';
select * from product where category_id <> 'c002'; ## <>等同于!=
select * from product where category_id in ('c002','c003'); ## 查询category_id为c002和c003的产品
case when:case when基本语法
- 模糊匹配:关键字 like
- %:任意长度任意字符
- _:一个长度的任意字符
select * from product where pname like '%想%';
select * from product where pname like '_想%';
select * from product where pname like '香%';
select * from product where pname like '_霸';
select * from product where pname like '%斯';
- null的判断:is null \ is not null
select * from product where category_id is not null;
select * from product where not category_id is null;
select * from product where category_id is null;
- 逻辑运算符:and or not
## and 交集
select * from product where category_id='c001' and price>3000;
## or 并集
select * from product where price<100 or price=400;
## not 取反
select * from product where price<100 or not price=440;
- 排序 order by:
- 升序:asc,默认是升序
- 降序:desc
select * from product order by price desc; ## 注意:根据数值类型的字段进行排序
select * from product order by category_id asc,price desc; ## 解释:先对第一个排序字段的值排序,当第一个字段的值有相等的结果的时候,再对第二个字段的值进行排序
- 聚合函数
- count(1):计算满足条件的数据量
- max():求最大值
- min():求最小值
- avg():求平均值
- sum():求和
select count(1) from product where price>200;
select max(price) max_p from product;
select min(price) from product;
select avg(price) from product;
select sum(price) from product;
- 分组查询 :group by
## 按照什么进行分组,就 group by 什么,group by 什么,select 就只能写什么,除了可以加聚合函数
select sex,count(1) from person group by sex;
select category_id,count(1) from product group by category_id;
- 二次过滤:having
## having:紧跟group by分组之后的二次过滤
## having 可以写聚合函数,但是where里面不能写聚合函数
## where是第一次过滤,having是第二次过滤
select category_id, count(1) cn
from product
where category_id is not null or category_id != ''
group by category_id
having cn > 1
order by cn desc;
- 取有限条数据,有分页功能:limit
select * from product limit 10; ##查看有限条数据
Limit M,N => M ##从0开始,起始位置,N代表每一页的数据量大小
select * from product limit 1,5;
- 插入结果数据到结果表
insert into:将结果数据写入对应的表
需求:按性别统计人数
select sex,count(1) as cn from person group by sex;
## 创建表:
create table sex_cn(sex varchar(6),cn int);
## 将结果写入结果表:
insert into sex_cn select sex,count(1) as cn from person group by sex;
create table sex_cn2 as select sex,count(1) as cn from person group by sex;
- 主外键关系:一个表的外键指向另外一个表的主键,形成主外键关系
- 主表:一个表的主键被另外一个表的外键依赖,就是主表
- 从表:有外键的那个表
- 外键:从表的外键
- 主键:主表的主键
注意:添加数据的时候从主表开始,删除数据从从表开始
九.多表操作
9.1 多表联查(重点)
- 内连接
## 隐式内连接
select * from products,category;
select * from products,category where category_id=cid;
## 显示内连接:inner join on(连接条件)
select * from products inner join category;
select * from products inner join category where category_id=cid;
## inner 可以不要
select * from products inner join category on category_id=cid;
重点把握:2个或者多个表的连接条件是什么
- 外连接:left join,right join
## 基表不同,left join以左数据表为基表,right join以右数据表为基表
select * from products left join category on category_id=cid;
select * from products right join category on category_id=cid;
- 子查询:将查询出来的结果当作一个值、一个集合或者一个表
## 查询是化妆品分类的产品
select * from products,category where category_id=cid and cname='化妆品';
select * from products where category_id = (select cid from category where cname='化妆品');
select * from products,category where category_id=cid and cname='化妆品';
select * from products where category_id = (select cid from category where cname='化妆品');
select cid from category where cname='化妆品' or cname='家电';
select * from products where category_id in (select cid from category where cname='化妆品' or cname='家电');
select * from products inner join (select cid from category where cname='化妆品' or cname='家电') t1 on products.category_id=t1.cid;
十.MySQL索引
- 索引:
- MySQL里面索引是B+树
- 索引的作用:提高检索(查询)速度,快速定位要查找的内容
- 索引的缺点:
- 在有索引的表里面,添加或者删除数据,会影响到索引文件,因为添加或者删除数据,都需要对索引文件进行修改
- 索引文件在查询表数据的时候需要加载到内存里面,需要耗费资源
- 索引不是随便创建的,只有在一个字段经常用于查询的时候,可以对这个字段创建索引
添加索引
alter table products add index index_comment(price);
十一.开窗函数
- 开窗函数:在mysql里面是在8.0版本以后才有
- row_number() over()
- rank() over()
- dense_rank() over()
主要解决排名问题,只要需求是说我要排名,就想到窗口函数
select ename,salary, row_number() over(partition by deptid order by salary) row1,
rank() over (partition by deptid order by salary) row2,
dense_rank() over (partition by deptid order by salary) row3
from employee;
row_number(),rank(),dense_rank()这三个函数都是用于返回结果集的分组内每行的排名
三者区别:
row_number:不管排名是否有相同的,都按照顺序1,2,3……n
rank:排名相同的名次一样,同一排名有几个,后面排名就会跳过几次
dense_rank:排名相同的名次一样,且后面名次不跳跃
练习1
题目:
1.增加一个字段salary int
2.修改category表,将cname都改成’特斯拉’
3.修改category表,将salary在1000到9999这个范围里面的cname改成’不差钱’
4.修改category表,将salary增加1000
答案:
create table category(
cid varchar(20) primary key,
cname varchar(100)
);
insert into category(cid,cname) values('c001','电器');
insert into category(cid,cname) values('c002','服饰');
insert into category(cid,cname) values('c003','化妆品');
insert into category(cid,cname) values('c004','书籍');
insert into category(cid) values('c005');
insert into category values('c006','玩具'),('c007','蔬菜');
## 1.增加一个字段salary int
alter table category add salary int;
## 2.修改category表,将cname都改成'特斯拉'
update category set cname='特斯拉';
## 3.修改category表,将salary在1000到9999这个范围里面的cname改成'不差钱'
update category set cname='不差钱' where salary between 1000 and 9999;
## 4.修改category表,将salary增加1000
update category set salary=salary+1000;
练习2
题目:
-
一共有多少门课程?
-
一共有多少个学生?
-
每个学生最高的分数是多少?
-
每个学生的信息及考试分数?
-
每个学生的课程信息以及对应的分数?
-
根据性别统计人数
-
根据年统计人数,substr一部分字符串
-
按照年和性别2个维度统计人数
-
统计每个学生的平均成绩
-
按性别分析最高分数
-
按年、性别和课程3个维度分析分数
-
统计每个分数出现了多少次,并按降序排列
-
按课程维度分组求最大的分数
答案:
create database learn_mysql;
use learn_mysql;
CREATE table Student(SId varchar(20),Sname varchar(20),Sage varchar(20),Ssex varchar(20));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');
create table Course(CId varchar(20),Cname varchar(20),TId varchar(20) );
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table SC(SId varchar(20) ,CId varchar(20),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
select * from Course;
select * from Student;
select * from SC;
#1. 一共有多少门课程?
select count(1) as couNum from Course;
#2. 一共有多少个学生?
select count(1) stuNum from Student;
#3. 每个学生最高的分数是多少?
select SId,max(score) from SC group by SId;
select t2.SId,t2.Sname,max(t1.score) from SC t1 inner join Student t2 on t1.sid=t2.sid group by t2.SId,t2.Sname;
select t1.sname,t1.sid, case when max(score) is null then 0 else max(score) end as score from Student t1 left join SC t2 on t1.sid=t2.sid group by t1.sname,t1.sid;
#4. 每个学生的信息及考试分数?
select * from Student left join SC on Student.SId=SC.SId;
select t1.SId,t1.Sname,t1.Ssex,t2.score from Student t1 left join SC t2 on t1.SId=t2.SId;
#5. 每个学生的课程信息以及对应的分数?
select Sname,Cname,score from SC right join Student on SC.SId=Student.SId right join Course on Course.TId=SC.CId;
#6. 根据性别统计人数
select Ssex,count(1) from Student group by Ssex;
#7.根据年统计人数,substr一部分字符串
select Sage,count(1) from Student group by Sage;
select substr(sage,1,4) as year,count(1) as cn from Student group by substr(sage,1,4);
#8.按照年和性别2个维度统计人数
select Ssex,substr(sage,1,4),count(1) from Student group by Ssex, substr(sage,1,4);
#9.统计每个学生的平均成绩
select * from Student left join(select SId,avg(score) from SC group by SId) c1 on Student.SId=c1.SId;
select t1.SId,t1.Sname,avg(score) from Student t1 left join SC t2 on t1.SId=t2.SId group by t1.SId,t1.Sname;
#10. 按性别分析最高分数
select Ssex,max(score) from SC right join Student on SC.SId=Student.SId group by Ssex;
#11.按年、性别和课程3个维度分析分数
select substr(sage,1,4),ssex,cid,max(score),min(score) from Student t1 inner join SC t2 on t1.sid=t2.sid group by substr(sage,1,4),ssex,cid;
select substr(t1.sage,1,4),ssex,t3.cname,max(score),min(score),sum(score) from Student t1 inner join SC t2 on t1.sid=t2.sid
inner join Course t3 on t2.cid=t3.cid
group by substr(t1.sage,1,4),ssex,t3.cname;
#12.统计每个分数出现了多少次,并按降序排列
select score,count(1) cn from SC group by score order by cn desc;
select score,count(1) from SC group by score order by score desc;
#13.按课程维度分组求最大的分数
select CId,max(score) from SC group by CId;
select * from Course left join (select CId,max(score) from SC group by CId) c1 on Course.CId=c1.CId;
select Cname,max(score) from SC t1 inner join Course t2 on t1.CId=t2.CId group by Cname;