mysql应用管理

精选原创

艾贺 2015-09-23 16:46:04 博主文章分类：MySQL ©著作权

文章标签 linux mysql 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者艾贺的原创作品，请联系作者获取转载授权，否则将追究法律责任

这篇文章主要讲述基本的SQL语句，以供新手参考使用，不过最好的办法还是查阅官方文档和help命令。

进入正题

什么是SQL？

SQL结构化查询语言，是关系型数据库查询和管理语言，是一种数据库查询和程序设计语言，用于存取数据以及查询，更新和管理关系型数据库系统。

SQL的分类

1、DQL 数据查询语言，SELECT

2、DML数据操作语言,INSERT,UPDATE,DELETE

3、TPL(事务处理语言)被DML影响的表能够及时更新。TRSANCTION，COMMIT，

4、DCL,数据控制语言，GRANT，REVOKE，COMMIT，ROLLBACK

5、DDL数据定义语言，CREATE，DROP

6，CCL指针控制语言，一般都用不到的

最常用分类：DDL，DML，DCL。DBA，运维主要使用DDL，DCL

MySQL应用管理：

1、数据库

2、用户管理

3、数据类型

4、表

5、DML数据操作

6、索引

7、约束

8、总结

1、数据库：数据库是以一定方式存储在一起，能为多个用户共享，具有尽可能小的冗余度，与应用程序彼此独立的数据集合。平时生活中经常遇到，比如excel表格存放的某一文件夹，存储下来方便以后随时查阅。

mysql> show databases;         #显示数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.15 sec)
mysql> create database test;          #创建test数据库
Query OK, 1 row affected (0.04 sec)
 
mysql> create database test1 character set utf8;  #创建字符集为utf8的数据库
Query OK, 1 row affected (0.00 sec)

mysql> use mysql;                    #使用数据库
Database changed

mysql> alter database test default character set utf8;  #更改数据库的字符集
Query OK, 1 row affected (0.00 sec)
 
mysql> drop database test;                  #删除数据库
Query OK, 0 rows affected (0.05 sec)

2、用户管理

mysql> select user,host,password from user;   #查看用户
+--------+-----------+----------+
| user   | host      | password |
+--------+-----------+----------+
| root   | localhost |          |
| system | node1     |          |
| root   | 127.0.0.1 |          |
| root   | ::1       |          |
+--------+-----------+----------+
4 rows in set (0.01 sec)
mysql> create user 'system'@'192.168.198.%' identified by 'redhat';    #创建局域网内可以远程连接的用户
Query OK, 0 rows affected (0.00 sec)

mysql> delete from mysql.user where user='system';   #删除用户
Query OK, 2 rows affected (0.00 sec)

mysql> update mysql.user set user='system' where host='node1';   #更新用户名。
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> grant all on *.* to 'test'@'localhost';   #创建test用户并且授予所有权限。
Query OK, 0 rows affected (0.00 sec)
 
mysql> show grants for 'test'@'localhost';  #查看用户被授予的权限
+---------------------------------------------------+
| Grants for test@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' |  
+---------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> revoke insert on *.* from 'test'@'localhost';        #吊销用户的某些权限
Query OK, 0 rows affected (0.00 sec)
 
使用此命令查看。用户可以使用的所有权限:
[root@node1 ~]# mysql -S /tmp/mysql.sock3 -e "show grants for 'test'@'localhost';" | grep -i 'grant\>' | tr ',' '\n'   
GRANT SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

3、数据类型：数据类型是数据的一种属性，表示数据所表示信息的类型，在MySQL数据库里面可以使用的数据类型由日期和时间数据类型，字符数据类型和数值数据类型

3.1日期和时间型

3.2字符数据类型

3.3数值数据类型

参考：http://www.cnblogs.com/zbseoag/archive/2013/03/19/2970004.html mysql数据类型

4、表操作，表是数据库中用来存储数据的对象是具有结构数据的集合，是整个数据库系统的基础。就像excel表。在表上面有各种控制方式(约束，规则，默认值和数据类型)确保数据的完整性和有效性

mysql> show tables;    #查看数据库里面的表
+----------------+
| Tables_in_test |
+----------------+
| comment        |
| student        |
| student1       |
| test1          |
| test2          |
+----------------+
5 rows in set (0.00 sec)
mysql> create table test(id int(2),name char(20));  #创建表
Query OK, 0 rows affected (0.11 sec)
 
mysql> create table table2 like student;     #根据表student创建表table2，
Query OK, 0 rows affected (0.01 sec)

mysql> create table table5 select * from student;    #根据表student创建表table5，
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc student;  #查看表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   |     | NULL    |       |
| name  | char(20)    | NO   | PRI | NULL    |       |
| age   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student modify column name varchar(25);      #修改表中的name列。
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table student add column phone char(15);      #在表student中添加phone列
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table student drop column phone;     #删除student表中的phone列。             
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test rename to newtest;   #改变表的名字
Query OK, 0 rows affected (0.03 sec)
 
mysql> drop table newtest;                  #删除表
Query OK, 0 rows affected (0.01 sec)

5、DML数据操作

基本的DML数据操作可以分为4类，INSERT，SELECT，UPDATE，和DELETE。

mysql> desc test_DML;    #查看表结构
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   |     | NULL    |       |
| name  | char(15) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> insert into test_DML values(1,'yun zhonghe');   #插入单个数据
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into test_DML values(2,'yang guo'),(3,'huang yaoshi');  #批量插入数据
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> create table test_DML1 like test_DML;        #根据test_DML创建表test_DML1
Query OK, 0 rows affected (0.03 sec)
 
mysql> insert into test_DML1 select * from test_DML;#把表test_DML 里面的数据插入test_DML1
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select * from test_DML1;        #查询数据，不建议使用。
+----+--------------+
| id | name         |
+----+--------------+
|  1 | yun zhonghe  |
|  2 | yang guo     |
|  3 | huang yaoshi |
+----+--------------+
 
mysql> select id from test_DML1;  #只查询id相关的列
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)
 
mysql> select id,name from test_DML1 where id>2;    #查询表中的id，和name同时满足id>2
+----+--------------+
| id | name         |
+----+--------------+
|  3 | huang yaoshi |
+----+--------------+
1 row in set (0.00 sec)
3 rows in set (0.00 sec)
 
mysql> update test_DML set id=5 where name='huang yaoshi';   #更新huang yaoshi 的id为5
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> delete from test_DML where id=2;   #删除id为2的数据
Query OK, 1 row affected (0.02 sec)
 
mysql> select id,name from test_DML;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | yun zhonghe  |
|  5 | huang yaoshi |
+----+--------------+
2 rows in set (0.00 sec)

6、索引

索引就像书的目录一样，如果再字段上建立了索引，那么索引列为查询条件时可以加快查询数据的速度，这是mysql优化的重要内容之一。

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   |     | NULL    |       |
| name  | varchar(25) | YES  |     | NULL    |       |
| age   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql> create index index_name on student(name); #在student表name字段建索引index_name
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> create unique index index_id_name  on student(id);  #创建唯一索引index_id在id上
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   | PRI | NULL    |       |
| name  | varchar(25) | YES  | MUL | NULL    |       |
| age   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql> alter table student add index index_age_name(age);   #使用alter建立索引
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> drop index index_age_name on student;   #删除索引index_age_name
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show index from student\G       #查看索引
*************************** 1. row ***************************
        Table: student     #表名
   Non_unique: 0          #s如果索引不能包含重复数据为0，如果可以则为1
     Key_name: index_id_name   #索引名称
 Seq_in_index: 1         #索引的列序列号
  Column_name: id          #列名称 
    Collation: A    #列以什么方式存储在索引中，A表示升序，NULL表示无分类
  Cardinality: 0     #
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE   #索引类型，可以是BTREE，FULLTEXT，HASH或者RTREE
      Comment:        #注释
Index_comment:
*************************** 2. row ***************************
        Table: student
   Non_unique: 1
     Key_name: index_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)
 
mysql> alter table student drop index index_name;   #使用alter删除索引。
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

7、完整性约束

数据的完整性是保证数据的正确性和一致性，可以通过在创建或修改表时候定义完整性约束。完整性约束分为行级别和表级两类，处理机制是一样的，行约束放在行后，表约束放在表之后。完整性约束是一种规则，不占用任何数据库的空间。

在MySQL数据库里面，常见的约束类型有NOT NULL，主键，唯一键和外键。

7.1NOT NULL 在某列上不允许输入空值，只能约束在列级。

7.2PRIMARY KEY主键，某一列或者多个列的组合的取值必须是唯一的，不能有重复数据，也不能存在空值，只能在表上创建一个主键，为表创建主键的同时，也会自动创建与主键同名的索引。

mysql> create table Const (id int primary key,name varchar(20) not null);     #id上创建主键，name非空约束
Query OK, 0 rows affected (0.04 sec)
 
mysql> alter table Const drop primary key;  #删除主键
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table Const add primary key(id);   #增加主键。
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

7.3UNIQUE，唯一键，用于指定列表上某一列或者多个列的组合取值必须是唯一的。不能有重复数据。

创建方式和创建主键类似，只需指定键类型。

7.4 FOREIGN KEY。外键指定一个表中的数据和另外一个表中的数据之间的关系，在表上创建外键之前必须先在另外一个表上创建主键，拥有主键的表称为主键表。拥有外键的表称为外键表。在外键列上的数据只能是主键列上已经存在的数据或者是空值，如果再外键列上插入或更新的数据在主键列上不存在，则无法操作。

如果要删除(ON DELETE)或者更新(ON UPDATE)主键列上的数据的同时，可以通过以下动作影响外键列上的数据。

RESTRICT ：禁止删除主键列上面的数据

NO ACTION：在检查约束的同时，检查约束的同时，如果还存在任何音乐行，则显示错误信息，如果不声明任何内容，那么他就是默认的行为。

CASCADE：删除主键上的数据的同时，外键列上的数据也会自动删除

SET NULL：删除主键的数据的时候，外键的数据设置为空

SET DEFAULT：删除主键列上数据的同时，外键列上的数据设置为默认值。

mysql> create table table1 (id int(2) primary key,name char(20));  
Query OK, 0 rows affected (0.02 sec)
 
mysql> create table table2(id int(2),sales int);
Query OK, 0 rows affected (0.08 sec)
 
mysql> alter table table2 add constraint fk_table2_id foreign key(id) references table1(id);  #创建外键。
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table table2 drop foreign key fk_table2_id;  #删除外键
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table table2 drop column id;   #删除id列。
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table table2 add column id int(2);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table table2 add constraint fk_table2_id foreign key(id) references table1(id) on delete CASCADE;  #删除外键列上数据的同时，也会删除主键上的数据。
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

到这里算是结束mysql的基础管理了。

8、总结

1、这篇文章主要是面向刚学习mysql的新手，所以才举了这么多的实例，而这些实例比较占用篇幅，所以写了这么多。

2、最好的学习方式还是查阅官方文档，这里也只是我最近的学习总结，方便后来查阅使用。尽量布局好看一些。

3、文章还有很多不足的地方，但愿不影响阅读

参考：

linux应用大全-服务器架设

http://www.cnblogs.com/zbseoag/archive/2013/03/19/2970004.html mysql数据类型

http://www.bkjia.com/Mysql/995562.html 详细解读mysql权限