mysql 学习（第一章）

最新推荐文章于 2024-05-09 10:11:53 发布

xiabe

最新推荐文章于 2024-05-09 10:11:53 发布

阅读量182

点赞数

分类专栏： Sql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/xiabenshu/article/details/89009075

版权

Sql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

mysql可以执行于命令行/也可以执行于批处理模式下。

使用下面的命令可以看到mysql提供的帮助。

shell> mysql --help

1.1 链接/断开链接从数据库

要链接到数据库，你总是得去提供一个MySQL用户名当你用了mysql，还可能，需要一个密码。如果服务器跑在不是你要登陆的机子上，你需要指定一个主机host名。链接到你的管理员去找出链接参数你应该用于链接（什么host，用户名和需要用到的密码）。一旦你知道了恰当的参数你这么链接。
远程链接：

shell> mysql -h host -u user -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25338 to server version: 8.0.18-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

host和user代表了主机名，你服务器运行和MySQL的用户名。代替恰当的值对于你的安装。

你在自己的机子上登陆，可以使用

shell> mysql -u user -p

一些版本的安装，允许你去做匿名登陆，不需要任何选项：

shell> mysql

断开链接：

mysql> QUIT
Bye

1.2 查询语句

就像这样，查询版本和日期
在这里插入图片描述
查询特点：

查询通常由SQL语句和分号组成。
你发送了查询命令给服务器，服务器给你执行后将显示的结果返回给你。
mysql通过扁平的格式显示你的查询结果。
mysql会返回数据集合有几条数据。
大小写不敏感。

你可以简单把它当一个计算器，就像：
在这里插入图片描述
当你命令打到一半不想让他执行的时候打\c:

mysql> SELECT
    -> USER()
    -> \c
mysql>

一些提示符:
在这里插入图片描述
就像，

在这种情况下打\c也是没用的。在这种情况下，里面的字符都仅仅是字符意义。

1.3 创建并且使用一个数据库

一旦你知道怎么使用sql语句，你得去学数据库。

你接下来得执行：

创建一个数据库
创建一个表
加载数据到表中
从表中用各种方法检索数据
使用多种表格

显示数据库
在这里插入图片描述
对于下面的示例，您可以使用测试数据库(如果您可以访问它)，但是您在该数据库中创建的任何内容都可以被访问它的任何人删除。出于这个原因，您可能应该请求MySQL管理员允许您使用自己的数据库。假设您想要调用您的动物园。管理员需要执行这样的语句:

mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

your_mysql_name是mysql用户名。
your_client_host 是主机名。

1.3.1 创建数据库

mysql> CREATE DATABASE menagerie;

在unix中，数据库名称是大小写敏感的。
在win中，数据库名称是大小写不敏感的。

使用数据库：

mysql> USE menagerie
Database changed

登陆的时候选择数据库：

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

-p + 密码的重要一点：
对于下面的示例，您可以使用测试数据库(如果您可以访问它)，但是您在该数据库中创建的任何内容都可以被访问它的任何人删除。出于这个原因，您可能应该请求MySQL管理员允许您使用自己的数据库。假设您想要调用您的menagerie。管理员需要执行如下语句:刚才显示的命令中的menagerie不是您的密码。如果希望在命令行中在-p选项之后提供密码，则必须不使用任何空格(例如-ppassword，而不是-p密码)。但是，不建议将密码放在命令行上，因为这样做会让登录到您机器上的其他用户窥探密码。

1.3.2 创建一个表

显示表格：

mysql> SHOW TABLES;
Empty set (0.00 sec)

年龄最好不要存储到表格中，因为年龄是变化的，可以记录生日来计算年龄。
请求· ·1 11
创建表格：

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
       species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

varchar 可以从1到65536.

更改表格： alter table

查询表格结构： describe
在这里插入图片描述

1.3.3 加载数据到表格中

在这里插入图片描述
加载桌面的文件test.txt：

将test.txt:

mysql> LOAD DATA LOCAL INFILE 'C:/Users/Administrator/Desktop/test.txt' INTO TABLE pet LINES TERMINATED BY '\r\n';

这里用不了：
在这里插入图片描述
使用插入语句：

mysql> INSERT INTO pet
       VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL)

多行插入：

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 xx
from xx
where xx;

1.3.4 选择数据

mysql> SELECT * FROM pet;

在这里插入图片描述

mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

在这里插入图片描述

mysql> SELECT * FROM pet WHERE name = 'Bowser';

使用where 可以补充 and /or 等。
distinct 独特/不同。
order by ：排序。[DESC]

1.3.5 日期计算

通过系统给的timestampdiff()函数：
在这里插入图片描述
MONTH(birth) ：月份。
CURDATE()：当前时间。
DATE_ADD()：创建一个新日期。
MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH))：
Mod(a,b)：求余
MOD(MONTH(CURDATE()), 12) + 1;

在这里插入图片描述

1.3.6 使用空值（NULL）

IS NULL/IS NOT NULL：
在这里插入图片描述
测试 null和 =, <, or <>：

任何的NULL 参与运算的结果都是NULL。

在mysql中，0或者null意味着false，其他都是true。boolean的默认值是1.
所以请使用death IS NOT NULL，而不是 <> NULL。

两个NULL 的值被视为相等的group by。
order by的时候 null在前面当使用asc 而在最后 dsc。

在处理NULL时，一个常见的错误是假设不可能将0或空字符串插入定义为not NULL的列中，但事实并非如此。这些实际上是值，而NULL的意思是“没有值”。“你可以很容易地测试这一点，使用IS [NOT] NULL如下所示:

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;

''空字符串不被视为NULL。

1.3.7 模式匹配

开始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个’_’ 表示5个字符：

mysql> SELECT * FROM pet WHERE name LIKE '_____';

MySQL提供的另一种模式匹配使用扩展正则表达式。当您测试这种类型模式的匹配时，使用REGEXP_LIKE()函数(或REGEXP或RLIKE操作符，它们是REGEXP_LIKE()的同义词)。

下面的列表描述了扩展正则表达式的一些特性:

.表示任何一个单一符号
一个[…]表示了配合括号里面的任何字符。举个例子,[abc]可以匹配a,b或者c。
- 表示匹配0-n个前面的符号。
任何一部分匹配成功都可以，like需要完全匹配。
^或者$ 来表示开始或者结尾。

为了演示扩展正则表达式是如何工作的，这里重写了前面显示的LIKE查询，以使用REGEXP_LIKE()。

就像下面的，找b\B开头：

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');

匹配b小写开始的:

SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs);
SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b');
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');

以fy结束：

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$');

找包含w：

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');

确切匹配5个字符的:

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');

或者，

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');

1.3.8 计算行数：

计算总行数：

mysql> SELECT COUNT(*) FROM pet;

计算各个owner的行数：

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

组合分组：

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;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
+---------+------+----------+

sql 模式：

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

改回来，

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)

1.3.9 使用多个表

在创建一个表，

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
       type VARCHAR(15), remark VARCHAR(255));

然后添加，
在这里插入图片描述
从多个表中查询：

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

关于这个查询，有几点需要注意:

FROM子句连接两个表，因为查询需要从这两个表中提取信息。
在组合(连接)来自多个表的信息时，需要指定如何将一个表中的记录与另一个表中的记录匹配。这很简单，因为它们都有一个name列。查询使用ON子句根据名称值匹配两个表中的记录。
查询使用内部连接来组合表。当且仅当两个表都满足ON子句中指定的条件时，内部联接允许两个表中的行出现在结果中。在本例中，ON子句指定pet表中的name列必须与事件表中的name列匹配。如果一个名称出现在一个表中，而没有出现在另一个表中，那么由于ON子句中的条件失败，结果中将不会出现该行。

您不需要有两个不同的表来执行连接。有时，如果您想将表中的记录与同一表中的其他记录进行比较，那么将表连接到它自己是很有用的。例如，要在您的宠物中找到繁殖配对，您可以将pet表与它自己连接起来，以生成类似物种的存活雄性和雌性的候选配对:
自己链接自己：

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;
+--------+------+-------+------+---------+
| name   | sex  | name  | sex  | species |
+--------+------+-------+------+---------+
| Fluffy | f    | Claws | m    | cat     |
| Buffy  | f    | Fang  | m    | dog     |
+--------+------+-------+------+---------+

1.4 获得数据库和表的信息

如查看现在用的databse()
在这里插入图片描述

1.5 使用mysql的批处理模式

linux下：

shell> mysql < batch-file

windows下：

C:\> mysql -e "source batch-file"

使用-f可以强制执行，当脚本中有错误：
为什么使用一个脚本：

如果你经常重复查询，你可以使用一个脚本来避免重复键入。
你可以通过改脚本得到相似的。
对于多行执行很有好处。
如果产生了很多输出可以使用：shell> mysql < batch-file
你可以输出到外面去：shell> mysql < batch-file > mysql.out
你可以将你的脚本文件给别人用。
有些情况不允许交互式使用，例如，当您从cron作业运行查询时。在这种情况下，必须使用批处理模式。
批处理模式输出有点不同。

运行命令：

mysql> source filename;
mysql> \. filename

1.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 ORDER BY article;

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

1.6.1选择最大值：

SELECT MAX(article) AS article FROM shop;

+---------+
| article |
+---------+
|       4 |
+---------+

如果直接max选择：
在这里插入图片描述
所以，只能这样：

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;

或者limit 倒序最后一条。

SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;

1.6.2 根据组选择列最大值

原表格：
+---------+--------+-------+
| 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 |
+---------+--------+-------+

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article
ORDER BY article;



+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

对于特定栏求最大值：

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article)
ORDER BY article;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

不相关子查询:

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;

左链接：

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;

带有窗口函数的公共表表达式:

WITH s1 AS (
   SELECT article, dealer, price,
          RANK() OVER (PARTITION BY article
                           ORDER BY price DESC
                      ) AS `Rank`
     FROM shop
)
SELECT article, dealer, price
  FROM s1
  WHERE `Rank` = 1
ORDER BY article;

1.6.3 用户自定义变量

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

1.6.4 使用外键

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

这种参考属性不会出现show create table 或者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=utf8mb4

在列定义中以这种方式使用引用作为注释或“提醒”，可以使用MyISAM表。

1.6.5双值查询

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';

1.6.6 计算每月的访问天数

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, count(0) as days
    from (select year, month, day,count(0) from t1 group by year, month, day) as tmp
    group by year, month;

在这里插入图片描述
也可以使用这样：

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函数表示计算二进制中1的个数。
bit_or函数的含义就是对两个二进制数进行或运算。
- 如：
  于是上面的计算：
  (1<< 02) | (1 << 23) | (1<<23) = 100 | 1000…（23个0）| 1000…（23个0） = 1000…100
  一种巧妙的算法。

1.6.7 使用自增

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;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

自增也跟你上一个输出的值有关。

INSERT INTO animals (id,name) VALUES(100,'rabbit');
INSERT INTO animals (id,name) VALUES(NULL,'mouse');
SELECT * FROM animals;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
| 101 | mouse     |
+-----+-----------+

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

修改表格中的自增开始位置。

MyISAM 注意：[不懂]

对于MyISAM表，可以在多列索引的辅助列上指定AUTO_INCREMENT。在本例中，AUTO_INCREMENT列生成的值计算为MAX(auto_increment_column) + 1，其中prefix= givin -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 |
+--------+----+---------+

xiabe

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 学习（第一章）

mysql可以执行于命令行/也可以执行于批处理模式下。使用下面的命令可以看到mysql提供的帮助。shell> mysql --help1.1 链接/断开链接从数据库要链接到数据库，你总是得去提供一个MySQL用户名当你用了mysql，还可能，需要一个密码。如果服务器跑在不是你要登陆的机子上，你需要指定一个主机host名。链接到你的管理员去找出链接参数你应该用于链接（什么host，...
复制链接

扫一扫