mysql必知必会

sql 语句:
1.数据定义类语句(DDL):
    create drop alter
2.数据操纵类语句(DML)
    增删查改 insert delete select update
3.数据控制类语句(DCL)
    grant revoke
这些语句都要以 ; 结尾 !

 

show databases;//查看系统中都存在哪些数据库
    information_schma    //系统预留的数据库
    performance_schma    //系统预留的数据库
    mysql                //系统预留的数据库,存储了 数据库、表、列、用户、权限 等信息
    sys                    //系统预留的数据库
create database db_name;    //创建数据库.若已经存在,则sql会报错
drop database db_name;        //删除一个数据库
use db_name;            //对某个数据库进行增删查改前,必须要先打开此数据库

show tables;        //显示某数据库下已经存在了的所有数据表,关于 show 的其他用法可以 help show;
desc table_name;    //查看某个表的定义,等价于 describe table_name; 或 show columns from table_name;
                    //返回 表的每一个字段的 "名字 类型 可否为null 键信息 默认值 其他信息(eg.auto_increment)"

create table if not exists `table_name` (    //一般表名与字段名都使用反引号, 但貌似也有啥也不用的?
    `id` int unsigned not null auto_increment,    //not null 表示在插入或更新时 该列必须有值,""也算是有值的!
    `name` char(40) not null,                    //允许null值的列允许在插入该行时不给出该列的值
    `ip address` bigint(20) default null,        //default 指定默认值,指定 not null 时候貌似可以指定一个 default 值
    `introduction` varchar(200) default null,
    `age` int not null,
    `grade` decimal(8,2) not null,    //十进制数字,整数部分占8位,小数部分占2位,其实就是一个浮点数了
    `born_date` DATE,
    primary key ( `id` )    //primary key(`k1`, `k2`)    //这是使用组合键
) engine=InnoDB default charset=utf8;
drop table table_name;
//每个表只可以有一个 auto_increment 列. auto_increment 列貌似可以 insert 的时候不指定
//主键使用单个列,则此列值必须唯一. 若主键使用多个列的组合,则这些列的组合值必须唯一

存储少量字符串使用 char 或 varchar
存储大文本可使用 text(只能保存字符数据) & blob(可保存二进制数据)
tinytext(256B),text(64KB),mediumtext(16MB),longtext(4GB),
tinyblob(256B),blob(64KB),mediumblob(16MB),longblob(4GB),

create table `usr_info` (
    `id` int(11) unsigned not null auto_increment comment '主键',
    `name` varchar(20) default null comment '姓名',
    `mobile` char(11) default null comment '手机号码',        //手机号码都是11位,用定长字符串最适合
    `address` varchar(100) default null comment '家庭住址',    //居住地址长度可变,所以用 varchar
    `description` text comment '个人简介',                    //不知道最大长度时,用 text,text 不允许有默认值,插入方法和 varchar 一样
    `sex` char(1) default null comment '性别',                // 0 男 1 女, 或用 tinyint bool
    `age` tinyint(1) default null comment '年龄',             //取值范围(-128 127)
    `idno` char(18) default null comment '身份证号码'        //定长字符串
    `image` blob comment '照片'
    `recordtime` datetime comment '记录时间'                //
    primary key (`id`),
    unique key `person` (`mobile`),    //约束,手机号码要唯一
    unique key `idcard` (`idno`),    //约束,身份证号码要唯一
    key `telephone` (`mobile`)        //索引,主要作用是加快查询速度
) engine=InnoDB, auto_increment=2, default, charset=utf8;    //auto_increment 表示每次递增2 ?
//至于以上数据类型应该如何正确插入,可以参考 github->PyMySql: pymysql/tests/test_basic.py

1.插入数据(涉及写操作(插入 删除 更新) 都需要 commit)

//这种方式插入数据需要给出完整的列
insert into table_name values(val1,val2,val3,...);

//这种方式插入数据可以不给出 有 auto_increment/default 等属性的列
//实际测试的时候发现 column1 column2 ... 是否用 ``括起来都可以,
//但 val1 val2 ... 中, char 类型必须用''或""括起来, int 类型是否用''或""括起来都可以
insert into table_name (column1,column2,column3,...) values(val1,val2,val3,...);

2.查找数据

select column_name(s) from table_name;    //只返回某一列,选择多个列则列之间以逗号隔开,返回所有列可以 select *,返回结果无序,也没有过滤
select column_name(s) from table_name limit 5;    //返回结果只拿取前5个
select column_name(s) from table_name limit 5,10;    //返回结果只拿取 行6开始的10行,等价于 limit 10 offset 5
select distinct column_name(s) from table_name;    //当选择返回某一列的时候,很可能多个结果此列值相同,distinct 保证只显示不同的值
select table_name.column_name(s) from db_name.table_name;    //完全限定的 表名和列名,效果同上

//从表中选择某一列或某几列,并按照 column_name 的 升序/降序 (默认升序)排序后返回
select column_name(s) from table_name
order by column1 desc, column2 asc;    //按第一列降序排序,第一列相同时内部再按第二列升序排序(asc可省略)
select column_name(s) from table_name
order by column_name desc limit 5;    //排序加上 limit 子句可以返回前xx的数据,很实用.注意 limit子句应该在 order by 之后

//过滤数据(order by 子句放在 where 子句后才有意义)
// where 支持:
//        = 等于
//        <> != 不等于
//        < <= > >= 小于 小于等于 大于 大于等于
//        between 范围以 and 分隔, eg. where column_name between 5 and 10
select column_name(s) from table_name
where column_name = 2.50;    //只返回 xx列=2.50 的那些行, column_name = 'fang': 只返回 xx列 名为 fang 的那些行(不区分大小写)
select column_name(s) from table_name
where column_name is null;    //只返回 xx列 无值 的那些行(where的特殊用法)

select * from table_name 
where Data(recordtime)='2018-09-01';    //sql 提供了从 datetime(或 date 或 time) 提取 Data() Time() Year() ... 以及时间的计算操作
select * from table_name 
where Data(recordtime) between '2018-09-01' and '2018-10-31';    //'yyyy-mm-dd hh:mm:ss'

//where 多条件组合 and or (如果 and or 同时出现,and 优先级更高)
select column_name(s) from table_name
where column1 < 2.50 and column2 = 'abc' and column3 >= 50;    //返回满足所有条件的行
select column_name(s) from table_name
where column1 < 2.50 or column2 = 'abc' or column3 >= 50;    //返回满足任意条件的行
select column_name(s) from table_name
where (column1 < 2.50 or column2 = 'abc') and column3 >= 50;    //and优先级更高,所以加括号用来消除歧义
select column_name(s) from table_name
where column1 in ('fang', 'liu', 'yang');    //in 集合,or 其实也可以实现. 还有一个: not in (xxx)
select column_name(s) from table_name
where column1 not in ('fang', 'liu', 'yang');    //not 通常可以用来对 in, between, exists 子句取反

//where search pattern(可以配置为大小写敏感), like 子句通常比上面的过滤子句慢一些, 通配符尽量出现在 search pattern 的末尾, %abc 这种通配搜索起来最慢
// % 通配符表示通配 任何字符(NULL 除外)出现任意次数(包括0次), % 可以有很多,可以出现在表达式的任何位置
// _ 通配符通配 任意单个字符. eg '_ ton data.' 可以匹配 '1 ton data.', '2 ton data.', ...
select column_name(s) from table_name
where column_name like '_jet%';    //选择 xx列名以 xjet(x代表任意字符...) 打头的那些行

//where 支持正则表达式(Regular Expression) 的一个小子集,
//sql 支持正则表达式的测试 eg. select 'hello fang' regexp '[0-9]' 将返回0,因为没有匹配上
select column_name(s) from table_name
where column_name regexp pattern;    //选择 xx列名 包含 正则表达式的那些行(不区分大小写, 若要区分大小写,可以 regexp binary)
select column_name(s) from table_name
where column_name regexp binary '[1-5] ton data\\.';    //列名中包含 '1 ton data.',或 '2 ton data.', ... ,或 '5 ton data.' 中任意一个的那些行
//为了匹配反斜杠\本身,需要 \\\
//'\\([0-9] sticks?\\)' 匹配 包含(0 stick) (1 stick)...等 或 (0 sticks) (1 sticks)...等 的字串
//'^\\([0-9] sticks?\\)' 同上,只是变成 了必须以(0 stick) 等字串打头(因为^代表了行的开始)

//查找数据后的 后处理:计算字段
select concat("column1=",column1,",column2=",column2)    //选出目标行中的两列后,返回 column1=xx,column2=xx 的形式
from table order by column1;
select concat("column1=",column1,",column2=",column2) as co12    //得到后处理结果后,将此列命名为 co12
from table order by column1;
//后处理 算数运算操作包括 + - * / ,将此列命名为 mul, select 可以用来测试运算:
//eg. select 3*6 返回 18; select Trim('  abc ')返回 abc; select Now();返回当前时间
select column1,column2,column1*column2 as mul
from table order by column1;

//数据信息的统计汇总(eg.行数,max,min,sum),这种情况返回实际数据再在上层处理就是资源浪费+带宽浪费
//    avg()        //返回某列的平均值,需要指定列名,忽略列值为null的行
//    count()        //返回某列的行数
//    max()        //返回某列的最大值,需要指定列名,忽略列值为null的行
//    min()        //返回某列的最小值,需要指定列名,忽略列值为null的行
//    sum()        //返回某列值之和,需要指定列名,忽略列值为null的行

select avg(column1) as avg_col                //对过滤后的结果取avg()
from table_name where column2 like "abc%";
select avg(distinct column1) as avg_col        //只针对不同的列值进行计算,默认是 all, 表示对所有的值进行
from table_name where column2 like "abc%";
select count(*) as num_items                //若指定列名,则指定列的值为null的行将被忽略,使用*则不忽略
from table_name where column2 like "abc%";
select count(*) as num_items, max(column1) as max_val, avg(column1) as avg_val    //组合
from table_name where column2 like "abc%";

//分组
//先将 column1 按照不同的值进行分组,再对每个分组进行统计汇总
select column1,count(*) as num_items    //这个count(*)应该是对组内结果做统计了,除了统计函数外,所有出现的列名应该在group by中 才是有意义的
from table_name group by column1;    //column1 有几种不同的值,返回结果就应该有几行。group by 可以作用于任意数目的列,用在where之后,order by 之前

//可以使用 having 对得到的分组进行过滤(where 只能过滤行,可理解为 where在分组前进行过滤,having在分组后进行过滤)
//having 支持所有 where 支持的操作
select column1,count(*) as num_items from table_name
group by column1 having count(*) >= 2;    //只返回分组中 num_items >=2 的那些组
select column1,count(*) as num_items from table_name
where column2 < 5                        //先使用 where 过滤行,
group by column1 having count(*) >= 2;    //对过滤的结果进行分组,且只返回分组中 num_items >=2 的那些组
select column1,count(*) as num_items from table_name
where column2 < 5                        //先使用 where 过滤行,
group by column1 having count(*) >= 2    //对过滤的结果进行分组,且只返回分组中 num_items >=2 的那些组
order by num_items;                        //这是再对上面的分组进行排序

//----------总结: 查询子句组合顺序
select * from * where xx..
group by * having xx..
order by * limit x

//子查询(可以实现跨表查询): 将一个 select 语句返回的结果用于另一个 select 语句的 where 子句
//子查询一般用 in 连接,但是使用 = 或 <> 其他测试符也是可以的
//sql一般会先执行子查询,然后再执行外面的查询
select column_name(s) from table_name1
where column1 in (select column1 from table_name2
                  where column2='abc');

//从 t1 表中选取多行数据,每行输出3列,其中第3列使用子查询完成(相当于两层for循环)
//子查询语句使用了 完全限定列名 告诉 sql比较 t2 表中的 d 列与外层当前正从 t1 表检索的 d 列,针对相同的那些做 count()
select t1a,t1b,(select count(*) from t2 where t2.d=t1.d) as c
from t1
order by t1d