sql题解及笔记220-232

220

SELECT d.dept_no, d.dept_name, t.title, COUNT(t.title) AS count
FROM departments d
JOIN dept_emp de ON d.dept_no = de.dept_no
JOIN titles t ON de.emp_no = t.emp_no
WHERE de.to_date = '9999-01-01' 
  AND t.to_date = '9999-01-01'
GROUP BY d.dept_no, d.dept_name, t.title
ORDER BY d.dept_no ASC, t.title ASC;

COUNT(xx) AS a

每个部门  和 每个职称被分开统计，别再忘记分组了

223

SELECT f.film_id, f.title
FROM film f
LEFT JOIN film_category fc ON f.film_id = fc.film_id
WHERE fc.category_id IS NULL;

干扰项，另一张表没用，category_id已经可以判断

224

不用子查询，注意c.name = 'Action'

SELECT f.title, f.description
FROM film f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id
WHERE c.name = 'Action';

用子查询，有点绕

SELECT f.title, f.description
FROM film f
WHERE f.film_id IN ( --在 film 表中查找对应的电影
    SELECT fc.film_id FROM film_category fc
    WHERE fc.category_id = ( --找"Action" category_id对应的film_id
        SELECT c.category_id FROM category c WHERE c.name = 'Action'
        --找到名称为 "Action" 分类的 category_id
    )
);

226 拼接

SELECT CONCAT(last_name, ' ', first_name) AS Name
FROM employees;

语法：CONCAT(string1, string2, ..., stringN)

227 建表

CREATE TABLE actor (
    actor_id SMALLINT ( 5 ) NOT NULL COMMENT '主键id',
    first_name VARCHAR ( 45 ) NOT NULL COMMENT '名字',
    last_name VARCHAR ( 45 ) NOT NULL COMMENT '姓氏',
    last_update date NOT NULL COMMENT '日期',
PRIMARY KEY ( actor_id ) 
)

通用建表语法

CREATE TABLE table_name (
    column_name1 data_type [constraint], --列级别约束
    column_name2 data_type [constraint],
    ...,
    [table_constraints] --表级别约束
);

数据类型：
整数类型: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
浮点类型: FLOAT, DOUBLE, DECIMAL
字符串类型: CHAR(n), VARCHAR(n), TEXT, BLOB
日期和时间类型: DATE, TIME, DATETIME, TIMESTAMP, YEAR
约束：

NOT NULL: 确保列不能存储 NULL 值。
DEFAULT: 为列指定默认值。
UNIQUE: 确保列中的所有值唯一。
PRIMARY KEY: 定义一个或多个列为表的主键。
FOREIGN KEY: 定义一个外键约束，引用另一个表的主键。
CHECK: 确保列中的值满足指定条件。
AUTO_INCREMENT: 为整数类型的列定义自动增长。

228 插入数据

INSERT INTO actor (actor_id, first_name, last_name, last_update)
VALUES
(1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');

229 插入新数据并忽略重复数据

INSERT IGNORE INTO actor (actor_id, first_name, last_name, last_update)
VALUES ('3', 'ED', 'CHASE', '2006-02-15 12:34:33');

INSERT IGNORE 忽略任何违反唯一性约束的插入操作

230 数据导入新表

CREATE TABLE actor_name (
    first_name VARCHAR(45) NOT NULL,
    last_name  VARCHAR(45) NOT NULL
);

-- 导入
INSERT INTO actor_name (first_name, last_name)
SELECT first_name, last_name FROM actor;

231 创建索引

ALTER TABLE actor
ADD UNIQUE INDEX uniq_idx_firstname (first_name);
ALTER TABLE actor
ADD INDEX idx_lastname (last_name);

ALTER TABLE 创建索引的语法

ALTER TABLE table_name
ADD INDEX index_name (column_name);

ALTER TABLE table_name
ADD UNIQUE INDEX index_name (column_name); 唯一

232 创建视图

CREATE VIEW actor_name_view (first_name_v, last_name_v) AS
SELECT first_name, last_name FROM actor;

CREATE VIEW actor_name_view AS
SELECT first_name AS first_name_v, last_name AS last_name_v FROM actor;

通用语法，第二种常用

--带列名重命名的视图创建方式
CREATE VIEW view_name (new_column1, new_column2, ...)
AS
SELECT column1, column2, ...FROM table_name;

--在 SELECT 语句中使用别名的视图创建方式
CREATE VIEW view_name AS
SELECT column1 AS new_column1, column2 AS new_column2, ...
FROM table_name;