mysql待整理

1. MYSQL SQL_NO_CACHE的真正含义 http://www.dewen.org/q/5149/Mysql

  是 结果不缓存,但查询还是缓存了。

  如果要重新测试,就在查询前先执行一下"FLUSH QUERY CACHE",清空一下query cache

1.1、新建数据库

  creat database if not exists testdb;

1.2、新建表:

 create table if not exists APIaccout (

    `id` int(11) NOT NULL AUTO_INCREMENT,
    `userid` varchar(50) NOT NULL,
    `host` varchar(50),
    `request_method` varchar(20),
    `script_url` varchar(1024),
    `request_url` varchar(1024),
    `domain` varchar(50) ,
    PRIMARY KEY (`id`, `userid`)  #此处设置多个主键
  )ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.INSERT ... ON DUPLICATE KEY UPDATE
可以实现 原主键记录存在的话更新, 不存在的话插入。
(而 REPLACE INTO 则会删除原来重复的记录,再插入)

建表语句:
create table 表名(列名称,列类型 [列属性][默认值]),
engine 引擎名 charset 字符集

增:
往那张表增,增哪几列,各为什么值;
insert into 表名 (列1,列2, ... 列n)
values
(值1, 值2.......值N)

如果不声明拆入的列,则默认拆入所有列;

改:update
修改哪张表,修改哪几列,修改成什么值?
在哪几行上生效

update 表名
set
列1 = 值1
列2 = 值2
.。。
列N = 值N

where 表达式

delete:
删除哪几张表的数据,删那些行

delete from 表名
where 表达式

查:
select * from 表名

查询5种子句:where 后面的表示式子代入到每行,确认是否成立;
where shop_price - market_price > 200;

in(值1, 值2, 值3, ...值N)等于值1-N任意之一,都可以;
select good_id, cat_id from goods where cat_id in (4, 5);

between 在某一范围内;
between 值1 and 值2, 表示在值1和值2之间(允许等于边界);
select good_it, cat_id from goods where cat_id between 1 and 6;

or用法
select good_id, good_name, shop_price from goods where shop_price >=3000
and shop_price <=5000 or shop_price >=500 and shop_price<=1000;

not的用法:
select good_id, cat_id from goods where cat_id not in (4, 5);
select good_id, cat_id from goods where cat_id!=4 and cat_id != 5;

模糊查询:‘ % ’:通配任意字符 '_':单个字符
select good_id, cat_id from goods where good_name like '%诺基亚%';

group by (要和聚合函数(统计函数)一起使用)
作用:把行 按 字段 分组
语法:group by col1, col2, ... colN
运用场合:
常见于统计场合,如按栏目计数帖子数,
统计每个人的平均成绩等;

max(shop_price) //聚合函数

select good_id, good_name, max(shop_price) from goods; //语法是错误的
select min(shop_price) from goods; //ok

select cat_id, max(shop_price) from goods group by cat_id;

select min(shop_price) from goods;

select min(goods_id) from goods;

select sum(goods_number) from goods;

select avg(shop_price) from goods;

计算表中函数
select count(*) from goods;

select cat_id, min(shop_price) from goods group by cat_id; //ok

select cat_id, count(*) from goods group by cat_id;

select good_id, good_name, market_price-shop_price from goods;

select cat_id, sum(shop_price *good_number) from goods group by cat_id;

给列取别名: as

select cat_id, sum(shop_price*goods_number) as huokuan from goods group by cat_id;

select goods_id, good_cat from goods where

上一篇:Shell初学(二)变量及数组


下一篇:QT学习之路--菜单、工具条、状态栏