Lesson 1 Intro to SQL Simple SQL Query
Instructor Cici Graduated from Cornell University majored in Operations Research and Information Technology, with a minor in business. She is currently working as a business associate at KPMG focusing on client-facing tax applications. She provided mock interviews, resume critiques, and cover letter critiques to assist over 200 students over the past five years.
What is SQL? SQL is a standard language for accessing and manipulating databases. SQL stands for Structured Query Language SQL lets you access and manipulate databases SQL is an ANSI (American National Standards Institute) standard Xi Rao 070116
What Can SQL do? SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views Xi Rao 070116
SQL? 数据库 Database 数据库管理软件 Database Management System 结构化查询语言 Structured Query Language 分析问题 解决问题 SQL 的优点 : 所有 DBMS 都支持 SQL 语句简单, 却可以进行复杂高级的操作可以处理大量的数据 ( 区别于 Excel 中的 VBA)
一个简单的例子 数据库 : 我们需要解决的问题 : 找出既会 VB 又会 PHP 的人 答案 ( 使用 SQL 语言 ): SELECT 员工 FROM [Table] WHERE 员工 IN (SELECT 员工 FROM [Table] WHERE 技能 = VB AND 技能 = PHP ) ID 员工技能 1 1 VB 2 1 PHP 3 1 ASP 4 2 PHP 5 3 ASP 6 4 VB 7 4 ASP
主键和外键 :Keys and Foreign Keys Company CName StockPrice Country Key GizmoWorks 25 USA Canon 65 Japan Hitachi 15 Japan Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi Foreign key
这节课我们只看一个表 : Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi
SELECT-FROM-WHERE Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT * FROM WHERE category= Gadgets 特别提示 : 注意这里一定要有引号 ( 单双引号均可 ), G 一定要大写 Powergizmo $29.99 Gadgets GizmoWorks
SELECT-FROM-WHERE Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT PName, Price, Manufacturer FROM WHERE Price > 100 特别提示 : 比较数字大小的时候不用加引号, 或者加单引号也可以, 双引号不可以 PName Price Manufacturer SingleTouch $149.99 Canon MultiTouch $203.99 Hitachi
SELECT-FROM-WHERE Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT PName, Manufacturer FROM WHERE Price > 100 PName SingleTouch MultiTouch Manufacturer Canon Hitachi
去掉重复值 :DISTINCT SELECT FROM DISTINCT category Category Gadgets Photography Household Compare to: SELECT FROM category Category Gadgets Gadgets Photography Household
取头几行值 :Limit SELECT FROM Limit 3 DISTINCT category Category Gadgets Photography Household Compare to: SELECT FROM Limit 3 category Category Gadgets Gadgets Photography
大小写问题 : Case insensitive: Same: SELECT Select select Same: product Same: Seattle Seattle Different: Seattle seattle 建议 : 初学时, 不管文本还是数字, 全部加上单引号肯定不会错
简单筛选 : 用在 WHERE 关键词后
高级筛选 :AND Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT FROM WHERE PName, Price, Manufacturer Price > 100 AND Manufacturer= Canon PName Price Manufacturer SingleTouch $149.99 Canon
高级筛选 :OR Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT FROM WHERE 特别提示 :AND 优先级高于 OR PName, Price, Manufacturer Price > 100 OR Manufacturer= GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi
高级筛选 :IN Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT FROM WHERE PName, Price, Manufacturer Manufacturer IN ( GizmoWorks, Canon ) Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon
高级筛选 :NOT Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT FROM WHERE PName, Price, Manufacturer NOT Manufacturer = Hitachi Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon
通配符 :LIKE Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT * FROM s WHERE PName LIKE %gizmo% 特别提示 : 与 % 能匹配 0 个字符不一样, _ 总是匹配一个字符, 不能多也不能少 Powergizmo $29.99 Gadgets GizmoWorks
使用 IN 和 NOT 的优点? 使用 LIKE 的缺点?
排序 :ORDER BY ( 永远写在最后, 除非有 limit 行数 ) Powergizmo $59.99 Gadgets GizmoWorks SingleTouch $59.99 Gadgets GizmoWorks MultiTouch $79.99 Gadgets GizmoWorks SELECT pname, price, manufacturer FROM WHERE category= Gadgets AND price > 50 ORDER BY price, pname Ties are broken by the second attribute on the ORDER BY list, etc. 特别提示 :Ordering is ascending( 升序 ), unless you specify the DESC keyword.
练习 : Table Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT DISTINCT category FROM ORDER BY category? SELECT Category FROM ORDER BY PName? SELECT DISTINCT category FROM ORDER BY PName?
作业 : 按照以下几页 PPT 的步骤安装 SQLite ( 数据库软件 ), SQLiteStudio (SQLite 对应的实用程序, 用来直观看 SQL 语言及其结果 ) 以及 Sample Database 熟悉每个表格的内容 (Primary key? Foreign key?) 了解每个表格的意义 练习本次课学过的语句, 例如右图 注 1:SQLite 只是众多数据库软件 (DBMS) 之一, 这里详细指导该软件的安装步骤仅是为了提供一种体验和实践 SQL 的方式 SQLite 软件本身的操作不会作为课程重点 不同数据库软件调取数据方式大同小异 ( 比如有的软件语句结尾必须加上分号 ) 我们的主要目标是掌握 SQL 语句, 因为 SQL 语句是标准化的, 适用于所有数据库软件, 如 MySQL, MS SQL Server, Oracle 等等 每个数据库软件都有其对应的实用程序 ( 例如 : SQLite SQLite Server; SQL Server- SQL Server Management Studio) 注 2:SQL 关键词不区分大小写 (SELECT=select), 但是引号里的文本要区分大小写 Rock 不等于 rock, 重复的空格也会被忽略
STEP 1: Download and Unzip SQLite and SQLiteStudio http://www.sqlite.org/download.html http://sqlitestudio.pl/?act=download
STEP 2: Click the SQLiteStudio Folder and open the highlighted exe file.
You should see:
STEP 3: download the sample database: http://chinookdatabase.codeplex.com/releases/view/55681
STEP 4: Open this database in the SQLitStudio:
STEP 5: Edit/Run query for this database Select Tools Open SQL Editor Write your query Click the triangle button to run Your query result will show in the bottom part: