MySQL入门学习（超详细图文解释）

目录

连接与断开服务器

输入查询

创建并使用数据库

创建表

将数据装入表中

表中检索信息

MySQL日期字段出现“0000-00-00 00:00:00”错误解决

获得数据库和表的信息

常用查询的例子

数据备份

恢复数据

---

 

连接与断开服务器

用户名：root
密码：root

1) 登录：
mysql -h localhost -u root -p，回车后输入密码

​

 2) 退出：quit(或Ctrl+Z)

​

 3) 重启：

a.可通过服务页面重启
​

b.也可通过命令行重启(需要以管理员身份运行命令提示符)
net stop mysql
net start mysql
​

​ 

输入查询

1) 查看日期和版本：SELECT VERSION(), CURRENT_DATE;

​

mysql中控制数据库名和表名的大小写敏感由参数lower_case_table_names控制，为0时表示区分大小写，为1时，表示将名字转化为小写后存储，不区分大小写.

2) 用作计算器：select+计算的式子
​

3) 可以在一行上输入多条语句，只需要以一个分号间隔开各语句，例如：SELECT VERSION(); SELECT NOW();打印版本和现在时间。

​ 

4) mysql接受自由格式的输入：它收集输入行但直到看见分号才执行。
​

5) 要注意提示符如何从mysql>变为->，这正是mysql如何指出它没见到完整的语句并且正在等待剩余的部分。提示符是你的朋友，因为它提供有价值的反馈，如果使用该反馈，将总是知道mysql正在等待什么。如果你决定不想执行正在输入过程中的一个命令，输入\c取消它： 
​

6) 提示符含义

​

 `> 提示符类似于 '> 和"> 提示符，但表示你已经开始但没有结束以`> 开始的识别符。
比如输入以下语句：SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
 

​

 什么都没返回，如何结束该语句呢？‘\c

​

创建并使用数据库

1) 使用SHOW语句找出服务器上当前存在什么数据库： show databases;

 ​

 2)访问某个数据库： use mytest;

​

3) 查看数据库用户名和密码： select * from mysql.user;

用户名就是localhost,密码被加密了

 4) 创建数据库jerry:create database jerry

5) 每次启动mysql会话时在使用前先选择这个固定的数据库：mysql -h localhost -u root -p  jerry

---

创建表

 1) 查看有哪些表：show tables;   开始的时候为空

 

2) 创建一个宠物关于名字，主人，种类，性别。注意的是，创建表时候VARCHAR必须大写，table可以小写。

例子如下：
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

 3) 查看刚创建的表:show tables; 

4) 查看某个表内容：describe pet;

将数据装入表中

1) 一条数据一条数据的插入：
INSERT INTO PET VALUES('lulu','jerry','dog','f','2012-5-5',NULL);

 2) 批量插入
LOAD DATA LOCAL INFILE 'D:/desktop/data.txt' INTO TABLE pet;

 检查导入的数据：SELECT * FROM pet;

---

表中检索信息

1) 查看所有数据：select * from pet;

 2) 修改表内容

方法一：

先删除用 DELETE FROM pet;  去修改txt中内容，再LOAD DATA LOCAL INFILE 'D:/desktop/data.txt' INTO TABLE pet;

方法二：

例如更改owners下的huanna为jerry，就执行：update pet set owner='jerry' where name='beila';

 方法三:

单独选择某一行：SELECT * FROM pet WHERE name = 'beila';

 你可以在任何列上指定条件，不只仅仅是name。例如，如果你想要知道哪个动物在2019以后出生的，测试birth列: SELECT * FROM pet WHERE birth > '2019-1-1';

可以组合条件,例如找出jerry的狗：select * from pet where owner='jerry' and species='dog';

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

mysql> select * from pet where( owner='jerry' and species='dog')
-> or ( owner='jerry' and species='cat');

 4）选择特殊列：select name,birth from pet;

找出谁拥有宠物，使用这个查询：select owner from pet; 

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

可以使用一个WHERE子句结合行选择与列选择。例如，要想查询狗和猫的出生日期，使用这个查询：

5）分类行 

为了排序结果，使用ORDER BY子句。这里是动物生日，按日期排序：select  name, birth from pet order by birth;

 

默认排序是升序，最小的值在第一。要想以降序排序，在你正在排序的列名上增加DESC（降序 ）关键字：SELECT name, birth FROM pet ORDER BY birth DESC;

 

按升序对动物的种类进行排序，然后按降序根据生日对各动物种类进行排序（最年轻的动物在最前面），使用下列查询：

mysql> SELECT name, species, birth FROM pet
-> ORDER BY species, birth DESC;

 6）日期计算

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

mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> AS age from pet;

MySQL中的RIGHT( str, len )函数用于从给定字符串的右侧提取指定数量的字符。第二个参数用于确定应返回多少个字符。

• str:给定的字符串，将从其右侧提取许多字符。
• len ：要提取的字符数。如果此参数大于字符串中的字符数，则此函数将返回实际的字符串。

尽管查询可行，如果以某个顺序排列行，则能更容易地浏览结果。添加ORDER BY name子句按照名字对输出进行排序则能够实现了按age而非name排序输出，只要再使用一个ORDER BY子句：

mysql> SELECT name, birth, CURDATE(),(YEAR(CURDATE())-YEAR(birth))- (RIGHT(CURDATE(),5)<RIGHT(birth,5)) AS age
-> FROM pet ORDER BY age;

 

 知道哪个动物几月过生日：SELECT name, birth, MONTH(birth) FROM pet;

 

7）NULL值操作

使用IS NULL和IS NOT NULL操作符，0或 NULL意味着假而其它值意味着真。布尔运算的默认真值是1。

判断哪个动物不再是活着的，使用death IS NOT NULL而不使用death != NULL

MySQL日期字段出现“0000-00-00 00:00:00”错误解决

此时death字段出现错误，且直接执行更新sql操作会报错

这是MySQL的严格模式造成的，这里采取一个简单的方法解决：
update pet set death = NULL where death < '1970-01-01'; 

可以看到已经解决，此时三个宠物death字段都为NULL

8）模式匹配

要想找出以“x”开头的名字：SELECT * FROM pet WHERE name LIKE 'x%' ;

 

 要想找出以“a”结尾的名字：select * from pet where name like  '%a';

要想找出包含“h”的名字： SELECT * FROM pet WHERE name LIKE '%o%';

要想找出正好包含5个字符的名字，使用“_”模式字符：SELECT * FROM pet WHERE name LIKE '_____'; 

---

获得数据库和表的信息

1）查看有哪些数据库：show databases;

2）选择数据库： use pet;

3）查看该数据库中有哪些表：show tables;

4）查看表结构：describe pet;

---

常用查询的例子

 1）你可以使用以下语句创建示例表：

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

zerofill的作用是根据你的默认形式设置来补零，

unsigned的作用是不能插入负数，如果插入负数默认为零。

 2）执行语句后，表应包含以下内容： SELECT * FROM shop;

3）列的最大值

选取价格最贵：select max(price) as article from shop;

 4）拥有某个列的最大值的行

任务：找出最贵物品的编号、销售商和价格。

方法一：

mysql> select article,dealer,price
-> from shop
-> where price=(select max(price) from shop);

 方法二：按价格降序排序所有行并用MySQL特定LIMIT子句只得到第一行，这里用到desc前面讲过，是降序的意思。

mysql> select article ,dealer,price
-> from shop
-> order by price desc
-> limit 1;

 

  注:如果有多项最贵的物品( 例如每个的价格为19.95)，LIMIT解决方案仅仅显示其中一个！

5）列的最大值：按组

任务：每项物品的的最高价格是多少？

mysql> SELECT article, MAX(price) AS price
-> FROM shop
-> GROUP BY article;

6）拥有某个字段的组间最大值的行

任务：对每项物品，找出最贵价格的物品的经销商。

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

7）使用用户变量

任务：要找出价格最高或最低的物品的

SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

 8）使用外键

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;

SELECT * FROM shirt;

SELECT s.* FROM person p, shirt s
 WHERE p.name LIKE 'Lilliana%'
   AND s.owner = p.id
   AND s.color <> 'white';

9）根据天计算访问量

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;

• BIT_OR：二进制数按位或。

• BIT_COUNT：是用来计算二进制数中1的个数的。

• 1 << day：表示1向左移day个位。

 例子中当计算2月份的访问天数时

BIT_OR(1<<day)=

(1<< 02) | (1 << 23) | (1<<23) = 100 | 1000...（23个0）| 1000...（23个0） = 1000...100
之后BIT_COUNT算出2，非常nb的计算方法，有人访问则置一，2号为10，3号访问为100，23号访问为100...（23个0）

 

 方法二：对day使用distinct来去重，得出每月有访问的天数：

select year,month,count(distinct day) from t1 group by year,month;

 10）自动更正AUTO_INCREMENT

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

 插入数据

mysql> INSERT INTO animals (name) VALUES
    ->     ('dog'),('cat'),('penguin'),
    ->     ('lax'),('whale'),('ostrich');

 

---

数据备份

1）备份某个数据库下的固定某些表

目标：备份我的jerry数据库下的pet表，在路径下建立jerry.sql这个文件。注意：先退出mysql再执行该命令，否则会报错outfile.

备份pet shirt shop三张表

2）对单个或多个库进行完全备份

 

 单个备份：
mysqldump -u root -p mysql>D:/desktop/test.sql

 多个备份：
mysqldump -u root -p --databases sys world>D:/desktop/duo.sql

 

 3）对所有库进行完全备份（建立all.sql文件)

mysqldump -u root -p --all-databases > D:/desktop/all.sql

 

---

恢复数据

1）查看数据库

2）新建空数据库text用于恢复

create database text ;

3）删除数据库jerry

drop database jerry;

 

4）表的恢复
退出mysql后再执行以下命令恢复数据库中的表，我放在了mytest数据库中：

mysql -h localhost -u root -p mytest <D:/desktop/mysql/jerry.sql

 

可以看到已成功导入 

 

 5）数据库的恢复
将chuan.sql备份到text空数据库中

mysql -h localhost -u root -p text <D:/desktop/mysql/jerry.sql

 

 登录查看后可看到jerry数据库已备份至text数据库

---