MySQL数据库管理3-hj
数据导入 ：把系统文件的内容保存到数据库服务器的表里需求：把/etc/passwd文件的内容保存到userdb库user表里。
createdatabaseuserdb;
create tableuserdb.user(
namechar(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>loaddatainfile "/var/lib/mysql-files/passwd"
into table userdb.user fieldsterminated by":"
lines terminated by"\n";
mysql>altertableuserdb.useradd> primary keyauto_increment first;
数据导出：把表记录存储系统文件里。
sql查询命令INTOOUTFILE“目录名/文件名”
FIELDS TERMINATED BY“分隔符”
LINES TERMINATED BY“\n”;
mysql>show variables like"secure_file_priv";
mysql>select* from userdb.user whereid >= < -> where
-> shell is null;
模糊查询
like'表达式'
_任意一个字符
% 零个或多个字符
selectname from userdb.user where name like'____';
selectname from userdb.user where name like'_____%';
selectname from userdb.user where name like'a%';
selectname from userdb.user where name like'a%d';
selectid,name from userdb.user where name like'%';
在查询结果里查询记录having条件；
selectid,name from userdb.user where name like'%'
havingid in (63,64,52,61);
正则匹配
. * [] ^ $
regexp'正则表达式'
select namefrom userdb.user wherenameregexp
't$';
select namefrom userdb.user wherenameregexp
'^a';
insert into userdb.user(id ,name)
values
(65,"plj9"),(66,"pl8j"),(67,"lu2cy"),(68,"3mack");
select namefrom userdb.user wherenameregexp'[0
-9]';
select namefrom userdb.user wherenameregexp'^
';
select namefrom userdb.user wherenameregexp'[0
-9]$';
select name,uidfrom userdb.user where uidregexp
'...';
select name,uidfrom userdb.user where uidregexp
'^...$';
select name,uidfrom userdb.user where name regexp
'^....$';
select name,uidfrom userdb.user where name regexp
'^a.*t$';
四则运算 +- * /
select name,uid,gid,uid+gid as sum from userdb.user
where name in("adm","bin");
alter tableuserdb.useradds_year yeardefault1990
aftername;
select name,s_year,2017-s_year as age from userdb.user
where name="root";
alter tableuserdb.useraddshellscript int(2)default
60 afters_year,add linuxsysint(2)default80after
shellscript;
select name,shellscript,linuxsys ,shellscript+linuxsysas
sum, (shellscript+linuxsys)/2 asavg from userdb.user
where name="root";
逻辑匹配
逻辑与 and多个查询条件时，必须同时成立。
逻辑或 or多个查询条件时，某个查询条件成立就可以
逻辑非 ！取反
selectname from userdb.userwherename="root"
and uid=0andshell="/bin/bash";
selectname from userdb.userwherename="root"or
uid=3orgid="2002";
selectname,uid,gid from userdb.userwhere
name="root"oruid=3orgid="2002";
selectname,uidfromuserdb.user where
name="root"and uid=0 or uid=1;
selectname,uidfromuserdb.user where
name="root"and( uid=0 or uid=1);
select namefrom userdb.user where name!="root";
集聚函数
sum(字段名)求和
avg(字段名)求平均值
max(字段名)求大值
min(字段名)求小值
count(字段名)求个数
selectcount(name)from userdb.userwhere shell!
="/bin/bash";
selectcount(id)from userdb.user ;
selectmin(uid) from userdb.userwhere
shell="/bin/bash";
selectmax(uid) from userdb.userwhere
shell="/bin/bash";
select sum(uid) from userdb.user;
selectavg(linuxsys) from userdb.user;
DISTINCT不显示字段的重复值
selectdistinctshellfrom userdb.db;
selectdistinctshellfrom userdb.user where uid
select> select*fromuserdb.userorder byuiddesclimit5;
select*fromuserdb.user where shell="/bin/bash"
order byuiddesclimit5;
++++++++++++++++++++++++++++++++++++
3 更新记录字段的值
update库.表set字段名=值, 字段名="值";
update库.表set字段名=值, 字段名="值"where条件;
updateuserdb.userset s_year=1980;
updateuserdb.usersetshellscript=100,linuxsys=100
whereid=1;
4 删除记录
delete from 库.表；删除所有记录
delete from 库.表where条件 ;
delete from userdb.user where name is null;
++++++++++++++++++++++++++++++++
复制表（功能 ： 备份表、快速建表）
createtable 库.表 sql查询；
create table userdb.user2select* from userdb.user;
create table userdb.user3selectname,uid,homedir
from userdb.userlimit10;
create table userdb.user4select* from userdb.user
where 1 = 2;
+++++++++++++++++++++++++++++++++
where嵌套查询：把内层的查询结果做为外层查询的查询条件。
sql查询where条件（sql查询）；
selectname,shellscript from userdb.user where
shellscript <(selectavg(shellscript) from userdb.user );
selectname,shellscript from userdb.user where
shellscript <(selectavg(shellscript) from userdb.user )
having name="lucy";
selectuser from mysql.user where user in (select name
from userdb.user where shell="/bin/bash");
多表查询
select 字段名列表 from 表名列表; 笛卡尔集
select 字段名列表 from 表名列表 where 条件；
createtableuserdb.t1select name,uid,shellfrom
userdb.userlimit3;
createtableuserdb.t2select name,gid,homedirfrom
userdb.userlimit5;
select* from t2,t1;
selectt1.name,t2.name from t2,t1;
selectt1.name,t2.* from t2,t1;
select* from t2,t1wheret1.name =t2.name;
select t1.name,t1.uid,t2.gid, t2.name from t2,t1where
t1.name =t2.name;
连接查询
左连接查询 :查询时以左边的表为主显示查询结果。
select字段名列表 from 表Aleft join表Bon条件；
右连接查询:查询时以右边的表为主显示查询结果
select字段名列表 from 表Arightjoin表Bon条件；
createtablet3 select name,uid,shellfrom userdb.user
limit3;
createtablet4 select name,uid,shellfrom userdb.user
limit6;
select*fromt3leftjoint4 ont3.uid = t4.uid;
selectt3.*fromt3leftjoint4 ont3.uid = t4.uid;
select*fromt3 rightjoint4ont3.uid = t4.uid;
select*fromt3 rightjoint4ont3.uid = t4.uid;
页:
[1]