MySQL

MySQL

1. Constraints

   • https://www.youtube.com/watch?v=bIFB-rz315U&index=8&list=PL_RGaFnxSHWr_6xTfF2FrIw-NAOo3iWMy
   • Not Null Constraint
     • If you want to make sure taht a field gets some value for every row in the table, apply the not null constraint to it.
   • Default Constraint
     • If while populating a table you miss giving a colum some value, the default value will be used
   • Unique constraint
     • Unique constraint makes sure that all values entered for the field on which the constraint applied are different
   • Primary Key Constraint
     • Not Null + Unique

2. DDL

   • Create Table
     • Name the Table Define the Columns Mention data types of columns
     • Syntax
       • CREATE TABLE table_name ( col1 datatype, col2 datatype, . . col n datatype, PRIMARY KEY (column));
     • Example
       • CREATE TABLE employee ( id int PRIMARy KEY,  name varchar (50),  dob date,  email VARCHAR(50) ) =============== Create table emp_info as select id, name from employee;
     • Questions
       • 1. How to check the Constraints of a table ? 2. How to see the list of tables in Oracle ? (SHOW Tables - in MySQL) 3. How to check the two tables are having same columns or not ? 4. DB defined column names - like - ROWID in Oracle DB ?
   • Remove Table
     • Deleting a table from database. Make sure to ensure the table by using  1) SHOW TABLES 2) DESC table_name
     • Syntax
       • DELETE TABLE table_name;
     • Questions
       • How to Remove only data from table ? any way to restore the deleted table ?
   • TRUNCATE
     • Syntax
       • TRUNCATE TABLE tab_name

3. DML

   • INSERT
     • https://www.youtube.com/watch?v=Tet3Z7Yb2gg&list=PL_RGaFnxSHWr_6xTfF2FrIw-NAOo3iWMy&index=12
     • Syntax
       • INSERT INTO table_name (Col 1, col3) VALUES (val 1, val 3) ==================== INSERT INTO table_name VALUES (val 1, val 2, .., val n) >> order of the values need to be correct in second one ==================== INSERT INTO emp_info (id,name, dob, email)  select from id, name, dob, email from employee. >> we can insert the data from other table also.
     • Questions
       • Can we avoid the column names, when inserting from another table like -  INSERT INTO emp_info (id,name,dob, email) SELECT all FROM employee;
   • SELECT
     • Syntax
       • SELECT col1, col3,.., coln FROM Table_name
     • WHERE Clause
       • Used to mention the conditions & joining the differnt tables
       • Syntax
         • SELECT col1 FROM table_name [WHERE condition]
     • TOP / LIMIT Clause
       • Used to fect the top N number of records from query. Not all DBs support Oracle supports ROWNUM while MySQL uses LIMIT
       • Syntax
         • MySQL -  SELECT Col-ListFROM table_name[WHERE condition]LIMIT n;
     • ORDER BY Clause
       • Syntax
         • SELECT col-list FROM table_name [WHERE Condition] [ORDER BY Col2,Col4] [ASC/DSC]
     • GROUP BY Clause
       • GROUP BY always follow the WHERE Clause and must precede the ORDER BY clause
       • Syntax
         • SELECT col-list FROM table_name Where Condition [GROUP BY col] [ORDER BY col]
     • DISTINT keyword
       • Used to fetch the single record only, when table is having the same value/data in multiple rows
     • AS Clause
       • Giving different name to column headers in SELECT
   • UPDATE
     • Syntax
       • UPDATE table_name SET col2=val2,..coln=valn [WHERE condition]
   • DELETE
     • Syntax
       • DELETE FROM table_name [WHERE condition]
   • JOINS
     • Syntax
       • SELECT e.ename, d.dname From emp e join DEPT d on e.deptno=d.deptno;
     • Questions
       • Can we use JOINS for update / delete ?
     • LEFT JOIN
     • RIGHT JOIN
     • FULL OUTER JOIN
   • VIEW
     • View is same as a table without any storage.
     • Syntax
       • CREATE VIEW EMP_VIEW AS SELECT EMPNO,ENAME FROM EMP
   • SubQueries

4. Operators

   • These are used with WHERE clause to limit the number of rows in output
   • Arithmetic
     • Addition (+)
     • Subtraction (-)
     • Multiplication (*)
     • Division (/)
     • Modular Division (%)
     • Questions
       • SELECT 5 + 5; is not working in SQL Developers. May be we can't use SQL Developer as calculator ??
   • Comparison
     • To compare data present in columns to data specified in conditions
     • Equal (=)
     • Not Equal (<>)
     • Greater Than (>)
     • Less Than (<)
     • Greather Than or Equal To(>=)
     • Lessthan Or Equal to (<=)
   • Logical
     • ALL
     • AND
     • BETWEEN
     • IN
     • LIKE
       • Used to compare a value to similar values in a table in database Used with Wildcard characters - % (Percentage) &amp; _ (underscore) % - substitution for zero, one or many char's _ - stands for one character or number
       • Syntax
         • SELECT Column-list&nbsp; FROM table_name [WHERE col LIKE '_XXX%']
     • OR
     • IS NULL