Software >> Services >> RDBMS >> MySQL >> What are the commonly used commands

Connecting via mysql client

> mysql --host=localhost --user=youruser --password=yourpassword  yourdb

or (exlude the password value to enter after password prompt)

> mysql --host=localhost --user=youruser --password    yourdb

(no need to delimit values with single quote ' )

or

> mysql -uyouruser -pyourpassword yourdb

listing tables in a database

mysql> use yourdb;

mysql> show tables;

Creating new user

mysql> create user 'youruser'@'localhost';

mysql> grant all on yourdb.* to 'youruser'@'localhost';

Viewing list of users

mysql> use mysql;

mysql> show tables;

Note the named user exists

mysql> describe user;

Note the columns in table user

mysql > select user, host, password from user;

Creating a table

CREATE TABLE `joomla_hfp`.`jos_joodb_places` (
`myid` INT NOT NULL AUTO_INCREMENT ,
`country` VARCHAR( 50 ) NOT NULL ,
`city` VARCHAR( 50 ) NOT NULL ,
`zone` VARCHAR( 30 ) NOT NULL ,
`place_name` VARCHAR( 50 ) NOT NULL ,
`cuisine` VARCHAR( 50 ) NOT NULL ,
`address` VARCHAR( 100 ) NOT NULL ,
`authority` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `myid` )
) ENGINE = InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
 

Deleting a user

TRUNCATE TABLE tablename;

or

DELETE FROM tablename;

Insert new row into a table

 INSERT INTO `tablename`
(`column1`,`column2`)
VALUES
('value1','value2');

Note:
- for table name use backtick ` character as delimiter
- for column names also use backtick ` character as delimiter
- but for values, use single quote ' character as delimiter