文章目录
sql语句常用语句:
查询和更新指令构成了 SQL 的 DML (数据操作语句)部分:
- SELECT - 从数据库表中获取数据-select
- UPDATE - 更新数据库表中的数据-update
- DELETE - 从数据库表中删除数据-delete
- INSERT INTO - 向数据库表中插入数据-insert into
SQL 中最重要的 DDL (数据定义语句)语句:
-
CREATE DATABASE - 创建新数据库-create database
-
ALTER DATABASE - 修改数据库-alter database
-
CREATE TABLE - 创建新表-create table
-
ALTER TABLE - 变更(改变)数据库表-alter table
-
DROP TABLE - 删除表-drop table
-
CREATE INDEX - 创建索引(搜索键)create index
-
DROP INDEX - 删除索引-drop index
between
使用between and 查询表时,mysql、oracle都会查出头和尾,即:
select * from paper where paper_id between 2 and 4;
会筛选出paper_id 为2、3、4的行。
为已存在的表格增加主键和外键mysql与oracle一样
alter table students add foreign key (course_id) references courses(id);
alter table students add primary key(id);
更新update:
update 表名称 set 列名称 = 新值 where 列名称 = 某值
join查数据:
1.
select paper.name,user.name from paper,user where paper.user_id = user.id;
2.使用内连接 inner join 也可理解为交集,其他的有left join左连接;right join右连接;full join 全连接(并集,mysql不直接支持,oracle支持);
select paper.name,user.name from paper inner join user on paper.user_id = user.id;
select students.name,courses.name from students full join courses on students.course_id =courses.id order by students.name;
select into
用于备份表,或者向其他数据库拷贝表,mysql 不直接支持select into
mysql中的复制表:
复制前备份表未创建新表
create table user_backup select * from user;
复制前已经创建了新表
insert into user_backup select * from user;
MySQL Select into outfile用于导出指定的查询数据到文件如下:
1.导出表中所有数据到C盘根目录outfile.txt中如下:
Select * into outfile 'c://outfile.txt' from test;
2.导出表中指定查询条件2005-06-08号的数据到C盘根目录outfile1.txt中如下:
Select * into outfile 'c://outfile.txt' from test where beginDate='2008-06-08';
oracle中的复制表
select * into courses_backup from courses;
如果在sql/plus或者PL/SQL执行这条语句,会报"ORA-00905:缺失关键字"错误,原因是PL/Sql与T- SQL的区别。
T-SQL中该句正常,但PL/SQL中解释是:
select…into is part of PL/SQL language which means you have to use it inside a PL/SQL block. You can not use it in a SQL statement outside of PL/SQL.
即不能单独作为一条sql语句执行,一般在PL/SQL程序块(block)中使用。
如果想在PL/SQL中实现该功能,可使用Create table newTable as select * from …:
create table NewTable as select * from ATable;
数据类型(data_type)
规定了列可容纳何种数据类型。下面的表格包含了SQL中最常用的数据类型:
数据类型 | 描述 |
---|---|
integer(size) int(size) smallint(size) tinyint(size) | 仅容纳整数。在括号内规定数字的最大位数。 |
decimal(size,d) numeric(size,d) | 容纳带有小数的数字。 “size” 规定数字的最大位数。“d” 规定小数点右侧的最大位数。 |
char(size) | 容纳固定长度的字符串(可容纳字母、数字以及特殊字符)。 在括号中规定字符串的长度。 |
varchar(size) | 容纳可变长度的字符串(可容纳字母、数字以及特殊的字符)。 在括号中规定字符串的最大长度。 |
date(yyyymmdd) | 容纳日期。 |
约束
unique
用第3点中的定义
(语法方面unique的位置与primary key 一样)
UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。每个表可以有多个 UNIQUE 约束,但是每个 表只能有一个 PRIMARY KEY 约束
1.mysql
定义与primary key 在mysql中的位置一样
create table user(id int(2),
name char(12),
unique[unique_name] (id));
撤销unique:
alter table user drop index unique(name);(不会报错,但是实际的unique约束没有被删除)
2.oracle
定义与primary key 在oracle中的位置一样
create table user(id int(2) not null unique,
name char(12),
;)
alter table user add unique(id);(给单个列加unique)
撤销unique:
alter table user drop constraint unique_name;(会报错,因为约束没有名字)
3.给约束命名以及为多列定义unique约束(mysql与oracle一样):
create table user (id int(2) not null,
name char(23) not null,
constraint unique_name unique (id,name)
);
alter table user add constraint unique_name unique (id,name);(给多列加unique,mysql与oracle一样)
撤销(用这种方法):
alter table user drop constraint unique_name;
foreign
用法与unique/primary key 一致,只是在后面多加(紧跟)reference table_name(col_name)
执行人表"Persons"
id_p(主键) | name | age | ||
---|---|---|---|---|
1 | zhang | 23 | ||
2 | han | 22 | ||
3 | ding | 21 |
命令表“Orders”
id_p(主键) | order_id | id_f(外键,对应Person中的id_p) |
---|---|---|
1 | 12345 | 3 |
2 | 23456 | 3 |
3 | 34567 | 2 |
4 | 45678 | 1 |
mysql:
create table Orders (
id_p int(2) not null auto_increment,
order_id int(5) not null,
id_f int(2),
primary key (id_p),
foreign key (id_f) references Person(id_p)
);
oracle:
create table Orders(
id_p int(2) not null primary key,
order_id int(5) not null,
id_f int(2) foreign key references Person(id_p)
);
表已存在时:
alter table Orders add constraint fk_name foreign key(id_f) references Person(id_p);
撤销外键:
mysql
alter table Orders drop foreign key fk_name;
oracle
alter table Orsers drop constraint fk_name
check
CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束,那么该列只允许特定的值。
如果对一个表定义 CHECK 约束,那么此约束会在特定的列中对值进行限制。
create table user(
id int(2) not null auto_increment ,
name char(24) not null ,
age int(3) not null,
primary key(id),
constraint check_name check(age<100 and id<100)
);
alter table user add constraint check_name check(age<100 and id=3);
撤销
mysql:
alter table user drop check check_name;
oracle:
alter table user drop constraint check_name;
default
mysql,oracle:创建时在创建语句后面加上default 默认值
mysql:
alter table user alter name set default '---';
oracle:
alter table user alter column name set default '---';
撤销:
mysql:
alter table user alter name drop default;
oracle:
alter table user alter column name drop default;
index 索引
帮助更快的查找到某几列数据
创建indec_name方便查找到user表的name和age列
create index index_name on user (name,age);
mysql查看索引:
show index from table user;
oracle查看索引:
/*<br>*
*查看目标表中已添加的索引,
*使用plsql Developer查看比较方便
*/
--在数据库中查找表名,oracle中报错,待研究
select * from user_tables where table_name like 'tablename%';
--查看该表的所有索引
select * from all_indexes where table_name = 'tablename';
--查看该表的所有索引列
select * from all_ind_columns where table_name = 'tablename';
/*
*创建索引
*
*/
create index idx01 tablename(column);
drop
仅仅去除表内数据:
truncate table 表名称
increment/identity
sql中把id列设置为自增列,用到identify关键字
create table Persons(
id int(2) primary key identity,
name char(10)
);
oracle中的语法:
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10
上面的代码创建名为 seq_person 的序列对象,它以 1 起始且以 1 递增。该对象缓存 10 个值以提高性能。CACHE 选项规定了为了提高访问速度要存储多少个序列值。
要在 “Persons” 表中插入新记录,我们必须使用 nextval 函数(该函数从 seq_person 序列中取回下一个值):
INSERT INTO Persons (P_Id,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen')
view
创建视图
create view view_name as select name from students where course_id = 3;
查询视图
select * from view_name;
更新视图
create or replace view view_name as select column_name from table_name where condition;
删除视图
drop view view_name;
data(日期)函数
mysql中的内建data函数
函数 | 描述 |
---|---|
NOW() | 返回当前的日期和时间 |
CURDATE() | 返回当前的日期 |
CURTIME() | 返回当前的时间 |
DATE() | 提取日期或日期/时间表达式的日期部分 |
EXTRACT() | 返回日期/时间按的单独部分 |
DATE_ADD() | 给日期添加指定的时间间隔 |
DATE_SUB() | 从日期减去指定的时间间隔 |
DATEDIFF() | 返回两个日期之间的天数 |
DATE_FORMAT() | 用不同的格式显示日期/时间 |
sql server中的内建日期函数:
函数 | 描述 |
---|---|
GETDATE() | 返回当前日期和时间 |
DATEPART() | 返回日期/时间的单独部分 |
DATEADD() | 在日期中添加或减去指定的时间间隔 |
DATEDIFF() | 返回两个日期之间的时间 |
CONVERT() | 用不同的格式显示日期/时间 |
SQL Date 数据类型
MySQL 使用下列数据类型在数据库中存储日期或日期/时间值:
- DATE - 格式 YYYY-MM-DD
- DATETIME - 格式: YYYY-MM-DD HH:MM:SS
- TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
- YEAR - 格式 YYYY 或 YY
SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值:
- DATE - 格式 YYYY-MM-DD
- DATETIME - 格式: YYYY-MM-DD HH:MM:SS
- SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS
- TIMESTAMP - 格式: 唯一的数字
null
查询列中不为null的数据
select name from students where coures_id is not null;
查询列中null的数据
select name from students where coures_id is null;
运行sql脚本
mysql命令行中
source E:/sql/myt.sql;
alter
mysql使用alter修改表结构
一: 修改表信息
1.修改表名
alter table test_a rename to sys_app;
2.修改表注释
alter table sys_application comment '系统信息表';
二:修改字段信息
1.修改字段类型和注释
alter table sys_application modify column app_name varchar(20) COMMENT '应用的名称';
2.修改字段类型
alter table sys_application modify column app_name text;
3.单独修改字段注释
目前没发现有单独修改字段注释的命令语句。
4.设置字段允许为空
alter table sys_application modify column description varchar(255) null COMMENT '应用描述';
5.增加一个字段,设好数据类型,且不为空,添加注释
alter table sys_application add url varchar(255) not null comment '应用访问地址';
6.增加主键
alter table t_app add aid int(5) not null ,add primary key (aid);
7.增加自增主键
alter table t_app add aid int(5) not null auto_increment ,add primary key (aid);
8.修改为自增主键
alter table t_app modify column aid int(5) auto_increment ;
9.修改字段名字(要重新指定该字段的类型)
alter table t_app change name app_name varchar(20) not null;
10.删除字段
alter table t_app drop aid;
11.在某个字段后增加字段
alter table `t_app` add column gateway_id int not null default 0 AFTER `aid`; #(在哪个字段后面添加)
12.调整字段顺序
alter table t_app change gateway_id gateway_id int not null after aid ; #(注意gateway_id出现了2次)
加锁问题
mysql中分析内部解决死锁问题,show engine innodb status;