{"id":1037,"date":"2011-06-03T19:20:26","date_gmt":"2011-06-03T11:20:26","guid":{"rendered":"http:\/\/www.jpuyy.com\/blog\/?p=1037"},"modified":"2020-05-05T12:49:17","modified_gmt":"2020-05-05T04:49:17","slug":"mysql-sql-statement","status":"publish","type":"post","link":"https:\/\/jpuyy.com\/?p=1037","title":{"rendered":"mysql \u547d\u4ee4 | sql\u8bed\u53e5 | sql\u8bed\u6cd5"},"content":{"rendered":"

0\u3001\u767b\u9646\uff1a<\/p>\n

mysql -u root -p<\/pre>\n
\u6307\u5b9a\u7528utf8\u6765\u8fde\u63a5\u6570\u636e\u5e93\uff0c\u5927\u90e8\u5206\u65f6\u5019\u5c31\u4e0d\u4f1a\u663e\u793a\u4e71\u7801<\/p>\n
mysql -u root -p --default-character-set=utf8<\/pre>\n
1\u3001\u663e\u793a\u6570\u636e\u5e93\u5217\u8868\u3002<\/p>\n
 show databases;<\/pre>\n
2\u3001\u663e\u793a\u5e93\u4e2d\u7684\u6570\u636e\u8868\uff1a<\/p>\n
 use mysql;\n show tables;<\/pre>\n
3\u3001\u663e\u793a\u6570\u636e\u8868\u7684\u7ed3\u6784\uff1a<\/p>\n
 describe \u8868\u540d;<\/pre>\n
4\u3001\u5efa\u5e93\uff1a<\/p>\n
 create database \u5e93\u540d;<\/pre>\n
create database if not exists \u5e93\u540d default charset utf8 collate utf8_general_ci;<\/pre>\n
5\u3001\u5efa\u8868\uff1a<\/p>\n
 use \u5e93\u540d\uff1b\n create table \u8868\u540d (\u5b57\u6bb5\u8bbe\u5b9a\u5217\u8868)\uff1b<\/pre>\n
6\u3001\u5220\u5e93\u548c\u5220\u8868:<\/p>\n
 drop database \u5e93\u540d;\n drop table \u8868\u540d\uff1b<\/pre>\n
7\u3001\u5c06\u8868\u4e2d\u8bb0\u5f55\u6e05\u7a7a\uff1a<\/p>\n
 delete from \u8868\u540d;<\/pre>\n
8\u3001\u663e\u793a\u8868\u4e2d\u7684\u8bb0\u5f55\uff1a<\/p>\n
 select * from \u8868\u540d<\/pre>\n
9\u3001update\u8bed\u6cd5<\/p>\n
UPDATE \u8868\u540d\u79f0 SET \u5217\u540d\u79f0 = \u65b0\u503c WHERE \u5217\u540d\u79f0 = \u67d0\u503c\n\u5f53\u6709\u591a\u4e2a\u5217\u540d\u79f0\u8981\u4fee\u6539\u7684\u65f6\u5019\u7528\"\uff0c\"\u5206\u9694\u3002<\/pre>\n
\u4f8b\uff1a\u628aradius\u67d0\u7528\u6237\u8bbe\u4e3a\u7981\u6b62<\/p>\n
UPDATE  `radusergroup` SET `groupname`='daloRADIUS-Disabled-Users'  WHERE `username` in ('xxyy ','uuxx');<\/pre>\n
10\u3001\u5237\u65b0\u6743\u9650\uff1a<\/p>\n
flush privileges;\u00ad<\/pre>\n
11\u3001\u6539\u53d8\u6570\u636e\u5e93databaseA\u7684\u5b57\u7b26\u96c6\u4e3autf8<\/p>\n
ALTER DATABASE `databaseA` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci<\/pre>\n
\u9000\u51fa\uff1a<\/p>\n
mysql>quit<\/pre>\n
12\u3001limit\u547d\u4ee4<\/p>\n
\u53d6\u51fa\u524d5\u6761\u6570\u636e<\/p>\n
SELECT * FROM `user_data` LIMIT 5<\/pre>\n
\u53d6\u51fa\u7b2c5, 6\u6761\u6570\u636e<\/p>\n
SELECT * FROM `user_data` limit 3,1<\/pre>\n
13\u3001like\u547d\u4ee4<\/p>\n
\u67e5\u627e\u4ee5189\u5f00\u5934\u7684\u6761\u76ee<\/p>\n
SELECT * from shoujihao WHERE number LIKE '189%';<\/pre>\n
14\u3001shell\u4e0b\u6267\u884csql\u8bed\u53e5<\/p>\n
mysql -u root -p database -e \"select User, Host From user\"<\/pre>\n
\u5728mysql\u547d\u4ee4\u884c\u5185\u6267\u884c\u7cfb\u7edf\u547d\u4ee4<\/p>\n
mysql> system less \/home\/jpuyy\/.vimrc<\/pre>\n
\u5220\u9664\u4e00\u4e2a\u7528\u6237<\/p>\n
drop user 'yyy'@'localhost';<\/pre>\n
14. SELECT DISTINCT\u7b5b\u9009\u552f\u4e00\u7684\u5217\uff0c\u5982<\/p>\n
SELECT DISTINCT vend_id FROM products;<\/p>\n
\u4f1a\u8fd4\u56de\u53bb\u91cd\u7684\u7ed3\u679c<\/p>\n
15. \u521b\u5efa\u552f\u4e00\u952e<\/p>\n
ALTER TABLE host add UNIQUE (object_id);
\n16. \u4e00\u4e9b\u51fd\u6570<\/p>\n
select count(column_name) \u8ba1\u6570\u000b\nselect sum(column_name) \u603b\u8ba1\nselect avg(column_name) \u5e73\u5747\u6570\nselect max(column_name) \u6700\u5927\nselect min(column_name) \u6700\u5c0f\n<\/pre>\n
16. \u6bcf\u4e2a\u5206\u7ec4\u5927\u4e8e1<\/p>\n
select genre, sum(cost) from Movies Group By genre Having count(*)>1; <\/pre>\n","protected":false},"excerpt":{"rendered":"
0\u3001\u767b\u9646\uff1a mysql -u root -p \u6307\u5b9a\u7528utf8\u6765\u8fde\u63a5\u6570\u636e\u5e93\uff0c\u5927\u90e8\u5206\u65f6\u5019\u5c31\u4e0d\u4f1a\u663e\u793a\u4e71\u7801 mysql -u root -p –default-character-set=utf8 1\u3001\u663e\u793a\u6570\u636e\u5e93\u5217\u8868\u3002 show databases; 2\u3001\u663e\u793a\u5e93\u4e2d\u7684\u6570\u636e\u8868\uff1a use mysql; show tables; 3\u3001\u663e\u793a\u6570\u636e\u8868\u7684\u7ed3\u6784\uff1a describe \u8868\u540d; 4\u3001\u5efa\u5e93\uff1a create database \u5e93\u540d; create database if not exists \u5e93\u540d default charset utf8 collate utf8_general_ci; 5\u3001\u5efa\u8868\uff1a use \u5e93\u540d\uff1b create table \u8868\u540d (\u5b57\u6bb5\u8bbe\u5b9a\u5217\u8868)\uff1b 6\u3001\u5220\u5e93\u548c\u5220\u8868: drop database \u5e93\u540d; drop table \u8868\u540d\uff1b 7\u3001\u5c06\u8868\u4e2d\u8bb0\u5f55\u6e05\u7a7a\uff1a delete from \u8868\u540d; […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[65],"tags":[23],"class_list":["post-1037","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-summary"],"_links":{"self":[{"href":"https:\/\/jpuyy.com\/index.php?rest_route=\/wp\/v2\/posts\/1037","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jpuyy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jpuyy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jpuyy.com\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/jpuyy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1037"}],"version-history":[{"count":26,"href":"https:\/\/jpuyy.com\/index.php?rest_route=\/wp\/v2\/posts\/1037\/revisions"}],"predecessor-version":[{"id":8877,"href":"https:\/\/jpuyy.com\/index.php?rest_route=\/wp\/v2\/posts\/1037\/revisions\/8877"}],"wp:attachment":[{"href":"https:\/\/jpuyy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1037"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jpuyy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1037"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jpuyy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1037"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}