mysql操作
来源:互联网 发布:apache 配置加载php 编辑:程序博客网 时间:2024/04/29 00:19
mysql操作
命令行操作
show engines;//查询数据库的引擎
show variables like 'table_type';//查询表类型
create table() type InnoDB;//创建表,设置表的存储引擎
create table() engine InnoDB;//创建表,设置表的存储引擎
truncate tbname;//清空表
insert into 表名() values();//插入数据
SELECT [distinct] * FROM 表名 WHERE [GROUP BY , HAVING , ORDER BY , LIMIT count];
SELECT a [AS] A,b [AS] B FROM 表名;
空值不能用=查询
SELECT * FROM 表名 WHERE id=null;//错
SELECT * FROM 表名 WHERE id is null;//对
SELECT * FROM 表名 WHERE id is not null;//对
SELECT * FROM 表名 WHERE id <=> null;//对 <=>和=作用一样,但可用于空值比较
SELECT * FROM products WHERE id BETWEEN 10 AND 20;和SELECT * FROM products WHERE id <= 20 && id >= 10;等价
SELECT * FROM products WHERE id NOT BETWEEN 10 AND 20;和SELECT * FROM products WHERE id > 20 && id < 10; 等价
SELECT * FROM products WHERE id IN(5,10,15,20,25);//查询出所有id等于5,10,15,20,25的记录
UPDATE products SET num=10 WHERE id IN(5,10);
LIKE 包含
NOT LIKE 不包含
_(任意一个字符)和%(0个或多个任意字符) 两个通配符号
SELECT * FROM products WHERE name LIKE "%java%";
SELECT * FROM products WHERE name LIKE "_java_";
SELECT * FROM products WHERE name NOT LIKE "%java%";
SELECT * FROM products WHERE name NOT LIKE "_java_";
正则 REGEXP RLIKE
SELECT * FROM products WHERE name REGEXP '^j';//以j开头的记录
SELECT * FROM products WHERE name REGEXP 's$';//以s结尾的记录
多表查询(连接查询)
SELECT c.name,p.name FROM cats as c,products as p WHERE c.id = p.pid; //两个表多表查询
SELECT a.id aid ,a.name aname,b.id bid ,b.name bname FROM cats a,cats b WHERE b.pid = a.id;//单表多表查询
嵌套查询(子查询)
SELECT * FROM products WHERE cid IN(SELECT id FROM cats WHERE name like 'j%');
排序 ORDER BY 字段 [ASC正序|DESC倒序]
SELECT * FROM products WHERE id < 10 ORDER BY id [ASC|DESC];
LIMIT count 限制条数
SELECT * FROM products WHERE id < 10 ORDER BY id LIMIT 5;
SELECT * FROM products WHERE id>14 ORDER BY id ASC LIMIT 1;//获取14的下一条记录
SELECT * FROM products WHERE id < 14 ORDER BY id DESC LIMIT 1;//获取14的上一条数据
GROUP BY 分组
函数:count()
sum()
avg()
max()
min()
SELECT cid,count(*),sum(price),avg(price),max(price),min(price) FROM products GROUP BY cid HAVING avg(price) > 50;
命令行操作
show engines;//查询数据库的引擎
show variables like 'table_type';//查询表类型
create table() type InnoDB;//创建表,设置表的存储引擎
create table() engine InnoDB;//创建表,设置表的存储引擎
truncate tbname;//清空表
insert into 表名() values();//插入数据
SELECT [distinct] * FROM 表名 WHERE [GROUP BY , HAVING , ORDER BY , LIMIT count];
SELECT a [AS] A,b [AS] B FROM 表名;
空值不能用=查询
SELECT * FROM 表名 WHERE id=null;//错
SELECT * FROM 表名 WHERE id is null;//对
SELECT * FROM 表名 WHERE id is not null;//对
SELECT * FROM 表名 WHERE id <=> null;//对 <=>和=作用一样,但可用于空值比较
SELECT * FROM products WHERE id BETWEEN 10 AND 20;和SELECT * FROM products WHERE id <= 20 && id >= 10;等价
SELECT * FROM products WHERE id NOT BETWEEN 10 AND 20;和SELECT * FROM products WHERE id > 20 && id < 10; 等价
SELECT * FROM products WHERE id IN(5,10,15,20,25);//查询出所有id等于5,10,15,20,25的记录
UPDATE products SET num=10 WHERE id IN(5,10);
LIKE 包含
NOT LIKE 不包含
_(任意一个字符)和%(0个或多个任意字符) 两个通配符号
SELECT * FROM products WHERE name LIKE "%java%";
SELECT * FROM products WHERE name LIKE "_java_";
SELECT * FROM products WHERE name NOT LIKE "%java%";
SELECT * FROM products WHERE name NOT LIKE "_java_";
正则 REGEXP RLIKE
SELECT * FROM products WHERE name REGEXP '^j';//以j开头的记录
SELECT * FROM products WHERE name REGEXP 's$';//以s结尾的记录
多表查询(连接查询)
SELECT c.name,p.name FROM cats as c,products as p WHERE c.id = p.pid; //两个表多表查询
SELECT a.id aid ,a.name aname,b.id bid ,b.name bname FROM cats a,cats b WHERE b.pid = a.id;//单表多表查询
嵌套查询(子查询)
SELECT * FROM products WHERE cid IN(SELECT id FROM cats WHERE name like 'j%');
排序 ORDER BY 字段 [ASC正序|DESC倒序]
SELECT * FROM products WHERE id < 10 ORDER BY id [ASC|DESC];
LIMIT count 限制条数
SELECT * FROM products WHERE id < 10 ORDER BY id LIMIT 5;
SELECT * FROM products WHERE id>14 ORDER BY id ASC LIMIT 1;//获取14的下一条记录
SELECT * FROM products WHERE id < 14 ORDER BY id DESC LIMIT 1;//获取14的上一条数据
GROUP BY 分组
函数:count()
sum()
avg()
max()
min()
SELECT cid,count(*),sum(price),avg(price),max(price),min(price) FROM products GROUP BY cid HAVING avg(price) > 50;
- 操作mysql
- mysql 操作
- MYSQL操作
- mysql 操作
- MySQL操作
- MYSQL操作
- mysql 操作
- mysql操作
- MYSQL操作
- mysql操作
- mysql操作
- MYSQL操作
- mysql 操作
- 操作mysql
- mysql操作
- mysql操作!
- Mysql----操作
- mysql操作
- mysql基本应用
- Tomcat的安装和配置(Windows系统)
- 你的产品为什么只被打开过一次?
- linux基础之mysql安装
- sqlserver 获取当月第一天,和最后一天
- mysql操作
- ZJU PAT 1011. World Cup Betting
- sqlserver 2005 异地备份
- UTF8和GBK编码转换
- java.util.Timer
- 《程序员的第一年》---------- 今天学习了一下aspnetpager分页控件的使用
- android 之用小米手机测试所报异常SoketException(wifi联网)
- Eclipse打JAR包,插件FatJar安装与使用
- Linux下如何修改终端提示符