Mysql

From Hugme.org

Contents 1 to connect to a mysql server do this 2 Tables 3 Rows 4 Columns 5 Cells 6 Users and Privileges 7 database control 8 Dump/restore

to connect to a mysql server do this

• /usr/local/mysql/bin/mysql --password=[password] --user=[username] -h [server name] [database]

---

Tables

• show tables;
• describe `[table]`;
• create table [tbl_name];
• create table [tbl_name] like [other_table name];
• drop table [tbl_name];

---

Rows

• delete from [table] where [field] = '[string];
• insert into [table] ([field1],[field2],[field3]) values ('[data1]','[data2]','[data3]');

---

Columns

• ALTER TABLE '[table]' DROP '[column]';
• ALTER TABLE [table] ADD [col_name] [col_type]([col_size]);
• alter table `[table]` change [old_col_name] [new_col_name] [new_col_type]([new_col_size]);

---

Cells

• update [table] set [field] = [string] where [field] = '[string]';
• select [field] from [table] where [field] = '[string]';
  • where [field] between "date1" and "date2"

---

Users and Privileges

• create a user
  • create user [user] identified by '[password]';
• give privileges to a user
  • grant [privilege] on [database] to [user];
  • grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on [database].*

to [user]@localhost identified by '[password]';

• • FLUSH PRIVILEGES;
  • show grants for [user]@locahost;
• remove privilege
  • revoke INDEX on [database].* from [user]@localhost;
• list the users
  • SELECT User,Host, Password FROM mysql.user;

database control

• show databases
  • show databases;
• create a database
  • create database [db-name];
• delete a database
  • drop database [db-name];

Dump/restore

• Dump: mysqldump -u root -p -h localhost so > /tmp/so.db
• restore: cat /tmp/so.db | /opt/dps/bin/mysql -u sysorg -h localhost -p so