1 / 16

SQL Statements for Database Creation and Manipulation

Learn about SQL statements for creating databases, tables, and manipulating data using MySQL. This includes creating, altering, inserting, selecting, updating, and deleting data.

kalia-briggs
Télécharger la présentation

SQL Statements for Database Creation and Manipulation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL statements

  2. SQL statement types • Data definition • Data manipulation • Control

  3. Data definition statements CREATE DATABASE CREATE TABLE

  4. CREATE DB • CREATEDATABASE `db_name` [DEFAULT[CHARACTERSET utf8] [COLLATE utf8_lithuanian_ci] ] • http://dev.mysql.com/doc/refman/5.1/en/create-database.html • CREATE DATABASE `testing` • CREATE DATABASE `testing` DEFAULT CHARSET utf8 COLLATE utf8_lithuanian_ci

  5. CREATE TABLE • CREATE TABLE `Supplier` ( `No` CHAR(5) NOT NULL,  `Name` CHAR(20) NOT NULL,  `State` SMALLINT,  `City` CHAR(15),PRIMARY KEY (`Nr`) ) • Write a SQL statement to create a table for products

  6. ALTER TAB:E • ALTER TABLE name • ADD • CHANGE • DROP • ALTER TABLE`supplier`ADD`founded`DATE • ALTER TABLE`supplier`CHANGE `founded``founded`TIMESTAMP • ALTER TABLE `supplier` DROP `founded`

  7. DROP TABLE • DROP TABLE name • DROP TABLE `Detales`

  8. Data manipulation statements INSERT SELECT UPDATE DELETE

  9. INSERT • INSERT INTO <table> (<field1>, <field2>)VALUES (<value1>, <value2>) • INSERT INTO `supplier` (`no`, `name`, `state`, `city`)VALUES (1,’Company’, 20, ‘Kaunas’) • INSERT INTO `supplier` (`no`, `name`, `state`, `city`)VALUES (2,’Company B’, 15, ‘Kaunas’),VALUES (3,’Company C’, 10, ‘Vilnius’),

  10. SELECT • SELECT <fields> FROM <table>  • SELECT`name`, `state`FROM`supplier` • All entries in table supplier

  11. SELECT • SELECT <fields> FROM <table>   [WHERE <clause>]  • SELECT `name`, `state` FROM `supplier` WHERE(`city`='Vilnius‘) OR (`city`=‘Kaunas‘)

  12. SELECT • SELECT <fields> FROM <table>   [WHERE <clause>]   [ORDER BY <fields> ] • SELECT `name`, `state` FROM `supplier` WHERE `state`>5ORDER BY `city` DESC

  13. SELECT • SELECT <fields> FROM <table>   [WHERE <clause>]   [ORDER BY <fields> ] • [LIMIT [<start>], <count> ] • SELECT `name`, `state` FROM `supplier` WHERE `state`>5ORDER BY `city` DESC • LIMIT 1, 3

  14. UPDATE • UPDATE <table> SET <field1>=<value1>[,  <field2>=<value2> ]  [WHERE <clause>]  [LIMIT [<start>], <count> ] • UPDATE `supplier`SET `stare`=50WHERE `city`=‘Vilnius’

  15. DELETE • DELETEFROM <table>   [WHERE <clause>]  [LIMIT [<start>], <count> ] • DELETE FROM `supplier` WHERE `No`=3LIMIT 1

  16. Exercise • Download sample database: • tiny.lt/sql1 • Import it to MySQL • Write SQL statements to select information for the following questions • Save statements to a text file • All clients from London • Employee list, sorted by last name • List of orders made in 2003 • Average price of 1:10 scale products • Sum of payments, made in 2004 by client no. 114 arunas@tiny.lt arunas@tiny.lt

More Related