MySql 笔记

1. 查看mysql的版本

select version();

2.创建数据库

create {database | schema} [if not exists] db_name [default] character set [=] charset_name

3. 修改数据库

alter {database | schema} [db_name] [default] character set [=] charset_name

4. 删除数据库

drop {database | schema| [if exists] db_name

5. Mysql 数据类型

(1)整型

数据类型	存储范围	字节
TINYINT	有符号值：-128～127（-2^7 ~2^7-1) 无符号值：0～255（0～2^8-1)	1
SMALLINT	有符号值：-32768～32767（-2^15~2^15-1) 无符号值：0～65535（0～2^15-1)	2
MEDIUMINT	有符号值：-8388698~8388607(-2^23~2^23-1) 无符号值：0~16777215(0~2^24-1)	3
INT	有符号值：-2147483648~2147483674(-2^31~2^31-1) 无符号值：0~4294967295(0~2^32-1)	4
BIGINT	有符号值：-9223372036854775808~9223373036854775807(-2^63~2^63-1) 无符号值：0~18446744073709551615(0~2^64-1)	8

（2）浮点型

数据类型	存储范围
FLOAT[(M,D)]	-3.402823466E+38到-1.175494351E-38、0和1.175494351E-38到3.402823466E+38 M是数字总位数，D是小数点后面的位数。如果M和D被省略，根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数位。
DOUBLE[(M,D)]	-1.7976931348623157E+308到-2.2250738585072014E-308、0和2.2250738585072014E-308到1.7976931348623157E+308

（3）日期时间类型

日期类型	存储需求
YEAR	1
TIME	3
DATE	3
DATETIME	8
TIMESTAMP	4

(4)字符型

列类型	存储需求
CHAR(M)	M个字节，0<=M<=255
VARCHAR(M)	L+1个字节，其中L<=M 且 0<=M<=65535
TINYTEXT	L+1个字节，其中L<2^8
TEXT	L+2个字节，其中L<2^16
MEDIUMTEXT	L+3个字节，其中L<2^24
LONGTEXT	L+4个字节，其中L<2^32
ENUM('value1','value2',...)	1或2个字节，取决于枚举值的个数（最多65535个值）
SET('value1','value2',...)	1、2、3、4或者8个字节，取决于set成员的数目（最多64个成员）

6.创建数据表

create table [if not exists] table_name (column_name data_type,....)

7.SHOW TABLES

查看数据表列表

show tables [FROM db_name] [LIKE 'pattern' | WHERE expr]

8.SHOW COLUMNS

查看数据表结构

SHOW COLUMNS FROM tb_name

9.插入记录 INSERT

INSERT [INTO] table_name [(col_name,...)] values(value,...)

方式一：INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

方式二：INSERT [INTO] tbl_name set col_name = {expr | DEFAULT},...

与第一种方式的区别：此方法可以使用子查询(subQuery)

方式三：INSERT [INTO] tbl_name [col_name,...)] SELECT ...

10.记录查找

SELECT expr,... FROM tb_name

查找记录

SELECT select_expr [,select_expr ...]

[

FROM table_references

[WHERE where_condition]

[GROUP BY {col_name | position} [ASC | DESC],...]

[HAVING where_condition]

[ORDER BY {col_name | expr | position} [ASC | DESC],...]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

]

查询表达式（select_expr）

每一个表达式表示想要的一列，必须有至少一个。

多个列之间以英文逗号分隔。

星号（*）表示所有列。tbl_name.*可以表示命名表的所有列。

查询表达式可以使用[AS] alias_name为其赋予别名。

别名可用于GROUP BY, ORDER BY 或 HAVING子句。

11.空值与非空

NULL，字段值可以为空

NOT NULL， 字段值禁止为空

12.AUTO_INCREMENT

自动编号，且必须与主键组合使用

默认情况下，起始值为1，每次的增量为1

13.PRIMARY KEY

主键约束

每张数据表只能存在一个主键

主键保证记录的唯一性

主键自动为NOT NULL

14.UNIQUE KEY

唯一约束

唯一约束可以保证记录的唯一性

唯一约束的字段可以为空值（NULL）

每张数据表可以存在多个唯一约束

15.DEFAULT

默认值

当插入记录时，如果没有明确为字段赋值，则自动赋予默认值

16.约束

（1）约束保证数据的完整性和一致性

（2）约束分为表级约束和列级约束

（3）约束类型包括：

NOT NULL（非空约束）

PRIMARY KEY（主键约束）

UNIQUE KEY（唯一约束）

DEFAULT（默认约束）

FOREIGN KEY（外键约束）

17.FOREIGN KEY

保证数据一致性，完整性。

实现一对一或者一对多关系

18.外键约束的要求

（1）父表和子表必须使用相同的存储引擎，而且禁止使用临时表。

（2）数据表的存储引擎只能为InnoDB。

（3）外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。

（4）外键列和参照列必须创建索引。如果外键列不存在索引的话，MySql将自动创建索引。

19.编辑数据表的默认存储引擎

mysql的配置文件

default-storage-engine=INNODB

20.外键约束的参照标准

（1）CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行

（2）SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL

（3）RESTRICT：拒绝对父表的删除或更新操作

（4）NO ACTION：标准SQL的关键字，在MySqL中与RESTTRICT相同

21.表级约束与列级约束

对一个数据列建立的约束，称为列级约束。

对多个数据列建立的约束，称为表级约束。

列级约束既可以在列定义时声明，也可以在列定义后声明。

表级约束只能在列定义后声明。

22.修改数据表

添加单列

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]

添加多列

ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,...)

删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name

添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,..)

添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name,...)

添加外键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symboll]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

添加/删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

删除主键约束

ALTER TABLE tbl_name DROP PRIMARY KEY

删除唯一约束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

修改列定义

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

修改列名称

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]

数据表更名

方法一：ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name

方法二：RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]...

23.UPDATE

更新记录（单表更新）

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [,col_name2={expr2|DEFAULT}]... [WHERE where_condition]

24.DELETE

删除记录（单表删除）

DELETE FROM tbl_name [WHERE where_condition]

25.WHERE

条件表达式

对记录进行过滤，如果没有指定WHERE字句，则显示所有记录

对WHERE表达式中，可以使用MySql支持的函数或运算符

26.GROUP BY

查询结果分组

[GROUP BY {col_name | position} [ASC | DESC],...]

27.HAVING

分组条件

[HAVING where_condition]

28.ORDER BY

对查询结果进行排序

[ORDER BY {col_name | expr | position} [ASC | DESC],...]

29.LIMIT

限制查询结果返回的数量

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

30.子查询

子查询（Subquery）是指出现在其他sql语句内的select字句

例如：

select * from t1 where col1= (select col2 from t2);

其中select * from t1,称为Outer Query/Outer Statement

select col2 from t2, 称为SubQuery

子查询指嵌套在查询内部，且必须始终出现在圆括号内。

子查询可以包含多个关键字或条件，

如DISTINCT、GROUP BY、ORDER BY、LIMIT函数等。

子查询的外层查询可以是：SELECT、INSERT、UPDATE、SET或DO。

子查询可以返回标量、一行、一列或子查询。

使用比较运算符的子查询：=、>、<、>=、<=、<>、!=、<=>

语法结构：operand comparison_operator subquery

用ANY、SOME或ALL修饰的比较运算符

operand comparison_operator ANY (subquery)

operand comparison_operator SOME (subquery)

operand comparison_operator ALL (subquery)

31.ANY、SOME、ALL

	ANY	SOME	ALL
>、>=	最小值	最小值	最大值
<、<=	最大值	最大值	最小值
=	任意值	任意值
<>、!=			任意值

32.使用[ NOT ] IN的子查询

operand comparison_operator [NOT] IN (subquery)

= ANY 运算符与IN等效

!= ALL 或 <>ALL运算符与NOT IN等效

33.使用[NOT] EXISTS的子查询

如果子查询返回任何行，EXISTS将返回TRUE；否则为FALSE

34.INSERT ...... SELECT

将查询结果写入数据表

INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

35.多表的更新

UPDATE table_references

SET col_name1={expr1 | DEFAULT}

[,col_name2={expr2 | DEFAULT}]...

[WHERE where_condition]

36.连接类型

INNER JOIN，内连接

在MySql中，JOIN、CROSS JOIN和INNER JOIN是等价的。

LEFT [OUTER] JOIN，左外连接

RIGHT [OUTER] JOIN，右外连接

语法结构

table_regerence

{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}

table_reference

ON conditional_expr

37.CREATE ... SELECT

创建数据表同时将查询结果写入到数据表

CREATE TABLE [IF NOT EXISTS] tbl_name

[(create_definition,...)]

select_statement

38.连接

MySql在SELECT语句、多表更新、多表删除语句中支持JOIN操作

数据表参照

table_reference

tbl_name [[AS] alias | table_subquery [AS] alias

数据表可以使用tbl_name AS alias_name 或tbl_name alias_name 赋予别名。

table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名

39.连接条件

使用ON关键字来设定连接条件，也可以使用WHERE来代替

通常使用ON关键字来设定连接条件。

使用WHERE关键字进行结果集记录的过滤。

40.内连接

显示左表及右表符合连接条件的记录

41.外连接

左外连接

显示左表的全部记录及右表符合连接条件的记录

右外连接

显示右表的全部及左表符合连接条件的记录

A LEFT JOIN B join_condition

数据表B的结果集依赖数据表A

数据表A的结果集根据左连接条件依赖所有数据表（B表除外）

左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）

如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行

如果使用内连接查找的记录在数据表中不存在，并且在WHERE子句中尝试以下操作：col_name IS NULL时，如果col_name被定义为NOT NULL，MySql将在找到符合连接条件的记录后停止搜索更多的行。

42.自身连接

同一个数据表对其自身进行连接

43.多表删除

DELETE tbl_name[.*] [,tbl_name[.*]]...

FROM table_references

[WHERE where_condition]

44.字符函数

函数名称	描述
CONCAT()	字符连接
CONCAT_WS()	使用指定的分隔符进行字符连接
FORMAT()	数字格式化
LOWER()	转换成小写字母
UPPER()	转换成大学字母
LEFT()	获取左侧字符
RIGHT()	获取右侧字符
LENGTH()	获取字符串长度
LTRIM()	删除前导空格
RTRIM()	删除后续空格
TRIM()	删除前导和后续空格
SUBSTRING()	字符串截取
[NOT] LIKE	模糊匹配
REPLACE()	字符串替换

45.数值运算符与函数

名称	描述
CEIL()	进一取整
DIV	整数除法
FLOOR()	舍一取整
MOD()	取余数（取模）
POWER()	幂运算
ROUND()	四舍五入
TRUNCATE()	数字截取
[NOT] BETWEEN ... AND ...	[不]在范围之内
[NOT] IN()	[不]在列出值范围内
IS [NOT] NULL	[不]为空

46.日期函数

名称	名称
NOW()	当前日期和时间
CURDATE()	当前日期
CURTIME()	当前时间
DATE_ADD()	日期变化
DATEDIFF()	日期差值
DATE_FORMAT()	日期格式化

47.信息函数

名称	描述
CONNECTION_ID()	连接ID
DATEBASE()	当前数据库
LAST_INSERT_ID()	最后插入记录的ID号
USER()	当前用户
VERSION()	版本信息

48.聚合函数

名称	描述
AVG()	平均值
COUNT()	计数
MAX()	最大值
MIN()	最小值
SUM()	求和

49.加密函数

名称	描述
MD5()	信息摘要算法
PASSWORD()	密码算法/5.7.9以后废弃

50.自定义函数

用户自定义函数(user-defined function, UDF)是一种对MySql扩展的途径，其用法与内置函数相同。

自定义函数的两个必要条件

（1）参数

（2）返回值

函数可以返回任意类型的值，同样可以接收这些类型的参数。

创建自定义函数

CREATE FUNCTION function_name

RETURNS

{STRING | INTEGER | REAL | DECIMAL}

routine_body

关于函数体

（1）函数体由合法的SQL语句构成

（2）函数体可以是最简单的SELECT或INSERT语句；

（3）函数体如果为复合结构则使用BEGIN...END语句；

（4）复合结构可以包含声明，循环，控制结构。

删除函数

DROP FUNCTION [IF EXISTS] function_name

51.存储过程

SQL命令---->MySql引擎--分析-->语法正确---->可识别命令--执行-->执行结果--返回-->客户端

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。

存储过程的优点

（1）增强SQL语句的功能和灵活性

（2）实现较快的执行速度

（3）减少网络流量

52.创建存储过程

CREATE

[DEFINER = {user | CURRENT_USER}]

PROCEDURE sp_name ([proc_parameter[,...]])

[characteristic ...] routine_body

 

proc_parameter: [IN | OUT | INOUT] param_name type

参数

（1）IN：表示该参数的值必须在调用存储过程时指定

（2）OUT：表示该参数的值可以被存储过程改变，并且可以返回

（3）INOUT：表示该参数的调用时指定，并且可以被改变和返回

特性

（1）COMMENT：注释

（2）CONTAINS SQL：包含SQL语句，但不包含读或写数据的语句

（3）NO SQL：不包含SQL语句

（4）READS SQL DATA：包含读数据的语句

（5）MODIFIES SQL DATA：包含写数据的语句

（6）SQL SECURITY {DEFINER | INVOKER}指明谁有权限来执行

过程体

（1）过程体由合法的SQL语句构成

（2）过程体可以是任意SQL语句

（3）过程体如果为复合结构则使用BEGIN...END语句

（4）复合结构可以包含声明，循环，控制结构

调用存储过程

（1）CALL sp_name([parameter[,...]])

（2）CALL sp_name[()]

修改存储过程

ALTER PROCEDURE sp_name [characteristic ...]

COMMENT 'string '

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER}

删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name

53.存储过程与自定义函数的区别

（1）存储过程实现的功能要复杂一些；而函数的针对性更强

（2）存储过程可以返回多个值；函数只能有一个返回值

（3）存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分来出现。

54.Mysql存储引擎

MySql可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎。

每一种存储引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能。

Mysql支持的存储引擎

（1）MyISAM

（2）InnoDB

（3）Memory

（4）CSV

（5）Archive

各种存储引擎的特点

特点	MyISAM	InnoDB	Memory	Archive
存储限制	256TB	64TB	有	无
事务安全		支持
支持索引	支持	支持	支持
锁颗粒	表锁	行锁	表锁	行锁
数据压缩	支持			支持
支持外键		支持

55.并发控制

当多个连接对记录进行修改时保证数据的一致性和完整性。

56.锁

（1）共享锁（读锁）

在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化

（2）排它锁（写锁）

在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。

57.锁颗粒

表锁：是一种开销最小的锁策略

行锁：是一种开销最大的锁策略

58.事务

事务用于保证数据库的完整性。

事务的特性

（1）原子性（Atomicity）

（2）一致性（Consistency)

（3）隔离性（Isolation）

（4）持久性（Durability)

59.外键

是保证数据一致性的策略

60.索引

是对数据表中一列或多列的值进行排序的一种结构

 

转载于:https://www.cnblogs.com/xiaobaixie/p/11240722.html