要求服务器告诉您它的版本号和当前日期。
mysql> SELECT VERSION(), CURRENT_DATE;
+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 5.7.21 | 2018-07-07 |
+-----------+--------------+
1 row in set (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| boot_crm |
| crm |
| music |
| mybatis |
| mysql |
| performance_schema |
| springmvc |
| sys |
| taotao |
| toutiao |
+--------------------+
11 rows in set (0.00 sec)
创建和选择数据库:
创建动物园数据库:
mysql> CREATE DATABASE menagerie;
选择数据库
:
mysql> USE menagerie
如果您收到错误,例如ERROR 1044(42000):在尝试创建数据库时,用户'micah'@'localhost'拒绝访问数据库'menagerie',这意味着您的用户帐户没有必要的权限。
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
查看有多少个表:
mysql> SHOW TABLES;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| pet |
+---------------------+
1 row in set (0.00 sec)
名称,所有者,物种,性别,出生和死亡。
要验证您的表是否按预期方式创建,请使用以下
DESCRIBE
语句:(
您可以随时使用
DESCRIBE
(
叙述
)
,例如,如果您忘记了表中列的名称或它们具有的类型。
)
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)
您可以创建一个文本文件
pet.txt
,每行包含一个记录,其值由制表符分隔,并按照
CREATE TABLE
语句
中列出的顺序给出
。
对于缺失值(例如未知性别或仍然生活的动物的死亡日期),您可以使用
NULL
值。
要在文本文件中表示这些,请使用
\N
(反斜杠,大写-N)。
例如,惠斯勒鸟的记录看起来像这样(值之间的空格是单个制表符):
Whistler Gwen bird \N 1997-12-09 \N
要将文本文件加载pet.txt到 pet表中(
path必须用/
)
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
例如
mysql> LOAD DATA LOCAL INFILE 'E:/MySQL/database/pet.txt'INTO TABLE pet;
添加一条新记录
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
从表中检索信息:
该SELECT语句用于从表中提取信息。声明的一般形式是:
SELECT what_to_select 表示你想看到什么。
FROM which_table 表示要从中检索数据的表。
WHERE conditions_to_satisfy; 指定行必须满足的一个或多个条件才有资格进行检索。
最简单的形式SELECT 从表中检索所有内容:
mysql> SELECT * FROM pet;
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)
清空表
mysql> DELETE FROM pet;
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
简单的SELECT操作:(
字符串比较通常不区分大小写
)
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 birth >= '1998-1-1';
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f'; //AND 逻辑运算符
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
-> OR (species = 'dog' AND sex = 'f'); //AND, OR可以混合,但 AND优先级高于 OR。
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 |
+----------+------------+
8 rows in set (0.00 sec)
其他测试:
mysql> SELECT owner FROM pet;
DISTINCT:输出不同
mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Benny |
| Diane |
+--------+
4 rows in set (0.00 sec)
mysql> SELECT owner FROM pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Harold |
| Benny |
| Diane |
| Gwen |
| Gwen |
| Benny |
+--------+
8 rows in set (0.00 sec)
仅获取狗和猫的出生日期
mysql> SELECT name, species, birth FROM pet
-> WHERE species = 'dog' OR species = 'cat';
+--------+---------+------------+
| name | species | birth |
+--------+---------+--------