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

 

 

sql执行的性能可能会受到 操作类型,表中数据量,是否存在索引或键,以及其他条件的影响

//如果数据存储在多个表中,可使用 联结 检索数据
//select 语句运行时关联多个表返回一组输出

//内部联结:
//定义表时指定主键和外键可以防止插入非法外键(插入的外键必须是另一个表的主键,sql会帮忙检查)
select a,b,c    //eg. a 是 t1 表中的列, bc 是 t2 表中的列
from t1,t2        //from 两个表,检索过程中会对第一个表中的每一行和第二个表中的每一行进行配对,where 作为过滤条件,只返回满足条件的配对
where t1.xid = t2.xid    //xid 是 t1 的外键,是 t2 的主键(or 是t2的外键t1的主键),没有此过滤条件 将返回"两个表行数乘积"个结果
order by a,b;

//内部联结:与上面等价的方式,可能不如使用 where 子句的灵活,但是更不易出错,所以下面这个应该是首选
select a,b,c
from t1 inner join t2
on t1.xid = t2.xid
order by a,b;

//内部联结:联结多个表,这种做法很多可以替代子查询(sql一般推荐使用联结),理解起来哪个更容易?
select a,b,c
from t1,t2,t3
where t1.xid = t2.xid and t1.yid = t3.yid and c > 50    //最后一个 and 是附件的过滤条件
order by a,b;

//自联结
select a,b,c
from table1 as t1, table1 as t2        //注意这里两个都是 table1,自联结的话这里必须要用表的别名了,不然会有歧义的
where t1.xid=t2.xid and t2.c = 'abc';    //找出满足特定条件(t2.c = 'abc')的行,再找到和其外键相同的那些行

//内部联结表中外键没有出现在另一个表主键中的,不会返回,外部联结则会有 null 与之对应
//LEFT JOIN(左连接): 获取左表所有记录,即使右表没有对应匹配的记录
//RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录，即使左表没有对应匹配的记录
select a,b,c
from t1 left outer join t2    //outer 可以省略?
on t1.xid=t2.xid;