1+1=10

记记笔记，放松一下...

Notes on MySQL

MySQL vs mysql

  • MySQL ==> the entire MySQL package
    • The MySQL server (mysqld)
    • MySQL client programs
    • MySQL client library
  • mysql ==> a client program of the MySQL package
    • The MySQL Command-Line Tool

Connecting to server

1
shell> mysql -h host -u user -p

If mysqld is running on the same machine:

1
shell> mysql -u user -p

If mysqld is running on the same machine and permit anonymous users.

1
shell> mysql

Disconnecting from server

1
mysql> quit;

Note, semicolon of this command can be omitted.

Entering Queries

We can do some query operations even without any database to work with.

1
2
3
4
5
6
7
mysql> select version(), current_date, (4+1)*5, user();
+-------------------------+--------------+---------+----------------+
| version()               | current_date | (4+1)*5 | user()         |
+-------------------------+--------------+---------+----------------+
| 5.5.43-0ubuntu0.14.04.1 | 2015-04-05   |      25 | root@localhost |
+-------------------------+--------------+---------+----------------+
1 row in set (0.00 sec)

Type \c if we want to cancel current command.

1
mysql> select user() \c

Meaning of prompts

Prompt Meaning
mysql> Ready for new command.
-> Waiting for next line of multiple-line command.
'> Waiting for next line, waiting for completion of a string that began with a single quote (').
"> Waiting for next line, waiting for completion of a string that began with a double quote (").
`> Waiting for next line, waiting for completion of an identifier that began with a backtick (`).
/*> Waiting for next line, waiting for completion of a comment that began with /*.

Creating and Selecting a Database

Creating a database called db_debao

1
2
mysql> create database db_debao;
Query OK, 1 row affected (0.05 sec)

View databases

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_debao           |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

Create a database does not select it for use, so we must make it the current database explicitly.(Note, semicolon of this command can be omitted.)

1
2
mysql> use db_debao
Database changed

To find out which database currently used

1
2
3
4
5
6
7
mysql> select database();
+------------+
| database() |
+------------+
| db_debao   |
+------------+
1 row in set (0.00 sec)

Important Note, this database is created by the root and will not be accessible to any other user unless permitted by the root. The following command will create a new user call "test" on localhost with the password "password".

1
2
mysql> grant all on db_debao.* to test@localhost identified by "password";
Query OK, 0 rows affected (1.79 sec)

Create a Table

List tables in current database

1
2
mysql> show tables;
Empty set (0.00 sec)

Create a table called pet with three columns: name, sex, birth

1
2
mysql> create table pet (name varchar(20), sex char(1), birth DATE);
Query OK, 0 rows affected (0.19 sec)

View tables now

1
2
3
4
5
6
7
mysql> show tables;
+--------------------+
| Tables_in_db_debao |
+--------------------+
| pet                |
+--------------------+
1 row in set (0.00 sec)

describe can be called at any time to view table column names and types. 

1
2
3
4
5
6
7
8
9
mysql> describe pet;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Loading Data into a Table

The load_data and insert can be used for this task.

Create a plain text file called pet_raw_txt:

1
2
3
4
at1    f       2015-01-01
cat2    m       2015-02-02
cat3    \N      2015-03-03
cat4    f       \N

Try load this file:

1
2
mysql> load data local infile '~/pet_raw.txt' into table pet;
ERROR 1148 (42000): The used command is not allowed with this MySQL version

How to fix this? Add the --local-infile=1 option when start mysql

1
2
3
4
5
$ mysql --local-infile=1  -u root -p
mysql> use db_debao
mysql> load data local infile '~/pet_raw.txt' into table pet;
Query OK, 4 rows affected (0.06 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

If we use insert instead of load data

1
2
3
4
mysql> insert into pet values ('dog1', 'f', '2015-04-01');
Query OK, 1 row affected (0.11 sec)
mysql> insert into pet values ('dog2', 'f', NULL);
Query OK, 1 row affected (0.05 sec)

If we want to empty the table pet:

1
mysql> delete from pet;

Retrieving Information from a Table

Selecting all data

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql> select * from pet;
+------+------+------------+
| name | sex  | birth      |
+------+------+------------+
| cat1 | f    | 2015-01-01 |
| cat2 | m    | 2015-02-02 |
| cat3 | NULL | 2015-03-03 |
| cat4 | f    | NULL       |
| dog1 | f    | 2015-04-01 |
| dog2 | f    | NULL       |
+------+------+------------+
6 rows in set (1.13 sec)

If we want to change something, update can be used.

1
2
3
mysql> update pet set birth = '2015-04-02' where name = 'dog2';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Selecting particular rows:

1
2
3
4
5
6
7
8
9
mysql> select * from pet where birth >= '2015-03-01';
+------+------+------------+
| name | sex  | birth      |
+------+------+------------+
| cat3 | NULL | 2015-03-03 |
| dog1 | f    | 2015-04-01 |
| dog2 | f    | 2015-04-02 |
+------+------+------------+
3 rows in set (0.02 sec)

Selecting particular columns:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql> select name, birth from pet;
+------+------------+
| name | birth      |
+------+------------+
| cat1 | 2015-01-01 |
| cat2 | 2015-02-02 |
| cat3 | 2015-03-03 |
| cat4 | NULL       |
| dog1 | 2015-04-01 |
| dog2 | 2015-04-02 |
+------+------------+
6 rows in set (0.00 sec)

Pattern Matching:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> select name, birth from pet where name like 'cat%';
+------+------------+
| name | birth      |
+------+------------+
| cat1 | 2015-01-01 |
| cat2 | 2015-02-02 |
| cat3 | 2015-03-03 |
| cat4 | NULL       |
+------+------------+
4 rows in set (0.00 sec)

Sorting rows: (order by)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql> select name, birth from pet order by birth;
+------+------------+
| name | birth      |
+------+------------+
| cat4 | NULL       |
| cat1 | 2015-01-01 |
| cat2 | 2015-02-02 |
| cat3 | 2015-03-03 |
| dog1 | 2015-04-01 |
| dog2 | 2015-04-02 |
+------+------------+
6 rows in set (0.00 sec)

Reference

  • http://dev.mysql.com/doc/refman/5.6/en/index.html
  • http://www.webdevelopersnotes.com/tutorials/sql/mysql_database_introduction_mysql_beginners_tutorial.php3

Posted by Debao Zhang tools