盘点MySQL数据库的数据类型、库和表常见操作、索引、视图、函数等知识点
发布时间：2025-11-04 23:34:54 来源：创站工坊 作者：人工智能
[[426046]]
前言
在日常开发中，盘点存储数据的数索引视图识点最常用的方式便是数据库了，其中最为著名的据库据类便是MySQL数据库，因它简便易于上手而且可扩展性强大，数等知跨平台使得它广为使用。型库上一篇文章，和表函数我们讲到了它的常见操作安装，今天我们就来具体聊聊它的盘点这篇文章分为11个部分，分别包括MySQL数据库的数索引视图识点数据类型、库和表常见操作、据库据类索引、数等知视图、型库函数、和表函数游标、常见操作触发器、盘点存储过程、事务、备份与还原、用户账号、其它等知识点。
一、mysql 数据类型
1.数值类型
7MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、服务器托管REAL和DOUBLE PRECISION)。
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
类型 大小 范围（有符号） 范围（无符号） 用途 TINYINT 1 字节 (-128，127) (0，255) 小整数值 SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值 MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值 INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值 BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值 FLOAT 4 字节 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，网站模板3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值 DOUBLE 8 字节 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值 DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值2.日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性，将在后面描述。
类型 大小(*字节)** 范围 格式 用途 DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值 TIME 3 -838:59:59/838:59:59 HH:MM:SS 时间值或持续时间 YEAR 1 1901/2155 YYYY 年份值 DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值 TIMESTAMP 4 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值，时间戳3.字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型 大小 用途 CHAR 0-255字节 定长字符串 VARCHAR 0-65535 字节 变长字符串 TINYBLOB 0-255字节 不超过 255 个字符的亿华云计算二进制字符串 TINYTEXT 0-255字节 短文本字符串 BLOB 0-65 535字节 二进制形式的长文本数据 TEXT 0-65 535字节 长文本数据 MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据 MEDIUMTEXT 0-16 777 215字节 中等长度文本数据 LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据 LONGTEXT 0-4 294 967 295字节 极大文本数据CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。
Enum(fds,fsa,fasf) ：枚举类型
set(val1,val2,val3)：集合类型
二、库表操作
1.数据库操作
复制数据库: SHOW DATABASES; 创建数据库: CREATEDATABASE IF NOT EXISTS people; 切换数据库: USE people; 删除数据库: DROPDATABASE IF EXISTS people; 查看当前数据库库信息: SHOW CREATEDATABASE people; 修改数据库的选项信息: ALTERDATABASE people; 1.2.3.4.5.6.2.数据表操作
复制显示数据库里所有数据表的信息: SHOW TABLE STATUS FROM people; 显示全部数据表: SHOW TABLES; 单张表：show tables from df 清空数据表: TRUNCATE df; 表检测: CHECKTABLE df; 表优化: OPTIMIZE TABLE df; 表修复: REPAIR TABLE df; 表分析: ANALYZE TABLE df; 分析表 键状态是否正确: ANALYZE TABLE orders; 检查表是否存在错误: checkTABLE orders,orderitems QUICK;# QUICK只进行快速扫描 优化表OPTIMIZE TABLE,消除删除和更新造成的磁盘碎片,从而减少空间的浪费:OPTIMIZE TABLE orders; 查询表结构: DESC df;DESCRIBE df; EXPLAIN df;SHOW COLUMNS FROM df; 复制表: CREATETABLE de LIKE df; SELECT * INTOINhwFROM df; 修改表名: RENAME TABLE de TO people.dh;（可将表移动到另一个数据库） 修改表字段: ALTERTABLE df ADD/DROP/CHANGE 拼接字段:SELECT CONCAT(us,(,tim,)) FROM df ORDERBY us ASC;result=>us(tim) 添加主键约束：alterTABLE 表名 ADDCONSTRAINT 主键 （形如：PK_表名） PRIMARYKEY 表名(主键字段); 添加外键约束：alterTABLE 从表 ADDCONSTRAINT 外键（形如：FK_从表_主表） FOREIGNKEY 从表(外键字段) REFERENCES 主表(主键字段); 删除主键约束：alterTABLE 表名 DROPPRIMARYKEY; 删除外键约束：alterTABLE 表名 DROPFOREIGNKEY 外键（区分大小写）; -- 添加外键约束 CREATETABLE stu(sid INTPRIMARYKEY,NAMEVARCHAR(50) NOTNULL); -- 添加外键约束方式一 CREATETABLE score1(score DOUBLE,sid INT,CONSTRAINT fk_stu_score1_sid FOREIGNKEY(sid) REFERENCES stu(sid)); -- 添加外键约束方式二(若表已存在，可用这种) CREATETABLE score1(score DOUBLE,sid INT); ALTERTABLE score1 ADDCONSTRAINT fk_sid FOREIGNKEY(sid) REFERENCES stu(sid) 1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.三、索引
复制CREATEUNIQUEINDEX qw ON df(us); #创建不重复索引 ALTERTABLE df ADDUNIQUEINDEX wq(id); #添加索引 SHOW INDEXFROM df;#检索索引 DROPINDEX qw ON people.df; #删除索引 ALTERTABLE df DROPINDEX wq; #删除索引 1.2.3.4.5.四、视图
复制CREATEVIEW shitu ASSELECT us FROM df; 创建视图 ALTERVIEW shitu ASSELECT us FROM df WHERE us=gf; 修改视图 SELECT * FROM shitu; 查看视图结果 DROPVIEW IF EXISTS shitu;删除视图 1.2.3.4.五、函数
复制文本处理函数 ASCII(CHAR)返回字符的ASCII码值 BIT_LENGTH(str)返回字符串的比特长度 CONCAT(s1,s2...,sn)将s1,s2...,sn连接成字符串 CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔 INSERT(str,X,Y,INSTR) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果 FIND_IN_SET(str,LIST)分析逗号分隔的list列表，如果发现str，返回str在list中的位置 LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果 LEFT(str,X)返回字符串str中最左边的x个字符 LENGTH(s)返回字符串str中的字符数 LOCATE(str) 找出str串的一个子串 LOWER(str) 将str串转换为小写 LTRIM(str) 从字符串str中切掉开头的空格 POSITION(SUBSTR,str) 返回子串substr在字符串str中第一次出现的位置 QUOTE(str) 用反斜杠转义str中的单引号 REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果 REVERSE(str) 返回颠倒字符串str的结果 RIGHT(str,X) 返回字符串str中最右边的x个字符 RTRIM(str) 返回字符串str尾部的空格 SOUNDEX(str) 返回str串的SOUNDEX值 STRCMP(s1,s2)比较字符串s1和s2 SUBSTRING() 返回子串的字符 TRIM(str)去除字符串首部和尾部的所有空格 UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果 日期和时间处理函数 ADDDATE() 增加一个日期（天、周等） ADDTIME() 增加一个时间（时、分等） CURDATE()或CURRENT_DATE() 返回当前的日期 CURTIME()或CURRENT_TIME() 返回当前的时间 DATE() 返回日期时间的日期部分 DATE_ADD(DATE,INTERVAL INT keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH); DATE_FORMAT(DATE,fmt) 依照指定的fmt格式格式化日期date值 DATE_SUB(DATE,INTERVAL INT keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH); DAY() 返回一个日期的天数部分 DAYOFWEEK(DATE) 返回date所代表的一星期中的第几天(1~7) DAYOFMONTH(DATE) 返回date是一个月的第几天(1~31) DAYOFYEAR(DATE) 返回date是一年的第几天(1~366) DAYNAME(DATE) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE); FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts HOUR(TIME) 返回time的小时值(0~23) MINUTE(TIME) 返回time的分钟值(0~59) MONTH(DATE) 返回date的月份值(1~12) MONTHNAME(DATE) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE); NOW() 返回当前的日期和时间 QUARTER(DATE) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE); SECOND() 返回一个时间的秒部分 TIME() 返回一个日期时间的时间部分 WEEK(DATE) 返回日期date为一年中第几周(0~53) YEAR(DATE) 返回日期date的年份(1000~9999) 获取当前系统时间： SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()); SELECT EXTRACT(YEAR_MONTH FROMCURRENT_DATE); SELECT EXTRACT(DAY_SECOND FROMCURRENT_DATE); SELECT EXTRACT(HOUR_MINUTE FROMCURRENT_DATE); 返回两个日期值之间的差值(月数)： SELECT PERIOD_DIFF(200302,199802); 在Mysql中计算年龄： SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(tim)),%Y)+0 AS us FROM df; 常用数值处理函数 ABS(X) 返回x的绝对值 BIN(X) 返回x的二进制（OCT返回八进制，HEX返回十六进制） CEILING(X) 返回大于x的最小整数值 COS(X) 返回角度x的余弦 EXP(X) 返回值e（自然对数的底）的x次方 FLOOR(X) 返回小于x的最大整数值 GREATEST(x1,x2,...,xn)返回集合中最大的值 LEAST(x1,x2,...,xn) 返回集合中最小的值 LN(X) 返回x的自然对数 LOG(X,Y)返回x的以y为底的对数 MOD(X,Y)返回x/y的模（余数） PI()返回pi的值（圆周率） RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。 ROUND(X,Y)返回参数x的四舍五入的有y位小数的值 SIGN(X) 返回代表数字x的符号的值 SIN(X) 返回角度x的正弦 SQRT(X) 返回一个数的平方根 TAN(X) 返回角度x的正切 TRUNCATE(X,Y) 返回数字x截短为y位小数的结果 聚合函数(常用于GROUPBY从句的SELECT查询中) AVG(col)返回指定列的平均值 COUNT(col)返回指定列中非NULL值的个数 MIN(col)返回指定列的最小值 MAX(col)返回指定列的最大值 SUM(col)返回指定列的所有值之和 GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果 加密函数 AES_ENCRYPT(str,KEY) 返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储 AES_DECRYPT(str,KEY) 返回用密钥key对字符串str利用高级加密标准算法解密后的结果 DECODE(str,KEY) 使用key作为密钥解密加密字符串str ENCRYPT(str,salt) 使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str ENCODE(str,KEY) 使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储 MD5() 计算字符串str的MD5校验和 PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。 SHA() 计算字符串str的安全散列算法(SHA)校验和 SELECT ENCRYPT(root,salt); SELECT ENCODE(xufeng,key); SELECT DECODE(ENCODE(xufeng,key),key);#加解密放在一起 SELECT AES_ENCRYPT(root,key); SELECT AES_DECRYPT(AES_ENCRYPT(root,key),key); SELECT MD5(123456); SELECT SHA(123456); 格式化函数DATE_FORMAT(DATE,fmt) 依照字符串fmt格式化日期date值 FORMAT(X,Y) 把x格式化为以逗号隔开的数字序列，y是结果的小数位数 INET_ATON(ip) 返回IP地址的数字表示 INET_NTOA(num) 返回数字所代表的IP地址 TIME_FORMAT(TIME,fmt) 依照字符串fmt格式化时间time值 SELECT FORMAT(34234.34323432,3); SELECT DATE_FORMAT(NOW(),%W,%D %M %Y %r); SELECT DATE_FORMAT(NOW(),%Y-%m-%d); SELECT DATE_FORMAT(19990330,%Y-%m-%d); SELECT DATE_FORMAT(NOW(),%h:%i %p); SELECT INET_ATON(10.122.89.47); SELECT INET_NTOA(175790383); 类型转化函数 CAST() 类型有：BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED SELECTCAST(NOW() AS SIGNED INTEGER),CURDATE()+0; SELECTf=BINARYF,f=CAST(FASBINARY); 系统信息函数 DATABASE() 返回当前数据库名 BENCHMARK(COUNT,expr) 将表达式expr重复运行count次 CONNECTION_ID() 返回当前客户的连接ID FOUND_ROWS() 返回最后一个SELECT查询进行检索的总行数 USER()或SYSTEM_USER() 返回当前登陆用户名 VERSION() 返回MySQL服务器的版本 SELECTDATABASE(),VERSION(),USER(); SELECTBENCHMARK(9999999,LOG(RAND()*PI())); 1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.101.102.103.104.105.106.107.108.109.110.111.112.113.114.115.116.117.118.119.120.121.122.123.124.125.126.127.128.129.130.131.132.133.134.135.136.137.138.139.140.141.142.143.六、游标
复制创建、打开、关闭游标 # 定义名为ordernumbers的游标，检索所有订单 DELIMITER // CREATEPROCEDURE processorders() BEGIN -- decalre the cursor 声明游标 declare ordernumbers CURSOR FOR SELECT order_num FROM orders; -- open the cursor 打开游标 open ordernumbers; -- close the cursor 关闭游标 close ordernumbers; END // DELIMITER ; -- 使用游标数据 # 例1：检索 当前行 的order_num列，对数据不做实际处理 DELIMITER // CREATEPROCEDURE processorders() BEGIN -- declare local variables 声明局部变量 DECLARE o INT; -- decalre the cursor 声明游标 declare ordernumbers CURSOR FOR SELECT order_num FROM orders; -- open the cursor 打开游标 open ordernumbers; -- get order number 获得订单号 FETCH ordernumbers INTO o; /*fetch检索 当前行 的order_num列（将自动从第一行开始）到一个名为o的局部声明变量中。 对检索出的数据不做任何处理。*/ -- close the cursor 关闭游标 close ordernumbers; END // DELIMITER ; # 例2：循环检索数据，从第一行到最后一行，对数据不做实际处理 DELIMITER // CREATEPROCEDURE processorders() BEGIN -- declare local variables 声明局部变量 DECLARE done BOOLEAN DEFAULT 0; DECLARE o INT; -- decalre the cursor 声明游标 declare ordernumbers CURSOR FOR SELECT order_num FROM orders; -- declare continue handler DECLARECONTINUE HANDLER FOR SQLSTATE 02000SET done =1; -- SQLSTATE 02000是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。 -- open the cursor 打开游标 open ordernumbers; -- loop through all rows 遍历所有行 REPEAT -- get order number 获得订单号 FETCH ordernumbers INTO o; -- FETCH在REPEAT内，因此它反复执行直到done为真 -- end of loop UNTIL done END REPEAT; -- close the cursor 关闭游标 close ordernumbers; END // DELIMITER ; # 例3：循环检索数据，从第一行到最后一行，对取出的数据进行某种实际的处理 DELIMITER // CREATEPROCEDURE processorders() BEGIN -- declare local variables 声明局部变量 DECLARE done BOOLEAN DEFAULT 0; DECLARE o INT; DECLARE t DECIMAL(8,2); -- declare the cursor 声明游标 DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; -- declare continue handler DECLARECONTINUE HANDLER FOR SQLSTATE 02000SET done = 1; -- create a table to store the results 新建表以保存数据 CREATETABLE IF NOT EXISTS ordertotals (order_num INT,total DECIMAL(8,2)); -- open the cursor 打开游标 OPEN ordernumbers; -- loop through all rows 遍历所有行 REPEAT -- get order number 获取订单号 FETCH ordernumbers INTO o; -- get the total for this order 计算订单金额 CALL ordertotal(o,1,t); # 参见23章代码，已创建可使用 -- insert order and total into ordertotals 将订单号、金额插入表ordertotals内 INSERTINTO ordertotals(order_num,total) VALUES(o,t); -- end of loop UNTIL done END REPEAT; -- close the cursor 关闭游标 close ordernumbers; END // DELIMITER ; # 调用存储过程 precessorders() CALL processorders(); # 输出结果 SELECT * FROM ordertotals; 1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.101.102.103.104.105.106.107.108.109.110.111.112.113.114.115.116.117.118.119.120.121.122.123.124.125.126.127.128.129.七、触发器
复制１、MySQL触发器的创建语法： CREATE[DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW [trigger_order] trigger_body 2、MySQL创建语法中的关键词解释： 字段 含义 可能的值 DEFINER= 可选参数，指定创建者， DEFINER=root@% 默认为当前登录用户（CURRENT_USER）； 该触发器将以此参数指定的用户执行， DEFINER=CURRENT_USER 所以需要考虑权限问题； trigger_name 触发器名称，最好由表名+触发事件关键词+触发时间关键词组成； trigger_time 触发时间，在某个事件之前还是之后；BEFORE、AFTER INSERT：插入操作触发器，INSERT、LOAD DATA、REPLACE时触发； UPDATE：更新操作触发器，UPDATE操作时触发； trigger_event 触发事件，如插入时触发、删除时触发；DELETE：删除操作触发器，DELETE、REPLACE操作时触发； INSERT、UPDATE、DELETE table_name 触发操作时间的表名； 可选参数，如果定义了多个具有相同触发事件和触法时间的触发器时（ 如：BEFORE UPDATE），默认触发顺序与触发器的创建顺序一致，可以 trigger_order 使用此参数来改变它们触发顺序。mysql 5.7.2起开始支持此参数。 FOLLOWS：当前创建触发器在现有触发器之后激活；FOLLOWS、PRECEDES PRECEDES：当前创建触发器在现有触发器之前激活； trigger_body 触发执行的SQL语句内容，一般以begin开头，end结尾 BEGIN .. END 触发执行语句内容（trigger_body）中的OLD，NEW 触发执行语句内容（trigger_body）中的OLD，NEW：在trigger_body中， 我们可以使用NEW表示将要插入的新行（相当于MS SQL的INSERTED）， OLD表示将要删除的旧行（相当于MS SQL的DELETED）。 通过OLD，NEW中获取它们的字段内容，方便在触发操作中使用， 下面是对应事件是否支持OLD、NEW的对应关系： 事件 OLD NEW INSERT × √ DELETE √ × UPDATE √ √ 由于UPDATE相当于删除旧行（OLD），然后插入新行（NEW），所以UPDATE同时支持OLD、NEW； DELIMITER $ ... --触发器创建语句； $ --提交创建语句； DELIMITER ; select * FROM information_schema.triggers; SHOW TRIGGERS; #查看触发器 -- 通过information_schema.triggers表查看触发器： select * FROM information_schema.triggers; -- mysql 查看当前数据库的触发器 SHOW TRIGGERS; -- mysql 查看指定数据库"people"的触发器 SHOW TRIGGERS FROM people; 创建测试表 DROPTABLE IF EXISTS tb; CREATETABLE IF NOT EXISTS tb(id INT,username CHAR(10),pass VARCHAR(20),ct INT); CREATETABLE IF NOT EXISTS bt(fid INT,username CHAR(10),pass VARCHAR(20),ct INT); 创建触发器 DELIMITER $ #droptrigger if exists df_names$ 删除前先判断触发器是否存在 CREATE DEFINER =CURRENT_USER TRIGGER df_names BEFORE INSERTON tb AFTERUPDATEON bt FOR EACH ROW BEGIN #set new.ct=new.id*5; #SET @ct=12; 变量 #SET @pass=hjfd; IF old.type=1 THEN UPDATE bt SET ct=old.ct WHERE fid=old.id; ELSE IF old.type=2 THEN UPDATE bt SET pass=old.pass WHERE fid=old.id; END$ DELIMITER; 测试 INSERTINTO tb(id) VALUES(4); SELECT *FROM tb; 1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.八、存储过程
复制-- 创建存储过程 # 返回产品平均价格的存储过程 DELIMITER // CREATEPROCEDURE productpricing() BEGIN selectAVG(prod_price) AS priceaverage FROM products; END // DELIMITER ; # 调用上述存储过程 CALL productpricing(); -- 删除存储过程,请注意:没有使用后面的()，只给出存储过程名。 DROPPROCEDURE productpricing; -- 使用参数 out # 重新定义存储过程productpricing DELIMITER // CREATEPROCEDURE productpricing(OUT pl DECIMAL(8,2), OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2)) BEGIN selectMIN(prod_price) INTO pl FROM products; SELECTMAX(prod_price) INTO ph FROM products; selectAVG(prod_price) INTO pa FROM products; END // DELIMITER ; # 为调用上述存储过程，必须指定3个变量名 CALL productpricing(@pricelow,@pricehigh,@priceaverage); # 显示检索出的产品平均价格 SELECT @priceaverage; # 获得3个值 SELECT @pricehigh,@pricelow,@priceaverage; -- 使用参数 in 和 out # 使用IN和OUT参数,存储过程ordertotal接受订单号并返回该订单的合计 DELIMITER // CREATEPROCEDURE ordertotal( in onumber INT, # onumber定义为IN，因为订单号被传入存储过程 OUT ototal DECIMAL(8,2) # ototal为OUT，因为要从存储过程返回合计 ) BEGIN selectSUM(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO ototal; END // DELIMITER ; # 给ordertotal传递两个参数； # 第一个参数为订单号，第二个参数为包含计算出来的合计的变量名 CALL ordertotal(20005,@total); # 显示此合计 SELECT @total; # 得到另一个订单的合计显示 CALL ordertotal(20009,@total); SELECT @total; -- 建立智能存储过程 # 获得与以前一样的订单合计，但只针对某些顾客对合计增加营业税 -- Name:ordertotal -- Parameters: onumber = order number -- taxable = 0 if not taxable, 1 if taxable -- ototal = order total variable DELIMITER // CREATEPROCEDURE ordertotal( in onumber INT, in taxable BOOLEAN, OUT ototal DECIMAL(8,2) ) COMMENT obtain order total, optionally adding tax BEGIN -- declare variable for total 定义局部变量total DECLARE total DECIMAL(8,2); -- declare tax percentage 定义局部变量税率 DECLARE taxrate INTDEFAULT 6; -- get the order total 获得订单合计 SELECTSUM(item_price * quantity) FROM orderitems WHERE order_num = onumber INTO total; -- is this taxable? 是否要增加营业税？ if taxable THEN -- Yes,so add taxrate to the total 给订单合计增加税率 select total+(total/100*taxrate) INTO total; end IF; -- and finally,save to out variable 最后，传递给输出变量 SELECT total INTO ototal; END // DELIMITER ; # 调用上述存储过程，不加税 CALL ordertotal(20005,0,@total); SELECT @total; # 调用上述存储过程，加税 CALL ordertotal(20005,1,@total); SELECT @total; # 显示用来创建一个存储过程的CREATE语句 SHOW CREATEPROCEDURE ordertotal; # 获得包括何时、由谁创建等详细信息的存储过程列表 # 该语句列出所有存储过程 SHOW PROCEDURE STATUS; # 过滤模式 SHOW PROCEDURE STATUS LIKEordertotal; 1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.九、事务
复制SET AUTOCOMMIT=off ;禁用或启用事务的自动提交模式 offON SET SESSION AUTOCOMMIT = OFF；禁用或启用事务的session自动提交模式 offON SHOW VARIABLES LIKE%auto%; -- 查看变量状态 执行DML语句是其实就是开启一个事务 只能回滚insert、delete和update语句 对于create、drop、alter这些无法回滚事务只对DML有效果 rollback，或者commit后事务就结束了 自动提交模式用于决定新事务如何及何时启动 START TRANSACTION; 启用自动提交模式下显式地启动事务 COMMIT和ROLLBACK; 禁用自动提交模式显式地提交或回滚 -- 事务 transaction 指一组sql语句 -- 回退 rollback 指撤销指定sql语句的过程 -- 提交 commit 指将未存储的sql语句结果写入数据库表 -- 保留点 savepoint 指事务处理中设置的临时占位符，可以对它发布回退（与回退整个事务处理不同） -- 控制事务处理 # 开始事务及回退 SELECT * FROM ordertotals; # 查看ordertotals表显示不为空 START TRANSACTION; # 开始事务处理 DELETEFROM ordertotals; # 删除ordertotals表中所有行 SELECT * FROM ordertotals; # 查看ordertotals表显示 为空 ROLLBACK; # rollback语句回退 SELECT * FROM ordertotals; # rollback后，再次查看ordertotals表显示不为空 # commit 提交 START TRANSACTION; DELETEFROM orderitems WHERE order_num = 20010; DELETEFROM orders WHERE order_num = 20010; COMMIT; # 仅在上述两条语句不出错时写出更改 # savepoint 保留点 # 创建保留点 SAVEPOINT delete1; # 回退到保留点 ROLLBACKTO delete1; # 释放保留点 RELEASE SAVEPOINT delete1; -- 更改默认的提交行为 SET autocommit = 0; # 设置autocommit为0（假）指示MySQL不自动提交更改 1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.十、备份与还原
复制备份一个数据库:mysqldump -u root -p --opt people> df.txt #--opt优化执行速度 备份两个数据库:mysqldump -u root -p --opt --databases people hw > all.txt 备份全部数据库:mysqldump -u root -p --opt --all-DATABASES > all.txt 恢复数据库:mysqldump -u root -p --opt --databases people hw < all.txt 1. 导出一张表 mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql) 2. 导出多张表 mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql) 3. 导出所有表 mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql) 4. 导出一个库 mysqldump -u用户名 -p密码 --lock-ALL-TABLES --database 库名 > 文件名(D:/a.sql) 可以-w携带WHERE条件 1.2.3.4.5.6.7.8.9.10.11.12.13.十一、用户账号
复制创建账户:CREATEUSER IF NOT EXISTS hw@localhost IDENTIFIED BY5201314; #创建用户hw,密码5201314 给该用户授予所有权限并可授权给其它用户:GRANTALLPRIVILEGESON people.df TOhw@localhost IDENTIFIED BYpasswordWITHGRANTOPTION; 重命名用户名: RENAME USERhwTOgh; 必须将localhost改为% 如果希望该用户能够在任何机器上登陆mysql，则将localhost改为 "%" 授权给其它用户 WITHGRANTOPTION privileges包括： alter：修改数据库的表 create：创建新的数据库或表 delete：删除表数据 drop：删除数据库/表 index：创建/删除索引 insert：添加表数据 select：查询表数据 update：更新表数据 all：允许任何操作 usage：只允许登录 刷新权限,使新创建的用户能够使用: FLUSH PRIVILEGES; 收回用户权限: REVOKEALLPRIVILEGESON people.df FROMroot@localhost; 删除用户: DROPUSER IF EXISTS hw@localhost; 设置指定用户的密码:SETPASSWORDFORhw@localhost = PASSWORD(123321); UPDATEUSERSETPASSWORD = PASSWORD(123321) WHEREUSER = hw; 设置密码: SETPASSWORD = PASSWORD(123321); 1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.十二、其他
复制SHOW STATUS;显示广泛的服务器状态信息 SHOW PROCEDURE STATUS; SHOW GRANTS;显示授予用户的安全权限 SHOW ERRORS;显示服务器的错误信息 SHOW WARNINGS;显示服务器的警告信息 SHOW PROCESSLIST;显示哪些线程正在运行 SHOW VARIABLES;显示系统变量信息 SELECTDATABASE(); 查看当前数据库 SELECT NOW(), USER(), VERSION():显示当前时间、用户名、数据库版本 SHOW ENGINES 引擎名 {LOGS|STATUS}:显示存储引擎的日志和状态信息 SHOW VARIABLES LIKEcharacter%; SHOW VARIABLES LIKEcollation%; 确定所用系统的字符集和校对 SHOW VARIABLES LIKEcharacter_set_client%; 客户端向服务器发送数据时使用的编码 SHOW VARIABLES LIKEcharacter_set_results%; 服务器端将结果返回给客户端所使用的编码 SHOW VARIABLES LIKEcharacter_set_connection%; 连接层编码 SHOW CHARACTERSET;查看所支持的字符集完整列表 SHOW COLLATION;查看所支持校对的完整列表,以及它们适用的字符集 SET character_set_client = gbk; SET character_set_results = gbk; SET character_set_connection = gbk; SET NAMES GBK; -- 相当于完成以上三个设置 创建window服务:sc CREATE mysql binPath= mysqld_bin_path 数据文件目录:DATA DIRECTORY=目录 索引文件目录:INDEX DIRECTORY = 目录 1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.十三、总结
这篇文章主要讲解了MySQL数据库的数据类型、库和表常见操作、索引、视图、函数、游标、触发器、存储过程、事务、备份与还原、用户账号、其它等知识点，希望对大家的学习有帮助。
本文转载自微信公众号「IT共享之家」，可以通过以下二维码关注。转载本文请联系IT共享之家公众号。
IT技术网企商汇源码库香港云服务器源码下载服务器租用IT资讯网益华科技亿华云益华IT技术论坛益强IT技术网极客编程多维IT资讯创站工坊益强编程舍极客码头益强智囊团汇智坊技术快报益强数据堂益强编程堂全栈开发亿华智造亿华云亿华互联益强资讯优选科技前瞻码力社亿华灵动思维库云智核码上建站益强智未来云站无忧亿华科技IT资讯网益强前沿资讯亿华云计算亿华智慧云运维纵横益华科技编程之道益强科技
- Copyright © 2025 Powered by 盘点MySQL数据库的数据类型、库和表常见操作、索引、视图、函数等知识点,创站工坊 滇ICP备2023006006号-44sitemap