The database... simple, fairly fast depending on how you use it and free !!.

1. Version
2. Users & Passwords
3. Utilities
4. DELETE
5. TRUNCATE
6. File Source
7. INSERT ... SELECT
8. REPLACE
9. Date Manipulations
10. LIMIT
11. Optimisation
12. UNIQUE
13. General
14. String
15. EXPLAIN
16. GROUP BY
17. FAQ

Before 4.1 (<= 4.0.*)
Forget about subqueries, duplicate keys ... so beware your hoster, check out wot version he is using vor just don't write such queries ! damn i did and Arch linux doesn't have the latest versions of that mysql.

AY(date)
DAY() is a synonym for DAYOFMONTH(). It is available as of MySQL 4.1.1.

DATEDIFF() was added in MySQL 4.1.1.
TIMEDIFF() was added in MySQL 4.1.1.

You should use TO_DAYS(date) instead.

DATE() is available as of MySQL 4.1.1.

To use SET PASSWORD on Windows, do this:

shell> mysql -u rootmysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('newpwd');

To use SET PASSWORD on Unix, do this:

shell> mysql -u root mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd'); mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');

- USER MANAGEMENT:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost'; mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost'; mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP -> ON bankaccount.* -> TO 'custom'@'localhost' -> IDENTIFIED BY 'obscure'; mysql> GRANT ALL PRIVILEGES ON bCo.* TO nan@'%' IDENTIFIED BY 'weenie' WITH GRANT OPTION;

- USERS:

mysql> SELECT Host, User FROM mysql.user;

C:\> C:\mysql\bin\mysqlshow
+-----------+
| Databases |
+-----------+
| mysql |
| test |
+-----------+

C:\> C:\mysql\bin\mysqlshow mysql
Database: mysql
+--------------+
| Tables |
+--------------+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+--------------+

-VERSION

shell> bin/mysqladmin version

-- MAINTENANCE

Verify that you can shut down the server:
shell> bin/mysqladmin -u root shutdown

Verify that you can restart the server. Do this by using mysqld_safe or by invoking mysqld directly.
For example: shell> bin/mysqld_safe --user=mysql --log &

Backup tha DB :

$ mysqldump --tab=/path/to/some/dir --opt db_name -p

or

The most common use of mysqldump is probably for making a backup of entire databases.

$ mysqldump --opt db_name | bzip2 -c > backup-file.sql.bz2

mysqldump is also very useful for populating databases by copying data from one MySQL server to another:

$ mysqldump --opt db_name | mysql --host=remote_host -C db_name

It is possible to dump several databases with one command:

shell> mysqldump --databases db_name1 [db_name2 ...] | bzip2 -c > my_databases.sql.bz2

If you want to dump all databases, use the --all-databases option:

shell> mysqldump --all-databases | bzip2 -c > all_databases.sql.bz2

Get back the Dbs :
mysql > source < dbbackupname.sql Or mysql> source filename (no need that < depending on the version !)

- DATABASE CREATE

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]

-CREATE

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

types:

type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
| NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
| DATE
| TIME
| TIMESTAMP
| DATETIME
| CHAR(length) [BINARY | ASCII | UNICODE]
| VARCHAR(length) [BINARY]
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT
| TEXT
| MEDIUMTEXT
| LONGTEXT
| ENUM(value1,value2,value3,...)
| SET(value1,value2,value3,...)
| spatial_type

Mysql

- ALTER

Mysql, Alter

> ALTER TABLE yourtablename
DROP FOREIGN KEY fk_symbol;

> ALTER TABLE tablname ADD colname BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY;

To rename a column (or only change var):

> ALTER table tb CHANGE col1 col2 VARCHAR(255);

> ALTER TABLE client MODIFY email VARCHAR(50)

> ALTER TABLE client MODIFY email VARCHAR(50) NOT NULL

> ALTER TABLE client ADD CONSTRAINT UNIQUE (email, nom)

> ALTER TABLE client ADD INDEX mon_index(email)

!!! Strange stuff with UNIQUE KEY and the MODIFY:
If you plan to modify a column which is included into unique set then you shoulddrop the index given the first column refered into the unique key then apply the change:

> alter table t drop index nameofcolumnorfirstnameofsetofcolumns;

then issue the modif:
> alter table t modify nameofcoltomodif TEXT;

-----> actually not strange it only happens on trying to index a TEXT type.. do not use TEXT typewith unique or index.

IGNORE to make it happen even with duplicate keys when changing to unique.
> alter ignore ...

Multiple rows in several tables:

> DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

Or:

> DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

> truncate tablename;

> source nom.sql

Mysql, insert..select

With INSERT ... SELECT, you can quickly insert many rows into a table from one or many tables.

For example:

INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted. See section 14.1.4 INSERT Syntax.

Mysql, datetime

MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format.
MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format.

SELECT EXTRACT(MONTH FROM date) as month,EXTRACT(YEAR FROM date) as year, EXTRACT(DAY FROM date) as day,EXTRACT(hour FROM date) as hour,EXTRACT(minute FROM date) as minute ,date FROM project;

SELECT MONTH(date) as month,
YEAR(date) as year,
DAY(date) as day,

DATEDIFF(dateclose,date) as as closeday,
HOUR(dateclose) - HOUR(date) as hourclose,
MINUTE(dateclose) - MINUTE(date) as minuteclose,

* current date : NOW(), CURDATE()

into the query: LIMIT 5,10 displays data between 5 and 10 rows.

LAST_INSERT_ID() into a mysql query retrieves the last inserted ID.

insert but update:

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
-> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

instead of selecting then checking then updating or inserting ...!! bloody time saved isn't it ?? (But Beware the Mysql Version !!!)

LEFT JOIN better than 2 queries:

SELECT rateprov,rateexec,nbproject,forumstars,diploma,rate_forum.*
FROM entity LEFT JOIN rate_forum ON rate_forum.entityid=entity.id WHERE entity.id=0;

Ex.:
SELECT project.* FROM project
LEFT JOIN accepted_project ON project.id = accepted_project.id
LEFT JOIN team ON accepted_project.teamid=team.id
EFT JOIN team_content ON team.id=team_content.teamid
WHERE project.state='rating'
AND (
project.entityid ={$_SESSION['id']}
OR
team_content.entityid={$_SESSION['id']})
ORDER BY project.date DESC"

instead of

$rsResult= mysql_query("SELECT * FROM project WHERE state='rating' AND (entityid={$_SESSION['id']} OR id IN
SELECT projectid FROM accepted_project WHERE coderid={$_SESSION['id']}) OR id IN
(SELECT projectid FROM accepted_project WHERE teamid IS NOT NULL AND
coderid IN (SELECT representid FROM team WHERE id IN
SELECT teamid FROM team_content WHERE entityid={$_SESSION['id']})))) ORDER BY date DESC",$dbconn) or die('Invalid query: 5' . mysql_error());

I use it instead of checking by select ... if error duplicate then launch msg otherwise continue with update.

When you use VARCHAR in MySQL, strings are also truncated if they're too long. But if they're shorter, they aren't padded with trailing spaces. So you do save some space by using this type. The tradeoff is that CHAR fields are slightly faster to process.

String

ex:

$strSqlQuery="UPDATE accepted_project SET stateid='2',state=concat('concate then man fdfdf'),time_ended=NOW() WHERE stateid='1' AND coderid={$_SESSION['id']}AND projectid={$_SESSION['ProjectId']}";

- FIND_IN_SET():

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
Returns 0 if str is not in strlist or if strlist is the empty string

- LOCATE():

mysql> SELECT LOCATE('bar', 'foobarbar');
-> 4
mysql> SELECT LOCATE('xbar', 'foobar');
-> 0
mysql> SELECT LOCATE('bar', 'foobarbar',5);
-> 7

Yes indeed we need explaination of how mysql works to make something efficient, shall we ?

A word about group by, i wanted a select to be selective for one specific ID for instance:

SELECT DISTINCT(sourceid),proposition,title,message,date FROM project_msgboard WHERE type='private' AND projectid=1 AND destinationid=2 GROUP BY sourceid;

Forum, Devshed