-- SQL 基础教程, 创建表,按条件选取数据,数据更新,删除
use sakila; -- 数据库, 数据表actor
-- 创建一个表
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
-- 删除一个表
-- drop table persons ;
-- 插入一条记录
insert persons values('1', 'x', 'y', '*路', '北京');
-- 选择
SELECT actor_id, last_name FROM actor;
select * from actor;
-- SELECT DISTINCT 语句用于返回唯一不同的值
select distinct last_name from actor;
-- WHERE 子句用于过滤记录。
select actor_id, last_name from actor where actor_id > 100;
select actor_id, last_name from actor where last_name = "CHASE";
select * from actor where actor_id > 100 and actor_id < 150;
select * from actor where actor_id >100 or actor_id < 50;
select * from actor where (actor_id >100 or actor_id < 50) and first_name like 'G%';
select * from actor where not actor_id > 150;
select * from actor where actor_id between 50 and 150;
select * from actor where first_name in ('NATALIE', 'GARY', 'CARMEN');
select * from actor where first_name is null;
select * from actor where first_name like 'G%';
/* % 表示多个字值,_ 下划线表示一个字符;
M% : 为能配符,正则表达式,表示的意思为模糊查询信息为 M 开头的。
%M% : 表示查询包含M的所有内容。
%M_ : 表示查询以M在倒数第二位的所有内容。
*/
-- ORDER BY 关键字用于对结果集进行排序。
select * from actor order by first_name;
select * from actor order by first_name desc;
select * from actor order by first_name, last_name;
-- INSERT INTO 语句用于向表中插入新记录。
insert into persons (PersonID, LastName, FirstName) values ('2', 'h', 'y');
-- UPDATE 语句用于更新表中的记录。
update actor set first_name = 'SQL' where last_name like 'G%';
-- DELETE 语句用于删除表中的记录。
delete from actor where first_name = 'NICK';