MySQL01-基础

1.MySQL下载、安装和登录

MySQL官网。https://www.mysql.com/。
MySQL下载地址。https://dev.mysql.com/downloads/mysql/。

MySQL安装。

下载完成后直接解压。
将${mysql}/bin加入到path。
在${mysql}下添加 mysql.ini文件。

[mysql]

# 设置mysql客户端默认字符集
default-character-set=utf8 

[mysqld]

#设置3306端口
port = 3306 

# 设置mysql的安装目录
basedir=D:\Program Files\mysql-8.0.25-winx64

# 设置mysql数据库的数据的存放目录
datadir=D:\Program Files\mysql-8.0.25-winx64\data

# 允许最大连接数
max_connections=200

# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8

# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

# 跳过输入密码，第一次进入MySQL的时候跳过输入密码，
# 然后再MySQL中修改密码，之后可以注释掉。
skip-grant-tables

MySQL的启动。
1. mysqld –install，安装MySQL服务。
2. mysqld --initialize-insecure --user=mysql，初始化MySQL数据。
3. net start mysql，启动MySQL服务。
修改MySQL密码。
1. mysql –u root –p，登录MySQL。
2. update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'，登录之后修改root用户的密码。
3. 删除msyql.ini文件的skip-grant-tables配置。
4. net restart msyql，重启MySQL服务。
5. mysql –u root –p123456，通过密码登录MySQL。

2.MySQL常用命令-操作数据库

show databases，显示所有数据库。
create database test01，创建数据库test01。
use test01，使用数据库test01。
select database()，显示当前正在使用的数据库。
drop database test01，删除数据库。
show create database test，查看创建数据库的SQL。

3.MySQL常用数据类型

数值类型。
1. tinyint，1个字节，只能保证-128~127之间的整数。
2. smallint，2个字节。
3. mediumint，3个字节。
4. int，4个字节。
5. bigint，8个字节。
6. float，可以保存小数，4个字节。
7. double，8个字节。
8. decimal，字符串形式的浮点数。
字符串。
1. char，固定大小的字符串，可以保存0-255个字符串。
2. varchar，可变长字符串0-65535，在创建表时如果指定了字符串的长度，就只能保存指定的长度。如name varchar(20)，就只能保存20个字符串。
3. tinytext，可以保存长度2^8-1个字符串。
4. text，用于保存文本类型，可以保存2^16-1个字符串。
日期类型。
1. date，保存年月日。
2. time，保存时分秒。
3. datetime，保存年月日+时分秒。
4. timestamp，保存1970.1.1到当前时间的毫秒数。datetime保存日期类型会占用8个字节，timestamp底层使用时间对应的毫秒数，即一个int类型的变量来保持时间，所以只占用了四个字节，但是显示的时候会显示为年月日+时分秒。timestamp保存日期的缺点是，由于使用int类型来保持时间，所以会受到int大小的限制，没有datetime保存的时间范围大。
5. year，保存年。

4.MySQL常用数据属性

unsigned，无符号整数，该列的值不能为负数。声明方法：age int unsigned DEFAULT NULL。
zerofill，零填充。声明方法：b int(5) unsigned zerofill DEFAULT NULL。输入3，会变为00003。
auto_increment，自增。声明方法：id int not null auto_increment。
not null，非空。声明方法：c varchar(255) NOT NULL。插入数据时，为null，就会报错。
null，默认为null。声明方法：d varchar(255) DEFAULT NULL。
默认值。声明方法：f varchar(255) DEFAULT '10'，默认为10。

5.MySQL创建表的语句

-- 变量上加``，防止变量名称和关键字冲突。
create table if not exists `tb_student`(
	`id` int(10) not null auto_increment comment '学号',
	`name` varchar(10) not null comment '姓名',
	`pwd` varchar(20) not null default '123456' comment '密码',
	`sex` varchar(1) not null default '男' comment '性别',
	`birthday` datetime default null comment '生日',
	`address` varchar(100) default null comment '地址',
	primary key(`id`) USING BTREE
)engine=innodb default charset=utf8;

6.int(10)和varchar(10)的区别

varchar(10)，只能保存10个字符。
int(10)，这个10是显示的宽度，具体保存的的值还是int的范围。

7.MYISAM和InnoDB的区别

事务。MYISAM不支持事务，InnoDB支持事务。
锁。MyISAM支持表锁，不支持行锁；InnoDB支持表锁和行锁。
外键。MyISAM不支持外键约束，InnoDB支持外键约束。
全文索引。MyISAM支持全文索引，InnoDB不支持全文索引。
存储。
1. MyISAM需要三个文件来保存表的结构和数据。.frm，保存表结构定义；.MYD，保存MYISAM数据文件；.MYI，保存 MYISAM索引文件。
2. InnoDB数据和索引文件都在${mysql}/data/数据库/*.idb。
索引。MyISAM，使用非聚簇索引，索引和文件分开，随机存储，只能缓存索引；InnoDB，使用聚簇索引，索引就是数据，顺序存储，因此能缓存索引，也能缓存数据。
应用场景。读多时使用MYISAM；写多时使用Innodb。原有：MyISAM是表锁，并发没有InnoDB高；而且不支持事务。

8.MySQL常用命令-操作表

show create table tb_student，查看创建tb_student表的SQL。
desc tb_student，查看tb_student表字段信息。
alter table tb_student rename as student，修改tb_student表名为student。
drop table tb_test，删除表。
表中字段的修改。
1. alter table student add age int(11)，为student表添加age字段。
2. alter table student modify age varchar(10)，修改student表中age字段数据类型和约束。
3. alter table student change age age1 varchar(11)，修改student表中age字段为age1字段。
4. alter table student drop age1，删除student表中age1字段。

9.MySQL常用命令-操作表的外键

创建表时添加外键。

-- 为class_id添加外键。
-- 1 key `FK_class_id` (`class_id`), 将class_id的外键名定义为FK_class_id。
-- constraint 约束
-- foreign key 外键
-- references 引用
-- references `tb_class`(`id`)，引用 tb_class表的id
create table `tb_student`(
    `id` int not null auto_increment,
    `name` varchar(10) default null,
    `class_id` int default null,
    primary key(`id`),
    key `FK_class_id`(`class_id`),
    constraint `FK_class_id` foreign key (`class_id`) references `tb_class`(`id`)
)engine=innodb default charset=utf8;

创建表后添加外键。

alter table `tb_student` add constraint `FK_class_id` foreign key (`class_id`) references `tb_class`(`id`);

删除外键。需要先删除外键，在删除索引。
1. alter table tb_student drop foreign key FK_class_id，删除外键，删除外键后索引还在，所以需要在删除索引。
2. alter table tb_student drop index FK_class_id，删除索引，定义外键时使用外键名FK_class_id，所以索引名为FK_class_id，这里需要删除索引FK_class_id。
外键不建议使用。阿里巴巴开发手册中有，强制-不要使用外键和级联，一切外键概念需要在应用层解决。原因：在update和delete时需要考虑到外键约束。

10.MySQL常用命令-操作表中数据

insert into tb_student(name, class_id) values ('tom',1),('bob',2)，特定列插入多条数据，id自动递增；insert into tb_student values ('3','tom',1),('4','bob',2)，不指定插入的列，就需要填充所有的数据，包含id。否则报错。
update tb_student set name = 'zs' where id = 1，更新数据。
delete from tb_student01 where id = 1，删除数据。
truncate table tb_student，删除表中所有的数据，只保留表结构。
select name as n,class_id ci from tb_student where id = 2，查询指定列的数据，并且修改查询结果显示的列名。

11.delete和truncate的区别

delete删除数据之后，自增id不会重新计数；truncate删除数据之后，自增id会重新计数。
delete删除的数据，可以在事务中回滚，恢复删除的数据；truncate删除的数据不能在事务中回滚。
delete删除的数据，可以在事务中回滚，所以删除的时候会有redo log日志。如果数据库服务器磁盘空间不足，使用delete删除数据，可能会由于产生redo log日志而报错。

12.MySQL常用命令-查询

字符拼接。select concat('a','b','c','d')，查询结果字符拼接。
distinct。select distinct class_id from tb_student，distinct去重。distinct需要放在查询字段的最前面；distinct class_id,name，不是只对class_id去重，而是对class_id+name去重，即只会去掉class_id重复并且name也重复的数据。
!=，不等于。select * from tb_student where id != 5，或者，select * from tb_student where not id = 5。
is null和is not null。select * from tb_student where id is not null，select * from tb_student where id is null。
like模糊查询。select * from tb_student where name like 't%'，%会匹配多个字符，即查询以t开头的结果；select * from tb_student where name like 't_'，下划线，只匹配一个字符，即以t开头，且总共两个字符的结果。
left join和right join。select s.id,s.name,s.class_id,c.id from tb_student s left join tb_class c on s.class_id = c.id。
1. left join，以左表为中心，先查询左表中的所有数据，然后去右表中通过s.class_id = c.id匹配。会查到左表中存在，但是有表中没有的数据。
2. right以右表为中心。
inner join。select s.id,s.name,s.class_id,c.id from tb_student s inner join tb_class c on s.class_id = c.id，查询左表和右表都有的数据。即会过滤 left join和right join中为null的数据。
union和union all。待更新...

13.分页、排序和分组

排序。order by id asc，通过id升序排列；order by id desc，通过id降序排列。
分页。
1. limit 0,5，查询第1-5条数据。0，开始索引；5，页面大小。
2. 一般情况前端传入currentPage和pageSize。转换为limit的简单公式，limit (currentPage-1)*pageSize,pageSize。
分组。select count(1),class_id from tb_student group by class_id having count(1) > 0，通过class_id分组，分组之后只能查询和分组字段相关的数据。

14.MySQL常用命令-系统函数

数值运算。

select abs(-19.1); -- 绝对值 19.1
select round(23.5); -- 四舍五入 24
select ceiling(12.3); -- 向上取整 13
select floor(12.9); -- 向下取整 12
select rand(); -- 返回0-1直接的随机小数 0.5384846679293408
select sign(10); -- 负数返回-1,0返回0，正数返回1。1
select mod(10,3); -- 取余，1

字符串函数。

select char_length('1234'); # 获取字符串长度 4
select concat('1','2','3'); # 字符串连接，123
select INSERT('1234567','2','5','小米') # 将第2个位置，后面5个字符替换为小米，1小米7

select lower('XIAOMI') # 转小写
select upper('xiaomi') # 转大写

select instr('12345','2'); # 获取字符串2第一次出现的位置，2
select left('1234567',2); # 截取左边前两个字符，12
select right('1234567',2); # 截取右边后2个字符， 67 

select replace('123+456+789','+','-'); # 将+替换为-， 123-456-789
select substr('1234567',2,2); # 获取第2个字符后面的两个字符 23
select reverse('123456'); # 反转 654321

时间函数

select current_date(); # 获取当前日期
select current_time(); -- 获取当前时间
select now(); -- 获取当前日期时间
select localtime(); -- 获取本地当前日期时间
select sysdate(); -- 获取系统当前日期时间

select year(now()); -- 年
select month(now()); -- 月
select day(now()); -- 日
select hour(now()); -- 时 
select minute(now()); -- 分
select second(now()); -- 秒
			
SELECT DATEDIFF('2021-11-30','2020-11-29'); -- 366，返回相差的天数。
			
# 时间格式化。
select date_format(now(), '%Y年%m月%d %H:%i:%s'); -- 2021年10月19 20:39:52

获取系统信息

select version(); -- 获取系统版本 8.0.25
select user(); -- 获取系统当前登录的用户
select system_user(); -- 获取系统当前登录的用户

聚合函数。sum、avg、count、max、min。
其他常用函数

-- md5加密，不可逆，对同一个字符加密的结果是一样的。
select md5('123456'); -- md5加密。e10adc3949ba59abbe56e057f20f883e
select 3*5; -- 15
select if(10>5, '0', '1'); -- 0

15.count(name)、count(1)和count(*)的区别

count(name)，不会统计name为null的行。
count(1)和count(*)，会统计列名为null的行。
如果列名为主键，则使用count(列名)，否则使用count(1)。

16.json常用函数

表结构和数据。

-- 表结构，info列是json类型。
CREATE TABLE `tb_person`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `info` json NULL,
  PRIMARY KEY (`id`) USING BTREE
);

-- 数据
INSERT INTO `tb_person` VALUES (1, 'tom', '{\"id\": 1, \"name\": \"tom\", \"phone\": \"15829550001\", \"address\": \"陕西西安\", \"password\": \"123456\"}');
INSERT INTO `tb_person` VALUES (2, 'bob', '{\"id\": 2, \"name\": \"bob\", \"phone\": \"15829550001\", \"address\": \"北京\", \"password\": \"abc\"}');
INSERT INTO `tb_person` VALUES (3, 'alice', '{\"id\": 3, \"name\": \"alice\", \"phone\": \"15829550001\", \"address\": \"上海\", \"password\": \"1qwerty\"}');

json查询的函数。

-- 查询json的类型
select json_type('{}'); -- OBJECT
select json_type('[]'); -- ARRAY
select json_type('['); -- 报错，不是json类型报错。

-- 查询json中password为'123456'的值，info -> 等价于 json_extract()函数。
select id from tb_person where info -> '$.password' = '123456'; -- 1
select id from tb_person where json_extract(info, '$.password') = '123456'; -- 1

-- 模糊查询
select id from tb_person where info -> '$.password' like '%1%'; -- 1 3

-- 对象转换为json字符串，数字转换为json字符串。
select json_object('id', 1, 'name', 'tom'); -- {"id": 1, "name": "tom"}
select json_array(1, 2, 3, 4); -- [1, 2, 3, 4]

-- $[0]，查询json中索引为0的数据。
select json_extract(json_array(1, 2, 3, 4), '$[0]'); -- 1

-- $[0].name，查询json中索引为0的对象的name值。
select json_extract(json_array(
		json_object('id', 1, 'name', 'tom'),
		json_object('id', 2, 'name', 'alice')), '$[0].name');

json值修改。

-- 修改id=3中info列的json数据的 password='8888'
update tb_person set info = json_set(info, '$.password', '8888') where id = 3;

-- json中插入值，{"id": 3, "name": "alice", "key01": "value01", "phone": "15829550001", "address": "上海", "password": "8888"}
update tb_person set info = json_insert(info, '$.key01', 'value01') where id = 3;

-- json_replace，替换json中的值。
-- json_replace和json_set的区别，json_set没有对象的key会添加；
-- json_replace如果没有对应的key，则不会进行操作。
update tb_person set info = json_replace(info, '$.key01', '01value01') where id = 3;

-- 删除json中指定的key。
update tb_person set info = json_remove(info, '$.password01') where id = 3;

posted @ 2021-10-31 14:27 行稳致远方阅读(22) 评论(0) 收藏举报

刷新页面返回顶部

行稳致远方

MySQL01-基础

MySQL01-基础

1.MySQL下载、安装和登录

2.MySQL常用命令-操作数据库

3.MySQL常用数据类型

4.MySQL常用数据属性

5.MySQL创建表的语句

6.int(10)和varchar(10)的区别

7.MYISAM和InnoDB的区别

8.MySQL常用命令-操作表

9.MySQL常用命令-操作表的外键

10.MySQL常用命令-操作表中数据

11.delete和truncate的区别

12.MySQL常用命令-查询

13.分页、排序和分组

14.MySQL常用命令-系统函数

15.count(name)、count(1)和count(*)的区别

16.json常用函数

公告