6.Mysql 常见的数据操作

常见的数据操作有：添加数据、删除数据、更新数据、数据的导入和导出。

数据的导入导出，可以查阅 Mysql官网 扩展更多，如果有安装数据库管理工具（datagrip、Navicat、DBeaver、MySQL Workbench），那么直接根据步骤点击即可。

以下都是在数据库和表已经建立操作的，如果还不清楚如何建库表，可以前往：Mysql 常见的数据定义语言DDL 。

添加数据

不指定字段添加单行数据

/*
insert into table_name
values (value1,value2,....);
*/
insert into sql231104 values(1,2138,'male',21,'北京大学',3.4,7);
insert into sql231104 values(2,3214,'male',null,'复旦大学',4.0,15);

指定字段添加单行数据

/*
insert into 表名(column1 [, column2, …, columnn])
values (value1 [,value2, …, valuen]);
*/
insert into sql231104(id,device_id,gender,age,university,gpa,active_days_within_30) values(1,2138,'male',21,'北京大学',3.4,7);
insert into sql231104(id,device_id,gender,age,university,gpa,active_days_within_30) values(2,3214,'male',null,'复旦大学',4.0,15);

指定字段，一次性添加多行数据

/*
insert into table_name(column1 [, column2, …, columnn])
values
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
*/
insert into sql231104(id,device_id,gender,age,university,gpa,active_days_within_30) 
values(1,2138,'male',21,'北京大学',3.4,7),
      (2,3214,'male',null,'复旦大学',4.0,15);

添加查询结果

/*
insert into table_name(column1 [, column2, …, columnn])
select column1 [, column2, …, columnn]
from table_name1 [<join_type> join table_name2 on <join_condition>]
*/
create table sql231104_qa like sql231104;-- 
insert into sql231104_qa select * from sql231104; -- 单表查询录入
insert into sql231104_qa table sql231104;-- 单表查询录入
-- 多表联结查询录入
insert into sql231110(device_id,university
select b.device_id,
			 b.question_id,
       a.university,
from sql231104 as a
right join sql231103 as b
on a.device_id = b.device_id;

insert into sql231110(device_id,university,question_cnt,right_question_cnt)
select a.device_id,
       a.university,
       sum(if(b.result is not null, 1, 0)) as question_cnt,
       sum(if(b.result = 'right', 1, 0))   as right_question_cnt
from sql231104 as a
left join sql231103 as b
on a.device_id = b.device_id
where 1=1
    and (month(date) = 8 or date is null)
group by a.device_id, a.university;

删除数据

删除全部数据

-- 清空数据
truncate table table_name;
-- 删除全部数据
delete
from table_name
where 1=1;

truncate table sql231110;
delete
from sql231110
where 1=1;

删除条件数据

/*
delete
from table_name
where 1=1
[and where_condition];
*/
delete
from sql231110
where 1=1
and university = '北京大学’;

更新数据

单表自更新

/* 
update table_name
set column1 = value1 [,conlumn2 = value2,...]
where 1=1
[and where_condition];
*/
update sql231110
set gender = 'female',age = 30
where 1=1;           -- 更新所有人员性别为男性，年龄30

update sql231110
set gender = 'female',age = 30
where 1=1
and device_id = 2138;-- 更新北京大学人员性别为男性，年龄30

多表联结更新

-- 更新回答正确的用户得分为80分
update sql231104
set gpa = 80
where device_id in (
    select distinct device_id
    from sql231103
    where result = 'right');

update sql231104 as a
join sql231103 as b on a.device_id = b.device_id
set a.gpa = 80;

导出数据

使用mysqldump导出sql文件

/* Windows 按 Win+R 输入 cmd ，Mac 打开终端
mysqldump [arguments] > file_name
[arguments]：有主机、端口号、用户，还有需要导出的操作，有数据库和表
file_name：是文件存储的路径和名字
输入完命令执行后需要输入用户密码
如果没有配置环境变量，报错mysql，也可以在执行命令前加上mysql路径，如/usr/local/mysql/bin/
*/
-- 导出指定数据库
mysqldump -h127.0.0.1 -p3306 -uroot -p sql_training > /Users/guanfawang/Downloads/mysqldump2/sql_training.sql
-- 导出多个数据库
mysqldump -h127.0.0.1 -p3306 -uroot -p --databases dwd sql_test1 > /Users/guanfawang/Downloads/mysqldump2/dwd_sqltest1.sql
-- 导出指定数据库的表
mysqldump -h127.0.0.1 -p3306 -uroot -p sql_training sql231030 > /Users/guanfawang/Downloads/mysqldump2/sql231030.sql
-- 导出指定数据库多个表
mysqldump -h127.0.0.1 -p3306 -uroot -p sql_training sql231030 sql231031 > /Users/guanfawang/Downloads/mysqldump2/sql231030_31.sql

导入数据

使用mysqldump导入sql文件

/*Windows 按 Win+R 输入 cmd ，Mac 打开终端
mysql -h127.0.0.1 -p3306 -uroot -p
本地操作的话可以省略成：mysql -u root -p
*/
create database if not exists sql_test2;
use sql_test2;
source /Users/guanfawang/Downloads/mysqldump2/sql231030_31.sql

如果有安装数据库管理工具，如Datagrip，则根据步骤即可操作；