MySQL快速入门02----数据库操作和表操作

于 2016-08-03 18:02:25 发布
阅读量1k 收藏 1
点赞数
分类专栏： MySQL 文章标签： 数据库 MySQL 表
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qianqin_2014/article/details/52105331
版权

一 数据库简介


数据库：对大量信息进行管理的高效解决方案，按照数据结构来组织、存储和管理数据的库

数据库系统（DBS,DATABASE SYSTEM）： 数据库（DB,DATABASE） + 数据库管理系统（DBMS,DATABASE MANAGEMENT SYSTEM)



1.1 关系型数据库系统


MySQL是一种关系型数据库系统，所谓关系型数据库系统，就是建立在关系模型上的数据库系统。

关系模型：当将日常生活中的 实体（学生），和 实体的属性（学生的学号，姓名）保存到数据中时，应该如何处理该实体结构。
  • 数据结构可以规定，同类数据，结构一致。就是一个二维的表格。
  • 数据之间的关系可以设置。实体之间的联系。


注：
  • 非关系型数据库NoSQL。Membase, MongoDB （类似与php的数组，通过下标标识，值任意格式）
  • 区别，通常关系数据库系统，要求具有相同的结构，而非关系型不一定要求。

1.2 关系型数据（RMDBS）的常用术语




  • 数据库（DataBase），仓库。
  • 表（table），数据库内的数据集合都放在表（二维表）内。
  • 行&列（row）&（column），数据表由数据行和数据列构成。
  • 记录（Record），一个数据行就是一个记录。
  • 字段（Field），记录内的每个列，就是一个字段。
  • 管理系统（management system）：用来执行增删改查等操作的软件。
  • 关系（relational），关系模型。
  • 结构化查询语言SQL（Structured query language)，关系数据库的操作语言，用于执行数据的检索和其他操作。

1.3 MySQL架构



MySQL采用的是 C/S （客户端/服务器端）架构：
  • 服务器端：存储和管理数据库的。
  • 客户端：发出操作请求的程序。
数据库（管理+数据） + 谁能向数据库系统发出如何管理的命令。

步骤：
  1. 连接，认证。
  2. 客户端发送操作指令到服务器端
  3. 服务器端处理请求指令
  4. 服务器端将处理结果返回给浏览器
  5. 客户端显示得到的结果

1.4 指令-SQL结构化查询语言






SQL=DDL，DML(DQL+DML)，DCL
  • 数据管理语言（DML，DataManipulationLanguage ）（DQL+DML）
  • 结构操作语言（数据定义语言，DDL，DataDefinitionLanguage ）
  • 数据库控制语言（数据库控制语言，DCL，DataBaseControlLanguage）

二 库操作


2.1 查询已经存在的数据库


show databases [like ‘pattern’];
like pattern 指的是显示符合哪些命名规则的。不存在指的是所有的数据库。（这里使用英文状态下的 单引号）。

注：
  • 数据库中的标识符大小写取决于操作系统（Windows下不区分大小写，Linux下是区分大小写的），为了兼容性更好，我们认为数据库系统中的标识符是区分大小写的；
  • 推荐使用下划线命名方式；
  • 标识符原则上可以使用任意字符，但是对于一些特殊的字符，用限定符来包裹起来，如纯数字、MySQL的内部关键字等等；
  • 限定符：反引号。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
结果表明：在当前状态下，总共有四个数据库。

2.2 创建数据库


Create database [if not exists] 数据库名 [数据库选项]
  • 数据库名：可以是任意字符（目录可以创建成功），但特殊的字符需要使用反引号包裹，中文名的存储方式时编码过的。标识符的大小写区别于操作系统的大小写特征；
  • If not exists 表示在数据库不存在时创建；
  • 数据库选项中，可以设定数据库字符集（character set utf8)和校对集(collate utf8_general_ci)；
  • 用中括号括起来的选项是可以不指名的。
你可以这样创建一个数据库 
mysql> create database student_1;
Query OK, 1 row affected (0.00 sec)

那么你也可以这样创建一个数据库 
mysql> create database if not exists student_2 character set gbk;
Query OK, 1 row affected (0.00 sec)

对于创建有特殊符号的数据库应该使用限定符，如： 
mysql> create database `1111`;
Query OK, 1 row affected (0.00 sec)

此时的总的数据库有： 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 1111               |
| mysql              |
| performance_schema |
| student_1          |
| student_2          |
| test               |
+--------------------+
7 rows in set (0.00 sec)

注意：其实，每一个数据库都在你的数据库存储目录中单独的被存储为一个工作目录，里边可以放多张表格，例如创建了前边两个数据库之后，在你的数据库存储目录中多出来两个文件夹，分别为student_1、student2和1111。


 2.3 查询创建数据库的语句

Show create database db_name;
可以查询数据库的一些属性。 

mysql> show create database `1111`;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| 1111     | CREATE DATABASE `1111` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
表明该1111数据库使用的字符集为utf8

2.4 更新数据库选项信息

alter database db_name 更新表选项

如更改1111数据库的字符集为gbk 
mysql> alter database `1111` character set gbk;
Query OK, 1 row affected (0.00 sec)

mysql> show create database `1111`;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| 1111     | CREATE DATABASE `1111` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
更改成功！

2.5 删除数据库

Drop database [if exists] db_name;
  • If exists 表示数据库存在才删除。
  • 当删除一个数据库时，同时删除该数据库相关的目录及其目录内容。
mysql> drop database `1111`;
Query OK, 0 rows affected (0.01 sec)
删除成功！ 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student_1          |
| student_2          |
| test               |
+--------------------+
6 rows in set (0.00 sec)

注意：删除表选项是不可逆的，当删除的时候一定要慎重处理，增加严格的筛选条件！

不可以直接修改数据库名（rename database 指令曾经出现过，但是被弃用了）


需要的做法是：
  • 创建新数据库，将原数据库内的表重命名到新数据库内，删除原数据库。
  • 将原数据库的数据和结构导出，然后在导入到新数据库内，删除原数据库

三 表操作


3.1 创建表

create table [if not exists] tbl_name （列定义） [表选项] 

create table [if not exists] tbl_name like old_tbl_name; 

create table [if not exists] tbl_name select 语句;
每当创建一个表，会在数据目录创建对应的文件保存表信息。

列定义：
  • 列名 类型 [是否为空] [Default 默认值] [是否为自动增长] [是否为主索引或唯一索引] [comment 注释] [引用定义]
  • 类型指的是 当前列所保存数据的类型。（简单的 int 整型，varchar字符串类型，需要指定最大长度）
  • 每个列定义使用逗号分隔。
表选项，常用的表选项
  • 表引擎：engine|type=引擎
  • 表字符集与校对集 charset set=字符集 collate=校对集
  • 注释 comment=‘注释’

mysql> create table if not exists student_class1(
    -> NO varchar(20),
    -> name varchar(20),
    -> score int
    -> );
Query OK, 0 rows affected (0.06 sec)
注意：最后一个列定义不能用逗号！

依次创建三个表。

3.2 查询表


show tables [from db_name] [like ‘pattern’];
查询数据库中存在的数据表，如果没有数据库名，则采用当前数据库，如果没有like则获得所有表。 

mysql> show tables from student_1;
+---------------------+
| Tables_in_student_1 |
+---------------------+
| student_class1      |
| student_class2      |
| student_class3      |
+---------------------+
3 rows in set (0.00 sec)
表明当前student_1数据库中有三个表存在。 

mysql> show tables from student_1 like '%1';
+--------------------------+
| Tables_in_student_1 (%1) |
+--------------------------+
| student_class1           |
+--------------------------+
1 row in set (0.00 sec)
其中%称之为通配符，表示任意字符的任意个数的组合。上方的代码表示筛选出最后一个字符是1的全部的表。 

mysql> show tables from student_1 like 'student%';
+--------------------------------+
| Tables_in_student_1 (student%) |
+--------------------------------+
| student_class1                 |
| student_class2                 |
| student_class3                 |
+--------------------------------+
3 rows in set (0.00 sec)
上述代码表明筛选出表名前缀是student的全部的表。


3.3 设置数据库为当前的默认数据库

mysql> use student_1;
Database changed
设置数据库student_1为当前默认的数据库。 

mysql> show tables;
+---------------------+
| Tables_in_student_1 |
+---------------------+
| student_class1      |
| student_class2      |
| student_class3      |
+---------------------+
3 rows in set (0.00 sec)
此时是从默认的数据库中选取表。

3.4 查看表结构

desc|describe tbl_name;(show columns from tbl_name);
查看当前表的结构。 

mysql> describe student_class1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| NO    | varchar(20) | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| score | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

查看当前表结构的另外一种写法： 
mysql> desc student_class1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| NO    | varchar(20) | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| score | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
即用desc代替describe

3.5 查看当前表的定义语句

show create table tbl_name;
查看当前表的定义语句 

mysql> show create table student_class1;
+----------------+------------------------------------------
-------------------------------------------------------+
| Table          | Create Table
                                                       |
+----------------+------------------------------------------
-------------------------------------------------------+
| student_class1 | CREATE TABLE `student_class1` (
  `NO` varchar(20) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------------+------------------------------------------
-------------------------------------------------------+
1 row in set (0.00 sec)

上述代码显得有些凌乱，可以在查看代码后边 加\G来优化图形化界面 
mysql> show create table student_class1 \G
*************************** 1. row ***************************
       Table: student_class1
Create Table: CREATE TABLE `student_class1` (
  `NO` varchar(20) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3.6 删除表

drop table [if exists] tbl_name;
注意，可以同时删除多个表名，表名之间使用逗号分割。
注意：如果不加if exists删除不存在的表将会出现错误！ 

mysql> drop table if exists student_class3, student_class4;
Query OK, 0 rows affected (0.05 sec)


mysql> show tables;
+---------------------+
| Tables_in_student_1 |
+---------------------+
| student_class1      |
| student_class2      |
+---------------------+
2 rows in set (0.00 sec)

3.7 表的重命名（数据库不能重命名）


rename table tbl_name to new_tbl_name;
可以同时针对多个表进行重命名，只需用逗号隔开，甚至可以跨数据库。 

mysql> rename table student_class1 to student_info;
Query OK, 0 rows affected (0.04 sec)
对一个表进行重命名； 

mysql> show tables;
+---------------------+
| Tables_in_student_1 |
+---------------------+
| student_class2      |
| student_info        |
+---------------------+
2 rows in set (0.00 sec)

mysql> rename table student_class2 to student_exam, student_info to info_student;
Query OK, 0 rows affected (0.04 sec)
对两个表同时进行重命名，只需用逗号隔开即可。

表student_2中是没有表的： 
mysql> show tables from student_2;
Empty set (0.00 sec)


mysql> rename table student_exam to student_2.exam_student;
Query OK, 0 rows affected (0.02 sec)
通过制定表的名称来实现跨数据库操作，这种行为类似于移动表，也就是将表从一个数据库中移动到另外一个数据库中。


3.8 修改列定义

alter table tbl_name add/modify/drop/change

3.8.1 增加一个新的列


Alter table tbl_name add column_definition。
可以同时增加多个列，使用括号括起来多个列的定义。

此时info_student中的信息列为： 
mysql> desc info_student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| NO    | varchar(20) | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| score | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

增加一个性别sex列： 
mysql> alter table info_student add sex varchar(10);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

显示新的表： 
mysql> desc info_student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| NO    | varchar(20) | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| score | int(11)     | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)


同时增加两列，分别为age和height 
mysql> alter table info_student add (age int, height int);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

显示新的表： 
mysql> desc info_student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| NO     | varchar(20) | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| score  | int(11)     | YES  |     | NULL    |       |
| sex    | varchar(10) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| height | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

3.8.2 删除列

alter table tbl_name drop column_name;
删除表中的一列数据 

alter table tbl_name drop column_name; 

<pre name="code" class="sql">mysql> alter table info_student drop age;
 删除表中两列数据，age列和height列 
      
 
      
 
       
 
      
 
      

        表信息： 
      
 
      
 
       
mysql> desc info_student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| NO    | varchar(20) | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| score | int(11)     | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
 
       
 
       
3.8.3 修改已有的列定义（修改数据类型或者容量）
 
      
 
      

        此时name中的容量为20，我们将他的容量更改为40； 
      
 
      
 
       
mysql> alter table info_student modify name varchar(40);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
       
 表信息： 
      
 
      
 
       
mysql> desc info_student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| NO    | varchar(20) | YES  |     | NULL    |       |
| name  | varchar(40) | YES  |     | NULL    |       |
| score | int(11)     | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
 
       
 
        
 
       
 
       
3.8.4 重命名一个列（同时更改列名和数据类型）
 更改表中的性别sex为height 类型为int： 
      
 
      
 
       
mysql> alter table info_student change sex height int;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
       
 表信息： 
      
 
      
 
       
mysql> desc info_student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| NO     | varchar(20) | YES  |     | NULL    |       |
| name   | varchar(40) | YES  |     | NULL    |       |
| score  | int(11)     | YES  |     | NULL    |       |
| height | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
 
       
 注：表中的信息太多，我们删掉最后一行； 
      
 
      
 
       
mysql> alter table info_student drop height;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

 
       
 
        
 
       
 表信息： 
      
 
      
 
       
mysql> desc info_student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| NO    | varchar(20) | YES  |     | NULL    |       |
| name  | varchar(40) | YES  |     | NULL    |       |
| score | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

 
       
 
       
3.9 修改表选项
 
       
 
        
alter table info_student character set gbk;
 
        
 
        
mysql> show create table info_student \G;
*************************** 1. row *******************
       Table: info_student
Create Table: CREATE TABLE `info_student` (
  `NO` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `name` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

 
        
 
        
3.10 对表中数据的操作（DML）
 
       
 
       
 
        
 
       
 
       
3.10.1 向表中增加数据:
 
       
 
        
 
       
 
       
 
        
insert into tbl_name (字段列表) values (值列表)

 
        

          注意：如果是为所有的字段都加入，则可以省略字段列表，但是值列表的顺序必须和表中的字段顺序一致。 
        
 
        
 
        
mysql> insert into info_student (NO, name, score) values('14011', 'Apple', 75);
Query OK, 1 row affected (0.02 sec)

mysql> insert into info_student values('14012', 'Lin', 85);
Query OK, 1 row affected (0.03 sec)

 
        
 
         
 
        
 
        
3.10.2 获得表中的数据
 
        
 
         
select  字段列表 from tbl_name 检索条件;

字段列表出位*表示获取所有的数据，检索条件为where 1时代表永远成立，和不写检索条件是一样的。 
         
 
         
 
        
 
       
 
       
 
        
mysql> select * from info_student where 1;
+-------+-------+-------+
| NO    | name  | score |
+-------+-------+-------+
| 14011 | Apple |    75 |
| 14012 | Lin   |    85 |
+-------+-------+-------+
2 rows in set (0.00 sec)
 
        
 
         
 
        
 
        
3.10.3 删除数据
 
        
 
         
Delete from tbl_name 删除条件;

 
         
 例如：删除分数低于80分的人员信息数据 
        
 
        
 
         
mysql> delete from info_student where score<=80;
Query OK, 1 row affected (0.03 sec)

 
         
 
          注意：删除表中的数据时一样要谨慎，因为这个过程是不可逆的！！！ 
         
 
         
 
          
 
         
 表中数据信息： 
        
 
        
 
         
mysql> select * from info_student;
+-------+------+-------+
| NO    | name | score |
+-------+------+-------+
| 14012 | Lin  |    85 |
+-------+------+-------+
1 row in set (0.00 sec)

 
         
 
         
3.10.4 修改数据
 
         
 
          
Update tbl_name set 字段=值 列表 更新条件

 
          
 
           
 
          
 如果表中人员的分数超过80分，则置为100分； 
         
 
         
 
          
mysql> update info_student set score=100 where score>=80;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from info_student;
+-------+------+-------+
| NO    | name | score |
+-------+------+-------+
| 14012 | Lin  |   100 |
+-------+------+-------+
1 row in set (0.00 sec)

 
          
 
          
 
          
总结:
 
          
 
           crud = create、read（retriere）、update、delete。增删改查！ 
          
 
          
 
         
 
         
 
          
 
         
 
         
 
        
 
        

 
 
        
 
        
 
       
 
       
 
        
 
       
 
       
 
      
 
      
 
       
 
       
 
       
 
      
 
      
 
       
 
      
 
      
 
     
 
     
 
    
 
    
 
     
 
    
 
    
 
    
 
    
 
    
 
    
 
    
 
    
 
   
 
   
 
    
 
   
 
   
 
    
 
    
 
   
 
   

 
 
   
 
    
 
   
 
   
 
    
 
   
 
   
 
   
 
  
 
  
 
   
 
  
 
  
 
   
 
   
 
  
 
  
 
   
 
  
 
  
 
   
 
  
 
  
 
   
 
  
 
  
 
 
 
 
 

 

 
 
 


                

        

        

    

  


    

      

        

            

              
                
                  烽火前秦路
                
              
            

            

                关注
              
            

        

        

          
      

      

            

                专栏目录
          









              

  



          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值