爱站程序员基地数据库的基本操作
- 条件查询
- 限制结果集
- 排序
- 分组
- 聚合函数
- 多表联合查询
- DCL 数据库控制语句
mysql
两千条是个坎 2000条以下全表扫描 速度很快 不用加索引 但是超过2000条需要我们优化
条件 查询
| 符号 | 说明 |
|---|---|
| = | |
| >= | |
| <= | |
| != | |
| > | |
| < | |
| or | 或者 |
| and | 并且 |
select * from articles where id<3 or name=‘二十不惑’;
限制结果集 limit
mysql> select * from articles limit 2;
结果集区间
select * from articles limit 0,10; #从0开始取10条数据
分页 :1.知道用户向看第几页的数据 通过浏览器知道结果 可以拿到2.每页我们应该显示多少条 我们自己规定好的 可以拿到用户想看的 页数 开始的索引 每页显示的条数1 0 92 9 93 18 9（用户想看的页数-1）*每页显示的条数
排序 order by
- desc 降序
- asc 正序
select * from post where id>10 and id <100 ORDER BY id asc limit 10,10;
聚合函数
- count 统计数目
- sum 求和
- avg 求平均数
- max 求最大值
- min 求最小值
select count(id) as 总数 from stars;select sum(id) as 总和 from stars;select avg(id) as 平均数 from stars;select max(id) as 最大值 from stars;select min(id) as 最小值 from stars;
分组 group by
SELECT province FROM stars group by province; # 对省份进行分组 每个省份显示1个# 需求 每个省份有多少个明星select COUNT(province) as 省份 FROM stars;select count(province) as 人数,province from stars group by province;人数 省份2 山东1 河北1 河南2 湖北
分组结果再过滤 having
select count(province) as result,province from stars group by province HAVING result>1;
模糊查询 like
select * from stars where username like \'王宝鹏\';select * from stars where username like \'%鹏%\';select * from stars where username like \'%鹏\';
查询区间 between and
SELECT * from stars where balance BETWEEN 4000 and 6000;
总结
select 你选择的列from 表名where 条件 like between andgroup by 分组 having 过滤条件order_by 排序limit 限制结果集SELECT username,balance from stars where id>1 and balance BETWEEN 4000 and 6000 ORDER BY balance asc limit 2;
外键注意条件
- 只有innodb引擎支持外键
- 外键字段 必须跟另外表的字段 要一致 比如 都是 int （11） 都是无符号 都不能为空
多表联合查询 重点
-
内连接
语法一
SELECT users.username as 用户名,`order`.`name`,`order`.buy_time FROM users,`order` where users.uid = `order`.uid;SELECT u.username,o.`name`,o.buy_time FROM users u,`order` o where u.uid = o.uid;
-
表1 inner join 表2 on 条件
SELECTusers.username AS 用户名,`order`.`name` AS 商品名,`order`.buy_time AS 购买时间FROMusersINNER JOIN `order` ON users.uid = `order`.uid;SELECTu.username AS 用户名,o.`name` AS 商品名,o.buy_time AS 购买时间FROMusers uINNER JOIN `order` o ON u.uid = o.uid;
外连接
- 左连接 表1 left join 表2 on 条件
SELECTu.username AS 用户名,o.`name` AS 商品名,o.buy_time AS 购买时间FROMusers uLEFT JOIN `order` o ON u.uid = o.uid;#用户表为准 lef join左边的表为准 买的和没买的都查出来-- case WHEN `order`.name is NULL THEN 精打细算 else `order`.name endSELECTu.username AS 用户名,(case WHEN o.`name` is NULL THEN \'精打细算\' else o.`name` end) AS 商品名,(case WHEN o.`buy_time` is NULL THEN \'等待何时\' else o.`buy_time` end) AS 购买时间FROMusers uLEFT JOIN `order` o ON u.uid = o.uid;SELECTu.username AS 用户名,IFNULL(o.`name`,\'精打细算\') AS 商品名,IFNULL(o.buy_time,\'等待何时\') AS 购买时间FROMusers uLEFT JOIN `order` o ON u.uid = o.uid;-------------------------------------------------SELECTu.username AS 用户名,o.`name` AS 商品名,o.buy_time AS 购买时间FROMusers uRIGHT JOIN `order` o ON u.uid = o.uid;#以订单表为准 right join右边的表为准-------------------------------------------------
子查询
- in
select * from users where uid in(1,2,3,4,5);select * from users where uid in(select uid from `order`);
联合查询
将两个表的结果并在一起显示
注意条件: 字段数量要一致 字段名称尽量一致
- union all
- union
SELECT uid FROM users union all SELECT uid from `order`;SELECT uid FROM users union SELECT uid from `order`; 将 union all的结果去重后显示
DCL 语句
-
添加用户并且授权
grant 权限 on 库.表 to \'用户\'@\'主机\' identified by \'密码\' with grant option;grent all on *.* to root@\'%\' identified by \'123456\' with grant option;权限 : all insert delete update select库: 库名 *代表所有的库用户:用户名 如果有就是授权 没有就是新添加用户主机:ip % 代表任何主机都可以访问mysql> use mysql;Database changedmysql> grant insert,select on whpython.* to \'tangyu\'@\'%\' identified by \'123456\' with grant option;mysql> flush privileges; #刷新权限
-
删除权限
revoke 权限 on 库.表 from \'用户\'@\'主机\';mysql> use mysql;Database changedmysql> revoke insert on whpython.* from \'tangyu\'@\'%\';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)
一条 查询 SQL语句 经历了什么
select * from users where id>1;
数据库高级应用
mysql查看帮助
- ?
- help contents 配合 ？
表结构复制 和表数据复制
create table t1 like t2; #表结构复制insert into t1 select * from t2;#表数据复制
索引
- 普通索引
- 唯一索引
- 主键索引
- 复合索引
- 全文索引
普通索引
任何字段都可以添加 普通索引 重复值 空都不是问题
? indexcreate index 不能创建主键索引alter tablealter table 表名 add index(字段)mysql> alter table articles drop index name; #删除指定的索引mysql> alter table articles add index in_name(name); #添加索引 并且给索引起个名字mysql> show index from articles\\G #列出所有的索引
唯一索引
alter table 表名 add unique 索引名称(字段名);mysql> alter table articles add unique un_name(title);Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table articles drop index un_name; #添加删除唯一索引
主键索引
特殊的唯一索引 不能有重复值 不能为空
mysql> alter table articles modify id int(11) unsigned not null; #删除主键之前 先消除主键自增Query OK, 3 rows affected (0.09 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> alter table articles drop primary key; #删除主键Query OK, 3 rows affected (0.08 sec)Records: 3 Duplicates: 0 Warnings: 0alter table articles add primary key(id);mysql> alter table articles modify id int(11) unsigned not null auto_increment; #自增
复合索引
给多个字段同时添加索引
mysql> alter table articles add index in_two_name(name,title);
全文索引
小说网站 内容网站 电商网站
mysql> alter table articles add fulltext(title);Query OK, 0 rows affected, 1 warning (0.30 sec)Records: 0 Duplicates: 0 Warnings: 1
什么样的字段适合添加索引？
- where order by 、group by 常用的字段
- 维度高的字段 也就是重复值少的字段
- 字段类型越小的字段 不给大数据字段添加索引
