做毕业网站的周记,企业建设网站的功能是什么意思,seo优化培训学校,网站建设内容规划表文章目录数据库了解#xff1a;快速操作#xff1a;安装mysql启动,关闭,重启mysql服务连接mysql的root用户创建数据库删除数据库选择数据库mysql 数据类型MySQL 创建数据表MySQL 删除数据表MySQL 插入数据MySQL 查询数据MySQL WHERE 子句BINARY 关键字MySQL UPDATE 更新批量更…
文章目录数据库了解快速操作安装mysql启动,关闭,重启mysql服务连接mysql的root用户创建数据库删除数据库选择数据库mysql 数据类型MySQL 创建数据表MySQL 删除数据表MySQL 插入数据MySQL 查询数据MySQL WHERE 子句BINARY 关键字MySQL UPDATE 更新批量更新1使用case when批量更新2创建临时表先更新临时表然后从临时表中update1.创建临时表1.1 首先获取原表中的字段结构1.2 创建临时表2.在临时表中插入要修改的数据3.更新原表3.1 我们先更新了runoob_title字段3.2 接着更新runoob_author字段3.3接着更新submission_date字段MySQL DELETE 语句MySQL LIKE 子句MySQL UNION 操作符SQL UNION 实例SQL UNION ALL 实例带有 WHERE 的 SQL UNION ALLMySQL 排序MySQL GROUP BY 语句使用 WITH ROLLUPMysql 连接的使用INNER JOINLEFT JOINRIGHT JOINMySQL NULL 值处理MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。MySQL 事务使用保留点 SAVEPOINTMySQL ALTER命令1.删除添加或修改表字段1.1指定新增字段的位置2.修改字段类型及名称2.1修改字段类型3.修改字段默认值4.修改表名MySQL 索引普通索引创建索引修改表结构(添加索引)创建表的时候直接指定删除索引的语法唯一索引创建索引修改表结构创建表的时候直接指定使用ALTER 命令添加和删除索引显示索引信息MySQL 临时表删除MySQL 临时表临时表的应用场景1.批量更新数据MySQL 复制表步骤一获取数据表的完整结构。步骤二修改SQL语句的数据表名并执行SQL语句。步骤三拷贝全部数据MySQL 元数据MySQL 序列使用使用 AUTO_INCREMENT获取AUTO_INCREMENT值重置序列设置序列的开始值MySQL 处理重复数据防止表中出现重复数据方法1设置主键方法2 设置数据的唯一性方法是添加一个 UNIQUE 索引统计重复数据过滤重复数据删除重复数据MySQL 运算符级联删除和级联更新1.建立两张表stu和sc分别插入数据级联删除将stu表中id为2的学生删除该学生在sc表中的成绩也会级联删除级联更新stu表中id为3的学生更改为id为6该学生在sc表中的对应id也会级联更新注意参考数据库了解
SQL语言包括四种主要程序设计语言类别的语句数据定义语言(DDL)数据操作语言(DML)数据控制语言(DCL)和事务控制语言TCL
快速操作
展示某个表中的所有字段信息 show columns from testalter_tb1;
安装mysql
sudo apt-get updatesudo apt-get -y mysql-server mysql-client 若上述步骤出现失败:参考:https://blog.csdn.net/weixx3/article/details/80782479或者参考:崔庆才网络爬虫第29页. -重置mysql root用户的密码,参考:https://www.cnblogs.com/woshimrf/p/ubuntu-install-mysql.html
启动,关闭,重启mysql服务
sudo service mysql start/stop/restart
连接mysql的root用户
mysql -u root -p
接着输入密码:123456或者连接远程数据库
mysql -h0.0.0.0 -u root -p创建数据库
create database runoob;删除数据库
drop database runoob;选择数据库
use runoob;mysql 数据类型
MySQL支持多种类型大致可以分为三类数值、日期/时间和字符串(字符)类型。
参考https://www.runoob.com/mysql/mysql-data-types.html
MySQL 创建数据表 CREATE TABLE table_name (column_name column_type); 例子 CREATE TABLE IF NOT EXISTS runoob_tb1(runoob_id INT UNSIGNED AUTO_INCREMENT,runoob_title VARCHAR(100) NOT NULL,runoob_author VARCHAR(40) NOT NULL,submission_date DATE,PRIMARY KEY ( runoob_id ))ENGINEInnoDB DEFAULT CHARSETutf8;实例解析 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL 在操作数据库时如果输入该字段的数据为NULL 就会报错。 AUTO_INCREMENT定义列为自增的属性一般用于主键数值会自动加1。 PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键列间以逗号分隔。 ENGINE 设置存储引擎CHARSET 设置编码。
MySQL 删除数据表
DROP TABLE table_name ;例子
DROP TABLE runoob_tb1MySQL 插入数据
INSERT INTO table_name ( field1, field2,...fieldN )VALUES( value1, value2,...valueN );例子
insert into runoob_tbl (runoob_title,runoob_author, submission_date)
values (学习 mysql,菜鸟,NOW());检查一下 select * from runoob_tbl;
MySQL 查询数据
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]例子
select * from runoob_tbl;MySQL WHERE 子句
我们知道从 MySQL 表中使用 SQL SELECT 语句来读取数据。 如需有条件地从表中选取数据可将 WHERE 子句添加到 SELECT 语句中。
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....例子
SELECT * from runoob_tbl WHERE runoob_author菜鸟教程;BINARY 关键字
MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。 如下实例: BINARY 关键字:
mysql SELECT * from runoob_tbl WHERE BINARY runoob_authorrunoob.com;MySQL UPDATE 更新
如果我们需要修改或更新 MySQL 中的数据我们可以使用 SQL UPDATE 命令来操作。
语法 以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法
UPDATE table_name SET field1new-value1, field2new-value2
[WHERE Clause]你可以同时更新一个或多个字段。你可以在 WHERE 子句中指定任何条件。你可以在一个单独表中同时更新数据。
例子
UPDATE runoob_tbl SET runoob_title学习 C WHERE runoob_id3;批量更新1使用case when
例子 修改前
runoob_idrunoob_titlerunoob_authorsubmission_date1学习 PHP菜鸟教程2017-04-122学习 MySQL菜鸟教程2017-04-123学习 JavaRUNOOB.COM2015-05-014学习 PythonRUNOOB.COM2016-03-065学习 CFK2017-04-05
修改了两个字段runoob_title和runoob_author中的信息
修改后
runoob_idrunoob_titlerunoob_authorsubmission_date1php菜鸟2017-04-122mysql菜鸟22017-04-123javarunoob.com2015-05-014学习 PythonRUNOOB.COM2016-03-065学习 CFK2017-04-05
update runoob_tbl
set runoob_author case runoob_id when 1 then 菜鸟 when 2 then 菜鸟2 when 3 then runoob.com end, runoob_title case runoob_id when 1 then php when 2 then mysql when 3 then java end
where runoob_id in (1,2,3);批量更新2创建临时表先更新临时表然后从临时表中update
修改前
runoob_idrunoob_titlerunoob_authorsubmission_date1php菜鸟2017-04-122mysql菜鸟22017-04-123javarunoob.com2015-05-014学习 PythonRUNOOB.COM2016-03-065学习 CFK2017-04-05
修改后
步骤
1.创建临时表
1.1 首先获取原表中的字段结构
show columns from runoob_tbl;1.2 创建临时表
具体代码
mysql create temporary table tmp(- runoob_id int unsigned auto_increment,- runoob_title varchar(100) not null,- runoob_author varchar(40) not null,- submission_date date,- PRIMARY KEY ( runoob_id )- )engineInnoDB default charsetutf8;
Query OK, 0 rows affected (0.00 sec)2.在临时表中插入要修改的数据
mysql insert into tmp values(1,myphp,菜鸟大佬,20190203);
Query OK, 1 row affected (0.00 sec)
mysql insert into tmp values(2,myjava,菜鸟大佬2,20140409);
Query OK, 1 row affected (0.00 sec)根据runoob_id可知我们要修改前两条而且我们修改除runoob_id之外的三个字段
3.更新原表
3.1 我们先更新了runoob_title字段
mysql update runoob_tbl,tmp set runoob_tbl.runoob_title tmp.runoob_title where runoob_tbl.runoob_id tmp.runoob_id;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 2 Changed: 0 Warnings: 0修改之后的表内容如下
runoob_idrunoob_titlerunoob_authorsubmission_date1myphp菜鸟2017-04-122myjava菜鸟22017-04-123javarunoob.com2015-05-014学习 PythonRUNOOB.COM2016-03-065学习 CFK2017-04-05
3.2 接着更新runoob_author字段
mysql update runoob_tbl,tmp set runoob_tbl.runoob_author tmp.runoob_author where runoob_tbl.runoob_id tmp.runoob_id;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0修改之后的表内容如下
runoob_idrunoob_titlerunoob_authorsubmission_date1myphp菜鸟大佬2017-04-122myjava菜鸟大佬22017-04-123javarunoob.com2015-05-014学习 PythonRUNOOB.COM2016-03-065学习 CFK2017-04-05
3.3接着更新submission_date字段
mysql update runoob_tbl,tmp set runoob_tbl.submission_date tmp.submission_date where runoob_tbl.runoob_id tmp.runoob
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0修改了三个字段之后的最终的表内容为
runoob_idrunoob_titlerunoob_authorsubmission_date1myphp菜鸟大佬2019-02-032myjava菜鸟大佬22014-04-093javarunoob.com2015-05-014学习 PythonRUNOOB.COM2016-03-065学习 CFK2017-04-05
MySQL DELETE 语句
你可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。 你可以在 mysql 命令提示符或 PHP 脚本中执行该命令。 语法 以下是 SQL DELETE 语句从 MySQL 数据表中删除数据的通用语法
DELETE FROM table_name [WHERE Clause]如果没有指定 WHERE 子句MySQL 表中的所有记录将被删除。你可以在 WHERE 子句中指定任何条件您可以在单个表中一次性删除记录。当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。
例子
DELETE FROM runoob_tbl WHERE runoob_id3;MySQL LIKE 子句
我们知道在 MySQL 中使用 SQL SELECT 命令来读取数据 同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。 WHERE 子句中可以使用等号 来设定获取数据的条件如 “runoob_author ‘RUNOOB.COM’”。
但是有时候我们需要获取 runoob_author 字段含有 “COM” 字符的所有记录这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。
SQL LIKE 子句中使用百分号 %字符来表示任意字符类似于UNIX或正则表达式中的星号 *。
如果没有使用百分号 %, LIKE 子句与等号 的效果是一样的。
语法 以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法
SELECT field1, field2,...fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 somevalue你可以在 WHERE 子句中指定任何条件。你可以在 WHERE 子句中使用LIKE子句。你可以使用LIKE子句代替等号 。LIKE 通常与 % 一同使用类似于一个元字符的搜索。你可以使用 AND 或者 OR 指定一个或多个条件。你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。
例子
select * from runoob_tbl where runoob_author like %com;MySQL UNION 操作符
本教程为大家介绍 MySQL UNION 操作符的语法和实例。
描述 MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
语法 MySQL UNION 操作符语法格式
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];参数
expression1, expression2, … expression_n: 要检索的列。tables: 要检索的数据表。WHERE conditions: 可选 检索条件。DISTINCT: 可选删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据所以 DISTINCT 修饰符对结果没啥影响。ALL: 可选返回所有结果集包含重复数据。
例子
SQL UNION 实例
下面的 SQL 语句从 “Websites” 和 “apps” 表中选取所有不同的country只有不同的值 实例 SELECT country FROM Websites UNION SELECT country FROM apps ORDER BY country;
SQL UNION ALL 实例
下面的 SQL 语句使用 UNION ALL 从 “Websites” 和 “apps” 表中选取所有的country也有重复的值
实例:
SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;带有 WHERE 的 SQL UNION ALL
下面的 SQL 语句使用 UNION ALL 从 “Websites” 和 “apps” 表中选取所有的中国(CN)的数据也有重复的值
实例:
SELECT country, name FROM Websites
WHERE countryCN
UNION ALL
SELECT country, app_name FROM apps
WHERE countryCN
ORDER BY country;MySQL 排序
我们知道从 MySQL 表中使用 SQL SELECT 语句来读取数据。 如果我们需要对读取的数据进行排序我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序再返回搜索结果。
语法 以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]你可以使用任何字段来作为排序的条件从而返回排序后的查询结果。 你可以设定多个字段来排序。 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下它是按升序排列。 你可以添加 WHERE…LIKE 子句来设置条件。 例子 SELECT * from runoob_tbl ORDER BY submission_date ASC;
MySQL GROUP BY 语句
GROUP BY 语句根据一个或多个列对结果集进行分组。 在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
GROUP BY 语法
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;实例
CREATE TABLE employee_tbl (id int(11) NOT NULL,name char(10) NOT NULL DEFAULT ,date datetime NOT NULL,singin tinyint(4) NOT NULL DEFAULT 0 COMMENT 登录次数,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8;INSERT INTO employee_tbl VALUES (1, 小明, 2016-04-22 15:25:33, 1), (2, 小王, 2016-04-20 15:25:47, 3), (3, 小丽, 2016-04-19 15:26:02, 2), (4, 小王, 2016-04-07 15:26:14, 4), (5, 小明, 2016-04-11 15:26:40, 4), (6, 小明, 2016-04-04 15:26:54, 2);使用 GROUP BY 语句 将数据表按名字进行分组并统计每个人有多少条记录
SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
使用 WITH ROLLUP
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计SUM,AVG,COUNT…。
例如我们将以上的数据表按名字进行分组再统计每个人登录的次数
SELECT name, SUM(singin) as singin_count
FROM employee_tbl GROUP BY name WITH ROLLUP;其中记录 NULL 表示所有人的登录次数。
我们可以使用 coalesce 来设置一个可以取代 NUll 的名称coalesce 语法
select coalesce(a,b,c); 参数说明如果anull,则选择b如果bnull,则选择c如果a!null,则选择a如果a b c 都为null 则返回为null没意义。
以下实例中如果名字为空我们使用总数代替
SELECT coalesce(name, 总数), SUM(singin) as singin_count
FROM employee_tbl GROUP BY name WITH ROLLUP;Mysql 连接的使用
在前几章节中我们已经学会了如何在一张表中读取数据这是相对简单的但是在真正的应用中经常需要从多个数据表中读取数据。
本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。
你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。
JOIN 按照功能大致分为如下三类 INNER JOIN内连接,或等值连接获取两个表中字段匹配关系的记录。 LEFT JOIN左连接获取左表所有记录即使右表没有对应匹配的记录。 RIGHT JOIN右连接 与 LEFT JOIN 相反用于获取右表所有记录即使左表没有对应匹配的记录。 新建表tcount_tbl CREATE TABLE tcount_tbl(
runoob_author varchar(255) NOT NULL DEFAULT ,
runoob_count int(11) NOT NULL DEFAULT 0)ENGINEInnoDB DEFAULT CHARSETutf8;INNER JOIN
SELECT a.runoob_id, a.runoob_author, b.runoob_count
FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author b.runoob_author;等价与
SELECT a.runoob_id, a.runoob_author, b.runoob_count
FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author b.runoob_author; MySQL LEFT JOIN MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据即便右边表无对应数据。
实例 尝试以下实例以 runoob_tbl 为左表tcount_tbl 为右表理解 MySQL LEFT JOIN 的应用
LEFT JOIN
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author b.runoob_author;
MySQL RIGHT JOIN MySQL RIGHT JOIN 会读取右边数据表的全部数据即便左边边表无对应数据。
实例: 尝试以下实例以 runoob_tbl 为左表tcount_tbl 为右表理解MySQL RIGHT JOIN的应用
RIGHT JOIN
SELECT a.runoob_id, a.runoob_author, b.runoob_count
FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author b.runoob_author;MySQL NULL 值处理
我们已经知道 MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时该命令可能就无法正常工作。
为了处理这种情况MySQL提供了三大运算符:
IS NULL: 当列的值是 NULL,此运算符返回 true。IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。: 比较操作符不同于运算符当比较的的两个值为 NULL 时返回 true。 关于 NULL 的条件比较运算是比较特殊的。你不能使用 NULL 或 ! NULL 在列中查找 NULL 值 。 在 MySQL 中NULL 值与任何其它值的比较即使是 NULL永远返回 false即 NULL NULL 返回false 。
MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。
注意
select * , columnName1ifnull(columnName2,0) from tableName; columnName1columnName2 为 int 型当 columnName2 中有值为 null 时columnName1columnName2null ifnull(columnName2,0) 把 columnName2 中 null 值转为 0。
MySQL 事务
MySQL 事务主要用于处理操作量大复杂度高的数据。比如说在人员管理系统中你删除一个人员你即需要删除人员的基本资料也要删除和该人员相关的信息如信箱文章等等这样这些数据库操作语句就构成一个事务 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。 事务处理可以用来维护数据库的完整性保证成批的 SQL 语句要么全部执行要么全部不执行。 事务用来管理 insert,update,delete 语句 一般来说事务是必须满足4个条件ACID原子性Atomicity或称不可分割性、一致性Consistency、隔离性Isolation又称独立性、持久性Durability。 原子性一个事务transaction中的所有操作要么全部完成要么全部不完成不会结束在中间某个环节。事务在执行过程中发生错误会被回滚Rollback到事务开始前的状态就像这个事务从来没有执行过一样。 一致性在事务开始之前和事务结束以后数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。 隔离性数据库允许多个并发事务同时对其数据进行读写和修改的能力隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别包括读未提交Read uncommitted、读提交read committed、可重复读repeatable read和串行化Serializable。 持久性事务处理结束后对数据的修改就是永久的即便系统故障也不会丢失。
在 MySQL 命令行的默认设置下事务都是自动提交的即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION或者执行命令 SET AUTOCOMMIT0用来禁止使用当前会话的自动提交。
事务控制语句 BEGIN 或 START TRANSACTION 显式地开启一个事务 COMMIT 也可以使用 COMMIT WORK不过二者是等价的。COMMIT 会提交事务并使已对数据库进行的所有修改成为永久性的 ROLLBACK 也可以使用 ROLLBACK WORK不过二者是等价的。回滚会结束用户的事务并撤销正在进行的所有未提交的修改 SAVEPOINT identifierSAVEPOINT 允许在事务中创建一个保存点一个事务中可以有多个 SAVEPOINT RELEASE SAVEPOINT identifier 删除一个事务的保存点当没有指定的保存点时执行该语句会抛出一个异常 ROLLBACK TO identifier 把事务回滚到标记点 SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
MYSQL 事务处理主要有两种方法 1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT0 禁止自动提交
SET AUTOCOMMIT1 开启自动提交实例 mysql CREATE TABLE runoob_transaction_test( id int(5)) engineinnodb; # 创建数据表 Query OK, 0 rows affected (0.04 sec)
mysql select * from runoob_transaction_test;
Empty set (0.01 sec)mysql begin; # 开始事务
Query OK, 0 rows affected (0.00 sec)mysql insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)mysql insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)mysql commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)mysql select * from runoob_transaction_test;
------
| id |
------
| 5 |
| 6 |
------
2 rows in set (0.01 sec)mysql begin; # 开始事务
Query OK, 0 rows affected (0.00 sec)mysql insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)mysql rollback; # 回滚
Query OK, 0 rows affected (0.00 sec)mysql select * from runoob_transaction_test; # 因为回滚所以数据没有插入
------
| id |
------
| 5 |
| 6 |
------
2 rows in set (0.01 sec)mysql使用保留点 SAVEPOINT
savepoint 是在数据库事务处理中实现“子事务”subtransaction也称为嵌套事务的方法。事务可以回滚到 savepoint 而不影响 savepoint 创建前的变化, 不需要放弃整个事务。
ROLLBACK 回滚的用法可以设置保留点 SAVEPOINT执行多条操作时回滚到想要的那条语句之前。
使用 SAVEPOINT
SAVEPOINT savepoint_name; // 声明一个 savepoint
ROLLBACK TO savepoint_name; // 回滚到savepoint 删除 SAVEPOINT
保留点再事务处理完成执行一条 ROLLBACK 或 COMMIT后自动释放。
MySQL5 以来可以用:
RELEASE SAVEPOINT savepoint_name; // 删除指定保留点
MySQL ALTER命令
当我们需要修改数据表名或者修改数据表字段时就需要使用到MySQL ALTER命令。 开始本章教程前让我们先创建一张表表名为testalter_tbl。
roothost# mysql -u root -p password;
Enter password:*******
mysql use RUNOOB;
Database changed
mysql create table testalter_tbl- (- i INT,- c CHAR(1)- );
Query OK, 0 rows affected (0.05 sec)
mysql SHOW COLUMNS FROM testalter_tbl;
-------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------
| i | int(11) | YES | | NULL | |
| c | char(1) | YES | | NULL | |
-------------------------------------------
2 rows in set (0.00 sec)1.删除添加或修改表字段
如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段
mysql ALTER TABLE testalter_tbl DROP i;如果数据表中只剩余一个字段则无法使用DROP来删除字段。
MySQL 中使用 ADD 子句来向数据表中添加列如下实例在表 testalter_tbl 中添加 i 字段并定义数据类型:
mysql ALTER TABLE testalter_tbl ADD i INT;执行以上命令后i 字段会自动添加到数据表字段的末尾。
mysql SHOW COLUMNS FROM testalter_tbl;
-------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------
| c | char(1) | YES | | NULL | |
| i | int(11) | YES | | NULL | |
-------------------------------------------
2 rows in set (0.00 sec)1.1指定新增字段的位置
如果你需要指定新增字段的位置可以使用MySQL提供的关键字 FIRST (设定位第一列) AFTER 字段名设定位于某个字段之后。
尝试以下 ALTER TABLE 语句, 在执行成功后使用 SHOW COLUMNS 查看表结构的变化
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。
2.修改字段类型及名称
如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。
2.1修改字段类型
例如把字段 c 的类型从 CHAR(1) 改为 CHAR(10)可以执行以下命令:
mysql ALTER TABLE testalter_tbl MODIFY c CHAR(10);使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后紧跟着的是你要修改的字段名然后指定新字段名及类型。尝试如下实例
mysql ALTER TABLE testalter_tbl CHANGE i j BIGINT;
mysql ALTER TABLE testalter_tbl CHANGE j j INT;ALTER TABLE 对 Null 值和默认值的影响 当你修改字段时你可以指定是否包含值或者是否设置默认值。
以下实例指定字段 j 为 NOT NULL 且默认值为100 。
mysql ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;如果你不设置默认值MySQL会自动设置该字段默认为 NULL。
3.修改字段默认值
你可以使用 ALTER 来修改字段的默认值尝试以下实例
mysql ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql SHOW COLUMNS FROM testalter_tbl;
-------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------
| c | char(1) | YES | | NULL | |
| i | int(11) | YES | | 1000 | |
-------------------------------------------
2 rows in set (0.00 sec)你也可以使用 ALTER 命令及 DROP子句来删除字段的默认值如下实例
mysql ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql SHOW COLUMNS FROM testalter_tbl;
-------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------
| c | char(1) | YES | | NULL | |
| i | int(11) | YES | | NULL | |
-------------------------------------------
2 rows in set (0.00 sec)
Changing a Table Type:修改数据表类型可以使用 ALTER 命令及 TYPE 子句来完成。尝试以下实例我们将表 testalter_tbl 的类型修改为 MYISAM
注意查看数据表类型可以使用 SHOW TABLE STATUS 语句。
mysql ALTER TABLE testalter_tbl ENGINE MYISAM;
mysql SHOW TABLE STATUS LIKE testalter_tbl\G
*************************** 1. row ****************Name: testalter_tblType: MyISAMRow_format: FixedRows: 0Avg_row_length: 0Data_length: 0
Max_data_length: 25769803775Index_length: 1024Data_free: 0Auto_increment: NULLCreate_time: 2007-06-03 08:04:36Update_time: 2007-06-03 08:04:36Check_time: NULLCreate_options:Comment:
1 row in set (0.00 sec)4.修改表名
如果需要修改数据表的名称可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。
尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl
mysql ALTER TABLE testalter_tbl RENAME TO alter_tbl;此外ALTER 命令还可以用来创建及删除MySQL数据表的索引。
MySQL 索引
MySQL索引的建立对于MySQL的高效运行是很重要的索引可以大大提高MySQL的检索速度。
打个比方如果合理的设计且使用索引的MySQL是一辆兰博基尼的话那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引。 单列索引即一个索引只包含单个列一个表可以有多个单列索引但这不是组合索引。 组合索引即一个索引包含多个列。
创建索引时你需要确保该索引是应用在SQL 查询语句的条件(一般作为 WHERE 子句的条件)。 实际上索引也是一张表该表保存了主键与索引字段并指向实体表的记录。
缺点
上面都在说使用索引的好处但过多的使用索引将会造成滥用。因此索引也会有它的缺点虽然索引大大提高了查询速度同时却会降低更新表的速度如对表进行INSERT、UPDATE和DELETE。因为更新表时MySQL不仅要保存数据还要保存一下索引文件。 建立索引会占用磁盘空间的索引文件。
普通索引
创建索引
这是最基本的索引它没有任何限制。它有以下几种创建方式
CREATE INDEX indexName ON mytable(username(length)); 如果是CHARVARCHAR类型length可以小于字段实际长度如果是BLOB和TEXT类型必须指定 length。
修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); 删除索引的语法
DROP INDEX [indexName] ON mytable; 唯一索引
它与前面的普通索引类似不同的就是索引列的值必须唯一但允许有空值。如果是组合索引则列值的组合必须唯一。它有以下几种创建方式
创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length)) 修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
); 使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键这意味着索引值必须是唯一的且不能为NULL。ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的除了NULL外NULL可能会出现多次。ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引索引值可出现多次。ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT 用于全文索引。
以下实例为在表中添加索引。
mysql ALTER TABLE testalter_tbl ADD INDEX (c);你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql ALTER TABLE testalter_tbl DROP INDEX c;使用 ALTER 命令添加和删除主键 主键只能作用于一个列上添加主键索引时你需要确保该主键默认不为空NOT NULL。实例如下
mysql ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);你也可以使用 ALTER 命令删除主键
mysql ALTER TABLE testalter_tbl DROP PRIMARY KEY;删除主键时只需指定PRIMARY KEY但在删除索引时你必须知道索引名。
显示索引信息
你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。
尝试以下实例:
mysql SHOW INDEX FROM table_name; \G
........MySQL 临时表
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见当关闭连接时Mysql会自动删除表并释放所有空间。
临时表在MySQL 3.23版本中添加如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。不过现在一般很少有再使用这么低版本的MySQL数据库服务了。
MySQL临时表只在当前连接可见如果你使用PHP脚本来创建MySQL临时表那每当PHP脚本执行完成后该临时表也会自动销毁。
如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表那么只有在关闭客户端程序时才会销毁临时表当然你也可以手动销毁。
实例 以下展示了使用MySQL 临时表的简单实例以下的SQL代码可以适用于PHP脚本的mysql_query()函数。
mysql CREATE TEMPORARY TABLE SalesSummary (- product_name VARCHAR(50) NOT NULL- , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00- , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00- , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)mysql INSERT INTO SalesSummary- (product_name, total_sales, avg_unit_price, total_units_sold)- VALUES- (cucumber, 100.25, 90, 2);mysql SELECT * FROM SalesSummary;
-------------------------------------------------------------
| product_name | total_sales | avg_unit_price | total_units_sold |
-------------------------------------------------------------
| cucumber | 100.25 | 90.00 | 2 |
-------------------------------------------------------------
1 row in set (0.00 sec)当你使用 SHOW TABLES命令显示数据表列表时你将无法看到 SalesSummary表。
如果你退出当前MySQL会话再使用 SELECT命令来读取原先创建的临时表数据那你会发现数据库中没有该表的存在因为在你退出时该临时表已经被销毁了。
删除MySQL 临时表
默认情况下当你断开与数据库的连接后临时表就会自动被销毁。当然你也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。
以下是手动删除临时表的实例
mysql CREATE TEMPORARY TABLE SalesSummary (- product_name VARCHAR(50) NOT NULL- , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00- , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00- , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)mysql INSERT INTO SalesSummary- (product_name, total_sales, avg_unit_price, total_units_sold)- VALUES- (cucumber, 100.25, 90, 2);mysql SELECT * FROM SalesSummary;
-------------------------------------------------------------
| product_name | total_sales | avg_unit_price | total_units_sold |
-------------------------------------------------------------
| cucumber | 100.25 | 90.00 | 2 |
-------------------------------------------------------------
1 row in set (0.00 sec)
mysql DROP TABLE SalesSummary;
mysql SELECT * FROM SalesSummary;
ERROR 1146: Table RUNOOB.SalesSummary doesnt exist 临时表的应用场景
1.批量更新数据
MySQL 复制表
如果我们需要完全的复制MySQL的数据表包括表的结构索引默认值等。 如果仅仅使用CREATE TABLE … SELECT 命令是无法实现的。
本章节将为大家介绍如何完整的复制MySQL数据表步骤如下
使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句该语句包含了原数据表的结构索引等。 复制以下命令显示的SQL语句修改数据表名并执行SQL语句通过以上命令 将完全的复制数据表结构。 如果你想复制表的内容你就可以使用 INSERT INTO … SELECT 语句来实现。 实例 尝试以下实例来复制表 runoob_tbl 。
步骤一获取数据表的完整结构。
mysql SHOW CREATE TABLE runoob_tbl \G;
*************************** 1. row ***************************Table: runoob_tbl
Create Table: CREATE TABLE runoob_tbl (runoob_id int(11) NOT NULL auto_increment,runoob_title varchar(100) NOT NULL default ,runoob_author varchar(40) NOT NULL default ,submission_date date default NULL,PRIMARY KEY (runoob_id),UNIQUE KEY AUTHOR_INDEX (runoob_author)
) ENGINEInnoDB
1 row in set (0.00 sec)ERROR:
No query specified步骤二修改SQL语句的数据表名并执行SQL语句。
mysql CREATE TABLE clone_tbl (- runoob_id int(11) NOT NULL auto_increment,- runoob_title varchar(100) NOT NULL default ,- runoob_author varchar(40) NOT NULL default ,- submission_date date default NULL,- PRIMARY KEY (runoob_id),- UNIQUE KEY AUTHOR_INDEX (runoob_author)
- ) ENGINEInnoDB;
Query OK, 0 rows affected (1.80 sec)步骤三拷贝全部数据
执行完第二步骤后你将在数据库中创建新的克隆表 clone_tbl。 如果你想拷贝数据表的数据你可以使用 INSERT INTO… SELECT 语句来实现。
mysql INSERT INTO clone_tbl (runoob_id,- runoob_title,- runoob_author,- submission_date)- SELECT runoob_id,runoob_title,- runoob_author,submission_date- FROM runoob_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0执行以上步骤后你将完整的复制表包括表结构及表数据。
MySQL 元数据
你可能想知道MySQL以下三种信息
查询结果信息 SELECT, UPDATE 或 DELETE语句影响的记录数。数据库和数据表的信息 包含了数据库及数据表的结构信息。MySQL服务器信息 包含了数据库服务器的当前状态版本号等。 在MySQL的命令提示符中我们可以很容易的获取以上服务器信息。
MySQL 序列使用
MySQL 序列是一组整数1, 2, 3, …由于一张数据表只能有一个字段自增主键 如果你想实现其他字段也实现自动增加就可以使用MySQL序列来实现。
本章我们将介绍如何使用MySQL的序列。
使用 AUTO_INCREMENT
MySQL 中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义列。
实例 以下实例中创建了数据表 insect insect 表中 id 无需指定值可实现自动增长。
mysql CREATE TABLE insect- (- id INT UNSIGNED NOT NULL AUTO_INCREMENT,- PRIMARY KEY (id),- name VARCHAR(30) NOT NULL, # type of insect- date DATE NOT NULL, # date collected- origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql INSERT INTO insect (id,name,date,origin) VALUES- (NULL,housefly,2001-09-10,kitchen),- (NULL,millipede,2001-09-10,driveway),- (NULL,grasshopper,2001-09-10,front yard);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql SELECT * FROM insect ORDER BY id;
-----------------------------------------
| id | name | date | origin |
-----------------------------------------
| 1 | housefly | 2001-09-10 | kitchen |
| 2 | millipede | 2001-09-10 | driveway |
| 3 | grasshopper | 2001-09-10 | front yard |
-----------------------------------------
3 rows in set (0.00 sec)获取AUTO_INCREMENT值
在MySQL的客户端中你可以使用 SQL中的LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。
重置序列
如果你删除了数据表中的多条记录并希望对剩下数据的AUTO_INCREMENT列进行重新排列那么你可以通过删除自增的列然后重新添加来实现。 不过该操作要非常小心如果在删除的同时又有新记录添加有可能会出现数据混乱。操作如下所示
mysql ALTER TABLE insect DROP id;
mysql ALTER TABLE insect- ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,- ADD PRIMARY KEY (id);设置序列的开始值
一般情况下序列的开始值为1但如果你需要指定一个开始值100那我们可以通过以下语句来实现
mysql CREATE TABLE insect- (- id INT UNSIGNED NOT NULL AUTO_INCREMENT,- PRIMARY KEY (id),- name VARCHAR(30) NOT NULL, - date DATE NOT NULL,- origin VARCHAR(30) NOT NULL
)engineinnodb auto_increment100 charsetutf8;或者你也可以在表创建成功后通过以下语句来实现
mysql ALTER TABLE t AUTO_INCREMENT 100;MySQL 处理重复数据
有些 MySQL 数据表中可能存在重复的记录有些情况我们允许重复数据的存在但有时候我们也需要删除这些重复的数据。
本章节我们将为大家介绍如何防止数据表出现重复数据及如何删除数据表中的重复数据。
防止表中出现重复数据
你可以在 MySQL 数据表中设置指定的字段为 PRIMARY KEY主键 或者 UNIQUE唯一 索引来保证数据的唯一性。 让我们尝试一个实例下表中无索引及主键所以该表允许出现多条重复记录。
CREATE TABLE person_tbl
(first_name CHAR(20),last_name CHAR(20),sex CHAR(10)
);方法1设置主键
如果你想设置表中字段 first_namelast_name 数据不能重复你可以设置双主键模式来设置数据的唯一性 如果你设置了双主键那么那个键的默认值不能为 NULL可设置为 NOT NULL。如下所示
CREATE TABLE person_tbl
(first_name CHAR(20) NOT NULL,last_name CHAR(20) NOT NULL,sex CHAR(10),PRIMARY KEY (last_name, first_name)
);如果我们设置了唯一索引那么在插入重复数据时SQL 语句将无法执行成功,并抛出错。
INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的数据如果数据库没有数据就插入新的数据如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据达到在间隙中插入数据的目的。
以下实例使用了 INSERT IGNORE INTO执行后不会出错也不会向数据表中插入重复数据
mysql INSERT IGNORE INTO person_tbl (last_name, first_name)- VALUES( Jay, Thomas);
Query OK, 1 row affected (0.00 sec)
mysql INSERT IGNORE INTO person_tbl (last_name, first_name)- VALUES( Jay, Thomas);
Query OK, 0 rows affected (0.00 sec)INSERT IGNORE INTO 当插入数据时在设置了记录的唯一性后如果插入重复数据将不返回错误只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录则先删除掉。再插入新记录。
方法2 设置数据的唯一性方法是添加一个 UNIQUE 索引
另一种设置数据的唯一性方法是添加一个 UNIQUE 索引如下所示
CREATE TABLE person_tbl
(first_name CHAR(20) NOT NULL,last_name CHAR(20) NOT NULL,sex CHAR(10),UNIQUE (last_name, first_name)
);统计重复数据
以下我们将统计表中 first_name 和 last_name的重复记录数
mysql SELECT COUNT(*) as repetitions, last_name, first_name- FROM person_tbl- GROUP BY last_name, first_name- HAVING repetitions 1;以上查询语句将返回 person_tbl 表中重复的记录数。 一般情况下查询重复的值请执行以下操作
确定哪一列包含的值可能会重复。在列选择列表使用COUNT(*)列出的那些列。在GROUP BY子句中列出的列。HAVING子句设置重复数大于1。
过滤重复数据
如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。
mysql SELECT DISTINCT last_name, first_name- FROM person_tbl;你也可以使用 GROUP BY 来读取数据表中不重复的数据
mysql SELECT last_name, first_name- FROM person_tbl- GROUP BY (last_name, first_name);删除重复数据
如果你想删除数据表中的重复数据你可以使用以下的SQL语句
mysql CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex);
mysql DROP TABLE person_tbl;
mysql ALTER TABLE tmp RENAME TO person_tbl;当然你也可以在数据表中添加 INDEX索引 和 PRIMAY KEY主键这种简单的方法来删除表中的重复记录。方法如下
mysql ALTER IGNORE TABLE person_tbl- ADD PRIMARY KEY (last_name, first_name);MySQL 运算符
本章节我们主要介绍 MySQL 的运算符及运算符的优先级。 MySQL 主要有以下几种运算符
算术运算符比较运算符逻辑运算符位运算符
级联删除和级联更新
1.建立两张表stu和sc
create table stu(
sid int UNSIGNED primary key auto_increment,
name varchar(20) not null)create table sc(
scid int UNSIGNED primary key auto_increment,
sid int UNSIGNED not null,
score varchar(20) default 0,
FOREIGN KEY (sid) REFERENCES stu(sid) ON DELETE CASCADE ON UPDATE CASCADE)分别插入数据
insert into stu (name) value (zxf);
insert into stu (name) value (ls);
insert into stu (name) value (zs);
insert into stu (name) value (ww);insert into sc(sid,score) values (1,98);
insert into sc(sid,score) values (1,98);
insert into sc(sid,score) values (2,34);
insert into sc(sid,score) values (2,98);
insert into sc(sid,score) values (2,98);
insert into sc(sid,score) values (3,56);
insert into sc(sid,score) values (4,78);
insert into sc(sid,score) values (4,98);注意在sc表中插入数据时若插入的sid为22则会插入失败违反外键约束因为外键sid 来自stu表中的id的主键即stu中的id没有等于22的数据。
级联删除将stu表中id为2的学生删除该学生在sc表中的成绩也会级联删除
delete from stu where sid 2;级联更新stu表中id为3的学生更改为id为6该学生在sc表中的对应id也会级联更新
update stu set sid6 where sid3;注意
删除表的时候必须先删除外键表(sc)再删除主键表stu
mysql drop table stu;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails上述为违反外键约束不能删除
mysql drop table sc;
Query OK, 0 rows affected (0.02 sec)mysql drop table stu;
Query OK, 0 rows affected (0.01 sec)上述为正常删除先删除sc表再删除stu表
参考
1https://www.runoob.com/mysql/mysql-tutorial.html 菜鸟联盟
