SQL语句书写复习数据库Calvin的博客-

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);  -- 更新记录 

——–本篇完———