温州网站推广优化公司,企业网站建设杭州公司,无极县在线招聘信息,个人网站建设服务器以下内容是学习《MySQL数据库应用 从入门到精通》过程中总结的一些内容提要#xff0c;供以后自己复现使用。一#xff1a;数据库查看所有数据库:SHOW DATABASES创建数据库:CREATE DATABSE database_name切换数据库:USE database_name删除数据库:DROP DATABASE database_name…以下内容是学习《MySQL数据库应用 从入门到精通》过程中总结的一些内容提要供以后自己复现使用。一数据库查看所有数据库:SHOW DATABASES创建数据库:CREATE DATABSE database_name切换数据库:USE database_name删除数据库:DROP DATABASE database_name二存储引擎查看MYSQL支持的引擎:SHOW ENGINES \G (\G 使显示格式更好看)查看所支持的存储引擎: SHOW VARIABLES LIKE have%;查看默认的存储引擎SHOW VARIABLES LIKE storage_engine%;经试验自己mysql上运行应该是: SHOW VARIABLES LIKE default_storage_engine%修改默认存储引擎:方法1向导模式修改 方法2修改配置文件my.ini中default-storage-engine三数据类型数据类型 字节TINYINT 1SMALLINT 2MEDIUMINT 3INT 和 INTEGER 4BIGINT 8FLOAT 4DOUBLE 8DECI(M,D) M2DECIMAL(M,D) M2存储小数用float需要精确到小数点后10位用double需要更精确的用decimalBIT(M) 1~8DATE 4DATETIME 8TIMESTAMP 4TIME 3YEAR 1年月日用DATE;年月日时分秒用DATETIME;经常插入或更新日期为当前系统时间用TIMESTAMP;时分秒用TIME;年份用YEAR。存储的日期需要让不同地区矠用户使用则可以使用TIMESTAMP因为只有该类型日期能够与实际时区相对应。eg:create table t_data_test(f_date DATE,f_datetime DATETIME,f_timestamp TIMESTAMP,f_time TIME,f_year YEAR);SELECT CURDATE(),NOW(),NOW(),TIME(NOW()),YEAR(NOW());结果:CURDATE()NOW()NOW()TIME(NOW())YEAR(NOW())12/1/201712/1/2017 20:16:5612/1/2017 20:16:5620:16:562017INSERT INTO t_data_test VALUES(CURDATE(),NOW(), NOW(), time(NOW()), YEAR(NOW()));SELECT * FROM t_data_test;f_datef_datetimef_timestamp f_timef_year12/1/201712/1/2017 20:13:37 12/1/2017 20:13:3720:13:37 2017CHAR(M) MVARCHAR(M) MTINYTEXT 0~255TEXT0~65535MEDIUMTEXT0~167772150LONGTEXT0~4294967295存文本文件BINARY(M)MVARBINARY(M)M存二进制数据(如图片、音乐、视屏文件)TINYBLOB 0~255BLOB0~2^16MEDIUMBLOB0~2^24LONGBLOB0~2^32存大量二进制数据(如电影等视屏文件)四表创建表CREATE TABLE table_name(属性名 数据类型,属性名 数据类型,属性名 数据类型,...属性名 数据类型)查看表:DESCRIBE table_name;查看表详细定义(创建语句):SHOW CREATE TABLE table_name (\G);删除表:DROP TABLE table_name;修改表改表名: ALTER TABLE old_table_name RENAME [TO] new_table_name;在表的最后一个位置增加字段: ALTER TABLE table_name ADD 属性名 数据类型;在表的最后一个位置增加字段: ALTER TABLE table_name ADD 属性名 数据类型 FIRST;在表的指定字段之后增加字段: ALTER TABLE table_name ADD 属性名 数据类型 AFTER 属性名;删除字段ALTER TABLE table_name DROP 属性名;修改字段ALTER TABLE table_name MODIFY 属性名 数据类型;修改字段名字ALTER TABLE table_name CHANGE 旧属性名 新属性名 数据类型;同时修改字段名字和属性ALTER TABLE table_name CHANGE 旧属性名 新属性名 新数据类型;修改字段的顺序ALTER TABLE table_name MODIFY 属性名1 数据类型 FIRST或: ALTER TABLE table_name MODIFY 属性名1 数据类型 AFTER 属性名2五表约束NOT NULLDEFAULTUNIQUE KEY(UK)PRIMARY KEY(PK)FOREIGH KEY(FK)AUTO_INCREMENTeg:CREATE TABLE table_name(属性名 数据类型 NOT NULL,属性名 数据类型 DEFAULT 默认值,属性名 数据类型 UNIQUE,属性名 数据类型 PRIMARY KEY,属性名 数据类型 AUTO_INCREMENT)或将约束条件写在后面CREATE TABLE table_name(属性名 数据类型 NOT NULL,属性名 数据类型 DEFAULT 默认值,属性名 数据类型,属性名 数据类型,属性名 数据类型,CONSTRAINT UK约束名 UNIQUE(dname)CONSTRAINT PK约束名 PRIMARY KEY(属性名属性名……),CONSTRAINT FK约束名 FOREIGH KEY(属性名1) REFERENCES 表名(属性名2))六索引表是存储和操作数据的逻辑结构而索引则是一种有效组合数据的方式。通过索引对象可以快速查询到表中矠特定记录是一种提高性能的常用方式。 一个索引会包含表中按照一定顺序排序的一列或多列字段。索引按照存储类型可以分为B型树索引(BTREE)和哈希索引(HASH). InnoDB和MyISAM存储引擎支持BTREE类型索引MEMORY存储引擎支持HASH类型索引。默认为前者索引。索引除了可以提高数据库管理系统的查找速度还可以保证字段的唯一性从而实现数据库表的完整性。创建索引可以提高查询速度但过多的创建索引则会占据许多磁盘空间。以下情况适合创建索引1)经常被查询的字段即在where子句中出现的字段2)分组的字段即在group by子句中出现的字段3)存在依赖关系的子表和父表之间的联合查询即主键或外键字段。4)设置唯一完整性约束的字段。以下情况不适合创建索引1)在查询中很少被使用的字段2)拥有许多重复值的字段。索引可以分为普通索引、全文索引、单列索引、多列索引和空间索引。创建(3种方式)CREATE TABLE table_name(属性名 数据类型,属性名 数据类型,……属性名 数据类型,【UNIQUE|FULLTEXT】INDEX | KEY [索引名] (属性名 【(长度)】 【ASC|DESC】, ...));CREATE 【UNIQUE|FULLTEXT】INDEX 索引名 ON 表明 (属性名 【(长度)】 【ASC|DESC】, ...)ALTER TABLE table_name ADD 【UNIQUE|FULLTEXT】INDEX|KEY 索引名(属性名 【(长度)】 【ASC|DESC】, ...)eg: CREATE TABLE t_dept(deptno INT,dname VARCHAR(20),loc VARCHAR(40),INDEX index_deptno(deptno));唯一索引要加UNIQUE修饰符全文索引加FULLTEXT修饰符。普通索引和多列索引不需要加。 多列索引后面是多个列名中间用,分隔。查看索引:SHOW CREATE TABLE table_name \G;校验索引是否被启用:EXPLAIN SELECT * FROM t_dept WHERE deptno 1\G;删除索引:DROP INDEX index_name ON table_name七、视图视图可以提高复杂SQL语句矠复用性和表操作的安全性。视图本质上是一种虚拟表其内容与真实的表相似但视图并不在数据库中以存储的数据值形式存在。行和列数据来自定义视图的查询所引用基本表并且在具体引用视图时动态生成。视图特点如下1)视图的列可以来自不同的表是表的抽象和在逻辑意义上建立的新关系2)视图是由基本表(实表)产生的表(虚表)3)视图的建立和删除不影响基本表4)对视图内容的更新(添加、删除和修改)直接影响基本表5)当视图来自多个基本表时不允许添加和删除数据。创建视图CREATE VIEW view_name AS 查询语句;查看视图:SHOW TABLES;DESCRIBE view_name;SHOW CREATE VIEW view_name;select * from information_schema.views where table_name like v%查看视图详细信息:SHOW TABLE STATUS 【FROM db_name】 【LIKE pattern】删除视图:DROP VIEW view_name【,view_name2, ...】修改视图:CREATE OR REPLACE VIEW view_name AS 查询语句ALTER VIEW view_name as 查询语句或先删除后创建新的利用视图操作基本表和操作实表一样只不过将表名换成了视图名。八、触发器经常使用触发器是因为触发器可以加强数据库表中数据的完整性约束和业务规则等。触发器使用地方DELETE、INSERT、UPDATE。触发器中可以写一行语句也可以写多行语句。每张表只能针对DELETE、INSERT、UPDATE三个事件的BEFORE和AFTER两个时间点添加最多六个(3event*2时间点)触发器。创建触发器:CREATE TRIGGER trigger_nameBEFORE|AFTER trigger_eventON table_name FOR EACH ROW【BEGIN】trigger_stmt;【END】其中trigger_event就是DELETE、INSERT、UPDATEtrigger_stmt就是要执行的语句,单行语句不需要加BEGIN END多行则需要。eg:每次t_dept表插入数据前都在t_diary日志表中增加一条日志记录。CREATE TRIGGER tri_diarytimeBEFORE INSERTON t_dept FOR EACH ROWINSERT INTO t_diary VALUES(NULL,t_dept,now());DELIMITER $$CREATE TRIGGER tri_diarytimeBEFORE INSERTON t_dept FOR EACH ROWBEGININSERT INTO t_diary VALUES(NULL,t_dept,now());INSERT INTO t_diary VALUES(NULL,t_dept,now());END$$DELIMITER ;写多行语句前要通过DELIMITER更换分隔符或还原分隔符。查看触发器SHOW TRIGGERS;SELECT * FROM information_schema.triggers where trigger_name like pattern;删除触发器:DROP TRIGGER trigger_name;九、插入、更新、删除数据插入完整数据记录:INSERT INTO table_name VALUES(value1,value2...);插入数据记录一部分:INSERT INTO table_name(field1,field2,...) VALUES(value1,value2,...);插入多条数据:INSERT INTO table_name VALUES(value11,value12...),(value21,value22...),...;INSERT INTO table_name(field1,field2,...) VALUES(value11,value12...),(value21,value22...),...;插入查询结果:INSERT INTO table_name(field1,field2,...)SELECT (field1,field2,...)FROM table_name2 WHERE ...注意:自增的字段或有默认值的字段可以采用插入数据记录的一部分的方法插入这种方法还不依赖表字段的顺序可扩展性好建议使用。更新数据:UPDATE table_nameSET field1 value1,SET field2 value2,...WHERE CONDITION;删除数据:DELETE FROM table_name WHERE CONDITION;删除整张表里的数据DELETE FROM table_name;TRUNCATE table_name(速度更快建议使用原理删除整张表然后再根据原表DLL语句创建一个一模一样的表)十、单笔数据记录简单查询简单查询使用方式包括:1)简单数据查询2)避免重复数据查询3)实现数学四则运算数据查询 -*/%4)设置显示格式数据查询。eg:SELECT * FROM t_dept;SELECT DISTINCT filed1,field2 ... fieldn FROM table_name; (多个字段组合起来唯一)SELECT ename, sal*12 AS yearsalary FROM t_employee;SELECT CONCAT(ename, 雇员的年薪为: sal*12) yearsalary FROM t_employee;条件数据查询:单条件多条件between andNOTIS NULL / IS NOT NULLIN / NOT IN (使用IN时查询的集合(IN后面的枚举值)中如果存在NULL则不会影响查询如果使用关键字NOT IN查询的集合中如果存在NULL则不会查询到任何数据)LIKE / NOT LIKE _ %排序:SELECT field1,field2...fieldnFROM table_nameWHERE CONDITIONORDER BY fieldm1 [ASC|DESC], fieldm2 [ASC|DESC] ...如果字段的值为空值(NULL)则该值为最小值因此在降序排序中将最后显示在升序排序中将最先显示。限制数据记录查询数量SELECT field1,field2...fieldnFROM table_nameWHERE CONDITIONLIMIT offset_start,row_count;使用方式:1)不指定初始位置只指定行数。 LIMIT row_count;2)指定初始位置指定行数。 LIMIT offset_start,row_count; 不包括offset那一行统计函数count() avg() sum() max() min()count(*):对表中数据进行统计不管表字段中包含的是NULL值还是非NULL值count(field):对指定字段的记录进行统计忽略NULL值。其他几个统计函数必须指定字段所以忽略NULL值。如果所操作的表中没有任何数据记录则count()函数返回0其他统计函数返回NULL。分组数据记录查询SELECT function()FROM table_nameWHERE CONDITIONGROUP BY field1,field2...HAVING CONDITION在具体进行分组查询时分组所依赖的字段上的值一定要具有重复值否则将没有任何意义。首先针对field1进行分组然后针对每组按照字段field2进行分组以此类推。SELECT * FROM table_name GROUP BY CONDITION; 是从分组中随机选择一条记录显示。 所以group by 要和统计函数一起使用才有意义。SELET deptno,GROUP_CONCAT(ename),COUNT(ename) number enames FROM t_employee GROUP BY deptno;会将每组中的ename组合在一起显示.结果:deptnoenamesnumber10MILLER,KING,CLARK320FORD,ADAMS,SCOTT,JONES,SMITH430BLAKE,MARTIN2十一、多表数据记录查询在具体应用中如果需要实现多表数据记录查询一般不使用连接查询因为该操作效率比较低而是使用子查询操作。进行连接操作时会求笛卡尔积如果表数据记录多则可能会造成死机。MYSQL在具体实现连接查询操作时首先将两个或两个以上的表按照某个条件连接起来然后再查询到所要求的数据记录。连接操作是关系数据库操作中专门用于数据库操作的关系运算包括并(UNION)、笛卡尔积(CARTESIAN PRODUCT)和专门针对数据库操作的运算-连接(JOIN)。并(UNION)把具有相同字段数目和字段类型的表合并到一起。笛卡尔积(CARTESIAN PRODUCT):是没有连接条件表关系返回的结果。eg表A字段数目为m行数为k。 表B字段数目为n行数为l。 则结果表字段数目为mn,行数为k*l.连接(JOIN)在表关系的笛卡尔积数据记录中按照相应字段值的比较条件进行选择生成一个新的关系。连接分为内连接(inner join) 、外连接(outer join) 和交叉连接(cross join)。1.内连接在表关系的笛卡尔积数据记录中保留表关系中所有匹配的数据记录舍弃不匹配的数据记录。按照匹配的条件可以分成自然连接、等值连接和不等连接。自然连接在表关系的笛卡尔积中首先根据表关系中相同名称的字段自动进行记录匹配然后去掉重复的字段。eg表A 有字段名a,b;表B也有字段名a,b。则自然连接时先求A和B的笛卡尔积然后匹配A.aB.a and A.b B.b的记录不符合该条件的全部舍弃。 笛卡尔积中A.a A.b B.a B.b都会显示共4个字段但自然连接中只显示a,b2个字段。特点:1)在具体执行自然连接时会自动判断相同名称的字段然后进行数据值的匹配。2)在执行完自然连接的新关系中虽然可以指定包含哪些字段但是不能指定执行过程中的匹配条件即哪些字段的值进行匹配。3)在执行完自然连接的关系中执行过程中所有匹配的字段名只有一个即会去掉重复字段。等值连接在表关系的笛卡尔积中选择所匹配字段值相等的数据记录。特点1)使用指定匹配条件2)在新关系中不会去掉重复字段。不等连接在表关系的笛卡尔积中选择所匹配字段值不相等的数据记录。特点1)使用!指定匹配条件2)在新关系中不会去掉重复字段。2.外连接:在表关系的笛卡尔积数据记录中不仅保留表关系中所有匹配的数据记录而且还会保留部分不匹配的数据记录。按照保留不匹配条件数据记录来源可以分为左外连接(left outer join)、右外连接(right outer join)和全外连接(full outer join)。左外连接在表关系的笛卡尔积中除了选择相匹配的数据记录还包含关联左边表中不相匹配的数据记录。右外连接在表关系的笛卡尔积中除了选择相匹配的数据记录还包含关联右边表中不相匹配的数据记录。全外连接在表关系的笛卡尔积中除了选择相匹配的数据记录还包含关联左右两边表中不相匹配的数据记录。3.交叉连接不带where子句的连接返回的就是笛卡尔积数据记录。总结表A和BUNION A并Binner join: A交B 根据交的条件分为自然连接、等值连接、不等值连接。 使用的是比较运算符进行匹配条件的判断。outer join:左外连接A并(A交B)右外连接(A交B)并B全外连接:A并(A交B)并Bcross join: A乘B 十字交叉笛卡尔积语法:内连接SELECT field1 field2 ... fieldnFROM join_table_name1 INNER JOIN join_table_name2 [INNER JOIN join_table_namen]ON join_condition外连接:SELECT field1 field2 ... fieldnFROM join_table_name1 LEFT|RIGHT|FULL [OUTER] JOIN join_table_name2 [INNER JOIN join_table_namen]ON join_conditionUNION:SELECT field1 field2 ... fieldnFROM table_name1UNION | UNION ALLSELECT field1 field2 ... fieldnFROM table_name2UNION | UNION ALLSELECT field1 field2 ... fieldnFROM table_nameN...子查询一个查询之中嵌套了其他的若干查询即在一个SELECT查询语句的WHERE或FROM子句中包含另外一个SELECT查询语句。通过子查询可以实现多表查询该查询语句中可能包含IN、ANY、ALL和EXISTS等关键字除此之外还可能包含比较运算符。理论上子查询可以出现在查询语句的任意位置但是在实际开发中子查询经常出现在WHERE和FROM子句中。WHERE子句中的子查询该位置处的子查询一般返回单行单列、多行单列、单行多列数据记录。FROM 子句中的子查询该位置处的子查询一般返回多行多列数据记录可以当做一张临时表。eg:子查询返回单行单列: select * from t_employee where sal (select sal from t_employee where ename SMITH); (工资大于SMITH的人)子查询返回单行多列select ename,sal,job from t_employee where (sal,job) (select sal,job from t_employee where enameSMITH);(职位和工资和SIMITH一样的人)子查询返回多行单列select * from t_employee where deptno in (select deptno from t_dept);select ename,sal from t_employee where sal any(select sal from t_employee where job MANAGER);ANY(等同于IN) ANY ALL select * from t_deptno c where not EXISTS(select * from t_employee where deptno c.deptno); (列出没有雇员的部门)子查询返回多行多列select d.deptno,d.dname,d.loc,number,averagefrom t_dept d inner join (select deptno dno,count(empno) number, avg(sal) averagefrom t_employee group by deptno desc ) employeeon d.deptno employee.dno;(查询t_employee表中各个部门的部门号、部门名称、部门地址、雇员人数和平均工资)十二、Mysql运算符算术运算符 - * / % (除数为0时返回null)比较运算符 或 ! 或 BETWEEN AND IS NULL INLIKEREGEXP不能操作NULL如果一方为NULL则返回NULL。 而则可以操作NULL。eg: SELECT NULL NULL 符号效果, NULL NULL 符号效果;结果1 NULL! 和 都不能操作NULL。 也不能操作NULL。REGEXP:正则表达式MYSQL支持的模式字符:^匹配字符串的开始部分$匹配字符串的结束部分.匹配字符串中的任意一个字符[字符集合]匹配字符集合中的任意一个字符[^字符集合]匹配字符集合外的任意一个字符str1|str2|str3匹配str1、str2、str3中的任意一个字符串* 匹配字符包含0个和1个匹配字符包含1个或以上字符串[N]字符串出现N次字符串[M,N]字符串出现至少M次至多N次逻辑运算符:AND() OR(||) NOT(!) XORAND:所有操作数不为0且不为NULL返回1存在任意一个操作数为0返回0存在任意一个操作数为NULL且没有操作数为0返回NULL。OR所有操作数存在任何一个操作数不为0则返回1所有操作数都为0数字返回0所有操作数中不包含非0数字但包含NULL返回NULL。NOT:操作数为非0数字返回0,操作数为0返回1操作数为NULL返回NULLXOR操作数同为0数字或非0数字返回0操作数一个为0另一个为非0返回1操作数中包含NULL返回NULL.总结OR NOT XOR如果有一个操作数为NULL就返回NULL,其他和正常理解的一样AND 如果有一个操作数为0就为0如果存在NULL且不存在0则返回NULL位运算符 | ~ ^ 现将十进制数转换为二进制然后再进行位运算最后结果转换为十进制再显示。显示二进制形式BIN(name);十三、常用函数mysql中函数不仅可以出现在select语句及其子句中还可以出现在update和delete语句中。函数可移植性不好。字符串函数:concat(str1,str2,...strn)连接字符串str1,str2,...strn为一个完整字符串 (一个为NULL,则结果为NULL)concat_ws(sep,str1,str2,...strn)连接字符串str1,str2,...strn为一个完整字符串各个字符串使用sep分隔符分割。(分隔符为NULL则返回NULL字符串中有NULL则忽略。)lower(str) lcase(str)将字符串str中所有字符变为小写upper(str) ucase(str)将字符串str中所有字符变为大写strcmp(str1,str2)比较字符串str1和str2length(str)返回字符串长度(一个英文算一个一个汉字算两个)char_length(str)返回字符串长度(一个英文算一个一个汉字算一个)left(str,count)返回字符串str中最左边的count个字符right(str,count)返回字符串str中最右边的count个字符substring(str,pos,count)返回字符串str中pos位置之后的count个字符。(从1开始数包括pos)mid(str,pos,count)返回字符串str中pos位置之后的count个字符。(从1开始数包括pos)ltrim(str)去掉字符串左侧的空格rtrim(str)去掉字符串右侧的空格trim(str)去掉字符串两侧的空格insert(str,pos,len,newstr)将字符串str中的pos位置开始长度为len的字符串用字符串newstr替换。若任何一个参数为null返回null.replace(str,substr,newstr)将字符串str中的子字符串substr用newstr替换数值函数:abs(x)返回x的绝对值ceil(x)ceiling(x)返回大于x的最小正整数floor(x)返回小于x的最大正整数round(x)返回x四舍五入后的整数round(x,y)返回x四舍五入后有y位小数的数值mod(x,y)返回x%yrand() rand(x)返回0~1内的随机数(rand()多次调用返回值不同但rand(x)只要x一样多次调用返回值一样。)truncate(x,y)返回x截断为y位小数的数值注y表示小数位数时如果取负数表示整数位。如-1表示截断个位数。日期函数:curdate() current_date()获取当前日期curtime() current_timestamp()获取当前时间localtime()获取当前时间sysdate()获取当前时间now()获取当前日期和时间unix_timestamp(date)from_unixtime(utime)utc_date(date)utc_time(date)获取日期和时间各个部分:year(date) quarter(date) month(date) week(date) hour(time) minute(time) second(time)monthname(date) dayname(date)weekofyear(date) dayofyear(date) dayofmonth(date) dayofweek(date) weekday(date)extract(type from date): extract(year from now()) extract(month from now()) ...datediff(date1,date2)date1和date2相隔天数adddate(date,n)date加上n天subdate(date,n)date减去n天addtime(time,n)time加上n秒subtime(time,n)time减去n秒系统信息函数:version()返回数据库版本号datebase()返回当前数据库名user()返回当前用户last_insert_id()返回最近生成的auto_increment值流程函数:if(value,t f)如果value为真返回t否则返回fifnull(value1,value2)如果value1不为空值返回value1否则返回value2case when [value1] then [result1] ... else [default] end如果value1为真返回result1,...都不符合返回defaultcase [expr] when [value1] then [result1] ... else [default] end如果expr等于value1返回result1...都不符合返回defalut。十四、存储过程和函数存储过程和函数是事先经过编译并存储在数据库中的一组sql语句集合。函数必须有返回值而存储过程则没有。存储过程的参数类型远远多于函数参数类型。优点1)存储过程和函数运行标注组件式编程提高了SQL语句的重用性、共享性和可移植性2)存储过程和函数能够实现较快的执行速度能够减少网络流量3)存储过程和函数可以被当做为一种安全机制来利用。缺陷1)存储过程和函数的编写比单条sql语句复杂需要用户具有更高的技能和更丰富的经验2)在编写存储过程和函数时需要创建这些数据库对象的权限。创建存储过程CREATE PROCEDURE procedure_name([procedure_parameter[,...]])[characteristic...] routine_bodyprocedure_name存储过程名字procedure_parameter:存储过程参数,语法形式为[IN|OUT|INOUT] parameter_name typecharacteristic存储过程特性routine_body:存储过程SQL语句代码可以用BEGIN...END 来标识SQL语句的开始和结束创建函数:CREATE FUNCTION function_name ([function_parameter[,...]])[characteristic...] routine_bodyfunction_parameter函数参数,语法形式为 parameter_name type.存储过程和函数中的表达式中由变量、运算符和流程控制构成。变量:声明变量:DECLARE var_name[,...] type [DEFAULT value]变量赋值:SET var_name expr[,...] 或 SELECT field_name[,...] INTO var_name[,...] FROM table_name WHERE condition;游标(取出结果集进行处理效率低能不使用就不使用游标)声明游标DECLARE cursor_name CURSOR FOR select_statement打开游标OPEN cursor_name使用游标FETCH cursor_name INTO var_name[,var_name] ...关闭游标CLOSE cursor_name流程控制条件控制语句:IF search_condition THEN statement_list[ELSEIF search_condition THEN statement_list] ...[ELSE statement_list]END IFCASE case_valueWHEN when_value THEN statement_list[WHEN when_value THEN statement_list] ...[ELSE statement_list]END CASE循环控制语句[begin_label:] LOOPstatement_listEND LOOP [end_label][begin_label:] WHILE search_condition DOstatement_listEND WHILE [end_label][begin_label:] REPEAT search_condition DOstatement_listEND REPEAT [end_label]查看存储过程和函数:SHOW CREATE PROCEDURE procedure_name;SHOW CREATE FUNCTION function_name;SHOW PROCEDURE STATUS [LIKE pattern] \GSHOW FUNCTION STATUS [LIKE pattern] \GSELECT * FROM information_schema.routines \G;SELECT * FROM information_schema.routines WHERE specific_name name \G;修改存储过程和函数:ALTER PROCEDURE procedure_name[characteristic ...]ALTER FUNCTION function_name[characteristic ...]删除存储过程和函数DROP PROCEDURE procedure_name;DROP FUNCTION function_name;十五、事务MYSQL引擎中InnoDB和BDB支持事务MyISAM和MEMORY不支持事务。 InnoDB通过UNDO日志和REDO日志来实现事务。事务4个特性1)原子性(atomicity)事务中所有的操作视为一个原子单元即对于事务所进行的数据修改等操作只能是完全提交或完全回滚。2)一致性(consistency)事务在完成时必须使所有的数据从一种一致性状态变更为另一种一致性状态所有的变更都必须应用于事务的修改以确保事务的完整性。3)隔离性(isolation):一个事务中的操作语句所做的修改必须与其他事务所做的修改相隔离。在进行事务查看数据时数据所处的状态要么是被另一并发事务修改之前的状态哟啊么是被另一个并发事务修改之后的状态即当前事务不会查看由另一个并发事务正在修改的数据。这种特性通过锁机制来实现。4)持久性(durability):事务完成之后所做的修改对数据的影响是永久性的即使系统重启或者出现系统故障仍可以恢复。MYSQL使用BEGIN开始事务使用COMMIT结束事务中间可以使用ROLLBACK回滚事务。START TRANSACTION | BEGIN [WORK]COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]SET AUTOCOMIT {0 | 1}事务隔离级别SQL标准定义了4种隔离级别指定了事务中哪些数据改变其他事务可以见哪些数据改变其他事务不可见。低级别的隔离级别可以支持更高的并发处理同时占用的资源更少。事务隔离级别可以使用以下语句设置:#未提交读SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;#提交读SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;#可重复性SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;#可串行化SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;1)READ UNCOMMITTED(读取未提交内容)在该隔离级别所有事务都可以看到其他未提交事务的执行结果因为其性能也不必其他级别搞很多因为此隔离级别实际应用中一般很少使用读取未提交的数据被称为脏读(Dirty Read)。2)READ COMMITTED(读取提交内容)这是大多数数据库系统的默认隔离级别但不是MYSQL默认的隔离级别。其满足了隔离的简单定义一个事务从开始到提交前所做的任何改变都是不可见的。事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读(Nonrepeatable Read),因为同一事务的其他实例在该实例处理期间可能会有新的数据提交导致数据改变所以统一查询可能返回不同结果。3)REPEATABLE-READ(可重复读)这是MYSQL的默认事务隔离级别能确保同一事务的多个实例在并发读取数据时会看到同样的数据行。理论上会导致另一个问题幻读(Phantom Read)。eg第1个事务对一个表中的数据进行了修改这种修改涉及表中的全部数据行。同时第2个事务也修改这个表中的数据这种修改是向表中插入一行新数据。那么以后就会发生操作第1个事务的用户发现表中还有没有修改的数据行。InnoDB和Falcon存储引擎通过多版本并发控制(Multi_Version Concurrency Control,MVCC)机制解决了该问题。4)Serializable(可串行化)这是最高的隔离级别通过强制事务排序使之不能相互冲突从而解决幻读问题。简言之就是在每个读的数据行上加上共享锁实现。在这个级别可能会导致大量的超时现象和锁竞争一般不推荐使用。MySQL中所有的DDL语句是不能回滚的并且部分的DDL语句会造成隐式的提交。比如ALTER TABLE、TRUNCATE TABLE 和 DROP TABLE等。锁机制:共享锁S(Share) 读锁 锁粒度为行或元组(多个行) 一个事务获取了共享锁之后可以对锁定范围内的数据进行读操作。排他锁X(eXclusive) 写锁 锁粒度为行或元组(多个行) 一个事务获取了排他锁之后可以对锁定范围内的数据进行写操作。意向锁:分意向共享锁(IS)和意向排他锁(IX)。 锁粒度为整张表。 有意表示事务想执行操作但还没有真正执行。锁和锁之间的关系相容(两个事务可以对同一组数据同时加锁)或互斥(一个事务对一组数据加锁后其他事务不能再加锁了)。各个锁的关系如下(Y:相容 N:互斥)参数XSIXISXNNNNSNNYNYIXNNYYISNYYY锁粒度分为表锁和行锁。表锁管理锁的开销最小同时运行的并发来那个也是最小的。 MyISAM存储引擎使用该锁机制。 一些特定的动作也使用表锁如ALTER TABLE.行锁可以支持最大的并发。InnoDB存储引擎使用的是行锁。 如果要支持并发读/写建议采用InnoDB存储引擎因为其是采用行级锁可以获的更多的更新性能。SQL执行时加锁:SELECT ... LOCK IN SHARE MODE 加上一个共享锁SELECT ... FOR UPDATE 加上一个排他锁InnoDB引擎会自动给会话事务中的共享锁、更新锁以及排他锁需要加到一个区间值域时再加上个间隙锁或称为范围锁对不存在的数据也锁住防止出现幻写。十六、Mysql安全机制创建用户CREATE USER usernameip IDENTIFIED BY [PASSWORD] password, usernameip IDENTIFIED BY [PASSWORD] password ...;通过向mysql数据库中的user表插入记录的方式来创建用户。注意1)需要插入x509_issuer等字段值2)需要FLUSH PRIVILEGES;生效。3)密码要使用PASSWORD函数加密。INSERT INTO user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject)VALUES(localhost,wyl,PASSWORD(password),,,);通过GRANT语句创建用户并同时赋权限GRANT priv_type ON databasename.tablenameTO username [IDENTIFIED BY [PASSWORD] password][,username [IDENTIFIED BY [PASSWORD] password]]...eg:GRANT SELECT ON paydb.t_txn_ds TO paydblocalhost IDENTIFIED BY 123456;修改密码使用超级权限用户修改root用户密码:方法一mysqladmin -u username -p oldpassword new_password;方法二: SET PASSWORDPASSWORD(new_password);方法三:通过修改user表记录修改。UPDATE user SET passwordPASSWORD(new_password) WHERE user root and host localhost;使用超级权限用户修改普通用户密码:方法一:GRANT priv_type ON database.table TO user[IDENTIFIED BY [PASSWORD] new_password]方法二SET PASSWORD FOR usernamehostnamePASSWORD(new_password); 如果是修改自己的密码不需要写FOR usernamehostname.方法三:通过修改user表记录修改。UPDATE user SET passwordPASSWORD(new_password) WHERE user username and host localhost;删除用户方法一DROP USER user1[,user2...];方法二:用root账号登陆删除user表中记录。DELETE FROM user where user wyl AND host localhost;权限管理授权GRANT priv_type [(column_list)] ON database.tableTO user [IDENTIFIED BY [PASSWORD] password][,user [IDENTIFIED BY [PASSWORD] password]]...[WITH with_option...];column_list表示权限作用的字段没有时表示作用于整张表。with_option取值:GRANT OPTION:被授权的用户可以将权限授权给其他用户。(给其他用户赋权限时权限不得超过自己的)MAX_QUERIES_PER_HOUR count:设置每小时可以执行count次查询MAX_UPDATES_PER_HOUR count:设置每小时可以执行count次更新MAX_CONNECTIONS_PER_HOUR count:设置每小时可以建立count次连接MAX_USER_CONNECTIONS count:设置单个用户可以用事具有count个连接查看权限SHOW GRANTS FOR username \G;或者去user表查看相应字段取值eg:SELECT host,user,password,select_priv,update_priv,grant_priv,drop_privFROM mysql.user WHERE user wyl \G;收回权限REVOKE priv_type [(column_list)] ON database.tableFROM user [IDENTIFIED BY [PASSWORD] password][,user [IDENTIFIED BY [PASSWORD] password]]...;回收全有权限:REVOKE ALL PREVILEGES,GRANT OPTIONFROM user [IDENTIFIED BY [PASSWORD] password][,user [IDENTIFIED BY [PASSWORD] password]]...;十七、日志管理Mysql日志分为二进制日志、错误日志和查询日志。默认情况下MYSQL指挥启动错误日志文件其他日志文件需要手动启动才可以被启动。二进制日志该日志文件会以二进制形式记录数据库的各种操作记录DDL和DML语句但是却不会记录查询语句。错误日志该日志文件会记录MYSQL服务器启动、关闭和运行时出错等信息。通用查询日志该日志记录MYSQL服务器的启动和关闭信息、客户端的连接信息、更新SQL语句和查询SQL语句。慢查询日志记录执行时间超过指定时间的各种操作通过工具分析慢查询日志可以定位MYSQL服务器性能瓶颈所在。启动日志后虽然可以实现对MYSQL服务器进行维护但是会降低mysql软件的执行速度。日志在my.ini配置文件中的mysqld组下面进行配置有配置就是启动了日志没有就是没启动。查看二进制日志mysqlbinlog filename.number;其他日志是文本形式可以直接打开查看。动态打开或关闭通用日志: SET global general_log on/off; 立刻生效动态打开或关闭慢查询日志:SET global slow_query_log on/off; 需要重新连接才能生效SET global long_query_time 3;//单位:秒配置文件[mysqld]log-low-queries[dir\[filename]]long_query_time n (单位秒默认10秒)MYSQL自带的慢查询日志分析工具mysqldumpslow.plmysqldumpslow.pl -s at -t 1 slow.log-s:分析慢查询日志时指定排序参数。取值有al-平均锁定时间 ar-平均返回记录数 at-平均查询时间-t:指定显示的行数最后跟慢查询日志的名称。删除mysql的日志进行重新写入mysqladmin -u root -p flush-logs十八、数据库维护和性能提高数据库维护包括备份数据、还原数据、数据库迁移、表导出和导入。注mysql和mysqldump命令不是在mysql命令窗口执行的而是在操作系统命令窗口(cmd命令窗口或者linux命令窗口)下执行的后面不需要加;数据库备份方法一复制数据文件进行备份。只适合存储引擎为MyISAM的表不适合InnoDB存储引擎的表。复制前1)FLUSH TABLES; 2)停止mysql服务器。拷贝文件:MySQL Server 5.5\data目录方法二通过mysqldump命令实现数据备份该命令会将包含数据的表结构和数据内容保存在相应的文件中。mysqldump -u username -p dbname table1 table2 ... tablen backupname.sqlmysqldump -u username -p --database dbname1 dbname2 ... dbnamen backupname.sqlmysqldump -u username -p --all -databases backupname.sql数据还原方法一复制数据文件进行还原。方法二通过mysql命令还原mysqldump -u username -p [dbname] backupname.sql表导出到文本文件:方法一SELECT ... INTO OUTFILESELECT ... FROM table_name [WHERE condition] INTO OUTFILE file_name [OPTION]方法二通过mysqldump命令mysqldump -u root -pPassword -T file_directory dbname table_name [OPTION]eg:mysqldump -u root -proot -T c:\ company t_dept方法三通过mysql命令导出mysql -u root -pPassword -e SELECT ... FROM table_namme dbnamefile_nameeg:mysql -u root -p -e SELECT * FROM customers crashcourse C:/customers.txt后缀可以改为其他比如xlsx.文本文件导入到数据库:方法一:执行LOAD DATA INFILE命令LOAD DATA[LOCAL] INFILE file_name INTO TABLE table_name [OPTION]方法二执行mysqlimport命令mysqlimport -u root -pPassword[--LOCAL] dbname file_name[OPTION]数据库迁移先导入再导入或同时进行。eg:mysqldump -h hostname1 -u root -passwordpassword1 -all-databases|mysql -h hostname2 -u root -passwordpassword2MYSQL性能优化建议:1)硬件参数要满足建议运行在专用的服务器上2)随着运行时间的推移不断调整内存分配、缓存区大小等参数配置。 可以通过SHOW VARIABLES和SHOW STATUS来查看当前设置。3)MYSQL是一个多用户多线程的数据库管理系统。可以通过SHOW PROCESSLIST显示所有活动进程通过执行kill命令终结消耗太多资源的进程。4)通过SELECT 语句实现多表查询时应该多次试验连接和子查询等各种方式找出最佳的方式。在具体判断SELECT语句执行性能时可以通过EXPLAIN语句查看SELECT语句的执行情况。5)使用存储过程比一条一条执行语句速度要块许多6)不要查找比需求还要多的数据内容换言之不要执行SELECT *语句(除非真正要查询所有字段);7)通过UNION关键字连接的SELECT语句代替包含一系列复杂OR条件的SELECT 语句可以有极大的性能改进。8)索引可以改善数据检索的性能但是会损失数据CUD操作(数据插入、数据更新和数据删除)性能。因此对于不经常被查询的表最好不要创建索引。9)关键字LIKE的执行效率很低一般来说会通过FULL TEXT来代替关键字LIKE10)数据库中的表是不断变化的尸体。一组结构优良的表使用一段时间后表的使用和内容就会需要进行更改因此当初理想的优化和配置就需要改变。
