MySQL是Web世界中使用最广泛的数据库服务器。SQLite的特点是轻量级、可嵌入,但不能承受高并发访问,适合桌面和移动应用。而MySQL是为服务器端设计的数据库,能承受高并发访问,同时占用的内存也远远大于SQLite。
此外,MySQL内部有多种数据库引擎,最常用的引擎是支持数据库事务的InnoDB。
常用MySQL命令
设置mysql root账号密码
mysql admin -u root password root
mysql admin -u root -p password newpwd
mysql -u root -p mysql -u root -p root
mysql -h 主机地址 -u 用户名 -p 用户密码
create database mydb;
alter database mydb default character set = utf8;
show databases;
use mydb;
显示本库中的所有表
show tables;
显示某表(table1)的结构
describe table1;
显示数据表的属性,属性类型,主键信息 ,是否为 NULL,默认值等其他信息。
show columns from tableName;
显示数据表的详细索引信息,包括PRIMARY KEY(主键)。
show index from tableName;
输出Mysql数据库管理系统的性能及统计信息。
SHOW TABLE STATUS LIKE [FROM db_name] [LIKE 'pattern'] G: SHOW TABLE STATUS from dbName; # 显示数据库 dbName 中所有表的信息 SHOW TABLE STATUS from dbName LIKE 'tableName%'; # 表名以tableName开头的表的信息 SHOW TABLE STATUS from dbName LIKE 'tableName%'G; # 加上 G,查询结果按列打印
建表
create table tableName (字段设定列表); // create table TestTable( id INT NOT NULL AUTO_INCREMENT, title VARCHAR(100) NOT NULL, author VARCHAR(40) NOT NULL, _date DATE, PRIMARY KEY ( id ) );
删库
drop database 库名;
删表
drop table 表名;
将表中的记录清空
delete from 表名;
为已经存在的表添加新列
alter table tableName add newColumn varchar(8) comment '新添加的字段' // comment为注释,就像在java中//作用是一样的。
为列设定默认值
create table tablename (columnname datatype default defaultvalue); // 新建并设置默认值 alter table tablename alter column columnname set default defaultvalue; // 修改现有列的默认值
删除列
alter table tableName drop column Gatewayid
插入新记录
insert into User(birthday) values (NOW()); // NOW() 是一个 MySQL 函数,该函数返回日期和时间。
查询
SELECT column_name,column_name FROM table_name [WHERE Clause] [OFFSET M ][LIMIT N] // 通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。 // 使用 LIMIT 属性来设定返回的记录数。
SQL LIKE 子句中使用百分号(%)字符来表示任意字符,类似于UNIX或正则表达式中的星号 (*)。
如果没有使用百分号(%), LIKE 子句与等号(=)的效果是一样的。
Select语法
SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法: SELECT field1, field2,...fieldN table_name1, table_name2... WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue' // 可以在WHERE子句中指定任何条件。也可以在WHERE子句中使用LIKE子句。可以使用LIKE子句代替等号(=)。 // LIKE 通常与 % 一同使用,类似于一个元字符的搜索。 // 可以使用AND或者OR指定一个或多个条件。
JOIN
按照功能大致分为如下三类:
- INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
- LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
- RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录
INNER JOIN
// 使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN,效果一样)来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值: SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; // 以上 SQL 语句等价于: SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;
LEFT JOIN
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; // 上实例中使用了LEFT JOIN,该语句会读取左边的数据表runoob_tbl的所有选取的字段数据,即便在右侧表tcount_tbl中没有对应的runoob_author字段值。
RIGHT JOIN
SELECT b.runoob_id, b.runoob_author, a.runoob_count FROM tcount_tbl a RIGHT JOIN runoob_tbl b ON a.runoob_author = b.runoob_author; // 上实例中使用了 RIGHT JOIN,该语句会读取右边的数据表 runoob_tbl 的所有选取的字段数据,即便在左侧表tcount_tbl中没有对应的runoob_author字段值。
NULL
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。 在MySQL中,NULL值与任何其它值的比较(即使是NULL)永远返回false,即 NULL = NULL 返回false 。 MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。
IS NULL: 当列的值是NULL,此运算符返回true。 IS NOT NULL: 当列的值不为NULL, 运算符返回true。 <=>: 比较操作符(不同于=运算符),当比较的的两个值为NULL时返回true。
正则表达式
MySQL中使用 REGEXP 操作符来进行正则表达式匹配。
查找name字段中以'st'为开头的所有数据: SELECT name FROM person_tbl WHERE name REGEXP '^st'; 查找name字段中包含'mar'字符串的所有数据: SELECT name FROM person_tbl WHERE name REGEXP 'mar';
事务
在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务,事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行,事务用来管理insert,update,delete语句
一般来说,事务是必须满足4个条件(ACID): Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)
- 事务的原子性:一组事务,要么成功;要么撤回。
- 稳定性 : 有非法数据(外键约束之类),事务撤回。
- 隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
- 可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。
在Mysql控制台使用事务来操作
- 开始一个事务 start transaction
- 做保存点 save point 保存点名称
- 操作
- 可以回滚,可以提交,没有问题,就提交,有问题就回滚。
修改数据库、数据表
mysql> ALTER TABLE testalter_tbl DROP age; // 如果数据表中只剩余一个字段则无法使用DROP来删除字段。
添加字段
使用 ADD 子句来想数据表中添加列,如下在表 testalter_tbl 中添加 i 字段,并定义数据类型:
mysql> ALTER TABLE testalter_tbl ADD i INT;
如果需要指定新增字段的位置,可以使用MySQL提供的关键字 FIRST (设定位第一列), AFTER 字段名(设定位于某个字段之后)。
尝试以下 ALTER TABLE 语句, 在执行成功后,使用 SHOW COLUMNS 查看表结构的变化:
ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT FIRST; ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT AFTER c;
FIRST 和 AFTER 关键字只占用于 ADD 子句,所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。
修改字段类型及名称
如果需要修改字段类型及名称, 你可以在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 TABLE testalter_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 UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。 ALTER TABLE tbl_name ADD INDEX index_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 FROM table_nameG 可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 G 来格式化输出信息。
临时表
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。
mysql> CREATE TEMPORARY TABLE SalesSummary ( -> 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 table tt G;
mysql> ......
// 执行上面的SQL语句 // 拷贝数据表的数据使用 INSERT INTO... SELECT 语句来实现。 mysql> INSERT INTO clone_tbl (runoob_id, -> runoob_title, -> runoob_author, -> submission_date) -> SELECT runoob_id,runoob_title, -> runoob_author,submission_date -> FROM runoob_tbl;
MySQL序列
MySQL序列是一组整数:1, 2, 3, ...,由于一张数据表只能有一个字段自增主键, 如果你想实现其他字段也实现自动增加,就可以使用MySQL序列来实现
设置序列的开始值
一般情况下序列的开始值为1,但如果需要指定一个开始值100,那我们可以通过以下语句来实现:
mysql> CREATE TABLE insect -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100, -> PRIMARY KEY (id), -> name VARCHAR(30) NOT NULL, # type of insect -> date DATE NOT NULL, # date collected -> origin VARCHAR(30) NOT NULL # where collected );
或者也可以在表创建成功后,通过以下语句来实现:
mysql> ALTER TABLE t AUTO_INCREMENT = 100;
MySQL 处理重复数据
防止表中出现重复数据
可以在MySQL数据表中设置指定的字段为 PRIMARY KEY(主键) 或者 UNIQUE(唯一) 索引来保证数据的唯一性。
下表中无索引及主键,所以该表允许出现多条重复记录。
CREATE TABLE person_tbl ( first_name CHAR(20), last_name CHAR(20), sex CHAR(10) );
如果想设置表中字段first_name,last_name数据不能重复,你可以设置双主键模式来设置数据的唯一性,如果设置了双主键,那么哪个键的默认值不能为NULL,可设置为NOT NULL。
CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(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 TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10) UNIQUE (last_name, first_name) );
统计重复数据
以下我们将统计表中 first_name 和 last_name的重复记录数:
mysql> SELECT COUNT(*) as repetitions, last_name, first_name -> FROM person_tbl -> GROUP BY last_name, first_name -> HAVING repetitions > 1;
以上查询语句将返回 person_tbl 表中重复的记录数。 一般情况下,查询重复的值,请执行以下操作:
- 确定哪一列包含的值可能会重复。
- 在列选择列表使用COUNT(*)列出的那些列。
- 在GROUP BY子句中列出的列。
- HAVING子句设置重复数大于1。
过滤重复数据
如果需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。
mysql> SELECT DISTINCT last_name, first_name -> FROM person_tbl -> ORDER BY last_name; # 也可以使用 GROUP BY 来读取数据表中不重复的数据: mysql> SELECT last_name, first_name -> FROM person_tbl -> GROUP BY (last_name, first_name);
删除重复数据
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex -> FROM person_tbl; -> GROUP BY (last_name, first_name); mysql> DROP TABLE person_tbl; mysql> ALTER TABLE tmp RENAME TO person_tbl;
当然也可以在数据表中添加 INDEX(索引) 和 PRIMAY KEY(主键)这种简单的方法来删除表中的重复记录。方法如下:
mysql> ALTER IGNORE TABLE person_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攻击等。