频道栏目
首页 > 数据库 > MySQL > 正文
MySQL基本操作
2017-05-19 09:58:00      个评论    来源:ZX714311728的博客  
收藏   我要投稿
初步知识
数据库是一个以某种有组织方式存储的数据的集合 DBMS是数据库管理系统也叫数据库软件 MySQL是一种DBMS SQL语句是DBMS用来操控数据库的语言。
MySQL主配置文件
/etc/my.cnf

MySQL数据库文件存放位置 /var/lib/mysql

MySQL日志文件 /var/log/mysqld.log

查看MySQL版本 rpm -qi mysql-server rpm -qi mysql-devel
rpm -qi mysql

MySQL默认绑定的端口号 3306

SQL语句不分大小写 show databases;
ShOW databases;
上面两条语句相同



初步操作
登录MySQL
mysql -u root -p
退出MySQL \q
quit
exit

查看帮助 \h

查看每条命令的用法 help 命令名 如:help create

创建数据库 create database zhouxin;

查看可用数据库 show databases;

打开数据库 use zhouxin;

返回当前数据库内可用的表 show tables;

显示表中每个字段(即每列的名字)的字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息 describe 表名;



检索


从表中检索某一列 select 列名 from 表名;
如:select Host from db;

从表中检索多列 SELECT 列名,列名,列名 FROM 表名; (列名之间加逗号,最后一个列名后不加)
如: SELECT Host, User, Drop_priv FROM db;
检索表的所有列 SELECT * FROM 表名
如:SELECT * FROM db;

只检索一列当中不同的行 SELECT DISTINCT 列名 FROM 表名
如:SELECT DISTINCT vend_id FROM products;

返回检索当中的前n行 select 列名 from 表名 LIMIT n;
如:select prod_name from products limit 5;

返回检索当中从第m行开始的n行 select 列名 from 表名 LIMIT m,n; (不包括第m行) 如:select prod_name from products limit 3,6;



排序


按某一列排序 SELECT 列名 FROM 表名 ORDER BY 列名; 如: SELECT prod_name FROM products ORDER BY prod_price;

按多列排序 SELECT 列名
FROM 表名
ORDER BY 列名,列名,列名,...;
如: SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price, prod_name;

按降序排列 SELECT 列名 FROM 表名 ORDER BY 列名 DESC; 如: SELECT prod_name, prod_price, prod_id FROM products ORDER BY prod_price DESC;
找出最大的一行 SELECT 列名
FROM 表名
ORDER BY 列名 DESC
LIMIT 1;



过滤数据


过滤行 SELECT 列名 FROM 表名 WHERE 列名 = 值; ( 还可为: WHERE 列名 < 值 WHERE 列名 <= 值
WHERE 列名 > 值
WHERE 列名 >= 值
WHERE 列名 != 值
WHERE 列名 <> 值
WHERE 列名 BETWEEN 值 AND 值
) 如: SELECT prod_name, prod_price, prod_id FROM products WHERE prod_price = 2.50;
又如: SELECT prod_id, prod_name, prod_price
FROM products WHERE prod_price BETWEEN 3 AND 5;
空值检查 SELECT 列名
FROM 表名
WHERE 列名 IS NULL;
如: SELECT cust_id
FROM customers WHERE cust_email IS NULL;
ORDER BY和WHERE同时使用时,ORDER BY 必须位于 WHERE之后,否则会出错。
WHERE子句的AND子句 SELECT 列名
FROM 表名
WHERE where子句 AND where子句
如: SELECT prod_id FROM products WHERE vend_id = 1003 AND prod_price < 5;
WHERE子句的OR子句 SELECT 列名
FROM 表名
WHERE where子句 OR where子句
如: SELECT prod_id FROM products WHERE vend_id = 1003 OR prod_price < 5;
WHERE子句的混合AND OR子句
WHERE子句中既有AND又有OR时,最好应以小括号限定计算次序。否则默认是先计算AND再计算OR 如: SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
WHERE子句的IN操作符 IN操作符完成与OR操作符相同的功能 如: SELECT prod_id FROM products WHERE vend_id IN (1002,1003);
NOT操作符 NOT操作符用来否定它之后所跟的所有条件 如: SELECT prod_id FROM products WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_name;
LIKE操作符 LIKE操作符使用通配符进行过滤 百分号(%)通配符、下划线(_)通配符
%通配符可以匹配0个或多个字符
_通配符只能匹配1个字符

%通配符 SELECT 列名
FROM 表名
WHERE 列名 LIKE '字符%'
( 还可为:
WHERE LIKE '%字符'
WHERE LIKE '字符%字符'
)
如: SELECT prod_id, prod_name
FROM products WHERE prod_name LIKE '%anvil%';
_通配符 SELECT 列名
FROM 表名
WHERE 列名 LIKE '字符_'
( 还可为:
WHERE LIKE '_字符'
WHERE LIKE '字符_字符'
) 如: SELECT prod_id, prod_name
FROM products WHERE prod_name LIKE '_ ton anvil';


使用正则表达式


基本字符匹配(.) SELECT 列名
FROM 表名
WHERE 列名 REGEXP '匹配字符串'; 如: SELECT prod_id, prod_name
FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name; '.000'中的.是正则表达式语言中一个特殊的字符,它表示匹配任意一个字符。
OR匹配(|) SELECT 列名
FROM 表名
WHERE 列名 REGEXP '表达式|表达式'
ORDER BY 列名;
如: SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;

匹配几个字符之一([ ]) SELECT 列名
FROM 表名
WHERE 列名 REGEXP '[要匹配的字符]表达式'
ORDER BY 列名; 如: SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name; [ ]是另一种形式的OR语句。事实上,正则表达式[123]Ton为[1|2|3]Ton的缩写

匹配指定字符之外的任何东西([^]) SELECT 列名
FROM 表名
WHERE 列名 REGEXP '[^要匹配的字符]表达式'
ORDER BY 列名; 如: SELECT prod_name FROM products WHERE prod_name REGEXP '[^123] Ton' ORDER BY prod_name;
匹配范围([m-n]) SELECT 列名
FROM 表名
WHERE 列名 REGEXP '[m-n]表达式'
ORDER BY 列名; 如: SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;
匹配特殊字符(\\) SELECT 列名
FROM 表名
WHERE 列名 REGEXP '\\特殊字符'
ORDER BY 列名; 如: SELECT prod_name FROM products WHERE prod_name REGEXP '\\.' ORDER BY prod_name;
定位符(^[ ]) SELECT 列名
FROM 表名
WHERE 列名 REGEXP '^[匹配字符]'
ORDER BY 列名; 如: SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name; ^匹配串的开始。因此,^[0-9\\.]只在.或任意数字为串中第一个字符时才匹配它们.
定位符([ ]$) SELECT 列名
FROM 表名
WHERE 列名 REGEXP '[匹配字符]$'
ORDER BY 列名; 如: SELECT prod_name FROM products WHERE prod_name REGEXP '[l]$' ORDER BY prod_name; $匹配串的结尾。因此,[l]$只在l为串中最后一个字符时才匹配它们.


计算字段


拼接字段(Concat( )) SELECT Concat(拼接字段)
FROM 表名
ORDER BY 列名
如: SELECT Concat(vend_name, '(', vend_country, ')')
FROM vendors ORDER BY vend_name; 上述Concat(vend_name, '(', vend_country, ')')语句中由4部分组成:vend_name, (, vend_country, )

RTrim去掉右边空格(Concat(RTrim(列), 语句)) SELECT Concat(RTrim(列), 拼接字段)
FROM 表名
ORDER BY 列名
如: SELECT Concat(RTrim(vend_name), '(', vend_country, ')')
FROM vendors ORDER BY vend_name; 上述RTrim(vend_name)去掉vend_name右侧空格 类似的还有 LTrim:去掉串左侧空格;Trim:去掉串两侧空格
为计算字段创建别名(AS) SELECT Concat(拼接字段) AS 别名
FROM 表名
ORDER BY 列名
如: SELECT Concat(vend_name, '(', vend_country, ')') AS vend_title
FROM vendors ORDER BY vend_name;
执行算术计算 SELECT 列名, 列名, 列名
列名 * 列名 AS 别名
FROM 表名
WHERE WHERE子句;
如: SELECT prod_id, quantity, item_price, quantity * item_price AS expanded_price FROM orderitems WHERE order_num = 20005; 列名 * 列名 AS 别名中的*号还可换为+, - , /号



小结


SELECT: 列、多列、*、DISTINCT、Concat( )、RTrim( )、列计算、函数、AS 别名 FROM: 表名 WHERE: =、!=、<、<=、>、>=、BETWEEN AND、IS NULL、 OR、AND、IN、NOT、LIKE '%'、LIKE '_'、 REGEXP '.[123][^123][1-3]^[1][l]$\\.|' ORDER BY: 列、多列、DESC LIMIT: 行、行m,行n


函数

文本处理函数 Left() 返回串左边的字符 Length() 返回串的长度 Locate() 找出串的一个子串 Lower() 将串转换为小写 LTrim() 去掉串左边的空格 Right() 返回串右边的字符 RTrim() 去掉串右边的空格 Soundex() 返回串的SOUNDEX值 SubString() 返回子串的字符 Upper() 将串转换为大写
如: SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
日期和时间函数
数值处理函数


聚集函数


AVG() 返回某列的平均值 COUNT() 返回某列的行数 MAX() 返回某列的最大值 MIN() 返回某列的最小值 SUM() 返回某列值之和
AVG( ): SELECT AVG(列名) AS 别名
FROM 表名; 或 SELECT AVG(列名) AS 别名 FROM 表名 WHERE WHERE子句;

如: SELECT AVG(prod_price) AS avg_price FROM products; 或 SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;

COUNT( ) SELECT COUNT(*) AS 别名 FROM 表名;
或 SELECT COUNT(列名) AS 别名
FROM 表名;

如果指定列名,则指定列的值为空的行被COUNT()函数忽略,但如果COUNT()函数中用的是星号(*),则不忽略。
如: SELECT COUNT(*) AS num_cust
FROM customers;
或 SELECT COUNT(cust_email) AS num_cust
FROM customers;

MAX( ) SELECT MAX(列名) AS 别名
FROM 表名
如: SELECT MAX(prod_price) AS max_price
FROM products;
MIN( ) SELECT MIN(列名) AS 别名
FROM 表名
如: SELECT MIN(prod_price) AS max_price
FROM products;
SUM( ) SELECT SUM(列名) AS 别名
FROM 表名
WHERE WEHRE子句; 如: SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;

组合聚集函数 SELECT COUNT(*) AS num_items, MIN(prod_price) AS min_price, MAX(prod_price) AS max_price, AVG(prod_price) AS avg_price FROM products;

 

分组


GROUP BY 对数据进行分组 SELECT 列名
FROM 表名
GROUP BY 列名;
如: SELECT COUNT(*) AS TOTAL
FROM products
GROUP BY vend_id;
GROPU BY必须出现在WHERE语句之后,ORDER BY 语句之前(如果它俩有的话) 不要依赖GROUP BY的排序,如果要排序应该明确的用ORDER BY
HAVING 过滤分组 SELECT 列
FROM 表
GROUP BY 列
HAVING 条件; 如: SELECT vend_id , COUNT(*) AS TOTAL FROM products GROUP BY vend_id HAVING COUNT(*) >= 2; WHERE和HAVING区别:WHERE在分组前过滤数据,HAVING在分组后过滤数据
SELECT子句顺序 SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT
SELECT 子查询 嵌套使用SELECT语句 SELECT 列 FROM 表 WHERE 列 IN (SELECT 子查询语句);
如: SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');


联结

外键:外键为某个表中的一列,它包含另一个表的主键值。 联结是一种机制,用来在一条SELECT语句中关联表。联结使得可以同时显示处于不同表中的内容 创建联结 SELECT 表A中的列,表B中的列
FROM 表A,表B
WHERE 表A.列x=表B.列x 如: SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name; 应该保证所有的联结都有WHERE语句
联结多个表 SELECT 表A中的列,表B中的列,表C中的列 FROM 表A,表B,表C WHERE 表A.列x = 表B.列x
AND 表A.列y = 表C.列y; 如: SELECT prod_name, vend_name, prod_price, quantity FROM orderitems, products, vendors WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id;
表也可以用AS建立别名 前面的联结称为“等值联结”

自联结 相同的表通过别名多次出现 SELECT A.列x,B.列y
FROM 表A AS 别名A, 表A AS 别名B
WHERE A.列x = B.列x
AND A.列语句
如: SELECT prod_id, prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DINTR'; 自联结通常代替同一表中使用的子查询,比子查询快的多。


组合查询

UNION 可用UNION操作符来组合数条SQL查询。称为 使用UNION可极大地简化复杂的WHERE子句,简化从多个表中检索数据的工作。
查询语句1 UNION 查询语句2; 如: SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001,1002);
使用UNION查询会自动取消在多个查询中重复的行,若想不取消重复的行,可使用UNION ALL 在用UNION组合查询时,只能使用一条ORDER BY子句,它必须出现在最后一条SELECT语句之后。


全文本搜索

Mysql中两个最常使用的引擎为MyISAM和InnoDB,前者支持全文本搜索,而后者不支持。
使用全文本搜索时,MySQL创建指定列中各词的一个索引,搜索可以针对这些词进行。为了进行全文本搜索,必须索引被搜索的列,而且要随着数据的改变不断地重新索引。 全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回。 SELECT 列 FROM 行 WHERE Match(列) Against('要搜索的词'); 如: SELECT note_text
FROM productnotes WHERE Match(note_text) Against('rabbit'); Match指定要全文本搜索的行,Against指定要搜索的字符串。
查询扩展 利用查询扩展,能找出可能相关的结果,即使它们并不精确包含所查找的词。 SELECT 列 FROM 行 WHERE Match(列) Against('要搜索的词' WITH QUERY EXPANSION);
布尔文本搜索 使用布尔搜索可以完成:要匹配的词;要排斥的词(如果某行包含这个词,则不返回该行,即使它包含其他指定的词也是如此);排列提示(指定某些词比其他词更重要,更重要的词等级更高);表达式分组;另外一些内容。 如: SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE); 搜索包含heavy的行,并且排除包含rope或以rope为开始单词的行 \


插入数据

插入一行 INSERT INTO 表名( 列1, 列2, 列3, 列4)
VALUES( 值1, 值2, 值3, 值4);
如: INSERT INTO customers(
cust_address, cust_city, cust_country) VALUES( NULL, 'Los Angles', 'USA');
插入时,值的顺序和前面语句中列出现的顺序相同,但不需要与表中列的顺序相同。
插入多行 只要每条INSERT语句中的列名(和次序)相同,可以如下插入 INSERT INTO 表名( 列1, 列2, 列3, 列4)
VALUES( 值1, 值2, 值3, 值4), VALUES( 值12, 值22, 值32, 值42);
插入检索出来的数据 如:从另一个表中检索出来的数据插入这个表 INSERT INTO 表A( 列1, 列2, 列3, 列4) SELECT 列1,
列2,
列3,
列4
FROM 表B



更新和删除数据

UPDATE UPDATE 表
SET 列1 = 新值
WHERE WHERE子句;
如: UPDATE customers
SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005; 上述中,cust_email是要更新的列,通过cust_id = 10005确定了要更新哪一行,若没有这条语句,则更新所有行。
更新多列 UPDATE 表
SET 列1 = 新值,
SET 列2 = 新值
WHERE WHERE子句;
删除某列的值 将该列更新为NULL(如果允许NULL的话)即可 UPDATE 表
SET 列 = NULL
WHERE WHERE子句;

DELETE DELETE删除整行而不是删除列。为了删除指定的列,请使用UPDATE语句。 DELETE FROM 表 WHERE WHERE子句;
MySQL没有撤销(undo)按钮。应该非常小心地使用UPDATE和DELETE。 UPDATE和DELETE语句最好必须具有WHERE子句,否则UPDATE会更新该列的所有行,DELETE会删除表的所有数据。

创建和操纵表

CREATE TABLE 如: CREATE TABLE customers
( cust_id int NOT NULL AUTO_INCREMENT, cust_name char(50) NOT NULL, cust_address char(50) NOT NULL, cust_city char(50) NULL, cust_email char(50) NULL, cust_quantity int NOT NULL DEFAULT 1, PRIMARY KEY (cust_id) )ENGINE=InnoDB; 其中: customers:表名 cust_*:列名 int, char(...):数据类型 NOT NULL:这一列不允许有空值 NULL:这一列运行有空值 AUTO_INCREMENT:插入新行时,该列的值会自动增加 DEFAULT 1:该列的默认值,插入新行时,若不指定具体指,则用该默认值代替。 PRIMARY KEY:表的主键,主键不能允许空值;可以有多个主键:PRIMARY KEY(主键1,主键2) ENGINE=InnoDB:采用哪种引擎。
在创建新表时,指定的表名必须不存在,否则将出错 每个表列或者是NULL列,或者是NOT NULL列。NULL为默认设置,如果不指定NOT NULL,则认为指定的是NULL。 主键值必须唯一。为创建由多个列组成的主键,应该以逗号分隔的列表给出各列名。 主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。 AUTO_INCREMENT告诉MySQL,本列每当增加一行时自动增量。 每个表只允许一个AUTO_INCREMENT列,而且它必须被索引
以下是几个需要知道的引擎:  InnoDB是一个可靠的事务处理引擎(参见第26章),它不支持全文 本搜索;  MEMORY在功能等同于MyISAM,但由于数据存储在内存(不是磁盘) 中,速度很快(特别适合于临时表);  MyISAM是一个性能极高的引擎,它支持全文本搜索(参见第18章), 但不支持事务处理。
删除表 DROP TABLE 表名;
重命名表 RENAME TABLE 旧表名 TO 新表名;


视图

创建视图 CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orders.order_num = orderitems.order_num;
使用视图 SELECT cust_name, cust_contact
FROM productcustomers WHERE prod_id = 'TNT2';
如上,productcustomers就是创建的视图。
视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询。 视图,它不包含表中应该有的任何列或数据,它包含的是一个SQL查询 视图的最常见的应用之一是隐藏复杂的SQL,这通常都会涉及联结。 可以看出,视图极大地简化了复杂SQL语句的使用。利用视图,可一次性编写基础的SQL,然后根据需要多次使用。


存储过程

存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。


事务处理

MyISAM和InnoDB是两种最常使用的引擎。前者不支持明确的事务处理管理,而后者支持。 事务处理(transaction processing)可以用来维护数据库的完整性,它保证成批的MySQL操作要么完全执行,要么完全不执行。
事务开始 START TRANSACTION
回退 ROLLBACK;
提交 COMMIT; 在事务处理块中,提交不会隐含地进行。为进行明确的提交,使用COMMIT语句
保留点 SAVEPOINT 保留点名字; 如:SAVEPOINT deletel;
回退到保留点 ROLLBACK TO 保留点名字;
如: START TRANSACTION; SELECT * FROM products; SAVEPOINT deletel; DELETE products; SELECT *FROM products; ROLLBACK TO deletel; COMMIT;

安全管理

在现实世界的日常工作中,决不能使用root。应该创建一系列的账号,有的用于管理,有的供用户使用,有的供开发人员使用,等等。
MySQL用户账号和信息存储在名为mysql的MySQL数据库中,mysql数据库有一个名为user的表,它包含所有用户账号。user表有一个名为user的列,它存储用户登录名。 USE mysql;
SELECT user FROM user;
创建用户账号 CREATE USER 账号 IDENTIFIED BY '密码';
如: CREATE USER zhouxin IDENTIFIED BY '111111';
重命名账号 RENAME USER 旧账号名 TO 新账号名; 如: RENAME USER zhouxin TO zx;
删除账号 DROP USER 账号名
如:
DROP USER zx;

更改账号的密码 SET PASSWORD FOR 账号 = Password('新密码');
如: SET PASSWORD FOR zx = Password('111');
更改当前账号的密码
SET PASSWORD = Password('新密码');

设置账号权限 1.显示账号的当前权限 SHOW GRANTS FOR 账号名;

2.GRANT设置权限 GRANT 要授予的权限 ON 被授予访问权限的数据库或表 TO 账号名;
如:
GRANT SELECT ON crashcourse.* TO zx;
上面SQL意思是:授予账号zx对数据库crashcourse的所有表(.*)有SELECT权限。

3.REVOKE撤销权限 REVOKE 要撤销的权限 ON 被撤销访问权限的数据库或表 FROM 账号名;
如:
REVOKE SELECT ON crashcourse.* FROM zx;

点击复制链接 与好友分享!回本站首页
上一篇:linux进入mysql数据库
下一篇:mysqlgroupby组内排序方法
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训

版权所有: 红黑联盟--致力于做实用的IT技术学习网站