《一起学mysql》1

佚名 6年前 (2019-05-05) 随笔 1437人围观抢沙发百度已收录

自从达内毕业后就没用过mysql，一直用的hive，hdfs 存储数据，最近突然又接触到了关系型数据库。本想随便从网上找个教程看看，但是都不是很满意，pdf看着又难受，还是自己个儿写个笔记吧。首先我们来安装mysql，我比较喜欢在linux下搞程序，因为我操作的线上环境都是centos，所以介绍在 centos下安装mysql mysql 的用户管理，安全设置啥的就不介绍了，不想当DBA。 mysql 建表 现在建我们的第一张表 (关于数据类型的介绍看这里 ) 首先进入数据库 mysql -ujason -p123 MariaDB [(none)]> use jason; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [jason]> 建表 create table student( id int(2), phone_num int, name char(2), introduction char ) 建表时我们并未指定intoduction字段的宽度，系统默认分配了1，phone_num也未指定，默认分配了11 MariaDB [jason]> desc student; +--------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------+------+-----+---------+-------+ | id | int(2) | YES | | NULL | | | phone_num | int(11) | YES | | NULL | | | name | char(2) | YES | | NULL | | | introduction | char(1) | YES | | NULL | | +--------------+---------+------+-----+---------+-------+ 4 rows in set (0.00 sec) 向数据库中插入两行数据并查看 MariaDB [jason]> insert into student values -> (1,15675698904,'jason','A boy from HB,like swimming'), -> (200,-15675698904,'dong','A boy from HB,like TKD'); Query OK, 2 rows affected, 6 warnings (0.00 sec) Records: 2 Duplicates: 0 Warnings: 6 MariaDB [jason]> select * from student; +------+-------------+------+--------------+ | id | phone_num | name | introduction | +------+-------------+------+--------------+ | 1 | 2147483647 | ja | A | | 200 | -2147483648 | do | A | +------+-------------+------+--------------+ 2 rows in set (0.00 sec) 在建表时id，name 字段都进行了字段宽度设置（id int(2),name char(2)）,为何数据库中显示的信息不一样？ mysql中数值类型和字符类型的宽度限制含义是不同的，对于字符传来讲，括号中的数字就指定了该字段可以存储几个“字符”——请注意是字符，而不是字节—— 若插入的字符串长度超过了这个长度，就会截断，对于数值来讲，当插入的数值长度不够2时，会以0填充，请看下下面的例子 create table student2( id int(5) zerofill //只有当指定zerofill时才起作用 ) insert into student2 values(123), (123456); MariaDB [jason]> select * from student2; +--------+ | id | +--------+ | 00123 | | 123456 | +--------+ 那为什么我们插入的手机号会变成了“2147483647”？ int字节长度为 4字节，最大只能存储到21亿多一些，当存储的数值超过最大值时，就存储int的最大值，另外， 21亿是个十位数字，为啥int默认宽度时 11，细心的朋友可以发现，多出来的1位用来存储正负号说到zerofill,干脆再说一下unsigned,,unsigned 抛弃了负数，tinyint 本来是 -128-127，如果加上unsigned 标识，则tinyint 标识 0-255，被zerofill修饰的字段默认是unsigned,看例子说话 create table unsigned_test(id tinyint unsigned); insert into unsigned_test values(-100),(200),(300); MariaDB [jason]> select * from unsigned_test; +------+ | id | +------+ | 0 | | 200 | | 255 | +------+ 既然有了char 来存储字符串，为啥还要有一个varchar？ 首先，char 和varchar 所能存储的最大字节数时不同的，char 最多255 字节，varchar最多65535字节，在底层存储二者也是不一样的，char在硬盘中每个字段的存储空间都是固定的，而varchar 则是根据需要来指定存储空间的，varchar 存储时会多记录一个信息——该字段占多少个字节，这样就带来读取方面的一个区别，char的读取速度快于varchar，话又说回来varchar省存储空间，建议如果字段时定长的那么选择char做存储。 将大量数据一次性插入msyql 除了上面的方法 insert into table(col1,col2,...) values(...),(....),还有一种更高效的方法，load data infile .... 这次我不想删表再重新建表了，我们来直接修改表的字段信息 alter table student modify phone_num char(11); alter table student change name name char(10); alter table student change introduction intro varchar(100); 来看看修改后表成啥样了 MariaDB [jason]> desc student; +-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | id | int(2) | YES | | NULL | | | phone_num | char(11) | YES | | NULL | | | name | char(10) | YES | | NULL | | | intro | varchar(100) | YES | | NULL | | +-----------+--------------+------+-----+---------+-------+ 大家根据修改后的效果自己总结下上海吗两种方法如何使用要导入的文件内容如下：分别对应了id,name,intro,phone_num 请注意时没有按着字段顺序来的 root@kali:~/data# cat student 1,jasona,nothing,15675698905 2,jasons,nothing,15675698905 3,jasond,nothing,15675698905 接下来导入数据 load data infile '/root/data/student' into table student fields terminated by ',' lines terminated by '\n' (id,name,intro,phone_num); 第一行指定了导入的文件和表，第二行指定了分隔符，第三方指定了文件里的字段顺序执行上述语句可能会报错 “acces denied for user ...”,是因为该mysql 用户无权限读写服务器的文件，用root 登陆mysql，执行如下语句 grant file on *.* to jason@'%' load data 比insert 快20倍，是插入数据最快的方法，在添加数据时禁用索引，可以加快插入速度，mysql 此时只需考虑增加数据，不用考虑更新索引文件，操作如下： alter table tablename disable keys; load data ....... alter table tablename enable keys; limit从指定位置返回数据 limit 默认从偏移量为0开始返回数据，但是可以指定偏移量——limit 偏移量,条数，从偏移量+1 条数据开始返回数据 MariaDB [jason]> select * from student limit 1; +------+-------------+--------+---------+ | id | phone_num | name | intro | +------+-------------+--------+---------+ | 1 | 15675698905 | jasona | nothing | +------+-------------+--------+---------+ 1 row in set (0.00 sec) MariaDB [jason]> select * from student limit 1,1; +------+-------------+--------+---------+ | id | phone_num | name | intro | +------+-------------+--------+---------+ | 2 | 15675698905 | jasons | nothing | +------+-------------+--------+---------+ 关于日期的一些函数 date_format 指定日期返回的格式，%d 和 %e 都返回具体的某一天，但是如果天是小于10的，%d 会返回用0 填充的日期，%e 不以0填充 MariaDB [jason]> select date_format(now(),'%Y-%m- %d') from student limit 1; +-------------------------------+ | date_format(now(),'%Y-%m-%d') | +-------------------------------+ | 2019-04- 04 | +-------------------------------+ 1 row in set (0.00 sec) MariaDB [jason]> select date_format(now(),'%Y-%m-%e') from student limit 1; +-------------------------------+ | date_format(now(),'%Y-%m- %e') | +-------------------------------+ | 2019-04- 4 | +-------------------------------+ 单独返回日期中的年月日 MariaDB [jason]> select year(now()),month(now()),day(now()) from student limit 1; +-------------+--------------+------------+ | year(now()) | month(now()) | day(now()) | +-------------+--------------+------------+ | 2019 | 4 | 4 | +-------------+--------------+------------+ 1 row in set (0.00 sec) 从左侧或右侧取 n 位返回 MariaDB [jason]> select left(now(),10),right(now(),8) from student limit 1; +----------------+----------------+ | left(now(),10) | right(now(),8) | +----------------+----------------+ | 2019-04-04 | 21:35:19 | +----------------+----------------+ 建表时到底该选用哪种类型的字符串，下面是一些小建议： 1）不要将数字类型存储成字符串，应将其存储为数字类型，数字类型效率高于字符串，比如工资，应将其设置为double,而不是varchar；空间上一个字符至少会占据一个字节，而数字是按位存储的；比较上也会带来差异，比如 “9”>"100",但是 9<100 2）如果从效率考虑的话，应该选择char而不是varchar 3）从空间方面考虑，应该选择varchar 4）如果字段的种类是固定的，比方说性别，只有'男','女'，应该选择enum 5）如果可以在字段中存储 m 个选项中的某n个，m>=n,可以选用set 6）另外还有text 和blob，blob中存储的字节，不存在字符集（gbk，utf8）的概念,blob 在检索时是大小写敏感的，varchar ,text,blob,最大都是支持 65535字节，建议使用varchar 7）对于图像的存储，建议把图片保存在一个文件夹下，然后mysql 保存图片地址 enum和set上个例子 create table enum_set( name char(4), gender enum('m','f'), hobby set('football','music','kungfu') ); insert into enum_set values ('李小龙','f','kungfu'), ('甄子丹','m','kungfu,music'); MariaDB [jason]> select * from enum_set; +-----------+--------+--------------+ | name | gender | hobby | +-----------+--------+--------------+ | 李小龙 | f | kungfu | | 甄子丹 | m | music,kungfu | +-----------+--------+--------------+ 有木有发现mysql中varchar 和char 是不区分大写写的？ MariaDB [jason]> select * from student where name like 'Jason%' limit 2; +------+-------------+--------+---------+ | id | phone_num | name | intro | +------+-------------+--------+---------+ | 1 | 15675698905 | jasona | nothing | | 2 | 15675698905 | jasons | nothing | +------+-------------+--------+---------+ 为了让它大小写敏感，可以这么干 MariaDB [jason]> select * from student where binary name like 'Jason%' limit 2; Empty set (0.00 sec) 上面我们做查询的时候都是水平打印，要不要来个垂直打印？ MariaDB [jason]> select * from student limit 2 \G; *************************** 1. row *************************** id: 1 phone_num: 15675698905 name: jasona intro: nothing *************************** 2. row *************************** id: 2 phone_num: 15675698905 name: jasons intro: nothing 2 rows in set (0.00 sec) 关键就在末尾的 \G mysql 引擎 首先我们来查看一下建表语句 MariaDB [jason]> show create table student \G; *************************** 1. row *************************** Table: student Create Table: CREATE TABLE `student` ( `id` int(2) DEFAULT NULL, `phone_num` char(11) DEFAULT NULL, `name` char(10) DEFAULT NULL, `intro` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) ENGINE=InnoDB 这句我们在建表时并未添加，是mysql建表时默认设置的，engine 中文翻译为引擎，引擎到底是个啥玩意儿？我们使用mysql要求各不相同，有人不需要事务功能，有人需要经常读，有人需要经常写，update，这些不同的技术或功能就叫引擎，我们来看看mysql默认有哪些引擎 MariaDB [jason]> show engines \G; *************************** 1. row *************************** Engine: MRG_MyISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 2. row *************************** Engine: CSV Support: YES Comment: Stores tables as CSV files Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 4. row *************************** Engine: MyISAM Support: YES Comment: Non-transactional engine with good performance and small data footprint Transactions: NO XA: NO Savepoints: NO *************************** 5. row *************************** Engine: SEQUENCE Support: YES Comment: Generated tables filled with sequential values Transactions: YES XA: NO Savepoints: YES *************************** 6. row *************************** Engine: Aria Support: YES Comment: Crash-safe tables with MyISAM heritage Transactions: NO XA: NO Savepoints: NO *************************** 7. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO *************************** 8. row *************************** Engine: InnoDB Support: DEFAULT Comment: Percona-XtraDB, Supports transactions, row-level locking, foreign keys and encryption for tables Transactions: YES XA: YES Savepoints: YES 8 rows in set (0.00 sec) 好多，晕死，我们重点看看 InnoDB 和 MyISAM InnoDB InnoDB是一个事务型的存储引擎，有行级锁定和外键约束 MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。而且它没有保存表的行数，当 SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。适用场景： 1）经常更新的表，适合处理多重并发的更新请求 2）支持事务 3）可以从灾难中恢复（通过bin-log日志等） 4）外键约束。只有他支持外键 5）支持自动增加列属性auto_increment MyIsam 没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些适用场景： 1）不支持事务的设计，但是并不代表着有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。 2）不支持外键的表设计。 3）查询速度很快，如果数据库insert和update的操作比较多的话比较适用。 4）整天对表进行加锁的场景。 5）MyISAM极度强调快速读取操作。 6）MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么 MyIASM也是很好的选择。其他的引擎各位感兴趣的话可以百度下如何设置引擎 1.在安装mysql的文章中有my.cnf,可以设置默认引擎 2.建表的时候在后面加上引擎类型，create table xx(id int) ENGINE=InnoDB 3.alter table student engine=myisam 一些sql小技巧 1)null 表中有些字段的值可能为null，我们来看看NUll的相关语法 MariaDB [jason]> select null=0,null>2,null = null,null is null,null is not null from test limit 1; +--------+--------+-------------+--------------+------------------+ | null=0 | null>2 | null = null | null is null | null is not null | +--------+--------+-------------+--------------+------------------+ | NULL | NULL | NULL | 1 | 0 | +--------+--------+-------------+--------------+------------------+ null 与任何值的比较结果都是null，判断一个值是否为null，要用 is，is not 2)between MariaDB [jason]> select 5 between 4 and 6, 5 between 6 and 4; +-------------------+-------------------+ | 5 between 4 and 6 | 5 between 6 and 4 | +-------------------+-------------------+ | 1 | 0 | +-------------------+-------------------+ between 语法的区间 m and n,必须是m<n 3)like 在查询时我们可能想要找名字叫做 "张大山"的人，可是查询时我们忘记了名字的全称，只记得姓张；也或者是想统计下姓张的总共有多少人，这时候就要用到like语法来进行模糊匹配，模糊匹配有两个通配符， %：匹配任意字符任意多次 _：匹配任意字符一次比方说： name like ‘%张%’ 匹配name 中带张的，只要name 中有张就行 name like '张%' 匹配姓张的，只要姓张就行 name like '张_' 匹配姓张的，但是名里只带一个字，张五，张六啥的除了like mysql 中还有正则匹配， regexp，这个我在工作中基本没用过，感兴趣的朋友可以百度下 在mysql 中使用变量 一般情况下变量在java，python中才拥有，但是mysql 中也拥有变量，来看看mysql中变量的使用 1）变量声明法儿一： MariaDB [jason]> select @avgid:=avg(id) from student; +-----------------+ | @avgid:=avg(id) | +-----------------+ | 2.000000000 | +-----------------+ 1 row in set (0.01 sec) 法儿二： MariaDB [jason]> set @avgid2=3; Query OK, 0 rows affected (0.00 sec) 2）变量使用 MariaDB [jason]> select * from student where id>@avgid; +------+-------------+--------+---------+ | id | phone_num | name | intro | +------+-------------+--------+---------+ | 3 | 15675698905 | jasond | nothing | | 3 | 15675698905 | jasond | nothing | | 3 | 15675698905 | jasond | nothing | +------+-------------+--------+---------+ 3 rows in set (0.00 sec) MariaDB [jason]> select * from student where id=@avgid2 limit 1; +------+-------------+--------+---------+ | id | phone_num | name | intro | +------+-------------+--------+---------+ | 3 | 15675698905 | jasond | nothing | +------+-------------+--------+---------+ 1 row in set (0.01 sec) mysql 脚本 想这样一个问题，我们操作mysql的语句一直是在交互界面执行的，生产中遇到每天执行的重复任务怎么办？和其他编程语言一样，我们可以把要执行的sql写在文件里，然后每次去执行这个文件，比如，aa.sql root@kali:~/code# cat aa.sql select * from student limit 2; 可以用以下两个方法来执行这个文件法儿一：在mysql 交互界面 MariaDB [jason]> source /root/code/aa.sql +------+-------------+--------+---------+ | id | phone_num | name | intro | +------+-------------+--------+---------+ | 1 | 15675698905 | jasona | nothing | | 2 | 15675698905 | jasons | nothing | +------+-------------+--------+---------+ 2 rows in set (0.00 sec) 法儿二：在linux 终端 root@kali:~/code# mysql -ujason -p123 jason < aa.sql id phone_num name intro 1 15675698905 jasona nothing 2 15675698905 jasons nothing