一、資料導入： 把系統檔案的内容存儲到資料庫的表裡
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;
++++++++++++++++++++++++++++++++++
改
數值比較 字元比較 範圍内比對 比對空 比對非空 邏輯比對
正則比對 模糊查詢
去掉字段重複值 數學計算 統計函數 分組 排序 限制行數
删
![MySQL系列：7 事務和隔離--概念篇[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)