DML（data manipulation language）数据操纵语言＆ DDL（data definition language）数据定义语言

最新推荐文章于 2024-07-02 19:32:39 发布

原创最新推荐文章于 2024-07-02 19:32:39 发布 · 420 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql

后端同时被 2 个专栏收录

63 篇文章

订阅专栏

mysql

22 篇文章

订阅专栏

这篇博客介绍了MySQL数据库的基本操作，包括创建、查询、修改和删除数据库，以及如何使用不同字符集。此外，还详细讲解了数据表的创建、修改、删除等操作，涉及数据类型如int、double、date、datetime、timestamp和varchar。同时，文章讨论了数据约束，如主键、非空、唯一和外键约束。最后，提到了一些常用的DML操作，如插入、删除和更新数据，以及数据库设计中表间关系的概念和实现方式。

1.1 数据库操作
* 创建数据库：
   * create database 数据库名称;
* 创建数据库，判断不存在，再创建：
   * create database if not exists 数据库名称;
* 创建数据库，并指定字符集
   * create database 数据库名称 character set 字符集名;
-------------------------------------------------------------------------
* 查询所有数据库的名称:
   * show databases;
* 查询某个数据库的字符集:查询某个数据库的创建语句
   * show create database 数据库名称;
-------------------------------------------------------------------------
*修改数据库的字符集
   * alter database 数据库名称 character set 字符集名称;
-------------------------------------------------------------------------
* 删除数据库
   * drop database 数据库名称;
* 判断数据库存在，存在再删除
   * drop database if exists 数据库名称;
-------------------------------------------------------------------------
* 查询当前正在使用的数据库名称
   * select database();
* 使用数据库
   * use 数据库名称;
```

#### 课堂练习
-- 创建db_demo数据库，设置数据库字符集为utf8;
CREATE DATABASE db_demo CHARACTER SET utf8;

1.2 数据表操作

* 表的创建
    * create table 表名(
                列名1 数据类型1 【字段约束】,
                列名2 数据类型2 【字段约束】,
                ....
                列名n 数据类型n 【字段约束】
            );
    * 注意：最后一列，不需要加逗号（,）
-------------------------------------------------------------------------   
* 查询当前数据库中所有的表
    * show tables;
* 查询某个数据库中所有的表
    * show tables from 数据库名称;
* 查询表结构
    * desc 表名;
-----------------   --------------------------------------------------------
* 修改表名   alter v. 改变，改动；（使）变化；改（服装）；改建（某建筑）；
    * alter table 表名 rename to 新的表名;
* 修改表的字符集
    * alter table 表名 character set 字符集名称;
* 添加一列
    * alter table 表名 add 列名 数据类型;
* 修改列名称 类型
    * alter table 表名 change 列名 新列别 新数据类型;
    * alter table 表名 modify 列名 新数据类型;
* 删除列
    * alter table 表名 drop 列名;
-------------------------------------------------------------------------
* 删除表
    * drop table 表名;
    * drop table  if exists 表名;

1.3 MySQL常用数据类型

1.int：整数类型

    age int，
    
2. double:小数类型

   score double(5,2)，
   
3. date:日期，只包含年月日，yyyy-MM-dd

4. datetime:日期，包含年月日时分秒  yyyy-MM-dd HH:mm:ss

5. timestamp:时间错类型  包含年月日时分秒     yyyy-MM-dd HH:mm:ss    

      如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值

6. varchar：字符串

     name varchar(20):姓名最大20个字符

     zhangsan 8个字符  张三 2个字符

课堂练习

使用db_demo数据库

创建表dept1

字段名	是否为空	数据类型
id		int(7)
name		varchar(25)

CREATE TABLE dept1( id INT(7), NAME VARCHAR(25) );

创建表emp5

字段名	是否为空	数据类型
id		int(7)
frist_name		varchar(25)
last_name		varchar(25)
dept_id		int(7)

CREATE TABLE emp5( id INT(7), frist_name VARCHAR(25), last_name VARCHAR(25), dept_id INT(7) );

1.4 约束

* 概念： 对表中的数据进行限定，保证数据的正确性、有效性和完整性。  
* 分类：
    1. 主键约束：primary key
    2. 非空约束：not null
    3. 唯一约束：unique
    4. 外键约束：foreign key
-------------------------------------------------------------------------
* 非空约束：not null，某一列的值不能为null
    1. 创建表时添加约束
        CREATE TABLE stu(
            id INT,
            NAME VARCHAR(20) NOT NULL -- name为非空
        );
    2. 创建表完后，添加非空约束
        ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

    3. 删除name的非空约束
        ALTER TABLE stu MODIFY NAME VARCHAR(20);
-------------------------------------------------------------------------
* 唯一约束：unique，某一列的值不能重复
    1. 注意：
        * 唯一约束可以有NULL值，但是只能有一条记录为null
    2. 在创建表时，添加唯一约束
        CREATE TABLE stu(
            id INT,
            phone_number VARCHAR(20) UNIQUE -- 手机号
        );
    3. 删除唯一约束
        ALTER TABLE stu DROP INDEX phone_number;
    4. 在表创建完后，添加唯一约束
        ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
-------------------------------------------------------------------------   
* 主键约束：primary key。
    1. 注意：
        1. 含义：非空且唯一
        2. 一张表只能有一个字段为主键
        3. 主键就是表中记录的唯一标识

    2. 在创建表时，添加主键约束
        create table stu(
            id int primary key,-- 给id添加主键约束
            name varchar(20)
        );

    3. 删除主键
        -- 错误 alter table stu modify id int ;
        ALTER TABLE stu DROP PRIMARY KEY;

    4. 创建完表后，添加主键
        ALTER TABLE stu MODIFY id INT PRIMARY KEY;

    5. 自动增长：
        1.  概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长

        2. 在创建表时，添加主键约束，并且完成主键自增长
        create table stu(
            id int primary key auto_increment,-- 给id添加主键约束
            name varchar(20)
        );
        3. 删除自动增长
            ALTER TABLE stu MODIFY id INT;
        4. 添加自动增长
            ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
-------------------------------------------------------------------------
* 外键约束：foreign key,让表于表产生关系，从而保证数据的正确性。
    1. 在创建表时，可以添加外键
        * 语法：
            create table 表名(
                ....
                外键列
                constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
            );

    2. 删除外键
        ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

    3. 创建表之后，添加外键
        ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
    4. 级联操作
            1. 添加级联操作
                语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
                        FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;
            2. 分类：
                1. 级联更新：ON UPDATE CASCADE 
                2. 级联删除：ON DELETE CASCADE

课堂练习

将表departments中的数据插入新表dept2中

CREATE TABLE dept02
AS
SELECT *
FROM myemployees.departments;
根据表employees创建employees2、

CREATE TABLE myemployees2
AS
SELECT *
FROM myemployees.departments;
SHOW TABLES FROM db_demo;

删除表emp5

DROP TABLE IF EXISTS emp5;
SHOW TABLES FROM db_demo;
ROLLBACK;
SHOW TABLES FROM db_demo;
DDL删除后数据表无法回滚，删除是永久操作

4.向表dept2的id列中添加PRIMARY KEY约束（my_dept_id_pk）

SHOW TABLES FROM db_demo;
ALTER TABLE dept02 MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE dept02 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);

5.向表employees2中定义FOREIGNKEY约束，与之相关联的列是dept2表中的id列

思考：主键与唯一键的区别？

①、一个表中可以有多个唯一索引，但是只能有一个主键。

②、主键一定是唯一性索引，唯一性索引并不一定就是主键

主键不允许为空，唯一键允许为空，空值不受唯一约束，也就是说可以有多个空值。

注：可以多列组合成一个唯一索引或者一个主键，即组合索引或组合主键

主要的关键字有auto increment自增 comment 对行进行注释，（这里的注释在数据库里可以看到）

2 DML

1. 添加数据：
    * 语法：
        * insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
    * 注意：
        1. 列名和值要一一对应。
        2. 如果表名后，不定义列名，则默认给所有列添加值
            insert into 表名 values(值1,值2,...值n);
        3. 除了数字类型，其他类型需要使用引号(单双都可以)引起来
-------------------------------------------------------------------------
2. 删除数据：
    * 语法：
        * delete from 表名 [where 条件]
    * 注意：
        1. 如果不加条件，则删除表中所有记录。
        2. 如果要删除所有记录
            1. delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作
            2. TRUNCATE TABLE 表名; -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表。
    * 面试题：delete与TRUNCATE区别？
        1.truncate不能加where条件，而delete可以加where条件
        2.truncate的效率高
        3.truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始
        4.delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
        5.truncate删除不能回滚，delete删除可以回滚
-------------------------------------------------------------------------
3. 修改数据：
    * 语法：
        * 修改单表： update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
        * 修改多表： update 表1 别名1,表2 别名2 set 字段=新值，字段=新值 where 连接条件 and 筛选条件

    * 注意：
        1. 如果不加任何条件，则会将表中所有记录全部修改

课堂练习

1.运行以下脚本创建表my_employees

id	first_name	last_name	userid	salary
1	patel	Ralph	Rpatel	895
2	Dancs	Betty	Bdancs	860
3	Biri	Ben	Bbiri	1100
4	Newman	Chad	Cnewman	750

USE db_demo;
CREATE TABLE my_employees(
id INT ( 10 ),
first_name VARCHAR ( 10 ),
last_name VARCHAR ( 10 ),
userid VARCHAR ( 10 ),
salary DOUBLE ( 10 , 2 )
);

INSERT INTO my_employees(id,first_name ,last_name ,userid ,salary)
VALUES
(1,'patel ','Ralph','Rpatel',895),
(2,'Dancs ','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750);

向 users 表中插入数据

id	userid	department_id
1	Rpatel	10
2	Bdancs	10
3	Bbiri	20
4	Cnewman	30
5	Aropebur	40

USE db_demo;
CREATE TABLE users(
id INT ,
userid VARCHAR ( 10 ),
department_id INT
);

INSERT INTO users(id,userid,department_id)
VALUES
(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Biri',20),
(4,'Cnewman',30),
(5,'Aropebur',40);

将 3 号员工的 last_name 修改为“drelxer”

UPDATE my_employees
SET last_name = 'drelxer'
WHERE id = 3;

修改前

修改后

将所有工资少于 900 的员工的工资修改为 1000

UPDATE my_employees
SET salary = 1000
WHERE salary < 900;

将 userid 为 Bbiri 的 user 表和 my_employees 表的记录全部删除

DELETE m,u
FROM my_employees m JOIN users u
ON m.userid = u.userid
WHERE m.userid = 'Bbiri';

删除所有数据

DELETE FROM my_employees;
DELETE FROM users;

检查所作的修正

SELECT * FROM my_employees;
SELECT * FROM users;

清空表 my_employees

TRUNCATE TABLE my_employees;

4 数据库设计

1. 多表之间的关系
    1. 分类：
        1. 一对一(了解)：
            * 如：人和身份证
            * 分析：一个人只有一个身份证，一个身份证只能对应一个人
        2. 一对多(多对一)：
            * 如：部门和员工
            * 分析：一个部门有多个员工，一个员工只能对应一个部门
        3. 多对多：
            * 如：学生和课程
            * 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
    2. 实现关系：
        1. 一对多(多对一)：
            * 如：部门和员工
            * 实现方式：在多的一方建立外键，指向一的一方的主键。
        2. 多对多：
            * 如：学生和课程
            * 实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
        3. 一对一(了解)：
            * 如：人和身份证
            * 实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。

    3. 案例
        -- 创建旅游线路分类表 tab_category
        -- cid 旅游线路分类主键，自动增长
        -- cname 旅游线路分类名称非空，唯一，字符串 100
        CREATE TABLE tab_category (
            cid INT PRIMARY KEY AUTO_INCREMENT,
            cname VARCHAR(100) NOT NULL UNIQUE
        );
        
        -- 创建旅游线路表 tab_route
        /*
        rid 旅游线路主键，自动增长
        rname 旅游线路名称非空，唯一，字符串 100
        price 价格
        rdate 上架时间，日期类型
        cid 外键，所属分类
        */
        CREATE TABLE tab_route(
            rid INT PRIMARY KEY AUTO_INCREMENT,
            rname VARCHAR(100) NOT NULL UNIQUE,
            price DOUBLE,
            rdate DATE,
            cid INT,
            FOREIGN KEY (cid) REFERENCES tab_category(cid)
        );
        
        /*创建用户表 tab_user
        uid 用户主键，自增长
        username 用户名长度 100，唯一，非空
        password 密码长度 30，非空
        name 真实姓名长度 100
        birthday 生日
        sex 性别，定长字符串 1
        telephone 手机号，字符串 11
        email 邮箱，字符串长度 100
        */
        CREATE TABLE tab_user (
            uid INT PRIMARY KEY AUTO_INCREMENT,
            username VARCHAR(100) UNIQUE NOT NULL,
            PASSWORD VARCHAR(30) NOT NULL,
            NAME VARCHAR(100),
            birthday DATE,
            sex CHAR(1) DEFAULT '男',
            telephone VARCHAR(11),
            email VARCHAR(100)
        );
        
        /*
        创建收藏表 tab_favorite
        rid 旅游线路 id，外键
        date 收藏时间
        uid 用户 id，外键
        rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
        */
        CREATE TABLE tab_favorite (
            rid INT, -- 线路id
            DATE DATETIME,
            uid INT, -- 用户id
            -- 创建复合主键
            PRIMARY KEY(rid,uid), -- 联合主键
            FOREIGN KEY (rid) REFERENCES tab_route(rid),
            FOREIGN KEY(uid) REFERENCES tab_user(uid)
        );