MySql基础(一)

文章摘要：
1、连接、退出MySql数据库
2、查询MySql用户以及localHost
3、创建数据库、显示数据库表结构
4、ALTER增加/修改数据库列
5、数据库外键
6、数据库日期函数
7、IS NOT NULL
8、模式匹配
9、计数

一、MySql 数据库连接、退出

1、连接MySql

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

host和user分别代表MySQL服务器运行的主机名和MySQL账户用户名。设置时替换为正确的值。******** 代表你的密码；当mysql显示Enter password:提示时输入它。
如果有效，你应该看见mysql>提示符后的一些介绍信息：

shell> mysql -h host -u user -p 
Enter password: ******** 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

2、退出MySql

输入“Quit（\q）”或者“Exit”。

3、查询用户以及Host

mysql> SELECT USER();
mysql> SELECT Host,User from mysql.user;

4、查询当前日期和版本号。

mysql> SELECT VERSION(), CURRENT_DATE;

5、不区分大小写。

mysql> SELECT VERSION(), CURRENT_DATE; 
mysql> select version(), current_date; 
mysql> SeLeCt vErSiOn(), current_DATE;

6、支持逻辑运算

mysql> SELECT SIN(PI()/4), (4+1)*5;

二、数据库相关：

1、存在那些数据库

mysql> SHOW DATABASES; 
mysql> select database();

+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

2、使用数据库

mysql> USE test

3、创建数据库

mysql> Ccreate table 宠物(名字 varchar(20),主人 varchar(20),种类 varchar(20),
性别 char(1),出生 DATE,死亡 DATE);

CREATE TABLE 宠物日志 (id INT NOT NULL AUTO_INCREMENT,
名字 VARCHAR(20), 时间 DATE, 时间类型 VARCHAR(15), 
评论 VARCHAR(255),`事件id` INT(45) NOT NULL,PRIMARY KEY (`id`));

4、数据库中有那些表。

mysql> SHOW TABLES;

5、显示数据表创建结构。

5.1、DESCRIBE方式

DESCRIBE + 表名。

+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| 名字   | varchar(20) | NO   | PRI | NULL    |                |
| 主人   | varchar(20) | YES  |     | NULL    |                |
| 种类   | varchar(20) | YES  |     | NULL    |                |
| 性别   | char(1)     | YES  |     | NULL    |                |
| 出生   | date        | YES  |     | NULL    |                |
| 死亡   | date        | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

5.2、SHOW CREATE TABLE + 表名

show create table 宠物;

+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 宠物   | CREATE TABLE `宠物` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `名字` varchar(20) NOT NULL,
  `主人` varchar(20) DEFAULT NULL,
  `种类` varchar(20) DEFAULT NULL,
  `性别` char(1) DEFAULT NULL,
  `出生` date DEFAULT NULL,
  `死亡` date DEFAULT NULL,
  PRIMARY KEY (`id`,`名字`)
) ENGINE=InnoDB AUTO_INCREMENT=92 DEFAULT CHARSET=utf8                 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

6、填充数据。Load/insert操作

要想将文本文件“pet.txt”装载到pet表中，使用这个命令：
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
请注意如果用Windows中的编辑器（使用\r\n做为行的结束符）创建文件，应使用：

mysql> -- 从txt中加载数据 --
LOAD DATA LOCAL INFILE '/Users/ifei/Downloads/宠物.txt'  INTO TABLE 宠物;
    -> LINES TERMINATED BY '\r\n'; 
（在运行OS X的Apple机上，应使用行结束符'\r'。）

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

附宠物.txt：

\N  大虎  高小胖 dog f   2010-02-05  \N
\N  老笨  高大胖 dog f   2014-09-05  \N
\N  旺财  高大胖 cat m   2016-07-03  \N
\N  Fang    Harold  bird    m   2010-03-03  2017-07-03
\N  Chirpy  Gwen    bird    f   2015-05-19  \N
\N  Slim    Gwen    cat f   2010-07-12  2014-01-03
\N  Buffy   Diane   snake   m   2002-04-12  2014-08-09
\N  Claws   Diane   snake   m   2003-08-17  \N

7、增加新列，增加主键

-- 增加新列、增加主键 --
ALTER TABLE `test`.`宠物`
ADD COLUMN `id` VARCHAR(45) NOT NULL FIRST,
ADD PRIMARY KEY (`id`);

8、修改数据库列，增加自增属性

-- 修改列、增加自增属性 --
ALTER TABLE `test`.`宠物` 
CHANGE COLUMN `id` `id` INT NOT NULL AUTO_INCREMENT ;

9、数据库外键

外键必须指向另一个表的主键。
在引用表中，必须有一个索引，外键列以同样的顺序被列在其中作为第一列。这样一个索引如果不存在，它必须在引用表里被自动创建。

-- 增加约束-外键 --
ALTER TABLE test.宠物日志 
ADD FOREIGN KEY test.宠物日志(`事件id`) REFERENCES test.宠物(`id`);

ALTER TABLE `test`.`宠物日志` 
ADD FOREIGN KEY (`事件id` , `名字`)
  REFERENCES `test`.`宠物` (`id` , `名字`);

10、查询选择数据

SELECT语句用来从数据表中检索信息。语句的一般格式是：

SELECT what_to_select 
FROM which_table 
WHERE conditions_to_satisfy;

what_to_select指出你想要看到的内容，可以是列的一个表，或*表示“所有的列”。
which_table指出你想要从其检索数据的表。
WHERE子句是可选项，如果选择该项，conditions_to_satisfy指定行必须满足的检索条件。

mysql> SELECT * FROM 宠物 WHERE 种类 = 'dog' AND 性别 = 'f'; 
mysql> SELECT * FROM 宠物 WHERE 种类 = 'snake' OR 种类 = 'bird';

AND和OR可以混用，但AND比OR具有更高的优先级。如果你使用两个操作符，使用圆括号指明如何对条件进行分组是一个好主意：

mysql> SELECT * FROM 宠物 WHERE (种类 = 'cat' AND 性别 = 'm') 
    -> OR (种类 = 'dog' AND 性别 = 'f');

关键字：DISTINCT
请注意该查询只是简单地检索每个记录的owner列，并且他们中的一些出现多次。为了使输出减到最少，增加关键字DISTINCT检索出每个唯一的输出记录：

mysql> SELECT DISTINCT 主人 FROM 宠物;

11、查询结果排序，默认按照ASC来排序。

mysql> SELECT 名字, 出生 FROM 宠物 ORDER BY 出生 （ASC）;

默认排序是升序，最小的值在第一。要想以降序排序，在你正在排序的列名上增加DESC（降序）关键字：

mysql> SELECT 名字, 出生 FROM 宠物 ORDER BY 出生 DESC;

可以对多个列进行排序，并且可以按不同的方向对不同的列进行排序。例如，按升序对动物的种类进行排序，然后按降序根据生日对各动物种类进行排序（最年轻的动物在最前面），使用下列查询：

mysql> SELECT 名字, 种类, 出生 FROM 宠物 
    -> ORDER BY 种类, 出生 DESC;

注意DESC关键字仅适用于在它前面的列名(“出生”)；不影响“种类”列的排序顺序。

12、日期函数

要想确定每个宠物有多大，可以计算当前日期的年和出生日期之间的差。如果当前日期的日历年比出生日期早，则减去一年。以下查询显示了每个宠物的出生日期、当前日期和年龄数值的年数字。

mysql> SELECT 名字, 出生, CURDATE(), 
    -> (YEAR(CURDATE())-YEAR(出生)) 
    -> - (RIGHT(CURDATE(),5)<RIGHT(出生,5)) 
    -> AS 年龄 
    -> FROM 宠物;

YEAR()提取日期的年部分，RIGHT()提取日期的MM-DD (日历年)部分的最右面5个字符。比较MM-DD值的表达式部分的值一般为1或0，如果CURDATE()的年比birth的年早，则年份应减去1。
附：

名字	出生	CURDATE()	年龄
大虎	2010-02-05	2017-07-04	7
老笨	2014-09-05	2017-07-04	2
旺财	2016-07-03	2017-07-04	1
Fang	2010-03-03	2017-07-04	7
Chirpy	2015-05-19	2017-07-04	2
Slim	2010-07-12	2017-07-04	6
Buffy	2002-04-12	2017-07-04	15
Claws	2003-08-17	2017-07-04	13
temp	NULL	2017-07-04	NULL

mysql> SELECT 名字, 出生 FROM 宠物 
    -> WHERE MONTH(出生) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

DATE_ADD( )允许在一个给定的日期上加上时间间隔。如果在NOW( )值上加上一个月，然后用MONTH()提取月份，就是下个月。
等价于使用MOD取模函数：

mysql> SELECT 名字, 出生 FROM pet 
    -> WHERE MONTH(出生) = MOD(MONTH(CURDATE()), 12) + 1;

13、IS NOT NULL的用法

NULL值可能令人感到奇怪直到你习惯它。概念上，NULL意味着“没有值”或“未知值”，且它被看作与众不同的值。为了测试NULL，你不能使用算术比较操作符例如=、<或!=。为了说明它，试试下列查询：

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;

+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     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中，0或 NULL意味着假而其它值意味着真。布尔运算的默认真值是1

mysql> SELECT 名字, 出生, 死亡,
-> (YEAR(死亡)-YEAR(出生)) - (RIGHT(死亡,5)<RIGHT(死亡,5))
-> AS 年龄
-> FROM 宠物 WHERE 死亡 IS NOT NULL ORDER BY 年龄;

+--------+------------+------------+--------+
| 名字 | 出生 | 死亡 | 年龄 |
+--------+------------+------------+--------+
| Slim | 2010-07-12 | 2014-01-03 | 4 |
| Fang | 2010-03-03 | 2017-07-03 | 7 |
| Buffy | 2002-04-12 | 2014-08-09 | 12 |
+--------+------------+------------+--------+

查询使用出生 IS NOT NULL而非出生 != NULL，因为NULL是特殊的值，不能使用普通比较符来比较。
下述语句不返回任何行，这是因为，对于任何表达式，expr = NULL永远不为“真”：

mysql> SELECT * FROM my_table WHERE phone = NULL;

要想查找NULL值，必须使用IS NULL测试。在下面的语句中，介绍了查找NULL电话号码和空电话号码的方式：

mysql> SELECT * FROM my_table WHERE phone IS NULL;
mysql> SELECT * FROM my_table WHERE phone = '';

- 使用DISTINCT、GROUP BY或ORDER BY时，所有NULL值将被视为等同的。 
- 使用ORDER BY时，首先将显示NULL值，如果指定了DESC按降序排列，NULL值将最后显示。 
- 对于聚合（累计）函数，如COUNT()、MIN()和SUM()，将忽略NULL值。对此的例外是COUNT(*)，它将计数行而不是单独的列值。例如，下述语句产生两个计数。首先计数表中的行数，其次计数"出生"列中的非NULL值数目：

mysql> SELECT COUNT(*), COUNT(出生) FROM 宠物;


##14、模式匹配
SQL模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零字符)。在 MySQL中，SQL的模式默认是忽略大小写的。注意使用SQL模式时，不能使用=或!=；而应使用LIKE或NOT LIKE比较操作符。

要想找出包含“w”的名字：

mysql> SELECT * FROM pet WHERE name LIKE '%w%';

要想找出正好包含5个字符的名字，使用“_”模式字符：

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

由MySQL提供的模式匹配的其它类型是使用扩展正则表达式。当你对这类模式进行匹配测试时，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)。 
扩展正则表达式的一些字符是： 
- ‘.’匹配任何单个的字符。 
- 字符类“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”、“b”或“c”。为了命名字符的范围，使用一个“-”。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。 
- “ * ”匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的“x”字符，“[0-9]*”匹配任何数量的数字，而“.*”匹配任何数量的任何字符。 
如果REGEXP模式与被测试值的任何地方匹配，模式就匹配(这不同于LIKE模式匹配，只有与整个值匹配，模式才匹配)。 
- 为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“^”或在模式的结尾用“$”。 

为了找出以“b”开头的名字，使用“^”匹配名字的开始：

mysql> SELECT * FROM 宠物 WHERE 名字 REGEXP '^b';

如果你想强制使REGEXP比较区分大小写，使用BINARY关键字使其中一个字符串变为二进制字符串。该查询只匹配名称首字母的小写‘b’。

mysql> SELECT * FROM 宠物 WHERE 名字 REGEXP BINARY '^b';

为了找出包含一个“w”的名字，使用以下查询：

mysql> SELECT * FROM 宠物 WHERE 名字 REGEXP 'w';


既然如果一个正则表达式出现在值的任何地方，其模式匹配了，就不必在先前的查询中在模式的两侧放置一个通配符以使得它匹配整个值，就像你使用了一个SQL模式那样。 
为了找出包含正好5个字符的名字，使用“^”和“$”匹配名字的开始和结尾，和5个“.”实例在两者之间：

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';

等价于：
你也可以使用“{n}”“重复n次”操作符重写前面的查询：

mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';


##15、计数
在前面，你检索了拥有宠物的人的名字。如果你想要知道每个主人有多少宠物，你可以使用COUNT( )函数：

mysql> SELECT 主人, COUNT(*) FROM 宠物 GROUP BY 主人;

+-----------+----------+
| 主人 | COUNT(*) |
+-----------+----------+
| Diane | 2 |
| Gwen | 2 |
| hailou | 1 |
| Harold | 1 |
| 高大胖 | 2 |
| 高小胖 | 1 |
+-----------+----------+

注意，使用GROUP BY对每个"主人"的所有记录分组，没有它，你会得到错误消息：

mysql> SELECT 主人, COUNT(*) FROM 宠物;

ERROR 1140 (42000): In aggregated query without GROUP BY,
expression #1 of SELECT list contains nonaggregated column
'test.宠物.主人'; this is incompatible with sql_mode=only_full_group_by

最后编辑于：2017.12.08 07:58:32

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 216,919评论 6赞 502
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,567评论 3赞 392
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 163,316评论 0赞 353
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,294评论 1赞 292
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,318评论 6赞 390
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,245评论 1赞 299
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,120评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,964评论 0赞 275
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,376评论 1赞 313
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,592评论 2赞 333
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,764评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,460评论 5赞 344
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,070评论 3赞 327
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,697评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,846评论 1赞 269
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,819评论 2赞 370
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,665评论 2赞 354