题目均来源于牛客网
SQL29
- 使用join查询方式找出没有分类的电影id以及名称
SELECT f.film_id, f.title FROM film f
WHERE f.film_id NOT IN
(
SELECT fc.film_id FROM film_category fc
JOIN category c
ON c.category_id = fc.category_id
)
SQL30
- 使用子查询的方式找出属于Action分类的所有电影对应的title,description
SELECT f.title, f.description FROM film f,
(SELECT c.name, c.category_id, fc.film_id FROM category c
JOIN film_category fc
ON c.category_id = fc.category_id
) t1
WHERE f.film_id = t1.film_id AND t1.name = 'Action'
SQL32
- 将employees表的所有员工的last_name和first_name拼接起来作为Name
SELECT CONCAT_WS(' ', last_name, first_name) AS `name` FROM employees
mysql 字段拼接的几种方式(常用):
1、mysql 自带语法 CONCAT(string1, string2,…):将 string1 和 string2 等的字符串无缝拼接起来
2、CONCAT_WS(separator, string1, string2,…): 这里的 separator 指的是连接的分隔符,可以是符号、字符串,也可以为 NULL ,将 string1 和 string2 等的字符串带分隔符拼接起来
SQL33
- 创建一个actor表,包含如下列信息
CREATE TABLE actor (
actor_id SMALLINT(5) NOT NULL,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
last_update DATE NOT NULL,
PRIMARY KEY(actor_id)
)
SQL34
- 批量插入数据
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')
SQL35
- 批量插入数据,不使用replace操作
INSERT INTO actor (actor_id, first_name, last_name, last_update)
VALUES(3, 'ED', 'CHASE', '2006-02-15 12:34:33')
ON DUPLICATE KEY UPDATE actor_id = 3
MySQL 插入更新:
ON DUPLICATE KEY UPDATE actor_id = 3
,此题中,actor_id 为主键,这条语句的意思是,如果存在 actor_id 为 3 的数据,就更新这条数据 values()
SQL36
- 创建一个 actor_name 表
CREATE TABLE actor_name (
first_name varchar(45) not null,
last_name varchar(45) not null
);
insert into actor_name
values ('PENELOPE', 'GUINESS'),
('NICK', 'WAHLBERG');
SQL37
- 对first_name创建唯一索引uniq_idx_firstname
DROP TABLE IF EXISTS actor;
CREATE TABLE actor (
actor_id smallint(5) NOT NULL primary key,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update datetime NOT NULL,
unique key uniq_idx_firstname (first_name),
key idx_lastname (last_name)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
注意标点符号
SQL38
- 针对actor表创建视图actor_name_view
CREATE VIEW actor_name_view(first_name_v, last_name_v) AS
SELECT first_name, last_name FROM actor