MySQL数据库(八)：表记录的基本操作(增删改查)
一、增
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 | .....
- 上一篇: MySQL数据库(七)：数据导出与导入
- 下一篇: MySQL数据库(九)：修改表结构