运维之mysql篇------3. 数据导入导出 、 表记录基本操作 、 查询及匹配条件 、 多表查询

three day

一。管理表记录（insert select update delete）

1数据导入：把系统文件的内容存储到数据库服务器的表里

mysql> LOAD DATA INFILE "目录名/文件名" INTO

TABLE 库表 FIELDS TERMINATED BY "分隔符"

LINES TERMINNATED BY "\n"；

/etc/psswd  ---> db3.user

1..1创建存储数据的表

create database db3;

use db3;

create table user(

name char(30),

passwd char(1),

uid int,

gid int,

comment varchar(50),

homedir char(35),

shell varchar(40),

index(name)

)engine=innodb;

1.1查看

Mysql>show variables like "secure_file_priv";

[root@localhost~]#ls -ld /var/lib/mysql-files/

1.2把文件拷贝到搜索目录

ls /var/lib/

cp /etc/passwd /var/lib/mysql.files/

ls /var/lib/mysql.logs/

1.3导入数据

mysql> load data infile 

"/var/lib/mysql-file/passwd" 

into table db3.user

fields terminateed by ":"

lines terminated by "\n";

#wc -l /etc/passwd

1.4.查询导入是否成功

desc uer;

select * from user;

1.5修改导入数据搜索文件的路径

mkdir /mydata;chown mysql /myload

#vim /etc/my.cnf

[mysqld]

secuure_file_priv="/myload"

systemctl restart mysqld

mysql -uroot -panc123

mysql> show variable like "secure_file_priv";

mysql> system cp /etc/passwd /mydata/

mysql> ls /mydata;

mysql> delete from db3.user

 

1.6数据导出;把表的记录存储的系统文件里

mysql:show variable like "secure_file_priv"

mysql>sql查询 into outfile "目录名/文件名"；

mysql>sql查询 into outfile "目录名/文件名" fields terminated by "间隔符号" lines termintaed by "间隔符号"

**********************************************************************************

select name ,uid,gid from db3.user into outfile "/mydata/a.txt"

select name ,uid,gid from db3.user limit 3;

select name ,uid,gid from db3.user limit 3into outfile "/mydata/c.txt" fields terminated by "#";

select name ,uid,gid from db3.user limit 3into outfile "/mydata/d.txt";

 

system cat /mydata/c.txt

system cat /mydata/d.txt

select name ,uid,gid from db3.user limit 3into outfile "/mydata/e.txt" lines terminated by "!!!";

system cat /mydata/e.txt

 

2.表记录

2.1 插入一条记录给所有字段赋值

insert into  库.表 values （字段赋值）

mysql>insert into db3.user values (

"jim","x",3001,3001,"this is student","/home/jim","/bin/bash");

 

2.2 插入多条记录给所有段赋值

insert into 库.表 values(字段赋值)，（字段值列表），（字段值列表）;

 

2.3 插入一跳记录给某些字段赋值

desc db3.user

insert into db3.user 

 

2.4. 查询表记录

mysql>alter table db3.user add id int primary key auto_increment first;

mysql> select * from db3.user;

2.5更新表记录字段的值

批量更新

update 库.表 set 字段名=值，字段名="值" where 条件；

update db3.user  set age=21;

select name from db3.user where name is null;

update db3.user set name"zhangsan" where name  is null; 

 

2.6删除表记录；

批量删除；

delete from 库.表；

删除表中指定记录；

delete from 库.表 where 条件；

delete from db3.user where id=3;

delete from db3.user where name in ("root","mysql","bin");

select * from db3.user where id=3;

 

3.查询/条件匹配

3.1

select 字段名列表 from 库.表；

select 字段名列表 from 库.表 where 条件匹配；

select * from db3.user;

select * from db3.user where id=1 ;

select name shell from db3.user where id=1;

Select name as ‘xingming’,age as ‘nianling’ from db3.use where gender=’girl’;

 

3.2.多条件查询

3.2.1.数值比较 > >= < <= = !=

字段名 符号 值

select id,name,uid from db3.user where uid=2000;

select id,name,uid from db3.user where uid<=10;

3.2.2.范围内比较

字段名IN （范围）  在...范围内

select  uid name from db3.userwhere name in ("root","daemon","mysql","rsync");

select id,name from db3.user where name in ("root","daemon","mysql","rsync");

select uid name from db3.userwhere uid in (100,500,1,3000);

字段名 between 起始值 and 结束值    在...与...之间

select id,name,uid,from db3.user where uid between 20 and 30;

select name,shell from db3.user where shell not in ("/bin/bash");

 

3.2.2 ! not 逻辑非 取反

select name from db3.user where name not in ("jim","lucy","root");

and 逻辑与 多个条件时 多个条件都匹配

条件1 and 条件2 or 条件3

select naem

or 多条件时，与某个条件匹配旧就可以

select name,uid from db3.user where name="root" or uid=0 or shell="/sbin/bash"

条件1 or 条件2 or 条件3

select name,uid from db3.user where name="root" uid=0 and shell="/sbin/nologin";

 

3.2.3 字符比较 = ！=

字段名 

select name,uid from db3.user where name="root" or uid=3 or shell="/bin/bash";

IS NULL 匹配空

IS NOT NULL 匹配非空

insert into db3.user(id,name) values(50,null),(51,"null")，(52,"");

 

3.2.4DISTINECT 不显示查询字段的重复值

select distinct shell from db3.user;

select distinct shell from db3.user where shell!="/sbin/nologin";

select distinct shell from db3.user where uid<1000;

 

3.2.5四则运算 + - * / %

alter table db3.user add age tinyint unsigned after name; 

select name,2017-age as s_year from db3.user where name="root";

 

select name ,uid,gid from db3.user where name="jim";

select name ,uid,gid,(uid+gid)/2 pjz from db3.user where name="jim";

select name ,uid,gid,gid+uid zcj,(uid+gid)/2 pjz from db3.user where name="jim";

 

3.3 模糊查询

3.3.1 任意一个字符

%零个或多个字符

select  id,name from user where name like 'a__';

select id,name from  user where name like '__';

select id ,name from user where name like is null;

select id,name from user where name like '%';

select name from user wherer uid=10 having name="zhangsan";

select id,name from user wherre name like "%a%";

在sql查询结果里查找数据

sql查询 having 条件匹配；

select id,name from user where name like "%3%";

select id,name from user where name like "%";

select id,name from user where name like "_%_";

列出name值，以 “以J开头或以Y结尾” 的记录

select name from user where name like "J%" or name like "%Y";

 

3.3.2 正则匹配

where 字段名 regexp “正则表达式”

. ^ $  * [] | 

insert into user(name)values("9yaya"),("y3aya"),("ya7ya"),("yaya"),("yaya5");

把名字包涵数字的用户名寻找出来

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

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

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

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

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

select  name from user where name regexp '^ay$';

select  name from user where name regexp 'a.*y';

select  name from user where name regexp '^a*y$';

select  name from user where name regexp '^r.*t$';

select  name from user where name regexp 'a';

列出name值，以"J"开头或者以"Y"结尾的记录

select name from user where name regexp "^J" or "Y$";

 

3.4 聚集函数

avg(字段名)  平均值

select sum(uid) from user;

select sum(uid) from user where uid <10;

sum（字段名）总数

select sum(uid) from user;

select sum(uid) from user where uid <10;

max（字段名） 最大

select max(uid) from user;

select max(uid) from user where uid<1000;

min（字段名）

select min(gid) from user;

select min(gid) from user where uid<1000;

count（字段名） 数量

select count(id) from user;

select count(name) from user;

select count(*) from user;

select count(name),count(id)  from user where uid<=1000;

 

3.5查询分组/查询排序

3.5.1 sql查询 group by 字段名 [having 条件]；

select  gid from user where uid <=10;

select gid from user where uid>=1000;

select shell from user wher uid>=1000;

select shell from user where uid>=1000 group by shell;

select distinct shell from user group by shell;

 

3.5.2 sql查询 order by 字段名 [asc]; 升序

sql查询 order by 字段名 desc; 降序

select id,name from user where regexp 'a';

select name uid from user where name regexp 'a' and uid is nou null order by uid ;

select name uid from user where name regexp 'a' and uid is nou null order by uid desc ;

 

3.5.3限制显示记录数量

sql查询 limit 数字；显示查询结果的前几行

sql查询 limit 数字1，数字2；

select * from user;

select * from usr limit 1;

select * from user limit 3;

select * from user where shell = "/bin/bash" order by uid desc limit 3;

select * from user where shell ="/bin/bash" oreder by uid desc limit 1;

select * from user limit 1,3;

select * from user limit 9,3;

 

3.6 嵌套查询

select 字段名 from 库.表 where  条件（select 字段名 from 库.表 where 条件）;

把user表中uid字段的值小于此字段平均值的用户名和对应的uid显示出来

select name ,uid from user;

select name,uid from user where uid < avg(uid);

把user表中uid字段的值小于此字段平均值

select name,uid from user where uid <(select avg(uid) from user where uid>1000);

select max(id) from user;

显示表中id号最大的后三行

select * from user where id>=(select  max(id)-2 from user);

显示user表中护照为11111的用户

select name from db3.user where name in (select name from studb.t28 where hzid="11111");

就业表

姓名  工资

JIM  7k

班级表

姓名  年龄

select 姓名 from 班级表 where 姓名 in （select 姓名） from 就业表 where 工资 >=8K);

select count(id) *3 from user;

select count(姓名)*100 from 班级表 where 姓名 in (select 姓名 from 就业表 where 工资>=8K);

select user from mydql.user where user="root";

select name from db3.user where name in (select user from mydql.user where user="root");

 

3.7复制表（作用1 备份表 作用2快速建表）

*源表 字段的key数星不会复制给新表

create table 库.表 sql查询；

mysql>create database db4;

复制所有数据

crtable db4.user2 select * from db3.user;

复制部分数据

crtable db4.user2 select name,uid,shell from db3.user where uid<=10;

复制表结构

crtable db4.uesr2 select * from db3.user where 1=2;

 

3.8多表查询

select 字段名列表 from 表1，表2；迪卡尔集

   2  *  3  =  6

只显示

select 字段名列表 from 表1，表2 where 条件；

create database db4;

create table db4.t1 select name,uid,homedir from db3.user limit 2;

create table db4.t2 select name,uid,shell from db3.user limit 3;

use database db4;

select * from t1;

select * from t2;

select * from t1,t2;

select t1.name,t2.name from t1,t2;

select t1.name,t2.* from t1,t2;

select t1.name,t2.name from t1,t2 where t1.uid = t2.uid;

select t1.name ,t1.homedir,t2.shell from t1,t2 where t1.uid = t2.uid；

左连接查询（以右边的表记录为主线四查询结果）

select 字段名列表 from 表A left join 表B on 条件;

右连接查询（）

select 字段名列表 from 表A right join 表B on 条件；

create table db4.t3 select name,uid,shell from db3.user limit 3;

create table db4.t4 select name,uid,shell from db3.user limit 5;

select * from t3;

select * from t4;

select * from t3 left join t4 on t3.uid = t4.uid and t3.name=t4.name;

select * from t3 right join t4 on t3.uid = t4.uid and t3.name=t4.name;

 

条件匹配：

数值比较 字符比较 范围内比较 模糊匹配 正则匹配

匹配空 匹配非空 逻辑匹配 

**************************************************************************************************************