Download
mysql n.
Skip this Video
Loading SlideShow in 5 Seconds..
MySQL PowerPoint Presentation

MySQL

183 Views Download Presentation
Download Presentation

MySQL

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. MySQL More…

  2. More on SQL • In MySQL, the Information Schema is the “Catalog” in the SQL standard • SQL has three components: • Data definition • Data manipulation • Data control • Granting access privileges

  3. Special SQL statements • CREATE DATABASE CLAIMS • USE CLAIMS • CREATE INDEX AGE ON CLAIMS-TABLE (AGE) • GRANT SELECT ON CLAIMST TO DIANE • CREATE VIEW BADCLAIMS … • SHOW WARNINGS • System variables, only some can be changed • SELECT @@version • SET @@GLOBAL.SQL.WARNINGS = TRUE • Three kinds of system variables • Session • Global

  4. Basic SQL statements • SELECT - columns returned • FROM – what tables used • WHERE – conditions rows must meet • GROUP BY - groups rows with equal column values • HAVING – selects groups that meet conditions • ORDER BY – sorts rows on column values • LIMIT – how many rows are to be returned • SET – creates variable definitions • SET @NUMBERCLAIMS = (SELECT …) • HANDLER – used to browse tables by rows • HANDLER CLAIMST OPEN • HANDLER CLAIMST READ FIRST

  5. More SQL • SELECT INTO FILE • Puts data from a table into a file • SELECT NAME FROM CLAIMST INTO OUTFILE ‘C:/CLAIMS.TXT’ • LOAD DATA INFILE ‘C:/CLAIMS.TEXT’ INTO TABLE CLAIMST • Takes data from a file and loads it into a table • PKs and FKs creation • CREATE TABLE CLAIMST (CLAIMID INTEGER NOT NULL, PLANID INTEGER NOT NULL, PRIMARY KEY (CLAIMID), FOREIGN KEY (PLANID) REFERENCES PLANS (PLANID))

  6. Domain types • Tinyint • Smallint • Mediumint • Integer • Bigint • Decimal • Float • Char • Varchar • Long varchar • Longtext

  7. More domain types • Date • Time • Datetime • Timestamp • Year • Blob • Longblob • Longvarbinary • Geometric types for vectors and points and polygons • Integer auto_increment

  8. Table storage options • Called “engines” • MyISAM – default and fast • Memory – for temporary tables that are small • InnoDB – supports row locks • Lots of others…

  9. Code • Stored procedures • CREATE PROCEDURE NEW_POLICY BEGIN … END • Can specify parameters • Can create local variables • CALL statement for running a stored procedure • Can create cursors for stepping through rows • INTO – for putting a value into a host variable • Stored functions • Can have input parameters but no output parameters • They have RETURN statement in them • Triggers • We will look at these… • Events – instead of set off by SQL execution, usually based on time