MySQL如何使用

大部分网站都要用到数据库,其中较为常见的是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 testtest不是-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.0service 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;创建数据库testmysql可以包含多个数据库,一个数据库可以包含多个表,一个表可以包含多个列,并添加多条记录。
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类型名,例如INTCHARVARCHARBOOLEANTEXTTIMESTAMP
allow_null是否允许为空(mysql中的NULL,与0和空字符串不等价),允许用NULL,不允许用NOT NULL,默认允许
default默认值,default xxx表示默认为xxx,默认默认值为NULLTEXT类型不能有默认值
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 COLUMNDROP COLUMN,意思分别是添加列和删除列
ADD COLUMN默认在末尾添加,可以用FIRST指定为第一个,或用AFTERBEFORE来指定
例如AFTER COLUMN id就是idcontent之间添加

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,单独改一列请用CHANGEMODIFY,如下

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)

这里创建了两张表(clientsrequests),分别代表客户信息和客户需求,显然一个客户可以有多个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.DELETETRUNCATEDROP和反引号

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.判断是否需要TABLEDATABASE标识

例如

SELECT * FROM test;
UPDATE test ...;
DROP TABLE test;
DROP DATABASE test;
ALTER TABLE test ...;
ALTER DATABASE test ...;

其中ALTER DATABASEALTER TABLE差不多,提供ALTER DATABASE xxx CHARSET = codeALTER DATABASE xxx CHARACTER SET code,更改数据库的默认编码。
观察发现,凡是不要加TABLEDATABASE的都只能加表名或数据库名,比如SELECT,只能从表中SELECT,不能从库中SELECT
而加上的两种情况都有,比如ALTER既可以加表也可以加数据库,如果使用ALTER test ...mysql就不知道到底是要修改test表还是test数据库了。
所以结论是,能引起歧义的需要加上,不引起歧义就不用。

12.SHOWmysql类型

SHOW TABLESSHOW DATABASES,不会不理解吧,显示所有表和数据库,下面开始讲mysql类型,以下是常用的类型。

整型	BIT TINYINT SMALLINT INT BIGINT 
字符串	CHAR VARCHAR TEXT
布尔	BOOLEAN(TINYINT(1))
时间	DATE DATETIME TIMESTAMP

对于一种整型,在后面加括号表示长度,例如TINYINT(1)表示长度为1的数,单位是byte
BOOLEANmysql中被视作TINYINT(1)TRUE为1,FALSE为0(可以使用TRUEFALSE标识,但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可以省略,默认是最新的连接。
如果$queryfalse,那么说明查询失败,可以用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目录,你会看到类似下图的页面
phpmyadmin
输入mysql的密码,就可以登录进入图形化管理界面,其中设有控制台,可以输入命令。

六、sql漏洞

凡是使用数据库就不得不修复sql漏洞,这个漏洞并不是由于数据库有bug,而是黑客利用自动查询前不检查字符串内容,使用反嵌套语句获得隐私信息的漏洞,这种漏洞修复也很简单,详见sqlmap 进行sql注入,这篇文章是写如何利用sql漏洞的,顺带提了几句sql漏洞是什么和如何修复,其他部分可以不用阅读(如果感兴趣也不妨读一读,不过不要拿来干非法的事情,不要在有漏洞的网站上搞破坏)。

希望本文对大家有所帮助。

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值