《一起学mysql》1

首先我们来安装mysql,我比较喜欢在linux下搞程序,因为我操作的线上环境都是centos,所以介绍在centos下安装mysql
 
mysql 的用户管理,安全设置啥的就不介绍了。
 
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
 
 
 
 
 
 
 
 
 
posted @ 2019-05-05 09:32  生心无住  阅读(265)  评论(0编辑  收藏  举报