Mysql-2

题目描述

存在actor表，包含如下列信息：

CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')));

现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’0000 00:00:00’

笔记:时间格式应为‘0000-00-00 00:00:00’

alter table actor add column create_date datetime not null default '0000-00-00 00:00:00';

题目描述

创建一个actor表，包含如下列信息
列表	类型	是否为NULL	含义
actor_id	smallint(5)	not null	主键id
first_name	varchar(45)	not null	名字
last_name	varchar(45)	not null	姓氏
last_update	timestamp	not null	最后更新时间，默认是系统的当前时间

create table if not exists actor(
    actor_id smallint(5) not null,
    first_name varchar(45) not null,
    last_name varchar(45) not null,
    last_update timestamp not null default(datetime('now','localtime')),
    primary key(actor_id)
);

笔记:创建表结构语法

create table [if not exists] 表名

题目描述

对于表actor批量插入如下数据

CREATE TABLE IF NOT EXISTS actor (
	actor_id smallint(5) NOT NULL PRIMARY KEY,
	first_name varchar(45) NOT NULL,
	last_name varchar(45) NOT NULL,
	last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
	actor_id	first_name	last_name	last_update

1	PENELOPE	GUINESS	2006-02-15 12:34:33
2	NICK	WAHLBERG	2006-02-15 12:34:33

insert into actor values(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
                          (2,'NICK','WAHLBERG','2006-02-15 12:34:33');

题目描述

查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

CREATE TABLE `employees` (
	`emp_no` int(11) NOT NULL,
	`birth_date` date NOT NULL,
	`first_name` varchar(14) NOT NULL,
	`last_name` varchar(16) NOT NULL,
	`gender` char(1) NOT NULL,
	`hire_date` date NOT NULL,
	PRIMARY KEY (`emp_no`));
	CREATE TABLE `salaries` (
	`emp_no` int(11) NOT NULL,
	`salary` int(11) NOT NULL,
	`from_date` date NOT NULL,
	`to_date` date NOT NULL,
	PRIMARY KEY (`emp_no`,`from_date`));

select tb1.emp_no,(tb2.salary-tb3.salary) as growth
from employees as tb1
inner join salaries as tb2
on tb1.emp_no=tb2.emp_no
and tb2.to_date='9999-01-01'
inner join salaries as tb3
on tb1.emp_no=tb3.emp_no
and tb3.from_date=tb1.hire_date
order by growth asc;

题目描述

将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

OJ上通过的代码:不支持concat

select (last_name || ' ' || first_name) as 'Name'
from employees;

Mysql支持的代码

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

题目描述

查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。
钻漏洞的方法
select 2 as cnt;
其余解法

select ( length('10,A,B') - length(replace('10,A,B',",",'')) ) as cnt;