【mysql】数据库之SQL查询语句

命令中大小写不敏感，但是数据库名称的大小写敏感。

1.1 命令格式

 格式：
 mysql [选项] [数据库名]

选项：

选项	含义
-A	–no-auto-rehash 禁止补全
-u	–user= 用户名, 默认为 root
-h	–host= 服务器主机, 默认为 localhost
-p（小）	–passowrd= 用户密码, 建议使用 -p, 默认为空密码
-P（大）	–port= 服务器端口
-S	–socket= 指定连接 socket 文件路径
-D	–database= 指定默认数据库
-C	–compress 启用压缩
-e	“SQL” 执行 SQL 命令
-V	–version 显示版本
-v	–verbose 显示详细信息
–print-defaults	获取程序默认使用的配置

1.2 基础命令

 mysql  -uroot  -p     // 默认空密码登录
 ​
 show databases;      // 查看所有数据库
 ​
 mysql> status       // 查看数据库的基本状态
 ​
 mysql> use mysql;    // 切换数据库,use 类似于 cd
 mysql> select database();      // 查看当前数据库
 ​
 mysql> select user();       // 查看当前用户
 +----------------+
 | user()         |
 +----------------+
 | root@localhost |
 +----------------+
 ​
 mysql> SELECT User,Host,Password FROM user;
 mysql> system clear         // 清屏
 ​
 system ifconfig   // 不退出 mysql，可以查看系统的一些信息

1.3 mysqladmin 命令

客户端程序：

工具	备注
mysql	交互式或非交互式的 CLI 工具 加 -e
mysqldump	备份工具。基于 mysql 协议向 mysqld 发起查询请求，并将查得的所有数据转换成
mysqladmin	基于 mysql 协议管理 mysqld

mysql 示例：

 [root@7-2 ~]# mysql -uroot -pabc123 -e "create database cxk;"     // 不进入 mysql 里面
 (root@localhost) [(none)]> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | cxk                |    //ok
 | mysql              |
 | performance_schema |
 +--------------------+

mysqladmin 格式：

 格式：mysqladmin - u 用户 - p 密码
 ​
 [root@7-2 ~]# mysqladmin -uroot -pabc123 ping    // 测试 mysql 服务是否正常，如果正常提示 mysqld is alive
 ​
 [root@7-2 ~]# mysqladmin -uroot -pcentos shutdown    // 关闭 mysql 服务，但只能关闭，没有开启命令
 ​
 [root@7-2 ~]# mysqladmin -uroot -pcentos   create testdb    // 创建数据库 testdb
 ​
 [root@7-2 ~]# mysqladmin -uroot -pcentos  drop testdb    // 删除数据库 testdb
 ​
 [root@7-2 ~]# mysqladmin -uroot -pAdmin@123 password‘zhj'    // 修改 root 密码
 ​
 [root@7-2 ~]# mysqladmin -uroot -pcentos flush-logs     // 日志滚动, 生成新文件 /var/lib/mysql/mariadb-bin.00000N

小拓展：

 应用单多线程：
 pstree -p
 cat /proc/ 线程 id/status
 proc 存在内存中
 ​
 mysql> system hostname
 localhost.localdomain
 #可以直接使用系统命令
 #source 可以调用脚本
 ​
 ​
 mysql> h, help
 mysql> u，use
 mysql> s，status
 mysql> !，system

MySQL 支持多种内置数据类型：

1. 数值类型

2. 字符串 (字符) 类型

   • char：固定长度，最多 255 个字符，注意不是字节
   • varchar：可变长度，最多 65535 个字符⭐
   • text：可变，最多 65535 个字符

3. 日期 / 时间类型

   • year：年份
   • date：年月日
   • time：时间 时分秒
   • datetime：年月日和时分秒

小拓展：文本中，如果超出字段长度限制，会有下面两种情况

• 整个不让录入
• 将超出部分截断

作用：修饰数据

适用所有类型的修饰符：

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

适用数值型的修饰符：

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

SQL 语句 规范格式：

• SQL 语句支持单行或多行书写，默认以 ” ; ” 结尾
• 客户端命令结尾不加分号

数据库 命名规则：

• 必须以字母开头，后续可以包括字母、数字和 3 个特殊字符（# _ $）
• 不能使用 MySQL 的保留字，如 table select show databases

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

修改默认的字符集：

默认的字符集是拉丁文。

 MariaDB [DB1]> show create database DB1;
 +----------+----------------------------------------------------------------+
 | Database | Create Database                                                |
 +----------+----------------------------------------------------------------+
 | DB1      | CREATE DATABASE `DB1` /*!40100 DEFAULT CHARACTER SET latin1 */ |  // 拉丁
 +----------+----------------------------------------------------------------+
 ​
 ` 查看支持哪些字符集：`
 MariaDB [DB1]> show variables like 'char%';
 +--------------------------+----------------------------+
 | Variable_name            | Value                      |
 +--------------------------+----------------------------+
 | character_set_client     | utf8                       |
 | character_set_connection | utf8                       |
 | character_set_database   | latin1                     |
 | character_set_filesystem | binary                     |
 | character_set_results    | utf8                       |
 | character_set_server     | latin1                     |
 | character_set_system     | utf8                       |
 | character_sets_dir       | /usr/share/mysql/charsets/ |
 +--------------------------+----------------------------+

 ` 修改字符集：`
 [root@7-3 ~]# vim /etc/my.cnf
 [mysqld]
 character-set-server=utf8mb4     // 将字符集由拉丁文，改成 utf8
 [root@7-3 ~]# systemctl restart mysqld

4.1 database 数据库

4.1.1 create 创建

 语法格式：
 create database 数据库名称;     //database 后不需要加 s 
 ​
 create database DB2;       // 创建数据库 DB2

示例 1：

 create database DB3 character set 'utf8';     // 创建 DB3, 字符集为 utf8
 ​
 MariaDB [DB1]> show create database DB3;
 +----------+--------------------------------------------------------------+
 | Database | Create Database                                              |
 +----------+--------------------------------------------------------------+
 | DB3      | CREATE DATABASE `DB3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
 +----------+--------------------------------------------------------------+

示例 2：

 create database IF NOT EXISTS DB1;    // 如果没有 DB1，那么就创建 DB1
 ​
 MariaDB [DB1]> show warnings;
 +-------+------+----------------------------------------------+
 | Level | Code | Message                                      |
 +-------+------+----------------------------------------------+
 | Note  | 1007 | Can't create database'DB1'; database exists |   //DB1 已存在
 +-------+------+----------------------------------------------+

4.1.2 drop 删除

 语法格式：
 drop database 数据库名称;

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

示例：

 drop database DB2;    // 删除 DB2 数据库

4.1.3 alter 修改

比如增加或者删除字段。一般不能修改数据库名字！这个命令用的非常非常少！！！

 help alter database    // 查看帮助
 ​
 ALTER DATABASE DB1 character set 'utf8';    // 修改字符集

4.2 table 表

表的增删改查，必须要先指定数据库！

库中有表，表中有数据。

table 基础命令：

 help create table    // 查看帮助
 ​
 show tables;        // 显示当前数据库下的 所有表格
 ​
 查看表结构:
 desc student;        // 显示当前数据库下的 所有表结构⭐
 desc DB1.student;     // 显示 DB2 数据库下的 所有表结构
 ​
 show table status like 'student'G;    // 分行显示表状态
 ​
 unsigned：取消负数
 primary  key：主键
 auto_increment：自增长
 enum('M','F')：多选，枚举值
 default 'M'：默认值为 M

4.2.1 create 新建表

 格式：
 create table 表名 (字段名1 字段类型 [修饰符], 字段名2 字段类型 [修饰符], 字段名3 字段类型 [修饰符]);        // 必须要定义字段类型，否则会报错。修饰符是可选项，可加可不加！

示例 1：新建 student 表

 create table student (id int primary key,name varchar(10),age char(3));

 MariaDB [DB1]> desc student;     // 查看 student 表结构
 +-------+-------------+------+-----+---------+-------+
 | Field | Type        | Null | Key | Default | Extra |
 +-------+-------------+------+-----+---------+-------+
 | id    | int(11)     | NO   | PRI | NULL    |       |
 | name  | varchar(10) | YES  |     | NULL    |       |
 | age   | char(3)     | YES  |     | NULL    |       |
 +-------+-------------+------+-----+---------+-------+

示例 2：在一行写

 create table student2 (id int primary key,name varchar(10),age char(3),gender enum('M','F') default 'M');      //gender 是性别，enum 是枚举类型，只能在 M 男和 F 女里面选择，默认是男。

示例 3：分成多行写

 MariaDB [DB1]> create table student3 (
     -> id int primary key,
     -> name varchar(10),
     -> age char(3),
     -> gender enum('M','F') default 'M');   // 只有出现分号; 才是结束

4.2.2 alter 修改表结构

4.2.2.1 add 添加字段

 格式：
 alter table 表名 add 字段名 字段类型 [修饰符];

示例 1：

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

4.2.2.2 change 修改字段名

 alter table student change phone mobile char(11);    // 将字段名 phone 改为 mobile

4.2.2.3 drop 删除字段

 alter table student drop mobile;

4.2.2.4 rename 表格重命名

 alter table student rename s1;    // 将 student 表重命名为 s1

4.3 字段

4.3.1 insert 插入行记录

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

 格式：
 insert [into] 表名(字段1, 字段2, 字段3...) values(值1, 值2, 值3...)     // 使用 insert 语句时 into 可以省略。

示例 1：

 insert student(name,age,mobile) values('wxy',22,'123456');
 ​
 MariaDB [DB1]> select * from student;
 +----+------+------+--------+
 | id | name | age  | mobile |
 +----+------+------+--------+
 |  0 | wxy  | 22   | 123456 |
 +----+------+------+--------+

示例 2：

 insert student values('2','wjy',32,'654321');    // 表名后不加字段，就要按字段顺序一一对应填写信息，id 不要忘了写！（注意：字符串要用引号引起来）
 MariaDB [DB1]> select * from student;
 +----+------+------+--------+
 | id | name | age  | mobile |
 +----+------+------+--------+
 |  0 | wxy  | 22   | 123456 |
 |  2 | wjy  | 32   | 654321 |
 +----+------+------+--------+

4.3.2 update 更新修改记录

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

 格式：
 update 表名 set 字段名 = 修改 where 指定条件;

示例 1：

 update student set id=1 where id=2;     // 当 student 表中，id= 2 时，将 id 改为 1 
 MariaDB [DB1]> select * from student;
 +----+------+------+--------+
 | id | name | age  | mobile |
 +----+------+------+--------+
 |  0 | wxy  | 22   | 123456 |
 |  1 | wjy  | 32   | 654321 |
 +----+------+------+--------+

4.3.3 delete 删除记录

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

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

 格式：
 delete  from  表名   where 指定条件

示例 1：

 MariaDB [DB1]> delete from student where id=0;     // 删除表中 id= 0 对应的记录
 MariaDB [DB1]> select * from student;
 +----+------+------+--------+
 | id | name | age  | mobile |
 +----+------+------+--------+
 |  1 | wjy  | 32   | 654321 |
 +----+------+------+--------+

前期准备：添加脚本

 [root@7-2 ~]# ***** 加载 sql 脚本 *****
 source /root/hellodb_innodb.sql        // 运行脚本
 ​
 show tables;

5.1 基础命令

 select * from 表名;     // 查看表中所有内容。生产环境中一定要慎用！！因为数据量太大！！！
 ​
 ` 可以与 where 搭配使用:`
 select * from 表名 where 指定条件;
 ​
 select name,age from student;     // 可以指定查看两个字段。中间用逗号隔开
 ​
 select name as 名字,age as 年龄 from student;     //as 类似于别名，可以省略不写。

5.2 单表查询

5.2.1 where 过滤查询

 语法：
 select 字段 from 表名 where 条件;

说明：

• 过滤条件：布尔型表达式
• 算术操作符：+, -, *, /, %
• 比较操作符：=,（相等或都为空）, , !=(非标准 SQL), >, >=,
• 范例查询: BETWEEN min_num AND max_num
• 不连续的查询: IN (element1, element2, …)
• 空查询: IS NULL, IS NOT NULL
• 字段显示可以使用别名

示例：

 select * from students where name="xi ren";

5.2.1.1 连续范围查询 between and

从一个范围到另一个范围 BETWEEN min_num AND max_num

示例 1：

 select * from students where classid between 1 and 3;    // 筛选出班级 id 为从 1 到 3 

5.2.1.2 非连续范围查询 in

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

示例：

 select * from students where classid in (1,3);     // 筛选出班级 id 为 1 和 3 

5.2.1.3 空查询

空查询 IS NULL, IS NOT NULL

示例：

 select * from students where classid is null;
 select * from students where classid is not null;

5.2.2 and 且 | or 或

 语法：
 SELECT "字段" FROM "表名" WHERE "条件 1" AND|OR "条件 2";

示例 1：

 select * from students where classid >= 1 and classid 3;    //classid 为 1 到 3 的

示例 2：

 select * from students where classid = 1 or classid = 3;    //classid 为 1 或 3 的

5.2.3 distinct 去除重复行

 格式：
 select distinct 字段名 from 表名;

示例 1：

 MariaDB [hellodb]> select distinct gender from students;
 +--------+
 | gender |
 +--------+
 | M      |
 | F      |
 +--------+

示例 2：

 select distinct classid from students;

5.2.4 like 模糊查询 与 通配符

mysql 里基本不使用正则表达式。

 %：百分号表示零个、一个或多个字符
 _：下划线表示单个字符
 ​
 '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 "匹配表达式";

示例：

 select * from students where name like "xu %";    // 名字以 xu 开头的

小拓展：复制表结构

 create table test like students;

5.2.5 聚合函数

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

语法格式：

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

示例：

 `avg 平均值:`
 select avg(age) from students;   // 表中所有人年龄的平均值
 select avg(age) from students where classid=1;  // 求 1 班年龄的平均值
 ​
 `count 计数:`
 select count(age) from students;
 ​
 `sum 求和:`
 select sum(age) from students;
 select sum(age) from students where classid = 3;

5.2.6 group by 分组

对 GROUP BY 后面的字段的查询结果进行汇总分组

 语法：
 SELECT "字段 1", 聚合函数("字段 2") FROM "表名" GROUP BY "字段 1";     // 前面有字段 1, group by 后面也必须要有字段 1 

示例 1：

 MariaDB [hellodb]> select gender from students group by gender;     // 按性别进行分组
 +--------+
 | gender |
 +--------+
 | F      |
 | M      |
 +--------+

示例 2：

 MariaDB [hellodb]> select gender,count(*) from students group by gender;    // 按性别分组，并统计个数
 +--------+----------+
 | gender | count(*) |
 +--------+----------+
 | F      |       10 |
 | M      |       15 |
 +--------+----------+

示例 3：

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

5.2.7 order by 排序

语法：

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

示例 1：asc

 select * from students order by age;     // 按年龄正序排列，默认是 ASC 正序

示例 2：desc

 select * from students order by age desc;     // 按年龄，倒序排列。

5.2.8 limit⭐

示例 1：显示前 5 个

 select * from students limit 5;

示例 2：

 MariaDB [hellodb]> select * from students order by age desc limit 3;    // 倒序显示前 3 个
 +-------+-------------+-----+--------+---------+-----------+
 | StuID | Name        | Age | Gender | ClassID | TeacherID |
 +-------+-------------+-----+--------+---------+-----------+
 |    25 | Sun Dasheng | 100 | M      |    NULL |      NULL |
 |     3 | Xie Yanke   |  53 | M      |       2 |        16 |
 |     6 | Shi Qing    |  46 | M      |       5 |      NULL |
 +-------+-------------+-----+--------+---------+-----------+

示例 3：跳过前 3 个，以第 4 个为基础，显示 5 个

 MariaDB [hellodb]> select * from students limit 3,5;
 +-------+-----------+-----+--------+---------+-----------+
 | StuID | Name      | Age | Gender | ClassID | TeacherID |
 +-------+-----------+-----+--------+---------+-----------+
 |     4 | Ding Dian |  32 | M      |       4 |         4 |
 |     5 | Yu Yutong |  26 | M      |       3 |         1 |
 |     6 | Shi Qing  |  46 | M      |       5 |      NULL |
 |     7 | Xi Ren    |  19 | F      |       3 |      NULL |
 |     8 | Lin Daiyu |  17 | F      |       7 |      NULL |
 +-------+-----------+-----+--------+---------+-----------+

5.2.9 having

having 通常与 group by 组合使用。

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

示例 1：

 MariaDB [hellodb]> select classid from students group by classid;     // 根据 classid 进行排序
 +---------+
 | classid |
 +---------+
 |    NULL |
 |       1 |
 |       2 |
 |       3 |
 |       4 |
 |       5 |
 |       6 |
 |       7 |
 +---------+
 ​
 MariaDB [hellodb]> select classid from students group by classid having classid > 5;    // 筛选出 classid 大于 5 的，并根据 classid 进行排序
 +---------+
 | classid |
 +---------+
 |       6 |
 |       7 |
 +---------+

示例 2：

 MariaDB [hellodb]> select age from students group by age having age > 35;
 +-----+
 | age |
 +-----+
 |  46 |
 |  53 |
 | 100 |
 +-----+

5.2.10 view 视图（临时表）

视图：数据库中的虚拟表，这张虚拟表中不包含真实数据，只是临时数据。

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

示例 1：

 create view wt as select * from students where age between 20 and 50;    // 创建 wt 临时表
 select * from wt;

示例 2：

 create view hlm as select * from students where stuid=7 or stuid=8;
 MariaDB [hellodb]> select * from hlm;
 +-------+-----------+-----+--------+---------+-----------+
 | StuID | Name      | Age | Gender | ClassID | TeacherID |
 +-------+-----------+-----+--------+---------+-----------+
 |     7 | Xi Ren    |  19 | F      |       3 |      NULL |
 |     8 | Lin Daiyu |  17 | F      |       7 |      NULL |
 +-------+-----------+-----+--------+---------+-----------+

5.3 多表查询

5.3.1 子查询（嵌套）

示例 1：

 update students set age=(select avg(age) from teachers) where stuid=23;    // 将 23 号的年龄修改为 教师表的平均年龄
 select * from students where stuid=23;     // 查看修改后的 23 号对应的年龄。

示例 2：

 select * from students where age > (select avg(age) from students);    // 选出比平均年龄大的人

5.3.2 联合查询（纵向合并）

union：表与表之间的字段数量相同，把第二张表追加到后面

示例 1：两张表根据对应字段 纵向合并

 select name,age from students union select name,age from teachers;

示例 2：相同的表之间关联，union 默认自动去重

 select * from teachers union  select *from teachers;

示例 3：union all 不去重

 select * from teachers union all select * from teachers;

5.3.3 交叉连接（横向合并）

cross join：即多表的记录之间做 笛卡尔乘积组合，并且多个表的列横向合并相加，这个用的比较少

示例：行的数量是两张表数量相乘，列的数量是两张表数量相加。

 select * from students cross join teachers;

5.3.4 内连接

inner join 内连接取多个表的交集

语法：

 第一张表    inner join   第二张表  on  条件

示例 1：学生表 id 和教师表 id 相同的行

 select * from students inner join teachers on students.stuid =teachers.tid;