Manage MySQL Databases

Posted on Updated on

This scripts will create db, db user and a password

grant select, insert, update, delete, create, drop, index, alter, create temporary tables, lock tables ,CREATE VIEW,CREATE ROUTINE, EXECUTE, ALTER ROUTINE on slocal.* to dbslocal@localhost identified by ‘H2Zy#4W’;

grant all on <db>.* to <user>@”%” identified by ‘passwd’;

1. To login (from unix shell) use -h only if needed.

# [mysql dir]/bin/mysql -h hostname -u root -p

2. Create a database on the sql server.

mysql> create database [databasename];

3.List all databases on the sql server.

mysql> show databases;

4. Switch to a database.

mysql> use [db name];

5. To see all the tables in the db.

mysql> show tables;

6.To see database’s field formats.

mysql> describe [table name];

7.To delete a db.

mysql> drop database [database name];

8.To delete a table.

mysql> drop table [table name];

9.Show all data in a table.

mysql> SELECT * FROM [table name];

10.Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

11.Show certain selected rows with the value “whatever”.

mysql> SELECT * FROM [table name] WHERE [field name] = “whatever”;

12.Show all records containing the name “Bob” AND the phone number ‘3444444’.

mysql> SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ‘3444444’;

13.Show all records not containing the name “Bob” AND the phone number ‘3444444’ order by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ‘3444444’ order by phone_number;

14.Show all records starting with the letters ‘bob’ AND the phone number ‘3444444’.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ‘3444444’;

15.Show all records starting with the letters ‘bob’ AND the phone number ‘3444444’ limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ‘3444444’ limit 1,5;

16. Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE “^a”;

17. Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

18. Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

19. Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

20. Sum column.

mysql> SELECT SUM(*) FROM [table name];

21. Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on

22.lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s