MySQL学习笔记（三）

目录

MySQL学习笔记（三）

一.数据操作

1.添加记录

  • INSERT [INTO] tbl_name[(字段名称,...)] {VALUE|VALUES}(VALUES...);
  • INSERT tbl_name VALUE(value...);

不指定字段名称，需要按照建表时的字段顺序给每一个字段赋值

  • INSERT tbl_name(字段名称,...) VALUES(值,...);

指定字段名称不写字段名称的按默认值（自增值）插入

  • INSERT tbl_name SET 字段名称=值,...;
  • INSERT tbl_name[(字段名称...)] SELECT 字段名称,... FROM tbl_name [WHERE 条件];
  • INSERT tbl_name[(字段名称,...)] VALUES(值,...),(值,...),(值,...),...;

一次插入多条数据

2.修改记录

  • UPDATE tbl_name SET 字段名称=值,字段名称=值 [WHERE 条件];

如果不添加条件，整个表中的记录都会被更新

3.删除记录

  • DELETE FROM tbl_name [WHERE 条件];

如果不添加条件，表中所有记录都会被删除
DELETE 清空数据表的时候不会重置AUTO_INCREMENT的值，可以通过ALTER 语句将其重置为1

  • TRUNCATE [TABLE] tbl_name;

清除表中所有记录
会重置AUTO_INCREMENT的值

4.单表查询记录

  • SELECT select_expr,... FROM tbl_name
    [WHERE 条件]
    [GROUP BY {col_name|position} HAVING 二次筛选]
    [ORDER BY {col_name|position|expr} [ASC|DESC]]
    [LIMIT 限制结果集的显示条数];
  • SELECT * FROM tbl_name;

查询表中所有记录

  • SELECT 字段名称,... FROM tbl_name;

查询指定字段的信息

  • SELECT DISTINCT (字段名称) FROM tbl_name;

查询时过滤结果中重复的值

  • SELECT 字段名称,... FROM db_name.tbl_name;

以库名.表名的形式查询某个表

  • SELECT 字段名称 [AS] 别名名称,... FROM db_name.tbl_name;

显示查询记录时给字段起别名

  • SELECT 字段名称 ,... FROM tbl_name [AS] 别名;

显示查询记录时给表起别名

  • SELECT tbl_name.col_name,... FROM tbl_name;

以表名.字段名的形式查询某个字段

使用 WHERE 条件

根据条件选择特定记录

  • 比较运算符> >= < <= != <> <=>

<=> 和 =的区别：<=>可以检测NULL值

  • IS [NOT] NULL

检测值是否为NULL或者NOT NULL

  • [NOT] BETWEEN ... AND

指定范围

  • [NOT] IN(值,...)

指定集合

  • 逻辑运算符
  1. AND 逻辑与
  2. OR 逻辑或
  • 匹配字符
  1. [NOT] LIKE
  2. % 任意长度的字符串
  3. _ 任意一个字符

使用 GROUP BY 分组

把值相同放到一个组中，最终查询出的结果只会显示组中一条记录

  • GROUP_CONCAT(字段名称)

配合查看组中某个字段的详细信息

  • 聚合函数
  1. COUNT({字段名称|*})

统计记录总数
如果写的是COUNT(字段名称)，字段中的值为NULL，不统计进来
写COUNT(*)会统计NULL值

  1. SUM(字段名称) 求和
  2. MAX(字段名称) 最大值
  3. MIN(字段名称) 最小值
  4. AVG(字段名称) 平均值
  • WITH ROLLUP

会在记录末尾添加一条记录，是上面所有记录的总和

  • WITH+聚合函数

对分组结果进行二次筛选

使用 ORDER BY 排序

根据特定规则进行排序

  • ORDER BY 字段名称 [ASC|DESC],字段名称 [ASC|DESC],...

ASC和默认不写均为升序
DESC为降序
可以按顺序进行多个字段的排序

  • ORDER BY RAND()

随机排序

使用 LIMIT 限制结果集显示条数

  • LIMIT [offset],row_count

从offset开始，显示几条记录，不写offset时默认从0开始
配合升序降序可以实现翻页效果

5.多表联查

  • 笛卡尔积
    SELECT tbl_name.字段名称,tbl_name.字段名称,...FROM tbl_name1,tbl_name2,...
  • 内连接
    SELECT 字段名称,... FROM tbl_name1
    INNER JOIN tbl_name2
    ON 连接条件;

查询两个表中符合连接条件的记录

  • 外连接
  1. 左外连接
    SELECT 字段名称,... FROM tbl_name1
    LEFT [OUTER] JOIN tbl_name2
    ON 条件;

先显示左表中的全部记录，再去右表中查询符合条件的记录，不符合的以NULL代替

  1. 右外链接
    SELECT 字段名称,... FROM tbl_name1
    RIGHT [OUTER] JOIN tbl_name2
    ON 条件;

先显示右表中的全部记录，再去左表中查询符合条件的记录，不符合的以NULL代替

  • 自身连接查询

可以实现无限分类

6.外键约束

只有InnoDB存储引擎支持外键

  • [CONSTRAINT 外键名称]FOREIGN KEY(字段名称) REFERENCES 主表(字段名称)

子表的外键字段和主表的主键字段类型要相似；如果是数值型要求一致，并且无符号也要一致；如果是字符型，要求类型一致，长度可以不同
如果外键字段没有创建索引，MySQL会自动帮我们添加索引
子表的外键关联的必须是父表的主键

  • 动态添加外键
    ALTER TABLE tbl_name ADD [CONSTRAINT 外键名称] FOREIGN KEY(外键字段) REFERENCES 主表(主键字段);

动态添加外键之前表中的记录一定合法的记录，没有脏值，否则外键添加不成功

  • 动态删除外键
    ALTER TABLE tbl_name
    DROP FOREIGN KEY fk_name;
  • 外键约束的参照操作
  1. CASCADE

从父表删除或更新，子表也跟着删除或者更新，级联操作

  1. SET NULL

从父表删除或者更新记录，并设置子表的外键列为NULL

  1. NO ACTION | RESTRICT

拒绝对父表做更新或者删除操作

7.子查询

内层语句查询的结果可以做为外层语句查询的条件

  • SELECT 字段名称 FROM tbl_name WHERE col_name=(SELECT col_name FROM tbl_name);
  • 由IN引发的子查询
    SELECT 字段名称 FROM tbl_name WHERE col_name IN (SELECT col_name FROM tbl_name);
  • 由比较运算符引发的子查询
    SELECT 字段名称 FROM tbl_name WHERE col_name>=[ANY](SELECT col_name FROM tbl_name);
  • 运算符 ANY SOME ALL
    >、>= 最小值 最小值 最大值
    <、<= 最大值 最大值 最小值
    = 任意值 任意值
    <>、!= 任意值
  • 由EXISTS引发的子查询
    SELECT 字段名称 FROM tbl_name WHERE col_name EXISTS (SELECT col_name FROM tbl_name);

内层语句查询存在时执行外层查询语句

  • INSERT ... SELECT(...)

将查询出的数据插入到表中

  • CREATE ... SELECT(...)

将查询出的数据插入到新创建的表中

8.联合查询

将查询出的值整合

  • SELECT 字段名称, ... FROM tbl_name1
    UNION
    SELECT 字段名称, ... FROM tbl_name2;
  • SELECT 字段名称,... FROM tbl_name1
    UNION ALL
    SELECT 字段名称... FROM tbl_name2;

UNION ALL是简单的合并，UNION会去掉表中重复记录

二、常用函数

数学函数

1.进一取整

CEIL()

2.舍掉小数部分

FLOOR()

3.四舍五入

ROUND()

4.截取小数点后几位

TRUNCATE()

5.取余数

MOD()

6.取绝对值

ABS()

7.幂运算

POWER()

8.圆周率

PI()

9.0~1之间的随机数

RAND() | RAND(X)

10.得到数字符号

SIGN(X)

11.计算e的x次方

EXP(X)

字符串函数

1.字符串的字符数

CHAR_LENGTH('char')

2.字符串的字节数

LENGTH('char')

UTF-8下一个中文字符占3个字节

3.将字符串拼接成一个整体

CONCAT('char1','char2')

如果字符串中包含NULL，拼接结果就为NULL

4.以指定分隔符拼接字符串

CONCAT_WS('-','char1','char2',...)

分隔符为NULL时，结果为NULL;拼接字符包含NULL，NULL不显示

5.将字符串转换为大写

UPPER() | UCASE()

6.将字符串转换为小写

LOWER() | LCASE()

7.反转字符串

REVERSE()

8.返回字符串的前几个字符或者后几个字符

LEFT('char',int) | RIGHT('char',int)

9.用字符串填充到指定长度

LPAD() | RPAD(原字符串,指定长度,填充字符)

10.去除字符串两端的空格符

TRIM() | LTRIM() | RTRIM()

11.重复指定的字符串达到s个

REPEAT(s)

12.替换字符串

REPLACE(需替换的字符串,需替换的子字符串,替换值)

13.截取字符串

SUBSTRING(字符串,fromIndex,toIndex)

14.比较字符串

STRCMP(字符串1,字符串2)

相等时返回0；前者较小时，返回-1；前者较大时，返回1

日期时间函数

1.获取当前日期

CURDATE() | CURRENT_DATE()

2.获取当前时间

CURTIME() | CURRENT_TIME()

3.获取当前日期时间

NOW() | CURRENT_TIMESTAMP() | SYSDATE()

4.获取日期中的月份

MONTH('1970-01-01')

5.获取月份的名称

MONTHNAME(CURDATE())

6.获取星期几

DAYNAME(日期)

7.获取一周内的第几天

DAYOFWEEK(日期)

8.获取一年内的第几周

WEEK()

9.获取年份 天数 小时数 分钟数 秒数

YEAR() DAY() HOUR() MINIUTE() SECOND()

10.计算两个日期的相差天数(前-后)

DATEDIFF(日期1，日期2)

11.以指定符号分隔日期

DATE_FORMAT(日期,'%Y/%m/%d')

%m,%d需小写，大写时显示的是月份，天数名称

其它函数

1.Mysql版本

VERSION()

2.服务器连接ID

CONNECTION_ID()

3.当前数据库

DATABASE() | SCHEMA()

4.当前用户

USER() | CURRENT_USER() | SYSTEM_USER() | SESSION_USER()

5.获取上一步AUTO_INCREMENT的值

LAST_INSERT_ID()

6.加密字符串

MD5(字符串)

返回32位的字符串

7.mysql默认用户密码加密算法

PASSWORD()

posted @ 2020-01-14 21:39  还没有女朋友的执念  阅读(141)  评论(0)  编辑  收藏  举报