好久没有敲sql语句了,手生的不行……特意拿出来一整天的时间,打开mysql敲了一遍数据库中的常用语句,通过练习来加深一下印象。
以下是mysql数据库的操作语句,在终端启动mysql后运行。为了便于理解,在语句中需要自己填写的部分我都用汉字来描述,以免出现歧义。
MySQL数据库中的语句操作
打开/关闭MySQL
首先打开终端,windows系统按Windows+R,输入cmd+回车;mac系统右上角🔍 – 输入Terminal – 点击打开终端。
之后,在终端中输入指令打开mysql。
mysql -u root -p
执行这条指令后,输入mysql的登录密码,进入mysql。
如果mysql还没安装,可以看一下我整理的mac端mysql安装步骤。
在打开mysql后,我们就可以在其中输入sql语句来操作mysql数据库系统了。可以输入“help;”来显示帮助菜单,查看输入语句提示。
MySQL的关闭很简单,只要输入“exit”就可以退出mysql。系统会显示“Bye”的字样,了然。
exit
数据库
在数据库的操作中,我们比较常用的是创建、删除和选择数据库这三个基础操作。
创建数据库
CREATE DATABASE 数据库名;
数据库的创建是操作数据库的第一步。
删除数据库
DROP DATABASE 数据库名;
用DROP来删除指定的数据库,而不是DELETE。删库跑路熟练度+1
选择数据库
USE 数据库名;
要选择指定的数据库,只需要用USE+数据库名就可以。通过use某个数据库来对该数据库中的表进行操作,避免多个数据库中的表名冲突。
数据表
数据库中主要的操作还是与数据表中的数据打交道,对数据的增删改查都是基于数据表的来进行管理和控制的。首先我先列举一下基本的增删改查操作,然后再介绍其中的细化操作。
创建数据表
数据表的创建与数据库类似,是用create+table+表名构成。
CREATE TABLE 表名(列名 列属性);
与创建数据库不同的是,在创建数据表时,我们必须在表中添加至少一个列及其属性,否则无法成功创建。列属性规定了该列输入的数据属性是什么,比如INT,VARCHAR等。
以下是创建一个数据表的示例代码。
Create Table: CREATE TABLE test (
`id` int(10) NOT NULL auto_increment,
`title` varchar(100) NOT NULL default '',
`author` varchar(40) NOT NULL default '',
`date` date default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `INDEX` (`author`)
) ENGINE=InnoDB
数据表创建后是一个空表,我们可以向表中添加数据或者修改表。
为了避免表名重复,我们可以在TABLE后面加上“IF NOT EXISTS”,来创建一个数据库中不存在的表。
创建临时表
通过create table,我们所创建的表是保存在系统中的,即使下线或关机也不会丢失。在mysql中还可以创建临时表,用来存储临时使用的数据。
CREATE TEMPORARY TABLE 表名;
临时表的操作与正常数据表相同,同样适用增删改查操作。如果想保存临时表中的数据,可以创建一个新表并把临时表的数据复制进去。
复制数据表
数据表的复制结合了创建和查询语句的功能,有两种复制数据表的方法。
第一种是查看原表的数据结构,按照该结构创建新表并插入原表的数据。
SHOW CREATE TABLE 原表名 \G;
通过该语句,得到创建原表时的指令,并使用该指令中的数据结构创建一个新表。之后,使用insert将数据导进去。
INSERT INTO 复制表 (列名1,列名2)
-> SELECT 原表列名1,原表列名2,
-> FROM 原表;
第二种是直接查询原表的信息,并在创建新表时传进去。
CREATE TABLE 新表(
SELECT * FROM 原表)
这种操作就很骚了,在select语句中我们可以指定新表中要复制的数据列,根据限定条件选择不同数据,或者用AS来更改表名和列名。
删除数据表
数据表的删除是用drop+table+表名。
DROP TABLE 表名;
该指令会直接删除该表中的所有数据,包括其主键和索引。
查询数据表
大量的数据库语句都是基于数据库的查询指令来运行的,在查询的基础上添加不同的查询条件,以输出特定顺序或规则的查询结果。
查询数据表中所有数据的语句如下:
SELECT * FROM 表名;
“ * ”代表任意字符,这条语句查询的是该表中的所有数据。可以根据需求来修改select后面查询的内容,或者从多个表中查询。若表中没有数据,则会显示"Empty set"。
修改数据表
修改的操作有很多种,最直观的操作是用ALTER语句对某个表中数据进行修改。
ALTER TABLE 表名 修改方式;
在使用ALTER TABLE选定要修改的表后,我们可以对该表中的数据以及数据结构进行调整。
数据表重命名
使用ALTER和REMNAME子句来对数据表进行重命名操作。
ALTER TABLE 表名 RENAME 新表名;
数据表添加列
使用ALTER和ADD子句来向数据表中添加新的数据列。
ALTER TABLE 表名 ADD 列名 列属性;
在添加列时,我们可以指定添加的列的位置。如果要将其置于第一列,就在列属性后面加上“FIRST”;如果位于某一列之后,就在列属性后面加上“AFTER + 列名”。
数据表删除列
使用ALTER和DROP子句来删除数据表中的数据列。
ALTER TABLE 表名 DROP 列名;
数据表添加数据
在创建了一个新数据表之后,如果没有指定添加数据,该数据表将是一个不包含任何数据的空表。我们可以手动地使用INSERT子句来向数据表中添加数据。
INSERT INTO 表名 ( 列名1, 列名2,...列名N )
VALUES( 具体值1, 具体值2,...具体值N );
用这条语句,我们就向数据表中插入了一行具体的数据。这里要注意的是,如果插入的这行数据中没有包含所有的列属性,那么这些未包含的列的值就默认为NULL,除非该列有自动生成的属性。在插入数据时,我们必须考虑到那些设置为非空的列,如果插入的数据没有给这些列分配非空的值,这条插入语句就会报错。
对于NULL值的处理,有一个快捷的sql方法可供我们使用。
select ifnull(列名,0) as 列名 from 表名;
通过该语句,我们可以把表中可能为空的数据项转换为0,这样就避免了非空的报错。
在添加数据时,可能会出现数据重复的问题。主键或唯一键是不可重复项,如果插入的数据中有重复则会报错,插入语句无法执行。在这种情况下,我们可以使用IGNORE子句来解决重复问题。
INSERT IGNORE INTO 表名 (列名1, 列名2)
-> VALUES( '具体值1', '具体值2');
通过加入IGNORE子句,当出现数据重复的时候,该条插入指令将会被忽略不执行,并继续运行其他语句,在语句执行结束后抛出一个warning。
数据表删除数据
在数据表中,具体数据的删除不是用DROP来操作,而是使用DELETE子句来执行。
DELETE FROM 表名 [WHERE 操作条件]
在删除数据的指令中,我们可以在WHERE后面加上限制条件来删除指定的数据,如果想要删除该表中的所有数据,可以不加WHERE子句及条件。
MySQL子句
在mysql中有很多子句,可以用来指定特殊的数据范围和处理逻辑。这些子句可以在数据库的增删改查中协同使用,以实现不同的功能。
WHERE子句
where子句用来设置sql语句的条件,类似于编程语言中的if条件句。
无论是select、drop还是insert,都可以使用WHERE子句来界定操作条件。
SELECT 列名1,列名2...fieldN FROM 表1,表2...
[WHERE 操作条件1 [AND [OR]] 操作条件2.....
在WHERE子句中,还可以用AND/OR来加入多个操作条件。
UPDATE子句
对于数据表中特定数据的数据更新和替换,我们可以使用UPDATE子句来实现。
UPDATE 表名 SET 列名=具体值 WHERE 其他列名=具体值;
通过UPDATE + WHERE语句,我们可以将某数据表中基于某个数据的值全部更新。比如,将id为3的字段的age属性改为40。
此外,我们可以使用UPDATE子句+REPLACE子句,将数据表中相同的字段统一替换为新字段。
UPDATE 表名 SET 列名=REPLACE(列名, '原字段', '新字段')
其中WHERE和REPALCE子句可以同时使用,将具有某个属性的数据的特定字段替换掉。
UPDATE 表名 SET 列名 = REPLACE(列名, '原字段', '新字段') where
其他列名 = 具体值;
LIKE子句
LIKE子句在数据库中用来获取操作语句的条件。以查询语句为例,如果要查询一个id为10的数据行,我们会用where id=10作为查询条件。而使用LIKE子句,我们可以查询包含某个特定字符的数据。比如要查询数据表中姓“王”的数据行,我们可以设定条件为“WHERE name LIKE ‘张%’。
SELECT 列名1, 列名2,...列名N FROM 表名
WHERE 列名1 LIKE 条件1 [AND [OR]] 列名2 = '具体值'
有了LIKE子句,我们可以在条件中使用“%”来代替任意字符,并展示所有符合部分条件的结果。其他情况下,LIKE和“=”可以起到同样的作用。
GROUP BY子句
GROUP BY子句允许我们根据列的属性来对查询语句结果进行分组,并在语句中对列的数据调用函数进行计算处理。
SELECT 列名, 计算方法(任意列名) FROM 表名
WHERE 列名 操作 value
GROUP BY 列名;
在对列进行计算操作时,我们可以使用 COUNT, SUM, AVG等函数方法,来对该列的数据进行计算取值。
ORDER BY 子句
ORDER BY 子句可以用来根据数据表中某一列的值进行顺序排序。其中有ASC和DESC两个语句,分别用来进行升序排序和降序排序。
SELECT * from 表名 ORDER BY 列名 ASC;
SELECT * from 表名 ORDER BY 列名 DESC;
ODRER BY语句可以对日期,数字和字符串进行排序,字符串是按照字母表顺序来进行排序的。
索引
mysql中的索引是其运行效率高的重要原因之一。添加索引使得mysql的查询速度大大提升,用户可以通过查询索引来快速搜索到指定的数据项。
索引虽然提高了数据库查询的速度,但却会影响数据库的修改操作速度。数据的添加、修改和删除都要连同索引一起处理,因此会增加数据更新的耗时。索引的存储也会占据额外的存储空间,因此要注意不要滥用索引。
mysql中的索引是很重要的一部分内容,在这里只是简单描述一下,主要介绍一下如何进行索引的添加和修改操作。
创建索引
普通索引的创建是很简单的,用CREATE子句根据数据表中的某一列来创建。要注意的是,如果该列是TEXT或BLOB类型,索引的length属性必须被指定。
CREATE INDEX 索引名 ON 表名 (列名)
添加索引
如果数据表中已有索引,也可以用ALTER来向表中添加新索引。
ALTER table 表名 ADD INDEX 索引名(列名)
删除索引
索引的删除与表的删除相同,是调用DROP子句来操作的。
DROP INDEX [索引名] ON 表名;
唯一索引
唯一索引与普通索引不同,该列中的数据值必须是独一无二的(允许有NULL,但只能有一个)。唯一索引在创建时需要用UNIQUE子句来修饰。
CREATE UNIQUE INDEX 索引名 ON 表名(列名(length))
ALTER table 表名 ADD UNIQUE [索引名] (列名(length))
此外,索引可以在创建表的同时被创建。将创建索引的语句放在表的列属性后面即可。
CREATE TABLE 表名(
ID INT NOT NULL,
username VARCHAR(10) NOT NULL,
UNIQUE [索引名] (username(length)) );
UNION操作符
在mysql中,UNION操作符用来将多个查询语句中的数据项组合到一个表中去,并集中展示出来。
SELECT 列名1, 列名2, ... 列名n FROM tables
[WHERE 操作条件]
UNION [ALL | DISTINCT]
SELECT 列名1, 列名2, ... 列名n
FROM tables
[WHERE 操作条件];
使用UNION可以将多个表中的多个数据项组合在一起,并且将重复项从中剔除。要注意的是,所选的多个表中的列属性应当是相同的。在使用UNION后,还可以用ORDER BY等子句对其进行排序。
如果想要保留表中的重复字段,我们可以使用UNION ALL语句。
JOIN连接语句
在MySQL中,我们可以单独查询单个表,当然也可以同时查询多个表。要实现多表查询,就要使用mysql中的JOIN语句来连接要查询的多个数据表,使它们在同一个临时表中显示全部的数据。
MySQL中的JOIN语句有三种,分别是INNER JOIN,LEFT JOIN和RIGHT JOIN,它们有不同的作用。
INNER JOIN,被称为内连接或等值连接,用来获取两个表中字段相匹配的数据。也就是输出两个数据表的交集。
SELECT a.id, a.student, b.score FROM table_a
INNER JOIN table_b ON a.student = b.student;
LEFT JOIN,也叫左连接,用来获取左侧表中的所有记录,即使右表没有相匹配的记录。也就是输出左侧表加上右侧表中与左侧表的交集。
SELECT a.id, a.student, b.score FROM table_a
LEFT JOIN table_b ON a.student = b.student;
RIGHT JOIN,也叫右连接,用来获取右侧表中的所有记录,即使左表没有相匹配的记录。也就是输出右侧表加上左侧表中与右侧表的交集。RIGHT JOIN与LEFT JOIN是完全相反的。
SELECT a.id, a.student, b.score FROM table_a
RIGHT JOIN table_b ON a.student = b.student;
JOIN语句在mysql中是很实用的操作语句,对于左右内连接一定要搞明白。
MySQL事务
MySQL是支持事务的数据库管理系统,满足事务的ACID属性,可以用事务来管理增删改查操作,维护数据库的完整性,对于大量且复杂的数据处理任务很有帮助。关于MySQL的事务性不在这里展开解释,在这里简单写一下mysql事务的操作方法。
事务控制语句在mysql中可以直接输入并使用。
BEGIN:用来开启一个事务。
COMMIT:确认并提交数据,将当前对数据库的操作永久保存到数据库中。
ROLLBACK:事务回滚,结束数据库的操作,并撤销未保存的操作。
以上是主要的三条控制语句。用来开启和结束sql事务。还有一些其他语句,可以配合进行事务处理。
SAVEPOINT xxx:在事务中设置xxx为保存点,可以标记多个保存点,来设置数据库的开发进度,方便回滚操作。
RELEASE SAVEPOINT xxx:释放保存点xxx,如果不存在该点,执行删除会报错。
ROLLBACK TO xxx:回滚到保存点xxx。
SET TRANSACTION:设置数据库的隔离级别。在InnoDB引擎中,支持事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
除了手动开启和结束事务,在MySQL中我们还可以设置自动提交事务。
输入SET AUTOCOMMIT=1开启自动事务提交,输入SET AUTOCOMMIT=0 来禁止自动事务提交。
MySQL中的基本操作语句就是这些了,也许还有没有整理上的内容,后面我会继续补充。
关于MySQL数据库,还有正则表达式的使用、MySQL函数的用法、导入导出数据操作以及防范SQL注入攻击等等许多知识点没有整理。我会另起篇幅来分别介绍这些内容点的。