目录


1 MySQL介绍

MySQL:开放源代码的关系数据库管理系统(RDBMS,relational database management system)。由于其开源性,稳定性和易用性,在数据库领域很受欢迎。

regular expression

1.1 安装

terminal 输入:brew install mysql

如果之前安装过,出现错误,需要完全卸载后重装,具体见Uninstall MySQL Completely;

1.2 Account registration and login

MySQLclient-server架构搭建。server端是mysqld,它可以运行在本地主机(localhost,127.0.0.1:3306)或者网络上。client端有mysql,或者图形界面版本MySQL workbench,以及其他语言版本例如python版的pymysql模块。

因此我们需要先在服务端注册一个账号,再在客户端登入。

1.2.1 Account registration

1
2
3
4
5
6
7
8
9
% mysql -h localhost -u root -p  //-h表示hostname,后面跟着本地主机(表示服务端运行在本地主机);-u表示username,后面跟着根用户;-p表示密码,后面要求输入。
Enter password: ******

mysql> CREATE USER 'cbuser'@'localhost' IDENTIFIED BY 'cbpass'; //创建来自localhost(客户端)的用户,以及密码。
mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'localhost';  //将数据库cookbook开放给cbuser客户。
Query OK, 0 rows affected (0.09 sec)

mysql> quit  //退出
Bye

1.2.2 Account login

1
2
% mysql -h localhost -u cbuser -p 
Enter password: cbpass

1.3 RDBMS Terminology

Database:a collection of tables, with related data;

Table:a collection of related data entries and it consists of column and rowTable是RDBMS的中心类。

Column:contains the field and its all data.

Filed:the data name.

Row:an entry.

Primary Key: a unique key as ID to a Row.

1.4 mysql数据类型

1 Numeric Type

TINYINT :127 to 128

SMALLINT :32,768 to -32767

MEDIUMINT :8,388,608 to -8,388,609

INT :2^31 to -2^31-1

TINYINT :127 to 128

SMALLINT :32,768 to -32767

2 String Type

CHAR :fiexed length

VARCHAR :variable length

BLOG :2^16 bytes

3 Collection

ENUM

SET

1.5 函数

MySQL有内建函数,比如MIN(),MAX()等。

1.6 大小写

MySQL对大小写不敏感,最佳实践是实例用小写(比如score,students),命令(比如SHOW,SELECT,WHERE)和类(比如TABLES,DATABASES)用大写。

1
SELECT score in Students WHERE id = 2

2 MySQL实战

1
2
3
4
5
6
7
8
9
10
11
12
13
//1 创建数据库
mysql > CREATE DATABASE Class1; 
//2 创建表(同时创建Field)
mysql > CREATE TABLE Student (first_name VARCHAR(30) NOT NULL, score INT NOT NULL,  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
//2.2 增删改查Row

INSER INTO Students Values (“Jim”,82), (“Lucy”,77);      //增:增加两row

DELETE FROM Students Where id = 2;                       //删:将id=2的row删除

UPDATE Students SET score = 99 where first_name = “Jim”; //改:将名为Jim的score改成99

SELECT first_name, id FROM Students WHERE score > 80;    //查:指定name,age列 和 score >80的行

3 总结

Database,Table,Column,Row的操作逃不出增删改查4种,只不过命令名字和规则略有不同。按照这个原则,我们 对MySQL的客户端mysql总结成下表,以供参考。

regular expression

4 参考资料


Share Post

Twitter Google+

Shunmian

The only programmers in a position to see all the differences in power between the various languages are those who understand the most powerful one.