SQL语句书写复习

SQL语句学完就忘,面试一问,一紧张什么都不记得,平时多写写练习,将课程实验总结如下。

一、创建数据库(数据库用户)并熟悉DDL

1.实验内容

​ 创建数据库用户,向数据库用户Soft05增加教师和教师上课2个数据库表:

2.实验过程

​ 通过Mysql command Line Cline 创建表和数据库完整行约束。

  • mysql登录命令:

    在此目录下打开cmd

在这里插入图片描述

执行命令:mysql -u root -p 并输入root密码即可登录mysql

在这里插入图片描述

  • 创建用户’soft05’,用grant给用户授权:

    create user 'soft05'@'localhost' IDENTIFIED BY 'soft05';
    grant all on *.* to 'soft05'@'localhost';
    

在这里插入图片描述

  • 创建数据库,选择数据库,创建表,展示表

    create database test;
    use test;
    CREATE TABLE teacher(
    	tno varchar(8) PRIMARY KEY,
        tname varchar(8) NOT NULL,
        sex varchar(2),
        birthday date,
        dno varchar(6),
        pno int(10),
        home varchar(40),
        zipcode varchar(6),
        tel varchar(40),
        email varchar(40));
    CREATE TABLE teacher_course(
    	tcid int PRIMARY KEY,
    	tno varchar(8),
    	spno varchar(8),
    	classno varchar(4),
    	cno varchar(10) NOT NULL,
    	semester varchar(6),
    	schoolyear varchar(10),
    	classtime varchar(40),
    	classroom varchar(40),
    	weektime int,
    	foreign key(tno) references teacher(tno));
    show tables;
    

在这里插入图片描述

  • 修改表

    -- 修改字段的类型
    alter table teacher modify column tel varchar(50);
    describe teacher;
    -- 修改表名
    alter table teacher rename to tr;
    

在这里插入图片描述
在这里插入图片描述

  • 添加、删除、修改表数据

    insert into tr(tno,tname,sex,birthday,dno,pno,home,zipcode,tel) VALUE ('20176666','小芳芳','女','19960705','信息院',666,'湖南大学','543210','18666666666');
    describe tr;
    select * from tr;
    update tr set dno='物理院' where dno='信息院';
    select * from tr;
    DELETE from tr WHERE dno='物理院';
    select * from tr;
    

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

二、数据库查询

1.简单查询

select * from movies
where studioName='Disney' and year=1999;
-- 查询语句,在 movies 表中查询 studioName 为 “Disney”且年份为 1999 的数据

select title, length from movies
where studioName='Disney' and year=1999;
-- 查询,只列出 title 和 length 字段

select title as name, length as duration from movies
where studioName='Disney' and year=1999;
-- 查询,把 title 字段改名为 name

select title as name, length*0.016667 as lengthlnHours from movies
where studioName='Disney' and year=1999;
-- 查询,并把 length 字段的每个值都乘以 0.016667,得到的结果作为 lengthHours 输出 

select title as name, length*0.016667 as length, 'hrs.' as InHours from movies
where studioName='Disney' and year=1999;
-- 新增 InHours 字段,数据全为 hrs. 

select title
from movies
where (year>1970 or length<90) and studioName='Fox';
-- 选择 studioName 为 Fox,且年份 晚于 1970 或长度小于 90 分钟的电影名称 

select title
from movies
where title like 'Star %';
-- 选择电影名称带”Star”的 

select date '1948-05-14', time '12:08:08.08'
from dual;
-- 将日期和时间置于 虚拟表 dual 中

在这里插入图片描述

select date'1948-05-14', timestamp'1948-05-14 12:08:08.08'
from dual;
-- 将日期和日期时间置于 虚拟表 dual 中

在这里插入图片描述

select 1+2, 1+null,null+1
from dual;
-- 计算结果,并置于虚拟表 dual 中

在这里插入图片描述

select * from movies
where 1=1;
-- 使得所有选取条件失效 

select * from movies
where null=null; 

select * from movies
where title is not null;
-- 标题非空 
  • 在SQL中逻辑表达式的可能值包括TRUE、FALSE和UNKNOWN,它们被称之为三值逻辑。UNKNOWN的意思是:什么都不知道,就是什么都不是。

    一般情况下我们将任何值(包括NULL本身)与NULL做比较的时候,都会返回UnKnown。

    而在查询表达式中(比如where与having中),UnKnown会视为false。

    但并不是在所有场情下UnKnown都会视为false来处理,在check约束中,UnKnown就会视为true来处理。这就是为什么我们设置某个字段的值必须大于等于0的情况下,我们还可以往该字段中插入Null值,那是因为在check约束中null>=0的逻辑结果UnKnown会被当作true来处理。

    需要注意的是,在分组子句与排序子句中,sql视null是相等的,即:

    • GROUP BY会把所有NULL值分到一组
    • ORDER BY会把所有NULL值排列在一起
select * from
movies for update;
-- 为数据库的行上一个排它锁。当一个事务的操作未完成时候,其他事务可以读取但是不能写入或更新。 

select * from movies
where length<=120 or length>120;
-- length 小于等于 120 或大于 120 

select * from movies
where Studioname = 'Fox' order by length, title;
-- studioname 为 Fox,且 按 length、title 升序排列 

2.练习A(针对简单查询的练习)

-- a)find the model number, speed, and hard-disk size for all PC's whose price is under $1000. 
 
Select MODEL, SPEED, HD from PC where price<1000; 

-- b)do the same as (a), but rename the speed column gigahertz and the hd column gigabytes. 
 
Select MODEL as gigahertz, SPEED, HD as gigabytes from PC where price<1000; 

-- c)find the manufactures of printers 
 
Select maker from PRODUCT where type = ‘pc’; 

-- d)find the model number, memeory size, and screen size for laptops costing more than $1500. 
 
Select MODEL, RAM, SCREEN from LAPTOP where PRICE>1500;

-- e)find all the tuples in the printe relation for color printers. 
 
Select * from printer where type=’true’; 

-- f)find the model number and hard-disk size for those PC's that have a speed of 3.2 and price less than $2000. 
 
Select MODEL, HD, from PC where SPEED=’3.2’ AND PRICE<2000; 

3.多关系查询/多表查询

select name
from movies, movieexec
where title='Star Wars' and producerc= cert;
-- movies 表中和 movieexec 表中,title 为 Star Wars 且 product字段 和 cert字段 一样的 name 

select moviestar.name, movieexec.name
from moviestar, movieexec
where moviestar.address = movieexec.address;
-- moviestar 中的 address 与 movieexec 中的 address 相同的数据的 name 分别是什么

在这里插入图片描述

select star1.name, star2.name
from moviestar star1, moviestar star2
where star1.address = star2.address
and star1.name<star2.name;
-- moviestar 表中的 star1 和另一个同在 moviestar 的 star2 的地址 且 star1 名字小于 star2 的名字(这里的小于是字符串比较),输出符合条件的这两个 star 的名字 

在这里插入图片描述

select star1.name, star2.name
from moviestar star1, moviestar star2;
-- moviestar 表格中 name 字段与自身的笛卡尔积 

4.练习B(针对多表查询的练习)

-- a) give the manufacturer and speed of laptops with a hard disk of at least thirty gigabytes.   
 
Select product.maker, laptop.speed from product, laptop where product.model=laptop.model AND laptop.hd>=30;

-- b)find the model number and price of all products of any type) made by manufacturer B. 
 
Select product.model, pc.price as price 
from product, pc 
where product.maker = ‘B’ AND product.model = pc.model 
UNION 
Select product.model, laptop.price as price 
from product, laptop 
where product.maker = ‘B’ AND product.model = laptop.model 
UNION 
Select product.model, printer.price as price 
from product, printer where product.maker = ‘B’ AND product.model = printer.model;

-- c)find those manufacturers that sell laptops, but not pc's 
 
select distinct maker 
from product 
where type = 'laptop' AND  maker not in 
(select distinct maker from product where type = 'pc'); 

-- d)find those hard-disk size that occur in two or more pc's(这里model为主键) 
 
select distinct pc1.hd 
from pc pc1, pc pc2 
where pc1.model != pc2.model AND pc1.hd = pc2.hd; 

-- e)find those pairs of PC models that have both the same speed and RAM. 
 
Select pc1.model,pc2.model 
from pc pc1,pc pc2 
where pc1.speed = pc2.speed AND pc1.ram=pc2.ram AND pc1.model != pc2.model;

-- f)find those manufacturers of at least two diffrent computers(PC"s or laptops) with speeds of at least 3.0

select distinct p1.maker 
from product p1, product p2 
where p1.maker = p2.maker 
AND p1.model != p2.model 
AND  p1.model in 
(select pc.model as model from pc where pc.speed >3.0 
 union 
 select laptop.model as model from laptop where laptop.speed >3.0) AND p2.model in 
(select pc.model as model from pc where pc.speed >3.0 
 union 
 select laptop.model as model from laptop where laptop.speed >3.0); 

三、数据库高级查询与更新

1.子查询

Select name  
From movieexec 
Where cert = (Select producerc From movies Where title='Star Wars'); -- 使用子查询,输出 movieexec 中这样一行的 name 字段, movieexec 的 cert 字段的内容与 movies 的 title 为'Star Wars'的 producerc 字段的内容相同 

-- *** Conditions Involving Tuples: (条件涉及元组)
SELECT name  
FROM movieexec 
WHERE cert in -- movieexec 表内 cert 为……的 name 
( 
    SELECT producerc  
    FROM movies  
    WHERE (title, year) in -- movies 表内 title 和 year 分别等于……的 producerc 
    ( 
        SELECT MOVIETITLE, MOVIEYEAR 
        FROM starsin 
        WHERE STARNAME = 'Harrison Ford' )-- starsin 表内的 starname 为 Harrison Ford 的 movietitle 和 movieyear 
);
-- 表内 cert 为 表内 title 和 year 分别等于 starsin 表内的 starname 为 Harrison Ford 的 movietitle 和 movieyear 的 producerc 的 name。

SELECT name 
FROM movieExec, movies, starsin 
WHERE cert = producer    -- movieexec 和 movies 靠 字段cert 和 字段producer 相连接 
AND title = MOVIETITLE   -- movies 和 starsin 靠 字段title 和 字段movietitle 相连接 
AND year = movieyear    -- 并且,movies 的 year 要等于 starsin 的 movieyear 
AND STARNAME = 'Harrison Ford'; -- 而 starsin 的 starname 要等于 Harrison Ford 


-- *** Correlated Subqueries: (相关子查询)
SELECT title, year  
FROM movies old  
WHERE year < ANY  ( select year from movies where title = old.title);
-- 输出表 movies 中同一 title 的 year 不是最大的 一行的 title 和 year


-- *** Subqueries in from Clauses: (子查询从句)
SELECT name 
from movieexec, 
(
    SELECT PRODUCERC 
    from movies, starsin 
    where title = MOVIETITLE and year = movieyear  and STARNAME = 'Harrison Ford') prod 
    -- 跟 Harrison Ford 主演的电影的年份一样的电影 的生产编号 
WHERE cert = prod.producerc; 


-- *** SQL Join Expressions: (SQL连接表达式)
select * from movies cross join starsin;
-- Movies 与 starsin 的笛卡尔积,movies 有 11 行,starsin 有 10 行,所以结果有 11*10=110行

select * from movies join starsin on title = MOVIETITLE AND year = MOVIEYEAR; 
-- Movies 与 starsin 自然连接,其中 movies 表中的 title 即为 starsin 表中的 movietitle,前 者中的 year 即为后者中的 movieyear


-- *** Natural Join 
select * from movies natural join starsin; 
-- movies 和 starsinstarsin 自然连接即为两者的笛卡尔积,因为没有共同属性
select * from moviestar natural join movieexec; 
-- 而 moviestar 和 movieexec 自然连接时,因为有 name 和 address 是共同属性


-- *** Outer joins
select * from moviestar left outer join movieexec on moviestar.name = movieexec.name 
UNION 
select * from moviestar  right outer join movieexec on moviestar.name = movieexec.name; 
-- Moviestar 左外连接 movieexec 与 moviestar 右外连接 movieexec 的并集

SELECT *  FROM moviestar left outer join movieexec on moviestar.name = movieexec.name; 
-- 左外连接,行数等于 moviestar 
select * from moviestar right outer join movieexec on moviestar.name = movieexec.name; 
-- 右外连接,行数等于 movieexec 

2.全关系操作(Full-Relation Operations )

-- *** Duplicates in Unions, Interseciions, mui Differences 
select title, year from movies  
union all 
select movietitle as title , movieyear as year from starsin;
-- Union all,把联合后的所有结果都展示,包括重复的


-- *** Grouping and Aggregation In SQL 
select avg(networth)  from movieexec; 
-- avg,统计平均

在这里插入图片描述

select count(*)  from starsin; 
-- Count 统计数量,参数为*时,返回该表的行数 

在这里插入图片描述

select count(starname) from starsin; 
-- Count 统计数量,参数为字段名时,忽略该字段为 NULL 的记录 
select count(distinct starname)  from starsin;
-- 返回指定列的不同值得记录 

Select studioname,sum(length) 
from movies  
group by studioname; 
-- 以 studioname 来分组,求和 length
-- 有group by来分组时,一般在select都使用聚合函数sum、avg

在这里插入图片描述

Select name, sum(length) 
From movieExec, movies 
Where producerc =cert 
Group by name 
Having min(year)<1985; 
-- Movieexec 与 movies,其中 producerc 等于 cert,以 name 分组,输出每组最小年份小于 1985 的 name 和 sum。

Select name, sum(length) 
From movieExec, movies 
Where producerc=cert 
And year<1985 
Group by name 
Having min(year)<1985; 
-- 在分组求 length 和之前剔除年份大于等于 1985 的,只统计年份小于 1985 的 length 和。

3.数据库修改操作

insert into studio( name) 
select distinct studioname 
from movies 
where studioname not in( select name from studio); 
-- 新增在 movies 表中的 studioname 而不在原来的 studio 表中的 studioname,其他两个字 段为空 

在这里插入图片描述

delete from starsin 
where movietitle='The maltese falcon' 
and movieyear=1942 
and starname='Sydney Greenstreet'; 
-- 删除记录

update movieexec 
set name ='Pres.' 
where cert in(select presc from studio); 
-- 更新记录

--------本篇完---------

展开阅读全文
©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值