Introduction to SQL PDF

IM 1 - Lesson 5 5 Introduction to SQL What languages are used to interact with databases? SQL (Structured Query Language) is by far the most common language used to interact with relational databases. Originally developed for use with IBM's DB2, the standard...

IM 1 - Lesson 5 5 Introduction to SQL What languages are used to interact with databases? SQL (Structured Query Language) is by far the most common language used to interact with relational databases. Originally developed for use with IBM's DB2, the standard -- often pronounced "sequel" -- is promoted in various formats by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO).  Structured Query Language SQL is a language for retrieving information out of relational databases such as Oracle, Informix, IBM DB2, SQL Server, Access and MySQL. Example: Select statement segment. select First_Name, Last_Name from Customer_Shipping where Zip = ‘4500'; SELECT Statement the SELECT clause specifies the columns of the result the FROM clause specifies the tables to be scanned in the query the WHERE clause specifies the condition on the columns of the tables in the FROM clause SELECT Statement SELECT A1, A2,... An FROM T1, T2,... Tm WHERE C1, C2, …Cx A: Attribute T: Table C: Column Capabilities of SQL statements Projection: Limits the table column (attribute) of database Selection: Limits the table row (tuple) of database. Join: Combines one or more tables on the database. Capabilities of SQL SELECT Statements Projection Selection Table 1 Table 1 Join Table 1 Table 2 DBMS Here is the format of the SELECT statement: SELECT [ALL | DISTINCT] column1 [, column2] FROM table1 [, table2] [WHERE "conditions"] [GROUP BY "column-list"] [HAVING "conditions] [ORDER BY "column-list" [ASC | DESC]] SQL Statements The SELECT statement is used to query the database and retrieve selected data that match the criteria that you specify. SQL Statements The DML( Data Manipulation language) statement is used to manipulate database by inserting, updating and deleting records from the tables. SQL Statements The DDL (Data Definition Language) statement is used to define a database by creating, altering, renaming truncating and dropping table. It is also use in enforcing constraints such as primary, null, unique, check and foreign keys. SQL Statements The TCL ( Transaction Control Language) statement is used for controlling database transaction such committing, saving and to rollback a series of SQL statements. SQL Statements The UCL (User Control Language) statement is used to control database users such creating, dropping users and granting, revoking user privileges.  MySQL Overview MySQL is a very popular, open source database. Officially pronounced “my Ess Que Ell” (not my sequel). Handles very large databases; very fast performance. Why are we using MySQL? Free (much cheaper than Oracle!) Each student can install MySQL locally. Easy to use Shell for creating tables, querying tables, etc. Easy to use with Java JDBC Connecting and Disconnecting  Connecting to MySQL MySQL provides an interactive shell for creating tables, inserting data, etc. On UNIX, you connect to MySQL via the mysql command. To get help: shell> mysql –-help To connect: shell> mysql –u user –p On Windows, just go to c:\mysql\bin, and type: mysql  Sample Session For example: shell> mysql -u cerami -p Enter password: ***** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 241 to server version: 3.23.49 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> To exit the MySQL Shell, just type QUIT: mysql> QUIT Basic MySQL Queries  Basic Queries Once logged in, you can try some simple queries. For example: mysql> SELECT VERSION(), CURRENT_DATE; +-----------+--------------+ | VERSION() | CURRENT_DATE | +-----------+--------------+ | 3.23.49 | 2002-05-26 | +-----------+--------------+ 1 row in set (0.00 sec) Note that most MySQL commands end with a semicolon (;) MySQL returns the total number of rows found, and the total time to execute the query.  Basic Queries Keywords may be entered in any lettercase. The following queries are equivalent: mysql> SELECT VERSION(), CURRENT_DATE; mysql> select version(), current_date; mysql> SeLeCt vErSiOn(), current_DATE;  Basic Queries Here's another query. It demonstrates that you can use mysql as a simple calculator: mysql> SELECT SIN(PI()/4), (4+1)*5; +-------------+---------+ | SIN(PI()/4) | (4+1)*5 | +-------------+---------+ | 0.707107 | 25 | +-------------+---------+  Basic Queries You can also enter multiple statements on a single line. Just end each one with a semicolon: mysql> SELECT VERSION(); SELECT NOW(); +--------------+ | VERSION() | +--------------+ | 3.22.20a-log | +--------------+ +---------------------+ | NOW() | +---------------------+ | 2004 00:15:33 | +---------------------+  Multi-Line Commands mysql determines where your statement ends by looking for the terminating semicolon, not by looking for the end of the input line. Here's a simple multiple-line statement: mysql> SELECT -> USER() -> , -> CURRENT_DATE; +--------------------+--------------+ | USER() | CURRENT_DATE | +--------------------+--------------+ | joesmith@localhost | 1999-03-18 | +--------------------+--------------+  Canceling a Command If you decide you don't want to execute a command that you are in the process of entering, cancel it by typing \c mysql> SELECT -> USER() -> \c mysql> Creating Tables  Using a Database To get started on your own database, first check which databases currently exist. Use the SHOW statement to find out which databases currently exist on the server: mysql> show databases; +----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.01 sec)  Using a Database To create a new database, issue the “create database” command: mysql> create database menagerie; To the select a database, issue the “use” command: mysql> use menagerie;  Creating a Table Once you have selected a database, you can view all database tables: mysql> show tables; Empty set (0.02 sec) An empty set indicates that I have not created any tables yet.  Creating a Table Let’s create a table for storing pets. Table: pets name: VARCHAR(20) owner: VARCHAR(20) species: VARCHAR(20) sex: CHAR(1) VARCHAR is birth: DATE usually used death: DATE to store string data.  Creating a Table To create a table, use the CREATE TABLE command: mysql> CREATE TABLE pet ( -> name VARCHAR(20), -> owner VARCHAR(20), -> species VARCHAR(20), -> sex CHAR(1), -> birth DATE, death DATE); Query OK, 0 rows affected (0.04 sec)  Showing Tables To verify that the table has been created: mysql> show tables; +------------------+ | Tables_in_test | +------------------+ | pet | +------------------+ 1 row in set (0.01 sec)  Describing Tables To view a table structure, use the DESCRIBE command: mysql> describe pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 6 rows in set (0.02 sec)  MySQL Data Types In MySQL there are three main data types: String Numeric Date and time  String Data Types Data type Description CHAR(size) A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters - can be from 0 to 255. Default is 1 VARCHAR(size) A VARIABLE length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum column length in characters - can be from 0 to 65535 BINARY(size) Equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1 VARBINARY(size) Equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes. TINYBLOB For BLOBs (Binary Large OBjects). Max length: 255 bytes TINYTEXT Holds a string with a maximum length of 255 characters  String Data Types TEXT(size) Holds a string with a maximum length of 65,535 bytes BLOB(size) For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data MEDIUMTEXT Holds a string with a maximum length of 16,777,215 characters MEDIUMBLOB For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data LONGTEXT Holds a string with a maximum length of 4,294,967,295 characters LONGBLOB For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data ENUM(val1, val2, val3,...) A string object that can have only one value, chosen from a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. The values are sorted in the order you enter them SET(val1, val2, val3,...) A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list  Numeric Data Types Data type Description BIT(size) A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. The default value for size is 1. TINYINT(size) A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. The size parameter specifies the maximum display width (which is 255) BOOL Zero is considered as false, nonzero values are considered as true. BOOLEAN Equal to BOOL SMALLINT(size) A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535. The size parameter specifies the maximum display width (which is 255) MEDIUMINT(size) A medium integer. Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215. The size parameter specifies the maximum display width (which is 255) INT(size) A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255) INTEGER(size) Equal to INT(size) BIGINT(size) Numeric Data Types A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255) FLOAT(size, d) A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. This syntax is deprecated in MySQL 8.0.17, and it will be removed in future MySQL versions FLOAT(p) A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE() DOUBLE(size, d) A normal-size floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter DOUBLE PRECISION(size, d) DECIMAL(size, d) An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0. DEC(size, d) Equal to DECIMAL(size,d)  Date and Time Data Types Data type Description DATE A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31' DATETIME(fsp) A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time TIMESTAMP(fsp) A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition TIME(fsp) A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59' YEAR A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000. MySQL 8.0 does not support year in two-digit format.  Deleting a Table To delete an entire table, use the DROP TABLE command: mysql> drop table pet; Query OK, 0 rows affected (0.02 sec) You need not to perform. Because the table pet will be deleted. Loading Data into a Table  Loading Data Use the INSERT statement to enter data into a table. For example: INSERT INTO pet VALUES ('Fluffy','Harold','cat','f', '1999-02-04',NULL); The next slide shows a full set of sample data.  More data… name owner species sex birth death Fluffy Harold cat f 1993-02-04 Claws Gwen cat m 1994-03-17 Buffy Harold dog f 1989-05-13 Fang Benny dog m 1990-08-27 Bowser Diane dog m 1995-07-29 1998-08-31 Chirpy Gwen bird f 1998-09-11 Whistler Gwen bird 1997-12-09 Slim Benny snake m 1996-04-29 43