Chapter 3 Tutorial(指南)
文章目录
Chapter 3 Tutorial(指南)
@[toc]
3.1 连接和断开服务器
3.2 进入查询
3.3 创建和使用数据库
3.3.1 创建和选择数据库
3.3.2 创造表
3.3.3 加载数据到表
3.3.4 从表中检索信息
3.3.4.1 选择所有数据
3.3.4.2 选择特定行
3.3.4.3 选择特定列
3.3.4.4 排序列
3.3.4.5 日期计算
3.3.4.6 空值
3.3.4.7 规则匹配
3.3.4.8 计算行数
3.3.4.9 多表
3.4 获取库和表的信息
3.5 MySQL批量模式
3.6 常用查询例子
3.6.1 某列最大值
3.6.2 某一列最大值的行
3.6.3 每个分组最大值
3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column
3.6.5 用户定义变量
3.6.6 Using Foreign Keys
3.6.7 使用两个Key搜索
3.6.8 Calculating Visits Per Day
3.6.9 使用AUTO_INCREMENT
3.7 Using MySQL with Apache
官方文档：https://dev.mysql.com/doc/
3.1 连接和断开服务器
# 远程登陆, host 主机, user 用户名
mysql -h host -u user -p
Enter password:
mysql>
# 本机登陆
mysql -u user -p
Enter password:
mysql>
# 退出, 注意后面必须跟上分号(;)
mysql> quit;
或者
mysql> exit;
如果是Unix，可以使用Control + D 退出
3.2 进入查询
# 查询版本和当前日期
mysql> SELECT VERSION(), CURRENT_DATE;
# 查当前日期和时间
mysql> SELECT CURRENT_TIMESTAMP;
mysql> SELECT NOW();
# 查询时间
mysql> SELECT CURRENT_TIME;
# 查询当前日期和时间
# 作简单计算
mysql> SELECT SIN(PI()/4),(4+1)*5;
# 多行语句, 用户和日期时间
mysql> SELECT
> USER()
>,
> NOW();
# \c 结束符
mysql> SELECT
> USER()
> \c
提示符：
Prompt
Meaning
mysql>
Ready for new query
->
Waiting for next line of multiple-line query
'>
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 /*
3.3 创建和使用数据库
# 显示库
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
# 访问库,
mysql> USE 库名称;
3.3.1 创建和选择数据库
# 创建库
mysql> CREATE TABLE practice_db;
# 使用库
mysql> use PRACTICE_DBB;
Database changed
# 登陆后直接进入数据库, 后面直接跟数据库名称
shell> mysql -h host -u user -p database_name
Enter password:
mysql>
3.3.2 创造表
# 显示所有表
mysql> SHOW TABLES;
# 创建表
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
# 查看表属性
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.00 sec)
3.3.3 加载数据到表
# 假如有 pet.txt 文件，文件格式如下：以TAB间隔
# Whistler Gwen bird \N 1997-12-09 \N
mysql> LOAD DATA LOCAL INFLE '/tmp/pet.txt' INTO TABLE pet;
Query OK, 1 row affected (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
# 如果是在Windows上创建的文件，以\r\n作为结束一行时需要指定结束符
mysql> LOAD DATA LOCAL INFILE '/tmp/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n';
# 插入数据
mysql> INSERT INTO pet VALUES ('Puffball', 'Jack', 'Dog', 'f', '2020-02-20', NULL);
3.3.4 从表中检索信息
# 语句格式
# what_t-_select -> * 表示所有列
mysql> SELECT what_to_select
> FROM which_table
> WHERE conditions_to_satisfy;
3.3.4.1 选择所有数据
# 查询所有数据
mysql> SELECT * FROM pet;
修改数据表pet的两种方法
将pet数据清空，然后使用load命令从pet.txt文件全部加载进去； mysql> DELETE FROM PET;
mysql> LOAD DATA LOCAL INFILE '/tmp/pet.txt' INTO TABLE pet;
使用UPDATE命令修改。 mysql> UPDATE pet SET birth='1989-08-31' WHERE name='Bowser';
3.3.4.2 选择特定行
mysql> SELECT * FROM pet WHERE sex='f';
mysql> SELECT * FROM pet WHERE birth>='1998-1-1';
mysql> SELECT * FROM pet WHERE species='dog' AND sex='f';
mysql> SELECT * FROM pet WHERE species='snake' OR species='bird';
mysql> SELECT * FROM pet WHERE (species='cat' AND sex='m') OR (species='dog' AND sex='f');
3.3.4.3 选择特定列
mysql> SELECT name,birth FROM pet;
mysql> SELECT owner FROM pet;
# 去重复项
mysql> SELECT DISTINCT owner FROM pet;
# 条件组合
mysql> SELECT name,species,birth FROM pet WHERE species='dog' OR species='cat';
3.3.4.4 排序列
# 默认升序
mysql> SELECT name,birth FROM pet ORDER BY birth;
# 降序
mysql> SELECT name,birth FROM pet ORDER BY birth DESC;
# 使用二进制比较排序
mysql> SELECT name,birth FROM pet ORDER BY BINARY birth;
# 多列排序, species列升序，birth列降序
mysql> SELECT name,species,birth FROM pet order by species,birth DESC;
# 多列排序
mysql> SELECT name,species,birth FROM pet order by name DESC, species ASC, birth DESC;
3.3.4.5 日期计算
mysql> SELECT name,birth,CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;
mysql> SELECT name,birth,CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;
mysql> SELECT name,birth,CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY age;
mysql> SELECT name,birth,death,TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;
# 日期的其它几个函数:
# YEAR() -> 年
# MONTH() -> 月
# DAY() 或者 DAYOFMONTH() -> 日
# WEEK() -> 周
mysql> SELECT name,YEAR(birth),MONTH(birth),DAY(birth),DAYOFMONTH(birth),WEEK(birth) FROM pet;
# 只取5月份的数据
mysql> SELECT name,birth FROM pet WHERE MONTH(birth)=5;
# 使用DATE_ADD(),CURDATE()
mysql> SELECT name,birth FROM pet WHERE MONTH(birth)=MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
# 使用MOD(something,12), 对12取余
mysql> SELECT name,birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
# 当前日期加1天
mysql> SELECT CURDATE() + INTERVAL 1 DAY;
# 当前日期减1天
mysql> SELECT CURDATE() - INTERVAL 1 DAY;
# 日期错误时会有警告信息
mysql> SELECT '2019-5-33' + INTERVAL 1 DAY;
+------------------------------+
| '2019-5-33' + INTERVAL 1 DAY |
+------------------------------+
| NULL |
+------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2019-5-33' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)
3.3.4.6 空值
# 测试 NULL，使用 IS NULL 或者 IS NOT NULL
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
# 不能使用算术比较NULL
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+
# 在MySQL, 0 或者 NULL 表示 false， 其它值表示 true
mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
| 0 | 1 | 0 | 1 |
+-----------+---------------+------------+----------------+
3.3.4.7 规则匹配
MySQL provides standard SQL pattern matching as well as a form of pattern matching based on extended regular expressions similar to those used by Unix utilities such as vi, grep, and sed
# 以b开头的名称
mysql> SELECT * FROM pet WHERE name LIKE 'b%';
# 以fy结尾
mysql> SELECT * FROM pet WHERE name LIKE '%fy';
# 包含w的名称
mysql> SELECT * FROM pet WHERE name LIKE '%w%';
# 查找5个字符的名称
mysql> SELECT * FROM pet WHERE name LIKE '_____';
# REGEXP 一些扩展规则：
# . 匹配任意单个字符
# 字符集合 [...], [abc] 匹配 a,b 或者 c, 字母范围 [a-z] 数字范围 [0-9]
# * 匹配 0 或者 多个
# ^ 匹配开头，$ 匹配结尾
# 使用 REGEXP 匹配
mysql> SELECT * FROM pet WHERE name REGEXP '^b';
# 使用二进行匹配
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';
# 以fy结尾
mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';
# 包含w
mysql> SELECT * FROM pet WHERE name REGEXP 'w';
# 查找5个字符的名称
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
3.3.4.8 计算行数
mysql> SELECT COUNT(*) FROM pet;
# 统计数量
mysql> SELECT owner,COUNT(*) FROM pet GROUP BY owner;
mysql> SELECT species,COUNT(*) FROM pet GROUP BY species;
mysql> SELECT species,sex,COUNT(*) FROM pet GROUP BY species,sex;
mysql> SELECT species,sex,COUNT(*) FROM pet WHERE species='dog' OR species='cat' GROUP BY species,sex;
If you name columns to select in addition to the COUNT() value, a GROUP BY clause should be present that names those same columns. Otherwise, the following occurs:
If the ONLY_FULL_GROUP_BY SQL mode is enabled, an error occurs: mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'menagerie.pet.owner';
this is incompatible with sql_mode=only_full_group_by
If ONLY_FULL_GROUP_BY is not enabled, the query is processed by treating all rows as a single group, but the value selected for each named column is nondeterministic. The server is free to select the value from any row: mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Harold | 8 |
+--------+----------+
1 row in set (0.00 sec)
3.3.4.9 多表
# 新建event table
mysql> CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255));
# 准备event.txt，内容如下，以tab间隔
Fluffy1995-05-15litter4 kittens, 3 female, 1 male
Buffy1993-06-23litter5 puppies, 2 female, 3 male
Buffy1994-06-19litter3 puppies, 3 female
Chirpy1999-03-21vetneeded beak straightened
Slim1997-08-03vetbroken rib
Bowser1991-10-12kennel
Fang1991-10-12kennel
Fang1998-08-28birthdayGave him a new chew toy
Claws1998-03-17birthdayGave him a new flea collar
Whistler1998-12-09birthdayFirst birthday
# 将event.txt导入到event表
mysql> LOAD DATA LOCAL INFILE '/tmp/event.txt' INTO TABLE event;
# The query uses an INNER JOIN to combine the tables. An INNER JOIN permits rows from either table to appear in the result if and only if both tables meet the conditions specified in the ON clause. In this example, the ON clause specifies that the name column in the pet table must match the name column in the event table. If a name appears in one table but not the other, the row will not appear in the result because the condition in the ON clause fails.
# 使用name连接两个表进行查询
mysql> SELECT pet.name, TIMESTAMPDIFF(YEAR,birth,date) AS age, remark FROM pet INNER JOIN event ON pet.name = event.name WHERE event.type='litter';
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
FROM pet AS p1 INNER JOIN pet AS p2
ON p1.species = p2.species
AND p1.sex = 'f' AND p1.death IS NULL
AND p2.sex = 'm' AND p2.death IS NULL;
3.4 获取库和表的信息
# 显示当前选择的数据库, 如果已经选择了某个数据库，则会显示库名称，否则显示NULL
mysql> SELECT DATABASE();
# 显示所有表
mysql> SHOW TABLES;
# 显示表结构属性， DESC是DESCRIBE缩写
mysql> DESCRIBE 表名称;
mysql> DESC 表名称；
3.5 MySQL批量模式
# 将批量处理语句写入一个文件
shell> vi batch.sql
SELECT name from pet;
SELECT birth FROM pet;
SELECT * FROM event;
# 批量执行
shell> mysql -h IP或者HOST -u 用户名称 -p 数据库名称 < batch.sql
# 批量执行分页显示
shell> mysql -h IP或者HOST -u 用户名称 -p 数据库名称 < batch.sql | more
# 将执行结果输出到某个文件
shell> mysql -h IP或者HOST -u 用户名称 -p 数据库名称 < batch.sql > mysql.out
3.6 常用查询例子
Start the command-line tool mysql and select a database:
shell> mysql your-database-name
Create and populate the example talbe, use these statements:
# 建表
CREATE TABLE shop (
article INT UNSIGNED DEFAULT '0000' NOT NULL,
dealer CHAR(20) DEFAULT '' NOT NULL,
price DECIMAL(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));
# 插入数据
INSERT INTO shop VALUES
(1,'A',3.45),
(1,'B',3.99),
(2,'A',10.99),
(3,'B',1.45);
# 查询
SELECT * FROM shop ORDER BY article;
3.6.1 某列最大值
mysql> SELECT MAX(article) AS article FROM shop;
3.6.2 某一列最大值的行
mysql> SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
# Other solutions are to use a LEFT JOIN or to sort all rows descending by price and get only the first row using the MySQL-specific LIMIT clause:
# 每一行都进行比较, s1 作为左边的值,每一生与s2的每一行进行对比，
mysql> SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;
mysql> SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;
如何理解 LEFT JOIN ?
3.6.3 每个分组最大值
mysql> SELECT article, MAX(price) AS price
FROM shop
GROUP BY article
ORDER BY article;
3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column
mysql> SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article)
ORDER BY article;
Uncorrelated subquery:
mysql> SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price
ORDER BY article;
LEFT JOIN:
mysql> SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL
ORDER BY s1.article;
3.6.5 用户定义变量
You can employ MySQL user variables to remember results without having to store them in temporary variables in the client.
mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
3.6.6 Using Foreign Keys
CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
SELECT * FROM person;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+
SELECT s.* FROM person p INNER JOIN shirt s
ON s.owner = p.id
WHERE p.name LIKE 'Lilliana%'
AND s.color <> 'white';
+----+-------+--------+-------+
| id | style | color | owner |
+----+-------+--------+-------+
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+----+-------+--------+-------+
When used in this fashion, the REFERENCES clause is not displayed in the outputted of SHOW CREATE TABLE or DESCRIBE:
SHOW CREATE TABLE shirt\G
*************************** 1. row ***************************
Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3.6.7 使用两个Key搜索
SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR field2_index = '1';
# UNION
SELECT field1_index, field2_index
FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
FROM test_table WHERE field2_index = '1';
3.6.8 Calculating Visits Per Day
CREATE TABLE t1 (year YEAR, month INT UNSIGNED,
day INT UNSIGNED);
INSERT INTO t1 VALUES (2000,1,1), (2000,1,20), (2000,1,30), (2000,2,2),
(2000,2,23), (2000,2,23);
The example table contains year-month-day values representing visits by users to the page. To determine how many different days in each month these visits occur, use this query:
SELECT year,month,BIT_COUNT(BIT_OR(1<
3.6.9 使用AUTO_INCREMENT
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
To start with an AUTO_INCREMENT value other than 1, set that value with CREATE TABLE or ALTER TABLE, like this:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
MyIISAM Notes
For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(*auto_increment_column*) + 1 WHERE prefix=*given-prefix*. This is useful when you want to put data into ordered groups. CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
) ENGINE=MyISAM;
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
Which returns: +--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+
In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.
If the AUTO_INCREMENT column is part of multiple indexes, MySQL generates sequence values using the index that begins with the AUTO_INCREMENTcolumn, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore thePRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.
3.7 Using MySQL with Apache
There are programs that let you authenticate your users from a MySQL database and also let you write your log files into a MySQL table.
You can change the Apache logging format to be easily readable by MySQL by putting the following into the Apache configuration file:
LogFormat \
"\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\", \
\"%U\",\"%{Referer}i\",\"%{User-Agent}i\""
To load a log file in that format into MySQL, you can use a statement something like this:
LOAD DATA INFILE '/local/access_log' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
The named table should be created to have columns that correspond to those that the LogFormat line writes to the log file.
238
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
