数据库与SQL

SQL

总的来说，SQL语言定义了这么几种操作数据库的能力：

• DDL：Data Definition Language

DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

• DML：Data Manipulation Language

DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。

• DQL：Data Query Language

DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

查询 SELECT

-- SELECT * FROM [table-name]
-- SELECT * FROM [table-name] WHERE [condition]
SELECT * FROM students WHERE scores <> 80;
SELECT * FROM students WHERE scores >= 80 AND gender = 'M';
SELECT * FROM students WHERE names LIKE '%bc%';

-- 投影
-- SELECT [column-names] FROM [table-name] WHERE [condition]
SELECT id, scores points, names FROM students; 

-- 排序
-- SELECT [column-names] FROM [table-name] ORDER BY [column-names] [ASC/DESC]
SELECT id, names, scores, gender FROM students ORDER BY scores DESC, gender ASC;
SELECT id, names, scores, class_id FROM students WHERE class_id = 1 ORDER BY scores DESC;

-- 分页
-- SELECT [column-names] FROM [table-name] LIMIT [page-size] OFFSET [offset];
SELECT id, names, scores FROM students ORDER BY scores DESC LIMIT 3 OFFSET 5;

-- 聚合函数 COUNT(), SUM(), AVG(), MAX(), MIN()
-- SELECT FUNC([column-name]) [result-name] FROM [table-name];
SELECT COUNT(*) num FROM students;

-- 分组聚合
-- SELECT FUNC([column-name]) [result-name] FROM [table-name] GROUP BY [column-name];
SELECT class_id, gender, AVG(scores) FROM students GROUP BY class_id, gender;

-- 多表查询
-- 结果是 MxN 行记录
SELECT s.id student_id, s.names, c.id class_id FROM students s, classes, c;

-- 连接查询
-- SELECT [column-names] FROM [table-name] [INNER/LEFT OUTER/RIGHT OUTER/FULL OUTER] JOIN [table-name] ON [condition];
-- INNER [-(+]-)  LEFT OUTER [+(+]-)  RIGHT OUTER [-(+]+)  FULL OUTER [+(+]+)
SELECT s.id, s.names, s.scores, c.names class_name 
FROM students s 
INNER JOIN classes c 
ON s.class_id = c.id;

增 INSERT

-- INSERT INTO [table-name] ([column-names]) VALUES ([values]);
INSERT INTO students (names, score, gender) 
VALUES 
    ('Tom', 98, 'M'),
    ('Bob', 60, 'M');

改 UPDATE

-- UPDATE [table-name] SET [column-name]=[value] WHERE [condition]
UPDATE students SET score=55 WHERE id=1;
UPDATE students SET score=score+10 WHERE id>5 AND id<10;

• 不加条件修改所有记录

删 DELETE

-- DELETE FROM [table-name] WHERE [condition]
DELETE FROM students WHERE id=1;

• 不加条件删除所有记录

MySQL

MySQL Client的可执行程序是 mysql，MySQL Server的可执行程序是 mysqld。

MySQL Client和MySQL Server的关系如下：

┌──────────────┐  SQL   ┌──────────────┐
│ MySQL Client │───────>│ MySQL Server │
└──────────────┘  TCP   └──────────────┘

通过 MySQL Client 登录/退出 MySQL

mysql -u root -p

exit

在MySQL Client中输入的SQL语句通过TCP连接发送到 MySQL Server。默认端口号是3306，即如果发送到本机 MySQL Server，地址就是127.0.0.1:3306。

也可以只安装MySQL Client，然后连接到远程MySQL Server。假设远程MySQL Server的IP地址是10.0.1.99，那么就使用-h指定IP或域名：

mysql -h 10.0.1.99 -u root -p

数据库

SHOW DATABASES;

CREATE DATABASE test;

DROP DATABASE test;

USE test;

表

SHOW TABLES;

DESC [table-name];

SHOW CREATE TABLE [table-name];

CREATE TABLE [table-name] (
    [column-name] [TYPE] NOT NULL,
    [column-name] [TYPE] NOT NULL,
    PRIMARY KEY ([column-name])
)

DROP TABLE [table-name];

ALTER TABLE [table-name] ADD COLUMN [column-name] [TYPE] NOT NULL;
ALTER TABLE [table-name] CHANGE COLUMN [column-name] [TYPE] NOT NULL;
ALTER TABLE [table-name] DROP COLUMN [column-name];

数据类型

• int / bigint
• real / double / decimal(m,n)
• char(n) / varchar(n)
• boolean
• date / time / datetime

每日复习

• MySQL
  • 创建 mydb 数据库
  • 切换到 mydb 数据库
  • 创建 mytab 表（id，name，score）
  • 增加一列、修改列名和类型、删除一列
• SQL
  • 增加记录
  • 按条件修改单条、多条记录
  • 条件查询、排序、分页、聚合函数、分组聚合、连接查询
• MySQL
  • 删除 mytab 表
  • 删除 mydb 数据库

附录：测试用SQL脚本

-- 如果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:';