一、数据导入： 把系统文件的内容存储到数据库的表里 mysql> load data infile "目录/文件名" into table 库.表名 fields
terminated by "字段间隔符号" lines terminated by "\n";
/etc/passwd studb.user
用户名：密码占位符 ： 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");
select name from user where name not in (select user from
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; show tables; 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; ++++++++++++++++++++++++++++++++++ 改 条件匹配的表示方式： 数值比较 字符比较 范围内匹配 匹配空 匹配非空 逻辑匹配 正则匹配 模糊查询
去掉字段重复值 数学计算 统计函数 分组 排序 限制行数
删 条件匹配的表示方式：
