前言 MySQL
原本是一个开放源码的关系数据库管理系统,原开发者为瑞典的MySQL AB
公司,该公司于2008年被昇阳微系统(Sun Microsystems)收购。2009年,甲骨文公司(Oracle)收购昇阳微系统公司,MySQL成为Oracle旗下产品。(维基百科 )
注释 单行注释 1 2 3 4 -- 注释内容 # 注释内容 /* 注释内容 */ /*! 注释内容 */
多行注释
帮助 查看所有帮助 1 2 3 mysql> help contents; mysql> ? contents;
查看所有函数帮助
查看指定内容的帮助
<msg>
:指定内容
查看数据库状态
对数据库的操作 查询所有数据库
创建数据库 1 CREATE DATABASE <database_name>;
同时指定字符集 1 CREATE DATABASE <database_name> CHARSET utf8;
同时指定数据库和数据表的字符集 utf8 1 CREATE DATABASE <database_name> DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
utf8mb4 1 CREATE DATABASE <database_name> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
如果不存在 1 CREATE DATABASE IF NOT EXISTS <database_name>;
查看创建数据库语句 1 SHOW CREATE DATABASE <database_name>;
进入数据库
查看当前进入的数据库
删除数据库 1 DROP DATABASE <database_name>;
如果存在 1 DROP DATABASE IF EXISTS <database_name>;
修改数据库的默认字符集 1 ALTER DATABASE <database_name> DEFAULT CHARACTER SET <character_set>;
修改默认校对规则 1 ALTER DATABASE <database_name> DEFAULT COLLATE <proofreading_rules>;
对数据表的操作 创建数据表
<field_name>
:字段名<type_of_data>
:数据类型<length>
:长度(根据需求添加)<constraint>
:约束(根据需求添加)
1 2 3 4 CREATE TABLE <table_name>( <field_name_1> <type_of_data>(<length>) <constraint>, <field_name_2> <type_of_data>(<length>) <constraint> );
查询所有数据表
查询数据表结构
查询数据表结构(详细) 1 SHOW CREATE TABLE <table_name>
修改数据表名 1 ALTER TABLE <old_table_name> RENAME TO <new_table_name>
删除数据表 1 DROP TABLE <table_name>;
复制表
只复制表结构 1 CREATE <new_table_name> like <old_table_name>;
复制表结构和所有数据 1 CREATE TABLE <new_table_name> AS (SELECT * FROM <old_table_name>);
对字段的操作 增加字段 1 2 3 4 5 ALTER TABLE <table_name> ADD <new_filed_name> <new_type_of_data>(<length>) <constraint>; ALTER TABLE <table_name> ADD <new_filed_name> <new_type_of_data>(<length>) <constraint> first; ALTER TABLE <table_name> ADD <new_filed_name> <new_type_of_data>(<length>) <constraint> after <old_field_name>;
删除字段 1 ALTER TABLE <table_name> DROP COLUMN <filed_name>;
修改数据类型 1 ALTER TABLE <table_name> MODIFY <field_name> <new_type_of_data>;
修改字段 1 ALTER TABLE <table_name> CHANGE <old_field_name> <new_field_name> <new_type_of_data>(<new_length>) <new_constraint>
修改字段名 1 ALTER TABLE <table_name> CHANGE <old_field_name> <new_field_name> <new_type_of_data>;
修改字段排列位置 1 2 3 ALTER TABLE <table_name> MODIFY <field_name_1> <type_of_data_1> first <field_name_2>; ALTER TABLE <table_name> MODIFY <field_name_2> <type_of_data_1> after <field_name_2>;
对数据的操作 插入数据 1 2 3 INSERT INTO <table_name>(<field_name_1>, <field_name_2>) VALUES(<value_1>, <value_2>); INSERT INTO <table_name> SET <field_name_1>=<value_1>, <field_name_2>=<value_2>;
插入数据(多条) 1 INSERT INTO <table_name>(<field_name_1>, <field_name_2>) VALUES(value_1_1, value_1_2),(value_2_1, value_2_2);
插入数据(所有字段) 1 INSERT INTO <table_name> VALUES(<value_1>, <value_2>);
修改数据 1 UPDATE <table_name> SET <field_name_1>=<value_1>, <field_name_2>=<value_2> WHERE <field_name><operator><value>;
覆盖插入
1 2 3 REPLACE INTO <table_name> (<field_name_1>,<field_name_2>) VALUES(<value_1>,<value_2>); REPLACE INTO <table_name> SET <field_name_1>=<value_1>, <field_name_2>=<value_2>;
删除所有数据 1 2 3 DELETE FROM <table_name>; TRUNCATE TABLE <table_name>;
删除指定数据 1 DELETE FROM <table_name> WHERE <field_name><operator><value>
查询所有数据 1 SELECT * FROM <table_name>;
查询部分数据 1 SELECT <field_name_1>,<field_name> FROM <table_name>;
为查询到的数据定义表头别名 1 SELECT <old_field_name_1> AS <new_field_name_1> FROM <table_name>;
简写 1 SELECT <old_field_name_1> <new_field_name_1> FROM <table_name>;
表名也可以设置别名
表名也可以设置别名,也可以简写
但是指定表名别名后,后面只能使用别名,不能再使用原表名
1 2 3 SELECT * FROM <old_table_name> AS <old_table_name>; SELECT * FROM <old_table_name> <old_table_name>;
按需求查看数据 运算符 比较运算
=
、<
、>
、<=
、>=
、<>
、!=
、!<
、!>
多重条件
AND
、OR
、NOT
确定范围
BETWEEN...AND...
、NOT BETWEEN...AND...
确定集合
IN
、NOT IN
字符匹配
LIKE
、NOT LIKE
%
:表示0或多个任意的字符_
:表示1个任意的字符
测试空值
IS NULL
、IS NOT NULL
空值替换
一些数据存在NULL值时,参与运算结果为NULL,不准确,为了解决问题,将NULL值替换为0
<field_name>
:可能会出现NULL值的字段
1 SELECT IFNULL(<field_name>, 0) FROM <table_name>;
运算符的应用 按规则查询数据
<operator>
:运算符
1 SELECT * FROM <table_name> WHERE <field_name><operator><value>;
按规则替换数据 1 SELECT CASE WHEN <field_name><operator><old_value> THEN <new_value> END FROM <table_name>
查询重新计算后的数据
<operator>
:这里的运算符,指的是+
、-
、*
、/
、%
1 SELECT <field_name><operator> FROM <table_name>
不显示重复结果 1 SELECT DISTINCT <field_name> FROM <table_name>
聚合函数(多行函数)
不能用在WHERE子句中
会默认直接丢弃NULL值
求和
SUM()
求平均值
AVG()
求最大值
MAX()
求最小值
MIN()
计算总数
COUNT()
计算所有数据总行数 COUNT(*)
聚合函数的应用 查询计算后的数据
<function>
:函数名
1 SELECT <function>(<field_name>) FROM <table_name>;
分组查询
1 SELECT <field_name> FROM <table_name> GROUP BY <field_name>;
筛选
1 SELECT <field_name> FROM <table_name> GROUP BY <filed_name> HAVING <field_name><operator><value>;
WHERE和HAVING的区别
WHERE是在分组之前,对记录进行筛选过滤,HAVING是在分组之后,对记录进行筛选过滤 WHERE子句中不能使用多行函数以及列别名,HAVING子句中可以使用多行函数以及列别名、表别名
排序
升序(从小到大) 1 2 3 SELECT <field_name> FROM <table_name> ORDER BY <field_name>; SELECT <field_name> FROM <table_name> ORDER BY <field_name> ASC;
降序(从大到小) 1 SELECT <field_name> FROM <table_name> ORDER BY <field_name> DESC;
多重排序 1 SELECT <field_name> FROM <table_name> ORDER BY <field_name_1> ASC, <field_name_2> DESC;
多表查询(连接查询)
内连接 显式内连接 1 SELECT <table_name_x>.<field_name_x> FROM <table_name_1>,<table_name_2>,<table_name_3> WHERE <table_name_1>.<field_name_1>=<table_name_2>.<field_name_1> AND <table_name_2>.<field_name_2>=<table_name_3>.<field_name_2>;
隐式内连接 1 SELECT <table_name_x>.<field_name_x> FROM <table_name_2> INNER JOIN <table_name_1> ON <table_name_2>.<field_name_1>=<table_name_1>.<field_name_1> JOIN <table_name_3> ON <table_name_2>.<field_name_2>=<table_name_3><field_name_2>;
省略INNER关键字 1 SELECT <table_name_x>.<field_name_x> FROM <table_name_2> JOIN <table_name_1> ON <table_name_2>.<field_name_1>=<table_name_1>.<field_name_1> JOIN <table_name_3> ON <table_name_2>.<field_name_2>=<table_name_3><field_name_2>;
外连接 左外连接查询
左侧显示所有数据,右侧如果没有对应关系则显示为null
1 SELECT * FROM <table_name_1> LEFT JOIN <table_name_2> ON <table_name_1>.<field_name_2>=<table_name_2>.<field_name_1>;
右外连接查询
右侧显示所有数据,左侧如果没有对应关系则显示为null
1 SELECT * FROM <table_name_1> RIGHT JOIN <table_name_2> ON <table_name_1>.<field_name_2>=<table_name_2>.<field_name_1>;
(模拟)全外连接查询 去重(取交集)
通过UNION
关键字将左外连接和右外连接取交集
,模拟全外连接查询
两表展示出的列数必须相同
两表展示的表头名如果不同,可以用AS
关键字为表头添加别名
1 2 3 SELECT id,name FROM <table_name_1> LEFT JOIN <table_name_2> ON <table_name_1>.<field_name_2>=<table_name_2>.<field_name_1> UNION SELECT id,name FROM <table_name_2> RIGHT JOIN <table_name_2> ON <table_name_1>.<field_name_2>=<table_name_2>.<field_name_1>;
不去重(取并集)
通过UNION ALL
关键字将左外连接和右外连接取并集
,模拟全外连接查询
1 2 3 SELECT id,name FROM <table_name_1> LEFT JOIN <table_name_2> ON <table_name_1>.<field_name_2>=<table_name_2>.<field_name_1> UNION ALL SELECT id,name FROM <table_name_2> RIGHT JOIN <table_name_2> ON <table_name_1>.<field_name_2>=<table_name_2>.<field_name_1>;
分页查询
<index_first>
:每页的第一条数据在总数据中的下标(下标从0开始),所以公式为(当前页码数-1)*每页显示的数据总数
<index_all>
:每页显示的数据总数
1 SELECT <field_name> FROM <table_name> LIMIT <index_first>,<index_all>;
子查询
将一个sql语句查询的结果作为条件完成另一个sql语句查询
子查询结果是一行一列
<select>
:子查询的SQL语句
1 SELECT * FROM <table_name> WHERE <field_name><operator>(<select>);
子查询的结果是多行一列
1 SELECT * FROM <table_name> WHERE <field_name> IN (<select>);
子查询结果是多行多列
1 SELECT * FROM <table_name>,(<select>) WHERE <field_name><operator><field_value>;
对视图的操作
将特定条件下的查询结果创建为视图,可以通过对视图的操作简便查询
创建视图
<select>
:select语句
1 CREATE VIEW <view_name> AS <select>;
重建视图 1 REPLACE VIEW <view_name> AS <select>;
创建视图(强制) 1 CREATE OR REPLACE VIEW <view_name> AS <select>;
查询所有视图
查询视图结构
修改视图 1 ALTER VIEW <view_name> AS <select>;
删除视图
查询视图中的数据 1 2 3 SELECT * FROM <view_name>; SELECT <field_name> FROM <view_name>;
插入数据 1 INSERT INTO <view_name> VALUES(<value_1>, <value_2>);
修改数据 1 UPDATE <view_name> SET <field_name>=<value>;
删除数据 1 DELETE FROM <view_name> WHERE <field_name>=<value>;
对索引的操作 查看所有索引 1 SHOW INDEX FROM <table_name>;
创建索引 1 2 3 4 5 CREATE INDEX <index_name> ON <table_name>(<field_name>); ALTER TABLE <table_name> ADD INDEX <index_name>(<field_name_1>, <field_name_2>); CREATE TABLE <table_name>(<field_name> <operator>(<length>), INDEX <index_name>(field_name));
删除索引 1 2 3 DROP INDEX <index_name> <table_name>; ALTER TABLE <table_name> DROP INDEX <index_name>;
对约束的操作 操作约束的方式 在建表时添加约束(直接加到字段定义的后面)
<constraint>
:约束(同时指定多个约束时,用空格分隔)
NULL
:可以为空,缺省值NOT NULL
:非空约束,定义这个字段不允许为空PRIMARY KEY
:主键约束,必须为非空,必须不重复
AUTO_INCREMENT
:如果是数值型主键,可以定义逐渐自增UNIQUE
:唯一约束,定义这个列的值必须是唯一的、不能重复的,但可以为空DEFAULT 默认值
:默认约束,当没有为字段赋值时的默认值
1 CREATE TABLE <table_name>(<field_name> <operator>(length) <constraint>);
在建表时添加约束(定义在所有字段定义的后面)
<constraint>
:约束(同时指定多个约束时,每行一种约束的定义)
PRIMARY(<field_name_1>)
:定义主键约束,必须为非空,必须不重复PRIMARY(<field_name_1>, <field_name_2>)
:定义联合主键约束,每个主键字段的数值可以重复,但是多个联合主键的数值的并集不可以重复UNIQUE(<field_name>)
:定义唯一约束,定义这个列的值必须是唯一的、不能重复的,但可以为空CHECK(<field_name><operator><value>)
:定义完整性约束,为字段定义范围
1 2 3 4 5 CREATE TABLE <table_name>( <field_name_1> <operator>(length), <field_name_2> <operator>(length), <constraint> );
修改约束(删除约束)
<constraint>
:修改后的约束(同时指定多个约束时,用空格分隔),如果修改后不添加约束,相当于删除已有约束
1 2 3 ALTER TABLE <table_name> MODIFY <field_name> <operator>(length) <constraint>; ALTER TABLE <table_name> MODIFY <field_name> <operator>(length);
外键约束
通过外键约束将表与表建立联系,当出现有损表的完整性操作时,外键约束会对表进行保护
一对多 创建表时直接添加外键 1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE 职位表( 职位编号 <type_of_data>, 职位名称 <type_of_data> ); CREATE TABLE 员工表( 员工编号 <type_of_data>, 员工名称 <type_of_data>, 员工表_职位编号 <type_of_data>, FOREIGN KEY(员工表_职位编号) REFERENCES 职位表(职位编号) );
对已创建的表添加外键 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE 职位表( 职位编号 <type_of_data>, 职位名称 <type_of_data> ); CREATE TABLE 员工表( 员工编号 <type_of_data>, 员工名称 <type_of_data>, 员工表_职位编号 <type_of_data> ); ALTER TABLE 员工表 ADD CONSTRAINT 外键名 FOREIGN KEY(员工表_职位编号) REFERENCES 职位表(职位编号);
1 ALTER TABLE 员工表 ADD CONSTRAINT FOREIGN KEY(员工表_职位编号) REFERENCES 职位表(职位编号);
删除外键 1 ALTER TABLE 员工表 DROP FOREIGN KEY 外键名;
多对多 创建表时直接添加外键 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 CREATE TABLE 职位表( 职位编号 <type_of_data>, 职位名称 <type_of_data> ); CREATE TABLE 等级表( 等级编号 <type_of_data>, 等级名称 <type_of_data> ); CREATE TABLE 员工表( 员工编号 <type_of_data>, 员工名称 <type_of_data>, 员工表_职位编号 <type_of_data>, 员工表_等级编号 <type_of_data>, FOREIGN KEY(员工表_职位编号) REFERENCES 职位表(职位编号), FOREIGN KEY(员工表_等级编号) REFERENCES 等级表(等级编号) );
其他函数
函数
备注
curdate()
获取当前日期,格式是:年月日
curtime()
获取当前时间 ,格式是:时分秒
sysdate()/now()
获取当前日期+时间,格式是:年月日 时分秒
year(date)
返回date中的年份
month(date)
返回date中的月份
day(date)
返回date中的天数
hour(date)
返回date中的小时
minute(date)
返回date中的分钟
second(date)
返回date中的秒
CONCAT(s1,s2..)
将s1,s2 等多个字符串合并为一个字符串
CONCAT_WS(x,s1,s2..)
同CONCAT(s1,s2,..)函数,但是每个字符串之间要加上x,x是分隔符
完成 参考文献 博客园——Bigben 博客园——下页、再停留 CSDN——时光·漫步zth 哔哩哔哩——黑马程序员 哔哩哔哩——黑马程序员 CSDN——念去去千里烟波 CSDN——辛丑年正月十五