2018-04-30-4章操作数据表中的记录

于 2019-03-17 11:47:28 发布
阅读量129 收藏
点赞数
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/github_38970218/article/details/88616511
版权

4章操作数据表中的记录

4-1 MySQL插入记录INSERT

INSERT [INTO] tbl_name [(col_name,…)] {VALUES|VALUE}

({expr|DEFAULT},…),(…),…



mysql> create table users(
    -> id smallint unsigned primary key auto_increment,
    -> username varchar(32) not null,
    -> password varchar(32) not null,
    -> age tinyint unsigned not null default 10,
    -> sex boolean
    -> );
Query OK, 0 rows affected (0.01 sec)



mysql> show columns from users;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(32)          | NO   |     | NULL    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
| sex      | tinyint(1)           | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


向表users中插入一条记录

mysql> insert users values(null,'tom',123,32,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tom      | 123      |  32 |    1 |
+----+----------+----------+-----+------+
1 row in set (0.00 sec)

mysql> insert users values(null,'john',123,28,1);
Query OK, 1 row affected (0.00 sec)



mysql> select * from users;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tom      | 123      |  32 |    1 |
|  2 | john     | 123      |  28 |    1 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)


向表users中插入一条记录age使用默认值

mysql> insert users values(default,'cookie',123,default,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tom      | 123      |  32 |    1 |
|  2 | john     | 123      |  28 |    1 |
|  3 | cookie   | 123      |  10 |    1 |
+----+----------+----------+-----+------+
3 rows in set (0.00 sec)

向users表中插入一条记录，age可以是表达式

mysql> insert users values(default,'session',123,3*7-5,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tom      | 123      |  32 |    1 |
|  2 | john     | 123      |  28 |    1 |
|  3 | cookie   | 123      |  10 |    1 |
|  4 | session  | 123      |  16 |    1 |
+----+----------+----------+-----+------+
4 rows in set (0.00 sec)

向users表中插入多条记录

mysql> insert users values(default,'rose',123,3*7-5,0),(null,'tuple',123,18,1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from users;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tom      | 123      |  32 |    1 |
|  2 | john     | 123      |  28 |    1 |
|  3 | cookie   | 123      |  10 |    1 |
|  4 | session  | 123      |  16 |    1 |
|  5 | rose     | 123      |  16 |    0 |
|  6 | tuple    | 123      |  18 |    1 |
+----+----------+----------+-----+------+
6 rows in set (0.00 sec)


向users表中插入多条记录，并对password进行加密

mysql> insert users values(default,'book',123,3*7-5,0),(null,'mark',md5('123'),16,1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  32 |    1 |
|  2 | john     | 123                              |  28 |    1 |
|  3 | cookie   | 123                              |  10 |    1 |
|  4 | session  | 123                              |  16 |    1 |
|  5 | rose     | 123                              |  16 |    0 |
|  6 | tuple    | 123                              |  18 |    1 |
|  7 | book     | 123                              |  16 |    0 |
|  8 | mark     | 202cb962ac59075b964b07152d234b70 |  16 |    1 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

4-2 MySQL 插入记录INSERT SET-SELECT

INSERT

插入记录

INSERT [INTO] tbl_name SET col_name={expr | DAEFAULT},…

说明:与第一种方式的却别在于,此方法可以使用子查询(SubQuery)

引发子查询有三种，其中一种就是由比较运算符引发的子查询,

那么等号就是点型的比较运算符



mysql> insert users set username='Ben',password='456';
Query OK, 1 row affected (0.00 sec)



mysql> select * from users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  32 |    1 |
|  2 | john     | 123                              |  28 |    1 |
|  3 | cookie   | 123                              |  10 |    1 |
|  4 | session  | 123                              |  16 |    1 |
|  5 | rose     | 123                              |  16 |    0 |
|  6 | tuple    | 123                              |  18 |    1 |
|  7 | book     | 123                              |  16 |    0 |
|  8 | mark     | 202cb962ac59075b964b07152d234b70 |  16 |    1 |
|  9 | Ben      | 456                              |  10 | NULL |
+----+----------+----------------------------------+-----+------+
9 rows in set (0.00 sec)

INSERT

插入记录

​ INSERT [INTO] tbl_name [(col_name,…)] SELECT …

说明: 此方法可以将查询结果插入指定数据表

mysql> create table test(
    -> id smallint unsigned primary key auto_increment,
    -> name varchar(30)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert test (name) select username from users where age >=20;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
|  2 | john |
+----+------+
2 rows in set (0.00 sec)

4-3 MySQL 单表更新记录UPDATE

UPDATE

更新记录(单表更新)

UDATE  [LOW_PRIORITY]  [IGNORE]  table_reference SET
col_name={expr1|DEFAULT}  [,col_name2={expr2|DEFAULT}] ...
[WHERE  where_condition]

将users表中的"Ben"的age加5岁

mysql> update users set age=age+5 where username='Ben';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  32 |    1 |
|  2 | john     | 123                              |  28 |    1 |
|  3 | cookie   | 123                              |  10 |    1 |
|  4 | session  | 123                              |  16 |    1 |
|  5 | rose     | 123                              |  16 |    0 |
|  6 | tuple    | 123                              |  18 |    1 |
|  7 | book     | 123                              |  16 |    0 |
|  8 | mark     | 202cb962ac59075b964b07152d234b70 |  16 |    1 |
|  9 | Ben      | 456                              |  15 | NULL |
+----+----------+----------------------------------+-----+------+
9 rows in set (0.00 sec)


更新users表中的age字段和sex字段的所有记录

mysql> update users set age=age - id,sex=0;
Query OK, 9 rows affected (0.00 sec)
Rows matched: 9  Changed: 9  Warnings: 0

mysql> select * from users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    0 |
|  2 | john     | 123                              |  26 |    0 |
|  3 | cookie   | 123                              |   7 |    0 |
|  4 | session  | 123                              |  12 |    0 |
|  5 | rose     | 123                              |  11 |    0 |
|  6 | tuple    | 123                              |  12 |    0 |
|  7 | book     | 123                              |   9 |    0 |
|  8 | mark     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
|  9 | Ben      | 456                              |   6 |    0 |
+----+----------+----------------------------------+-----+------+
9 rows in set (0.00 sec)


更改users表中的id为偶数的记录

mysql> update users set age=age-1,sex=1 where id % 2 = 0;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0


mysql> select * from users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    0 |
|  2 | john     | 123                              |  25 |    1 |
|  3 | cookie   | 123                              |   7 |    0 |
|  4 | session  | 123                              |  11 |    1 |
|  5 | rose     | 123                              |  11 |    0 |
|  6 | tuple    | 123                              |  11 |    1 |
|  7 | book     | 123                              |   9 |    0 |
|  8 | mark     | 202cb962ac59075b964b07152d234b70 |   7 |    1 |
|  9 | Ben      | 456                              |   6 |    0 |
+----+----------+----------------------------------+-----+------+
9 rows in set (0.00 sec)

4-4 MySQL单表删除记录DELETE

DELETE

删除记录(单表删除)

​ DELETE FROM tbl_name [WHERE where_condition]

删除users表中的id=9这条记录

mysql> delete from users where id = 9;
Query OK, 1 row affected (0.00 sec)


mysql> select * from users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    0 |
|  2 | john     | 123                              |  25 |    1 |
|  3 | cookie   | 123                              |   7 |    0 |
|  4 | session  | 123                              |  11 |    1 |
|  5 | rose     | 123                              |  11 |    0 |
|  6 | tuple    | 123                              |  11 |    1 |
|  7 | book     | 123                              |   9 |    0 |
|  8 | mark     | 202cb962ac59075b964b07152d234b70 |   7 |    1 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)


当我再次插入一条记录的时候id就变成了10

mysql> insert users set username='which',password=md5('123');
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    0 |
|  2 | john     | 123                              |  25 |    1 |
|  3 | cookie   | 123                              |   7 |    0 |
|  4 | session  | 123                              |  11 |    1 |
|  5 | rose     | 123                              |  11 |    0 |
|  6 | tuple    | 123                              |  11 |    1 |
|  7 | book     | 123                              |   9 |    0 |
|  8 | mark     | 202cb962ac59075b964b07152d234b70 |   7 |    1 |
| 10 | which    | 202cb962ac59075b964b07152d234b70 |  10 | NULL |
+----+----------+----------------------------------+-----+------+
9 rows in set (0.00 sec)

4-5 MySQL 查询表达式解析

查找记录

​ SELECT select_expr [,select_expr …]

​ [

​ FROM table_references

​ [WHERE where_condition]

​ [GROUP BY {col_name | position} [ASC | DESC], …]

​ [HAVING where_condition]

​ [ORDER BY {col_name |expr | position} [ASC |DESC], …]

​ [LIMIT {[offset,] row_count | row_count OFFSET offset}]

​ ]

select_expr

查询表达式

​ 每一个表达式表示想要的一列，必须有至少一个

​ 多个列之间以英文逗号隔开

​ 星号(**)表示所有列。tbl_name.* * 可以表示命名表的所有列

​ 查询表达式可以使用 [AS] alias_name 为其赋予别名。

​ 别名可用于GROUP BY , ORDER BY 或HAVING 子句。


查询表达式的字段顺序可以和表的字段顺序不一致

mysql> select username,id from users;
+----------+----+
| username | id |
+----------+----+
| tom      |  1 |
| john     |  2 |
| cookie   |  3 |
| session  |  4 |
| rose     |  5 |
| tuple    |  6 |
| book     |  7 |
| mark     |  8 |
| which    | 10 |
+----------+----+
9 rows in set (0.00 sec)


使用tbl_name.col_name，假如我们以后有多张表，
查询id字段，每个表都有，就分不清是哪张表的id了
  

mysql> select users.id,users.username from users;
+----+----------+
| id | username |
+----+----------+
|  1 | tom      |
|  2 | john     |
|  3 | cookie   |
|  4 | session  |
|  5 | rose     |
|  6 | tuple    |
|  7 | book     |
|  8 | mark     |
| 10 | which    |
+----+----------+
9 rows in set (0.00 sec)



假如某个字段名比较长可以给给表的字段赋予别名
建议平时查询的时候起别名

mysql> select id as m_id,username as m_name from users;
+------+---------+
| m_id | m_name  |
+------+---------+
|    1 | tom     |
|    2 | john    |
|    3 | cookie  |
|    4 | session |
|    5 | rose    |
|    6 | tuple   |
|    7 | book    |
|    8 | mark    |
|   10 | which   |
+------+---------+
9 rows in set (0.00 sec)

4-6 MySQL where语句进行条件查询

WHERE

条件表达式

​ 对记录进行过滤,如果没有指定WHERE子句,则显示所有记录

​ 在WHERE表达式中,可以使用MySQL支持的函数或运算符

4-7 MySQL group by语句对查询结果分组

GROUP BY

查询结果分组

​ [GROUP BY {col_name | position} [ASC |DESC], …]

mysql> select * from users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    0 |
|  2 | john     | 123                              |  25 |    1 |
|  3 | cookie   | 123                              |   7 |    0 |
|  4 | session  | 123                              |  11 |    1 |
|  5 | rose     | 123                              |  11 |    0 |
|  6 | tuple    | 123                              |  11 |    1 |
|  7 | book     | 123                              |   9 |    0 |
|  8 | mark     | 202cb962ac59075b964b07152d234b70 |   7 |    1 |
| 10 | which    | 202cb962ac59075b964b07152d234b70 |  10 | NULL |
+----+----------+----------------------------------+-----+------+
9 rows in set (0.00 sec)

mysql> select sex from users group by sex;
+------+
| sex  |
+------+
| NULL |
|    0 |
|    1 |
+------+
3 rows in set (0.00 sec)

4-8 having语句设置分组条件

HAVING

分组条件

​ [HAVING where_condition]

4-9 order by语句对查询结果排序

ORDER BY

对查询结果进行排序

​ [ORDER BY {col_name | expr | position} [ASC | DESC], …]

mysql> select * from users order by id desc;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
| 10 | which    | 202cb962ac59075b964b07152d234b70 |  10 | NULL |
|  8 | mark     | 202cb962ac59075b964b07152d234b70 |   7 |    1 |
|  7 | book     | 123                              |   9 |    0 |
|  6 | tuple    | 123                              |  11 |    1 |
|  5 | rose     | 123                              |  11 |    0 |
|  4 | session  | 123                              |  11 |    1 |
|  3 | cookie   | 123                              |   7 |    0 |
|  2 | john     | 123                              |  25 |    1 |
|  1 | tom      | 123                              |  31 |    0 |
+----+----------+----------------------------------+-----+------+
9 rows in set (0.00 sec)

4-10 limit语句限制查询数量

LIMIT

限制查询结果返回的数量

​ [LIMIT {[offset,] row_count | row_count OFFSET offset}]

mysql> select * from test;
+----+-----------------+------+
| id | t_name          | sex  |
+----+-----------------+------+
|  1 | 宇智波佐助      |    0 |
|  2 | 酒泽麻衣        |    1 |
|  4 | 宇智波鼬        |    1 |
|  6 | 旋涡玖辛奈      |    0 |
|  7 | 麦克凯          | NULL |
|  8 | 浓眉            | NULL |
+----+-----------------+------+
6 rows in set (0.00 sec)

mysql> select * from `test` limit 2,3;
+----+-----------------+------+
| id | t_name          | sex  |
+----+-----------------+------+
|  4 | 宇智波鼬        |    1 |
|  6 | 旋涡玖辛奈      |    0 |
|  7 | 麦克凯          | NULL |
+----+-----------------+------+
3 rows in set (0.00 sec)


mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.60    |
+-----------+
1 row in set (0.00 sec)
mhh4399
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值