Monday, 10 February 2014

MYSQL how to intensive complete guide (part1) linux fedora ubuntu

MYSQL INTENSIVE

logon
/usr/bin/mysqladmin -u root -p password

[root@servo user5]# rpm -qa 'mysql*'
mysql-devel-5.5.14-2.fc15.i686
mysql-libs-5.5.14-2.fc15.i686
mysql-server-5.5.14-2.fc15.i686
mysql-5.5.14-2.fc15.i686
mysql-connector-java-5.1.15-1.fc15.i686
[root@servo user5]# mysql_secure_installation


-----------------------------------------------------------------------------
/usr/bin/mysqladmin  Ver 8.42 Distrib 5.5.14, for Linux on i686
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Administration program for the mysqld daemon.
Usage: /usr/bin/mysqladmin [OPTIONS] command command....
-c, --count=#       Number of iterations to make. This works with -i
(--sleep) only.
--debug-check       Check memory and open file usage at exit.
--debug-info        Print some debug info at exit.
-f, --force         Don't ask for confirmation on drop database; with
multiple commands, continue even if an error occurs.
-C, --compress      Use compression in server/client protocol.
--character-sets-dir=name
Directory for character set files.
--default-character-set=name
Set the default character set.
-?, --help          Display this help and exit.
-h, --host=name     Connect to host.
-b, --no-beep       Turn off beep on error.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's asked from the tty.
-P, --port=#        Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
--protocol=name     The protocol to use for connection (tcp, socket, pipe,
memory).
-r, --relative      Show difference between current and previous values when
used with -i. Currently only works with extended-status.
-s, --silent        Silently exit if one can't connect to server.
-S, --socket=name   The socket file to use for connection.
-i, --sleep=#       Execute commands repeatedly with a sleep between.
--ssl               Enable SSL for connection (automatically enabled with
other flags).
--ssl-ca=name       CA file in PEM format (check OpenSSL docs, implies
--ssl).
--ssl-capath=name   CA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=name     X509 cert in PEM format (implies --ssl).
--ssl-cipher=name   SSL cipher to use (implies --ssl).
--ssl-key=name      X509 key in PEM format (implies --ssl).
--ssl-verify-server-cert
Verify server's "Common Name" in its cert against
hostname used when connecting. This option is disabled by
default.
-u, --user=name     User for login if not current user.
-v, --verbose       Write more information.
-V, --version       Output version information and exit.
-E, --vertical      Print output vertically. Is similar to --relative, but
prints output vertically.
-w, --wait[=#]      Wait and retry if connection is down.
--connect-timeout=#
--shutdown-timeout=#
--plugin-dir=name   Directory for client-side plugins.
--default-auth=name Default authentication client-side plugin to use.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
count                             0
debug-check                       FALSE
debug-info                        FALSE
force                             FALSE
compress                          FALSE
character-sets-dir                (No default value)
default-character-set             auto
host                              (No default value)
no-beep                           FALSE
port                              0
relative                          FALSE
socket                            (No default value)
sleep                             0
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-verify-server-cert            FALSE
user                              root
verbose                           FALSE
vertical                          FALSE
connect-timeout                   43200
shutdown-timeout                  3600
plugin-dir                        (No default value)
default-auth                      (No default value)

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
The following groups are read: mysqladmin client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.

Where command is a one or more of: (Commands may be shortened)
create databasename Create a new database
debug   Instruct server to write debug information to log
drop databasename Delete a database and all its tables
extended-status       Gives an extended status message from the server
flush-hosts           Flush all cached hosts
flush-logs            Flush all logs
flush-status  Clear status variables
flush-tables          Flush all tables
flush-threads         Flush the thread cache
flush-privileges      Reload grant tables (same as reload)
kill id,id,... Kill mysql threads
password [new-password] Change old password to new-password in current format
old-password [new-password] Change old password to new-password in old format
ping   Check if mysqld is alive
processlist  Show list of active threads in server
reload  Reload grant tables
refresh  Flush all tables and close and open logfiles
shutdown  Take server down
status  Gives a short status message from the server
start-slave  Start slave
stop-slave  Stop slave
variables             Prints variables available
version  Get version info from server



COMMANDS

Start




To start mysql at dnaserver:
# mysql -p

Databases

To create a database:
mysql> create database FoldRNA;
To query which databases have been created:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| FoldRNA            |
| test               |
+--------------------+
3 rows in set (0.05 sec)
To create a table describing the types of each field:
mysql> create table step_param(seq_num CHAR(5), chain_num VARCHAR(5));
To select a database, and then show the tables inside of it, and then show the fields in the table:
mysql> use FoldRNA;
mysql> show tables;
+-------------------+
| Tables_in_FoldRNA |
+-------------------+
| step_param        |
+-------------------+
1 row in set (0.00 sec)
mysql> show columns from step_param;
To show the fields and values in table step_param and then to insert new values to the table:
mysql> select * from step_param;
+----------+-------+-------+------+
| step_num | Shift | Slide | Rise |
+----------+-------+-------+------+
|        2 |     3 |     5 |    2 |
|        1 |     4 |     2 |    3 |
+----------+-------+-------+------+
2 rows in set (0.00 sec)

mysql> insert into step_param (step_num, Shift, Slide, Rise) values (3, 2, 3, 4);
To delete the values in a table without deleting the table fields:
mysql> delete from step_param;
To change a value in a table (change 3 to 4 in column step_num):
mysql> UPDATE step_param SET step_num = "3" where step_num = "4"
To import a tab separated table from a local file:
mysql> load data local infile 'steparams.tab' into table step_param;

Queries

To show the first five rows of a table use limit 5:
mysql> select * from step_param limit 5;
+----------+-----------+---------+-------+-------+-------+--------+-------+-------+-------+-------+
| step_num | chain_num | base_id | Shift | Slide | Rise  | Tilt   |Roll   | Twist | extra | intra |
+----------+-----------+---------+-------+-------+-------+--------+-------+-------+-------+-------+
|       10 |         1 | U/A     | -0.82 | -0.37 | 10.34 | -78.69 | 30.95 | 18.69 |  1.01 |  0.04 |
|       11 |         2 | A/U     |  2.93 | -1.34 |  3.09 |   3.75 |  5.82 | 57.45 |  5.80 |  2.94 |
|       12 |         3 | U/G     |  5.51 | -1.92 |  3.20 |   1.70 |  8.63 | 53.28 |  0.94 |  0.00 |
|       13 |         4 | G/C     | -0.42 | -1.84 |  3.52 |  -4.16 |  3.23 | 36.01 |  3.91 |  1.26 |
|       14 |         5 | C/C     | -0.07 | -1.52 |  3.14 |   5.46 | 10.86 | 29.98 |  0.51 |  0.00 |
+----------+-----------+---------+-------+-------+-------+--------+-------+-------+-------+-------+
5 rows in set (0.00 sec)
To count how many fields of something in table just change the general * to COUNT(*) (Notice that RLIKE is used to query using regular expressions.):
mysql> SELECT COUNT(*) FROM step_param WHERE base_id = "C/G" AND Tilt RLIKE "^\\-1.";
+----------+
| COUNT(*) |
+----------+
|       13 |
+----------+
1 row in set (0.02 sec)

mysql> SELECT * FROM step_param WHERE base_id = "C/G" AND Tilt RLIKE "^\\-1." limit 5;
+----------+-----------+---------+-------+-------+------+-------+-------+-------+-------+-------+
| step_num | chain_num | base_id | Shift | Slide | Rise | Tilt  | Roll  | Twist | extra | intra |
+----------+-----------+---------+-------+-------+------+-------+-------+-------+-------+-------+
|      156 |       145 | C/G     |  0.74 | -2.39 | 3.35 | -1.63 |  7.08 | 29.34 |  0.00 |  0.00 |
|      364 |       353 | C/G     |  0.72 | -1.33 | 3.42 | -1.79 | 17.90 | 29.81 |  1.17 |  0.40 |
|      764 |       752 | C/G     |  2.85 | -1.95 | 3.60 | -1.71 |  2.65 | 41.64 |  1.11 |  0.18 |
|      890 |       878 | C/G     |  0.87 | -1.71 | 3.31 | -1.62 |  3.94 | 32.06 |  0.36 |  0.06 |
|     1142 |      1102 | C/G     | -0.73 | -2.88 | 3.51 | -1.21 |  4.20 | 17.48 |  0.00 |  0.00 |
+----------+-----------+---------+-------+-------+------+-------+-------+-------+-------+-------+
5 rows in set (0.00 sec)
To make a batch query with a script.
# mysql -u username -p password < query1.sql
or
# ./mysqlquery4.sh

Backups

To backup your database use mysqldump. Remember to create the database before restoring the backup, in case it doesn't exist in the server where it's being restored.
# mysqldump -ppassword FoldRNA > foldrna.sql  #To Backup
# mysql -u username -ppassword FoldRNA < foldrna.sql  #To Restore

Additional Commands


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

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

Create a database on the sql server.

mysql> create database [databasename];

List all databases on the sql server.

mysql> show databases;

Switch to a database.

mysql> use [db name];

To see all the tables in the db.

mysql> show tables;

To see database's field formats.

mysql> describe [table name];

To delete a db.

mysql> drop database [database name];

To delete a table.

mysql> drop table [table name];

Show all data in a table.

mysql> SELECT * FROM [table name];

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

mysql> show columns from [table name];

Show certain selected rows with the value "whatever".

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

Show all records containing the name "Bob" AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

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;

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';

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;

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";

Show unique records.

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

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

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

Return number of rows.

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

Sum column.

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

Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on 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.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;

Change a users password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'

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;

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Set a root password if there is on root password.

# mysqladmin -u root password newpassword

Update a root password.

# mysqladmin -u root -p oldpassword newpassword

Allow the user "bob" 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 bob@localhost identified by 'passwd';
mysql> flush privileges;

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (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;

To update info already in a table.

mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

Delete a row(s) from a table.

mysql> DELETE from [table name] where [field name] = 'whatever';

Update database permissions/privilages.

mysql> flush privileges;

Delete a column.

mysql> alter table [table name] drop column [column name];

Add a new column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

Change column name.

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.

mysql> alter table [table name] add unique ([column name]);

Make a column bigger.

mysql> alter table [table name] modify [column name] VARCHAR(3);

Delete unique from table.

mysql> alter table [table name] drop index [colmn name];

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);

Dump all databases for backup. Backup file is sql commands to recreate all db's.

# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Create Table Example 1.

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));

Create Table Example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');

Selecting a database

mysql> USE database; Listing databases: mysql> SHOW DATABASES;

Listing tables in a db

mysql> SHOW TABLES;

Describing the format of a table

mysql> DESCRIBE table;

Creating a database

mysql> CREATE DATABASE db_name; Creating a table: mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE)); Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE); Load tab-delimited data into a table: mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name; (Use \n for NULL) Inserting one row at a time: mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31'); (Use NULL for NULL)

Retrieving information (general)

mysql> SELECT from_columns FROM table WHERE conditions; All values: SELECT * FROM table; Some values: SELECT * FROM table WHERE rec_name = "value"; Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";

Reloading a new data set into existing table

mysql> SET AUTOCOMMIT=1; # used for quick recreation of table mysql> DELETE FROM pet; mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table; Fixing all records with a certain value: mysql> UPDATE table SET column_name = "new_value" WHERE record_name = "value";

Selecting specific columns

mysql> SELECT column_name FROM table;

Retrieving unique output records:

mysql> SELECT DISTINCT column_name FROM table;

Sorting:

mysql> SELECT col1, col2 FROM table ORDER BY col2; Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;

Date calculation

mysql> SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table]; MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.

Pattern Matching

mysql> SELECT * FROM table WHERE rec LIKE "blah%"; (% is wildcard - arbitrary # of chars) Find 5-char values: SELECT * FROM table WHERE rec like "_____"; (_ is any single character)

Extended Regular Expression Matching

mysql> SELECT * FROM table WHERE rec RLIKE "^b$"; (. for char, [...] for char class, * for 0 or more instances ^ for beginning, {n} for repeat n times, and $ for end) (RLIKE or REGEXP) To force case-sensitivity, use "REGEXP BINARY"

Counting Rows

mysql> SELECT COUNT(*) FROM table;

Grouping with Counting

mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner; (GROUP BY groups together all records for each 'owner') Selecting from multiple tables: (Example) mysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name; (You can join a table to itself to compare by using 'AS') Currently selected database: mysql> SELECT DATABASE(); Maximum value: mysql> SELECT MAX(col_name) AS label FROM table; Auto-incrementing rows: mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL); mysql> INSERT INTO table (name) VALUES ("tom"),("dick"),("harry"); Adding a column to an already-created table: mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name; Removing a column: mysql> ALTER TABLE tbl DROP COLUMN col; (Full ALTER TABLE syntax available at mysql.com.) Batch mode (feeding in a script): # mysql -u user -p < batch_file (Use -t for nice table layout and -vvv for command echoing.) Alternatively: mysql> source batch_file;

Backing up a database with mysqldump

# mysqldump --opt -u username -p database > database_backup.sql (Use 'mysqldump --opt --all-databases > all_backup.sql' to backup everything.) (More info at MySQL's docs.)

STATEMENTS

SQL Statement
Syntax
AND / OR
SELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition
ALTER TABLE
ALTER TABLE table_name 
ADD column_name datatype
or
ALTER TABLE table_name 
DROP COLUMN column_name
AS (alias)
SELECT column_name AS column_alias
FROM table_name
or
SELECT column_name
FROM table_name  AS table_alias
BETWEEN
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
CREATE DATABASE
CREATE DATABASE database_name
CREATE TABLE
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name2 data_type,
...
)
CREATE INDEX
CREATE INDEX index_name
ON table_name (column_name)
or
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
CREATE VIEW
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
DELETE
DELETE FROM table_name
WHERE some_column=some_value
or
DELETE FROM table_name 
(
Note: Deletes the entire table!!)
DELETE * FROM table_name 
(
Note: Deletes the entire table!!)
DROP DATABASE
DROP DATABASE database_name
DROP INDEX
DROP INDEX table_name.index_name (SQL Server)
DROP INDEX index_name ON table_name (MS Access)
DROP INDEX index_name (DB2/Oracle)
ALTER TABLE table_name
DROP INDEX index_name (MySQL)
DROP TABLE
DROP TABLE table_name
GROUP BY
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
IN
SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)
INSERT INTO
INSERT INTO table_name
VALUES (value1, value2, value3,....)
or
INSERT INTO table_name
(column1, column2, column3,...)
VALUES (value1, value2, value3,....)
INNER JOIN
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
 
ON table_name1.column_name=table_name2.column_name
LEFT JOIN
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
 
ON table_name1.column_name=table_name2.column_name
RIGHT JOIN
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
 
ON table_name1.column_name=table_name2.column_name
FULL JOIN
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
 
ON table_name1.column_name=table_name2.column_name
LIKE
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
ORDER BY
SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]
SELECT
SELECT column_name(s)
FROM table_name
SELECT *
SELECT *
FROM table_name
SELECT DISTINCT
SELECT DISTINCT column_name(s)
FROM table_name
SELECT INTO
SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_table_name
or
SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_table_name
SELECT TOP
SELECT TOP number|percent column_name(s)
FROM table_name
TRUNCATE TABLE
TRUNCATE TABLE table_name
UNION
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
UNION ALL
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
UPDATE
UPDATE table_name
SET column1=value, column2=value,...
WHERE some_column=some_value
WHERE
SELECT column_name(s)
FROM table_name
WHERE column_name operator value

MYSQL Statements and clauses


ALTER DATABASE
ALTER TABLE
ALTER VIEW
ANALYZE TABLE
BACKUP TABLE
CACHE INDEX
CHANGE MASTER TO
CHECK TABLE
CHECKSUM TABLE
COMMIT
CREATE DATABASE
CREATE INDEX
CREATE TABLE
CREATE VIEW
DELETE
DESCRIBE
DO
DROP DATABASE
DROP INDEX
DROP TABLE
DROP USER
DROP VIEW
EXPLAIN
FLUSH
GRANT
HANDLER
INSERT
JOIN
KILL
LOAD DATA FROM MASTER
LOAD DATA INFILE
LOAD INDEX INTO CACHE
LOAD TABLE...FROM MASTER
LOCK TABLES
OPTIMIZE TABLE
PURGE MASTER LOGS
RENAME TABLE
REPAIR TABLE
REPLACE
RESET
RESET MASTER
RESET SLAVE
RESTORE TABLE
REVOKE
ROLLBACK
ROLLBACK TO SAVEPOINT
SAVEPOINT
SELECT
SET
SET PASSWORD
SET SQL_LOG_BIN
SET TRANSACTION
SHOW BINLOG EVENTS
SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW CREATE DATABASE
SHOW CREATE TABLE
SHOW CREATE VIEW
SHOW DATABASES
SHOW ENGINES
SHOW ERRORS
SHOW GRANTS
SHOW INDEX
SHOW INNODB STATUS
SHOW LOGS
SHOW MASTER LOGS
SHOW MASTER STATUS
SHOW PRIVILEGES
SHOW PROCESSLIST
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW VARIABLES
SHOW WARNINGS
START SLAVE
START TRANSACTION
STOP SLAVE
TRUNCATE TABLE
UNION
UNLOCK TABLES
USE
String Functions
AES_DECRYPT
AES_ENCRYPT
ASCII
BIN
BINARY
BIT_LENGTH
CHAR
CHAR_LENGTH
CHARACTER_LENGTH
COMPRESS
CONCAT
CONCAT_WS
CONV
DECODE
DES_DECRYPT
DES_ENCRYPT
ELT
ENCODE
ENCRYPT
EXPORT_SET
FIELD
FIND_IN_SET
HEX
INET_ATON
INET_NTOA
INSERT
INSTR
LCASE
LEFT
LENGTH
LOAD_FILE
LOCATE
LOWER
LPAD
LTRIM
MAKE_SET
MATCH    AGAINST
MD5
MID
OCT
OCTET_LENGTH
OLD_PASSWORD
ORD
PASSWORD
POSITION
QUOTE
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SHA
SHA1
SOUNDEX
SPACE
STRCMP
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UNCOMPRESS
UNCOMPRESSED_LENGTH
UNHEX
UPPER
Date and Time Functions
ADDDATE
ADDTIME
CONVERT_TZ
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATE_ADD
DATE_FORMAT
DATE_SUB
DATEDIFF
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
FROM_UNIXTIME
GET_FORMAT
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SEC_TO_TIME
SECOND
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIMEDIFF
TIMESTAMP
TIMESTAMPDIFF
TIMESTAMPADD
TIME_FORMAT
TIME_TO_SEC
TO_DAYS
UNIX_TIMESTAMP
UTC_DATE
UTC_TIME
UTC_TIMESTAMP
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
Mathematical and Aggregate Functions
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
BIT_AND
BIT_OR
BIT_XOR
CEIL
CEILING
COS
COT
COUNT
CRC32
DEGREES
EXP
FLOOR
FORMAT
GREATEST
GROUP_CONCAT
LEAST
LN
LOG
LOG2
LOG10
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
STD
STDDEV
SUM
TAN
TRUNCATE
VARIANCE
Flow Control Functions
CASE
IF
IFNULL
NULLIF
Command-Line Utilities
comp_err
isamchk
make_binary_distribution
msql2mysql
my_print_defaults
myisamchk
myisamlog
myisampack
mysqlaccess
mysqladmin
mysqlbinlog
mysqlbug
mysqlcheck
mysqldump
mysqldumpslow
mysqlhotcopy
mysqlimport
mysqlshow
perror
Perl API - using functions and methods built into the Perl DBI with MySQL
available_drivers
begin_work
bind_col
bind_columns
bind_param
bind_param_array
bind_param_inout
can
clone
column_info
commit
connect
connect_cached
data_sources
disconnect
do
dump_results
err
errstr
execute
execute_array
execute_for_fetch
fetch
fetchall_arrayref
fetchall_hashref
fetchrow_array
fetchrow_arrayref
fetchrow_hashref
finish
foreign_key_info
func
get_info
installed_versions
last_insert_id
looks_like_number
neat
neat_list
parse_dsn
parse_trace_flag
parse_trace_flags
ping
prepare
prepare_cached
primary_key
primary_key_info
quote
quote_identifier
rollback
rows
selectall_arrayref
selectall_hashref
selectcol_arrayref
selectrow_array
selectrow_arrayref
selectrow_hashref
set_err
state
table_info
table_info_all
tables
trace
trace_msg
type_info
type_info_all
Attributes for Handles PHP API - using functions built into PHP with MySQL
mysql_affected_rows
mysql_change_user
mysql_client_encoding
mysql_close
mysql_connect
mysql_create_db
mysql_data_seek
mysql_db_name
mysql_db_query
mysql_drop_db
mysql_errno
mysql_error
mysql_escape_string
mysql_fetch_array
mysql_fetch_assoc
mysql_fetch_field
mysql_fetch_lengths
mysql_fetch_object
mysql_fetch_row
mysql_field_flags
mysql_field_len
mysql_field_name
mysql_field_seek
mysql_field_table
mysql_field_type
mysql_free_result
mysql_get_client_info
mysql_get_host_info
mysql_get_proto_info
mysql_get_server_info
mysql_info
mysql_insert_id
mysql_list_dbs
mysql_list_fields
mysql_list_processes
mysql_list_tables
mysql_num_fields
mysql_num_rows
mysql_pconnect
mysql_ping
mysql_query
mysql_real_escape_string
mysql_result
mysql_select_db
mysql_stat
mysql_tablename
mysql_thread_id
mysql_unbuffered_query

1 comment:

  1. Great Post ! Thank you very much It's much appreciated !!

    ReplyDelete