MySQL——Establish

1.创建数据库

mysql> create database yolo;
Query OK, 1 row affected (0.93 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| yolo |
+--------------------+

2.创建表和字段

已知有两张表yolo1，yolo2。

创建完成后利用.py脚本编写后运行导入。

mysql> use yolo;
Database changed

mysql> create table yolo1(studentid varchar(10),name varchar(6),major varchar(8),mark int(6));

mysql> create table yolo2(studentid varchar(10),age int(6),height int(6));

mysql> select *from yolo1;
+-----------+--------+----------+------+
| studentid | name | major | mark |
+-----------+--------+----------+------+
| 1 | 张三 | 信息技术 | 99 |
| 2 | 李四 | 地质工程 | 98 |
| 3 | 王五 | 化学 | 97 |
| 4 | 小明 | 古典文学 | 99 |
| 5 | 李华 | 商业英语 | 98 |
| 6 | 温思莞 | 数学 | 99 |
| 7 | 陆流 | 金融 | 100 |
+-----------+--------+----------+------+
7 rows in set (0.05 sec)

mysql> select *from yolo2;
+-----------+------+--------+
| studentid | age | height |
+-----------+------+--------+
| 1 | 21 | 165 |
| 2 | 22 | 166 |
| 3 | 23 | 167 |
| 4 | 24 | 168 |
| 5 | 25 | 175 |
| 6 | 27 | 180 |
| 7 | 26 | 183 |
+-----------+------+--------+
7 rows in set (0.00 sec)

3.创建索引

 

4.创建视图

视图其实就是自定义的函数，创建如下：

mysql> create view picture as
-> select name,age from yolo1,yolo2
-> where yolo1.studentid=yolo2.studentid order by age;

mysql> select *from picture;
+--------+------+
| name | age |
+--------+------+
| 张三 | 21 |
| 李四 | 22 |
| 王五 | 23 |
| 小明 | 24 |
| 李华 | 25 |
| 陆流 | 26 |
| 温思莞 | 27 |
+--------+------+

5.创建存储过程和存储函数

存储过程就是一种sql脚本：

delimiter $
create procedure store()
begin
select avg(age) as avgage from yolo2;
end $

mysql> call store();
+---------+
| avgage |
+---------+
| 24.0000 |
+---------+

 

存储函数：

@@@@@@@@@@