EV: MySQL Learning
Installation
Login MySQL from command line:
shell>mysql -h -P -u -p[password] [database]
Notes: no space between -p and password
Install/uninstall MySQL service in Windows
shell>mysqld --install MySQL55 --defaults-file=\"E:\Data\Database\MySQL\my.ini\"
shell>mysqld --remove MySQL55
Start MySQL Server:
Two ways:
1. shell>mysqld --console
2. net start MySQL55
Stop/shutdown MySQL Server:
Two ways:
1. shell>mysqladmin shutdown -u root -p
2. net stop MySQL55
Starting Multiple MySQL Instances
1. Make a copy of one data directory. Assume that the new data directory is C:\mydata2
2. Create two option files. For example, create one file named C:\my-opts1.cnf that looks
like this:
[mysqld]
datadir = C:/mydata1
port = 3307
Create a second file named C:\my-opts2.cnf that looks like this:
[mysqld]
datadir = C:/mydata2
port = 3308
3. Use the --defaults-file option to start each server with its own option file:
C:\> C:\mysql\bin\mysqld --defaults-file=C:\my-opts1.cnf
C:\> C:\mysql\bin\mysqld-debug --defaults-file=C:\my-opts2.cnf
Each server starts in the foreground (no new prompt appears until the server
exits later), so you will need to issue those two commands in separate console
windows.
4. To shut down the servers, connect to each using the appropriate port number:
C:\> C:\mysql\bin\mysqladmin --port=3307 shutdown
C:\> C:\mysql\bin\mysqladmin --port=3308 shutdown
Syntax
; = \g
\G
\c (terminate input, useful for statement of multiple lines )
Notes: Do NOT use comma at the end when invoking parameters
, --comments, #comments
"", '' (string delimiter)
\ (escape character)
delimiter // (delimiter $$)
Identifier quote: backtick (`) (equivalent of [] in sql server)
\. or source: to run a script file
Data Types
date, datetime, timestamp, text
Statements
LIMIT: SELECT * FROM table_name ... ORDER BY ... LIMIT
INNER/LEFT/RIGHT JOIN
SELECT * FROM t1 INNER/LEFT/RIGHT JOIN t2 ON t1.id = t2.id
FULL OUTER JOIN:
You can emulate FULL OUTER JOIN using UNION (from MySQL 4.0.0 on):
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
IF ... THEN ... END IF;
LOOP
declare i int default 100;
1. Used within stored programs;
2. All declarations must appear at the first portion in the nearest BEGIN/END block.
3. Variable declarations must appear before cursor or handler declarations.
Assign a value to a user variable (@):
1. SET statement
SET @var_name = expr [, @var_name = expr] …
For SET, either = or := can be used as the assignment operator.
2. Other statements. In this case, the assignment operator must be := and not = because the latter is treated as the comparison operator = in non-SET statements.
select @max := max(ID) from customer;
DB Objects
Table
Column data type
AUTO_INCREMENT (Identity column)
Constraint of column default value (the default value must be a constant; it cannot be a function or an expression.)
PRIMARY KEY (id) (PK)
SHOW INDEX FROM tbl_name
SHOW CREATE TABLE tbl_name \G
SHOW TABLE STATUS WHERE Name = 'xxx'
ALTER TABLE tbl_name ENGINE = innodb
Column
Constraint
Index
Trigger
View
Procedure
select * from mysql.proc WHERE specific_name='myproc' AND db='test';
select routine_schema, routine_name, routine_type, data_type, routine_definition
from information_schema.routines;
show create procedure test.myproc;
Function
System Objects
Commands
use
status
Function, variable or constant
SELECT current_date, current_time, now()
SELECT user();
SELECT database();
SELECT version();
SELECT LAST_INSERT_ID(); (equivalent of SCOPE_IDENTITY())
SELECT table_name,engine FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=DATABASE();
SHOW ENGINES;
SET storage_engine=MyISAM;
DESCRIBE (equivalent of sp_help)
sp_helptext
show databases;
show tables;
show warnings/errors;
warnings;
nowarning;
The manual on SHOW WARNINGS states that:
It shows nothing if the last statement used a table and generated no messages. (That is, a statement that uses a table but generates no messages clears the message list.)
Statements that do not use tables and do not generate messages have no effect on the message list.
Maintenance
backup/restore db
Shutdown
Slow Shutdown:
1. Log into mysql;
2. run command "SET GLOBAL innodb_fast_shutdown=0;"
3. exit from mysql, then run the shutdown command.
Data directory
Look up the directory using command: show variables like 'datadir';
Physical (Raw)
MyISAM - Copy db/table files
InnoDB -
Logical Backups
shell>mysqldump -u root -p db_name
User
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
DROP USER 'jeffrey'@'localhost';
Password
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
shell>mysqladmin -u root -p[password] password
Privilege
Tables: mysql.user/db/tables_priv/columns_priv
grant all privileges on mydb.* to me@localhost;
revoke all privileges on mydb.* from me@localhost;
Notes:
1. Database test is always available for access to any user.
2. It seems revoking *.* can not cancel privileges granted on mydb.*.
delete from user where user = 'me' and host=’localhost’;
Notes:
Actions directly applied to user table need “flush privileges” to refresh
cached memory.
Client Tool
MySQL Workbench
Snippets tool window
phpMyAdmin
