MySQL

gvln5241
Mind Map by gvln5241, updated more than 1 year ago
gvln5241
Created by gvln5241 over 6 years ago
490
4

Description

Mind Map on MySQL, created by gvln5241 on 16/05/2015.

Resource summary

MySQL
  1. Data types

    Annotations:

    • https://www.youtube.com/watch?v=7fOdo8PhPaw&list=PL_RGaFnxSHWr_6xTfF2FrIw-NAOo3iWMy&index=7
    1. Numeric type
      1. BigInt (Really Larger Numebrs)
        1. Int (Big Values)
          1. Small Int (-32,768 to 32, 767)
            1. Tinyint (0 to 255)
              1. Decimal (m,d)
                1. Numeric (same As Decimal)
                  1. Float (m,d)
                  2. Date and Time Data Type
                    1. Datetime (YYYY-MM-DD HH:MM:SS)
                      1. Date

                        Annotations:

                        • MySQL - (YYYY-MM-DD) Oracle - (DD-MMM-RR)
                        1. Question

                          Annotations:

                          • How to See the Format in results ?
                        2. Time (HH:MM:SS)
                          1. Year (YYYY)
                          2. Character Type
                            1. Char (M)
                              1. Varchar (M)
                                1. Text (to store large amount of text)
                                2. Blob (binary large object) - for storing images
                                  1. Questions

                                    Annotations:

                                    • Unable to change the Column data type from Numeric to INT in SQL Deveopler tool... Why? Can we change the data type after table is created via SQL commands ??
                                  2. Constraints

                                    Annotations:

                                    • https://www.youtube.com/watch?v=bIFB-rz315U&index=8&list=PL_RGaFnxSHWr_6xTfF2FrIw-NAOo3iWMy
                                    1. Not Null Constraint

                                      Annotations:

                                      • 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.
                                      1. Default Constraint

                                        Annotations:

                                        • If while populating a table you miss giving a colum some value, the default value will be used
                                        1. Unique constraint

                                          Annotations:

                                          • Unique constraint makes sure that all values entered for the field on which the constraint applied are different
                                          1. Primary Key Constraint

                                            Annotations:

                                            • Not Null + Unique
                                          2. DDL
                                            1. Create Table

                                              Annotations:

                                              • Name the Table Define the Columns Mention data types of columns
                                              1. Syntax

                                                Annotations:

                                                • CREATE TABLE table_name ( col1 datatype, col2 datatype, . . col n datatype, PRIMARY KEY (column));
                                                1. Example

                                                  Annotations:

                                                  • 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;
                                                  1. Questions

                                                    Annotations:

                                                    • 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 ?
                                                  2. Remove Table

                                                    Annotations:

                                                    • Deleting a table from database. Make sure to ensure the table by using  1) SHOW TABLES 2) DESC table_name
                                                    1. Syntax

                                                      Annotations:

                                                      • DELETE TABLE table_name;
                                                      1. Questions

                                                        Annotations:

                                                        • How to Remove only data from table ? any way to restore the deleted table ?
                                                      2. TRUNCATE
                                                        1. Syntax

                                                          Annotations:

                                                          • TRUNCATE TABLE tab_name
                                                      3. DML
                                                        1. INSERT

                                                          Annotations:

                                                          • https://www.youtube.com/watch?v=Tet3Z7Yb2gg&list=PL_RGaFnxSHWr_6xTfF2FrIw-NAOo3iWMy&index=12
                                                          1. Syntax

                                                            Annotations:

                                                            • 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.
                                                            1. Questions

                                                              Annotations:

                                                              • Can we avoid the column names, when inserting from another table like -  INSERT INTO emp_info (id,name,dob, email) SELECT all FROM employee;
                                                            2. SELECT
                                                              1. Syntax

                                                                Annotations:

                                                                • SELECT col1, col3,.., coln FROM Table_name
                                                                1. WHERE Clause

                                                                  Annotations:

                                                                  • Used to mention the conditions & joining the differnt tables
                                                                  1. Syntax

                                                                    Annotations:

                                                                    • SELECT col1 FROM table_name [WHERE condition]
                                                                  2. TOP / LIMIT Clause

                                                                    Annotations:

                                                                    • Used to fect the top N number of records from query. Not all DBs support Oracle supports ROWNUM while MySQL uses LIMIT
                                                                    1. Syntax

                                                                      Annotations:

                                                                      • MySQL -  SELECT Col-ListFROM table_name[WHERE condition]LIMIT n;
                                                                    2. ORDER BY Clause
                                                                      1. Syntax

                                                                        Annotations:

                                                                        • SELECT col-list FROM table_name [WHERE Condition] [ORDER BY Col2,Col4] [ASC/DSC]
                                                                      2. GROUP BY Clause

                                                                        Annotations:

                                                                        • GROUP BY always follow the WHERE Clause and must precede the ORDER BY clause
                                                                        1. Syntax

                                                                          Annotations:

                                                                          • SELECT col-list FROM table_name Where Condition [GROUP BY col] [ORDER BY col]
                                                                        2. DISTINT keyword

                                                                          Annotations:

                                                                          • Used to fetch the single record only, when table is having the same value/data in multiple rows
                                                                          1. AS Clause

                                                                            Annotations:

                                                                            • Giving different name to column headers in SELECT
                                                                          2. UPDATE
                                                                            1. Syntax

                                                                              Annotations:

                                                                              • UPDATE table_name SET col2=val2,..coln=valn [WHERE condition]
                                                                            2. DELETE
                                                                              1. Syntax

                                                                                Annotations:

                                                                                • DELETE FROM table_name [WHERE condition]
                                                                              2. JOINS
                                                                                1. Syntax

                                                                                  Annotations:

                                                                                  • SELECT e.ename, d.dname From emp e join DEPT d on e.deptno=d.deptno;
                                                                                  1. Questions

                                                                                    Annotations:

                                                                                    • Can we use JOINS for update / delete ?
                                                                                    1. LEFT JOIN
                                                                                      1. RIGHT JOIN
                                                                                        1. FULL OUTER JOIN
                                                                                        2. VIEW

                                                                                          Annotations:

                                                                                          • View is same as a table without any storage.
                                                                                          1. Syntax

                                                                                            Annotations:

                                                                                            • CREATE VIEW EMP_VIEW AS SELECT EMPNO,ENAME FROM EMP
                                                                                          2. SubQueries
                                                                                          3. Operators

                                                                                            Annotations:

                                                                                            • These are used with WHERE clause to limit the number of rows in output
                                                                                            1. Arithmetic
                                                                                              1. Addition (+)
                                                                                                1. Subtraction (-)
                                                                                                  1. Multiplication (*)
                                                                                                    1. Division (/)
                                                                                                      1. Modular Division (%)
                                                                                                        1. Questions

                                                                                                          Annotations:

                                                                                                          • SELECT 5 + 5; is not working in SQL Developers. May be we can't use SQL Developer as calculator ??
                                                                                                        2. Comparison

                                                                                                          Annotations:

                                                                                                          • To compare data present in columns to data specified in conditions
                                                                                                          1. Equal (=)
                                                                                                            1. Not Equal (<>)
                                                                                                              1. Greater Than (>)
                                                                                                                1. Less Than (<)
                                                                                                                  1. Greather Than or Equal To(>=)
                                                                                                                    1. Lessthan Or Equal to (<=)
                                                                                                                    2. Logical
                                                                                                                      1. ALL
                                                                                                                        1. AND
                                                                                                                          1. BETWEEN
                                                                                                                            1. IN
                                                                                                                              1. LIKE

                                                                                                                                Annotations:

                                                                                                                                • 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
                                                                                                                                1. Syntax

                                                                                                                                  Annotations:

                                                                                                                                  • SELECT Column-list  FROM table_name [WHERE col LIKE '_XXX%']
                                                                                                                                2. OR
                                                                                                                                  1. IS NULL
                                                                                                                                3. Functions

                                                                                                                                  Annotations:

                                                                                                                                  • Map is for MySQL. =========http://www.oracle-dba-online.com/sql/oracle_sql_functions.htm
                                                                                                                                  1. ABS
                                                                                                                                    1. ROUND
                                                                                                                                      1. DIV()
                                                                                                                                        1. MOD()
                                                                                                                                          1. CEIL
                                                                                                                                            1. FLOOR
                                                                                                                                              1. EXP(x)

                                                                                                                                                Annotations:

                                                                                                                                                • e = 2.57
                                                                                                                                                1. LOG(X)
                                                                                                                                                  1. POWER(X,Y)
                                                                                                                                                    1. GREATEST(X,Y,Z)
                                                                                                                                                      1. LEAST
                                                                                                                                                        1. RADIANS

                                                                                                                                                          Annotations:

                                                                                                                                                          • Not working in ORACLE SQL
                                                                                                                                                          1. SQRT
                                                                                                                                                            1. TRUNCATE
                                                                                                                                                              1. RAND
                                                                                                                                                                1. CONCAT
                                                                                                                                                                  1. UPPER
                                                                                                                                                                    1. LOWER
                                                                                                                                                                      1. INITCAP
                                                                                                                                                                        1. TRIM
                                                                                                                                                                          1. SUBSTR

                                                                                                                                                                            Annotations:

                                                                                                                                                                            • select substr('mohammed sami',10,3) from dual; Substr -------- sam
                                                                                                                                                                            1. RIGHT
                                                                                                                                                                              1. LEFT
                                                                                                                                                                                1. LENGTH
                                                                                                                                                                                  1. INSERT
                                                                                                                                                                                    1. REPEAT
                                                                                                                                                                                      1. REPLACE
                                                                                                                                                                                        1. REVERSE
                                                                                                                                                                                          1. STRCMP
                                                                                                                                                                                            1. DATE & TIME
                                                                                                                                                                                              1. ADDDATE
                                                                                                                                                                                                1. SUBDATE
                                                                                                                                                                                                  1. DAYNAME
                                                                                                                                                                                                    1. CURDATE & CURTIME
                                                                                                                                                                                                      1. NOW()
                                                                                                                                                                                                        1. MAKEDATE
                                                                                                                                                                                                          1. MONTHNAME
                                                                                                                                                                                                            1. TIMEDIFF
                                                                                                                                                                                                              1. TIME_TO_SEC
                                                                                                                                                                                                              2. Aggregate
                                                                                                                                                                                                                1. COUNT
                                                                                                                                                                                                                  1. MAX
                                                                                                                                                                                                                    1. MIN
                                                                                                                                                                                                                      1. SUM
                                                                                                                                                                                                                        1. AVERAGE
                                                                                                                                                                                                                      Show full summary Hide full summary

                                                                                                                                                                                                                      Similar

                                                                                                                                                                                                                      Types and Components of Computer Systems
                                                                                                                                                                                                                      Jess Peason
                                                                                                                                                                                                                      Input Devices
                                                                                                                                                                                                                      Jess Peason
                                                                                                                                                                                                                      Output Devices
                                                                                                                                                                                                                      Jess Peason
                                                                                                                                                                                                                      General ICT Quiz
                                                                                                                                                                                                                      leahshaw
                                                                                                                                                                                                                      ICT Revison Flash Cards
                                                                                                                                                                                                                      Arun Johal
                                                                                                                                                                                                                      Networks
                                                                                                                                                                                                                      Will8324
                                                                                                                                                                                                                      FLAT FILE VS RELATIONAL DATABASE
                                                                                                                                                                                                                      rosiejones
                                                                                                                                                                                                                      Online World - Learning Aim A
                                                                                                                                                                                                                      andysedge
                                                                                                                                                                                                                      User Interfaces
                                                                                                                                                                                                                      Skeletor
                                                                                                                                                                                                                      General ICT Quiz
                                                                                                                                                                                                                      Jade Fantom
                                                                                                                                                                                                                      Data Types
                                                                                                                                                                                                                      Jacob Sedore