MySQL 基础知识与常用命令

MySQL

　　MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），MySQL数据库系统使用最常用的数据库管理语言--结构化查询语言（SQL）进行数据库管理。

　　MySQL在过去由于性能高、成本低、可靠性好，已经成为最流行的开源数据库，因此被广泛地应用在Internet上的中小型网站中。

 

SQL(Structured Query Language)

  事务的四个特性ACID
　　ACID，是指数据库管理系统（DBMS）在写入/更新资料的过程中，为保证事务（transaction）是正确可靠的，所必须具有的四个特性：原子性（atomicity，或称不可分割性）、一致性（consistency）、隔离性（isolation，又称独立性）、持久性（durability）。

 

范式

常用（第一范式-->BC范式)

 

order by

　　对指定列的结果集进行排序。

 

group by 

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

 

　　聚合函数（Aggregate Functions)

• • Average: avg
  • Minimum: min
  • Maximum: max
  • Total: sum
  • Count: count

 　　聚合函数会忽略空值。

 

DDL和DML

 

嵌套子查询（Nested Subqueries)

　　in, all, exists, some, any （可以相互转换）。

　　eg: here.

 

 这里有一个简单的SQL测验（w3cschool).

 

数据库连接池是什么意思？

　　像打开关闭数据库连接这种和数据库的交互可能是很费时的，尤其是当客户端数量增加的时候，会消耗大量的资源，成本是非常高的。可以在应用服务器启动的时候建立很多个数据库连接并维护在一个池中。连接请求由池中的连接提供。在连接使用完毕以后，把连接归还到池中，以用于满足将来更多的请求。

 

 

MySQL

　　mysql 使用的数据类型分为三类： 数值类型，日期和时间类型，字符类型。

 

Software

installation (on raspbian as an example)

sudo apt-get install mysql-server 

login with password prompt

mysql -u root -p

show database;     # list databases
use db-name;       # use a database
show tables;       # list table for current database being used

 

Basic CRUD

CREATE DATABASE demo;    # create a database named demo;

# 创建一个表tutorials_tbl
CREATE TABLE demo.tutorials_tbl(
   tutorial_id INT NOT NULL AUTO_INCREMENT,
   tutorial_title VARCHAR(100) NOT NULL,
   tutorial_author VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( tutorial_id )
);

# 查询
SELECT tutorial_id, tutorial_title,  tutorial_author FROM tutorials_tbl;　

# 更新
UPDATE tutorials_tbl SET tutorial_title="Learning JAVA" WHERE tutorial_id=3;

# 删除
DELETE FROM tutorials_tbl WHERE tutorial_id=3;

# 删除表
DELETE FROM tutorials_tbl;
DROP TABLE tutorials_tbl;    # drop表

DROP DATABASE demo;   # drop数据库demo

　　

MySQL keywords MySQL 5.7

 

例子

在 db_SPJ 中创建以下四个关系（表）

• 供应商表 S(SNO,SNAME,STATUS,CITY)  
• 零件表 P(PNO,PNAME,COLOR,WEIGHT)
• 工程项目表 J(JNO,JNAME,CITY)
• 供应情况表 SPJ(SNO,PNO,JNO,QTY)

 每一张表都必须有主键。
 需要使用外键的表必须使用外键。
 根据需要适当采用唯一值、检查、非空和默认值约束。要求这四种约束在 S、P 和 J 表这三张表中至少使用一次。
 根据主键、外键、唯一值、检查、非空和默认值六种约束的特性，设计适 当的方案对 S、P 和 J 表中的这六种约束进行检验。方案自定。一种约束进行检验一次即可

 

使用 `SHOW CREATE DATABASE db_SPJ;`来查看更多信息(类似地，可以用它来查看创建表的信息）

DDL:

USE db_SPJ;

CREATE TABLE S(SNO char(10) ,SNAME char(10) not null,STATUS int check(STATUS >0),CITY char(30) not null, primary key(SNO)) ;
CREATE TABLE P(PNO char(10) UNIQUE,PNAME char(20) not null,COLOR char(10) default '白',WEIGHT int CHECK(WEIGHT >0), primary key(PNO));
CREATE TABLE J(JNO char(10) UNIQUE,JNAME char(20) not null,CITY char(30) not null, primary key(JNO));
CREATE TABLE SPJ(SNO char(10),PNO char(10),JNO char(10),QTY int, primary key(SNO,PNO,JNO));
ALTER TABLE SPJ ADD CONSTRAINT FK_SNO foreign key(SNO) references S(SNO) ON DELETE CASCADE;
ALTER TABLE SPJ ADD CONSTRAINT FK_PNO foreign key(PNO) references P(PNO) ON DELETE CASCADE;
ALTER TABLE SPJ ADD CONSTRAINT FK_JNO foreign key(JNO) references J(JNO) ON DELETE CASCADE;

 插入记录：

INSERT INTO  S VALUES
('S1','精益',20,'天津'),
('S2','盛锡',10,'北京'),
('S3','东方红',30,'北京'),
('S4','丰泰',20,'天津'),
('S5','为民',30,'上海');

INSERT INTO P VALUES
('P1','螺母','红',12),
('P2','螺栓','绿',17),
('P3','螺丝刀','蓝',14),
('P4','螺丝刀','红',14),
('P5','凸轮','蓝',40),
('P6','齿轮','红',30);

INSERT INTO  J VALUES
('J1','三建','北京'),
('J2','一汽','长春'),
('J3','弹簧厂','天津'),
('J4','造船厂','天津'),
('J5','机车厂','唐山'),
('J6','无线电厂','常州'),
('J7','半导体厂','南京');

INSERT INTO SPJ VALUES
('S1','P1','J1',200),
('S1','P1','J3',100),
('S1','P1','J4',700),
('S1','P2','J2',100),
('S2','P3','J1',400),
('S2','P3','J2',200),
('S2','P3','J4',500),
('S2','P3','J5',400),
('S2','P5','J1',400),
('S2','P5','J2',100),
('S3','P1','J1',200),
('S3','P3','J1',100),
('S4','P5','J1',100),
('S4','P6','J1',100),
('S4','P6','J4',100),
('S5','P2','J4',100),
('S5','P3','J1',200),
('S5','P6','J2',200),
('S5','P6','J4',500);

 

实现以下查询（Transact-SQL)：

[1] 找出所有供应商的姓名和所在城市。 
[2] 找出所有零件的名称、颜色和重量。 
[3] 找出使用了供应商 S1 所供应的零件的工程号码。 
[4] 找出工程 J2 使用的各种零件的名称和数量。
[5] 找出上海供应商供应的所有零件的零件号码。
[6] 找出使用了上海供应商供应的零件的工程名称。 
[7] 找出供应工程 J1 零件的供应商号 SNO。 
[8] 找出供应工程 J1 零件 P1 的供应商号 SNO。 
[9] 找出供应工程 J1 红色零件的供应商号 SNO。 
[10] 找出没有使用天津供应商生产的红色零件的工程号 JNO。 
[11] 求所有有关 project 的信息。 
[12] 求在北京的所有 project 的信息。 
[13] 求为 project（工程）J1 提供 part（零件）的 supplier（供应商）的号 码。 
[14] 求数量在 300 到 750 之间的发货。 
[15] 求所有的零件颜色 / 城市对。注意：这里及以后所说的“所有”特指 在数据库中。 [16] 求所有的 supplier-number / part-number / project-number 对。其中所 指的供应商和工程在同一个城市。 
[17] 求所有的 supplier-number / part-number / project-number 对。其中所 指的供应商和工程不在同一个城市。 
[18] 求由北京供应商提供的零件的信息。 
[19] 求由北京供应商为北京工程供应的零件号。 
[20] 求满足下面要求的城市对，要求在第一个城市的供应商为第二个城 市的工程供应零件。 
[21] 求供应商为工程供应的零件的号码，要求供应商和工程在同一城市。 
[22] 求至少被一个不在同一城市的供应商供应零件的工程号。 
[23] 求由同一个供应商供应的零件号的对。
[24] 求所有由供应商 S1 供应的工程号。 
[25] 求供应商 S1 供应的零件 P1 的总量。 
[26] 对每个供应给工程的零件，求零件号、工程号和相应的总量。 
[27] 求为单个工程供应的零件数量超过 350 的零件号。 
[28] 求由 S1 供应的工程名称。 
[29] 求由 S1 供应的零件颜色。 
[30] 求供应给北京工程的零件号。 
[31] 求使用了 S1 供应的零件的工程号。 
[32] 求 status 比 S1 低的供应商号码。 
[33] 求所在城市按字母排序为第一的工程号。 
[34] 求被供应零件P1 的平均数量大于供应给工程J1 的任意零件的最大 数量的工程号。 [35] 求满足下面要求的供应商号码，该供应商供应给某个工程零件 P1 的 数量大于这个工程被供应的零件 P1 的平均数量。 
[36] 求没有被北京供应商供应过红色零件的工程号码。 
[37] 求所用零件全被 S1 供应的工程号码。 
[38] 求所有北京工程都使用的零件号码。 
[39] 求对所有工程都提供了同一零件的供应商号码。 
[40] 求使用了 S1 提供的所有零件的工程号码。 
[41] 求至少有一个供应商、零件或工程所在的城市。 
[42] 求被北京供应商供应或被北京工程使用的零件号码。 
[43] 求所有 supplier-number / part-number 对，其中指定的供应商不供应 指定的零件。 
[44] 向 p 表追加如下记录（P0,PN0,蓝）。 
[45] 把零件重量在 15 到 20 之间的零件信息追加到新的表 p1 中。 
[46] 向 s 表追加记录（s1, n2, ’上海’）能成功吗?为什么？ 
[47] 把 s、p、j 三个表中的 s#,p#,j#列进行交叉联接，把结果追加到 spj1 表中（如果只考虑下面表格中的原始数据，应该在 spj1 表中追加多 少条记录？你是如何计算记录条数的？）。 
[48] 向 spj 表追加（s6,p1,j6,1000）本操作能正确执行吗？为什么？如果 追加(s4,p1,j6,-10) 行吗？如果现在想强制追加这两条记录该怎么
办？ 
[49] 把 s1 供应商供应的零件为 p1 的所有项目对应的数量 qty改为 500。 
[50] 把 qty值大于等于 1000 的所有供应商城市更改为‘北京’ 。 
[51] 把 j1 更改成 j7，本操作能正确执行吗？为什么？如果改成 j0 呢？spj 表中记录有何变化？为什么？ 
[52] 把零件重量低于 15 的增加 3，高于 15 的增加 2。 
[53] 删除为 j7 工程供应零件的所有供应商信息（如果建立外键时没有带 级联删除选项，本操作能正确执行吗？为什么？） 
[54] 删除 p1 表中所有记录。 

[55] 删除供应商和工程在同一个城市的供应商信息。 

 

# DML on spj database
USE db_spj;

# [1]
SELECT sname,city FROM s;

# [2]
SELECT pname,color,weight FROM p;

#[3]
SELECT jno FROM spj
WHERE sno = 'S1';

#[4]
SELECT p.pname, spj.qty FROM spj 
LEFT JOIN p ON (spj.pno = p.pno)
WHERE spj.jno = 'J2';

#[5] 
SELECT DISTINCT spj.pno FROM spj
WHERE spj.sno IN (SELECT sno FROM s WHERE s.city='上海');

#[6] nested query
SELECT DISTINCT j.jname FROM j
RIGHT JOIN spj ON 
(j.jno in (SELECT jno FROM spj WHERE spj.sno IN (SELECT sno FROM s WHERE s.city='上海')));

#[7]
SELECT DISTINCT sno FROM spj 
WHERE spj.jno='J1';

#[8]
SELECT sno FROM spj
WHERE jno='J1' AND pno='P1';

#[9]
SELECT DISTINCT sno FROM
p INNER JOIN spj ON p.pno=spj.pno
WHERE p.color='红';

#[10]
SELECT DISTINCT spj.jno FROM spj
WHERE spj.sno NOT IN (SELECT sno FROM s WHERE s.city='天津')
OR spj.pno NOT IN (SELECT pno FROM p WHERE p.color='红');

#[11]
SELECT SPJ.JNO,JNAME,CITY,SNO,PNO,QTY FROM 
J inner join SPJ on J.JNO=SPJ.JNO 
ORDER BY SPJ.JNO; 

#[12]
SELECT SPJ.JNO,JNAME,CITY,SNO,PNO,QTY FROM
 J inner join SPJ on J.JNO=SPJ.JNO  WHERE CITY='北京' 
 ORDER BY SPJ.JNO;

#[13]
SELECT sno FROM spj
WHERE spj.jno = 'j1';

#[14]
SELECT SNO,PNO,JNO FROM SPJ WHERE QTY BETWEEN 300 AND 750;

#[15]
SELECT DISTINCT p.color, s.city
FROM (p INNER JOIN spj ON p.pno=spj.pno)
INNER JOIN s ON s.sno = spj.sno
ORDER BY color ASC;

# [16]
select distinct spj.sno,spj.jno,spj.pno from s,spj
right join j on spj.jno=j.jno
where s.sno=spj.sno and s.city=j.city;

# [17]
select distinct spj.sno,spj.jno,spj.pno from s,spj
right join j on spj.jno=j.jno
where s.sno=spj.sno and s.city!=j.city;

# [18]
select * from p
where p.pno in
(select spj.pno from spj where spj.sno in
    (select s.sno from s where s.city='北京')
);

# [19]
select distinct spj.pno from s,spj
right join j on spj.jno=j.jno
where s.sno=spj.sno and s.city='北京' and s.city=j.city;

# [20]
select distinct s.city, j.city from s,spj
right join j on spj.jno=j.jno
where s.sno=spj.sno;


#[55]
DELETE FROM S WHERE S.SNO
IN (SELECT SPJ.SNO FROM (SPJ INNER JOIN S ON SPJ.SNO=S.SNO) INNER JOIN J ON SPJ.JNO=J.JNO  
WHERE S.CITY=J.CITY) ;

 

　　

Reference

http://blog.webfsd.com/article-7.html