MySQL quick reference.

Let’s start by making a new user within the MySQL shell:

 

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Sadly, at this point newuser has no permissions to do anything with the databases. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell.

Therefore, the first thing to do is to provide the user with access to the information they will need.

 

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@‘localhost';

The asterisks in this command refer to the database and table (respectively) that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables.

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

 

FLUSH PRIVILEGES;

Your changes will now be in effect.

How To Grant Different User Permissions

Here is a short list of other common possible permissions that users can enjoy.

• ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)


• CREATE- allows them to create new tables or databases


• DROP- allows them to them to delete tables or databases


• DELETE- allows them to delete rows from tables


• INSERT- allows them to insert rows into tables


• SELECT- allows them to use the Select command to read through databases


• UPDATE- allow them to update table rows


• GRANT OPTION- allows them to grant or remove other users' privileges

To provide a specific user with a permission, you can use this framework:

 

GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@‘localhost’;

If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name.

Each time you update or change a permission be sure to use the Flush Privileges command.

If you need to revoke a permission, the structure is almost identical to granting it:

 

REVOKE [type of permission] ON [database name].[table name] TO ‘[username]’@‘localhost’;

Just as you can delete databases with DROP, you can use DROP to delete a user altogether:

 

DROP USER ‘demo’@‘localhost’;

To test out your new user, log out by typing

 

quit 

and log back in with this command in terminal:

 

mysql -u [username]-p

Query
SELECT * FROM table
SELECT * FROM table1, table2, ...
SELECT field1, field2, ... FROM table1, table2, ...
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUP BY field
SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC
SELECT ... FROM ... WHERE condition LIMIT 10
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...

SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...
SELECT ... FROM t1 JOIN t2 USING(id) WHERE condition


 Conditionals
field1 = value1
field1 <> value1
field1 LIKE 'value _ %'
field1 IS NULL
field1 IS NOT NULL
field1 IN (value1, value2)
field1 NOT IN (value1, value2)
condition1 AND condition2
condition1 OR condition2


 Data Manipulation
INSERT INTO table1 (field1, field2, ...) VALUES (value1, value2, ...)
INSERT table1 SET field1=value_1, field2=value_2 ...

DELETE FROM table1 / TRUNCATE table1
DELETE FROM table1 WHERE condition
-- join:
DELETE FROM table1, table2 WHERE table1.id1 = table2.id2 AND condition

UPDATE table1 SET field1=new_value1 WHERE condition
-- join:
UPDATE table1, table2 SET field1=new_value1, field2=new_value2, ...
WHERE table1.id1 = table2.id2 AND condition


 Browsing
SHOW DATABASES
SHOW TABLES
SHOW FIELDS FROM table / SHOW COLUMNS FROM table / DESCRIBE table / DESC table / EXPLAIN table
SHOW CREATE TABLE table
SHOW CREATE TRIGGER trigger
SHOW TRIGGERS LIKE '%update%'
SHOW PROCESSLIST
KILL process_number
SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '**yourdbname**';
$ mysqlshow
$ mysqlshow database


 Create / delete / select / alter database
CREATE DATABASE [IF NOT EXIST] mabase [CHARACTER SET charset] [COLLATE collation]
CREATE DATABASE mabase CHARACTER SET utf8
DROP DATABASE mabase
USE mabase

ALTER DATABASE mabase CHARACTER SET utf8


 Create/delete/modify table
CREATE TABLE table (field1 type1, field2 type2, ...)
CREATE TABLE table (field1 type1, field2 type2, ..., INDEX (field))
CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1))
CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1, field2))
CREATE TABLE table1 (fk_field1 type1, field2 type2, ...,
  FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA)
    [ON UPDATE] [CASCADE|SET NULL|RESTRICT]
    [ON DELETE] [CASCADE|SET NULL|RESTRICT])
CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ...,
  FOREIGN KEY (fk_field1, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB))
CREATE TABLE table IF NOT EXISTS (...)

CREATE TABLE new_tbl_name LIKE tbl_name
  [SELECT ... FROM tbl_name ...]

CREATE TEMPORARY TABLE table (...)

DROP TABLE table
DROP TABLE IF EXISTS table
DROP TABLE table1, table2, ...
DROP TEMPORARY TABLE table

ALTER TABLE table MODIFY field1 type1 
ALTER TABLE table MODIFY field1 type1 NOT NULL ... 
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 NOT NULL ...
ALTER TABLE table ALTER field1 SET DEFAULT ...
ALTER TABLE table ALTER field1 DROP DEFAULT
ALTER TABLE table ADD new_name_field1 type1
ALTER TABLE table ADD new_name_field1 type1 FIRST
ALTER TABLE table ADD new_name_field1 type1 AFTER another_field
ALTER TABLE table DROP field1
ALTER TABLE table ADD INDEX (field);
ALTER TABLE table ADD PRIMARY KEY (field);

-- Change field order:
ALTER TABLE table MODIFY field1 type1 FIRST
ALTER TABLE table MODIFY field1 type1 AFTER another_field
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 FIRST
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 AFTER another_field

ALTER TABLE old_name RENAME new_name;


 Keys
CREATE TABLE table (..., PRIMARY KEY (field1, field2))
CREATE TABLE table (..., FOREIGN KEY (field1, field2) REFERENCES table2 (t2_field1, t2_field2))
ALTER TABLE table ADD PRIMARY KEY (field);


 Privileges
GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host'; -- one permission only
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions

SET PASSWORD = PASSWORD('new_pass')
SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass')
SET PASSWORD = OLD_PASSWORD('new_pass')

DROP USER 'user'@'host'


 Main data types
TINYINT (1o: -127+128) SMALLINT (2o: +-65 000)
  MEDIUMINT (3o: +-16 000 000) INT (4o: +- 2 000 000 000)
  BIGINT (8o: +-9.10^18)
  Precise interval: -(2^(8*N-1)) -> (2^8*N)-1
  /!\ INT(2) = "2 digits displayed" -- NOT "number with 2 digits max"

INT NOT NULL auto_increment PRIMARY KEY -- auto-counter for PK

FLOAT(M,D) DOUBLE(M,D) FLOAT(D=0->53) 
  /!\ 8,3 -> 12345,678 -- NOT 12345678,123!

TIME (HH:MM) YEAR (AAAA) DATE (AAAA-MM-JJ) DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
  TIMESTAMP (like DATETIME, but 1970->2038, compatible with Unix)

VARCHAR (single-line; explicit size)  TEXT (multi-lines; max size=65535)  BLOB (binary; max size=65535)
  Variants for TEXT&BLOB: TINY (max=255) MEDIUM (max=~16000) LONG (max=4Go)
 Ex: VARCHAR(32), TINYTEXT, LONGBLOB, MEDIUMTEXT

ENUM ('value1', 'value2', ...) -- (default NULL, or '' if NOT NULL)


 Forgot root password?
$ /etc/init.d/mysql stop
$ mysqld_safe --skip-grant-tables
$ mysql # on another terminal
mysql> UPDATE mysql.user SET password=PASSWORD('nouveau') WHERE user='root';
## Kill mysqld_safe from the terminal, using Control + \
$ /etc/init.d/mysql start


 Repair tables after unclean shutdown
mysqlcheck --all-databases
mysqlcheck --all-databases --fast


 load data from local file
If you are running mysql and using your newly created database use 
the below code to run the script file mysql> SOURCE input_file


from terminal mysql -u root -p database < filename-20120201.tbz