MySQL Command Line Tips
MySQL Command Line Tips
SCLinux.org – Brion Hase
Why Learn MySQL
Huge installed base - 4 million MySQL installations worldwide and 30,000 downloads of the software per day.
Free database to start/continue learning SQL with.
Runs great on Linux, very stable and very fast.
Open Source Software – you can make modifications to the source code.
Transactional support, online backups, clustering
Most Recent Releases
MySQL 3.23.57 released this week is current production version of version 3.
MySQL 4.0.13 is the current production version of version 4.
Support for sub queries and derived tables
Speed enhancements
New functionality - Create table extensions - “CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table LIKE table”
New functionality - Support for OpenGIS (Spatial Extensions)
Usability enhancements – Command line help – “HELP select”
Usability enhancements – Multi-line queries can be enabled - issue several queries in a single call and then read all the results in one go
Usability enhancements – new “INSERT … ON DUPLICATE KEY UPDATE” syntax
Usability enhancements – new aggregate function “GROUP_CONCAT()” or “Group By” functions such as “Count(*)”, “COUNT(DISTINCT results)”, “AVG(expr)”, “MIN(expr)”, “MAX(expr)”, “SUM(expr)”, “GROUP_CONCAT(expr)”, “VARIANCE(expr), …
ODBC/Connector – current production version is 3.51.06. Allows you to connect to a MySQL database server using the ODBC database API on all Microsoft Windows and most Unix platforms, including through such applications and programming environments such as Microsoft Access, Microsoft Excel, and Borland Delphi.
Connecting to Database
# mysql -u -h -p
# mysql –p (Don’t put the password on the command line)# mysql mysql –p
Updating a user’s password
mysql> use mysql;
mysql> update user where user =’root’ set Password = PASSWORD(’
‘);mysql> flush privileges;
Creating database
mysql> create database testdb;
Query OK, 1 row affected (0.23 sec)
Listing databases
mysql> show databases;
+———–+
| Database |
+———–+
| bsrboard |
| mysql |
| rideboard |
| slugmb |
| temp |
| testdb |
+———–+
6 rows in set (0.01 sec)
Selection/Changing Database
mysql> use testdb;
Dropping Databases
mysql> create database testdb2;
mysql> drop table testdb2;
Listing Tables
mysql> use mysql;
mysql> show tables;
+—————–+
| Tables_in_mysql |
+—————–+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+—————–+
6 rows in set (0.05 sec)
Creating Tables
mysql> use testdb;
mysql> create table test_table (
-> lname varchar(30),
-> fname varchar(30),
-> age int(3),
-> birthdate date,
-> bio varchar(250),
è lucky_number int);
mysql> create table test_table2 (field1 int);
mysql> show tables;
+——————+
| Tables_in_testdb |
+——————+
| test_table |
| test_table2 |
+——————+
2 rows in set (0.01 sec)
Dropping Tables
mysql> drop table test_table2;
mysql> show tables;
+——————+
| Tables_in_testdb |
+——————+
| test_table |
+——————+
1 row in set (0.00 sec)
Describing Tables
mysql> describe test_table;
mysql> SHOW COLUMNS FROM test_table; (alternate from describe)
+————–+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————–+————-+——+—–+———+——-+
| lname | varchar(30) | YES | | NULL | |
| fname | varchar(30) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| birthdate | date | YES | | NULL | |
| lucky_number | int(11) | YES | | NULL | |
+————–+————-+——+—–+———+——-+
5 rows in set (0.00 sec)
Inserting Data Into Tables
mysql> insert into test_table (lname, fname, age, birthdate, lucky_number)
è values (’Doe’, ‘John’, 42, ‘1961-01-01’, 44);
Alternate Method for Inserting Data Into Tables
mysql> insert into test_table set
è lname = ‘Smith’,
è fname = ‘Joe’,
è age = ‘21’,
è birthdate = ‘1984-08-13’,
è lucky_number = 7;
Displaying Data in Tables
mysql> select * from test_table;
+——-+——-+——+————+————–+
| lname | fname | age | birthdate | lucky_number |
+——-+——-+——+————+————–+
| Doe | John | 42 | 1961-01-01 | 44 |
| Smith | Joe | 21 | 1984-08-13 | 7 |
+——-+——-+——+————+————–+
2 rows in set (0.00 sec)
Deleting Data From Tables
delete from testdb; (this deletes all rows in table)
mysql> delete from test_table where lname = ‘Smith’;
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_table;
+——-+——-+——+————+————–+
| lname | fname | age | birthdate | lucky_number |
+——-+——-+——+————+————–+
| Doe | John | 42 | 1961-01-01 | 44 |
+——-+——-+——+————+————–+
1 row in set (0.00 sec)
Adding Columns to Tables
mysql> alter table test_table add column bio varchar(250);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> describe test_table;
+————–+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————–+————–+——+—–+———+——-+
| lname | varchar(30) | YES | | NULL | |
| fname | varchar(30) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| birthdate | date | YES | | NULL | |
| lucky_number | int(11) | YES | | NULL | |
| bio | varchar(250) | YES | | NULL | |
+————–+————–+——+—–+———+——-+
6 rows in set (0.00 sec)
Dropping Columns from Tables
mysql> alter table test_table drop column bio;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
Adding Primary Key
alter table test_table modify lname varchar(40) not null;
alter table test_table modify fname varchar(40) not null;
alter table test_table add primary key (lname, fname);
INSERT INTO test_table (lname, fname, age, birthdate, lucky_number)
è values (’Smith’, ‘Joe’, 21, ‘1984-02-13′, 7);
mysql> select * from test_table;
+——-+——-+——+————+————–+
| lname | fname | age | birthdate | lucky_number |
+——-+——-+——+————+————–+
| Doe | John | 42 | 1961-01-01 | 44 |
| Smith | Joe | 21 | 1984-02-13 | 7 |
+——-+——-+——+————+————–+
2 rows in set (0.01 sec)
Using mysqldump commands to create SQL Command File
# mysqldump testdb –p >database.sql
# cat database.sql
– MySQL dump 8.22
–
– Host: localhost Database: testdb
———————————————————
– Server version 3.23.53-log
–
– Table structure for table ‘test_table’
–
CREATE TABLE test_table (
lname varchar(40) NOT NULL default ‘’,
fname varchar(40) NOT NULL default ‘’,
age int(3) default NULL,
birthdate date default NULL,
lucky_number int(11) default NULL,
PRIMARY KEY (lname,fname)
) TYPE=MyISAM;
–
– Dumping data for table ‘test_table’
–
INSERT INTO test_table VALUES (’Doe’,'John’,42,’1961-01-01′,44);
INSERT INTO test_table VALUES (’Smith’,'Joe’,21,’1984-02-13′,7);
Using SQL command file to modify database
Make modifications to SQL file above:
DROP TABLE test_table;
CREATE TABLE test_table (
lname varchar(40) NOT NULL default ‘’,
fname varchar(40) NOT NULL default ‘’,
age int(3) default NULL,
birthdate date default NULL,
lucky_number int(11) default NULL,
fav_color varchar(20),
PRIMARY KEY (lname,fname)
) TYPE=MyISAM;
–
– Dumping data for table ‘test_table’
–
INSERT INTO test_table VALUES (’Doe’,'John’,42,’1961-01-01′, 44,’Red’);
INSERT INTO test_table VALUES (’Smith’,'Joe’,21,’1984-02-13′, 7,’Blue’);
Now use the following command to load table changes:
# mysql testdb –p < database.sql
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3207 to server version: 3.23.53-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
# mysql testdb –p
mysql> use testdb;
Database changed
mysql> select * from test_table;
+——-+——-+——+————+————–+———–+
| lname | fname | age | birthdate | lucky_number | fav_color |
+——-+——-+——+————+————–+———–+
| Doe | John | 42 | 1961-01-01 | 44 | Red |
| Smith | Joe | 21 | 1984-02-13 | 7 | Blue |
+——-+——-+——+————+————–+———–+
2 rows in set (0.00 sec)
Originally published June 2003.