Friday, June 12, 2015

MySQL Commands

Login via MySql client:
$ mysql -u XXXXX -pXXXXX db_name
Create a new user:
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';
GRANT ALL ON
*.* TO 'user1'@'localhost';
CREATE USER
'user1'@'%' IDENTIFIED BY 'pass1';
GRANT ALL ON
*.* TO 'user1'@'%';
List users:
select host, user, password, Create_priv from mysql.user;
List databases and tables:
show databases;
show tables
;
Rename column:
ALTER TABLE Article change article_id id bigint(20);
Make column non-null:
ALTER TABLE Person MODIFY firstName varchar(255) NOT NULL;
Make column unique:
ALTER TABLE Person ADD UNIQUE INDEX(memberId);
Calculate the database size:
SELECT table_schema "DB Name", sum(data_length + index_length) / 1024 / 1024  "DB size in MB" FROM information_schema.TABLES GROUP BY table_schema;

0 comments: