【MySQL-初级】mysql基础操作（账户、数据库、表的增删查改）

概述

数据备份与恢复

数据库备份：在cmd下

• root用户：sudo mysqldump -u root -p Test > Test.sql
• 普通用户：mysqldump -u zzz -p db_name > db_name.sql

数据库恢复

• 先创建一个空的数据库
• 在cmd下：sudo mysql -u root -p db_name < dbname.sql

备份原理：重新执行

• 通过协议连接到MySQL数据库，将需要备份的数据查询出来，转换成对应的insert语句
• 当需要还原时，执行这些insert语句，即可还原对应的数据

数据库账户

添加账户

删除账户

查找账户

修改账户（权限）

登录数据库

退出数据库

登录MySQL

-u root指定了以root用户登录，-p会提示你输入root用户的密码。

#root用户登录
sudo mysql -u root -p

#普通账户登
mysql -u zzz -p 

退出MySQL

#退出数据库
exit;
quit;
<ctrl+d>

查看MySQL用户表

在MySQL中，你可以通过查询mysql.user表来查看现有的账户信息。这个表包含了MySQL服务器中所有用户账户的信息，包括用户名、宿主（即用户可以从哪个主机连接）、密码哈希（在MySQL 5.7及之前版本中是可见的，但在MySQL 8.0及更高版本中默认是加密的）、权限等。

请注意，要执行这个查询，你需要有足够的权限来访问mysql.user表。通常，这意味着你需要以root用户或具有足够权限的用户身份登录到MySQL服务器。

#查看用户表
SELECT User, Host FROM mysql.user;

创建新用户

可以使用CREATE USER语句来创建一个新用户。

以下是一个例子，我们将创建一个名为newuser的用户，该用户可以从任何主机连接（出于安全考虑，你可能想限制这个用户只能从特定的IP地址或主机名连接）。

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';

请将newuser替换为你想要的用户名，password替换为你想为该用户设置的密码。'%'表示这个用户可以从任何主机连接到MySQL服务器。如果你想要限制用户的访问来源，可以将'%'替换为具体的IP地址或主机名。

授权用户

创建用户后，你需要给用户授予访问数据库的权限。

这可以通过GRANT语句来完成。例如，如果你想让newuser有权限访问名为mydatabase的数据库中的所有表，你可以执行以下命令：

GRANT ALL PRIVILEGES ON mydatabase.* TO 'newuser'@'%';

请注意，ALL PRIVILEGES授予了用户在该数据库上执行所有操作的权限。如果你只想授予用户有限的权限，你可以将ALL PRIVILEGES替换为具体的权限，如SELECT, INSERT等。

应用权限修改

完成权限授予后，你需要执行FLUSH PRIVILEGES;命令来使更改生效。

FLUSH PRIVILEGES;

然而，在使用CREATE USER和GRANT语句时，通常不需要执行FLUSH PRIVILEGES，因为这两个语句会自动使更改生效。这个命令在直接修改MySQL的权限表（如mysql.user）时使用。

查看MySQL端口

• 在MySQL的配置文件中查看 sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
• 一般是3306

SQL语言

是一种功能强大，灵活且易于学习的语言

广泛应用于关系型数据库的管理和操作中

1. DDL（Data Definition Language，数据定义语言）
   • DDL用于定义或修改数据库的结构。它包含用于创建、修改、删除数据库中的表、视图、索引等对象的语句。常见的DDL语句有CREATE、ALTER、DROP等。
2. DML（Data Manipulation Language，数据操纵语言）
   • DML用于添加、删除或修改数据库中的数据。它包含INSERT、UPDATE、DELETE等语句，这些语句用于对数据库表中的数据进行操作。
3. DQL（Data Query Language，数据查询语言）
   • DQL主要用于从数据库中检索数据。它包含SELECT语句，这是SQL中最常用的语句之一，用于从数据库表中查询数据。
4. DCL（Data Control Language，数据控制语言）
   • DCL用于定义数据库、表、用户等的访问权限和安全级别。它包含GRANT、REVOKE等语句，用于控制用户对数据库对象的访问权限。
5. TCL（Transaction Control Language，事务控制语言）
   • TCL实际上是用于管理数据库事务的语句的集合。虽然在这里将其作为单独的一种语言列出，但实际上它是一组SQL语句，如COMMIT、ROLLBACK和SAVEPOINT，用于确保数据的一致性和完整性。这些语句允许用户将一系列的操作作为一个单独的工作单元来执行，这些操作要么全部成功，要么在遇到错误时全部回滚。

DDL

数据定义语言

常用关键字：SHOW CREATE DROP TRUNCATE ALTER

功能：创建数据库和表的结构

定义数据库

展示所有数据库 SHOW databases;

增加数据库 CREATE database db_name;

删除数据库 DROP database db_name;

修改数据库 ALTER database db_name…

使用数据库 USE db_name;

#注释
-- 注释
/*注释*/

#显示所有数据库
show databases;

#显示某个数据库属性
show create database Netdisk;

#创建数据库
create database Test;
create database D1;

#修改数据库的字符属性
alter database Test character set utf8;

#切换数据库
use D1;

#显示某个数据库属性
show create database D1;

#删除数据库
drop database D1;

#显示当前数据库
select database();

定义表

增加表 create table table_name…

修改表 alter table table_name… （add/change/modify）

删除表 drop table table_name;

清空表 truncate table_name;

查看所有表 show tables;

查看表属性 show create table_name;

查看表信息 desc table_name;

#创建一张学生表
create table stu_s1 (id int, name char(20), age int, math float);

#查看此数据库下的所有表
show tables;

#查看某张表
desc stu_s1;
show create table stu_s1;

#添加字段
alter table stu_s1 add column height int;
alter table stu_s1 add chinese float default 60; #设置默认值
alter table stu_s1 add english float after math; #设置某个字段后面
alter table stu_s1 add th int first; #放在最前面

#修改字段
alter table stu_s1 modify height float after age;
alter table stu_s1 change name stu_name varchar(20);

#删除字段
alter table stu_s1 drop english;
alter table stu_s1 drop th;

#修改表的字符集
alter table stu_s1 character set latin1;

#修改表的名字
rename table stu_s2 to stu_ss;
alter table stu_ss rename to stu_s2;

#删除表
drop table stu_s2;

• modify无法修改字段名字，change可以
• modify和change都可以修改字段属性

常用数据类型

数值类型

BIT(M)				位类型；M用来定位，默认是1，范围在1~64
BOOL,BOOLEAN		使用0或1，表示真假
TINYINT				带符号的范围是-128~127，无符号范围是0~255
SMALLINT			2的16次方
INT					2的32次方
BIGINT				2的64次方
FLOAT(M,D)			M指定长度，D指定小数位数
DOUBLE(M,D)			比float更精确

文本、二进制类型

CHAR(size)			固定长度字符串（size表示有多少个字符）   
VARCHAR(size)		可变长度字符串
BLOB 				可以最大存放的长度是50
LONGBLOB			二进制数据，图片或音乐等
TEXT(clob)			text文本
LONGTEXT(longclob)

• char的查询效率高
• varchar省空间
• MySQL存储大数据时效率较差，通常不直接存储数据，而是存储文件的路径

日期类型

DATE				日期类型（YYYY-MM-DD）
DATETIME			日期时间类型（YYYY-MM-DD HH:MM:SS）
TIMESTAMP			时间戳类型（YYYY-MM-DD HH:MM:SS）

特点

• VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度。

• TimeStamp表示时间戳，它可用于自动记录insert、update操作的时间

• ‘1970-01-01 00:00:01’ UTC 至’2038-01-19 03:14:07’ UTC

• TimeStamp 改为DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 会自动记录最后一次更新时间

表的属性

字段（又称列、域、field）

数据库引擎

默认字符集

字符集

数据库的常用字符集包括多种，每种字符集都有其特定的应用场景和优势。以下是一些常见的数据库字符集：

1. UTF-8
   • 特点：UTF-8是目前最为广泛使用的字符集，它是Unicode的一种变长字符编码，可以编码世界上几乎所有的字符，因此具有极强的兼容性。它使用一至四个字节表示一个符号，根据不同的符号而变化。对于英文字母、数字等ASCII字符，UTF-8字符集只使用一个字节，存储效率较高；而中文字符在UTF-8字符集中通常占用三个字节。
   • 优势：支持多语言混合存储，极大地提高了数据库的通用性。同时，UTF-8字符集兼容ASCII字符集，便于数据迁移和升级。
   • 应用场景：适用于需要存储多种语言数据的数据库，特别是包含中文、英文、日文等多种语言的数据库。
2. GBK
   • 特点：GBK是一种专门用于存储中文字符的字符集，它是在GB2312的基础上扩展而来，包含了所有的汉字字符和符号。在GBK字符集中，所有的中文字符都只占用两个字节，存储效率较高。
   • 优势：对于中文数据来说，GBK字符集的存储效率高于UTF-8字符集。
   • 应用场景：适用于主要存储中文数据的数据库，如中文网站的后端数据库。
3. ISO-8859-1（也称为Latin-1）
   • 特点：ISO-8859-1是一种单字节字符集，主要用于表示西欧的拉丁字母。所有的字符都只占用一个字节，存储效率很高。
   • 优势：对于只包含西欧语言字符的数据库来说，ISO-8859-1字符集能够高效存储数据。
   • 应用场景：适用于存储和处理西欧语言字符的数据库，如英语、法语、西班牙语等。但需要注意的是，ISO-8859-1字符集不能表示中文字符。
4. UTF-16
   • 特点：UTF-16是另一种Unicode字符集，采用固定长度的16位（即2字节）编码方式。它能够支持全球范围内的所有字符，包括各种语言的字符、特殊符号以及表情符号等。
   • 优势：与UTF-8相比，UTF-16在处理某些类型的字符时可能更高效，尤其是在处理大量非ASCII字符时。
   • 应用场景：适用于需要高效处理大量非ASCII字符的数据库。然而，由于其固定长度的特性，UTF-16在某些情况下可能会比UTF-8占用更多的存储空间。

除了上述字符集外，还有其他一些字符集也被用于数据库系统中，如GB2312、Big5等。这些字符集各有其特点和适用场景。在选择数据库的字符集时，需要根据数据库的实际需求（如需要存储的数据类型、数据的兼容性要求、存储效率等）来选择合适的字符集。

总的来说，UTF-8字符集由于其广泛的兼容性和灵活性，在大多数情况下都是数据库字符集的首选。然而，在特定场景下（如主要存储中文数据的数据库），GBK字符集或其他特定字符集可能更为合适。

DML（表）

数据操纵语言

常用关键字：INSERT UPDATE DELETE

功能：用于向数据库表中插入，删除，修改数据

插入数据

插入数据 INSERT INTO table_name… values…

查看表中所有数据 SELECT * from table_name;

#插入数据，写出所有字段和对应的数据
insert into stu_s1 (id, stu_name, age, height, math) values(1001, 'xiao', 8, 183, 89);

#查询表中所有数据
select * from stu_s1;

#插入部分数据，写部分字段和对应的数据
insert into stu_s1 (id, stu_name) values(1009, 'qin');

#插入数据，不用写字段但需写出所有与字段对应的值
insert into stu_s1 values(1004, 'liu', 12, 167, 92);

#插入多条数据
insert into stu_s1 values(1008, 'ming', 31, 192, 87), (1006, 'xia', 23, 178, 67);

#插入空值
insert into stu_s1 (id) values(NULL);

• 字符串或日期数据需用单引号括起来

更新数据

限定 WHERE

设置 SET

更新数据 UPDATE table_name SET… WHERE…

#重置某个字段的数据，需要限定
update stu_s1 set age = 18 where id = 1009;
update staff set salary = 3000 where name = 'ee';

#增加
update staff set salary = salary + 1000 where name = 'rr';

#修改字段所有数据
update staff set salary = 5000;

删除数据

限定 WHERE

删除数据 DELETE FROM table_name WHERE…

删除整张表 DELETE FROM table_name;

#删除表中数据
delete from stu_s1 where id = 1006;

#删除表 仅删除表中数据，不能删除表本身
delete from stu_s1;

#删除空行
delete from staff where uid is NULL;

DQL

数据查询语句

常用关键字： SELECT

过滤语句

比较运算符

> >= < <= = <>					大于，大于等于，小于，小于等于，等于，不等于
BETWEEN ...AND...				在某一区间内的值，包含头尾
IN(set)							在in列表中的值，例如：in(10, 20)
LIKE							模糊查询（%:0~多个任意字符， _:一个字符），例如：like '_a%'
IS NULL							判断是否为空

逻辑运算符

and								多个条件同时成立
or								多个条件任一成立
not								不成立

查找指定字段

#查看整张表数据
select * from stu_s1;

#查看某个字段
select id from stu_s1;

#查看多个字段
select id,stu_name from stu_s1;

操控查询结果

distinct

算术运算符

as

#对查询数据去重
select distinct id from stu_s1;

#对数学字段做运算
select id,math+10 from stu_s1;
+------+---------+
| id   | math+10 |
+------+---------+
| 1001 |      99 |
| 1009 |      97 |
| 1004 |     102 |
| 1008 |      97 |
+------+---------+



select id,math+10-20+18*0.5 from stu_s1;
+------+-------------------+
| id   | math+10-20+18*0.5 |
+------+-------------------+
| 1001 |                88 |
| 1009 |                86 |
| 1004 |                91 |
| 1008 |                86 |
+------+-------------------+


#给查询结果起别名
select id,math+10-20+18*0.5 as total_source from stu_s1;
+------+--------------+
| id   | total_source |
+------+--------------+
| 1001 |           88 |
| 1009 |           86 |
| 1004 |           91 |
| 1008 |           86 |
+------+--------------+
4 rows in set (0.00 sec)

过滤查询结果

where + 过滤语句

#查找范围信息
mysql> select * from stu_s1 where age > 12;
+------+----------+------+--------+------+
| id   | stu_name | age  | height | math |
+------+----------+------+--------+------+
| 1009 | qin      |   18 |   NULL |   87 |
| 1008 | ming     |   31 |    192 |   87 |
+------+----------+------+--------+------+
2 rows in set (0.00 sec)

#在范围内查找
mysql> select * from stu_s1 where math between 80 and 90;
+------+----------+------+--------+------+
| id   | stu_name | age  | height | math |
+------+----------+------+--------+------+
| 1001 | xiao     |    8 |    183 |   89 |
| 1009 | qin      |   18 |    172 |   87 |
| 1008 | ming     |   31 |    192 |   87 |
+------+----------+------+--------+------+
3 rows in set (0.00 sec)

#在集合内查找
mysql> select * from stu_s1 where math in(85, 86, 87, 88, 89);
+------+----------+------+--------+------+
| id   | stu_name | age  | height | math |
+------+----------+------+--------+------+
| 1001 | xiao     |    8 |    183 |   89 |
| 1009 | qin      |   18 |    172 |   87 |
| 1008 | ming     |   31 |    192 |   87 |
+------+----------+------+--------+------+
3 rows in set (0.00 sec)

#模糊查找
mysql> select * from stu_s1 where stu_name like '%in';
+------+----------+------+--------+------+
| id   | stu_name | age  | height | math |
+------+----------+------+--------+------+
| 1009 | qin      |   18 |    172 |   87 |
+------+----------+------+--------+------+
1 row in set (0.00 sec)

#查找空数据
mysql> select * from stu_s1 where id is NULL;
+------+----------+------+--------+------+
| id   | stu_name | age  | height | math |
+------+----------+------+--------+------+
| NULL | NULL     | NULL |   NULL | NULL |
+------+----------+------+--------+------+
1 row in set (0.00 sec)

#多条件全部成立
mysql> select * from stu_s1 where id > 1001 and stu_name like 'l%';
+------+----------+------+--------+------+
| id   | stu_name | age  | height | math |
+------+----------+------+--------+------+
| 1004 | liu      |   12 |    167 |   92 |
+------+----------+------+--------+------+
1 row in set (0.00 sec)

#多条件任一成立
mysql> select * from stu_s1 where id > 1001 or stu_name like 'l%';
+------+----------+------+--------+------+
| id   | stu_name | age  | height | math |
+------+----------+------+--------+------+
| 1009 | qin      |   18 |    172 |   87 |
| 1004 | liu      |   12 |    167 |   92 |
| 1008 | ming     |   31 |    192 |   87 |
+------+----------+------+--------+------+
3 rows in set (0.00 sec)

#排除某一条件
mysql> select * from stu_s1 where not(age > 18);
+------+----------+------+--------+------+
| id   | stu_name | age  | height | math |
+------+----------+------+--------+------+
| 1001 | xiao     |    8 |    183 |   89 |
| 1009 | qin      |   18 |    172 |   87 |
| 1004 | liu      |   12 |    167 |   92 |
+------+----------+------+--------+------+
3 rows in set (0.00 sec)

排序查询结果

order by 默认升序

ASC：升序

DESC：降序

LIMIT：限定范围

OFFSET：偏移

#按年龄排序
mysql> select * from stu_s1 order by age;
+------+----------+------+--------+------+
| id   | stu_name | age  | height | math |
+------+----------+------+--------+------+
| 1001 | xiao     |    8 |    183 |   89 |
| 1004 | liu      |   12 |    167 |   92 |
| 1009 | qin      |   18 |    172 |   87 |
| 1008 | ming     |   31 |    192 |   87 |
+------+----------+------+--------+------+
4 rows in set (0.00 sec)


#按id排序
mysql> select * from stu_s1 order by id;
+------+----------+------+--------+------+
| id   | stu_name | age  | height | math |
+------+----------+------+--------+------+
| 1001 | xiao     |    8 |    183 |   89 |
| 1004 | liu      |   12 |    167 |   92 |
| 1008 | ming     |   31 |    192 |   87 |
| 1009 | qin      |   18 |    172 |   87 |
+------+----------+------+--------+------+
4 rows in set (0.00 sec)

#降序排序 desc
mysql> select * from stu_s1 order by id desc;
+------+----------+------+--------+------+
| id   | stu_name | age  | height | math |
+------+----------+------+--------+------+
| 1009 | qin      |   18 |    172 |   87 |
| 1008 | ming     |   31 |    192 |   87 |
| 1004 | liu      |   12 |    167 |   92 |
| 1001 | xiao     |    8 |    183 |   89 |
+------+----------+------+--------+------+
4 rows in set (0.00 sec)

#对排序结果做限定，打印前3个
mysql> select * from stu_s1 order by age limit 3;
+------+----------+------+--------+------+
| id   | stu_name | age  | height | math |
+------+----------+------+--------+------+
| 1001 | xiao     |    8 |    183 |   89 |
| 1004 | liu      |   12 |    167 |   92 |
| 1009 | qin      |   18 |    172 |   87 |
+------+----------+------+--------+------+
3 rows in set (0.00 sec)

#对排序结果做偏移
mysql> select * from stu_s1 order by age limit 3 offset 1;
+------+----------+------+--------+------+
| id   | stu_name | age  | height | math |
+------+----------+------+--------+------+
| 1004 | liu      |   12 |    167 |   92 |
| 1009 | qin      |   18 |    172 |   87 |
| 1008 | ming     |   31 |    192 |   87 |
+------+----------+------+--------+------+
3 rows in set (0.00 sec)

如何写表

表的标识

表的约束

数据完整性类型

主键外键

表的主键：是表的唯一标识

表的外键：是与其他表关联的字段

数据完整性

目的：是为了保证插入表中的数据是正确的

类型

• 实体完整性：通过表的主键完成，作为主键的字段不可重复
• 域完整性：字段必须符合某种数据类型或约束
• 参照完整性：不允许引用不存在的实体，可以与另一个表的主键对应

关系型模型必须满足的约束条件：实体完整性、参照完整性

表的约束

定义主键约束（primary key）：不允许空，不允许重复

定义主键自动增长（auto_increment）

定义唯一（unique）：不允许空，不允许重复

定义非空（not NULL）

定义外键约束（CONSTRAINT equipment1_ibfk_1 FOREIGN KEY (hero_id) REFERENCES hero1(id)）

#创建一张表，并指定主键，然后设置主键自动增长
create table member (id int auto_increment, name char(30), age int, primary key(id));
create table member1 (id int auto_increment primary key, name char(30), age int);

表的设计

一对多（一张表对应多张表，表中有多个外键）

多对多（多张表对应多张表，有单独的索引表）

一对一（一张表对应一张表，表中只有一个外键，方便表的扩展）

多表示例

王者荣耀：英雄表，装备表

#创建英雄表
create table hero (id int auto_increment primary key, name varchar(20), age int);

#创建装备表
create table equipment (id int auto_increment primary key, name varchar(20), price int not NULL, hero_id int, foreign key(hero_id) references hero(id)d));

#查看两个表的结构
mysql> show create table hero;
| Table | Create Table                                                                                   
| hero  | CREATE TABLE `hero` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
1 row in set (0.05 sec)

mysql> show create table equipment;
| Table     | Create Table                                                                               
| equipment | CREATE TABLE `equipment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `price` int NOT NULL,
  `hero_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `hero_id` (`hero_id`),
  CONSTRAINT `equipment_ibfk_1` FOREIGN KEY (`hero_id`) REFERENCES `hero` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
1 row in set (0.04 sec)


#添加英雄
mysql> insert into hero values
    -> (1, 'libai', 33),
    -> (2, 'hanxin', 23),
    -> (3, 'chengyaojin', 44),
    -> (4, 'diaochan', 18),
    -> (5, 'xiaoqiao', 21);
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0


#添加装备
insert into equipment values(1, 'dayedao', 500, 1);
insert into equipment values(2, 'tiejian', 450, 3);

mysql> select * from equipment;
+----+---------+-------+---------+
| id | name    | price | hero_id |
+----+---------+-------+---------+
|  1 | dayedao |   500 |       1 |
|  2 | tiejian |   450 |       3 |
+----+---------+-------+---------+
2 rows in set (0.00 sec)

多表操作

删除

• 删除数据：要删除被外键关联的数据前，必须先删除外键才可
• 删除表：要删除被外键关联的表，也需要先删除所有外键
• 删除外键：alter table equipment1 drop foreign key equipment1_ibfk_2; （最后的参数是外键名）
• 删除主键：alter table hero1 drop primary key; （如果有外键关联，则无法删除）

复制

• 复制数据：create table hero1 select * from hero;
• 复制表结构：create table hero2 like hero;

设置

• 设置主键（还没有主键）：alter table hero1 modify id int primary key;

• 设置外键：alter table equipment1 add foreign key(hero_id) references hero1(id);

DQL多表查询

连接查询（交叉连接、内连接、外连接）

联合查询

报表查询

子查询

统计函数

连接查询

交叉连接（cross join）：不带on子句，返回连接表中的所有数据行的笛卡尔积

内连接（inner join）：返回连接表中符合连接条件和查询条件的数据行

• 显式内连接：使用inner join关键字，在on子句中设定连接条件
• 隐式内连接：不用inner join和on关键字，在where子句中设定连接条件

外连接：分为左外连接（left outer join）和右外连接（right outer join），不仅返回符合连接和查询条件的数据行，也返回不符合查询但符合连接条件的数据行

#使用交叉连接查询
select * from hero cross join equipment;
select * from hero,equipment;

#显式内连接
select * from hero inner join equipment on hero.id = equipment.hero_id;
select * from hero as h inner join equipment as e on h.id = e.hero_id;	#使用别名

#隐式内连接
select * from hero,equipment where hero.id = equipment.hero_id;
select * from hero as h, equipment as e where h.id = e.hero_id;

#左外连接
mysql> select * from hero left outer join equipment on hero.id = equipment.hero_id;
+----+-------------+------+------+---------+-------+---------+
| id | name        | age  | id   | name    | price | hero_id |
+----+-------------+------+------+---------+-------+---------+
|  1 | libai       |   33 |    1 | dayedao |   500 |       1 |
|  2 | hanxin      |   23 | NULL | NULL    |  NULL |    NULL |
|  3 | chengyaojin |   44 |    2 | tiejian |   450 |       3 |
|  4 | diaochan    |   18 | NULL | NULL    |  NULL |    NULL |
|  5 | xiaoqiao    |   21 | NULL | NULL    |  NULL |    NULL |
+----+-------------+------+------+---------+-------+---------+
5 rows in set (0.00 sec)

#右外连接
mysql> select * from hero right outer join equipment on hero.id = equipment.hero_id;
+------+-------------+------+----+---------+-------+---------+
| id   | name        | age  | id | name    | price | hero_id |
+------+-------------+------+----+---------+-------+---------+
|    1 | libai       |   33 |  1 | dayedao |   500 |       1 |
|    3 | chengyaojin |   44 |  2 | tiejian |   450 |       3 |
+------+-------------+------+----+---------+-------+---------+
2 rows in set (0.00 sec)

子查询

也叫嵌套查询

是指在where子句或from子句中，有嵌入select语句

一般写在where子句中

#嵌套查询
select * from hero where id = (select hero_id from equipment where id = 1);

联合查询

使用UNION关键字

合并两条查询语句的结果，并去掉重复行

union 去重

union all 不去重

#联合查询
select * from hero where id > 3 union all select * from hero where age > 23;

报表查询

对数据行进行分组统计

select... from... [where...] [group by...] [having...][order by...]

group by子句指定按照哪些字段分组

having子句设置分组查询条件

在报表查询中，可以使用SQL函数（count、sum、arg、max、min等）

#报表查询，会去除重复数据
select age from hero group by age;

#使用count函数，统计age组中每个成员的出现次数
mysql> select count(age), age from hero group by age;
+------------+------+
| count(age) | age  |
+------------+------+
|          1 |   33 |
|          1 |   23 |
|          1 |   44 |
|          1 |   18 |
|          1 |   21 |
|          2 |   32 |
+------------+------+
6 rows in set (0.04 sec)


#查找某个字段的最大值，最小值
select max(age) from hero;
select min(age) from hero;
select min(age), max(age) from hero;

#限制分组范围
mysql> select count(age), age from hero group by age having age > 30;
+------------+------+
| count(age) | age  |
+------------+------+
|          1 |   33 |
|          1 |   44 |
|          2 |   32 |
+------------+------+
3 rows in set (0.04 sec)

数据库编程

架构：C/S编程

网址：www.mysql.com

API接口

必须加编译选项：-lmysqlclient

目前需求：对数据库中的表，增删查改

查询 query.c

#include <mysql/mysql.h>
#include <string.h>
#include <stdio.h>

int main(int argc,char* argv[])
{
    //输入：query, 在hero表中的名字
    if(2 != argc) {
        printf("error args\n");
        return -1;
    }

    MYSQL *conn;
    MYSQL_RES *res;//SQL语句结果
    MYSQL_ROW row;
    char server[] = "localhost";//限制从本地主机连接
    char user[] = "zzz";//MYSQL账户
    char password[] = "1111";//账户密码
    char database[] = "Test";//要访问的数据库名称

    char query_tmp[300] = "select * from hero where name='";
    unsigned int queryRet;
    char query[500] = "";
    sprintf(query, "%s%s%s", query_tmp, argv[1], "'");
    /* strcpy(query,"select * from hero"); */

    //在输出前先打印查询语句
    puts(query);

    //初始化
    conn = mysql_init(NULL);
    if(!conn)  {
        printf("MySQL init failed\n");
        return -1;
    }

    //连接数据库，看连接是否成功，只有成功才能进行后面的操作
    if(!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
        printf("Error connecting to database: %s\n", mysql_error(conn));
        return -1;
    }
    else {
        printf("MySQL Connected...\n");
    }

    //把SQL语句传递给MySQL
    queryRet = mysql_query(conn, query);
    if(queryRet) {
        printf("Error making query: %s\n", mysql_error(conn));
    }
    else {
        //用mysql_num_rows可以得到查询的结果集有几行
        //要配合mysql_store_result使用
        //第一种判断方式
        res = mysql_store_result(conn);
        printf("mysql_num_rows = %lu\n", (unsigned long)mysql_num_rows(res));

        //第二种判断方式,两种方式不能一起使用
        /* res = mysql_use_result(conn); */

        row = mysql_fetch_row(res);
        if(NULL == row) {
            printf("Don't find any data\n");
        }
        else {
            do 
            {	
                /* printf("num=%d\n",mysql_num_fields(res));//列数 */

                //每次for循环打印一整行的内容
                for(queryRet = 0; queryRet < mysql_num_fields(res); ++queryRet) {
                    printf("%12s ", row[queryRet]);
                }
                printf("\n");

            }while(NULL != (row = mysql_fetch_row(res)));
        }

        //回收res
        mysql_free_result(res);
    }

    //回收conn
    mysql_close(conn);

    return 0;
}

插入 insert.c

#include <mysql/mysql.h>
#include <string.h>
#include <stdio.h>

int main(int argc,char* argv[])
{
	MYSQL *conn;
	char server[] = "localhost";
	char user[] = "zzz";
	char password[] = "1111";
	char database[] = "Test";//要访问的数据库名称

    char query[200]="insert into hero (id, name, age) values (17, 'daji', 16)";
	int queryResult;

    //初始化MYSQL
	conn = mysql_init(NULL);

	if(!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
		printf("Error connecting to database: %s\n", mysql_error(conn));
	}
    else {
		printf("MYSQL Connected...\n");
	}

    //输入SQL语句
	queryResult = mysql_query(conn, query);
	if(queryResult) {
		printf("Error making query:%s\n", mysql_error(conn));
	}
    else {
		printf("insert success\n");
	}

	mysql_close(conn);
	return 0;
}

删除 delete.c

#include <mysql/mysql.h>
#include <string.h>
#include <stdio.h>

int main(int argc,char* argv[])
{
    MYSQL *conn;

    char server[] = "localhost";
    char user[] = "zzz";
    char password[] = "1111";
    char database[] = "Test";

    char query[200] = "delete from equipment where name='tiejian'";
    int queryRet;

    //初始化MYSQL
    conn = mysql_init(NULL);

    //连接数据库
    if(!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
        printf("Error connecting to database: %s\n", mysql_error(conn));
    }
    else {
        printf("MYSQL Connected...\n");
    }

    //输入SQL语句
    queryRet = mysql_query(conn, query);
    if(queryRet) {
        printf("Error making query:%s\n",mysql_error(conn));
    }
    else {
        unsigned long ret = mysql_affected_rows(conn);
        if(ret) {
            printf("delete success, delete row=%lu\n", ret);
        }
        else {
            printf("delete failed, mysql_affected_rows: %lu\n", ret);
        }
    }

    mysql_close(conn);

    return 0;
}

更新 update.c

#include <mysql/mysql.h>
#include <stdio.h>
#include <string.h>

int main(int argc,char* argv[])
{
    //输入：./update hero表中要更新的名字 哪个id
	if (argc != 3) {
		printf("error args\n");
		return -1;
	}

	MYSQL *conn;
	/* MYSQL_RES *res; */
	/* MYSQL_ROW row; */

	char server[] = "localhost";
	char user[] = "zzz";
	char password[] = "1111";
	char database[] = "Test";

	char query_tmp[200] = "update hero set name ='"; 
    char query[300] = "";
	sprintf(query, "%s%s%s%s", query_tmp, argv[1],"' where id =", argv[2]);
	int queryResult;

    //显式更新语句
	puts(query);

    //初始化MYSQL
	conn=mysql_init(NULL);

    //连接数据库
	if(!mysql_real_connect(conn,server,user,password,database,0,NULL,0)) {
		printf("Error connecting to database:%s\n",mysql_error(conn));
	}
    else {
		printf("Connected...\n");
	}

    //输入SQL语句
	queryResult = mysql_query(conn, query);
	if(queryResult) {
		printf("Error making query:%s\n",mysql_error(conn));
	}
    else {
        int ret = mysql_affected_rows(conn);
        if(ret) {
            printf("update success\n");
        }
        else {
            printf("update fail, mysql_affected_rows:%d\n", ret);
        }
	}

	mysql_close(conn);
	return 0;
}