mysql常用基本操作_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 tablestu(-> 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 TABLEclass(

idint unsigned not null primary keyauto_increment,

snamevarchar(10) not null default '',

genderchar(1) not null default '',

companyvarchar(20) not null default '',

salarydecimal(6,2) not null default 0.00,

fanbusmallint not null default 0)engine myisam charset utf8;

插入操作

INSERT INTOclass

(id,sname,gender,company,salary,fanbu)VALUES(1,'张三','男','百度',5000.00,200);INSERT INTOclass

(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...

例：

CREATETALBE member(

idint unsigned not null auto_increment primary key,

usernamechar(20) not null default '',

genderchar(1) not null defalut '',

weighttinyint unsigned not null default 0,

birth datenot null default '0000-00-00',

salarydecimal(8,2) not null default 0.00,

lastloginint unsigned not null default 0)engine myisam charset utf8;

修改表语法

//增加列--ALTER TABLE 表名 add 列名称 列类型 列参数 列声明[加的列在表的最后]

CREATE TABLEm1(

idint 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 0first;--删除列

ALTER TABLE 表名 drop列名ALTER TABLE m1 droppid;--修改列类型：

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的系列商品

selectgoods_id,cat_id,goods_name,shop_price,click_countfromgoodswhere 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) fromgoods;--查出最大(最新)的商品编号

mysql> select goods_id,goods_name,max(goods_id) fromgoods;--查出最便宜的商品的价格

mysql> select goods_id,goods_name,min(shop_price) fromgoods;--查出最旧(最小)的商品编号

mysql> select goods_id,goods_name,min(goods_id) fromgoods;--查询该店所有商品的库存总量

mysql> select sum(goods_number) fromgoods;--查询所有商品的平均价

mysql> select avg(shop_price) fromgoods;--查询该店一共有多少种商品

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_idfromgoodsgroup 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) asdiscountfromgoodswhere 1 having discount > 200;--查询每个商品所积压的货款(提示:库存*单价)

select goods_id,goods_name,goods_number,(shop_price*goods_number)ashuokuanfromgoods;--查询该店积压的总货款,每个商品积压货款之和,sum那个列？列当成变量

select sum(goods_number*shop_price) fromgoods;--查询该店每个栏目下面积压的货款

select sum(goods_number*shop_price) from goods group bycat_id;--查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)--where实现

select goods_id,goods_name,(market_price-shop_price) asdiscountfromgoodswhere market_price-shop_price>200;--having实现

select goods_id,goods_name,(market_price-shop_price) asdiscountfromgoodshaving discount>200;--查询积压货款超过2W元的栏目,以及该栏目积压的货款

select cat_id, sum(goods_number*shop_price) ashuokuanfromgoodsgroup 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)fromresultgroup by name having gks>=2;--思路是逆向思维，先查出所有人的平均，在筛选。--分解1，查询所有的平均分

select name,avg(score) from result group byname;--分解2，想办法计算每个人挂科的情况

select name,subject,score<60 as g fromresult;--如上，挂科数目就是g的sum结果

select name,subject,sum(score<60) as gks,avg(score) aspingjunfrom 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) astmp;//根据前二步取得该名字中学生的平均分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) astmp)group by name;

order by 排序

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

--语法：Order by结果集中的列名 DESC/ASC--取出栏目为4的商品降序排列

selectgoods_id,cat_id,goods_name,shop_pricefromgoodswhere cat_id = 4 order by shop_price DESC;--多字段排序--Order by 列1 asc/desc,列2 desc/asc,...列n asc/desc

selectgoods_id,cat_id,goods_name,shop_pricefromgoodsorder by cat_id ASC,shop_price DESC;

limit

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

Limit [offset,]N

Offset:偏移量-->跳过几行

N:取出条目

Offset如果不写，则相当与 limit0，N--限制条数

selectgoods_id,cat_id,goods_name,shop_pricefromgoodswhere cat_id = 3 order by shop_price ASC limit 10;--取出本店商品价格最高第三名到第五名

selectgoods_id,cat_id,goods_name,shop_pricefromgoodsorder by shop_price DESC limit 2,3;