Database Day 03: MySQL in Ubuntu

Create a user at localhost
create user 'david'@'localhost' identified by 'password';

Grant permissions to access all databases and all tables for the user "david"
grant all on *.* to 'david'@'localhost' identified by 'password'; 
SET PASSWORD FOR 'username'@'localhost'=PASSWORD('mynewpassword');

SET PASSWORD FOR 'username'@'hostname'=PASSWORD('mynewpassword');

List all databases managed by the server

show databases;

Work on database test

use test

Display all tables in database test

show tables;

Describe a table in the current database test

describe tablename;

Create table users in the database test
use test;
create table users (users_id INT unsigned NOT NULL AUTO_INCREMENT, users_name VARCHAR(50) NOT NULL, users_email VARCHAR(50) NOT NULL, users_datejoined DATETIME NOT NULL, PRIMARY KEY (users_id));

Add another column to the table "users" after the "users_email" column
ALTER TABLE users ADD users_pass CHAR(40) NOT NULL AFTER users_email;

Insert a new record to the table users
INSERT INTO users (users_name, users_email, users_pass, users_datejoined) VALUES ('John Smith','johnsmith@abc.com', SHA1('mypassword'), NOW());

Insert another record to the table users using the second method
INSERT INTO users VALUES (NULL, 'Peter Jones','peterjones@fakeemail.com', SHA1('password'), NOW());

Show all the records in the table "users"
USE test;
SELECT * from users;

Show all the records in the table "users"
USE test;
SELECT users_name from users;

Show all the records in the table "users"
USE test;
SELECT users_name, users_email from users;

Display the current date and time
SELECT NOW();

Show the user that has the users_id = 5
SELECT users_name FROM users WHERE users_id = 5;

Show users that have the users_id > 3
SELECT users_name FROM users WHERE users_id >3;

Show users that have the users_id between 3 and 10
SELECT users_name FROM users WHERE users_id BETWEEN 3 AND 10;


Show users that have the users_id between 3 and 10 and users_post > 10
SELECT users_name FROM users WHERE (users_id BETWEEN 3 AND 10) AND (users_post  > 10);

Run mysql script to create and populate database
mysql -u root -p
source c:\mysqlstatements.sql