小熊糖否
MySQL初识
ZeroJiu 愚昧之巅V4
  2017-01-03 23:18:15   2024-07-19 21:19:49    2.3k Words  11 Mins  

记不住过去，看不透未来——失败。

MySQL基础知识点罗列，帮助理清思路，主要包含安装、基本操作、MySQL约束、C/C++库使用等知识点。

Ubuntu下安装MySQL

按照顺序执行下面三条指令：

1
2
3
$ sudo apt-get install mysql-server
$ sudo apt-get install mysql-client
$ sudo apt-get install libmysqlclient-dev

安装完成，检查MySQL是否成功安装：

1
$ sudo netstat -tap | grep mysql

记住安装过程中需要输入的密码，通过下列命令可以登录mysql数据库：

1
$ mysql -u root -p # -u for user, -p for password

安装后，MySQL是默认启动的，如果需要自己控制，则可以执行下列命令：

1
2
3
4
5
6
$ sudo /etc/init.d/mysql start
$ sudo /etc/init.d/mysql stop
$ sudo /etc/init.d/mysql restart
$ sudo service mysql start
$ sudo service mysql stop
$ sudo service mysql restart

MySQL基本操作

登录操作

1
2
3
4
5
$ mysql -u root -p -P 3306 -h 127.0.0.1
# -u --user
# -p --passwd
# -P --port, 3306 is the default port for mysql
# -h --host

设置提示符

1
2
3
4
5
6
7
8
mysql> prompt \u@\h \d>
PROMPT set to '\u@\h \d> '
root@localhost (none) > use test
root@localhost test >
# \u 设置显示用户名
# \h 设置显示主机
# \d 设置显示当前数据库
# \D 设置显示完整日期

查询警告

1
mysql > show warnings;

查询命令帮助

使用help命令能够找到MySQL语句的具体使用帮助指南，我们以create database举例：

1
2
3
4
mysql> help create;
mysql> help create database;
mysql> help alter;
mysql> help alter database;

MySQL数据库操作

数据库创建

使用create database创建数据库t1，并显示详细信息：

1
2
3
4
root@localhost test > create database t1;
root@localhost test > show databases;
root@localhost test > show create database t1;
root@localhost test > create database if not exists t2 character set gbk;

数据库修改

1
root@localhost test > alter database t2 character set = utf8;

数据库删除

1
root@localhost test > drop database t1;

使用数据库

1
2
root@localhost test > use t1;
root@localhost t1 > select database(); # 显示当前数据库

创建数据库表

1
2
3
4
5
root@localhost test > create table tb1(
    -> username varchar(20),
    -> age tinyint unsigned,
    -> salary float(8,2) unsigned
    -> );

查看数据库表

1
2
3
root@localhost test > show tables;           # 查看数据库表列表
root@localhost test > show tables from test; # 查看数据库test表列表
root@localhost test > show columns from tb1; # 查看数据库表结构

插入记录到数据库表

1
2
3
root@localhost test > insert tb1 values('Tom', 25, 7863.25);
root@localhost test > insert tb1 values('Tom', 25); # error
root@localhost test > insert tb1(username,salary) values('John', 4500.69); # ok

记录查找

1
root@localhost test > select * from tb1;

MySQL数据类型

整形

数据类型 存储需求
TINYINT 8位
SMALLINT 16位
MEDIUMINT 24位
INT 32位
BIGINT 64位

浮点型

数据类型 存储需求
FLOAT(M,D) M是数字总位数，D是小数点后面的位数
DOUBLE(M,D)

日期时间型

列类型 存储需求 范围
YEAR 8位 1901~2155
TIME 24位 -838:59:59~838:59:59
DATE 24位 1000.01.01~9999.12.31
DATETIME 64位 1000.01.01 00:00:00~9999.12.31 23:59:59
TIMESTAMP 32位 1970.01.01 00:00:01~2037 年某时

字符类型

列类型 存储需求
CHAR(M) 固定长度，M个字节，0<=<M<=255
VARCHAR(M) 可变长度，L+1个字节，L<=M且0<=M<=2^16 -1
TINYTEXT L+1个字节，L<2^8
TEXT L+1个字节，L<2^16
MEDIUMTEXT L+1个字节，L<2^24
LONGTEXT L+1个字节，L<2^32
ENUM(‘value1’,‘value2’,…) 1或2个字节，最多2^16 -1个值
SET(‘value1’,‘value2’,…) 1、2、3、4或8个字节，最多64个成员

MySQL约束

主键约束

NOT NULL和UNIQUE的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

AUTO_INCREMENT必须与主键组合使用，其会在新记录插入表中时生成一个唯一的数字。默认情况下，起始值为1，每次的增量为1。

1
2
3
4
5
6
7
8
root@localhost test > create table tb3(                    
    -> id smallint unsigned AUTO_INCREMENT PRIMARY KEY,
    -> username varchar(30) NOT NULL
    -> );
root@localhost test > show columns from tb3;
root@localhost test > insert tb3(username) values('Tom');
root@localhost test > insert tb3(username) values('John');
root@localhost test > select * from tb3;

非空约束

NOT NULL指示某列不能存储NULL值。在默认的情况下，表的列接受NULL值。

  • NULL，字段值可以为空
  • NOT NULL，字段值禁止为空
1
2
3
4
5
6
root@localhost test > create table tb2(
    -> username varchar(20) NOT NULL,
    -> age tinyint unsigned NULL
    -> );
root@localhost test > insert tb2 values('tom',NULL); # ok
root@localhost test > insert tb2 values(NULL,25);    # error

唯一约束

UNIQUE保证某列的每行必须有唯一的值。唯一约束的字段可以为NULL值，但是必须只有一个NULL值。

请注意，每个表可以有多个UNIQUE约束，但是每个表只能有一个PRIMARY KEY约束。

1
2
3
4
5
6
7
8
root@localhost test > create table tb5(
    -> id smallint unsigned AUTO_INCREMENT PRIMARY KEY,
    -> username varchar(20) NOT NULL UNIQUE KEY,
    -> age tinyint unsigned
    -> );
root@localhost test > show columns from tb5;
root@localhost test > insert tb5(username, age) values('Tom',22);
root@localhost test > insert tb5(username, age) values('Tom',22); # error

默认约束

为没用明确指出值的字段赋予默认值。

1
2
3
4
5
6
7
root@localhost test > create table tb6(
    -> id smallint unsigned AUTO_INCREMENT PRIMARY KEY,
    -> username varchar(20) NOT NULL UNIQUE KEY,
    -> sex ENUM('1','2','3') DEFAULT '3'
    -> );
root@localhost test > insert tb6(username) values('Tom');
root@localhost test > select * from tb6;

外键约束

保证数据一致性、完整性。父表和子表必须使用相同的存储引擎，数据表的存储引擎只能为InnoDB。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# 父表
root@localhost test > create table provinces(
    -> id smallint unsigned PRIMARY KEY AUTO_INCREMENT,
    -> pname varchar(20) NOT NULL
    -> );
# 子表
root@localhost test > create table users(
    -> id smallint unsigned PRIMARY KEY AUTO_INCREMENT,
    -> username varchar(20) NOT NULL,
    -> pid smallint unsigned,
    -> FOREIGN KEY (pid) REFERENCES provinces (id)
    -> );

root@localhost test > insert provinces(pname) values('A');
root@localhost test > insert provinces(pname) values('B');
root@localhost test > insert provinces(pname) values('C');
root@localhost test > select * from provinces;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
|  3 | C     |
+----+-------+
3 rows in set (0.00 sec)
root@localhost test > insert users(username, pid) values('Tom',3);
root@localhost test > insert users(username, pid) values('John',7); # error
root@localhost test > insert users(username, pid) values('John',3);
root@localhost test > insert users(username, pid) values('Rose',1);
root@localhost test > select * from users;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  1 | Tom      |    3 |
|  3 | John     |    3 |
|  4 | Rose     |    1 |
+----+----------+------+

上面插入*(‘John’,7)*时出错，是因为provinces中并没有id为7的索引。同时，即使插入失败，索引值还是会增加，users表中不存在id为2的项。

外键列和数据列必须具有相似的数据类型，其中数据的长度或是否是有符号位必须相同，而字符的长度则可以不同。上例中，provinces中的id和users中的pid数据类型和长度必须相同。

外键列（users.pid）和参照列（provinces.id）必须创建索引，如果外键列不存在索引的话，MySQL将自动创建。

我们可以决定进行外键约束的创建以后，在更新表的时候，子表是否也进行相应的更新——外键约束的参照操作。具体的参照操作如下：

  • CASCADE：从父表删除或更新记录时，自动删除或更新子表中匹配的行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
root@localhost test > create table users1(
    -> id smallint unsigned PRIMARY KEY AUTO_INCREMENT,
    -> username varchar(20) NOT NULL,
    -> pid smallint unsigned,
    -> FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE
    -> );
root@localhost test > show create table users1;
root@localhost test > insert users1(username, pid) values('Tom',3);
root@localhost test > insert users1(username, pid) values('John',3);
root@localhost test > insert users1(username, pid) values('Rose',1);
root@localhost test > select * from users1;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  1 | Tom      |    3 |
|  2 | John     |    3 |
|  3 | Rose     |    1 |
+----+----------+------+
3 rows in set (0.00 sec)
root@localhost test > delete from provinces where id=3;
root@localhost test > select * from provinces;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
+----+-------+
2 rows in set (0.00 sec)
root@localhost test > select * from users1;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  4 | Rose     |    1 |
+----+----------+------+

可以看出，删除provinces中id=3的项，对应的users1中pid=3的项也会被删除。

  • SET NULL：从父表中删除或更新记录时，把子表中的外键列设置为NULL，如果使用该选项，必须保证子表列没有指定NOT NULL。

  • RESTRICT：拒绝对父表的删除或更新操作。

  • NO ACTIION：标准SQL关键字，在MySQL中与RESTRICT相同。

使用C/C++语言来操作MySQL

我们可以使用命令行来操作MySQL数据库，譬如：

1
2
3
root@localhost test > show databases;
root@localhost test > use mysql;
root@localhost test > show tables;

也可以通过MySQL对外公布的C接口来操作数据库，譬如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
/* show_tables.cxx */

#include <mysql/mysql.h>
#include <stdio.h>
#include <stdlib.h>

int main()
{
    MYSQL * conn;
    MYSQL_RES * res;
    MYSQL_ROW row;

    char server[] = "localhost";
    char user[] = "root";
    char passwd[] = "lovemime";
    char database[] = "mysql";

    conn = mysql_init(NULL);
    if(!mysql_real_connect(conn, server, user, passwd, database, 0, NULL, 0))
    {
        fprintf(stderr, "%s/n", mysql_error(conn));
        exit(1);
    }

    if(mysql_query(conn, "show tables"))
    {
        fprintf(stderr, "%s/n", mysql_error(conn));
        exit(1);
    }

    res = mysql_use_result(conn);

    printf("MySQL Tables in mysql database:\n");

    while((row =mysql_fetch_row(res)) != NULL)
    {
        printf("%s \n", row[0]);
    }

    mysql_free_result(res);
    mysql_close(conn);

    printf("finish! \n");
    return 0;
}

连接MySQL库，来编译该段代码：

1
$ g++ -Wall show_tables.cxx -o showtables -lmysqlclient

参考链接

MySQL初识
develop/mysql-basic/
Author
ZeroJiu
Published
2017-01-03 23:18
License
© 2020 - 2024    ZeroJiu
Powered by Hexo & Theme Keep
This site is deployed on
Unique Visitor Page View