数据导入、导出、管理表记录、

逃离我推掉我的手 2022-05-21 12:17 182阅读 0赞

数据导入: 把系统文件的内容存储到数据库服务器的表里  
load data infile “目录名/文件名” into table 库名.表名  
FIELDS TERMINATED BY “分隔符” LINES TERMINATED

BY “\\n”;

load data infile "/etc/passwd" into table userdb.user

fields terminated by ":" lines terminated by "\\n";  
  
例子：把系统用户信息存储到数据库服务器的表里  
/etc/passwd  
用户名:密码站位符号:uid:gid:描述信息:家目录:shell

create database userdb;  
create table userdb.user(  
name varchar(35),  
password char(1),  
uid int(2),  
gid int(2),  
comment varchar(60),  
homedir varchar(60),  
shell varchar(30),  
index(name)  
);

mysql> show variables like "secure\_file\_priv";

\#cp /etc/passwd /var/lib/mysql-files/

mysql > load data infile "/var/lib/mysql-files/passwd"

into table userdb.user fields terminated by ":"

lines terminated by "\\n";

mysql> alter table userdb.user add id int(2) zerofill

primary key auto\_increment first;

自定义数据导入时文件所在的目录  
\#mkdir /mydata  
\#chown mysql /mydata  
\#setenforce 0  
\#cp /etc/passwd /mydata/  
vim /etc/my.cnf  
\[mysqld\]  
secure\_file\_priv= /mydata  
:wq  
\#systemctl restart mysqld

数据导出:把表记录存储到系统文件里  
mysql> show variables like "secure\_file\_priv";

sql查询命令 INTO OUTFILE “目录名/文件名“;

sql查询命令 INTO OUTFILE “目录名/文件名“ fields

terminated by "符号" lines terminated by "符号";

mysql>select \* from userdb.user where id=18 into

outfile "/var/lib/mysql-files/user1.txt";

mysql> system cat /var/lib/mysql-files/user1.txt  
\++++++++++++++++++++++++++++++++++  
管理表记录(增 查 改 删)（userdb.user）  
insert into 增加表记录

一次插入1条记录给所有字段赋值  
insert into 库名.表名 values(值列表);  
  
一次插入N条记录给所有字段赋值  
insert into 库名.表名 values(值列表),(值列表),(值列表);

插入记录时只给指定的字段赋值  
insert into 库名.表名(字段名列表)values(值列表);  
insert into 库名.表名(字段名列表)values(值列表),(值列表),(

值列表);

insert into userdb.user values

(50,"bob","x",2022,2022,"this is

student","/home/bob","/bin/bash");

insert into userdb.user values

(60,"bob","x",2122,2122,"this is

student","/home/bob","/bin/bash"),

(63,"lucy","x",2322,2322,"this is

student","/home/lucy","/bin/bash");

insert into userdb.user(name,uid)values("tom",2300),

("alic",2987);

insert into userdb.user

(name,password,uid,gid,comment,homedir,shell)

values

("yaya","x",3003,3003,"teacher","/home/yaya","/bin/ba

sh");

select 查询表记录  
单表查询  
查询表中所有记录所有字段的值  
select \* from 库.表 ；

查询表中所有记录指定字段的值  
select 字段名列表 from 库.表 ；

查询表中符合条件记录指定字段的值  
select 字段名列表 from 库.表 where 条件；

查询条件的表示方式？  
1 数值比较 > >= < <= = !=  
字段名 符号 数字  
select name,uid from userdb.user where uid<10;  
select \* from userdb.user where id<=5;

2 字符比较 = ！=  
字段名 符号 “字符串”  
select name from userdb.user where name="apache";  
select name from userdb.user where shell="/bin/bash";

select name,shell,uid from userdb.user where

shell="/bin/bash";

select name from userdb.user where name!="root";

3 范围内查找  
in (值列表) 在...里  
not in (值列表) 不在...里  
between....and... 在...之间

select name from userdb.user where uid in (10,20,30);

select id,name from userdb.user where name in (

"mysql","rysnc","bob");

select name from userdb.user where shell not in

("/bin/bash","/sbin/nologin");

select name,shell,uid from userdb.user where shell not

in ("/bin/bash","/sbin/nologin");

select name,uid from userdb.user where uid between

10 and 20;

select id,name,uid from userdb.user where id between

30 and 40;

多个查询条件  
逻辑与 and 多个查询条件必须同时成立  
逻辑或 or 多个查询条件某一个条件成立就可以  
逻辑非 ！ 取反

select name from userdb.user where name="apache"

and uid=1200 and shell="/bin/bash";

select name from userdb.user where name="apache"

or uid=2 or shell="/bin/bash";

select name,uid,shell from userdb.user where

name="apache" or uid=2 or shell="/bin/bash";

select name,uid from userdb.user  
where  
name="root" or name="bin" and uid=1;

select name,uid from userdb.user  
where  
name="root" and name="bin" or uid=1;

select name,uid from userdb.user  
where  
(name="root" or name="bin" ) and uid=2;

select name,uid from userdb.user  
where  
(name="root" or name="bin" ) and uid=1;

DISTINCT 不显示重复的值  
select distinct shell from userdb.user;  
select distinct shell from userdb.user where

uid<1000;

is null 匹配空  
is not null 匹配非空  
insert into userdb.user(id ,name)values(66,NULL),

(67,null),(68,"null"),(69,"");

select id , name from userdb.user where name is null;  
select id , name from userdb.user where name is not

null;

select id , name from userdb.user where name="null";  
select id , name from userdb.user where name="";

四则运算 + - \* / %  
alter table userdb.user  
add linuxsys tinyint(2) default 60 after name,  
add linuxser tinyint(2) default 80 after linuxsys;  
  
alter table userdb.user  
add age tinyint(2) unsigned default 20 after name;

select name,linuxsys,linuxser from userdb.user;

select name,linuxsys,linuxser from userdb.user where

name="root";

select name,linuxsys,linuxser,linuxsys+linuxser as zcj

from userdb.user where name="root";

select name,linuxsys,linuxser,linuxsys+linuxser as zcj,

(linuxsys+linuxser)/2 as pjf from userdb.user where

name="root";

select name,age from userdb.user where name="root";

select name,age,2017-age as s\_year from userdb.user

where name="root";

聚集函数（做数据统计的内置命令）  
sum(字段名) 求和  
avg(字段名) 平均值  
min(字段名) 最小值  
max(字段名) 最大值  
count(字段名) 个数

select sum(linuxsys) ,avg(linuxsys) from userdb.user;

select sum(linuxsys) ,avg(linuxsys) from userdb.user  
\-> where  
\-> uid<=10;

select min(uid) from userdb.user where

shell="/bin/bash";

select max(uid) from userdb.user where

shell="/bin/bash";  
  
select count(id) ,count(name) from userdb.user;

select count(name) from userdb.user where

shell="/bin/bash";

模糊匹配  
like ‘表达式’  
% 零个或多个字符  
\_ 任意单个字符

select name from userdb.user where name like '\_\_\_\_';

select name,uid from userdb.user  
where name like '%a%';

select name,uid from userdb.user  
where name like 'a%';

select id,name,uid from userdb.user  
where name like '%';

select id,name,uid from userdb.user  
where name like '\_a\_';

select id,name,uid from userdb.user  
where name like 'r\_\_t';

select id,name,uid from userdb.user  
where uid like '\_\_\_\_';

select id,name,uid from userdb.user  
where uid like '%\_%';

select name,id from userdb.user where name like '%\_%'

and name="";

select name,uid from userdb.user where name like

'\_\_\_\_' and uid>=1000;

正则匹配  
regexp '正则表达式'  
^ $ . \* \[\]

insert into userdb.user(id , name)values(71,"yaya3"),

(72,"9yaya"),(73,"ya7ya"),(74,"yay6a");

select name from userdb.user  
where  
name regexp '\[0-9\]';

select name from userdb.user  
where  
name regexp '^\[0-9\]';

select name from userdb.user  
where  
name regexp '\[0-9\]$';

select name from userdb.user  
where  
name regexp '.....';

select name from userdb.user  
where  
name regexp '^.....$';

select name,uid from userdb.user  
where  
uid regexp '...';

select name,uid from userdb.user  
where  
uid regexp '^...$';

insert into userdb.user(id ,name)values(80,"ro");

select id,name,uid from userdb.user  
where name regexp '^r.\*o$';

select id,name,uid from userdb.user  
where name regexp '.\*';

限制显示查询记录数  
limit 数字 显示查询结果的前几行

limit 起始行,总行数  
起始行的编号从零开始

select \* from userdb.user;  
select \* from userdb.user limit 3;  
select \* from userdb.user where uid <=10 limit 5;

select \* from userdb.user limit 1,3;

mysql> select \* from userdb.user where uid <=10

limit 2,3;

查询排序  
order by 字段名 asc/desc

select name,uid,shell from userdb.user where shell!

="/bin/bash" and shell is not null order by uid;

select name,uid,shell from userdb.user where shell!

="/bin/bash" and shell is not null order by uid desc;

select name,uid,shell from userdb.user where shell!

="/bin/bash" and shell is not null order by uid desc

limt 3;

select name,uid,shell from userdb.user where shell!

="/bin/bash" and shell is not null order by uid limit

select 姓名，工资 from yginfo where age<=30 and 部门

="ope" order by 工资 desc;

查询分组  
group by 字段名；

select shell from userdb.user where uid>=10 and

uid<=40 ;

select shell from userdb.user where uid>=10 and

uid<=40 group by shell;

select gid from userdb.user group by gid;

select shell from userdb.user group by shell;

select gid from userdb.user group by gid having gid=1;

update修改表记录（修改记录字段的值）  
批量修改  
update 库.表 set 字段名="值"，字段名=值；  
update userdb.user set linuxser=100,linuxsys=80;

修改与条件匹配的记录字段的值；  
update 库.表 set 字段名="值"，字段名=值 where 条件；  
update userdb.user set age=18 where name="root";

update userdb.user set shell=null where name="bin";  
update userdb.user set shell="" where name="root";

delete删除表记录(以行为单位删除)  
删除表的所有记录  
delete from 库.表；

删除表中与条件匹配的记录  
delete from 库.表 where 条件；  
delete from userdb.user where shell is null;

where嵌套查询（把内层的查询结果作为外层查询的查询条件）  
select 字段名列表 from 库.表 where 条件 （select 字段名列

表 from 库.表）；

update userdb.user set linuxsys=93 where uid>=10

and uid<=50;

select name,linuxsys from userdb.user where linuxsys

< (select avg(linuxsys) from userdb.user);

select name,linuxsys from userdb.user where linuxsys

> (select avg(linuxsys) from userdb.user);

select name from userdb.user where name in (select

user from mysql.user where user="root" and

host="localhost");  
\+++++++++++++++++++++++++++++++++++++++++++++  
复制表  
作用： 1 快速创建新表  
2 备份表  
create table 库.表 sql查询命令；  
  
create table userdb.user2 select \* from userdb.user;

create table userdb.user3 select \* from userdb.user

where 1 = 2;

create table userdb.top5user select \* from

userdb.user order by uid desc limit 5;

create database teadb;  
create table teadb.t1 select name,uid,homedir from

userdb.user where uid<=10;  
select \* from teadb.t1;

多表查询  
select 字段名列表 from 表名列表；  
select 字段名列表 from 表名列表 where 条件；

use teadb;  
create table t2 select name,uid,homedir from userdb.user limit 3;

create table t3 select name,uid,shell from userdb.user limit 5;

3 x 5 15  
select \* from t2,t3; 笛卡尔集  
select t2.\* ,t3.name from t2,t3;

select t2.name,t3.name from t2,t3 where t2.name=t3.name;

select t2.name,t2.homedir,t3.name,t3.shell from t2,t3 where t2.name=t3.name;

连接查询  
左连接查询:以左边的表为主显示查询记录  
select 字段名列表 from 表名 left join 表名 on 条件；  
右连接查询:以右边的表为主显示查询记录  
select 字段名列表 from 表名 right join 表名 on 条件；  
use tead;  
create table t4 select name,uid,shell from userdb.user limit 3;  
create table t5 select name,uid,shell from userdb.user limit 5;  
select \* from t4 left join t5 on t4.uid = t5.uid;  
select \* from t4 right join t5 on t4.uid = t5.uid;