MySQL
2024-04-18 16:30:41 0 举报
AI智能生成
登录查看完整内容
MySQL
作者其他创作
大纲/内容
TINYINT 1字节
SMALLINT 2字节
MEDIUMINT 3字节
INT 4字节
BIGINT 8字节
整型
M:数字总位数
D:小数点后的位数
ps:M和D被省略则根据硬件 允许的来保存数值
浮点型
YEAR(1)
TIME(3)
DATE(3)
DATETIME(8)
TIMESTAMP(4)
日期时间型括号内为存储需求
CHAR(m) font color=\"#0076b3\
VARCHAR(m) font color=\"#0076b3\
TINYTEXT font color=\"#0076b3\
TEXT font color=\"#0076b3\
MEDIUMTEXT font color=\"#0076b3\
LONGTEXT font color=\"#0076b3\
字符型
数据类型
当插入记录时,如果没有明确字段赋值则自动赋予默认值。
例:font color=\"#662c90\
默认值 DEFAULT:
每张表只能存在一个主键
保证记录的唯一性
自动为 NOT NULL
主键约束 PRIMARY KEY:
每张表可以存在多个唯一约束
可以为 NULL
唯一约束 UNIQUE KEY:
保持数据一致性和参照完整性
实现一对一或一对多的关系
父表(被参照表)和子表(参照表)用相同的存储引擎,禁止使用临时表
引擎只能为InnoDB
外键列和参照列具有线割弄得数据类型(数字的长度或是否有符号位这些必须相同,字符长度可以不同)
外键列和参照列必须创建索引,外键列索引没有的话MySQL会自动创建
要求
修改引擎:在my.ini文件中改 default-strorage-engine=INNODB
CASCADE(级联):从父表删除/更新时自动删除/更新子表中匹配的行
SET NULL:从父表中删除/更新行,并设置子表外键为NULL(需要相应的子表列没有设置NOT NULL)
RESTRICT(拒绝):拒绝对父表进行删除/更新操作
NO ACTION(无动作):标准的SQL关键字,与RESRICT作用相同
例:在定义表时有这样一条语句(删除级联)FOREIGN KEY (pid) REFERENCES province (id) ON DELETE CASCADE
外键约束的参照操作
外键约束 FOREIGN KEYp
列级约束:对一个数据列建立的约束(可以在列定义时声明也可以在列定义后声明,如上面tb4的主键)
表级约束:对多个数据列建立的约束(只可以在列定义后声明)
表级/列级约束:
约束
定义:子查询(SubQuery)是指出现在其他SQL语句内的SLECT子句例:SELECT * FROM t1 WHERE col=(SELECT col2 FROM t2);
子查询嵌套在查询内部,且必须始终出现在圆括号内
子查询可以返回标量、一行、一列或子查询
备注
定义
operand comparison_operator ANY(subquery)
operand comparison_operator SOME(subquery)
operand comparison_operator ALL(subquery)
语法
返回值表
使用比较运算符的子查询
语法:operand comparison_operator [NOT] IN (subquery)
备注:“=ANY”运算符与 IN 等效 “!=ALL”或“<>ALL”运算符与 NOT IN 等效
使用 [NOT] IN的子查询
INSERT ... SELECT 将查询结果写入数据表:font color=\"#f15a23\
子查询
MySQL在SELECT语句,多表更新,多表删除语句中支持JOIN操作
语法(表的参照关系):table_reference {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} table_reference ON conditional_expr;p.s:JOIN=CROSS JOIN=INNER JOIN INNER表示内连接,LEFT/RIGHT OUTER表示左/右外连接例:UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate=cate_name SET goods_cate=cate_id;
ON 关键字设定连接条件,也可以用WHERE代替通常用ON设定连接条件,用WHERE进行结果记录的过滤例:查询所有商品的详细信息font color=\"#662c90\
内连接(INNER JOIN)A与B的交集
左外连接(LEFT JOIN):显示左表全部的记录以及右表符合条件的记录
右外连接(RIGHT JOIN):显示右表全部的记录以及左表符合条件的记录
外连接
多表连接(两张表以上的连接)参照内连接例子的写法
同一数据表对其自身进行连接(想象有相同的一张表当左表或右表进行多表连接)例:font color=\"#662c90\
自身连接(无限级分类表设计)
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将在符合连接条件的记录后停止搜索更多行
关于连接的说明
连接
字符函数
数值运算符及其函数
例:1)SELECT 5 BETWEEN 1 AND 22; =>1 #1表示truefont color=\"#662c90\
比较运算符及函数
日期时间函数
信息函数
例:SELECT AUG(goods_price) AS AUG_PRICE FROM tdb_goods;
聚合函数
例:1)SELECT MD5('admin'); 2)SET password=PASSWORD('dimitar'); #改密码
加密函数
一些内置函数和运算符
用户自定义函数(user-defined function,UDF)是一种对MySQL扩展的途径,其语法和内置函数类似
参数
返回值
两个必要条件
可以返回任意类型的值,同样也可以接收任意类型的参数
语法:CREATE FUNCTION function_nameRETURNS{STRING | INTERGER | REAL | DECIMAL}routine_body;
routine_body是函数体
函数体有合法的SQL语句构成
函数体可以是简单的SELECT或INSERT语句
函数体如果为复合结构则使用BEGIN ... END结构体
复合结构可以还包含声明、循环、控制结构
p.s
创建不带参数的自定义函数例:font color=\"#662c90\
创建带有参数的自定义函数例:font color=\"#662c90\
创建复合结构的自定义函数例:DELIMITER // CREATE FUNCTION adduser(username VARCHAR(20)) RETURNS INT UNSIGNED BEGIN INSERT test(username) VALUES(username); RETURN LAST_INSERT_ID(); END // DELIMITER ;
创建自定义函数
语法:DROP FUNCTION [IF EXISTS] function_name;
删除自定义函数
MySQL自定义函数
运算符和函数
net stop mysql
net start mysql
停止/启动MySQL服务
--delimiter=name 指定分隔符
--prompt=name 设置提示符
登录:mysql -uroot -p -P3306 -h
mysql> prompt \\u@\\h \\D>
p.s:\\D 完整日期 \\d 当前数据库 \\h 服务器名称 \\u 当前用户
修改提示符
举例
登录(参数)
exit;
quit;
\\q;
退出MySQL的三种方式
MySQL登录与退出
打开数据库:USE db_name;例:USE TEST;
创建数据库:CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name[DEFAULT] CHARACTER SET [=] charset_name;例:CERATE DATABASE t1;
查看当前服务器下的数据表列表:SHOW {DATABASE | SCHEMAS} [LIKE 'pattern' | WHERE expr];例:SHOW DATABASE;
修改数据库:ALTER {DATABASE | SCHEMA} [db_name][DEFAULT] CHARACTER SET [=] charset_name;
删除数据库:DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
操作数据库
简单的操作
定义:数据表(或表)是数据库的重要组成部分,为其他对象的基础
创建表:font color=\"#f15a23\
AUTO_INCREMENT:自动编号且须与主键(primary key)组合使用,默认起始为1,每次增1,自动编号可以不用赋值例:font color=\"#662c90\
NULL
NOT NULL
空值与非空
font color=\"#f15a23\
INSERT [INTO] tbl_name SET col_name={expr | DEFAULT};p.s:和上面的语句相比,这条语句可以使用子查询(SubQuery)例:font color=\"#662c90\
插入记录(INSERT):font color=\"#f15a23\
单表更新:font color=\"#f15a23\
多表更新:font color=\"#f15a23\
一步到位多表更新:创建数据表的同时将查询结果写入到数据表(CREATE ... SELECT)font color=\"#f15a23\
更新记录(UPDATE):
单表删除:DELETE FROM tbl_name [WHERE where_condition]; p.s:在where表达式中可以使用MySQL支持的函数或运算符例:DELETE FROM users WHERE id=6;
多表删除:font color=\"#f15a23\
删除记录(DELETE):
查询表达式每一个表示想要的一列,多列间用逗号分隔
“*”表示所有列,tbl_name.* 可以命名所有列
查询表达式可以用 [AS] alias_name 为其赋予别名
分组条件(HAVING):[HAVING where_condition];p.s:having后加聚合函数例:font color=\"#662c90\
对查询结果进行排序(ORDER BY):font color=\"#f15a23\
查询结果分组(GROUP BY):font color=\"#f15a23\
限制查询结果返回的数量(LIMIT):font color=\"#f15a23\
完整的select语句:font color=\"#f15a23\
查找(SELECT):font color=\"#f15a23\
数据表操作
说明:ALTER命令是对建立好的数据表进行修改
方法1:ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name;
方法2:RENAME TABLE tbl_name TO new_tbl_name;
数据表更名:
添加单列:ALTER TABLE tbl_name ADD [COLUMN] col_namecol_definition [FIRST | AFTER col_name];p.s:first为插入成为第一列;after col_name插入到指定的列后面例:ALTER TABLE user1 ADD age TINYINT UNSIGNED NOT NULL AFTER name;
添加多列(在现有的最后一列后面添加新列):font color=\"#f15a23\
添加列:
删除列:ALTER TABLE tbl_name DROP [COLUMN] col_name;例:font color=\"#662c90\
修改列定义:ALTER TABLE tbl_name MODIFY [COLUMN] col_namecolumn_definition [FIRST | AFTER col_name];例:ALTER TABLE user2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;
修改列名称(功能比修改列定义高一级):ALTER TABLE tbl_name CHANGE [COLUMN]old_col_namenew_col_name col_definition [FIRST | AFTER col_name];
修改列:
添加主键约束:ALTER TABLE tbl_name ADD [CONSTRAINT [symbol] ]PRIMARY KEY [index_type] (index_rol_name);例:ALTER TABLE user2 ADD CONSTRAINT PK_user2_id PRIMARY KEY(id);
添加唯一约束:font color=\"#f15a23\
添加外键约束:font color=\"#f15a23\
添加默认约束DEFAULT:ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal};例:ALTER TABLE user2 ALTER age SET DEFAULT 15;
添加约束:
删除主键约束:ALTER TABLE tbl_name DROP PRIMARY KEY;
删除唯一约束:ALTER TABLE tbl_name DROP {INDEX | KEY} index_name;例:ALTER TABLE user2 DROP INDEX name;
删除外键约束:ALTER TABLE tbl_name DROP FOREIGN KEY fk_name; p.s:可以用 'SHOW CREATE TABLE tbl_name' 命令查看外键名例:ALTER TABLE user2 DROP FOREIGN KEY user2_ibfk_1;
删除默认约束:ALTER TABLE tbl_name ALTER [COLUMN] col_name {DROP DEFAULT};
删除约束:
修改数据表(ALTER)
过程
存储过程(procedure)是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理,只在第一次进行语句分析和编译,之后调用结果即可
增强SQL语句的功能和灵活性
实现较快的执行速度
减少网络流量
特点
定义与特点
IN:输入input,该参数值在调用存储过程时指定例:DELIMITER // #修改分隔符为 // 免得和 ; 冲突 CREATE PROCEDURE removeUserById (IN D_id INT UNSIGNED) BEGIN DELETE FROM user WHERE id=D_id; END // DELIMITER ; #改回 ; 为分隔符 CALL removeUserById(3);
OUT:输出output,该参数值可以被存储过程改变且可以返回例:font color=\"#662c90\
局部变量:作用范围在begin和end之间,在语句块里面设置
用户变量:以“@”开始,形式为“@变量名”用户变量和MySQL客户端绑定,仅仅对当前使用客户端有效
set GLOBAL 变量名
set @@global.变量名
定义语法
对所有客户端生效,只有具有super权限才能设置该变量
全局变量
会话变量:只对连接的客户端生效
declare语句专门定义局部变量,set语句是设置不同类型的变量包括会话变量和全局变量
MySQL变量
INOUT:该参数在调用是指定且可以被改变和返回例:font color=\"#662c90\
创建存储过程:CREATE[DEFINER={user | CURRENT_USER}] (默认为当前用户)font color=\"#f15a23\
CALL sp_name [()];例:CALL sp1;
调用存储过程
插入表格截图
存储过程和函数的区别
SQL存储过程
MySQL可以将数据以不同技术存储在文件(内存)中,这种技术成为存储引擎每种存储引擎使用不同的存储机制、索引技巧、锁定水平,最终提供广泛且不同的功能
MyISAM
InnoDB
Memory
CSV
Archive
MySQL存储引擎分类
简介
并发控制:当多个连接对记录进行修改是保证数据的一致性和完整性 (例如A、B两人同时浏览一个商品,A要买走商品,而B也想买,怎样解决?用到锁系统)
共享锁(读锁):同时间段内,多个用户可读取同一资源,过程中数据不变化
排它锁(写锁):在任何时候只能有一个用户写入资源,当进行写锁是会阻塞其他读锁或写锁的操作
锁系统
表锁(开销最小)
行锁(开销最大)
锁颗粒(锁的粒度)
并发处理
事务:保证数据库的完整性,是数据库区别于文件系统的重要特征之一 例:A转200元给B,事务要实现 ①从A中减去200元(A的余额大于等于200);②B的账户中减去200元
原子性A
一致性C
隔离性I
持久性D
特征(ACID)
事物处理
外键:保证数据一致性的策略
索引:是对数据表中一列或者多列的值进行排序的一种结构(快速定位和访问表中信息)
外键和索引
修改MySQL配置文件my.ini实现:default-strorage-engine=engine例:default-strorage-engine=InnoDB
通过穿件数据表命令实现:CREATE TABLE tbl_name(... ...)ENGINE=engine;例:CREATE TABLE tb1(S1 VACHAR(10)) ENGINE=MyISAM;
通过修改数据表命令实现:ALTER TABLE tbl_name ENGINE [=] engine_name;例:ALTER TABLE tb1 ENGINE InnoDB;
修改存储引擎的几种方法
各种存储引擎的特点
MySQL存储引擎
MySQL
0 条评论
回复 删除
下一页