Mysql入门（学习笔记一）

liyf
214 阅读7分钟

Mysql入门

安装

参考文章：MySQL安装（Mac版）

mac 安装mysql

brew install mysql

启动 mysql

$ mysql.server start
Starting MySQL
 SUCCESS!

数据库操作

创建数据库

CREATE DATABASE IF NOT EXISTS test_db02;

所谓utf8_unicode_ci，其实是用来排序的规则。对于mysql中那些字符类型的列，如VARCHAR，CHAR，TEXT类型的列，都需要有一个COLLATE类型来告知mysql如何对该列进行排序和比较。简而言之，COLLATE会影响到ORDER BY语句的顺序，会影响到WHERE条件中大于小于号筛选出来的结果，会影响DISTINCT、GROUP BY、HAVING语句的查询结果。另外，mysql建索引的时候，如果索引列是字符类型，也会影响索引创建，只不过这种影响我们感知不到。总之，凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。

COLLATE通常是和数据编码（CHARSET）相关的，一般来说每种CHARSET都有多种它所支持的COLLATE，并且每种CHARSET都指定一种COLLATE为默认值。例如Latin1编码的默认COLLATE为latin1_swedish_ci，GBK编码的默认COLLATE为gbk_chinese_ci，utf8mb4编码的默认值为utf8mb4_general_ci。

这里顺便讲个题外话，mysql中有utf8和utf8mb4两种编码，在mysql中请大家忘记utf8，永远使用utf8mb4。这是mysql的一个遗留问题，mysql中的utf8最多只能支持3bytes长度的字符编码，对于一些需要占据4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行。

很多COLLATE都带有_ci字样，这是Case Insensitive的缩写，即大小写无关，也就是说”A”和”a”在排序和比较的时候是一视同仁的。selection * from table1 where field1=”a”同样可以把field1为”A”的值选出来。与此同时，对于那些_cs后缀的COLLATE，则是Case Sensitive，即大小写敏感的。

在mysql中使用show collation指令可以查看到mysql所支持的所有COLLATE。以utf8mb4为例，该编码所支持的所有COLLATE如下图所示。

显示数据库

SHOW DATABASES;

显示数据库创建语句

SHOW CREATE DATABASE test_db01;

删除数据库

DROP DATABASE test_db01;
# 可以添加： IF EXISTS

数据表的操作

创建表

CREATE TABLE emp (
  id INT,
  name VARCHAR(32),
  sex CHAR(1),
  birthday DATE,
  entry_day DATETIME,
  job VARCHAR(32),
  `salay` FLOAT,
  `resume` TEXT
)

删除表

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]

修改表

添加列

ALTER TABLE emp
  ADD image VARCHAR(32) NOT NULL DEFAULT ''
  AFTER `resume`;

修改列

ALTER TABLE emp
    MODIFY `image` varchar(8)

删除列

ALTER TABLE emp
  DROP image

查看表结构

DESC emp;

截屏2021-10-26 下午5.20.17.png

修改表名

rename table emp to empployer;

MySQL数据类型

位类型

数值类型

MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，其属性字段可以添加 AUTO_INCREMENT 自增约束条件。下表中列出了 MySQL 中的数值类型。

类型名称说明存储需求
TINYINT很小的整数1个字节
SMALLINT小的整数2个宇节
MEDIUMINT中等大小的整数3个字节
INT (INTEGHR)普通大小的整数4个字节
BIGINT大整数8个字节


根据占用字节数可以求出每一种数据类型的取值范围。例如，TINYINT 需要 1 个字节（8bit）来存储，那么 TINYINT 无符号数的最大值为 28-1，即 255；TINYINT 有符号数的最大值为 27-1，即 127。其他类型的整数的取值范围计算方法相同，如下表所示。\

类型名称说明存储需求
TINYINT-128〜1270 〜255
SMALLINT-32768〜327670〜65535
MEDIUMINT-8388608〜83886070〜16777215
INT (INTEGER)-2147483648〜21474836470〜4294967295
BIGINT-9223372036854775808〜92233720368547758070〜18446744073709551615

浮点类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）；定点类型只有一种，就是 DECIMAL。

浮点类型和定点类型都可以用(M, D)来表示，其中M称为精度，表示总共的位数；D称为标度，表示小数的位数。

浮点数类型的取值范围为 M（1～255）和 D（1～30，且不能大于 M-2），分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的，FLOAT 和 DOUBLE DECIMAL 类型不同于 FLOAT 和 DOUBLE。DOUBLE 实际上是以字符串的形式存放的，DECIMAL 可能的最大取值范围与 DOUBLE 相同，但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D，则取值范围将随 M 的变大而变大。

从上表中可以看到，DECIMAL 的存储空间并不是固定的，而由精度值 M 决定，占用 M+2 个字节。

FLOAT 类型的取值范围如下：

  • 有符号的取值范围：-3.402823466E+38～-1.175494351E-38。
  • 无符号的取值范围：0 和 -1.175494351E-38～-3.402823466E+38。


DOUBLE 类型的取值范围如下：

  • 有符号的取值范围：-1.7976931348623157E+308～-2.2250738585072014E-308。
  • 无符号的取值范围：0 和 -2.2250738585072014E-308～-1.7976931348623157E+308。

提示：不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。

FLOAT 和 DOUBLE 在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL 如果不指定精度，默认为（10，0）。

浮点数相对

截屏2021-10-26 下午4.33.59.png

截屏2021-10-26 下午5.08.02.png

文本

截屏2021-10-26 下午5.18.07.png

二进制

时间日期

截屏2021-10-26 下午5.16.17.png

ALTER TABLE emp 
    ADD update_time TIMESTAMP NOT NULL
    DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP;

CRUD

INSERT

INSERT INTO table_name [(column[, column...])] VALUES (value[,value])

# 指定字段
INSERT INTO goods (id, goods_name, price)
VALUES(3, '华为', 4500.20)
# 所有字段
INSERT INTO goods VALUES(1, 'iphone', 2000.00),(2, '小米', 3200.10);

UPDATE

UPTATE table_name
SET col_name=expr1, [col_name=expr2...]
[WHERE where_definition]

# 更改表中的所有纪录
UPDATE goods 
    SET price=3000;
# 更改符合条件的数据
UPDATE goods 
    SET price=4000
    WHERE id=1;
# 更新多个字段
UPDATE goods 
    SET goods_name='iphone plus', price= price + 1000
    WHERE id = 1;

DELETE

DELETE FROM table_name
[WHERE where_definition]

DELETE FROM goods
    WHERE id=4;
# 删除表中的所有纪录
DELETE FROM goods

SELECT

SELECT [DISTINCT] * | (column [,column2...]) from table_name

mysql> select * from students;
+----+-----------+---------+---------+------+
| id | name      | chinese | english | math |
+----+-----------+---------+---------+------+
|  1 | 张三      |      98 |      89 |   61 |
|  2 | 张一元    |      89 |      47 |  121 |
|  3 | 李四      |      90 |      78 |   23 |
|  4 | 王五      |      56 |      89 |   82 |
+----+-----------+---------+---------+------+
  • 使用表达式
SELECT name, chinese+english+math FROM students;
  • as 使用别名
SELECT name, chinese+english+math as total FROM students;

where 子句

截屏2021-12-22 下午11.04.57.png

order by 子句

order by 指定排序的列，排序的列既可以字段也可以是别名

select column from table
    order by column asc|desc;

SELECT name, (chinese+math+english) as total from students 
    ORDER BY total DESC;

统计函数

1.count

select count(*) | count(列名） from table_name;
- count(*) 返回满足条件的纪录的行数
- count(列）返回满足条件的某列有多少个，会排除为null情况

SELECT COUNT(*) from students WHERE chinese > 90;

2.sum 返回满足条件的行的和

# 统计数学分数的总和
SELECT sum(math) from students;
# 统计数学分数和英语分数的总和
SELECT sum(math) as math_t,sum(english) as en_t from students;
# 数学的平均分
SELECT sum(math)/count(math) from students;

3.avg

返回满足条件的平均分

4.max/min

返回满足条件一列的最大值和最小值

SELECT max(math) as math_m from students;

分组统计

使用group by 对列进行分组 使用having 子句对分组后的结果进行过滤

# 获取部门平均工资大于2000的
SELECT AVG(sal) as avg_s,deptno FROM emp 
    GROUP BY deptno 
    HAVING avg_s > 2000;

字符串相关的函数

截屏2021-12-27 下午11.47.51.png

# 链接字符串
SELECT CONCAT(ename, ' job is ', job) FROM emp;
# substing出现的位置
SELECT INSTR('hello word', 'word') FROM DUAL;
# 7
# 转换为小写
SELECT LCASE(job) from emp;
# sting的长度
SELECT LENGTH(job) from emp;
SELECT LENGTH('李云飞') FROM DUAL; # 9 按照字节返回
# 替换
SELECT REPLACE(job,'MAN', 'w') FROM emp;

数学相关的函数

截屏2021-12-28 上午12.04.03.png

# 绝对值
SELECT ABS(-10) FROM DUAL;
# 二进制
SELECT BIN(10) FROM DUAL;
# 向上取整
SELECT CEILING(-1.1) FROM DUAL;
# SELECT CONV(N,from_base,to_base)
SELECT CONV(18,16,2) # 11000
SELECT FORMAT(12.3446,2) FROM DUAL; # 12.34
SELECT FORMAT(12.3456,2) FROM DUAL; # 12.35
SELECT HEX(18) FROM DUAL;
SELECT MOD(10,3) FROM DUAL;
SELECT LEAST(19, 45) FROM DUAL;
SELECT RAND() FROM DUAL;

时间和日期相关的函数

截屏2021-12-28 上午12.15.42.png

SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP FROM DUAL;


CREATE TABLE mes(
    id INT , 
    content VARCHAR(30), 
    send_time DATETIME
);
INSERT INTO mes VALUES (1, '北京新闻', CURRENT_TIMESTAMP);
INSERT INTO mes VALUES (2, '北京新闻2', CURRENT_TIMESTAMP);
INSERT INTO mes VALUES (3, '北京新闻3', CURRENT_TIMESTAMP);
SELECT id, content, DATE(send_time) FROM mes;
-- 请查询在10分钟内发布的新闻
SELECT * 
	FROM mes
	WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW()

SELECT * 
	FROM mes
	WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE) 
-- 请在mysql 的sql语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL;
avatar
文章
阅读
粉丝