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.索引
是对数据表中一列或多列的值进行排序的一种结构