Mysql

最新推荐文章于 2024-05-09 00:12:29 发布

Atwood-Tom

最新推荐文章于 2024-05-09 00:12:29 发布

阅读量103

点赞数

分类专栏：知识总结文章标签： mysql

本文链接：https://blog.csdn.net/Atwood_Tom/article/details/113871815

版权

知识总结专栏收录该内容

13 篇文章 0 订阅

订阅专栏

sql数据库

创建学生表.表中包含姓名,学生号,性别,年龄

create table if not exists student(
  name varchar(20),
  id   varchar(20),
  gender bit,
  age    int 
);

插入操作

insert into student (name,id,gender,age) values
("22","18407020429",1,20),
("陈1","18407020430",1,21),
("张2","18407020431",1,20),
("王4","18407020432",1,21);

查询操作

select * from student;
select name,age,id from student;
select name from student where age>=20;

别名

select name as 姓名 from student;

去重

select distinct age from student;

排序

Asc为升序,desc为降序

一般情况下不表明默认为asc

select *from student order by age asc, id desc;

条件查询

运算符	说明
>,>=,<,<=	大于,大于等于,小于,小于等于
=	等于,null不安全例如null=null的结果是null
<=>	等于,null安全例如null=null结果是true
!=,<>	不等于
between A1 and A2	范围匹配 A1<=value<=A2 返回true
in(value1,value2…)	如果是value中任意一个值返回true
is null/is not null	是否为null
like	模糊匹配,%表示任意多个字符,_表示一个字符

select *from student where age between 19 and 20;
select *from student where age in(10,20,25);
select *from student where name like "张%";
select *from student where name like "张书_";
select *from student where gender is null;

运算符	说明
and	多个条件必须都为true ,结果才为true
or	任意一个条件为true,结果就位true
not	条件为true,结果为false

where条件可以使用表达式,但不能使用别名

and的优先级高于or,同时使用时需要使用小括号()包裹优先执行部分

select *from student where (not id>"184074020430") and (age between 19 and 20);

日期的使用

mysql默认的日期格式 yyyy-MM-dd HH:mm:ss

drop table if exists date_test;
create table date_test(
	id int,
	start_time timestamp,
	end_time timestamp
);
insert into date_test values(1,'1980-01-01 14:00:00', '1982-05-01 00:00:00');
insert into date_test values(2,'1982-03-01 14:00:00', '1985-05-01 00:00:00');
insert into date_test values(3,'1982-04-01 14:00:00', '1982-07-01 00:00:00');
insert into date_test values(4,'2000-06-01 14:00:00', '2020-05-01 00:00:00');
select *from date_test where (start_time >='1982-04-06 00:00:00') and
(end_time <='1982-06-01 00:00:00');

分页查询(Limit)

select *from 表名 [where…] [order by] limit n offset s; 从s开始筛选n条信息

select *from 表名 [where…] [order by] limit s,n; 从s开始,筛选n条信息

select *from student order by chinese+math+english desc limit 1,3;

修改(update)

update students set math=90,chinese=90 where name="***";

数据库约束

约束类型

约束类型	说明
not null	指定某列不能储存null值
unique	保证每列的每行必须有唯一的值
primary key	not null和unique的结合,确保每列有唯一标识,有助于更容易更快速的找到表中某一特定记录
foreign key	保证一个表中的数据匹配另一个表中的值的参照完整性
default	规定没有给列赋值时的默认值
	保证列中的值符合指定的条件

foreign key用于关联其他的表的主键或者唯一键

foreign key (字段名) references 主表(列);

对于整数类型的主键,常常搭配自增长auto_increment来使用,插入数据对应字段不给指时,使用最大值+1

例如

create table school(
id int primary key auto_increment,
name varchar(20)
);

create table student(
    name varchar(20) default "UNKNOW",
    id   int primary key auto_increment,
    School_id  int ,
    foreign key (School_id) references school(id)
);

三大范式

例:设计图书管理系统，包含学生和图书信息，且图书可以进行分类，学生可以在一个时间范围内借阅图书，并在这个时间范围内归还图书。要求:

1.涉及以上场景的数据库表，并建立表关系。

2查询某个分类下的图书借阅信息。

3.查询在某个时间之后的图书借阅信息。

4.查询图书借阅周期在某个时间范围内的图书借阅信息（(图书借阅周期与查询时间范围有交集)。

create table student(
id int primary key auto_increment,
name varchar(20)
);
create table book_sort(
    id int primary key,
    sort_name varchar(20)
);

create table book(
id int primary key auto_increment,
sort int,
name varchar(20),
foreign key(sort) references book_sort(id)    
);
create table borrowing_information(
  id int primary key auto_increment,
  start_time datetime,
  end_time datetime,
  student_id int ,
  book_id int ,
  foreign key(student_id) references student(id),
  foreign key(book_id) references book(id)
);

新增

插入查询结果

语法

insert into table_name (***,***,***) select ***,***,*** from ***;

例如:创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的学生数据复制进来，可以复制的字段为name、qq_mail

-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
   id INT primary key auto_increment,
   name VARCHAR(20) comment '姓名',
   age INT comment '年龄',
   email VARCHAR(20) comment '邮箱',
 sex varchar(1) comment '性别',
 mobile varchar(20) comment '手机号'
);
-- 将学生表中的所有数据复制到用户表
insert into test_user(name, email) select name,qq_email from student;

查询

聚合函数

函数	说明
count	返回查询到的数据的数量
sum	返回查询到的数据的总和,不是数字没有意义
avg	返回查询到的数据的平均值,不是数字没有意义
max	返回查询到的数据的最大值,不是数字没有意义
min	返回查询到的数据的最小值,不是数字没有意义

例如:

count

--统计班级共有多少学生
select count(*) from student;
select count(0) from student;
--统计班级收集的qq_mail有多少个,qq_mail为null的数据不会计入结果
select count(qq_mail) from student; 
--统计班级姓陈的有多少人
select count(name) from student where name like '陈%';

sum,avg,max,min

--求全班数学成绩
select sum(math) from student;
--求全班数学平均成绩
select avg(math) from student;
--求全班数学成绩最高分
select max(math) from student;
--求全班数学成绩最低分
select min(math) from student;

count(*) 和count(0) 的区别??

group by子句

select 中使用group by子句可以对指定列进行分组查询.

需要满足:使用group by进行分组查询时,select 指定的字段必须是"分组依据字段"

其他字段若想出现在select 中则必须包含在聚合函数中

create table emp (
    id int primary key auto_increment,
    name varchar(20) not null,
    role varchar(20) not null,
    salary numeric(11,2)
);
insert into emp (name,role,salary) values
("马云","服务员",1000.20),
("马化腾","游戏陪玩",2000.99),
("孙悟空","游戏陪玩",99901),
("猪八戒","游戏陪玩",333.5),
("沙和尚","游戏角色",700.33),
("隔壁老王","董事长",120000);


--查询每个角色的最高工资,最低工资,平均工资
select role,max(salary),avg(salary),min(salary) from emp group by role;

having

group by 子句进行分组后,需要对分组结果在进行过滤是,不能用where语句,而要用having

--显示平均工资低于1500的角色和他的工资
select role,max(salary),avg(salary) from emp group by role having avg(salary)<1500;

联合查询

$C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images$

-- 创建班级表，有使用MySQL关键字作为字段时，需要使用``来标识
DROP TABLE IF EXISTS classes;
CREATE TABLE classes (
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20),
	`desc` VARCHAR(100)
);

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
    id INT PRIMARY KEY auto_increment,
    sn INT UNIQUE,
    name VARCHAR(20) DEFAULT 'unkown',
    qq_mail VARCHAR(20),
	classes_id int,
	FOREIGN KEY (classes_id) REFERENCES classes(id)
);

-- 创建课程表
DROP TABLE IF EXISTS course;
CREATE TABLE course (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(20)
);

-- 创建课程学生中间表：考试成绩表
DROP TABLE IF EXISTS score;
CREATE TABLE score (
    id INT PRIMARY KEY auto_increment,
	score DECIMAL(3, 1),
    student_id int,
	course_id int,
	FOREIGN KEY (student_id) REFERENCES student(id),
	FOREIGN KEY (course_id) REFERENCES course(id)
);


insert into classes(name, `desc`) values 
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');

insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',2);

insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);

内连接

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

例:查询"许仙"同学的成绩

select score.course_id,score.score from student student inner join score on student.id=score.student_id and student.name ='许仙';



select score.score from student stu, score sco where stu.id=sco.student_id and
stu.name='许仙';

--两个表 联合查询
select student.name,student.classes_id,student.sn
from student join score on student.id=score.student_id
group by score.student_id;
--三个表 联合查询
select 
          student.name,
          student.classes_id,
          student.sn,
          course.name
from 
      student 
      join score on student.id=score.student_id
      join course on score.course_id=course.id
and student.name="白素贞";

select 
      student.name,
      student.id,
      course.name,
      score.score,
      classes.name as classes_name
from 
      student,score,course,classes
where 
           student.id=score.student_id
      and  score.course_id=course.id
      and classes.id=student.classes_id;
      
      
      
select 
      student.name,
      student.id,
      sum(score.score),
      avg(score.score),
      classes.name as classes_name
from 
      student,score,course,classes
where 
           student.id=score.student_id
      and  score.course_id=course.id
      and classes.id=student.classes_id
group by student.name 
order by sum(score.score);

外连接

外连接分为左外连接和右外连接.如果联合查询,左侧的表完全显示我们就说是左外连接,右侧的表完全显示我们就说是右外连接

select 
      student.id,
      student.sn,
      student.name,
      score.score,
      score.course_id,
      course.name
from student 
   left join score on student.id=score.student_id
   right join course on score.course_id=course.id;

内连接和外连接的区别

自连接

自连接是指在同一张表链接自身进行查询

--显示所有"计算机原理"成绩比"java"成绩高的成绩信息
select
      s1.*,
      s1.score as java,
      s2.score as 计算机原理
from
     score s1
     join score s2 on s1.student_id=s2.student_id
     join student stu on s1.student_id=stu.id
     join course c1 on s1.course_id=c1.id
     join course c2 on s2.course_id=c2.id
     and s1.score<s2.score
     and c1.name='java'
     and c2.name='计算机原理';

Atwood-Tom

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql

sql数据库创建学生表.表中包含姓名,学生号,性别,年龄create table if not exists student( name varchar(20), id varchar(20), gender bit, age int );插入操作insert into student (name,id,gender,age) values("22","18407020429",1,20),("陈1","18407020430",1,21),("张2","1840
复制链接

扫一扫