SQL Notes

this file can be found at www.estss.com/opensource/cheatsheet.php


The following notes are based primarily on using MySQL directly.

==================================================

# just in case you forget the root password:
1. root_prompt> /usr/local/mysql/bin/safe_mysqld --skip-grant-tables &
2. root_prompt> /usr/local/mysql
3. mysql_root> USE mysql;
4. mysql_root> UPDATE user SET password=PASSWORD('.......') WHERE user='root' AND host='localhost';
3. mysql_root> FLUSH PRIVILEGES;
5. stop and restart MySQL server.

==================================================

http://dev.mysql.com/doc/mysql/en/grant.html
http://www.mysql.com/documentation/mysql/bychapter/manual_Privilege_system.html

# list all current users found in this mysql database
	mysql_prompt> SELECT Host, User FROM mysql.user;

# to get rid of anonymous access (not necessary)
	mysql_root> DELETE FROM user WHERE user = "";
# or
	mysql_root> DELETE FROM mysql.user WHERE Host='localhost' AND User='';

# nuke existing user
	mysql_root> DELETE FROM user WHERE user = "username_to_delete";

# replace root info to grant complete access
	mysql_root> REPLACE INTO user ( Host, User, Password,
					Select_priv, Insert_priv, Update_priv,
					Delete_priv, Create_priv, Drop_priv,
					Reload_priv, Shutdown_priv, Process_priv, File_priv,
					Grant_priv, References_priv, Index_priv, Alter_priv )
				VALUES ( 'localhost', 'root', password( '.......' ),
					'Y', 'Y', 'Y',
					'Y', 'Y', 'Y',
					'Y', 'Y', 'Y', 'Y',
					'Y', 'Y', 'Y', 'Y' );

	mysql_root> REPLACE INTO user ( Host, User, Password,
					Select_priv, Insert_priv, Update_priv,
					Delete_priv, Create_priv, Drop_priv,
					Reload_priv, Shutdown_priv, Process_priv, File_priv,
					Grant_priv, References_priv, Index_priv, Alter_priv )
				VALUES ( 'localhost.localdomain', 'root', password( '.......' ),
					'Y', 'Y', 'Y',
					'Y', 'Y', 'Y',
					'Y', 'Y', 'Y', 'Y',
					'Y', 'Y', 'Y', 'Y' );

==================================================

# to quickly fill in the database
	root_prompt> mysqladmin [options] create sampleDBname
	root_prompt> mysql mysql < createusers.sql

	root_prompt> mysql sampleDBname < createtable.sql
	prompt> mysql -u sampleDBuser -p sampleDBname < create_another_table.sql
	Enter password:
	prompt> mysql -u sampleDBuser -p sampleDBname < fill_in_table.sql
	Enter password:
	root_prompt> mysql sampleDBname < etc_etc_etc.sql


# ........................................
# ( note: REPLACE will INSERT if no entry matches )
# where createusers.sql might contain:
REPLACE INTO user ( host, user, password )
VALUES (
	'localhost',
	'sampleDBuser',
	password( 'sampleDBuserPassword' )
);

REPLACE INTO db ( host, db, user,
		select_priv, insert_priv, update_priv,
		delete_priv, create_priv, drop_priv )
VALUES (
	'localhost',
	'sampleDBname',
	'sampleDBuser',
	'Y', 'Y', 'Y',
	'Y', 'Y', 'Y'
);


# ........................................
# ( note: there is no "REPLACE" equivalent with "CREATE" TABLE )
# where createtable.sql might contain:
CONNECT sampleDBname;

# nuke existing table
DROP TABLE sampleDBtable;

CREATE TABLE sampleDBtable (
# database id
	id    int(11) not null auto_increment,

# sample db items
	title varchar(20),
	name  varchar(25) not null,
	yrs1  int(4), # begining term year
	yrs2  int(4), # ending yerm year
	notes text,

# database control stuff
	primary key (id)
);


==================================================

# to do this manually:

	mysql_root> CREATE DATABASE sampleDBname;
	mysql_root> REPLACE INTO user ( host, user, password )
			VALUES (
				'localhost',
				'sampleDBuser',
				password( 'sampleDBuserPassword' )
			);
	mysql_root> REPLACE INTO db ( host, db, user,
					select_priv, insert_priv, update_priv,
					delete_priv, create_priv, drop_priv )
			VALUES (
				'localhost',
				'sampleDBname',
				'sampleDBuser',
				'Y', 'Y', 'Y',
				'Y', 'Y', 'Y'
			);
	mysql_root> FLUSH PRIVILEGES;

	mysql_prompt> DROP sampleDBname sampleDBtable;
# -- or --
	mysql_prompt> USE sampleDBname;
	mysql_prompt> DROP TABLE sampleDBtable;

	mysql_prompt> USE sampleDBname;
	mysql_prompt> CREATE TABLE sampleDBtable ( ... see createtable.sql above ... );


==================================================

# remote access examples:

 1: Set up the 'host' table (assuming it has not yet been set up)

	mysql_root> INSERT INTO host ( host, db,
					Select_priv, Insert_priv, Update_priv,
					Delete_priv, Create_priv, Drop_priv)
# with these example entries:
			VALUES ('localhost','mydb','Y', 'Y', 'Y', 'Y', 'Y', 'Y');
			VALUES ('devshed','mydb','Y', 'Y', 'Y', 'Y', 'Y', 'Y');
			VALUES ('localhost','%','Y', 'Y', 'Y', 'Y', 'Y', 'Y');
			VALUES ('somedomain','%','Y', 'Y', 'Y', 'Y', 'Y', 'Y');


 2: Update the 'user' table, granting access to a new host+user combination.

	mysql_root> INSERT INTO user (host,user,password)
# with these example entries:
			VALUES('localhost','dario',password('mamamia'));
			VALUES('www.devshed.com','dario',password('mamamia'));


Step 3: Update the 'db' table.

	mysql_root> INSERT INTO db ( host, db, user,
				Select_priv, Insert_priv, Update_priv,
				Delete_priv, Create_priv, Drop_priv)
# with these example entries:
			VALUES ('localhost','pasta','dario','Y','Y','Y','Y','Y','Y');
			VALUES ('%','chicken','dario','Y','Y','Y','Y','Y','Y');


==================================================

http://www.devshed.com/Server_Side/MySQL/Administration/print

	root_prompt> mysqlshow
+--------------+
| Databases    |
+--------------+
| mysql        |
+--------------+

	root_promt> mysqlshow mysql
Database: mysql
+----------+
| Tables   |
+----------+
| db       |
| host     |
| user     |
+----------+

	root_promt> mysql -e "SELECT host,db,user FROM db" mysql
+------+----------+------+
| host | db       | user |
+------+----------+------+
| %    | test     |      |
| %    | test_%   |      |
+------+----------+------+

# ........................................

http://www.devshed.com/Server_Side/MySQL/Speak/Speak1/print

	mysql_prompt> SHOW DATABASES;
+----------+
| Database |
+----------+
| library  |
| mysql    |
| test     |
+----------+

	mysql_prompt> USE mysql;
Database changed

	mysql_prompt> SHOW TABLES;
+----------+
| Tables   |
+----------+
| db       |
| host     |
| user     |
+----------+

	mysql_prompt> SELECT host,db,user FROM db;
+------+----------+------+
| host | db       | user |
+------+----------+------+
| %    | test     |      |
| %    | test_%   |      |
+------+----------+------+

==================================================

Basic Security tips:

 1. Above all, take a moment to update the root privileges, and give it a password!
    Someone managing to get on the localhost can enter simply by typing:
    mysql -u root mysql, if a password is not established.

 2. Assign passwords for every user.

 3. If the user doesn't have a specific need for it, don't assign permission to use File_priv.
    This gives the user the ability to write files to the MySQL server. In addition, don't
    give permission to the process_priv, unless there is a good reason for doing so.

 4. Don't use '%' within the host names. It allows a user to enter that server from any outside
    host. In addition, don't use wildcards (i.e. devshed%) It would theoretically allow someone
    to use http://devshed.badcracker.com to aid them in entering the server.

==================================================

# the following two are the same
	mysql_prompt> SHOW KEYS FROM tableItem;
	mysql_prompt> SHOW INDEX FROM sampleDBtable FROM sampleDBname;


# to list all the entries from the table
# the following two are the same
	mysql_prompt> SHOW COLUMNS FROM sampleDBtable;
	mysql_prompt> SHOW FIELDS FROM sampleDBtable;


# narrow a search
	mysql_prompt> SHOW FIELDS FROM sampleDBtable LIKE '%yyy';


# SEARCH
	mysql_prompt> SELECT * FROM sampleDBtable WHERE tableItem LIKE '%zzz';


# multiple columns
	mysql_prompt> SELECT tableItem,tableItem2 FROM sampleDBtable;


# list unique entries
	mysql_prompt> SELECT DISTINCT tableItem FROM sampleDBtable;

==================================================


Copyright © 2010 by Nick Shin. All Rights Reserved.
These pages are designed by ESTSS.