数据库基本操作

数据库基本操作

连接数据库

root> mysql -u root -p
Enter password:admin

创建数据库

mysql> CREATE DATABASE <数据库名>
或则使用mysqladmin创建
mysqladmin -u root -p create <数据库名>

选择数据库

mysql> use <数据库名>
Database changed

创建数据表

• 表名
• 表字段名
• 定义每个表字段

通用语法：CREATE TABLE <数据表名> (column_name column_type);

mysql> CREATE DATABASE RUNOOB;
mysql> USE RUNOOB;
mysql> CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `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` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> CREATE TABLE IF NOT EXISTS `Websites`(
`id` INT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`url` VARCHAR(500) NOT NULL,
`alexa` INT UNSIGNED NOT NULL,
`country` VARCHAR(10) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=InnoDB DEFAULT CHARSET = utf8;

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。

AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。

PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。

ENGINE 设置存储引擎，CHARSET 设置编码。

数据表中插入数据

通用语法：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

//插入单条记录
mysql> INSERT INTO runoob_tbl 
		(runoob_title, runoob_author, submission_date)
   		VALUES
    	("学习 PHP", "菜鸟教程", NOW());
//插入多条记录
mysql> INSERT INTO websites
(name,url,alexa,country)
VALUES
("Google","https://www.google.com/",1,"USA"),
("淘宝","https://www.taobao.com/",13,"CN"),
("菜鸟教程","http://www.runoob.com/",4689,"CN"),
("微博","http://www.weibo.com/",20,"CN"),
("Facebook","http://www.facebook.com/",3,"USA"),
("stackoverflow","http://www.stackoverflow.com/",0,"IND");

在以上实例中，我们并没有提供 runoob_id 的数据，因为该字段我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性。 所以，该字段会自动递增而不需要我们去设置。实例中 NOW() 是一个 MySQL 函数，该函数返回日期和时间。

删除数据库

mysql> DROP DATABASE <数据库名>
或者使用mysqladmin删除
mysqladmin -u root -p drop <数据库名>

删除数据表

通用语法：DROP TABLE <数据表名>;

mysql> use RUNOOB;
mysql> DROP TABLE runoob_tbl;

查询数据

**where：**数据库中常用的是where关键字，用于在初始表中筛选查询。它是一个约束声明，用于约束数据，在返回结果集之前起作用。

**group by:**对select查询出来的结果集按照某个字段或者表达式进行分组，获得一组组的集合，然后从每组中取出一个指定字段或者表达式的值。

**having：**用于对where和group by查询出来的分组经行过滤，查出满足条件的分组结果。它是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作。

执行顺序

select –>where –> group by–> having–>order by

通用语法

SELECT column_name,column_name
FROM <数据表名>
[WHERE Clause]
[LIMIT N][ OFFSET M]

• 查询语句中你可以使用一个或者多个表，表之间使用逗号（,）分割，并使用WHERE语句来设定查询条件。
• SELECT 命令可以读取一条或者多条记录。
• 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
• 你可以使用 WHERE 语句来包含任何条件。
• 你可以使用 LIMIT 属性来设定返回的记录数。
• 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

mysql> select * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习PHP      | 菜鸟教程       | 2020-07-06      |
|         2 | JAVA教程     | RUNOOB.COM    | 2020-07-06       |
|         3 | 学习 MySQL   | 菜鸟教程       | 2020-07-06      |
+-----------+--------------+---------------+-----------------+

WHERE 子句

通用语法：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

• 查询语句中你可以使用一个或者多个表，表之间使用逗号（,）分割，并使用WHERE语句来设定查询条件。
• 你可以在 WHERE 子句中指定任何条件。
• 你可以使用 AND 或者 OR 指定一个或多个条件。
• WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
• WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

SELECT * from runoob_tbl WHERE runoob_author='菜鸟教程';

MySQL 的 WHERE 子句的字符串比较是不区分大小写的，使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

SELECT * from runoob_tbl WHERE BINARY runoob_author='runoob.com';
SELECT * from runoob_tbl WHERE BINARY runoob_author='RUNOOB.COM';

UPDATE 更新

通用语法：

UPDATE <数据表名> SET field1=new-value1, field2=new-value2
[WHERE Clause]

• 可以同时更新一个或多个字段。
• 可以在 WHERE 子句中指定任何条件。
• 可以在一个单独表中同时更新数据。

mysql> UPDATE runoob_tbl
    SET runoob_title = 'C#教程'
    WHERE runoob_id = 2;
mysql> SELECT * FROM runoob_tbl where runoob_id = 2;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         2 | C#教程       | RUNOOB.COM    | 2020-07-06      |
+-----------+--------------+---------------+-----------------+

DELETE 语句

通用语法：

DELETE FROM <数据表名> [WHERE Clause]

• 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
• 可以在 WHERE 子句中指定任何条件
• 可以在单个表中一次性删除记录。

mysql> DELETE FROM runoob_tbl WHERE runoob_id=3;
mysql> SELECT * FROM runoob_btl;
+-----------+-----------------+---------------+-----------------+
| runoob_id | runoob_title    | runoob_author | submission_date |
+-----------+-----------------+---------------+-----------------+
|         1 | 学习PHP         | 菜鸟教程       | 2020-07-06       |
|         2 | C#教程          | RUNOOB.COM    | 2020-07-06       |
|         4 | Learning Python | RUNOOB.COM    | 2020-06-28      |
+-----------+-----------------+---------------+-----------------+

LIKE 子句

通用语法：

SELECT field1, field2,...fieldN 
FROM <数据表名>
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

like 匹配/模糊匹配，会与 % 和 _ 结合使用。

'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的

• 可以在 WHERE 子句中指定任何条件。
• 可以在 WHERE 子句中使用LIKE子句。
• 可以使用LIKE子句代替等号 =。
• LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
• 可以使用 AND 或者 OR 指定一个或多个条件。
• 可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

mysql> SELECT * from runoob_tbl  WHERE runoob_author LIKE '%COM';
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3         | 学习 Java     | RUNOOB.COM     | 2015-05-01      |
| 4         | 学习 Python   | RUNOOB.COM     | 2016-03-06      |
+-----------+---------------+---------------+-----------------+

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: 可选，返回所有结果集，包含重复数据。

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

ORDER BY 子句

通用语法：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

• 你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
• 你可以设定多个字段来排序。
• 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
• 你可以添加 WHERE…LIKE 子句来设置条件。

GROUP BY 子句

GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

通用语法：

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小明    |        3 |
| 小王    |        2 |
| 小丽    |        1 |
+--------+----------+

使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽    |            2 |
| 小明    |            7 |
| 小王    |            7 |
| NULL   |           16 |
+--------+--------------+

我们可以使用 coalesce 来设置一个可以取代 **NULL*的名称，coalesce 语法：

select coalesce(a,b,c);
参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

mysql> SELECT coalesce(name,"总数"),SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+-------------------------+--------------+
| coalesce(name,"总数")   | singin_count |
+-------------------------+--------------+
| 小丽                    |            2 |
| 小明                    |            7 |
| 小王                    |            7 |
| 总数                    |           16 |
+-------------------------+--------------+

连接数据表

JOIN的使用

你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

  mysql> 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;
  

  

• **LEFT JOIN（左连接）：**获取左表所有记录和右表中联结字段相等的记录。

  mysql> 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;
  

  

• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录和左表中联结字段相等的记录。

  mysql> 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;
  

  

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）永远返回 NULL，即 NULL = NULL 返回 NULL 。

MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

注意：

select * , columnName1+ifnull(columnName2,0) from tableName;

columnName1，columnName2 为 int 型，当 columnName2 中，有值为 null 时，columnName1+columnName2=null， ifnull(columnName2,0) 把 columnName2 中 null 值转为 0。

导入导出SQL文件

导入数据库

常用source命令进行数据库的导入，在数据库控制台环境下进行导入操作~

进入mysql数据库控制台，如mysql -u root -p

选择导入数据表的数据库，如mysql> use <数据库名>

使用source命令，后面参数为脚本文件(如.sql)：mysql> source d:/dbname.sql

导出数据库

使用mysqldump命令进行数据库的导出，在windows控制台环境下进行导出操作~

导出整个数据库：

mysqldump -u <用户名> -p <数据库名> > <导出的文件名>
mysqldump -u root -p runoob > d:/test.sql

导出一个表

mysqldump -u <用户名> -p <数据库名> <数据表名> > <导出的文件名>
mysqldump -u root -p runoob runoob_tbl > d:/test_tbl.sql

导出一个数据库结构

mysqldump -u root -p -d --add-drop-table runoob > d:/test_struct.sql
-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table

事务控制语句：

事务的定义

​ 如果把多条语句作为一个整体进行操作的功能，被称为数据库事务***。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败*。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。

事务的特性

可见，数据库事务具有ACID这4个特性：

• Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
• Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
• Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
• Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

事务的隔离级别

​ 对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

SQL标准定义了4种隔离级别，分别对应可能出现的数据不一致的情况：

Isolation Level	脏读（Dirty Read）	不可重复读（Non Repeatable Read）	幻读（Phantom Read）
Read Uncommitted	Yes	Yes	Yes
Read Committed	-	Yes	Yes
Repeatable Read	-	-	Yes
Serializable	-	-	-

脏读：一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。

不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。

幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。可见，幻读就是没有读到的记录，以为不存在，但其实是可以更新成功的，并且，更新成功后，再次读取，就出现了。

• BEGIN 或 START TRANSACTION 显式地开启一个事务；
• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 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 AUTOCOMMIT = 0 禁止自动提交
• SET AUTOCOMMIT = 1 开启自动提交

常见问题

• 加快数据库查询的方法

  1. 建立索引，提升查询效率，但会降低insert、update效率，增加磁盘空间占用。

  2. 优化查询语句，避免全表扫描，例如应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描；

     select id from t where num is null;
     改为
     select id from t where num = 0;
     

  3. 尽量避免大事务操作，提高系统并发能力。

• MySQL可重复读隔离级别的实现原理

  MySQL默认的隔离级别是可重复读，即：事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。 那么MySQL可重复读是如何实现的呢？

  使用的的一种叫MVCC的控制方式 ，即Mutil-Version Concurrency Control,多版本并发控制，类似于乐观锁的一种实现方式。

  实现方式：

  InnoDB在每行记录后面保存两个隐藏的列来，分别保存了这个行的创建时间和行的删除时间。这里存储的并不是实际的时间值,而是系统版本号，当数据被修改时，版本号加1
  在读取事务开始时，系统会给当前读事务一个版本号，事务会读取版本号<=当前版本号的数据
  此时如果其他写事务修改了这条数据，那么这条数据的版本号就会加1，从而比当前读事务的版本号高，读事务自然而然的就读不到更新后的数据了

• 不可重复读和脏读的区别

  不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

• 执行一条SQL语句期间，数据库都做了什么？

  • 客户端

    所谓客户端，即是我们登录与操作Mysql所使用的终端。我们都是在客户端对Mysql进行操作的，无论是输入连接数据库的信息，还是输入查询某个表的SQL，或者是收到Mysql返回给我们的查询信息，这些都是在客户端完成的。

  • 连接器

    用户信息验证→我们在一个客户端跟前，想要使用Mysql数据库，那么第一步就是要先连接上你要使用的数据库。

    用户权限获取→如果用户名密码也没有问题，接下来连接器则会取出权限表读取该用户相应的权限数据。用户跟着所做的所有操作，都基于此时读取到的用户权限。

    连接与超时等待→若等待超过了一段时间，则连接器会自动断开。

  • 分析器

    分析器做的事情就是对你输入的语句做 “词法分析” 与 “语法分析”。

    所谓 “词法分析” ，就是判断每一个你输入的词的准确性。 “语法分析” 就是判断是否符合语法规则。

    假如符合语法规则，则会顺利进行下去并返回相应信息。

  • 优化器

    优化器就是对该语句的执行做优化。例如，该表可能有多个索引，优化器会判断使用哪个索引会使语句的执行效率最高。

  • 执行器

    在上述步骤完成之后，就轮到执行器去执行具体的语句了。

Leetcode刷题

• 第二高的薪水
  编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。

  +----+--------+
  | Id | Salary |
  +----+--------+
  | 1  | 100    |
  | 2  | 200    |
  | 3  | 300    |
  +----+--------+
  
  SELECT IFNULL(
  (SELECT DISTINCT salary
    FROM employee
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1
  ),NULL) AS SecondHighestSalary;
  

• 超过经理收入的员工（self join的使用）
  给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

  +----+-------+--------+-----------+
  | Id | Name  | Salary | ManagerId |
  +----+-------+--------+-----------+
  | 1  | Joe   | 70000  | 3         |
  | 2  | Henry | 80000  | 4         |
  | 3  | Sam   | 60000  | NULL      |
  | 4  | Max   | 90000  | NULL      |
  +----+-------+--------+-----------+
  
  SELECT e1.name Employee
  FROM employee e1
    JOIN employee e2 ON
  (e1.managerId = e2.Id)
  WHERE e1.salary > e2.salary;
  

• 超过5名学生的课
  有一个courses 表 ，有: student (学生) 和 class (课程)。请列出所有超过或等于5名学生的课。

  +---------+------------+
  | student | class      |
  +---------+------------+
  | A       | Math       |
  | B       | English    |
  | C       | Math       |
  | D       | Biology    |
  | E       | Math       |
  +---------+------------+
  
  SELECT class
  FROM courses
  GROUP BY class
  HAVING COUNT(DISTINCT student) >=5;