1 简述
- 数据库是存储数据的仓库，英文DataBase，简称DB。数据库管理系统，操纵和管理数据库的大型软件，英文DataBase Management System，简称DBMS。SQL是操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。
- 关系型数据库
- 建立在关系模型基础上，由多张相互连接的二维表组成的数据库
- 主流的关系型数据库：Oracle、MySQL、SQL Server、PostgreSQL、SQLite
- 特点： 使用表存储结构，格式统一，便于维护。使用SQL语言操作，标准统一，使用方便。
- 非关系型数据库
- 用来处理大量分布式数据，不依赖于固定的表结构。
- 它支持多种数据模型，包括文档、键值对、宽列存储和图形数据库等。这类数据库通常能够更好地适应快速变化的数据需求，且易于水平扩展。
- 如Redies、MongoDB等。
- 本文以MYSQL数据库为例，讲述下数据库的基础操作之增删改查。
2 MYSQL数据库安装使用
2.1 Windows安装
-
下载这个包
-
下载后双击安装即可。安装完成后，Win + R 打开运行框，输入 services.msc，打开服务界面。找到MySQL80、可以启动或者停止MySQL。或者在命令行输入
net start mysql80启动，输入net stop mysql80停止。 -
在开始菜单中找到
MySQL 8.0 Command Line Client命令行工具就可以操作MySQL数据库。 -
或者添加一个
Path系统变量C:\Program Files\MySQL\MySQL Server 8.0\bin，可以直接使用cmd命令行工具操作数据库。
2.2 Linux安装
2.2.1 命令行安装
-
sudo apt-get install mysql-server mysql-client - 数据库编程还需要安装开发包
-
sudo apt-get install libmysqlclient-dev
2.2.2 安装包安装
-
以Centos 7为例，下载8.0.40版本。
-
下载后执行以下命令解压到指定目录
-
mkdir mysql tar -xvf mysql-8.0.40-1.el7.x86_64.rpm-bundle.tar -C mysql
-
-
接下来手动安装以下包
-
rpm -ivh mysql-community-common-8.0.40-1.el7.x86_64.rpm rpm -ivh mysql-community-client-plugins-8.0.40-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-8.0.40-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-compat-8.0.40-1.el7.x86_64.rpm # 这个包如果安装失败，需要openssl开发包，执行 yum install openssl-devel 命令安装 rpm -ivh mysql-community-devel-8.0.40-1.el7.x86_64.rpm rpm -ivh mysql-community-client-8.0.40-1.el7.x86_64.rpm rpm -ivh mysql-community-icu-data-files-8.0.40-1.el7.x86_64.rpm rpm -ivh mysql-community-server-8.0.40-1.el7.x86_64.rpm
-
2.2.3 MySQL使用
-
启停服务
-
# 启动服务 systemctl start mysqld # 重启服务 systemctl restart mysqld # 停止服务 systemctl stop mysqld
-
-
登录
- 安装并启动MySQL服务后，执行
mysql -u root -p命令，输入密码后登录。完整命令为mysql -h [主机名或ip] -u [用户] -p。
- 安装并启动MySQL服务后，执行
-
密码设置
-
# 查看默认密码 grep 'temporary password' /var/log/mysqld.log # 修改密码，登录后执行SQL ALTER USER 'root'@'localhost' IDENTIFIED BY 'Xhh123456%'; # 可以执行以下命令将密码复杂度设置为最低 set global validate_password.policy=0;
-
-
设置数据库远程连接
- ① 修改配置文件
/etc/mysql/mysql.conf.d/mysqld.cnf中的bing-address字段值为bind-address = 0.0.0.0 - ② 重启mysql服务
sudo systemctl restart mysql - ③ 登录mysql数据库
mysql -u root -p - ④ 创建用户并授权，分别执行以下命令
-
# 创建一个用户 your-username CREATE USER 'your-username'@'%' IDENTIFIED BY 'your-pwd'; # 分配权限 GRANT ALL PRIVILEGES ON *.* TO 'your-username'@'%' WITH GRANT OPTION; # 刷新配置 FLUSH PRIVILEGES;
-
- ① 修改配置文件
3 SQL
3.1 SQL通用语法
- SQL语句可以单行或多行书写，以分号结尾。
- SQL语句可以使用空格或者缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
- 注释
- 单行注释：
--注释内容 或者#注释内容 （MySQL特有） - 多行注释：
/*注释内容*/
- 单行注释：
3.2 SQL分类
- DDL(Data Definition Language)：数据定义语言，用来定义数据库对象（数据库、表、字段）
- DML(Data Manipulation Language)：数据操作语言，用来对数据库表中的数据进行增删改
- DQL(Data Query Language)：数据查询语言，用来查询数据库中表的记录
select 字段列表 from 表名 where 条件 group by 分组字段 having 分组后条件 order by 排序字段 limit 分页参数;
- DCL(Data Control Language)：数据控制语言，用来创建数据库用户、控制数据库的访问权限
4 库的操作
- 库就是仓库，主要存放数据表。
- 库的操作比较简单，主要是以下四种：
4.1 显示库信息
- show databases
- 该命令主要查看数据库中所有的库信息
- eg
-
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)
4.2 创建库
- create database 库名
-
create databases [if not exists] 数据库名 [default charset 字符集] [collate 排序规则]; - eg
-
mysql> create database csdn; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | csdn | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> - 创建成功后，我们再查看，就会看到多了一个csdn的库
4.3 删除库
- drop database 库名
- eg
-
mysql> drop database csdn; Query OK, 0 rows affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> - 删除库后，再查看，刚才创建的库就被删除了
- 删库时一定要谨慎操作，库一旦被删除，库下面所有的数据表也会被删除掉。
4.4 选择库
- use 库名
- 如果要对库下面的表进行操作，就要先选择库
-
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql>
5 表的操作
5.1 数据类型
-
介绍数据表的操作之前先介绍下mysql数据库的数据类型
-
数值型
类型 字节 说明 TINYINT 1 整数型 SAMLLINT 2 整数型 INT 4 整数型 BIGINT 8 整数型 FLOAT 4 浮点型 DOUBLE 8 浮点型 -
字符串型
类型 字节 说明 CHAR 0 ~ 255 定长字符串 VARCHAR 0 ~ 255 不定长字符串 -
时间日期型
类型 字节 范围 格式 说明 DATA 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期型 TIME 3 -838:59:59/838:59:59 HH:MM:SS 时间值或持续时间 YEAR 1 1901/2155 YYYY 年份值 DATETIME 8 1000-01-01 00:00:00/9999-12-21 23:59:59 YYYY-MM-DDHH:MM:SS 日期和时间值
5.2 创建表
-
create table 表名(字段名1 数据类型 约束条件，字段名2 数据类型 约束条件，...) - eg
-
mysql> create database 优快云; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | 优快云 | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use 优快云; Database changed mysql> create table student(id INT PRIMARY KEY,name VARCHAR(30),age INT,score FLOAT); Query OK, 0 rows affected (0.02 sec) - 创建表前，先要选择库，如果没有，可以先创建一个库。表创建好后，可以使用desc命令查看表结构。
5.3 查看表结构
-
describe/desc 表名 - eg
-
mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | age | int | YES | | NULL | | | score | float | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) mysql> - 创建好表后，可以查看表的结构。可以看到表的字段名，对应字段的数据类型以及约束等信息
5.4 删除表
-
drop table 表名
5.5 修改表
5.5.1 修改表名
-
alter table 旧表名 rename 新表名
5.5.2 增加字段
- 在表的最后一个位置增加字段
-
alter table 表名 add 字段名 字段数据类型 - eg
-
mysql> alter table students add class INT; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc students; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | age | int | YES | | NULL | | | score | float | YES | | NULL | | | class | int | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 5 rows in set (0.01 sec) mysql> - 在最后一个位置插入一个class字段
-
- 在表的第一个位置增加字段
-
alter table 表名 add 字段名 字段数据类型 first - eg
-
mysql> alter table students add code INT first; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc students; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | code | int | YES | | NULL | | | id | int | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | age | int | YES | | NULL | | | score | float | YES | | NULL | | | class | int | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> - 将code字段添加到表的第一个位置
-
- 在表的指定位置增加字段
-
alter table 表名 add 字段名 字段数据类型 after 字段名 - eg
-
mysql> alter table students add sex VARCHAR(10) after name; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc students; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | code | int | YES | | NULL | | | id | int | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | sex | varchar(10) | YES | | NULL | | | age | int | YES | | NULL | | | score | float | YES | | NULL | | | class | int | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 7 rows in set (0.01 sec) mysql> - 我们可以在name字段后面加一个sex字段
-
5.5.3 删除字段
-
alter table 表名 drop 字段名
5.5.4 修改字段
- 修改字段的数据类型
-
alter table 表名 modify 字段名 字段新的数据类型 - eg
-
mysql> desc students; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | code | int | YES | | NULL | | | id | int | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | sex | varchar(10) | YES | | NULL | | | age | int | YES | | NULL | | | score | float | YES | | NULL | | | class | int | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 7 rows in set (0.01 sec) mysql> alter table students modify score DOUBLE; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc students; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | code | int | YES | | NULL | | | id | int | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | sex | varchar(10) | YES | | NULL | | | age | int | YES | | NULL | | | score | double | YES | | NULL | | | class | int | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql> - 可以将score字段的数据类型由float改为double
-
- 修改字段名称
-
alter table 表名 change 旧字段名 新字段名 旧字段类型 - eg
-
mysql> desc students; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | code | int | YES | | NULL | | | id | int | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | sex | varchar(10) | YES | | NULL | | | age | int | YES | | NULL | | | score | double | YES | | NULL | | | class | int | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql> alter table students change code lcode INT; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc students; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | lcode | int | YES | | NULL | | | id | int | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | sex | varchar(10) | YES | | NULL | | | age | int | YES | | NULL | | | score | double | YES | | NULL | | | class | int | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql> - 将code字段名称修改为了lcode
-
- 修改字段名称和类型
-
alter table 表名 change 旧字段名 新字段名 新字段类型
-
- 修改字段顺序
- 将字段放到第一个位置
-
alter table 表名 modify 字段名 字段类型 first - 将字段放到指定位置
-
alter table 表名 modify 字段名1 字段类型 after 字段名2
6 数据的操作
- 前面介绍了表的一些操作，接下来就主要介绍下数据的增删改查
6.1 插入数据
- 单条数据插入
-
insert into 表名(字段1, 字段2, 字段3, ...) values(值1, 值2, 值3, ...) - eg
-
mysql> insert into students(id,name,age,score) values(1001,'TOM',22,90.5); Query OK, 1 row affected (0.00 sec) mysql> select * from students; +------+------+------+-------+ | id | name | age | score | +------+------+------+-------+ | 1001 | TOM | 22 | 90.5 | +------+------+------+-------+ 1 row in set (0.00 sec) mysql>
-
- 多条数据插入
-
insert into 表名(字段1, 字段2, 字段3, ...) values(值11, 值21, 值31, ...),(值12, 值22, 值32, ...),... - eg
-
mysql> insert into students(id,name,age,score) values(1002,'JACK',18,80.0),(1003,'LUCY',20,98.5); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from students; +------+------+------+-------+ | id | name | age | score | +------+------+------+-------+ | 1001 | TOM | 22 | 90.5 | | 1002 | JACK | 18 | 80 | | 1003 | LUCY | 20 | 98.5 | +------+------+------+-------+ 3 rows in set (0.00 sec) mysql>
-
6.2 更新数据
- 更新特定数据
-
update 表名 set 字段1=值1, 字段2=值2, ..., where 条件 - eg
-
mysql> select * from students; +------+--------+------+-------+ | id | name | age | score | +------+--------+------+-------+ | 1001 | TOM | 22 | 90.5 | | 1002 | JACK | 18 | 80 | | 1003 | LUCY | 16 | 100 | | 1004 | 曹操 | 18 | 80 | +------+--------+------+-------+ 4 rows in set (0.00 sec) mysql> update students set age=20,score=99.0 where id=1003; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from students; +------+--------+------+-------+ | id | name | age | score | +------+--------+------+-------+ | 1001 | TOM | 22 | 90.5 | | 1002 | JACK | 18 | 80 | | 1003 | LUCY | 20 | 99 | | 1004 | 曹操 | 18 | 80 | +------+--------+------+-------+ 4 rows in set (0.00 sec) mysql>
-
- 更新所有数据
-
update 表名 set 字段1=值1, 字段2=值2, ... - eg
-
mysql> select * from students; +------+--------+------+-------+ | id | name | age | score | +------+--------+------+-------+ | 1001 | TOM | 22 | 90.5 | | 1002 | JACK | 18 | 80 | | 1003 | LUCY | 20 | 99 | | 1004 | 曹操 | 18 | 80 | +------+--------+------+-------+ 4 rows in set (0.00 sec) mysql> update students set age=20,score=80; Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> select * from students; +------+--------+------+-------+ | id | name | age | score | +------+--------+------+-------+ | 1001 | TOM | 20 | 80 | | 1002 | JACK | 20 | 80 | | 1003 | LUCY | 20 | 80 | | 1004 | 曹操 | 20 | 80 | +------+--------+------+-------+ 4 rows in set (0.00 sec) mysql>
-
6.3 删除数据
- 删除指定数据
-
delete from 表名 where 条件 - eg
-
mysql> select * from students; +------+--------+------+-------+ | id | name | age | score | +------+--------+------+-------+ | 1001 | TOM | 20 | 80 | | 1002 | JACK | 20 | 80 | | 1003 | LUCY | 20 | 80 | | 1004 | 曹操 | 20 | 80 | +------+--------+------+-------+ 4 rows in set (0.00 sec) mysql> delete from students where name='曹操'; Query OK, 1 row affected (0.00 sec) mysql> select * from students; +------+------+------+-------+ | id | name | age | score | +------+------+------+-------+ | 1001 | TOM | 20 | 80 | | 1002 | JACK | 20 | 80 | | 1003 | LUCY | 20 | 80 | +------+------+------+-------+ 3 rows in set (0.00 sec) mysql>
-
- 删除所有数据
-
delete from 表名
-
6.4 查询数据
- 我先贴一下原始数据，下面的所有数据查询结果都可以跟原始数据比较下
-
+------+------+------+------+-------+-------+ | id | name | sex | age | score | class | +------+------+------+------+-------+-------+ | 1001 | TOM | boy | 18 | 80 | 1 | | 1002 | JACK | boy | 20 | 85.5 | 1 | | 1003 | LUCY | girl | 20 | 10.5 | 2 | | 1004 | ANDY | girl | 20 | 82.2 | 1 | | 1005 | BOB | boy | 19 | 86.9 | 2 | | 1006 | AMY | girl | 18 | 70.5 | 1 | | 1007 | EMMY | girl | 19 | 99.5 | 2 | | 1008 | DAWN | boy | 21 | 95.5 | 2 | +------+------+------+------+-------+-------+
6.4.1 简单数据查询
- 查询所有字段
-
select * from 表名 - eg
-
mysql> select * from students; +------+------+------+------+-------+-------+ | id | name | sex | age | score | class | +------+------+------+------+-------+-------+ | 1001 | TOM | boy | 18 | 80 | 1 | | 1002 | JACK | boy | 20 | 85.5 | 1 | | 1003 | LUCY | girl | 20 | 10.5 | 2 | | 1004 | ANDY | girl | 20 | 82.2 | 1 | | 1005 | BOB | boy | 19 | 86.9 | 2 | | 1006 | AMY | girl | 18 | 70.5 | 1 | | 1007 | EMMY | girl | 19 | 99.5 | 2 | | 1008 | DAWN | boy | 21 | 95.5 | 2 | +------+------+------+------+-------+-------+ 8 rows in set (0.00 sec) mysql>
-
- 查询指定字段
-
select 字段1,字段2,... from 表名 - 比如我们只查询id，姓名和班级
-
mysql> select id,name,class from students; +------+------+-------+ | id | name | class | +------+------+-------+ | 1001 | TOM | 1 | | 1002 | JACK | 1 | | 1003 | LUCY | 2 | | 1004 | ANDY | 1 | | 1005 | BOB | 2 | | 1006 | AMY | 1 | | 1007 | EMMY | 2 | | 1008 | DAWN | 2 | +------+------+-------+ 8 rows in set (0.00 sec) mysql>
-
- 避免重复数据查询
-
select distinct 字段1,字段2,... from 表名 - 比如数据表中学生数据可能有上千条，但班级可能就几十个，我们想查询都有哪些班级。就可以去掉重复信息查询。
-
mysql> select distinct class from students; +-------+ | class | +-------+ | 1 | | 2 | +-------+ 2 rows in set (0.00 sec) mysql>
-
- 实现数字四则运算数据查询
-
select 字段1 运算符 运算符操作数, 字段2 运算符 运算符操作数, ... from 表名 - 运算符包括：+ - * / %
-
mysql> select id + 1, name from students; +--------+------+ | id + 1 | name | +--------+------+ | 1002 | TOM | | 1003 | JACK | | 1004 | LUCY | | 1005 | ANDY | | 1006 | BOB | | 1007 | AMY | | 1008 | EMMY | | 1009 | DAWN | +--------+------+ 8 rows in set (0.00 sec) mysql>
-
- 给字段起别名
-
select 字段1 as 字符串, 字段2 as 字符串, ... from 表名 - eg
-
mysql> select name as '姓名', sex as '性别', age as '年龄', score as '分数', class as '班级' from students; +--------+--------+--------+--------+--------+ | 姓名 | 性别 | 年龄 | 分数 | 班级 | +--------+--------+--------+--------+--------+ | TOM | boy | 18 | 80 | 1 | | JACK | boy | 20 | 85.5 | 1 | | LUCY | girl | 20 | 10.5 | 2 | | ANDY | girl | 20 | 82.2 | 1 | | BOB | boy | 19 | 86.9 | 2 | | AMY | girl | 18 | 70.5 | 1 | | EMMY | girl | 19 | 99.5 | 2 | | DAWN | boy | 21 | 95.5 | 2 | +--------+--------+--------+--------+--------+ 8 rows in set (0.00 sec) mysql>
-
6.4.2 条件数据查询
- 运算符：分为关系运算符和逻辑运算符
- 关系运算符
>：大于>=：大于等于<：小于<=：小于等于=：等于!=：不等于between ... and ...：在某个范围之内（含最小最大值）in( ... )：在列表中的值link 占位符：模糊匹配（_匹配单个字符，%匹配任意个字符）is NULL：判断是否为NULL
- 逻辑运算符
and或&&：多个条件同时成立or或||：多个条件任意一个成立not或!：非
- 单条件数据查询
-
select 字段 from 表名 where 条件 - 比如我们只查询1班的学生信息
-
mysql> select * from students where class=1; +------+------+------+------+-------+-------+ | id | name | sex | age | score | class | +------+------+------+------+-------+-------+ | 1001 | TOM | boy | 18 | 80 | 1 | | 1002 | JACK | boy | 20 | 85.5 | 1 | | 1004 | ANDY | girl | 20 | 82.2 | 1 | | 1006 | AMY | girl | 18 | 70.5 | 1 | +------+------+------+------+-------+-------+ 4 rows in set (0.00 sec) mysql>
-
- 多条件数据查询
-
select 字段 from 表名 where 条件1 and 条件2 - 比如我们查询1班女生的学生信息
-
mysql> select * from students where class=1 and sex='girl'; +------+------+------+------+-------+-------+ | id | name | sex | age | score | class | +------+------+------+------+-------+-------+ | 1004 | ANDY | girl | 20 | 82.2 | 1 | | 1006 | AMY | girl | 18 | 70.5 | 1 | +------+------+------+------+-------+-------+ 2 rows in set (0.00 sec) mysql>
-
- 符合范围的数据查询
-
select 字段 from 表名 where 字段名 between 值1 and 值2 - 比如我们要查询分数在80到90区间段的学生信息
-
mysql> select * from students where score between 80 and 90; +------+------+------+------+-------+-------+ | id | name | sex | age | score | class | +------+------+------+------+-------+-------+ | 1001 | TOM | boy | 18 | 80 | 1 | | 1002 | JACK | boy | 20 | 85.5 | 1 | | 1004 | ANDY | girl | 20 | 82.2 | 1 | | 1005 | BOB | boy | 19 | 86.9 | 2 | +------+------+------+------+-------+-------+ 4 rows in set (0.00 sec) mysql>
-
- 不符合范围的数据查询
-
select 字段 from 表名 where 字段名 not between 值1 and 值2 - 我们要查询分数不在80到90区间段的学生信息
-
mysql> select * from students where score not between 80 and 90; +------+------+------+------+-------+-------+ | id | name | sex | age | score | class | +------+------+------+------+-------+-------+ | 1003 | LUCY | girl | 20 | 10.5 | 2 | | 1006 | AMY | girl | 18 | 70.5 | 1 | | 1007 | EMMY | girl | 19 | 99.5 | 2 | | 1008 | DAWN | boy | 21 | 95.5 | 2 | +------+------+------+------+-------+-------+ 4 rows in set (0.00 sec) mysql>
-
- 空值查询
-
select 字段 from 表名 where 字段名 is null - 我们查询下字段为空的数据。因为数据里没有空值，所以查询结果为空。
-
mysql> select * from students where id is null; Empty set (0.00 sec)
-
- 非空值查询
-
select 字段 from 表名 where 字段名 is not null -
mysql> select * from students where id is not null; +------+------+------+------+-------+-------+ | id | name | sex | age | score | class | +------+------+------+------+-------+-------+ | 1001 | TOM | boy | 18 | 80 | 1 | | 1002 | JACK | boy | 20 | 85.5 | 1 | | 1003 | LUCY | girl | 20 | 10.5 | 2 | | 1004 | ANDY | girl | 20 | 82.2 | 1 | | 1005 | BOB | boy | 19 | 86.9 | 2 | | 1006 | AMY | girl | 18 | 70.5 | 1 | | 1007 | EMMY | girl | 19 | 99.5 | 2 | | 1008 | DAWN | boy | 21 | 95.5 | 2 | +------+------+------+------+-------+-------+ 8 rows in set (0.00 sec) mysql>
-
- 带in关键字的集合查询
-
select 字段 from 表名 where 字段名 in(值1, 值2, ...) - 我们只查询TOM和JACK的信息
-
mysql> select * from students where name in('TOM','JACK'); +------+------+------+------+-------+-------+ | id | name | sex | age | score | class | +------+------+------+------+-------+-------+ | 1001 | TOM | boy | 18 | 80 | 1 | | 1002 | JACK | boy | 20 | 85.5 | 1 | +------+------+------+------+-------+-------+ 2 rows in set (0.00 sec) mysql>
-
- 不在集合中的数据查询
-
select 字段 from 表名 where 字段名 not in(值1, 值2, ...) - 查询除了TOM和JACK的其他学生信息
-
mysql> select * from students where name not in('TOM','JACK'); +------+------+------+------+-------+-------+ | id | name | sex | age | score | class | +------+------+------+------+-------+-------+ | 1003 | LUCY | girl | 20 | 10.5 | 2 | | 1004 | ANDY | girl | 20 | 82.2 | 1 | | 1005 | BOB | boy | 19 | 86.9 | 2 | | 1006 | AMY | girl | 18 | 70.5 | 1 | | 1007 | EMMY | girl | 19 | 99.5 | 2 | | 1008 | DAWN | boy | 21 | 95.5 | 2 | +------+------+------+------+-------+-------+ 6 rows in set (0.00 sec) mysql>
-
- 带like关键字的模糊查询
-
select 字段 from 表名 where 字段名 like 值 - “%” 通配任意多字符，"_"通配一个字符
- 我们查询下姓名以字母A开头的学生信息和姓名以字母A开头且只有3个字母组成的学生信息
-
mysql> select * from students where name like 'A%'; +------+------+------+------+-------+-------+ | id | name | sex | age | score | class | +------+------+------+------+-------+-------+ | 1004 | ANDY | girl | 20 | 82.2 | 1 | | 1006 | AMY | girl | 18 | 70.5 | 1 | +------+------+------+------+-------+-------+ 2 rows in set (0.00 sec) mysql> select * from students where name like 'A__'; +------+------+------+------+-------+-------+ | id | name | sex | age | score | class | +------+------+------+------+-------+-------+ | 1006 | AMY | girl | 18 | 70.5 | 1 | +------+------+------+------+-------+-------+ 1 row in set (0.00 sec) mysql>
-
6.4.3 排序查询
-
select 字段名 from 表名 order by 字段名 asc/desc - asc : 升序， desc : 降序
- 以成绩降序查询学生信息
-
mysql> select * from students order by score desc; +------+------+------+------+-------+-------+ | id | name | sex | age | score | class | +------+------+------+------+-------+-------+ | 1007 | EMMY | girl | 19 | 99.5 | 2 | | 1008 | DAWN | boy | 21 | 95.5 | 2 | | 1005 | BOB | boy | 19 | 86.9 | 2 | | 1002 | JACK | boy | 20 | 85.5 | 1 | | 1004 | ANDY | girl | 20 | 82.2 | 1 | | 1001 | TOM | boy | 18 | 80 | 1 | | 1006 | AMY | girl | 18 | 70.5 | 1 | | 1003 | LUCY | girl | 20 | 10.5 | 2 | +------+------+------+------+-------+-------+ 8 rows in set (0.00 sec) mysql>
6.4.4 分组查询
-
select 字段名 from 表名 group by 字段名 - 分组后再进行条件过滤
-
select 字段名 from 表名 [where 条件] group by 字段名 having 条件 - 这里使用到了
having，和where有什么区别- 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
- 判断条件不同：where不能对聚合函数进行判断，而having可以。
-
mysql> select class from students group by class; +-------+ | class | +-------+ | 1 | | 2 | +-------+ 2 rows in set (0.00 sec) mysql> - 是不是感觉分组查询没啥用？别着急，分组是为了统计，所以一般是和统计函数一起使用的
- 统计函数：也称为聚合函数，将一列数据作为一个整体，进行纵向计算。
count：统计表中记录的条数avg：统计计算字段的平均值sum：统计字段的总和max：查询字段的最大值min：查询字段的最小值
- 比如我们统计1班和2班分别有多少人
-
mysql> select class,count(class) from students group by class; +-------+--------------+ | class | count(class) | +-------+--------------+ | 1 | 4 | | 2 | 4 | +-------+--------------+ 2 rows in set (0.00 sec) mysql> - 统计1班和2班的平均分
-
mysql> select class,avg(score) from students group by class; +-------+------------+ | class | avg(score) | +-------+------------+ | 1 | 79.55 | | 2 | 73.1 | +-------+------------+ 2 rows in set (0.00 sec) mysql> - 只统计1班平均分
-
mysql> select class,avg(score) from students group by class having class=1; +-------+------------+ | class | avg(score) | +-------+------------+ | 1 | 79.55 | +-------+------------+ 1 row in set (0.00 sec) mysql>
7 数据库管理
7.1 用户管理
-
查询用户
-
use mysql select * from user; 
-
-
创建用户
-
# 如果只允许用户在本机访问数据库，设置主机名为 localhost； # 如果允许用户在任意主机访问数据库，设置主机名为%，表示通配任意主机。 create user '用户名'@'主机名' identified by '密码'; 
-
-
修改用户密码
-
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码'
-
-
删除用户
-
drop user '用户名'@'主机名';
-
7.2 权限控制
- MySQL定义了以下几种权限
ALL,ALL PRIVILEGES：所有权限SELECT：查询数据INSERT：插入数据UPDATE：修改数据DELETE：删除数据ALTER：修改数据DROP：删除数据库/表/视图CREATE：创建数据库/表
- 查询权限
-
SHOW GRANTS FOR '用户名'@'主机名';
-
- 授予权限
-
# 如果要给所有数据库和表赋予权限，直接写*.* GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
-
- 撤销权限
-
REVOKE 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
-
8 函数
8.1 字符串函数
- MySQL中内置的字符串函数，常用的如下
concat(s1, s2, ... sn)：字符串拼接，将s1,s2,…sn拼接成一个字符串lower(str)：将字符串str全部转为小写upper(str)：将字符串str全部转为大写lpad(str,n,pad)：左填充，用字符串pad对str的左边进行填充，达到n个字符串长度rpad(str,n,pad)：右填充，用字符串pad对str的右边进行填充，达到n个字符串长度trim(str)：去掉字符串头部和尾部的空格substring(str,start,len)：返回字符串str从start位置起的len个长度的字符串
- 示例
8.2 数值函数
-
常见的数值函数如下
ceil(x)：向上取整floor(x)：向下取整mod(x,y)：返回x/y的模rand()：返回0~1内的随机数round(x,y)：求参数x的四舍五入的值，保留y位小数
-
示例
8.4 日期函数
- 常见日期函数如下
curdate()：返回当前日期curtime()：返回当前时间now()：返回当前日期和时间year(date)：返回执行date的年份month(date)：返回指定date的月份day(date)：返回指定date的日期date_add(date, interval expr type)：返回一个日期/时间值加上一个时间间隔expr后的时间值datediff(date1, data2)：返回起始时间data1和结束时间data2之间的天数
- 示例
8.4 流程函数
- 在SQL语句中实现条件筛选，从而提高语句的效率
if(value, t, f)：如果value为true，返回t，否则返回fifnull(value1, value2)：如果value1不为空，返回value1，否则返回value2case when [val1] then [res1] ... else [default] end：如果val1为true，返回res1，… 否则返回default默认值case [expr] when [val1] then [res1] ... else [default] end：如果expr的值等于value1，返回res1，… 否则返回default默认值
- 示例
9 约束设置
-
约束是对插入数据库中的数据进行限定，这么做的目的是为了保证数据的有效性和完整性。
约束关键字 含义 NOT NULL 约束字段值不能为空 DEFAULT 设置字段默认值 UNIQUE KEY 设置字段的值是唯一的 PRIMARY KEY 设置字段为表的主键 FOREIGN KEY 设置字段为表的外键 CHECK 检查约束（8.0.16版本之后）
9.1 基础约束
- 根据以下SQL语句，创建一张
student表。-
# 创建表 create table student(id int primary key auto_increment, name varchar(30) not null unique, age int check(age > 0 && age <= 150), status char(1) default '1', gender char(1), course_id int) comment '学生表'; # 插入数据 insert into student(name, age, status, gender,course_id) values('Tom', 19, '1', '男', 2), ('Lucy', '20', '0', '女', 1), ('Jack', '22', '0', '男', 2);
-
- 示例
9.2 外键约束
- 外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
- 具有外键的表称为子表，外键关联的表称为父表。
- 添加外键
-
# 创建表时添加 create table 表名(字段名 数据类型, [constraint][外键名称] foreing key(外键字段名) references 主表(主表列名)); # 新增外键约束 alter table 表名 add constraint 外键名称 foreing key(外键字段名) references 主表(主表列名); # 删除外键 alter table 表名 drop foreing key 外键名称;
-
- 执行以下语句，创建一张父表，课程表
-
create table course(id int primary key, name varchar(30) not null unique, teacher varchar(30) not null); insert into course(id, name, teacher) values(1, '语文', '张老师'),(2, '数学', '李老师'),(3, '英语', '王老师');
-
- 给上述学生表添加一个
course_id字段，并设置外键。-
alter table student add constraint fk_course_id foreign key(course_id) references course(id);
-
- 演示
- 这个时候如果删除
course表中id=2的数据，就会报错，无法删除。这样就可以保证数据的完整性。
9.3 外键删除/更新行为
NOT ACTION：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。外键约束的默认行为。RESTRICT：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。CASCADE：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。SET NULL：当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有，则设置子表中该外键值为null（这就要求该外键允许设置null）。SET DEFAULT：父表有变更时，子表将外键列设置成一个默认的值（InnoDB不支持）。- 语法如下
-
# 设置更新和删除记录时，行为为cascade alter table 表名 add constraint 外键名称 foreing key(外键字段) references 主表(主表列名) on update cascade on delete cascade;
-
10 系统数据库
- MySQL数据库安装后，自带了以下四个数据库
- mysql：存储MySQL服务器正常运行所需要的各种信息（时区、主从、用户、权限等）。
- information_schema：提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等。
- performance_schema：为MySQL服务器运行状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数。
- sys：包含了一系列方便DBA和开发人员利用performance_schema性能诗剧苦进行性能调优和诊断的视图。
- 图示
11 常用工具
11.1 mysql
- 指mysql的客户端工具
- 语法
-
mysql [options] [database] [options] -u, --user=name # 指定用户名 -p, --password[=name] # 指定密码 -h, --host=name # 指定服务器ip或域名 -P, --port=port # 指定连接端口 -e, --execute=name #执行SQL语句并退出
-
- 示例
11.2 mysqladmin
- 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。
- 语法
-
mysqladmin [OPTIONS] command command....
-
- 示例
11.3 mysqlbinlog
- 由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog日志管理工具。
- 语法
-
mysqlbinlog [options] log-files1 log-file2 ... [options] -d, --database=name # 指定数据库名称，只列出指定的数据库相关操作 -o, --offset=# # 忽略掉日志中的前n行命令 -r, --result-file=name # 将输出的文本格式日志输出到指定文件 -s, --short-form # 显示简单格式，省略掉一些信息 --start-datetime- date1 --stop-datetime=date2 # 指定日期间隔内的所有日志 --start-position=pos1 --stop-position=pos2 # 指定位置间隔内的所有日志
-
- Linux系统下的MySQL日志默认存储在
/var/lib/mysql目录下
11.4 mysqlshow
- 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。
- 语法
-
mysqlshow [options] [ [db_name] [table_name] [col_name] ] [options] --count # 显示数据库及表的统计信息 -i # 显示指定数据库或者指定表的状态信息
-
- 示例
11.5 mysqldump
- mysqldump用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。
- 语法
-
# 备份指定数据库 mysqldump [options] db_name [tables] mysqldump [options] --databases/-B db1 [db2 db3 ...] # 备份所有数据库 mysqldump [options] --all-databases/-A [options] --add-drop-database # 在每个数据库创建语句前加上drop database语句 --add-drop-table # 在每个表创建语句前加上drop table语句，默认开启。不开启（--skip-add-drop-table） -n, --no-create-db # 不包含数据库的创建语句 -t, --no-create-info # 不包含数据表的创建语句 -d, --no-data # 不包含数据 -T, --tab=name # 自动生成两个文件：一个.sql文件，创建表结构的语句，一个.txt文件，数据文件
-
- 示例
11.6 mysqlimport/source
- mysqlimport是客户端导入工具，用来导入mysqldump加-T参数导出的文本文件。
- source用来导入sql文件
- 语法
-
mysqlimport [options] db_name textfile1 [textfile2...] # 在数据库中直接执行 source xxx.sql
-
- 示例
- 删除表中数据
- 导入数据
- 导入成功
扫一扫
2456
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
