mysql 1067 abouting_mysql学习笔记！

最新推荐文章于 2021-03-18 09:06:34 发布

CRomputer-罗军

最新推荐文章于 2021-03-18 09:06:34 发布

阅读量92

点赞数

文章标签： mysql 1067 abouting

本文链接：https://blog.csdn.net/weixin_28709649/article/details/113418834

版权

好久没有整理东西，感觉自己越来越懒，正好近期有个MYSQL的项目(一个ORACLE的应用迁移到MYSQL)，以前没有接触过MYSQL，但是也知道MYSQL数据库目前广泛的应用在各种个人、商务系统中，各种技术都比较成熟。把自己学习的一些过程总结一下，该文章设计到的内容都没有做太详细的阐述，只是一个简单的入门手册，如果想看更多内容可以参看MYSQL的联机手册。

1 MYSQL安装

先下载安装包：

mysql-5.0.27-win32.zip

mysql-noinstall-6.0.0-alpha-win32.zip

下载了2个版本：一个5.0.27安装版；一个6.0.0非安装版。本人测试用的为5.0.27安装版，安装过程不再赘述。各个操作系统平台的安装可以看MYSQL联机文档。

2 登录及一些基本操作

本章的主要目的是让我们对MYSQL的基础框架有个大概的了解。

1 连接与断开MYSQL服务器

安装完毕登陆MYSQL(有过一些其他数据库基础的人都应该很容易使用这几步)：

我们可以利用如下参数查看MYSQL命令的帮助：

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql –help

联接MYSQL服务器：

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -h localhost -uroot -p888888

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 22 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| root |

+--------------------+

3 rows in set (0.08 sec)

安装完毕，都会有这几个默认的数据库。

注意到目前没有连接到任何数据库。

mysql> SELECT DATABASE();

+------------+

| database() |

+------------+

| NULL |

+------------+

1 row in set (0.78 sec)

mysql> QUIT

Bye

我们也可以在连接MYSQL服务器的时候指定想要连接的数据库，如下：

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql --user=root -p mysql

Enter password: ******

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 7 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select database();

+------------+

| database() |

+------------+

| mysql |

+------------+

1 row in set (0.00 sec)

INFORMATION_SCHEMA数据库

我们在介绍MYSQL基本操作的同时顺便把INFORMATION_SCHEM数据库做个简单介绍：

类似其他数据库的数据字典，各个字典含义不做详述，以下摘自MYSQL联机文档：

INFORMATION_SCHEMA提供了访问数据库元数据的方式。

元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和

“系统目录”。INFORMATION_SCHEMA是信息数据库，其中保存着关于MySQL服务器所维护的所有其他数据库的信息。在INFORMATION_SCHEMA

中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。

2 使用数据库

mysql> USE INFORMATION_SCHEMA;

Database changed

mysql> SELECT DATABASE();

+--------------------+

| database() |

+--------------------+

| information_schema |

+--------------------+

1 row in set (0.00 sec)

mysql> SELECT VERSION(), CURRENT_DATE, CURDATE(), NOW(), USER();

+---------------------+--------------+------------+---------------------+-------

---------+

+---------------------+--------------+------------+---------------------+-------

---------+

| 5.0.27-community-nt | 2007-05-24 | 2007-05-24 | 2007-05-24 17:01:16 | root@l

ocalhost |

+---------------------+--------------+------------+---------------------+-------

---------+

1 row in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'version';

+---------------+---------------------+

| Variable_name | Value |

+---------------+---------------------+

| version | 5.0.27-community-nt |

+---------------+---------------------+

1 row in set (0.13 sec)

3 MYSQL的SHOW命令

前边的例子中我们已经用过了MYSQL的SHOW命令:

mysql> SHOW DATABASES;

SHOW命令可用于获取关于INFORMATION_SCHEMA本身结构的信息。

一些SHOW语句允许使用FROM、WHERE子句，这样，在指定需要显示的行时，可更为灵活。下边给出部分例子：

mysql> SHOW TABLES FROM MYSQL;

+---------------------------+

| Tables_in_mysql |

+---------------------------+

| columns_priv |

| db |

| func |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| host |

| proc |

| procs_priv |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

+---------------------------+

17 rows in set (0.00 sec)

mysql> SHOW TABLES;

+---------------------------------------+

| Tables_in_information_schema |

+---------------------------------------+

| CHARACTER_SETS |

| COLLATIONS |

| COLLATION_CHARACTER_SET_APPLICABILITY |

| COLUMNS |

| COLUMN_PRIVILEGES |

| KEY_COLUMN_USAGE |

| ROUTINES |

| SCHEMATA |

| SCHEMA_PRIVILEGES |

| STATISTICS |

| TABLES |

| TABLE_CONSTRAINTS |

| TABLE_PRIVILEGES |

| TRIGGERS |

| USER_PRIVILEGES |

| VIEWS |

+---------------------------------------+

16 rows in set (0.00 sec)

SHOW TABLES命令显示了当前用数据库中的数据库对象列表，而从TABLES视图的查询我们将得到所有数据库下的对象列表。这个例子就是给出了一个查询MYSQL的表相关的系统视图，类似ORACLE中的(DBA_TABLES、USER_TABLES)和SYBASE中的SYSOBJECTS。

mysql> SELECT TABLE_NAME, TABLE_TYPE, ENGINE FROM TABLES;

+---------------------------------------+-------------+--------+

| table_name | table_type | engine |

+---------------------------------------+-------------+--------+

| CHARACTER_SETS | SYSTEM VIEW | MEMORY |

| COLLATIONS | SYSTEM VIEW | MEMORY |

| COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | MEMORY |

| COLUMNS | SYSTEM VIEW | MyISAM |

| COLUMN_PRIVILEGES | SYSTEM VIEW | MEMORY |

| KEY_COLUMN_USAGE | SYSTEM VIEW | MEMORY |

| ROUTINES | SYSTEM VIEW | MyISAM |

| SCHEMATA | SYSTEM VIEW | MEMORY |

| SCHEMA_PRIVILEGES | SYSTEM VIEW | MEMORY |

| STATISTICS | SYSTEM VIEW | MEMORY |

| TABLES | SYSTEM VIEW | MEMORY |

| TABLE_CONSTRAINTS | SYSTEM VIEW | MEMORY |

| TABLE_PRIVILEGES | SYSTEM VIEW | MEMORY |

| TRIGGERS | SYSTEM VIEW | MyISAM |

| USER_PRIVILEGES | SYSTEM VIEW | MEMORY |

| VIEWS | SYSTEM VIEW | MyISAM |

| columns_priv | BASE TABLE | MyISAM |

| db | BASE TABLE | MyISAM |

| func | BASE TABLE | MyISAM |

| help_category | BASE TABLE | MyISAM |

| help_keyword | BASE TABLE | MyISAM |

| help_relation | BASE TABLE | MyISAM |

| help_topic | BASE TABLE | MyISAM |

| host | BASE TABLE | MyISAM |

| proc | BASE TABLE | MyISAM |

| procs_priv | BASE TABLE | MyISAM |

| tables_priv | BASE TABLE | MyISAM |

| time_zone | BASE TABLE | MyISAM |

| time_zone_leap_second | BASE TABLE | MyISAM |

| time_zone_name | BASE TABLE | MyISAM |

| time_zone_transition | BASE TABLE | MyISAM |

| time_zone_transition_type | BASE TABLE | MyISAM |

| user | BASE TABLE | MyISAM |

+---------------------------------------+-------------+--------+

33 rows in set (0.03 sec)

mysql> SHOW COLUMNS FROM TABLES;

+-----------------+--------------+------+-----+---------+-------+

+-----------------+--------------+------+-----+---------+-------+

+-----------------+--------------+------+-----+---------+-------+

21 rows in set (0.06 sec)

mysql> SHOW CHARACTER SET;

+----------+-----------------------------+---------------------+--------+

+----------+-----------------------------+---------------------+--------+

| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |

| dec8 | DEC West European | dec8_swedish_ci | 1 |

| cp850 | DOS West European | cp850_general_ci | 1 |

| hp8 | HP West European | hp8_english_ci | 1 |

| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |

| tis620 | TIS620 Thai | tis620_thai_ci | 1 |

| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |

| cp1250 | Windows Central European | cp1250_general_ci | 1 |

| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |

| cp866 | DOS Russian | cp866_general_ci | 1 |

| cp852 | DOS Central European | cp852_general_ci | 1 |

| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |

| cp1256 | Windows Arabic | cp1256_general_ci | 1 |

| cp1257 | Windows Baltic | cp1257_general_ci | 1 |

| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |

+----------+-----------------------------+---------------------+--------+

36 rows in set (0.00 sec)

mysql> SHOW CHARACTER SET like 'big5';

+---------+--------------------------+-------------------+--------+

+---------+--------------------------+-------------------+--------+

| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |

+---------+--------------------------+-------------------+--------+

1 row in set (0.00 sec)

mysql> SELECT * FROM COLLATIONS WHERE COLLATION_NAME LIKE '%big5%';

+-----------------+--------------------+----+------------+-------------+--------

+-----------------+--------------------+----+------------+-------------+--------

| big5_chinese_ci | big5 | 1 | Yes | Yes | 1

| big5_bin | big5 | 84 | | Yes | 1

+-----------------+--------------------+----+------------+-------------+--------

2 rows in set (0.00 sec)

mysql> SHOW GRANTS;

+-------------------------------------------------------------------------------

---------------------------------------------------------+

| Grants for root@localhost

+-------------------------------------------------------------------------------

---------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*DA2

8842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB' WITH GRANT OPTION |

+-------------------------------------------------------------------------------

---------------------------------------------------------+

1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR ROOT;

+-------------------------------------------------------------------------------

--------------------------------------------------------------------------------

-------+

| Grants for root@%

+-------------------------------------------------------------------------------

--------------------------------------------------------------------------------

-------+

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,

FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,

LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'%

' IDENTIFIED BY PASSWORD '*DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB' WITH GRANT

OPTION |

+-------------------------------------------------------------------------------

--------------------------------------------------------------------------------

-------+

1 row in set (0.00 sec)

我们也可以通过查询系统表来获得用户的权限：

mysql> SELECT * FROM USER_PRIVILEGES;

下边给出了MYSQL的权限列表功参考：

Privilege Meaning

ALL [PRIVILEGES] Sets all simple privileges except GRANT OPTION

ALTER Enables use of ALTER TABLE

ALTER ROUTINE Enables stored routines to be altered or dropped

CREATE Enables use of CREATE TABLE

CREATE ROUTINE Enables creation of stored routines

CREATE TEMPORARY TABLES Enables use of CREATE TEMPORARY TABLE

CREATE USER Enables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.

CREATE VIEW Enables use of CREATE VIEW

DELETE Enables use of DELETE

DROP Enables use of DROP TABLE

EXECUTE Enables the user to run stored routines

FILE Enables use of SELECT ... INTO OUTFILE and LOAD DATA INFILE

INDEX Enables use of CREATE INDEX and DROP INDEX

INSERT Enables use of INSERT

LOCK TABLES Enables use of LOCK TABLES on tables for which you have the SELECT privilege

PROCESS Enables use of SHOW FULL PROCESSLIST

REFERENCES Not implemented

RELOAD Enables use of FLUSH

REPLICATION CLIENT Enables the user to ask where slave or master servers are

REPLICATION SLAVE Needed for replication slaves (to read binary log events from the master)

SELECT Enables use of SELECT

SHOW DATABASES SHOW DATABASES shows all databases

SHOW VIEW Enables use of SHOW CREATE VIEW

SHUTDOWN Enables use of mysqladmin shutdown

SUPER Enables use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the mysqladmin debug command; allows you to connect (once) even if max_connections is reached

UPDATE Enables use of UPDATE

USAGE Synonym for “no privileges”

GRANT OPTION Enables privileges to be granted

关于SHOW命令我们就简单介绍这么几个，如果想知道更多的SHOW命令可以得到的信息内容可以执行如下命令来获取帮助或者参看MYSQL的联机文档第23章：INFORMATION_SCHEMA信息数据库。

mysql> HELP SHOW

Name: 'SHOW'

Description:

SHOW has many forms that provide information about databases, tables,

columns, or status information about the server. This section describes

those following:

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']

SHOW CREATE DATABASE db_name

SHOW CREATE FUNCTION funcname

SHOW CREATE PROCEDURE procname

SHOW CREATE TABLE tbl_name

SHOW DATABASES [LIKE 'pattern']

SHOW ENGINE engine_name {LOGS | STATUS }

SHOW [STORAGE] ENGINES

SHOW ERRORS [LIMIT [offset,] row_count]

SHOW FUNCTION STATUS [LIKE 'pattern']

SHOW GRANTS FOR user

SHOW INDEX FROM tbl_name [FROM db_name]

SHOW INNODB STATUS

SHOW PROCEDURE STATUS [LIKE 'pattern']

SHOW [BDB] LOGS

SHOW MUTEX STATUS

SHOW PRIVILEGES

SHOW [FULL] PROCESSLIST

SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern']

SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']

SHOW TRIGGERS

SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']

SHOW WARNINGS [LIMIT [offset,] row_count]

The SHOW statement also has forms that provide information about

replication master and slave servers and are described in [HELP PURGE

MASTER LOGS]:

SHOW BINARY LOGS

SHOW BINLOG EVENTS

SHOW MASTER STATUS

SHOW SLAVE HOSTS

SHOW SLAVE STATUS

If the syntax for a given SHOW statement includes a LIKE 'pattern'

part, 'pattern' is a string that can contain the SQL `%' and `_'

wildcard characters. The pattern is useful for restricting statement

output to matching values.

Several SHOW statements also accept a WHERE clause that provides more

flexibility in specifying which rows to display. See

http://dev.mysql.com/doc/refman/5.0/en/extended-show.html.

4 创建用户数据库

mysql> CREATE DATABASE MYTEST;

Query OK, 1 row affected (0.00 sec)

如果想改变MYSQL数据文件的默认路径，我们可以关闭MYSQL实例，修改配置文件”my.cnf”或”my.ini”(WINDOWS系统)中的datadir对应的参数值，然后把MYSQL默认安装的datadir下的内容拷贝到新的数据文件路径下，启动MYSQL实例这样我们再次创建数据库的时候数据文件就放在新的路径下了。

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| mytest |

| root |

+--------------------+

4 rows in set (0.01 sec)

mysql> USE MYTEST;

Database changed

mysql> SELECT DATABASE();

+------------+

| DATABASE() |

+------------+

| mytest |

+------------+

1 row in set (0.00 sec)

mysql> SHOW TABLES;

Empty set (0.01 sec)

5 创建数据库用户

可以用两种方式创建MySQL账户：

1、直接操作MySQL授权表user(不推荐)

2、 CREATE USER

mysql> create user test_cr identified by 'test_cr';

Query OK, 0 rows affected (0.23 sec)

3、直接使用GRANT语句

我们可以不用第二步的CREATE语法来执行，而直接GRANT就可以创建用户。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'TEST'@'LOCALHOST' IDENTIFIED BY 'TEST' WITH GRANT OPTION;

Query OK, 0 rows affected (0.06 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'TEST'@'%' IDENTIFIED BY 'TEST' WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON MYSQL.* TO TEST_NORMAL@'LOCALHOST' IDENTIFIED BY 'TEST_NORMAL';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON MYSQL.* TO TEST_NORMAL@'%' IDENTIFIED BY 'TEST_NORMAL';

Query OK, 0 rows affected (0.02 sec)

mysql> SELECT USER FROM MYSQL.USER;

+-------------+

| USER |

+-------------+

| TEST |

| TEST_NORMAL |

| root |

| TEST |

| TEST_NORMAL |

| root |

+-------------+

6 rows in set (0.00 sec)

注意：其中两个账户有相同的用户名TEST和密码TEST。两个账户均为超级用户账户，具有完全的权限可以做任何事情。一个账户

('TEST'@'localhost')只用于从本机连接时。另一个账户('TEST'@'%')可用于从其它主机连接。请注意TEST的两个账户必须能

从任何主机以TEST连接。没有localhost账户，当TEST从本机连接时，mysql_install_db创建的localhost的匿名用户账户将占先。

结果是，TEST将被视为匿名用户。原因是匿名用户账户的Host列值比'TEST'@'%'账户更具体，这样在user表排序顺序中排在前面。

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uTEST -p mytest

Enter password: ****

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 14 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

注意：用户名和密码区分大小写。

mysql> SELECT DATABASE();

+------------+

| DATABASE() |

+------------+

| mytest |

+------------+

1 row in set (0.00 sec)

mysql> SELECT USER();

+----------------+

| USER() |

+----------------+

| TEST@localhost |

+----------------+

1 row in set (0.00 sec)

6 创建数据库对象

1 创建表

在测试MYSQL创建的时候还遇到了一些小问题，这里整理出来供大家参考：

mysql> CREATE TABLE TEST(ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

-> MC VARCHAR(60),DT DATE DEFAULT NOW());

ERROR 1067 (42000): Invalid default value for 'DT'

注意: AUTO_INCREMENT为MYSQL的自增类型。我们可以利用如下函数查询最后一个序列号的值：

mysql> SELECT LAST_INSERT_ID();

+------------------+

| LAST_INSERT_ID() |

+------------------+

| 3 |

+------------------+

1 row in set (0.06 sec)

本来想在创建表的时候给DT字段一个DEFAULT值(当前时间)，但是出现错误，尝试了几个MYSQL的函数(如CURDATE()等)都是如此，查阅了一下资料得出结论：

MYSQL的DEFAULT值只能是常量，如果想实现上述功能只有表中第一个TIMESTAMP类型字段可以做到。可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。如果你有多个TIMESTAMP列，只有第一个自动更新。自动更新第一个TIMESTAMP列在下列任何条件下发生：

A、列没有明确地在一个INSERT或LOAD DATA INFILE语句中指定。

B、列没有明确地在一个UPDATE语句中指定且一些另外的列改变值。(注意一个UPDATE设置一个列为它已经有的值，这将不引起TIMESTAMP列被更新，因为如果你设置一个列为它当前的值，MySQL为了效率而忽略更改。)

C、明确地设定TIMESTAMP列为NULL或NOW()。

格式：TIMESTAMP[(M)]

MySQL可以以YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD、YYMMDD格式来显示TIMESTAMP值，这主要取决于M值，它们分别为14(缺省值)\12\8\6。

mysql> CREATE TABLE TEST(ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

-> MC VARCHAR(60),DT TIMESTAMP);

Query OK, 0 rows affected (1.08 sec)

mysql> SHOW COLUMNS FROM TEST;

+-------+-------------+------+-----+-------------------+----------------+

+-------+-------------+------+-----+-------------------+----------------+

| ID | int(11) | NO | PRI | NULL | auto_increment |

| MC | varchar(60) | YES | | NULL | |

| DT | timestamp | NO | | CURRENT_TIMESTAMP | |

+-------+-------------+------+-----+-------------------+----------------+

3 rows in set (0.17 sec)

mysql> insert into test(mc) values('ZhangSan');

Query OK, 1 row affected (0.13 sec)

mysql> select * from test;

+----+----------+---------------------+

| ID | MC | DT |

+----+----------+---------------------+

| 1 | ZhangSan | 2007-05-25 09:54:59 |

+----+----------+---------------------+

1 row in set (0.06 sec)

注意：为表TEST新增加TIMESTAMP类型字段，我们发现不是表的第一个TIMESTAMP类型的字段的DEFAULT值不是系统时间。

mysql> ALTER TABLE TEST ADD RQ TIMESTAMP;

Query OK, 1 row affected (0.70 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM TEST;

+-------+-------------+------+-----+---------------------+----------------+

+-------+-------------+------+-----+---------------------+----------------+

| ID | int(11) | NO | PRI | NULL | auto_increment |

| MC | varchar(60) | YES | | NULL | |

| DT | timestamp | NO | | CURRENT_TIMESTAMP | |

| RQ | timestamp | NO | | 0000-00-00 00:00:00 | |

+-------+-------------+------+-----+---------------------+----------------+

4 rows in set (0.19 sec)

mysql> INSERT INTO TEST(MC) VALUES('LiSi');

Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM TEST;

+----+----------+---------------------+---------------------+

| ID | MC | DT | RQ |

+----+----------+---------------------+---------------------+

| 1 | ZhangSan | 2007-05-25 09:54:59 | 0000-00-00 00:00:00 |

| 2 | LiSi | 2007-05-25 10:02:47 | 0000-00-00 00:00:00 |

+----+----------+---------------------+---------------------+

2 rows in set (0.01 sec)

mysql> INSERT INTO TEST(MC,RQ) VALUES('LiSi',NOW());

Query OK, 1 row affected (0.11 sec)

mysql> SELECT * FROM TEST;

+----+----------+---------------------+---------------------+

| ID | MC | DT | RQ |

+----+----------+---------------------+---------------------+

| 1 | ZhangSan | 2007-05-25 09:54:59 | 0000-00-00 00:00:00 |

| 2 | LiSi | 2007-05-25 10:02:47 | 0000-00-00 00:00:00 |

| 3 | LiSi | 2007-05-25 10:03:29 | 2007-05-25 10:03:29 |

+----+----------+---------------------+---------------------+

3 rows in set (0.00 sec)

注意：执行UPDATE操作,表中第一个TIMESTAMP字段自动修改为系统时间。

mysql> UPDATE TEST SET MC='WangWu' WHERE ID=3;

Query OK, 1 row affected (0.13 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM TEST;

+----+----------+---------------------+---------------------+

| ID | MC | DT | RQ |

+----+----------+---------------------+---------------------+

| 1 | ZhangSan | 2007-05-25 09:54:59 | 0000-00-00 00:00:00 |

| 2 | LiSi | 2007-05-25 10:02:47 | 0000-00-00 00:00:00 |

| 3 | WangWu | 2007-05-25 10:04:01 | 2007-05-25 10:03:29 |

+----+----------+---------------------+---------------------+

1 rows in set (0.00 sec)

2 创建索引

MYSQL索引类型：

1、普通索引

这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：

创建索引，例如CREATE INDEX ON tablename (列的列表);

修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);

创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

2、唯一性索引

这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种

方式创建：

创建索引，例如CREATE UNIQUE INDEX ON tablename (列的列表);

修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);

创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );

3、主键

主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。

主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表

的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。

4、全文索引

MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型

的列上创建。它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER

TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再涉及全文索引，如要了解

更多信息，请参见MySQL documentation。

CRomputer-罗军

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 1067 abouting_mysql学习笔记！

好久没有整理东西，感觉自己越来越懒，正好近期有个MYSQL的项目(一个ORACLE的应用迁移到MYSQL)，以前没有接触过MYSQL，但是也知道MYSQL数据库目前广泛的应用在各种个人、商务系统中，各种技术都比较成熟。把自己学习的一些过程总结一下，该文章设计到的内容都没有做太详细的阐述，只是一个简单的入门手册，如果想看更多内容可以参看MYSQL的联机手册。1 MYSQL安装先下载安装...
复制链接

扫一扫