How to dump a table from a database.
# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
How we get Sum of column
mysql> SELECT SUM(*) FROM [table name];
How to allow the user “sonia” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs
# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to sonia@localhost identified by ‘passwd’;
mysql> flush privileges;
How to Show all records starting with the letters ‘sonia’ AND the phone number ‘9876543210’
mysql> SELECT * FROM tablename WHERE name like “sonia%” AND phone_number = ‘9876543210’;
Change column name and Make a unique column so we get nodupes.
mysql> alter table [table name] change [old column name] [new column name] varchar (50);
mysql> alter table [table name] add unique ([column name]);
How to Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs
# mysql -u root -p
mysql> use mysql;
mysql>INSERTINTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));
mysql> flush privileges;
Restore database (or database table) from backup.
# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
how to list or view all databases from the mysql server.
mysql> show databases
How to delete a table
mysql> drop table tablename;
How you will Show all data from a table.
mysql> SELECT * FROM tablename