Mysql:常用代码
C/S: Client Server
B/S: Brower Server
Php主要实现B/S
.net IIS
Jave TomCat
LAMP:L
Mysql:常用代码
Create table ceshi1
(
Uid varchar(50) primary key,
Pwd varchar(50),
Name varchar(50),
Nation varchar(50),
foreign key(nation) references nation(code)
)
写查询语句需要注意:
- 创建表的时候,最后一列不要写逗号
- 如果有多条语句一起执行,在语句之间加分号
- 写代码所有符号都是半角的
关系型数据库:表和表之间是有关系存在的
创建表时的几个关键字
- 主键:primary key
- 非空:not null
- 自增长列:auto_increnment 例:Pwd varchar(50) auto_increnment-----mysql里专用
- 外键关系:foreign key(列名) references 表名(列名)-----mysql里专用
CRUD操作:
- 添加数据:
Insert into info values(‘’,’’,’’) 要求values 括号里的值个数要和表里列数相同
Insert into info (code,name) values(‘’,’’) 添加指定列
- 修改数据
Update info set name =’张三’ where code =’p001’
- 删除数据
Delete from info where code =’p001’
- 查询数据:
普通查询:
Select * from info 查所有的
Select code,name from info 查指定列
条件查询:
Select * from info where code =’p001’
Select * from info where name=’张三’ and nation =’n001’
Select * from info where name=’张三’ or nation =’n001’
排序查询:
Select * from info order by birthday #默认升序asc 降序desc
Select * from car order by brand,oil desc #多列排序
聚合函数:
Select count(*) from info #取个数 可以写*也可以写主键列 一般写主键列(占内存少)
Select sum(price) from car
Select avg(price) from car
Select max(price) from car
Select min(price) from car
分页查询:
Select * from car limit 0,5 #跳过n条数据,取m条数据
分组查询:
Select brand from car group by brand #简单分组查询
Select brand from car group by brand having count (*)>2 #查询系列里面车的数量大于2的系列
去重查询:
Select distinct brand from car
修改列名:
Select brand as ’系列’ from car
模糊查询:
Select * from car where name like ‘_迪%’ %代表任意多个字符 _代表一个字符
离散查询:
Select * from car where code in (‘c001’,’c002’,’c003’)
Select * from car where code not in (‘c001’,’c002’,’c003’)
高级查询:
- 连接查询
Select * from info,nation #得出的结果称为笛卡尔积
Select * from info,nation where info.nation=nation.code
Join on
Select * from info join nation #join 连接
Select * from info join nation on info.nation=nation.code
- 联合查询
Select code,name from info
Union
Select code,name from nation
- 子查询
1) 无关子查询
Select code from nation where name=’汉族’ #取nation表中查询汉族的民族代号
Select * from info where nation=()#在info表中查询民族代号为上一个查询结果的所有信息
Select * from info where nation=(Select code from nation where name=’汉族’)
子查询的结果被父查询使用,子查询可以单独执行的称为无关子查询
2) 相关子查询
Select * from car where oil<(该系列的平均油耗)
Select avg(oil)from car where brang=’值’ #查询某系列的平均油耗
Select * from car a where oil<( Select avg(oil) from car b where b.brang=’a.brand’)