MySQL Cheatsheet

To start mysql at dnaserver:

# mysql -p

To create a database:

mysql> create database FoldRNA;

To query which databases have been created:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| FoldRNA            |
| test               |
+--------------------+
3 rows in set (0.05 sec)

To create a table describing the types of each field:

mysql> create table step_param(seq_num CHAR(5), chain_num VARCHAR(5));

To select a database, and then show the tables inside of it, and then show the fields in the table:

mysql> use FoldRNA;
mysql> show tables;
+-------------------+
| Tables_in_FoldRNA |
+-------------------+
| step_param        |
+-------------------+
1 row in set (0.00 sec)
mysql> show columns from step_param;

To show the fields and values in table step_param and then to insert new values to the table:

mysql> select * from step_param;
+----------+-------+-------+------+
| step_num | Shift | Slide | Rise |
+----------+-------+-------+------+
|        2 |     3 |     5 |    2 |
|        1 |     4 |     2 |    3 |
+----------+-------+-------+------+
2 rows in set (0.00 sec)

mysql> insert into step_param (step_num, Shift, Slide, Rise) values (3, 2, 3, 4);

To delete the values in a table without deleting the table fields:

mysql> delete from step_param;

To change a value in a table (change 3 to 4 in column step_num):

mysql> UPDATE step_param SET step_num = "3" where step_num = "4"

To import a tab separated table from a local file:

mysql> load data local infile 'steparams.tab' into table step_param;

To show the first five rows of a table use limit 5:

mysql> select * from step_param limit 5;
+----------+-----------+---------+-------+-------+-------+--------+-------+-------+-------+-------+
| step_num | chain_num | base_id | Shift | Slide | Rise  | Tilt   |Roll   | Twist | extra | intra |
+----------+-----------+---------+-------+-------+-------+--------+-------+-------+-------+-------+
|       10 |         1 | U/A     | -0.82 | -0.37 | 10.34 | -78.69 | 30.95 | 18.69 |  1.01 |  0.04 | 
|       11 |         2 | A/U     |  2.93 | -1.34 |  3.09 |   3.75 |  5.82 | 57.45 |  5.80 |  2.94 | 
|       12 |         3 | U/G     |  5.51 | -1.92 |  3.20 |   1.70 |  8.63 | 53.28 |  0.94 |  0.00 | 
|       13 |         4 | G/C     | -0.42 | -1.84 |  3.52 |  -4.16 |  3.23 | 36.01 |  3.91 |  1.26 | 
|       14 |         5 | C/C     | -0.07 | -1.52 |  3.14 |   5.46 | 10.86 | 29.98 |  0.51 |  0.00 | 
+----------+-----------+---------+-------+-------+-------+--------+-------+-------+-------+-------+
5 rows in set (0.00 sec)

To count how many fields of something in table just change the general * to COUNT(*) (Notice that RLIKE is used to query using regular expressions.):

mysql> SELECT COUNT(*) FROM step_param WHERE base_id = "C/G" AND Tilt RLIKE "^\\-1.";
+----------+
| COUNT(*) |
+----------+
|       13 | 
+----------+
1 row in set (0.02 sec)

mysql> SELECT * FROM step_param WHERE base_id = "C/G" AND Tilt RLIKE "^\\-1." limit 5;
+----------+-----------+---------+-------+-------+------+-------+-------+-------+-------+-------+
| step_num | chain_num | base_id | Shift | Slide | Rise | Tilt  | Roll  | Twist | extra | intra |
+----------+-----------+---------+-------+-------+------+-------+-------+-------+-------+-------+
|      156 |       145 | C/G     |  0.74 | -2.39 | 3.35 | -1.63 |  7.08 | 29.34 |  0.00 |  0.00 | 
|      364 |       353 | C/G     |  0.72 | -1.33 | 3.42 | -1.79 | 17.90 | 29.81 |  1.17 |  0.40 | 
|      764 |       752 | C/G     |  2.85 | -1.95 | 3.60 | -1.71 |  2.65 | 41.64 |  1.11 |  0.18 | 
|      890 |       878 | C/G     |  0.87 | -1.71 | 3.31 | -1.62 |  3.94 | 32.06 |  0.36 |  0.06 | 
|     1142 |      1102 | C/G     | -0.73 | -2.88 | 3.51 | -1.21 |  4.20 | 17.48 |  0.00 |  0.00 | 
+----------+-----------+---------+-------+-------+------+-------+-------+-------+-------+-------+
5 rows in set (0.00 sec)

To make a batch query with a script.

# mysql -u username -p password < query1.sql

# ./mysqlquery4.sh

To backup your database use mysqldump. Remember to create the database before restoring the backup, in case it doesn't exist in the server where it's being restored.

# mysqldump -ppassword FoldRNA > foldrna.sql  #To Backup
# mysql -u username -ppassword FoldRNA < foldrna.sql  #To Restore

Links to similar pages:
-Click-
-Click-