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

Leave a Reply

Your email address will not be published. Required fields are marked *