MySQL常用基本操作

连接数据库并查看库

mysql -uusername -ppassword    //连接mysql 
mysql> show dateabases; //查看库mysql> use 库名    //选择库

查看库下面所有表

mysql> show tables; 

创建库：create database 数据库名 [charset 字符集]

mysql> create database test1 charset utf8;

删除库：drop database 数据库名

mysql> drop database test1; 

mysql中，表/列可以改名，database不能改名。

查看库下面所有表

mysql> show tables; 

创建简单表

mysql> create table stu(
    -> id int,
    -> name varchar(10)
    -> )engine myisam charset utf8; 

删除表 drop table 表名

mysql> drop table stu; 

修改表名

mysql> rename table stu to newstu; 

插入数据

mysql> insert into newstu values(1,'zhangsan'),
    -> (2,'lisi'); 

清空表数据：truncate 表名

mysql> truncate newstu; 

truncate和delete是有区别的，在于truncate相当于删表再重建一张同样结构的表，操作后得到一张全新表。而delete是从删除所有数据行的层面来操作的，如果决定清空的话，truncate的速度更快一些。

创建表

CREATE TABLE class(
    id int unsigned not null primary key auto_increment,
    sname varchar(10) not null default '',
    gender char(1) not null default '',
    company varchar(20) not null default '',
    salary decimal(6,2) not null default 0.00,
    fanbu smallint not null default 0
)engine myisam charset utf8; 

插入操作

INSERT INTO class 
(id,sname,gender,company,salary,fanbu)
 VALUES
(1,'张三','男','百度',5000.00,200);
INSERT INTO class
(sname,gender,salary)
VALUES
('刀锋冷','男',8644.32); 

如果插入所有列，则可以不声明待插入的列，即，如果不声明插入的列，则理解为依次插入所有的列。

INSERT INTO class VALUES
(3,'李四','女','新浪',5432.23,312) 

列与值必须按照顺序一一对应，即使id是自增的也不能省略，但可以使用null占位。

修改

--UPDATE 表名 set 列1 = 新值，列2 = 新值2， where expr; 
UPDATE class set gender = '女' company = '微软' where sname = '张三'; 

删除

--delete from 表名 where expr
DELETE FROM class where sname = '张三'; 

删除就是指删除整行，不存在删除一行的某列或几列。

查询

--SELECT 列1，列2，列3 FROM 表名 WHERE EXPR;
SELECT sname,salary,company FROM class WHERE id=3; 
SELECT * FROM class; 

*代表所有列，而表名后面不加where条件，则选所有行，因此取出所有行，所有列。不要进行这样的查询，浪费资源和系统io开销。

建表语法

所谓建表就是一个声明列的过程。

CREATE TABLE 表名(
列名1 列类型1 声明 列1 参数，
列名2 列类型2 声明 列2 参数，
......
列名n 列类型n 声明 列n 参数
)engine myisam/innodb charset utf8/gbk/latin1... 

例：

CREATE TALBE member(
    id int unsigned not null auto_increment primary key,
    username char(20) not null default '',
    gender char(1) not null defalut '',
    weight tinyint unsigned not null default 0,
    birth date not null default '0000-00-00',
    salary decimal(8,2) not null default 0.00,
    lastlogin int unsigned not null default 0
)engine myisam charset utf8; 

修改表语法

//增加列
--ALTER TABLE 表名 add 列名称 列类型 列参数 列声明[加的列在表的最后] 
CREATE TABLE m1(
    id int unsigned auto_increment primary key
)engine myisam charset utf8;

ALTER TABLE m1 add username char(20) not null default'';
ALTER TABLE m1 add birth date not null default '0000-00-00';
--添加sex性别列，加在username的后面
--ALTER TABLE 表名 add 列名称 列类型 列参数 after 某列[把新列指定加在某列后]
ALTER TABLE m1 add sex char not null default '' after username;
--如果新键一个列，且在表的最前面，用first
ALTER TABLE m1 add pid int unsigned not null default 0 first; 
--删除列
ALTER TABLE 表名 drop 列名
ALTER TABLE m1 drop pid; 
--修改列类型：
ALTER TABLE 表名 modify 列名 新的列类型 新列参数 列声明
ALTER TABLE m1 modify sex char(4) not null default'男女男女'
--修改列名及列类型
ALTER TABLE 表名 change 旧列名 新列名 新类型
ALTER TABLE m1  change id uid int unsigned; 

mysqldump导出数据库用

1、导出数据和表结构：

mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql

~$ /usr/local/mysql/bin/   mysqldump -uroot -p abc > abc.sql

敲回车后会提示输入密码

2、只导出表结构 
mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql

~$/usr/local/mysql/bin/   mysqldump -uroot -p -d abc > abc.sql

导入数据库

1、首先建空数据库

mysql>create database abc;

2、导入数据库

方法一： 
（1）选择数据库

mysql>use abc;

（2）设置数据库编码

mysql>set names utf8;

（3）导入数据（注意sql文件的路径）

mysql>source /home/abc/abc.sql;

方法二：

mysql -u用户名 -p密码 数据库名 < 数据库名.sql

~$ mysql -uroot -proot abc < abc.sql

建议使用第二种方法导入，且导入前该数据库必须存在。

where条件查询

等于、不等于

--主键为32的商品
select goods_id,goods_name,shop_price from goods where goods_id = 32;
--查询不属于第3个栏目的所有商品
select goods_id,cat_id,goods_name from goods where cat_id <> 3;
--或者
select goods_id,cat_id,goods_name from goods where cat_id != 3; 

>、<=

--价格高于3000的商品
select goods_id,goods_name,shop_price from goods where shop_price > 3000;
--价格小于等于100的商品
select goods_id,goods_name,shop_price from goods where shop_price <=100; 

between and、in

name in(a,b,c,f)是指name值只要在（a,b,c,f）这个集合（散点）中，就满足条件。而between and表示的是一个区间的范围，并且边界值是可以等于的。

--取出第4栏目和第11栏目的商品(不许用or)
select goods_id,cat_id,goods_name,shop_price from goods where cat_id in (4,11);
--取出100<=价格<=500的商品(不许用and)
select goods_id,goods_name,shop_price from goods where shop_price between 100 and 500; 

and、not in

--取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)
select goods_id,cat_id,goods_name,shop_price from goods where cat_id !=3 and cat_id !=11;
--或者
select goods_id,cat_id,goods_name,shop_price from goods where cat_id not in(3,11); 

and、or

--取出价格大于100且小于300,或者大于4000且小于5000的商品()
select goods_id,cat_id,goods_name,shop_price from goods where shop_price >=100 and shop_price <=300 or shop_price >=4000 and shop_price <=5000;
--或者
select goods_id,cat_id,goods_name,shop_price from goods where shop_price between 100 and 300 or shop_price between 4000 and 5000;
--取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品
select goods_id,cat_id,goods_name,shop_price,click_count
from goods 
where cat_id =3 and (shop_price <1000 or shop_price >3000) and click_count >5;
--取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)
select goods_id,cat_id,goods_name from goods where cat_id in (2,3,4,5); 

模糊查询like

'%'通配任意字符，'_'通配单一字符

--取出名字以"诺基亚"开头的商品
select goods_id,goods_name,shop_price from goods where goods_name like '诺基亚%'; 
--取出名字为"诺基亚Nxx"的手机
select goods_id,goods_name,shop_price from goods where goods_name like '诺基亚N__'; 

MySQL查询模型

把列看成变量，把where后面看成php中if(exp)里的exp表达式，哪一行能让exp为真，哪一行就能取出来。

group by分组与统计函数

--查出最贵的商品的价格
mysql> select goods_id,goods_name,max(shop_price) from goods;
--查出最大(最新)的商品编号
mysql> select goods_id,goods_name,max(goods_id) from goods;
--查出最便宜的商品的价格
mysql> select goods_id,goods_name,min(shop_price) from goods;
--查出最旧(最小)的商品编号
mysql> select goods_id,goods_name,min(goods_id) from goods;
--查询该店所有商品的库存总量
mysql> select sum(goods_number) from goods;
--查询所有商品的平均价
mysql> select avg(shop_price) from goods;
--查询该店一共有多少种商品
mysql> select count(*) from goods; 

select count( * ) from 表名，查询的就是绝对的行数，哪怕某一行所有字段为NULL,也计算在内，而select count（列名）from 表名，查询的是该列不为null的所有行的行数。用count(*)，count(1)，其实对于myisam引擎的表没有区别的，这种引擎内部有一计数器在维护着行数，innodb的表，用count(*)直接读行数，效率很低，因为innodb真的要去数一遍。

--查询每个栏目下面,最贵商品价格,最低商品价格,商品平均价格,商品库存量,商品种类
--提示:(5个聚合函数,sum,avg,max,min,count与group综合运用)
select max(shop_price),min(shop_price),avg(shop_price),sum(goods_number),cat_id 
from goods
group by cat_id; 

严格来讲以group by a，b，c为列，则select的列只能在a，b，c里选择，语义上才没有矛盾。

Having 筛选

where和having的区别：where是对表中的数据发挥作用，查询出数据来，where发挥作用时，并没有discount列，发挥完作用，形成结果里才能有discount列，对于结果中的列，如果再想筛选，须用having。

select goods_id,goods_name,(market_price-shop_price) as discount
from goods
where 1 having discount > 200; 
--查询每个商品所积压的货款(提示:库存*单价)
select goods_id,goods_name,goods_number,(shop_price*goods_number)as huokuan 
from goods; 
--查询该店积压的总货款,每个商品积压货款之和,sum那个列？列当成变量
select sum(goods_number*shop_price) from goods; 
--查询该店每个栏目下面积压的货款
select sum(goods_number*shop_price) from goods group by cat_id; 
--查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)
--where实现
select goods_id,goods_name,(market_price-shop_price) as discount 
from goods
where market_price-shop_price>200;
--having实现
select goods_id,goods_name,(market_price-shop_price) as discount 
from goods
having discount>200; 
--查询积压货款超过2W元的栏目,以及该栏目积压的货款
select cat_id, sum(goods_number*shop_price) as huokuan
from goods
group by cat_id having huokuan>20000; 

综合运用

--要求:查询出2门及2门以上不及格者的平均成绩
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三 | 数学    |    90 |
| 张三 | 语文    |    50 |
| 张三 | 地理    |    40 |
| 李四 | 语文    |    55 |
| 李四 | 政治    |    45 |
| 王五 | 政治    |    30 |
+------+---------+-------+
--错误解法(count(score<60)并不能计算出分数小于60的行,而是当前数据总行数)
select name,count(score<60) as gks,avg(score)
from result
group by name having  gks>=2; 
--思路是逆向思维，先查出所有人的平均，在筛选。
--分解1，查询所有的平均分
select name,avg(score) from result group by name;
--分解2，想办法计算每个人挂科的情况
select name,subject,score<60 as g from result;
--如上，挂科数目就是g的sum结果
select name,subject,sum(score<60) as gks,avg(score) as pingjun
from result group by name having gks>=2;
--正向思维解法(3层嵌套子查询) 
//查出符合的挂科数姓名
select name,count(1) as gks from class where score<60 group by name having gks>=2;
//根据第一步拿出所有符合的姓名
select name from (select name,count(1) as gks from class where score<60 group by name having gks>=2) as tmp;
//根据前二步取得该名字中学生的平均分
select name,avg(score) from class where name in
(select name from (select name,count(1) as gks from class where score<60 group by name having gks>=2) as tmp)
group by name;

order by 排序

最终结果集出来后，可以再排序，反过来说，排序是针对最终结果集的，即：order by要放在where/group/having的后面，顺序不能乱。

 

--语法：Order by结果集中的列名 DESC/ASC
--取出栏目为4的商品降序排列
select goods_id,cat_id,goods_name,shop_price 
from goods 
where cat_id = 4 order by shop_price DESC; 
--多字段排序
--Order by 列1 asc/desc,列2 desc/asc,...列n asc/desc
select goods_id,cat_id,goods_name,shop_price 
from goods 
order by cat_id ASC,shop_price DESC; 

 

limit

在语句的最后，起限制条目的作用

Limit [offset,] N
Offset:偏移量-->跳过几行
N:取出条目
Offset如果不写，则相当与 limit 0，N
--限制条数
select goods_id,cat_id,goods_name,shop_price 
from goods 
where cat_id = 3 order by shop_price ASC limit 10; 
--取出本店商品价格最高第三名到第五名
select goods_id,cat_id,goods_name,shop_price 
from goods 
order by shop_price DESC limit 2,3;