数据导入、导出、管理表记录、
数据导入: 把系统文件的内容存储到数据库服务器的表里
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
5;
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;
