More Related Content
What's hot (17)
Similar to MySQL notes - Basic Commands and Definitions (20)
Recently uploaded (20)
MySQL notes - Basic Commands and Definitions
- 1. MySQL Notes Structured Query Language
- 2. What is Database? 1. Collection of data 2. A method for accessing and manipulating data 3. A structured set of computerized data with an accessible interface
- 3. Data Definition Language (DDL) A set of statements that allow the user to define or modify data structures and objects, such as tables - CREATE - ALTER - DROP - RENAME - TRUNCATE
- 4. Data Manipulation Language (DML) Its statements allow us to manipulate the data in the tables of a database - SELECT… FROM… - INSERT INTO… VALUES… - UPDATE… SET… WHERE… - DELETE FROM… WHERE…
- 5. Data Control Language (DCL) the GRANT and REVOKE statements allow us to manage the rights users have in a database GRANT type_of_permission ON database_name.table_name TO ‘username’@’localhost’ REVOKE type_of_permission ON database_name.table_name FROM ‘username’@’localhost’
- 6. Transaction Control Language (DCL) not every change you make to a database is saved automatically the COMMIT statement - will save the changes you’ve made - will let other users have access to the modified version of the database - related to INSERT, DELETE, UPDATE the ROLLBACK clause the clause that will let you make a step back - allows you to undo any changes you have made but don’t want to be saved permanently
- 7. SQL Syntax DDL – Data Definition Language SQL Syntax creation of data DML – Data Manipulation Language manipulation of data DCL – Data Control Language assignment and removal of permissions to use this data TCL – Transaction Control Language saving and restoring changes to a database
- 8. Primary Key A column (or a set of columns) whose value exists and is unique for every record in a table is called a primary key - each table can have one and only one primary key - in one table, you cannot have 3 or 4 primary keys - primary keys are the unique identifiers of a table - cannot contain null values! - not all tables you work with will have a primary key
- 9. Foreign Key Identifies the relationships between tables, not the tables themselves Unique Key used whenever you would like to specify that you don’t want to see duplicate data in a given field
- 10. Relationships relationships tell you how much of the data from a foreign key field can be seen in the primary key column of the table the data is related to and vice versa types of relationships - one-to-many (many-to-one) - one-to-one - many-to-many
- 11. Creating Database show databases; CREATE DATABASE <name>; CREATE DATABASE soap_store; CREATE DATABASE DogApp; CREATE DATABASE My App; USE <database name>; SELECT database();
- 12. Different Data Types in MySQL Numeric Types a. INT b. SMALLINT c. TINYINT d. MEDIUMINT e. BIGINT f. DECIMAL g. NUMERIC h. FLOAT i. DOUBLE j. BIT Strings Types a. CHAR b. VARCHAR c. BINARY d. VARBINARY e. BLOB f. TINYBLOB g. MEDIUMBLOB h. LONGBLOB i. TEXT j. TINYTEXT k. MEDIUMTEXT l. LONGTEXT m. ENUM Date Types a. DATE b. DATETIME c. TIMESTAMP d. TIME e. YEAR
- 13. Creating Table CREATE TABLE tablename ( column_name data_type, column_name data_type ); CREATE TABLE cats ( name VARCHAR(100), age INT ); SHOW TABLES; SHOW COLUMNS FROM <tablename>; DESC <tablename>;
- 14. Deleting Table DROP TABLE <tablename>; Insert into Table INSERT INTO cats(name, age) VALUES ("Jetson", 7); INSERT INTO cats(name, age) VALUES ('Charlie', 10) ,('Sadie', 3) ,('Lazy Bear', 1);
- 15. Using Database and Tables USE sales; SELECT * FROM customers; SELECT * FROM sales.customers; DROP TABLE sales;
- 16. Constraints Specific rules, or limits, that we define in our tables - the role of constraints is to outline the existing relationships between different tables in our database Example :- NOT NULL
- 17. Primary Key
- 18. Foreign Key ON DELETE CASCADE if a specific value from the parent table’s primary key has been deleted, all the records from the child table referring to this value will be removed as well
- 19. Unique Key
- 22. SELECT / WHERE / AND
- 23. AND / OR
- 24. IN / NOT IN
- 25. LIKE / NOT LIKE
- 26. BETWEEN …. AND ….
- 27. IS NULL / IS NOT NULL
- 28. DISTINCT / AGGREGATE FUNCTION ORDER BY
- 29. GROUP BY
- 30. HAVING
- 31. INSERT
- 32. UPDATE
- 34. IFNULL() / COALESCE() For One Column For Multiple Columns
- 35. INNER JOIN
- 36. LEFT JOIN
- 37. RIGHT JOIN / SELF JOIN
- 38. CROSS JOIN / AGGREGATE FUNCTION WITH JOIN
- 39. SUBQUERIES
- 40. VIEW a virtual table whose contents are obtained from an existing table or tables, called base tables - The view itself does not contain any real data - the data is physically stored in the base table - the view simply shows the data contained in the base table