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];
分页:
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