GroovyGrails:: Home Page

MySql Queries

Create database on the sql server
```
mysql> create database database_name;
```
List all the database on the sql server
```
mysql> show databases;
```
Switch database
```
mysql> use database_name;
```
List all the tables in the database
```
mysql> show tables;
```

Show database field formats

mysql> desc table_name; / describe table_name;

To delete a database
```
mysql> drop database database_name;
```
To delete a table
```
mysql> drop table table_name;
```
Show all data in a table
```
mysql> select * from table_name;
```
Gives columns and column information related to designated table
```
mysql> show columns from table_name;
```

Show certain selected rows with the value "whatever".

mysql> select * from table_name where field_name = "whatever";

Show all records containing the name "peter" and deptno '121'

mysql> select * from table_name where name="peter" and deptno=121;

Show all records containing the name "peter" and deptno=121 order by the deptno field
```
mysql> select * from table_name where name="peter" and deptno=121 order by deptno;
```

Show all records not containing the name "peter"

mysql> select * from table_name where name !="peter";

Show all records starting with letter "p"

mysql> select * from table_name where name like "p%";

Show all records starting with letter "p" limit to record 1 through 5
```
mysql> select * from table_name where name like "p%" limit 1,5;
```

Show unique records

mysql> select distinct column_name from table_name;

Show selected records sorted in an ascending (asc) or descending(desc).
```
mysql> select column1, column2 from table_name order by column1 desc;
```
Return number of rows
```
mysql> select COUNT(*) from table_name;
```
Sum column
```
mysql> select SUM(*) from table_name;
```
Set a root password if there is on root password.
```
# mysqladmin -u root password newpassword
```

Update a root password.

# mysqladmin -u root -p oldpassword newpassword

Update data already in table

mysql> update table_name set name="xyz" where name="peter";

Delete row from a table

delete from table_name where field_name = "whatever";

Delete column from a table

mysql> alter table table_name  drop column column_name;

Add a new column to table

mysql> alter table user add column new_column_name varchar(30);

Make a unique column so you get no duplicate

mysql> alter table table_name add unique column_name;

Make a column bigger.

mysql> alter table table_name modify column_name varchar(10);

Delete unique from table.

mysql> alter table table_name drop index colmn_name;

Load a CSV file into a table

mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' 
(field1,field2,field3);

Dump all databases for backup. Backup file is sql commands to recreate all databases
```
# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/databases.sql
```

Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/database.sql

Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Create Table

mysql>create table table_name (id int(20) not null auto_increment primary key, firstname VARCHAR(20), lastname VARCHAR(30), officeid 
VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp 
time);

Table join

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

MySql Queries

Tutorials Available