1 创建与使用数据库
1.1 创建数据库
mysql> show databases;+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database sample;
Query OK, 1 row affected (0.00 sec)
1.2 切换数据库
使用'use'切换到某个数据库：mysql> use sample
Database changed
也可以在shell使用如下的办法，直接登录：
db2a:~ # mysql -h localhost -u root -p sample
1.3 删除数据库
MySQL使用drop database <dbname>来删除某个数据库mysql> drop database sample;
Query OK, 0 rows affected (0.05 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
2. 表基本操作
2.1 创建表
在sample数据库下创建一个名为pet的表，这个表主要记录每个宠物的名子、主人、种类、性别、出生日期和死亡日期mysql> create database sample;
mysql> use sample;
mysql> show tables;
Empty set (0.00 sec)
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)
mysql> create table pet2 like pet;
Query OK, 0 rows affected (0.06 sec)
mysql> show tables;
+------------------+
| Tables_in_sample |
+------------------+
| pet |
| pet2 |
+------------------+
2 rows in set (0.00 sec)
2.2 删除表
mysql> drop table pet2;Query OK, 0 rows affected (0.10 sec)
2.3 查看表的定义
查看表字段结构：mysql> 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)
查看表的创建语句：
mysql> show create table pet \G
*************************** 1. row ***************************
Table: pet
Create Table: CREATE TABLE `pet` (
`name` varchar(20) DEFAULT NULL,
`owner` varchar(20) DEFAULT NULL,
`species` varchar(20) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`birth` date DEFAULT NULL,
`death` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
2.4 插入数据
使用load工具从文件装入：pet.txt内容如下，列与列之间用TAB隔开，\N表示NULL值。
db2a:~ # cat pet.txt
Fluffy Harold cat f 1993-02-04 \N
Claws Gwen cat m 1994-03-17 \N
Buffy Harold dog f 1989-05-13 \N
Fang Benny dog m 1990-08-27 \N
Bowser Diane dog m 1989-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11 \N
Whistler Gwen bird \N 1997-12-09 \N
Slim Benny snake m 1996-04-29 \N
mysql> load data local infile '/root/pet.txt' into table pet;
Query OK, 8 rows affected (0.28 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
直接使用INSERT语句：
mysql> INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.01 sec)
2.5 查询数据
查询所有数据：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 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)
查询符合条件的固定行：
mysql> SELECT * FROM pet WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
1 row in set (0.00 sec)
同时符合两个条件的记录：
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'm';
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
2 rows in set (0.00 sec)
符合任何一个条件的记录：
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+----------+-------+---------+------+------------+-------+
3 rows in set (0.00 sec)
查询固定列：
mysql> SELECT name, birth FROM pet;
+----------+------------+
| name | birth |
+----------+------------+
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+
9 rows in set (0.00 sec)
根据出生日期排序：
mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name | birth |
+----------+------------+
| Buffy | 1989-05-13 |
| Bowser | 1989-08-31 |
| Fang | 1990-08-27 |
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Slim | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+
9 rows in set (0.04 sec)
根据出生日期降序排列：
mysql> SELECT name, birth FROM pet ORDER BY birth desc;
mysql除了SQL匹配外，还支持正则表达式，下面两种方式都可以查询到名子以B开头的宠物名：
mysql> SELECT * FROM pet WHERE name LIKE 'b%';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM pet WHERE name REGEXP '^b';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)
统计总行数：
mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
1 row in set (0.00 sec)
可以不在mysql shell，而直接在系统命令行执行查询：
db2a:~ # mysql -u root -p sample --execute="SELECT name, birth FROM pet ORDER BY birth desc"
Enter password:
+----------+------------+
| name | birth |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Claws | 1994-03-17 |
| Fluffy | 1993-02-04 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Buffy | 1989-05-13 |
+----------+------------+
如果不想交互输入密码，可以直接在-p后面跟上密码，注意中间不要有空格：
db2a:~ # mysql -u root -pqingsong sample --execute="SELECT name, birth FROM pet ORDER BY birth desc"
2.6 修改数据
将Whistler的主人改为Diane:mysql> update pet set owner = 'Diane' where name = 'Whistler';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from pet where name = 'Whistler';
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Whistler | Diane | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)
宠物Buffy于1996-07-31号死亡：
mysql> update pet set death = '1996-07-31' where name = 'Buffy';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from pet where name = 'Buffy';
+-------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | 1996-07-31 |
+-------+--------+---------+------+------------+------------+
1 row in set (0.00 sec)
2.7 删除记录
将类别为snake的记录删掉：mysql> delete from pet where species = 'snake';
Query OK, 1 row affected (0.00 sec)
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 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | 1996-07-31 |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Diane | bird | NULL | 1997-12-09 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.00 sec)