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;
条件匹配：
数值比较 字符比较 范围内比较 模糊匹配 正则匹配
匹配空 匹配非空 逻辑匹配
**************************************************************************************************************
