数据库操作与SQL查询技巧-CSDN博客

1.连接和断开服务器

shell> mysql -h host -u user -p
Enter password: ********

mysql> QUIT  #或者使用 \q  ，在Unix中也可以使用ctrl+D   不需要分号来结束
Bye

2.查询

mysql> SELECT VERSION(), CURRENT_DATE;

mysql> SELECT SIN(PI()/4), (4+1)*5;#将把表达式作为列名

sql语句使用分号结束，或者\g

sql 关键字大小写不敏感

可以输入多个sql语句，以分号分隔，可以跨越多行

可能出现的提示符：

提示符	含义
`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 host -u user -p menagerie  #在启动时选择数据库menagerie  
Enter password: ********

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 把数据载入表中

在windows下，行结束符为\r\n，以制表符分隔的文本文件导入方法

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;

YEAR(), MONTH(), and DAYOFMONTH(). 从时间值中，提取部分值

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模式匹配

SQL 模式匹配 _ 匹配单字符 %匹配任意字符大小写不敏感，使用LIKE和NOT LIKE来匹配

扩展的正则表达式使用 REGEXP 和 NOT REGEXP 或者 RLIKE 和 NOT RLIKE ,

.匹配单字符，[abc] [a-z] x* 表示0到多个x ^ 开头 $结尾 s{20} 表示s出现20次

匹配只要出现，就匹配成功

SQL匹配模式 %w%

扩展的正则表达式 w

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     |
+--------+------+--------+------+---------+

SELECT p1.species AS Species, p1.name AS Female, p2.name AS Male
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 host -u user -p < batch-file
Enter password: ********

使用 --force 选项，即使出现错误，也会继续执行

shell> mysql < batch-file | more

shell> mysql < batch-file > mysql.out

交互模式输出格式

SELECT DISTINCT species FROM pet

+---------+
| species |
+---------+
| bird    |
| cat     |
| dog     |
| hamster |
| snake   |
+---------+

批模式输出格式（使用 mysql -t 选项可与交互模式格式相同，mysql -vvv 显示命令的执行结果）

species
bird
cat
dog
hamster
snake

mysql> source filename;  #在提示符下运行批命令
mysql> \. 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)
);

使用 REFERENCES tbl_name(col_name) 子句定义列时可以使用外部关键字，该子句没有实际的效果，只作为备忘录或注释来提醒，你目前正定义的列指向另一个表中的一个列。

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 |
+------+-------+------+

BIT_COUNT( expr )：返回 expr 的二进制表达式中”1“的个数。

BIT_OR( expr )：返回 expr 中所有比特的bitwise OR。计算执行的精确度为64比特(BIGINT) 。

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 |
+----+---------+

对于 AUTO_INCREMENT的列可以指定0值，NOT NULL的列指定NULL 让数据库自动生成列值

当 AUTO_INCREMENT插入一个新值，将从新值计数，不能为已有值

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 biggest AUTO_INCREMENT value in any group. This happens even for MyISAMtables, 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_INCREMENT column, if there is one. For example, if the animals table contained indexesPRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp 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行对应。