Mysql的基础语法
相关MySQL语言提示命令符相关命令:
- mysql -u guest -h … -p : 访问数据库链接其他服务器。
- show databases；:显示所有数据库。
- quit: 退出命令行窗口。
- use school; :链接数据库
- ? functions;：查询相关函数功能命令
- mysql -u root -p : 启动服务器
SQL语言的数据类型：
-
数据类型：
- 整数：
-
- int / integer —> -2^31 ~ 2^31 - 1
-
- tinyint / smallint / bigint
-
- int unsigned —> 0 ~ 2^32 - 1
-
-
- tinyint unsigned —> 0 ~ 255
- 小数：
-
- float / double 常识：计算机常识造成，不能进行运算。
-
-
- decimal —> decimal(10,2)：保留小数。
- 字符串：
-
- char() / varchar()
-
-
- longtext —> longblob（二进制大对象）—> 放资源路径（URL）(大对象) —4G
- 时间日期：
-
- date / time / datetime
-
-
- timestamp：时间戳
- 布尔型：
-
- boolean —> tinyint(1) —> 0 / 1
SQL ——>结构化查询语言
- - DML - 数据操作语言 —> insert / delete / update
- - DQL - 数据查询语言 —> select
- - DCL - 数据控制语言 —> grant / revoke
- - DDL - 数据库定义语言 —> create / drop / alter
SQL语句是不不区分大小写的
创建SQL语句
-
use school; : 切换数据库。
-
drop database school; :删除数据库。drop database school if exists school; :先判断表是否存在，在进行表的删除。这是慎重的操作。
-
show create database school; : 查看创建数据库的过程。
-
use school; : 切换数据库。create database school; :创建表。create datebase school default charset utf8mb4;
-
show tabes; :查看所有的表。
-
drop databases; : 删除数据库。
-
create tables tb_student; : 创建二维表
create table `tb_student` ( `stu_id` integer not null, `stu_name` varchar(10) not null, `stu_sex` char(1) default 'M' not null, `stu_birth` date, primary key (`stu_id`) )engine=innodb; 主键（primary key）：能够唯一确定一条记录的字段。
not null ：非空约束。
default：默认值约束。
comment ：注释（在每一个键后面）。
engine=innodb: 引擎。
-
drop table if exists tb_student; : 删除表。
-
修改表
-- 添加一个列 alter table tb_student add column stu_addr varchar(50) default '' comment '籍贯'; -- 删除一个列 alter table tb_student drop column stu_addr; -- 修改一个列 alter table tb_student change column stu_sex stu_gender boolean default 1 comment '性别'; -- 修改一个列(修改一个列的数据类型) alter table tb_student modif column stu_gender char(1) default 1 comment '性别'; -- 修改学生表添加一个列表来维护学生对学院表的多对一关系 -- 多对一关系都是在多的一方添加一个列维护 alter table tb_student add column col_id int unsigned not null comment '学院编号'; -- 修改学生表添加一个外键约束，限制学生表中的学院编号，必须参照学院表的学院编号 alter table tb_student add constraint fk_student_col_id foreign key (col_id) references tb_college(col_id);
SQL的查询语句
use school;
-- 查询所有学生信息
-- 一般不写*号，有50列就写50列
-- select * from tb_student;
select stu_id, stu_name, stu_addr from tb_student;
-- 查询所有课程名称及学分(投影和别名)
-- as可以省略
select cou_name as 课程名称,cou_credit as 学分 from tb_course;
-- 查询所有女学生的姓名和出生日期(筛选)
select stu_name,stu_birth from tb_student where stu_sex='0';
-- 查询所有80后学生的姓名、性别和出生日期(筛选)
select stu_name,stu_sex,stu_birth
from tb_student
where stu_birth between '1980-1-1' and '1990-1-1';
-- where stu_birth>='1980-1-1' and stu_name<='1989-12-31';
-- 进行一些处理
select
stu_name as 姓名,
case stu_sex when 1 then '男' else '女' end as 性别,
stu_birth as 生日
from tb_student
where stu_birth between '1980-1-1' and '1990-12-31';
-- 多分支结构
-- case stu_sex when 1 then '男' when 0 then '女' else '未知' end as 性别,
-- sql方言if() --->oracl --->decode
select
stu_name as 姓名,
if(stu_sex, '男', '女') as 性别,
stu_birth as 生日
from tb_student
where stu_birth between '1980-1-1' and '1990-12-31';
-- 查询姓”杨“的学生姓名和性别(模糊)
-- 通配符(wildcard):1.% 匹配0个或多个字符 2._ 匹配一个字符
select stu_name,stu_sex from tb_student
where stu_name like '杨%';
select stu_name,stu_sex from tb_student
where stu_name like '杨_';
-- 查询姓”杨“名字两个字的学生姓名和性别(模糊)
select stu_name,stu_sex from tb_student
where stu_name like '杨__';
-- 查询姓”杨“名字三个字的学生姓名和性别(模糊)
select stu_name,stu_sex from tb_student where stu_name like '杨__';
-- 查询名字中有”不“字或“嫣”字的学生的姓名(模糊)
-- or性能比较差, where语句靠近那个能够筛选更多的条件
select stu_name,stu_sex from tb_student
where stu_name like '%不%' or stu_name like '%嫣%';
-- union并集：是会去重的
-- union all:重复的元素会保留
select stu_name,stu_sex from tb_student where stu_name like '%不%'
union
select stu_name,stu_sex from tb_student where stu_name like '%嫣%';
-- 基于正则表达式的模糊查询[杨林].{2}
select stu_name from tb_student where stu_name regexp '杨.{2}';
-- 查询没有录入家庭住址的学生姓名(空值)
-- null做任何运算最终都是一个空值
-- stu_addr=null 相当于是 where=0
-- <=>:等于，<>:不等于 针对null
select stu_name from tb_student where stu_addr is null;
select stu_name from tb_student where stu_addr is null or stu_addr='';
select stu_name from tb_student where stu_addr<=>null;
-- 查询录入了家庭住址的学生姓名(空值)
select stu_name from tb_student
where stu_addr is not null and stu_addr<>'';
-- where stu_addr is not null;
-- 查询学生选课的所有日期(去重)
select distinct sel_date from tb_record;
-- 查询学生的家庭住址(去重)
select distinct stu_addr from tb_student
where stu_addr is not null;
-- 查询男学生的姓名和生日按年龄从大到小排列(排序)
-- 升序（从大到小） ---> asending
-- current date
-- floor：向下取整 cell：向上取整
select stu_name,
stu_birth,
cell(datediff(curate(),stu_birth)/365) as stu_age
from tb_student
where stu_sex='1'
order by stu_age asc;
-- 降序（从大到小） ---> descending
select stu_name,stu_birth from tb_student
where stu_sex='1'
order by stu_birth desc;
-- 查询年龄最大的学生的出生日期(聚合函数)
select min(stu_birth) from tb_student;
-- 查询年龄最小的学生的出生日期(聚合函数)
select max(stu_birth) from tb_student;
-- 查询编号为1111的课程考试成绩的最高分
select max(score) from tb_record where cou_id=1111;
-- 查询学号为1001的学生考试成绩的最低分
-- 所有聚合函数自动干掉空值
select min(score) from tb_record where stu_id=1001;
-- 查询学号为1001的学生考试成绩的平均分95.6250000
select avg(score) from tb_record where stu_id=1001;
-- 查询学号为1001的学生考试成绩的平均分，如果有null值，null算0分 75.20000
-- count(*) 会计算出null
select sum(score)/count(cou_id) from tb_record where stu_id=1001;
select avg(ifnull(score,0)) from tb_record where stu_id=1001;
-- ifnull ---> 如果遇到null空值，将其替换成指定的值
-- 查询学号为1001的学生考试成绩的标准差:低值代表稳定
select std(score) from tb_record where stu_id=1001;
-- 查询男女学生的人数(分组和聚合函数)
select
if(stu_sex,'男','女') as 性别,
count(*) as 人数
from tb_student group by stu_sex;
-- 查询每个学院男女学生人数
select
col_id as 学院编号,
if(stu_sex, '男', '女') as 性别,
count(*) as 人数
from tb_student group by col_id,stu_sex;
-- 查询每个学生的学号和平均成绩
select stu_id as 学号,
round(avg(score),1) as 平均分
from tb_record group by stu_id;
-- 查询课程编号为1111的课程的平均成绩(筛选和聚合函数)
select avg(score) from tb_record where cou_id=1111;
-- 查询学号为1001的学生所有课程的平均分(筛选和聚合函数)
select avg(score) from tb_record where stu_id=1001;
-- 查询每个学生的学号和平均成绩(分组和聚合函数)
select avg(score) from tb_record group by stu_id;
-- 查询平均成绩大于等于90分的学生的学号和平均成绩
-- 分组以前的筛选使用where子句，分组以后的数据筛选使用having子句
select stu_id,avg(score) from tb_record
group by stu_id
having avg(score)>=90;
select
stu_id as 学号,
round(avg(score), 2) as 平均分
from tb_record
where cou_id in (1111, 2222, 3333)
group by stu_id
having 平均分>=90;
-- 查询年龄最大的学生的姓名(子查询)
set @a=(select min(stu_birth) from tb_student);
select @a:=(select min(stu_birth) from tb_student);
select @a;
select stu_name from tb_student where stu_birth=@a;
-- 嵌套查询：把一个select的结果作为另一个select的一部分来使用
-- 嵌套查询也通常称之为子查询，在查询语句中有两个或多个select
select stu_name from stu_student
where stu_birth=(
select min(stu_birth) from tb_student
);
-- 查询年龄最大的学生姓名和年龄(子查询+运算)
select stu_name as 姓名,
floor(datediff(curdate(), stu_birth)/365) as 年龄
from tb_student
where stu_birth=(select min(stu_birth) from tb_student
);
-- 查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)
select stu_name from tb_student
where stu_id in (
select stu_id from tb_record
group by stu_id having count(*)>2
);
select distinct stu_id from tb_record
where stu_id in (
select stu_id from tb_record
group by stu_id having count(*)>2
);
-- 查询学生的姓名，生日和所在学院名称
-- 这是笛卡尔积
select stu_name,stu_birth,col_name
from tb_student,tb_college
where tb_student.col_id=tb_college.col_id;
select stu_name,stu_birth,col_name
from tb_student inner join tb_college
on tb_student.col_id=tb_college.col_id;
-- 自然链接，因为这个一般都是外键因素约束着，一般不写
select stu_name,stu_birth,col_name
from tb_student natural join tb_college;
-- 查询课程的名称，学分，和授课老师的姓名
select cou_name,cou_credit,tea_name
from tb_course,tb_teacher
where tb_course.tea_id=tb_teacher.tea_id;
select cou_name,cou_credit,tea_name
from tb_course t1
inner join tb_teacher t2 on t1.tea_id=t2.tea_id;
-- 查询学生姓名、课程名称以及成绩(连接查询)
select stu_name,cou_name,score
from tb_student t1
inner join tb_record t2 on t1.stu_id=t2.stu_id
inner join tb_course t3 on t2.cou_id=t3.cou_id
where score is not null;
select stu_name,cou_name,score
from tb_student,tb_record,tb_course
where tb_student.stu_id=tb_record.stu_id
and tb_record.cou_id=tb_course.cou_id
and score is not null;
select stu_name,cou_name,score
from tb_student natural join tb_record natural join tb_course
where score is not null
order by score desc limit 8;
select stu_name,cou_name,score
from tb_student natural join tb_record natural join tb_course
where score is not null
order by score desc limit 5 offset 5;
-- 拿到多少，先跳过多少
select stu_name,cou_name,score
from tb_student natural join tb_record natural join tb_course
where score is not null
order by score desc limit 5,5;
-- 跳过几行拿到几行
-- 查询选课学生的姓名和平均成绩(子查询和连接查询)
-- 链接查询，嵌套查询
select stu_name,avg_score
from
tb_student t1,
(select stu_id, avg(score) as avg_score
from tb_record group by stu_id
)t2
where t1.stu_id=t2.stu_id;
– 查询每个学生的姓名和选课数量
– 外连接
– 内链接：查询左右两表满足链接条件的数据
– 左外链接：确保左表（现在join前面的表）中所有记录都能查出来，不满足连接条件的补充null
– 右链接：确保右表（现在join后面的表）中所有记录都能查出来，不满足连接条件的补充null
– 全外链接 ：确保左表和右表所有记录都能查出来，不满足连接条件的补充null
select stu_name,ifnull(total,0) as total from tb_student t1,
(select stu_id,count(*) as total from tb_record group by stu_id) t2
where t1.stu_id=t2.stu_id;
-- 查询每个学生的姓名和选课数量(左外连接和子查询)
select stu_name,ifnull(total,0) as total
from tb_student t1 left outer join
(select stu_id,count(*) as total from tb_record group by stu_id) t2
on t1.stu_id=t2.stu_id;
-- 删除tb_record表的外键约束
alter table tb_record drop foreign key fk_record_stu_id;
alter table tb_record drop foreign key fk_record_cou_id;
-- 给tb_record表加两条记录，学号5566在学生表没有对应的记录
insert into tb_record
values
(default, 5566, 1111, '2019-09-02', 80),
(default, 5566, 2222, '2019-09-02', 70);
-- 右外连接
select t1.stu_id, stu_name, t2.stu_id, total as total
from tb_student t1 right outer join (
select stu_id, count(*) as total from tb_record
group by stu_id
) t2 on t1.stu_id=t2.stu_id;
-- MySQL不支持全外连接
-- 可以通过左外连接与右外连接求并集运算得到全外连接的结果
select t1.stu_id, stu_name, t2.stu_id, total as total
from tb_student t1 left outer join (
select stu_id, count(*) as total from tb_record
group by stu_id
) t2 on t1.stu_id=t2.stu_id
union
select t1.stu_id, stu_name, t2.stu_id, total as total
from tb_student t1 right outer join (
select stu_id, count(*) as total from tb_record
group by stu_id
) t2 on t1.stu_id=t2.stu_id;
8954
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
