数据导入 ：把系统文件的内容保存到数据库服务器的表里
需求：把/etc/passwd文件的内容保存到userdb库user表里。
create database userdb;
create table userdb.user(
name char(30),
password char(1),
uid int(2),
gid int(2),
comment varchar(100),
homedir varchar(50),
shell varchar(25),
index(name)
);
mysql>show variables like "secure_file_priv";
#cp /etc/passwd /var/lib/mysql-files/
#setenforce 0
导入命令
LOAD DATA INFILE “目录名/文件名”
INTO TABLE 库名.表名
FIELDS TERMINATED BY “分隔符”
LINES TERMINATED BY “\n”;
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)
primary key auto_increment first;
数据导出：把表记录存储系统文件里。
sql查询命令 INTO OUTFILE “目录名/文件名”
FIELDS TERMINATED BY “分隔符”
LINES TERMINATED BY “\n”;
mysql>show variables like "secure_file_priv";
mysql>select * from userdb.user where id<=5 into
outfile "/var/lib/mysql-files/user.txt";
#cat /var/lib/mysql-files/user.txt
++++++++++++++++++++++++++++++++
管理表记录
1 插入新记录
insert into 库.表(字段名列表)values(值列表);
insert into userdb.user values
(50,"bob","x",2002,2002,"this is user
student","/home/bob","/bin/bash");
insert into userdb.user values
(51,"bob","x",2002,2002,"this is user
student","/home/bob","/bin/bash"),
(52,null,"x",2002,2002,"this is user
student","/home/bob","/bin/bash");
insert into userdb.user(name,gid) values
("jerry",2078),("lucy",3009);
insert into userdb.user
(name,password,uid,gid,comment,homedir,shell)values
("alic","x",1009,1009,"teac","/home/alic","/sbin/nologi
n");
2 查询记录
select 字段名列表 from 库.表 where 条件；
select * from userdb.user;
select id,name,uid from userdb.user;
select id,name,uid from userdb.user where id=5;
查询条件:
数值比较 > >= < <= =
字段名 符号 数字
select * from userdb.user where uid=10;
select * from userdb.user where id=5;
select * from userdb.user where gid<=5;
字符比较 = !=
字段名 符号 "字符"
select name from userdb.user where name="daemon";
select name,uid,shell from userdb.user where shell!
="/bin/bash";
范围内查找
between...and.... 在...之间
in （值列表） 在...里
not in （值列表） 不在...里
select name,uid,shellfrom userdb.user where uid
between 0 and 5;
select name from userdb.user where name in
("rsync","daemon","apache","mysql");
select name,uid from userdb.user where uid in
(0,21,2002,13);
select name ,shell from userdb.user where shell not in
("/bin/bash","/sbin/nologin");
匹配空 is null
匹配非空 is not null
insert into userdb.user(id ,name)
values
(61,""),(62,"null"),(63,NULL),(64,null);
select id,name from userdb.user
where
name is null;
select id,name from userdb.user
where
name="null";
select id,name from userdb.user
where
name="";
select id,name from userdb.user
where
name is not null;
mysql> select id,name from userdb.user
-> where
-> shell is null;
模糊查询
like '表达式'
_ 任意一个字符
% 零个或多个字符
select name from userdb.user where name like '____';
select name from userdb.user where name like '_____%';
select name from userdb.user where name like 'a%';
select name from userdb.user where name like 'a%d';
select id,name from userdb.user where name like '%';
在查询结果里查询记录 having 条件；
select id,name from userdb.user where name like '%'
having id in (63,64,52,61);
正则匹配
. * [ ] ^ $
regexp '正则表达式'
select name from userdb.user where name regexp
't$';
select name from userdb.user where name regexp
'^a';
insert into userdb.user(id ,name)
values
(65,"plj9"),(66,"pl8j"),(67,"lu2cy"),(68,"3mack");
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,uid from userdb.user where uid regexp
'...';
select name,uid from userdb.user where uid regexp
'^...$';
select name,uid from userdb.user where name regexp
'^....$';
select name,uid from userdb.user where name regexp
'^a.*t$';
四则运算 + - * /
select name,uid,gid,uid+gid as sum from userdb.user
where name in ("adm","bin");
alter table userdb.user add s_year year default 1990
after name;
select name,s_year,2017-s_year as age from userdb.user
where name="root";
alter table userdb.user add shellscript int(2) default
60 after s_year ,add linuxsys int(2) default 80 after
shellscript;
select name,shellscript,linuxsys ,shellscript+linuxsys as
sum , (shellscript+linuxsys)/2 as avg from userdb.user
where name="root";
逻辑匹配
逻辑与 and 多个查询条件时，必须同时成立。
逻辑或 or 多个查询条件时，某个查询条件成立就可以
逻辑非 ！ 取反
select name from userdb.user where name="root"
and uid=0 and shell="/bin/bash";
select name from userdb.user where name="root" or
uid=3 or gid="2002";
select name,uid,gid from userdb.user where
name="root" or uid=3 or gid="2002";
select name,uid from userdb.user where
name="root" and uid=0 or uid=1;
select name,uid from userdb.user where
name="root" and ( uid=0 or uid=1);
select name from userdb.user where name!="root";
集聚函数
sum(字段名) 求和
avg(字段名) 求平均值
max(字段名) 求大值
min(字段名) 求小值
count(字段名) 求个数
select count(name) from userdb.user where shell!
="/bin/bash";
select count(id) from userdb.user ;
select min(uid) from userdb.user where
shell="/bin/bash";
select max(uid) from userdb.user where
shell="/bin/bash";
select sum(uid) from userdb.user;
select avg(linuxsys) from userdb.user;
DISTINCT 不显示字段的重复值
select distinct shell from userdb.db;
select distinct shell from userdb.user where uid<=10;
查询分组
sql查询命令 group by 字段名；
select shell from userdb.user where uid<1000 group
by shell;
查询排序
sql查询命令 order by 字段名 升序/降列；
asc / desc
select name,gid from userdb.user where
shell="/bin/bash";
select name,gid from userdb.user where shell!
="/bin/bash" order by gid desc;
select 姓名，部门，工资 from 员工信息表 where 性别="女"
and 工资 >=20000 order by 工资 desc;
限制显示查询记录数
sql查询命令 limit 数字
sql查询命令 limit 数字1，数字2
select id,name,shell from userdb.user limit 1 ;
select id,name,shell from userdb.user limit 2,2 ;
select * from userdb.user order by uid desc limit 5;
select * from userdb.user where shell="/bin/bash"
order by uid desc limit 5;
++++++++++++++++++++++++++++++++++++
3 更新记录字段的值
update 库.表 set 字段名=值, 字段名="值";
update 库.表 set 字段名=值, 字段名="值" where 条件;
update userdb.user set s_year=1980;
update userdb.user set shellscript=100,linuxsys=100
where id=1;
4 删除记录
delete from 库.表；删除所有记录
delete from 库.表 where 条件 ;
delete from userdb.user where name is null;
++++++++++++++++++++++++++++++++
复制表（功能 ： 备份表 、快速建表）
create table 库.表 sql查询；
create table userdb.user2 select * from userdb.user;
create table userdb.user3 select name,uid,homedir
from userdb.user limit 10;
create table userdb.user4 select * from userdb.user
where 1 = 2;
+++++++++++++++++++++++++++++++++
where嵌套查询：把内层的查询结果做为外层查询的查询条件。
sql查询 where 条件 （sql查询）；
select name,shellscript from userdb.user where
shellscript < (select avg(shellscript) from userdb.user );
select name,shellscript from userdb.user where
shellscript < (select avg(shellscript) from userdb.user )
having name="lucy";
select user from mysql.user where user in (select name
from userdb.user where shell="/bin/bash");
多表查询
select 字段名列表 from 表名列表; 笛卡尔集
select 字段名列表 from 表名列表 where 条件；
create table userdb.t1 select name,uid,shell from
userdb.user limit 3;
create table userdb.t2 select name,gid,homedir from
userdb.user limit 5;
select * from t2,t1;
select t1.name,t2.name from t2,t1;
select t1.name,t2.* from t2,t1;
select * from t2,t1 where t1.name = t2.name;
select t1.name,t1.uid,t2.gid, t2.name from t2,t1 where
t1.name = t2.name;
连接查询
左连接查询 :查询时以左边的表为主显示查询结果。
select 字段名列表 from 表A left join 表B on 条件；
右连接查询:查询时以右边的表为主显示查询结果
select 字段名列表 from 表A right join 表B on 条件；
create table t3 select name,uid,shell from userdb.user
limit 3;
create table t4 select name,uid,shell from userdb.user
limit 6;
select * from t3 left join t4 on t3.uid = t4.uid;
select t3.* from t3 left join t4 on t3.uid = t4.uid;
select * from t3 right join t4 on t3.uid = t4.uid;
select * from t3 right join t4 on t3.uid = t4.uid;