Transcript Managing Databases and Tables

Chapter 5
MYSQL Database
Introduction to MYSQL
MySQL is the world's most popular open-source database.
Open source means that the source code, the programming
code that makes up MySQL, is freely available to anyone.
People all over the world can add to MySQL, fix bugs, make
improvements, or suggest optimizations.
It's grown so quickly because of the countless dedicated
people who have contributed to the project in some way, as
well as the dedication of the MySQL team.
MySQL is faster, more reliable, and cheaper—or, simply put,
better—than any other database system (including commercial
systems such as Oracle and DB2)
Case Sensitivity
SQL keywords are not case sensitive. This is standard across
database systems.
In MySQL, whether database and table names are case
sensitive depends on your operating system.
If you are using Windows, database and table names are not
case sensitive, but if you are using a Unix-like operating
system, they are.
it is good practice to treat all identifiers as case sensitive, even
if you are working on a Windows system
Column names, indexes, and aliases are never case sensitive
in MySQL.
MySQL Data Types
MySQL uses many different data types, which are broken into
three categories: numeric, date and time, and string types.
1.) Numeric Data Types
MySQL Data Types
2.) Date and Time Types
MySQL Data Types
2.) String Types
Options and Attributes
A variety of options and additional attributes can be specified
when a column is created.
Managing Databases and Tables
1.) Logging on to MYSQL
Fire up the mysql client by issuing the following command at your command
prompt:
> mysql –u USER –p PASSWORD
For example, if your database username and password are "phpuser" and
"phppass“. The command would look like this:
>mysql –u phpuser –p
>enter password:phppass
2.) Creating database
The CREATE DATABASE statement can create a new, empty database
without any tables or data.
The following statement creates a database called employee:
mysql> CREATE DATABASE employee;
3.) Selecting Databases
The USE statement selects a database to make it the default (current)
database for a given connection to the server:
USE db_name;
Example : mysql> use employee;
Managing Databases and Tables
4.) Dropping Databases
Dropping a database is just as easy as creating one, assuming that you
have sufficient privileges:
DROP DATABASE db_name;
Example : mysql> DROP DATABASE employee;
5.) Creating Tables
To create the tables in the employee database, we use the CREATE
TABLE SQL statement. The usual form of this statement is:
create table tablename ( table definition ) [ENGINE=table_type];
create table department(
departmentID int not null auto_increment primary key,
name varchar(20)
)ENGINE=InnoDB;
Managing Databases and Tables
create table employee(
employeeID int not null auto_increment primary key,
name varchar(80),
job varchar(15),
departmentID int not null,
Foreign key (departmentID) references department(departmentID)
) ENGINE=InnoDB;
6.) Dropping Tables
Dropping a table is much easier than creating it because you don't have to
specify anything about its contents. You just have to name it:
DROP TABLE tbl_name;
You can drop several tables by specifying them all on the same statement:
DROP TABLE tbl_name1, tbl_name2, ... ;
example: mysql> drop table employee;
Managing Databases and Tables
7.) Altering Table Structure
You can also use ALTER TABLE to rename tables, add or
drop columns, change column data types,
A.) Changing a column's data type
To change a data type, you can use either a CHANGE or MODIFY
clause. Example:
ALTER TABLE mytbl MODIFY i MEDIUMINT UNSIGNED;
ALTER TABLE mytbl CHANGE i i MEDIUMINT UNSIGNED;
Why is the column named twice in the command that uses CHANGE?
Because one thing that CHANGE can do that MODIFY cannot is to
rename the column in addition to changing the type. If you had
wanted to rename i to j at the same time you changed the type, you'd
do so like this:
ALTER TABLE mytbl CHANGE i j MEDIUMINT UNSIGNED;
Managing Databases and Tables
7.) Altering Table Structure
You can also use ALTER TABLE to rename tables, add or
drop columns, change column data types.
B.) Adding a column to the table
To add a column, you can use ADD clause:
ADD [COLUMN] col_definition [FIRST | AFTER col_name]
Example:
ALTER TABLE tblstudent ADD sex enum(‘F’,’M’);
ALTER TABLE tblstudent add phone char(20) after sex;
C.) Deleting any column from the table
To delete any column, you use DROP clause:
DROP [COLUMN] col_name
example:
ALTER TABLE tblstudent DROP sex;
Managing Databases and Tables
7.) Altering Table Structure
You can also use ALTER TABLE to rename tables, add or
drop columns, change column data types,
D.) Renaming a table
Use a RENAME clause that specifies the new table name:
ALTER TABLE tbl_name RENAME TO new_tbl_name;
Another way to rename tables is with RENAME TABLE. The syntax looks
like this:
RENAME TABLE old_name TO new_name;
Note:
One thing that RENAME TABLE can do that ALTER TABLE cannot is
rename multiple tables in the same statement. For example, you can
swap the names of two tables like this:
RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2;
Example:
ALTER TABLE sampdb.tbl RENAME TO sampdb.tbl1;
RENAME TABLE sampdb.tbl TO sampdb.tbl1;
Selecting, Inserting, Deleting, and Updating Data
1.) Using INSERT
The INSERT SQL statement is used to insert rows into a table.
Some sample insert statements are shown below:
use employee;
delete from department;
insert into department values(42, 'Finance'),
(128, 'Research and Development'),
(NULL, 'Human Resources'),
(NULL, 'Marketing');
2.) Using DELETE
The DELETE SQL statement allows us to delete rows from a table. There
are some delete statements shown below:
delete from department;
delete from department where name='Asset Management' ;
Selecting, Inserting, Deleting, and Updating Data
3.) Using UPDATE
We can use the UPDATE SQL statement to change rows already stored in
the database. For example, imagine that one of our employees changes
jobs:
update employee set job='DBA‘ where employeeID='6651';
4.) Using SELECT
The SELECT statement has the following general form:
SELECT columns
FROM tables
[WHERE conditions]
[GROUP BY group
[HAVING group_conditions]]
[ORDER BY sort_columns]
[LIMIT limits];
Selecting, Inserting, Deleting, and Updating Data
Example of Select statement:
a.) select employeeID, name from employee
where job='Programmer';
b.) select distinct job from employee;
c.) select count(*), job from employee
group by job desc;
d.) select count(*), job from employee
group by job having count(*)=1;
e.) select * from employee
order by job asc, name desc;
f.) select * from employeeSkills limit 5;
g.) select * from employeeSkills limit 5, 3;
The end of Chapter 5
Thanks for your paying attention