大部分网站都要用到数据库,其中较为常见的是mysql
,其他数据库大同小异,这里我就以mysql
为例,在Ubuntu lts 18.04
中讲讲如何使用数据库。
首先是搭建mysql
环境,Ubuntu
使用apt
安装就行了。
# apt install mysql-client mysql-server
一、创建mysql
用户
我接触mysql
以来没有成功以root
登录mysql
,后来我自行摸索出一个方案。
# mysql
,一定要root
权限
mysql> CREATE USER 'test'@'%' IDENTIFIED BY '123456';
创建test
用户,名字自定,可以从任何主机访问(%
代表任何主机,localhost
代表本机,ip
地址代表指定ip
),密码是123456
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY '123456';
给test
用户赋予操作所有数据库(第一个*
)的所有表(第二个*
)的所有权限(ALL PRIVILEGES
)
然后quit
退出mysql
,在终端输入mysql -utest -p123456
登录test
用户
mysql
可能会警告在终端输入密码不安全,因为mysql
可以用下列命令输入密码
mysql -utest -p
这样mysql
会类似sudo
提示输入密码,这样不会显示你的密码,更安全
注意,-u
后面不一定要紧跟用户名,可以-u test
,但-p
一定要紧跟密码,不能-p 123456
,因为mysql
接一个数据库名称(例如rm -r test
中test
不是-r
的参数,是文件夹名称),例如mysql -u test -p123456 test
就打开test
数据库。
如须远程登录请用mysql -h ip -u test -p
,前提是装有mysql
的计算机上已经启动了mysql
服务(使用命令service mysql start
来启动),并且已经经过了相关配置(编辑文件/etc/mysql/mysql.conf.d/mysqld.cnf
,找到bind-address = 127.0.0.1
一行,把127.0.0.1
改为0.0.0.0
,service mysql restart
重启mysql
服务即可)
二、sql
查询命令
sql
命令有很多,但都很容易理解,因为sql
的设计很符合英文习惯
1.SELECT
查询
SELECT * FROM test WHERE id = 4;
,意思是从test
这张表中选取(查找)id=4
的记录,语法是
SELECT columns FROM table (WHERE condition);
括号中的内容可以省略(即没有条件,全部输出)
columns
是指列名,table
指表名,稍后会讲到。注,sql
语句不区分大小写,以下查询成立
sEleCt * fRoM test
,但数据库名、表名、列表不能改变,例如
sEleCt * fRoM tESt
,会提示找不到test.tESt
这张表。
2.CREATE
创建数据库、表以及表的路径
CREATE DATABASE test;
创建数据库test
,mysql
可以包含多个数据库,一个数据库可以包含多个表,一个表可以包含多个列,并添加多条记录。
USE test;
进入数据库test
,相当于cd
进入文件夹
CREATE TABLE test;
创建空表test
CREATE TABLE test ( columns );
创建含有列columns
的表
表的路径类似文件系统,可以是/test/test
,如果当前路径为/test/
,也可以是./test
,数据库也一样,可以通过test.test
访问该表,也可以USE test;
之后用test
访问该表。
3.列的属性
列的描述一般是name type allow_null default primary_key auto_increment
name
列名一定要排在第一个,其他属性可以任意顺序排在后面
type
类型名,例如INT
、CHAR
、VARCHAR
、BOOLEAN
、TEXT
、TIMESTAMP
allow_null
是否允许为空(mysql
中的NULL
,与0和空字符串不等价),允许用NULL
,不允许用NOT NULL
,默认允许
default
默认值,default xxx
表示默认为xxx
,默认默认值为NULL
,TEXT
类型不能有默认值
primary_key
主键,表示鉴别记录的id
,该表的每一条记录的主键值必须不同,用primary key
表示该列是主键,默认不是,每个表最多有一个主键
auto_increment
自增,表示该列自动增长(仅限于整形),即每次添加记录自动加1,每个表只能有一个,必须是一种键(上面讲的主键或者外部键foreign key
)
下面举一个创建表的例子,
mysql> CREATE TABLE test(
-> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> content VARCHAR(5) NOT NULL DEFAULT 'hello'
-> );
Query OK, 0 rows affected (0.09 sec)
最后一行是输出,mysql
的输入以mysql>
或>
打头
在mysql
中,任何换行都可以被忽略(除了字符串中的换行被视作字符串中的换行符),并开启新的一行,在字符串外直到输入;
或\g
使用SHOW FULL COLUMNS FROM test;
查看表的信息
mysql> SHOW FULL COLUMNS FROM test;
+---------+---------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+---------+---------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| content | text | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | |
+---------+---------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
2 rows in set (0.02 sec)
Field
是指列名,Type
是指类型,Collation
是指编码,一般默认latin1_swdish_ci
(只有字符串类型才有编码),Null
指是否允许为空,Key
指什么类型的键,PRI
指主键,Default
指默认值,EXTRA
值其他的标注,Privileges
指用户的权限(后三种操作后面会讲),Comment
指注释(使用comment xxx
添加注释,例如id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'This is a primary key.'
)
mysql> SELECT * FROM test;
Empty set (0.00 sec)
现在test
是一张空表,那么怎么插入数据呢?
4.INSERT
插入数据
mysql> INSERT INTO test (content) VALUES ('This is the first message.');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test (content) VALUES
-> ('This is the second message.'),
-> ('This is the third message.');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+----+-----------------------------+
| id | content |
+----+-----------------------------+
| 1 | This is the first message. |
| 2 | This is the second message. |
| 3 | This is the third message. |
+----+-----------------------------+
3 rows in set (0.00 sec)
mysql> SELECT content FROM test;
+-----------------------------+
| content |
+-----------------------------+
| This is the first message. |
| This is the second message. |
| This is the third message. |
+-----------------------------+
3 rows in set (0.00 sec)
INSERT INTO table_name (columns) VALUES (messages),...;
columns
就是要插入记录的列名(可以有多个,用逗号隔开)
messages
就是要插入记录每一项的值,与列名对应
...
指可以同时插入多条记录,用逗号隔开
5.UPDATE
修改记录
mysql> UPDATE test SET content = 'First' WHERE id = 1;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM test;
+----+-----------------------------+
| id | content |
+----+-----------------------------+
| 1 | First |
| 2 | This is the second message. |
| 3 | This is the third message. |
+----+-----------------------------+
3 rows in set (0.01 sec)
UPDATE table_name SET column_name = value (WHERE condition);
不用解释了吧,和前面的差不多。
6.ALTER
修改表的信息
mysql> ALTER TABLE test ADD COLUMN author VARCHAR(20) NOT NULL DEFAULT 'Anonymous';
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+----+-----------------------------+-----------+
| id | content | author |
+----+-----------------------------+-----------+
| 1 | First | Anonymous |
| 2 | This is the second message. | Anonymous |
| 3 | This is the third message. | Anonymous |
+----+-----------------------------+-----------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE test DROP COLUMN author;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+----+-----------------------------+
| id | content |
+----+-----------------------------+
| 1 | First |
| 2 | This is the second message. |
| 3 | This is the third message. |
+----+-----------------------------+
3 rows in set (0.00 sec)
上面这段测试代码用到了ADD COLUMN
和DROP COLUMN
,意思分别是添加列和删除列
ADD COLUMN
默认在末尾添加,可以用FIRST
指定为第一个,或用AFTER
或BEFORE
来指定
例如AFTER COLUMN id
就是id
和content
之间添加
mysql> ALTER TABLE test ADD COLUMN author VARCHAR(20) NOT NULL DEFAULT 'Anonymous',DROP COLUMN id;
Query OK, 3 rows affected (0.14 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+-----------------------------+-----------+
| content | author |
+-----------------------------+-----------+
| First | Anonymous |
| This is the second message. | Anonymous |
| This is the third message. | Anonymous |
+-----------------------------+-----------+
3 rows in set (0.01 sec)
使用逗号来分割两个操作
mysql> SELECT * FROM test;
+-----------------------------+-----------+
| content | author |
+-----------------------------+-----------+
| First | Anonymous |
| This is the second message. | Anonymous |
| This is the third message. | Anonymous |
+-----------------------------+-----------+
3 rows in set (0.01 sec)
mysql> ALTER TABLE test MODIFY author VARCHAR(20) NOT NULL DEFAULT 'No author.';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+-----------------------------+-----------+
| content | author |
+-----------------------------+-----------+
| First | Anonymous |
| This is the second message. | Anonymous |
| This is the third message. | Anonymous |
+-----------------------------+-----------+
3 rows in set (0.01 sec)
使用MODIFY
进行不改列名但改属性的操作,并且要保证原纪录不变(可以转换适应新的列)
使用CHANGE
替换列名(纪录仍不变)
mysql> ALTER TABLE test CHANGE author writer VARCHAR(20) NOT NULL DEFAULT 'No author.';
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+-----------------------------+-----------+
| content | writer |
+-----------------------------+-----------+
| First | Anonymous |
| This is the second message. | Anonymous |
| This is the third message. | Anonymous |
+-----------------------------+-----------+
3 rows in set (0.00 sec)
使用CHARACTER SET
更改编码
mysql> ALTER TABLE test CHARACTER SET 'utf8';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW FULL COLUMNS FROM test;
+---------+-------------+-------------------+------+-----+------------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+---------+-------------+-------------------+------+-----+------------+-------+---------------------------------+---------+
| content | text | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | |
| writer | varchar(20) | latin1_swedish_ci | NO | | No author. | | select,insert,update,references | |
+---------+-------------+-------------------+------+-----+------------+-------+---------------------------------+---------+
2 rows in set (0.01 sec)
mysql> ALTER TABLE test ADD COLUMN newc TEXT;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW FULL COLUMNS FROM test;
+---------+-------------+-------------------+------+-----+------------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+---------+-------------+-------------------+------+-----+------------+-------+---------------------------------+---------+
| content | text | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | |
| writer | varchar(20) | latin1_swedish_ci | NO | | No author. | | select,insert,update,references | |
| newc | text | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+---------+-------------+-------------------+------+-----+------------+-------+---------------------------------+---------+
3 rows in set (0.00 sec)
CHARACTER SET
只改变默认的编码,不改变已有列的编码
CREATE TABLE table_name (...) CHARACTER SET 'utf8';
在创建表时设置
mysql> CREATE TABLE t (id INT) CHARACTER SET 'utf8';
Query OK, 0 rows affected (0.06 sec)
mysql> ALTER TABLE test CONVERT TO CHARACTER SET 'utf8';
Query OK, 3 rows affected (0.13 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SHOW FULL COLUMNS FROM test;
+---------+-------------+-----------------+------+-----+------------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+---------+-------------+-----------------+------+-----+------------+-------+---------------------------------+---------+
| content | mediumtext | utf8_general_ci | NO | | NULL | | select,insert,update,references | |
| writer | varchar(20) | utf8_general_ci | NO | | No author. | | select,insert,update,references | |
| newc | text | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+---------+-------------+-----------------+------+-----+------------+-------+---------------------------------+---------+
3 rows in set (0.00 sec)
如须更改已有列的编码类型,加上CONVERT TO
,单独改一列请用CHANGE
或MODIFY
,如下
mysql> ALTER TABLE test MODIFY content TEXT NOT NULL CHARSET 'utf8';
Query OK, 3 rows affected (0.11 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SHOW FULL COLUMNS FROM test;
+---------+-------------+-----------------+------+-----+------------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+---------+-------------+-----------------+------+-----+------------+-------+---------------------------------+---------+
| content | text | utf8_general_ci | NO | | NULL | | select,insert,update,references | |
| writer | varchar(20) | utf8_general_ci | NO | | No author. | | select,insert,update,references | |
| newc | text | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+---------+-------------+-----------------+------+-----+------------+-------+---------------------------------+---------+
3 rows in set (0.00 sec)
在列中使用CHARSET
指定,在表中使用CHARACTER SET
指定
7.FOREIGN KEY
外键
mysql> CREATE TABLE clients (
-> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20) NOT NULL
-> ) ENGINE = 'InnoDB';
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO clients (name) VALUES ('Bob'),('Alice'),('Jack');
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM clients;
+----+-------+
| id | name |
+----+-------+
| 1 | Bob |
| 2 | Alice |
| 3 | Jack |
+----+-------+
3 rows in set (0.00 sec)
mysql> CREATE TABLE requests (
-> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> cid INT NOT NULL, FOREIGN KEY (cid) REFERENCES clients (id),
-> req VARCHAR(100) NOT NULL
-> ) ENGINE = 'InnoDB';
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO requests (cid,req) VALUES (1,'I want a cup of tea.'),(2,'I want a cup of coffee');
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO requests (cid,req) VALUES (4,'I does not exist.');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`requests`, CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `clients` (`id`))
mysql> SELECT * FROM requests;
+----+-----+------------------------+
| id | cid | req |
+----+-----+------------------------+
| 1 | 1 | I want a cup of tea. |
| 2 | 2 | I want a cup of coffee |
+----+-----+------------------------+
2 rows in set (0.01 sec)
这里创建了两张表(clients
和requests
),分别代表客户信息和客户需求,显然一个客户可以有多个req
,但只有一个id
,这个时候我们需要一个一对多的关系,外键可以帮我们解决这个问题。如上所述使用外键,引擎一定要是InnoDB
,不然不支持外键。外键可以提供一个约束,request.cid
一定要在clients.id
中出现,如果添加在限制外的cid
,就会报错(如测试)。
mysql> DELETE FROM clients WHERE id = 3;
Query OK, 1 row affected (0.05 sec)
mysql> DELETE FROM clients WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`requests`, CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `clients` (`id`))
这里DELETE
就是删除记录的意思,很显然,id=3
的记录(Jack
的记录)直接删除,但id=1
(Bob
的记录)没有被删除,原因是request.cid
引用了这一条记录。所以外键是互相约束的。如果要取消这种约束(比如客户注销了,就删除他的req
),只需要在定义外键时加上ON DELETE CASCADE ON UPDATE CASCADE
,即
FOREIGN KEY (cid) REFERENCES clients (id) ON DELETE CASCADE ON UPDATE CASCADE
如果需要修改已创建的表,使用如下代码
mysql> ALTER TABLE requests DROP FOREIGN KEY requests_ibfk_1;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE requests ADD FOREIGN KEY (cid) REFERENCES clients (id) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 2 rows affected (0.12 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> DELETE FROM clients WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM clients;
+----+-------+
| id | name |
+----+-------+
| 2 | Alice |
+----+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM requests;
+----+-----+------------------------+
| id | cid | req |
+----+-----+------------------------+
| 2 | 2 | I want a cup of coffee |
+----+-----+------------------------+
1 row in set (0.00 sec)
主要是一下两段代码
ALTER TABLE requests DROP FOREIGN KEY requests_ibfk_1;
ALTER TABLE requests ADD FOREIGN KEY (cid) REFERENCES clients (id) ON DELETE CASCADE ON UPDATE CASCADE;
那么有人会好奇,requests_ibfk_1
是啥,我没有创建这样一列啊?其实mysql
对每个外键的约束都有保存,这就是约束的名称,也可以自定义,例如
mysql> CREATE TABLE reqs (cid INT NOT NULL, CONSTRAINT ibfk FOREIGN KEY (cid) REFERENCES clients (id));
Query OK, 0 rows affected (0.07 sec)
mysql> ALTER TABLE reqs DROP FOREIGN KEY ibfk;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
当然,在报错信息中,我们也可以找到约束名称
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`requests`, CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `clients` (`id`))
最后值得注意的是,外键引用的一定是主键,类型一定要一致或可以转化。
8.DELETE
、TRUNCATE
、DROP
和反引号
DELETE FROM table_name (WHERE condition);
从table_name
中删除满足condition
的记录,如果不加WHERE
子句就删除全部记录(是不是很恐怖,万一一下子忘加了就完了,所以要备份数据库,稍后会讲)
TRUNCATE table_name
,清空表table_name
,与DELETE
不同的是删除的不仅是记录,还有一些相关信息,例如auto_increment
自增数(即下一个插入记录时对自增列的填充数,每次会加1),但表还在(类似重新创建这张表)
DROP TABLE table_name
,删除表table_name
DROP DATABASE database_name
,删除库database_name
在mysql
中,反引号被用作标识库名、表名、列名(不加不要紧,看到报错信息或者网上一些文章加了反引号能看懂就行)
9.时间日期
mysql
中提供一些变量和函数来获取当前时间和日期
mysql> SELECT CURRENT_TIME;
+--------------+
| CURRENT_TIME |
+--------------+
| 01:59:27 |
+--------------+
1 row in set (0.01 sec)
mysql> SELECT CURRENT_TIME();
+----------------+
| CURRENT_TIME() |
+----------------+
| 01:59:33 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
| 2019-02-20 |
+--------------+
1 row in set (0.01 sec)
mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2019-02-20 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP |
+---------------------+
| 2019-02-20 02:00:21 |
+---------------------+
1 row in set (0.01 sec)
mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2019-02-20 02:00:27 |
+---------------------+
1 row in set (0.00 sec)
每个变量的意思我就不说,看结果自然就明白了,一般使用CURRENT_TIMESTAMP
,看代码
mysql> CREATE TABLE timetest (
-> cur_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP()
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> INSERT INTO timetest () VALUES ();
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO timetest () VALUES ();
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO timetest () VALUES ();
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM timetest;
+---------------------+
| cur_time |
+---------------------+
| 2019-02-20 02:14:05 |
| 2019-02-20 02:14:08 |
| 2019-02-20 02:14:12 |
+---------------------+
3 rows in set (0.00 sec)
为了方便阅读,我在输入三次INSERT
之间间隔了几秒
10.ALTER
更改表的信息
之前我们用ALTER
改表的列(也叫字段),现在我们用ALTER
改其他的信息
mysql> ALTER TABLE timetest ENGINE = 'InnoDB';
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE timetest CHARACTER SET 'utf8';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE timetest CHARSET = 'utf8';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE timetest AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
一般来说,使用property_name = value
来更改属性,特别地,可以用CHARACTER SET code
来代替CHARSET = code
,这个在第一次讲ALTER
时提到过
这里特别讲一下auto_increment
自增数,举个例子
mysql> CREATE TABLE test ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT );
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO test () VALUES (),(),();
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
mysql> DELETE FROM test WHERE id = 3;
Query OK, 1 row affected (0.04 sec)
mysql> SELECT * FROM test;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
mysql> INSERT INTO test () VALUES ();
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM test;
+----+
| id |
+----+
| 1 |
| 2 |
| 4 |
+----+
3 rows in set (0.00 sec)
在删除了表中的一条记录后,自增数没有减为3,而是继续添加id=4
,这个时候
可以用一下代码将自增数调为3
mysql> DELETE FROM test WHERE id = 4;
Query OK, 1 row affected (0.04 sec)
mysql> ALTER TABLE test AUTO_INCREMENT = 3;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> INSERT INTO test () VALUES ();
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
首先先将id=4
这条记录删去,用于模拟添加这条记录前的状态
然后会发现,在改了自增数后,新增的记录变成id=3
了
11.判断是否需要TABLE
或DATABASE
标识
例如
SELECT * FROM test;
UPDATE test ...;
DROP TABLE test;
DROP DATABASE test;
ALTER TABLE test ...;
ALTER DATABASE test ...;
其中ALTER DATABASE
和ALTER TABLE
差不多,提供ALTER DATABASE xxx CHARSET = code
或ALTER DATABASE xxx CHARACTER SET code
,更改数据库的默认编码。
观察发现,凡是不要加TABLE
或DATABASE
的都只能加表名或数据库名,比如SELECT
,只能从表中SELECT
,不能从库中SELECT
。
而加上的两种情况都有,比如ALTER
既可以加表也可以加数据库,如果使用ALTER test ...
,mysql
就不知道到底是要修改test
表还是test
数据库了。
所以结论是,能引起歧义的需要加上,不引起歧义就不用。
12.SHOW
和mysql
类型
SHOW TABLES
和SHOW DATABASES
,不会不理解吧,显示所有表和数据库,下面开始讲mysql
类型,以下是常用的类型。
整型 BIT TINYINT SMALLINT INT BIGINT
字符串 CHAR VARCHAR TEXT
布尔 BOOLEAN(TINYINT(1))
时间 DATE DATETIME TIMESTAMP
对于一种整型,在后面加括号表示长度,例如TINYINT(1)
表示长度为1的数,单位是byte
BOOLEAN
在mysql
中被视作TINYINT(1)
,TRUE
为1,FALSE
为0(可以使用TRUE
或FALSE
标识,但mysql
内部保存的和显示的都是0或1)
13.字符串类型辨析
CHAR
必须使用CHAR(n)
,表示字符串长度为n
(含有n
个字符),存储长度为n
,存储时自动在字符串后填充空格,查询时自动删除结尾的空格,所以查询结果可能无法保留数据末尾的空格。
VARCHAR
必须使用VARCHAR(n)
,表示字符串长度为n
(在4.x
版本及以前,n
表示保存数据实际长度,对于非英文单词,例如utf-8
中的中文,一个字符占几个byte
,有时候长度没到n
也会报错;对于5.x
版本及更高版本,n
表示字符数),存储长度为字符串数据长度+1,因为存储方式与CHAR
不同,采用1个byte
存储字符串实际长度,所以需要+1。
TEXT
禁止使用TEXT(n)
,禁止使用DEFAULT
,可以输入任意长度的字符串。
三、备份mysql
数据库
使用mysqldump
来备份数据库,在mysql-client
包中已经安装
$ mysqldump -utest -p test >test.sql
会提示输入密码,输完后就会将test
数据库的内容输出,重定向至test.sql
打开test.sql
,你会看到许多sql
语句,mysql
通过执行那些语句就可以还原
$ mysql -utest -p test <test.sql
注意这里是用mysql
而不是mysqldump
,因为执行者是mysql
四、使用php5.6+mysql
安装过程见Ubuntu18 搭建apache2+php5.6+mysql服务器(和Apache2
有关的不需要)
1.创建连接
$conn = mysql_connect($hostname,$username,$password);
$conn
就是创建连接的id
,之后的操作都要用到它。
2.执行sql
语句
$query = mysql_query($sql,$conn);
$query
是执行后返回数据的id
,查询结果会用到它。$conn
可以省略,默认是最新的连接。
如果$query
为false
,那么说明查询失败,可以用mysql_error()
查询操作的错误信息
3.获取返回信息
如果查询含有返回信息(例如SELECT
语句),那么可以用mysql_fetch_array
来获取
while ($row = mysql_fetch_array($query)){...}
mysql_fetch_array
每次只返回一行(一条记录),直到所有记录都输出了才返回false
,事实上还有第二个参数表示返回数组的类型,可选的值有
MYSQL_ASSOC 关联数组
MYSQL_NUM 数字数组
MYSQL_BOTH 上述两个合并后的数组
一般使用关联数组,键就是列(字段)名称。
4.获取操作信息
mysql_affected_rows($conn) 获取最新查询影响行数,默认是最近的连接
mysql_error($conn) 获取最新查询的错误信息,默认是最近的连接
mysql_info($conn) 获取最新查询返回的信息,默认是最近的连接
5.关闭连接
mysql_close($conn)
其余许多的php mysql
函数请见PHP MYSQL 函数。
下面做几个测试
<?php
$host = 'localhost';
$user = 'test';
$pass = '123456';
$conn = mysql_connect($host,$user,$pass);
function sql($m_conn, $m_sql){
mysql_query($m_sql,$m_conn);
$rows = mysql_affected_rows();
$error = mysql_error();
$info = mysql_info();
echo "sql: $m_sql
affected: $rows
error: $error
info: $info
";
}
sql($conn,'CREATE DATABASE php_test');
sql($conn,'USE DATABASE php_test');
sql($conn,'CREATE TABLE test ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT )');
sql($conn,'INSERT INTO test () VALUES (),(),()');
sql($conn,'ALTER TABLE test ADD COLUMN newc INT NOT NULL DEFAULT 0');
sql($conn,'SELECT * FROM test');
sql($conn,'DROP TABLE test');
sql($conn,'DROP DATABASE php_test');
mysql_close($conn);
执行php test.php
,输出为
sql: CREATE DATABASE php_test
affected: -1
error: Can't create database 'php_test'; database exists
info:
sql: USE DATABASE php_test
affected: -1
error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE php_test' at line 1
info:
sql: USE php_test
affected: 0
error:
info:
sql: CREATE TABLE test ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT )
affected: 0
error:
info:
sql: INSERT INTO test () VALUES (),(),()
affected: 3
error:
info: Records: 3 Duplicates: 0 Warnings: 0
sql: ALTER TABLE test ADD COLUMN newc INT NOT NULL DEFAULT 0
affected: 0
error:
info: Records: 0 Duplicates: 0 Warnings: 0
sql: SELECT * FROM test
affected: 3
error:
info:
sql: DROP TABLE test
affected: 0
error:
info:
sql: DROP DATABASE php_test
affected: 0
error:
info:
五、phpMyAdmin
远程图形化管理mysql
在Ubuntu
中,使用apt install phpmyadmin
安装phpmyadmin
,自动安装到/usr/share
下的phpmyadmin
文件夹,文件夹中是使用php7
编写的远程mysql
管理,使用ln
链接到网站根目录下。
apt install phpmyadmin # 安装phpmyadmin文件
apt install php7.2 # phpmyadmin使用php7.2
cd /var/www/html # /var/www/html是网站根目录,有时候是/var/www
ln -s /usr/share/phpmyadmin # /etc/phpmyadmin是phpmyadmin的文件夹
访问网站下的phpmyadmin
目录,你会看到类似下图的页面
输入mysql
的密码,就可以登录进入图形化管理界面,其中设有控制台,可以输入命令。
六、sql
漏洞
凡是使用数据库就不得不修复sql
漏洞,这个漏洞并不是由于数据库有bug
,而是黑客利用自动查询前不检查字符串内容,使用反嵌套语句获得隐私信息的漏洞,这种漏洞修复也很简单,详见sqlmap 进行sql注入,这篇文章是写如何利用sql
漏洞的,顺带提了几句sql
漏洞是什么和如何修复,其他部分可以不用阅读(如果感兴趣也不妨读一读,不过不要拿来干非法的事情,不要在有漏洞的网站上搞破坏)。
希望本文对大家有所帮助。