1.连接和断开服务器
shell>mysql -h
Enter password:host
-uuser
-p********
mysql> QUIT #或者使用 \q ,在Unix中也可以使用ctrl+D 不需要分号来结束
Bye
2.查询
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> SELECT SIN(PI()/4), (4+1)*5;#将把表达式作为列名
提示符 | 含义 |
---|---|
mysql> | 可以输入新命令 |
-> | 等待跨行命令结束 |
'> | 期待字符串的结束的单引号(') |
"> | 期待字符串结束的双引号(“) |
`> | 期待标识符结束的反引号(·). |
/*> | 期待注释的结束(*/) |
3.创建、使用数据库
mysql> SHOW DATABASES; #
查看已有数据库,不显示无权访问的数据库
mysql> USE test # 使用一个数据库,USE不需要分号来结束
Database changed
mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host'; #授权访问数据库
menagerie.*
3.1 创建、选择一个数据库
mysql> CREATE DATABASE menagerie; #在Unix环境下数据库名,表名等大小写敏感
mysql> USE menagerie #选择数据库menagerie
Database changed
shell>mysql -h
Enter password:host
-uuser
-p menagerie #在启动时选择数据库menagerie********
mysql>SELECT DATABASE();#查看当前的数据库
3.2 建立一个表
mysql>CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
->species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
mysql> SHOW TABLES; #查看当前数据库下的表
mysql> DESCRIBE pet; #查看表的结构
3.3 把数据载入表中
mysql>LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
->LINES TERMINATED BY '\r\n';
mysql>INSERT INTO pet
->VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
3.4 从表中检索信息
3.4.1 选择所有数据
mysql> SELECT * FROM pet;
3.4.2选择特殊行
mysql> SELECT * FROM pet WHERE name = 'Bowser';
3.4.3选择特殊列
mysql> SELECT name, birth FROM pet;
mysql> SELECT DISTINCT owner FROM pet; #
DISTINCT 使输出唯一
3.4.4 对结果排序
mysql> SELECT name, birth FROM pet ORDER BY birth; #ORDER BY BINARY col_name
. 使大小写敏感,默认asc,降序desc
mysql>SELECT name, species, birth FROM pet #多列排序,不同列可使用不同规则
->ORDER BY species, birth DESC;
3.4.5 日期计算
mysql>SELECT name, birth, CURDATE(), #TIMESTAMPDIFF(YEAR,birth,CURDATE()) 计算两个时间的year差值
->TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
->FROM pet;
MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH))
3.4.6 NULL值
NULL
means
“a missing unknown value”
IS NULL
和
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 |
+----------+-----------+----------+----------+
GROUP BY
.中的NULL值被认为是相等的
ORDER BY
中的NULL被认为是最小的
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.4.7模式匹配
REGEXP
和
NOT REGEXP
或者
RLIKE
和
NOT RLIKE
,
3.4.8 计数
mysql> SELECT COUNT(*) FROM pet;
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
+--------+----------+
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex; #根据group条件计数
ONLY_FULL_GROUP_BY
模式启用
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): Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause
ONLY_FULL_GROUP_BY
模式禁用,每一行不可分辨
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.4.9 使用多张表
mysql>CREATE TABLE event (name VARCHAR(20), date DATE,
->type VARCHAR(15), remark VARCHAR(255));
mysql>SELECT pet.name,
->(YEAR(date)-YEAR(birth)) -
(RIGHT(date,5)<RIGHT(birth,5))
AS age,
->remark
->FROM pet INNER JOIN event
->ON pet.name = event.name
->WHERE event.type = 'litter';
+--------+------+-----------------------------+ | name | age | remark | +--------+------+-----------------------------+ | Fluffy | 2 | 4 kittens, 3 female, 1 male | | Buffy | 4 | 5 puppies, 2 female, 3 male | | Buffy | 5 | 3 puppies, 3 female | +--------+------+-----------------------------+
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 p2.sex = 'm';
+--------+------+--------+------+---------+ | name | sex | name | sex | species | +--------+------+--------+------+---------+ | Fluffy | f | Claws | m | cat | | Buffy | f | Fang | m | dog | | Buffy | f | Bowser | m | dog | +--------+------+--------+------+---------+
FROM pet AS p1
INNER JOIN pet AS p2 ON p1.species = p2.species
WHERE p1.sex = 'f' AND p2.sex = 'm';
+---------+--------+--------+ | Species | Female | Male | +---------+--------+--------+ | cat | Fluffy | Claws | | dog | Buffy | Fang | | dog | Buffy | Bowser | +---------+--------+--------+
4. 得到数据库和表的信息
mysql> SELECT DATABASE(); #得到当前数据库
mysql> SHOW TABLES;
mysql> DESCRIBE pet;
5.批模式使用sql
shell> mysql < batch-file
C:\> mysql -e "source batch-file
" #含有引起问题的特殊字符
shell>mysql -h
Enter password:host
-uuser
-p <batch-file
********
shell> mysql < batch-file
| more
shell> mysql < batch-file
> mysql.out
SELECT DISTINCT species FROM pet
+---------+ | species | +---------+ | bird | | cat | | dog | | hamster | | snake | +---------+
species bird cat dog hamster snake
mysql>source
mysql>filename
; #在提示符下运行批命令\.
filename
6 常见的查询例子
CREATE TABLE shop ( article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL, price DOUBLE(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), (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
SELECT * FROM shop; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | A | 3.45 | | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | B | 1.45 | | 0003 | C | 1.69 | | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---------+--------+-------+
6.1 列的最大值
SELECT MAX(article) AS article FROM shop; +---------+ | article | +---------+ | 4 | +---------+
6.2 列最大值所在行
SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop); +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0004 | D | 19.95 | +---------+--------+-------+
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL; SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;
6.3 每组,列的最大值
SELECT article, MAX(price) AS price FROM shop GROUP BY article; +---------+-------+ | article | price | +---------+-------+ | 0001 | 3.99 | | 0002 | 10.99 | | 0003 | 1.69 | | 0004 | 19.95 | +---------+-------+
6.4拥有某个字段的组间最大值的行
SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article); +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | C | 1.69 | | 0004 | D | 19.95 | +---------+--------+-------+
Uncorrelated subquery:
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;
LEFT JOIN
:
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;
6.5. 使用用户变量
mysql> SELECT
@min_price:=MIN(price)
,@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
6.6. 使用外键
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 | +----+---------+--------+-------+
SHOW CREATE TABLE
or
DESCRIBE
: 不会显示出REFERENCES person(id),6.7. 根据两个键搜索
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' OR field2_index = '1'
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';
6.8. 根据天计算访问量
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL); INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2), (2000,2,23),(2000,2,23);计算每个月中用户访问网页的天数
SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month;
+------+-------+------+ | year | month | days | +------+-------+------+ | 2000 | 01 | 3 | | 2000 | 02 | 2 | +------+-------+------+
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;
Which returns:
+----+---------+ | id | name | +----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | +----+---------+
LAST_INSERT_ID()SQL function mysql_insert_id()
C API 最新的AUTO_INCREMENT值
对于多行插入,LAST_INSERT_ID()和mysql_insert_id()从插入的第一行实际返回AUTO_INCREMENT关键字。
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100; #通过CREATE TABLE或ALTER TABLE来设置
AUTO_INCREMENT初始值
MyISAM Notes
MAX(auto_increment_column
) + 1 WHERE prefix=given-prefix
.
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;
+--------+----+---------+ | 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 biggestAUTO_INCREMENT
value in any group. This happens even forMyISAM
tables, for whichAUTO_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 theAUTO_INCREMENT
column, if there is one. For example, if theanimals
table contained indexesPRIMARY KEY (grp, id)
andINDEX (id)
, MySQL would ignore thePRIMARY KEY
for generating sequence values. As a result, the table would contain a single sequence, not a sequence pergrp
value.
当定义PRIMARY KEY时 如果AUTO_INCREMENT的列出现在其它列后,当前面的列满足时才会增加,否则从头开始
8. 与Apache一起使用MySQL
你可以将以下内容放到Apache配置文件中,更改Apache日志格式,使MySQL更容易读取:
LogFormat \
"\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\", \
\"%U\",\"%{Referer}i\",\"%{User-Agent}i\""
要想将该格式的日志文件装载到MySQL,你可以使用以下语句:
LOAD DATA INFILE '/local/access_log' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
所创建的表中的列应与写入日志文件的LogFormat行对应。