sql基础部分语句整理

-- 创建表

-- create table t_person(

-- p_personId int(11) AUTO_INCREMENT,

-- personName VARCHAR(50) not null,

-- personAddress VARCHAR(50) not null,

-- personSex bit default 0,

-- personAge int not null,

-- check (personAge > 0),

-- PRIMARY key(p_personId)

-- )

-- 快速添加数据使用该命令

insert into t_user (u_name,u_pwd,u_age,u_sex,u_p_id) SELECT u_name,u_pwd,u_age,u_sex,u_p_id from t_user

-- 添加

-- insert into 表名 (字段名，字段名) values (值1，值2)

insert into t_person values (null,'lisi','绵阳',1,20);

insert into t_person (personName,personAddress,personSex,personAge)

select personName,personAddress,personSex,personAge from t_person

-- 修改

-- update 表名 set 列名1 = 新的值,列名2 = 新的值

-- update t_person set personSex = 1,personAge = personAge+1

-- 删除

-- delete from 表名

-- delete from t_person

-- TRUNCATE t_person

-- 查询

-- select p_personId,personName,personAddress,personSex,personAge from t_person

-- select * from t_person

-- 根据条件查询

-- where 语句

-- SELECT p_personId,personName,personAddress,personSex,personAge

-- from t_person where personAge <> 20 or personName ='lisi'

-- SELECT p_personId,personName,personAddress,personSex,personAge

-- from t_person where personAddress is not null

-- SELECT p_personId,personName,personAddress,personSex,personAge

-- from t_person where personName like '%liu%'

SELECT p_personId,personName,personAddress,personSex,personAge

from t_person where personAge > 20 and personAge <30

SELECT p_personId,personName,personAddress,personSex,personAge

from t_person where personAge BETWEEN 21 and 29

SELECT p_personId,personName,personAddress,personSex,personAge

from t_person where personAge not in (18,19,30)

-- 分组查询 如果用了分组，select后面或者having后面只能是GROUP BY后面的字段或者聚合函数

-- select personSex,count(*) from t_person where personAddress='成都' GROUP BY personSex

select personSex,personAddress,count(*) from t_person GROUP BY personSex,personAddress HAVING count(*) < 4

-- 聚合函数 sum(字段名) avg(字段) max() min()

-- select count(*) from t_person where personAddress is not null

-- order by排序 ASC升序

select * from t_person ORDER BY personAge DESC,p_personId DESC

-- 链接查询

-- 内链接 表示两个表中有相同的数据才查询

-- 外链接

-- select * from t_stu,classinfo where classinfo.classId = t_stu.classId

select * from t_stu s join classinfo c on s.classId = c.classId

select * from classinfo c RIGHT OUTER JOIN t_stu s on s.classId = c.classId

-- UNION去重链接

select * from t_stu s LEFT JOIN classinfo c on s.classId = c.classId

UNION

select * from t_stu s RIGHT JOIN classinfo c on s.classId = c.classId

select 'a'

union all

select 'a'

-- SELECT DISTINCT 去重查询

-- 子查询

-- select className from classinfo where classId in (select classId from t_stu where stuName='dgh' or stuName='sdf')