Mysql入门 原
一、数据库介绍
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';
1、基础语句
select * from money;
2、指定
select id,username from money;
3、不重复
select distinct age depthon from money;
4、条件
select * from money where age=29;
select * from money where id<10 and province='北京';
5、结果集排序
select id,username from money order by balance dese;
select id,username from money order by balance asc;
asc升序
6、多字段排序
select id,username from money order by balance desc,age asc;
7、结果集限制
select id,username,balance from money limit 5;
8、限制结果集排序
select id,username,balance from money order by balance desc limit 5;
9、结果集区间选择（分页）
select id,username from money limit 0,3;
10、统计
函数 | 说明 | 操作 |
|---|---|---|
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; |
11、分组统计
(1)分组
select * money group by province;
(2)分组统计个数
select count(province) as result,province from money group by province;
(3)分组统计后再统计
select count(province),province from money group by province with rollup;
(4)结果再过滤
select count(province) as result,province from money group by province with having result>2;
四、表连接
1、内连接
方式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;
2、外连接
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);
六、记录操作
1、记录联合
select * from user where uid in(1,3,4);
select uid from user union select uid from order_goods;
换个数据库
2、更新记录
update up set balance=balance-15555500 where userid=15;
3、修改多字段
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;
4、删除记录
delete from up where userid=1;
5、清空记录
truncate table up;
七、数据控制语言
其他问题：
1、字符集乱码问题修改：
清空数据后重新插入即可