cmdref.net is command references/cheat sheets/examples for system engineers.
| Cloud |
|---|
| Etc |
|---|
| Reference |
|---|
| Description | Command |
|---|---|
| # mysql -u USER -p # mysql -u USER -p DATABASE |
|
| mysql> exit mysql> \q |
| Description | Command |
|---|---|
| List all databases on the sql server. | mysql> show databases; |
| Switch to a database. | mysql> use DATABASE; |
| Create database | mysql> create database DATABASE; mysql> create database DATABASE character set CODE; |
| Delete database | mysql> drop database DATABASE; |
| show database's LANG | mysql> show create database DATABASE; |
CREATE TABLE test ( id INTEGER, txt VARCHAR(20), ts1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
mysqldump -u root -p db1 > dump.sql mysqladmin -u root -p create db2 mysql -u root -p db2 < dump.sql
| Description | Command |
|---|---|
| see all the tables in the db. | mysql> show tables; |
| see database's field formats. | mysql> describe TABLENAME; mysql> show fields from TABLENAME; |
| Create Table | mysql> create table TABLENAME (NAME1 TYPE, NAME2 TYPE); |
| delete a table. | mysql> drop table TABLENAME; |
| see LANG | mysql> show table status from DATABASE; |
| see SQL of create table | mysql> show create table TABLENAME; mysql> show create table TABLENAME\G |
| Description | Command |
|---|---|
| Change tables name | mysql> alter table TABLENAME rename as NEW_TABLENAME; |
| Add column | mysql> alter table TABLE add COLUMN TYPE |
| mysql> alter table TABLE add COLUMN TYPE first; | |
| mysql> alter table TABLE add COLUMN TYPE after COLUMN; | |
| Delete field | mysql> alter table TABLE drop COLUMN; |
| Change column name | mysql> alter table TABLE change COLUMN NEW_COLUMN TYPE; |
| Change column type | mysql> alter table TABLE modify COLUMN NEWTYPE; |
| Description | Command |
|---|---|
| mysql> select host, user, password from mysql.user; mysql> select host, user, password from mysql.user where user='USER'; mysql> select host, user, password from mysql.user where user like '%STRING%'; |
|
| mysql> show grants for USER; mysql> select * from mysql.user \G; mysql> select * from mysql.user; mysql> select * from mysql.user where user='USER'; |
|
| mysql> select user(); |
mysql> grant all privileges on DBNAME.* to USERNAME@localhost identified by 'PASSWORD'; mysql> grant all privileges on DBNAME.* to USERNAME@"%" identified by 'PASSWORD'; mysql> flush privileges; --all DB mysql> grant all privileges on *.* to USERNAME@localhost identified by 'PASSWORD'; mysql> grant all privileges on *.* to USERNAME@"%" identified by 'PASSWORD'; mysql> flush privileges;
mysql> drop user USERNAME; or mysql> delete from mysql.user where user = 'USERNAME';
--User
mysql> set password for USER@"%" = password('PASSWORD');
mysql> set password for USER@localhost = password('PASSWORD');
--root
mysql> set password for root=password('PASSWORD');
mysql> set password for root@localhost=password('PASSWORD');
ERROR 1045 (28000) at line 1: Access denied for user 'USER'@'localhost' (using password: YES)
mysql> grant file on *.* to USER@localhost;
--Create table as same data and type mysql> create table newtable as select * from orgtable; --Create table with same type. data is null. mysql> create table newtable as select * from orgtable where id is null;
# mysql -uUSER -pPASS DATABASE -e "select * from TABLE;" | sed -e 's/^/"/g' | sed -e 's/$/"/g' | sed -e 's/\t/","/g' > /tmp/test.csv
mysql> LOAD DATA LOCAL INFILE '/tmp/import.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' mysql> LOAD DATA LOCAL INFILE '/tmp/import.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' (column1, column2)
ERROR 1045 (28000) at line 1: Access denied for user 'USER'@'localhost' (using password: YES)grant file on *.* to USER@localhost;
select * from TABLE where col1 LIKE '%AAA%' or col2 LIKE '%AAA%' or col3 LIKE '%AAA%'; -- Only MySQL select * from TABLE where concat(col1, col2, col3) LIKE '%AAA%'
select * from tb where
( concat( column1 , column2 , column3) like '%word1%' )
and ( concat( column1 , column2 , column3) like '%word2%' )
and ( concat( column1 , column2 , column3) like '%word3%' );