1、数据库服务器
2、数据库:按照用途及业务,分类使用
3、数据表:不同数据
4、数据字段:也叫数据列,英文
5、数据行:格式
创建 | 显示 | 删除 | 使用 | |
---|---|---|---|---|
库 | create database php; | show databases; | drop database php; | use php; |
表 | create table use (,)); | show tables; | drop table user; | |
字段 | desc user; show columns from user; describe db; |
命令 | |
---|---|
增加 | alter table user add first/after xxx; |
删除 | alter table user drop bbb; |
修改 | alter table user modify username varchar(20); alter table user modify old new; alter table old rename new; alter table user change old new; |
字段 | 整型 | tinyint smallint mediumint bigint |
---|---|---|
浮点型 | float double decimal | |
字符型 | char varchar text blob | |
时间型 | date time datetime year | |
复合型 | set enum | |
引擎 | MYISAM(效率) InnDB(写入事处) Achive(压缩) NBD | |
索引 | 普通 | alter table user index(username); |
唯一 | alter table user add UNIQUE(username); | |
全文 | alter table user add FullTEXT(username); | |
主键 | alter table user add PRIMARY KEY(username); |
操作 | |
---|---|
单条插入 | insert into user values( , , ); insert into user( , , )values( , , ); |
多条插入 | insert into user( , , ); value( , , ); |
show variables like "%char%";
SET character_set_client='utf8';
SET character_set_connection='utf8';
SET character_set_database='utf8';
SET character_set_results='utf8';
select * from money;
select id,username from money;
select distinct age depthon from money;
select * from money where age=29;
select * from money where id<10 and province='北京';
select id,username from money order by balance dese;
select id,username from money order by balance asc;
asc升序
select id,username from money order by balance desc,age asc;
select id,username,balance from money limit 5;
select id,username,balance from money order by balance desc limit 5;
select id,username from money limit 0,3;
函数 | 说明 | 操作 |
---|---|---|
count | 计数 | select count(id) as zongshu from money; |
count | 求和 | select count(id) sum(balance) from money; |
max | 最大值 | select max(balance) from money; |
min | 最小值 | select min(balance) from money; |
avg | 平均数 | select avg(balance) from money; |
select * money group by province;
select count(province) as result,province from money group by province;
select count(province),province from money group by province with rollup;
select count(province) as result,province from money group by province with having result>2;
方式1:
select u.uid,u.username as username,o.oid,o.uid,o.name as shopname from user u,order_goods o where u.uid=o.uid;
方式二:
select user.uid,user.username as username,order_goods.oid,order_goods.uid,order_goods.name as shopname from user inner join order_goods on user.uid=order_goods.uid;
select * from user left join order_goods on user.uid=order_goods.uid;
左连接
select * from user right join order_goods on user.uid=order_goods.uid;
右连接
select * from user where uid in(1,3,4);
select * from user where uid in (select uid from order_goods);
select * from user where uid in(1,3,4);
select uid from user union select uid from order_goods;
换个数据库
update up set balance=balance-15555500 where userid=15;
update money set balance=balance-500,username='李文凯' where userid=15;
undate money m,user u m.balance=m.balance*u.age where m.userid=u.id;
delete from up where userid=1;
truncate table up;
1、字符集乱码问题修改:
清空数据后重新插入即可