SQL练习题(3)-牛客网

题目2：

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

<span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span> `employees` ( `emp_no` <span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">11</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
`birth_date` date </span><span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
`first_name` </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">14</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
`last_name` </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">16</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
`gender` </span><span style="color: #0000ff">char</span>(<span style="color: #800000; font-weight: bold">1</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
`hire_date` date </span><span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
</span><span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span> (`emp_no`));

<span style="color: #0000ff">SELECT</span> last_name<span style="color: #808080">||</span><span style="color: #ff0000">‘</span> <span style="color: #ff0000">‘</span><span style="color: #808080">||</span>first_name <span style="color: #0000ff">from</span> employees;

"||"用以连接字符串，在Mysql, SQL server, oracle等也支持用“+”来连接。

在Mysql中，可以用concat函数完成：

<span style="color: #0000ff">SELECT</span> CONCAT(last_name, <span style="color: #ff0000">‘</span> <span style="color: #ff0000">‘</span>, first_name) <span style="color: #0000ff">FROM</span> employees;

还可以用stuff把同列的数据连接起来：http://blog.csdn.net/rolamao/article/details/7745972 

题目3：

创建一个actor表，包含如下列信息

<span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> actor(
    actor_id </span><span style="color: #0000ff">smallint</span>(<span style="color: #800000; font-weight: bold">5</span>) <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
    first_name </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">45</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
    last_name </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">45</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
    last_update </span><span style="color: #0000ff">timestamp</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span> (<span style="color: #0000ff">datetime</span>(<span style="color: #ff0000">‘</span><span style="color: #ff0000">now</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">localtime</span><span style="color: #ff0000">‘</span><span style="color: #000000">))
);</span>

注意，datetime(‘now‘, ‘localtime‘)外面的括号不可省略。

CREATE语句最好再补充为CREATE TABLE IF NOT EXISTS actor(...);

题目4：

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

<span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span><span style="color: #000000"> actor 
(actor_id, first_name, last_name, last_update)
</span><span style="color: #0000ff">VALUES</span><span style="color: #000000">
(</span><span style="color: #800000; font-weight: bold">1</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">PENELOPE</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">GUINESS</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">2006-02-15 12:34:33</span><span style="color: #ff0000">‘</span><span style="color: #000000">),
(</span><span style="color: #800000; font-weight: bold">2</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">NICK</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">WAHLBERG</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">2006-02-15 12:34:33</span><span style="color: #ff0000">‘</span>);

题目5：

<span style="color: #0000ff">INSERT</span> <span style="color: #808080">OR</span> IGNORE <span style="color: #0000ff">INTO</span><span style="color: #000000"> actor
(actor_id, first_name, last_name, last_update)
</span><span style="color: #0000ff">VALUES</span><span style="color: #000000">
(</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">3</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">ED</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">CHASE</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">2006-02-15 12:34:33</span><span style="color: #ff0000">‘</span>);

还可以把“IGNORE”替换成“REPLACE”。

在mysql中，用insert IGNORE into actor（去掉“or”）。

SQL练习题(3)-牛客网

标签：sdn   cti   www.   编程   last   支持   pre   strong   birt