• 数据库第二天 DML操作 DQL查询

    DML 数据操作语言

    DML:对表中数据进行操作的语言，涵盖的内容为:INSERT(增),DELETE(删),UPDATE(改)

    准备一张表:

    1. CREATE TABLE person(
    2. 	name VARCHAR(30),
    3. 	age INT(3)
    4. )

    插入数据(INSERT)

    语法:

    INSERT INTO 表名[(字段1,字段2,...)] VALUES(字段的1值,字段2的值,...)

    注:在语法定义上"[]"中的内容表示可写可不写

    例

    1. INSERT INTO person(name,age) VALUES('张三',25)
    2. INSERT INTO person(age,name) VALUES(33,'李四')

    注:

    • 数据库中字符串的字面量是使用单引号''表达的

    • VALUES中指定的值要与指定的字段名个数,顺序,以及类型完全一致

    • 查看表中数据

      SELECT * FROM person

    插入默认值

    当插入数据时不指定某个字段，那么该字段插入默认值。若创建表时字段没有显示的指定默认值时，默认值插入NULL

    例:

    INSERT INTO person (name) VALUES ('王五')

    注:

    • age字段没有指定，因此插入默认值NULL

    • 数据库中任何字段任何类型默认值都是NULL，当然可以在创建表时使用DEFAULT指定。

      1. 修改person表中age的默认值为20
      2. ALTER TABLE person CHANGE age age INT(3) DEFAULT 20
      3.  
      4. 修改后向person表中插入数据
      5. INSERT INTO person (name) VALUES ('赵六')
      6.  
      7.  
      8. 提示:也可在创建person表时为字段指定默认值
      9. CREATE TABLE person(
      10. 	name VARCHAR(30) DEFAULT '无名氏',
      11.     age INT(3) DEFAULT 20
      12. )

      为age添加默认值20:

      插入'赵六'

    ​

    全列插入

    当我们插入数据是不指定任何字段名时，就是全列插入。此时VALUES子句中需要为每个字段指定值。并且要求(个数，顺序，类型必须与表完全一致)

    例:

    1. INSERT INTO person VALUES('钱七',21);
    2.  
    3. 若某个字段有默认值，可以用DEFAULT表示默认值
    4. INSERT INTO person VALUES('钱七',DEFAULT);
    5.  
    6. 若某个字段想插入NULL值，可以使用NULL表达
    7. INSERT INTO person VALUES('钱七',NULL);
    8.  
    9. 下列为错误示例
    10. INSERT INTO person VALUES('钱七');		列的值个数不匹配
    11. INSERT INTO person VALUES(27,'钱七');		列的值类型不匹配
    12.

    修改数据(UPDATE)

    语法

    UPDATE 表名 SET 字段1=新值1[,字段2=新值2,...][WHERE 过滤条件]

    例

    1. UPDATE person SET age=15
    2. 上述SQL执行后会导致person表中所有记录的age字段值都被改为15

    UPDATE语句通常都要添加WHERE子句，用于添加要修改记录的条件，否则全表修改！

    修改指定记录(添加WHERE子句)

    例

    1. 将张三的年龄改成20岁
    2. UPDATE person
    3. SET age=20
    4. WHERE name='张三'		仅将person表中某条记录name字段值为‘张三’的age改为20
    5.  
    6. 将person表中年龄为16岁的人改为36岁
    7. UPDATE person
    8. Set age=36
    9. WHERE age=16		  person表中凡是满足WHERE条件的记录都会被修改

    WHERE子句中常用的条件

    =, <, <= ,> ,>= ,<>(不等于使用<>。!=不是所有数据库都支持)

    1. 将person表中年龄大于30岁的人改为年龄29
    2. UPDATE person
    3. SET age=29
    4. WHERE age>30

    将一个计算表达式的结果作为值使用

    1. 将person表中每个人的年龄涨1岁
    2. UPDATE person
    3. SET age=age+1			将每条记录年龄的值+1后再修改回年龄字段

    同时修改多个字段

    1. 将'李四'改为'李老四'且年龄改为55岁
    2. UPDATE person
    3. SET name='李老四',age=55
    4. WHERE name='李四'

    删除语句(DELETE)

    语法:

    DELETE FROM 表名 [WHERE 过滤条件]

    注:DELETE语句通常都要添加WHERE子句，否则是清空表操作

    例:

    1. 删除'李老四'
    2. DELETE FROM person
    3. WHERE name='李老四'	仅删除person表中满足WHERE条件的记录
    4.  
    5.  
    6. 删除年龄大于等于30岁的人
    7. DELETE FROM person
    8. WHERE age>=30
    9.

    清空表操作

    DELETE FROM person

    练习:

    1. 1.创建数据库day1db 字符集utf8并使用
    2.   CREATE DATABASE day1db CHARSET=utf8
    3.   USE day1db
    4. 2.创建t_hero表, 有name字段,字符集utf8
    5.   CREATE TABLE t_hero(
    6.   	name VARCHAR(30)
    7.   )CHARSET=utf8
    8. 3.修改表名为hero
    9.   RENAME TABLE t_hero TO hero
    10. 4.最后面添加价格字段money, 最前面添加id字段, name后面添加age字段
    11.   ALTER TABLE hero ADD money INT
    12.   ALTER TABLE hero ADD id INT FIRST
    13.   ALTER TABLE hero ADD age INT AFTER name
    14.   
    15. 5.表中添加以下数据: 1,李白,50,6888 2,赵云,30,13888 3,刘备,25,6888
    16.   INSERT INTO hero(id,name,age,money) VALUES(1,'李白',50,6888)
    17.   INSERT INTO hero(id,name,age,money) VALUES(2,'赵云',30,13888)
    18.   INSERT INTO hero(id,name,age,money) VALUES(3,'刘备',25,6888)
    19. 6.修改刘备年龄为52岁
    20.   UPDATE hero
    21.   SET age=52
    22.   WHERE name='刘备'
    23. 7.修改年龄小于等于50岁的价格为5000
    24.   UPDATE hero
    25.   SET money=5000
    26.   WHERE age<=50
    27. 8.删除价格为5000的信息
    28.   DELETE FROM hero
    29.   WHERE money=5000
    30. 9.删除表, 删除数据库
    31.   DROP TABLE hero
    32.   DROP DATABASE day1db
    33.

    总结

    • DML:数据操作语言，它是对表中数据进行操作的语言，涵盖操作:增(INSERT)，删(DELETE)，改(UPDATE)

    • INSERT语句用于将数据插入表中

      • INSERT时指定的字段顺序可以与表不一致，但是VALUES子句中值的顺序要与指定顺序一致

      • INSERT时可以不指定某字段，那么此时该条记录该字段会插入默认值

      • INSERT时为某个字段插入其指定的默认值，使用关键字DEFAULT。

        字段若指定了默认值则使用该默认值，否则字段默认值为NULL。

      • INSERT时使用NULL插入一个字段为null值。

      • INSERT时可以不指定字段名，那么为全列插入，此时VALUES子句指定的值顺序，个数，类型必须与表结构完全一致。

    • UPDATE语句用于修改表中数据

    • DELETE语句用于删除表中数据

    • UPDATE语句和DELETE语句通常都要添加WHERE子句，否则是对全表所有记录操作。

    数据库常用的数据类型

    不同的数据库数据类型不完全一致

    数字类型

    整数类型:INT(m)和BIGINT(m)

    • m:整数的位数
    • int(5):最大保存一个5位整数。
      • 若保存的数字为18，实际存的时候前面会补0:00018

    浮点类型:DOUBLE(m,n)

    • m:数字的位数

    • n:小数部分的位数

    • m包含n

      • DOUBLE(5,3):该数字总共5位，其中有3位小数(意味着整数位2位)。

        最大个保存的数字为:99.999

      • 当保存的数字精度超过可保存精度时，会进行4舍5入

        1. 例如person表中salary字段的类型:DOUBLE(7,4)
        2.  
        3. INSERT INTO person (salary) VALUES(553.12568)
        4. 实际salary保存的数字为:553.1257

      • 当最大值需要进行四舍五入时，此时会报错:值超出了范围

    字符类型

    定长字符:CHAR(n)

    • n表示长度，单位是字符。

    • name CHAR(10):name可以保存10个字符

    • 最大值为255个字符

    • 表中该字段保存数据时，实际在磁盘中保存数据一定是指定长度的字符。当实际保存的字符不足时会补充空格来达到长度。

      例如:name插入数据时为'张三'。那么实际存储时后面还有8个空格要占够10个字符。

    • 优点:每条记录占用的字节长度是固定的，这对于数据库扫描磁盘查询数据时效率高。

    • 缺点:浪费磁盘空间

    变长字符:VARCHAR(n)

    • n表示长度，单位时字节。

    • 最大值为65535个字节

    • 表中该字段保存数据时，实际在磁盘中保存数据用多少占多少字节。

      例如:name VARCHAR(30)

      插入数据时name为'张三',实际存储时磁盘中仅6个字节。

    • 优点:不浪费磁盘空间

    • 缺点:查询速度慢

    变长字符:TEXT(n)

    • n表示长度，单位是字符
    • 最大值为65535

    日期类型

    • DATE:仅保存年月日
    • TIME:时分秒
    • DATETIME:保存年月日时分秒
    • TIMESTAMP:时间戳，记录UTC时间。自1970-01-01 00:00:00到表达的时间所经过的毫秒

    例

    • 准备一张表测试各种类型

      1. CREATE TABLE userinfo(
      2. 	id INT,
      3. 	username VARCHAR(30),
      4. 	gender CHAR(1),
      5. 	birth DATETIME,
      6. 	salary DOUBLE(7,2)
      7. )

    • 插入日期时，可以以字符串形式插入，格式为:"yyyy-MM-dd hh:mm:ss"

      注:MM表示两位数字的月，mm表示两位数字的分

      1. INSERT INTO userinfo(id,username,gender,birth,salary)
      2. VALUES (1,'张三','男','2001-02-03 12:22:55',5000.99)

    • 插入日期时，如果类型为DATETIME，插入字符串中可以不指定时分秒

      1. INSERT INTO userinfo(id,username,gender,birth,salary)
      2. VALUES (2,'李四','女','1998-08-24',6000)
      3. 此时时分秒都为0

    • DATETIME类型插入数据时不能忽略年月日

      1. INSERT INTO userinfo(id,username,gender,birth,salary)
      2. VALUES (3,'王五','男','12:15:32',5000)
      3. 会报错，DATETIME字段不能只插入时分秒

    • 插入DOUBLE值时精度超过部分会四舍五入，整数部分若超过长度则会报错

      1. INSERT INTO userinfo(id,username,gender,birth,salary)
      2. VALUES (3,'王五','男','1985-06-01 12:15:32',100000)

    约束条件

    可以对表添加约束条件，这样一来仅当满足约束条件的操作才可以进行。这样做可以更好的为我们的业务服务，保证数据库的操作是服务业务要求的。

    主键约束(PRIMARY KEY)

    • 主键的特点:非空且唯一。符合该特点的值才可以用来标识表中唯一的一条记录。
    • 通常一张表中的第一个字段都是主键字段，名字通常选取为:id
    • 主键约束一张表只能为一个字段添加

    例

    1. CREATE TABLE user1(
    2.     id INT PRIMARY KEY,
    3.     name VARCHAR(30),
    4.     age INT(3)
    5. );
    6.  
    7. INSERT INTO user1(id,name,age) VALUES (1,'张三',22)
    8. INSERT INTO user1(id,name,age) VALUES (2,'李四',33)

    • 主键字段不能插入重复的值

    • 主键字段不能插入NULL值

    • 不能修改重复的值

    • 不能修改为NULL

    具体主键约束的字段通常会配合自增(AUTO_INCREMENT)使用

    • 将字段定义为主键约束时，同时指定:AUTO_INCREMENT

      1. CREATE TABLE user2(
      2.     id INT PRIMARY KEY AUTO_INCREMENT,
      3.     name VARCHAR(30),
      4.     age INT(3)
      5. );
      6.  
      7. 也可以修改现有的表字段
      8. ALTER TABLE user1 CHANGE id id INT PRIMARY KEY AUTO_INCREMENT

    • 当主键字段具有自增功能时，插入数据则可以忽略主键字段的插入

      1. INSERT INTO user2 (name,age) VALUES('张三',22)
      2. INSERT INTO user2 (name,age) VALUES('李四',33)

    • 当显示的给自增字段插入NULL值时，该字段的值仍然会自增。(不推荐)

      INSERT INTO user2 (id,name,age) VALUES(NULL,'王五',36)

    非空约束(NOT NULL)

    被非空约束修饰的字段表中每条记录该字段必须有值，不能为NULL

    例

    1. CREATE TABLE user3(
    2. 	id INT PRIMARY KEY AUTO_INCREMENT,
    3. 	name VARCHAR(30) NOT NULL,
    4. 	age INT(3)
    5. )
    6.

    • 非空约束在查看表结构时有所体现

      DESC user3

    • 不能将NULL值插入具有非空约束的字段中

      INSERT INTO user3 (name,age) VALUES(NULL,22)

    • 插入数据时不能忽略具有非空约束的字段(字段默认插入NULL)

      INSERT INTO user3(age) VALUES(33)

    DQL数据查询语言

    DQL是用于查询表中数据的语言。

    语法:

    1. SELECT 子句
    2. FROM 子句
    3. WHERE 子句
    4. JOIN...ON...子句
    5. GROUP BY 子句
    6. HAVING 子句
    7. ORDER BY子句

    一条DQL语句至少要包含的两个子句分别是SELECT子句和FROM子句

    • SELECT子句用于指定查询的字段名称。选定的字段会体现在查询结果集中
    • FROM子句用于指定数据来自那些表(指定查询的表)

    基础查询

    由SELECT和FROM构成。

    语法:

    • SELECT子句和FROM子句

      1. SELECT 字段1[,字段2,字段3,...]
      2. FROM 表1[,表2,表3...]

    • SELECT子句中可以使用*表达查询所有的字段

      实际开发中不推荐使用,因为查询低效*

    例

    1. 查询teacher表中的所有数据
    2. SELECT * FROM teacher
    3. 上述SQL执行时，数据库会先解析"*",这个操作会导致数据库先查看内部数据字典了解表的字段后
    4. 才能查看表中数据。由于查询是非常频繁的操作，因此每次查看数据字段无疑是浪费性能且消耗时间的操作!
    5. 全字段查询时应当在SELECT子句中将所有字段全部列出来(后期java程序执行时)。
    6. 如果是手动临时查询，可以使用*。
    7.  
    8.  
    9. 查看每个老师的名字，职称，工资，性别
    10. SELECT name,title,salary,gender
    11. FROM teacher

    WHERE子句

    在DQL中用于筛选查询的记录。最终数据库仅将满足WHERE子句条件的记录体现在结果集中。

    例

    • 查看职称为"一级讲师"的老师名字和工资?

      1. 1:查询的是老师信息，能确定FROM子句中的表名为teacher
      2. 2:查看的是老师的名字和工资，能确定SELECT子句中的字段name和salary
      3. 3:由于仅查看"一级讲师",能确定WHERE子句过滤条件为title='一级讲师'
      4. SELECT name,salary,title
      5. FROM teacher
      6. WHERE title='一级讲师'

    • 查看除'刘苍松'老师以外的其他老师的名字，工资和年龄?

      1. SELECT name,salary,age
      2. FROM teacher
      3. WHERE name<>'刘苍松'

    • 查看所有职位是'大队长'的同学的名字年龄和性别

      1. SELECT name,age,gender
      2. FROM student
      3. WHERE job='大队长'

    • 查看年龄在30(含)岁以上的老师都有谁？查看老师的名字,年龄,性别,工资

      1. SELECT name,age,gender,salary
      2. FROM teacher
      3. WHERE age>=30

    使用AND"与"和OR"或"来连接多个条件进行查询

    • AND:都为真时才为真
    • OR:都为假时才为假

    例

    • 查看7岁的大队长都是谁?列出:名字，年龄，性别，职位

      1. SELECT name,age,gender,job
      2. FROM student
      3. WHERE job='大队长' AND age=7

    • 查看班级编号小于6的所有中队长的名字，年龄，性别，职位和所在班级编号

      1. SELECT name,age,gender,job,class_id
      2. FROM student
      3. WHERE class_id<6 AND job='中队长'

    • 查看所有一级讲师和三级讲师的名字，职称，工资?

      1. SELECT name,title,salary
      2. FROM teacher
      3. WHERE title='一级讲师' OR title='三级讲师'

    • 查看所有大队长，中队长和小队长的名字，年龄，性别

      1. SELECT name,age,gender
      2. FROM student
      3. WHERE job='大队长' OR job='中队长' OR job='小队长'

    • 查看班级编号在6(含)以下的所有大队长和中队长的名字，年龄，职位，班级编号

      AND的优先级是高于OR的

      可以使用()提高优先级

      1. SELECT name,age,job,class_id
      2. FROM student
      3. WHERE class_id<=6 AND job='大队长' OR job='中队长'
      4. 上述SQL的条件应当读作:查看班级编号6以下的大队长和所有班级编号的中队长
      5.  
      6.  
      7. SELECT name,age,job,class_id
      8. FROM student
      9. WHERE class_id<=6 AND (job='大队长' OR job='中队长')
      10. 提高OR的优先级满足查询要求

    IN(列表):等于列表其中之一(在列表中)

    例

    • 查看所有的大队长，中队长，小队长的名字，年龄，性别

      1. SELECT name,age,gender
      2. FROM student
      3. WHERE job='大队长' OR job='中队长' OR job='小队长'
      4. 等价于
      5. SELECT name,age,gender
      6. FROM student
      7. WHERE job IN('大队长','中队长','小队长')

    • 查看所有一级讲师，二级讲师，三级讲师的老师名字，职称和工资?

      1. SELECT name,title,salary
      2. FROM teacher
      3. **WHERE title IN ('一级讲师','二级讲师','三级讲师')**

    NOT IN(列表):不在列表中，不能等于列表中的所有项

    • 查看一级讲师和二级讲师以外的所有老师的名字，职称，工资?

      1. SELECT name,title,salary
      2. FROM teacher
      3. WHERE title NOT IN('一级讲师','二级讲师')

    • 查看不是中队长和大队长以及小队长的其他学生的名字，年龄，职位

      1. SELECT name,age,job
      2. FROM student
      3. WHERE job NOT IN('大队长','中队长','小队长')

    BETWEEN...AND...:在一个范围之内

    • 查看工资在2000-5000之间的老师的名字，工资，职称?

      1. SELECT name,salary,title
      2. FROM teacher
      3. WHERE salary>=2000 AND salary<=5000
      4. 等价于
      5. SELECT name,salary,title
      6. FROM teacher
      7. WHERE salary BETWEEN 2000 AND 5000
      8.                      下限      上限

    • 查看年龄在7-10岁之间的学生的名字，年龄，性别

      1. SELECT name,age,gender
      2. FROM student
      3. WHERE age BETWEEN 7 AND 10

    • 查看年龄在20-35岁之间的男老师的名字，职称，年龄

      1. SELECT name,title,age
      2. FROM teacher
      3. WHERE age BETWEEN 20 AND 35
      4. AND gender='男'

    • 查看3-5楼的班级名称都是什么?

      1. SELECT name
      2. FROM class
      3. WHERE floor BETWEEN 3 AND 5

    3

    DISTINCT去重操作。在结果集中去除指定字段值相同的记录

    语法

    1. SELECT DISTINCT 字段1[,字段2,...]
    2. FROM 表名
    3. ...
    • DISTINCT关键字必须紧跟在SELECT子句之后
    • DISTINCT后面可以指定多个字段，当这几个字段组合相同的记录会在结果集中被去除

    例

    • 查看老师的职称都有哪些?

      1. SELECT title
      2. FROM teacher
      3. 上述SQL语句的查询结果集是展现每个老师的职称，与查询需求不匹配
      4.  
      5. SELECT DISTINCT title
      6. FROM teacher
      7. 将查询结果集中重复的title去除后得到正确效果

    • 查看学生的职位都有哪些?

      1. SELECT DISTINCT job
      2. FROM student

    • 查看各年龄的职位都有哪些?

      1. SELECT DISTINCT age,job
      2. FROM student

    练习:

    1. 1.查看负责课程编号(subject_id)为1的男老师都有谁?
    2. 2.查看工资高于5000的女老师都有谁?
    3. 3.查看工资高于5000的男老师或所有女老师的工资？
    4. 4.查看所有9岁学生的学习委员和语文课代表都是谁?
    5.  
    6. 5.查看工资在6000到10000之间的老师以及具体工资?
    7. 6.查看工资在4000到8000以外的老师及具体工资?
    8.  
    9. 7.查看老师负责的课程编号都有什么?
    10. 8.查看所有女老师的职称都是什么?
    11.  
    12. 9.查看7-10岁的男同学的职位都有哪些?
    13.  
    14. 10.查看一级讲师和二级讲师的奖金(comm)是多少?
    15. 11.查看除老板和总监的其他老师的工资和奖金是多少?
    16. 12.查看'3年级2班'和'5年级3班'在那层楼?
  • 相关阅读:
    PDF转Word免费的软件有哪些？教给你三种转换方法
    02333软件工程历年解答题
    Java网络编程
    python数据结构 操作指南（列表、元组、字典、集合）
    用指针低三位存放额外信息的优化方法
    GraphQL 查询：一个全面指南
    【二叉树-中等】1530. 好叶子节点对的数量
    正则基本使用
    org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)
    XML的解析
  • 原文地址：https://blog.csdn.net/m0_61332438/article/details/127934796