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
