MySQL Learning Notes

从软件源安装基本是一键安装。

源码安装参见MySQL installation

启动和停止服务，反正gentoo下是

sudo /etc/init.d/mysqld start
sudo /etc/init.d/mysqld stop
sudo /etc/init.d/mysqld restart

手动启动=mysqld_safe &=也行。

检查服务是否启动：

 ~/Work/learn/MySQL ⮀ mysqladmin -uroot -p ping
Enter password: 
mysqld is alive

mysqladmin=还可以用来关闭服务，参见=man mysqladmin.

=mysql -uroot -p=直接连接到mysql的交互shell中去。

在这个shell中可以使用命令(比如=system pwd=)也可以使用SQL语句，SQL语句以分号结束。

查看数据库:

SHOW DATABASES;

创建数据库=mydb=

CREAT DATABASE mydb;

使用数据库=mydb=

use mydb;

显示数据库中的表：

SHOW TABLES;

执行数据库文件：

source cars.sql

从表中选择：

SELECT * FROM Cars;

创建新用户

CREATE USER user123@localhost IDENTIFIED BY 'pass123';

授予用户对特定数据库/表的特定权限

GRANT ALL ON mydb.* to user123@localhost;

root用户：

• 创建数据库
• 使用数据库
• 导入数据库文件(吐槽下mysql数据库导入的神慢)
• 授予权限

普通用户：

• 查看数据库
• 使用数据库
• 查看表

描述表：

DESCRIBE City;

查看创建表的命令

SHOW CREAT TABLE City;

使用=mysqldump=备份表(在系统shell中而不是mysql的交互shell)

mysqldump -uroot -p world City > city.sql

移除表

DROP TABLE City;

接着讲讲查表。

限制条数：

SELECT Id, Name, Population FROM City LIMIT 10;
SELECT Id, Name, Population FROM City LIMIT 15, 5;

使用=less=分页，

pager less

非交互式使用SQL命令

mysql -u user123 -p world -e "SELECT * FROM City" > city

COUNT函数

SELECT COUNT(Id) AS 'Number of rows' FROM City;

MAX, 下面命令显示了SQL的子查询特性:

mysql> SELECT Name, Population FROM City
    -> WHERE Population = (SELECT Max(Population) FROM City);

MIN:

mysql> SELECT Name, Population FROM City
    -> WHERE Population = (SELECT Min(Population) FROM City);

WHERE语句

mysql> SELECT Name, Population FROM City 
    -> WHERE Population > 1000000;

mysql> SELECT Name FROM City WHERE Name LIKE 'Kal%';

mysql> SELECT Name, Population FROM City 
    -> WHERE ID IN (5, 32, 344, 554);

mysql> SELECT * FROM City WHERE Name = 'Bratislava';

mysql> SELECT Name, Population FROM City 
    -> WHERE Population BETWEEN 670000 AND 700000;

排序数据

mysql> SELECT Name, Population FROM City
    -> ORDER BY Population DESC LIMIT 10;

mysql> SELECT Name, Population FROM City 
    -> ORDER BY Population ASC LIMIT 10;

mysql> SELECT Name, Population FROM City 
    -> ORDER BY Name LIMIT 10;

聚合数据

mysql> SELECT District, SUM(Population) FROM City
    -> WHERE District = 'Henan' GROUP BY District;

mysql> SELECT Name, District, Population FROM City
    -> WHERE District = 'Henan';

mysql> SELECT District, SUM(Population) FROM City
    -> WHERE CountryCode = 'USA' GROUP BY District
    -> HAVING SUM(Population) > 3000000;

更新，删除和插入数据

mysql> UPDATE Country SET HeadOfState = 'Xi Jinping' 
    -> WHERE Name = 'China';

mysql> DELETE FROM City WHERE ID IN (2, 4, 6, 8, 10);

mysql> TRUNCATE TABLE City;

mysql> INSERT INTO City VALUES(999999, 'Kabul', 'AFG', 'Kabol', 1780000);

mysql> DROP TABLE City

存储引擎是数据库管理系统读写更新数据库中数据的软件模型。

MySQL支持的存储引擎有：

• MyISAM，早于5.5版本的默认引擎
• InnoDB
• Memory
• CSV
• 等等

情况很复杂，选择之前最好上StackOverFlow问问。

创建表时使用=ENGINE=关键字指定引擎：

mysql> CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(50), 
    -> Cost INTEGER) ENGINE='MyISAM';

查看当前引擎：

mysql> SHOW VARIABLES LIKE 'storage_engine';

发现特定数据库的表引擎

mysql> SELECT ENGINE FROM information_schema.TABLES
    -> WHERE TABLE_SCHEMA='mydb'
    -> AND TABLE_NAME='Cars';

更改表的引擎：

mysql> ALTER TABLE Cars ENGINE='InnoDB';

删除表：

mysql> DROP TABLE Testing;

使用=ALTER TABLE=语法更改表。以下为重命名表、添加列、添加主键、更改列、删除列操作。(DESCRIBE和SHOW COLUMNS FROM同义，INTEGER和INT相同)

mysql> ALTER TABLE Testing RENAME TO TestTable;
mysql> ALTER TABLE TestTable ADD iValues INT;
mysql> ALTER TABLE TestTable ADD PRIMARY KEY (Id);
mysql> ALTER TABLE TestTable CHANGE COLUMN iValues iValues1 INT;
mysql> ALTER TABLE TestTable DROP COLUMN iValues1;

插入时可以指定插入的列，也可以不指定

mysql> INSERT INTO Books(Id, Title, Author) VALUES(1, 'War and Peace', 
-> 'Leo Tolstoy');
mysql> INSERT INTO Books VALUES(3, 'Crime and Punishment',
-> 'Fyodor Dostoyevsky');

若不指定=Id=也可。

mysql> INSERT INTO Books(Title, Author) VALUES ('The Brothers Karamazov',
-> 'Fyodor Dostoyevsky');

一次可以插入单个或多个值

mysql> INSERT INTO Books(Title, Author) VALUES ('The Insulted and Humiliated',
-> 'Fyodor Dostoyevsky'), ('Cousin Bette', 'Honore de Balzac');

=REPLACE=可以取代特定行：

mysql> REPLACE INTO Books VALUES(3, 'Paradise Lost', 'John Milton');

插入也可以和=SELECT=结合：

mysql> INSERT INTO Books2 SELECT * FROM Books;

可以使用=LOAD DATA INFILE=和=LOAD XML INFILE=语法从文件插入：

mysql> LOAD DATA INFILE '/tmp/books.csv'    
    -> INTO TABLE Books    
    -> FIELDS TERMINATED BY ','    
    -> LINES TERMINATED BY '\n';

mysql> LOAD XML INFILE '/home/vronskij/programming/mysql/books.xml' INTO TABLE Books;

mysql> DELETE FROM Books2 WHERE Id=1;
mysql> DELETE FROM Books2;
mysql> TRUNCATE Books2;

=GROUP BY=语句被用来结合值一样的数据库记录到一个单一的记录中。通常和聚合函数一起使用。

mysql> SELECT SUM(OrderPrice) AS Total, Customer FROM Orders GROUP BY Customer;

使用聚合函数时不能使用=WHERE=而是使用=HAVING=：

mysql> SELECT SUM(OrderPrice) AS Total, Customer FROM Orders
    -> GROUP BY Customer HAVING SUM(OrderPrice)>1000;

mysql> SELECT * INTO OUTFILE '/tmp/cars.txt'
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> FROM Cars;

又称嵌套查询，可以和=INSERT=、=SELECT=、=UPDATE=和=DELETE=配合使用：

mysql> INSERT INTO Cars2 SELECT * FROM Cars;
mysql> SELECT Name FROM Customers WHERE 
-> CustomerId=(SELECT CustomerId FROM Reservations WHERE Id=5);
mysql> SELECT Name FROM Customers WHERE CustomerId IN    
-> (SELECT DISTINCT CustomerId FROM Reservations);
mysql> SELECT Name FROM Cars WHERE Cost <
-> (SELECT AVG(Cost) FROM Cars);

=EXISTS=和=NOT EXISTS=：

mysql> SELECT Name FROM Customers WHERE EXISTS
-> (SELECT * FROM Reservations WHERE
-> Customers.CustomerId=Reservations.CustomerId);
mysql> SELECT Name FROM Customers WHERE NOT EXISTS    
-> (SELECT * FROM Reservations WHERE 
-> Customers.CustomerId=Reservations.CustomerId);

限制用来限定列的类型。

=NOT NULL=：

mysql> CREATE TABLE People(Id INTEGER, LastName TEXT NOT NULL,
->                     FirstName TEXT NOT NULL, City VARCHAR(55));

=UNIQUE=：

mysql> CREATE TABLE Brands(Id INTEGER, BrandName VARCHAR(30) UNIQUE);

=PRIMARY KEY=自动拥有=UNIQUE=和=NOT NULL=：

mysql> CREATE TABLE Brands(Id INTEGER PRIMARY KEY, BrandName VARCHAR(30) UNIQUE);

=FOREIGN KEY=是指向另一个表主键的索引，用来联系两个表：

mysql> CREATE TABLE Books(BookId INTEGER PRIMARY KEY, Title VARCHAR(50),
    -> AuthorId INTEGER, FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId))
    -> type=InnoDB;

=ENUM=可以是列出中的一个：

mysql> CREATE TABLE Shops(Id INTEGER, Name VARCHAR(55), 
    -> Quality ENUM('High', 'Average', 'Low'));

=SET=可以是零个或多个：

mysql> CREATE TABLE Students(Id INTEGER, Name VARCHAR(55), 
    -> Certificates SET('A1', 'A2', 'B1', 'C1')); 

注意插入时=Certificates=列的值用括号括起来，之间用逗号分隔且不能有空格。

mysql> INSERT INTO Students VALUES(3, 'Mark', 'A1,A2,D1,D2');

简单导出到文件和导入到数据库：

mysql> SELECT * FROM Cars INTO OUTFILE '/tmp/cars';  # 空格分隔
mysql> LOAD DATA INFILE '/tmp/cars.csv' INTO TABLE Cars
-> FIELDS TERMINATED BY ',';

导入到xml文件：

$ mysql -uroot -p --xml -e 'SELECT * FROM mydb.Cars' > /tmp/cars.xml
mysql> LOAD XML /tmp/cars.xml INTO TABLE Cars;

使用mysqldump工具：

只保存数据结构：

$ mysqldump -u root -p --no-data mydb > bkp1.sql

只保存数据

$ mysqldump -uroot -p --no-create-info mydb > bkp2.sql

完整数据库

$ mysqldump -uroot -p mydb > bkp3.sql

恢复数据：

mysql> CREATE DATABASE mydb;
mysql> USE mydb;
mysql> source bkp3.sql

视图是一种从表中切取的特殊伪表。有以下限制：

• =SELECT=不能包含子查询
• =SELECT=语句不能指向系统或用户变量
• 在定义中指向的任何表或视图必须存在
• 不能创建临时视图
• 一个视图不能被触发关联。

创建、更改和删除视图：

mysql> CREATE VIEW CheapCars AS 
    -> SELECT Name FROM Cars WHERE Cost<25000;
mysql> ALTER VIEW CheapCars AS SELECT Name FROM Cars
    -> WHERE Cost<30000;
mysql> DROP VIEW CheapCars;

找到视图：

mysql> SHOW FULL TABLES;
mysql> SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES;
mysql> SELECT TABLE_NAME FROM information_schema.VIEWS;

=UNION=被用来结合两个或更多=SELECT=语句得到的结果集，可以通过=UNION=创建视图

mysql> CREATE VIEW FavoriteCars AS
    -> SELECT * FROM Cars WHERE Id=7
    -> UNION SELECT * FROM Cars WHERE Id=4
    -> UNION SELECT * FROM Cars WHERE Id=5;

数据库并发操作控制单位。这些操作可以commit可以回滚。

解释下ACID。

mysql> SELECT @@tx_isolation;
mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

mysql> SELECT @@autocommit;
mysql> SET autocommit=0;

mysql> START TRANSACTION;

mysql> INSERT INTO Test VALUES (5), (6);
mysql> INSERT INTO Test VALUES (7), (8);
mysql> COMMIT;

mysql> INSERT INTO Test VALUES (5), (6);
mysql> INSERT INTO Test VALUES (7), (8);
mysql> ROLLBACK;

在MySQL中存在两种存储例程，一种是过程，一种是函数。

过程用=CALL=调用，不返回值。函数通过=SELECT=使用，返回值。

过程存储有利有弊。

mysql> CREATE PROCEDURE AllCars() SELECT * FROM Cars;
mysql> CALL AllCars();

-- this function computes the area
-- of a circle; it takes a radius as
-- a parameter

DELIMITER $$

DROP FUNCTION IF EXISTS CircleArea;

CREATE FUNCTION CircleArea(r DOUBLE) RETURNS DOUBLE
BEGIN
    DECLARE area DOUBLE;

    SET area = r * r * pi();
    RETURN area;
END 

$$

DELIMITER ;

mysql> source circlearea.sql
mysql> SELECT CircleArea(5.5);

-- this procedure computes the power 
-- of a given value

DELIMITER $$

DROP PROCEDURE IF EXISTS Pow;

CREATE PROCEDURE Pow(IN val DOUBLE, OUT p DOUBLE) 
BEGIN
    SET p = val * val;
END 

$$

DELIMITER ;

mysql> source power.sql

mysql> CALL Pow(3, @p);

mysql> SELECT @p;

mysql> SELECT SPECIFIC_NAME from information_schema.ROUTINES  
    -> WHERE ROUTINE_TYPE='PROCEDURE';
mysql> SELECT SPECIFIC_NAME from information_schema.ROUTINES 
    -> WHERE ROUTINE_TYPE='FUNCTION';

MySQL有个C API

debian上要装这个：

$ sudo apt-get install libmysqlclient-dev

我们只要在头文件中包含=mysql.h=，编译时以以下方式编译：

$ gcc version.c -o version  `mysql_config --cflags --libs`

基本步骤是先初始化连接：

MYSQL *con = mysql_init(NULL);

if (con == NULL) 
{
    fprintf(stderr, "%s\n", mysql_error(con));
    exit(1);
}

再真的连到服务器或某个数据库中：

if (mysql_real_connect(con, "localhost", "root", "root_pswd", 
        NULL, 0, NULL, 0) == NULL) 
{
    fprintf(stderr, "%s\n", mysql_error(con));
    mysql_close(con);
    exit(1);
}

执行操作：

if (mysql_query(con, "CREATE DATABASE testdb")) 
{
    fprintf(stderr, "%s\n", mysql_error(con));
    mysql_close(con);
    exit(1);
}

取回数据：

MYSQL_RES *result = mysql_store_result(con);

if (result == NULL) 
{
    finish_with_error(con);
}

int num_fields = mysql_num_fields(result);

MYSQL_ROW row;

while ((row = mysql_fetch_row(result))) 
{ 
    for(int i = 0; i < num_fields; i++) 
    { 
        printf("%s ", row[i] ? row[i] : "NULL"); 
    } 
        printf("\n"); 
}

mysql_free_result(result);

还有些其它函数，参考http://dev.mysql.com/doc/refman/5.1/zh/apis.html