用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优化建议 1.数据去重使用group by代替distinct, 禁止使用count(distinet); 2. 分区表使用时where条件必须加上分区过滤如: d为每日全量表，day取最新分区 di每日增量表，限定日期，如day betreen 20190415 and 201904161 3.多表关联，先子查询过滤后关联 4.慎用in not in,连续性值过滤用between and代替，如: age in (1.2.3.4) 修改为:age between 1 and 4 5子查询禁止使用*号，只把需要的字段列出来 6. 表关联时一定要加上on条件，避免笛卡尔系数 7.表关联时关联条件一定要在子查询里过滤掉null值，避免数据倾斜; 8.不要对一个表进行重要处理，多使用临时表，尽里做到一次处理多次使用 9慎用order by 大数据排序会很慢
《SQL 必知必会》这本书是学习数据库的入门级经典书籍，经典中的经典，里面知识点虽然都比较基础，不过讲解的清晰简介，也比较全面。这也是我看过的最棒的SQL入门书啦，短小精悍，实用性超强，一点也不枯燥。”比起我们的教程《数据库原理及应用教程》，这里讲的也太通俗易懂啦！满满的都是干货，认真看完真的会收货很多！超棒！SQL小白必备书籍，五星������������������������������那是必须滴。这次看这本书算是对已经学过的知识的做个复习，当然这里面还有很多我没有学过，所以这次又涨了不少知识。这本书有一个超亮点就是在书上给出一些在学习SQL需要注意的地方，和不同DBMS的语句差别，对于初学者真的超级友好，细节性的知识很友好地在旁边标注，超喜欢它讲解一个知识点时，会把不同数据库软件支持的形式做对比说出来。当我把这本书的都看完的时候，以前有疑惑的地方慢慢的就解开啦。后面几章的内容就比较偏理论的，什么存储过程，什么事务处理，什么游标的就只是涉及了一些概念，如果还想要继续深入学习SQL的话还是需要阅读其他书籍的。[鼓掌][鼓掌][鼓掌]
用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有大概的了解，当然，看书只是第一步，后续还要做大量的上机训练，实际操作，才能真正掌握，数据展现应该是一门学术艺术，会用工具，还要思考怎样更好的让旁人一看就懂，自己还是要继续努力，加油！争取进阶数据行业工作者[奋斗]
触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的INSERT、UPDATE和DELETE操作（或组合）相关联。 与存储过程不一样（存储过程只是简单的存储SQL语句），触发器与单个的表相关联。与Orders表上的INSERT操作相关联的触发器只在Orders表中插入行时执行。类似地，Customers表上的INSERT和UPDATE操作的触发器只在表上出现这些操作时执行。 触发器内的代码具有以下数据的访问权： □ INSERT操作中的所有新数据； □ UPDATE操作中的所有新数据和旧数据； □ DELETE操作中删除的数据。 根据所使用的DBMS的不同，触发器可在特定操作执行之前或之后执行。
子仲于2020/08/25复习完本书，都是很基础的知识，还有些基础知识，这本书没有讲到，书无完书，不苛求。 不管是哪类语言，如果很久不用，总归是容易忘记，以此鞭策自己吧，学以致用，为用而学。[玫瑰]
适合SQL小白入门级阅读，关键还得是编程逻辑要清晰，同时要注意一些基础语句的坑，要不查漏很麻烦。。。
前面部分很简易易懂，后面需要权限的那些advanced的相对难。但看完感觉需要practice才会记住这些语言，或者用到的时候再查询。总体来说很不错
在b站看了黑马程序员的mysql视频，再用这本书复习一遍所有知识点，感觉还是培训班的教学视频讲的更仔细更全，还有实际操作掩饰，这本书讲的内容太少了，对于比如视图，索引，储存过程等高阶知识通过这边书了解的太少，储存过程这本书根本看不懂。所以想好好学sql的话，建议去看b站的视频，和多手动敲代码练习。
M 只能说我同事对我很好，他教会了我很多，对这个东西，因为我既不是计科出身，也没有学过类似的东西，但是我还是花了两天时间，还是完成了一个小项目，非常感谢，如果有人能在旁边教你这些东西，会学的很快，特别是对一些具体的操作
工作中没有用过数据库，想了解一下这方面的知识。这本书清晰地介绍了关系型数据库的常用操作的SQL语言，以一个供应商的例子贯穿，原理性的东西讲解较少，入门不错。
数据开发书籍
不得不说，计算机方面的书籍老外写得确实好，由浅入深，层层递进，还包含了一些调试中容易遇到的坑。希望国内也会有更多优秀书籍出版～
SQL优化建议 1.数据去重使用group by代替distinct, 禁止使用count(distinet); 2. 分区表使用时where条件必须加上分区过滤如: d为每日全量表，day取最新分区 di每日增量表，限定日期，如day betreen 20190415 and 201904161 3.多表关联，先子查询过滤后关联 4.慎用in not in,连续性值过滤用between and代替，如: age in (1.2.3.4) 修改为:age between 1 and 4 5子查询禁止使用*号，只把需要的字段列出来 6. 表关联时一定要加上on条件，避免笛卡尔系数 7.表关联时关联条件一定要在子查询里过滤掉null值，避免数据倾斜; 8.不要对一个表进行重要处理，多使用临时表，尽里做到一次处理多次使用 9慎用order by 大数据排序会很慢
好在对ACCESS还有点印象，大学里学数据库只是会操作，至于为什么这么操作也没深究。入门级读物，边上机边读可以加强记忆。
很棒的数据库操作书籍。内容简洁明了，不冗余，主要知识点都涵盖进来了，基本操作也都有举例，入门很不错了。是讲的SQL的通识知识，可能具体到某个数据库还得另外学习。适合深挖某个数据库之前打底子用。