sql语句实现查询

    说明：此内容衔接上一次的内容,所有的语句是属于sqlite，并且都经过调试，结果都是正确的；

    十八、数据的插入

    创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下：

    实现语句：

   create table if not exists actor_name(
    first_name varchar(45) not null,
    last_name varchar(45) not null
     );
   insert into actor_name select first_name,last_name from actor;

    技术要点：

     1. 用create table 语句建立actor_name 表

     2. 用inset into actor select插入子查询的结果集(不需要用values()，()这种形式。这种形式是手工插入单条数据或多条数据时用圆括号分割。插入结果集是不需要）

     即，插入单条数据或是多条数据时，需要使用values语句进行数据的插入

     例如：insert into table (ziduan1,ziduan2)

                values ('100','aa'),

                            ('200','bb');

        若是插入的结果集，则不需要使用values，直接插入即可；

       例如：insert into table

                  select * 

                  from table2;

     十九、创建表的索引

      针对如下表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')))
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

    实现语句：

   create unique index uniq_idx_firstname on actor(first_name);
   create index idx_lastname on actor(last_name);

  注意：唯一索引：unique index    普通索引：index

    二十、SQL语句实现视图的创建

     针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，fist_name为first_name_v，last_name修改为last_name_v：
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')))

    实现语句:

     create view actor_name_view as
     select first_name as fist_name_v, last_name as last_name_v
     from actor;

     或者是：

    create view actor_name_view(fist_name_v,last_name_v) as
    select first_name,last_name
    from actor;

   二十一、使用强制索引实现查询

     针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。

    实现语句：

   select  *

   from salaries 

   indexed by idx_emp_no

   where emp_no=10005;

  二十二、向已经存在的表中添加、删除、修改字段

  实现语句：

   1.增加一个字段
alter table user add COLUMN new1 VARCHAR(20) DEFAULT NULL; //增加一个字段，默认为空
alter table user add COLUMN new2 VARCHAR(20) NOT NULL;  //增加一个字段，默认不能为空  www.2cto.com  
 
2.删除一个字段
alter table user DROP COLUMN new2; 　 //删除一个字段
 
3.修改一个字段
alter table user MODIFY new1 VARCHAR(10); 　//修改一个字段的类型
 
alter table user CHANGE new1 new4 int;　 //修改一个字段的名称，此时一定要重新指定该字段的类型

   二十三、SQL语句实现创建触发器

    构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);

  实现语句：

  create trigger audit_log

  AFTER INSERT ON employees_test
  FOR EACH ROW
  BEGIN
     insert into audit(EMP_no,NAME) values(new.ID,new.NAME);
  END;

   二十四、SQL语句实现记录删除

  删除emp_no重复的记录，只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

  实现语句：

   delete from titles_test
  where id not in(
  select min(id)
  from titles_test
  group by emp_no
);

  二十五、数据的更新

  实现语句：

  update table 

  set ziduan=''

  where ziduan2='';

    将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。

   实现语句： 

  UPDATE titles_test  
  SET emp_no = REPLACE(emp_no,10001,10005) 
  WHERE id = 5;

   注意：replace（字段名，被替换掉的，替换的）函数的用法；

   二十六、数据库更改表名

   实现语句：

    alter table table1

    rename to ss;(ss表示新的表名)

   二十七、创建完整的表（包括索引、外键、主键）

    实现语句：

    create table table1(

no INT NOT NULL AUTO_INCREMENT,

product_category INT NOT NULL ,
product_id INT NOT NULL ,
customer_id INT NOT NULL ,
PRIMARY KEY (no),
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id) REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT ,
INDEX (customer_id),
FOREIGN KEY (customer_id) REFERENCES customer(id) );

   二十八、创建表的外键约束

    在audit表上创建外键约束，其emp_no对应employees_test表的主键id。

   实现语句：

    drop table audit；

    create table audit(

      EMP_no INT NOT NULL,
    create_date datetime NOT NULL,
    FOREIGN KEY(EMP_no) REFERENCES employees_test(ID));

   二十九、实现字符串的连接

    注意：在sqlite中是通过||实现字符串的连接

   实现语句：

   select  last_name ||  "'" first_name

    from  employees;

  三十、查找字符串中的逗号的个数

  解题技巧：巧用length函数以及replace函数；

  实现语句：

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

   三十一、replace与substring结合使用（MySQL语句实现）

   查询数据类型为varchar类型的日期（2001-09-09）获取其年份

   实现语句：

    select SUBSTRING(REPLACE(book_date,'-',''),1,4)
   from book_info;

   输出结果为：2001

   知识点：MySQL语句中substr与substring函数的区别：

   同样，    select SUBSTR(REPLACE(book_date,'-',''),4)
                from book_info;（输出结果为：10909）

    同理， select SUBSTR(REPLACE(book_date,'-',''),-4)
                from book_info;（输出结果为：0909）

  注意：在substr中正、负数，表示从左右开始数；

  三十二、group_concat的用法

    按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

   实现语句：

   select dept_no,group_concat(emp_no) as employess

   from  emp_dept

   group by dept_no;

  三十三、查询平均工资

   查找排除当前最大、最小salary之后的员工的平均工资avg_salary。

  语句实现：

  select avg(s.salary) as avg_salary
  from salaries s
  where s.salary <> (select max(salary) from salaries)
  and s.salary <> (select min(salary) from salaries) 
  and s.to_date='9999-01-01';

  三十四、利用limit函数显示分页查询

   分页查询employees表，每5行一页，返回第2页的数据

  实现语句：

   select *
   from employees
   limit 5,5;

  解题技巧：此题巧妙利用limit函数实现分页技术；