Misc Client Commands
[this section added 2018-07]
Connecting from Windows MySQL 64 bit client,
To note, I changed prompt symbol ‘>’ to ‘$’ in my powershell. This will appear across the post.
In Powershell,
$ $Env:Path += 'C:\Program Files\MySQL\MySQL Server 8.0\bin;'
There are some articles showing how to do it with MySQL Workbench. Here’s an example of connecting to a remote Azure MySQL Database using MySQL cmd client,
$ mysql -p -h testaz-mysql-server.mysql.database.azure.com -u mysqluser@testaz-mysql-server fundb
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 65017
Server version: 5.6.39.0 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
The usual export command,
$ mysqldump -p -h testaz-mysql-server.mysql.database.azure.com -u mysqluser@testaz-mysql-server fundb > db-export.sql
The import command,
$ mysql -p -h testaz-mysql-server.mysql.database.azure.com -u mysqluser@testaz-mysql-server fundb < db-export.sql
List mysql users,
mysql> select user from mysql.user; +-----------------+ | user | +-----------------+ | fun-admin | | azure_superuser | | azure_superuser | | fun-user | | mysql.session | | mysql.sys | +-----------------+ 6 rows in set (0.03 sec)
Create new MySQL user,
CREATE USER 'comedy_user'@'testaz-mysql-server' IDENTIFIED BY 'comedy_is_fun_anymore';
Reset password for user [1],
Set Password for 'fun_user'@'testaz-mysql-server' = 'testazpass';
Grant access to a user,
GRANT ALL PRIVILEGES ON fundb.* TO 'fun_user'@'testaz-mysql-server' WITH GRANT OPTION;
Shows grant for specific user,
mysql> SHOW grants for fun-user@localhost; +--------------------------------------------------------------------------------+ | Grants for fun-user@localhost | +--------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'fun-user'@'localhost' | | GRANT ALL PRIVILEGES ON `bnblog`.* TO 'fun-user'@'localhost' WITH GRANT OPTION | +--------------------------------------------------------------------------------+ 2 rows in set (0.02 sec) mysql> SHOW grants for 'fun-user'@'testaz-mysql-server'; +------------------------------------------------------------------------------------------+ | Grants for fun-user@testaz-mysql-server | +------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'fun-user'@'testaz-mysql-server' | | GRANT ALL PRIVILEGES ON `bnblog`.* TO 'fun-user'@'testaz-mysql-server' WITH GRANT OPTION | +------------------------------------------------------------------------------------------+ 2 rows in set (0.02 sec)
Database Commands
Drop DB and Create DB,
mysql> DROP database fundb; Query OK, 12 rows affected (4.15 sec) mysql> create database fundb; Query OK, 1 row affected (0.26 sec)
Creating Tables
An example of creating table named Words,
create table Words (WordAlias varchar(50), WordSerial tinyint, PartsOfSpeech varchar(30), Meaning varchar(1000), UseInSentence varchar(200), BPron varchar(30), BAntonym varchar(100), EngMeaning varchar(), primary key(customer_name) );
An example of creating table named customer,
create table Words (customer_id tinyint, customer_name varchar(50), customer_street varchar(200), customer_city varchar(30), primary key(customer_id) );
Example of Creating a table named 'visitor',
create table bvisitor (userid varchar(30) not null, userfullname varchar(120) not null, useremail varchar(120) not null, totpageload int default 0, unipageload int default 0, cookie char(100), primary key(userid) );
Let's create a few tables: worker, building, assignment for exercise,
create table worker( worker_id int(10) not null, worker_name varchar(20), hourly_wage float(5,2), skill_type varchar(25), constraint w_pk primary key (worker_id), constraint hw_chk check (hourly_wage>10) );
create table building ( building_id int(10) not null, address varchar(50) not null, building_type varchar(15) not null, constraint b_pk primary key (building_id) ); create table assignment ( worker_id int(10) not null, building_id int(10) not null, start_date date, constraint w_fk foreign key (worker_id) references worker(worker_id), constraint b_fk foreign key (building_id) references building(building_id) );
We will insert data into some of these tables on 'inserting data' section.
Applying constraints
Apply on table 'building',
alter table building modify column building_id varchar(10) not null;
Apply on table 'assignment',
alter table assignment modify column building_id varchar(10) not null;
Misc DB Queries
How to know the name of database I'm using currently,
SELECT DATABASE() FROM DUAL;
Working With Views
To see details no a view,
desc viewName;
To remove a view,
drop view all_customer;
Inserting Data into Table
An example of inserting data into customer table that has been created in above section,
insert into customer
(customer_id, customer_name, customer_street, customer_city)
values (100, 'Nazrul', '600 Kakrail Avenue', 'Dhaka');
Inserting data in worker, building etc tables that have been created above,
insert into worker
values(1, 'Moznu', 20, 'Farmer');
insert into worker
values(2, 'Karim', 25, 'Drawing');
insert into worker
values(3, 'Dirham', 30, 'Carpenter');
insert into worker
values(4, 'Laili', 25, 'Embroidary');
insert into worker
values(5, 'Badhon', 50, 'Artist');
insert into building
values('B101', '23/1 Motijheel', 'Hospital');
insert into building
values('B102', '123/1/1 Rajabazar', 'Warehouse');
insert into building
values('B103', '100 GPO', 'Office');
insert into building
values('B104', '24/1 Motijheel', 'Hospital');
insert into building
values('B105', '25/1 Gulistan', 'Hospital');
insert into assignment
values('1', 'B101', '1999-12-10');
insert into assignment
values('2', 'B102', '2000-12-10');
insert into assignment
values('3', 'B105', '1998-11-10');
insert into assignment
values('4', 'B103', '1999-01-10');
insert into assignment
values('5', 'B104', '2001-01-10');
select skill_type
from worker natural join assignment
where worker.worker_id = assignment.worker_id and
building_id='B102';
Modifying Table Schema
A demo to drop a column/field from this table,
alter table customer drop column cusomer_name;
A demo to add a column/field,
alter table customer add column customer_name char(20);
Using command lines like this we can initialize a database, create tables, insert all data using script.
Query Examples
Find the name, street and city of the customer whose account number is A-102,
More background info:
– depositor table has the accounts info including account_no
– customer table does not have the account_no, however has customer_name etc..
We reach the final query in a step by step query making,
First, we try to find all customers with that account number 'A-102',
select * from customer, depositor where account_no='A-102'; select * from customer, depositor where account_no='A-102' and customer.customer_name=depositor.customer_name; select customer.customer_name, customer_street, customer_city from customer, depositor where account_no='A-102' and customer.customer_name=depositor.customer_name;
Find customer names who have account in the bank,
select customer_name
from depositor;
To eliminate duplicate entries,
select distinct customer_name
from depositor;
Find customer names who have loans in the bank,
select customer_name
from borrower;
Find all customer names who have account or loan or both in the bank,
(select customer_name
from depositor)
union
(select customer_name
from borrower);
Include duplicates,
(select customer_name
from depositor)
union all
(select customer_name
from borrower);
Find all customer names who have both account and loan in the bank,
select distinct depositor.customer_name
from depositor, borrower
where depositor.customer_name = borrower.customer_name;
select tuples from loan relation whose branch is Perryridge,
select *
from loan;
select *
from loan
where branch_name = 'Perryridge';
Find customer name who has accounts in the bank but does not have a loan in the bank,
(select distinct customer_name
from depositor)
minus
(select customer_name
from borrower);
List all loan numbers and amounts of loan,
select loan_no, amount
from loan;
Find all customer name whose city is Harrison,
select *
from customer;
select *
from customer
where customer_city = 'Harrison';
Find average account balances in Perryridge branch from account relation,
select avg(balance)
from account
where branch_name='Perryridge';
inserting a new record into account table,
insert into account
(account_no, branch_name, balance)
values('A-420', 'Perryridge', 500);
or,
insert into account
values('A-420', 'Perryridge', 500);
deleting a record from account table,
delete from account
where account_no = 'A-420';
Find the branch which has lowest asset to fire out,
select min(assets)
from branch;
select max(assets)
from branch;
insert into branch
values('ARstall', 'Dhaka', '9000000');
select max(assets)
from branch;
max shows only one output even if more than one tuple has max,
delete from branch
where branch_name='ARstall';
Let's also show branch name,
select branch_name
from (select max(assets)
from branch) as max;
Find the branches who have assets at least more than one branch located in Brooklyn,
select T.branch_name, T.assets from branch as T, branch as S where T.assets > S.assets and S.branch_city='Brooklyn';
We can do this using set comparison,
select branch_name, assets
from branch
where assets> some (select assets
from branch
where branch_name='Brooklyn');
Find customers who live in customer street where their names an*,
select customer_name, customer_street
from customer
where customer_street like '%a%n%';
Find customer whose customer street names have exactly 4 characters,
select customer_name, customer_street
from customer
where customer_street like '____';
Find the customers who have both loan and account in the Downtown branch,
(select distinct customer_name
from account, depositor
where account.account_no = depositor.account_no and branch_name='Perryridge')
union
(select distinct customer_name
from loan, borrower
where loan.loan_no = borrower.loan_no and branch_name = 'Perryridge')
select *
from loan
order by amount desc, loan_no asc;
Similarly, another query stands out,
select *
from loan
order by amount desc, loan_no;
select branch_name, max(balance)
from account
group by branch_name;
Find depositors in each branch grouped,
select customer_name, branch_name
from account, depositor
where depositor.account_no = account.account_no
group by branch_name;
Then, we find the number of depositors in each branch,
select branch_name, count(distinct customer_name)
from account, depositor
where (customer_name, branch_name) in
(select customer_name, branch_name
from account, depositor
where depositor.account_no = account.account_no
group by branch_name)
group by branch_name;
Find the average balance in each branch,
select branch_name, avg(balance)
from account
group by branch_name;
count the depositors in each branch,
select branch_name, count(distinct customer_name)
from account, depositor
where account.account_no= depositor.account_no
group by branch_name;
find the branches who have average balance more than 500,
select branch_name, avg(balance) from account group by branch_name having avg(balance) > 500;
count the number of tuples in customer,
select count(*)
from customer;
Find the average balance of customers who has accounts in Brighton,
select customer_name, avg(balance)
from account, depositor
where account.account_no = depositor.account_no and branch_name = 'Brighton'
group by customer_name;
select customer_name
from customer
where customer.customer_city = 'Harrison';
select customer.customer_name, avg(balance)
from customer, depositor, account
where customer.customer_name=depositor.customer_name and
account.account_no=depositor.account_no and
customer.customer_city = 'Harrison'
group by customer_name
having count(distinct depositor.account_no) > 2;
Find the loan_no which does have amounts,
select loan_no
from loan
where amount is not null;
Arrange in assending, descending.. order,
select branch_name, balance, account_no
from account
order by balance, account_no;
Find the maximum balances of branches,
select branch_name, max(balance)
from account
group by branch_name;
Find the branch with maximum balance,
select branch_name, assets
from branch
where assets in (select max(assets)
from branch);
select branch_name, assets
from branch
having assets>= all (select assets
from branch);
select branch_name, assets
from branch
where assets = (select max(assets)
from branch);
Find the customers who have both loans and account in the bank,
select customer_name
from depositor
where customer_name in
(select customer_name
from borrower);
Find the customers who have accounts but no loans in the bank,
select distinct customer_name
from depositor
where customer_name not in
(select customer_name
from borrower);
Find the depositors in Perryridge branch,
select customer_name
from account, depositor
where account.account_no=depositor.account_no and
branch_name = 'Perryridge';
Find the depositors who have loans in Perryridge branch,
select customer_name
from loan, borrower
where loan.loan_no=borrower.loan_no and
branch_name = 'Perryridge';
Then, find all customers who have both loans and accounts in 'Perryridge' branch using set intersection examples below,
select customer_name
from account, depositor
where account.account_no=depositor.account_no and
branch_name = 'Perryridge' and customer_name in (select customer_name
from loan, borrower
where loan.loan_no=borrower.loan_no and
branch_name = 'Perryridge');
select distinct customer_name
from account, depositor
where account.account_no = depositor.account_no and
customer_name not in ('Hayes', 'Blair');
creating view with all customers who have account or loan in the bank,
create view all_customer as
(select customer_name
from depositor)
union (select customer_name
from borrower);
select d_CN
from (depositor left outer join borrower
on depositor.customer_name=borrower.customer_name)
as db1 (d_CN, account_no, b_CN, loan_no)
where b_CN is null;
Natural join automatically matches the primary key if available,
select *
from worker natural join assignment
where worker.worker_id = assignment.worker_id;
select *
from worker, assignment
where worker.worker_id = assignment.worker_id;
Find the branch that has the highest average balance,
select branch_name, avg(balance)
from account
group by branch_name;
select branch_name, avg(balance)
from account
group by branch_name
having avg(balance) >= all (select avg(balance)
from account
group by branch_name);
create view with the average loan amount in each branch along with the branch_name,
create view avgloan as
select branch_name, avg(amount) as avg_amount
from loan
group by branch_name;
select * from avgloan;
Now find the branch having max amount,
select branch_name
from avgloan
where avg_amount = (select max(avg_amount)
from avgloan);
The Learning Machine 