MySQL - Intro to Database

MySQL - Intro to Database

• 1.
  MYSQL – INTROTO DATABASE Prepared by Chester Chin
• 2.
  Structured Query Language programminglanguage for getting information from and updating a database. What does “SQL” Means?
• 3.
  Relate SQL likea smart Excel or Access •Speed, speed and more speed. •No programming language out there can outperform SQL when it comes to bulk operations. •Data integrity •SQL has many tools to help make sure the data stored conforms to rules designed by the system architects. •Availability & scalability •allows multiple users to work on the same dataset Advantage of SQL
• 4.
  In SQL Language: SELECT* FROM Book WHERE price > 100.00 ORDER BY title; A quick look at it In Human Language: I want a list all of books in the order of alphabetical order according to title where the price is more than 100.
• 5.
  CHECKLIST – BeforeWe Start • MYSQL 5.5 Command Line Client • Type every command • MYSQL Workbench CE • Graphical User Interface(GUI) • Make sure you have this two installed
• 6.
  KEYWORDS Database Tables Rows& Columns Excel File Excel Sheets Rows & Columns
• 7.
  LETSSTART LOAD MYSQL CommandLine Client
• 8.
  COMMONMISTAKES ; Missing Semicolon ‘ ` Confusionover ‘ & ` ` (Located at top left corner) ‘ (Located beside ENTER)
• 9.
  SHOWDATABASES – CommandLine TYPE: mysql>show databases; RESULT: +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec)
• 10.
  USEDATABASES – CommandLine TYPE: mysql>use mysql; RESULT: +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | func | | help_category | | help_keyword | | help_relation | | help_topic | ……….AND MORE +---------------------------+ 17 rows in set (0.00 sec)
• 11.
  CREATEDATABASE - testdb Mysql>create DATABASE testdb Mysql > show DATABASES;
• 12.
  CREATINGTABLES NOTE : Whenyou create a ‘TABLE’, you need to create AT LEAST ONE column. Mysql>CREATE TABLE `newtable` ( `firstcolumn` VARCHAR(20) NOT NULL, PRIMARY KEY (`firstcolumn`)); HUMAN LANGUAGE: Hey, Create a table call ‘newtable’ and create a column named ‘firstcolumn’ that can fit 20 character and must not be empty which is also a primary key.
• 13.
  INSERTINGVALUES Try type thesame thing again. What happened? INSERT INTO `newtable` (`firstcolumn`) VALUES ('1111'); HUMAN LANGUAGE: Hey, please insert the value ‘1111’ into the ‘firstcolumn’ or the table ‘newtable’
• 14.
  KEYTERMS Primary Key :ONLY UNIQUE VALUE, NO REPEAT VALUE VARCHAR : Variable-length character VARCHAR(10) : Variable-length character, Max 10 character INT : Integer only ….Many More, however for this level lets just fixed a few. NOT NULL : Cannot store empty value
• 15.
  EXERCISE CREATE a database‘exercisedb’ CREATE a table ‘exercisetable’ CREATE a varchar(20) column ‘firstcolumn’ and make it a primary key. INSERT any value into column ‘firstcolumn’ [10 times] INSERT INTO `newtable` (`firstcolumn`) VALUES ('1111'); Mysql>CREATE TABLE `newtable` ( `firstcolumn` VARCHAR(20) NOT NULL, PRIMARY KEY (`firstcolumn`)); Cheat Sheet
• 16.
  SELECTSTATEMENT SELECT * FROMnewtable; * ALL Column available in this table
• 17.
  COMMANDLINE - LIMITATION Ifthe exercise challenge change to this question. What do you think? INSERT any value into column ‘firstcolumn’ [300 times]
• 18.
  MYSQLWORKBENCH – GRAPHICALWAY
• 19.
  That’s All, ThanksFor Your Time. ^.^ Chinschian@Hotmail.com Drop me an email if you have inputs for me to improve.