Mysql基础

一、链接和退出

1、命令行链接 server

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

如果出现错误: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' ,说明 Mysql 服务没有启动。

2、退出

mysql> QUIT  -- or(\q)
Bye


二、查询

1、查询版本和当前日期

mysql> SELECT VERSION(), CURRENT_DATE;
+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 5.7.20    | 2019-08-26   |
+-----------+--------------+
1 row in set (0.01 sec)

mysql>

mysql 允许多行输入,直到遇到分号为止,下面的执行遇见和上面的示例执行的结果一致。

mysql> SELECT VERSION(), 
CURRENT_DATE;

如果你决定不执行输入的查询,在其后面输入 “\c”

mysql> SELECT
    -> USER()
    -> \c
mysql>
PromptMeaning
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 /*

在查询中包含了引号,如

mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
    '>

mysql 在等待单引号的结束,所以,虽然第一行包含了分号,查询并没有执行,你只能取消当前查询。在这种情况下,直接输入 "\c"是无法退出的,需要补全引号。如下:

mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
    '>' \c

2、创建和使用数据库

1)使用 show 查询当前管理系统有那些数据库

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+

2)授权

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

3)创建数据库

mysql> CREATE DATABASE menagerie;
mysql> USE menagerie
Database changed
shell> mysql -h host -u user -p menagerie
Enter password: ********

4)创建表

mysql> SHOW TABLES;
Empty set (0.00 sec)
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                 |
+---------------------+
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    |       |
+---------+-------------+------+-----+---------+-------+

5、load 填充表

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

文本 文件格式

一行作为一条记录,列和列之间通过 tab 分离,空字符 用 “\N” 表示 NULL

Windows 环境填充表格命令如下:

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

6、从表中查询数据

select 语法

SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

7、mysql 日期函数

mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
+----------+------------+------------+------+

IS NOT NULL 优于 <> null, 因为NULL 是一个特殊的不可比较的值。

其他时间函数

 YEAR(), MONTH(), DAYOFMONTH().

DATE_ADD() CURDATE()

mysql> SELECT name, birth FROM pet
       WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
mysql> SELECT '2018-10-31' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-31' + INTERVAL 1 DAY |
+-------------------------------+
| 2018-11-01                    |
+-------------------------------+

mysql> SELECT '2018-10-32' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-32' + INTERVAL 1 DAY |
+-------------------------------+
| NULL                          |
+-------------------------------+

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2018-10-32' |
+---------+------+----------------------------------------+

8、NULL值

NULL值使用 IS NULL 和IS NOT NULL 操作

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

不可以使用算术运算符进行比较,

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

9、模式匹配

  .       匹配任何单个字符。

[...]     匹配括号内的任何字符。例如,[abc]匹配a,b或c。要命名一系列字符,请使用短划线。 [a-z]匹配任何字母,而[0-9]匹配任何数字。

  *       匹配前面的事物的零个或多个实例。例如,x *匹配任意数量的x个字符,[0-9] *匹配任意数量的数字,而。*匹配任意数量的任何数字。

10、正则表达式匹配

mysql> SELECT * FROM pet WHERE name REGEXP '^b';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

^b 以b 开头的字符匹配

fy$ 以fy结尾的字符匹配

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+


10、计数

mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

如果除了COUNT()值之外还要指定要选择的列,则应该存在一个GROUP BY子句,该子句将这些列命名为相同的列。否则,会发生以下情况:

如果启用了ONLY_FULL_GROUP_BY 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): 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)

三、获取数据库和表信息

要找出当前选择的数据库,请使用DATABASE()函数:

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie  |
+------------+

查看当前数据库包含那些表:

mysql> SHOW TABLES;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| event               |
| pet                 |
+---------------------+

查看表结构:

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

您可以使用SHOW CREATE TABLE语句获取创建现有表所需的CREATE TABLE语句。

如果表上有索引,则SHOW INDEX FROM tbl_name会生成有关它们的信息。

四、mysql 批处理

shell> mysql < batch-file

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

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

如果您希望脚本继续运行,即使其中的某些语句产生错误,您也应该使用--force命令行选项。

您还可以使用source命令或\来使用mysql提示符中的脚本。命令:

mysql> source filename;
mysql> \. filename

五、Mysql 查询

1、前置准备

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),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

该表应具有以下内容:

SELECT * FROM shop ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       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 |
+---------+--------+-------+

2、查询最大值

“什么是最高的项目编号?”

SELECT MAX(article) AS article FROM shop;
+---------+
| article |
+---------+
|       4 |
+---------+

3、查询某列最大值的行

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+

其他解决方案是使用LEFT JOIN或按价格降序排序所有行,并使用特定于MySQL的LIMIT子句获取第一行:

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;

4、每组最大列数

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

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;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

6、外键

7、计算每日访问量

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
       GROUP BY year,month;

8、AUTO_INCREMENT

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

You can retrieve the most recent automatically generated AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. 

标签: mysql
相关文章
评论留言
发布留言