MySQL数据库汇总

最新推荐文章于 2024-09-19 19:08:57 发布
最新推荐文章于 2024-09-19 19:08:57 发布
阅读量134 收藏 1
点赞数
分类专栏： python 文章标签： mysql
原文链接：https://blog.csdn.net/qq_37969433/article/details/102637226
版权

目录

一、准备

下文整理常见SQL语句的用法，使用MySQL5.7测试，参考了尚硅谷MySQL教程及用例。用例数据：
链接: https://pan.baidu.com/s/10_galApgP55qCxltU0rTlA
密码: knno

二、SQL常用数据类型

1、数值型

1）整型
类型所占字节数范围(有符号/无符号)
TINYINT1-128-127
0-255
SMALLINT2-32768-32767
0-65535
MEDIUMINT3-8388608-8388607
0-16777215
INT(INTEGER)4-2147683648-2147683647
0-4294967295
BIGINT8-263–263-1
0–264-1
  • 默认有符号，需要无符号的话，用UNSIGNED INT
  • 插入超过范围的数，最终为临界值
  • 整型的长度代表显示的宽度，如果要使用，需要搭配zerofill使用，对于int(M)，如长度小于M，用0左填充至宽度为M，如果大于M则无影响。没实际意义。
2）小数
类型所占字节数
DECIMAL(M,D)M+2定点数
FLOAT(M,D)4浮点数
DOUBLE(M,D)8浮点数
  • D保留小数位数
  • M整数+小数位数和
  • MD可以省略，DECIMAL默认为(10,0)，FLOAT和DOUBLE会根据实际插入的值来确定
  • 定点型精确度高一点，一些高精度要求的可以用定点型，如货币汇率等

2、字符型

类型备注
CHAR(M)存放长度固定或有限的字符
VARCHAR(M)存放长度不固定的字符
TEXT存放长文本
BINARY(M)存放长度固定或有限的二进制
VARBINARY(M)存放长度不固定的二进制
BLOB存放较大的二进制，如图片等
ENUM()枚举
SET()集合
  • M为最多的字符数，“abc”是3个字符，“你好”是两个字符
  • CHAR是固定长度的字符，可省略M，默认为1，费空间，效率高
  • VARCHAR是可变长度字符，不可以省略M，M为最大长度，省空间，效率低
  • BINARY和VARBINARY与CHAR和VARCHAR类似用法
  • ENUM(“a”,“b”,“c”) ，多选一，只能保存"a",“b”,"c"其中之一，不区分大小写，在mysql5.7中如果插入非列表中的内容，则为null
  • SET(“a”,“b”,“c”)，多选多，能保存一个或多个abc中的值，如保存"a,b"，不区分大小写，在mysql5.7如果插入非列表中的内容，则报错

3、日期型

字节保存
DATE4年-月-日
DATETIME8年-月-日 时-分-秒
TIMESTAMP4时间戳，时区影响小，占空小，用的多
TIME3时-分-秒
YEAR1年

三、SQL字段约束

1、六大约束

约束类型含义备注
NOT NULL非空
PRIMARY KEY主键保证值的唯一性，且非空
UNIQUE唯一保证值的唯一性，可以为空
DEFAULT默认指定字段的默认值
CHECK检查保证必须是满足条件的值，mysql不支持
FOREIGN KEY外键用于限制两个表的关系
  • 外键说明
    • 保证从表的值必须来自于主表的某一列的值，需在从表中添加外键。
    • 主表从表对应的字段类型要一致或兼容
    • 主表的字段必须是一个key（一般是主键或唯一）
    • 插入数据时，必须先插入主表再插入从表
  • 主键与唯一的区别
    • 主键具有唯一性，不允许为null，一张表最多一个，可以组合使用（即多个字段为组合为一个主键）但不推荐。
    • 唯一具有唯一性，允许为null且在mysql5.7中默认可以有多个null，一张表可以有多个，可以组合使用但不推荐

2、标识列（自增长列）

  • 标识列用AUTO_INCREMENT设置
  • 标识列必须是主键或唯一
  • 一个表中最多一个标识列
  • 标识列类型只能是数值型

有关约束和标识列的使用，在DDL表的管理部分有整理

四、DQL（Data Query Language）数据查询语言

1、语句顺序

  • 书写顺序：SELECT、DISTINCT、FROM、JOIN ON、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT
  • 执行顺序：FROM、 JOIN ON、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、ORDER BY、LIMIT

2、基础查询（SELECT）

SELECT 查询内容 FROM 表名

(1)查询常量
SELECT 200;
SELECT 'hello';

 
 

 
 
  • 1
  • 2
(2)查询表达式

对于"+"运算符，仅用于数字类型的相加。若运算数为字符，尝试转化为数字，若转换失败，则认为是0;若运算数为null，结果为null。如下：

SELECT 100%3;
SELECT '123'+9;  #结果为 132，'123'-->123
SELECT 'haha'+9;  # 结果为 9，’haha’-->0
SELECT null+9;  # 结果为 null

 
 

 
 
  • 1
  • 2
  • 3
  • 4
(3)查询字段
a.表中字段查询
SELECT first_name FROM employees; #查询单个字段
SELECT first_name,last_name FROM employees; #查询多个字段
SELECT * FROM employees; #查询所有字段

 
 

 
 
  • 1
  • 2
  • 3
b.使用别名（AS）
SELECT first_name AS '名',last_name  '姓' FROM employees; #查询结果使用别名

 
 

 
 
  • 1
c.去重（DISTINCT）
SELECT DISTINCT department_id FROM employees; #查询结果去重

 
 

 
 
  • 1
(3)查询函数

SQL提供了很多现成函数，常用的大致可分为单行函数和分组函数。

  • 单行函数为处理一条数据，输出一个结果，如对字符串的处理等。
  • 分组函数又称聚合函数、统计函数或组函数，是对多条记录的统计结果，如求和等。
a.单行函数

根据处理的数据类型不同，单行函数又可细分为字符函数、数学函数、日期函数、流程控制函数等

·字符函数
  • LENGTH(str) 返回字符串长度
		 SELECT LENGTH('hello'); #结果为5
		 SELECT LENGTH(last_name); #结果为last_name字段的长度

 
 

 
 
  • 1
  • 2
  • CONCAT(str1,str2) 拼接字符串
		SELECT CONCAT(last_name,'-',first_name); #结果为 last_name字段 - first_name字段

 
 

 
 
  • 1
  • UPPER(str) | LOWER(str) 转换为大/小写
		 	SELECT UPPER('hello'); #结果为'HELLO'
		 	SELECT LOWER('HeLLo');#结果为'hello'

 
 

 
 
  • 1
  • 2
  • SUBSTR(str,pos,len) 截取字符串，字符串索引从1开始
			SELECT SUBSTR('my name is xiaoming',4,4); #结果为'name'
		  	SELECT SUBSTR('my name is xiaoming',4); #结果为'name is xiaoming'

 
 

 
 
  • 1
  • 2
  • INSTR(str,substr) 返回子串第一次出现的索引，字符串索引从1开始
			 SELECT INSTR('my name is xiaoxiao','xiao'); #结果为12

 
 

 
 
  • 1
  • TRIM(str,substr) 首尾去除规定字符，默认去空格
			   SELECT TRIM('   my  name  is  xiaoming  ');
			   #结果为'my  name  is  xiaoming'
               SELECT TRIM('7' FROM '77my  name 777 is  xiaoming  777');
               #结果为'my  name 777 is  xiaoming  '

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • LPAD(str,len,padstr) | RPAD(str,len,padstr) 用规定字符左(右)填充至指定长度
			   SELECT LPAD('my',10,'*');#结果为'********my'
               SELECT RPAD('my',10,'ab');#结果为'myabababab'

 
 

 
 
  • 1
  • 2
  • REPLACE(str,old,new) 字符串替换
			   SELECT REPLACE('my name is xiaoxiao','xiao','da');#结果为'my name is dada'

 
 

 
 
  • 1
·数学函数
  • ROUND(X,D) 四舍五入，保留指定位数，默认为保留整数
			   SELECT ROUND(-1.65); #-2
			   SELECT ROUND(-1.65,1); #-1.7

 
 

 
 
  • 1
  • 2
  • CEIL(X) | FLOOR(X) 向上|向下取整
			   SELECT CEIL(1.44);#2
			   SELECT FLOOR(1.55);#1

 
 

 
 
  • 1
  • 2
  • TRUNCATE(X) 截取保留指定小数位
			   SELECT TRUNCATE(2.666,1); #2.6

 
 

 
 
  • 1
  • MOD(X1,X2) 取模
			   SELECT MOD(10,3);#1,符号与被除数一致
			   SELECT MOD(-10,3);#-1
			   SELECT MOD(10,-3);#1

 
 

 
 
  • 1
  • 2
  • 3
·日期函数
  • NOW()、CURDATE() 当前时间、当前日期
			   SELECT NOW();#2019-10-19 14:40:54
			   SELECT CURDATE();#2019-10-19

 
 

 
 
  • 1
  • 2
  • YEAR() | MONTH() | DAY() | HOUR() | MINUTE() | SECOND()
			   SELECT YEAR('2019-10-1');#2019

 
 

 
 
  • 1
  • DATEDIFF(date1,date2) | TIMEDIFF(date1,date2) 两个日期相差的天数|两个时刻相差的时间
			   SELECT DATEDIFF('2019-10-17','2019-10-1');#结果为16，前面的减后面的
			   SELECT TIMEDIFF('2019-10-17 15:30:2','2019-10-1 7:0:0');#结果为 392:30:02

 
 

 
 
  • 1
  • 2
  • STR_TO_DATE(str,format) 字符串按格式转为日期
  • DATE_FORMAT(date,format) 日期按格式转为字符串
    符号意义符号意义符号意义
    %Y四位年月%y两位年月%m月份(01,02…)
    %c月份(1,2,…)%d日(01,02…)%H小时(24制)
    %h小时(12制)%i分钟(01,02…)%s秒(01,02…)
			   SELECT STR_TO_DATE('10-1 2019','%c-%d %Y');#2019-10-01
			   SELECT DATE_FORMAT(NOW(),'%Y.%m.%d');#2019.10.19

 
 

 
 
  • 1
  • 2
·流程控制函数
  • IF 二选一
			   SELECT IF(10>5,'yes','no');#结果为'yes'

 
 

 
 
  • 1
  • CASE-WHEN-THEN-END 多选一
	用法1，相当于Java的which，判断一个*值*：
   		        SELECT last_name,job_id,CASE job_id
                 	WHEN 'AD_PRES' THEN '1'
                 	WHEN 'AD_VP' THEN '2'
                 	WHEN 'IT_PROG' THEN '3'
                 	ELSE '4'
                 	END  FROM employees;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
	用法2，相当于Java的if-else if-else，判断一个*表达式*：
			       SELECT last_name,CASE
                  	WHEN salary<5000 THEN
                  		'<5000'
                  	WHEN salary BETWEEN 5000 AND 10000 THEN
                  		'5000-10000'
                  	ELSE
                  		'>10000'
                  	END  FROM employees;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
b.分组函数

分组函数又称为聚合函数、统计函数、组函数，所有分组函数对null值处理为忽略它，而非当做0。常用的分组函数有以下几个：

  • SUM() 对数值型数据求和，用+相加，符合+的运算法则
  • AVG() 对数值型数据求平均
  • MAX() 对所有可比较类型求最大值
  • MIN() 对所有可比较类型求最小值
  • COUNT() 统计非空个数

分组函数的调用为：

SELECT SUM(salary) FROM employees;
SELECT COUNT(*) FROM employees;#统计表的总行数
SELECT COUNT(1) FROM employees;#统计表的总行数

 
 

 
 
  • 1
  • 2
  • 3

3、条件查询（WHERE）

SELECT 查询内容 FROM 表名 WHERE 条件

（1）运算符做条件

sql语法中运算符有:

运算符含义运算符含义
>大于<小于
=等于,不可用于null<> 或!=不等于
<=小于等于>=大于等于
<=>安全等于，可以用于null
#查询部门编号不等于90的员工信息
SELECT * FROM employees WHERE department_id<>90;

 
 

 
 
  • 1
  • 2
（2）逻辑表达式做条件

sql语法中逻辑表达式有:

表达式含义
AND与
OR或
NOT非
#查询部门编号大于50小于100的员工信息
SELECT * FROM employees WHERE department_id > 50 AND department_id < 100;

 
 

 
 
  • 1
  • 2
（3）模糊查询

sql中用于模糊查询的关键字有：

关键字含义备注
LIKE模糊匹配%任意0个或多个字符; _任意1个字符；无法匹配null
BETWEEN a AND b匹配a-b范围内的内容包括a和b临界
IN(list)匹配list中的内容
IS NULL / IS NOT NULL判断是否为null建议使用这个而非<=>
#查询last_name第二个字符是a的员工
SELECT * FROM employees WHERE last_name LIKE '_a%';
#查询工资在10000到20000的员工信息，包括10000和20000
SELECT * FROM employees WHERE salary BETWEEN 10000 AND 20000;
#查询职位为D_VP和FI_MGR的员工信息
SELECT * FROM employees WHERE job_id IN ('D_VP','FI_MGR');
#查询没有奖金的员工信息
SELECT * FROM employees WHERE commission_pct IS NULL;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

4、排序查询（ORDER BY）

SELECT 查询内容 FROM 表名 ORDER BY [ASC|DESC]
ASC：升序，默认项
DESC：降序

（1）基本排序
#按月薪降序
SELECT * FROM employees ORDER BY salary DESC;
#按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) salary_total 
FROM employees ORDER BY salary_total DESC;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
（2）按函数排序
#按名字长度降序排序
SELECT * 
FROM employees 
ORDER BY LENGTH(last_name) DESC;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
（3）按多个字段排序
#先按salary降序排序，同样salary的按employee_id升序排序
SELECT * 
FROM employees 
ORDER BY salary DESC , employee_id ASC;

 
 

 
 
  • 1
  • 2
  • 3
  • 4

5、分组查询（GROUP BY）

SELECT 查询内容 FROM 表名 [WHERE 条件] GROUP BY 分组列表 [HAVING 条件]

  • 查询内容中的字段，必须要出现在分组列表中
  • WHERE是在分组前对原始表筛选，HAVING是在分组后对结果表筛选
  • 分组查询中，若用分组函数做筛选的条件，一定是在HAVING子句中
（1）按单字段分组
#查询每个部门的员工数量
SELECT department_id,COUNT(1) 
FROM employees 
GROUP BY department_id ;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
（2）按多字段分组
#查询每个部门，每个职务的平均工资
SELECT AVG(salary),department_id,job_id 
FROM employees 
GROUP BY department_id,job_id;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
（3）按函数分组
#按名字长度分组，并筛选出数量大于5的名字长度
SELECT COUNT(1) c,LENGTH(last_name) len_name 
FROM employees 
GROUP BY len_name 
HAVING c>5;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5

6、连接查询（JOIN）

（1）sql92标准

SELECT 查询列表
FROM 表1,表2
WHERE 连接条件
[AND 查询条件]

a.内连接

sql92标准只支持内连接，内连接即用来查询两张表的"交集"部分，即满足条件的、两张表都存在的部分。内连接又分为等值连接、非等值连接和自连接。

等值连接

等值连接即以两张表的两个字段的值相等为连接条件，进行两张表的连接，如：

#查询每个员工的job_title
SELECT e.last_name,e.job_id,job_title 
FROM employees e,jobs j 
WHERE e.job_id = j.job_id;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
非等值连接

非等值连接即以两张表的两个字段的值满足一定条件为连接条件，进行两张表的连接，如：

#查询某个员工的工资等级
SELECT e.last_name,j.grade_level 
FROM employees e,job_grades j 
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
自连接

自连接即一张表和本身的连接，因涉及到同一张表的连接，一般要对这张表使用不同的别名加以区分，如：

#查询某个员工及其对应的领导
SELECT e1.last_name 'employee',e2.last_name 'manager'
FROM employees e1,employees e2 
WHERE e1.manager_id = e2.employee_id;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
（2）sql99标准

SELECT 查询列表
FROM 表1
[INNER|LEFT OUTER|RIGHT OUTER|FULL OUTER|UNION] JOIN 表2
ON 连接条件
[WHERE 查询条件]

a.内连接（INNER JOIN）

sql99的内连接含义与sql92语法完全一致，只是语法不同。用sql92的三个例子改为sql99语法形式如下：

等值连接
#查询每个员工的job_title
SELECT e.last_name,e.job_id,job_title 
FROM employees e 
INNER JOIN jobs j 
ON e.job_id = j.job_id;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
非等值连接
#查询某个员工的工资等级
SELECT e.last_name,j.grade_level 
FROM employees e
INNER JOIN job_grades j 
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
自连接
#查询某个员工及其对应的领导
SELECT e1.last_name 'employees',e2.last_name 'manager'
FROM employees e1
INNER JOIN employees e2 
ON e1.manager_id = e2.employee_id;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
b.外连接（OUTER JOIN）

在sql99标准中，又增加的外连接的功能。外连接又包括左外连接、右外连接和全外连接。多用来查询一张表中有，另一张表中没有的记录。

外连接会查询主表的所以记录
若主表中的某条记录在从表中有与之对应的记录，则相应的字段为从表的内容；
反正，相应的字段为null。
换句话说，外连接查询结果=内连接查询结果+主表有但从表没有的记录

对于主从表的区分，有以下几种：
左外连接：主表 LEFT JOIN 从表
右外连接：从表 RIGHT JOIN 主表
全外连接：表1 FULL JOIN 表2，其中表1表2分别轮流作为主表，查询结果是两张表的"并集"。另外，全外连接在MySQL中不支持使用。

左外连接（LEFT JOIN ON）
#查询没有迟到记录的员工名
SELECT last_name,late.count_late 
FROM employees
LEFT JOIN late 
ON employees.employee_id = late.employee_id 
WHERE late.count_late IS NULL;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
右外连接（RIGHT JOIN ON）
#查询没有迟到记录的员工名
SELECT last_name,late.count_late
FROM late 
RIGHT JOIN employees 
ON employees.employee_id = late.employee_id 
WHERE late.count_late IS NULL;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
全外连接（FULL JOIN ON）

全外连接在mysql中不支持，语法如下：

SELECT beauty.name 
FROM boys 
FULL OUTER JOIN beauty 
ON beauty.boyfriend_id = boys.id ;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
c.交叉连接（CROSS JOIN）

效果即笛卡尔积形式，即两个表的所有记录都一一匹配一遍，查询的结果一共A×B条记录（A、B分别为两个表的记录数）

SELECT * FROM jobs CROSS JOIN locations;

 
 

 
 
  • 1

7、子查询

嵌套在其他语句的SELECT语句为子查询(内查询)，外部的查询语句为主查询(外查询)

可分类为：

  • 标量子查询（结果集只有一行一列）
  • 列子查询（结果集多为一列多行）
    • IN、NOT IN：等于/不等于列表中的任意一个
    • ANY/SOME：子查询中某一个值满足就行
    • ALL：子查询中所以值都满足
  • 行子查询（结果集多为一行多列）
  • 表子查询（结果集有多行多列）
（1）子查询在SELECT后面

只支持标量子查询，如

#查询每个部门的信息加部门的人数
SELECT * ,
(SELECT COUNT(1) FROM employees WHERE employees.department_id = departments.department_id)
 FROM departments;
  • 1
  • 2
  • 3
  • 4
  • 5
（2）子查询在FROM后面

支持表子查询，在一个SELECT查询后的表中查询新的内容，如：

#查询每个部门的平均工资的工资等级
SELECT department_id,department_name,a , j.grade_level 
FROM (
	SELECT e.department_id ,d.department_name, AVG(salary) a 
	FROM employees e 
	LEFT JOIN departments d 
	ON  e.department_id=d.department_id 
	GROUP BY e.department_id
) tmp 
INNER JOIN job_grades j 
ON tmp.a BETWEEN j.lowest_sal AND j.highest_sal;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
（3）子查询在WHERE/HAVING后面

支持标量子查询、列子查询、行子查询

#标量子查询：查询工资最少的员工信息
SELECT * 
FROM employees 
WHERE salary = (
	SELECT MIN(salary)
	FROM employees
);

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
#列子查询：查询location_id为1400或1500或2700的部门中的所有员工姓名
SELECT last_name 
FROM employees 
WHERE department_id IN (
	SELECT department_id 
	FROM departments 
	WHERE location_id IN(1400,1500,2700)
);

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
#行子查询：查询编号最小并且工资最高的员工信息
#满足行子查询的条件笔记苛刻，所以用的不多
SELECT * 
FROM employees 
WHERE (employee_id,salary) = (
	SELECT MIN(employee_id),MAX(salary) 
	FROM employees
);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
（4）子查询在EXISTS后面（相关子查询）

EXISTS(SELECT 语句)：有记录，则为1，无记录，则为0
相关子查询是先执行外查询，在由EXISTS过滤； 都能用IN代替

#查询有员工的部门名
SELECT department_name 
FROM departments d 
WHERE EXISTS(
	SELECT * FROM employees e WHERE e.department_id = d.department_id
);
#用IN代替
SELECT department_name 
FROM departments d
 WHERE d.department_id IN (
 	SELECT DISTINCT department_id 
 	FROM employees
 );
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

8、分页查询（LIMIT）

SELECT 查询列表
FROM 表名
LIMIT offset,size;

  • offset 要查询的起始索引，从0开始，缺省为0
  • size 要查询的记录数目
#查询有奖金的员工中工资最高的10个员工的信息
SELECT * 
FROM employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 0, 10;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

9、联合查询（UNION）

SELECT完整语句1
UNION [ALL]
SELECT完整语句2
UNION [ALL]
…
将多条查询语句的结果合并为一个结果，结果的字段名为第一个查询的字段。

  • 要求各部分字段列数一致
  • 要求各部分字段顺序一致
  • UNION默认去重，不去重用UNION ALL
#查询department_id>50和salary>5000的员工信息
SELECT * 
FROM employees 
WHERE department_id > 50 
UNION 
SELECT * 
FROM employees 
WHERE salary > 5000 ;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

五、DML（Data Manipulation Language）数据操作语言

1、增（INSERT）

1）方式一

INSERT INTO 表名(字段1，字段2…) VALUE (值1，值2 …)

  • 插入的值的类型要与列的类型一致或兼容

  • 可以为null的列或有默认值的列可以不写（默认为null，或默认值）

  • 列的顺序可以颠倒，与值对应即可

  • 可以省略列名不写，默认是所有列名

    INSERT INTO 表名 VALUE (值1，值2 ...)

   
   
    
   
   
    • 1

  • 支持多行插入

    INSERT INTO 表名 VALUE (值1，值2 ...),(值1，值2 ...),(值1，值2 ...)...

   
   
    
   
   
    • 1

  • 支持子查询，插入查询的结果

    INSERT INTO 表名(字段1,字段2...) SELECT 字段1,字段2... FROM...

   
   
    
   
   
    • 1
2）方式二

INSERT INTO 表名 SET 字段1=值1,字段2=值2…

2、删（DELETE）

1）方式一
a.删除单表记录

DELETE FROM 表名 WHERE 筛选条件

b.删除多表记录（连接删除）
sql92语法

DELETE [表1|表2]
FROM 表1,表2
WHERE 连接条件
AND 筛选条件

sql99语法

DELETE [表1|表2]
FROM 表1
[LEFT|RIGHT|FULL|INNER] JOIN 表2
ON 连接条件
WHERE 筛选条件

2）方式二

TRUNCATE TABLE 表名

3）比较
  • TRUNCATE不能加筛选条件，只是清空整个表。
  • DELETE删除后，自增长列从断点开始，可以返回受影响的行数，效率低，可以回滚
  • TRUNCATE删除后，自增长列从1开始，不可以返回受影响的行数，效率高，不可以回滚

3、改（UPDATE）

1）方式一
a.修改单表记录

UPDATE 表名
SET 字段1=新值1,字段2=新值2…
WHERE 筛选条件

b.修改多表记录（连接修改）
sql92语法

UPDATE 表1,表2
WHERE 连接条件
AND 筛选条件
SET 字段1=新值1,字段2=新值2…

sql99语法

UPDATE 表1
[INNER|LEFT|RIGHT|FULL] JOIN 表2
ON 连接条件
WHERE 筛选条件
SET 字段1=新值1,字段2=新值2…

六、DDL（Data Define Language）数据定义语言

1、库的管理

1）库的创建（CREATE）
CREATE DATABASE IF NOT EXISTS database1;

 
 

 
 
  • 1
2）库的修改（ALTER）
#修改字符集
ALTER DATABASE database1 CHARACTER SET utf-8或gbk;

 
 

 
 
  • 1
  • 2
3）库的删除（DROP）
DROP DATABASE IF EXISTS database1;

 
 

 
 
  • 1

2、表的管理

1）表的创建（CREATE）
a.普通创建
CREATE TABLE  IF NOT EXISTS table_name(
				字段名 字段类型 ,
				……
				字段名 字段类型 
			)

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
b.复制创建
#复制表的所有结构
CREATE TABLE new_table LIKE old_table;
#复制表的部分结构
CREATE TABLE new_table SELECT list FROM old_table WHERE 0;
#复制表的所有结构+所有数据
CREATE TABLE new_table SELECT * FROM old_table;
#复制表的部分结构+所有数据
CREATE TABLE new_table SELECT field_list FROM old_table;
#复制表的部分结构+部分数据
CREATE TABLE new_table SELECT field_list FROM old_table WHERE condition

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
c.带约束创建

创建表时的约束可分为列级约束和表级约束，在mysql中：

  • 列级约束：支持主键、唯一、非空、默认
  • 表级约束：支持主键、唯一、外键
  • 一般的，外键用表级约束添加，其他的用列级约束添加
#创建表时添加列级约束
      CREATE TABLE user(
      	id INT PRIMARY KEY, #主键
      	name VARCHAR(20) NOT NULL, #非空
      	no INT UNIQUE, #唯一
      	flag TINYINT DEFAULT 1, #默认
      )
#创建表时添加表级约束
      CREATE TABLE user(
      	id INT,
      	no INT ,
      	book_id INT,
      	#以下添加表级约束
      	CONSTRAINT pk PRIMARY KEY(id), #主键
      	UNIQUE(no),#唯一，可以省略CONSTRAINT(其他约束也可一省略)
      	CONSTRAINT fk_user_book FOREIGN KEY(book_id) REFERENCES book(id) #外键，建议取个约束名
      ) 

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
d.带标识列创建

同约束的设置方法，在创建表时，在后面添加 AUTO_INCREMENT

CREATE TABLE user(
			id INT UNIQUE AUTO_INCREMENT,
			name VARCHAR(20)
)

SET auto_increment_increment=3 #设置步长
SET auto_increment_offset=3 #设置起始值，mysql不支持，但可以通过插入数据时来手动插入一个起始值

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
2）表的修改（ALTER）

  • 修改表名

    ALTER TABLE 表名 RENAME TO  新表名;

   
   
    
   
   
    • 1

  • 修改字段名

    ALTER TABLE 表名 CHANGE COLUMN 字段名 新字段名;

   
   
    
   
   
    • 1

  • 修改字段的类型、约束或添加约束

    ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 约束类型;

   
   
    
   
   
    • 1

  • 添加新列

    ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 约束类型;

   
   
    
   
   
    • 1

  • 删除列

    ALTER TABLE 表名 DROP COLUMN 字段名;
  • 1
  • 2

  • 删除约束

    	ALTER TABLE user DROP PRIMARY KEY;#删除主键
	ALTER TABLE user DROP  FOREIGN KEY  fk_user_book;#删除外键

  
  
    
  
  
    • 1
    • 2
  • 3）表的删除（DROP）
    DROP TABLE IF EXISTS 表名;

 
 
    
 
 
    • 1

    七、TCL（Transaction Control Language）事务控制语言

    使用事务一般有以下步骤：

    • step1：关闭自动提交
      SET autocommit = 0 #默认为1，自动提交
    • step2:写多条sql语句
      ……
      ……
    • step3:结束事务
      成功：COMMIT 提交事务
      失败：ROLLBACK 回滚

    即：

    SET autocommit = 0; #关闭自动提交
START TRANSACTION;
INSERT INTO book values(1,'book1');
INSERT INTO book values(2,'book2');
COMMIT;#提交，插入两条记录成功

    SET autocommit = 0; #关闭自动提交
    START TRANSACTION;
    INSERT INTO book values(1,‘book1’);
    INSERT INTO book values(2,‘book2’);
    ROLLBACK;#回滚，不插入记录

    SET autocommit = 0; #关闭自动提交
    START TRANSACTION;
    INSERT INTO book values(1,‘book1’);
    SAVEPOINT a;
    INSERT INTO book values(2,‘book2’);
    ROLLBACK a;#回滚到a，第一条插入，第二条不插入

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    八、DCL（Data Control Language）数据控制语言

    1、补充用户基本操作

    mysql [-h localhost -P 3306] -u root -p;#登录
exit;#退出
show databases;#查看所有库
use db_name;#进入库
select database();#查看当前库
show tables [from dbname];#查看表
desc tablename;#查看表的结构

 
 
    
 
 
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    2、创建用户

    CREATE USER 用户名@‘IP地址’ IDENTIFIED BY '密码'；#在指定ip访问
CREATE USER 用户名@’%’  IDENTIFIED BY '密码'； #所有ip都可访问

 
 
    
 
 
    • 1
    • 2

    3、给用户授权

    GRANT 权限 ON 库名.表名 to 用户名@’IP地址’;
#如果是所有权限，则权限处用ALL
#如果是多个权限，中间用,隔开
#如果是所有库，库名用*
#如果是所有表，表名用*
#如果是所有IP，ip地址用%

 
 
    
 
 
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    常用权限：
    USAGE：默认用，不可删，登录权限
    SELECT：查询权限
    INSERT：插入权限
    UPDATE：更改权限
    DELETE：删除权限
    CREATE：建表权限
    CREATE VIEW：创建视图权限
    ALTER：修改表权限
    DROP：删除表权限index
    INDEX：创建/删除索引
    GRANT OPTION：将自己有的权限授予其他人

    4、撤销权限

    REVOKE 权限 ON 库名.表名 to 用户名@’IP地址’;

 
 
    
 
 
    • 1

    5、查看权限

    SHOW GRANTS FOR 用户名@’IP地址’;

 
 
    
 
 
    • 1

    6、删除用户

    DROP USER 用户名@‘IP地址’;

 
 
    
 
 
    • 1

    九、视图

    视图是在mysql5.1版本出现的新特性，它是一张虚拟表，和普通表一样的用法。但在几个特点的条件下，视图不支持更新，一般也不对视图进行更新。在物理空间中，只保存视图的sql逻辑结构，而不保存具体的数据，在使用时，动态生成查询数据。应用场景：

    • 多个地方用到同样的查询结果
    • 查询语句较为复杂

    1、视图创建

    CREATE VIEW view_name
AS
SELECT语句

 
 
    
 
 
    • 1
    • 2
    • 3

    2、视图修改

    #方式一
CREATE OR REPLACE VIEW view_name
AS
SELECT 语句
#方式二
ALTER VIEW view_name
AS
SELECT 语句

 
 
    
 
 
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    3、视图删除

    DROP VIEW view_name,view_name…;#支持同时删除多个视图

 
 
    
 
 
    • 1
                                    </div>
qq_40389637
关注
专栏目录
MySQL数据库PDF文档
10-20
"MySQL数据库PDF文档" 本文档主要介绍了MySQL数据库的基本概念、数据类型、SQL语言、约束条件、查询语言、聚合函数、子查询和多表查询等知识点。 数据库基础概念 在介绍MySQL数据库之前，首先需要了解数据库的...
mysql数据库常用命令汇总
12-25
MySQL数据库常用命令汇总 MySQL数据库是目前最流行的开源关系数据库管理系统，它提供了丰富的命令来管理和操作数据库。本文汇总了MySQL数据库常用的命令，包括数据库操作、表操作和数据操作三部分。 数据库操作 1...
MySQL数据库面试题汇总
weixin_45014379的博客
08-23 1432
MySQL是一种关系型数据库管理系统，它使用SQL语言进行查询和管理数据。它是一种开源软件，可以在多个操作系统上运行。在数据中，索引相当于字典的目录一下，通过索引可以快速的检索数据。顾名思义，最左优先，以最左边为起点任何连续的索引都能匹配上。同时遇到范围查询（>、
头歌MySQL数据库实训答案 有目录
个人博客地址https://sun98k.github.io/
12-04 25万+
计算机系统综合实训 头歌MySQL数据库实训平台作业，内容比较全比较多，内容仅供参考，如有错误部分希望联系我跟正，觉得有用记得点赞收藏。数据库1-MySQL数据定义与操作实战 数据库2-MySQL数据管理技术实战 数据库3-MySQL数据库系统设计实战 数据库4-层次、网状、关系模型实战
MySQL数据库面试题（2020最新版）
热门推荐
ThinkWon的博客
03-10 121万+
文章目录数据库基础知识为什么要使用数据库什么是SQL？什么是MySQL?数据库三大范式是什么mysql有关权限的表都有哪几个MySQL的binlog有有几种录入格式？分别有什么区别？数据类型mysql有哪些数据类型引擎MySQL存储引擎MyISAM与InnoDB区别MyISAM索引与InnoDB索引的区别？InnoDB引擎的4大特性存储引擎选择索引什么是索引？索引有哪些优缺点？索引使用场景（重点）............
MySQL数据库权限管理
不屈的铝合金的博客
07-23 1283
MySQL服务权限针对用户设置，用来控制及约束用户在数据库里对不同对象(库、表) 都能干啥，比如增删改查等。数据库服务常见的权限管理定义设置：（
Mysql数据库管理
fantuan_sss的博客
03-21 1575
Structured Query Language的缩写，即结构化查询语言，关系型数据库的标准语言，用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。
Mysql数据库的权限汇总
云计算运维工程师的成长之路
10-23 284
允许使用CREATE USER，DROP USER，RENAME USER 和 REVOKE ALL PRIVILEGESDELETEDROP。
MySQL数据库基础知识点复习总结
2301_78349092的博客
04-28 1176
数据库就是一个存放计算机数据的仓库，这个仓库是按照一定的数据结构（数据结构是指数据的组 织形式或数据之间的联系）来对数据进行组织和存储的，可以通过数据库提供的多种方法来管理其 中的数据。最常用的数据库模式主要有两种，即关系型数据库和非关系型数据库。生产环境主流的关系型数据库有 Oracle、Microsoft SQL Server、MySQL/MariaDB等。 生产环境主流的关系型数据库有 MongoDB Memcached Redis1、关系型数据库介绍 关系型数据库模型是把复杂的数据结构归结为简单的二
Mysql数据库深入理解
一坨小橙子的博客
03-23 1738
数据库基本框架图、数据库访问请求数据过程、一条查询SQL语句执行过程、一条记录如何存储、补充拓展（compact行格式）、总结Mysql数据库常用的命令行
MySQL数据库(Python)
weixin_46287157的博客
04-13 4323
表示取消自动提交事务，需要手动提交，不然操作不会提交到数据库，开启事务之后，对数据库的表格操作之后，如果不执行提交，查询得到的结果只是当前的结果，当前结果并没有保存至数据库，只是虚拟的表格，使用。在启动数据库，使用数据库之后，可以在该数据库里面创建表格，对表格进行各种操作，一个数据库可以拥有多个表格，数据保存在表格中。：对数据库执行增删改查的时候，默认会在事务环境中进行操作，操作完成后要进行手动提交， 如果不提交，程序默认操作为回滚，即更改的操作不被记录，事务提交操作由数据库连接对象来完成（
头歌MySQL数据库
qq_46162008的博客
10-21 9405
数据库部分一条一条的写，可鼠标手动粘贴，除特定命令外未分大小写。 第1关：创建数据库 第2关创建表 第3关：使用主键约束 第4关：外键约束 第5关：添加常用约束 MySQL数据库 - 数据库和表的基本操作（一） 第1关：查看表结构与修改表名 第2关：修改字段名与字段数据类型 第3关：添加与删除字段 第4关：修改字段的排列位置 第5关：删除表的外键约束 MySQL数据库 - 数据库和表的基本操作（二） 第1关：插入数据 第2关：更新数据 第3关：删除数据 MySQL数据库 - 单表查询（一） 第1关：基本查询
mysql数据库参考文献汇总.pdf
11-22
mysql数据库参考文献汇总.pdf
MySQL数据库考试试题及答案-34页
06-01
MySQL数据库考试试题及答案 在本文中，我们将对 MySQL 数据库考试试题及答案进行详细的解析，并总结出相关的知识点。 1．数据库聚合函数 在 MySQL 中，聚合函数用于对数据进行汇总和计算。 SUM 函数用于计算数据...
MySQL数据库精华汇总.pdf
06-06
这份“MySQL数据库精华汇总”文档提供了关于MySQL的一些核心概念和常用操作，涵盖了从数据库创建、数据操作到查询和优化等多个方面。 首先，数据库是数据的逻辑组织形式，类似于计算机文件系统中的树结构。在MySQL...
MySQL中几种常见的行格式
weixin_45923633的博客
09-13 484
动态格式会根据行中数据的实际大小动态地调整行的存储方式。如果行中的某个字段是变长的，那么这个字段的实际数据将被存储在行的末尾，而不会在行的开头占用固定的空间。这种格式适用于所有列都是固定长度的情况，可以提供最快的访问速度，但可能会浪费存储空间。：紧凑格式试图减少内部碎片，使得行的平均大小变小，从而在一个数据页中可以存储更多的行。：页面格式是一种较新的行存储格式，它旨在提高大表的访问性能和存储效率。是用来指定表中行的存储格式的属性。不同的行格式会影响数据的存储方式，进而影响存储空间的使用效率和访问性能。
MySQL练手题--体育馆的人流量(困难)
weixin_66919047的博客
09-15 588
此题稍微微有一点难度，如果看不明白可以对题目进行分解分析，分析出一步之后再接下一步来查询，题目已经要求人数大于等于100且id连续三行，就可以先筛选出大于等于100 的id 然后排序求差 差值相等的均为id连续的 ，最后筛选差值总数大于等于3 的，便出来了最后结果；
基于SpringBoot+Vue+MySQL的网上甜品蛋糕售卖店管理系统
程序员大金的博客
09-12 898
基于SpringBoot+Vue+MySQL的网上甜品蛋糕售卖店管理系统，附源码。
基于SpringBoot+Vue+MySQL的招聘管理系统
程序员大金的博客
09-13 1137
基于SpringBoot+Vue+MySQL的招聘管理系统，附源码。
梧桐数据库（WuTongDB）：MySQL 优化器简介
最新发布
lunan的博客
09-19 658
MySQL 优化器是一个复杂的系统，它通过生成多个查询计划并基于代价模型选择最优计划来提升查询效率。优化器依赖多种技术，包括索引优化、连接重排序、子查询优化等，来尽可能减少查询的代价。在 MySQL 的最新版本中，优化器功能得到了进一步增强，例如引入了哈希连接、并行查询等功能，极大提升了查询性能。梧桐数据库（WuTongDB）是基于 Apache HAWQ 打造的一款分布式 OLAP 数据库。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值