mysql语句-秋风落叶

数据库管理

创建数据库

--创建数据库wjdb，设置字符集为utf8mb4，排列顺序为utf8mb4_general_ci
CREATE DATABASE `wjdb` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

登录数据库

#登录本机数据库
[root@localhost ~]# mysql -uroot -pwangjian

#登录其它IP数据库
mysql -h 127.0.0.1 -u <用户名> -p<密码>
mysql -D 数据库名 -h 主机名 -u 用户名 -p
mysql -h <host> -P <端口号> -u <user> -p [db_name]
mysql -h <host> -u <user> -p [db_name]

删除数据库

DROP database `wjdb`;

查询数据库

查询当前用户有多少数据库

mysql> show databases;

查询当前使用的是哪个库

mysql> select database();

查询数据库字符集

--方式1：
mysql> select * from information_schema.SCHEMATA;
--方式2：
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
character_set_client      为客户端编码方式；
character_set_connection  为建立连接使用的编码；
character_set_database    为数据库的编码；
character_set_results     为结果集的编码；
character_set_server      为数据库服务器的编码；
只要保证以上采用的编码方式一样，就不会出现乱码问题。

查询数据库大小

--方式1
SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES 
WHERE table_schema = "你的数据库名称"
GROUP BY table_schema;

--方式2：
mysql> use information_schema;
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='你的数据库名称';

切换数据库

--必须是管理用户，才能切换到其它数据库
#方式1:
use wjdb;
#方式2:
CONNECT wjdb ;

退出数据库

--退出mysql
mysql> exit
或者 退出 quit; 或 \q; 一样的效果

慢sql

--显示是否开启慢查询日志，off则未开启，on表示开启
show VARIABLES like 'slow_query_log'

--显示设置的慢sql时间，当sql执行的时间超过这个值，将会被记录为慢sql 
--这里查询得到long_query_time的值为10秒（具体的值可能根据数据库的配置而不同）。
mysql> SHOW VARIABLES LIKE 'long_query_time'; 
+-----------------+-----------+ 
| Variable_name   | Value     | 
+-----------------+-----------+ 
| long_query_time | 10.000000 | 
+-----------------+-----------+ 
--显示记录慢sql的日志文件
show VARIABLES like 'slow_query_log_file'
--显示记录的慢sql数量
show global status like 'slow_queries'

库中数据查询

查询库中超过1000条数据以上的表

SELECT
  table_schema AS 'Database',
  table_name AS 'Table',
  table_rows AS 'Rows' 
FROM
  information_schema.`TABLES` 
WHERE
  table_rows > 1000 
  AND table_schema != 'mysql'   --排除自带的mysql库，根据实际情况排除其它库
ORDER BY
  table_rows DESC;

查询数据库中，所有库中表数据条数

SELECT
  table_schema AS 'Database',
  table_name AS 'Table',
  table_rows AS 'Rows' 
FROM
  information_schema.`TABLES` 
WHERE
  table_schema != 'mysql' 
  AND table_schema != 'performance_schema' 
ORDER BY
  table_rows DESC;

结果如下:

wordpress	wp_postmeta	3642
wordpress	wp_options	979
wordpress	wp_posts	758
wordpress	wp_term_relationships	681
wordpress	wp_termmeta	532
wordpress	wp_aioseo_posts	460

查询某个库中，每个表的数量

SELECT
  table_schema AS 'Database',
  table_name AS 'Table',
  table_rows AS 'Rows' 
FROM
  information_schema.`TABLES` 
WHERE
  table_schema = 'wordpress' 
ORDER BY
  table_rows DESC;

结果如下:

wordpress	wp_postmeta	3642
wordpress	wp_options	979
wordpress	wp_posts	758
wordpress	wp_term_relationships	681
wordpress	wp_termmeta	532
wordpress	wp_aioseo_posts	460
wordpress	wp_aioseo_cache	263
wordpress	wp_links	259
--------省略-----

数据库版本查询

方式1：
[root@wj ~]# mysql -V
方式2：
[root@wj ~]# mysql --help |grep Distrib
方式3：
mysql> select version();
方式4：
mysql> \s
方式5：
mysql> status;

修改字符集

mysql> ALTER DATABASE wjdb CHARACTER SET 'utf8';
Query OK, 1 row affected (0.00 sec)

再次查询wjdb数据库的字符集已修改
mysql> select * from information_schema.SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | mysql              | latin1                     | latin1_swedish_ci      | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | sys                | utf8                       | utf8_general_ci        | NULL     |
| def          | wjdb               | utf8                       | utf8_general_ci        | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
5 rows in set (0.00 sec)

端口

--数据库端口查看
mysql>show global variables like 'port';

--修改端口
vim /etc/my.cnf 
[mysqld]
port=3506

连接数并发数

最大连接数
mysql>  show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

连接数并发数
mysql> show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 1     |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)
Threads_cached : 代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created :代表从最近一次服务启动，已创建线程的数量。
Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。

表管理

表备份

-- 备份和还原
--同时备份表结构和表数据：
create table 新表名 as select * from 旧表名

--只备份表结构：
create table 新表名 as select * from 旧表名 where 1=2

--还原表
方式1：
insert into 现表 select * from 备份表;
方式2：
delete 旧表名; #当前旧表的数据
create table 旧表名 as select * from 之前备份的表

表删除

-- 删除老师表 删除整个表，包括数据
DROP TABLE IF EXISTS `teacher`;
-- 删除学生表 删除整个表，包括数据
DROP TABLE IF EXISTS `student`;
-- 删除表数据，保留表
DELETE FROM `student`
-- 删除指定某个数据
mysql> delete from student where id=2;

表创建

-- 创建老师表
CREATE TABLE `teacher` (
  `teacher_id` INT NOT NULL COMMENT '老师id',
  `teacher_name` VARCHAR ( 255 ) NULL COMMENT '老师姓名',
  `teacher_age` INT NULL COMMENT '老师年龄',
  `teacher_time` DATETIME NULL COMMENT '老师入校时间',
  PRIMARY KEY ( `teacher_id` ) 
) COMMENT = '老师表';
-- 创建学生表
CREATE TABLE `student` (
  `student_id` INT NOT NULL COMMENT '学生id',
  `student_name` VARCHAR ( 255 ) NULL COMMENT '学生姓名',
  `student_age` INT NULL COMMENT '学生年龄',
  `student_time` DATETIME NULL COMMENT '学生入校时间',
  `student_teacher` VARCHAR ( 255 ) NULL COMMENT '学生所属老师',
  PRIMARY KEY ( `student_id` ) 
) COMMENT = '学生表';

表数据插入

-- 添加student表数据
INSERT INTO `student` VALUES (1, '张三', 3, '2024-09-01 00:00:00', '王老师');
INSERT INTO `student` VALUES (2, '李四', 3, '2023-09-01 00:00:00', '李老师');
INSERT INTO `student` VALUES (3, '王五', 4, '2022-09-01 00:00:00', '付老师');
INSERT INTO `student` VALUES (4, '老九', 5, '2021-09-01 00:00:00', '王老师');
INSERT INTO `student` VALUES (5, '六六', 10, NULL, '李老师');
INSERT INTO `student` (`student_id`,`student_age`) VALUES (7,4);

表修改

修改表名

-- 修改表名
RENAME TABLE wjdb.student TO wjdb.students;

修改表数据

update student set student_name = '张三三' where student_id = '1';

表注释

-- 给表添加或修改注释
ALTER TABLE wjdb.student COMMENT='学生表';

修改表约束

外键

外键（Foreign Key）是一种数据库约束，用于维护数据库中表之间的关系。
外键是某个表中的一列或一组列，它们被用于引用另一个表中的主键列。
确定两个表的关系，通常是“一对多”关系。
在“多”的那一方表中创建外键列。

创建有外键的表

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `teacher_id` int NOT NULL COMMENT '老师id',
  `teacher_name` varchar(255) NULL COMMENT '老师姓名',
  PRIMARY KEY (`teacher_id`)
)COMMENT='老师表';

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `student_id` int NOT NULL COMMENT '学生id',
  `student_name` varchar(255) NULL COMMENT '学生姓名',
  PRIMARY KEY (`student_id`),
  CONSTRAINT `学生表的外键` FOREIGN KEY (`student_id`) REFERENCES `teacher` (`teacher_id`)
)COMMENT='学生表';

修改添加外键

-- 先给需要添加的新外键添加index
ALTER TABLE `wjdb`.`teacher`ADD INDEX(`teacher_name`);
-- 删除原来的外键
ALTER TABLE `wjdb`.`student` DROP FOREIGN KEY `学生表的外键`;
-- 修改外键
ALTER TABLE `wjdb`.`student` 
ADD CONSTRAINT `学生表的外键` FOREIGN KEY (`student_id`) REFERENCES `wjdb`.`teacher` (`teacher_name`) ON DELETE RESTRICT ON UPDATE RESTRICT;

主键约束（PRIMARY KEY）：确保某列或多列的组合在表中的唯一性。 一个表只能有一个
唯一约束（UNIQUE）：确保某列或多列的组合在表中的唯一性，但允许有空值。
非空约束（NOT NULL）：确保某列的值不能为空。
默认约束（DEFAULT）：为某列设置默认值。
外键约束（FOREIGN KEY）：确保某列或多列的值与另一个表的主键或唯一键相匹配。
检查约束（CHECK）：确保某列的值满足指定的条件。
自动递增约束（AUTO_INCREMENT）：确保某列的值自动递增。

-- 添加主键约束
alter table wjdb.xuesheng add primary key (id)
-- 删除主键约束
ALTER table wjdb.xuesheng drop primary key

-- 添加唯一约束
alter table wjdb.xuesheng add unique(name)
-- 删除唯一约束
ALTER table wjdb.xuesheng drop INDEX name

-- 添加非空约束
alter table wjdb.xuesheng modify nianji varchar(255) not null 
-- 删除非空约束
alter table wjdb.xuesheng modify nianji varchar(255) null

-- 添加自动增长 
alter table wjdb.xuesheng  modify id int  auto_increment
-- 删除自动增长
alter table wjdb.xuesheng modify id int  

-- 添加默认值
alter table wjdb.xuesheng alter nianji  set default '五年级'
-- 删除默认值
alter table wjdb.xuesheng alter nianji  drop default

--添加外键

-- 删除外键
如果想删除表或者更新数据，但是又有外键可以临时关闭在打开
禁用外键约束
SET FOREIGN_KEY_CHECKS = 0;
删除完成后再启动外键约束
SET FOREIGN_KEY_CHECKS = 1; 
这样设置只会影响当前会话， 不会影响全局
如果全局想用可以这样设置
SET GLOBAL FOREIGN_KEY_CHECKS = 0; 
-- 查询是否开启了外键限制 值为1说明开启了限制
mysql> SELECT  @@FOREIGN_KEY_CHECKS;
+----------------------+
| @@FOREIGN_KEY_CHECKS |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

添加列名

-- 添加列，必须指定列的属性
ALTER TABLE wjdb.student ADD teacher varchar(100) DEFAULT 王老师 NOT NULL COMMENT '学生所属老师';

修改列名

-- 5.7版本可用 必须指定字段类型
alter table wjdb.student change column nianji banji varchar(255)
-- 8.0版本可用 age 修改为ages
ALTER TABLE wjdb.student CHANGE age ages int NOT NULL COMMENT '学生年龄';

修改列注释

alter table wjdb.student modify column name VARCHAR(255) comment '学生姓名1';

删除列

-- 删除列 删除school列
ALTER TABLE wjdb.student DROP COLUMN school;

表查询

查询表结构及注释

--查询表结构及注释
-- 方式1：默认
desc wjdb.student;
-- 方式2:还会显示表具有的权限以及表是否有注释
show full columns from wjdb.student;
-- 方式2：还会显示表是否有注释
SHOW CREATE TABLE wjdb.student;
-- 方式3：查看表注释
   先切换数据库
   use information_schema;
   完整表数据(表的创建时间，表的字符集,表注释等数据)
   select * from TABLES where TABLE_SCHEMA='wjdb' and TABLE_NAME='student';
   简写
   select table_comment from TABLES where TABLE_SCHEMA='wjdb' and TABLE_NAME='student';

查询表的所有信息

-- 查询表的所有信息
select * from student

查询某个字段信息

-- 查询某个字段信息
select name from student where id = 1

限制查询条数

select * from student LIMIT 2

格式化输出

-- 时间转字符串 DATE_FORMAT 2024-09-01
SELECT student_id,student_name ,DATE_FORMAT(student_time, '%Y-%m-%d') AS "入学时间" FROM student;

--字符串转时间 STR_TO_DATE  '%Y-%m-%d %H:%i:%s'
SELECT STR_TO_DATE('2023-10-05 14:30:00', '%Y-%m-%d %H:%i:%s') AS converted_date;

模糊查询

查询所有包含字符串nignx的，可以出现在任何位置

select * from wp_links  WHERE 列名 like '%nginx%'

包含mr的数据，mr开头

where 列名 like 'mr%'

其它案例

--包含mr的数据，mr结尾
where 列名 like '%mr'

--包含前两个字符为mr的数据，后一个字符为任意字符
where 列名 like 'mr_'

--前面为任意字符，mr结尾
where 列名 like '_mr'

--以m或者r开头
where 列名 like '[mr]%'

--查询某列中以a-e之间的字符开头的数据，其中包a和e
where 列名 like '[a-e]%'

----查询某列中不以m或 r之间的字符开头的数据
where 列名 like '[^mr]%'

--包含mr的数据，mr结尾
where 列名 like '%mr'

--不以a-e之间开头的
where 列名 like '[^a-e]%'

时间查询

实例1：查询某个时间段的数据，默认排序方式是（旧新）在选择用某个字段进行分组显示排序查询

ASC表示升序（默认），DESC表示降序 order by 进行分组

select * from file_record where create_time >= '2024-05-01' and  create_time <= '2024-05-31'
新旧 使用order by create_time desc 在选择用某个字段进行分组显示
select * from file_record where create_time >= '2024-05-01' and  create_time <= '2024-05-31' order by create_time desc

案例2：查询某个时间段的数据，然后在查询某个字段在此时间的数据用and连接即可

select * from file_record where create_time >= '2024-05-01' and  create_time <= '2024-05-31' and user_name = '小明哥'

案例3:查询某个时间段的数据，然后在查询多个字段在此时间的数据用in连接

select * from file_record where create_time >= '2024-05-01' and  create_time <= '2024-05-31' and user_name in（'小明哥','小李哥','小张哥'）

案例:排除空值用is not null，在使用子查询

select * from (select * from student s where s.student_time is not null )student ORDER BY student_time

条件查询

查询users表中年龄大于18岁的用户：

SELECT * FROM users WHERE age > 18;

查询年龄大于18岁且性别为男的yongh

SELECT * FROM users WHERE age > 18 AND gender = 'male';

聚合函数查询

COUNT：统计行数

SELECT COUNT(*) FROM your_table;

SUM：求和

SELECT SUM(column1) FROM your_table;

AVG：求平均值

SELECT AVG(column1) FROM your_table;

MAX和MIN：求最大值和最小值

SELECT MAX(age) FROM users; 
SELECT MIN(age) FROM users;

分组查询

使用GROUP BY子句按照性别分组统计用户数量

SELECT gender, COUNT(*) FROM users GROUP BY gender;

连接查询

练习sql如下：

create table `students`(
    `id` int COMMENT '学生编号,主键',
    `name` VARCHAR ( 255 ) NULL COMMENT '学生姓名',
    `class_id` int COMMENT '班级编号',
    PRIMARY KEY ( `id` ) 
)COMMENT = '学生表'

create table `classes`(
    `id` int COMMENT '班级编号,主键',
    `class_name` VARCHAR ( 255 ) NULL COMMENT '班级名称',
    PRIMARY KEY ( `id` ) 
)COMMENT = '班级表'

insert into `students` values (1,'小王',1) ;
insert into `students` values (2,'小李',2) ;
insert into `students` (`id`,`name`) values (3,"小j") ;
insert into `students` values (4,'小11',4) ;
insert into `students` values (5,'小12',2) ;

insert into `classes` values(1,'一班'); 
insert into `classes` values(2,'二班') ;
insert into `classes` values(3,'三班') ;
insert into `classes` values(4,'四班') ;
insert into `classes` values(5,'五班') ;

内连接（INNER JOIN）只返回两个表中相关联的部分并集

查询学生及其所在班级的信息，有的学生可能还未分班级

mysql> select students.name,classes.class_name from students  inner join classes on students.class_id = classes.id ;
+--------+------------+
| name   | class_name |
+--------+------------+
| 小王   | 一班       |
| 小李   | 二班       |
| 小11   | 四班       |
| 小12   | 二班       |
+--------+------------+
4 rows in set (0.00 sec)

左连接（LEFT JOIN）：会返回左表（table1）中的所有记录，即使右表（table2）中没有匹配的记录。

查询所有学生及其所在班级的信息，如果某个学生没有对应的班级（可能数据不完整），也要显示该学生的信息

mysql> select students.name,classes.class_name from students left join classes on students.class_id = classes.id ;
+--------+------------+
| name   | class_name |
+--------+------------+
| 小王   | 一班       |
| 小李   | 二班       |
| 小j    | NULL       |
| 小11   | 四班       |
| 小12   | 二班       |
+--------+------------+
5 rows in set (0.00 sec)

右连接（RIGHT JOIN）：与左连接相反，会返回右表中的所有记录，即使左表中没有匹配的记录。

现在假设我们想要查询所有班级以及班级中的学生信息，如果某个班级没有学生（可能是新创建的班级），也要显示该班级的信息。

mysql> select students.name,classes.class_name from students right join classes on students.class_id = classes.id ;
+--------+------------+
| name   | class_name |
+--------+------------+
| 小王   | 一班       |
| 小李   | 二班       |
| 小11   | 四班       |
| 小12   | 二班       |
| NULL   | 三班       |
| NULL   | 五班       |
+--------+------------+
6 rows in set (0.00 sec)

全外连接（FULL OUTER JOIN或FULL JOIN）myql中没有这个语句，但是可以用其他方式

首先使用LEFT JOIN获取table1中的所有行以及table2中与之匹配的行。
然后使用UNION操作符与RIGHT JOIN的结果合并。在RIGHT JOIN部分，使用WHERE子句排除了已经在LEFT JOIN中出现过的重复行（即table1和table2中id能够匹配的行），从而模拟了全外连接的效果。

-- 使用LEFT JOIN并选择所有列
select * from students s left join classes c on s.id = c.id 
union
-- 使用RIGHT JOIN并选择所有列，排除已经在LEFT JOIN中出现过的重复行 
select * from students s right join classes c on s.id = c.id where s.id is null

mysql> select * from students s left join classes c on s.id = c.id union select * from students s right join classes c on s.id = c.id where s.id is null;
+------+--------+----------+------+------------+
| id   | name   | class_id | id   | class_name |
+------+--------+----------+------+------------+
|    1 | 小王   |        1 |    1 | 一班       |
|    2 | 小李   |        2 |    2 | 二班       |
|    3 | 小j    |     NULL |    3 | 三班       |
|    4 | 小11   |        4 |    4 | 四班       |
|    5 | 小12   |        2 |    5 | 五班       |
+------+--------+----------+------+------------+
5 rows in set (0.00 sec)

去重

mysql> select distinct class_id from students;
+----------+
| class_id |
+----------+
|        1 |
|        2 |
|     NULL |
|        4 |
+----------+
4 rows in set (0.00 sec)

子查询

先查询年龄大于18岁的用户，再从这些用户中查询name列：

SELECT name FROM (SELECT * FROM users WHERE age > 18) AS subquery;

表索引

索引占空间大小

use  information_schema;
select concat(round(sum(index_length)/(1024*1024),2),'MB') as 'DB Size' from tables where table_schema='wordpress';

文章版权归作者所有，未经允许请勿转载。

THE END

mysql
# mysql