本文详细介绍了如何在MySQL中连接与断开服务器，执行SQL查询，创建和使用数据库及表，数据装载，表内检索，解决日期字段问题，备份与恢复数据，以及常用查询和操作技巧。
目录
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数据库
扫一扫
1056
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
