Table of Contents

Middleware

MySQL


Table of Contents


Web Sites

Installing MySQL


Command

Login, Logout

Description Command
# mysql -u USER -p
# mysql -u USER -p DATABASE
mysql> exit
mysql> \q

Database Operation

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;

Tips

Auto insert with TimeStamp

CREATE TABLE test (  
  id INTEGER,  
  txt VARCHAR(20),  
  ts1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  
);

Copy Database

mysqldump -u root -p db1 > dump.sql
mysqladmin -u root -p create db2
mysql -u root -p db2 < dump.sql


Table Operation

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

Table Definition

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;


User Management

Check User

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();

Create 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;

Delete User

mysql> drop user USERNAME;
or
mysql> delete from mysql.user where user = 'USERNAME';

Change Password

--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');

CSV Import / Export

ERROR 1045 (28000) at line 1: Access denied for user 'USER'@'localhost' (using password: YES)

mysql> grant file on *.* to USER@localhost;


Tips

--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;


Export to CSV

# mysql -uUSER -pPASS DATABASE -e "select * from TABLE;" | sed -e 's/^/"/g' | sed -e 's/$/"/g' | sed -e 's/\t/","/g' > /tmp/test.csv


Import from 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 Multi colomns

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%' );


References