首页 > 代码库 > MySQL学习笔记-操作数据表中的记录

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

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   |+----+----------+

 


本文出自 “谢育政” 博客,请务必保留此出处http://kurolz.blog.51cto.com/11433546/1929133

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