MySQL 学习笔记
1.数据类型 数值、日期/时间和字符串（字符）
类型 所占字节数 说明
tinyint 1 小整数值，如状态
smallint 2 大整数值
mediumint 3 大整数值
int 4 大整数值
bigint 8 极大整数值
float 4 单精度浮点数值
double 8 双精度浮点数值
decimal Max(D+, M+) 含小数值，例如金额
date 3 YYYY-MM-DD
time 3 HH:MM:SS
year 1 YYYY
datetime 8 YYYY-MM-DD HH:MM:SS
timestamp 8 YYYYMMDDHHMMSS
char 0~255 定长字段串
varchar 0~65535 变长字符串
text 0~65535 长文本数据
blob 二进制形式的文本数据
2.DDL Data Definition Language
#创建数据库
CREATE DATABASE 数据库名
#删除数据库
DROP DATABASE 数据库名
#查看mysql支持的存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
CREATE ［TEMPORARY］ TABLE [IF NOT EXISTS] table_name [(create_definition,...)][table_options][select_statement]
create table contacts(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30),
id_number varchar(18) unique comment ‘IDCARDNUMBER‘,
phone VARCHAR(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table sc(id int not null auto_increment primary key comment ‘主键id‘,
stu_no int not null comment ‘学号‘,
course varchar(30) comment ‘课程‘,
grade int comment ‘成绩‘,
foreign key(stu_no) references stu(stu_no));
alter TABLE contacts ADD sex VARCHAR(1);
alter TABLE contacts MODIFY sex tinyint;
alter table contacts DROP COLUMN sex;
DROP table contacts;
3. DML Data Manipulation Language
INSERT 插入单条数据：
INSERT INTO table_name(field1,field2,...,fieldN) VALUES (value1,value2,...,valueN);
INSERT 插入多条数据：
INSERT INTO table_name(field1,field2,...,fieldN) VALUES (valueA1,valueA2,...,valueAN),(valueB1,valueB2,...,valueBN),...,(valueN1,valueN2,...,valueNN);
注意事项：
1、如果字段是字符型，值必须使用单引号或者双引号，如”value”;如果值本身带单引号或双引号，需要转义
2、如果所有列都要添加数据，insert into语句可以不指定列，即
insert into contacts(name,sex,phone) values(‘tom\‘s cat‘,1,‘13312345678‘);
insert into contacts(name,sex,phone) values("Lily\‘s cat",1,‘13312345678‘);
UPDATE table_name set field1=newValue1,field2=newValue2 [where clause];
DELETE from table_name [where clause];
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| contacts |
+----------------+
1 row in set (0.00 sec)
mysql> desc contacts;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
| phone | varchar(11) | YES | | NULL | |
| sex | tinyint | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
4. DQL https://dev.mysql.com/doc/refman/8.0/en/select.html
SELECT column_name1, column_name2
FROM table_name
[WHERE where_condition]
[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
#SQL语句中使用where子句语法
SELECT column_name FROM table_name WHERE column_name 运算符 value
运算符 描述
= 等于
<> 或 != 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
between and 选取介于两个值之间的数据范围；在MySQL中，相当于>=并且<=
#and、or运算符语法
运算符 描述
and 表示左右两边的条件同时成立
or 表示左右两边只要有一个条件成立
SELECT column_name FROM table_name WHERE condition1 AND condition2 OR condition3
#where子句使用in语法
SELECT column_name FROM table_name WHERE column_name IN(value1, value2, …)
#where子句使用like语法
SELECT column_name FROM table_name WHERE column_name LIKE ‘%value%’
说明：
1、LIKE子句中的%类似于正则表达式中的*，匹配任意0个或多个字符
2、LIKE子句中的_匹配任意单个字符
3、LIKE子句中如果没有%和_，就相当于运算符=的效果
select * from employee where name like ‘李_‘ #李后面只有一个字符
select date_format(now(), ‘%Y%-%M-%D %H:%i:%s‘);
select ifnull(1/0,0);
select ifnull(1,0);
CASE WHEN [expr] THEN [result1]…ELSE [default] END
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
select id,name,
case sex when ‘‘ then ‘F‘
when ‘‘ then ‘M‘
end as sex,
salary
from employee;
SELECT * from contacts;
select sum(salary) from employee;
select max(salary) from employee;
select min(salary) from employee;
select avg(salary) from employee;
sum,avg,min,max 忽略空值
select count(*) from employee;
#order by语法
SELECT column_name1, column_name2 FROM table_name1, table_name2 ORDER BY column_name, column_name [ASC|DESC]
1. ASC表示按升序排列，DESC表示按降序排列。
2. 默认情况下，对列按升序排列。
#limit语法 约束要返回的记录数，通常使用LIMIT实现分页。
SELECT column_name1, column_name2 FROM table_name1, table_name2 LIMIT [offset,] row_count
1. offset指定要返回的第一行的偏移量。第一行的偏移量是0，而不是1。
2. row_count指定要返回的最大行数。
【经验分享】limit的分页公式：limit (page-1)*row_count, row_count
SELECT * from contacts limit 0, 5;
SELECT * from contacts limit 5, 10;
#group by语法
SELECT column_name, aggregate_function(column_name) FROM table_name
GROUP BY column_name
1. aggregate_function表示聚合函数。
2. group by可以对一列或多列进行分组。
#having语法 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以对分组后的各组数据进行筛选。
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value
GROUP BY column_name HAVING aggregate_function(column_name) operator value
#group_concat语法
group_concat配合group by一起使用，用于将某一列的值按指定的分隔符进行拼接，MySQL默认的分隔符为逗号。
group_concat([distinct] column_name [order by column_name asc/desc ] [separator ‘分隔符‘])
select dept,group_concat(name order by name desc separator ‘;‘) from employee group by dept;
#distinct语法
distinct用于在查询中返回列的唯一不同值（去重复），支持单列或多列
SELECT DISTINCT column_name, column_name FROM table_name;
#表连接语法
连接类型 定义 图示 例子
内连接 只连接匹配的行 A and B select A.c1, B.c2 from A join B on A.c3 = B.c3
左连接 包含左表的全部行（不管右表是否存在与之匹配的行），以及右表中全部匹配的行 Only A select A.c1, B.c2 from A left join B on A.c3 = B.c3
右连接 包含右表的全部行（不管左表是否存在与之匹配的行），以及左表中全部匹配的行 ONLY B select A.c1, B.c2 from A right join B on A.c3 = B.c3
全连接 包含左右两个表的全部行（不管在另一个表中是否存在与之匹配的行） A OR B select A.c1, B.c2 from A full join B on A.c3 = B.c3
交叉连接（cross join）：没有用where子句的交叉连接将产生笛卡尔积，第一个表的行数乘以第二个表的行数等于笛卡尔积和结果集的大小。
select A.stu_no,A.name,B.course,B.score from stu A join score B on (A.stu_no = B.stu_no)
select A.stu_no,A.name,B.course,B.score from stu A inner join score B on (A.stu_no = B.stu_no)
select A.stu_no,A.name,B.course,B.score from stu A ,score B where A.stu_no = B.stu_no
select A.stu_no,A.name,B.course,B.score from stu A left join score B on (A.stu_no = B.stu_no)
select A.stu_no,A.name,B.course,B.score from stu A right join score B on (A.stu_no = B.stu_no)
select A.stu_no,A.name,B.course,B.score from stu A ,score B
#自连接语法
自连接是一种特殊的表连接，它是指相互连接的表在物理上同为一张表，但是逻辑上是多张表。自连接通常用于表中的数据有层次结构，如区域表、菜单表、商品分类表等。
SELECT A.column, B.column FROM table A, table B WHERE A.column = B.column;
SELECT A.id,A.name,B.name as province from area A, area B where A.pid = B.id and A.pid<>0;
#where子句使用in语法
SELECT column_name FROM table_name WHERE column_name IN(value1, value2, …)
SELECT column_name FROM table_name WHERE column_name IN(SELECT column_name FROM table_name [WHERE]);
#where子句使用exists语法
SELECT column_name1 FROM table_name1 WHERE EXISTS (SELECT * FROM table_name2 WHERE condition);
)
5.MySQL内置函数
函数 描述
数学函数如 ABS、SQRT、MOD、SIN、COS、TAN、COT等
字符串函数 如LENGTH、LOWER、UPPER、TRIM、SUBSTRING等
日期和时间函数 如NOW、CURDATE、CURTIME、SYSDATE、DATE_FORMAT、YEAR、MONTH、WEEK等
聚合函数 COUNT、SUM、AVG、MIN、MAX
条件判断函数 IF、IFNULL、CASE WHEN等
系统信息函数 VERSION、DATABASE、USER等
加密函数 MD5、SHA1、SHA2等
函数now() 用于返回当前的日期和时间。
date_format() 用于以指定的格式显示日期/时间。 select name, date_format(birthday, ‘%Y/%m/%d‘) from user;
聚合函数
函数 描述
count 返回符合条件的记录总数
sum 返回指定列的总和，忽略空值
avg 返回指定列的平均值，忽略空值
min 返回指定列的最小值，忽略空值
max 返回指定列的最大值，忽略空值
函数ifnull()用于处理NULL值。 ifnull(v1,v2)，如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。
case when是流程控制语句，可以在SQL语句中使用case when来获取更加准确和直接的结果。
SQL中的case when类似于编程语言中的if else或者switch。
#case when的语法有2种
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
CASE WHEN [expr] THEN [result1]…ELSE [default] END