sql statements and use within rmysql

sql_statement_rmysql.txt

brew install mysql


show databases;
exit;

mysql -u root -p
UPDATE mysql.user SET Password = PASSWORD('foo.bar') WHERE User = 'root';
FLUSH  PRIVILEGES;

CREATE USER web@localhost;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, FILE, INDEX, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON *.* TO web@localhost;
GRANT ALL on *.* TO admin@localhost WITH GRANT OPTION;
FLUSH PRIVILEGES;
UPDATE mysql.user SET PASSWORD = PASSWORD('foo.baz') WHERE User = 'admin'

mysql -u admin -p < world-mysql.sql
mysql -u admin -p
use test;
show tables;
select * from item;
select count(*) from City;
quit

CREATE DATABASE sales;
SHOW DATABASES;
DROP DATABASE sales;

use test;
CREATE TABLE test_customer(
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  address VARCHAR(255),
  city VARCHAR (255),
  state CHAR(2),
  zip CHAR(10)
);
show tables;
select * from test_customer;
select count(*) from test_customer;
drop table test_customer;

use test;
show tables;
describe numerics;
select * from numerics;
select da, db, da + db from numerics;
select da, db, da + db = .3 from numerics;
select fa, fb, fa + fb = .3 from numerics;
select fa, fb, fa + fb from numerics;

use album;
show tables;
describe album;

use test;
drop table datetest;
create table datetest (
  date datetime,
  stamp timestamp
);
insert into datetest ( date ) values ('2009-05-04 15:31:29');
select date, stamp, datediff (date, stamp) from datetest;

use test;
create table bittest (
  b1 bit(8),
  b2 bit(10)
);
insert into bittest (b1, b2) values (b'11110000', b'01001');
select b1 + 0, b2 + 0 from bittest;
select bin(b1), oct(b1), hex(b1) from bittest;

select 5 = 5;
select 5 = 7;

use test;
create table enumtest(
  color enum ('red', 'blue', 'green')
);
describe enumtest;
insert into enumtest (color) values ('red');
insert into enumtest (color) values ('green');
insert into enumtest (color) values ('blue');
insert into enumtest (color) values ('orange');
select * from enumtest;
select count(*) from enumtest;
create table settest(
  attrib set ('bold', 'italic', 'underline')
);
insert into settest (attrib) values ('bold');
insert into settest (attrib) values ('bold,italic');
insert into settest (attrib) values ('bold,underline');
insert into settest (attrib) values ('underline,italic');
insert into settest (attrib) values ('underline,bold');
select * from settest;
describe settest;

use world;
show tables;
select count(*) from city;

select 'hello, world';
select concat('hello, world');

use album;
select title from album;

install.packages("RMySQL")
library(RMySQL)
m <- dbDriver("MySQL")
album <- dbConnect(m, user='admin', password = 'abdmijn4$', dbname = 'album')
title_album <- dbSendQuery(album, "select title from album")
a <- fetch(title_album, n = -1)
a