题目描述
存在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;