MySQL学习笔记

来源：互联网发布：淘宝网秋冬半身裙编辑：程序博客网时间：2024/06/18 16:48

数据库基本操作

通过DOS窗口链接MySQL软件

mysql -h 127.0.0.1 -u root -p

创建数据库

create Database database_name

查看数据库

SHOW DATABASES

选择数据库

USE database_name

删除数据库

DROP DATABASE database_name

查看数据库存储引擎

SHOW ENGINES \G

数据库表操作

创建表的语法

CREATE TABLE table_name(

属性名数据类型，

属性名数据类型

)

查看表结构

DESC table_name

删除表

DROP TABLE table_name

修改表名

ALTER TABLE old_table_name RENAME 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|AFTER 属性名2

设置非空约束

CREATE TABLE table_name(

属性名数据类型 NOT NULL，

属性名数据类型

)

设置字段的默认值

CREATE TABLE table_name(

属性名数据类型 DEFAULT 默认值，

属性名数据类型

)

设置唯一约束

CREATE TABLE table_name(

属性名数据类型 UNIQUE，

属性名数据类型

)

单字段主键

CREATE TABLE table_name(

属性名数据类型 PRIMARY KEY，

属性名数据类型

)

多字段主键

CREATE TABLE table_name(

属性名数据类型，

CONSTRAINT 约束名 PRIMARY KEY (属性名，属性名)

)

设置字段值自动增加

CREATE TABLE table_name(

属性名数据类型 AUTO_INCREMENT，

属性名数据类型

)

设置外键约束

CREATE TABLE table_name(

属性名数据类型，

CONSTRAINT 外键约束名 FOREIGN KEY (属性名1)

REFERENCES 表名(属性名2)

)

数据库索引

创建表时创建普通索引

CREATE TABLE table_name(

属性名数据类型，

INDEX|KEY 索引名(属性名)

)

在已经存在的表上创建普通索引

CREATE INDEX 索引名 ON 表名(属性名)

通过SQL语句创建普通索引

ALTER TABLE table_name ADD INDEX|KEY 索引名(属性名)

创建表时创建唯一索引

CREATE TABLE table_name(

属性名数据类型，

UNIQUE INDEX|KEY 索引名(属性名)

)

在已经存在的表上创建唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(属性名)

通过SQL语句创建唯一索引

ALTER TABLE table_name ADD UNIQUE INDEX|KEY 索引名(属性名)

创建表时创建全文索引

CREATE TABLE table_name(

属性名数据类型，

FULLTEXT INDEX|KEY 索引名(属性名)

)

在已经存在的表上创建全文索引

CREATE FULLTEXT INDEX 索引名 ON 表名(属性名)

通过SQL语句创建全文索引

ALTER TABLE table_name ADD FULLTEXT INDEX|KEY 索引名(属性名)

创建表时创建多列索引

CREATE TABLE table_name(

属性名数据类型，

INDEX|KEY 索引名(属性名1，属性名2)

)

在已经存在的表上创建多列索引

CREATE INDEX 索引名 ON 表名(属性名1，属性名2)

通过SQL语句创建多列索引

ALTER TABLE table_name ADD INDEX|KEY 索引名(属性名1，属性名2)

删除索引

DROP INDEX index_name ON table_name

数据库视图

创建视图

CREATE VIEW view_name AS 查询语句

查看视图定义信息

SHOW CREATE VIEW viewname

查看视图设计信息

DESC viewname

删除视图

DROP VIEW viewname

修改视图

CREATE OR REPLACE VIEW viewname as 查询语句

ALTER VIEW viewname as 查询语句

对视图数据进行添加，删除直接影响基本表视图来自多个基本表时，不允许添加和删除数据

数据库触发器

创建触发器

CREATE trigger trigger_name BEFORE|AFTER trigger_event ON table_name FOR EACH ROW trigger_STMT

trigger_name: 触发器名称 trigger_event: 包括(INSERT UPDATE DELETE) trigger_STMT:激活触发器后被执行的语句

创建包含多条执行语句的触发器

DELIMITER $$

CREATE trigger trigger_name

BEFORE|AFTER trigger_event

ON table_name FOR EACH ROW

BEGIN

trigger_STMT

END

DELIMITER;

DELIMITER $$:设置结束符号为$$

DELIMITER:将结束符号还原成"；"

查看触发器

SHOW TRIGGERS

删除触发器

DROP TRIGGER trigger_name

数据的操作

插入完整数据记录

INSERT INTO table_name(field1，field2，field3，.....fieldn) VALUES (value1，value2，value3，.....valuen)

INSERT INTO table_name VALUES (value1，value2，value3，.....valuen)

插入数据记录一部分

INSERT INTO table_name(field1，field2，field3，.....fieldn) VALUES (value1，value2，value3，.....valuen)

插入多条完整数据记录

INSERT INTO table_name(field1，field2，field3，.....fieldn) VALUES (value11，value21，value31，.....valuen1)，(value12，value22，value32，.....valuen2)......(value1m，value2m，value3m，.....valuenm)

INSERT INTO table_name VALUES (value11，value21，value31，.....valuen1)，(value12，value22，value32，.....valuen2)......(value1m，value2m，value3m，.....valuenm)

插入多条部分数据记录

插入查询结果

INSERT INTO table_name1 (field11，field12，field13，...field1n) SELECT (field21，field22，field23，...field2n) FROM table_name2 WHERE...

更新特定数据记录

UPDATE table_name SET field1=value1，field2=value2，field3=value3 WHERE CONDITION

更新所有数据记录

UPDATE table_name SET field1=value1，field2=value2，field3=value3 WHERE CONDITION 参数CONDITION 需要满足表table_name中所有的数据记录或者无关键字WHERE语句

删除特定的数据记录

DELETE FROM table_name WHERE CONDITION

删除所有的数据记录

DELETE FROM table_name WHERE CONDITION 参数CONDITION 需要满足表table_name中所有的数据记录或者无关键字WHERE语句

单表数据记录查询

简单数据查询

SELECT field1，field2，....fieldn FROM table_name

避免重复数据查询

SELECT DISTINCT field1，field2，....fieldn FROM table_name

MYSQL支持的关系运算符

运算符描述+加法-减法*乘法/除法%求余

字段别名

SELECT field1 [AS] otherfield1，field2 [AS] otherfield2，fieldn [AS] otherfieldn FROM table_name

条件数据记录查询

SELECT field1，field2，....fieldn FROM table_name WHERE CONDITION

带BETWEEN AND 关键字的范围查询

SELECT field1，field2，....fieldn FROM table_name WHERE field BETWEEN value1 AND value2

带IS NULL， IS NOT NULL关键字的空值查询

SELECT field1，field2，....fieldn FROM table_name WHERE field IS NULL

SELECT field1，field2，....fieldn FROM table_name WHERE field IS NOT NULL

带IN， NOT IN关键字的空值查询

SELECT field1，field2，....fieldn FROM table_name WHERE field IN (value1，value2，value3，..........valuen)

SELECT field1，field2，....fieldn FROM table_name WHERE field NOT IN (value1，value2，value3，..........valuen)

注意：在具体使用关键字IN时，查询的集合中如果存在NULL，则不会影响查询；如果使用关键字NOT IN，查询的集合中如果存在NULL，则不会有任何的查询结果

带LIKE关键字的模糊查询

SELECT field1，field2，....fieldn FROM table_name WHERE field LIKE value

通配符“_”：匹配单个字符

通配符“%”：匹配任意长度的字符

排序查询(默认为升序)

SELECT field1，field2，....fieldn FROM table_name WHERE CONDITION ORDER BY field1 [ASC|DESC] ，field2 [ASC|DESC]

限制数据记录查询数量

SELECT field1，field2，....fieldn FROM table_name WHERE CONDITION LIMIT OFFSET_START，ROW_COUNT

统计函数查询记录

函数：

COUNT()：记录条数

AVG()：平均值

SUM()：总和

MAX()：最大值

MIN()：最小值

SELECT function(field) FROM table_name WHERE CONDITION

注意事项：

对于MySQL软件所支持的统计函数，如果所操作的表中没有任何数据记录，则COUNT()函数返回数据0，而其他函数则返回NULL

分组数据查询(分组关键字建议与统计函数一起使用)

SELECT function() FROM table_name WHERE CONDITION GROUP BY field

如果想显示每个分组中的字段，可以通过函数GROUP_COUNT()来实现

SELECT GROUP_COUNT(field) FROM table_name WHERE CONDITION GROUP BY field

多个字段分组

SELECT GROUP_COUNT(field)，function(field) FROM table_name WHERE CONDITION GROUP BY field1，field2，....fieldn

HAVING来实现条件限制分组数据记录

SELECT function(field) FROM table_name WHERE CONDITION GROUP BY field1，field2，..fieldn HAVING CONDITION

多表记录查询

UNION：把具有相同字段数目和字段类型的表合并到一起
笛卡尔积：没有连接条件表关系返回的结果
内连接分为：自然连接、等值链接、不等链接
自然连接：

在具体执行自然连接时，会自动判断相同名称的字段，然后进行数据值的匹配

在执行完自然连接的新关系中，虽然可以指定包含哪些字段，但是不能指定执行过程中的匹配条件，即哪些字段的值进行匹配

在执行完自然链接的新关系中，执行过程中所匹配的字段名只有一个，即会去掉重复字段

等值链接：在新关系中不会去掉重复字段
不等链接：和等值链接查询的结果相反
外连接分为：左外链接、右外链接、全外连接
左外链接：包含关联左边表中不匹配的数据记录
右外链接：包含关联右边表中不匹配的数据记录
全外连接：表关系的笛卡尔积中除了选择相匹配的数据记录，还包含关联关系左右两边表中不匹配的数据记录
内连接查询语句

SELECT field1,field2,fieldn FROM join_tablename1 INNER JOIN join_tablename2 【INNER JOIN join_tablenamen】 ON join_condition

内连接中特殊的等值链接--自连接：表与自身进行链接

SELECT e.ename employeename,e.job,l.ename loadername FROM t_employee e, t_employee l WHERE e.mgr=l.empno;

外连接查询语句

SELECT field1,field2,fieldn FROM join_tablename1 LEFT|RIGHT|FULL [OUTER] JOIN join_tablename2 ON join_condition

合并查询数据记录

SELECT field1,field2.....fieldn

FROM tablename1

UNION | UNION ALL

SELECT field1,field2.....fieldn

FROM tablename2

UNION | UNION ALL

SELECT field1,field2.....fieldn

FROM tablename3

UNION、UNION ALL的区别在于UNION会去掉重复记录，UNION ALL不会去除重复记录

子查询

WHERE子句中的子查询：该位置处的子查询一般返回单行单列，多行单列，单行多列数据记录

FROM子句中的子查询：该位置处的子查询一般返回多行多列数据记录，可以当做一张临时表

返回结果为单行单列或者单行多列子查询通常会包含比较运算符号（>，<，=，!=）等运算符
返回结果为多行单列的子查询通常会包含IN、ANY、ALL、EXISTS等关键字
当主查询的条件是子查询的查询结果时用IN
当主查询的条件满足子查询的返回结果中任意一条数据记录时用ANY
=ANY：其功能与关键字IN一样
>ANY：比子查询中返回数据记录中最小的还要大的数据记录
<ANY：比子查询中返回数据记录中最大的还要小的数据记录
当主查询的条件满足子查询的返回结果中所有数据记录时用ALL
>ALL（>=ALL）：比子查询中返回数据记录中最大的还要大（大于等于）的数据记录
<ALL（<=ALL）：比子查询中返回数据记录中最小的还要小（小于等于）的数据记录
EXISTS关键字是一个布尔类型，当返回结果集时为TRUE，不能返回结果集时为FALSE，查询时EXISTS对外表采用遍历方式逐条查询，每次查询都会比较EXISTS的条件语句，当EXISTS里的条件语句返回记录行时则条件为真，此时返回当前遍历到的记录；反之，如果EXISTS里的条件语句不能返回记录行，则丢弃当前遍历到的记录。

运算符

算数运算符 + - * / % 算数运算符除了可以直接操作数值外，还可以操作表中的字段注意：/和%运算符如果除数为0将是非法运算，返回结果为NULL
比较运算符 > < =（<=>） !=（<>） >= <= BETWEEN AND IS NULL IN LIKE REGEXP（正则）

=和<=>比较运算符在比较字符串是否相等时，依据字符的ASCII码来进行判断。前者不能操作NULL 后者可以。

!=和<>这两个比较运算符不能操作NULL。

> >= < <=这四个比较运算符不能操作NULL

实现特殊功能的比较运算符

模式字符含义^匹配字符串的开始部分$匹配字符串的结束部分.匹配字符串中的任意一个字符[字符集合]匹配字符集合中的任意一个字符[^字符集合]匹配字符集合外的任意一个字符str1|str2|str3匹配str1、str2、str3中的任意一个字符串*匹配字符，包含0个或多个前面的元素+匹配字符，包含1个或多个前面的元素字符串{N}字符串出现N次字符串{M，N}字符串出现至少M次，最多N次

逻辑运算符

运算符描述表达式AND(&&)与x1 AND X2OR(||)或x1 OR x2NOT(!)非NOTx1XOR异或x1 XOR x2

AND和&&符号作用一样，所有操作数不为0且不为NULL时，结果返回1；存在任何一个操作数为0时，返回结果为0,；存在任意一个操作数为NULL且没有操作数为0时，返回结果为NULL

OR和||符号作用一样，所有操作数中存在任何一个操作数不为0，结果返回1；所有操作数中不包含非0的数字，但包含NULL，结果返回NULL；所有操作数字都为0数字，结果返回0

NOT和!符号作用一样，如果操作数为非0数字，结果返回0,；如果操作数为0，结果返回1；如果操作数为NULL，返回结果NULL

XOR，如果操作数中包含NULL，返回结果为NULL；如果操作数同为数字0或者同为非0数字，返回结果0；如果一个操作数为0而另一个操作数不为0，结果返回1

位运算符

运算符描述表达式形式&按位与x1 & x2|按位或x1 & x2~按位取反~x1^按位异或x1 ^ x2<<按位左移x1 << x2>>按位右移x1 >> x2

常用函数

CONCAT(S1，S2，...Sn)：将字符串连接起来

CONCAT_WS(SEP，S1，S2，...Sn)：将字符串连接起来，并且通过分割符将各个字符串分割开

STRCMP(str1，str2)：比较字符串，如果参数str1大于str2返回1，小于返回-1，等于返回0

LENGTH(str)：获取str的字节长度

CHAR_LENGTH(str)：获取str的字符数

UPPER(S) UCASE(S)：所有字母转换大写

LOWER(S) LCASE(S)：所有字母转换小写

FIND_IN_SET(str1，str2)：返回在字符串str2中与str1相匹配的字符串的位置，参数str2字符串中将包含若干个用逗号隔开的字符串

FIELD(str，str1，str2...)：返回第一个与字符串str匹配的字符串的位置

LOCATE(str1，str) POSITION(str1，str) INSTR(str，str1)：返回参数str中字符串str1的开始位置

ELT(n，str1，str2....)：返回第n个字符串

MAKE_SET(num，str1，str2...strn)：会将num转换成二进制数然后从左到右如果为1选择该字符串，否则不选择

LEFT(str，num)：获取字符串中从左边数的部分字符串

RIGHT(str，num)：获取字符串中从右边数的部分字符串

SUNSTRING(str，num，len) MID(str，num，len) ：返回字符串str中的第num个位置开始长度为len的子字符串

LTRIM(str) RTRIM(str) TRIM(str)：去除字符串左边空格去除字符串右边空格去除字符串首尾空格

INSERT(str，pos，len，newstr)：将字符串str中的pos位置开始长度为len的字符串用字符串newstr来替换起始位置大于字符串长度的话将返回原字符串当所要替换的长度大于原来字符串中所剩字符串的长度，则从起始位置开始进行全部替换

REPLACE(str，substr，newstr)：将字符串str中的子字符串substr用字符串newstr来替换

RAND()：获取随机数如果想获取相同的随机数可以通过执行带有相同参数值得RAND()函数来实现例如 RAND(3) RAND(3)

CEIL(X)：返回大于或等于数值x的最小整数

FLOOR(X)：返回小于或者等于数值x的最大整数

TRUNCATE(x，y)：返回数值x保留到小数点后y位的值

ROUND(x) ROUND(x，y)：返回数值x经过四舍五入操作后的数值返回数值x保留到小数点后y位的值四舍五入

NOW()：获取当前日期和时间

CURDATE() CURRENT_DATE()：获取当前日期推荐使用前一种

CURTIME() CURRENT_TIME()：获取当前时间推荐使用前一种

UNIX_TIMESTAMP()：获取当前时间戳有参数时获取传入的时间的时间戳

FROM_UNIXTIME()：将时间戳格式时间转换成普通格式时间

UTC_DATE() UTC_TIME()：用UTC的方式显示日期和时间

存储过程和函数

创建存储过程

CREATE PROCEDURE procedure_name(procedure_parameter[.....]) [characteristic] routine_body

procedure_name参数表示所要创建的存储过程名字，procedure_parameter参数表示存储过程的参数，characteristic参数表示存储过程的特性，routing_body参数表示存储过程的SQL语句代码，可以用BEGIN...END来标志SQL语句的开始和结束。

procedure_parameter中的每个参数语法 [IN|OUT|INOUT] parameter_name type

IN：表示输入类型 OUT：表示输出类型 INOUT：表示输入/输出类型 parameter_name 表示参数名 type 表示参数类型

characteristic参数的取值：

LANGUAGE SQL

| [NOT] DETERMINISTIC 表示存储过程的执行结果是否确定

| {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA} 包含SQL语句|不包含SQL语句|包含读数据的语句|包含修改数据的语句

|SQL SECURITY{DEFINER|INVOKER} 设置谁有权限来执行定义者自己执行|调用者可以执行默认为DEFAULT

|COMMENT 'string' 注释语句

DELIMITER $$：将SQL语句的结束符由；修改为$$

CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT )

READS SQL DATA

BEGIN

SELECT COUNT(*) INTO count_num

FROM employee

WHERE d_id=emp_id ;

END

DELIMITER $$

CREATE PROCEDURE proce_employee_sal()

COMMENT '查询所有雇员的工资'

BEGIN

SELECT sal FROM t_employee;

END$$

DELIMITER;

创建函数

CREATE FUNCTION function_name([function_parameter[...]]) [characteristic] routine_body

function_parameter中每个参数的语法 parameter_name type

parameter_name 表示参数名 type 表示参数类型

DELIMITER $$

CREATE FUNCTION func_employee_sal (empno INT(11))

RETURNS DOUBLE(10,2)

COMMENT'查询某个雇员的工资'

BEGIN

RETURN(SELECT sal FROM t_employee WHERE t_employee.empno=empno);

END$$

DELIMITER;

该函数有一个类型为INT(11)名字为empno的参数返回值为DOUBLE(10,2)类型

操作变量

声明变量：DECLARE var_name[..] type [DEFAULT value]

赋值变量 SET var_name=expr[...]

SELECT field_name INTO var_name FROM table_name WHERE condition 当将查询结果赋值给变量时，该查询语句的返回结果只能是单行

操作条件（异常）

定义条件

DECLARE...CONDITION语句
这个语句其实是为了让我们的错误条件更加的清晰明了化的，对于上面的情况，像SQLSTATE '23000'这种表示是一种很不直观的方法，要通过相应的文档去对应，阅读起来比较不方便。而DECLARE....CONDITION可以对条件定义相对应的名称，看个例子就清楚了：
DECLARE duplicate_key CONDITION FOR SQLSTATE '23000'; 定义操作异常条件
DECLARE CONTINUE HANDLER FOR duplicate_key 处理定义的条件
BEGIN
-- body of handler
END;

游标

声明游标

DECLARE cursor_name CURSOR FOR select_statement

cursor_name：表示游标的名称

select_statement：表示SELECT语句

打开游标

OPEN cursor_name 打开一个游标时，游标并不指向第一条记录，而是指向第一条记录的前边

使用游标

FETCH cursor_name INTO var_name

将参数游标cursor_name中的SELECT语句的执行结果保存到变量参数var_name中变量参数var_name必须在游标使用前定义当第一次使用游标时游标指向结果集的第一条记录

关闭游标

CLOSE cursor_name

使用流程控制

条件控制语句

IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list ]

[ELSE search_condition]

END IF

CASE case_value

WHEN when_value THEN statement_list

END CASE

循环控制语句

begin_lable：LOOP

statement_list

END LOOP end_lable begin_lable和end_lable必须相同

对于循环语句想要实现退出循环体可以用LEAVE lable来退出相当JAVA中的break

WHILE search_condition DO

statement_list

END WHILE

REPEAT search_condition DO

statement_list

ENDREPEAT

查看存储过程状态信息

SHOW PROCEDURE STATUS [LIKE 'pattern'] \G

查看函数状态信息

SHOW FUNCTION STATUS [LIKE 'pattern'] \G

在系统数据库information_schema中存在一个存储所有存储过程和函数信息的系统表routines
查看存储过程定义信息

SHOW CREATE PROCEDURE proce_name \G

查看函数定义信息

SHOW CREATE FUNCTION func_name \G

修改存储过程

ALTER PROCEDURE procedure_name [characteristic]

修改函数

ALTER FUNCTION function_name [characteristic]

删除存储过程

DROP PROCEDURE proce_name

删除函数

DROP FUNCTION func_name

MySql事务

事务的特性：原子性，一致性，隔离性，持久性

脏读：读取到未提交的数据

不可重复读：一个事务范围内两个相同的查询却返回了不同数据，这是由于查询时系统中其他事务修改的提交而引起的

幻读：是指当事务不是独立执行时发生的一种现象。事务A读取与搜索条件相匹配的若干行。事务B以插入或删除行等方式来修改事务A的结果集，然后再提交。

阅读全文

0 0