0、索引类型
- 普通索引：index、key
- 唯一索引：uniqe
- 主键索引：primary key
- 全文索引：fulltext（MyISAM引擎或InnoDB引擎v5.7及以上版本支持）
1、查看当前表创建
- show create table user;
mysql> show[......]索引
Leave a reply
Category Archives: 数据库
触发器的简单使用
1、创建触发器
- 修改界定符：delimiter //
- 定义触发器：create trigger xxx after|before insert|update|delete on xxxx for each row
- 触发器执行内容：begin xxx end
- //执行
- 还原界定符：delimiter ;
[……]
视图的简单使用
1、什么是视图
- 视图是表虚拟表，通过sql语句组合查询的结果，视图是原始数据的一种变换
- 定义：create view xxx (a, b, c, d) as select h, i, j, k from xxxx xxxxx；
- 删除：drop view xxxx；
mysql> sel[......]DTL 事务控制语言
1、一个简单的事务
- 开启事务：start transaction
- 回滚事务：rollback
- 提交事务：commit
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set fee = 1200 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
| id | user_name | sex | password | email | address | fee | age | create_at |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
| 1 | sian | 男 | e10adc3949ba59abbe56e057f20f883e | yusian@163.com | NULL | 1200.00 | 32 | 2019-04-23 14:45:36 |
| 2 | 余西安 | 男 | c4ca4238a0b923820dcc509a6f75849b | yusian@163.com | NULL | 1.00 | 33 | 2019-04-21 17:12:55 |
| 3 | Jack | 男 | c81e728d9d4c2f636f067f89cc14862c | jack@yusian.com | NULL | 0.00 | 22 | 2019-04-21 20:11:59 |
| 4 | Rose | 女 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | rose@yusian.com | NULL | 0.00 | 18 | 2019-04-22 10:46:41 |
| 5 | Sim | 女 | a87ff679a2f3e71d9181a67b7542122c | sim@yusian.com | NULL | 0.00 | 28 | 2019-04-22 10:46:41 |
| 6 | Tony | 男 | c4ca4238a0b923820dcc509a6f75849b | tony@yusian.com | NULL | 0.00 | 22 | 2019-04-21 20:41:26 |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
6 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
| id | user_name | sex | password | email | address | fee | age | create_at |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
| 1 | sian | 男 | e10adc3949ba59abbe56e057f20f883e | yusian@163.com | NULL | 1100.00 | 32 | 2019-04-23 14:38:53 |
| 2 | 余西安 | 男 | c4ca4238a0b923820dcc509a6f75849b | yusian@163.com | NULL | 1.00 | 33 | 2019-04-21 17:12:55 |
| 3 | Jack | 男 | c81e728d9d4c2f636f067f89cc14862c | jack@yusian.com | NULL | 0.00 | 22 | 2019-04-21 20:11:59 |
| 4 | Rose | 女 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | rose@yusian.com | NULL | 0.00 | 18 | 2019-04-22 10:46:41 |
| 5 | Sim | 女 | a87ff679a2f3e71d9181a67b7542122c | sim@yusian.com | NULL | 0.00 | 28 | 2019-04-22 10:46:41 |
| 6 | Tony | 男 | c4ca4238a0b923820dcc509a6f75849b | tony@yusian.com | NULL | 0.00 | 22 | 2019-04-21 20:41:26 |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
6 rows in set (0.00 sec)
[……]
DQL常用操作及常见问题
DQL: Database Query Language
1、查询某字段去重复
- select distinct xxxx from xxxx
2、合并字段查询函数concat( )、concat_ws( )
- select concat(user_name, password) from user;
- select concat(user_name, password) as user_name_email from user;
- select concat_ws(‘->’, user_name, password) from user; // 查询结果用’->’分隔符隔开
[……]