SQL基本语法

关键词：

USE test; CREATE DATABASE IF NOT EXISTS ccc;

USE ccc;

DROP TABLE IF EXISTS classes, students;

CREATE TABLE classes (

    id BIGINT NOT NULL AUTO_INCREMENT,

    name VARCHAR(100) NOT NULL,

    PRIMARY KEY (id)

) ENGINE = innoDB DEFAULT CHARSET = utf8;

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;

-- 对行的操作

-- 插入记录（行）

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, '四班');

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);

-- 插入或替换

REPLACE INTO students (id, class_id, name, gender, score) VALUES (10, 4, '小', 'F', 855);

-- 插入或更新

INSERT INTO students (id, class_id, name, gender, score) VALUES (10, 4, '小', 'F', 855) ON DUPLICATE KEY UPDATE class_id = 5;

-- 插入或忽略

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (10, 41, '小XIAO', 'F', 8);

-- 快照（复制表）

CREATE TABLE students_of_classes SELECT * FROM students WHERE class_id = 2;

-- 写入查询结果集

-- INSERT INTO statistic SELECT class_id, AVG(score) FROM students GROUP BY class_id;

-- 强制使用指定索引

-- CREATE INDEX idx_class_id ON students(class_id);

-- SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

-- 更新记录（行）

UPDATE students SET name = '小牛' WHERE id = 1;

-- 查询记录（行）

SELECT name FROM students WHERE id = 1;

-- 删除记录（行）

DELETE FROM students WHERE id = 1;

SELECT * FROM students;

-- 对字段（列）的操作

--增

ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;

SELECT * FROM students;

--改

ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

SELECT * FROM students;

--删

ALTER TABLE students DROP COLUMN birthday;

SELECT * FROM students;

-- 隐式事务

SELECT * FROM students;

-- 显式事务

BEGIN;

UPDATE students SET score = score - 100 WHERE id = 1;

UPDATE students SET score = score + 100 WHERE id = 2;

COMMIT;

-- 回滚事务

BEGIN;

UPDATE students SET score = score + 100 WHERE id = 1;

UPDATE students SET score = score - 200 WHERE id = 2;

ROLLBACK;