Wednesday, May 27, 2015

Mysql note

Not all commands need to be in the same line. For example:


mysql> select 
    -> user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)


If you decide not to execute the command in the process of entering it, type \c to cancel it:

mysql> select 
    -> user()
    -> ,now()
    -> \c
mysql> 


The table that summarizes the meaning of each prompt:

PromptMeaning
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 /*.


Create a table:

mysql> create table pet (name varchar(20), owner varchar(20),
    -> species varchar(20), sex char(1),birth DATE, death DATE);
Query OK, 0 rows affected (0.02 sec)


Use describe to see the information of the table:



describe pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)


Load data to table:


mysql> load data local infile '/path/pet.txt' into table pet;
Query OK, 8 rows affected, 9 warnings (0.00 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 9

mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | 0000-00-00 |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | 0000-00-00 |
| Whistler | Gwen   | bird    | N    | 1997-12-09 | 0000-00-00 |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.00 sec) 


Insert values:


mysql> insert into pet 
    -> values ('Jiemee', 'Shirley','cat','f', '2000-01-20', '2014-10-28');
Query OK, 1 row affected (0.01 sec)

mysql> select * from pet where name = 'Jiemee';
+--------+---------+---------+------+------------+------------+
| name   | owner   | species | sex  | birth      | death      |
+--------+---------+---------+------+------------+------------+
| Jiemee | Shirley | cat     | f    | 2000-01-20 | 2014-10-28 |
+--------+---------+---------+------+------------+------------+
1 row in set (0.00 sec)



No comments:

Post a Comment