How to give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs
# mysql -u root -p
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’databasename’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);
mysql> flush privileges;
or
mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges
How to show all records starting with the letters ‘sonia’ AND the phone number ‘9876543210’ limit to records 1 through 5.
mysql> SELECT * FROM tablename WHERE name like “sonia%” AND phone_number = ‘9876543210’ limit 1,5;
How to make a column bigger and Delete unique from table.
mysql> alter table [table name] modify [column name] VARCHAR(3);
mysql> alter table [table name] drop index [colmn name];
How to Change a users password from unix shell
# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’
How to Create Table show Example
mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
How Switch (select or use) to a database.
mysql> use databasename;
How to returns the columns and column information pertaining to the designated table
mysql> show columns from tablename;
How you will Show unique records.
mysql> SELECT DISTINCT columnname FROM tablename;
How to Load a CSV file into a table
mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘n’ (field1,field2,field3);
How to Change a users password from MySQL prompt. Login as root. Set the password. Update privs.
# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
mysql> flush privileges;