mySQL笔记2
php主要实现B/S
.net IIS java TomCat
LAMP: Linux 系统 A阿帕奇服务器 Mysql数据库 Php语言(KE)
mysql:c常用代码
create table ceshi1
(
uid varchar (50) primary key,
pwd varchar (50),
name varchar (50),
nation varchar (50),
foreign key (nation) references nation（code）
)
写查询语句需要注意：
1，创建表的时候，最后一列后面不要写逗号
2，如果有多条语句一起执行，注意在语句之间加分号分隔
3，写代码时所有的符号都是半角的
关系型数据库：表和表之间是有关系存在的
创建表的几个关键字：
1，主键： primary key
2，非空：not null
3，自增长列：auto_increment
4,外键关系： foreign key (列名) references 表名（列名）
CRUD操作
1，添加数据
insert into info values("","","",) 要求values括号里面的值得个数要和表里面的列数相同
insert into info (code,name) values ("","") 添加指定列的值
2，修改数据
update info set name = "张三" where code="poo1"
3,删除数据
delete from info where code = "poo1"
4,查询数据
1.普通查询，查所有的
select * from info #查所有数据
select code,name from info #查指定列
2.条件查询
select * from info where code = "poo1" #一个条件
select * from info where name = "张三" and nation = "n001"
#两个条件并的关系
select * from info where name = "张三" or nation = "n001"
#两个条件或的关系
3.排序查询
select * from info order by birthday #默认升序排列 asc ；降序排列 desc
select * from car order by brand asc, oil desc #多列排序
4.聚合函数
select count(*) from info #q取个数
select sum(price) from car #查询price列的和
select avg(price) from car #查询price列的平均值
select max(price) from car #查询price列的最大值
select min(price) from car #查询price列的最小值
5.分页查询
select * from car limit (n-1)*5,5 #跳过 (n-1)*5 条数据取 m 条数据
6.分组查询
select brand from car group by brand #简单的分组查询
select brand from car group by brand having count(*)>2
#查询系列里面车的数量大于2的系列
7.去重查询
select distinct brand from car
8.修改列名
select brand as "系列" from car
9.模糊查询
select * from car where name like "奥迪%" #%代表“奥迪”开头
select * from car where name like "%奥迪%" #包含“奥迪”
select * from car where name like "_奥迪%" #“奥迪”前只有一个
10.离散查询
select * from car where code in ("c001","c002","c003")
select * from car where code not in ("c001","c002","c003")
高级查询：
1.连接查询
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
2.联合查询
select code,name from info
union
select code,name from nation #查询的列相同
3.子查询
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 brand = "值"
#查询某系列的平均油耗
select * from car a where oil < (select avg(oil) from car b where b.brand = a.brand)
