mysql和Oracle常用命令笔记

 

https://www.runoob.com/sql/sql-insert-into-select.html

登录：mysql -u 账号 -p 密码

 

建表语句：

CREATE TABLE table_name
(
column_name1 varchar(size) not null primary key,
column_name2 int(size) DEFAULT '999',
column_name3 date DEFAULT GETDATE(),
....
);

多个主键字段：
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

create table table_name_new as select * from table_name_old;    复制表结构和数据

create table table_name_new as select * from table_name_old where 1=2;    只复制表结构

 

mysql、oracle增删改查：

insert into table_name values ('','','');           insert into table_nameA select * from table_nameB;

delete * from table_name where id ='xxx';

update table_name set id='yyy' , age ='aaa' where id between '1' and '10';

select * from table_name;

 

Oracle进入表编辑状态，手动修改数据：

select * from table_name for update;

 

drop table xxx;          删除表操作

truncate table xxx;    清空表数据，无需commit操作

 

alter修改表结构：

添加列：ALTER TABLE table_name ADD column_name datatype

删除列：ALTER TABLE table_name DROP COLUMN column_name

修改数据类型：ALTER TABLE table_name MODIFY COLUMN column_name datatype

                         ALTER TABLE table_name ALTER COLUMN DateOfBirth year   也可以用alter

添加约束：ALTER TABLE Persons MODIFY Age int NOT NULL;

添加主键：ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

                  ALTER TABLE Persons ADD PRIMARY KEY (P_Id)   --一个主键字段

删除主键：ALTER TABLE Persons DROP CONSTRAINT pk_PersonID

 

联合查询：MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

 

分页：

http://uusama.com/458.html

select * from orders_history where type=8 limit 1000,10;    --第1001起，取10条，即1001-1010.

select * from orders_history where type=2 and id between 1000000 and 1000100 limit 100;   

select * from orders_history where id >= 1000001 limit 100;

不建议用第一种方式，取的条数越往后，查询时间越长。

 

游标：

https://www.cnblogs.com/oukele/p/10684639.html

 

存储过程：

https://www.cnblogs.com/geaozhang/p/6797357.html

https://www.runoob.com/w3cnote/mysql-stored-procedure.html