MySQL笔记

一、SQL语句

创建数据库：create database XXX charset=utf8;
查询当前所用数据库：select database();
删除数据库：drop database XXX;
创建表：create table XXX(id int, name varchar(20));
查看表结构：desc XXX;
创建表：
create table stu(
    id int unsigned not null auto_increment primary key,
    name varchar(20),
    age tinyint unsigned default 0,
    high decimal(5,2),
    gender enum("man", "woman"),
    class_id int unsigned
);

插入数据：insert into stu values(0, "shen", 20, 188, "man", 1);
添加属性：alter table stu add birthday datatime;
修改属性约束：alter table stu modify birthday date;
修改字段名：alter table stu change birthday birth date default "1999-06-08"
删除属性：alter table stu drop high;
删除表：drop table stu;
查看表的创建语句：show create table stu;

插入： insert into stu  values(0, "zhang", 20, "man", 1, "1999-09-11");
	insert into stu  values(null, "zhang", 20, "man", 1, "1999-09-11");
	insert into stu  values(default, "zhang", 20, "man", 1, "1999-09-11");
部分插入：insert into stu(name, gender) values("shen", "man");
批量插入：insert into stu(name, gender) values("shen", "man")
									values("zhang", "woman")
									value("huang", "man");

修改：
update stu set gender="man", age=22 where name = "shen";

查询：
select * from stu where id = 2;
select name as 姓名, gender as 性别 from stu where id = 2;  -- as起别名

逻辑删除：
alter table stu add is_deleted int defalut 0;

 select s.id as 编号, s.name as 姓名 from stu as s;  -- 起别名
 select stu.id as 编号, stu.name as 姓名 from stu as s;  -- 起了别名不用就会异常，不认识stu
 select distinct gender from stu;  -- 去重
 
 
 -- 条件查询
 select * from stu where age > 18;
 select * from stu where age = 18;
 select * from stu where age <= 18;
 select * from stu where age>18 and age<28;
 select * from stu where age<18 or height>170;
 select * from stu where not (age<18 or height>170);
 select * from stu where age>18 and gender="man";
 
 
 -- 模糊查询, "%"替换一个或多个或没有，"_"替换一个
 select * from stu where name like "沈%";  -- 沈姓
 select * from stu where name like "%鑫%";  -- 含有"鑫"
 select * from stu where name like "__";  -- 两个字符的名字
 select * from stu where name like "__%";  -- 至少含有两个字符的名字
 -- 利用regular expression查找
 select * from stu where name rlike "^周.*伦$" -- 名字为"周...伦"
 
 
 -- 范围查询
 select * from stu where age in (11, 22, 33);
 select * from stu where age not in (11, 22, 33);
 select * from stu where age between 11 and 22; -- [11, 22]
 select * from stu where age not between 11 and 22;  -- "between and" 和 "not between and"的用法
 
 
 -- 判空
 select * from stu where height is null;
 select * from stu where height is not null;
 
 -- 排序
 select * from stu where gender = "man" order by age asc;
 select * from stu where gender = "man" order by age desc;
 select * from stu where (age between 18 and 24) and gender="man" order by age desc, id desc;
 
 
 -- 聚合函数
 select count(*) as 男生人数 from stu where gender = "man";
 select max(age) as 最大年龄 from stu;
 select avg(age) as 平均年龄 from stu;
 select round(avg(age), 2) as 平均年龄保留两位小数 from stu;  -- 四舍五入，round不写参数，默认为0
 select min(height) as 最小身高 from stu;
 select sum(height) as 总身高 from stu;
 
 -- 分组，分组和聚合一起用才有意义
 select gender, count(*) from stu group by gender;  -- 聚合函数对分组进行操作，查询结果必须含有分组属性
 select gender, avg(age) from stu group by gender;  -- 各个性别的平均年龄
 select gender, group_concat(name, "_", id) from stu group by gender;  -- 查询分组里的姓名
 -- where对原表的数据进行条件判断，having对分组进行条件判断，计算男性人数
 select gender, count(*) from stu group by gender having gender = "man";
 select gender, count(*) from stu where gender = "man" group by gender;  -- 计算男性人数
 select gender, group_concat(name) from stu group by gender having count(*)>2;  -- 人数大于2的性别信息

-- 分页
-- 限制查询的数量，用法：limit count 或 limit start count
select * from stu limit 2;  -- 只显示2个
select * from stu limit 0, 5;  -- 从0开始显示5个
select * from stu limit 5, 5;  -- 从5开始显示5个，limit (page-1)*num, num
select * from stu limit (7-1)*5, 5;  -- 报错
select * from stu order by age desc limit 10,2;  -- 先按照年龄降序排列，再输出第6页的数据

-- 连接查询
select * from stu, class;  -- 笛卡尔积
select * from stu inner join class;  -- 笛卡尔积

-- 内连接，相同属性也显示
select * from stu, class where stu.class_id = class.cls_id;  
select * from stu inner join class on stu.class_id = class.cls_id;  -- 内连接，不满足条件则不显示
select s.name, c.name from stu as s inner join class as c on s.class_id = c.cls_id;
select c.name, s.name from stu as s inner join class as c on s.class_id = c.cls_id order by c.name, s.class_id;

-- 左连接 
-- 以"left join"左边的表为基准，到另外一张表查找数据，满足条件则显示，不满足则为NULL
select * from stu left join class on stu.class_id = class.cls_id; 
select * from stu left join class on stu.class_id = class.cls_id having class.cls_id = null; 
select * from stu left join class on stu.class_id = class.cls_id where class_id = null;

-- 子查询，可以把子查询得到的结果当作一个新的表对待
select * from stu where height = (select max(height) from stu);

-- 以left join左边的表为基准，到另外一张表查找数据，满足条件则显示，不满足则为NULL
select * from 
(select cate_name, max(price) as max_price from goods group by cate_name) as g_new 
left join 
goods as g
on g_new.cate_name=g.cate_name and g_new.max_price=g.price
order by g_new.cate_name;

-- 带子查询的insert
insert into goods_cates(name) select cate_name from goods group by cate_name;
-- 更新的是goods表
update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;
-- 插入外键foreign key，goods表中的cate_id 参照 goods_cates表中的id
alter table goods add foreign key (cate_id) references goods_cates(id);
-- 删除外键
alter table goods drop foreign key 外键名称

-- 视图只操作它可以查询出来的数据，对于它查询不出的数据，即使基表有，也不可以通过视图来操作。 
-- 1.对于update,有with check option，要保证update后，数据要被视图查询出来
-- 2.对于delete,有无with check option都一样
-- 3.对于insert,有with check option，要保证insert后，数据要被视图查询出来
-- 4.对于没有where 子句的视图，使用with check option是多余的

-- 视图方便查数据
create view v_goods_info as select g.*, c.name as cate_name, b.name as brand_name from goods as g left join goods_cates as c on g.cate_id=c.id left join goods_brands as b on g.brand_id=b.id
-- 删除视图
drop view v_goods_info

start transaction  -- 开启事务 或 begin
 insert into class values(3,"python_03期");
 rollback
commit  -- 提交事物，只要不提交，期间执行的事物都可以rollback

-- 建立索引，查找的时间大大减小
-- 建立索引影响insert和update，因为数据表更新后还得更新索引查找树
set profiling = 1 
create index my_index on stu(name(20))
show profiles  -- 查看所用时间
show index from stu  -- 查看索引

-- 主从数据库：读写分离、负载均衡、数据备份

-- 导出数据库：mysqldump -u root -p test > test.sql
-- 导入数据库: mysql -u roor new_test < test.sql

二、C语言连接数据库

安装开发c/c++的库：apt install libmysqlclient-dev

1. 连接数据库使用的头文件和库文件

#include <mysql/mysql.h>
有些也在 #include <mysql.h>
程序中使用了访问mysql的有关函数接口，需要在链接时指定库名: linux平台为 -lmysqlclient

2、初始化连接句柄

MYSQL *mysql_init(MYSQL *mysql);
该方法用来初始化一个连接句柄，如果参数为空，则返回一个指向新分配的连接句柄的指针。如果传递一个已
有的结构，它将被重新初始化。出错时返回为NULL。

3. 连接数据库

MYSQL *mysql_real_connect(MYSQL *mysql, const char *host,
const char *user, const char *passwd,
const char *db, unsigned int port,
const char *unix_socket,
unsigned long clientflag);
参数介绍，其中
mysql是上一步mysql_init方法初始化后返回的指针，
host是主机名，或者连接的服务器IP地址，本地可以使用"localhost"，或"127.0.0.1"或""，或
NULL
user是用户名，数据库中添加的用户，管理员是"root"，
passwd 是用户的密码,
db 是数据库的名字，
port 是数据库的端口 3306，也可直接写0，意味着使用mysql默认端口，
unix_socket 一般为NULL,表示不使用unix套接字或者管道
clientflag 标志位，一般给0
返回值，失败为NULL,成功与第一个参数值相同。

4. 关闭连接

void mysql_close(MYSQL *mysql);
在不使用时，可以用该方法关闭连接。

5. 执行sql语句

int mysql_query(MYSQL *mysql, const char *q);
参数: mysql 是之前连接后返回的指针，
	  q 是要执行的sql语句，末尾可以没有分号，这个在命令工具中使用不同。
返回值：成功返回0。
注意:如果sql语句中有二进制数据，则应该使用mysql_real_query();

6. 提取结果

MYSQL_RES *mysql_store_result(MYSQL *mysql); 一次性提取所有数据
//MYSQL_RES *mysql_use_result(MYSQL *mysql);一次提取一行数据q

该方法，是在执行 mysql_query()成功之后调用的，可以立刻保存在客户端中收到的所有数据。
它返回一个指向结果集结构的指针。如果失败返回NULL;

7. 获取结果集中有多少行

uint64_t mysql_num_rows(MYSQL_RES *res);
只有执行了 mysql_store_result()之后,才可以调用该方法，获取结果集中的行数。如果没有返回行，则为0;

8. 取出结果集中的一行记录

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);
从结果集中取出一行，并把它存放到一个行结构中。当数据用完或者出错时，返回为NULL，通常该方法需要循环调用。

9. 查看记录行的列数

unsigned int mysql_field_count(MYSQL *mysql);

10. 释放结果集占用的内存

void mysql_free_result(MYSQL_RES *result);
执行完sql语句，得到返回的结果集，处理完数据后，需要调用该方法释放存放结果集的内存空间。

11. 获取错误信息

unsigned int mysql_errno(MYSQL *mysql); 返回错误码
const char *mysql_error(MYSQL *mysql); 返回错误信息描述

12. 连接测试程序

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

int main(){
    MYSQL connect;//mysql连接对象
    mysql_init(&connect);
    //连接到mysql
    if(mysql_real_connect(&connect,"localhost","root","root","test",3306,NULL,0)){   
        printf("连接mysql成功\n");
    }else{
        printf("err:%s\n",mysql_error(&connect));
        printf("连接mysql失败\n");
    }
    //关闭mysql连接
    mysql_close(&connect);
    return 0;
}

三、增加、修改、删除的程序

仅仅是SQL语句不同

#include <stdio.h>
#include <unistd.h>
#include <stdlib.h>
#include <mysql/mysql.h>

int main(){
    MYSQL* conn = mysql_init(NULL);

    if(NULL == conn){
        printf("err:%s\n",mysql_error(conn));
        exit(0);
    }

    //连接到mysql
    if(mysql_real_connect(conn,"localhost","root","root","testdb",3306,NULL,0)){   
        printf("连接mysql成功\n");
    }else{
        printf("err:%s\n",mysql_error(conn));
        exit(0);
    }

    char sql[128] = {0};
    // sprintf(sql, "insert into stu values(%s, %d)", "\"小李\"", 23);
    // sprintf(sql, "update stu set age = %d where name = %s", 30, "\'小张\'");
    sprintf(sql, "delete from stu where name = %s", "\'小张\'");
    
    int res = mysql_query(conn, sql);
    if(0 != res){
        printf("err:%s\n",mysql_error(conn));
        exit(0);
    }
    printf("sql执行成功\n");

    //关闭mysql连接
    mysql_close(conn);
    return 0;
}

三、查询的程序

#include <stdio.h>
#include <unistd.h>
#include <stdlib.h>
#include <mysql/mysql.h>

int main(){
    MYSQL* conn = mysql_init(NULL);

    if(NULL == conn){
        printf("err:%s\n",mysql_error(conn));
        exit(0);
    }

    //连接到mysql
    if(mysql_real_connect(conn,"localhost","root","root","testdb",3306,NULL,0)){   
        printf("连接mysql成功\n");
    }else{
        printf("err:%s\n",mysql_error(conn));
        exit(0);
    }

    char sql[128] = {0};
    sprintf(sql, "select * from stu");
    int res = mysql_query(conn, sql);

    if(0 != res){
        printf("err:%s\n",mysql_error(conn));
        exit(0);
    }
    printf("sql执行成功\n");

    // 获取结果集
    MYSQL_RES* mysql_res = mysql_store_result(conn);
    if(NULL == mysql_res){
        printf("err:%s\n",mysql_error(conn));
        exit(0);
    }

    // 获取记录条数以及属性个数
    int num = mysql_num_rows(mysql_res);
    int column = mysql_field_count(conn);
    if(0 ==  num){
        printf("没有查询到信息\n");
        exit(0);
    }
    printf("查询到%d条信息， 每条信息有%d个属性\n", num, column);
    
    // 循环打印获取的记录
    for(int i = 0; i < num; i++){
        MYSQL_ROW mysql_row = mysql_fetch_row(mysql_res);
        if(NULL == mysql_row){
            printf("数据处理完成\n");
        }
        for(int j = 0; j < column; j++){
            printf("%s ", mysql_row[j]);
        }
        printf("\n");
    }
    // 释放结果集
    mysql_free_result(mysql_res);
    //关闭mysql连接
    mysql_close(conn);
    return 0;
}

四、用户管理与授权

查看用户信息： select user,host,plugin from mysql.user;

创建用户示例： create user 'stu'@'localhost' identified by '123456';

创建用户指定加密方式 示例：create user 'stu1'@'localhost' identified WITH mysql_native_password by '123456';

更新用户密码，指定加密方式，注意密码强度大小写数字：
ALTER user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

授权用户对那些数据库的那些表可以进行操作 示例：
GRANT SELECT ON database_name.table_name TO 'user_name'@'localhost';
GRANT INSERT ON database_name.table_name TO 'user_name'@'localhost';
GRANT ALL ON database_name.table_name TO 'user_name'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'%'; -- * 表示任意所有库、所有表，%表示任意地址

删除用户：drop user 'name'@'localhost';