DB2017-02 Pre-class materials

From Innovation
Jump to: navigation, search


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 https://dev.mysql.com/downloads/mysql/ download and install

add the directory to the PATH environment

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

 
vi ~/.bash_profile
PATH=/usr/local/mysql/bin:$PATH&

To use the path environment, you have to relaunch the terminal or you can run the following line export PATH="/usr/local/mysql/bin:$PATH" in the command line.

Then, source ~/.bash_profile

To stop or start mysql service

stop, start, restart

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

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

Change the MySQL root password through safe mode

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

> mysql -u root 
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY ‘YOURPASSWORD’;

to start the mysql server with changed password - sudo /usr/local/mysql/support-files/mysql.server start

Know that there are more steps to configure the mysql service

alternate way: safe mode

  • Start up safe mode sudo mysqld_safe --skip-grant-tables
  • Log into MySQL as root: mysql -u root -p
  • Change to the mysql database, which handles the settings for MySQL itself: use mysql;
  • Update the password for the root user: update user set password=PASSWORD("the new password you want to use") where User='root';
  • Refresh the MySQL user privileges: flush privileges;
  • Exit MySQL: exit
  • 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: sudo /usr/local/mysql/support-files/mysql.server start To find the version of the mysql /usr/local/mysql/bin/mysql -v -uroot -p; it asks for the password; use the password as you have typed in. 

> 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>

to create user

log in as root 

> mysql -u root -p
flush privileges;
create user ‘username’@‘localhost’ identified by ‘PASSWORD’;

to grant privileges

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

grant all privileges on *.* to ‘username’@‘localhost’; 
flush privileges;

type the following line to finish the current mysql session

\q


to log in

mysql -u USERNAME -p

to create database

create database `class-db`

to use database

use databasename;

to see what tables are in the database

show tables;

to see the schema of a table

describe TABLENAME


to create table and Insert Rows into the table

Account

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');

Teacher

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');

Course

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');

Student

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);

Faculty

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);

Customer

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);

Salesperson

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);

Checking

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);

Savings

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);

to drop table

drop table `TABLENAME`;

to insert, update, and delete rows in a table

to insert rows

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');

to update a row

UPDATE table name
SET column1 = value1, column2 = value2
WHERE condition;

to delete a row

delete from table name
where condition;
Retrieved from "http://open.gnu.ac.kr/mediawiki/index.php?title=DB2017-02_Pre-class_materials&oldid=267"