James: /* To stop or start mysql service */

2018-01-18T23:20:02Z

To stop or start mysql service

← Older revision		Revision as of 08:20, 19 January 2018
Line 21:		Line 21:
	== To stop or start mysql service ==		== 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		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>		check if mysql is running <code>sudo /usr/local/mysql/support-files/mysql.server stop</code>

James: /* to create table and Insert Rows into the table */

2017-10-04T03:04:38Z

to create table and Insert Rows into the table

← Older revision		Revision as of 12:04, 4 October 2017
Line 262:		Line 262:
	</source>		</source>

			= to insert, update, and delete rows in a table =
	== to insert rows==		== to insert rows==
	<source>		<source>

James: /* database */

2017-10-04T03:03:24Z

database

← Older revision		Revision as of 12:03, 4 October 2017
Line 1:		Line 1:
	~~= database =~~

	== Database install ==		== Database install ==

James: Created page with "= database = == 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 t..."

2017-10-04T03:02:50Z

Created page with "= database = == 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 t..."

New page

= database =

== 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="/usr/local/mysql/bin:$PATH"</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("the new password you want to use") 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>> 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 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>

DB2017-02 Pre-class materials - Revision history

James: /* To stop or start mysql service */

James: /* to create table and Insert Rows into the table */

James: /* database */

James: Created page with "= database = == 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 t..."