SQL学习笔记--2

昨天状态简直是不好．
昨天晚上一直睡不着，今天早上起来后没精神，书放在旁边也不想翻，想查一下其他git命令，没办法集中精神去看。

中午该补觉了，躺在床上一个小时没睡着。
下午看书脑袋痛，恶性循环啊，天啊。

1 安装mysql-server

for-python@ubuntu:~$ sudo apt-get install mysql-server libmysqlclient-dev

安装过程中会需要设置mysql密码：

2 MySQL常用命令
习惯上，MySQL关键字都用大写．

2.1 mysql -u root -p

用户名root，再输入刚才安装过程中设置的密码。
(u-user; p-password)

2.2 mysql> SHOW DATABASES;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (3.26 sec)

2.3 CREATE DATABASE FLASKAPP;

mysql> CREATE DATABASE FLASKAPP;
Query OK, 1 row affected (1.94 sec)

2.4 USE FLASKAPP;

mysql> USE FLASKAPP;
Database changed

2.5 CREATE TABLE users()

mysql> CREATE TABLE users(id INT(11) AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100),email VARCHAR(100), username VARCHAR(30), password VARCHAR(100), register_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (1.09 sec)

CREATE TABLE Syntax

1. 
   
2. 每个字段按定义由三部分组成:
   名称（id, name,email…）；
   数据类型(VARCHAR, TIMESTAMP…)；
   其他可选属性(AUTO_INCREMENT, DEFAULT…)．
   
3. 这里把id设置成了主键（PRIMARY KEY），这里mysql是一个表存储数据，primary key用于唯一标识表中的某一条记录。
   
4. VARCHAR is a good choice for the name, owner, and species columns because the column values vary in length. The lengths in those column definitions need not all be the same.
   
5. AUTO_INCREMENT
   An integer or floating-point column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| FLASKAPP           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (1.36 sec)

2.6 DESCRIBE users;

mysql> DESCRIBE users;
+---------------+--------------+------+-----+-------------------+----------------+
| Field         | Type         | Null | Key | Default           | Extra          |
+---------------+--------------+------+-----+-------------------+----------------+
| id            | int(11)      | NO   | PRI | NULL              | auto_increment |
| name          | varchar(100) | YES  |     | NULL              |                |
| email         | varchar(100) | YES  |     | NULL              |                |
| username      | varchar(30)  | YES  |     | NULL              |                |
| password      | varchar(100) | YES  |     | NULL              |                |
| register_date | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+---------------+--------------+------+-----+-------------------+----------------+
6 rows in set (0.34 sec)

下面的命令都是用来查询MySQL表的，这里新建的表什么数据也没有，所以什么都查不到。

2.7 SELECT * FROM users;

mysql> SELECT * FROM users;
Empty set (0.06 sec)

2.8 SELECT * FROM users WHERE id=2;

mysql> SELECT * FROM users WHERE id=2;
Empty set (0.10 sec)

2.9 SELECT * FROM users WHERE name LIKE “%mio%”;

mysql> SELECT * FROM users WHERE name LIKE "%mio%";
Empty set (0.00 sec)

2.10 SELECT username,register_date FROM users WHERE name LIKE “%mio%”;

mysql> SELECT username,register_date FROM users WHERE name LIKE "%mio%";
Empty set (0.00 sec)

2.11 DELETE FROM users WHERE id=1;

DELETE 语法与SELECT 类似，只要把SELECT * 换成DELETE就可以了。
删除之后不能恢复了，所以删除之前用SELECT查看一下，防止误删。

mysql> DELETE FROM users WHERE id=1;
Query OK, 0 rows affected (0.84 sec)

2.12 UPDATE

mysql> UPDATE users SET name="mio-muhan" WHERE id=2;
Query OK, 0 rows affected (0.05 sec)
Rows matched: 0  Changed: 0  Warnings: 0

2.13 QUIT

mysql> QUIT
Bye
for-python@ubuntu:~$

建立自己的数据库后，接下来就是把mysql应用到Flask Web Development 代码中，用来存储用户注册信息，验证用户登录。