MySql语句整理

最新推荐文章于 2022-07-18 15:51:45 发布

wys5

最新推荐文章于 2022-07-18 15:51:45 发布

阅读量446

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/wys5wys/article/details/103008908

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

6.左关联、右关联与自关联(left join on、right join on、inner join on)

7.分组(GROUP BY)

8.触发器(trigger)

9.数据库三大删除操作

一.数据库创建、删除与选择

1.先连接数据库服务再创建数据库

mysql> CREATE DATABASE 数据库名;

2.使用mysqladmin命令来创建数据库

C:\Users>mysqladmin -u root -p create 数据库名
Enter password: ****

3.删除数据库。先连接数据库服务，然后执行drop database 数据库名；

mysql> drop database 数据库名;

4.选择数据库。先连接数据库，然后使用指令use 数据库名，即可更换到指定的数据库下

C:\Users>mysql -u root -p
Enter password:******
mysql> use 数据库名;
Database changed
mysql>

二.数据表创建与删除

以下举例都是针对数据库test

创建表user_01、user_02

USE test;
CREATE TABLE IF NOT EXISTS user_01(
   user_id INT UNSIGNED AUTO_INCREMENT,
   user_name VARCHAR(100) NOT NULL,
   user_sex VARCHAR(40) NOT NULL,
   PRIMARY KEY ( user_id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS user_02(
   user_num INT UNSIGNED AUTO_INCREMENT,
   user_name VARCHAR(100) NOT NULL,
   user_sex VARCHAR(40) NOT NULL,
   PRIMARY KEY ( user_num )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除表user_02

DROP TABLE user_02;

三.数据表数据增删改查

针对上一模块的user_01进行数据的增删改查，使用图形化界面工具MySql Workbench

1.新增表格数据

insert into user_01(user_id,user_name,user_sex)values(0001,"王一","男");
insert into user_01(user_id,user_name,user_sex)values(0010,"孙二","男");
insert into user_01(user_id,user_name,user_sex)values(0011,"张三","男");
insert into user_01(user_id,user_name,user_sex)values(0100,"丽丽","女");
insert into user_01(user_id,user_name,user_sex)values(0101,"婷婷","女");
insert into user_01(user_id,user_name,user_sex)values(0008,"娇娇","女");

2.查询当前表中全部字段内容

SELECT * FROM test.user_01;

3.查询部分内容

SELECT 列名1,列名2... FROM 表名;

SELECT user_name FROM test.user_01;

4.根据条件查询

select * from user_01 where user_id>10;

select * from user_01 where user_sex="女";

5.联合查询

先再创建一个表user_02

CREATE TABLE IF NOT EXISTS user_02(
   user_id INT UNSIGNED AUTO_INCREMENT,
   user_name VARCHAR(100) NOT NULL,
   user_grade INT,
   user_class varchar(40),
   PRIMARY KEY ( user_id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

注入数据

insert into user_02(user_id,user_name,user_grade,user_class)values(0001,"王一",81,"一班");
insert into user_02(user_id,user_name,user_grade,user_class)values(0010,"孙二",56,"二班");
insert into user_02(user_id,user_name,user_grade,user_class)values(0011,"张六",71,"二班");

查询两个表中user_id相同的人(未去除重复项)

select * from user_01,user_02 where user_01.user_id = user_02.user_id;

去除重复项（暂时只知道直接提取字段输出）

select user_01.user_id,user_01.user_name,user_01.user_sex,user_02.user_grade,user_02.user_class 
from user_01,user_02 
where user_01.user_id = user_02.user_id and user_01.user_name = user_02.user_name;

6.数据筛选：union查询

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

普通查询：select user_01.user_name from user_01,user_02;（左下）

union查询：SELECT user_name FROM user_01 union select user_name from user_02; （右上）

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）

UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

使用形式如下：

SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称；
SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称；

ORDER BY 列名称为根据该列名排序，注意前两个列名相同

7.修改/更新表格数据

update user_01 set user_id = 555 where user_id = 1;

8.删除表格数据对应项

删除user_02中成绩不及格的人

delete from user_02 where user_grade < 60;

如果遇到非主键不能删除的情况，执行命令SET SQL_SAFE_UPDATES = 0;修改下数据库模式。

四.其他操作

1.排序

在user_02中再次添加一些数据

insert into user_02 (user_id,user_name,user_grade,user_class) values(123,"李二",59,"三班");
insert into user_02 (user_id,user_name,user_grade,user_class) values(143,"张三",75,"二班");
insert into user_02 (user_id,user_name,user_grade,user_class) values(183,"王五",100,"一班");
insert into user_02 (user_id,user_name,user_grade,user_class) values(523,"田七",97,"三班");
insert into user_02 (user_id,user_name,user_grade,user_class) values(4773,"老九",23,"二班");

根据user_grade排序

升序

SELECT * from user_02 ORDER BY user_grade ASC;

降序

SELECT * from user_02 ORDER BY user_grade DESC;

2.LIKE

LIKE在查询过程中可以起到正则表达式的作用，SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *

例子：在user_02查询名字中姓张的用户

SELECT * from user_02 where user_name like '张%';

3.正则表达式

图文出处：https://www.runoob.com/mysql/mysql-regexp.html

模式	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
.	匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。
[...]	字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...]	负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
p1\|p2\|p3	匹配 p1 或 p2 或 p3。例如，'z\|food' 能匹配 "z" 或 "food"。'(z\|f)ood' 则匹配 "zood" 或 "food"。
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n}	n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

实例(表名：user )：

查找name字段中以'st'为开头的所有数据：

SELECT name FROM user WHERE name REGEXP '^st';

查找name字段中以'ok'为结尾的所有数据：

SELECT name FROM user WHERE name REGEXP 'ok$';

查找name字段中包含'mar'字符串的所有数据：

SELECT name FROM user WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：

SELECT name FROM user WHERE name REGEXP '^[aeiou]|ok$';

查找name字段中名字中带有零个或多个a字母的所有数据：

SELECT name FROM user WHERE name REGEXP 'a*';

查找name字段中名字中带有一个或多个a字母的所有数据：

SELECT name FROM user WHERE name REGEXP 'a+';

查找name字段中名字中带有指定范围[2,4]个数a字母的所有数据：

SELECT name FROM user WHERE name REGEXP 'a{2,4}';

4.ALTER（修改列）

当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

user_02数据如下

删除user_class列

ALTER TABLE user_02 drop user_class;

删除多列

ALTER TABLE 表名 drop 列名1, drop 列名2......;

如果数据表中只剩余一个字段则无法使用DROP来删除字段。

添加sex列

ALTER TABLE user_02 ADD sex varchar(20);

在第一列添加

ALTER TABLE user_02 ADD kkk INT FIRST;

在user_id之后添加

ALTER TABLE user_02 ADD sss INT AFTER user_id;

为user表添加主键i

ALTER TABLE user MODIFY i INT NOT NULL;

删除主键

ALTER TABLE user ADD PRIMARY KEY (i);

5.复制表

如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。我们就需要使用以下步骤

使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
复制以上命令显示的SQL语句，修改数据表名，并执行SQL语句，但是此时新表中并没有数据。
如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现。

就我使用的MySql Workbench而言，

复制表user_01

获取建表语句

SHOW CREATE TABLE user_01;

右键选择Copy Field(unquoted)获取建表语句，更改表名为user_03

CREATE TABLE `user_03` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(100) NOT NULL,
  `user_sex` varchar(40) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8

执行上面的语句

使用insert语句导入user_01中的数据

insert into user_03(user_id,user_name,user_sex) 
select user_id,user_name,user_sex from user_01;

6.左关联、右关联与自关联(left join on、right join on、inner join on)

左关联是用左表相关数据与右表中对应数据进行对比，然后返回左表数据和右表中符合要求数据

右关联是用左表相关数据与右表中对应数据进行对比，然后返回右表数据和左表中符合要求数据

自关联是两表数据对比，然后返回符合要求的数据

现有两张表

class 表学生表

现学生表左关联class表，返回class_id相等且为1 的数据

SELECT * FROM user_01 t1 LEFT JOIN class t2 on t1.class_id = t2.class_id and t1.class_id = 1

再相同方式右关联

SELECT * FROM user_01 t1 RIGHT JOIN class t2 on t1.class_id = t2.class_id and t1.class_id = 1

相同方式自关联

SELECT * FROM user_01 t1 inner join class t2 on t1.class_id = t2.class_id and t1.class_id = 1

7.分组(GROUP BY)

出自菜鸟教程 https://www.runoob.com/mysql/mysql-group-by-statement.html

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

GROUP BY 语法

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

实例演示

本章节实例使用到了以下表结构及数据，使用前我们可以先将以下数据导入数据库中。

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `employee_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `employee_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

导入成功后，执行以下 SQL 语句：

mysql> set names utf8;
mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | singin |
+----+--------+---------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
|  4 | 小王 | 2016-04-07 15:26:14 |      4 |
|  5 | 小明 | 2016-04-11 15:26:40 |      4 |
|  6 | 小明 | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)

接下来我们使用 GROUP BY 语句将数据表按名字进行分组，并统计每个人有多少条记录：

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+
3 rows in set (0.01 sec)

使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL   |           16 |
+--------+--------------+
4 rows in set (0.00 sec)

其中记录 NULL 表示所有人的登录次数。

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);

参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

以下实例中如果名字为空我们使用总数代替：

mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)

8.触发器(trigger)

基本的语法规则是：
create trigger 触发器名称 on 表 for 用途(delete|update|insert)
as
delete|update|insert语句
----------------------------------------------------------------------
例如：
create trigger tri_A on tab_1 FOR DELETE
AS
delete tab_2 from deleted where tab_2.id = deleted.id;

意思是：在tab_1表上，建立触发器（tri_A），用于删除该表的数据时触发一个事务，什么事务呢？——删除tab_2中的该编号人员的记录。

9.数据库三大删除操作

1.DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录
在日志中保存以便进行进行回滚操作。

DELETE     FROM      表名    WHERE    条件表达式

2.TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，
删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
TRUNCATE 只能对TABLE；DELETE可以是table和view

3.DROP则删除整个表（结构和数据）。

TRUNCATE 和DELETE只删除数据、表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

wys5

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录