简介

精彩点评

• 

  孔鹏

  推荐

  用mysql把书上代码操作了一遍，内容比较基础，实际上需要记得内容不是很多.这是一本sql的入门书籍，后面的存储过程，事务处理，游标，索引，触发器等基本等于没讲，只是了解了一下，要想深入需要补充其他知识，但是这本书对新手很友好，学起来比较轻松 SELECT prod_name FROM Products; SELECT pro_names FROM Procucts; SELECT prod_name FROM Products; SELECT prod_id,prod_name,prod_price FROM Products; SELECT * FROM Products; SELECT vend_id FROM products; SELECT DISTINCT vend_id FROM products;#distinic 返回唯一值 SELECT prod_name FROM products LIMIT 5 OFFSET 5; SELECT prod_name FROM Products ORDER BY prod_name; SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name; SELECT prod_id,prod_price,prod_name FROM products ORDER BY 2,3; SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC; SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC,prod_name; SELECT prod_name,prod_price FROM products WHERE prod_price=3.49; SELECT prod_name,prod_price FROM products WHERE prod_price<=10; SELECT vend_id,prod_name FROM products WHERE vend_id <>"DLL01"; SELECT vend_id,prod_name FROM products WHERE vend_id!="DLL01"; SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10; SELECT prod_name FROM products WHERE prod_price IS NULL; SELECT cust_name,cust_email FROM customers#第一到第四节：检索，排序，过滤 SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id="DLL01" AND prod_price<=4; SELECT prod_name,prod_price,vend_id FROM products WHERE vend_id="DLL01" OR vend_id="BRS01" ORDER BY prod_price SELECT prod_name,prod_price FROM products WHERE (vend_id="DLL01" OR vend_id="BRS01") AND prod_price>=10; SELECT prod_name,prod_price FROM products WHERE vend_id IN ("DLL01","BRS01") ORDER BY prod_name; SELECT prod_name,vend_id FROM products WHERE NOT vend_id="DLL01" ORDER BY prod_name; SELECT prod_name FROM products WHERE vend_id<>"DLL01" ORDER BY prod_name;#第五章AND,OR,IN,NOT SELECT prod_id,prod_name FROM products WHERE prod_name LIKE "F%y"; SELECT prod_id,prod_name FROM products WHERE prod_name LIKE "__ inch teddy bear"; SELECT cust_contact FROM customers WHERE cust_contact LIKE "J%" ORDER BY cust_contact;#第六章通配符：like %,_ []适用于access SELECT CONCAT(RTRIM(vend_name),"(", RTRIM(vend_country),")") AS vend_title FROM vendors ORDER BY vend_name; SELECT * FROM vendors ORDER BY vend_name; SELECT prod_id, quantity, item_price, quantity * item_price AS expanded_price FROM orderitems WHERE order_num=20008; SELECT prod_id, quantity, item_price, NOW() AS expanded_price FROM orderitems WHERE order_num=20008;#第7课 创建计算字段 SELECT vend_name,UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name; SELECT cust_name,cust_contact FROM customers WHERE SOUNDEX(cust_contact)=SOUNDEX("Michael Green") SELECT order_num,order_date FROM orders WHERE YEAR(order_date)=2012;#第8课 使用函数处理数据 SELECT AVG(prod_price) AS avg_price FROM products; SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id="DLL01"; SELECT prod_price,vend_id FROM products; SELECT COUNT(*) AS num_cast FROM customers; SELECT COUNT(cust_email) AS num_cust FROM customers; SELECT * FROM orderitems; SELECT MAX(prod_price) AS max_price FROM products; SELECT MIN(prod_price) AS min_price FROM products; SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num=20005; SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num=20005; SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id ="DLL01" SELECT * FROM products WHERE vend_id ="DLL01" SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM products; #第9课 汇总数据 SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id; SELECT cust_id,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2; SELECT vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price>=4 GROUP BY vend_id HAVING COUNT(*)>=2; SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=2; SELECT order_num,COUNT(*) AS items FROM orderitems GROUP BY order_num HAVING COUNT(*)>=3 ORDER BY items,order_num;#第10课 分组数据 我们看到了如何使用HAVING子句过滤特定的组，还知道了ORDER BY和GROUP BY之间以及WHERE和HAVING之间的差异。 SELECT cust_name,cust_contact FROM customers WHERE cust_id IN(SELECT cust_id FROM orders WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id="RGAN01")); SELECT COUNT(*) AS orders FROM orders WHERE cust_id="1000000001"; SELECT cust_name, cust_state, cust_id, (SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id) AS orders FROM customers ORDER BY cust_name;#第11课 使用子查询 SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id=products.vend_id; SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id; SELECT prod_name,vend_name,prod_price,quantity FROM orderitems,products,vendors WHERE products.vend_id=vendors.vend_id AND orderitems.prod_id=products.prod_id AND order_num=20007; SELECT cust_name,cust_contact FROM customers,orders,orderitems WHERE customers.cust_id=orders.cust_id AND orderitems.order_num=orders.order_num AND prod_id="RGAN01";#第12课 联结表 SELECT cust_name,cust_contact FROM customers AS c,orders AS o,orderitems AS oi WHERE c.cust_id=o.cust_id AND oi.order_num=o.order_num AND prod_id="RGAN01"; SELECT DISTINCT cust_id,cust_name,cust_contact FROM customers WHERE cust_name = (SELECT DISTINCT cust_name FROM customers WHERE cust_contact="Jim Jones"); SELECT DISTINCT c1.cust_id,c1.cust_name,c1.cust_contact FROM customers AS c1,customers AS c2 WHERE c1.cust_name = c2.cust_name AND c2.cust_contact="Jim Jones"; SELECT DISTINCT customers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id=orders.cust_id ORDER BY cust_id; SELECT customers.cust_id, COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id; SELECT customers.cust_id, COUNT(orders.order_num) AS num_ord FROM customers LEFT OUTER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id;#第13课 创建高级联结 SELECT DISTINCT cust_name,cust_contact,cust_email FROM customers WHERE cust_state IN ("IL","IN","MI") UNION SELECT DISTINCT cust_name,cust_contact,cust_email FROM customers WHERE cust_name="Fun4All" ORDER BY cust_name,cust_contact; SELECT DISTINCT cust_name,cust_contact,cust_email FROM customers WHERE cust_state IN ("IL","IN","MI") OR cust_name="Fun4All"; #第14课 组合查询 SELECT * FROM customers WHERE cust_id="1000000005" INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES("1000000006", "Toy Land", "123 Any Street", "New York", "NY", "11111", "USA"); INSERT INTO customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) SELECT cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country FROM custnew; CREATE TABLE custcopy AS SELECT * FROM customers; SELECT * FROM custcopy#第15课 插入数据 UPDATE customers SET cust_email="kim@thetoystore.com" WHERE cust_id="1000000005" UPDATE customers SET cust_contact="Sam Roberts", cust_email="sam@toyland.com" WHERE cust_id="1000000006"; UPDATE customers SET cust_email=NULL WHERE cust_id="1000000005"; DELETE FROM customers WHERE cust_id="1000000006"#第16课 更新和删除数据 delete删除行，update更新列 CREATE TABLE products (prod_id CHAR(10) NOT NULL, vend_id CHAR(10) NOT NULL, prod_name CHAR(254) NOT NULL, prod_price DECIMAL(8,2) NOT NULL, prod_desc TEXT(1000) NULL ); SELECT * FROM products CREATE TABLE orders (order_nu INTEGER NOT NULL, order_date DATETIME NOT NULL, cust_id CHAR(10) NOT NULL ); CREATE TABLE vendors (vend_id CHAR(10) NOT NULL, vend_name CHAR(50) NOT NULL, vend_address CHAR(50) , vend_city CHAR(50) , vend_state CHAR(5) , vend_zip CHAR(10) , vend_country CHAR(50) ); CREATE TABLE orderitems (order_num INTEGER NOT NULL, order_item INTEGER NOT NULL, prod_id CHAR(10) NOT NULL, quantity INTEGER NOT NULL DEFAULT 1, item_price DECIMAL(8,2) NOT NULL ); ALTER TABLE vendors ADD vend_phone CHAR(20); ALTER TABLE vendors DROP COLUMN vend_phone; #第17课 创建和操作表 CREATE TABLE products (prod_id CHAR(10) NOT NULL, vend_id CHAR(10) NOT NULL, prod_name CHAR(254) NOT NULL, prod_price DECIMAL(8,2) NOT NULL, prod_desc TEXT(1000) NULL ); CREATE TABLE orders (order_nu INTEGER NOT NULL, order_date DATETIME NOT NULL, cust_id CHAR(10) NOT NULL ); DROP TABLE custcopy; SELECT DISTINCT cust_name,cust_contact FROM customers AS c,orders AS o,orderitems AS oi WHERE c.cust_id=o.cust_id AND oi.order_num=o.order_num AND prod_id="RGAN01"; CREATE VIEW productcustomers AS SELECT cust_name,cust_contact,prod_id FROM customers AS c,orders AS o,orderitems AS oi WHERE c.cust_id=o.cust_id AND oi.order_num=o.order_num; SELECT DISTINCT cust_name,cust_contact FROM productcustomers WHERE prod_id="RGAN01"; SELECT CONCAT(RTRIM(vend_name),"(", RTRIM(vend_country),")") AS vend_title FROM vendors ORDER BY vend_name; CREATE VIEW vendorlocations AS SELECT CONCAT(RTRIM(vend_name),"(", RTRIM(vend_country),")") AS vend_title FROM vendors; SELECT * FROM vendorlocations CREATE VIEW customersemaillist AS SELECT cust_id,cust_name,cust_email FROM customers WHERE cust_email IS NOT NULL; SELECT DISTINCT* FROM customersemaillist CREATE VIEW orderitemsexpanded AS SELECT DISTINCT order_num, prod_id, quantity, item_price, quantity*item_price AS expended_price FROM orderitems; SELECT * FROM orderitemsexpanded WHERE order_num=20006;#第18课 使用视图 #第19课 使用存储过程 无mysql代码，如深入需看DBMS文档 DELETE FROM orders; ROLLBACK; START TRANSACTION DELETE orderitems WHERE order_num=12345; DELETE orders WHERE order_num=12345; COMMIT ; SAVEPOINT delete1; ROLLBACK TO delete1; #第20课 管理事务处理 DECLARE custcursor CURSOR FOR SELECT * FROM customers WHERE cust_email IS NULL # 创建此游标的MySQL版本，执行出错 OPEN CURSOR custcursor#第21课 使用游标 未讲诉详细使用，详细使用方法，需看DBMS文档 SELECT * FROM vendors CREATE TABLE vendors (vend_id CHAR(10) NOT NULL PRIMARY KEY, vend_name CHAR(50) NOT NULL, vend_address CHAR(50) NULL, vend_city CHAR(50) NULL, vend_state CHAR(5) NULL, vend_zip CHAR(10) NULL, vend_country CHAR(50) NULL, ); ALTER TABLE vendors ADD CONSTRAINT PRIMARY KEY (vend_id); CREATE TABLE orders (order_num INTEGER NOT NULL PRIMARY KEY, order_date DATETIME NOT NULL, cust_id CHAR(10) NOT NULL REFERENCES customers(cust_id) ); ALTER TABLE customers ADD CONSTRAINT PRIMARY KEY (cust_id); SELECT * FROM customers ALTER TABLE orders ADD CONSTRAINT FOREIGN KEY(cust_id) REFERENCES customers(cust_id) CREATE TABLE orderittems (order_num INTEGER NOT NULL, order_item INTEGER NOT NULL, prod_id CHAR(10) NOT NULL, quantity INTEGER NOT NULL CHECK(quantity>0), item_price DECIMAL(9,2) NOT NULL );#无money ADD CONSTRAINT CHECK(gender LIKE"[MF]") CREATE INDEX prod_name_ind ON products(prod_name); CREATE TRIGGER customer_state ON customers FOR INSERT,UPDATE AS UPDATE customers SET cust_state-UPPER(cust_state) WHERE customers.cust_id=inserted.cust_id;#第22课 高级SQL特性 索引可改善数据检索的性能，触发器可以用来执行运行前后的处理，安全选项可用来管理数据访问。

• 

  诸葛风风

  推荐

  工作中没有用过数据库，想了解一下这方面的知识。这本书清晰地介绍了关系型数据库的常用操作的SQL语言，以一个供应商的例子贯穿，原理性的东西讲解较少，入门不错。

• 

  钟茂胜

  推荐

  子仲于2020/08/25复习完本书，都是很基础的知识，还有些基础知识，这本书没有讲到，书无完书，不苛求。 不管是哪类语言，如果很久不用，总归是容易忘记，以此鞭策自己吧，学以致用，为用而学。[玫瑰]

• 

  ...未央

  推荐

  算是浅显易懂的吧，老公从这本书自学起家成为大数据开发工程师，一直推荐我看，感觉毕业这几年，自己长进很少，学海无涯，不进则退，只有不断的学习成长，才能不被快速进阶的社会所淘汰，今年终于下决心线上线下一起看本书，感觉还算好理解，算是对sql有大概的了解，当然，看书只是第一步，后续还要做大量的上机训练，实际操作，才能真正掌握，数据展现应该是一门学术艺术，会用工具，还要思考怎样更好的让旁人一看就懂，自己还是要继续努力，加油！争取进阶数据行业工作者[奋斗]

• 

  snail

  推荐

  《SQL 必知必会》这本书是学习数据库的入门级经典书籍，经典中的经典，里面知识点虽然都比较基础，不过讲解的清晰简介，也比较全面。这也是我看过的最棒的SQL入门书啦，短小精悍，实用性超强，一点也不枯燥。”比起我们的教程《数据库原理及应用教程》，这里讲的也太通俗易懂啦！满满的都是干货，认真看完真的会收货很多！超棒！SQL小白必备书籍，五星������������������������������那是必须滴。这次看这本书算是对已经学过的知识的做个复习，当然这里面还有很多我没有学过，所以这次又涨了不少知识。这本书有一个超亮点就是在书上给出一些在学习SQL需要注意的地方，和不同DBMS的语句差别，对于初学者真的超级友好，细节性的知识很友好地在旁边标注，超喜欢它讲解一个知识点时，会把不同数据库软件支持的形式做对比说出来。当我把这本书的都看完的时候，以前有疑惑的地方慢慢的就解开啦。后面几章的内容就比较偏理论的，什么存储过程，什么事务处理，什么游标的就只是涉及了一些概念，如果还想要继续深入学习SQL的话还是需要阅读其他书籍的。[鼓掌][鼓掌][鼓掌]