数据库与表的操作

发布于：2025-03-26 ⋅ 阅读:(28) ⋅ 点赞:(0)
1. SQL 分类

SQL 根据功能分为以下几类：

  • **DDL **: 定义数据库对象（库、表、列、索引等）

    • 常用语句：CREATE, DROP, ALTER, RENAME, TRUNCATE
    • 示例：
      CREATE TABLE t_user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

  • **DML **: 操作数据库记录

    • 常用语句：INSERT, DELETE, UPDATE, SELECT
    • 示例：
      INSERT INTO t_user (name) VALUES ('Alice');

  • **DQL **: 数据查询

    • 常用语句：SELECT
    • 示例：
      SELECT * FROM t_user WHERE age > 20;

  • **DCL **: 控制数据库访问权限

    • 常用语句：GRANT, REVOKE
    • 示例：
      GRANT SELECT ON db_name.* TO 'user'@'localhost';
2. SQL 语句书写规范
  • SQL 语句不区分大小写，但建议关键字大写。
  • 字符串常量区分大小写。
  • 每条 SQL 语句以 ; 结尾。
  • 使用空格和缩进提高可读性。
  • 注释：
    • 多行注释：/* 注释内容 */
    • 单行注释：-- 注释内容 或 # 注释内容
3. 数据库操作
  • 创建数据库:
     #创建数据库
 CREATE DATABASE 数据库名称;
 # 创建数据库，同时指定编码
 create database db_name default charset="utf8mb4";
 create database school DEFAULT CHARACTER SET utf8mb4;
 create database school DEFAULT CHARACTER SET utf8mb4 COLLATE  utf8mb4_general_ci;
 # 查询当前数据库是哪个数据库
 select database();
 查看数据库版本
 SELECT VERSION();
 查看当前用户
 SELECT USER();
 查看所有用户
 SELECT User,Host,Password FROM mysql.user;
 注意：在 MySQL 5.7 及以上版本中，mysql.user 表中的 password 字段已经被移除，取而代之的是 authentication_string 字段。因此，如果你想查看用户的密码信息，需要使用 authentication_string 字段。
 SELECT user, host, authentication_string FROM mysql.user;

 # 查看创建的数据库
 show create databsse 数据库名称;
    在这里插入图片描述
    在这里插入图片描述
  • 删除数据库:
    DROP DATABASE 数据库名;
    在这里插入图片描述
  • 查看数据库编码:
    SHOW VARIABLES LIKE 'character%';
    在这里插入图片描述
character_set_client：MySQL客户机字符集。
character_set_connection：数据通信链路字符集，当MySQL客户机向服务器发送请求时，请求数据以
该字符集进行编码。
character_set_database：数据库字符集。
character_set_filesystem：MySQL服务器文件系统字符集，该值是固定的binary。
character_set_results：结果集的字符集，MySQL服务器向MySQL客户机返回执行结果时，执行结果以
该字符集进行编码。
character_set_server：MySQL服务实例字符集。
character_set_system：元数据(字段名、表名、数据库名等) 的字符集，默认值为utf8。
  • 切换数据库:
    USE db_name;
4. 数据库表操作

  • 创建表:

    -- 语法结构
create table [if not exists] t_name (
# 定义表结构
  字段名称1 类型 [约束条件],
  字段2  类型 [约束条件],
  ……
  字段n 类型 [约束条件]
  );
  /**
  创建一个用户表
  用户的姓名
  用户的年龄
  用户的性别
  用户的地址
  用户的电话**/
  create table t_user (
  id int,
  name varchar(50),
  age int,
  gender char(5),
  address varchar(255),
  tel char(11)
  );
  # 查询表中的数据 
 select * from t_user;

# 插入数据
insert into t_user values(1, "张三", 16, '男', "四川成都", "110");

    在这里插入图片描述
    在这里插入图片描述

  • 删除表:

    DROP TABLE t_user;

  • 查看表结构:

    DESC t_user;
5. 数据类型

  • 数值型:

    • INT: 整数
    • FLOAT: 单精度浮点数
    • DOUBLE: 双精度浮点数
    • DECIMAL: 精确小数

  • 字符串型:

    • CHAR: 定长字符串
    • VARCHAR: 变长字符串
    • TEXT: 长文本数据
    • BLOB: 二进制大对象

  • 日期和时间型:

    • DATE: 日期（YYYY-MM-DD）
    • DATETIME: 日期和时间（YYYY-MM-DD HH:MM:SS）
    • TIMESTAMP: 时间戳（从 1970-01-01 开始的秒数）
6. 数据库约束
  • 主键约束: 唯一标识一条记录
    id INT PRIMARY KEY
    案列：
     create table t_user(
  id int,
  name varchar(50));
# 插入数据测试
insert into t_user values(1, "张三");
insert into t_user values(1, "张三");
select * from t_user;

drop table t_user;

create table t_user(
  id int primary key,  -- 主键约束
  name varchar(50)
);

# 插入数据测试
insert into t_user values(1, "张三");
insert into t_user values(1, "张三");
insert into t_user values(null, "张三");
insert into t_user values(2, "张三");


drop table t_user;
create table t_user(
  id int primary key auto_increment,  -- 主键约束
  name varchar(50)
);
# 插入数据测试
insert into t_user values(1, "张三");
insert into t_user values(1, "张三");
insert into t_user values(null, "张三");
insert into t_user values(2, "张三");


insert into t_user(name) value("张三");

-- 主键还存在一种写法
create table t_user(
  id int auto_increment,  -- 主键约束
  name varchar(50),
  primary key(id)
);
    在这里插入图片描述

在这里插入图片描述

  • 唯一约束: 确保字段值唯一

    email VARCHAR(100) UNIQUE

    案例：

    唯一性约束条件确保所在的字段或者字段组合不出现重复值
唯一性约束条件的字段允许出现一个NULL
同一张表内可建多个唯一约束
唯一约束可由多列组合而成
建唯一约束时MySQL会为之建立对应的索引——唯一索引。
如果不给唯一约束起名，该唯一约束默认与列名相同。

CREATE TABLE tb_student(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(18) UNIQUE  -- 唯一约束
);

CREATE TABLE tb_student(
  id INT AUTO_INCREMENT,
  NAME VARCHAR(18),  -- 唯一约束
  PRIMARY KEY(id),
  unique(name)
);

    在这里插入图片描述

  • 非空约束: 字段值不能为空

    name VARCHAR(50) NOT NULL

    案例：

    create table t_user(
  id int primary key auto_increment,
  name varchar(50) unique not null,
  age  int default 18 ,
  password varchar(255) not null
);


insert into t_user values(1, "张三", 20, "123456");
-- 报错
insert into t_user(name, age) values("李四", 18);
insert into t_user values(null, "王二", 16);  -- 因为存在自增，所以可以写null

insert into t_user(name, age, password) values("刘帅哥", 18, "123456");
insert into t_user(name, password) values("张三", "123456");

    在这里插入图片描述

  • 默认值约束: 字段的默认值

    age INT DEFAULT 18

    案例：

    create table t_user(
  id int primary key auto_increment,
  name varchar(50) unique,
  age  int default 18  -- 默认值约束
);

insert into t_user values(1, "刘二", 20);
insert into t_user(name, age) values("刘帅哥", 18);
insert into t_user values(null, "刘欧巴", 16);  -- 因为存在自增，所以可以写null
insert into t_user values(default, "刘文理", 16);  -- 因为存在自增，所以可以写默认
insert into t_user(name) values("张三");
insert into t_user(name) values("李四");

    在这里插入图片描述
    在这里插入图片描述

  • 外键约束: 关联其他表的主键

    FOREIGN KEY (class_id) REFERENCES classes(id)

  • 检查约束: 确保字段值符合条件（MySQL 8.0+）

    age INT CHECK (age >= 18)

    案例：

    create table t_stu(
  id int primary key auto_increment,
  name varchar(50) not null unique,
  age int check(age >= 18),
  # gender char(2) check(gender in ("男", "女"))
  gender enum("男", "女")
);


insert into t_stu value(null, "zs", 20, "女");
insert into t_stu value(null, "lisi", 10, "女");

insert into t_stu value(null, "lisi", 18, "女");
insert into t_stu value(null, "ww", 28, "哈");

    在这里插入图片描述

7. 修改表的结构

在sql中，也提供了动态修改表结构的sql功能。

使用alter指令，实现对数据库对象的结构的调整和修改。

修改列类型
  ALTER TABLE 表名 MODIFY 列名 列类型;   -- 注意存在值的情况，类型不一定能成功
增加列
  ALTER TABLE 表名 ADD 列名 列类型;
删除列
  ALTER TABLE 表名 DROP 列名;
列改名
  ALTER TABLE 表名 CHANGE 旧列名 新列名 列类型;
更改表名
  ALTER TABLE 表名 RENAME 新表名;
  RENAME TABLE 表名 TO 新表名;
  
  
案例：
alter table t_user add mark int default 0;
alter table t_user modify mark varchar(10);
alter table t_user modify mark int;
alter table t_user add test int;
alter table t_user drop test;
alter table t_user change test address varchar(255);
alter table t_user rename user;
rename table user to t_user;

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

8. 复制表的内容
复制一个表结构的实现方法有两种

方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下。
create table 新表名 like 源表

方法二：在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表
记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中。
create table 新表名 select * from 源表


方法三：如果已经存在一张机构一致的表，复制数据
insert into 表 select * from 原表;

案例：

mysql> create table user like t_user;
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
+----------------------+
| Tables_in_db_chengke |
+----------------------+
| t_stu                |
| t_user               |
| user                 |
+----------------------+
3 rows in set (0.00 sec)

mysql> desc user;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| name     | varchar(50)  | NO   | UNI | NULL    |                |
| age      | int          | YES  |     | 18      |                |
| password | varchar(255) | NO   |     | NULL    |                |
| mark     | varchar(10)  | YES  |     | NULL    |                |
| address  | varchar(255) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> select * from user;
Empty set (0.00 sec)

mysql> select * from t_user;
+----+-----------+------+----------+-----------+---------+
| id | name      | age  | password | mark      | address |
+----+-----------+------+----------+-----------+---------+
|  1 | 张三    |   20 | 123456   | 0         | NULL    |
|  2 | 刘帅哥    |   18 | 123456   | 0         | NULL    |
|  3 | 张三      |   18 | 123456   | 0         | NULL    |
|  4 | hehe      |   20 | 122      | 张三    | NULL    |
+----+-----------+------+----------+-----------+---------+
4 rows in set (0.00 sec)


mysql> create table user select * from t_user;
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show tables;
+----------------------+
| Tables_in_db_chengke |
+----------------------+
| t_stu                |
| t_user               |
| user                 |
+----------------------+
3 rows in set (0.00 sec)

mysql> select * from user;
+----+-----------+------+----------+-----------+---------+
| id | name      | age  | password | mark      | address |
+----+-----------+------+----------+-----------+---------+
|  1 | 张三    |   20 | 123456   | 0         | NULL    |
|  2 | 刘帅哥    |   18 | 123456   | 0         | NULL    |
|  3 | 张三      |   18 | 123456   | 0         | NULL    |
|  4 | hehe      |   20 | 122      | 张三    | NULL    |
+----+-----------+------+----------+-----------+---------+
4 rows in set (0.00 sec)

mysql> desc user;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | NO   |     | 0       |       |
| name     | varchar(50)  | NO   |     | NULL    |       |
| age      | int          | YES  |     | 18      |       |
| password | varchar(255) | NO   |     | NULL    |       |
| mark     | varchar(10)  | YES  |     | NULL    |       |
| address  | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

create table user select id, name, mark from t_user;


-- 将查询的数据快速插入到表中
insert into user select * from user;

在这里插入图片描述
在这里插入图片描述

8. 数据库字典
由information_schema数据库负责维护

tables－存放数据库里所有的数据表、以及每个表所在数据库。
schemata－存放数据库里所有的数据库信息
views－存放数据库里所有的视图信息。
columns－存放数据库里所有的列信息。
triggers－存放数据库里所有的触发器。
routines－存放数据库里所有存储过程和函数。
key_column_usage－存放数据库所有的主外键
table_constraints－存放数据库全部约束。
statistics－存放了数据表的索引。

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布