MySQL数据库

基本知识点

• 启动数据库服务net start mysql,关闭数据库服务net stop mysql
• 登录数据库mysql -u 用户名 -p 密码
• 显示数据库库名 show databases
• 建立数据库create database 数据库名
• 使用数据库use 数据库名
• 删除数据库 drop database 数据库名
• 进入数据库建立表; create table 表名(属性名 数据类型约束条件)
• 数据类型有int,tinyint,varchar,text,date,datetime,time等，现在而且已经支持json数据类型。
• 约束条件有NOT NULL非空，UNIQUE唯一性，PRIMARY KEY主键，AUTO_INCREMENT自增（一个表中最左只有一个字段约束条件为自增）
• 查看表的类型时可用DESC+表名，查看。或者show create table 表名\G,查看。第二种查看较详细。
• 表的存储引擎常见的三种MyISAM，IMongo。

如何修改数据表字段？

• 修改字段数据类型alter table 数据库表名 modify 字段 数据类型约束条件。
• 修改字段名的位置alter table 数据库表名 modify 字段名 数据类型约束条件first(在第一个位置) | alter 字段名 (在此字段名之后)
• 修改字段名的名称 alter table 数据库表名 change 字段名 需要修改为的字段名 数据类型约束条件。
• 删除该字段 alter table 数据库表名 drop 字段名
• 添加字段 alter table 数据库表名 add 字段名 数据类型约束条件。
• 修改此表的存储引擎alter table 数据库表名 engine = 需修改为的数据存储引擎。
• 重新命名表名 alter table 数据表名 rename 需修改为的数据表名

索引

• 普通索引
  1、建表时：关键字 index(字段名)
  2、建表后： create index 索引属性名 on 表名(字段名)
  3、建表后：alter table 表名 add index 索引属性名 (字段名(索引长度))

• 唯一性索引
  1、关键字UNIQUE，值必须有唯一性
  2、UNIQUE INDEX 索引属性名 (字段名类型(升序ASC降序DESC))
  3、create unique index 索引属性名 on 表名 (具有唯一性的字段名)
  4、alter table 表名 add unique index 索引名(属性名)

• 全文索引
  1、关键字fulltext，只能创建在char，varchar，text类型的字段上。
  2、fulltext index 索引属性名(字段名)
  3、create fulltext index 索引属性名 on 表名(字段名)
  4、alter table 表名 add fulltext index 索引名(索引名)

• 单列索引
  1、对应一个字段
  2、INDEX 索引属性名(字段名(索引长度))
  3、create index 索引属性名 on 表名 (字段名(索引长度))
  4、alter table 表名 add index 索引名 (字段名(索引长度))

• 多列索引
  1、对应多个字段
  2、index 索引属性名(字段1，字段2，…)
  3、使用多列索引必须使用第一个字段查询
  4、create index索引属性名 on 表名 (字段1，字段2…)
  5、alter table 表名 add index 索引名(字段名1，字段名2，…)

• 空间索引
  1、关键字geometry,point,linestring,polygon等，只有MyISAM存储引擎支持空间检索。
  2、spatial index 索引属性名(字段名)
  3、create spatial index 索引属性名 on 表名(字段名)
  4、alter table 表名 add apatial index 索引名(字段名)

• 删除索引
  drop index 索引名 on 表名

视图

• 查询权限: select select_priv,Create_view_priv from mysql.user where user=’root’;

• 创建视图:单表创建视图：create view 视图名(视图的字段名) as select 表的字段(*号表示全部字段) from 表名

• 多表创建视图： create algorithm=merge view 视图名 (视图的字段名) as select 表的字段名 (两个字段名相同时可用表名.字段名表示) from 表名1，表名2… where条件。
  创建视图时数字加减字段名相当于字段里的数据加减数字。视图的约束条件在最后。算法在create之后。

• 查看视图
  1、desc 视图名
  2、SHOW TABLE STATUS LIKE’视图名’\G；也可查看数据表
  3.SHOW CREATE VIEW视图名\G;
  4.在视图的表中查看所有视图，SELECT*FROM information_schema.views\G;

• 修改视图
  1.CREATE OR REPLACE算法语句/表的类型VIEW视图名（视图字段名）AS SELECT表的字段名FROM表名约束条件；（此语句可创造或修改）
  2.ALTER算法语句/表的类型VIEW视图名（视图字段名）AS SELECT表的字段名FROM表名WHERE条件约束条件；

• 更新视图
  当更新视图时，表中的数据也随之改变。只能更改权限范围里的数据。
  有些视图无法更新：一.包含某些函数像SUM（）COUNT()MAX()MIN()等函数时。二.视图包含关键字时无法更新如UNION,UNIONALL,GROUP BY,HAVING等关键字。三.常量视图不能更改（就是只有一个值）。四.子查询不能更新视图五.由不可更新的视图导出的视图是不可更新的。五.临时表TEMPTABLE是不可更新的。
  1.UPTADE视图名SET视图字段名1=“更改后的数据”，…；
  2.视图中插入数据与表中插入数据相同INSERT INTO视图名VALUES（数据）；

• 删除视图
  删除视图：1.DROP VIEW IF EXISTS视图名1，…；

  触发器

  触发器是由INSERT,UPDATE,DELETE来触发的某种特定操作。
  创建触发器：1.单个语句：CREATE TRIGGER触发器名BEFORE/AFTER特定操作ON表名FOR EACH ROW执行语句；
  多个语句：要用BEGIN，END关键字
  查看触发器：1.SHOW TRIGGERS；
  2.在表中查看SELECT*FROM information_schema.trigger\G;
  触发器的使用：触发器执行顺序BEFORE执行操作AFTER
  删除触发器：DROP TRIGGER触发器名；

  操作数据

  1.插入数据：1.不指定字段名：INSERT INTO表名VALUES（数据1,…）；
  2.指定字段名:INSERT INTO表名（字段名1，…）values(数据1，…);
  3.同时插入多条记录：INSERT INTO表名（字段名1，…）values(数据1，…)，(数据1，…)，…;
  4.查询结果插入到表中：INSERT INTO表名（字段名1，…）SELECT字段名FROM表名；

  更新数据

  UPDATE表名SET字段名1=‘数据’…WHERE条件；

  删除数据

  DELETE FROM表名WHERE条件；

  查询数据

  完全语法SELECT属性列表FROM表名和视图列表[WHERE条件表达式][GROUP BY属性名1[HAVING条件表达式2]][ORDER BY属性名2[ASC|DESC]]；
  1.SELECT字段名FROM表名；
  2.SELECT字段名FROM表名WHERE条件；
  3.SELECT字段名FROM表名WHERE条件ORDER BY字段名ASC/DESC；
  单表查询：1.查询所有字段
  2.查询部分字段（查询顺序与列出的属性顺序相同）
  3.查询指定记录WHERE
  4.带IN关键字的查询语法规则：WHERE[NOT]IN(元素1，元素2，…)
  查询条件表配合WHERE
  比较：<,>,=,<=,>=,!=,<>(不等于),!>,!<
  指定范围：BETWEEN AND,NOT BETWEEN AND
  指定集合：IN,NOT IN
  匹配字符：LIKE,NOT LIKE包括完全匹配和使用通配符%（表示多个字符）_（表示一个字符）。
  是否为空值：IS NULL,IS NOT NULL
  多个查询条件：AND,OR
  5.查询结果不重复SELECT DISTINCT字段名FROM表名；
  6.对查询结果排序ORDER BY属性名[ASC|DESC]，多个字段排序时，先排序第一个字段，如果第一个字段数据有相同再用第二个字段排序
  7.分组查询：1.SELECTFROM GROUP BY字段名;最先分到的优先。
  2.使用GROUP_CONCAT(聚合某个字段的数据)再按某个字段名分组：SELECT字段名,GROUP_CONCAT(字段名)FROM表名GROUP BY字段名。
  3.与集合函数使用COUNT()是统计要分组的数量：SELECT字段名,COUNT(字段名)FROM表名GROUP BY字段名。
  4.GROUP BY与HAVING一起使用：SELECT字段名,COUNT(字段名)FROM表名GROUP BY字段名HAVING COUNT(字段名)条件。
  5.按多个字段分组：SELECTFROM GROUP BY字段名1，字段名2，…（分组要全局考虑）
  6.记录求和：SELECT字段名,COUNT(字段名)FROM表名GROUP BY字段名WITH ROLLUP
  SELECT字段名,GROUP_CONCAT(字段名)FROM表名GROUP BY字段名WITH ROLLUP
  8.用LIMIT限制查询结果的数量1.不指定初始位置SELECTFROM表名LIMIT数量；
  2.指定初始位置SELECTFROM表名LIMIT开始，结束；
  9.使用集合函数查询：集合函数有COUNT(),SUM(),AVG(),MAX(),MIN()等。
  1.查询所有记录数：SELECT COUNT()FROM表名；
  2.分组计数：SELECT COUNT()FROM表名GROUP BY字段名；
  3.求总成绩：SELECT字段名，SUM(字段名)FROM表名WHERE条件；（根据分组来求总数）
  4.求平均值：SELECT AVG(字段名)FROM表名；（根据分组来求平均值）
  5.求最大值：SELECT MAX(字段)FROM表名；（根据分组来求最大值）
  6.求最小值：SELECT MIN(字段)FROM表名；（根据分组来求最小值）
  连接查询：1.内连接查询：SELECT多个字段名FROM多个表WHERE条件
  2.外连接查询：SELECT多个字段名FROM表一LEFT|RIGHT JOIN表二ON条件
  3.复合条件查询：SELECT多个字段名FROM多个表WHERE条件AND条件；
  子查询：子查询关键字包括IN、NOT IN、ANY、ALL、EXISTS、NOT EXISTS等关键字。子查询中包含比较运算符。
  举例：SELECT字段名FROM表名WHERE字段名IN(SELECT字段名FROM表名)
  SELECT字段名FROM表名WHERE字段名>=(SELECT字段名FROM表名WHERE条件)
  SELECT字段名FROM表名WHERE EXISTS(查询语句只返回真假)
  SELECT字段名FROM表名WHERE字段名>=ANY(查询语句的数据)
  SELECT字段名FROM表名WHERE字段名>=ALL(查询语句的数据)
  合并查询结果：用UNION和UNION ALL关键字
  举例：SELECT字段名FROM表名UNION/UNION ALL SELECT字段名FROM表名；UNION不去除相同的值UNION ALL去除相同的值
  为表取别名：SELECT字段名FROM表名别名后面就可用此别名
  为字段取别名：SELECT字段名AS别名FROM表名
  正则表达式查询:正则表达式模式字符^:匹配字符串开始的部分\$:匹配字符串结束的部分.:代表字符串中的任意一个字符，包括回车和换行[字符集合]:匹配“字符集合”中的任意一个字符[^字符集合]：匹配“字符集合”外的任何一个字符S1|S2|S3:匹配S1,S2,S3的任意一个字符串*：包括多个该符号之前的字符，包括0和1个+：代表多个该符号之前的字符，包括1个字符串{N}：字符串出现N次{M,N}:字符串至少出现M次，至多出现N次。
  1.SELECT * FROM表名WHERE字段名REGEXP‘^开头的字符及字符串’;
  2.SELECT * FROM表名WHERE字段名REGEXP‘结尾的字符及字符串\$’;
  3.SELECT * FROM表名WHERE字段名REGEXP‘^开头的字符及字符串..结尾的字符串\$’;
  4.SELECT * FROM表名WHERE字段名REGEXP‘[多个字符]’;
  5.SELECT * FROM表名WHERE字段名REGEXP‘[0-9a-z]’;
  6.SELECT * FROM表名WHERE字段名REGEXP‘[^0-9a-z]’;
  7.SELECT * FROM表名WHERE字段名REGEXP‘字符串1|字符串2|字符串3’；
  8.SELECT * FROM表名WHERE字段名REGEXP‘a+c’(a至少出现一次)；
  9.SELECT * FROM表名WHERE字段名REGEXP‘a*c’(a可出现零次)；
  7.SELECT * FROM表名WHERE字段名REGEXP‘a{3}’(a出现三次)；
  7.SELECT * FROM表名WHERE字段名REGEXP‘a{3,6}’(a至少出现三次至多六次)；

  运算符号

  符号+、-、*、/、%、DIV、MOD。

  mysql函数

  1.数学函数：ABS()取绝对值,PI()取圆周率，SQRT()求平方根，MOD(X,Y)求余运算，CEIL(X)大于或等于x的最小整数,CEILING(X)大于或等于x的最小整数,FLOOR(X)小于或等于x的最大整数,RAND()随机的,RAND(X)x一定返回值相同，ROUND(X)返回离x最近的整数四舍五入，ROUND(X,Y)返回x保留小数点y位进行四舍五入，TRUNCATE(X,Y)返回x保留小数点y位不进行四舍五入，SIGN(X)返回x的符号用1 0-1表示，POW(X,Y)X的Y次方,POWER(X,Y)X的Y次方,EXP(X)e的X方，LOG(X)计算x的自然对数，LOG10(X)底数为10的对数，RADIANS(X)角度转换为弧度，DEGREES（X)弧度转换为角度，SIN（X)正弦值，ASIN（X)反正弦值x为-1到1，COS（X)余弦值，ACOS（X)反余弦值x为-1到1，COT(),TAN(),ATAN()。
  2.字符串函数：CHAR_LENGTH(s）字符串的字符数，LENGTH(s)字符串的长度，CONCAT(S1,S2,…)合并多个字符串，CONCAT_WS(X,S1,S2,…)可以将各字符串用参数X隔开，INSERT(S1,X,LEN,S2)函数将s1中x位置开始长度为len的字符串用s2替换，UPPER(S)和UCASE(S)将字符串s的所有字母变成大写字母，LOWER(S)和LCASE(S)函数将字符串s所有字母转换为小写字母，LEFT(s，n）返回字符串前n个字符，RIGHT(s，n）返回字符串后n个字符，LPAD(s1，len，s2）将s2填充到s1的开始处，使字符串达到len长度，RPAD（s1，len，s2）将s2填充到s1的结尾处，使字符串达到长度len，LTRIM（s）将去掉字符串s开始处的空格，RTRIM（s）将去掉字符串s结尾处的空格，TRIM（s）将去掉字符串s开始处和结尾处的空格，TRIM(s1 FROM s)将去掉字符串s中开始处和结尾处的字符串s1，REPEAT（s，n）将字符串s重复n次，SPACE(n）返回n个空格，REPLACE（s，s1，s2）将字符串s2替换到字符串s中的字符串s1，STRCMP(s1，s2）比较字符串s1，s2，返回-1 0 1，SUBSTRING(s，n，len）和MID（s，n，len）从字符串s的第n个位置获取长度为n的字符串，LOCATE(s1，s）、POSITION(s1 IN s）和INSTR(s，s1）从字符串s中获取s1的开始位置，REVERSE（s）将字符串s的顺序反过来，ELT（n，s1，s2，…）返回第n个字符串，FIELD(s，s1，s2，…）返回第一个与字符串s匹配的字符串的位置，FIND_IN_SET(s1，s2）返回在s2中与s1匹配的字符串的位置，MAKE_SET(x，s1，s2，…)按x的二进制数来取字符串。
  3.日期和时间函数：CURDATE()和CURRENT_DATE()获取当前日期，CURTIME()和CURRENT_TIME（）获取当前时间，NOW(),CURRENT_TIMESTAMP,LOCALTIME()和SYSDATE()获取当前日期和时间，UNIX_TIMESTAMP()以UNIX时间戳的形式返回当前时间，UNIX_TIMESTAMP(d)将时间d以UNIX时间戳的形式返回，FROM_UNIXTIME(d)把UNIX时间戳的形式转换为普通格式时间，UTC_DATE()返回UTC国际协调日期，UTC_TIME（）返回UTC国际协调时间，MONTH（d）返回日期d中的月份值，MONTHNAME(d）返回日期d中的月份的英文名字，DAYNAME（d）返回日期d是星期几英文名，DAYOFWEEK(d）返回日期d是星期几开头1为星期日以此类推，WEEKDAY（d）返回日期d是星期几开头0为星期一以此类推，WEEK(d）和WEEKOFYEAR（d）都是计算日期d是本年的第几个星期，DAYOFYEAR返回日期d是本年的第几天，DAYOFMONTH（d）返回日期d是本月的第几天，YEAR(d)返回日期d中的年份值，QUARTER（d）返回d是本年第几季度，HOUR（t）返回时间t中的小时值，MINUTE(t）返回时间t中的秒钟值，EXTRACT(type FROM d）从日期d中获取指定的值，由type类型决定，TIME_TO_SEC(t）将时间t转换为以秒为单位的时间，SEC_TO_TIME(s）将以秒为单位的时间s转换为转换为时分秒。
  计算日期和时间的函数：TO_DAYS(d）把日期转化为天数，FROM_DAYS(n）把天数转化为日期，DATEDIFF(d1，d2）指定某一天去比较d2-d1，ADDDATE(d，n）增加n天，SUBDATE(d，n）减去n天，ADDTIME(t，n)增加n秒，SUBTIME（t，n）减去n秒，ADDDATE(d，INTERVAL expr type)和DATE_ADD(d，INTERVAL expr type)d时间后的日期，
  将时间和日期格式化的函数：DATE_FORMAT（d，f）日期，TIME_FORMATE(t，f）时间，GET_FORMAT(type，s)

  条件判断函数

  IF（expr，v1，v2）如果表达式expr成立，返回结果v1，否则返回v2。
  IFNULL(v1，v2）如果v1不为空就显示v1的值，否则就显示v2的值。
  CASE WHEN expr1 THEN v1[WHEN expr2 THEN v2…][ELSE vn]END。
  CASE expr1 WHEN e1 THEN v1[WHEN e2 THEN v2…][ELSE vn]END。

  系统信息函数

  VERSION()获取mysql版本号，CONNECTION_ID()函数返回服务器的连接数，DATABASE()和SCHEMA()返回当前数据库名，USER()，SYSTEM_USER()，SESSION_USER(),CURRENT_USER()和CURRENT_USER获取用户名,CHARSET(str)返回字符串str的字符集，COLLATION（str）返回字符串str的字符排列顺序，LAST_INSERT_ID()返回最后生成的AUTO_INCREMENT值。

  加密函数

  PASSWORD(str)对字符串str进行加密用于用户名密码。
  MID5(str)对字符串str进行加密用于一般数据。
  ENCODE(str，pswd_str)使用字符串pswd_str加密字符串str,加密的结果是一个二进制数，必须使用BLOB类型的字段来保存。

  解密函数

  DECODE(crypt_str,pswd_str)可以使用字符串pswd_str来为crypt_str来解密crypt_str是通过ENCODE(str，pswd_str)加密的二进制数据。

  其他函数

  格式化函数FORMAT（x，n)可以将数字x进行格式化，将x保留到小数点后n位需进行四舍五入。
  ASCLL（s）返回字符串s的第一个字符的ASCLL码，BIN(x)返回x的二进制编码，HEX(x)返回x的十六进制编码，OCT(x)返回x的八进制编码，CONV（x，f1，f2）将x从f1进制数变成f2进制数。
  INET_ATON(IP)可以将IP地址转换为数字表示需加引号，INET_NTOA（n）可以将数字n转换为IP地址。

  解密加密函数

  CET_LOCT(name，time)定义一个名称为name、持续时间长度为time秒的锁，如果锁定成功返回1，如果尝试超时返回0.如果失败返回NULL。
  RELEASE_LOCK（name）解除名称为name的锁。如果解锁成功返回1，如果解锁失败返回0，如果尝试超时返回0
  IS_FREE_LOCK(name)判断是否使用name的锁，如果使用返回0，否则返回1.
  BENCHMARK(count,expr)将表达式expr重复执行count次，然后返回执行时间。
  改变字符集的函数CONVERT(s USING cs)将字符串s的字符集变成cs。

  改变数据类型的函数

  CAST(x AS type)和CONVERT(x，type）将x变成type型，只改变输出值的类型，字段类型未变。

  存储过程和函数

  1.创建存储过程：CREATE PROCEDURE存储过程名(IN数据名数据类型,OUT数据名数据类型,INOUT数据名数据类型)提取数据（如READS SQL DATA）BEGIN多个执行语句；END结束；
  2.创建存储函数：CREATE FUNCTION存储函数名（数据名数据类型）RETURNS返回值类型BEGIN多个执行语句：END结束；
  3.变量的使用：1.定义变量：DECLARE变量名数据类型默认值（DEFAULT)2.为变量赋值：SET变量名=数据值
  4.定义条件和处理程序：1.定义条件：DECLARE条件名CONDITION FOR SQLSDATE‘sqlsdate_value’。
  DECLARE条件名CONDITION FOR mysql_error_code。
  2.定义处理程序：DECLARE处理方式HANDLER FOR
  法一捕获sqlsdate_value：DECLARE CONTINUE HANDLER FOR SQLSTATE‘sqlsdate_value‘SET@info=‘CAN NOT FIND’；
  法二捕获mysql_error_code：DECLARE CONTINUE HANDLER FOR mysql_error_code SET@info=‘CAN NOT FIND’；
  法三调用定义条件：DECLARE CONTINUE HANDLER FOR条件名SET@info=‘CAN NOT FIND’；
  法四使用SQLWARNING：DECLARE EXIT HANDLER FOR SQLWARNING SET@info=‘ERROR’；
  法四使用SQLEXCEPTION：DECLARE EXIT HANDLER FOR SQLEXCEPTION SET@info=‘ERROR’；
  法五使用NOT FOUND:DECLARE EXIT HANDLER FOR NOT FOUND SET@info=‘CAN NOT FIND’；
  5.光标使用：1.声明光标：声明光标必须在处理程序之前，并且声明在变量和条件之后。
  DECLARE光标名CURSOR FOR SELECT多个字段名FROM表名；
  2.打开光标：OPEN光标名；
  3.使用光标：FETCH光标名INTO变量名；
  4.关闭光标：CLOSE光标名；
  6.流程控制的使用：1.IF语句判断语句THEN执行语句[ELSEIF判断语句THEN执行语句][ELSE执行语句]END IF;
  举例：IF age>20 THEN SET@count1=@count1+1 ELSEIF age=20 THEN@count2=@count2+1 ELSE@count3=count3+1 END IF;
  2.CASE判断变量WHEN变量THEN执行语句WHEN变量THEN执行语句ELSE执行语句END CASE;
  3.开始标签：LOOP SET执行语句END LOOP结束标签；
  4.开始标签：LOOP SET执行语句IF判断语句THEN执行语句LEAVE标签名；END LOOP结束标签；
  5.ITERATE与LEAVE语句相同；
  6.REPEAT SET执行语句UNTIL条件END REPEAT；
  7.WHILE判断条件DO SET执行语句END WHILE；
  7.调用存储过程和函数：1.调用存储过程：CALL存储过程名举例：CALL存储名（1002，@n）；
  2.调用存储函数：与调用内部函数一样；
  8.查看存储过程和函数：1.SHOW PROCEDURE（查看存储函数）|FUNCTION（查看存储过程）STATUS LIKE‘名称’；
  2.SHOW CREATE PROCEDURE（查看存储函数）|FUNCTION（查看存储过程）名称；
  3.SELECT*FROM表名WHERE ROUTINE_NAME=‘名称’；
  9.修改存储过程和函数：ALTER PROCEDURE（查看存储函数）|FUNCTION（查看存储过程）名称