MySQL数据库(八):表记录的基本操作(增删改查)

@lianst  July 28, 2016

一、增
insert:增加(条件一条新纪录,默认新添加的记录都添加在已有记录的末尾)

1.格式:
1.1添加新纪录时,只给记录中的某几个字段赋值

insert into 表名(字段名1,字段名2...)values(值1,值2....);

*值得类型是字符的话需要用双引号引起来

1.2 添加新纪录时,给所有记录中的所有字段赋值

insert into 表名 values(值1,值2....);

*值得类型是字符的话需要用双引号引起来
*值与字段的类型一定匹配

2.例子
1.1 给表中插入一条记录

insert into usertab values(46,"plj",100,"x",2000,2000,"my teahere","/pljdir","/bin/bash");

1.2 给表中的指定字段赋值

insert into usertab(username,password,uid,gid,shell)values("plj","x",3000,3000,"/bin/bash");

1.3 如何给字段赋空值

update usertab set comment=NULL;

二、删
delete:删除(删除的是一整条记录)

1.格式:
1.1 删除表中的所有记录

delete from 数据库.表名;

1.2 只删除指定的记录

delete from 数据库.表名  where 条件表达式;

*条件表达式:数值比较、字符比较、范围内、空、非空、逻辑比较、模糊、正则表达式

2.例子:

1.1 删除uid字段为null的记录

mysql> select id,uid from usertab where uid is null;
+----+------+
| id | uid  |
+----+------+
| 27 | NULL |
| 28 | NULL |
| 29 | NULL |
| 30 | NULL |
| 31 | NULL |
| 32 | NULL |
| 33 | NULL |
| 34 | NULL |
| 35 | NULL |
| 36 | NULL |
| 37 | NULL |
| 38 | NULL |
| 39 | NULL |
| 40 | NULL |
| 41 | NULL |
| 42 | NULL |
+----+------+
16 rows in set (0.00 sec)
mysql> delete from usertab where uid is null;
Query OK, 16 rows affected (0.00 sec)
mysql> select id,uid from usertab where uid is null;
Empty set (0.00 sec)

三、改
update:修改字段的值

1.批量修改
1.1 格式:

 
update 数据库名.表名  set  字段名=值;
update 数据库名.表名  set  字段名=值,字段名=值;

1.2 例子

批量修改age字段的值为20
mysql> update usertab set age=20;
Query OK, 42 rows affected (0.00 sec)
Rows matched: 42  Changed: 42  Warnings: 0
mysql> select age from usertab;
+------+
| age  |
+------+
|   20 |
|   20 |
|   20 |
|   20 |
|   20 |
.....

2.只修改符合条件的记录中指定字段的值
1.1格式:

update 数据库名.表名  set  字段名=值,字段名=值 where 条件表达式;

*条件表达式:数值比较 字符比较 范围内 空 非空 逻辑比较 模糊 正则表达式

1.2例子:
修改username是root的age字段的值为30

mysql> update usertab set age=30 where username="root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select username,age from usertab;
+----------+------+
| username | age  |
+----------+------+
| root     |   30 |
| bin      |   20 |
| daemon   |   20 |
| adm      |   20 |
....

四、查
select:查询记录内容(值)
1.格式

 select     字段名列表    from    表名;
 select     字段名列表    from    数据库名.表名;
 select     字段名列表    from    数据库名.表名     where    条件表达式;

2.字段名列表的表示方式

#所有字段
*       
#查询某几个字段          
字段名1,字段名2,字段名N             

select 字段名列表 from 数据库名.表名 where 条件表达式;

例子:查看uid小于10的

 select  username,uid  from  数据库名.表名  where  uid < 10;  

3.条件表达式
1.1 数值比较
比较符号:> >= < <= = !=
格式:
字段名(数值类型且是整型) 比较符号 数字

例子:

select   * from usertab    where    uid    =    10;

查询id=10的记录

mysql> select * from usertab where id = 10;
+----+----------+----------+------+------+---------+-----------------+---------------+
| id | username | password | uid  | gid  | comment | homedir         | shell         |
+----+----------+----------+------+------+---------+-----------------+---------------+
| 10 | uucp     | x        |   10 |   14 | uucp    | /var/spool/uucp | /sbin/nologin |
+----+----------+----------+------+------+---------+-----------------+---------------+
1 row in set (0.00 sec)

1.2字符比较
比较符号:= !=

格式:
字段名 "字符串"
比如:name="root"

例子:
查询表中username=root的记录

mysql> select * from usertab where username="root";
+----+----------+----------+------+------+---------+---------+-----------+
| id | username | password | uid  | gid  | comment | homedir | shell     |
+----+----------+----------+------+------+---------+---------+-----------+
|  1 | root     | x        |    0 |    0 | root    | /root   | /bin/bash |
+----+----------+----------+------+------+---------+---------+-----------+
1 row in set (0.00 sec)

查询usertab表中usernam=root用户的username,uid,gid的记录

mysql> select username,uid,gid from usertab where username="root";
+----------+------+------+
| username | uid  | gid  |
+----------+------+------+
| root     |    0 |    0 |
+----------+------+------+
1 row in set (0.00 sec)

1.3范围内查找

in   在......里
not     in    不在.....里
between.......  and   在.....与....之间 (可以做字符比较,但很少用做字符)

例子:

select username ,uid from usertab where uid between 10 and 20;   :大于等于10小于等于20的
select username ,uid from usertab where uid a  and c;  

1.4 空和非空
匹配空:is null
匹配非空:is not null

例子:
匹配为空的

mysql> select * from usertab where username is NULL;
Empty set (0.00 sec)

匹配非空的

*赋值的时候 "" 和 " " 是等效的

#匹配空
select  id,username from  usertab where username is null;
#匹配字符串null
select  id,username from  usertab where username="null";
#匹配空
select  id,username from  usertab where username="";
#匹配非空
select  id,username from  usertab where username is not null;

***********************

1.5逻辑比较
(查询时使用2个或2个以上查询条件)

逻辑与: and-多个条件必须同时成立

select username,uid from usertab where username="root" and uid=35 and shell="/bin/shell"

逻辑或:or-多个条件只要有一个条件成立就可以

select username,uid from usertab where username="root"     or    uid=35     or    shell="/bin/shell"
select     username,uid    from    usertab    where    username    in("root","daemon")    or    uid=10;

逻辑非:!-取反

select     username     from    usertab    username!="root"

distinct 不显示重复的值

select shell from usertab;
mysql> select distinct shell from usertab;
+----------------+
| shell |
+----------------+
| /bin/bash |
| /sbin/nologin |
| /bin/sync |
| /sbin/shutdown |
| /sbin/halt |
| NULL |
| NULL |
+----------------+
7 rows in set (0.00 sec)

1.6模糊查询
查询用户名姓张的学生信息
格式:
where 字段名 like '表达式'

表达式符号 :
% 匹配0个到多个字符
_ 匹配任意一个字符

#匹配任意3个字符
select username from usertab where username like '_ _ _'
#匹配以r开头的任意3个字符
select username from usertab where username like 'r_ _'
#匹配以a开头的
select username from usertab where username like 'a%'

select username from usertab where username like '_a_'
select username from usertab where username like '张%'

**********************************
使用这则表达式做查询条件

INSERT INTO usertab(username,password,uid,gid,comment,homedir,shell)values("jim3",NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO usertab(username,password,uid,gid,comment,homedir,shell)values("jim8",NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO usertab(username,password,uid,gid,comment,homedir,shell)values("3jim",NULL,NULL,NULL,NULL,NULL,NULL);

mysql> select username from usertab
-> where
-> username like 'j%';
+----------+
| username |
+----------+
| jim8 |
| jim9 |
+----------+
2 rows in set (0.00 sec)

1.7使用正则表达式的格式:
where 字段名 regexp '正则表达式'

查询名字中包含数字的
mysql> select username from usertab where username regexp '[0-9]';
+----------+
| username |
+----------+
| 3jim |
| jim8 |
| jim9 |
+----------+
3 rows in set (0.01 sec)

查询uid是两位数的
mysql> select id,username,uid from usertab where uid regexp '^..$';
+----+----------+------+
| id | username | uid |
+----+----------+------+
| 10 | uucp | 10 |
| 11 | operator | 11 |
| 12 | games | 12 |
| 13 | gopher | 13 |
| 14 | ftp | 14 |
| 15 | nobody | 99 |
| 16 | vcsa | 69 |
| 18 | postfix | 89 |
| 19 | sshd | 74 |
| 20 | ntp | 38 |
| 21 | dbus | 81 |
| 24 | mailnull | 47 |
| 25 | smmsp | 51 |
+----+----------+------+
13 rows in set (0.00 sec)

4.查询时做四则运算
运算符号: + - * / %

1.1 加法

mysql> select  username,uid+gid   from  usertab;
+----------+---------+
| username | uid+gid |
+----------+---------+
| root     |       0 |
| bin      |       2 |
| daemon   |       4 |
| adm      |       7 |
| lp       |      11 |
| sync     |       5 |
| shutdown |       6 |
| halt     |       7 |
| mail     |      20 |
| uucp     |      24 |
| operator |      11 |
| games    |     112 |
| gopher   |      43 |
| ftp      |      64 |
| nobody   |     198 |
| vcsa     |     138 |
| saslauth |     575 |
| postfix  |     178 |
| sshd     |     148 |
| ntp      |      76 |
| dbus     |     162 |
| mysql    |    1000 |
| www      |    1002 |
| mailnull |      94 |
| smmsp    |     102 |
| NULL     |       0 |
|          |    NULL |
|          |    NULL |
|          |    NULL |
|          |    NULL |
|          |    NULL |
|          |    NULL |
|          |    NULL |
|          |    NULL |
|          |    NULL |
|          |    NULL |
|          |    NULL |
|          |    NULL |
|          |    NULL |
| jim8     |    NULL |
| jim9     |    NULL |
| 3jim     |    NULL |
+----------+---------+
42 rows in set (0.00 sec)

1.2减法运算

mysql> select username,2016-age as s_year from usertab where username="root";
+----------+--------+
| username | s_year |
+----------+--------+
| root     |   1995 |
+----------+--------+
1 row in set (0.00 sec)

3.乘法运算

mysql> select uid,gid,(uid*gid) as chengji from usertab where username="mail";
+------+------+---------+
| uid  | gid  | chengji |
+------+------+---------+
|    8 |   12 |      96 |
+------+------+---------+
1 row in set (0.00 sec)

*as chengji:表示给运算结果列取一个名字
4.除法运算

mysql> select  username,uid,gid,(uid+gid)/2  as pjcj from  usertab where username="www";
+----------+------+------+----------+
| username | uid  | gid  | pjcj     |
+----------+------+------+----------+
| www      |  501 |  501 | 501.0000 |
+----------+------+------+----------+
1 row in set (0.00 sec)

5.常用统计函数
avg():集合的平均值
sum():对集合中的各参数求和
min():集合中的最小值
max():集合中的最大值
count():记录的个数(空值不算)

1.1 统计所有字段的个数

mysql> select count(*) from usertab;
+----------+
| count(*) |
+----------+
|       42 |
+----------+
1 row in set (0.00 sec)

1.2 统计指定字段的个数

mysql> select count(username),count(uid) from usertab;
+-----------------+------------+
| count(username) | count(uid) |
+-----------------+------------+
|              42 |         26 |
+-----------------+------------+
1 row in set (0.00 sec)

6.排序
1.1 格式:(默认为升序)

order by 字段名  排序方式{ASC(升序),DESC(降序)}

1.2 例子:

*默认(升序)排序

mysql> select uid,username from usertab order by uid;
mysql> select uid,username from usertab order by uid;
+------+----------+
| uid  | username |
+------+----------+
| NULL |          |
| NULL |          |
| NULL |          |
| NULL |          |
| NULL |          |
| NULL |          |
| NULL |          |
| NULL | jim8     |
| NULL | jim9     |
| NULL | 3jim     |
| NULL |          |
| NULL |          |
| NULL |          |
| NULL |          |
| NULL |          |
| NULL |          |
|    0 | root     |
|    0 | NULL     |
|    1 | bin      |
|    2 | daemon   |
|    3 | adm      |
|    4 | lp       |
|    5 | sync     |
......

*降序排列

mysql> select uid,username from usertab order by uid desc;
+------+----------+
| uid  | username |
+------+----------+
|  501 | www      |
|  500 | mysql    |
|  499 | saslauth |
|   99 | nobody   |
|   89 | postfix  |
|   81 | dbus     |
|   74 | sshd     |
|   69 | vcsa     |
|   51 | smmsp    |
|   47 | mailnull |
|   38 | ntp      |
|   14 | ftp      |
|   13 | gopher   |
|   12 | games    |
|   11 | operator |
|   10 | uucp     |
|    8 | mail     |
|    7 | halt     |
|    6 | shutdown |
|    5 | sync     |
|    4 | lp       |
......

7.分组
1.格式:

select shell from usertab group by 字段名;

2.例子:

mysql> select  shell from usertab group by shell;
+----------------+
| shell          |
+----------------+
| NULL           |
| /bin/bash      |
| /bin/sync      |
| /sbin/halt     |
| /sbin/nologin  |
| /sbin/shutdown |
| NULL           |
+----------------+
7 rows in set (0.00 sec)

3.处理分组后的结果
1.1格式:

select shell from usertab group by 字段名 having 条件表达式

例子:
查询usertab表中的shell字段并分组,然后查找出uid小于10的

mysql> select  shell from usertab group by shell having "uid"<10;
+----------------+
| shell          |
+----------------+
| NULL           |
| /bin/bash      |
| /bin/sync      |
| /sbin/halt     |
| /sbin/nologin  |
| /sbin/shutdown |
| NULL           |
+----------------+
7 rows in set, 1 warning (0.00 sec)

8.限制显示记录的条目数

limit限制显示记录的条目数

1.1格式:

limit N;  
# N表示从查询结果的第几条记录开始显示,默认从查询结果的第一条记录开始显示,第一条记录的编号是0
# M 表示显示条目的记录数
limit N,M;   

例子:

显示查询结果的前10条记录

mysql> select id,username,uid from usertab limit 10;
mysql> select id,username,uid from usertab limit 0,10;
+----+----------+------+
| id | username | uid  |
+----+----------+------+
|  1 | root     |    0 |
|  2 | bin      |    1 |
|  3 | daemon   |    2 |
|  4 | adm      |    3 |
|  5 | lp       |    4 |
|  6 | sync     |    5 |
|  7 | shutdown |    6 |
|  8 | halt     |    7 |
|  9 | mail     |    8 |
| 10 | uucp     |   10 |
+----+----------+------+
10 rows in set (0.00 sec)

从第三行显示三行

mysql> select id,username,uid from usertab limit 3,3;
+----+----------+------+
| id | username | uid  |
+----+----------+------+
|  4 | adm      |    3 |
|  5 | lp       |    4 |
|  6 | sync     |    5 |
+----+----------+------+
3 rows in set (0.00 sec)

9.嵌套查询
里层查询结果做为外层查询条件

格式/例子:
从usertab表查找uid小于uid列平均值的记录

mysql> select username,uid from usertab where uid < (select avg(uid) from usertab);
+----------+------+
| username | uid  |
+----------+------+
| root     |    0 |
| bin      |    1 |
| daemon   |    2 |
| adm      |    3 |
| lp       |    4 |
| sync     |    5 |
| shutdown |    6 |
| halt     |    7 |
| mail     |    8 |
| uucp     |   10 |
| operator |   11 |
| games    |   12 |
.....

添加新评论