【MySql】数据库_MySql基础

欢都洛兰 · 发表于 2021-4-19 13:59

一、数据库简介

1.数据库构成：

数据库（数据）
数据库管理系统（软件）
数据库管理员（人员）
硬件平台（硬件）
软件平台（软件）

二、SQL语句

1.SQL(Structure Query Language)概述

SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建项目，查询内容，更新内容，并删除条目等操作：
Create, Read, Update, and Delete 通常称为CRUD操作。

2.SQL语句分类

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；
DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）增删改；
DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
DQL（Data Query Language）：数据查询语言，用来查询记录（数据）查询。
注意：① SQL命令不区分大小写（但为了区别命令，我一般使用大写）；
② SQL语句已分号（;）结束；

3.DDL：定义数据库的库、表、列等

（1）DDL：操作数据库--库

<1>创建数据仓库

create database 数据库名【character set 编码方式】【 collate 排序规则】

【】表示可选
首先连接数据库

mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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> create database firstdatabases character set gbk collate gbk_chinese_ci;
Query OK, 1 row affected (0.58 sec)

<2>查看所有数据仓库

show databases
如下：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| firstdatabases     |
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
+--------------------+
6 rows in set (0.41 sec)

<3>修改数据仓库属性

alter database 数据库名 character set 编码方式
如下：

mysql> ALTER DATABASE firstdatabases CHARACTER SET utf8;
Query OK, 1 row affected, 1 warning (0.43 sec)

<4>删除数据库

drop database 数据库名
如下：

mysql> DROP DATABASE student;
Query OK, 0 rows affected (0.21 sec)

<5>其它语句

查看当前在用数据仓库

Select database();
如下：

mysql> SELECT databases();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databases()' at line 1
mysql> SELECT database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

切换数据仓库

 use 数据库名
 如下：

mysql> USE firstdatabases;
Database changed

（2）DDL：操作数据库--表

<1>创建表

CREATE TABLE 表名(
列名1 数据类型 [约束],
列名2 数据类型 [约束],
列名n 数据类型 [约束]
);

常用数据类型：
int：整型

double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为
999.99；默认支持四舍五入；
char：固定长度字符串类型； char(10) 'aaa ' 占10位；
varchar：可变长度字符串类型； varchar(10) 'aaa' 占3位；
text：字符串类型，比如小说信息；
blob：字节类型，保存文件信息(视频，音频，图片)；
date：日期类型，格式为：yyyy-MM-dd；
time：时间类型，格式为：hh:mm:ss；
timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值；
datetime:日期时间类型 yyyy-MM-dd hh:mm:ss。
创建如下：

mysql> CREATE TABLE student(name VARCHAR(4),age INT);
Query OK, 0 rows affected (0.72 sec)

<2>查看表

查看当前库中所有表

show tables;
如下：

 mysql> SHOW TABLES;
+--------------------------+
| Tables_in_firstdatabases |
+--------------------------+
| student                  |
+--------------------------+
1 row in set (0.40 sec)

查看表的字段（列）信息（类型、大小等）

desc 表名；// desc表示降序
如下：

mysql> DESC student;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(4) | YES  |     | NULL    |       |
| age   | int        | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

查看表格创建细节

show create table 表名;
如下：

mysql> SHOW CREATE TABLE student;
+---------+--------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                             |
+---------+--------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `name` varchar(4) DEFAULT NULL,
  `age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

<3>修改列的数据类型、列名

alter table 表名 change 旧列名新列名新的数据类型
alter table 表名 modify 旧列名新列名新的数据类型
如下：

mysql> ALTER TABLE student CHANGE name newname VARCHAR(5);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

<4>增加列

alter table 表名 add 新列名新的数据类型
如下：

mysql> ALTER TABLE student ADD newage INT;
Query OK, 0 rows affected (0.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC student;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| newname | varchar(5) | YES  |     | NULL    |       |
| newage  | int        | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

<5>删除列,一次只能删除一列

alter table 表名 drop 列名
如下：

mysql> ALTER TABLE student DROP age;
Query OK, 0 rows affected (1.57 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC student;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| newname | varchar(5) | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

<6>修改表

修改表编码方式

alter table 表名 character set 编码方式
如下：

mysql> ALTER TABLE student CHARACTER SET gbk;
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改表名

alter table 旧表名 rename 新表名;
如下：

mysql> ALTER TABLE student RENAME students;
Query OK, 0 rows affected (0.37 sec)

mysql> SHOW tables;
+--------------------------+
| Tables_in_firstdatabases |
+--------------------------+
| students                 |
+--------------------------+
1 row in set (0.00 sec)

<7>删除表

drop table 表名;
如下：

mysql> DROP TABLE students;
Query OK, 0 rows affected (0.75 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)

（3）DDL：操作数据库--总结

库：

创建：create database 数据库名【character set 编码方式】【 collate 排序规则】
查看：show databases
修改：alter database 数据库名 character set 编码方式
删除：drop database 数据库名
当前：Select database();
切换： use 数据库名
表：
创建：CREATE TABLE 表名(
列名1 数据类型 [约束],
列名2 数据类型 [约束],
列名n 数据类型 [约束]
);
查看： show tables;
细节：show create table 表名;
修改：alter table 表名 character set 编码方式
改名：alter table 旧表名 rename 新表名;
删除：drop table 表名;
列：
增加：alter table 表名 add 新列名新的数据类型
修改：alter table 表名 change 旧列名新列名新的数据类型
alter table 表名 modify 旧列名新列名新的数据类型
删除：alter table 表名 drop 列名

2.DML：数据增删改

注意：在mysql中，字符串类型和日期类型都要用单引号括起来；空值：null;

（1）INSERT：插入数据

insert into 表名(列名1，列名2) values(数值1，数值2),
（数值1，数值2），
（数值1，数值2）;
给所有列添加数据时，可省略列名；
如下：

mysql> insert into student(name,number,age) values('张三',27020,16),('李四',27050,18);
Query OK, 2 rows affected (0.44 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from student;
+--------+--------+------+
| name   | number | age  |
+--------+--------+------+
| 张三   |  27020 |   16 |
| 李四   |  27050 |   18 |
+--------+--------+------+
2 rows in set (0.00 sec)

（2）UPDATA：修改数据

UPDATE 表名 SET 列名1=列值1,列名2=列值2 ... WHERE 列名=值
如下：

mysql> UPDATE student set name='王五',number=22222,age=22 where age=16;
Query OK, 1 row affected (0.51 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM student;
+--------+--------+------+
| name   | number | age  |
+--------+--------+------+
| 王五   |  22222 |   22 |
| 李四   |  27050 |   18 |
+--------+--------+------+
2 rows in set (0.00 sec)

（3）DELETE：删除数据

DELETE from 表名【WHERE 列名=值】
如下：

mysql> DELETE FROM student WHERE age=18;
Query OK, 1 row affected (0.43 sec)

mysql> select * from student;
+--------+--------+------+
| name   | number | age  |
+--------+--------+------+
| 王五   |  22222 |   22 |
+--------+--------+------+
1 row in set (0.00 sec)

（4）运算符

（1）算术运算符:+，-，*，/(除法),求余(%)
示例：
5/2
5%2
2/5
2%5
（2）赋值运算符:=
(3) 逻辑运算符：
and(并且),or(或者),not（取非）
作用:用于连接多个条件时使用
(4) 关系运算符：
· >,<,>=,<=,!=(不等于),=(等于),<>(不等于)

3.DCL：定义权限

（1）创建用户

create user 用户名@指定ip identified by 密码;
create user 用户名@客户端ip identified by 密码; 指定IP才能登陆
create user 用户名@‘% ’ identified by 密码任意IP均可登陆
如下：

mysql> CREATE USER haha@localhost IDENTIFIED BY '123';
Query OK, 0 rows affected (0.44 sec)

（2）用户授权

grant 权限1,权限2,........,权限n on
数据库名.* to 用户名@IP; 给指定用户授予指定指定数据库指定权限

grant all on . to 用户名@IP 给指定用户授予所有数据库所有权限
如下：

mysql> grant select,insert,delete,create on firstdatabases.* to haha@localhost;
Query OK, 0 rows affected (0.40 sec)

（3）权限查询

show grants for 用户名@IP;
如下：

mysql> show grants for haha@localhost;
+----------------------------------------------------------------------------------+
| Grants for haha@localhost                                                        |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `haha`@`localhost`                                         |
| GRANT SELECT, INSERT, DELETE, CREATE ON `firstdatabases`.* TO `haha`@`localhost` |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

（4）撤销用户权限

revoke 权限1,权限2,........,权限n on 数据库名.* from 用户名@IP;
如下：

mysql> revoke INSERT on firstdatabases.* from haha@localhost;
Query OK, 0 rows affected (0.49 sec)

（5）删除用户

drop user 用户名@IP;
如下：

mysql> drop user haha@localhost;
Query OK, 0 rows affected (0.47 sec)

4.DQL：查询语句

SELECT 要查询的列名称
FROM 表名称
WHERE 限定条件 /行条件/
GROUP BY grouping_columns /对结果分组/
HAVING condition /分组后的行条件/
ORDER BY sorting_columns /对结果分组/
LIMIT offset_start, row_count /结果限定/

（1）简单查询

查询所有列：
SELECT * FROM 表名;
查询指定列：
select 列1,列2 from 表名；

mysql> SELECT * FROM grade;
+---------+--------------+
| gradeid | gradename    |
+---------+--------------+
|       1 | 第一阶段     |
|       2 | 第二阶段     |
|       3 | 第三阶段     |
|       4 | 就业期       |
+---------+--------------+
4 rows in set (0.00 sec)

（2）条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
=、!=、<>、<、<=、>、>=; BETWEEN…AND； IN(set)； IS NULL； AND；OR； NOT；

mysql> SELECT studentno,score FROM result WHERE subjectid=8 AND score<60;
+-----------+-------+
| studentno | score |
+-----------+-------+
| S1103001  |    59 |
+-----------+-------+
1 row in set (0.00 sec)

（3）模糊查询

当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。
语法: 列名 like '表达式' //表达式必须是字符串
通配符:
_(下划线): 任意一个字符
%：任意0~n个字符

mysql> SELECT * FROM student WHERE studentname LIKE '凌_';
+-----------+-----------+-------------+-----+---------+-------------+-----------------------------+---------------------+---------------------+
| studentno | loginpwd  | studentname | sex | gradeid | phone       | address                     | borndate            | email               |
+-----------+-----------+-------------+-----+---------+-------------+-----------------------------+---------------------+---------------------+
| S1101003  | 228996247 | 凌辉        | 女  |       1 | 353149818   | 北京市海淀区成府路          | 1993-04-04 00:00:00 | eepispykh@oitbl.com |
| S1102001  | 228996255 | 凌晨        | 女  |       2 | 18866660005 | 江苏                        | 2001-01-01 00:00:00 | lingchen@qq.com     |
+-----------+-----------+-------------+-----+---------+-------------+-----------------------------+---------------------+---------------------+
2 rows in set (0.00 sec)

（4）字段控制查询

<1>去除重复记录：
SELECT DISTINCT 列 FROM 表;
<2>两列取和:
(comm,0)可以将null替换为0；
SELECT ,列1+列2(comm,0) FROM 表;
<3>添加别名（as可以省略）
SELECT , 列1+列2(comm,0) AS total FROM 表;

（5）排序

order by 列名 asc/desc

如下：

mysql> SELECT * FROM student WHERE gradeid=1 ORDER BY borndate ASC;
+-----------+-----------+--------------+-----+---------+-------------+-----------------------------+---------------------+---------------------+
| studentno | loginpwd  | studentname  | sex | gradeid | phone       | address                     | borndate            | email               |
+-----------+-----------+--------------+-----+---------+-------------+-----------------------------+---------------------+---------------------+
| S1101006  | 228996253 | 欧阳燕飞     | 男  |       1 | 13512345678 | 河南省南阳市                | 1987-06-19 00:00:00 | dkwkiqbrj@yrkro.com |
| S1101005  | 228996252 | 夏一桐       | 男  |       1 | 397739963   | 北京市朝阳区大屯            | 1989-09-12 00:00:00 | npgiygxox@ootml.com |
| S1101001  | 228996245 | 金蝶         | 男  |       1 | 717215147   | 天津市河西区                | 1989-11-09 00:00:00 | okiwcydml@ryhlo.com |
| S1101002  | 228996246 | 洛飞         | 女  |       1 | 666762663   | 天津市南开区                | 1990-02-07 00:00:00 | jnqlpkdwb@nsjpt.com |
| S1101004  | 228996251 | 白燕         | 男  |       1 | 676151367   |                             | 1990-09-01 00:00:00 | cxmnnrhfn@sjsam.com |
| S1101007  | 228996254 | 孟祥亚       | 男  |       1 | 13512345679 | 河南省洛阳市涧西区          | 1993-02-22 00:00:00 | gekdinmky@ijisq.com |
| S1101003  | 228996247 | 凌辉         | 女  |       1 | 353149818   | 北京市海淀区成府路          | 1993-04-04 00:00:00 | eepispykh@oitbl.com |
+-----------+-----------+--------------+-----+---------+-------------+-----------------------------+---------------------+---------------------+
7 rows in set (0.00 sec)

多列排序：当前面的列的值相同的时候，才会按照后面的列值进行排序

（6）聚合函数

聚合函数是用来做纵向运算的函数：

COUNT(列名)：统计指定列不为NULL的记录行数；

mysql> SELECT COUNT(*) FROM student WHERE address LIKE '北京%';
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

MAX(列名)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

mysql> SELECT * FROM subject WHERE hour=(SELECT MAX(hour) FROM subject WHERE gradeid=2);
+-----------+-----------------------------+------+---------+
| subjectid | subjectname                 | hour | gradeid |
+-----------+-----------------------------+------+---------+
|         2 | 深入.NET平台和C#编程        |   76 |       2 |
+-----------+-----------------------------+------+---------+
1 row in set (0.00 sec)

MIN(列名)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM(列名)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

mysql> SELECT SUM(hour) FROM subject WHERE gradeid=2;
+-----------+
| SUM(hour) |
+-----------+
|       221 |
+-----------+
1 row in set (0.00 sec)

AVG(列名)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

mysql> SELECT AVG(score) FROM result WHERE subjectid=8;
+------------+
| AVG(score) |
+------------+
|    87.2500 |
+------------+
1 row in set (0.00 sec)

（7）分组查询

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。
注意:如果查询语句中有分组操作，则select后面能添加的只能是聚合函数和被分组的列名

mysql> SELECT subjectid,COUNT(*) FROM result GROUP BY subjectid;
+-----------+----------+
| subjectid | COUNT(*) |
+-----------+----------+
|         3 |       11 |
|         2 |        1 |
|         4 |        4 |
|         5 |        4 |
|         6 |        4 |
|         7 |        4 |
|         8 |        4 |
|         9 |        4 |
+-----------+----------+
8 rows in set (0.00 sec)

mysql> SELECT gradeid,sex,COUNT(*) FROM student GROUP BY gradeid,sex;
+---------+-----+----------+
| gradeid | sex | COUNT(*) |
+---------+-----+----------+
|       1 | 男  |        5 |
|       1 | 女  |        2 |
|       2 | 女  |        2 |
|       2 | 男  |        2 |
|       4 | 男  |        2 |
|       4 | 女  |        2 |
+---------+-----+----------+
6 rows in set (0.00 sec)

（8）HAVING

注：having与where的区别:
1.having是在分组后对数据进行过滤,where是在分组前对数据进行过滤
2.having后面可以使用分组函数(统计函数)
where后面不可以使用分组函数。
WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分
组；而HAVING是对分组后数据的约束。

（9）LIMIT限制显示行数

SELECT FROM emp LIMIT 开始下标, 显示行数;
分页查询：
limit (pageindex-1)pagesize,pagesize;
pageIndex 页码值 pageSize 每页显示条数

查询语句书写顺序：select – from- where- groupby- having- order by-limit
查询语句执行顺序：from - where -group by -having - select - order by

红蓝黄 · 发表于 2021-4-19 14:03

这个很厉害

LichkingArthas · 发表于 2021-4-19 14:14

我直接收藏

cj4862 · 发表于 2021-4-19 14:20

感谢分享

纸偶 · 发表于 2021-4-19 14:23

以前学过，现在忘的七七八八了，复习一下

zylz9941 · 发表于 2021-4-19 14:24

谢谢分享

yimaoer · 发表于 2021-4-19 14:29

插个眼, 方便后续查看.

展大锤 · 发表于 2021-4-19 14:38

这个腻害了，基本操作都包含在内了

546866 · 发表于 2021-4-19 14:42

基础资料不错啊

有、 · 发表于 2021-4-19 14:43