Chinaunix首页 | 论坛 | 博客
  • 博客访问: 198361
  • 博文数量: 58
  • 博客积分: 2763
  • 博客等级: 少校
  • 技术积分: 731
  • 用 户 组: 普通用户
  • 注册时间: 2008-09-04 19:07
文章分类

全部博文(58)

文章存档

2011年(55)

2009年(1)

2008年(2)

分类: LINUX

2011-09-02 20:31:57

 今天主要聊一聊有关表的一些操作。创建表,删除表,修改表,在表中插入数据,修改表中的数据,以及多表连接等。

表:由行和列组成
表空间:比表大,比数据库小的物理单位
 创建表:
            CREAT    TABLE [IF NOT EXISTS] tbl_name (create_definition,...)
            [table_options]
            [partition_options]
    每个表必须属于一个数据库
    如果在创建表时,如果没有指定数据库,在创建时就必须指定数据库。
    
mysql> CREAT TABLE user1(
    -> ID INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> Uname CHAR(20) NOT NULL,
    -> Uage INT(2) NOT NULL,
    -> U*** ENUM('F','M') DEFAULT 'M',
    -> UNIQUE(ID));
                
设定表将使用的引擎
       ENGINE=engin_name

在最后一行的括号外添加。例如:
mysql> CREAT TABLE user2(
    -> ID INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> Uname CHAR(20) NOT NULL,
    -> Uage INT(2) NOT NULL,
    -> U*** ENUM('F','M') DEFAULT 'M',
    -> UNIQUE(ID))
    -> ENGINE = InnoDB;

查看当前数据库中表的信息,包含所修改的存储引擎
      SHOW TABLE STATUS\G;
      SHOW TABLE STATUS like 'user2'\G;

              
       COLLATE:为表指定专门的排序规则
       AUTO_INCREMENT:自动加1,设置从1开始
                
-------------------------                
    CREAT    TABLE [IF NOT EXISTS] tbl_name (create_definition,...)
            [table_options]
            [partition_options]
            select_statement

 select_statemnet 表示从一个表中检索的数据创建一个表
  例如:     
mysql> create table user3 select Uname from user;    我这里的user表是在写这篇博客前已经创建过
                                                     了,所以直接拿来用了,在后面的过程中会有
                                                     user表的创建的,这里你可以换成别的表。
mysql> desc user3;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| Uname | char(20) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

以user表为准创建表,只负责创建表结构,不会复制表中的数据。
    create table user4 like user; 
   
        
例如:
mysql> desc user4;
  
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| ID       | int(10)     | NO   | PRI | NULL    | auto_increment |
| Uname    | char(20)    | NO   |     | NULL    |                |
| Uage     | int(2)      | NO   |     | NULL    |                |
| U***     | char(2)     | NO   |     | M       |                |
| Tutor    | varchar(20) | YES  |     | NULL    |                |
| CourseID | int(10)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)              
   
在哪些字段创建索引:
    PRIMARY KEY,INDEX,CONSTRAINT,FULLTEXT
索引类型:            
         BTREE索引
         HASH索引
    使用using指定索引类型


 创建索引:
    例如:CREATE TABLE IF NOT EXSITS users(
              ->  ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
              ->  Name VARCHAR(30) NOT NULL,
              ->  Age TINYINT UNSIGNED NOT NULL,
              ->  *** ENUM('F','M') NOT NULL DEFAULT 'M',
              ->  Score FLOAT );                
                
           CREATE TABLE IF NOT EXSITS users2(
              ->  ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
              ->  Name VARCHAR(30) NOT NULL,
              ->  Age TINYINT UNSIGNED NOT NULL)
              ->  *** ENUM('F','M') NOT NULL DEFAULT 'M',
              ->  Score FLOAT,
              ->  INDEX index_score(Score));
    
    SHOW INDEXES FROM users2;显示users2表的索引
    
    例:加一个约束,name上要求必须唯一
    CREATE TABLE IF NOT EXSITS users3(
            -> ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            -> Name VARCHAR(30) NOT NULL,
            -> Age TINYINT UNSIGNED NOT NULL)
            -> *** ENUM('F','M') NOT NULL DEFAULT 'M',
            -> Score FLOAT,
            -> INDEX index_score(Score),
            -> UNIQUE(Name));            
                
-------------------------------------------
 设置默认存储引擎
    1.在启动mysqld 时    
                --default-storage-engin = MYISAM
                #vim /etc/my.cnf
                
在[mysqld]范围内添加:--default-storage-engin = MYISAM
    2.SET SESSION storage_engin = InnoDB
      SET GLOABLE storage_engin = InnoDB

        
---------------------------------------------
查询:
    SELECT 字段 from 表名;    
    SELECT * from 表名;        
    where 条件
    条件包括: >,<,=,>=,<=,<>,!=,LIKE,BETWEEN .. AND ..,IN在特定集合中,IS NULL,IS NOT NULL,REGEXP(正则表达式)(RLIKE)
    匹配多个条件:AND(&&),OR(||),NOT(!)
    LIKE :
                % 任意长度任意字符
                _ 任意一个
        
             
        SELECT * FROM user WHERE ID IN(1,2,4);
        
        SELECT * FROM user WHERE ID+1 > 3;
        
    排序:ORDER by 条件 ASC|DESC    (默认是升序)
    
        SELECT * FROM user ORDER BY ID;              升序
        SELECT * FROM user ORDER BY ID DESC;         降序
    LIMIT n:表示显示前n行
        SELECT * FROM user LIMIT 2;
        SELECT * FROM user LIMIT 2,1;从第二行开始显示一行。
        
    消除重复值:DISTINCT(以下仅是例子,后面会提供相应的练习)
        >SELECT DISTINCT zone FROM zones LIMIT 100;
        >CREATE TABLE test SELECT * FROM zones LIMIT 100;
        >SELECT * FROM test;
        >SELECT DISTINCT FROM test;
        
    GROUP BY 把相同的分为一组,HAVING 对结果做过滤
      >SELECT * FROM mytb;
      >SELECT zone,COUNT(*) FROM mytb;
      >SELECT zone,COUNT(*) FROM mytb GROUP BY zone;
      >SELECT zone,COUNT(*) FROM mytb GROUP BY zone HAVING COUNT(*)>= 10;
      
    别名:AS
    SELECT zone AS domain FROM mytb;    
    
    设置变量;    
    >SET @num=10    
    >SELECT zone,COUNT(*) FROM mytb GROUP BY zone HAVING COUNT(*)>= @num;    
        
------------------------------------------------------------------       
        
 导入数据;
        create database mydb;
        use mydb;
        show tables;
        mysql > LOAD DATA INFILE '数据文件路径' INTO table 表名
    
    
mysql练习1: table.zip 
1.创建表结构:
        mysql> CREATE TABLE www (
    -> ID BIGINT(20) NOT NULL DEFAULT '0',
    -> UserID BIGINT(20) NOT NULL,
    -> ZoneID BIGINT(20) NOT NULL,
    -> Zone VARCHAR(255) NOT NULL,
    -> Host VARCHAR(255) NOT NULL,
    -> Type ENUM('A','SOA','NS','MX','CNAME','PTR','TXT','AAAA') NOT NULL,
    -> Mx_priority INT(10),
    -> Data VARCHAR(255) NOT NULL,
    -> TTL INT(10) NOT NULL DEFAULT '600',
    -> View VARCHAR(20) NOT NULL DEFAULT 'DEF',
    -> Active ENUM('Y','N') NOT NULL DEFAULT 'Y',
    -> DomainLevel INT(11) NOT NULL DEFAULT '1',
    -> Standby VARCHAR(255) NOT NULL,
    -> CheckHostID BIGINT(20) NOT NULL DEFAULT '0',
    -> IsFensheng TINYINT(1) NOT NULL DEFAULT '0');

2、将table.sql(我提供的附件中)中的数据导入至此表,将table.sql放到/tmp下。使用如下命令
    mysql> LOAD DATA INFILE '/tmp/table.sql' INTO TABLE mytb;

练习2:
1、显示www表中Host为www的所有行;
select * from www where Host='www';
2、显示www表中Zone、Host、Type和Data字段;
mysql> select Zone,Host,Type,Data from www;
3、将第2题中的Data字段显示时的Data改为显示Value;
mysql> select Zone,Host,Type,Data AS 'Value' from www LIMIT 100;
4、显示www表中Host为ftp的所有行的Zone、Host、Type和Data字段;
mysql> select Zone,Host,Type,Data from www where Host='ftp';
5、显示www表中Zone为以.org结尾或以.net结尾的所有行;
mysql> select * from www where Zone LIKE '%.org' or Zone LIKE '%.net' ;
6、显示www表中Host字段的值为mail,@,*或oa的所有行;
mysql> select * from www where Host IN ('mail','@','*','oa');
7、以Host字段为准对www表中的数据进行分组,并对分组后的结果进行行数统计;而后显示统计结果中行数大于10的组;
mysql> select Host,count(*) from www GROUP BY Host HAVING count(*) > 10;
8、显示www表中Mx_priority不为空的行,并对其结果以zone字段为准进行降序排序;
mysql> select * from www where Mx_priority  IS NOT NULL ORDER BY Zone DESC;
9、将第1题中的结果限定为只显示第50行后100行;
mysql> select * from www where Host='www' LIMIT 50,100;
10、显示www表中存在的所有域名;
mysql> select DISTINCT Zone from www;
11、显示www表中存在的所有记录类型;
mysql> select DISTINCT Type from www;
+-------+
| Type  |
+-------+
| A     |
| CNAME |
| MX    |
| TXT   |
+-------+


         
--------------------------------------------------

修改表结构:    
        ALTER TABLE tbl_name 操作
        操作:
        添加:ADD (默认添加字段)
              ADD col_name col_def 默认添加在最后
              AFTER col_name 在某一个字段后面添加
              FIRST 添加到第一个
mysql> CREAT TABLE user(
    -> UID INT(10) UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE,
    -> Uname CHAR(20) NOT NULL,
    -> Uage INT(2) NOT NULL,
    -> U*** CHAR(2),
    -> CourseID TINYINT);
mysql> DESC user;

添加:
ALTER TABLE user ADD Master TINYINT UNSIGNED ;
    
        修改:CHANGE (不仅可以改字段,还可以改名字),MODIFY(只改字段定义,不改字段名字)
              CHANGE old_name new_name col_defination
              MODIFY
mysql>  ALTER TABLE user MODIFY U*** ENUM('F','M') NOT NULL DEFAULT 'M';
mysql>  ALTER TABLE user CHANGE Master Tutor TINYINT UNSIGNED AFTER Uage;
        
        添加索引
        ADD {INDEX|KEY} [index_name] [index_type](col1[,...])
mysql>  ALTER TABLE user ADD INDEX (Uage);不指定索引类型,默认则为BTREE索引类型。    
        
        删除
        删除字段:
                    DROP col_name
        删除键:
                    DROP PRIMARY KEY
                    DROP {INDEX|KEY} index_name
                
                
        为表重命名
            ALTER TABLE user RENAME TO users;            
            RENAME TABLE users TO user;
            SHOW TABLES;
        
        
        
---------------
删除表
DROP TABLE [IF EXISTS] table_name        
        
        
往表中插入数据
  (1)INSERT INTO table_name (col1,col2,..) VALUES      (value1,value2,...)

mysql> INSTERT INTO user (Uname) VALUES ('Yang Guo');

  (2)INSERT INTO table_name SET col1=value1,col2=value2....
如果你把第二行删除,那么重新插入数据时,ID会从3开始,此时,可以修改LAST_INSERT_ID的值
                LAST_INSERT_ID:记录上一次插入行的ID号,
                ALTER TABLE user AUTO_INCREMENT=2;
          
  (3)如果不写字段名,那就得从头到尾为每个字段赋值
     INSSERT INTO table_name VALUES (val1,val2,val3,val4)
          
  (4)一个字段中插入多个值:
     INSERT INTO user (uname) VALUES ('Qiao Feng'),('Duan Yu'),('Xu Zhu'),('Hong xigong'),('lingxiaosu'),('xiaoling');
        
修改表中数据
                UPDATE table_name SET col1=val1[,...] [WHERE 条件];
                UPDATE user SET Uage=23;如果不指定条件,则所有的行的Uage都会被修改。
        
        UPDATE user SET Uage=23 LIMIT=2;只设置前两行
 
删除表中的数据
                DELETE FROM table_name WHERE 条件
                DELETE FROM user WHERE UID=5        
                清空一个表,并重置为初始状态,TRUNCATE TABLE tbl_name;
----------------------------------

多表查询:

连接:join
首先,创建两个表。user表之前已经创建过了,只需要再稍作修改即可。再创建一个juexue表。

mysql> ALTER TABLE user ADD Tutor VARCHAR(20);
mysql> ALTER TABLE user ADD CourseID INT(10);

mysql> UPDATE user SET CourseID=2 WHERE ID=1;
mysql> UPDATE user SET CourseID=4 WHERE ID=2;
mysql> UPDATE user SET CourseID=9 WHERE ID=3;
mysql> UPDATE user SET CourseID=10 WHERE ID=4;
Uage属性可以自己随意设置,方法和上面的语句类似。我这里就不演示了。
mysql> SELECT * FROM user;
+----+-------------+------+------+-------+----------+
| ID | Uname       | Uage | U*** | Tutor | CourseID |
+----+-------------+------+------+-------+----------+
|  1 | Yang Guo    |   25 | M    | NULL  |        2 |
|  2 | xiao feng   |   35 | M    | NULL  |        4 |
|  3 | Duan Yu     |   20 | M    | NULL  |        9 |
|  4 | Xu Zhu      |   22 | M    | NULL  |       10 |
|  5 | Hong xigong |   70 | M    | NULL  |     NULL |
|  6 | lingxiaosu  |    0 | M    | NULL  |     NULL |
|  7 | xiaoling    |    0 | M    | NULL  |     NULL |
+----+-------------+------+------+-------+----------+
mysql> CREATE TABLE juexue (
    -> CID TINYINT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> Cname VARCHAR(50) NOT NULL,
    -> Author TINYINT(4));
    
mysql> INSERT INTO juexue (Cname) Values ('qiankuangdanoyi'),('anranxiaohunzhang'),('tianweifeixian'),('xianglongshibazhang'),('hamagong'),('dagoubangfa'),('tiebushan'),('jinzhongzhao'),('liumaishenjian'),('beimingshengong');

mysql> UPDATE juexue SET Author=10 WHERE CID=1;
mysql> UPDATE juexue SET Author=1 WHERE CID=2;
mysql> UPDATE juexue SET Author=12 WHERE CID=3;
mysql> UPDATE juexue SET Author=2 WHERE CID=4;
mysql> UPDATE juexue SET Author=1 WHERE CID=5;
mysql> UPDATE juexue SET Author=14 WHERE CID=7;
mysql> UPDATE juexue SET Author=15 WHERE CID=8;
mysql> UPDATE juexue SET Author=3 WHERE CID=9;
mysql> UPDATE juexue SET Author=4 WHERE CID=10;

mysql> select * from juexue;
+-----+---------------------+--------+
| CID | Cname               | Author |
+-----+---------------------+--------+
|   1 | qiankuangdanoyi     |     10 |
|   2 | anranxiaohunzhang   |      1 |
|   3 | tianweifeixian      |     12 |
|   4 | xianglongshibazhang |      2 |
|   5 | hamagong            |      1 |
|   6 | dagoubangfa         |   NULL |
|   7 | tiebushan           |     14 |
|   8 | jinzhongzhao        |     15 |
|   9 | liumaishenjian      |      3 |
|  10 | beimingshengong     |      4 |
+-----+---------------------+--------+
10 rows in set (0.00 sec)



多表连接的类型
                    交叉连接(笛卡尔乘积)第一个表的第一行和第二个表的每一行连接,依此类推。因此会产生一个非常庞大的表。
                    内连接:对称连接,通常是等值条件
                    外连接
                                左外连接
                                右外连接
                                全外连接
                    自连接
                    UNION
(1)    交叉连接:    
                    SELECT * FROM user,juexue
                    
(2)内连接:(等值连接)当a表中的条件与b表中的条件匹配时,才会显示
                  
mysql> SELECT * FROM user,juexue WHERE user.CourseID = juexue.CID;
+----+-----------+------+------+-------+----------+-----+---------------------+--------+
| ID | Uname     | Uage | U*** | Tutor | CourseID | CID | Cname               | Author |
+----+-----------+------+------+-------+----------+-----+---------------------+--------+
|  1 | Yang Guo  |   25 |    2 | NULL  |        2 |   2 | anranxiaohunzhang   |      1 |
|  2 | xiao feng |   35 |    2 | NULL  |        4 |   4 | xianglongshibazhang |      2 |
|  3 | Duan Yu   |   20 |    2 | NULL  |        9 |   9 | liumaishenjian      |      3 |
|  4 | Xu Zhu    |   22 |    2 | NULL  |       10 |  10 | beimingshengong     |      4 |
+----+-----------+------+------+-------+----------+-----+---------------------+--------+
4 rows in set (0.00 sec)


mysql> SELECT user.Uname,juexue.Cname FROM user,juexue WHERE user.CourseID = juexue.CID;
+-----------+---------------------+
| Uname     | Cname               |
+-----------+---------------------+
| Yang Guo  | anranxiaohunzhang   |
| xiao feng | xianglongshibazhang |
| Duan Yu   | liumaishenjian      |
| Xu Zhu    | beimingshengong     |
+-----------+---------------------+
4 rows in set (0.00 sec)


mysql> SELECT Cname,Uname AS Author FROM juexue,user WHERE juexue.Author = user.ID;
+---------------------+-----------+
| Cname               | Author    |
+---------------------+-----------+
| anranxiaohunzhang   | Yang Guo  |
| xianglongshibazhang | xiao feng |
| hamagong            | Yang Guo  |
| liumaishenjian      | Duan Yu   |
| beimingshengong     | Xu Zhu    |
+---------------------+-----------+
5 rows in set (0.00 sec)
            
            
 (3)外连接(非对称)
   1)左外连接:左表中的用户选了某门课,但该门课在右表中不存在,则显示为空
mysql> SELECT user.Uname,juexue.Cname From user LEFT JOIN juexue ON user.CourseID = juexue.CID;
+-------------+---------------------+
| Uname       | Cname               |
+-------------+---------------------+
| Yang Guo    | anranxiaohunzhang   |
| xiao feng   | xianglongshibazhang |
| Duan Yu     | liumaishenjian      |
| Xu Zhu      | beimingshengong     |
| Hong xigong | NULL                |
| lingxiaosu  | NULL                |
| xiaoling    | NULL                |
+-------------+---------------------+
7 rows in set (0.24 sec)


     2)右外连接
mysql> SELECT j.Cname,u.Uname FROM user AS u RIGHT JOIN juexue AS j ON  j.Author = u.ID;
+---------------------+-----------+
| Cname               | Uname     |
+---------------------+-----------+
| qiankuangdanoyi     | NULL      |
| anranxiaohunzhang   | Yang Guo  |
| tianweifeixian      | NULL      |
| xianglongshibazhang | xiao feng |
| hamagong            | Yang Guo  |
| dagoubangfa         | NULL      |
| tiebushan           | NULL      |
| jinzhongzhao        | NULL      |
| liumaishenjian      | Duan Yu   |
| beimingshengong     | Xu Zhu    |
+---------------------+-----------+
10 rows in set (0.00 sec)

(4)自连接 ,简言之就是自己连接自己。        
mysql> UPDATE user SET Tutor=5 WHERE UID=1;
                        
mysql> SELECT u1.Uname,u2.Uname from user As u1,user AS u2 WHERE u1.Tutor = u2.ID;
+----------+-------------+
| Uname    | Uname       |
+----------+-------------+
| Yang Guo | Hong xigong |
+----------+-------------+
1 row in set (0.02 sec)
          
(5) UNION,将两张表的查询结果合成一张
                       
mysql> SELECT ID AS ID,Uname AS NAME FROM user UNION SELECT CID AS ID,CNAME AS NAME FROM juexue;
+----+---------------------+
| ID | NAME                |
+----+---------------------+
|  1 | Yang Guo            |
|  2 | xiao feng           |
|  3 | Duan Yu             |
|  4 | Xu Zhu              |
|  5 | Hong xigong         |
|  6 | lingxiaosu          |
|  7 | xiaoling            |
|  1 | qiankuangdanoyi     |
|  2 | anranxiaohunzhang   |
|  3 | tianweifeixian      |
|  4 | xianglongshibazhang |
|  5 | hamagong            |
|  6 | dagoubangfa         |
|  7 | tiebushan           |
|  8 | jinzhongzhao        |
|  9 | liumaishenjian      |
| 10 | beimingshengong     |
+----+---------------------+
17 rows in set (0.02 sec)        
    
        
        
        
        

        
        
        
        
        
        
        
        
        
        
        
        
        
        
     
阅读(1034) | 评论(1) | 转发(0) |
给主人留下些什么吧!~~

漫舞清风2011-09-02 22:12:53

这表建的俨然是要被推荐的样子啊-。-