数据库基本操作
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 表名(属性名)
ALTER TABLE table_name ADD INDEX|KEY 索引名(属性名)
CREATE TABLE table_name(
UNIQUE INDEX|KEY 索引名(属性名)
)
CREATE UNIQUE INDEX 索引名 ON 表名(属性名)
ALTER TABLE table_name ADD UNIQUE INDEX|KEY 索引名(属性名)
CREATE TABLE table_name(
FULLTEXT INDEX|KEY 索引名(属性名)
)
CREATE FULLTEXT INDEX 索引名 ON 表名(属性名)
ALTER TABLE table_name ADD FULLTEXT INDEX|KEY 索引名(属性名)
CREATE TABLE table_name(
INDEX|KEY 索引名(属性名1,属性名2)
)
CREATE INDEX 索引名 ON 表名(属性名1,属性名2)
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_name(field1,field2,field3,.....fieldn) 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
运算符 | 描述 |
+ | 加法 |
- | 减法 |
* | 乘法 |
/ | 除法 |
% | 求余 |
SELECT field1 [AS] otherfield1,
field2 [AS] otherfield2,
fieldn [AS] otherfieldn FROM table_name
SELECT field1,field2,....fieldn FROM table_name WHERE CONDITION
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
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,则不会有任何的查询结果
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
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 X2 |
OR(||) | 或 | x1 OR x2 |
NOT(!) | 非 | NOTx1 |
XOR | 异或 | 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的结果集,然后再提交。