数据库操控指南：玩转数据

大家好，欢迎来到IT知识分享网。

数据的操作——DML语句（增删改）

1.插入数据

方法一：

​ 为表的一行所有字段（列）插入数据

INSERT INTO 表名 VALUES (VALUES2, VALUES3……); 

笼统的插入行数据时，总需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

方法二：

​ 为表的一行指定字段（列）插入数据

INSERT INTO 表名(列名1, 列名2, ……) VALUES (value1, value2, ……)……(value1, value2……); 

特殊的插入行数据时，需要为表后指定的列指定值，并且值的顺序和类型必须和指定的列顺序相同。

方法三：

​ 同时插入多条记录

INSERT INTO 表名 VALUES(value1, value2……); # 或者 INSERT INTO 表名(列名1, 列名2, ……) VALUES (value1, value2, ……)……(value1, value2……); 

注：

对于方法一和方法二而言，只需要在values后面写多个值即可，并且（）代表的是一行。

示例：

CREATE TABLE students ( stu_id INT COMMENT '学号', stu_name VARCHAR(100) COMMENT '姓名', stu_age TINYINT UNSIGNED COMMENT '年龄', stu_birthday DATE COMMENT '生日', stu_height DECIMAL(4, 1) DEFAULT 200 COMMENT '身高，保留以为小数' ); SHOW TABLES; -- 使用 SELECT 语句查看表的数据 SELECT * FROM students; USE ddl_d1; -- 插入数据 INSERT INTO students (stu_id, stu_name, stu_age, stu_birthday, stu_height) VALUES (1, '二狗子', 20, '2000-01-05', 185.5); INSERT INTO students (stu_name, stu_age, stu_birthday, stu_height, stu_id) VALUES ('二狗子', 20, '2000-01-05', 185.5, 2); 

2.修改数据

方式一：

​ 修改表中所有行数据，即全表修改

# 这是一行指令，分开写只是方便阅读，同时分开写这种形式不会影响sql编译 UPDATE table_name SET column = value, column2 = value2,……,column = value; 

更新表总所有行指定的列数据。

方式二：

​ 修改表中符合条件的数据，即条件修改

UPDATE table_name SET column1 = value1, column2 = value2,……, column = vlaue [WHERE condition]; 

条件修改只是在后面添加where，where后面指定相关的条件即可。

示例：

USE ddl_d1; -- 插入数据 INSERT INTO students (stu_id, stu_name, stu_age, stu_birthday, stu_height) VALUES (6, '张三', 20, '2000-01-05', 185.5), (7, '李四', 45, '2000-01-05', 185.5), (8, '王五', 40, '2000-01-05', 185.5), (9, '赵六', 30, '2000-01-05', 185.5), (10, '钱七', 80, '2000-01-05', 185.5), (11, '孙八', 21, '2000-01-05', 185.5), (12, '周九', 13, '2000-01-05', 185.5), (13, '吴十', 15, '2000-01-05', 185.5), (14, '郑十一', 18, '2000-01-05', 185.5), (15, '王狗子', 56, '2000-01-05', 185.5) -- 按 stu_id 升序排序 SELECT * FROM students ORDER BY stu_id ASC; # 将id为1的学生的名字改为赵一 UPDATE students SET stu_name = '赵一' WHERE stu_id = 1; # 将所有年龄小于20的学生的升高减20 UPDATE students SET stu_height = stu_height - 20 WHERE stu_age < 20; # 让所有学生大一岁 UPDATE students SET stu_age = stu_age + 1; 

3.数据删除

方法一：

​ 删除表中的所有数据，即全表删除，但不删除表的记录，只清理数据。

DELETE FROM table_name; 

方法二：

​ 删除表中符合条件行的数据，即条件删除。

DELETE FROM talbe_name [WHERE condition]; 

示例：

# 删除年龄大于40且身高小于170的 DELETE FROM students WHERE stu_age > 40 AND stu_height > 170; # 清空表数据 DELETE FROM students; 

数据的查询——DQL语句

1.原理：

多表查询：

​ 多张数据表 ——> 合并语法 ——> 中间虚拟表 ——> 查询语法 ——> 虚拟表

2.查看表结构

DESCRIBE table_names; 或 DESC table_names; 

3.条件查询

SELECT 字段你1,字段2 FROM 表名 WHERE 过滤条件; 

这种方法是先过滤条件，然后再把符合的返回。

4.基础的SELECT语法

方法一：

​ 非表查询

SELECT 1; SELECT 9/2; SELECT VERSION; # sql的版本 

类似java的输出，直接在控制台输出。

方法二：

​ 指定表

SELECT 列名1, 列名2, 列名3 FROM 表名; 或者 SELECT * FROM 表名; 

方法三：

​ 查询列起别名

SELECT 列名1 as 别名1, 列名2, 列名3 as 别名3 FROM 表名; 或者 SELECT 列名1 别名1,列名2， 列名3 别名3 FROM 表名; 

起别名这种方式是为了让java在读取数据库时，当数据库的字段与Java的对象名不一致时，采用对数据库的字段起别名的方式来匹配java的对象。

方法四：

​ 去除重复行

SELECT DISTINCT 别名 [列名,列名]FROM 表名; 

指定删除重复行，可以指定单行或者多行

方法五：

​ 查询常数

SELECT '尚硅谷' as corporation, 列名, 列名……FROM表名 

这一部分的意思是选择一个字符串常量”尚硅谷”并将其命名为”corporation”。这意味着结果集中将会有一列，其所有行的值都将是”尚硅谷”。

CREATE DATABASE IF NOT EXISTS test04_dql; USE test04_dql; -- 创建员工表 DROP TABLE IF EXISTS `t_employee`; CREATE TABLE `t_employee` ( `eid` INT NOT NULL COMMENT '员工编号', `ename` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工姓名', `salary` DOUBLE NOT NULL COMMENT '薪资', `commission_pct` DECIMAL(3,2) DEFAULT NULL COMMENT '奖金比例', `birthday` DATE NOT NULL COMMENT '出生日期', `gender` ENUM('男','女') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '男' COMMENT '性别', `tel` CHAR(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '手机号码', `email` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '邮箱', `address` VARCHAR(150) DEFAULT NULL COMMENT '地址', `work_place` SET('北京','深圳','上海','武汉') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '北京' COMMENT '工作地点' ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO `t_employee`(`eid`,`ename`,`salary`,`commission_pct`,`birthday`,`gender`,`tel`,`email`,`address`,`work_place`) VALUES (1,'孙洪亮',28000,'0.65','1980-10-08','男','','','白庙村西街','北京,深圳'), (2,'何进',7001,'0.10','1984-08-03','男','','','半截塔存','深圳,上海'), (3,'邓超远',8000,NULL,'1985-04-09','男','','','宏福苑','北京,深圳,上海,武汉'), (4,'黄熙萌',9456,NULL,'1986-09-07','女','','','白庙村东街','深圳,上海,武汉'), (5,'陈浩',8567,NULL,'1978-08-02','男','','','回龙观','北京,深圳,上海'), (6,'韩庚年',12000,NULL,'1985-04-03','男','','','龙泽','深圳,上海'), (7,'贾宝玉',15700,'0.24','1982-08-02','男','','','霍营','北京,武汉'), (8,'李晨熙',9000,'0.40','1983-03-02','女','','','东三旗','深圳,上海,武汉'), (9,'李易峰',7897,NULL,'1984-09-01','男','','','西山旗','武汉'), (10,'陆风',8789,NULL,'1989-04-02','男','','','天通苑一区','北京'), (11,'黄冰茹',15678,NULL,'1983-05-07','女','','','立水桥','深圳'), (12,'孙红梅',9000,NULL,'1986-04-02','女','','','立城苑','上海'), (13,'李冰冰',18760,NULL,'1987-04-09','女','','','王府温馨公寓','北京'), (14,'谢吉娜',18978,'0.25','1990-01-01','女','','','园中园','上海,武汉'), (15,'董吉祥',8978,NULL,'1987-05-05','男','','','小辛庄','北京,上海'), (16,'彭超越',9878,NULL,'1988-03-06','男','','','西二旗','深圳,武汉'), (17,'李诗雨',9000,NULL,'1990-08-09','女','','','清河','北京,深圳,武汉'), (18,'舒淇格',16788,'0.10','1978-09-04','女','','','名流花园','北京,深圳,武汉'), (19,'周旭飞',7876,NULL,'1988-06-13','女','','','小汤山','北京,深圳'), (20,'章嘉怡',15099,'0.10','1989-12-11','女','','','望都家园','北京'), (21,'白露',9787,NULL,'1989-09-04','女','','','西湖新村','上海'), (22,'刘烨',13099,'0.32','1990-11-09','男','','','多彩公寓','北京,上海'), (23,'陈纲',13090,NULL,'1990-02-04','男','','','天通苑二区','深圳'), (24,'吉日格勒',10289,NULL,'1990-04-01','男','','','北苑','北京'), (25,'额日古那',9087,NULL,'1989-08-01','女','','','望京','北京,上海'), (26,'李红',5000,NULL,'1995-02-15','女','','','冠雅苑','北京'), (27,'周洲',8000,NULL,'1990-01-01','男','','','冠华苑','北京,深圳'); 

开始操作数据：

# 查询表的全部信息 SELECT * FROM t_employee; # 按 eid 升序排序 SELECT * FROM t_employee ORDER BY eid ASC; # 查询员工的姓名与工资 SELECT ename, salary FROM t_employee; # 查询时给列起别名 SELECT ename AS NAME,salary FROM t_employee; SELECT ename NAME,salary FROM t_employee; SELECT ename `name`,salary FROM t_employee; # 查询员工的性别（去重查找） SELECT DISTINCT gender FROM t_employee; # 查询常数列(人为的创建列常数列） SELECT ename NAME,salary, '尚硅谷' company FROM t_employee; # 查询所有员工姓名，月薪和年薪（年薪等于月薪*12,结果列字段为 姓名 , 月薪 ， 年薪 ） SELECT ename 姓名,salary 月薪 , salary * 12 AS 年薪 FROM t_employee; # 查询所有员工姓名，月薪，每月奖金，每月总收入（结果列字段为 姓名 , 月薪 ， 奖金，月总 ） SELECT ename 姓名 , salary 月薪 , salary * commission_pct 奖金 , salary + salary * commission_pct 月总收入 FROM t_employee; SELECT ename 姓名 , salary 月薪 , salary * IFNULL(commission_pct,0) 奖金 , salary + salary * IFNULL(commission_pct,0) 月总收入 FROM t_employee; # 因为,有些员工没有奖金, 奖金占比就是null, null 运算 任何值 = null # ifnull(列,为null你给与的默认值) 0