Writing Illiad SQL Queries
…or how I learned to stop
worrying and love the result set
Why Write SQL?
•
•
•
•
Need data in machine readable format
Need to manipulate data
Want automated or triggered queries
Want to build a user friendly, abstracted
front end to your data
• SQL is a standard
• It’s fun and will impress your friends at
parties!
Tools for writing SQL queries
• Query Analyzer (MS standard)
• Access (GUI, but adds nonsense to your
SQL)
• Toad (free version! www.toadsoft.com)
These programs allow you to write ad-hoc SQL queries.
Often, when you have a finished query, you will be
running it in a programming/scripting language outside
of them.
You use Enterprise Manager to administer the SQL
Server (add logins/users, administer rights, etc)
MS SQL Server Security Basics
• Two stages of authentication:
– Login: used to connect to SQL Server
– User: user account granted rights to
database objects (tables + other fun stuff)
• Users can be SQL Server Users OR
NT/Win2K Domain Users
– SQL Server Users: maintained in DB (live in
dbo.master)
– Domain Users: Users already authenticated
on your windows network
MS SQL Server Security Basics
Logins in Enterprise Manager
DB Users in Enterprise Manager
MS SQL Server Security Basics
• Choosing to use SQL Server Users
(login/pass) vs Windows Domain Users
depends on your needs, however
Domain users are more secure, create
accountability, and can be easier to
maintain.
MS SQL Server Security Basics
• Roles = fixed sets of rights that can be
assigned to Users at DB level. Common
ones:
– Public (equivalent to “everyone” group)
– db_owner = has full rights
– db_dataread = SELECT rights on all tables
– db_datawriter = modify rights on all tables
• In additon, DBAs will often have the
“sysadmin” role associated with their
login (gives full rights to SQL Server)
Diving into SQL
• SQL = Structured Query Language
– ANSI compliant standard
– More than just queries: can modify data
through the Data Manipulation Language
(DML) set:
•
•
•
•
SELECT (extract records from table)
INSERT INTO (insert new records into table)
UPDATE (update records in table)
DELETE (delete records from table)
SELECT Statement
• Basic SELECT statement syntax:
– SELECT field1, field2 FROM table_name
• Simple Illiad Queries:
SELECT * FROM UsersAll
SELECT LastName, FirstName, EMailAddress FROM UsersAll
* = wildcard for select all fields
- the second query specifies only three fields to
return
- query returns a result set, or table of data
WHERE Clause
• What if I want conditionality?
use the WHERE clause:
SELECT LastName, FirstName, Department
FROM UsersAll
WHERE Status = 'Faculty/Staff'
Returns all users specified as ‘Faculty/Staff’
Use single quotes (‘) to denote strings, do not
use for number values
WHERE Clause
• Other operators for WHERE clause
include <, >, <=, >= and LIKE (which
allows wildcards):
SELECT LastName, FirstName, Number
FROM UsersAll
WHERE Number LIKE ‘20000%’
• This query returns all users with a Number (barcode)
starting with 20000 (they have an OHSU library card)
• % = wildcard, use anywhere in string (can also use
more than one)
JOINing Tables
• JOINING tables (i.e.: one result set for
multiple tables)
– Done in WHERE clause, or in FROM clause
(more ANSI compliant)
– Links two or more tables utilizing a primary
key (a unique row in one table) that
correlates to one or more rows in another
table
• What follows are the table relationships
of the Illiad DB…
JOINing Tables
JOINing Tables
• Yeah, I know they are too small, you can
view the real deals at:
– http://www.atlassys.com/documentation/illiad/content/ILLi
adDatabaseDiagram.pdf
– http://www.atlassys.com/documentation/illiad/content/ILLi
adSSSDiagram.pdf
– http://www.atlassys.com/documentation/illiad/content/ILLi
adBillingDiagram.pdf
JOINing Tables
• JOIN example in WHERE:
SELECT *
FROM dbo.Transactions t, dbo.UsersAll u
WHERE t.Username = u.Username
• …in FROM:
SELECT *
FROM dbo.Transactions t INNER JOIN dbo.UsersAll u ON (t.Username =
u.UserName)
Notes:
- Either usage tells the DB where the join occurs – in
other words it creates a ‘virtual table’ for your
result set combining the two tables on this column
- Shortcut letter after table name makes for less
typing later!
JOINing Demo
Joining Demo:
SELECT u.Status, u.Department, t.TransactionDate, t.LoanTitle,
t.LoanPublisher, t.LoanDate, t.LoanEdition, t.ISSN,
t.LendingLibrary
FROM dbo.Transactions t INNER JOIN dbo.UsersAll u ON
(t.Username = u.UserName)
WHERE LOWER(t.TransactionStatus) = 'request finished'
AND LOWER(t.DocumentType) = 'loan'
AND LOWER(u.Status) IN ('faculty/staff', 'student',
'distanceeducation')
AND ((t.TransactionDate < '1/1/06') AND (t.transactionDate >=
'1/1/05'))
JOINing Demo
• Notes from demo query:
– JOIN creates a clean dataset as if the two
tables were one
– using LOWER function to ensure data
consistency
– using ANDs to logically specify multiple
conditions in my WHERE clause (you can use
ORs and NOTs too)
– using the IN keyword instead of millions of
ANDs where I have a list of values
ORDER BY
• I am anal and want my result set sorted!
– use the ORDER BY clause:
SELECT u.Status, u.Department, t.TransactionDate, t.LoanTitle,
t.LoanPublisher, t.LoanDate, t.LoanEdition, t.ISSN,
t.LendingLibrary
FROM dbo.Transactions t INNER JOIN dbo.UsersAll u ON
(t.Username = u.UserName)
WHERE LOWER(t.TransactionStatus) = 'request finished'
AND LOWER(t.DocumentType) = 'loan'
AND LOWER(u.Status) IN ('faculty/staff', 'student',
'distanceeducation')
AND ((t.TransactionDate < '1/1/06') AND (t.transactionDate >=
'1/1/05'))
ORDER BY t.TransactionDate, u.UserName
– Sorts by first field alphabetically, then
second if there are more than one value for
the first, etc…
Dupe Records
• Wait a minute buddy, I am getting duplicate records!
– The DISTINCT keyword tells SQL Server to only return unique
rows:
SELECT DISTINCT a.Type AS 'Type',
u.Username as 'UserName',
u.LastName as 'LastName',
u.FirstName as 'FirstName',
u.Status as 'Status',
u.EMailAddress as 'EmailAddress'
FROM UsersAll u
JOIN UserAccountsLink l ON (u.UserName = l.UserName)
JOIN UserAccounts a ON (l.InternalNo = a.InternalNo)
WHERE u.NVTGC = 'ILL' AND u.Cleared = 'Yes' And a.Active =
'Yes' AND a.Type like 'VISA%'
ORDER BY u.LastName
– So, in this instance, it says only give us rows in our
results set that are unique (go ahead, be elitist!)
Manipulating Data
• INSERT, UPDATE, DELETE statements
are used for manipulating data:
– INSERT to add new records to table(s)
– UPDATE to modify existing record(s)
– DELETE to remove records.
INSERT INTO
• INSERT example (I don’t have a tested
example for use with Illiad, but here is
the context):
INSERT INTO table_name (column1, column2,....)
VALUES (value1, value2,....)
- Here we are saying insert value1,
colum1, column2, … of table table_name
value2, …
into
UPDATE
• UPDATE example (setting user accounts to inactive
who have visa cards):
UPDATE UserAccounts
SET UserAccounts.Active = 'No'
WHERE UserAccounts.InternalNo IN
(SELECT a.InternalNo FROM UserAccounts a
JOIN UserAccountsLink l ON (l.InternalNo = a.InternalNo)
JOIN UsersAll u ON (u.UserName = l.Username)
WHERE u.NVTGC = 'ILL' And a.Active = 'Yes' AND a.Type like 'VISA%')
- The embedded SELECT query runs first returning a result set
of user accounts in our site (ILL), which are Active (Yes) and
the type of their account is VISA (wildcard to catch spaces and
other funky characters)
- …THEN the outside UPDATE query sets the field Active for
each of those user account to ‘No’
DELETE
• DELETE example (delete accounts for users
with visa cards):
DELETE FROM UserAccounts a2
WHERE a2.InternalNo IN
(SELECT a.InternalNo FROM UserAccounts a
JOIN UserAccountsLink l ON (l.InternalNo = a.InternalNo)
JOIN UsersAll u ON (u.UserName = l.Username)
WHERE u.NVTGC = 'ILL' And a.Active = 'Yes' AND a.Type like 'VISA%')
- Here we are again using an embedded SELECT
query to find all the user accounts with VISA cards
in our site (same as before)
- …then we use a simple delete query to remove
those records from the DB
What do I do with my SQL?
• Limitless options. Common ones:
– Create an Access view (easy) and show staff
how to run that view on their own (giving
you more YOU time so you can work on your
golf handicap)
– Create a stored procedure or user defined
function (UDF – more difficult). This
abstracts your query (and data) and makes
it simple to integrate into things like web
front ends (ASP, PHP, etc) or scripting
languages (visual basic, perl)
• Use those applications and scripts to give users the data
and reports they need from a GUI interface
A Few Resources Online
• Resources online (I didn’t make this stuff up, I stole
it):
Straight forward/no nonsense SQL reference (geared towards
web work):
http://www.w3schools.com/sql/default.asp
SQL Server security model and security best practices, tips:
http://vyaskn.tripod.com/sql_server_security_best_practices.htm
Should I use a view, a stored procedure, or a user-defined
function?
http://www.aspfaq.com/show.asp?id=2537
MSDN reference for SQL syntax, Server functions, T-SQL, etc…:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_tsqlcon_6lyk.asp
End
• Contact:
Nick Peterson
Oregon Health & Science University
Library Computer User Support Analyst
Email: [email protected]
• Questions? Insults?
Descargar

SQL Queries - Oregon Health & Science University