[每天记录一个问题，记录十年 Blog]

1.1 mysql管理

A. mysql服务启、停、查询

net start mysql
net stop mysql
net stop mysql&net start mysql
sc query mysql      // 查询服务状态

B. mysq登录

mysql -h 127.0.0.1 -P 3306 -u root -p       // mysql -h 主机名或IP地址 -P 端口号 -u 用户名 -p
exit/quit       // 退出登录

C. 数据库操作

// 创建数据库
create database if exists mydatabase
  character set utf8mb4             # utf8mb4字符集
  collate utf8mb4_general_ci;       # general_cli排序规则

// 删除数据库
drop database if exists mydatabase;

show databases;         # 列出数据库
use your_database;      # 选择数据库
show tables;            # 列出表
desc table_name;                    # 查看表结构
show index from table_name;         # 查看表的索引
show create table table_name;       # 查看表创建语句
select count(*) from table_name;    # 查看表行数

// 查看列的信息
select column_name, data_type, is_nullable, column_key
from information_schema.columns
where table_schema = 'your_database_name'
and table_name = 'your_table_name';

// 查看外键信息
select
    table_name,
    column_name,
    constraint_name,
    referenced_table_name,
    referenced_column_name
from
    information_schema.key_column_usage
where
    table_schema = 'your_database_name'
    and table_name = 'your_table_name'
    and referenced_table_name is not null;

D. 数据表操作（增删）

// 创建表1
create table users (
    id int auto_increment primary key,      # id，自增，主键
    username varchar(50) not null,          # 用户名，变长，不为空
    email varchar(100) not null,            # 邮箱，变长，不为空
    birthdate date,                         # 生日，日期
    is_active boolean default true          # 激活状态，布尔，默认true
);

// 创建表2
create table mytable (
    id int primary key,
    name varchar(50)
) character set utf8mb4 collate utf8mb4_general_ci;     # utf8字符集，general_cli排序规则

// 创建表3
create table if not exists `runoob_tbl`(        # 不存在，则创建
   `runoob_id` int unsigned auto_increment,     # id，无符号整数，自增
   `runoob_title` varchar(100) not null,
   `runoob_author` varchar(40) not null,
   `submission_date` date,
   primary key ( `runoob_id` )
)engine=innodb default charset=utf8;        # innodb存储引擎，utf8编码

// 删除表
drop table if exists table_name;            # 检查是否存在，存在则删除

1.2 表数据增删改

A. 插入表数据insert into

// 插入表数据1
insert into users (username, email, birthdate, is_active)
values ('test', 'test@runoob.com', '1990-01-01', true);         #字符型数据添加单/双引号

// 插入表数据2
insert into users                                               # 插入所有列，省略列名
values (null,'test', 'test@runoob.com', '1990-01-01', true);    # null，自增长列占位符

// 插入表数据3
insert into users (username, email, birthdate, is_active)       # 插入多行数据
values
    ('test1', 'test1@runoob.com', '1985-07-10', true),
    ('test2', 'test2@runoob.com', '1988-11-25', false),
    ('test3', 'test3@runoob.com', '1993-05-03', true);

B. 删除表数据delete

delete from users where username = 'test';              # 删除特定用户
delete from users where email = 'test@runoob.com';      # 删除特定邮箱用户
delete from users where is_active = false;              # 删除未激活用户
delete from users where birthdate < '1990-01-01';       # 删除指定日期前用户

// 删除符合的行
delete from students where graduation_year = 2021;

// 删除所有行(但表结构保持不变)
delete from orders;

// 使用子查询删除符合条件的行
delete 
from
    customers 
where
    customer_id in ( select customer_id from orders where order_date < '2023-01-01' );

C. 更新表数据update

// 更新单列值
update employees set salary = 60000 where employee_id = 101;

// 更新多列值
update orders set status = 'shipped', ship_date = '2023-03-01' where order_id = 1001;

// 表达式更新值
update products set price = price * 1.1 where category = 'electronics';

// 更新符合的所有行
update students set status = 'graduated';       # 所有学生状态更为'graduated'

// 更新使用子查询的值
update customers 
set total_purchases = ( select sum( amount ) from orders where orders.customer_id = customers.customer_id ) 
where
    customer_type = 'premium';

1.3 查询表数据select

select * from users;                            # 所有列，所有行
select username, email from users;              # 指定列，所有行
select * from users where is_active = true;     # 满足条件的行
select * from users order by birthdate;         # 默认asc，按birthdate升序排列
select * from users order by birthdate desc;    # desc，按birthdate降序排列
select * from users limit 10;                   # limit，返回十行

1.3.1 select的where子句，>=,<=,=,!=,and,or,like模糊匹配,in条件,not条件,between条件,is null,is not null条件

select * from users where username like 'j%' and is_active = true;      # and运算，%通配符
select * from users where is_active = true or birthdate < '1990-01-01'; # or运算
select * from users where birthdate in ('1990-01-01', '1992-03-15', '1993-05-03');  # in

select * from products where category = 'electronics' and price > 100.00;
select * from orders where order_date >= '2023-01-01' or total_amount > 1000.00;
select * from customers where first_name like 'j%';                 # %多个字符，_单个字符
select * from countries where country_code in ('us', 'ca', 'mx');   # in多值匹配，类似or简写
select * from products where not category = 'clothing';             # not逻辑否定
select * from orders where order_date between '2023-01-01' and '2023-12-31';    # 范围匹配
select * from employees where department is null;       # is null，查找空值
select * from customers where email is not null;        # is not null，查找非空值

// where的like子句：%多个字符,_单个字符
select * from customers where last_name like 'S%';      # 匹配S开头客户
select * from products where product_name like '_a%';   # 匹配第二字符a的产品
select * from users where username like 'a%o_';         # 匹配a开头，o为倒数第二位
select * from employees where last_name like 'smi%' collate utf8mb4_general_ci; #不分大小写

1.3.2select的UNION操作符

// 基本 union 操作
select city from customers
union
select city from suppliers
order by city;

// 选择客户表和供应商表中所有城市的唯一值，并按城市名称升序排序
select product_name from products where category = 'electronics'
union
select product_name from products where category = 'clothing'
order by product_name;

// employees 表中的 first_name 和 last_name，以及 departments 表中的 department_name 和 null， 所有的结果都按照 first_name 列排序
select first_name, last_name from employees
union
select department_name, null from departments
order by first_name;

// 使用 union all 将客户表和供应商表中的所有城市合并在一起，不去除重复行
select city from customers
union all
select city from suppliers
order by city;

// 带有 where 的 sql union all,使用 union all 从 "websites" 和 "apps" 表中选取所有的中国(cn)的数据（也有重复的值）
select country, name from websites
where country='cn'
union all
select country, app_name from apps
where country='cn'
order by country;

1.3.3 select的order by子句

// 多列排序,选择员工表 employees 中的所有员工，并先按部门 id 升序 asc 排序，然后在相同部门中按雇佣日期降序 desc 排序
select * from employees
order by department_id asc, hire_date desc;

// 使用数字表示列的位置,选择员工表 employees 中的名字和工资列，并按第三列（salary）降序 desc 排序，然后按第一列（first_name）升序 asc 排序
select first_name, last_name, salary
from employees
order by 3 desc, 1 asc;

// 使用表达式排序
select product_name, price * discount_rate as discounted_price
from products
order by discounted_price desc;

// 从 mysql 8.0.16 版本开始，可以使用 nulls first 或 nulls last 处理 null 值
select product_name, price          # null 值排在最后
from products
order by price desc nulls last;

select product_name, price          #  null 值排在前面
from products
order by price desc nulls first;

1.3.4 select的group by 语句，在分组的列上我们可以使用 COUNT, SUM, AVG,等函数

// 使用 group by customer_id 将结果按 customer_id 列分组，然后使用 sum(order_amount) 计算每个组中 order_amount 列的总和，as total_amount 是为了给计算结果取一个别名，使查询结果更易读
select customer_id, sum(order_amount) as total_amount
from orders
group by customer_id;

select column1, column2, aggregate_function(column3)
from table_name
where condition
group by column1, column2;

// 将数据表按名字进行分组，并统计每个人有多少条记录
select name, count(*) from   employee_tbl group by name;

// 将以上的数据表按名字进行分组，再统计每个人登录的次数
select name, sum(signin) as signin_count from  employee_tbl group by name with rollup;

// 如果名字为空我们使用总数代替
select coalesce(name, '总数'), sum(signin) as signin_count from  employee_tbl group by name with rollup;

1.3.5 MySQL 连接

// 可以在 select, update 和 delete 语句中使用 mysql 的 join 来联合多表查询，join 按照功能大致分为如下三类：
inner join（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
left join（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
right join（右连接）： 与 left join 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

// 等值连接
// 简单的 inner join，选择 orders 表和 customers 表中满足连接条件的订单 id 和客户名称
select orders.order_id, customers.customer_name
from orders
inner join customers on orders.customer_id = customers.customer_id;

// 使用表别名，使用表别名 o 和 c 作为 orders 和 customers 表的别名
select o.order_id, c.customer_name
from orders as o
inner join customers as c on o.customer_id = c.customer_id;

// 多表 inner join，选择了订单 id、客户名称和产品名称，连接了这些表的关联列
select orders.order_id, customers.customer_name, products.product_name
from orders
inner join customers on orders.customer_id = customers.customer_id
inner join order_items on orders.order_id = order_items.order_id
inner join products on order_items.product_id = products.product_id;

// where 子句进行过滤，过滤订单日期在 '2023-01-01' 及以后的订单
select orders.order_id, customers.customer_name
from orders
inner join customers on orders.customer_id = customers.customer_id
where orders.order_date >= '2023-01-01';


// 左连接
// 选择客户表中的客户 id 和客户名称，并包括左表 customers 中的所有行，以及匹配的订单 id（如果有）
select customers.customer_id, customers.customer_name, orders.order_id
from customers
left join orders on customers.customer_id = orders.customer_id;

// 使用表别名 c 和 o 分别代替 customers 和 orders 表的名称
select c.customer_id, c.customer_name, o.order_id
from customers as c
left join orders as o on c.customer_id = o.customer_id;

// 选择客户 id、客户名称、订单 id 和产品名称。左连接保证了即使在 order_items 或 products 中没有匹配的行，仍然会返回客户和订单的信息
select customers.customer_id, customers.customer_name, orders.order_id, products.product_name
from customers
left join orders on customers.customer_id = orders.customer_id
left join order_items on orders.order_id = order_items.order_id
left join products on order_items.product_id = products.product_id;

// 过滤订单日期在 '2023-01-01' 及以后的订单，以及没有匹配订单的客户
select customers.customer_id, customers.customer_name, orders.order_id
from customers
left join orders on customers.customer_id = orders.customer_id
where orders.order_date >= '2023-01-01' or orders.order_id is null;


// 右连接
// 选择右表 orders 中的所有订单 id，并包括左表 customers 中匹配的客户 id。如果在 customers 表中没有匹配的客户 id，相关列将显示为 null
select customers.customer_id, orders.order_id
from customers
right join orders on customers.customer_id = orders.customer_id;

// right join 并不经常使用，因为它可以用 left join 和表的顺序交换来实现相同的效果。例如，上面的查询可以通过使用 left join 改写为：
select customers.customer_id, orders.order_id
from orders
left join customers on orders.customer_id = customers.customer_id;

1.3.6 mysql null 值处理

// 在 mysql 中，null 用于表示缺失的或未知的数据，处理 null 值需要特别小心，因为在数据库中它可能会导致不同于预期的结果。
// 为了处理这种情况，mysql提供了三大运算符：

is null： 当列的值是 null，此运算符返回 true。
is not null： 当列的值不为 null， 运算符返回 true。
<=>： 比较作符（不同于 = 运算符），当比较的两个值相等或者都为 null 时返回 true。
关于 null 的条件比较运算是比较特殊的。你不能使用 = null 或 ！= null 在列中查找 null 值 。

// 在 mysql 中，null 值与任何其它值的比较（即使是 null）永远返回 null，即 null = null 返回 null 。
// mysql 中处理 null 使用 is null 和 is not null 运算符。

select * , columnname1+ifnull(columnname2,0) from tablename;

// columnname1，columnname2 为 int 型，当 columnname2 中，有值为 null 时，columnname1+columnname2=null， ifnull（columnname2,0） 把 columnname2 中 null 值转为 0。

// mysql 中处理 null 值的常见注意事项和技巧

A. 检查是否为 null：

// 要检查某列是否为 null，可以使用 is null 或 is not null 条件。
select * from employees where department_id is null;
select * from employees where department_id is not null;

B. 使用 coalesce 函数处理 null

// coalesce 函数可以用于替换为 null 的值，它接受多个参数，返回参数列表中的第一个非 null 值。
// 如果 stock_quantity 列为 null，则 coalesce 将返回 0。
select product_name, coalesce(stock_quantity, 0) as actual_quantity
from products;

C. 使用 ifnull 函数处理 null：

// ifnull 函数是 coalesce 的 mysql 特定版本，它接受两个参数，如果第一个参数为 null，则返回第二个参数。
select product_name, ifnull(stock_quantity, 0) as actual_quantity
from products;

D. null 排序：

// 在使用 order by 子句进行排序时，null 值默认会被放在排序的最后。如果希望将 null 值放在最前面，可以使用 order by column_name asc nulls first，反之使用 order by column_name desc nulls last。
select product_name, price
from products
order by price asc nulls first;

E. 使用 <=>作符进行 null 比较：

// <=>作符是 mysql 中用于比较两个表达式是否相等的特殊作符，对于 null 值的比较也会返回 true。它可以用于处理 null 值的等值比较。
select * from employees where commission <=> null;

F. 注意聚合函数对 null 的处理：

// 在使用聚合函数（如 count， sum， avg）时，它们会忽略 null 值，因此可能会得到不同于预期的结果。如果希望将 null 视为 0，可以使用 coalesce 或 ifnull。
// 这样即使 salary 为 null，聚合函数也会将其视为 0。
select avg(coalesce(salary, 0)) as avg_salary from employees;

1.3.7 mysql 正则表达式

正则表达式匹配的字符类:
.：匹配任意单个字符。
^：匹配字符串的开始。
$：匹配字符串的结束。
*：匹配零个或多个前面的元素。
+：匹配一个或多个前面的元素。
?：匹配零个或一个前面的元素。
[abc]：匹配字符集中的任意一个字符。
[^abc]：匹配除了字符集中的任意一个字符以外的字符。
[a-z]：匹配范围内的任意一个小写字母。
[0-9]：匹配一个数字字符。
\w：匹配一个字母数字字符（包括下划线）。
\s：匹配一个空白字符。

// regexp 是用于进行正则表达式匹配的运算符。用于检查一个字符串是否匹配指定的正则表达式模式

// 查找 name 字段中以 'st' 为开头的所有数据：
mysql> select name from person_tbl where name regexp '^st';

// 查找 name 字段中以 'ok' 为结尾的所有数据：
mysql> select name from person_tbl where name regexp 'ok';

// 查找 name 字段中包含 'mar' 字符串的所有数据：
mysql> select name from person_tbl where name regexp 'mar';

// 查找 name 字段中以元音字符开头或以 'ok' 字符串结尾的所有数据：
mysql> select name from person_tbl where name regexp '^[aeiou]|ok';

// 选择订单表中描述中包含 "item" 后跟一个或多个数字的记录。
select * from orders where order_description regexp 'item[0-9]+';

// 使用 binary 关键字，使得匹配区分大小写：
select * from products where product_name regexp binary 'apple';

// 使用 or 进行多个匹配条件，以下将选择姓氏为 "smith" 或 "johnson" 的员工记录：
select * from employees where last_name regexp 'smith|johnson';


// 使用 rlike 进行模式匹配
// rlike 是 mysql 中用于进行正则表达式匹配的运算符，与 regexp 是一样的，rlike 和 regexp 可以互换使用，没有区别。

// 选择产品名称以数字开头的所有产品。
select * from products where product_name rlike '^[0-9]';

1.3.8 mysql事务

在 mysql 中，事务是一组sql语句的执行，它们被视为一个单独的工作单元。只有使用了 innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 sql 语句要么全部执行，要么全部不执行。
事务用来管理 insert、update、delete 语句
一般来说，事务是必须满足4个条件（acid）：：原子性（atomicity，或称不可分割性）、一致性（consistency）、隔离性（isolation，又称独立性）、持久性（durability）。

// 事务控制语句：
begin 或 start transaction 显式地开启一个事务；

commit 也可以使用 commit work，不过二者是等价的。commit 会提交事务，并使已对数据库进行的所有修改成为永久性的；

rollback 也可以使用 rollback work，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

savepoint identifier，savepoint 允许在事务中创建一个保存点，一个事务中可以有多个 savepoint；

release savepoint identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

rollback to identifier 把事务回滚到标记点；

set transaction 用来设置事务的隔离级别。innodb 存储引擎提供事务的隔离级别有read uncommitted、read committed、repeatable read 和 serializable。


// mysql 事务处理主要有两种方法：

1、用 begin, rollback, commit 来实现
begin 或 start transaction：开用于开始一个事务。
rollback 事务回滚，取消之前的更改。
commit：事务确认，提交事务，使更改永久生效。

2、直接用 set 来改变 mysql 的自动提交模式:
set autocommit=0 禁止自动提交
set autocommit=1 开启自动提交

// begin 或 start transaction -- 用于开始一个事务：
egin; -- 或者使用 start transaction;

// commit -- 用于提交事务，将所有的修改永久保存到数据库：
commit;

// rollback -- 用于回滚事务，撤销自上次提交以来所做的所有更改：
rollback;

// savepoint -- 用于在事务中设置保存点，以便稍后能够回滚到该点：
savepoint savepoint_name;

// rollback to savepoint -- 用于回滚到之前设置的保存点：
rollback to savepoint savepoint_name;

实例

-- 开始事务
start transaction;

-- 执行一些sql语句
update accounts set balance = balance - 100 where user_id = 1;
update accounts set balance = balance + 100 where user_id = 2;

-- 判断是否要提交还是回滚
if (条件) then
    commit; -- 提交事务
else
    rollback; -- 回滚事务
end if;


实例：一个简单的事务测试

mysql> use runoob;
database changed
mysql> create table runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
query ok, 0 rows affected (0.04 sec)

mysql> select * from runoob_transaction_test;
empty set (0.01 sec)

mysql> begin;  # 开始事务
query ok, 0 rows affected (0.00 sec)

mysql> insert into runoob_transaction_test value(5);
query ok, 1 rows affected (0.01 sec)

mysql> insert into runoob_transaction_test value(6);
query ok, 1 rows affected (0.00 sec)

mysql> commit; # 提交事务
query ok, 0 rows affected (0.01 sec)

mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

mysql> begin;    # 开始事务
query ok, 0 rows affected (0.00 sec)

mysql>  insert into runoob_transaction_test values(7);
query ok, 1 rows affected (0.00 sec)

mysql> rollback;   # 回滚
query ok, 0 rows affected (0.00 sec)

mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

mysql>

1.3.9 mysql alter 修改表名/表字段

// alter 命令允许你添加、修改或删除数据库对象，并且可以用于更改表的列定义、添加约束、创建和删除索引等操作。
// 使用 alter 命令时要格外小心，因为一些操作可能需要重建表或索引，这可能会影响数据库的性能和运行时间。

1. 添加列
// 添加一个名为 birth_date 的日期列：
alter table employees
add column birth_date date;

2. 修改列的数据类型
// 将 employees 表中的 salary 列的数据类型修改为 decimal（10,2）：
alter table employees
modify column salary decimal(10,2);

3. 修改列名
// 将 employees 表中的某个列的名字由 old_column_name 修改为 new_column_name，并且可同时修改数据类型
alter table employees
change column old_column_name new_column_name varchar(255);

4. 删除列
// 将 employees 表中的 birth_date 列删除：
alter table employees
drop column birth_date;

5. 添加 primary key
// 在 employees 表中添加了一个主键：
alter table employees
add primary key (employee_id);

6. 添加 foreign key
// 在 orders 表中添加了一个外键，关联到 customers 表的 customer_id 列：
alter table orders
add constraint fk_customer
foreign key (customer_id)
references customers (customer_id);

7. 修改表名
// 将表名由 employees 修改为 staff：
alter table employees
rename to staff;

实例：

root@host# mysql -u root -p password;
enter password:*******
mysql> use runoob;
database changed
mysql> create table testalter_tbl
    -> (
    -> i int,
    -> c char(1)
    -> );
query ok, 0 rows affected (0.05 sec)
mysql> show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | yes  |     | null    |       |
| c     | char(1) | yes  |     | null    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)


1. 删除，添加或修改表字段

// 删除，使用 alter 命令及 drop 子句来删除以上创建表的 i 字段,如果只剩余一个字段则无法使用drop来删除字段
mysql> alter table testalter_tbl  drop i;

// 添加，使用 add 子句来向数据表中添加列
mysql> alter table testalter_tbl add i int;
mysql> show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | yes  |     | null    |       |
| i     | int(11) | yes  |     | null    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

// 如需要指定新增字段的位置，可以使用mysql提供的关键字 first (设定位第一列)， after 字段名（设定位于某个字段之后）。
alter table testalter_tbl drop i;
alter table testalter_tbl add i int first;
alter table testalter_tbl drop i;
alter table testalter_tbl add i int after c;

first 和 after 关键字可用于 add 与 modify 子句，所以如果你想重置数据表字段的位置就需要先使用 drop 删除字段然后使用 add 来添加字段并设置位置。

// 修改字段类型及名称
使用 modify 或 change 子句 。在 change 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。
mysql> alter table testalter_tbl modify c char(10);
mysql> alter table testalter_tbl change i j bigint;
mysql> alter table testalter_tbl change j j int;

// alter table 对 null 值和默认值的影响,当你修改字段时，你可以指定是否包含值或者是否设置默认值。
// 指定字段 j 为 not null 且默认值为 100。如果你不设置默认值，mysql会自动设置该字段默认为 null。
mysql> alter table testalter_tbl 
    -> modify j bigint not null default 100;

// 修改字段默认值
mysql> alter table testalter_tbl alter i set default 1000;
mysql> show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | yes  |     | null    |       |
| i     | int(11) | yes  |     | 1000    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
// 也可以使用 alter 命令及 drop子句来删除字段的默认值
mysql> alter table testalter_tbl alter i drop default;
mysql> show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | yes  |     | null    |       |
| i     | int(11) | yes  |     | null    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
changing a table type:


// 修改数据表类型，可使用 alter 命令及 type 子句来完成。查看数据表类型使用 show table status
mysql> alter table testalter_tbl engine = myisam;
mysql>  show table status like 'testalter_tbl'\g
*************************** 1. row ****************
           name: testalter_tbl
           type: myisam
     row_format: fixed
           rows: 0
 avg_row_length: 0
    data_length: 0
max_data_length: 25769803775
   index_length: 1024
      data_free: 0
 auto_increment: null
    create_time: 2007-06-03 08:04:36
    update_time: 2007-06-03 08:04:36
     check_time: null
 create_options:
        comment:
1 row in set (0.00 sec)


// 修改表名
// 如果需要修改数据表的名称，可在 alter table 语句中使用 rename 子句。
// alter 命令还可以用来创建及删除mysql数据表的索引
mysql> alter table testalter_tbl rename to alter_tbl;

1.3.10 mysql 索引

mysql 索引是一种数据结构，用于加快数据库查询的速度和性能，可以大大提高 mysql 的检索速度。

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。
组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在 sql 查询语句的条件(一般作为 where 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

普通索引：普通索引是最常见的索引类型，用于加速对表中数据的查询

// 创建索引
// 在 students 表的 name 列上创建一个名为 idx_name 的普通索引，这将有助于提高通过姓名进行搜索的查询性能
create index idx_name on students (name);

// 使用 alter table 创建索引
alter table employees add index idx_age (age);

// 可在创建表的时候，在 create table 语句中直接指定索引，以创建表和索引的组合
create table students (
  id int primary key,
  name varchar(50),
  age int,
  index idx_age (age)           # 创建名为 idx_age 的普通索引
);

// 删除索引
drop index idx_age on employees;
alter table employees drop index idx_age;

唯一索引：唯一索引确保索引中的值是唯一的，不允许有重复值。

create unique index idx_email on employees (email);                 # create unique index
alter table employees add constraint idx_email unique (email);      # alter table 命令创建

create table employees (            # 创建表时指定
  id int primary key,
  name varchar(50),
  email varchar(100) unique
);

// 使用alter 命令添加和删除索引，有四种方式来添加数据表的索引：
alter table tbl_name add primary key (column_list): 该语句添加一个主键，主键列中的值必须唯一，主键的列的列表，可以是一个或多个列，不能包含 null 值。 。
alter table tbl_name add unique index_name (column_list): 这条语句创建索引的值必须是唯一的（除了null外，null可能会出现多次）。
alter table tbl_name add index index_name (column_list): 添加普通索引，索引值可出现多次。
alter table tbl_name add fulltext index_name (column_list):该语句指定了索引为 fulltext ，用于全文索引。

alter table testalter_tbl add index (c);            # 添加索引
mysql> alter table testalter_tbl drop index c;      # drop删除索引

// 使用 alter 命令添加和删除主键,主键作用于列上（可以一个列或多个列联合主键），添加主键时，你需要确保该主键默认不为空（not null）
alter table testalter_tbl modify i int not null;
alter table testalter_tbl add primary key (i);
// 删除主键时只需指定 primary key，但在删除索引时，你必须知道索引名。
alter table testalter_tbl drop primary key;
// 列出索引：show index，\g格式化输出
show index from table_name\g

1.4.1 mysql临时表

临时表是一种在当前会话中存在的表，作用范围仅限于创建它的会话。其他会话无法直接访问或引用该临时表

// 创建临时表
create temporary table temp_orders as
select * from orders where order_date >= '2023-01-01';

// 查询临时表
select * from temp_orders;

// 插入数据到临时表
insert into temp_orders (order_id, customer_id, order_date)
values (1001, 1, '2023-01-05');

// 查询临时表
select * from temp_orders;

// 删除临时表
drop temporary table if exists temp_orders;

// drop table 命令来手动删除临时表
mysql> create temporary table salessummary (
    -> product_name varchar(50) not null
    -> , total_sales decimal(12,2) not null default 0.00
    -> , avg_unit_price decimal(7,2) not null default 0.00
    -> , total_units_sold int unsigned not null default 0
);
query ok, 0 rows affected (0.00 sec)

mysql> insert into salessummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> values
    -> ('cucumber', 100.25, 90, 2);

mysql> select * from salessummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> drop table salessummary;
mysql>  select * from salessummary;
error 1146: table 'runoob.salessummary' doesn't exist

1.4.2 mysql复制表

// 1、获取数据表的完整结构
mysql> show create table runoob_tbl \g;
*************************** 1. row ***************************
       table: runoob_tbl
create table: create table `runoob_tbl` (
  `runoob_id` int(11) not null auto_increment,
  `runoob_title` varchar(100) not null default '',
  `runoob_author` varchar(40) not null default '',
  `submission_date` date default null,
  primary key  (`runoob_id`),
  unique key `author_index` (`runoob_author`)
) engine=innodb 
1 row in set (0.00 sec)

error:
no query specified

// 2、修改 sql 语句的数据表名，并执行 sql 语句
mysql> create table `clone_tbl` (
  -> `runoob_id` int(11) not null auto_increment,
  -> `runoob_title` varchar(100) not null default '',
  -> `runoob_author` varchar(40) not null default '',
  -> `submission_date` date default null,
  -> primary key  (`runoob_id`),
  -> unique key `author_index` (`runoob_author`)
-> ) engine=innodb;
query ok, 0 rows affected (1.80 sec)

// 3、拷贝数据表数据
mysql> insert into clone_tbl (runoob_id,
    ->                        runoob_title,
    ->                        runoob_author,
    ->                        submission_date)
    -> select runoob_id,runoob_title,
    ->        runoob_author,submission_date
    -> from runoob_tbl;
query ok, 3 rows affected (0.07 sec)
records: 3  duplicates: 0  warnings: 0


使用 mysqldump 备份和还原数据表
mysqldump -u username -p dbname old_table > old_table_dump.sql
mysql -u username -p new_dbname < old_table_dump.sql

1.4.3 mysql元数据

元数据存储在系统表中，位于 MySQL 数据库的 information_schema 数据库，通过查询这些系统表，可以获取数据库结构、对象和其他相关信息的详细信息

查询结果信息： select, update 或 delete语句影响的记录数。
数据库和数据表的信息： 包含了数据库及数据表的结构信息。
mysql 服务器信息： 包含了数据库服务器的当前状态，版本号等。

// information_schema 数据库
schemata 表:存储有关数据库的信息，如数据库名、字符集、排序规则等。
select * from information_schema.schemata;

tables 表:包含有关数据库中所有表的信息，如表名、数据库名、引擎、行数等。
select * from information_schema.tables where table_schema = 'your_database_name';

columns 表:包含有关表中列的信息，如列名、数据类型、是否允许 null 等。
select * from information_schema.columns where table_schema = 'your_database_name' and table_name = 'your_table_name';

statistics 表:提供有关表索引的统计信息，如索引名、列名、唯一性等。
select * from information_schema.statistics where table_schema = 'your_database_name' and table_name = 'your_table_name';

key_column_usage 表:包含有关表中外键的信息，如外键名、列名、关联表等。
select * from information_schema.key_column_usage where table_schema = 'your_database_name' and table_name = 'your_table_name';

// 获取服务器元数据
命令                  描述
select version( )   服务器版本信息
select database( )  当前数据库名 (或者返回空)
select user( )      当前用户名
show status         服务器状态
show variables      服务器配置变量

1.4.4 mysql 序列（auto_increment）
在 mysql 中，序列是一种自增生成数字序列的对象，是一组整数 1、2、3、…，一张数据表只能有一个字段自增主键。尽管 mysql 本身并没有内建的序列类型，但可以使用 auto_increment 属性来模拟序列的行为，通常 auto_increment 属性用于指定表中某一列的自增性。

// id 列被定义为 int auto_increment，这表示每次插入一行数据时，id 列的值会自动增加。主键约束保证了 id 列的唯一性。
create table example_table (
    id int auto_increment primary key,
    name varchar(50)
);

插入数据，可以不指定 id 列的值，数据库会自动为其分配一个唯一的、自增的值：
insert into example_table (name) values ('john');

也可使用 last_insert_id() 函数来获取刚插入的行的自增值：
select last_insert_id();

如需要获取表的当前自增值，可以使用,在结果集中，auto_increment 列的值即为当前表的自增值
show table status like 'example_table';

注意，使用 auto_increment 属性的列只能是整数类型（通常是 int 或 bigint）。此外，如果删除表中的某一行，其自增值不会被重新使用，而是会继续递增。如果希望手动设置自增值，可以使用 set 语句，但这不是一种常规做法，因为可能引起唯一性冲突。

// 使用 mysql auto_increment 来定义序列
mysql> create table insect
    -> (
    -> id int unsigned not null auto_increment,
    -> primary key (id),
    -> name varchar(30) not null, # type of insect
    -> date date not null, # date collected
    -> origin varchar(30) not null # where collected
);
query ok, 0 rows affected (0.02 sec)
mysql> insert into insect (id,name,date,origin) values
    -> (null,'housefly','2001-09-10','kitchen'),
    -> (null,'millipede','2001-09-10','driveway'),
    -> (null,'grasshopper','2001-09-10','front yard');
query ok, 3 rows affected (0.02 sec)
records: 3  duplicates: 0  warnings: 0
mysql> select * from insect order by id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

// 重置序列
如果删除了数据表中的多条记录，并希望对剩下数据的 auto_increment 列进行重新排列，那么可以通过删除自增的列，然后重新添加来实现。 不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。
mysql> alter table insect drop id;
mysql> alter table insect
    -> add id int unsigned not null auto_increment first,
    -> add primary key (id);

// 设置序列的开始值,一般情况下序列的开始值为 1，但如果需要指定一个开始值 100
mysql> create table insect
    -> (
    -> id int unsigned not null auto_increment,
    -> primary key (id),
    -> name varchar(30) not null, 
    -> date date not null,
    -> origin varchar(30) not null
)engine=innodb auto_increment=100 charset=utf8;

// 或者也可以在表创建成功后，通过以下语句来实现：
mysql> alter table t auto_increment = 100;

1.4.5 MySQL 处理重复数据

防止表中出现重复数据
// 可以设置指定的字段为 primary key（主键） 或者 unique（唯一） 索引来保证数据的唯一性,下表中无索引及主键，所以该表允许出现多条重复记录。
create table person_tbl
(
    first_name char(20),
    last_name char(20),
    sex char(10)
);

// 如果想设置表中字段 first_name，last_name 数据不能重复，可以设置双主键模式来设置数据的唯一性， 如果设置了双主键，那么那个键的默认值不能为 null，可设置为 not null。
create table person_tbl
(
   first_name char(20) not null,
   last_name char(20) not null,
   sex char(10),
   primary key (last_name, first_name)
);

// 如果设置了唯一索引，那在插入重复数据时，sql 语句将无法执行成功,并抛出错。
// insert ignore into 与 insert into 的区别就是 insert ignore into 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

// insert ignore into执行后不会出错，也不会向数据表中插入重复数据：
mysql> insert ignore into person_tbl (last_name, first_name)
    -> values( 'jay', 'thomas');
query ok, 1 row affected (0.00 sec)
mysql> insert ignore into person_tbl (last_name, first_name)
    -> values( 'jay', 'thomas');
query ok, 0 rows affected (0.00 sec)

insert ignore into 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而 replace into 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。

另一种设置数据的唯一性方法是添加一个 unique 索引：
create table person_tbl
(
   first_name char(20) not null,
   last_name char(20) not null,
   sex char(10),
   unique (last_name, first_name)
);

//统计重复数据，将统计表中 first_name 和 last_name的重复记录数：
mysql> select count(*) as repetitions, last_name, first_name
    -> from person_tbl
    -> group by last_name, first_name
    -> having repetitions > 1;

// 过滤重复数据,如果你需要读取不重复的数据可以在 select 语句中使用 distinct 关键字来过滤重复数据。
mysql> select distinct last_name, first_name
    -> from person_tbl;
// 也可以使用 group by 来读取数据表中不重复的数据：
mysql> select last_name, first_name
    -> from person_tbl
    -> group by (last_name, first_name);

// 删除重复数据，如果想删除数据表中的重复数据：
mysql> create table tmp select last_name, first_name, sex from person_tbl  group by (last_name, first_name, sex);
mysql> drop table person_tbl;
mysql> alter table tmp rename to person_tbl;
// 当然也可以在数据表中添加 index（索引） 和 primay key（主键）这种简单的方法来删除表中的重复记录：
mysql> alter ignore table person_tbl
    -> add primary key (last_name, first_name);

1.4.6 MySQL 及 SQL 注入

select * from users where username = '' or '1'='1'; --' and password = 'input_password';

if (preg_match("/^\w{8,20}/",_get['username'], matches)){result = mysqli_query(conn, "select * from users                          where username=matches[0]");
}
 else 
{
   echo "username 输入异常";
}

// 设定name 中插入了我们不需要的sql语句name = "qadir'; delete from users;";
 mysqli_query(conn, "select * from users where name='{name}'");


防范 sql 注入：

1、使用参数化查询或预编译语句： 使用参数化查询（prepared statements）可以有效防止 sql 注入，因为它们在执行查询之前将输入数据与查询语句分离。

2、输入验证和转义： 对用户输入进行适当的验证，并使用合适的转义函数（如mysqli_real_escape_string）来处理输入，以防止恶意注入。

3、最小权限原则： 给予数据库用户最小的权限，确保它们只能执行必要的操作，以降低潜在的损害。

4、使用orm框架： 使用对象关系映射（orm）框架（如hibernate、sequelize）可以帮助抽象 sql 查询，从而降低 sql 注入的风险。

5、禁用错误消息显示： 在生产环境中，禁用显示详细的错误消息，以防止攻击者获取有关数据库结构的敏感信息。

like 语句中的注入
sub = addcslashes(mysqli_real_escape_string(conn, "%something_"), "%_");
// sub == \%something\_
 mysqli_query(conn, "select * from messages where subject like '{$sub}%'");

 addcslashes() 函数在指定的字符前添加反斜杠。

语法格式:
addcslashes(string,characters)
参数          描述
string       必需。规定要检查的字符串。
characters   可选。规定受 addcslashes() 影响的字符或字符范围。

1.5.1 mysql导出数据:select … into outfile

select id, name, email
into outfile '/tmp/user_data.csv'
fields terminated by ','
lines terminated by '\n'
from users;

// 导出到 /tmp/runoob.txt 
mysql> select * from runoob_tbl 
    -> into outfile '/tmp/runoob.txt';

// 导出 csv
mysql> select * from passwd into outfile '/tmp/runoob.txt'
    -> fields terminated by ',' enclosed by '"'
    -> lines terminated by '\r\n';

// 生成一个文件，各值用逗号隔开
select a,b,a+b into outfile '/tmp/result.text'
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n'
from test_table;

select ... into outfile 语句属性:
1、load data infile是select ... into outfile的逆操作，select句法。为了将一个数据库的数据写入一个文件，使用select ... into outfile，为了将文件读回数据库，使用load data infile。
2、select...into outfile 'file_name'形式的select可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有file权限，才能使用此语法。
3、输出不能是一个已存在的文件。防止文件数据被篡改。
你需要有一个登陆服务器的账号来检索文件。否则 select ... into outfile 不会起任何作用。
4、在unix中，该文件被创建后是可读的，权限由mysql服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。

mysqldump 导出表作为原始数据

1. 导出整个数据库,导出 mydatabase 数据库到 mydatabase_backup.sql 文件：
mysqldump -u root -p mydatabase > mydatabase_backup.sql

2. 导出特定表,如果只想导出数据库中的某个表
mysqldump -u username -p password -h hostname database_name table_name > output_file.sql
或：
mysqldump -u root -p mydatabase mytable > mytable_backup.sql

3. 导出数据库结构,如果只想导出数据库结构而不包括数据，可以使用 --no-data 选项：
mysqldump -u username -p password -h hostname --no-data database_name > output_file.sql

4. 导出压缩文件,你可以将导出的数据进行压缩，以减小文件大小。例如，使用 gzip：
mysqldump -u username -p password -h hostname database_name | gzip > output_file.sql.gz

5、导出 sql 格式的数据到指定文件
mysqldump -u root -p runoob runoob_tbl>dump.txt
password ******

6、以上命令创建的文件内容如下：
-- mysql dump 8.23
--
-- host: localhost    database: runoob
---------------------------------------------------------
-- server version       3.23.58

--
-- table structure for table `runoob_tbl`
--

create table runoob_tbl (
  runoob_id int(11) not null auto_increment,
  runoob_title varchar(100) not null default '',
  runoob_author varchar(40) not null default '',
  submission_date date default null,
  primary key  (runoob_id),
  unique key author_index (runoob_author)
) type=myisam;

--
-- dumping data for table `runoob_tbl`
--

insert into runoob_tbl       values (1,'learn php','john poul','2007-05-24');
insert into runoob_tbl       values (2,'learn mysql','abdul s','2007-05-24');
insert into runoob_tbl       values (3,'java tutorial','sanjay','2007-05-06');

7、如果需要导出整个数据库的数据： mysqldump -u root -p runoob > database_dump.txt
password ******

8、如果需要备份所有数据库：
mysqldump -u root -p --all-databases>database_dump.txt
password ******


9、将数据表及数据库拷贝至其他主机
如果你需要将数据拷贝至其他的 mysql 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。

在源主机上执行以下命令，将数据备份至 dump.txt 文件中: mysqldump -u root -p database_name table_name > dump.txt
password *****

如果需要将备份的数据库导入到mysql服务器中，可以使用以下命令，使用以下命令需要确认数据库已经创建：
mysql -u root -p database_name mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name
以上命令中使用了管道来将导出的数据导入到指定的远程主机上。

1.5.2 MySQL 导入数据

1、mysql 命令导入
mysql -u your_username -p -h your_host -p your_port -d your_database
# mysql -uroot -p123456 < runoob.sql

2、source 命令导入
mysql> create database abc;      # 创建数据库
mysql> use abc;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source /home/abc/abc.sql  # 导入备份数据库

3、使用 load data 导入数据
mysql> load data local infile 'dump.txt' into table mytbl
  -> fields terminated by ':'
  -> lines terminated by '\r\n';
能明确地在load data语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。两个命令的 fields 和 lines 子句的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，fields 子句必须出现在 lines 子句之前。如果用户指定一个 fields 子句，它的子句 （terminated by、[optionally] enclosed by 和 escaped by) 也是可选的，不过，用户必须至少指定它们中的一个。

在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a
mysql> load data local infile 'dump.txt' 
    -> into table mytbl (b, c, a);

4、使用 mysqlimport 导入数据
// 从文件 dump.txt 中将数据导入到 mytbl 数据表中
mysqlimport -u root -p --local mytbl dump.txt
password *****

// mysqlimport 命令可以指定选项来设置指定格式 mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  mytbl dump.txt
password *****

// mysqlimport 语句中使用 --columns 选项来设置列的顺序
$ mysqlimport -u root -p --local --columns=b,c,a \
    mytbl dump.txt
password *****

mysqlimport的常用选项介绍
 选项                         功能
-d or --delete              新数据导入数据表中之前删除数据数据表中的所有信息
-f or --force               不管是否遇到错误，mysqlimport将强制继续插入数据
-i or --ignore              mysqlimport跳过或忽略那些有相同唯一关键字的行， 导入文件中的数据将被忽略
-l or -lock-tables      数据被插入之前锁住表，这样就防止了， 你在更新数据库时，用户的查询和更新受到影响。
-r or -replace              这个选项与－i选项的作用相反；此选项将替代 表中有相同唯一关键字的记录。
--fields-enclosed- by= char 指定文本文件中数据的记录时以什么括起的， 很多情况下 数据以双引号括起。 默认的情况下数据是没有被字符括起的。
--fields-terminated- by=char    指定各个数据的值之间的分隔符，在句号分隔的文件中， 分隔符是句号。您可以用此选项指定数据之间的分隔符。 默认的分隔符是跳格符（tab）
--lines-terminated- by=str  此选项指定文本文件中行与行之间数据的分隔字符串 或者字符。 默认的情况下mysqlimport以newline为行分隔符。 您可以选择用一个字符串来替代一个单个的字符： 一个新行或者一个回车。
mysqlimport 命令常用的选项还有 -v 显示版本（version）， -p 提示输入密码（password）等。

1.5.3 mysql 连接python

pip install pymysql

import pymysql

# 创建数据库连接
db = pymysql.connect(
    host="localhost",
    user="root",
    password="root",
    database="mysql"
)

print("数据库连接成功!")

cursor = db.cursor()
cursor.execute("SELECT * FROM db")

# 获取所有结果
results = cursor.fetchall()

for row in results:
    print(row)