【数据库】mysql基础

一、简介

什么是数据库？
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database

术语：

• 数据库: 数据库是一些关联表的集合。
• 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
• 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
• 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
• 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
• 外键：外键用于关联两个表。
• 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
• 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
• 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

二、安装（linux centos）

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum update
yum install mysql-server

权限设置：

chown -R mysql:mysql /var/lib/mysql/

初始化 MySQL：

mysqld --initialize

启动 MySQL：

systemctl start mysqld

查看 MySQL 运行状态：

systemctl status mysqld

三、常用的mysql命令

3.1、查看版本：mysqladmin --version

[root@localhost ~]# mysqladmin --version
mysqladmin  Ver 8.42 Distrib 5.6.51, for Linux on x86_64

3.2、无密码连接mysql

[root@localhost ~]#  mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

3.2、给mysql设置用户名密码并连接

Mysql安装成功后，默认的root用户密码为空，你可以使用以下命令来创建root用户的密码：

#配置用户名密码
 mysqladmin -u root password "admin123"   
 
 #使用用户名密码连接mysql，注意-p后面不加空格，如果密码有特殊字符请加转义符
 mysql -u root -padmin123
 
 #密码密文连接方式
[root@host]# mysql -u root -p
Enter password:******

[root@localhost ~]#  mysqladmin -u root password "admin123"
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost ~]# mysql -u root -padmin123
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

3.3、退出mysql的命令：exit

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[root@localhost ~]#

3.4、创建数据库

create database 数据库名;

创建数据库，下面的命令的作用：

1. 如果数据库不存在则创建，存在则不创建。
2. 创建testdemo数据库，并设定编码集为utf8

CREATE DATABASE IF NOT EXISTS testdemo DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

3.5、删除数据库

#连接mysql后，使用下面命令删除数据库
drop database 数据库名; 

#直接删除
[root@localhost ~]# mysqladmin -u root -p drop testdemo
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'testdemo' database [y/N] y
Database "testdemo" dropped

3.6、选择数据库

#查询当前mysql中所有的数据库
show databases;
#选择其中的一个数据库
use 数据库名;

3.7、创建数据表

创建MySQL数据表需要以下信息：

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

语法
以下为创建MySQL数据表的SQL通用语法：

CREATE TABLE table_name (column_name column_type);

创建数据表实例：

CREATE TABLE stu_tbl(
stu_id INT NOT NULL AUTO_INCREMENT,
stu_title VARCHAR(100) NOT NULL,
stu_author VARCHAR(40) NOT NULL,
submission_date DATE,
PRIMARY KEY ( stu_id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.8、删除数据表

DROP TABLE table_name ;

3.9、插入数据

#选择数据库
mysql> use emp_manage;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

#在该数据库 emp_manage中往数据表stu_tbl 中插入数据
Database changed
INSERT INTO stu_tbl 
(stu_title, stu_author, submission_date)
VALUES
("数据库教程", "Lili", NOW());

3.10、 查询数据

语法
以下为在MySQL数据库中查询数据通用的 SELECT 语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

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

3.10.1、查询表中name=“张三”的数据:select+where

#表tbl_user中所有的数据展示如下
mysql> select * from tbl_user;
+----+--------+------+----------+---------------------+
| id | name   | age  | salary   | bir                 |
+----+--------+------+----------+---------------------+
|  1 | 张三   |   18 |  5000.00 | 2022-03-29 14:38:40 |
|  2 | 李四   |   20 | 10000.00 | 2022-03-29 14:38:40 |
+----+--------+------+----------+---------------------+
2 rows in set (0.00 sec)

#查询表中name=“张三”的数据
mysql> select * from tbl_user where name='张三' ;
+----+--------+------+---------+---------------------+
| id | name   | age  | salary  | bir                 |
+----+--------+------+---------+---------------------+
|  1 | 张三   |   18 | 5000.00 | 2022-03-29 14:38:40 |
+----+--------+------+---------+---------------------+
1 row in set (0.00 sec)

3.10.2、查询表中age='18’的数据，并且只返回两条:select+where+limit

mysql> select * from tbl_user where age='18' limit 2;
+----+--------+------+----------+---------------------+
| id | name   | age  | salary   | bir                 |
+----+--------+------+----------+---------------------+
|  1 | 张三   |   18 |  5000.00 | 2022-03-29 14:38:40 |
|  3 | 王五   |   18 | 11000.00 | 2022-03-29 16:42:05 |
+----+--------+------+----------+---------------------+
2 rows in set (0.00 sec)

3.10.3、根据数据库名+表名查询

语法：

select * from 数据库名.表名;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| emp_manage         |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> select * from emp_manage.table1;
+--------+-----------------+------------+-----------------+
| stu_id | stu_title       | stu_author | submission_date |
+--------+-----------------+------------+-----------------+
|      1 | 数据库教程      | Lili       | 2022-03-29      |
|      2 | python教程      | 张三       | 2022-03-29      |
+--------+-----------------+------------+-----------------+
2 rows in set (0.00 sec)

3.11、多表查询

多表查询分为 内、外连接

外连接
分为左连接（left join 或left outer join）、右连接（right join 或者 right outer join）、和完整外部连接 （full join 或者 full outer join）

内连接：
概念：内连接就是用比较运算符比较要用连接列的值的连接

3.11.1、外连接-左连接(left join)

左连接（left join 或 left outer join）的结果就是left join子句中的左表的所有行，而不仅仅是链接列所匹配的行，如果左表中的某行在右表中没有匹配，则在相关联的结果行中右表的所有选择列均为空值（NULL）

SQL语法

select * from table1 left join table2 on table1.条件列名 = table2.条件列名；

3.11.2、外连接-右连接(right join)

与左连接相反

SQL语法

select * from table1 right join table2 on table1.条件列名 = table2.条件列名；

3.11.3、内连接(join)

SQL语法：

select *fron table1 join table2 on table1.条件列名 = table2.条件列名

返回符合匹配条件的两表列

3.12、删除 MySQL 数据表中的记录

语法

DELETE FROM table_name [WHERE Clause]

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

举例：删除table2中名字叫赵六的数据

delete from  table2 where name = "赵六";

3.13、修改或更新 MySQL 中的数据

语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

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

举例：更新table2表中王五的年龄为21

 update table2 set age = 21 where name = "王五";

3.14、排序ORDER BY

语法
以下是 SQL SELECT 语句使用 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 子句来设置条件。

举例：查询table2 按照age升序排序；

 select * from table2 order by age;

3.15、执行sql脚本

mysql -u root -p密码 <SQL脚本路径

data.sql （SQL脚本文件示例）

drop database if EXISTS emp_manage;
create database emp_manage CHARACTER SET utf8 COLLATE utf8_general_ci;
use emp_manage;
        create table table2(
        id int(10) primary key auto_increment,
        name varchar(50),
        age int(20),
        salary double(10,2),
        bir timeStamp
);

insert into table2(name,age,salary,bir)
values('张三',18,5000,Now());
insert into table2(name,age,salary,bir)
values('李四',20,10000,Now());
insert into table2(name,age,salary,bir)
values('王五',18,11000,Now());
insert into table2(name,age,salary,bir)
values('赵六',18,12000,Now());

四、SQL 查询实例(力扣题)

4.1、2020年最后一次登录

表: Logins

+----------------+----------+
| 列名           | 类型      |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
+----------------+----------+
(user_id, time_stamp) 是这个表的主键。
每一行包含的信息是user_id 这个用户的登录时间。

编写一个 SQL 查询，该查询可以获取在 2020 年登录过的所有用户的本年度 最后一次 登录时间。结果集 不 包含 2020 年没有登录过的用户。

返回的结果集可以按 任意顺序 排列。

查询结果格式如下例。

输入：
Logins 表:
+---------+---------------------+
| user_id | time_stamp          |
+---------+---------------------+
| 6       | 2020-06-30 15:06:07 |
| 6       | 2021-04-21 14:06:06 |
| 6       | 2019-03-07 00:18:15 |
| 8       | 2020-02-01 05:10:53 |
| 8       | 2020-12-30 00:46:50 |
| 2       | 2020-01-16 02:49:50 |
| 2       | 2019-08-25 07:59:08 |
| 14      | 2019-07-14 09:00:00 |
| 14      | 2021-01-06 11:59:59 |
+---------+---------------------+
输出：
+---------+---------------------+
| user_id | last_stamp          |
+---------+---------------------+
| 6       | 2020-06-30 15:06:07 |
| 8       | 2020-12-30 00:46:50 |
| 2       | 2020-01-16 02:49:50 |
+---------+---------------------+
解释：
6号用户登录了3次，但是在2020年仅有一次，所以结果集应包含此次登录。
8号用户在2020年登录了2次，一次在2月，一次在12月，所以，结果集应该包含12月的这次登录。
2号用户登录了2次，但是在2020年仅有一次，所以结果集应包含此次登录。
14号用户在2020年没有登录，所以结果集不应包含。

实现如下：

# Write your MySQL query statement below
select user_id,max(time_stamp) last_stamp
from Logins
where year(time_stamp) = '2020'
group by user_id;