educoder里mysql数据库_数据库开发 MySQL

修改字段类型及名称

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10); 把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，

使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

ALTER TABLE 对 Null 值和默认值的影响

当修改字段时，你可以指定是否包含空值或者是否设置默认值。

mysql> ALTER TABLEtestalter_tbl-> MODIFY j BIGINT NOT NULL DEFAULT 100; 指定字段 j 为 NOT NULL 且默认值为100 。

修改字段默认值

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

使用ALTER命令及 DROP子句来删除字段的默认值

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

修改数据表类型

可以使用 ALTER 命令及 TYPE 子句来完成。我们将表 testalter_tbl 的类型修改为 MYISAM ：

注意：查看数据表类型可以使用 SHOW TABLE STATUS 语句。

mysql> ALTER TABLE testalter_tbl TYPE = MYISAM;

修改表名

如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

索引

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

创建索引时，你需要确保该索引是应用在SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

普通索引

最基本的索引，它没有任何限制，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

添加索引:create index index_time on Record(time(20));alter table Record add index index_time (time(20));create table User2(id integer primary key auto_increment, province varchar(20), index index_province (province(20)));

删除索引:drop index index_time on Record;

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

CREATE INDEX indexName ON mytable(username(length));

修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))

创建表的时候直接指定

CREATE TABLE mytable(ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)));

删除索引

DROP INDEX [indexName] ON mytable;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

create unique index index_name on table(column(length));alter table Record add unique index_time (time(20));create table User2(id integer primary key auto_increment, province varchar(20), unique index_province (province(20)));

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

创建表的时候直接指定

CREATE TABLE mytable(ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)));

使用ALTER 命令添加和删除索引

ALTER TABLE tbl_name ADD PRIMARY KEY(column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。ALTER TABLE tbl_name ADD UNIQUEindex_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)。ALTER TABLE tbl_name ADD INDEXindex_name (column_list): 添加普通索引，索引值可出现多次。ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

添加索引

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

删除索引

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空(NOT NULL)

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;

mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

显示索引信息

mysql> SHOW INDEX FROMtable_name\G

可以使用 SHOWINDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

临时表

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

mysql> CREATE TEMPORARY TABLESalesSummary (-> product_name VARCHAR(50) NOT NULL

-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00

-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00

-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0);

MySQL 复制表

如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。 如果仅仅使用CREATE TABLE ... SELECT 命令，是无法实现的。

使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。

复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。

如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现。

mysql> show create tablett \G;

mysql> ......

// 执行上面的SQL语句

// 拷贝数据表的数据使用INSERT INTO... SELECT语句来实现。

mysql> INSERT INTOclone_tbl (runoob_id,->runoob_title,->runoob_author,->submission_date)-> SELECTrunoob_id,runoob_title,->runoob_author,submission_date-> FROM runoob_tbl;

MySQL序列

MySQL序列是一组整数：1, 2, 3, ...，由于一张数据表只能有一个字段自增主键， 如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现

设置序列的开始值

一般情况下序列的开始值为1，但如果需要指定一个开始值100，那我们可以通过以下语句来实现：

mysql> CREATE TABLEinsect->(-> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,-> PRIMARY KEY(id),-> name VARCHAR(30) NOT NULL, # type ofinsect-> date DATE NOT NULL, # date collected-> origin VARCHAR(30) NOT NULL # wherecollected

);

或者也可以在表创建成功后，通过以下语句来实现：

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

MySQL 处理重复数据

防止表中出现重复数据

可以在MySQL数据表中设置指定的字段为 PRIMARY KEY(主键) 或者 UNIQUE(唯一) 索引来保证数据的唯一性。

下表中无索引及主键，所以该表允许出现多条重复记录。

CREATE TABLEperson_tbl

(

first_nameCHAR(20),

last_nameCHAR(20),

sexCHAR(10)

);

如果想设置表中字段first_name，last_name数据不能重复，你可以设置双主键模式来设置数据的唯一性，如果设置了双主键，那么哪个键的默认值不能为NULL，可设置为NOT NULL。

CREATE TABLEperson_tbl

(

first_nameCHAR(20) NOT NULL,

last_nameCHAR(20) NOT NULL,

sexCHAR(10),PRIMARY KEY(last_name, first_name)

);

如果我们设置了唯一索引，那么在插入重复数据时，SQL语句将无法执行成功,并抛出错。

INSERT IGNORE INTO 与 INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

REPLACE INTO

INSERT IGNORE INTO当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而REPLACE INTO 如果存在primary 或 unique相同的记录，则先删除掉。再插入新记录。

另一种设置数据的唯一性方法是添加一个UNIQUE索引，如下所示：

CREATE TABLEperson_tbl

(

first_nameCHAR(20) NOT NULL,

last_nameCHAR(20) NOT NULL,

sexCHAR(10)UNIQUE(last_name, first_name)

);

统计重复数据

以下我们将统计表中 first_name 和 last_name的重复记录数：

mysql> SELECT COUNT(*) asrepetitions, last_name, first_name-> FROMperson_tbl-> GROUP BYlast_name, first_name-> HAVING repetitions > 1;

以上查询语句将返回 person_tbl 表中重复的记录数。 一般情况下，查询重复的值，请执行以下操作：

确定哪一列包含的值可能会重复。

在列选择列表使用COUNT(*)列出的那些列。

在GROUP BY子句中列出的列。

HAVING子句设置重复数大于1。

过滤重复数据

如果需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

mysql> SELECT DISTINCTlast_name, first_name-> FROMperson_tbl-> ORDER BYlast_name;

# 也可以使用GROUP BY来读取数据表中不重复的数据：

mysql> SELECTlast_name, first_name-> FROMperson_tbl-> GROUP BY (last_name, first_name);

删除重复数据

mysql> CREATE TABLE tmp SELECTlast_name, first_name, sex-> FROMperson_tbl;-> GROUP BY(last_name, first_name);

mysql> DROP TABLEperson_tbl;

mysql> ALTER TABLE tmp RENAME TO person_tbl;

当然也可以在数据表中添加 INDEX(索引) 和 PRIMAY KEY(主键)这种简单的方法来删除表中的重复记录。方法如下：

mysql> ALTER IGNORE TABLEperson_tbl-> ADD PRIMARY KEY (last_name, first_name);

约束

外键

create table User(id integer primary key auto_increment, name varchar(20));create table Record(id integer primary key auto_increment, time varchar(20), ur_id integer not null, foreign key (ur_id) references User(id));insert into User(name) values('ccf'); // id = 1

insert into User(name) values('yyn'); // id = 2

insert into Record(time, ur_id) values('1', '1'); // id = 1

insert into Record(time, ur_id) values('0', '2'); // id = 2

insert into Record(time, ur_id) values('0', '3'); // fail: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`record`, CONSTRAINT `record_ibfk_1` FOREIGN KEY (`ur_id`) REFERENCES `User` (`id`))

级联

alter table Record add constraint id_check foreign key(ur_id) references User(id) on delete cascade on update cascade; // ?

其它

防止SQL注入，我们需要注意以下几个要点：

永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。

永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。

永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。

不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。

应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装

sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。