SQL练习

• MySQL常见报错
• DDL和DML补充

SQL练习

MySQL常见报错

1055 报错

/*
将SQL_mode中的only_full_group_by 删除，MySQL重启后会失效
*/

-- 查看SQL_mode
select @@global.sql_mode
-- 默认的值为ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

-- 修改SQL_mode 
set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

练习一：创建学生信息表

DML 修改表数据

##这是一个单行注释
/*
多行注释
多行注释
多行注释
*/
/*
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
*/
-- 创建数据库：
    -- 库名：excepenxi
    -- 字符集：utf8mb4

-- 创建数据库表：
CREATE TABLE t_student(
    sno INT(6), -- 6显示长度
    sname VARCHAR(5), -- 5个字符
    sex CHAR(1),
    age INT(3),
    enterdate date,
    classname VARCHAR(10),
    email VARCHAR(15)
);

-- 查看表的结构：展示表的字段详细信息
DESC t_student;

-- 查看表中数据：
SELECT * FROM t_student;

-- 查看建表语句：
SHOW CREATE TABLE t_student;
/*
CREATE TABLE `t_student` (
  `sno` int DEFAULT NULL,
  `sname` varchar(5) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `enterdate` date DEFAULT NULL,
  `classname` varchar(10) DEFAULT NULL,
  `email` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

-- 查看表记录：
select * from t_student;

-- 在t_student数据库表中插入数据：
INSERT INTO t_student VALUES(1,'修','男',18,'2022-05-01','1班','penxi@gamil.com');
INSERT INTO t_student VALUES(2,"exce",'男',18,'2022.05.01','1班','penxi@gamil.com');
INSERT INTO t_student (sno,sname,enterdate) VALUES(3,'penxi','2022/05/01');

-- 修改表中数据
UPDATE t_student set sex='女';
UPDATE t_student set sex='男' WHERE sno=1;

-- 删除操作：
DELETE FROM t_student WHERE sno=2;

DDL 修改表

-- 查看表记录：
SELECT * FROM t_student;

-- 修改表的结构
-- 增加一列：score 学生成绩
ALTER TABLE t_student ADD score DOUBLE(5,2); -- 5:总位数  2：小数位数 
UPDATE t_student SET score = 123.5678 WHERE sno = 1 ;  -- 添加数据
-- 增加一列（放在最前面）
ALTER TABLE t_student ADD score DOUBLE(5,2) first;
-- 增加一列（放在sex列的后面）
ALTER TABLE t_student ADD score DOUBLE(5,2) AFTER sex;
-- 删除一列：
ALTER TABLE t_student drop score;

-- 修改一列：
ALTER TABLE t_student MODIFY score FLOAT(4,1); 
-- modify修改是列的类型的定义，但是不会改变列的名字
ALTER TABLE t_student change score score1 double(5,1); 
-- change修改列名和列的类型的定义

-- 删除表：
DROP TABLE t_student;

DDL和DML补充

-- 学生表删除,班级表删除：
drop table t_student;

-- 创建表：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        sex char(1) default '男' check(sex='男' || sex='女'),
        age int(3) check(age>=18 and age<=50),
        enterdate date,
        classname varchar(10),
        email varchar(15) unique
);

-- 添加数据：
insert into t_student values (null,'张三','男',21,'2023-9-1','java01班','zs@126.com');
insert into t_student values (null,'李四','男',21,'2023-9-1','java01班','ls@126.com');
insert into t_student values (null,'露露','男',21,'2023-9-1','java01班','ll@126.com');

-- 查看学生表：
select * from t_student;

-- 添加一张表：快速添加：结构和数据跟t_student 都是一致的
create table t_student2
as
select * from t_student;
select * from t_student2;

-- 快速添加，结构跟t_student一致，数据没有：
create table t_student3
as
select * from t_student where 1=2;
select * from t_student3;

-- 快速添加：只要部分列，部分数据：
create table t_student4
as
select sno,sname,age from t_student where sno = 2;

select * from t_student4;

-- 删除数据操作 :清空数据
delete from t_student;
truncate table t_student;

delete和truncate的区别

从最终的结果来看，虽然使用TRUNCATE操作和使用DELETE操作都可以删除表中的全部记录，但是两者还是有很多区别的，其区别主要体现在以下几个方面：

1. DELETE为数据操作语言DML；TRUNCATE为数据定义语言DDL
2. DELETE操作是将表中所有记录一条一条删除直到删除完；TRUNCATE操作则是保留了表的结构，重新创建了这个表，所有的状态都相当于新表。因此，TRUNCATE操作的效率更高
3. DELETE操作可以回滚；TRUNCATE操作会导致隐式提交，因此不能回滚
4. DELETE操作执行成功后会返回已删除的行数（如删除4行记录，则会显示“Affected rows：4”）；截断操作不会返回已删除的行量，结果通常是“Affected rows：0”。DELETE操作删除表中记录后，再次向表中添加新记录时，对于设置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增；TRUNCATE操作则会重新从1开始自增。