常用SQL语句

---- 创建表
–create table user_name
–(
– userid int,
– uname varchar(12)
–);
–添加字段
–alter table user_name add (age int);
--修改字段字段大小
– alter table user_name modify (uname varchar(10));
– 删除字段
– ALTER TABLE user_name DROP COLUMN age ;
–删除表
–drop table user_name;

–创建表并设置字段自增
CREATE TABLE POJO (
UID INT auto_increment primary key,
UNAME VARCHAR(20) NOT NULL,
UAGE INT NOT NULL
);
– UID INT auto_increment primary key 设置为主键 并且自增 默认自增1
ALTER TABLE POJO auto_increment=2;
– 定义自增的值
– UNAME VARCHAR(20) NOT NULL, 定义字段不能为空

–在表中添加字段信息
–insert into user_name values (1,‘xiaohong’);
–insert into user_name values (2,‘xiaoming’);
–insert into user_name values (2,‘xiaoming’);
–insert into user_name values (2,‘xiaoming’);

– 提交每条 DML 执行都是在内存中完成，只有在执行 COMMIT 后对数据的操纵方才生效。
– commit

– 回退 撤销上次使用 COMMIT 语句以来的当前事务中所做的全部或部分更改。
– rollback

–添加一条数据
–insert into user_name values (3,‘laoba’);
–insert into user_name (userid,uname,age) values (4,‘xiaowang’,22);
–根据条件修改
update user_name set uname=(select upwd from pwd where uadmin=123);
–update user_name set (userid,uname,age)=(select userid,uname,age from user_name
–where uname=‘xiaohong’)where uname=‘xh’;
–根据条件删除字段值
delete from pwd where upwd=(select upwd from pwd where uadmin=‘123’);

----查询表
select * from user_name;‘’
----分页查询表查询表3行
select * from user_name limit 3;
–查询表结构
–desc user_name;
–查询指定字段
–select userid from user_name;
–取消重复
–select distinct userid,uname from user_name;
–使用算术表达式：
–select userid*12,uname from user_name;
–给字段设置字段别名
–select userid as “编号”,uname “名字” from user_name;
–连接字符串
–select uname||‘的id是’||userid as “userid” from user_name;

– 创建Student表
create table Student(
s_id NUMBER not null,
s_name VARCHAR(10),
s_sex VARCHAR(5),
s_age NUMBER
);
–创建一个学生表
create table Student_bak(
s_id NUMBER not null,
s_name VARCHAR(10),
s_sex VARCHAR(5),
s_age NUMBER
);

–添加一条语句但是不给s_age值
insert into STUDENT(S_ID,S_NAME,S_SEX) values(1,‘张三’,‘男’);
insert into STUDENT(S_ID,S_NAME,S_SEX,S_AGE) values(2,‘李四’,‘女’,20);
insert into STUDENT_BAK(S_ID,S_NAME,S_SEX,S_AGE) values(1,‘zhangsan’,‘nan’,18);
insert into STUDENT_BAK(S_ID,S_NAME,S_SEX,S_AGE) values(2,‘lisi’,‘女’,20);

– 修改STUDENT表中的s_age值让s_age=user_name表中userid=4的age,根据user_name表中的unmae=123的usrid找到的student表中的s_id,根据s_id修改s_age;
update STUDENT set s_age=(select age from user_name where userid=4) where S_ID=(select USERID from USER_NAME where uname=123);

– 使用子查询为STUDENT_BAK表插入数据
– 注意: 这两张表必须,数据类型相同,并且约束相同,表名和字段名可以不同也可以使用/APPEND/函数
insert into STUDENT_BAK(s_id,s_name,s_sex,s_age) select s_id,s_name,s_sex,s_age from STUDENT;

– 根据条件删除一跳数据
delete STUDENT_BAK where s_id=1;
delete STUDENT where s_id=1;
–修改student_bak表 根据student表中的name和sex查到age和id进行修改
update STUDENT_BAK set s_age=(select s_age from STUDENT where s_name=‘张三’) where s_id=(select s_id from STUDENT where s_sex=‘男’);

–清空表中数据
truncate table student;
truncate table student_bak;

–合并查询取消重复
select s_id from STUDENT where s_id>0 union
select s_age from STUDENT where s_age=20;

–合并查询不取消重复
select s_id from STUDENT where s_id>0 union all
select s_age from STUDENT where s_age=20;

–合并查询
select * from Student, STUDENT_BAK;
– inner join
select * from STUDENT a inner join STUDENT_BAK b on a.S_ID = b.S_ID;
–left outer join
select * from STUDENT a left join STUDENT_BAK b on a.S_ID = b.S_ID;