数据库：数据导入/数据导出

一、数据导入： 把系统文件的内容存储到数据库的表里

mysql> load data infile "目录/文件名" into table 库.表名 fields

terminated by "字段间隔符号" lines terminated by "\n";

用户名：密码占位符 ： uid ： gid ： 描述信息 ： 家目录 ： shell

create database studb;

create table studb.user(

name varchar(50),

password char(1),

uid int(2),

gid int(2),

comment varchar(100),

homedir char(100),

shell char(25),

index(name)

);

desc studb.user;

select * from studb.user;

alter table studb.user add id int(2) primary key auto_increment

first;

二、数据导出 : 把表记录存储到系统文件里。

mysql> sql查询 into outfile "目录/文件名";

mysql> sql查询 into outfile "目录/文件名" fields terminated by "

符号" lines terminated by "符号";

三、管理表记录 studb.user

增

insert into 库.表 values （字段值列表）；

insert into 库.表 values （字段值列表），（字段值列表）；

insert into 库.表（字段名列表） values （字段值列表）；

insert into 库.表（字段名列表） values （字段值列表），（字段值列表

）；

查

select 字段名列表 from 库.表;

select 字段名列表 from 库.表 where 条件;

select from user;

select from user where name="mysql";

条件匹配的表示方式：

数值比较 > >= < <= = !=

字段名 符号 值

select name from user where uid=15;

select * from user where id=10;

字符比较 = !=

字段名 符号 "值"

select name,shell from user where shell!="/bin/bash";

select id,name from user where name="apache";

范围内匹配

字段名 between 值1 and 值2 在...之间

select * from user where id between 10 and 15;

select name from user where uid between 1 and 10;

字段名 in （值列表） 在...里

select id,name from user where name in ("apache","root","bob");

select id,name,uid from user where uid in (10,15,9,12);

字段名 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

匹配非空 is not null

字段名 is not null

select id from user where name is null;

select id,name,shell from user where shell is not null;

insert into user(name)values(""),("null"),(null);

select id,name from user where name="";

select id,name from user where name="null";

distinct 不显示重复值

distinct 字段名

select distinct shell from user;

select distinct shell from user where uid<=10;

逻辑匹配 ： 有多个条件

逻辑与 and 多个条件必须都成立

逻辑或 or 多个条件有一个条件成立即可

逻辑非 ! 取反

条件1 && 条件2 && 条件n

条件1 || 条件2 || 条件n

select name from user where name="zhangsan" and

uid=500 and shell="/bin/bash";

select name from user where name="zhangsan" or uid=500

or shell="/bin/bash";

数学运算操作 + - * / %

字段类型必须是数值类型

select 字段名 符号 字段名 from 表 where 条件；

select uid+gid from user where name="root";

select name,uid,gid,uid+gid he from user;

select name,uid,gid,uid+gid he from user where name="bin";

alter table user add age tinyint(2) unsigned default 21 after

name;

select name,age,2017-age old from user where name="bob";

select name,uid,gid,(uid+gid)/2 pjz from user where name="bin";

模糊查询 like

where 字段名 like '表达式';

任意一个字符

% 0个或多个字符

select name from user where name like ' ';

select name,uid from user where name like ' _' and uid<=10;

select name from user where name like 'a%';

select name from user where name like '%a%';

select id,name from user where name in ("","null") or name is

null;

select id,name from user where name like '%_%';

select id,name from user where name like 'a';

select id,name from user where name like 'j%' or "%y";

正则匹配

where 字段名 regexp '正则表达式';

. ^ $ [ ] * |

insert into user(name) values("bob9"),("j7im"),("1yaya");

select name from user where name regexp '[0-9]';

select name from user where name regexp '^[0-9]';

select name,uid from user where uid regexp '..';

select name,uid from user where uid regexp '^..$';

select name,uid from user where name regexp 'a.t';

select name,uid from user where name regexp '^a.t';

select name,uid from user where name regexp '^r|t$'

统计函数 字段得是数值类型。

求和 求平均值 求最大值 最小值 统计个数

sum(字段名) avg(字段名) max(字段名) min(字段名) count(字段名)

select count(name) from user where shell="/bin/bash";

select max(uid) from user;

select min(gid) from user;

select avg(age) from user;

select sum(gid) from user;

select sum(gid) , count(name) from user;

查询排序 sql查询 order by 字段名 asc/desc;

select name,uid from user where uid between 10 and 50 ;

select name,uid from user where uid between 10 and 50 order

by uid desc;

查询分组 sql查询 group by 字段名;

select shell user where uid between 10 and 50 ;

select shell from user where uid between 10 and 50 group by

shell;

select shell from user group by shell;

限制查询显示行数 limit

sql查询 limit 数字； 显示查询结果的前几行

sql查询 limit 数字1 ， 数字2； 设置显示行的范围

select from user;

select from user limit 2 ;

select from user limit 2 ,2 ;

select from user order by uid desc;

select from user order by uid desc limit 5;

select from user order by uid desc limit 1;

单表查询

++++++++++++++++++++++++++++++++++++++

where嵌套查询 ：把内层的查询结果作为外层查询的查询条件。

select 字段名列表 from 表名 where 条件 ( select 字段名列表 from

表名 where 条件 );

显示用户名和uid uid字段的值 大于 uid字段的平均值。

select name,uid from user where uid > ( select avg(uid) from

user );

select name from user where name not in (select user from

mysql.user );

select name from user where name in (select user from

mysql.user where user="zhangsan");

mysql.user where user="zhangsan";);

+++++++++++++++++++++

复制表： 作用： 快速建表 、 备份表

create table 库.表 sql查询；

create database dbbak;

create table dbbak.user2 select from studb.user;

create table dbbak.user3 select from studb.user where 1 = 2;

create table dbbak.user4 select name,uid from studb.user limit

3;

多表查询

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 = t2.uid and

t1.name=t2.name;

++++++++++++++++++++++

连接查询

左连接查询

select 字段名列表 from 表a left join 表b on 条件；

右连接查询

select 字段名列表 from 表a right join 表b on 条件；

create table studb.t3 select name,uid,shell from user limit 3;

create table studb.t4 select name,uid,shell from user limit 5;

select from t3; select from t4;

select from t3 left join t4 on t3.uid=t4.uid;

select from t3 right join t4 on t3.uid=t4.uid;

++++++++++++++++++++++++++++++++++

改

数值比较 字符比较 范围内匹配 匹配空 匹配非空 逻辑匹配

正则匹配 模糊查询

去掉字段重复值 数学计算 统计函数 分组 排序 限制行数

删