复制表：
作用： 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;
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] 新表名；