db

gistfile1.md

#实验二 SQL数据定义和操作

####李奥 3130001009 ####日期：2014/10/16

##实验目的：

1. 掌握关系数据库语言SQL的使用。
2. 完成如下SQL作业能上机通过。

##实验平台：

• OS X 10.10, Ubuntu server 14.04 LTS, MYSQL

##实验内容和要求：

1. 建立数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

USE db_name

2. 数据定义：表的建立/删除/修改; 索引的建立/删除；视图的建立/删除

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name   #create a table
    (create_definition,...)
    [table_options]
    [partition_options]

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name    #modify the table
    [alter_specification [, alter_specification] ...]
    [partition_options]

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

3. 数据更新：用 insert/delete/update命令插入/删除/修改表数据。

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name,...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

4. 数据查询： 单表查询，多表查询， 嵌套子查询等。

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

5. 视图操作：通过视图的数据查询和数据修改

6. 第三章作业Exercise 3.2, 3.3, 3.14上机通过，并将完成代码文本插入本实验报告。

#create tables

CREATE TABLE `employee` ( `employee_name` char(20)  PRIMARY KEY, `street` text, `city` text ) DEFAULT CHARSET=utf8;
CREATE TABLE `works` ( `employee_name` char(20) PRIMARY KEY, `company_name` char(50), `salary` double ) DEFAULT CHARSET=utf8; 
CREATE TABLE `company` ( `company_name` char(50) PRIMARY KEY, `city` text ) DEFAULT CHARSET=utf8;
CREATE TABLE `managers` ( `employee_name` char(20) PRIMARY KEY, `manager_name` char(20) ) DEFAULT CHARSET=utf8;

#Exercise 3.2

#a
SELECT employee.employee_name, city FROM employee, works WHERE company_name='First Bank Corporation' AND works.employee_name=employee.employee_name;

#b
SELECT employee_name, street, city FROM (employee NATURAL LEFT OUTER JOIN works) WHERE company_name='First Bank Corporation' AND salary>10000;

#c
SELECT employee_name FROM (employee NATURAL LEFT OUTER JOIN works) WHERE NOT company_name='First Bank Corporation';

#d
SELECT employee_name FROM works WHERE salary > (SELECT MAX(salary) as max_salary FROM works WHERE company_name='Small Bank Corporation');

#e
SELECT company_name FROM company AS C WHERE NOT EXISTS ( SELECT city FROM company WHERE company_name='Small Bank Corporation' AND city NOT IN (SELECT R.city FROM company AS R WHERE R.company_name=C.company_name));

#f
SELECT company_name, count FROM (SELECT company_name, count(employee_name) AS count FROM works GROUP BY company_name)C  ORDER BY count DESC LIMIT 1;

#g
SELECT company_name FROM works GROUP BY company_name HAVING AVG(salary) > (SELECT AVG(salary) FROM works GROUP BY company_name HAVING company_name="Small Bank Corporation");

#Exercise 3.3

#a
UPDATE employee SET city='Newtown' WHERE employee_name='Jones';

#b
UPDATE works, manages SET works.salary = CASE WHEN salary+salary*0.1 > 1000000 then salary+salary*0.03 else salary+salary*0.1 end WHERE works.employee_name = manages.manager_name AND works.company_name='First Bank Corporation';

#Exercise 3.14
CREATE VIEW v AS SELECT manager_name, AVG(salary) FROM (manages NATURAL LEFT OUTER JOIN works) GROUP BY manager_name;

#Because the from clause has two database relations and what's more, the AVG(salary) attribute was calculated by many items. Updating this attribute will be ambiguous.

##遇到的问题及解决方法

1. While using MYSQL I found that MYSQL doesn't support some syntax like EXCEPT and WITH. After searching this problem via stackoverflow.com, I solved this problem by using NOT IN and LEFT JOIN.

2. MYSQL syntax is different from standard SQL syntax, so it is useful to look up the documentation through this web site, http://dev.mysql.com/doc/.

233333333