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

转载请注明出处：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身份查看的）

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| incubator          |
| movie              |
| mysql              |
| mytest             |
| performance_schema |
| product            |
+--------------------+

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

mysql> use product;
Database changed

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

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

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

mysql> desc test;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| Id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| type_name | varchar(10) | YES  |     | NULL    |                |
| color_id  | varchar(10) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

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表结构

#查看当前表结构
mysql> desc mytest;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| Id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| type_name  | varchar(10) | YES  |     | NULL    |                |
| color_name | varchar(10) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

#修改列
mysql> alter table mytest change color_name description varchar(50);
mysql> desc mytest;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| Id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| type_name   | varchar(10) | YES  |     | NULL    |                |
| description | varchar(50) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

#增加列
mysql> alter table mytest add extra_name varchar(20) not null default '豆豆';
mysql> desc mytest;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| Id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| type_name   | varchar(10) | YES  |     | NULL    |                |
| description | varchar(50) | YES  |     | NULL    |                |
| extra_name  | varchar(20) | NO   |     | 豆豆    |                |
+-------------+-------------+------+-----+---------+----------------+

#删除列
mysql> alter table mytest drop extra_name;
mysql> desc mytest;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| Id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| type_name   | varchar(10) | YES  |     | NULL    |                |
| description | varchar(50) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

#修改表名
mysql> alter table mytest rename to test01;
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| test01         |
+----------------+
mysql> desc test01;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| Id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| type_name   | varchar(10) | YES  |     | NULL    |                |
| description | varchar(50) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

#修改指定列名
mysql> alter table test01 modify column type_name char(10);
mysql> desc test01;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| Id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| type_name   | char(10)    | YES  |     | NULL    |                |
| description | varchar(50) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

#查看test01表中以t开头的字段信息。
mysql> desc test01 't%';
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| type_name | char(10) | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+

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

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

#查看当前数据库的编码方式
mysql> show variables like 'character%';
+--------------------------+-----------------------------------------+
| Variable_name            | Value                                   |
+--------------------------+-----------------------------------------+
| character_set_client     | gbk                                     |
| character_set_connection | gbk                                     |
| character_set_database   | utf8                                    |
| character_set_filesystem | binary                                  |
| character_set_results    | gbk                                     |
| character_set_server     | latin1                                  |
| character_set_system     | utf8                                    |
| character_sets_dir       | D:\MySQL\MySQLServer5.6\share\charsets\ |
+--------------------------+-----------------------------------------+

#修改编码方式
mysql> set names 'utf8';
mysql> show variables like 'character%';
+--------------------------+-----------------------------------------+
| Variable_name            | Value                                   |
+--------------------------+-----------------------------------------+
| character_set_client     | utf8                                    |
| character_set_connection | utf8                                    |
| character_set_database   | utf8                                    |
| character_set_filesystem | binary                                  |
| character_set_results    | utf8                                    |
| character_set_server     | latin1                                  |
| character_set_system     | utf8                                    |
| character_sets_dir       | D:\MySQL\MySQLServer5.6\share\charsets\ |
+--------------------------+-----------------------------------------+

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

存储过程：是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。MySQL存储过程创建的格式：create procedure 过程名 ([过程参数[,...]]) [特性 ...] 过程体。比如要往表test1中插入10000条数据，此时用普通的插入语句显然不太现实，这时可以考虑存储过程，实例如下：
mysql> desc test1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| Id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| value | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
#删除存储过程
mysql> drop procedure if exists pro_test1;
#创建存储过程
mysql> delimiter //
mysql> create procedure pro_test1()
    -> begin
    ->   declare i int default 1;
    -> while i < 10000 DO
    -> insert into test1(name,value) value('name'+i,'value'+i);
    -> set i = i + 1;
    -> end while;
    -> end;
    -> //

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