MySQL Cheatsheet
To start mysql at dnaserver:
# mysql -pTo 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.sqlor
# ./mysqlquery4.shTo 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-