全面掌握Mysql及select支持的运算操作

浏览数：20 / 时间：2015年06月12日

转载请注明出处：http://blog.csdn.net/lhy_ycu/article/details/45725317

花了2天时间整理了一下Mysql中的常用命令及select支持的运算操作，希望这篇博客对大家有所帮助。

内容提纲

1、登录mysql
2、查看mysql信息
3、查看所有数据库
4、打开指定数据库
5、李处指定数据库中的所有表
6、查看指定表的结构
7、查看建表语句
8、建库建表
9、修改指定表结构
10、查看数据库的编码方式
11、DML语句(CRUD)操作
12、注释方式
13、表数据的导入导出
14、数据库的导入导出
15、视图View
16、索引index
17、存储过程Procedure
18、事务Transaction
19、Mysql用户创建、授权及删除
20、select支持的运算操作
21、select支持的逻辑运算操作
附：忘记密码及修改密码

1）登录Mysql(本地、远程)服务器

登录本地(假设用户名：root; 密码：123456)：mysql -u root -p 123456
登录远程(假设服务器ip：192.168.0.111; 用户名：admin; 密码：admin)：mysql -h 192.168.0.111 -u admin -p admin

2）查看mysql信息：status

mysql> status
--------------
mysql Ver 14.14 Distrib 5.6.10, for Win32 (x86)

Connection id: 8
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.6.10 MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: gbk
Conn. characterset: gbk
TCP port: 3306
Uptime: 17 min 5 sec

3）列出所有数据库（这里是以root身份查看的）

4）打开指定数据库：use db_name;

mysql> use product;
Database changed

5）列出product数据库中的所有表：show tables;

mysql> show tables;
+-------------------+
| Tables_in_product |
+-------------------+
| test |
+-------------------+

6）查看指定表的结构：desc table_name;

7）查看建表语句DDL： show create table table_name;

| test | CREATE TABLE `test` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`type_name` varchar(10) DEFAULT NULL,
`color_id` varchar(10) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

8）实战：建库、建表

#若存在mydb数据库则删除
drop database if exists mydb;
#建库mydb
create database mydb default charset=utf8;
#打开数据库
use mydb;
#若存在mytest表则删除
drop table if exists mytest;
#建表
create table mytest (
Id int(11) NOT NULL AUTO_INCREMENT,
type_name varchar(10) DEFAULT NULL,
color_name varchar(10) DEFAULT NULL,
PRIMARY KEY (Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#外键约束(级联删除)：constraint 约束名 foreign key 该表的字段 references dept(Id) on delete cascade on update cascade;
#查看mydb里面有哪些表
show tables;
#查看mytest的表结构
desc mytest;
#查看mytest的建表语句
show create table mytest;
#删除表：drop table mytest;
#删除指定数据库下的所有表
mysql> use information_schema;
mysql> select concat(‘drop table if exists ‘,table_name, ‘;‘ ) from tables where table_schema=‘db_name‘;
#删除库：drop database mydb;

9）修改mytest表结构

#创建临时表：临时表是指创建后只在当前数据库内有效，断开与数据库的连接后表就自动删除
create temporary table tb_temp(
id integer,
name varchar(100),
primary key(id)
);

10）查看数据库的编码方式

11）DML语句（select、insert、update、delete），简称CRUD

#插入
mysql> insert into test01(type_name,description) values(‘综艺‘,‘最近更新‘);

#更新
mysql> update test01 set type_name=‘电影‘ where type_name=‘综艺‘;

#查询(由于select支持的查询语句非常广，所以后面会单独拿出一篇来写)
mysql> select * from test01 where Id=1;
#查询test01表中有几行记录
mysql> select count(*) from test01;
#注意：count(*)和count(1)时统计包括null的, 而count(列名)不包含null值，且经试验得出count(1)的速度最快(Oracle中也是如此)。

#删除(单删、全删)
mysql> delete from test01 where Id=1 and type_name=‘电影‘;
mysql> delete from test01;

#清空数据：Id从1开始
mysql> truncate table user;/*清除user表中所有数据*/

12）注释方式：#、--、/**/

13）表中数据的导入、导出

#查看表test01的数据
mysql> select * from test01;
+----+-----------+-------------+
| Id | type_name | description |
+----+-----------+-------------+
| 1 | 综艺 | 最近更新 |
| 2 | 电影 | 精彩预告 |
+----+-----------+-------------+
#导出：将表test01中的记录导出到f盘的test.txt文件中
mysql> select * from test01 into outfile ‘f:\\test.txt‘;
#导出数据后，清空表test01中的数据
mysql> truncate table test01;
#可以看到，test01表中当前无数据
mysql> select * from test01;
Empty set (0.00 sec)

#导入：将f盘的test.txt文件中备份的数据导入到表test01中
mysql> load data infile ‘f:\\test.txt‘ into table test01;
#可以看到，数据已被导入到表test01中
mysql> select * from test01;
+----+-----------+-------------+
| Id | type_name | description |
+----+-----------+-------------+
| 1 | 综艺 | 最近更新 |
| 2 | 电影 | 精彩预告 |
+----+-----------+-------------+

14）数据库的导入、导出

#导出数据库：-d导出表结构，-t导出数据，不加是导出整个数据库
mysql> mysqldump -uroot -p [-d|-t] dbname [tab_name]>e:\mybk.sql

#导入数据库：
mysql> mysql -uroot -p new_dbname <e:\mybk.sql

15）视图View

视图的工作机制: 当调用视图的时候，才会执行视图中的sql，进行取数据操作。视图的内容并没有存储，而是在视图被引用的时候才派生出数据。这样不会占用空间，由于是即时引用，视图的内容总是与真实表的内容是一致的。从视图的工作机制可以看出：视图是一种虚拟的表，它并没有保存内容，只是引用数据。一般在要经常需要某个查询结果做子查询的时候会使用到视图，注意运行创建/删除视图的语句需要用户具有创建/删除视图的权限。创建视图的语法：create view 视图名 as select 查询语句; 实例说明如下：

mysql> select * from goods;
+----------+------------+------------+--------+
| goods_id | goods_name | shop_price | cat_id |
+----------+------------+------------+--------+
| 1 | 苹果 | 8.80 | 2 |
| 2 | 西瓜 | 2.98 | 2 |
| 3 | 香蕉 | 6.80 | 2 |
| 4 | 白菜 | 1.80 | 1 |
| 5 | 韭菜 | 2.80 | 1 |
| 6 | 茄子 | 1.80 | 1 |
| 7 | 哈密瓜 | 3.80 | 2 |
+----------+------------+------------+--------+

如果要频繁查询某个种类下面的商品平均价格：mysql> select cat_id, avg(shop_price) from goods group by cat_id;
那么我们可以创建针对这种平均价格的视图：mysql> create view avgPrice as select cat_id, avg(shop_price) from goods group by cat_id;
然后我们就可以通过普通的查询语句来简化查询了：
mysql> select * from avgPrice;
+--------+-----------------+
| cat_id | avg(shop_price) |
+--------+-----------------+
| 1 | 2.133333 |
| 2 | 5.595000 |
+--------+-----------------+

可以看到，通过视图我们可以简化查询操作，视图还有一个好处就是可以进行权限控制。比如：对于goods表，我们不想让别人看到我们的销售价格，这时候我们就可以把查看goods表的权限封闭，创建一张视图：mysql> create view baseGoods as select goods_id,goods_name from goods;
mysql> select * from baseGoods;
+----------+------------+
| goods_id | goods_name |
+----------+------------+
| 1 | 苹果 |
| 2 | 西瓜 |
| 3 | 香蕉 |
| 4 | 白菜 |
| 5 | 韭菜 |
| 6 | 茄子 |
| 7 | 哈密瓜 |
+----------+------------+
这样就可以不出现销售价格列了。当然我们也可以修改视图，修改视图也比较简单：alter view 视图名 as select语句，比如对于avgPrice视图，我们还想看到商品名称：

mysql> alter view avgPrice as select cat_id,goods_name, avg(shop_price) from goods group by cat_id;
mysql> select * from avgPrice;
+--------+------------+-----------------+
| cat_id | goods_name | avg(shop_price) |
+--------+------------+-----------------+
| 1 | 白菜 | 2.133333 |
| 2 | 苹果 | 5.595000 |
+--------+------------+-----------------+

删除视图：mysql> drop view avgPrice;

16）索引index

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引是一个索引包含多个列。
对字段建立索引可以大大提高查询速度。假如我们创建了一个mytable表：
mysql> desc mytable;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID | int(11) | NO | | NULL | |
| username | varchar(16) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
MySQL索引类型包括：
（1）普通索引，这是最基本的索引，它没有任何限制。它有以下几种创建方式：
-- 创建索引
CREATE INDEX indexName ON mytable(username(10)); -- 单列索引
-- CREATE INDEX indexName ON mytable(username(10),Id(11)); -- 组合索引
-- indexName为索引名，mytable表名，username和Id为列名, 如果是CHAR，VARCHAR类型，前缀长度可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定前缀长度。

#实例演示：创建普通索引
mysql> create index indexName on mytable(username(10));
mysql> desc mytable;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID | int(11) | NO | | NULL | |
| username | varchar(16) | NO | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
#修改索引
mysql> alter table mytable add index indexName (username(10));
mysql> desc mytable;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID | int(11) | NO | | NULL | |
| username | varchar(16) | NO | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
#删除索引
mysql> drop index indexName on mytable;
mysql> desc mytable;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID | int(11) | NO | | NULL | |
| username | varchar(16) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+

（2）唯一索引：它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式（仅仅在创建普通索引时关键字 INDEX 前加 UNIQUE

#创建唯一索引
mysql> create unique index indexName on mytable(username(10));
#修改唯一索引
mysql> alter table mytable add unique index indexName (username(10));
#删除唯一索引
mysql> drop index indexName on mytable;

（3）主键索引：它是一种特殊的唯一索引，不允许有空值。在建表的时候同时创建的主键即为主键索引。主键索引无需命名，一个表只能有一个主键。主键索引同时可是唯一索引或者全文索引，但唯一索引或全文索引不能共存在同一索引。
#通过修改表结构来创建主键索引
mysql> alter table mytable add primary key(ID);
mysql> desc mytable;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID | int(11) | NO | PRI | NULL | |
| username | varchar(16) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+

（4）全文索引：InnoDB存储引擎不支持全文索引
#创建全文索引
mysql> create fulltext index indexName on mytable(username(10));
#修改表结构来创建索引
mysql> alter table mytable add fulltext index indexName (username(10));
#删除全文索引
mysql> drop index indexName on mytable;

17）存储过程Procedure

mysql> call pro_test1;--执行存储过程

还可以在存储过程中使用自定义(用户)变量，简单实例演示：
mysql> create procedure pro_bigworld() select concat(@greeting,‘ world‘);
mysql> set @greeting=‘Hi‘;
mysql> call pro_bigworld;
+----------------------------+
| concat(@greeting,‘ world‘) |
+----------------------------+
| Hi world |
+----------------------------+

再看一个if-then-else语句：
delimiter ;
drop procedure if exists pro_test2;
delimiter //
create procedure pro_test2(IN para int)
begin
declare var1 int;
set var1=para+1;
if var1=0 then
insert into test1(name,value) values(‘var1name‘,‘var1value‘+0);
end if;
if para=0 then
update test1 set value=value+‘0‘;
else
update test1 set value=value+‘1‘;
end if;
end;
//
mysql> call pro_test2(1);
mysql> //
mysql> select * from test1;

关于Mysql存储过程中的控制语句比较多，这里限于篇幅就不一一列举了。

18）事务Transaction

事务几个基本特性: ACID
原子性(atomicity)：事务在执行时，要做到“要么不做，要么全做”，也就是说不允许事务部分得执行。即使因为故障而使事务不能完成，在rollback时也要消除对数据库得影响！
一致性(consistency)：在事务处理执行前后，数据库是一致的(两个账户要么都变，或者都不变)。
隔离性(isolcation)：一个事务处理对另一个事务处理没有影响。
持续性(durability)：事务处理的效果能够被永久保存下来。
MYSQL的事务处理主要有两种方法:
1、用begin,rollback,commit来实现
begin 开始一个事务
rollback 事务回滚
commit 事务确认
2、直接用set来改变mysql的自动提交模式
MYSQL默认是自动提交的，也就是你提交一个QUERY，它就直接执行！我们可以通过
select @@autocommit;//查看当前事务是否为自动提交(1为自动提交)
set autocommit=0 禁止自动提交
set autocommit=1 开启自动提交
来实现事务的处理。

【注意】当你用 set autocommit=0 的时候，你以后所有的SQL都将做为事务处理，直到你用commit确认或rollback结束，注意当你结束这个事务的同时也开启了个新的事务！按第一种方法只将当前的作为一个事务！

19）MySQL用户创建，授权以及删除

创建用户yy,密码123： create user yy identified by ‘123‘;
授权：grant all privileges on dbname.* to yy@localhost identified by ‘123‘;
授予用户yy查询数据库dbname中所有表的权限：grant select on dbname.* to yy;
撤销用户yy查询数据库dbname中所有表的权限：revoke select on dbname.* from yy;
删除用户：drop user yy;

20）select支持的运算操作

select case 1 when 1 then ‘one‘ when 2 then ‘two‘ end;/*选择事件1：推荐使用单引号表示字符串*/
select if(1<2,‘yes‘,‘no‘);/*判断语句*/
select char_length(‘qwert‘);/*求字符长度*/
select concat(‘my‘,‘sql‘);/*连接字符串*/
select locate(‘bar‘,‘foobarhat‘);/*查找母串‘bar‘所在位置,若未找到则为0*/
select substring(‘yichun university‘,5,6);/*提取字串*/
select trim(‘ bar ‘);;/*去掉前后空格*/
select repeat(‘mysql‘,3);/*mysql字符串重复3次*/
select reverse(‘abcd‘);/*反转*/
select round(2.5);/*四舍五入到3*/
select rand()*10;/*返回0~10之间的数*/
select hex(‘cat‘);/*查看ASCII码*/
select true,false;/*输出1,0*/
select now();/*显示当前日期时间*/
select curtime();/*显示当前时间*/
select current_time();/*显示当前时间*/
select curdate();/*显示当前日期*/
select current_date();/*显示当前日期*/
select current_timestamp();/*显示当前时间戳*/
select database();/*显示当前数据库*/
select user();/*显示当前用户*/
select version();/*显示当前mysql版本*/
select database();/*显示当前打开的数据库*/
select left(type_name,2) from test;/*查看test表中名为type_name列的所有值的前两个字母*/
select right(type_name,2) from test;/*查看test表中名为type_name列的所有值的后两个字母*/

21）select支持的逻辑运算操作

select 2>1&&3>2;/*逻辑与1*/
select 2>1 and 3>2;/*逻辑与1*/
select 1>2 || 3>2;/*逻辑或1*/
select 1>2 or 3>2;/*逻辑或1*/
select !(1>2);/*逻辑非1*/
select not(1>2);/*逻辑非1*/
select 15 ^ 2;/*逻辑异或：15的二进制1111，2的二进制0010，根据"同0异1"原则，结果为1101十进制13*/
select 15 xor 2;/*逻辑异或：13*/

附1：忘记密码

1.关闭正在运行的MySQL。
2.打开DOS窗口，转到mysql\bin目录。
3.输入mysqld --skip-grant-tables回车。如果没有出现提示信息，那就对了。
4.再开一个DOS窗口(因为刚才那个DOS窗口已经不能动了)，转到mysql\bin目录。
5.输入mysql回车，如果成功，将出现MySQL提示符 >,接下来就可以修改密码了。

附2：修改密码
方法一:
(适用于管理员或者有全局权限的用户重设其它用户的密码)
进入命令行模式
mysql> use mysql;
mysql> UPDATE user SET password="newPassword" WHERE user=‘name‘;
mysql> FLUSH PRIVILEGES; //刷新权限
mysql> QUIT

方法二:
(应用同上,只是方法不同)
mysql> SET PASSWORD FOR name=PASSWORD(‘new password‘);
mysql> QUIT