MySQL学习笔记-操作数据表中的记录

 

1.插入记录 INSERT

INSERT [INTO] tbl_name [(col_name,...)] {VAULES|VALUE} ({expr|DEFAULT},...),(...),...

例，插入单条记录：

mysql> USE testDatabase changed
mysql> CREATE TABLE users(    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,    -> username VARCHAR(20) NOT NULL,    -> password VARCHAR(32) NOT NULL,    -> age TINYINT UNSIGNED NOT NULL DEFAULT 10,    -> sex BOOLEAN    -> );
mysql> INSERT users VALUES(NULL,'Tom','asd123',24,1);
mysql> SELECT * FROM users;+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  1 | Tom      | asd123   |  24 |    1 |+----+----------+----------+-----+------+

 

插入多条记录：

mysql> INSERT users VALUES(DEFAULT,'John','asd123',24,1),    -> (NULL,'Huang','daddd',25,1);
mysql> SELECT * FROM users;                           
+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  1 | Tom      | asd123   |  24 |    1 ||  2 | John     | asd123   |  24 |    1 ||  3 | Huang    | daddd    |  25 |    1 |+----+----------+----------+-----+------+

 

插入数学表达式：

mysql> INSERT users VALUES(NULL,'John','asd123',3*7-5,1);
mysql> SELECT * FROM users;+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  1 | Tom      | asd123   |  24 |    1 ||  2 | John     | asd123   |  24 |    1 ||  3 | Huang    | daddd    |  25 |    1 ||  4 | John     | asd123   |  16 |    1 |+----+----------+----------+-----+------+

 

插入md5的哈希密码：

mysql> INSERT users VALUES(NULL,'John',md5('123'),DEFAULT,0)；
mysql> SELECT * FROM users;+----+----------+----------------------------------+-----+------+| id | username | password                         | age | sex  |+----+----------+----------------------------------+-----+------+|  1 | Tom      | asd123                           |  24 |    1 ||  2 | John     | asd123                           |  24 |    1 ||  3 | Huang    | daddd                            |  25 |    1 ||  4 | John     | asd123                           |  16 |    1 ||  5 | John     | 202cb962ac59075b964b07152d234b70 |  10 |    0 |+----+----------+----------------------------------+-----+------+

 

2.插入记录 INSERT SET-SELECT    

 (与第一种方式的区别是 可以使用子查询，不可以同时插入多条记录)

INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},...

 

例：

mysql> INSERT users SET username='Ben',password=md5('345');
mysql> SELECT * FROM users;+----+----------------------+----------------------------------+-----+------+| id | username             | password                         | age | sex  |+----+----------------------+----------------------------------+-----+------+|  1 | Tom                  | asd123                           |  24 |    1 ||  2 | John                 | asd123                           |  24 |    1 ||  3 | Huang                | daddd                            |  25 |    1 ||  4 | John                 | asd123                           |  16 |    1 ||  5 | John                 | 202cb962ac59075b964b07152d234b70 |  10 |    0 ||  6 | Ben                  | d81f9c1be2e08964bf9f24b15f0e4900 |  10 | NULL |+----+----------------------+----------------------------------+-----+------+

 

3.更新记录 UPDATE 

单表更新

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

 

  • 例，将所有记录的年龄加5：

mysql> UPDATE users SET age = age+5;
mysql> SELECT * FROM users;                                
+----+----------------------+----------------------------------+-----+------+| id | username             | password                         | age | sex  |+----+----------------------+----------------------------------+-----+------+|  1 | Tom                  | asd123                           |  29 |    1 ||  2 | John                 | asd123                           |  29 |    1 ||  3 | Huang                | daddd                            |  30 |    1 ||  4 | John                 | asd123                           |  21 |    1 ||  5 | John                 | 202cb962ac59075b964b07152d234b70 |  15 |    0 ||  6 | Ben                  | d81f9c1be2e08964bf9f24b15f0e4900 |  15 | NULL |+----+----------------------+----------------------------------+-----+------+

 

  • 将Tom的年龄加10岁：

mysql> UPDATE users SET age = age+10 WHERE id=1;
mysql> SELECT * FROM users WHERE id=1;+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  1 | Tom      | asd123   |  39 |    1 |+----+----------+----------+-----+------+

 

  • 将id为偶数的年龄加10岁：

mysql> UPDATE users SET age = age+10 WHERE id % 2 = 0;
mysql> select * from users;                           
+----+----------------------+----------------------------------+-----+------+| id | username             | password                         | age | sex  |+----+----------------------+----------------------------------+-----+------+|  1 | Tom                  | asd123                           |  39 |    1 ||  2 | John                 | asd123                           |  39 |    1 ||  3 | Huang                | daddd                            |  30 |    1 ||  4 | John                 | asd123                           |  31 |    1 ||  5 | John                 | 202cb962ac59075b964b07152d234b70 |  15 |    0 |+----+----------------------+----------------------------------+-----+------+

 

  • 修改所有记录的sex为0：

mysql> UPDATE users SET sex = 0；
mysql> select * from users;    
+----+----------------------+----------------------------------+-----+------+| id | username             | password                         | age | sex  |+----+----------------------+----------------------------------+-----+------+|  1 | Tom                  | asd123                           |  39 |    0 ||  2 | John                 | asd123                           |  39 |    0 ||  3 | Huang                | daddd                            |  30 |    0 ||  4 | John                 | asd123                           |  31 |    0 ||  5 | John                 | 202cb962ac59075b964b07152d234b70 |  15 |    0 ||  7 | Ben                  | d81f9c1be2e08964bf9f24b15f0e4900 |  15 |    0 |+----+----------------------+----------------------------------+-----+------+

 

4.删除记录 DELETE

单表删除    (若不添加WHERE则删除【全部记录】)

DELETE FROM tbl_name [WHERE where_condition]

 

  • 例，删除id=7的记录：

mysql> DELETE FROM users WHERE id = 7;
mysql> select * from users;           
+----+----------+----------------------------------+-----+------+| id | username | password                         | age | sex  |+----+----------+----------------------------------+-----+------+|  1 | Tom      | asd123                           |  39 |    0 ||  2 | John     | asd123                           |  39 |    0 ||  3 | Huang    | daddd                            |  30 |    0 ||  4 | John     | asd123                           |  31 |    0 ||  5 | John     | 202cb962ac59075b964b07152d234b70 |  15 |    0 |+----+----------+----------------------------------+-----+------+

 

5.查找记录 SELECT

SELECT select_expr [,select_expr...][
  FROM tbl_references
  [WHERE where_condition]
  [GROUP BY {col_name | position} [ASC | DESC],...]  [HAVING where_condition]
  [ORDER BY {col_name | expo | position}  [ASC | DESC],...]  [LIMIT {[offset,] row_count | row_count OFFSET offset}]
]

 

 

查询表达式（select_expr） 

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

2. 多个列之间以英文逗号分隔 

3. 星号（*）表示所有列；tbl_name.*可以表示命名表的所有列 

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

5. 别名可用于GROUP BY，ORDER BY或HAVING子句 

SELECT查询表达式字段出现的顺序将影响结果集字段出现的顺序 

字段的别名也会影响以后的结果集  , 在以后的PHP中有重要作用

例，只查询users表中 id 和 username 两列：

mysql> SELECT id,username FROM users;+----+----------+| id | username |+----+----------+|  1 | Tom      ||  2 | John     ||  3 | Huang    ||  4 | John     ||  5 | John     |+----+----------+

 

查询users表中 id 和 username 两列，分别用userid，uname作为别名：

mysql> SELECT id AS userid ,username AS uname FROM users;                         
+--------+-------+| userid | uname |+--------+-------+|      1 | Tom   ||      2 | John  ||      3 | Huang ||      4 | John  ||      5 | John  |+--------+-------+

 

6.where语句进行条件查询

  •  条件表达式（WHERE）    

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

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

7.group by语句对查询结果分组

group by

查询结果分组

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

 

可以制定列的列名和列的位置。

asc生序，desc降序，多个分组中间用逗号区分。

例，查询users表，按照性别分组：

mysql> SELECT sex FROM users GROUP BY sex;+------+| sex  |+------+|    0 |+------+

 

8.having语句设置分组条件

分组条件（HAVING）

[HAVING where_condition]

 

其中，where_condition要么是聚合函数（max,min,avg,count,sum），要么其中的字段必须是SELECT中的一个查询字段，否则会报错]

例，查询users表中sex分组，条件为age>20（这种方法需要查询字段也添加age字段）：

mysql> SELECT sex,age FROM users GROUP BY sex having age > 20; 
+------+-----+| sex  | age |+------+-----+|    0 |  39 |+------+-----+

 

 

查询users表中sex分组，条件为id >=2 :

mysql> SELECT sex FROM users GROUP BY sex HAVING count(id) >= 2;    
+------+| sex  |+------+|    0 |+------+

 

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

对查询结果进行排序（ORDER BY）

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

 

可以按照多个列排序

例，查询users表，按年龄从小到大排列：

mysql> SELECT * FROM users ORDER BY age ASC; 
+----+----------+----------------------------------+-----+------+| id | username | password                         | age | sex  |+----+----------+----------------------------------+-----+------+|  8 | Hui      | 9e1e06ec8e02f0a0074f2fcc6b26303b |   8 |    1 ||  5 | John     | 202cb962ac59075b964b07152d234b70 |  15 |    0 || 10 | Luyuan   | 9e1e06ec8e02f0a0074f2fcc6b26303b |  21 |    1 ||  9 | Dui      | 9e1e06ec8e02f0a0074f2fcc6b26303b |  27 |    1 ||  3 | Huang    | daddd                            |  30 |    0 || 12 | Murouan  | 9e1e06ec8e02f0a0074f2fcc6b26303b |  31 |    1 || 11 | Oduyuan  | 9e1e06ec8e02f0a0074f2fcc6b26303b |  31 |    1 || 13 | Spqoan   | 9e1e06ec8e02f0a0074f2fcc6b26303b |  31 |    1 ||  4 | John     | asd123                           |  31 |    0 ||  2 | John     | asd123                           |  39 |    0 ||  1 | Tom      | asd123                           |  39 |    0 |+----+----------+----------------------------------+-----+------+

 

查询users表，第一顺序按年龄从小到大排列，第二顺序按id从小到大排列：

mysql> SELECT * FROM users ORDER BY age,id ASC;+----+----------+----------------------------------+-----+------+| id | username | password                         | age | sex  |+----+----------+----------------------------------+-----+------+|  8 | Hui      | 9e1e06ec8e02f0a0074f2fcc6b26303b |   8 |    1 ||  5 | John     | 202cb962ac59075b964b07152d234b70 |  15 |    0 || 10 | Luyuan   | 9e1e06ec8e02f0a0074f2fcc6b26303b |  21 |    1 ||  9 | Dui      | 9e1e06ec8e02f0a0074f2fcc6b26303b |  27 |    1 ||  3 | Huang    | daddd                            |  30 |    0 ||  4 | John     | asd123                           |  31 |    0 || 11 | Oduyuan  | 9e1e06ec8e02f0a0074f2fcc6b26303b |  31 |    1 || 12 | Murouan  | 9e1e06ec8e02f0a0074f2fcc6b26303b |  31 |    1 || 13 | Spqoan   | 9e1e06ec8e02f0a0074f2fcc6b26303b |  31 |    1 ||  1 | Tom      | asd123                           |  39 |    0 ||  2 | John     | asd123                           |  39 |    0 |+----+----------+----------------------------------+-----+------+

 

10.LIMIT语句限制查询数量

LIMIT 限制查询结果返回的数量

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

 

SELECT * from users  #返回所有的结果 
SELECT * from users limit 2  #返回所有的结果集中前两条

 

mysql> SELECT * FROM users LIMIT 2;+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  1 | Tom      | asd123   |  39 |    0 ||  2 | John     | asd123   |  39 |    0 |+----+----------+----------+-----+------+

 

SELECT * FROM users LIMIT 2,2;  #要查询结果集中第3条和第4条记录。mysql中的记录是从0开始的，共几条。

 

mysql> SELECT * FROM users LIMIT 2,2;+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  3 | Huang    | daddd    |  30 |    0 ||  4 | John     | asd123   |  31 |    0 |+----+----------+----------+-----+------+

 

 

总结

记录操作: INSERT , UPDATE , DELETE , SELECT

1.insert的三种

insert [into]  表名 [(列名,列名)]{values|value} ({（表达式|default},（）)insert [into]  表名 set 列名={（表达式|default}，。。insert [into]  表名 [(列名,列名)] SELECT..

 

2.UPDATE 

（1）单表更新

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

 

多表更新

3.DELETE : 删除数据

（1）单表删除

DELETE FROM tbl_name [WHERE where_condition}

 

4.SELECT: //查询

SELECT select_expr [,select expr2...] #只查找某一个函数或表达式[FROM table_references #查询表名[WHERE where_conditon]  #查询条件[GROUP BY {col_name|position} [ASC|DESC],...] #按某个字段进行分组，相同的只显示第一个[HAVING where_conditon] #分组时，给出显示条件[ORDER BY {col_name|expr|position} [ASC|DESC],...] #排序[LIMIT {[offset,]row_count|row_count OFFSET offset}] #限制返回数量]

 

SELECT 

FROM 表的参照

WHERE 条件

GROUP BY 对记录结果进行分组

HAVING 对分组条件的设定

 

结合内容，将查找的结果存储在指定的数据表：

mysql> CREATE TABLE test(    -> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,    -> username VARCHAR(20) NOT NULL

    -> );

 

mysql> INSERT test(username) SELECT username FROM users WHERE age >= 25;

mysql> SELECT * FROM test;                                   

+----+----------+| id | username |+----+----------+|  1 | Tom      ||  2 | John     ||  3 | Huang    ||  4 | John     ||  5 | Dui      ||  6 | Oduyuan  ||  7 | Murouan  ||  8 | Spqoan   |+----+----------+