Partial preview of the text
Download SQL Mastery: A Comprehensive Guide to Database Management and more Study notes Computer Science in PDF only on Docsity!
SQL Tutorial SQL is a standard language for accessing databases. Our SQL tutorial will teach you how to use SQL to access and manipulate data in: MySQL, SQL Server, Access, Oracle, Sybase, DB2, and other database systems. What is SQL? e SQL stands for Structured Query Language « SQL lets you access and manipulate databases e SQL is an ANSI (American National Standards Institute) standard 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 RDBMS RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows. DOUBLE(size,d) DECIMAL(size,d) A large number with a floating decimal point. The maximum number of digits may be specifiedin the size parameter. The maximum number of digits tothe right of the decimal pointis specified in the d parameter A DOUBLE stored as a string, allowing forafixed decimal point. The maximum numberof digits may be specified in the size parameter. The maximum number of digits to the right of the decimal pointis specified in the d parameter *The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number, Date types: Data type DATE() DATETIME() TIMESTAMP() TIME() Description A date. Format: YYYY-MM-DD Note: The supported range is from '1000-01-01' to '9999-12- at! *A date and time combination. Format: YYYY-MM-DD HH:MI:SS Note: The supported range is from '1000-01-01 00:00:00' to "9999-12-31 23:59:59" *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:MI:SS Note: The supported range is from ‘1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC A time. Format: HH:MI:SS Note: The supported range is from ‘-838:59:59' to '838:59:59' YEAR() A year in two-digit or four-digit format. Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069 *Even if DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT or UPDATE query, the TIMESTAMP automatically set itself to the current date and time. TIMESTAMP also accepts various formats, like YYYYMMDDHHMISS, YYMMDDHHMISS, YYYYMMDD, or YYMMDD. The following SQL statement selects all the records in the "Customers" table: Example SELECT * FROM Customers; In this tutorial we will teach you all about the different SQL statements. Keep in Mind That... « SQL keywords are NOT case sensitive: select is the same as SELECT In this tutorial we will write all SQL keywords in upper-case. Semicolon after SQL Statements? Some database systems require a semicolon at the end of each SQL statement. Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server. In this tutorial, we will use semicolon at the end of each SQL statement. Some of The Most Important SQL Commands « SELECT - extracts data from a database e UPDATE - updates data in a database « DELETE - deletes data from a database e INSERT INTO - inserts new data into a database « (CREATE DATABASE - creates a new database » ALTER DATABASE - modifies a database e CREATE TABLE - creates a new table « ALTER TABLE - modifies a table « DROP TABLE - deletes a table e CREATE INDEX - creates an index (search key) « DROP INDEX - deletes an index The SQL SELECT Statement The SELECT statement is used to select data from a database. The result is stored in a result table, called the result-set. SQL SELECT Syntax SELECT column_name,column_name FROM table_name; and SELECT * FROM table_name; SELECT Column Example The following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table: Example SELECT CustomerName,City FROM Customers; SELECT * Example The following SQL statement selects all the columns from the "Customers" table: Example SELECT * FROM Customers; 
