MySQL学习笔记-操作数据表中的记录
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL学习笔记-操作数据表中的记录,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含14177字,纯文字阅读大概需要21分钟。
内容图文
![MySQL学习笔记-操作数据表中的记录](/upload/InfoBanner/zyjiaocheng/483/a6af9d965cd548cf80d93ee0f84317e4.jpg)
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 |+----+----------+----------+-----+------+
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 |+----+----------+----------------------------------+-----+------+
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 |+----+----------------------+----------------------------------+-----+------+
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [,col_name2={expr2|DEFAULT}]... [WHERE where_condition]
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 |+----+----------------------+----------------------------------+-----+------+
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 |+----+----------+----------+-----+------+
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 |+----+----------------------+----------------------------------+-----+------+
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 |+----+----------------------+----------------------------------+-----+------+
DELETE FROM tbl_name [WHERE where_condition]
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 |+----+----------+----------------------------------+-----+------+
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)
mysql> SELECT id,username FROM users;+----+----------+| id | username |+----+----------+| 1 | Tom || 2 | John || 3 | Huang || 4 | John || 5 | John |+----+----------+
mysql> SELECT id AS userid ,username AS uname FROM users; +--------+-------+| userid | uname |+--------+-------+| 1 | Tom || 2 | John || 3 | Huang || 4 | John || 5 | John |+--------+-------+
[GROUP BY {col_name | position} [ASC | DESC],...]
mysql> SELECT sex FROM users GROUP BY sex;+------+| sex |+------+| 0 |+------+
[HAVING where_condition]
mysql> SELECT sex,age FROM users GROUP BY sex having age > 20; +------+-----+| sex | age |+------+-----+| 0 | 39 |+------+-----+
mysql> SELECT sex FROM users GROUP BY sex HAVING count(id) >= 2; +------+| sex |+------+| 0 |+------+
[ORDER BY {col_name | expr | position} [ASC | DESC],...]
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 |+----+----------+----------------------------------+-----+------+
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 |+----+----------+----------------------------------+-----+------+
[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 [into] 表名 [(列名,列名)]{values|value} ({(表达式|default},())insert [into] 表名 set 列名={(表达式|default},。。insert [into] 表名 [(列名,列名)] SELECT..
UPDATE [LOW_PRIORITY][IGNORE] table_reference SET col_name1={expr1|DEFAULT},[col_name2={expr2|DEFAULT}]...[WHERE where_condition]
DELETE FROM tbl_name [WHERE where_condition}
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}] #限制返回数量]
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学习笔记-操作数据表中的记录
标签:数据库 mysql
本文系统来源:http://kurolz.blog.51cto.com/11433546/1929133
内容总结
以上是互联网集市为您收集整理的MySQL学习笔记-操作数据表中的记录全部内容,希望文章能够帮你解决MySQL学习笔记-操作数据表中的记录所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。