第 11.5 节 MySQL 数据库

UNIX 中连接到数据库、执行 SQL 脚本

# mysql -u root -p
# source FileName.sql

建立数据库

create database db_name;
  • db_name:数据库名

数据库命名区分大小写,最长 64 字符,别名最长 256 字符,不能使用数据库关键字。

数据库重命名

rename database old_name to new_name;
  • old_name:旧数据库名

  • new_name:新数据库名

查看数据库

show databases;

进入数据库

use db_name;
  • db_name:数据库名

删除数据库

drop database db_name;
  • db_name:数据库名

更改数据库字符集

alter database xxx
default character set UTF-8;

SQL 数据类型

  • tinyint (1B), smallint (2B), mediumint (3B), int (4B), bigint (8B)

  • float (4B), double (8B), decimal(整体位数, 小数点后位数)

  • char 是字符类型,想存字符串用 char(字符数)

  • unsigned + 数据类型 可以设置为无符号的数据类型。

建立表

create table table_name (
  column_name data_type,
  column_name data_type,
  column_name data_type
);
  • table_name:表名

  • column_name:列名

  • data_type:数据类型

表重命名

alter table old_name rename to new_name;
  • old_name:旧表名

  • new_name:新表名

查看数据库有哪些表

show tables;

展示表结构

desc tables;

显示表全部信息(select 后面细说)

select * from tables;

SQL 语法:注释

-- 注释不会被执行

写脚本加注释是一个好习惯,防止你过一段时间不知道自己写的什么...

主键、唯一、不许为空、自动编号

create table 表名
(
  -- 主键
  列名 数据类型 primary key,
  -- 唯一
  列名 char(20) unique,
  -- 不许为空
  列名 数据类型 not null,
  -- 自动编号
  列名 数据类型 auto_increment
);

信息写入表

insert into table_name (col1, col2) values (123, "李明");
  • col1:列名 1

  • col2:列名 2

表新增列

alter table table_name
add column new_col data_type;
  • new_col:新列名

  • data_type:数据类型

表删除列

alter table 表名 drop 列名;

表修改列数据类型

alter table table_name modify column col_name new_data_type;
  • new_data_type:新数据类型

表添加主键

alter table table_name
add primary key (col_name);
  • col_name:新列名

表删除主键

alter table table_name drop primary key;

唯一性约束

表添加唯一性约束

alter table table_name
add constraint constraint_name unique (col_name);
  • constraint_name:唯一约束名

  • col_name:列名

表删除唯一性约束

alter table table_name
drop index constraint_name;

外键

表添加外键

ALTER TABLE table_name
ADD CONSTRAINT fk_column_name
FOREIGN KEY (fk_column)
REFERENCES tstu(sid);
  • fk_name:外键名

  • fk_col:外部表格列名

表删除外键

alter table table_name
drop foreign key fk_name;
  • table_name:子表名

  • fk_name:外键名

外键的概念

  • 父表:外键引用的表。父表中的被引用列通常是主键(PRIMARY KEY)或唯一键(UNIQUE)。例如上面的黄色表。

  • 子表:包含外键的表。子表中的外键列指向父表中的主键或唯一键。例如上面的绿色表。

删除父表数据时失败

  • 原因:子表中存在引用该数据的记录。

  • 解决:使用 ON DELETE CASCADE 或先删除子表中的记录。

下面是一个父、子表格和外键的示例

-- 创建父表 customers
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY, -- 主键,自动编号
    name VARCHAR(100) NOT NULL,        -- 客户名
    email VARCHAR(150) UNIQUE          -- 唯一的电子邮件
);

-- 创建子表 orders
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY, -- 主键,订单编号
    order_date DATE NOT NULL,                -- 订单日期
    customer_id INT,                         -- 外键列,关联客户 ID
    amount DECIMAL(10,2),                    -- 订单金额
    FOREIGN KEY (customer_id) REFERENCES customers(id) -- 定义外键
    ON DELETE CASCADE -- 当父表记录被删除时,级联删除子表相关记录
    ON UPDATE CASCADE -- 当父表主键更新时,子表外键自动更新
);
  1. 创建父表 customers: 包含列: id:主键,用于唯一标识客户。 name:客户姓名。 email:唯一约束,用于防止重复的电子邮件地址。

  2. 创建子表 orders: order_id:主键,用于唯一标识订单。 order_date:记录订单日期。 customer_id:外键列,用于关联客户。 amount:订单金额。

  3. 设置外键:FOREIGN KEY (customer_id) REFERENCES customers(id) 表示子表的 customer_id 列引用父表的 id 列。 ON DELETE CASCADE 删除父表记录时,子表中引用该记录的行也会被删除。 ON UPDATE CASCADE 更新父表主键时,子表外键列会自动更新。

修改列数据类型

ALTER TABLE 表名
MODIFY COLUMN 列名 新数据类型;

-- 例如(重设数据类型并不许为空、设置默认值)
ALTER TABLE book_table
MODIFY COLUMN BookName CHAR(100) NOT NULL DEFAULT "《书名》";

-- 如果修改的列是外键,必须先删除外键约束,然后修改数据类型,再重新添加外键
alter table 子表格 drop FOREIGN KEY 外键名;
alter table 子表格 modify column 列名 新数据类型;
alter table 子表格 add constraint 外键名 FOREIGN KEY (列名) REFERENCES 父表名(父列名);

修改表信息、删除行

-- 修改表信息
update 表名
set 列名 = 新的值
where 条件;

注:set 你选择了一整列,想修改单个或多个位置的值,就需要 where 的条件来限定,你是要修改整列,还是其中哪个。类似横竖交叉定位一个坐标点。

-- 删除行
DELETE FROM book_table
WHERE BookNumber < 200;

下面给几个示例:

update book_table
set Price = Price * 1.2
where Publisher = "人民邮电出版社";

翻译成人话:把表 book_tablePublisher 值为 "人民邮电出版社" 的行,Price 值全乘以 1.2(就是增加 20%)

DELETE FROM book_table
WHERE BookNumber < 200;

DELETE FROM book_table
WHERE author IN ('王阳', '刘天洋');

“Where 子句”

...(update 或 delete)
where Price < 50;
...(update 或 delete)
WHERE author = '王阳' or author = '刘天洋';
...(update 或 delete)
WHERE name in ('张三', '李四');
  1. 如果 Price 值小于 50,就...

  2. 如果 author 值是王阳或者刘天洋,就...

  3. 如果 name 值是张三或者李四,就... where 接收写在后面的表达式,表达式会运算出一个布尔值。

这里就需要一个知识:SQL 运算符

算术运算符

  • + 加法运算符

  • - 减法运算符

  • * 乘法运算符

  • / 除法运算符

  • % 取模运算符

比较运算符

  • = 等于(也是赋值运算符)

  • != 不等于

  • < 小于

  • > 大于

  • <= 小于等于

  • >= 大于等于

  • <=> NULL 安全等于,NULL <=> NULL,返回结果是 TRUE

逻辑运算符

  • and 两条件都为真才返回真,否则为假

  • or 两条件有一个为真就是真,全是假才为假

  • not 反转真假

  • xor 仅一个条件为真才返回真,否则返回假。

特殊运算符

  • in :“值在列表中”运算符,用来筛选符合条件的表项,并对符合的项返回一个“真”的布尔值,交给前面 update 或者 delete 进行操作。例如 5 in (1, 3, 5) 返回真。

  • between 范围匹配:例如 5 between 1 and 10 返回真。

  • like 模式匹配:'abc' like 'a%' 返回真。百分号在这里类似 sh 的通配符。

  • IS NULL ”判断表项是否为 NULL“:NULL IS NULL 返回 TRUE。 例子:查看书名有没有叫《xxx 设计 xxx》的

select * from book_table
where BookName like '%设计%';

select * from book_table
where BookName like '_____';
-- 五个下划线代表五个字,返回长度为 5 个字的书名。

在一个书籍管理库中会返回类似《mysql 数据库设计》的书名。

这样一来,上面 where 后面的东西不就能看懂了?

自动维护父表和子表之间的参照完整性

  • CASCADE 级联操作:父表记录删除/更新时,子表中相关记录也被删除/更新。

  • SET NULL 将子表中的外键列设置为 NULL(要求外键列允许 NULL)。

  • RESTRICT 拒绝操作:不允许删除/更新父表中被引用的记录(立即返回错误)。

  • NO ACTION 类似 RESTRICT,但延迟到事务结束时才检查完整性。

  • SET DEFAULT 设置为默认值,MySQL 不支持。

SELECT 语句

select 后面是列名,写上 * 则是选择所有列

select * from student_table
where age=(select max(age) from student_table;);

显示 student_table 中,年龄为“student_table 中‘年龄为最大值’的条目”

升序输出和降序输出

ASC 升序,DESC 降序。

SELECT student_name FROM student_table DESC;

LIMIT a, b 截取内容。a 是索引,从 0 开始,b 是偏移量,写几就偏移几。

SELECT * from `学生表`
ORDER BY `学号` desc
LIMIT 0,3;

怎么把别的表给带上(连接查询)

显式连接

SELECT name, score --name 来自学生表,score 来自分数表
FROM stu_table -- 表一
JOIN score_table -- 表二
ON stu_table.stu_id = score_table.s_id; -- 连接条件
WHERE name = 'Jack' -- 筛选条件,后面还可以接上 AND 或 OR。

name 取自 stu_table score 取自 score_table

隐式连接

SELECT name, score
FROM stu_table, score_table
WHERE stu_table.stu_id = score_table.s_id
AND name = 'Jack';

显然隐式连接更加简洁,所以这里推荐大家使用。

最后更新于