MySQL Commands
Friday, January 25th, 2008MySQL commands I use but can never remember:
- Logon> mysql –user=userame –password=password
- Create a database> create database richard_projects character set utf8;
- Explain> explain select * from projects
- Alter Table> alter table PROJECTS add primary key (ID);
- Add Foreign Key> alter table`USER_ROLE` add foreign key(`USER_ID`) references `USER`(`ID`) on delete cascade on update cascade;
- Create Index> create index USER_PASSWORD on USER(PASSWORD);
- Create Unique Index> create unique index CATEGORY_NAME on CATEGORY(NAME);
- Insert> insert into CATEGORY VALUES (6, ‘Food’,'Generic Food Category’);
- Update> update CATEGORY set NAME=’Other’ ,DESCR =’Other’ where NAME=’Food’;
- Delete> delete from CATEGORY where NAME=’Food’;
User Management
Add a user with both remote and local access:
- Grant>GRANT ALL PRIVILEGES ON *.* TO ‘richard’@'localhost’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;
- Grant>GRANT ALL PRIVILEGES ON *.* TO ‘richard’@'%’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;