複制表：
作用： 1: 快速建立新表
2: 備份表
格式：create table 庫.表 sql查詢指令；
源表的索引屬性不會複制給新表
mysql>create database userdb;
mysql> create table userdb.user2 select * from userdb.user; //所有表結構和資料
mysql> create database teadb;
mysql> create table teadb.user select name,uid from userdb.user where shell="/bin/bash"; //部分表結構和資料
快速複制表結構
mysql> create table teadb.user2 select *from userdb.user where 1=2;
1=2 #條件不成立，所有查詢結果為空表。
單表查詢
查詢表中所有記錄所有字段的值
格式：select * from 庫.表 ；
查詢表中所有記錄指定字段的值
格式：select 字段名清單 from 庫.表 ；
查詢表中符合條件記錄指定字段的值
格式：select 字段名清單 from 庫.表 where 條件；
查詢條件的表示方式
數值比較 > >= < <= = !=
mysql> select * from userdb.user where id=23;
mysql> select * from userdb.user where uid>500;
字元比較 = ！=
字段名 符号 “字元串”
mysql> select name from userdb.user where name="apache";
mysql>select name from userdb.user where name="apache";
mysql>select name from userdb.user where shell="/bin/bash";
mysql>select name,shell,uid from userdb.user where shell="/bin/bash";
mysql>select name from userdb.user where name!="root";
範圍内查找
in (值清單) 在...裡
not in (值清單) 不在...裡 (數值 、字元)
between..數字1..and...數字2 在...之間
mysql> select name,homedir,uid from userdb.user where uid between 20 and 30;
mysql> select name,homedir,uid from userdb.user where uid in (12,9,500,513);
mysql> select name,shell from userdb.user where shell not in ("/bin/bash","/sbin/nologin");
邏輯比對：多個查詢條件
邏輯與 and 多個查詢條件必須同時成立
邏輯或 or 多個查詢條件某一個條件成立就可以
邏輯非 ！ 取反
mysql>select name from userdb.user where name="apache" and uid=1200 and shell="/bin/bash";
mysql>select name from userdb.user where name="apache" or uid=2 or shell="/bin/bash";
mysql>select name,uid,shell from userdb.user where name="apache" or uid=2 or shell="/bin/bash";
mysql>select name,uid from userdb.user where (name="root" or name="bin" and uid=1;
mysql>select name,uid from userdb.user where (name="root" and name="bin" or uid=1;
mysql>select name,uid from userdb.user where (name="root" or name="bin" ) and uid=2;
mysql>select name,uid from userdb.user where (name="root" or name="bin" ) and uid=1;
distinct 不顯示字段的重複的值（去掉字段的重複的值）單獨使用
mysql>select distinct shell from userdb.user;
mysql>select distinct shell from userdb.user where uid<1000;
is null 比對空
is not null 比對非空
mysql>insert into userdb.user(id ,name)values(66,NULL),(67,null),(68,"null"),(69,"");
mysql>select id , name from userdb.user where name is null;
mysql>select id , name from userdb.user where name is not null;
mysql>select id , name from userdb.user where name="null";
mysql>select id , name from userdb.user where name="";
正則比對
regexp '正規表達式'
^ $ . * []
Where 字段名 regrexp ‘正規表達式’；
mysql>insert into userdb.user(id , name)values(71,"yaya3"),(72,"9yaya"),(73,"ya7ya"),(74,"yay6a");
mysql>select name from userdb.user where name regexp '[0-9]';
mysql>select name from userdb.user where name regexp '^[0-9]';
mysql>select name from userdb.user where name regexp '[0-9]$';
mysql>select name from userdb.user where name regexp '.....';
mysql>select name from userdb.user where name regexp '^.....$';
四則運算 + - * / %
mysql> alter table userdb.user add age tinyint(2) unsigned zerofill default 21 after name;
mysql> select name,age,2017-age from userdb.user where name="root";
mysql> select name,age,2017-age as s_tear from userdb.user;
as :命名
例題：減法運算
mysql>select name,linuxsys,linuxser from userdb.user;
mysql>select name,linuxsys,linuxser from userdb.user where name="root";
mysql>select name,linuxsys,linuxser,linuxsys+linuxser as zcjfrom userdb.user where name="root";
mysql>select name,linuxsys,linuxser,linuxsys+linuxser as zcj,(linuxsys+linuxser)/2 as pjf from userdb.user where name="root";
mysql>select name,age from userdb.user where name="root";
mysql>select name,age,2017-age as s_year from userdb.user where name="root";
模糊比對
字段 like ‘表達式’
% 零個或多個字元
_ 任意單個字元
mysql> select name from userdb.user where name like 'a%';
mysql> select name from userdb.user where name like '____';
mysql> select name from userdb.user where name like '_%_';
#在sql查詢結果裡查找資料
mysql> select name from userdb.user where name like 'a%' having id=50；
# 名字至少是兩個字元
mysql> select name from userdb.user where name like 'a%t';
# a 開頭t結尾的内容，中間是任意字元。
select name,uid from userdb.user where name like '%a%';
select name,uid from userdb.user where name like 'a%';
select id,name,uid from userdb.user where name like '%';
select name,id from userdb.user where name like '%_%' and name="";
select name,uid from userdb.user where name like '____' and uid>=1000；
聚集函數（對資料做統計的函數）
sum(字段名) 求和
avg(字段名) 平均值
min(字段名) 最小值
max(字段名) 最大值
count(字段名) 個數
備注：使用者名是空的統計個數是不出來，因為空是什麼都沒有是以不算個數。
mysql> select max(uid) from userdb.user;
mysql> select max(uid) from userdb.user where shell="/bin/bash";
mysql> select sum(linuxsys) ,avg(linuxsys) from userdb.user;
mysql> select sum(linuxsys) ,avg(linuxsys) from userdb.user where uid<=10;
mysql>select min(uid) from userdb.user where shell="/bin/bash";
mysql>select max(uid) from userdb.user where shell="/bin/bash";
mysql>select count(id) ,count(name) from userdb.user;
mysql>select count(name) from userdb.user where shell="/bin/bash";
查詢排序
格式：sql查詢 order by 字段名 排序方式；
排序方式：
升序asc(從小到大排序)
降序 desc(從大到小排序)
預設asc升序(從小到大排序)
mysql> select name,uid from userdb.user where uid<1000 order by uid;
mysql> select name,uid from userdb.user where uid<1000 order by uid desc;
查詢分組（去重）
格式：sql查詢 group by 字段名；
mysql> select shell from userdb.user where name like "____" group by shell;
mysql> select shell from userdb.user where uid<=10 group by shell;
mysql> select 部分 from 員工資訊表where 性别="女" and 年齡<30 and 工資>20000 group by部門；
mysql>select shell from userdb.user where uid>=10 and uid<=40 ;
mysql>select shell from userdb.user where uid>=10 and uid<=40 group by shell;
mysql>select gid from userdb.user group by gid;
mysql>select shell from userdb.user group by shell;
mysql>select gid from userdb.user group by gid having gid=1;
限制顯示查詢記錄行數 （預設顯示全部）
sql查詢 limit 數字；
sql查詢 limit 數字1 數字2；
數字1 ：是從第幾行顯示
數字2 ：顯示到第幾行。
limit 數字 顯示查詢結果的前幾行
limit 起始行,總行數 起始行的編号從零開始
例題：
mysql> select id,name,uid from userdb.user where uid>100 order by uid desc limit 1;
mysql> select id,name,uid from userdb.user where uid>100 order by uid desc limit 2;
mysql> select * from userdb.user limit 1,3; #顯示第2行到第3行。
where嵌套查詢
把内層的查詢結果作為外層查詢的查詢條件
格式：select 字段名清單 from 庫.表 where 條件 （select 字段名清單 from 庫.表）；
mysql> select avg(uid) from userdb.user;
mysql> select name,uid from userdb.user where uid< (select avg(uid) from userdb.user);
mysql> select count(uid) from userdb.user where uid< (select avg(uid) from userdb.user);
在不同表裡查找内容：
mysql> select name from userdb.user where name in (select name from teadb.user where uid in(1008,800,13));
In #在teadb.user表裡 (1008,800,13)範圍内。
mysql> select name from userdb.user where name in (select user from mysql.user where name="root");
多表查詢
select 字段名清單 from 表名清單 ； //笛卡爾集
select 字段名清單 from 表名清單 where 條件； //隻顯示與條目比對的值
mysql> create database db4;
mysql> create table db4.t1 select name,uid,shell from userdb.user limit 2;
mysql> create table db4.t2 select name,uid,homedir from userdb.user limit 4;
mysql> use db4;
mysql> select t11.*,t22.name t22.homedir from t11,t22;
mysql> select t11.name,t22.name from t11,t22;
連接配接查詢
左連接配接查詢:以左邊的表為主顯示查詢記錄
select 字段名清單 from 表名 left join 表名 on 條件；
右連接配接查詢:以右邊的表為主顯示查詢記錄
select 字段名清單 from 表名 right join 表名 on 條件；
mysql>select * from t3 left join t4 on t3.uid = t4.uid ;
mysql>select * from t3 left join t4 on t3.name = t4.name and t3.uid=t4.uid and t3.shell = t4.shell;
mysql> select t3.name ,t4.name from t3 left join t4 on t3.uid = t4.uid ;
mysql> select t3.name ,t4.name from t3 right join t4 on t3.uid = t4.uid ;
update修改表記錄（修改記錄字段的值）
批量修改
update 庫.表 set 字段名="值"，字段名=”值”；
修改與條件比對的記錄字段的值；
update 庫.表 set 字段名="值"，字段名=值 where 條件；
mysql> update userdb.user set age=29,comment="system user";
#把user表中的年齡都改為29，把描述資訊改為system user.
mysql> update userdb.user set age=18 where id<=10;
# 把user表中的前10行的年齡改為18
mysql> select id,name from userdb.user where name in (null,"");
mysql> update userdb.user set name="tom" where name in("null", "")
delete删除表記錄(以行為機關删除)
删除表的所有記錄
mysql>delete from 庫.表；
删除表中與條件比對的記錄
mysql>delete from 庫.表 where 條件；
删除前先查一下，避免出現錯誤。
mysql>delete from userdb.user where shell is null;
mysql> delete from db4.t4;
mysql> delete from userdb.user where id<=3;
mysql> delete from userdb.user where shell="" or shell is null;
修改表名
alter table 源表名 rname [to] 新表名；