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
or
# ./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 -p password FoldRNA > foldrna.sql #To Backup
# mysql -p password FoldRNA < foldrna.sql #To Restore
Links to similar pages:
-Click-
-Click-