mysql 之 增删改查_mysql增删改查

一，数据库的增删改查

1，插入记录

语法一：insert 表 values(值1，值2，值n);

           insert user values(666,'张三‘,'女')；  向user 表中插入值id为666，姓名为张三，性别女

语法二：insert into 表(字段1，字段2，字段n) values(值1，值2，值n);

  insert into user(id,username,age) values(666,'学'，25)；

 向user 表中插入id 为666，username 为学，age 为25

说明：

基本语法1和基本语法2的区别是：

         基本语法1的插入语句，表中有多少个字段就必须要插入多少个值。一个不能多，一个也不能少。若有默认值，不想传，可以写上null。

         基本语法2中，除非有必填字段必须要写入值外。如果有默认值的不想写可以忽略不写。mysql会自动补主默认值。

基本语法变形：一次插入多条记录  

insert into user(username,password,sex)

 values('黄晓明', 'abcdef', 1),

 ( 'angelababy', 'bcdeef', 0),

 ( '陈赫', '123456', 1),

 ('王宝强', '987654', 1);

2，基础查询

一，创建表的语句

create table if not exists money(

uid int(11) not null,

username varchar(30) not null,

password  char(32) not null,

age tinyint unsigned not null,

sex tinyint not null,

)engint =  InnoDB DEFAULT CHARSET=utf8;

二，查询表

select * from 表；

select * from money;   查询money表中所有字段中的所有结果

注：”*” 是一种正则表达式的写法，表示匹配所有，上面的查询语句和下面的是等价

3，指定字段查询

select 字段 from 表；

select  id,username,balance from money;  查询money 表中id,username,balance字段中的所有结果

4，查询单个字段不重复记录 distinct

select  distinct 字段 from 表；

select  distinct age  deptno from money;  查询money 表中age 唯一的所有结果

deptno  部门编号

5，条件查寻  where 

select * from 表 where 条件；

select * from money  where  age = 25;  查询money表 中年龄为25的所有结果；

6，where 后接的条件

上面的例子中，where 后面的田间是一个字段的 ‘=’。

除此之外，还可以使用>、<、>=、<=、!=等比较运算符；

符号         说明

>       大于

<       小于

>=     大于等于

<=     小于等于

!=     不等于

=       等于

7，逻辑运算符

or      或者  

and  并且

select * from money where id<10 and province="湖北"；  查询money 里面id<10,并且province="湖北

8 ，结果集排序

select 字段 from 表 order by 字段  排序关键词；

select  id,username,balance  from money order by balance desc;

查询money表中的id,username,balance字段，按照余额进行降序排序

asc   升序排列，从小到大（默认）

desc 降序排列，从大到小

如果不写关键字默认升序排列

9,结果集限制

     对于查询或者排序后的结果集，如果希望只显示一部分而不是全部，使用 limit 关键字结果机数量限制

select 字段  from 表 limit  数量；

select  id,username,balance  from money  limit 5;  显示money表里 id,username,balance字段的前五个用户

select * ffrom  money limit 5； 显示money表中的前五个用户

10，限制结果集并排序

select  字段 from  表 order by 字段 关键词 limit 数量；

select  id,username,balance from money order by balance  desc limit 5;

按照钱来排序，显示前五个最有钱的用户

11，统计类函数的使用

如果我们想知道总用户数怎么办？

查询谁是数据表里的首富怎么办？

如果我们想知道用户的平均金额怎么办？

如果我们想知道所有用户的总金额怎么办？

统计类函数最常用的我们有四个：

select  函数(字段) form 表

select  count(id) from money;     查询money表中的id总数 

select avg(balance) from money;      查询money表中平均金额

select count(balance) from money;   查询money表中总金额

select max(balance) from money;      查询money表中最大金额

select min(balance) from money;       查询money表中最小金额

select  count(id)  as  zongshu   from money;  把id 总数改名为zongshu

12，分组  group by 

select  * from 表 group by 字段；

select  * from money  group by  province;    把money表中按地区进行分组，

select deptno,count(1) from  emp grop by deptno;  统计emp 表中的deptno的字段1的总数

统计省份数量后在进行分组显示

select count(province),province from money grop by province;   统计money表中的省份数量后再显示省份数量（显示每个省份的 数量）

在分组基础上进行统计

with rollup   用的比较少，了解就行

 select * from 表 group by 字段 with rollup；

 select count(province),province  from money group by province with rollup;

 对分组的数再次进行总的统计（每个省份的数量最后还有个总合）

结果再过滤  having

having子句与where有相似之处但也有区别,都是设定条件的语句。

having 是筛选组 而where是筛选记录。

select * from 表 group by 字段 having 条件

select count(province) as result ,province  from money group by province having result >2;

对地区分组并统计总数，将分组结果中大于2的分组地区显示出来

整体使用sql

将语句进行整合，整体的sql 语句配合使用的语法结构

 from 表名

 [WHERE where条件]

 [GROUP BY 字段]

 [HAVING where_contition]

 [order 条件]

 [limit 条件]

注：上面的语句中可以[] 代表可选。最终的语法总结如下：

关键词     说明

select        选择的列

from          表

where        查询的条件

group by    分组属性  having 分组过滤的条件

order by     排序属性

limit            起始记录位置，取记录的条数

查询money表字段：id,username,balance,province 要求id>1 余额大于50，使用地区进行分组。我们使用用户id进行降序，要求只准显示3条;

select id,username,balance,province from money where id > 1 and balance > 50 group by province order by id desc limit 3;

二，mysql 多表联合查询

user表创建语句

 show databases;       显示所有库

create database user; 创建user 库

use user;                      进入user库

1，CREATE TABLE IF NOT EXISTS user (

   uid int(11) NOT NULL,

   username varchar(30) NOT NULL,

   password char(32) NOT NULL

 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  创建user表,字段分为uid，username ,password,.

user表数据:   创建表中的内容

INSERT INTO user(uid,username,password) values('1','jintian','123456');

INSERT INTO user(uid,username,password) values('2','zhangsha','1234f56');

INSERT INTO user(uid,username,password) values('3','wangwu','1234fs56');

INSERT INTO user(uid,username,password) values('4','lisi','123s456');

INSERT INTO user(uid,username,password) values('5','xiaotian','123d456');

INSERT INTO user(uid,username,password) values('6','wangfei','123f456');

INSERT INTO user(uid,username,password) values('7','oldboy','123w456');

INSERT INTO user(uid,username,password) values('8','oldboyww','123w45ee6');

2，创建order_goods表，字段有oid,uid,name,buytiame,.

CREATE TABLE IF NOT EXISTS order_goods (

   oid int(11) NOT NULL,

   uid int(11) NOT NULL,

   name varchar(50) NOT NULL,

   buytime int(11) NOT NULL

 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

order_goods数据如下：   表中的数据

INSERT INTO order_goods(oid,uid,name,buytime) values('1','7','opper','12345622');

INSERT INTO order_goods(oid,uid,name,buytime) values('2','2','iphone','1234356');

INSERT INTO order_goods(oid,uid,name,buytime) values('3','4','xieli','12342s56');

INSERT INTO order_goods(oid,uid,name,buytime) values('4','3','lisi','12322456');

INSERT INTO order_goods(oid,uid,name,buytime) values('5','5','juzi','12344456');

INSERT INTO order_goods(oid,uid,name,buytime) values('6','6','tianmiju','12322456');

INSERT INTO order_goods(oid,uid,name,buytime) values('7','1','xiangjiao','12311456');

注意：在上表order_goods表中uid是指user表中的uid字段。上表中oid为1的数据行，uid为7的用户。为user表中uid为7的用户：oldboy。该用户购买了商品为xiangjiao。购买时间buytime为一个unix时间戳。

mysql> select u.uid ,u.username as username,o.oid,o.uid,o.name as shopname from user u,order_goods o  where u.uid = o.uid;

外连接

select  表 1.字段 [as 别名]， 表n.字段  from 表 1 left join 表n on  条件;

select  * from user  left jion order_goods  on user.uid = order_goods.uid;

以左边为主，查询哪些用户未购买过商品，并将用户信息显示出来

连接又分为左连接和右链接，具体定义如下。

左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录

 select *  from user left join order_goods  on  user.uid = order_goods.uid;

右连接：包含所有的右边表中的记录甚至是右边表中没有和它匹配的记录

 select 表1.字段 [as 别名],表n.字段  from 表1 right join 表n on  条件;

select *  from user right join order_goods  on  user.uid = order_goods.uid;

 查询商品表中哪些用户购买过商品，并将用户信息显示出来

子查询

有时候，当我们查询的时候，需要的条件是另外一个select语句的结果，这时就需要使用子查询。用于子查询的关键字包括in、not in、=、!=、exists、not exists等。

select  字段 from 表 where  字段  in(条件)；

select * from user where uid in(1,3,4); 按照uid查询指定用户

select * from user where uid in(select uid from order_goods);  将购买过商品的用户信息显示出来

记录联合

使用 union 和 union all  关键字，将两个表的数据按照一定的查询条件查询出来以后，将结果合并到一起，union 是将 union all 后的结果进行一次distinct，去除重复记录后的结果。

select语句1 union[all] select语句2

select * from user where uid in(1,3,4); 按照uid查询指定用户

select uid from user union select uid from order_goods;

将商品表中的用户信息和用户表中的用户信息的结果组合在一起

三，增删改查之 更新记录

更新记录

更新数据我们已经说过。需要修改内容，修改银行卡余额，修改装备信息的时候都需要使用到update，修改语句

money表数据结构：     创建money表结构

CREATE TABLE IF NOT EXISTS money (

   uid int(11) NOT NULL,

   username varchar(30) NOT NULL,

   balance DECIMAL(9,2) NOT NULL

 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

money表数据：   增加money表数据

INSERT INTO money(uid,username,balance) values('1','zhangsai','15000.22');

INSERT INTO money(uid,username,balance) values('2','zhangsa','16000.22');

INSERT INTO money(uid,username,balance) values('3','lisi','17000.22');

INSERT INTO money(uid,username,balance) values('4','wangwu','18000.22');

更新语句的基本语法

update 表名 set 字段 1=值1，字段2=值2，字段n=值n  where 条件

update money set  balance=balance-500 where  uid=1;   修改money表，将balance余额减500。要求uid为1

select * from money where username='lisi';  查询money表中 username为lisi 的信息；

同时对两个表进行更新

update 表1,表2 set 字段1=值1,字段2=值2,字段n=值n where 条件

update money m,user u m.balance=m.balance*u.age where m.userid=u.id;修改money，将money表的别名设置为m；user表的别名设置为u；将m表的余额改为m表的balance*用户表的age。执行条件是：m.userid = u.id

使用 delete 删除记录

delete from 表 [where 条件]；

delete from  user where  id>10;   删掉用户表中id大于10的所有用户

delete from  user where  uid=’1‘；删掉用户表中id等于1的所有用户

清空表记录

delete和truncate是一样的，但它们有一点不同，那就是deiete可以返回被删除的记录数，而truncate table返回的是0

如果一个表中有自增字段，使用truncate table 这个自增字段将起始值恢复成1,

truncate  table 表名；

truncate table user; 清空user表的数据，并且让自增的id从 1 开始自增

【切记】

删除时一定要记住加上where条件，不然会清空掉整个表的记录。

删除重要数据前一定要备份、备份、备份。

四，MySQL 权限操作

添加权限

grant 权限 on  库.表. to  '用户’@‘主机' identified by '密码'；

grant  select,insert on test.* to'liwenkai'@'localhost' identified by '4331';

给予liwenkai用户，在本机连接test库所有表的权限。操作的这些表具有查询和写入权限

删除权限

revoke 权限 on 库.表 from '用户'@'主机';

revoke select, insert on test.* to 'liwenkai'@'localhost' identified by '4311';

删除liwenkai用户，在本机连接test库所有表的权限。删除查询和写入权限

参数说明

grant all   在grant后接all说明给予所有权限

revoke all 在revoke后接all说明删除所有权限

权限 on .         . 所明给予所有库所有表的操作权限

'用户'@'主机' 主机里面若为%。任意来源的主机均可以使用这个用户来访问

创建数据库用户liwenkai ，具有对test数据库中所有标的 select / insert 权限