-

Queries on mysql databases

Example queries on mysql tables Creating a user with all privileges: grant all privileges on *.* to 'myuser'@'localhost' identified by 'mypassword' with grant option; Queries on a database:
Open a database use databasename
Create a table create table tablename (field1 int unsigned auto_increment,field2 char(10) not null)
Rename a table rename table oldname to newname
Delete a table drop table [if exists] tablename
Compact a table optimize table tablename
Check table check table tablename [quick|fast|medium|extended]
Backup to file backup tablename[, othertable] TO ‘/the/path/to/backup/dir/’
Restore from file restore tablename[,othertable] FROM ‘/the/path/to/backup’
Repair tables repair table tablename[,othertable] [QUICK | EXTENDED]
List table names Show tables
Show fields Show columns from tablename
Add field in table alter table tablename add column thecounter TINYINT signed auto_increment
  Syntax for getting table info

    SHOW DATABASES [LIKE wild]

or SHOW [OPEN] TABLES [FROM db_name] [LIKE wild]

or SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]

or SHOW INDEX FROM tbl_name [FROM db_name]

or SHOW TABLE STATUS [FROM db_name] [LIKE wild]

or SHOW STATUS [LIKE wild]

or SHOW VARIABLES [LIKE wild]

or SHOW LOGS

or SHOW [FULL] PROCESSLIST

or SHOW GRANTS FOR user

or SHOW CREATE TABLE table_name

  Alter Table Syntax ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]   alter_specification: ADD [COLUMN] create_definition [FIRST | AFTER column_name ] or    ADD [COLUMN] (create_definition, create_definition,...) or    ADD INDEX [index_name] (index_col_name,...) or    ADD PRIMARY KEY (index_col_name,...) or    ADD UNIQUE [index_name] (index_col_name,...) or    ADD FULLTEXT [index_name] (index_col_name,...) or    ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...) [reference_definition] or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} or    CHANGE [COLUMN] old_col_name create_definition or    MODIFY [COLUMN] create_definition or    DROP [COLUMN] col_name or    DROP PRIMARY KEY or    DROP INDEX index_name or    RENAME [TO] new_tbl_name or    ORDER BY col or    table_options