mysql必知必会2

2018年7月10日笔记

1.创建数据表和插入数据

创建表commodityType

create table commodityType(
id int primary key,
name varchar(50)
)

表commodityType插入数据

insert into commodityType (id,name) values (1,'玩具'); 
insert into commodityType values (2,'文具');
insert into commodityType values (3,'书籍'),(4,'食物'),(5,'衣物');

创建表commodity

create table commodity(
id int primary key,
name varchar(20),
madein varchar(20),
c_type int references commodityType(id),
inprice int unsigned,
outprice int unsigned,
num int unsigned default 200);

表commodity插入数据

insert into commodity(id,name,madein,c_type,inprice,outprice,num)values(1,'变形金刚','美国',1,100,200,default);
insert into commodity values(2,'红楼梦','中国',3,1000,1500,1000);
insert into commodity values(3,'西游记','中国',3,500,600,30);
insert into commodity values(4,'文具盒','日本',2,10,100,20);
insert into commodity values(5,'铅笔','中国',2,1,2,1000000);
insert into commodity values(6,'HADOOP权威指南','美国',3,149,199,default);
insert into commodity values(7,'多啦A梦','日本',1,600,900,100);
insert into commodity values(8,'Java编程思想','中国',3,100,null,1000);

2.数据表的删除数据

语法:delete from {1} where {2} 第一对大括号替换为表名,第二对大括号替换为查询条件。 注意:删除语句一定要写删除条件,否则整张表删除。 例如:delete from commodity 这个SQL语句删除commodity表中的所有数据。 例如:delete from commodity where id = 5 这个SQL语句删除commodity表中的id=5的数据

3.数据表的清空表格数据

语法:truncate table {} 大括号替换为表名。 例如:truncate table commodity 这个SQL语句清空commodity表中的所有数据。 相同:都能删除数据,不删除表结构,但truncate速度更快 不同:使用truncate table 重新设置auto_increment计数器; 使用truncate table不会对事务有影响

4.数据库的修改数据

语法:update {1} set {2} = {3} where {4} = {5} 第1对大括号替换为要修改表的表名,第2对大括号替换为新字段的字段名,第3对大括号替换为新字段的值, 第4对大括号替换为限定条件的字段名,第5对大括号为限定条件的值。 例如:update commodity set name = '超人' where id = 8

课堂联系

图片.png-30.7kB

drop table if exists subject;
create table subject(
sub_id int primary key,
sub_name varchar(50),
sub_hour int,
sub_grand_id int
);
insert into subject values(1,'高等数学-1',120,1);
insert into subject values(2,'高等数学-2',110,2);
insert into subject values(3,'高等数学-3',100,3);
insert into subject values(4,'高等数学-4',130,4);

5.数据库的单表查询

5.1选出限定的字段

select id,name,madein,c_type,inprice,outprice,num from commodity;

sql语句运行结果如下图所示:

图片.png-7.8kB

5.2 between关键字

select id,name,inprice from commodity where inprice between 100 and 101; select id,name,inprice from commodity where inprice <= 101 and inprce >= 100;

两种写法效果相同,sql语句运行结果如下图所示:

图片.png-2.7kB

5.3 is null关键字

is null关键字的条件查询语句示例:

select * from commodity where outprice is null;

5.4 is not null关键字

is not null 关键字的条件查询语句示例:

select * from commodity where outprice is not null;

5.5 in关键字

in关键字的条件查询语句示例:

select * from commodity where outprice in (200,600);

5.6 like关键字

like关键字的条件查询语句示例:

select * from commodity where madein like "%国";

sql语句运行结果如下图所示:

图片.png-6.3kB

查出name字段值长度为3的行的所有信息:

select * from commodity where name like '___';

sql语句运行结果如下图所示:

图片.png-4kB

5.7 order by关键字

order by 关键字使用示例,desc降序从大到小,asc升序从小到大:

select * from commodity order by num desc;

sql语句运行结果如下图所示:

图片.png-7.5kB

5.8 limit关键字

limit关键字使用示例: 选出前5条记录

select * from commodity limit 5;

选出第6-8条记录:

select * from commodity limit 5,3;

练习

练习1 使用SQL命令重新创建myschool数据库并新增适量数据,完成如下需求: 1.查询出所有男生; 2.查询出住址不在宿舍的所有学员; 3.查询出所有张姓学员;

插入语句:

insert into students values("张三",'男',"宿舍"), ("赵六","女","校外"),("李四","男","宿舍"),("张五","男","校外");

查询所有男生:

select * from students where sex = "男";

查询住址不在宿舍的所有学员:

select * from students where address != "宿舍";

查询出所有张姓学员:

select * from students where name like "张%";

练习2 查询《XXX》课程成绩前10名且分数大于80的学生信息(学号,姓名,课程名,分数)

select * from stu where score > 80 limit 10;

6.聚合函数

聚合函数一般配合group by 关键字使用 聚合函数:count()、avg()、sum()、max()、min()

7.数据分组

通过group by关键字实现,可与having关键字配合使用继续进行筛选

8.多表连接查询

内连接,左连接,右连接三种连接方式查询的区别可以通过下面三张图总结: 内连接是根据条件取两个表的交集

image_1ci7l580fpuek5lij4n2756613.png-58.9kB

左连接是根据条件取两个表的交集+左表中没有被取进交集的部分

image_1ci7l4g5i1o3v1rpb1ifa1ucv123cm.png-73.2kB

右连接是根据条件取两个表的交集+右表中没有被取进交集的部分

image_1ci7l7abl12ss1jqj163v1f5311h31t.png-74.3kB

9.子查询

9.1概念:

1.子查询是一种常用计算机语言select-sql语言中嵌套查询下层的程序模块。当一个查询是另一个查询的条件时,称为子查询。 2.当需要一张表的数据作为条件去查询另一张表,我们需要用到子查询。

9.2子查询的使用

1.子查询必须被圆括号括起来。 2.子查询只能在有一列的select子句中。 3.order by不能再子查询中使用,在主查询中可以使用。group by 可以用来在子查询中如order by 相同的功能。 4.返回多于一个子查询只能用于多个值运算符,比如in 5.between 操作符不能与子查询使用,但是可以在子查询中使用。

实战

1.创建student表,结构如下

create table student(
id int(10) primary key not null auto_increment comment '学号',
name varchar(20) not null comment '姓名',
sex varchar(4) null comment '性别',
birth year null comment '出生年份',
department varchar(20) not null comment '院系',
address varchar(50) null comment '家庭住址'
);

2.创建score表,结构如下

create table score(
id int(10) primary key not null auto_increment comment'编号',
stu_id int(10) not null comment'学号',
c_name varchar(20) null comment '课程名',
grade int(10) null comment '分数'
);

3.给student表添加以下数据

insert into student values(901,'张老大','男',1985,'计算机系','北京市海淀区');
insert into student values(902,'张老二','男',1986,'中文系','北京市昌平区');
insert into student values(903,'张三','女',1990,'中文系','湖南省永州市');
insert into student values(904,'李四','男',1990,'英语系','辽宁省阜新市');
insert into student values(905,'王五','女',1991,'英语系','福建省厦门市');
insert into student values(906,'王六','男',1988,'计算机系','湖南省衡阳市');

4.给score表添加以下数据

insert into score values(1,901,'英语',80);
insert into score values(2,902,'计算机',65);
insert into score values(3,902,'中文',88);
insert into score values(4,904,'中文',95);
insert into score values(5,904,'计算机',70);
insert into score values(6,904,'英语',92);
insert into score values(7,905,'英语',94);
insert into score values(8,906,'计算机',90);
insert into score values(9,906,'英语',85);

5.查询student表的第2条到第4条记录

select * from student limit 1,3;

6.从student表查询所有学生的学号、姓名和院系的信息

select id,name,department from student;

7.从student表中查询计算机系和英语系的学生的信息

select * from student where department in ('计算机系','英语系');

8.从student表中查询年龄18-22岁的学生信息

select id,name,sex,(2018-birth) as age,department,address from student where 2018-birth <= 22 and 2018 - birth >= 18;

9.从student表中查询每个院系有多少人

select department,count(id) from student group by department;

10.从score表中查询每个科目的最高分

select c_name,max(grade) from score group by c_name;

11.用连接的方式所有学生的信息和考试信息

select student.id,name,sex,birth,department,address,c_name,grade 
from student inner join score on student.id = stu_id;

12.计算每个学生的总成绩

select student.id,name,sum(grade) 
from student inner join score on student.id = stu_id group by student.id;

13.计算每个考试科目的平均成绩

select c_name,avg(grade) from score group by c_name

14.将计算机考试成绩按从高到低进行排序

select stu_id,name,grade from score where c_name = '计算机'  order by grade desc

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏程序猿

SQL Server基础SQL脚本之内外连接、交叉连接;函数、子查询

代码大概200行左右 本系列,几乎都是代码,记得当时写的时候用的是微软的官方实例数据库AdventureWorks_Data.mdf、AdventureWor...

34340
来自专栏杨建荣的学习笔记

怎样突破表名30个字符的限制(r2笔记51天)

根据oracle的规范,对象的长度最大为30位,也就是说,在平时的使用中如果碰到表名长度大于30位,首先oracle是不答应的,它会提示idnetifier t...

29860
来自专栏熊二哥

那些年我们写过的T-SQL(上篇)

在当今这个多种不同数据库混用,各种不同语言不同框架融合的年代(一切为了降低成本并高效的提供服务),知识点多如牛毛。虽然大部分SQL脚本可以使用标准SQL来写,但...

214100
来自专栏恰童鞋骚年

《MSSQL2008技术内幕:T-SQL语言基础》读书笔记(上)

  数据库在物理上由数据文件和事务日志文件组成,每个数据库必须至少有一个数据文件和一个日志文件。

10650
来自专栏技术专栏

SQL 编写技巧总结(持续更新中)

参考 http://blog.csdn.net/bitcarmanlee/article/details/51926530

20030
来自专栏数据库

常用SQL语句和语法汇总

近几年数据库发挥了越来越重要的作用,这其中和大数据、数据科学的兴起有不可分割的联系。学习数据库,可以说是每个从事IT行业的必修课。你学或不学,它就在那里;你想或...

25950
来自专栏CDA数据分析师

十步完全理解 SQL

文 | 水果泡腾片 来源 | 伯乐在线 很多程序员视 SQL 为洪水猛兽。SQL 是一种为数不多的声明性语言,它的运行方式完全不同于我们所熟知的命令行语言、面向...

30790
来自专栏james大数据架构

你真的会玩SQL吗?EXISTS和IN之间的区别

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

20860
来自专栏Web项目聚集地

Oracle知识点总结(一)

这里用1,也是为了方便,当然如果数据量较大的话,也可以提高速度,因为写count(*)的话会所有列扫描,这里用1的话或者用字段名的话,只扫描你写的那个列其实1就...

9510
来自专栏冷冷

【译】MySQL char、varchar的区别

本文旨在涵盖两种数据类型的相似性和差异。两者几乎相同,但在某些方面,两略有不同。 介绍 CHAR和VARCHAR几乎相同,但在存储和从数据库中检索数据的阶段,两...

38690

扫码关注云+社区

领取腾讯云代金券