MySQL数据库基础知识

目录

一.MySQL简介

二.MySQL的安装

1.yum安装5.7版本

2.编译安装mysql

3. 二进制安装

三.安装mycli插件 客户端工具

四.基本操作

1.SQL分类

2.SQL语言规范

3.数据库的对象和命名

4.SQL语言分类

5.查看帮助信息

6.查看支持的字符集

7.管理数据库

7.1  创建数据库

7.2  修改数据库

7.3  删除数据库

7.4  数据库类型

7.5 CHAR与VARCHAR区别

7.6 修饰符

8.表操作

8.1  查看数据库结构

8.2  新建表

（1）显示当前数据库下所有的表格

（2）查看表的结构

（3）添加表的字段

（4）在表中插入数据

9.DML语言

9.1   INSERT语句

9.2  UPDATE 语句

9.3  DELETE 语句

10. DOL 语句 单表查询

10.1  select  显示表格中一个或者多个字段的所有数据记录

10.2 WHERE  过滤查询

10.3 AND ---> 且   OR ---> 或

10.4 DISTINCT  去除重复行

10.5  like 模糊查询与通配符

10.6  数学函数

10.7  聚合函数

（1）avg  平均值

（2）count  返回指定列中非 NULL 值的个数

（3）min 最小值

（4）max  最大值

（5）sum 求和

10.8 字符串函数

（1）concat 

（2）substr  

10.9 GROUP BY 分组

10.10  排序  order  by

10.11  limit  输出行数数量限制

10.12  having  筛选

五.练习

---

一.MySQL简介

MySQL是一个开源的关系型数据库管理系统（RDBMS），由瑞典MySQL AB公司开发，后被Sun Microsystems收购，再后被Oracle收购。它是目前最流行的数据库管理系统之一，特别是在Web应用程序开发中被广泛使用。

主要特点和功能：

1. 开源和免费：

   • MySQL是开源软件，采用GPL（GNU General Public License）许可证，可以免费使用和修改。这使得它在开发者社区中得到广泛的支持和贡献。

2. 跨平台：

   • MySQL支持多种操作系统，包括Windows、Linux、macOS等，可以在不同平台上运行和部署。

3. 关系型数据库管理系统：

   • MySQL是一种关系型数据库管理系统，采用SQL（Structured Query Language）作为查询语言。它支持标准的SQL语法，并提供了丰富的SQL功能。

4. 高性能：

   • MySQL以其高效的性能而闻名，可以处理大量数据和高并发访问。它采用了多种优化策略和存储引擎，如InnoDB和MyISAM，以提供高效的数据存储和访问能力。

5. 灵活性：

   • MySQL支持多种存储引擎（Storage Engines），每种存储引擎有其独特的特性和适用场景。常见的存储引擎包括InnoDB、MyISAM、Memory等，可以根据具体需求选择合适的引擎。

6. 扩展性：

   • MySQL具有良好的扩展性，支持主从复制（Replication）、分区（Partitioning）、分布式数据库等特性，可以实现数据的水平和垂直扩展。

7. 安全性：

   • MySQL提供了多层次的安全性控制，包括密码加密、用户权限管理、SSL支持等，可以保护数据库的安全性和数据的完整性。

8. 广泛的应用场景：

   • MySQL被广泛应用于Web开发中，作为后端数据库存储数据。许多流行的Web应用程序和网站，如WordPress、Facebook、Twitter等，都使用MySQL作为其关键数据存储系统。

二.MySQL的安装

1.yum安装5.7版本

（1）安装mariadb服务，开启服务，初始化设置

[root@localhost ~]#yum install mariadb-server -y
[root@localhost ~]#systemctl start mariadb.service
[root@localhost ~]#mysql_secure_installation 
#初始化设置  先输入密码 一路回车     

（2）搭建yum仓库

[root@centos7 ~]#tee /etc/yum.repos.d/mysql.repo <<EOF
[mysql]
name=mysql5.7
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
gpgcheck=0
EOF

清华大学源      没有初始密码 5.7.41

[root@centos7 ~]#tee /etc/yum.repos.d/mysql.repo <<EOF
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/
enabled=1
gpgcheck=0
EOF

官方源   5.7.3.

（3）安装mysql服务，并开启服务

[root@centos7 ~]#yum -y install mysql-community-server
[root@localhost yum.repos.d]# systemctl start mysqld
[root@localhost yum.repos.d]# ss -ntap |grep 3306

（4）登录mysql

[root@localhost yum.repos.d]# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@centos7 ~]#grep password /var/log/mysqld.log   #官方源直接登录过滤出密码，一串特殊符号
[root@localhost yum.repos.d]# mysql -u root -p'+8saq%5+ljzG'
#特殊符号用单引号引起来

2.编译安装mysql

安装脚本

---
- hosts:  web
  gather_facts:  no


  tasks: 
    - name:  install  packages
      yum: 
        name:  
          - gcc
          - gcc-c++ 
          - cmake 
          - bison
          - zlib-devel
          - bison-devel
          - libcurl-devel
          - ncurses-devel
          - libarchive-devel
          - gnutls-devel
          - libaio-devel
          - libevent-devel
          - libxml2-devel
          - openssl  
          - openssl-devel
        state:  present
    - name: user
      command: useradd -M -s /sbin/nologin  mysql
    - name:  unarchive
      unarchive: src=/opt/mysql-boost-5.7.20.tar.gz  dest=/opt
    - name:  bianyi
      command: cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock -DSYSCONFDIR=/etc -DSYSTEMD_PID_DIR=/usr/local/mysql -DDEFAULT_CHARSET=utf8  -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DMYSQL_DATADIR=/usr/local/mysql/data -DWITH_BOOST=boost -DWITH_SYSTEMD=1
      args:
        chdir: /opt/mysql-5.7.20

    - name:   make
      command: make -j2
      args:
        chdir: /opt/mysql-5.7.20
    
    - name:   make  install
      command: make  install
      args:
        chdir: /opt/mysql-5.7.20
    - name: copy peizhi
      shell: |
             cat > /etc/my.cnf <<EOF
             [client]
             port = 3306
             socket=/usr/local/mysql/mysql.sock

             [mysqld]
             user = mysql
             basedir=/usr/local/mysql
             datadir=/usr/local/mysql/data
             port = 3306
             character-set-server=utf8
             pid-file = /usr/local/mysql/mysqld.pid
             socket=/usr/local/mysql/mysql.sock
             bind-address = 0.0.0.0
             skip-name-resolve
             max_connections=2048
             default-storage-engine=INNODB
             max_allowed_packet=16M
             server-id = 1

             sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
             EOF

    - name: chown
      shell: chown -R mysql:mysql /usr/local/mysql/
    - name: chown my.cnf
      shell: chown mysql:mysql /etc/my.cnf
    - name: echo
      shell: echo 'export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
    - name: source  
      shell: source /etc/profile
    - name: chushihua
      command: ./mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
      args:
        chdir: /usr/local/mysql/bin/
    - name: tianjiafuwu
      command: cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
    - name: reload
      command: systemctl  daemon-reload
    - name: start
      command: systemctl start mysqld.service
    - name: enable
      command: systemctl enable mysqld

#############模块解释#####################
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
#指定mysql的安装路径
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
#指定mysql进程监听套接字文件（数据库连接文件）的存储路径
-DSYSCONFDIR=/etc \
#指定配置文件的存储路径
-DSYSTEMD_PID_DIR=/usr/local/mysql \
#指定进程文件的存储路径
-DDEFAULT_CHARSET=utf8  \
#指定默认使用的字符集编码，如utf8
-DDEFAULT_COLLATION=utf8_general_ci \
#指定默认使用的字符集校对规则
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
#安装INNOBASE存储引擎
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
#安装ARCHIVE存储引擎
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
#安装BLACKHOLE存储引擎
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
#安装FEDERATED存储引擎
-DMYSQL_DATADIR=/usr/local/mysql/data \
#指定数据库文件的存储路径
-DWITH_BOOST=boost \
#指定boost的路径，
-DWITH_SYSTEMD=1
#生成便于systemctl管理的文件
#############模块解释#####################

设置mysql密码

mysqladmin -u root -p"password"
#开始初始密码为空  回车即可   然后输入新密码

登录数据库

mysql -u root -p 

3. 二进制安装

（1）准备用户

groupadd -r -g 306 mysql
useradd -r -g 306 -u 306 -d /data/mysql mysql

（2）准备数据目录，建议使用逻辑卷

#可选做，后面的脚本mysql_install_db可自动生成此目录
mkdir /data/mysql
chown mysql:mysql /data/mysq

（3）准备二进制程序

tar xf mysql-VERSION-linux-x86_64.tar.gz -C /usr/local
cd /usr/local
ln -sv mysql-VERSION mysql
chown -R root:root /usr/local/mysql/

（4）准备配置文件

cd /usr/local/mysql
cp -b support-files/my-default.cnf   /etc/my.cnf
vim /etc/my.cnf
#mysql语句块中添加以下三个选项
[mysqld]
datadir = /data/mysql
innodb_file_per_table = on  #在mariadb5.5以上版的是默认值，可不加
skip_name_resolve = on      #禁止主机名解析，建议使用 可选项

（5）创建数据库文件

cd /usr/local/mysql/
./scripts/mysql_install_db --datadir=/data/mysql --user=mysql
ll /data/mysql/

（6）准备服务脚本，并启动服务

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start

#如果有对应的service 文件可以执行下面
cp /usr/local/mysql/support-files/systemd/mariadb.service 
/usr/lib/systemd/system/
systemctl daemon-reload
systemctl enable --now mariadb

（7）PATH路径

echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh

（8）安全初始化

/usr/local/mysql/bin/mysql_secure_installation

（9）优化操作

#修改提示符

[root@localhost ~]#vim /etc/my.cnf                       
[mysql]
prompt=(\\u@\\h) [\\d]>\\_
auto-rehash
#自动补全 只能补全敲过的命令

三.安装mycli插件 客户端工具

1.安装环境

yum -y install zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel tk-devel gdbm-devel db4-devel libpcap-devel xz-devel libffi-devel

2.将pip软件包拖至/opt目录下并解压包

[root@localhost opt]#tar zxvf Python-3.7.7_.tgz

3.切换到编译安装目录下，进行编译安装

[root@localhost Python-3.7.7]#cd Python-3.7.7/
[root@localhost Python-3.7.7]#./configure --prefix=/usr/local/Python-3.7.7/
[root@localhost Python-3.7.7]#make  -j2  &&   make install

4.制作软链接

[root@localhost Python-3.7.7]#ln -s  /usr/local/Python-3.7.7/bin/python3.7  /usr/bin/python37
[root@localhost Python-3.7.7]#ln -s /usr/local/Python-3.7.7/bin/pip3.7 /usr/bin/pip37

5.升级pip软件

[root@localhost Python-3.7.7]#pip37 install --upgrade pip -i http://mirrors.aliyun.com/pypi/simple --trusted-host mirrors.aliyun.com

6.安装mycli服务

[root@localhost Python-3.7.7]#pip37 install mycli -i http://mirrors.aliyun.com/pypi/simple --trusted-host mirrors.aliyun.com

7.制作软链接

[root@localhost Python-3.7.7]#ln -s /usr/local/Python-3.7.7/bin/mycli /usr/bin/mycli

8.登录mycli服务，优化服务（需重启生效）

[root@localhost Python-3.7.7]#mycli -u root -p passwd（密码）

[root@localhost ~]#vim /etc/my.cnf                       
[mysql]
prompt=(\\u@\\h) [\\d]>\\_
auto-rehash

四.基本操作

1.SQL分类

• 数据库：database

• 表：table，行：row 列：column

• 索引：index

• 视图：view

• 存储过程：procedure

• 存储函数：function

• 触发器：trigger

• 事件调度器：event scheduler，任务计划

• 用户：user

• 权限：privilege

2.SQL语言规范

• 在数据库系统中，SQL 语句不区分大小写，建议用大写

• SQL语句可单行或多行书写，默认以 " ; " 结尾

• 关键词不能跨多行或简写

• 用空格和TAB 缩进来提高语句的可读性

• 子句通常位于独立行，便于编辑，提高可读性

3.数据库的对象和命名

数据库的组件（对象）:

数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

数据库的命名规则：

必须以字母开头，后续可以包括字母,数字和三个特殊字符（# _ $）

不要使用MySQL的保留字,tabble select show databases

4.SQL语言分类

• DDL: Data Defination Language 数据定义语言

CREATE，DROP，ALTER

• DML: Data Manipulation Language 数据操纵语言

INSERT，DELETE，UPDATE

软件开发：CRUD

• DQL：Data Query Language 数据查询语言

SELECT

• DCL：Data Control Language 数据控制语言

GRANT，REVOKE

• TCL：Transaction Control Language 事务控制语言

COMMIT，ROLLBACK，SAVEPOINT

5.查看帮助信息

mysql> help create
#help 后面跟上具体命令可以查看帮助

6.查看支持的字符集

show charset;
#查看支持字符集
默认拉丁文字

utf8     | UTF-8 Unicode   
#阉割版的

utf8mb4  | UTF-8 Unicode 
#真实的版本

修改字符集
vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4

show create database test;

7.管理数据库

7.1  创建数据库

mysql> show variables like 'char%';
#查看当前默认字符

建立数据库cxk并查看数据库

指定数据库字符集创建数据库

7.2  修改数据库

当建立好数据库后可以使用 ALTER 命令修改

ALTER DATABASE DB_NAME character set utf8;
#修改字符集
[root@node2 cxk1]#cat /usr/local/mysql/data/cxk1/db.opt 
#查看当前字符集   yum  安装可以用find 查找同名的数据库
default-character-set=utf8
default-collation=utf8_general_ci

示例：

修改名为 cxk1 的数据库，将其字符集（character set）设置为 utf8，并且校对规则（collation）设置为 utf8_bin。

• Character Set（字符集）：决定了数据库中存储的文本数据的编码方式。在这里，utf8 表示使用 UTF-8 编码，它支持各种语言的字符集，是较为通用和推荐的选择。

• Collation（校对规则）：定义了字符如何进行比较和排序的规则。在 utf8_bin 中，utf8 表示字符集为 UTF-8，而 bin 表示使用二进制排序，这意味着比较时对字符进行二进制比较，区分大小写。

7.3  删除数据库

格式：

DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';

MySQL root@localhost:(none)> drop database db1;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 0 rows affected
Time: 0.005s
MySQL root@localhost:(none)> show databases;

[root@localhost mysql]#cd /home/mysql/
#数据库其实 就是一个文件夹，删除数据库的文件夹等于删除数据库

7.4  数据库类型

MySQL支持多种内置数据类型

数值类型

日期/时间类型

字符串(字符)类型

选择正确的数据类型对于获得高性能至关重要，三大原则：

1. 更小的通常更好，尽量使用可正确存储数据的最小数据类型

2. 简单就好，简单数据类型的操作通常需要更少的CPU周期

3. 尽量避免NULL，包含为NULL的列，对MySQL更难优化

1.整数型

• tinyint(m) 1个字节 范围(-128~127) 有一个正或负的表示符

• smallint(m) 2个字节 范围(-32768~32767)

• mediumint(m) 3个字节 范围(-8388608~8388607)

• int(m) 4个字节 范围(-2147483648~2147483647)

• bigint(m) 8个字节 范围(+-9.22*10的18次方)

上述数据类型，如果加修饰符unsigned后，则最大值翻倍

如：tinyint unsigned的取值范围为(0~255)

2.浮点型(float和double)，近似值

float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位, 注意: 小数点不占用总个数

double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位, 注意: 小数点不占用总个数

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位

3.定点数

在数据库中存放的是精确值,存为十进制 decimal(5.2) 100.01 - 999.99

格式 decimal(m,d) 表示 最多 m 位数字,其中 d 个小数,小数点不算在长度内

比如: DECIMAL(6,2) 总共能存6位数字，末尾2位是小数，字段最大值 9999.99 （小数点不算在长度内）

参数m<65 是总个数，d<30且 d<m 是小数位

MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。

例如: decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个

字节，小数点后的9个数字用4个字节，小数点本身占1个字节

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用

8个字节

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时

4.字符串

char(n) 固定长度，最多255个字符,注意不是字节

varchar(n) 可变长度，最多65535个字符

tinytext 可变长度，最多255个字符

text 可变长度，最多65535个字符

mediumtext 可变长度，最多2的24次方-1个字符

longtext 可变长度，最多2的32次方-1个字符

BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节

VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节

内建类型：ENUM枚举, SET集合

7.5 CHAR与VARCHAR区别

在MySQL数据库中，CHAR和VARCHAR是用来存储字符数据的两种主要类型。它们有一些区别，主要涉及到存储方式和适用场景：

CHAR

1. 固定长度存储：

   • CHAR用于存储固定长度的字符串。你必须指定一个固定的长度，在创建表时就确定了这个长度，存储时不管实际字符串的长度，都会占用指定长度的存储空间。

2. 适用场景：

   • 当你存储的字符串长度基本是固定的时候，可以使用CHAR，这样可以节省一些存储空间和访问时间。
   • 例如，存储邮政编码、国家代码或者固定长度的代码等。

3. 存储方式：

   • CHAR类型的存储方式是右填充空格到指定长度。比如，如果定义一个CHAR(10)，而实际存储的字符串长度是5，那么MySQL会在字符串后面填充5个空格。

VARCHAR

1. 可变长度存储：

   • VARCHAR用于存储可变长度的字符串。你需要指定一个最大长度，实际存储的字符串长度可以不超过这个最大长度，但是存储实际长度，不会占用固定的空间。

2. 适用场景：

   • 当你存储的字符串长度不固定，或者经常变化时，应该使用VARCHAR。这样可以灵活地根据实际需要分配存储空间，节省空间和优化性能。
   • 例如，存储用户的姓名、地址或者评论内容等。

3. 存储方式：

   • VARCHAR类型的存储方式是实际存储的字符串长度加上一个字节来记录字符串的长度。这个额外的字节用于存储实际字符串的长度信息，所以存储的空间会随着实际数据的长度而变化。

总结

• 使用CHAR当你的数据长度是固定的，这样可以节省空间。
• 使用VARCHAR当你的数据长度是可变的，或者长度不确定时，这样可以灵活分配存储空间。
• 在设计数据库表时，根据具体的数据特点和存储需求选择合适的类型，可以有效地优化存储空间和提升数据库性能

7.6 修饰符

适用所有类型的修饰符：

名称	含义
NULL	数据列可包含NULL值，默认值
NOT NULL	数据列不允许包含NULL值，*为必填选项
DEFAULT	默认值
PRIMARY KEY	主键，所有记录中此字段的值不能重复，且不能为NULL
UNIQUE KEY	唯一键，所有记录中此字段的值不能重复，但可以为NULL
CHARACTER SET	name 指定一个字符集

适用数值型的修饰符：

名称	作用
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号

8.表操作

8.1  查看数据库结构

#查看数据库
show create database cxk1;

#选择数据库，切换库类似于cd
show tables;
show tables in mysql;

select database();  #查看当前在哪个数据库下

####查看表结构(字段)####
格式：describe [数据库名.]表名；
desc user;
desc servers;
#查看表结构

desc mysql.servers;
#不想先使用数据库可以使用上面的

8.2  新建表

###2.创建新的表####
create table 表名 （字段1 数据类型，字段2 数据类型[,...] [,PRIMARY KEY (主键名)]）；
#主键一般选择代表唯一性的字段不允许为空值（null）,且一个表只能有一个主键###

（1）显示当前数据库下所有的表格

show  tables;

（2）查看表的结构

desc  表名;
desc student;

（3）添加表的字段

语法结构：alter table 表名 add 字段名 数据类型[修饰符]

alter table student add phone varchar(11) not null;

（4）在表中插入数据

格式：insert 表名(字段1，字段2...) values(字段1的值，字段2的值,......)

示例：

(root@localhost) [cxk1]> insert student(id,name,phone) values(1,'caixukun','10000');

insert 表名(字段1，字段2...) values(字段1的值，字段2的值,......)

(root@localhost) [cxk1]> select * from student;
#查看表中所有的 数据
select *from 表名;

9.DML语言

9.1   INSERT语句

功能：一次插入一行或多行数据

INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

#解释
insert  表名[（字段）]  值（值1，值2....）,(值1，值2)......；
使用 insert 语句时 如果不在表后加上字段就要一一对应填写上信息（注意 字符串用引号引起来）

也可以指定添加的字段 

示例：

添加多行数据

9.2  UPDATE 语句

语法：update  表名   set  字段=修改的值     指定哪条记录;

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
   [WHERE where_condition]
   [ORDER BY ...]
   [LIMIT row_count]

注意：一定要有限制条件，否则将修改所有行的指定字段

示例：

将student表中的id为6的gerder改为F

将student表中的id为5的name改为web，phone改为159263

9.3  DELETE 语句

删除表中数据，但不会自动缩减数据文件的大小。

语法：  delete  from  表名   指定条件

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
   [WHERE where_condition]
   [ORDER BY ...]
   [LIMIT row_count]
 可先排序再指定删除的行数

注意：一定要有限制条件，否则将清空表中的所有数据

如果想清空表，保留表结构，也可以使用下面语句，此语句会自动缩减数据文件的大小

示例：

10. DOL 语句 单表查询

MYSQL中的SELECT语句的语法结构

1. SELECT子句：

   • ALL | DISTINCT | DISTINCTROW：指定返回的结果集中是否包括重复行。ALL 表示包括所有行（默认行为），DISTINCT 表示只返回不同的行（即去重），DISTINCTROW 是某些数据库系统特有的，具体含义可以参考相应数据库的文档。
   • SQL_CACHE | SQL_NO_CACHE：用于指定查询结果是否应该被缓存。SQL_CACHE 表示结果应该被缓存以供后续查询使用（如果支持），SQL_NO_CACHE 表示不应该缓存结果。

2. select_expr：要查询的字段或表达式，用逗号分隔。

3. FROM子句：指定要查询的表及其关联条件。

4. WHERE子句：用于指定筛选条件，只有符合条件的行会被包含在结果集中。

5. GROUP BY子句：用于对结果进行分组，常与聚合函数（如COUNT、SUM等）一起使用。

6. HAVING子句：类似于WHERE子句，但是作用于GROUP BY分组后的结果集，用于筛选分组后的结果。

7. ORDER BY子句：用于指定结果集的排序顺序，可以按照一个或多个列进行升序（ASC，默认）或降序（DESC）排序。

8. LIMIT子句：用于限制返回的结果行数。可以指定从哪一行开始以及返回多少行。

9. FOR UPDATE | LOCK IN SHARE MODE：用于在SELECT语句中对查询的数据行进行锁定，以确保在事务中其它会话不能修改这些行。

10.1  select  显示表格中一个或者多个字段的所有数据记录

语法使用：

select  字段 from  表名;   
#字段可以用* 表示 代表所有，也可以挑选出自己想要的

示例：

查看student表name和age字段

select  age from student; 
select  age,name  from student;

10.2 WHERE  过滤查询

语法： SELECT "字段" FROM "表名" WHERE "条件";

说明：

• 过滤条件：布尔型表达式

• 算术操作符：+, -, *, /, %

• 比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=

• 范例查询: BETWEEN min_num AND max_num

• 不连续的查询: IN (element1, element2, ...)

• 空查询: IS NULL, IS NOT NULL

• 字段显示可以使用别名

示例：

（1）查询students表中名为xu  xian 的学生

select * from students where name='xu xian';

（2）查询students表中年龄大于等于20岁小于等于30岁的学生

select * from students where age >=20 and age <=30;

select * from students where age between 20 and 30;

（3）不连续查询，查询年龄为20，27，46的学生

IN (element1, element2, ...)

select  * from students where age in (20,27,46);

（4）空查询 

IS NULL（空查询），IS NOT NULL（非空查询）

 select * from students where classid is null;

select * from students where classid is not null;

（5）字段使用别名

select 字段名 as 别名,字段名 as 别名 from  students; （as可以省略）

select age 年龄,classid 班级  from  students;

10.3 AND ---> 且   OR ---> 或

语法：  SELECT "字段" FROM "表名" WHERE "条件1" {[AND|OR] "条件2"}+ ;

示例：

过滤出students表中年龄大于20且小于30或大于50的学生

select * from students where (age >20 and age <30) or age >50;

过滤出年龄大于30且性别为男（M）的学生

select  * from students where age >30 and gender='M';

10.4 DISTINCT  去除重复行

示例：

去除年龄相同的行

select age from students;

select distinct age from students;     #去重

10.5  like 模糊查询与通配符

% ：百分号表示零个、一个或多个字符   *
_ ：下划线表示单个字符             ？     

'A_Z'：所有以 'A' 起头，另一个任何值的字符，且以 'Z' 为结尾的字符串。例如，'ABZ' 和 'A2Z' 都符合这一个模式，而 'AKKZ' 并不符合 (因为在 A 和 Z 之间有两个字符，而不是一个字符)。
'ABC%': 所有以 'ABC' 起头的字符串。例如，'ABCD' 和 'ABCABC' 都符合这个模式。
'%XYZ': 所有以 'XYZ' 结尾的字符串。例如，'WXYZ' 和 'ZZXYZ' 都符合这个模式。
'%AN%': 所有含有 'AN'这个模式的字符串。例如，'LOS ANGELES' 和 'SAN FRANCISCO' 都符合这个模式。
'_AN%'：所有第二个字母为 'A' 和第三个字母为 'N' 的字符串。例如，'SAN FRANCISCO' 符合这个模式，而 'LOS ANGELES' 则不符合这个模式。

语法：SELECT "字段" FROM "表名" WHERE "字段" LIKE   "匹配表达式";

示例：

过滤出name姓名以“x”开头的学生

select * from students where name like 'x%';

过滤出姓名以“g”结尾的学生

 select * from students where name like '%g';

过滤出姓名中包含“x”的学生

select * from students where name like '%x%';

复制表结构

create table test like students;

10.6  数学函数

函数名	函数值
abs(x)	返回 x 的绝对值
rand()	返回 0 到 1 的随机数
mod(x,y)	返回 x 除以 y 以后的余数
power(x,y)	返回 x 的 y 次方
round(x )	返回离 x 最近的整数
round(x,y)	保留 x 的 y 位小数四舍五入后的值
sqrt(x)	返回 x 的平方根
truncate(x,y)	返回数字 x 截断为 y 位小数的值
ceil(x)	返回大于或等于 x 的最小整数
floor(x)	返回小于或等于 x 的最大整数
greatest(x1,x2...)	返回集合中最大的值，也可以返回多个字段的最大的值

示例：

select abs(-100);   #取绝对值
select rand();      #随机数 0到1 间
select mod(10,3);   #10 除3 取余数
select power(2,3)   #求2的3次方
select round(2.6);   #返回离2.6最近的整数3
select sqrt(9);     #返回9 的平方根
select truncate (1.235,2);   #返回前两位值
select ceil (1.5);  #返回大于等于1.5 的值
select floor (1.5);  #返回小于等于1.5 的值
select greatest(1,2,3); #返回集合中的 最大值
select least(1,2,3);    #返回集合中的最小值 

10.7  聚合函数

函数名	函数意
avg()	返回指定列的平均值
count()	返回指定列中非 NULL 值的个数
min()	返回指定列的最小值
max()	返回指定列的最大值
sum(x)	返回指定列的所有值之和

语法格式：

select  函数(*)   from   表名;   # * 代表所有字段
select  函数(单个字段)   from   表名;  

（1）avg  平均值

求出所有学生的平均年龄值

select  avg(age) from students;

求出1班学生的平均年龄值

select  avg(age) from students where classid=1;

（2）count  返回指定列中非 NULL 值的个数

聚合函数 count()  括号中是具体的字段  如果有null 值不统计 
count()  括号中是  *   会统计 null
#count(*) 包括了所有的列的行数，在统计结果的时候，不会忽略列值为 NULL
#count(列名) 只包括列名那一列的行数，在统计结果的时候，会忽略列值为 NULL 的行

显示出性别为女的学生个数

select count(*) from students where gender='F';

（3）min 最小值

select  min(age) from students;

（4）max  最大值

 select  max(age) from students;

（5）sum 求和

将students表中学生年龄求和，女性年龄求和，男性年龄求和

 select sum(age) from students;
 select sum(age) from students where gender='F';
 select sum(age) from students where gender='M';

10.8 字符串函数

函数名	函数意义
concat(x,y)	将提供的参数 x 和 y 拼接成一个字符串
substr(x,y)	获取从字符串 x 中的第 y 个位置开始的字符串，
substr(x,y,z)	获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
length(x)	返回字符串 x 的长度
replace(x,y,z)	将字符串 z 替代字符串 x 中的字符串 y
upper(x)	将字符串 x 的所有字母变成大写字母
lower(x)	将字符串 x 的所有字母变成小写字母
left(x,y)	返回字符串 x 的前 y 个字符
right(x,y)	返回字符串 x 的后 y 个字符
repeat(x,y)	将字符串 x 重复 y 次
space(x)	返回 x 个空格
strcmp(x,y)	比较 x 和 y，返回的值可以为-1,0,1
reverse(x)	将字符串 x 反转

示例：

（1）concat 

将姓名和年龄字段合并在一起

 select concat(name,age) from students ;
 select concat(name,' ',age) from students ;#  ' '代表空格，在姓名和年龄中间加空格

（2）substr  

获取name字段的 前1到4个字符（空格也是一个字符）

select substr(name,1,4) from students;

获取name从第3个字符开始显示

 select substr(name,3) from students;

select length(name) from students where stuid=1;      #返回数据的长度
select replace(name,"y",11) from students where stuid=1;  # 将name 字段中的 y  换成11
select left(name,3) from students where stuid=1;   #显示name字段左边三个字符  即最开始的三个
select right(name,3) from students where stuid=1;  #显示name字段 右边三个字符 即最后三个
select repeat(name,2) from students where stuid=1; #将name 字段 重复显示2次
select lower(name) from students;   #返回结果全是小写字母
select reverse(name) from students where stuid=1;  # 反向显示字符串

10.9 GROUP BY 分组

对GROUP BY后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的GROUP BY 有一个原则，凡是在 GROUP BY 后面出现的字段，必须在 SELECT 后面出现；凡是在 SELECT 后面出现的、且未在聚合函数中出现的字段，必须出现在 GROUP BY 后面

语法：SELECT "字段1", 聚合函数("字段2") FROM "表名" GROUP BY "字段1";

示例：

对classid字段进行分组，一共有8组

select classid from students group by classid;

对性别进行分组

select gender from students group by gender;

按班级（classid）分组，并显示每一组的 人数

select classid,count(*) from students group by classid;

求男女的平均年龄

select gender,avg(age) from students group by gender;

求每个班级的平均年龄

select classid,avg(age) from students group by classid;

10.10  排序  order  by

语法：

SELECT "字段" FROM "表名" [WHERE "条件"] ORDER BY "字段" [ASC, DESC];
#ASC 是按照升序进行排序的，是默认的排序方式。
#DESC 是按降序方式进行排序。

示例：

按年龄升序排序

select  * from students order by age;

按年龄倒序排序

select  * from students order by age desc;

找出一班的学生安装年龄升序排序

select  * from students where classid=1 order by age;

10.11  limit  输出行数数量限制

LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制,跳过offset,显示row_count行,offset默为值为0

一个页面可以显示商品也是靠limit 分页实现

示例：

 按年龄倒序排序，显示前五行

select  * from students order by age desc limit 5;

limit分页

按年龄升序排序，跳过前三行往后取四行

select * from students order by age limit 3,4;

10.12  having  筛选

HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足

WHERE子句在数据分组前进行行级别的筛选。

HAVING子句在数据分组后对分组结果进行筛选。

示例：

过滤出students表中classid>3的班级，并统计出每个班级的人数

select classid,count(classid) from students group by classid having classid >3;

select classid,count(classid) from students where classid > 3 group by classid;
#使用where过滤

统计出年龄小于三十的每个年龄的人数

select age,count(age) from students group by age having age <30;

10.13  视图 view  零时表

视图：数据库中的虚拟表，这张虚拟表中不包含真实数据，只是做了映射

格式：create  view   视图名  as   查询结果

示例：

创建视图，查看视图

create view v1 as select * from students where age < 30;
show tables;
select * from v1;

当将学生表中stuid为1的学生（也在v1视图表中）年龄修改为25，查看视图中的数据也被修改

update students set age=25 where stuid=1;
select * from v1;

五.练习

1. 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

select name,age from students where age > 25 and gender='M';

2. 以ClassID为分组依据，显示每组的平均年龄

select classid,avg(age) from students group by classid;

3. 显示第2题中平均年龄大于30的分组及平均年龄

select classid,avg(age) from students group by classid  having avg(age) > 30;

4. 显示以L开头的名字的同学的信息

select * from students where name like 'L%';

5. 显示TeacherID非空的同学的相关信息

select * from students where teacherid is not null;

6. 以年龄排序后，显示年龄最大的前10位同学的信息

select * from students order by age desc limit 10;

7. 查询年龄大于等于20岁，小于等于25岁的同学的信息

select * from students where age >= 20 and age <= 25;

8. 以ClassID分组，显示每班的同学的人数

select classid,count(classid) from students group by classid;

9. 以Gender分组，显示其年龄之和

select gender,sum(age) from students group by gender;

10. 以ClassID分组，显示其平均年龄大于25的班级

select classid,avg(age) from students group by classid having avg(age) > 25;

11. 以Gender分组，显示各组中年龄大于18的学员的年龄之和

select gender,sum(age) from students where age > 18  group by gender;