SQL - Study note :SQL - Study note By Maggie Zhou Sep, 2008


Preface :2 Preface This is one of my study note for SQL Main part of this note is from Head First SQL: Your Brain on SQL -- A Learner's Guide By Lynn BeighleyAugust 2007 Pages: 607 Series: Head FirstISBN 10: 0-596-52684-9 | ISBN 13: 9780596526849 This is a great book for beginner, and you will have a lot of fun when you go through this book (trust me, this is not a commercial) But it isn’t only from this book: I organized this note in my understanding way. I added several concepts which I think are important


Basic concepts :3 Basic concepts SQL: /siːkwəl/, Structured Query Language, RDBMS: Relational DataBase Management Systems SQL Appeared in 1974 MySQL: SQL/PSM SQL/Persistent Stored Module (as in ISO SQL:2003) Oracle: PL/SQL Procedural Language/SQL (based on Ada)


MySQL installation :4 MySQL installation MySQL community Server Official free version of the MySQL RDBMS Steps to install MySQL on windows Download http://dev.mysql.com/downloads/ Choose windows from list Choose Windows ZIP/Setup.exe option Pick a mirror Run Setup Wizard Choose typical Install to default location: c:\program files\mysql\mysql server 5.0 Finally install it


Create (1) :5 Create (1) Case insensitive Database CREATE DATABASE name Table CREATE TABLE name ( data_name1 CHAR/CHARACTER(x~256), data_name2 DEC/DECIMAL(x,x), data_name3 INT/INTEGER, data_name4 BLOB, data_name5 DATE, data_name6 DATETIME/TIMESTAMP, data_name7 VARCHAR(x~256) );


Create (2) :6 Create (2) Columns / Attributes Mandatory value (… data_name CHAR(10) NOT NULL, …) set default value (… data_name DEC(3.2) NOT NULL DEFAULT 1.00, …) Use keyword as column’s name BAD, but can do  ‘INT’ is ok


Data type (1) :7 Data type (1) BOOLEAN RDBMS store a 1 for true, 0 for false You can insert: 1/’true’, 0/’false’, null INT Unsigned integer: values in the range 0 to 4294967295 Signed integer: INT(SIGNED) SMALLINT BIGINT MySQL also has: TINYINT, MEDIUMINT


Data type (2) :8 Data type (2) DATE and TIME DATE: yyyy-mm-dd DATATIME: yyyy-mm-dd hh:mm:ss TIMESTAMP: yyyymmddhhmmss TIME: hh:mm:ss DATE_FORMAT tells function the date_format you want SELECT DATE_FORMAT (date, ‘%M %Y’) FROM table_name; Result will be:  Auguest 2007


CAST :9 CAST CAST (column, TYPE); Types CHAR() DATE, DATETIME, TIME DECIMAL SIGNED [INTEGER] UNSIGNED [INTEGER] CAN Be included inside the INSERT, SELECT CAN’T DEC to INT TIME, DATE, DATETIEM, CHAR to DEC, or INT


Slide 10:10


Maintain :11 Maintain Change database USE database_name; Check structure of table DESC table_name; Check table content SELECT * FROM table_name Change Please see from Slides 23 Delete DROP TABLE table_name; Only can use on empty table


Add data :12 Add data Insert data INSERT INTO table_name (column_name1, column_name2,…) VALUES (‘value1’, ‘value2’,…); For the AUTO_INCREMENT column, can use ‘’, instead input value Synax for column Using column name can control the insert order, the value order should match Not using column name, value need including all the value, in exact order in table Synax for value: CHAR, VARCHAR, DATE, BLOB need ‘single quote’ INT, DEC do not use quote


Select (1) :13 Select (1) Show all data & all column SELECT * FROM table_name; Show chosen data & all column SELECT * FROM table_name WHERE column_name = value; Can use =, >, =, <= INT, DEC can use ‘’ or not CHAR, BLOB, VARCHAR, DATE, TIMESTAMP… must use ‘’ Never use “ ” Show chosen data & chosen columns SELECT column_name1, column_name3,… FROM table_name WHERE column_name = value;


Select (2) :14 Select (2) Show multiple choose conditions SELECT * FROM table_name WHERE condition1 AND, OR AND: match all the conditions OR: match only one condition is enough Condition2; Comparison operators =, >, =, mean not equal Find NULL SELECT * FROM table_name WHERE column_name IS NULL;


Select (3) :15 Select (3) Wide card SELECT * FROM table_name WHERE column_name LIKE ‘%XX’; %  any number of unknown characters _  just only one unknown character Given Range SELECT * FROM table_name WHERE column_name BETWEEN xx AND xx; Given Group SELECT * FROM table_name WHERE column_name IN (value, value,…); IN  the data match conditions NOT IN  the data do not match conditions


Select (4) :16 Select (4) NOT conditions Combine AND, OR, IS with NOT Right after AND, OR  AND NOT… IS NOT NULL means WHERE NOT name IS NULL Could do, but you shouldn’t do double negative Bad: WHERE NOT column ‘value’;


Database design :17 Database design Steps to design Pick one thing your table want to describe Make a list of information using the table Break down information into pieces and organizing table Atomic data Column with atomic data can’t have several value of the same type of data Table with atomic data can’t have multiple columns with same type of data Normal table Normal table won’t have duplicate data With less data to search through


1NF – FIRST NORMAL FORM :18 1NF – FIRST NORMAL FORM Each row of data must only contain atomic values Each row of data must have a unique identifier


Primary key :19 Primary key Create CREATE TABLE table_name (key_name data_type NOT NULL, PRIMARY KEY (key_name) ); OR CREATE TABLE table_name (key_name data_type NOT NULL PRIMARY KEY, … ); Key: Can not be NULL Must be compact Can not be changed Auto incrementally (key_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY, … ); Data has same key value won’t be accepted The key can be overwritten – you still can make own value AUTO-INCREMENT  Only one per table, must be INT, cannot be NOT NULL


Show statements :20 Show statements SHOW CREATE TABLE table_name; Return a CREATE TABLE statement SHOW CREATE DATABASE database_name; Return a CREATE DATABASE statement SHOW COLUMNS FROM table_name; Equals to DESC table_name SHOW INDEX FROM table_name; SHOW WARNINGS; SHOW TABLES Show all the tables, views


Delete row :21 Delete row Delete row/rows base on condition DELETE FROM table_name WHERE condition…; Without condition, you will delete all data


Change row :22 Change row Update row/rows base on condition UPDATE table_name SET column_name = ‘new value’, other changes… WHERE column_name = ‘value’; Without condition, you will update all data Update can use simple math: Can use *, /, +, - SET column_name = column_name +1 Also can use operation on other data


ALTER Table (1) :23 ALTER Table (1) All about the columns – table structure Change Change name, data type of existing column Modify Data type or position of existing column Add Add new column Drop Delete column from table


ALTER Table (2) – ADD COLUMN :24 ALTER Table (2) – ADD COLUMN Alter table ALTER TABLE table_name Add column ADD COLUMN column_name type NOT NULL AUTO_INCREMENT FIRST, (don’t forget , ) ADD PRIMARY KEY (column_name); OR ADD COLUMN column_name type … AFTER another_column_name; Define the position; Can use: FIRST, LAST, AFTER, BEFORE Even can use: SECOND, FIFTH…


ALTER Table (3) – CHANGE/RENAME :25 ALTER Table (3) – CHANGE/RENAME Rename table ALTER TABLE table_name RENAME TO new_table_name Change column name and type ALTER TABLE table_name CHANGE COLUMN column_name new_column_name new_type …, …;


ALTER Table (3) – MODIFY :26 ALTER Table (3) – MODIFY Modify column ALTER TABLE table_name MODIFY COLUMN column_name new_type/size; Cannot change order of columns It won’t matter, due to you can use SELECT to control order


ALTER Table (3) – DROP :27 ALTER Table (3) – DROP Delete column ALTER TABLE table_name DROP COLUMN column_name; Remove AUTO_INCREMENT ALTER TABLE table_name CHANGE column_name new_column_name new_type;  without AUTO_INCREMENT Just simply without AUTO_INCREMENT After remove AUTO_INCREMENT, then you can drop the PRIMARY KEY, otherwise, will be error Remove PRIMARY KEY ALTER TABLE table_name DROP PRIMARY KEY;


Slide 28:28


String functions (1) :29 String functions (1) SELECT RIGHT(column_name, number) FROM table_name; Can use RIGHT, LEFT  start from right or left side of column Number: how many characters to select SELECT SUBSTRING_INDEX (column_name, ‘key’, index) FROM table_name; SUBSTRING_INDEX  subestring, everything in front of it ‘key’  the key character, key string is looking for Index  1 means looking for the first key, 2 means looking for the second key


String functions (2) :30 String functions (2) SUBSTRING(‘string’, index, index); SUBSTR(‘string’, index) Shortening the length of the string start right after the index UPPER(‘string’); LOWER(‘string’); REVERSE(‘string’); LTRIM(‘ string’); RTRIM(‘string ’); LENGTH(‘string’);


Copy :31 Copy Change all the data in a column UPDATE table_name SET column_name = new_value; Copy column UPDATE table_name SET column_name = source_column_name; Can use all the string function


Group data (1) :32 Group data (1) Category UPDATE table_name SET category = ‘value’ WHERE conditions… Update statements help you classify data Update statement’s order matters, due to they may change same value group data  data have same value in the column SELECT * FROM table_name GROUP BY column_name; Only show the only one and the first one record


Group data (2) :33 Group data (2) Choose value UPDATE table_name SET column_name = CASE WHEN condition1 THEN new_value1 WHEN condition2 AND condition3 THEN new_value2 ELSE new_value3  else is optional END; or END WHERE condition4; use this condition to choose specific rows; CASE expression can be used with SELECT, INSERT, DELETE and UPDATE


Order data (1) :34 Order data (1) Show ordered data SELECT column_names, … FROM table_name WHERE conditions, … ORDER BY column_name; Order rules Non-alphabet Number < text character NULL < Number NULL < alphabet character Uppercase < Lowercase Space < number…


Order data (2) :35 Order data (2) Ordered data by multiple columns ORDER BY column_name1, column_name2, …; First order by column_name1, then 2 Descending order ORDER BY column_name DESC; Can use DESCRIBE for DESC table_name But can not use DESCENDING Default order ASC means default order


Calculate data (1) :36 Calculate data (1) NULL is not used by all the calculation functions (absence) SUM() SELECT columns, SUM (column_name1) FROM table_name GROUP BY column_name1; Group all the date with same value in column_name1 Sum all value within the group AVG() SELECT columns, AVG (column_name1) FROM table_name GROUP BY column_name1;


Calculate data (2) :37 Calculate data (2) MIN() SELECT columns, MIN (column_name1) FROM table_name GROUP BY column_name1; MAX() SELECT columns, MAX (column_name1) FROM table_name GROUP BY column_name1;


Calculate data (3) :38 Calculate data (3) COUNT(): Count number of the rows in the column SELECT COUNT (column_name) FROM table_name; GROUP BY another_column; DISTINCT: Remove all the duplicate records SELECT DISTINCT column_name FROM table_name Be careful DISTINCT is a keyword DISTINCT + COUNT() use to calculate the number of absolute different data DISTINCT is used to remove duplicate record COUNT is used the count the number of the rows Thus this avoid count duplicate records


Calculate data (4) :39 Calculate data (4) Limit the number of results SELECT columns, SUM (column_name1) FROM table_name GROUP BY column_name1; ORDER BY SUM (column_name1); LIMIT number; ORDER BY must after the GROUP BY Limit number by range LIMIT index1, index2 index1 is start point  Start from 0 Index2 is number of showing result


SCHEMA :40 SCHEMA Schema is : A description of the data (columns and tables) in your database, along with any other related objects and the way they all connect Diagram for table design Split the non-atomic field into another table


Connect tables (1) :41 Connect tables (1) FOREIGN KEY Is a column in a table that reference the PRIMARY KEY of another table Child_table’s FOREIGN KEY is the PRIMARY KEY of Parent_table FOREIGN KEY is parent key, is from parent table FOREIGN KEY can be different name FOREIGN KEY can be NULL means there is no matching primary key in parent table FOREIGN KEY don’t have to be unique


Connect tables (2) :42 Connect tables (2) Foreign key + constraint Referential integrity Only be able to insert values into your foreign key that in parent table Use a foreign key to reference a unique value in the parent table UNIQUE Not available in MySQL CHECK


Create child table (1) :43 Create child table (1) CREATE TABLE table_name ( Columns… , child_column_name INT NOT NULL, Define child table column name, type…, Don’t forget “,” CONSTRAINT ‘parent_id_fk’  name: parent_table_name + parent_column_name + fk(foreign key) FOREIGN KEY (‘child_column_name’) REFERENCES parent_table_name (‘parent_column_name’) ) ;  book has mistake here


Relationships :44 Relationships One-to-one: plain line Faster queries Isolate column to avoid NULL in main table Make part of data less accessible Put large piece data into a separate table One-to-many: black arrow line, only one arrow at the end of many Many-to-many: black arrow line, arrows at both ends Two one-to-many relationships with a junction table in between


Junction table :45 Junction table Avoid duplicate data in many-to-many relationship A table step in between two many-to-many tables and simplify the relationship to one-to-many. Contain the two primary key columns of the two related table.


Composite primary key :46 Composite primary key Is a primary key Uses several columns together to form a unique primary key in a table


Dependent column :47 Dependent column The A column functionally depend B column,  A must change when B’s data is modified Notations T.x ; T.y Relational table T, column y is functionally dependent on column x Partial functional dependency Non-key column is dependent on some but not all, of the columns in a composite primary key Transitive functional dependency Non-key column is related to any of the other non-key columns


2NF- Second Normal Form :48 2NF- Second Normal Form 2NF focuses on how the primary key relates to the data 2NF rules: Be in 1NF Have no partial functional dependencies 1NF is also 2NF If all the columns in the table are part of the primary key OR It has a signal column primary key


3NF- Third Normal Form :49 3NF- Third Normal Form 3NF rules: Be in 2NF Have not transitive dependencies Table is in 2NF If it has an artificial primary key and no composite primary key


Slide 50:50


Pre-populate table (1) :51 Pre-populate table (1) Create table, then insert with select CREATE TABLE table_name  normal way ( Create Primary_key column, Column_name ); INSERT INTO table_name (column_name) SELECT column_name FROM parent_table_name GROUP BY column_name ORDER BY column_name;


Pre-populate table (2) :52 Pre-populate table (2) Create table with select, then alter to add primary key CREATE TABLE table_name AS SELECT column_name FROM parent_table_name GROUP BY column_name ORDER BY column_name; ALTER TABLE table_name ADD COLUMN primary_key column; AS  funneling all the output of the SELECT into the new table


Pre-populate table (3) :53 Pre-populate table (3) Create, select and insert at the same time CREATE TABLE table_name ( Create Primary_key column, Column_name ) AS SELECT column_name FROM parent_table_name GROUP BY column_name ORDER BY column_name ;


alias :54 alias SQL allow to temporarily give the columns and tables new names Column aliases SELECT column_name AS column_alias  temporary FROM table_name GROUP BY column_alias ORDER BY column_alias; Table aliases: called correlation name SELECT column_name AS column_alias FROM table_name AS table_alias … OR FROM table_name table_alias without AS


Cartesian Join (1) :55 Cartesian Join (1) Different names: Cartesian product, cross product, cross join, no join SELECT TableA.column1, TableB.column2 FROM TableA CROSS JOIN TableB OR FROM TableA , TableB COMMA JOIN use “ , ” instead of CROSS JOIN Return every row from table_A cross with every row from table_B The column which has more values, the column show up in groups.


Cartesian Join (2) :56 Cartesian Join (2) Usage Can help with fix the joins Used to test the speed of the RDBMS and its configuration Cartesian join is a type of inner join


Inner Join :57 Inner Join Inner Join combine records from two tables using comparison operator in a condition Columns only return the matched join rows Inner Join is a Cross Join with some result rows removed by a condition in the query Create Inner Join SELECT A.columns, ... , B.columns,… ,  columns From table_A AS A INNER JOIN Table_B AS B ON A.column = B.column;  relationship Also can use WHERE Matches the two columns in different tables


Inner Join in actions :58 Inner Join in actions Equijoin … INNER JOIN … ON A.id = B.id; Inner join test for equality Non-euqijoin … INNER JOIN … ON A.id B.id; Inner join test for inequality Natural join SELECT columns, … FROM table_A NATURAL JOIN table_B; Inner join identify matching column names Do not need ON conditions


Subquery (1) :59 Subquery (1) A query is wrapped within another query OUTER query INNER query  Subquery Take the result of one query and use it as the input to another query Can be used to Avoid duplicate data Make queries more dynamic


Subquery (2) :60 Subquery (2) Subquery is always a single SELECT statement Subqueries are always inside () parentheses Subqueries do not get their own ; semicolon One ; semicolon goes at the end of entire query Subqueries can be show up in four places SELECT clause SELECT column List FROM clause HAVING clause Subqueries can be used with INSERT DELETE UPDATE SELECT


Subquery (3) :61 Subquery (3) SELECT columns FROM table_A NATURAL JOIN table_B WHERE column_name IN (SELECT column FROM table_C);  Subquery All others are OUTER query


Subquery (4) :62 Subquery (4) SELECT columns FROM table_name WHERE condition (subquery) Usually, Subquery return single value,  one row from one column where column = (subquery) Subquery return more than one value when using IN where column IN (subquery) Subquery also can use WHERE condition Can use subquery instead the INNER JOIN Join combine all columns from two tables Subquery can only use the columns from one table


Subquery as SELECT column :63 Subquery as SELECT column Used as a column expression in SELECT statement Can only return one value from one column SELECT columns (SELECT column FROM table WHERE conditions) FROM table


Noncorrelated subquery :64 Noncorrelated subquery Correlated subquery is slow Noncorrelated subquery Subquery stands alone Doesn’t reference anything from the outer query Inner query in no way depends on values from the outer query It can run as a standalone query Noncorrelated subquery with IN, NOT IN Use IN, NOT IN to test if the values returned in subquery are members of a set(or not)


Correlated subquery :65 Correlated subquery Correlated subquery Inner query relies on the outer query before it can be resolved Use the same alias or correlated name from the outer query Common use Find all the rows in the outer query for which no rows exist in a related table EXISTS NOT EXISTS


EXISTS and NOT EXISTS :66 EXISTS and NOT EXISTS EXISTS: Find the results in table_A which at least shows in table_B once SELECT columns FROM table_A WHERE EXISTS (SELECT * FROM table_B WHERE condition usually condition involve outer query ); NOT EXISTS: Find the results in table_A which is not in table_B SELECT columns FROM table_A WHERE NOT EXISTS (SELECT * FROM table_B WHERE condition usually condition involve outer query );


Outer Join (1) :67 Outer Join (1) Deal with two tables which don’t have any matching counterpart Relationship between two tables One-to-many relationship LEFT OUTER JOIN: SELECT columns FROM Left_table LEFT OUTER JOIN Right_table ON relationship; Takes all the rows in the Left_table and matches them to rows in the Right_table.


Outer Join (2) :68 Outer Join (2) RIGHT OUTER JOIN: SELECT columns FROM Left_table RIGHT OUTER JOIN Right_table ON relationship; Takes all the rows in the Right_table and matches them to rows in the Left_table. Reverse the LEFT OUTER JOIN Reason to use these two: change the key word is easier than change the order of the tables Similar with INNER JOIN but not  OUTER JOIN gives all the rows whether there is a match with the other table or not Use NULL value to show no match exists In LEFT OUTER JOIN, NULL means the Right_table has no value that correspond to the Left_table Some RDBMS use FULL OUTER JOIN, but not in MySQL, SQL Server or Access


Self-join (1) :69 Self-join (1) Self-referencing foreign key means that it is a key is referencing another field in the same table The primary key of a table used in that same table for another purpose The primary key use to describe other attribute of the elements Join a single table to itself Used to simulate having two tables with exactly same information In a normalized database, would never have two copies of the same table


Self-join (2) :70 Self-join (2) Use different alias to separate the same table act as two tables SELECT alias1.column, …, alias.column, …, FROM table_name AS alias1 INNER JOIN Table_name AS alias2 ON alias1.primary_key = alias2.self_referencing_key;


Union :71 Union Combine the results of queries Combine the results from multiple tables SELECT column FROM table_A UNION OR UNION ALL Return every match, including the duplicate values By default, results removed duplicate values SELECT column FROM table_B ORDER BY column; Only can add ORDER BY at the end of the statement Rules: Number of columns in each SELECT statement must match Must have same expression and aggregate function in each SELECT statement Order of SELECT statement doesn’t matter The data types in the columns need to either same or convertible to each other


INTERSECT & EXCEPT :72 INTERSECT & EXCEPT UNION, INTERSECT, EXCEPT are used to find parts of queries that overlap INTERSECT: Returns only those values that are in the first query and also in the second query EXCEPT: Returns only those values that are in the first query but not in the second query MySQL do not have INTERSECT and EXCEPT


Subqueries vs. Joins (1) :73 Subqueries vs. Joins (1) Subquery  ORDER BY + LIMIT SELECT title FROM job_listings WHERE salary = (SELECT MAX(salary) from job_listings); How about: SELECT title FROM job_listings ORDER BY salary DESC LIMIT 1; Self-join  subquery SELECT f1.name, f2.name AS boss FROM friend f1 INNER JOIN friend f2 ON f1.id = f2.boss; How about: SELECT f1.name, (SELECT name FROM friend WHERE f1.boss = id) AS boss FROM friend f1;


Subqueries vs. Joins (2) :74 Subqueries vs. Joins (2) Inner Join  Subquery SELECT title FROM job_listings jl INNER JOIN job_current jc ON jl.title = jc.title; How about: SELECT title FROM job_listings WHERE title IN (SELECT title from job_current);


Database management :75 Database management Defensive database Avoid entering wrong data Stop concurrent operation on the same data at the same time CHECK constraints and views both help maintain control when you have multiple users Limit authority to allow operate on part of data


CHECK CONSTRAINT :76 CHECK CONSTRAINT Constraint Is a restriction on what you can insert into a column Example: NOT NULL, PRIMARY KEY, FOREIGN KEY, UNIQUE CHECK CREATE TABLE table_name ( Column_name type CHECK (column_name IN (‘F’, ‘M’)) Condition can be any ); OR ALTER TABLE table_name ADD CONSTRAINT CHECK column_name IN () ;  condition CHECK constraint restricts what values you can insert into a column Try to INSERT INTO wrong data will get a error and nothing be inserted Use the same conditions as a WHERE clause, such as AND, OR, IN, NOT, BETWEEN, but not subquery CHECK doesn’t enforce data integrity in MySQL Can create table with check, but MySQL won’t do anything and just ignores it.


View (1) :77 View (1) View Is basically a table that only exists when you use view in query Virtual table Can turn any query as a SELECT into a view View can do more than SELECT data from tables, also can UPDATE, INSERT, DELETE Benefits Keep change database structure from breaking the applications that depend on the tables Make easier by simplifying the complex query into a simple command Create view to hide information that isn’t needed by the use


View (2) :78 View (2) Create a view CREATE VIEW view_name AS Conditions All the queries you want to do ; Use the view SELECT * FROM view_name; Actually view do all the job of the queries you defined when it has been created Delete view DROP VIEW view_name; Different RDBMSs has different behaviors Best drop views before drop the table it is based on MySQL won’t let you drop views without its base table is existing, but you can drop table which has view Check structure DESC view_name;


View (3) :79 View (3) CHECK OPTION CREATE VIEW view_name AS WHERE Conditions WITH CHECK OPTION; Force all inserts and updates to satisfy the WHERE clause in the view MySQL use CHECK OPTION to imitate the CHECK CONSTRAINT If the update/insert data is not according the condition of view, it will show error


View (4) :80 View (4) Updatable view An updatable view includes all the NOT NULL columns from the table it references Allow this kind of view to do the INSERT, UPDATE, DELETE due to all the NOT NULL column can be touched via the view Non-updatable view doesn’t include all the NOT NULL columns Only can do is SELECT CANNOT update views that contains aggregate operators SUM, COUNT and AVG BETWEEN, HAVING, IN, and NOT IN


ACID :81 ACID A: ATOMICITY All the pieces of transaction must be completed or none of them be completed. C: CONSISTENCY Keep database in a consistent state at the end of transaction. I: ISOLATION Every transaction has a consistent view of the database regardless of other transaction take place at the same time. D: DURABILITY After transaction, the database needs to save the data correctly and protect it from power outages or other threats.


Transaction (1) :82 Transaction (1) Transaction is a set of SQL statements to accomplish a single unit of work During a transaction, if all the steps can’t be completed without interference, none of them should be completed. Avoid concurrent problem


Transaction (2) :83 Transaction (2) START TRANSACTION; START TRANSACTION keeps track of all the SQL COMMIT; Make the actions permanent ROLLBACK; Reverse everything to the way it was before you START TRANSACTION No change will occur to the database until you COMMIT RDBMS keep record of everything that has been done when you inside a transaction It cost storage space


Storage engines :84 Storage engines Make sure your storage engine is either BDB or InnoDB, BDB, InnoDB are two possible way that support transaction RDBMS can store data behind the scenes. Change storage engine ALTER TABLE table_name TYPE = InnoDB;


Root user account :85 Root user account ROOT user The first user  ROOT user  has complete control over everything in the database. Set password SET PASSWORD FOR ‘root’ @ ‘localhost’ = PASSWORD(‘password’); ORACLE use: ALTER user root IDENTIFIED BY new_password; Root user’s user name: root ‘localhost’ indicates that this is where the SQL software is installed and running ‘localhost’ is the default value, and it is optional Remote access You have to tell the query: IP address, or a hostname instead of localhost. root@A100.simpleworlf.com


Create new user :86 Create new user SQL doesn’t specify how to manage users Need check RDBMS’s documentation Most use statements: CREATE USER user_name IDENTIFIED BY ‘user_password’;


GRANT (1) :87 GRANT (1) Control exactly what users can do to tables and columns Typical GRANT SELECT Can change to INSERT, DELETE, UPDATE ON table_name TO user_name; Give multiple privilege GRANT SELECT, INSERT ON table_name TO user_name; Name multiple users GRANT DELETE ON table_name TO user1, user2;


GRANT (2) :88 GRANT (2) Assign ALL privileges GRANT ALL ON table_name TO user_name; Allow all permission with SELECT, INSERT, DELETE, UPDATE Allow current user gives this privilege to any other user GRANT SELECT ON table_name TO user_name WITH GRANT OPTION


GRANT (3) :89 GRANT (3) Specific column: only can used for SELECT GRANT SELECT (column_name, column_name 2) ON table_name TO user_name Wildcard: Give the user the privilege for every table in the database GRANT SELECT ON database_name.* TO user_name GRANT global privileges in MySQL GRANT SELECT ON *.* TO user_name;


REVOKE (1) :90 REVOKE (1) REVOKE privileges REVOKE SELECT ON table_name FROM user_name; If the unassigned privilege with get an error Revoke GRANT OPTION privilege: Without remove the privilege on current user, just remove the GRANT OPTION privilege REVOKE GRANT OPTION ON DELETE ON table_name FROM user_name; CAN NOT assign this privilege to others any more But, all the user got the privilege from this user, will lose their permission as well


REVOKE (2) :91 REVOKE (2) CASCADE: Remove the privilege from current user as well as anyone else that current user gave permission to. (usually is default) REVOKE DELETE ON table_name FROM user_name CASCADE; RESTRICT: both current user and the sub_user can retain the privilege, the root get an error, if the current user gave any other user this privilege before REVOKE DELETE ON table_name FROM user_name RESTRICT;


REVOKE (3) :92 REVOKE (3) If the user get the privilege from different time/people Different RDBMSs have different response Some will pay attention to where GRANT came from when CASCADE is used Some will ignore it Just check the documentation Both GRANT, REVOKE can use on VIEW, but be careful with the non-updatable view


Role (1) :93 Role (1) There is no ROLE in MySQL To give groups the privileges instead of assign them individually Create CREATE ROLE role_name; Define roles’ privileges GRANT SELECT ON table_name TO role_name; Assign user to the role GRANT role_name TO user_name;


Role (2) :94 Role (2) Delete role DROP ROLE role_name; Drop a role in use, it also cut related users off from the permissions Revoke role from user REVOKE role_name FROM user_name; A user can have more than one role at a time Need make sure do not have conflicting permissions Denied permission > Grant permission


Role (3) :95 Role (3) Allow the role’s users to grant this role to anyone else GRANT role_name TO user_name WITH ADMIN OPTION CASCADE REVOKE role_name FROM user_name CASCADE; RESTRICT REVOKE role_name FROM user_name RESTRICT;


Combine user creation and grant :96 Combine user creation and grant GRANT SELECT ON table_name TO user_name IDENTIFIED BY ‘password’;


Check status :97 Check status Check current user SELECT CURRENT_USER; root@localhost Check current… SELECT CURRENT_DATE; SELECT CURRENT_TIME;


Reserved words :98 Reserved words There are bunch of reserved words. When try to use single-world, you glance through the lists to make sure SQL has a list of non-reserved words may become reserved in future releases of SQL.


Special characters :99 Special characters * () ; , . _ % ! ‘’ “” \ + Comparison > !, != >= <= & | ^ ~ To escape ‘, you need use \’ or extra ‘’


ALL :100 ALL Before SELECT columns FROM table_name WHERE column IN (SELECT column FROM table_name); ALL: Greater than ALL finds any values larger than the biggest value in the set SELECT columns FROM table_name WHERE column > ALL (SELECT column FROM table_name); Less than ALL finds any values smaller than the smallest value in the set WHERE column < ALL


ANY :101 ANY ANY Greater than ANY finds any values larger than the smallest value in the set SELECT columns FROM table_name WHERE column >ANY (SELECT column FROM table_name); Less than ANY finds any values smaller than the largest value in the set SELECT columns FROM table_name WHERE column < ANY (SELECT column FROM table_name);


SOME :102 SOME Means the things as ANY in standard SQL syntax, and in MySQL . Check the RDBMS to confirm that it works which way


Temporary table :103 Temporary table Temporary table exists from the time you create it until you drop it, or until the user session ends Session means from signed in to sign out RDBMS syntax varies greatly with this, please check first CREATE TEMPORARY TABLE table_name ( columns); OR CREATE TEMPORARY TABLE table_name AS SELECT * FROM permanent_table;


Numeric functions :104 Numeric functions ABS(x) ACOS(x) ASIN(x) ATAN(x,y) CELL(x) COS(x) COT(x) EXP(x) FLOOR(x) FORMAT(x,y) LN(x) LOG(x), LOG(x,y) MOD(x,y) PI() POWER(x,y) RADIANS(x) RAND(x) ROUND(x), ROUND(x,y) SIGN(x) SIN(x) SQRT(x) TAN(x) TRUNCATE(x,y)


Indexing :105 Indexing Index help RDBMS speed up If you search the column frequently Add index ALTER TABLE table_name ADD INDEX (column_name); To be continue…


Get a GUI for RDBMS :106 Get a GUI for RDBMS MySQL GUI tools http://dev.mysql.com/downloads/gui-tools/5.0.html CocoaMySQL http://cocoamysql.sourceforge.net/ Web solution: phphMySQL http://www.phpmysql.net/ Navicat offer 30 days free trail http://www.navicat.com/ SQLyog offers a free community edition http://www.webyog.com/en/ To be continue…


PHP/MySQL :107 PHP/MySQL PHP help you get data via web PHP code take all date from database and store it in an array To be continue…