數(shù)據(jù)導(dǎo)入導(dǎo)出 、 表記錄基本操作 、 查詢及匹配條件 、 多表查詢 、

數(shù)據(jù)管理:(管理表記錄)
數(shù)據(jù)導(dǎo)入:把系統(tǒng)文件的內(nèi)容存儲(chǔ)到數(shù)據(jù)庫(kù)的表里.
mysql> create table uesr(
-> name varchar(50),
-> mima char(1),
-> UID int(2),
-> GID int(2),
-> ms varchar(100),
-> honame char(100),
-> jsq char(25),
-> index(name)
-> );

查看默認(rèn)使用目錄及目錄是否存在: show variables inke "secure_file_priv"

格式: load data infile "系統(tǒng)目錄/文件名" into table 庫(kù)名.表名 fields treminated by "字段間隔符號(hào)" lines treminated by "\n" 數(shù)據(jù)導(dǎo)入
例: load data infile "/var/lib/mysql-files/passwd" into table uesr fields terminated by ":" lines terminated by "\n" 格式 "\n";
系統(tǒng)目錄/文件名 表名 指定分隔符 "字段間隔符號(hào)" 列指定的分隔符是"\n"
添加一個(gè)行號(hào)方便后續(xù)工作的查找:alter table uesr add id int(2) primary key auto_increment first;
表名 添加 ID 類型
注意事項(xiàng):
字段分隔符要與文件內(nèi)的一致
指定導(dǎo)入文件的絕對(duì)路徑
導(dǎo)入數(shù)據(jù)的表字段類型要與文件字段匹配
禁用SElinux
數(shù)據(jù)導(dǎo)出:把表記錄存儲(chǔ)到系統(tǒng)文件里.
select 查詢 into outfile "目錄/文件名"; [fields terminated by "符號(hào)" lines terminated by "符號(hào)"];
select id,name from uesr into outfile "/var/lib/mysql-files/uesr1.txt"

格式:

注意事項(xiàng):導(dǎo)出的內(nèi)容是由SQL查詢語(yǔ)句

limit 選取第5行
lines terminated by "\n" 行分隔符
fields terminated by "分隔符" 字段分隔符

管理表記錄:
增: insert into 庫(kù).表 values(字段值列表); #(一次給一個(gè)字段賦值)
insert into 庫(kù).表 (字段值列表) values (字段值列表); ##(指定字段賦值)
查:
select 字段名列表 from 庫(kù).表 wher 條件 #全部查詢.并且條件匹配 寫(xiě)wher 條件的話就是匹配表中什么條件里的.
select 字段名列表 (列) from 庫(kù).表 where 條件(行) ; //指定查找
條件匹配的表示方式:
數(shù)值比較 > >= < <= = !=
字段名 符號(hào) 值
例: select name from uesr where uid=15; # 查多個(gè)時(shí)可用逗號(hào)分隔.

字符比較: = !=
字段名 符號(hào) "值"
select name,shell from uesr where shell!="/bin/bash";
select name from uesr where name="apache";
范圍內(nèi)匹配
字段名 in (值列表) 在.....里
select id,name from user where name in("apache","root","bob");
select id,name,uid from uesr where uid in (10,15,9,12);
字段名 between 值1 and 值2 在....之間
select from user where id between 10 and 15;
select from user where uid between 1 and 10;
字段名 not in (值列表) 不在 .......里
select name from user where uid not in(0,1,5,7)
select from user where name not in("root","mysql","bin")
匹配空 is null
字段名 is null
select id from uesr where name is null;
匹配非空 is not null
字段名 is not null
select id,name.shell from uesr where shell is not null;
select id,name from user where name="";
select id,name from user where name="null";
distinct不顯示重賦值
distinct 字段名
select destinct shell from user;
關(guān)鍵字
select distinct shell from user where uid<=10;
邏輯匹配: 有多個(gè)條件匹配
邏輯與 && and多個(gè)條件必須都成立
邏輯或 || or多個(gè)條件有一個(gè)條件成立即可.
邏輯非 ! 取反.
條件1 &&條件2 條件n 可以用&&連接(三個(gè)條件都有)
select name from uesr where name ="zhangsan"and uid=500 and shell="/bin/bash";
表名 列名 內(nèi)容 與 列名條件 與 列名條件
select name from uesr where name ="zhangsan" or uid=500 or shell="/bin/bash";
運(yùn)算操作: + - / %
select 字段名 符號(hào) 字段名 from 表名 where 條件;
例:
select UID + GID from uesr where name="root";
select UID,GID,UID + GID he from uesr where name="root";
模糊查詢: like
where 字段名 like '表達(dá)式'; ###一般情況下表達(dá)式可以用
任意一個(gè)字符
% 0個(gè)或者多個(gè)字符

select name from uesr where name like '___' and UID<=10;
select name from uesr where name like 'a%'; ##用戶名里有a的
select id,name from uesr where name like '%%'; ##包含任意字符的
select name from uesr where name in ("","null") or name is null; #### 匹配空,或者空的名,
正則匹配
where 字段名 regexp '正則表達(dá)式';
. ^ $ [ ]
insert into uesr(name)values("bob9"),("j7im"),("1yaya");
select name from uesr where name regexp '[0-9]$'; 數(shù)字結(jié)尾的名字
select name from uesr where name regexp '^[0-9]'; 數(shù)字在前的名字
select name from uesr where name regexp '..'; 任意字符和數(shù)字的
select name,uid from uesr where uid regexp '^..$';
select name,uid from user where name regexp 'a.t'
select * from uesr where name regexp '^r|t$'; 查出r開(kāi)頭t結(jié)尾的
統(tǒng)計(jì)函數(shù):
求和, 求平均值, 求最大值, 求最小值 統(tǒng)計(jì)個(gè)數(shù)
sum(字段名) avg(字段名) max(字段名) min(字段名) count(字段名)

select count(name) from uesr where jsq="/bin/bash"; #統(tǒng)計(jì)
select max(uid) from uesr; #求最大值
select min(gid) from uesr; #求最小值
select avg(age) from uesr; #平均值
select sum(gid) from uesr; #求和
select sum(uid),count(name) from uesr; ##求和統(tǒng)計(jì)
查詢排序 order by
sql查詢 order by 字段名 desc;(asc/desc) #默認(rèn)升序

select name,uid from uesr where uid between 10 and 50 order by uid desc; 降序
select name,uid from uesr where uid between 10 and 50; 默認(rèn)升序

查詢分組 group by
sql查詢 group by 字段名

select shell from user where uid between 10 and 50; 10到50行不顯示重復(fù)的組,
select 字段名 from 表名 where 字段名 between 10 and 50 group by 字段名;

查詢限制行數(shù) limit
sql查詢 limit 數(shù)字; 顯示查詢結(jié)果的前幾行
select * from uesr limit 1 顯示查詢結(jié)果的第一行

sql查詢 limit 數(shù)字1 , 數(shù)字2 ; 設(shè)置顯示行的范圍

select from user; 顯示所有
select from user limit 2; 顯示行
select * from user limit 2,5; 顯示行的范圍

select * from uesr order by uid desc limit 5; #uid用戶最大的前五個(gè)顯示出來(lái)
改:
條件匹配的表示方式:
數(shù)值比較 字符比較 范圍內(nèi)匹配 匹配空 匹配非空 邏輯匹配
正則匹配 模糊查詢
去掉字段重賦值 數(shù)字計(jì)算 統(tǒng)計(jì)函數(shù) 分組 排序 限制行數(shù).
單表查詢:select 字段名列表 from 庫(kù).表 wher 條件
where嵌套查詢:把內(nèi)存的查詢結(jié)果作為外層查詢的查詢條件
格式:select 字段名列表 from 表名 where 條件 ( );

#顯示用戶名和uid號(hào) uid字段的值要大于uid字段的平均值

select name,uid from uesr where uid > (select avg(uid) from uesr); ##同庫(kù)同表
select name from uesr where name not in (select user from mysql.user); ###不同庫(kù)不同表
select name from uesr where name in (select user from mysql.user where user="zhangsan");
select name from user where name not in (select user from mysql.user where user="zhangsan";);
復(fù)制表;作用 快速建表 備份表
格式:create table 庫(kù).表 sql查詢;

create database dbbak; 快速創(chuàng)建庫(kù)
create table dbbak.uesr2 select from dc.uesr; 在dbbak這個(gè)庫(kù)里克隆一個(gè)dc庫(kù)里的表
create database dbbak.uesr3 select from dc.uesr where 1=2;
create database dbbak.uesr3 select name,uid from dc.uesr limit 3; ##備份dc.uesr表里的前三行
庫(kù)名.表名 列表名,列表名 庫(kù),表名 前三行
多表查詢:
select 字段名列表 from 表名列表; 迪卡爾集
select 字段名列表 from 表名列表 where 條件
create table studb.t1 select name,uid shell from user limit 3;
create table studb.t2 select name,uid,homedir from user limit 4;
show tables;
select from t1; select from t2;
select from t1,t2 where t1.uid = t2.uid and t1.name=t2.name;
select t1.,t2/homedir from t1,t2 where t1.uid
連接查詢:
左連接查詢 : select 字段名列表 from 表A left join 表B on 條件;
select * from t3 left join t4 on t3.UID=t4.UID;
右連接查詢 : select 字段名列表 from 表A right join 表B on 條件;