本系列出自《深入浅出SQL》,全文以问答形式展开,是我的个人学习笔记。
A:SQL语言要求所有的表都放在数据库中,这当然有它的理由。SQL能控制多位用户同时访问表的行为,能够授予或撤销对整个数据库的访问权,这有时比控制每张表的权限要简单的多。
A:有些系统确实要求某些关键字采用大写形式,但SQL本身不区分大小写。也就是说,命令不大小写也可以,但命令大小写是良好的SQL编程惯例。
大写让我们很容易分辨命令与数据库名称。
A:创建具有描述性的名称通常有不错的效果。有时候要多用几个单词来命名。所有名称都不能包含空格,所以使用下划线能够让你创建更具描述性的名称。
命名时最好避免首字母大写,因为SQL不区分大小写,极可能会搞错数据库。
A:因为这样很浪费空间。VARCHAR或CHAR只会占用特定空间,不会多于256个字符。但BLOB需要很大的存储空间。
另外,有些重要的字符串运算无法操作BLOB类型的数据,只能用于VARCHAR或CHAR。
A:当然不是!! 它从来就不等于0。而且它也不等于另一个NULL,事实上,两个NULL根本不能放在一起比较。值可以是NULL,但是它不会等于NULL,因为NULL代表未定义的值!
A:从web浏览器剪切过来的查询有时包含了外观像空格,但在SQL里有其他含义的隐形字符。你可以把查询粘贴到文本编辑器中,如此一来,就可以仔细寻找并移除这些小麻烦。
A:建议使用norepad(PC)或TextEdit(Mac)的纯文本编辑模式。
A:其实没有优劣之分,但是斜杠对我们肉眼有利。
A:如果想在AND或OR子句中使用NOT,请直接将它放在关键字后面,如下:
SELECT * FROM asd WHERE NOT main = ‘aaa’ AND NOT mian = ‘bbb’;
IS NULL
A:因为表中的记录排序方式没有一定的规则,而且我们很快又要调整查询结果的记录,所以实在无法保证表的最后一条记录是最后插入的记录。除非我们记住哪份数据先进来。
A:但表设计的越好,整体所需的更新操作就会越少。良好的表设计能让我们从专心于表的内容中解放出来。
A:LIKE没有问题,但可能很难运用到你的查询中,而且你会冒着找出你不需要的一大堆数据的风险。如果你的列包含复杂信息的话,LIKE搜索精确数据的能力还不够。
A:查询越简短越好。随着数据的增长,还有对新表的添加,你的查询就会变得越来越复杂。如果现在就练习设计最简单的查询,以后你会感谢现在的及早训练。
A:1、挑出事物,挑出你希望表描述的某样事物。
2、列一份关于那样事物的信息列表,这些信息都是使用表时的必要信息。
3、使用信息列表,把关于那样事物的综合信息拆分成小块信息,以便用于组织表。
A:不是哦,让数据具有原子性,表示把数据分割成创建有效率的表所需的最小片段。
别把数据切割的超出必要。如果不需要增加额外的列,就别因为可以增加而增加。
A:原子性有助于确保表内容的准确性。
原子性也可以使查询更加有效率。因为查询会因原子性而更容易设计,而且所需时间也更短,因此在面对大量数据时有加分效果。
A:1、主键用于独一无二地识别出每条记录。
2、主键不可以为NULL。
3、插入新纪录时必须指定主键值。
4、主键必须简洁。
5、主键不可以被修改。
ALTER TABLE MODIFY COLUMN proj_desc AFTER con_name;
这样做可以吗?创建表后你就无法真正的改变列的顺序了。最多只能在指定位置添加新列,然后删除旧列,但是这样会失去旧列中的所有数据。
A:可以,而且很简单。
ALTER TABLE your_table DROP PRIMARY KEY,ADD PRIMARY KEY(XXX);
A:你可以把它添加到没有自动递增功能的列中,如下所示:
ALTER TABLE your_table CHANGE yoour_id your_id INT(11) NOT NIULL AUTO_INCREMENT;
而且可以这样就将它删除:ALTER TABLE your_table CHANGE your_id your_id INT(11) NOT NULL;
有一点要记住:每个表中只有一列可以加上AUTO_INCREMENT,该列必须为整形而且不能包含NULL。
ALTER TABLE my_contacts ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY(contact_id);
ALTER TABLE my_contacts ADD COLUMN phone VARCHAR(10) AFTER list_name;
CHANGE --可同时改变现有列的名称和数据类型 MODIFY --修改现有列的数据类型或数据 ADD --在当前表中添加一列,可自选类型 DROP --从当前表中删除某列
ALTER TABLE project_list CHANGE COLUMN number proj_id INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(proj_id); --将原名为“name”的列的名称和类型修改 --如果把数据改成另一种类型,你可能会丢失数据
ALTER TABLE project_list CHANGE COLUMN descriptionofproj proj_desc VARCHAR(10), CHANGE COLUMN contractoronjob con_name VARCHAR(30),
ALTER TABLE project_list MODIFY COLUMN proj_desc VARCHAR(120);
ALTER TABLE project_list DROP COLUMN start_date;
SELECT RIGHT(lie,2) FROM my_contacts; --从lie列中读取两个字符
SELECT SUBSTRING_INDEX(lie,',',1) FROM my_contacts; --截取部分字符串,第三个参数就是寻找第一个逗号,用于截取第一个逗号之前的所有字符。
SELECT UPPER('usa'); --把整整组字符串改大写 SELECT LOWER('USA'); --改小写
SELECT LTRIM(' dogfood '); --清除左侧空格 SELECT RTRIM(' catfood ');
SELECT LENGTH('San Antonio,TX'); --返回字符串中的字符数量
A:看你咯,无所谓。
在你想更新的列里面不会发生任何改变。
A:可以加上WHERE,可以在END后加上WHERE子句。这样,CASE就只会套用在符合WHERE子句的列上。
CASE表达式可以搭配UPDATE以外的语句吗?
A:why not?
A:好问题。NULL其实不会有影响,因为NULL代表此处无值,而不是此值为0.
看图:
现在怎么办?是像这样吗?
:
这样要执行n次啊。。。
那有没有更好的办法,其实在主语言中,这不过就是个分支语句的事情嘛,奈何SQL语言我不熟啊。。。
没事,一直以来不懂,从今以后懂了:
UPDATE my_table SET new_column = CASE WHEN column1 = somevalue1 THEN newvalue1 WHEN column2 = somevalue2 THEN newvalue2 ELSE newvalue3 END;
越靠前的列权重越高,拥有对后面列的一票否决权。
SELECT first_name,SUM(sales) FROM cookie_sales GROUP BY first_name ORDER BY SUM(sales) DESC ;
SELECT first_name,AVG(sales) FROM cookie_sales GROUP BY first_name ORDER BY AVG(sales) DESC ;
SELECT first_name,MAX(sales) FROM cookie_sales GROUP BY first_name ;
SELECT first_name,MIN(sales) FROM cookie_sales GROUP BY first_name ;
SELECT COUNT(sale_date) FROM cookie_sales ;
SELECT DISTINCT sale_date FROM cookie_sales ORDER BY sale_date ;
SELECT COUNT(DISTINCT sale_date) FROM cookie_sales ;
SELECT first_name,SUN(sales) FROM cookie_sales GROUP BY first_name ORDER BY SUN(sales) DESC LIMIT 2 OFFSET 4 --跳过两条,查询两条记录 --也可以这样写:LIMIT 4,2 ;
A:外键为NULL,表示在父表中没有相符的主键。但我们可以确认外键包含有意义、已经存储在父表中的值,请通过约束实现。
A:其实可以,但创建成外键约束后,就只能插入已经存在于父表中的值,有助于加强两张表间的连接。
A:外键约束能确保引用完整性(换句话说,如果表中的某行有外键,约束能确保该行通过外键与另一张表中的某一行一一对应)。如果我们试着删除主键表中的行或者是改变主键值,而这个主键是其他表的外键约束时,你就会收到错误警告。
A:还是可以的,先移除外键行即可。
A:不然呢?
创建数据库的视觉解析图,在设计查询时有助于理解数据相连的方式,但模式也能以文字形式表达,看个人。
创建一张表并加上可作为外键的列虽然很简单,但除非你利用CREATE或ALTER语句来指定外键,否则都不算是真的外键。创建在结构内的外键被称为约束。
插入外键列的值必须已经存在与父表的来源中,这是引用完整性。
创建外键作为表的约束提供了明确的优势,如果违反了规则,约束会阻止我们破坏表。
外键不一定要是父表的主键,但是要具有唯一性。
在模式图中,一对一关系的连接线是单纯的实线,表示连接一件事物与另一件事物。
事实上,很少。
A表的某一条记录可以对应到B表的多条记录,但B表中的一条记录只能对应A表中的某一条记录。
连接线应该带有黑色箭头来表示一对多的连接关系。
司空见惯了,中介者模式(调停者模式)该上场了。
组合键就是有多个数据列构成的主键。
当某列的数据必须随着另一列的数据改变而改变时,表示第一列函数依赖与第二列。
部分函数依赖:非主键的列依赖与组合键的某个部分(但不是完全依赖与组合主键)。
传递函数依赖:如果改变任何非键列可能造成其他列的改变,即为传递依赖。
A:知道交叉联接的存在,有助于我们找出修正联接的正确方式。还有,交叉联接有时可用于RDBMS软件及其配置的运行速度。运行交叉联接所需的时间可以轻易的检测与比较出速度慢的查询。
A:交叉联接属于内联接的一种。内联接就是通过查询中的条件移除了某些结果的交叉联接。
A:可以,后续章节再说,有点饿了。
A:是的。
A:莫急。
CREATE TABLE profession( id INT(11) NOT NULL AUTP+INCREMENT PRIMARY KEY, profession varchar(20) ); INSERT INTO profession (profession) SELECT profession FROM my_contacts GROUP BY profession ORDER BY profession;
CREATE TABLE profession AS SELECT profession FROM my_contacts GROUP BY profession ORDER BY profession; ALTER TABLE profession ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY(id);
CREATE TABLE profession( id INT(11) NOT NULL AUTP+INCREMENT PRIMARY KEY, profession varchar(20) ) AS SELECT profession FROM my_contacts GROUP BY profession ORDER BY profession;
看上面语句,AS能把SELECT的查询结果填入表中。
短短一个查询语句,就出现了五次“profession”,这五次profession效果各有不同,我们容易弄晕,但是SQL能够很轻易的分辨。
为了能让我们容易分辨,SQL推出了假名功能。
创建别名真的很简单,在查询软件中首次使用原始列名的地方后接一个AS并设定要采用的别名,告诉软件现在开始要以另一个名称引用my_contacs表的profession列,这样可以让查询更容易被我们理解。
你会需要!
接下来要开始对表进行联结了,嘿嘿,睁大眼睛吧。
创建表的别名的方式和创建列的别名的方式几乎一样。在查询中首次出现表名的地方后接AS并设定别名。
当然,你甚至可以连AS也省了。
假设你有一个存储男孩姓名的表以及一个记录男孩们都有哪些玩具的表,现在我们要试着找出每个男孩拥有的玩具。
SELECT t.toy,b.boy From toys t CROSS JOIN boys AS b ;
CROSS JOIN返回两张表的每一行相乘的结果。
INNER JOIN利用条件判断中的比较运算符结合两张表的记录。只有联接记录符合记录条件时才会返回列。
SELECT somecolumns FROM table1 INNER JOIN table2 ON somecondition; --条件式里课采用任何一个比较运算符,也可以改用WHERE
示例:
SELECT mc.last_name,mc.first_name,p.profession FROM my_contacts AS mc INNER JOIN profession AS p ON mc.prof_id = p.prof_id;
属于内联接的一种。
自然联接只有在联接的列在两张表中的名称相同时才会用。
SELECT boys.boy,toys.toy FROM boys NATURAL JOIN toys;
在单一查询不够用的时候,请使用子查询。
子查询只不过是查询里的查询。
SELECT some_column,anther_column FROM table WHERE column = (SELECT column FROM table);
因为查询里使用了 = 运算符,所以子查询里只会返回单一值,特定行和列的交叉点,这一个值将是WHERE子句中比对数据列的条件。
SELECT zip_code FROM zip_code WHERE city = (SELECT zip_code From zip_code WHERE city = 'Memphis' AND state ='TN' ) ;
如果子查询可以独立运行且不会引用外层查询的任何结果,即称为外层查询。
上面都是
有时候最好创建测试数据库来尝试各种查询方式,比较查询运行时间。
联接比子查询更有效率。
关联子查询是内层查询的解析需要依赖于外层查询的结果。
关联子查询的常见用法是找出所有外层查询结果里不存在于关联表里的数据。
SELECT mc.first_name firstname,mc.last_name lastname,mc.email email FROM my_contacts mc WHERE NOT EXISTS( SELECT * FROM job_cerrent jc WHERE mc.contact_id = jc.contact_id ) ;
A:大多数情况下,内层查询只能返回单一值,也就是一列里的一行。而后,外层查询才能利用这个值与列中其他值进行比较。
一般而言,子查询必须返回一个值,使用IN是例外情况。
A:of couse.
A:不然呢?
LEFT OUTER JOIN 会匹配左表中的每一行及右表中符合条件的行。
当左表与右表具有一对多关系时,左外联接特别有用。
理解外联接的最大秘密在于知道表在左边还是右边,在LEFT OUTER JOIN中,出现在FROM后,联接前的表称为左表,而出现在联接后的表称为右表。
SELECT g.girl,t.toy FROM girls g --g是左表 LEFT OUTER JOIN toys t --t是右表 ON g.toy_id = t.toy_id;
内外联接有什么差别?外联接一定会提供数据行,无论该行能否在另一个表中找出相匹配的行。
左外联接的结果为NULL表示右表没有找到与左表相符的记录。
具体流程概览(图有点小瑕疵):
有外联接与左外联接一样,除了它是用右表与左表比对。
同一个表可以同时作为外联接的左右表。虽然听起来很奇怪,不过却很好用。
来一题看看;
SELECT c1.name,c2.name AS boss FROM clown_info1 c1 INNER JOIN clown_info2 c2 ON c1.bossid = c2.id ;
自联接能够把一张表当成两张完全相同的表来进行查询。
还有一种取得多张表的查询结果的方式:UNION联合。
UNION根据我们在SELECT中指定的列,把两张表或更多张表的查询结果合并至一个表中。
SELECT title FROM job_current UNION SELECT title FROM job_desired UNION SELECT title FROM job_listings;
SELECT title FROM job_current UNION SELECT title FROM job_desired UNION SELECT title FROM job_listings ORDER BY title;
结果集:
SELECT title FROM job_current UNION ALL SELECT title FROM job_desired UNION ALL SELECT title FROM job_listings ORDER BY title;
结果集:
联合规则说:选取的列必须可以互相转换。
A:一般来说,固定使用一种联接的习惯会让事情更简单,这样不容易搞混。
CREATE TABLE piggy_bank( id INT NOT NULL PRIMARY KEY, coin CHAR(1) CHECK (coin IN ('P','N','D','Q')) );
CHECK(检查)用于限定允许插入某个列的值。它与WHERE子句都使用相同的条件表达式。
如果插入的值无法通过CHECk条件,则出现错误信息。
ALTER TABLE my_contacts ADD CONSTRAINT CHECk gender IN ('M','F');
CREATE VIEW web_designers AS SELECT mc.first_name,mc.last_name.mc.phone,mc.email FROM my_contacts mc NATURAL JOIN job_desired jd WHERE jd.title = 'Web Designer';
就像普通表那样。
SELECT * FROM web_designers;
SELECT* FROM( SELECT mc.first_name,mc.last_name,mc.phone,mc.email FROM my_contacts mc NATURAL JOIN job_desired jd WHERE jd.title = 'Web Designer' AS web_designers; )
最后为什么要用个AS呢?因为当SELECT语句的结果是一个虚表时,若没有别名,SQL就无法取得其中的表。
DROP VIEW pb_dimes;
事务是一群可以完成一组工作的SQL语句。
START TRANSACTION; --持续追踪后续所有SQL语句 COMMIT; --提交所有程序代码造成的改变 ROLLBACK; --回滚,回到事务开始前
A:SHOW TABLES;
A:看情况。有的RDBMS允许使用视图,但不返回数据。一般而言,最好先去除视图,然后再卸载它所依据的表。