说明:此内容衔接上一次的内容,所有的语句是属于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 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);
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 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;
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;
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; //修改一个字段的名称,此时一定要重新指定该字段的类型
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 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;
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);
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
);
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));
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;
from book_info;
输出结果为:2001
知识点:MySQL语句中substr与substring函数的区别:
同样,
select SUBSTR(REPLACE(book_date,'-',''),4)
from book_info;(输出结果为:10909)
from book_info;(输出结果为:10909)
同理, select SUBSTR(REPLACE(book_date,'-',''),-4)
from book_info;(输出结果为:0909)
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';
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;
from employees
limit 5,5;
解题技巧:此题巧妙利用limit函数实现分页技术;