Transcript Working with MS SQL Server

Working with MS SQL Server II
1
The sqlcmd Utility

Command line utility for MS SQL Server
databases.



Available on classroom and lab PCs.
Free download from Microsoft


Previous version called osql
google sqlcmd download
In Windows command window



Connect to a Database Server
Enter SQL commands on the command line.
Results output to the console.
2
Getting and Installing sqlcmd

Included with Management Studio Express.


Documentation available in SQL Server
2005 Books Online


Free download from Microsoft,
Free download from Microsoft.
In Visual Studio 2008 Help

search for sqlcmd.
3
Visual Studio Search Result
The SQL Language


The following slides demonstrate SQL
commands using the sqlcmd program.
Remember SQL is used everywhere we
interact with a database server:


Command line
Database management programs


Including Visual Studio Server Explorer
Our own programs (in the form of strings)
5
Using sqlcmd


In order to use sqlcmd with the SQL Server on
scorpius you will need a VPN connection.
See:
http://www.cse.usf.edu/~turnerr/Software_Systems_Development/
073a_Working_with_MS_SQL_Server.pdf

The following slides use the addresses table that we
created and populated in a previous class.

Slides 9 - 37 of same presentation.
Using sqlcmd
The Server
My Username
Password entered here
Execute commands in buffer
7
SQL Command Line Example

View a subset of the columns.
8
Retrieve Specific Entries
Find all addresses with zip code 33707
9
Wildcard Selection
Find all addresses with last name beginning with S
10
Wildcard Selection
All addresses with last name containing an s
11
Case Sensitivity




Typically SQL commands and keywords are not case sensitive.
Data is case sensitive
 Characters are stored as upper case or lower case.
Sorting order is a mode
 Default is not case sensitive
 Search comparisons same as sorting order.
http://sqlserver2000.databases.aspfaq.com/how-can-i-make-mysql-queries-case-sensitive.html
12
Case Sensitivity
13
Case Sensitive Select
14
Case Sensitivity




You can make the default for a column
case sensitive.
See:
http://social.msdn.microsoft.com/Forums/enUS/sqlsetupandupgrade/thread/850deb43-18ff-492b-bec0-b2b9a7ad76ac
Or google mssql collate
15
Sorting the Output
Zip_Code is a string. So sort is lexicographic, not numeric.
16
Counting Rows
How many addresses are there with zip code 33707?
17
Updating Records
18
Updating Records
19
Updating Multiple Fields
20
Deleting a Record
21
SQL Commands in Visual Studio


We can also use Visual Studio to issue
arbitrary SQL commands to the server.
On the Data menu select New Query
22
SQL Commands in Visual Studio
23
SQL Commands in Visual Studio
Click here
24
SQL Commands in Visual Studio
Enter SQL command here.
25
SQL Commands in Visual Studio
Click here to execute the command.
26
SQL Commands in Visual Studio
Results appear below the Query window.
27
Learning SQL

Many books available

One that I recommend:

Teach Yourself SQL in 24 Hours (Third Edition)

Ryan Stephens and Ron Plew, SAMS, 2003

Lots of information on the web

A sampling:



http://en.wikipedia.org/wiki/SQL
http://www.w3schools.com/sql/default.asp
http://db.grussell.org/ch3.html
28