SQL数据

练习脚本

下载廖雪峰老师的MySQL练习脚本,右键另存为init-test-data.sql

脚本内容如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- 如果test数据库不存在,就创建test数据库:
CREATE DATABASE IF NOT EXISTS test;

-- 切换到test数据库
USE test;

-- 删除classes表和students表(如果存在):
DROP TABLE IF EXISTS classes;
DROP TABLE IF EXISTS students;

-- 创建classes表:
CREATE TABLE classes (
id BIGINT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 创建students表:
CREATE TABLE students (
id BIGINT NOT NULL AUTO_INCREMENT,
class_id BIGINT NOT NULL,
name VARCHAR(100) NOT NULL,
gender VARCHAR(1) NOT NULL,
score INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入classes记录:
INSERT INTO classes(id, name) VALUES (1, '一班');
INSERT INTO classes(id, name) VALUES (2, '二班');
INSERT INTO classes(id, name) VALUES (3, '三班');
INSERT INTO classes(id, name) VALUES (4, '四班');

-- 插入students记录:
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'M', 90);
INSERT INTO students (id, class_id, name, gender, score) VALUES (2, 1, '小红', 'F', 95);
INSERT INTO students (id, class_id, name, gender, score) VALUES (3, 1, '小军', 'M', 88);
INSERT INTO students (id, class_id, name, gender, score) VALUES (4, 1, '小米', 'F', 73);
INSERT INTO students (id, class_id, name, gender, score) VALUES (5, 2, '小白', 'F', 81);
INSERT INTO students (id, class_id, name, gender, score) VALUES (6, 2, '小兵', 'M', 55);
INSERT INTO students (id, class_id, name, gender, score) VALUES (7, 2, '小林', 'M', 85);
INSERT INTO students (id, class_id, name, gender, score) VALUES (8, 3, '小新', 'F', 91);
INSERT INTO students (id, class_id, name, gender, score) VALUES (9, 3, '小王', 'M', 89);
INSERT INTO students (id, class_id, name, gender, score) VALUES (10, 3, '小丽', 'F', 85);

-- OK:
SELECT 'ok' as 'result:';

运行脚本

cmdcdsql文件所在目录
方法一:

1
mysql -u root -p < init-test-data.sql

方法二:

1
2
3
mysql -u root -p
// 输入密码
\. init-test-data.sql

DEBUG

  • 报错:ERROR 1366 (HY000) at line 29: Incorrect string value: '\x80\xE7\x8F\xAD' for column 'name' at row 1
    • 脚本开头添加语句:set character set utf8;
  • 中文信息乱码:可能是cmd的问题,解决方案参考文章

外键约束

参考前一篇文章外键部分的内容

定义外键约束

1
2
3
4
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

外键约束的名称fk_class_id可以任意,FOREIGN KEY (class_id)指定了class_id作为外键,REFERENCES classes (id)指定了这个外键将关联到classes表的id列(即classes表的主键)

删除外键约束

1
2
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

创建索引

参考前一篇文章索引部分的内容

  • score列,创建了一个名称为idx_score,使用列score的索引

    1
    2
    ALTER TABLE students
    ADD INDEX idx_score(score);
  • 如果索引有多列

    1
    2
    ALTER TABLE students
    ADD INDEX idx_name_score (name, score);
  • 唯一索引

假设students表的name不能重复

1
2
ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);
  • 添加唯一约束而不创建唯一索引
    1
    2
    ALTER TABLE students
    ADD CONSTRAINT uni_name UNIQUE (name);
打赏
  • 版权声明: 本博客所有文章除特别声明外,著作权归作者所有。转载请注明出处!
  • Copyrights © 2020-2021 zhangguoliu
  • 访问人数: | 浏览次数:

请我喝杯咖啡吧~

支付宝
微信