MySQL

Submitted by code_admin on Wed, 07/11/2018 - 18:46

Login

mysql -h127.0.0.1 -uroot -p<>

Usage

No Database
show databases;
drop database $$DB Name$$;
create database $$DB Name$$;
use $$DB Name$$;
source $$FILENAME$$Run a pre-prepared script

User Management

show grants for $$USER NAME$$;
use mysql;
select user, host from user;List users
grant ALL on $$DB Name$$.$$TABLE NAME$$ TO $$USER NAME$$@127.0.0.1 IDENTIFIED BY '$$USER PASS$$';Create a new user
use . for all databases and tables, use $$DB Name$$.* to all tables in a database
Omit @127.0.0.1 for connecting from anywhere.
drop user $$User Name$$@$$Host$$;Delete a user.
To drop a user you must specify the host. Use select user, host from user; to find out what the host is. If it's % user '%' e.g. user@'%'

Database selected

show tables;
show fields from $$TABLE Name$$;
show create table $$TABLE Name$$;

Example DDL

  1. CREATE TABLE `food_diary_entry` (
  2. `PK_diary_entry` int(11) NOT NULL AUTO_INCREMENT,
  3. `time_input` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  4. `time_eaten` TIMESTAMP,
  5. `FK_meal` int (11) NOT NULL,
  6. `FK_item` int(11) NOT NULL,
  7. `FK_portion_unit` int(11) NOT NULL,
  8. `Quantity` int(11) NOT NULL DEFAULT 1,
  9. `UNI_CHECK` varchar(255) NOT NULL,
  10. `DELETED` int(11) NOT NULL DEFAULT 0,
  11. PRIMARY KEY (`PK_diary_entry`)
  12. );

Tags

RJM Article Type
Quick Reference