View source for DB2017-02 Pre-class materials



== Database install ==

you can use variety of databases; there are Mysql, Sqlite, Oracle, Redis, Cassandra, and many more.

If you want to use mysql, you need to download and install first. Mac OS X does not come with mysql or any other datatbases. MySQL :: Download MySQL Community Server <code>https://dev.mysql.com/downloads/mysql/</code> download and install

=== add the directory to the PATH environment ===

installed mysql file is in <code>/usr/local/mysql/bin</code> add this to your PATH in <code>.bash_profile</code> or <code>.bash</code> file

<source> 
vi ~/.bash_profile
PATH=/usr/local/mysql/bin:$PATH&
</source>
To use the path environment, you have to relaunch the terminal or you can run the following line <code>export PATH=&quot;/usr/local/mysql/bin:$PATH&quot;</code> in the command line.

Then, <code>source ~/.bash_profile</code>

== To stop or start mysql service ==

stop, start, restart 
* Ubuntu, Debian sudo service mysql stop/start/restart 
* Fedora, CentOS 6 sudo service mysqld <code>stop</code> 
* CentOS 7 sudo systemctl <code>stop</code> mariadb.service 
* Arch Linux sudo systemctl <code>stop</code> mysqld.service

check if mysql is running <code>sudo /usr/local/mysql/support-files/mysql.server stop</code>

=== Change the MySQL root password through safe mode ===

start the mysql in safe mode <code>sudo mysqld_safe --skip-grant-tables</code> type in the following in an other terminal

<source>
> mysql -u root 
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY ‘YOURPASSWORD’;
</source>
to start the mysql server with changed password - <code>sudo /usr/local/mysql/support-files/mysql.server start</code>

Know that there are more steps to configure the mysql service

=== alternate way: safe mode ===

* Start up safe mode <code>sudo mysqld_safe --skip-grant-tables</code>
* Log into MySQL as root: <code>mysql -u root -p</code>
* Change to the mysql database, which handles the settings for MySQL itself: <code>use mysql;</code>
* Update the password for the root user: <code>update user set password=PASSWORD(&quot;the new password you want to use&quot;) where User='root';</code>
* Refresh the MySQL user privileges: <code>flush privileges;</code>
* Exit MySQL: <code>exit</code>
* If this doesn’t work, you can try force the application to quit by pressing CTRL-C on your keyboard.

=== starting and configuring the mysql ===

To start: <code>sudo /usr/local/mysql/support-files/mysql.server start</code> To find the version of the mysql <code>/usr/local/mysql/bin/mysql -v -uroot -p</code>; it asks for the password; use the password as you have typed in.

<source>
> mysql -v -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Reading history-file /Users/James/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
</source>

= to create user =

log in as root

<pre>&gt; mysql -u root -p
flush privileges;
create user ‘username’@‘localhost’ identified by ‘PASSWORD’;</pre>
= to grant privileges =

all is for every command you can specify only the commands user can use. for example, create, select, alter, etc.

<pre>grant all privileges on *.* to ‘username’@‘localhost’; 
flush privileges;</pre>

= type the following line to finish the current mysql session =
<code>\q </code>



= to log in =

mysql -u USERNAME -p

== to create database ==

<pre>create database `class-db`</pre>

= to use database =

<code>use databasename;</code>

= to see what tables are in the database =

<code>show tables;</code> 

= to see the schema of a table =
<code>describe TABLENAME</code>


= to create table and Insert Rows into the table=
== Account ==
<source>
CREATE TABLE `Account` (
  `Number` int(7) NOT NULL,
  `Owner` varchar(50) NOT NULL,
  `Balance` int(10) NOT NULL,
  `Type` varchar(10) NOT NULL,
   PRIMARY KEY (`Number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DESCRIBE Account;

insert  into `Account`(`Number`,`Owner`,`Balance`,`Type`) values 
(7003001, 'Jane Smith', 1000000, 'Savings'),
(7003003, 'Alfred Hitchcock', 4400200, 'Savings'),
(7003005, 'Takumi Fujiwara', 2230000, 'Checking'),
(7003007, 'Brian Mills', 1200000, 'Savings');
</source>

== Teacher ==
<source>
CREATE TABLE `Teacher` (
  `TID` int(7) NOT NULL,
  `Tname` varchar(50) NOT NULL,
   PRIMARY KEY (`TID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert  into `Teacher`(`TID`,`Tname`) values 
(101, 'Emma Thompson'),
(105, 'Billy Elliot'),
(110, 'John Waine');
</source>

== Course==
<source>
CREATE TABLE `Course` (
  `CID` int(7) NOT NULL,
  `Cname` varchar(50) NOT NULL,
   PRIMARY KEY (`CID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert  into `Course`(`CID`,`Cname`) values 
(346, 'How to Act'),
(491, 'How to Think');
</source>

== Student==
<source>
CREATE TABLE `Student` (
  `sid` int(7) NOT NULL,
  `name` varchar(50) NOT NULL,
  `advisor` varchar(50) NOT NULL,
  `age` int(3) NOT NULL,
   PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert  into `Student`(`sid`,`name`,`advisor`,`age`) values 
(101, 'Bill', 301, 20),
(102, 'John', 302, 20),
(103, 'Edward', 301, 19),
(104, 'Albert', 301, 19),
(105, 'Thompson', 302, 19);
</source>

== Faculty==
<source>
CREATE TABLE `Faculty` (
  `fid` int(7) NOT NULL,
  `name` varchar(50) NOT NULL,
  `age` int(3) NOT NULL,
   PRIMARY KEY (`fid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert  into `Faculty`(`fid`,`name`,`age`) values 
(301, 'Morrison', 45),
(302, 'Groot', 37);
</source>

== Customer==
<source>
CREATE TABLE `Customer` (
  `Number` int(7) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Address` varchar(50) NOT NULL,
  `Crating` int(10) NOT NULL,
  `Camount` int(10) NOT NULL,
  `Cbalance` int(10) NOT NULL,
  `Salesperson` int(10),
   PRIMARY KEY (`Number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert  into `Customer`(`Number`,`Name`,`Address`,`Crating`, `Camount`, `Cbalance`, `Salesperson`) values 
(1, 'Smith', '1st str.', 700, 10000, 9000, 55),
(2, 'Jones', '2nd str.', 700, 8000, 4000, 77),
(3, 'Mills', '3rd str.', 700, 11000, 8000, NULL),
(4, 'Bill', '4th str.', 700, 13000, 5000, 55),
(5, 'Jane', '5th str.', 800, 3000, 3000, 55),
(6, 'Haley', '8th str.', 700, 2000, 8000, 20),
(7, 'Khale', '9th str.', 900, 6000, 1000, 77);
</source>

== Salesperson==
<source>
CREATE TABLE `Salesperson` (
  `Number` int(7) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Address` varchar(50) NOT NULL,
  `Office` int(5) NOT NULL,
   PRIMARY KEY (`Number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert  into `Salesperson` (`Number`,`Name`,`Address`,`Office`) values 
(55, 'Miller', '5th str.', 101),
(77, 'Khan', '7th str.', 102),
(83, 'Dunham', '8th str.', 103);
</source>

== Checking==
<source>
CREATE TABLE `Checking` (
  `CNum` int(7) NOT NULL,
  `COwner` varchar(50) NOT NULL,
  `CBalance` int(10) NOT NULL,
   PRIMARY KEY (`CNum`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert  into `Checking` (`CNum`,`COwner`,`CBalance`) values 
(101, 'Smith',  1000),
(102, 'Mills', 2000),
(104, 'Jones', 1000),
(105, 'Schwab', 3000);
</source>

== Savings==
<source>
CREATE TABLE `Savings` (
  `SNum` int(7) NOT NULL,
  `SOwner` varchar(50) NOT NULL,
  `SBalance` int(10) NOT NULL,
   PRIMARY KEY (`SNum`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert  into `Savings` (`SNum`,`SOwner`,`SBalance`) values 
(103, 'Smith',  1000);
</source>

== to drop table==
<source>
drop table `TABLENAME`;
</source>

= to insert, update, and delete rows in a table =
== to insert rows==
<source>
insert  into `Account`(`Number`,`Owner`,`Balance`,`Type`) values 
(7003001, 'Jane Smith', 1000000, 'Savings'),
(7003003, 'Alfred Hitchcock', 4400200, 'Savings'),
(7003005, 'Takumi Fujiwara', 2230000, 'Checking'),
(7003007, 'Brian Mills', 1200000, 'Savings');
</source>

== to update a row==
<source>
UPDATE table name
SET column1 = value1, column2 = value2
WHERE condition;
</source>

== to delete a row==
<source>
delete from table name
where condition;
</source>