mysql 学习记录（九）--索引、视图

最新推荐文章于 2022-09-19 18:21:07 发布
最新推荐文章于 2022-09-19 18:21:07 发布
阅读量1.2k 收藏 3
点赞数
分类专栏： mysql mysql学习 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/bwshqh/article/details/48919201
版权
一   理论：
1) 设计索引的原则：
1.搜索的索引列是出现在where子句或者连接子句中的列。
2.使用唯一索引时，索引的列基数越大效果越好。
3.使用短索引涉及的IO更好，比较更快。在索引缓存中的块能容纳更多的键值。
4.最多前缀：即在创建一个n列的索引时，实际是创建了mysql的N个索引，可利用索引中最左边的列来匹配行。
5.过度索引可能会使mysql选择不到要使用的最好索引。
6.在innodb存储引擎的表的保存顺序是：主键、唯一索引、内部列。
2) 视图的优势：
1.简单：面对用户的是过滤完成的结果集。
2.安全：对视图的权限控制可以限制到行与列。
3.数据独立：源表增加与修改列名可与视图无影响。
 

二   sql记录：

1) 使用hash与b-tree索引
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql>  CREATE TABLE city_memory(
    ->   `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    ->   `city` varchar(50) NOT NULL,
    ->   `country_id` smallint(5) unsigned NOT NULL,
    ->   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`city_id`),
    ->   KEY `idx_fk_country_id` (`country_id`),
    ->   CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
    -> ) ENGINE=memory AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from city;
+---------+------+------------+---------------------+
| city_id | city | country_id | last_update         |
+---------+------+------------+---------------------+
|     251 | bill |      10000 | 2015-10-02 20:48:51 |
+---------+------+------------+---------------------+
1 row in set (0.00 sec)

mysql> explain 
    -> select * from city 
    -> where country_id > 9 and country_id<23186  \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
         type: range
possible_keys: idx_fk_country_id
          key: idx_fk_country_id
      key_len: 2
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql> insert into city_memory select * from city;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> explain  select * from city_memory  where country_id > 9 and country_id<23186\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city_memory
         type: system
possible_keys: idx_fk_country_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: 
1 row in set (0.00 sec)

2)使用包含join,union,group,having,count等的视图
mysql> CREATE TABLE `payment` (
    ->   `id` smallint(6) DEFAULT NULL,
    ->   `staff_id` smallint(6) DEFAULT NULL,
    ->   `amount` decimal(15,2) DEFAULT NULL,
    ->   KEY `id` (`id`)
    -> ) ENGINE=innodb AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into payment(staff_id,amount) values
    -> (1,3.2),
    -> (1,5.8),
    -> (2,3.5),
    -> (1,9.3),
    -> (2,9.1);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> create or replace view payment_sum as
    -> select staff_id ,sum(amount) from payment group by staff_id;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from payment_sum;
+----------+-------------+
| staff_id | sum(amount) |
+----------+-------------+
|        1 |       18.30 |
|        2 |       12.60 |
+----------+-------------+
2 rows in set (0.01 sec)

mysql> insert into payment(staff_id,amount) values 
    -> (1,105.71038);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from payment_sum;
+----------+-------------+
| staff_id | sum(amount) |
+----------+-------------+
|        1 |      124.01 |
|        2 |       12.60 |
+----------+-------------+
2 rows in set (0.00 sec)

mysql> drop view payment_sum;
Query OK, 0 rows affected (0.00 sec)

mysql> create or replace view payment_sum as
    -> select * from payment having sum(amount) > 10;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from payment_sum;
+------+----------+--------+
| id   | staff_id | amount |
+------+----------+--------+
| NULL |        1 |   3.20 |
+------+----------+--------+
1 row in set (0.00 sec)

mysql> insert into payment_test( id,staff_id,amount)  values 
    -> (1,1,3.2),
    -> (2,2,9.3),
    -> (3,1,1.83512935),
    -> (4,1,2.283915),
    -> (5,2,3.283852),
    -> (6,2,1.8);
Query OK, 6 rows affected, 3 warnings (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 3

mysql> drop view payment_sum ;
Query OK, 0 rows affected (0.01 sec)

mysql> create or replace view payment_sum as
    -> select * from payment group by id having sum(amount) > 3;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from payment_sum;
+------+----------+--------+
| id   | staff_id | amount |
+------+----------+--------+
|    1 |        1 |   3.20 |
|    2 |        2 |   9.30 |
|    5 |        2 |   3.28 |
+------+----------+--------+
3 rows in set (0.01 sec)

mysql> insert into payment(id,staff_id,amount) values ( 9,1,3.185), (19,1,9.91852);
Query OK, 2 rows affected, 2 warnings (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> select * from payment_sum;
+------+----------+--------+
| id   | staff_id | amount |
+------+----------+--------+
|    1 |        1 |   3.20 |
|    2 |        2 |   9.30 |
|    5 |        2 |   3.28 |
|    9 |        1 |   3.19 |
|   19 |        1 |   9.92 |
+------+----------+--------+
5 rows in set (0.00 sec)

mysql> update payment set amount = 20.185 where id = 5;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from payment_sum;
+------+----------+--------+
| id   | staff_id | amount |
+------+----------+--------+
|    1 |        1 |   3.20 |
|    2 |        2 |   9.30 |
|    5 |        2 |  20.19 |
|    9 |        1 |   3.19 |
|   19 |        1 |   9.92 |
+------+----------+--------+
5 rows in set (0.00 sec)

mysql> drop view payment_sum;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `payment_test` (
    ->   `id` smallint(6) DEFAULT NULL,
    ->   `staff_id` smallint(6) DEFAULT NULL,
    ->   `amount` decimal(15,2) DEFAULT NULL,
    ->   KEY `id` (`id`)
    -> ) ENGINE=innodb AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into payment_test( id,staff_id,amount)  values 
    -> (1,1,13.2),
    -> (2,2,91.3),
    -> (3,1,13.83512935),
    -> (4,1,25.283915),
    -> (5,2,28.283852),
    -> (6,2,13.8);
Query OK, 6 rows affected, 3 warnings (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 3

mysql> ( select * from payment ) 
    -> union 
    -> ( select * from payment_test);
+------+----------+--------+
| id   | staff_id | amount |
+------+----------+--------+
|    1 |        1 |   3.20 |
|    2 |        2 |   9.30 |
|    3 |        1 |   1.84 |
|    4 |        1 |   2.28 |
|    5 |        2 |  20.19 |
|    6 |        2 |   1.80 |
|    9 |        1 |   3.19 |
|   19 |        1 |   9.92 |
|    1 |        1 |  13.20 |
|    2 |        2 |  91.30 |
|    3 |        1 |  13.84 |
|    4 |        1 |  25.28 |
|    5 |        2 |  28.28 |
|    6 |        2 |  13.80 |
+------+----------+--------+
14 rows in set (0.00 sec)

mysql> create or replace view payment_sum as
    -> ( select * from payment ) 
    -> union 
    -> ( select * from payment_test) 
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from payment_sum;
+------+----------+--------+
| id   | staff_id | amount |
+------+----------+--------+
|    1 |        1 |   3.20 |
|    2 |        2 |   9.30 |
|    3 |        1 |   1.84 |
|    4 |        1 |   2.28 |
|    5 |        2 |  20.19 |
|    6 |        2 |   1.80 |
|    9 |        1 |   3.19 |
|   19 |        1 |   9.92 |
|    1 |        1 |  13.20 |
|    2 |        2 |  91.30 |
|    3 |        1 |  13.84 |
|    4 |        1 |  25.28 |
|    5 |        2 |  28.28 |
|    6 |        2 |  13.80 |
+------+----------+--------+
14 rows in set (0.00 sec)

mysql> update payment_test set amount = 113.3333
    ->  where id = 6;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from payment_sum;
+------+----------+--------+
| id   | staff_id | amount |
+------+----------+--------+
|    1 |        1 |   3.20 |
|    2 |        2 |   9.30 |
|    3 |        1 |   1.84 |
|    4 |        1 |   2.28 |
|    5 |        2 |  20.19 |
|    6 |        2 |   1.80 |
|    9 |        1 |   3.19 |
|   19 |        1 |   9.92 |
|    1 |        1 |  13.20 |
|    2 |        2 |  91.30 |
|    3 |        1 |  13.84 |
|    4 |        1 |  25.28 |
|    5 |        2 |  28.28 |
|    6 |        2 | 113.33 |
+------+----------+--------+
14 rows in set (0.00 sec)

mysql> alter table payment_test add column payment_id int (11);
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from payment_test;
+------+----------+--------+------------+
| id   | staff_id | amount | payment_id |
+------+----------+--------+------------+
|    1 |        1 |  13.20 |       NULL |
|    2 |        2 |  91.30 |       NULL |
|    3 |        1 |  13.84 |       NULL |
|    4 |        1 |  25.28 |       NULL |
|    5 |        2 |  28.28 |       NULL |
|    6 |        2 | 113.33 |       NULL |
+------+----------+--------+------------+
6 rows in set (0.00 sec)

mysql> update payment_test set payment_id = 1 where id = 1 ; 
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update payment_test set payment_id = 2 where id = 2 ; 
= 6 where id = 6 ; 
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update payment_test set payment_id = 3 where id = 3 ; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update payment_test set payment_id = 4 where id = 4 ; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update payment_test set payment_id = 5 where id = 5 ; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update payment_test set payment_id = 6 where id = 6 ; 
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into payment_test(id,payment_id) values (7,20);
Query OK, 1 row affected (0.02 sec)

mysql> select * from payment_test;
+------+----------+--------+------------+
| id   | staff_id | amount | payment_id |
+------+----------+--------+------------+
|    1 |        1 |  13.20 |          1 |
|    2 |        2 |  91.30 |          2 |
|    3 |        1 |  13.84 |          3 |
|    4 |        1 |  25.28 |          4 |
|    5 |        2 |  28.28 |          5 |
|    6 |        2 | 113.33 |          6 |
|    7 |     NULL |   NULL |         20 |
+------+----------+--------+------------+
7 rows in set (0.00 sec)

mysql> update payment_test set payment_id = 3 where id = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from payment_test;
+------+----------+--------+------------+
| id   | staff_id | amount | payment_id |
+------+----------+--------+------------+
|    1 |        1 |  13.20 |          1 |
|    2 |        2 |  91.30 |          2 |
|    3 |        1 |  13.84 |          3 |
|    4 |        1 |  25.28 |          4 |
|    5 |        2 |  28.28 |          3 |
|    6 |        2 | 113.33 |          6 |
|    7 |     NULL |   NULL |         20 |
+------+----------+--------+------------+
7 rows in set (0.00 sec)

mysql> update payment_test set payment_id = 98 where id = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from payment_test;
+------+----------+--------+------------+
| id   | staff_id | amount | payment_id |
+------+----------+--------+------------+
|    1 |        1 |  13.20 |          1 |
|    2 |        2 |  91.30 |          2 |
|    3 |        1 |  13.84 |          3 |
|    4 |        1 |  25.28 |          4 |
|    5 |        2 |  28.28 |         98 |
|    6 |        2 | 113.33 |          6 |
|    7 |     NULL |   NULL |         20 |
+------+----------+--------+------------+
7 rows in set (0.00 sec)

mysql> select * from payment;
+------+----------+--------+
| id   | staff_id | amount |
+------+----------+--------+
|    1 |        1 |   3.20 |
|    2 |        2 |   9.30 |
|    3 |        1 |   1.84 |
|    4 |        1 |   2.28 |
|    5 |        2 |  20.19 |
|    6 |        2 |   1.80 |
|    9 |        1 |   3.19 |
|   19 |        1 |   9.92 |
+------+----------+--------+
8 rows in set (0.00 sec)

mysql> drop view payment_sum;
Query OK, 0 rows affected (0.01 sec)

mysql> show table status where comment='view' ;
Empty set (0.05 sec)

mysql> create or replace view payment_sum as
    -> select p.id,p.staff_id,p.amount from payment p 
    -> left join payment_test pt 
    -> on p.id = pt.payment_id;
Query OK, 0 rows affected (0.02 sec)

mysql> select *  from payment_sum;
+------+----------+--------+
| id   | staff_id | amount |
+------+----------+--------+
|    1 |        1 |   3.20 |
|    2 |        2 |   9.30 |
|    3 |        1 |   1.84 |
|    4 |        1 |   2.28 |
|    5 |        2 |  20.19 |
|    6 |        2 |   1.80 |
|    9 |        1 |   3.19 |
|   19 |        1 |   9.92 |
+------+----------+--------+
8 rows in set (0.01 sec)

mysql> update payment set amount = 333.333333 where id = 1;
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select *  from payment_sum;
+------+----------+--------+
| id   | staff_id | amount |
+------+----------+--------+
|    1 |        1 | 333.33 |
|    2 |        2 |   9.30 |
|    3 |        1 |   1.84 |
|    4 |        1 |   2.28 |
|    5 |        2 |  20.19 |
|    6 |        2 |   1.80 |
|    9 |        1 |   3.19 |
|   19 |        1 |   9.92 |
+------+----------+--------+
8 rows in set (0.00 sec)

mysql> show tables;
+--------------------+
| Tables_in_test1    |
+--------------------+
| ai                 |
| ai1                |
| ai2                |
| ai3                |
| autoincre_demo     |
| autoincre_demo_old |
| city               |
| city_memory        |
| country            |
| country_old        |
| myisam_char        |
| payment            |
| payment_2006       |
| payment_2007       |
| payment_all        |
| payment_sum        |
| payment_test       |
| salary             |
| t                  |
| t1                 |
| t1_test            |
| t2                 |
| t6                 |
| t8                 |
| t_binary           |
| t_date             |
| t_enum             |
| t_index            |
| t_old              |
| t_oldtable         |
| t_optimize         |
| t_test             |
| tab_memory         |
| test               |
| vc                 |
| vc_old             |
+--------------------+
36 rows in set (0.00 sec)

mysql> drop view payment_sum;
Query OK, 0 rows affected (0.01 sec)

mysql> show table status where comment='view'  ;
Empty set (0.05 sec)

mysql> create or replace view payment_sum as
    -> select p.id,p.staff_id,p.amount from payment p 
    -> left join payment_test pt 
    -> on p.id = pt.payment_id;
Query OK, 0 rows affected (0.02 sec)

mysql> select *  from payment_sum;
+------+----------+--------+
| id   | staff_id | amount |
+------+----------+--------+
|    1 |        1 |   3.20 |
|    2 |        2 |   9.30 |
|    3 |        1 |   1.84 |
|    4 |        1 |   2.28 |
|    5 |        2 |  20.19 |
|    6 |        2 |   1.80 |
|    9 |        1 |   3.19 |
|   19 |        1 |   9.92 |
+------+----------+--------+
8 rows in set (0.01 sec)

mysql> update payment set amount = 333.333333 where id = 1;
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select *  from payment_sum;
+------+----------+--------+
| id   | staff_id | amount |
+------+----------+--------+
|    1 |        1 | 333.33 |
|    2 |        2 |   9.30 |
|    3 |        1 |   1.84 |
|    4 |        1 |   2.28 |
|    5 |        2 |  20.19 |
|    6 |        2 |   1.80 |
|    9 |        1 |   3.19 |
|   19 |        1 |   9.92 |
+------+----------+--------+
8 rows in set (0.00 sec)

mysql> drop view payment_sum;
Query OK, 0 rows affected (0.01 sec)

mysql> select id from payment where id = 5 
+------+
| id   |
+------+
|    5 |
+------+
1 row in set (0.01 sec)

mysql>  select * from payment where id = (
    ->  select id from payment_test where staff_id = 2 limit 1 );
+------+----------+--------+
| id   | staff_id | amount |
+------+----------+--------+
|    2 |        2 |   9.30 |
+------+----------+--------+
1 row in set (0.01 sec)

mysql> select  * from payment_test where staff_id = 2 limit 1;
+------+----------+--------+------------+
| id   | staff_id | amount | payment_id |
+------+----------+--------+------------+
|    2 |        2 |  91.30 |          2 |
+------+----------+--------+------------+
1 row in set (0.00 sec)

mysql> create or replace view payment_sum as
    ->  select * from payment where id = (
    ->  select id from payment_test where staff_id = 2 limit 1 ) ;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from payment_sum;
+------+----------+--------+
| id   | staff_id | amount |
+------+----------+--------+
|    2 |        2 |   9.30 |
+------+----------+--------+
1 row in set (0.00 sec)

mysql> update payment set amount = 99.99999 where staff_id = 2;
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 3

mysql> select * from payment_sum;
+------+----------+--------+
| id   | staff_id | amount |
+------+----------+--------+
|    2 |        2 | 100.00 |
+------+----------+--------+
1 row in set (0.00 sec)

mysql> select *  from payment;
+------+----------+--------+
| id   | staff_id | amount |
+------+----------+--------+
|    1 |        1 | 333.33 |
|    2 |        2 | 100.00 |
|    3 |        1 |   1.84 |
|    4 |        1 |   2.28 |
|    5 |        2 | 100.00 |
|    6 |        2 | 100.00 |
|    9 |        1 |   3.19 |
|   19 |        1 |   9.92 |
+------+----------+--------+
8 rows in set (0.01 sec)

mysql> select id ,amount from payment where amount<10 ;
+------+--------+
| id   | amount |
+------+--------+
|    3 |   1.84 |
|    4 |   2.28 |
|    9 |   3.19 |
|   19 |   9.92 |
+------+--------+
4 rows in set (0.00 sec)

3.嵌套视图
备注：此处有些问题，没能按‘要求’更新
mysql> create or replace view payment_view as 
    -> select id ,amount from payment where amount<10 with check option;
Query OK, 0 rows affected (0.03 sec)

mysql> create or replace view payment_view1 as 
    -> select id ,amount from payment_view 
    -> where amount>5 with local check option;
Query OK, 0 rows affected (0.01 sec)

mysql> create or replace view payment_view2 as 
    -> select id ,amount from payment_view 
    -> where amount>5 with cascaded check option;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from payment_view1 limit 1;
+------+--------+
| id   | amount |
+------+--------+
|   19 |   9.92 |
+------+--------+
1 row in set (0.01 sec)

mysql> update payment_view1 set amount = 10 where id = 19;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update payment_view2 set amount = 10 where id = 19;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select *from payment_view1;
Empty set (0.00 sec)

mysql> select *  from payment_view1;
Empty set (0.00 sec)

mysql> select *  from payment_view2;
Empty set (0.00 sec)

mysql> show create view payment_view1 \G;
*************************** 1. row ***************************
                View: payment_view1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `payment_view1` AS select `payment_view`.`id` AS `id`,`payment_view`.`amount` AS `amount` from `payment_view` where (`payment_view`.`amount` > 5) WITH LOCAL CHECK OPTION
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from 
    -> information_schema.views 
    -> where table_name = 'payment_view1' \G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: test1
          TABLE_NAME: payment_view1
     VIEW_DEFINITION: select `payment_view`.`id` AS `id`,`payment_view`.`amount` AS `amount` from `test1`.`payment_view` where (`payment_view`.`amount` > 5)
        CHECK_OPTION: LOCAL
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.02 sec)

mysql> select * from  information_schema.views  where table_name = 'payment_view'\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: test1
          TABLE_NAME: payment_view
     VIEW_DEFINITION: select `test1`.`payment`.`id` AS `id`,`test1`.`payment`.`amount` AS `amount` from `test1`.`payment` where (`test1`.`payment`.`amount` < 10)
        CHECK_OPTION: CASCADED
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.01 sec)
bwshqh
关注
专栏目录
数据库:mysql基础(七)--视图和索引
XiaodunLP的博客
10-13 288
视图 什么是视图? 举个例子:假如你是数据库的管理员,数据库中存的着前面我们一直用的emp员工信息表.这时候有个用户客户端希望查看这个表,但是这个表中的sal工资字段的内容,你不希望他看到,那么用什么办法解决这个问题呢?我们很容易想到的方法是,我创建另外一张emp_bak(名字随意,只是举例)表.将emp表中不含有sal字段的内容复制过来,供你查看. 但是上面的处理方法有...
mysql索引视图实验总结_视图和索引的创建和使用实验报告
weixin_31608241的博客
02-08 7298
信息工程学院实验报告成 绩：指导老师(签名)：课程名称：《数据库原理》实验项目名称：视图和索引的创建和使用一、实 验 目 的:(1)掌握创建视图的SQL语句的用法。(2)掌握修改视图的方法。(3)熟悉视图更新与基本表更新的区别与联系；认识视图的作用。(4)熟悉索引的作用，以及不同类型索引的区别。(5)学会用T-SQL语句对表创建和删除索引二、实 验 设 备 与 器 件Win7 +Sql serv...
mysql view index_mysql基础（九） 索引和视图
weixin_42451611的博客
02-17 124
索引：是一种按照特定存储格式存储的特殊数据，用于SQL查询索引的类型：聚集索引和非聚集索引：数据是否和索引存储在一起主键索引和辅助索引：索引是否在主键之上左前缀索引和覆盖索引：是否只截取左边的前几个字节最为索引，mysql默认使用左前缀索引左前缀索引的存储：在指定索引的字段中截取左边的前几个字节生成索引，并对索引进行排序处理，将排完序的索引进行分组管理并生成源数据，当用户以索引字段作为查询条件时，...
mysql实验索引和视图_[mysql]索引和视图
weixin_30488835的博客
02-18 299
索引和视图索引：可以加快查询速度视图：是一种虚拟存在的表导入：从文件中导入数据到表导出：从表中导出到文件中备份：mysqldump 备份数据库到文件恢复：从文件恢复数据库#索引alter table tbname add index indexname (colname);create index indexname on tbname (colname);show index from tbna...
mysql实验7 视图与索引_MySQL操作(第八章)-视图和索引
weixin_28744601的博客
02-02 684
视图视图是一个虚拟表(非真实存在)，其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。创建视图基本语法：create view + 视图名 + as + select语句;其中，select语句可以是普通查询，也可以是连接查询、联合查询、子查询等。create view tb1 as select * from studen...
MySQL SQL高级特性 字段约束-索引-视图-外键学习实践
04-27
总结来说，字段约束、索引、视图和外键是MySQL数据库设计和优化的重要组成部分。通过理解和合理使用这些特性，可以有效地提高数据库管理的效率和数据操作的性能。在实际应用中，应当根据具体的数据模型和业务需求...
Mysql数据库学习--索引及视图操作
For_learning的博客
03-14 853
1.索引 1.1常见索引类型： 顺与文件索引、B+树索引、散列索引、位图索引 1.2索引分类 普通索引与唯一索引 单列索引与组合索引 全文索引：FULLTEXT, MyISAM存储引擎支持 空间索引：GEOMETRY、POINT、LINESTRING、POLYGON, MyISAM存储引擎支持 1.3索引创建...
mysql高级笔记 P1 day01-02. MySQL 高级 - Linux上安装MySQL P2 day01-03. MyS
最新发布
09-20
mysql高级笔记 P1 day01-02. MySQL 高级 - Linux上安装MySQL P2 day01-03. MySQL 高级 - 启动及登录MySQL P3 day01-04. MySQL 高级 - 索引 - 概述 P4 day01-05. MySQL 高级 - 索引 - 优势和劣势 P5 day01-06. MySQL ...
MySQLSQL高级特性字段约束-索引-视图-外键学习实践.rar
05-31
本教程主要探讨MySQL中的四大核心高级特性：字段约束、索引、视图和外键。这四个概念是数据库设计和管理的基础，对于提升数据库性能和确保数据完整性至关重要。 ### 字段约束 字段约束是定义数据库表结构时用来限制...
MySQL------事务、索引、约束、存储、视图
qq_43115961的博客
09-19 316
MySQL------事务、索引、约束、存储、视图
mysql实验7 视图与索引_mysql索引与视图
weixin_29708043的博客
01-19 2926
实验内容与步骤：以下操作均在YGGL数据库中进行。一、索引的使用：1.使用命令对YGGL数据库的Employees表中的DepartmentID列建立普通索引。2.在Employees表的Name列和Address列上建立复合索引。3.对Departments表上的DepartmentName列建立唯一索引。4.使用命令查看表中的索引。5.对Employees表上的Address列进行前...
educoder初识MySQL_学习笔记:初识httpd
weixin_42481708的博客
02-23 406
yum install https://mirrors.ustc.edu.cn/epel/epel-release-latest-6.noarch.rpm https://mirrors.ustc.edu.cn/remi/enterprise/remi-release-6.rpmyum -y install yum-utilsyum-config-manager --enable remi-php...
educoder里mysql数据库_Educoder–Java高级特性（第九章）- JDBC（上）【笔记+参考代码】...
weixin_29924309的博客
01-21 698
Educoder–Java高级特性(第九章)- JDBC(上)【笔记+参考代码】日期：2020-05-21浏览：404评论：0核心提示：Educoder–Java高级特性(第九章)- JDBC(上)【笔记+参考代码】第一关编程要求在右侧编辑器补充代码，完成下列相应任务：加载数据库驱动；【平台数据库连接的用户(user)为root，密码(password)为123123】创建数据库my...
PHP EduCoder答案 PHP对MySQL的基本操作
qq_45662837的博客
11-29 4192
PHP EduCoder答案 PHP语言之MySQL操作 PHP对MySQL的基本操作 任务描述 随着公司规模的扩大，老板希望能够利用数据库技术对员工与部门的信息进行管理，而这个任务正好落到了你的身上。你选择了PHP+MySQL的组合来完成这个任务，为了能让老板对你的工作有一个大致的了解，你准备第一步进行数据库的建立和员工以及部门信息数据的录入，并将录入的数据显示在页面中便于老板查看。为了完成这一步，你需要学习以下关于PHP与MySQL的知识。 <?php /** * 初始化数据库连接 */
mysql实验视图及索引_实验5 索引和视图
weixin_35959379的博客
02-27 1689
mysql实验5 索引和视图实验5 索引和视图一、实验目的1. 掌握索引的使用方法2. 掌握数据完整性的实现方法 3. 熟悉视图的概念和作用 4. 掌握视图的创建方法 5. 掌握如何查询和修改视图 实验准备1. 了解索引的作用与分类 2. 掌握索引的创建方法 3. 了解视图的概念 4. 了解创建视图的方法 5. 了解对视图的操作 实验内容及要求1. 创建索引1) 在Employees表的Name列...
MySQL学习之路——七（视图与索引）
蝈子小生的博客
09-29 887
                                                   视图与索引 一、视图 作用：用于并方便于查询，本质即为对查询进行封装     1.定义视图 命令：create view 视图名 as SQL语句     2.使用视图 命令：select * from 视图名     3.修改视图 命令：alter view 视图名 as...
MySQL | CSV数据导入MySQL各种问题解决
weixin_46023346的博客
05-18 3399
报错1 错误描述 Loading local data is disabled; this must be enabled on both the client and server sides禁用加载本地数据，必须在客户端和服务器端都启用 判断是参数设置问题，经查，local infile默认禁用。 load data local infile 'C:\Users\85171\Desktop\如何分析用户留存.csv' into table user_behavior_test fields termi
mysql 学习记录（十七）--水平拆分表、垂直拆分表、中间表
bwshqh的博客
10-26 3507
一、理论： 1.垂直拆分表：把主码某些列放在一个表，把主码及另外的列放在另一个表中。优点是查询时可以减少I/O次数，缺点是查询时需要联合操作。 2.水平拆分：根据一列或多列的值把数据行放到多个表中。 3.水平拆分表的使用情况： a.表很大，分割后可以降低在查询时需要读的数据和索引页数以及层数，可提高查询速度。 b.表中的数据有逻辑上的独立性。如：不同时期的数据或明显具有不同使用频率的数据。 c.需要把数据放在多个介质上。 4.反规范的优势： a.增加冗余列：在多个表中具有相同列，可以在查询时避免连接操作
mysql 学习记录（二十）--Mysql Server参数调整
bwshqh的博客
11-02 2539
一、理论： 1.内存优化原则： a.将尽量多的内存分配给mysql做缓存，但要给操作系统和其他程序预留足够的内存，否则将产生SWAP页交换，影响自身性能 b.MyISAM的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存 c.排序区、连接区等缓存是分配给每个数据库会话专用的，其默认值的设置要根据最大连接数合理分配。不能设置太大否则
国家开放大学：mysql实验4 - 视图与索引实战
本实验训练专注于MySQL数据库管理和优化，主要目标是帮助学生理解和掌握视图和索引在数据库中的关键应用。首先，实验4-1引导学生实践创建不同类型的视图，包括单源视图（如展示今年新增会员的特定信息和仅限奔驰品牌...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值