ºÝºÝߣ

ºÝºÝߣShare a Scribd company logo

Intro to my sql

?Download as PPTX, PDF?
?
Alamgir Bhuyan
Alamgir Bhuyan

This document provides an introduction and overview of MySQL. It discusses that MySQL is a popular open-source database management system, pronounced "my ess que ell". The document then outlines topics like connecting to MySQL, entering queries, creating and using databases and tables, and provides examples of basic queries for selecting, sorting, and counting rows of data.

1 of 22
Download to read offline
Introduction to MySQL
2 Road Map ? Introduction to MySQL ? Connecting and Disconnecting ? Entering Basic Queries ? Creating and Using a Database
3 MySQL ? MySQL is a very popular, open source database. ? Officially pronounced ¡°my Ess Que Ell¡± (not my sequel). ? Handles very large databases; very fast performance. ? Why are we using MySQL? ? Free (much cheaper than Oracle!) ? Each student can install MySQL locally. ? Easy to use Shell for creating tables, querying tables, etc. ? Easy to use with Java JDBC
4 Using a Database ? To create a new database, issue the ¡°create database¡± command: ? mysql> create database webdb; ? CREATE DATABASE bookinfo;
5 Creating a Table ? Once you have selected a database, you can view all database tables: mysql> show tables; Empty set (0.02 sec) ? An empty set indicates that I have not created any tables yet.
6 Creating a Table ? Let¡¯s create a table for storing pets. ? Table: pets ?ID: INT(8) ?name: VARCHAR(20) ?owner: VARCHAR(20) ?species: VARCHAR(20) ?sex: CHAR(1) ?birth: DATE ?date: DATE ?Primary Key (ID); VARCHAR is usually used to store string data.
7 Creating a Table ? To create a table, use the CREATE TABLE command: mysql> CREATE TABLE pet ( -> name VARCHAR(20), -> owner VARCHAR(20), -> species VARCHAR(20), -> sex CHAR(1), -> birth DATE, death DATE); Query OK, 0 rows affected (0.04 sec)
8 Showing Tables ? To verify that the table has been created: mysql> show tables; +------------------+ | Tables_in_test | +------------------+ | pet | +------------------+ 1 row in set (0.01 sec)
9 Deleting a Table ? To delete an entire table, use the DROP TABLE command: mysql> drop table pet; Query OK, 0 rows affected (0.02 sec)
10 Loading Data ? Use the INSERT statement to enter data into a table. ? For example: INSERT INTO pet VALUES ('Fluffy','Harold','cat','f', '1999-02-04',NULL); ? The next slide shows a full set of sample data.
11 More data¡­ name owner species sex birth death Fluffy Harold cat f 1993-02-04 Claws Gwen cat m 1994-03-17 Buffy Harold dog f 1989-05-13 Fang Benny dog m 1990-08-27 Bowser Diane dog m 1998-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 Whistler Gwen bird 1997-12-09 Slim Benny snake m 1996-04-29
12 SQL Select ? The SELECT statement is used to pull information from a table. ? The general format is: SELECT what_to_select FROM which_table WHERE conditions_to_satisfy
13 Selecting All Data ? The simplest form of SELECT retrieves everything from a table mysql> select * from pet; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1999-02-04 | NULL | | Claws | Gwen | cat | f | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1999-08-27 | NULL | | Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+--------+---------+------+------------+------------+ 8 rows in set (0.00 sec)
14 Selecting Particular Rows ? You can select only particular rows from your table. ? For example, if you want to verify the change that you made to Bowser's birth date, select Bowser's record like this: mysql> SELECT * FROM pet WHERE name = "Bowser"; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 1 row in set (0.00 sec)
15 Selecting Particular Rows ? To find all animals born after 1998 SELECT * FROM pet WHERE birth >= "1998-1-1"; ? To find all female dogs, use a logical AND SELECT * FROM pet WHERE species = "dog" AND sex = "f"; ? To find all snakes or birds, use a logical OR SELECT * FROM pet WHERE species = "snake" OR species = "bird";
16 Selecting Particular Columns ? If you don¡¯t want to see entire rows from your table, just name the columns in which you are interested, separated by commas. ? For example, if you want to know when your pets were born, select the name and birth columns. ? (see example next slide.)
17 Selecting Particular Columns mysql> select name, birth from pet; +----------+------------+ | name | birth | +----------+------------+ | Fluffy | 1999-02-04 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | | Fang | 1999-08-27 | | Bowser | 1998-08-31 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | +----------+------------+ 8 rows in set (0.01 sec)
18 Sorting Data ? To sort a result, use an ORDER BY clause. ? For example, to view animal birthdays, sorted by date: mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ | name | birth | +----------+------------+ | Buffy | 1989-05-13 | | Claws | 1994-03-17 | | Slim | 1996-04-29 | | Whistler | 1997-12-09 | | Bowser | 1998-08-31 | | Chirpy | 1998-09-11 | | Fluffy | 1999-02-04 | | Fang | 1999-08-27 | +----------+------------+ 8 rows in set (0.02 sec)
19 Sorting Data ? To sort in reverse order, add the DESC (descending keyword) mysql> SELECT name, birth FROM pet ORDER BY birth DESC; +----------+------------+ | name | birth | +----------+------------+ | Fang | 1999-08-27 | | Fluffy | 1999-02-04 | | Chirpy | 1998-09-11 | | Bowser | 1998-08-31 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | +----------+------------+ 8 rows in set (0.02 sec)
20 Working with NULLs ? NULL means missing value or unknown value. ? To test for NULL, you cannot use the arithmetic comparison operators, such as =, < or <>. ? Rather, you must use the IS NULL and IS NOT NULL operators instead.
21 Working with NULLs ? For example, to find all your dead pets (what a morbid example!) mysql> select name from pet where death >IS NOT NULL; +--------+ | name | +--------+ | Bowser | +--------+ 1 row in set (0.01 sec)
22 Counting Rows Example ? A query to determine total number of pets: mysql> SELECT COUNT(*) FROM pet; +----------+ | COUNT(*) | +----------+ | 9 | +----------+

More Related Content

Intro to my sql

  • 2. 2 Road Map ? Introduction to MySQL ? Connecting and Disconnecting ? Entering Basic Queries ? Creating and Using a Database
  • 3. 3 MySQL ? MySQL is a very popular, open source database. ? Officially pronounced ¡°my Ess Que Ell¡± (not my sequel). ? Handles very large databases; very fast performance. ? Why are we using MySQL? ? Free (much cheaper than Oracle!) ? Each student can install MySQL locally. ? Easy to use Shell for creating tables, querying tables, etc. ? Easy to use with Java JDBC
  • 4. 4 Using a Database ? To create a new database, issue the ¡°create database¡± command: ? mysql> create database webdb; ? CREATE DATABASE bookinfo;
  • 5. 5 Creating a Table ? Once you have selected a database, you can view all database tables: mysql> show tables; Empty set (0.02 sec) ? An empty set indicates that I have not created any tables yet.
  • 6. 6 Creating a Table ? Let¡¯s create a table for storing pets. ? Table: pets ?ID: INT(8) ?name: VARCHAR(20) ?owner: VARCHAR(20) ?species: VARCHAR(20) ?sex: CHAR(1) ?birth: DATE ?date: DATE ?Primary Key (ID); VARCHAR is usually used to store string data.
  • 7. 7 Creating a Table ? To create a table, use the CREATE TABLE command: mysql> CREATE TABLE pet ( -> name VARCHAR(20), -> owner VARCHAR(20), -> species VARCHAR(20), -> sex CHAR(1), -> birth DATE, death DATE); Query OK, 0 rows affected (0.04 sec)
  • 8. 8 Showing Tables ? To verify that the table has been created: mysql> show tables; +------------------+ | Tables_in_test | +------------------+ | pet | +------------------+ 1 row in set (0.01 sec)
  • 9. 9 Deleting a Table ? To delete an entire table, use the DROP TABLE command: mysql> drop table pet; Query OK, 0 rows affected (0.02 sec)
  • 10. 10 Loading Data ? Use the INSERT statement to enter data into a table. ? For example: INSERT INTO pet VALUES ('Fluffy','Harold','cat','f', '1999-02-04',NULL); ? The next slide shows a full set of sample data.
  • 11. 11 More data¡­ name owner species sex birth death Fluffy Harold cat f 1993-02-04 Claws Gwen cat m 1994-03-17 Buffy Harold dog f 1989-05-13 Fang Benny dog m 1990-08-27 Bowser Diane dog m 1998-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 Whistler Gwen bird 1997-12-09 Slim Benny snake m 1996-04-29
  • 12. 12 SQL Select ? The SELECT statement is used to pull information from a table. ? The general format is: SELECT what_to_select FROM which_table WHERE conditions_to_satisfy
  • 13. 13 Selecting All Data ? The simplest form of SELECT retrieves everything from a table mysql> select * from pet; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1999-02-04 | NULL | | Claws | Gwen | cat | f | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1999-08-27 | NULL | | Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+--------+---------+------+------------+------------+ 8 rows in set (0.00 sec)
  • 14. 14 Selecting Particular Rows ? You can select only particular rows from your table. ? For example, if you want to verify the change that you made to Bowser's birth date, select Bowser's record like this: mysql> SELECT * FROM pet WHERE name = "Bowser"; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 1 row in set (0.00 sec)
  • 15. 15 Selecting Particular Rows ? To find all animals born after 1998 SELECT * FROM pet WHERE birth >= "1998-1-1"; ? To find all female dogs, use a logical AND SELECT * FROM pet WHERE species = "dog" AND sex = "f"; ? To find all snakes or birds, use a logical OR SELECT * FROM pet WHERE species = "snake" OR species = "bird";
  • 16. 16 Selecting Particular Columns ? If you don¡¯t want to see entire rows from your table, just name the columns in which you are interested, separated by commas. ? For example, if you want to know when your pets were born, select the name and birth columns. ? (see example next slide.)
  • 17. 17 Selecting Particular Columns mysql> select name, birth from pet; +----------+------------+ | name | birth | +----------+------------+ | Fluffy | 1999-02-04 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | | Fang | 1999-08-27 | | Bowser | 1998-08-31 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | +----------+------------+ 8 rows in set (0.01 sec)
  • 18. 18 Sorting Data ? To sort a result, use an ORDER BY clause. ? For example, to view animal birthdays, sorted by date: mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ | name | birth | +----------+------------+ | Buffy | 1989-05-13 | | Claws | 1994-03-17 | | Slim | 1996-04-29 | | Whistler | 1997-12-09 | | Bowser | 1998-08-31 | | Chirpy | 1998-09-11 | | Fluffy | 1999-02-04 | | Fang | 1999-08-27 | +----------+------------+ 8 rows in set (0.02 sec)
  • 19. 19 Sorting Data ? To sort in reverse order, add the DESC (descending keyword) mysql> SELECT name, birth FROM pet ORDER BY birth DESC; +----------+------------+ | name | birth | +----------+------------+ | Fang | 1999-08-27 | | Fluffy | 1999-02-04 | | Chirpy | 1998-09-11 | | Bowser | 1998-08-31 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | +----------+------------+ 8 rows in set (0.02 sec)
  • 20. 20 Working with NULLs ? NULL means missing value or unknown value. ? To test for NULL, you cannot use the arithmetic comparison operators, such as =, < or <>. ? Rather, you must use the IS NULL and IS NOT NULL operators instead.
  • 21. 21 Working with NULLs ? For example, to find all your dead pets (what a morbid example!) mysql> select name from pet where death >IS NOT NULL; +--------+ | name | +--------+ | Bowser | +--------+ 1 row in set (0.01 sec)
  • 22. 22 Counting Rows Example ? A query to determine total number of pets: mysql> SELECT COUNT(*) FROM pet; +----------+ | COUNT(*) | +----------+ | 9 | +----------+