MySQL笔记
1,配置path环境变量,到bin目录
C:\Program Files\MySQL\MySQL Server 5.0\bin
2,启动MySQL服务
DOS命令: net start MySQL (ipconfig 查看本机IP)
登录: mysql -u root -p (回车)
Enter password: 123
==>(-h:主机名,本机登录不用) -u:用户名 -p:密码
3,退出 exit;
4,展示所有数据库 show databases;
5,使用数据库 use mysql;
6,展示所有的表 show tables;
7,查询数据 select * from user \G;
8,更改用户密码 update user set password=password('新密码') where user='root';
==>flush privileges; 刷新权限命令
9,寻求帮助 ? show;
10,创建数据库 create database 'bdqn';
11,删除数据库 drop database 'bdqn';
12,创建表
create table `bdqn`.`users`(
`userId` int NOT NULL AUTO_INCREMENT COMMENT '用户编号,主键',
`userName` varchar(20) NOT NULL DEFAULT '匿名' COMMENT '用户姓名',
PRIMARY KEY (`userId`)
)
13,创建数据库
create database 数据库名;
14,删除数据库
dorp database 数据库名;
15,查看数据库
show databases;
16,使用数据库
use 数据库名;
17,创建数据表
create table [if not exists] `表名`(
`字段名1` 列类型[属性][索引][注释],
`字段名2` 列类型[属性][索引][注释],
);
注意:反引号(1左边位置``)用于区别mysql保留字与普通字符而引入的 (可以省略)
18,列类型
1):数值类型:
类型 说明
tinyint 非常小的数据
smallint 较小的数据
mediumint 中等大小的数据
int 标准整数 int(4) 查询结果低于4位的按4位展示,如12显示为0012,超过4位正常显示
bigint 较大的整数
float 单精度浮点数
double 双精度浮点数
decimal 字符串形式的浮点数 decimal(m,d) 如decimal(5,2) 5位数小数2位 推荐使用,float 和 double 存在精度损失
2):字符串类型
类型 说明
char[(M)] 固定长字符串,检索快但费空间 0 <= M <= 255
varchar[(M)] 可变字符串 0 <= M <= 65535
tinytext 微型文本串
text 文本串
3):日期和时间类型
类型 说明
date YYYY-MM-DD
time Hh:mm:ss
DATETIME YY-MM-DD hh:mm:ss
TIMESTAMP YYYYMMDDhhmmss格式表示的时间戳(一般用来验证数据是否被修改) 使用时间的默认值 NOW(),列类型必须是 TIMESTAMP
YEAR yyyy格式的年份值
19,属性
1):unsigned 无符号的,声明该数据列不允许负数
2):zerofill 0填充,不足位数的用0来补充(如int(4), 5则为0005)
3):auto_increment 自增的,通常用于设置主键,且为整数类型,每添加一条数据,自动在上一个记录数上加1,可定义起始值
4):null 和 not null 默认为null,即没有插入该列的数值,如果设置为not null,则该列必须有值
5):default 默认的,用于设置默认值
20,NULL值
理解为"没有值"或"未知值"
不要用NULL进行算术运算,结果仍为NULL
MySQL中,0或NULL都意味着假,1为真
21,删除数据
delete from 表名;
注意: delete删除所有数据不会影响自增列 , 需使用 truncate;
delete from 表名; auto_increment 没有影响
truncate 表名;重新设置 auto_increment , 对事务没有影响,速度更快
22,设置自增列
CREATE TABLE t4(
id INT(4) PRIMARY KEY AUTO_INCREMENT,
sname CHAR(4)
)AUTO_INCREMENT=100;
23,设置数据表类型
create table 表名(
)engine = MyISAM 或 InnoDB
常见MySQL的数据表类型: MyISAM, InnoDB, HEAP, BOB, CSV
名称 MyISAM InnoDB(适合增删改,默认)
*事务处理 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
*全文索引 支持 不支持
表空间大小 较小 较大,约2倍
24,数据表的存储位置
文件方式存储在磁盘中,包括表文件,数据文件以及数据库的选项文件
位置:MySQL安装目录\data下存放数据表. 目录名对应数据库名,该目录下文件名对应数据表
1):InnoDB类型数据表只有一个 *.frm 文件,以及上一级目录的 ibdata1 文件
2):MyISAM类型数据表对应三个文件:
*.frm -- 表结构定义文件
*.MYD -- 数据文件
*.MYI -- 索引文件
25,设置当前数据库字符编码
set names 字符编码;
26,创建数据表(库)时指定字符集
create table 表名(
)charset= utf-8;
如无设定,则根据MySQL数据库配置文件 my.ini 中的参数设定,如 character-set-sever=utf8
27,删除指定数据
delete from 表名 where 条件;
28,修改数据表
1):修改表名
alter table 旧表名 rename as 新表名
2):添加字段
alter table 表名 add 字段名 列类型[属性] [first | after 字段名]
3):修改字段
alter table 表名 modify 字段名 列类型[属性]
alter table 表名 change 旧字段名 新字段名 列类型[属性]
4):删除字段
alter table 表名 drop 字段名
29,删除表
drop table [if exists] 表名
30,DML 数据操作语言
insert : insert into 表名 [字段1, 字段2...] values (值1, 值2...) 可同时插入多条数据,values后用英文逗号隔开
如: INSERT INTO teacher VALUES(1001,"格格"),(1002,"紫薇"),(1003,"测试");
update :
UPDATE t5 SET sname = SUBSTRING(sname,1,2) WHERE id = 102;
substring(filed,m):截取filed字段从第m个字符开始到结束的字符串；
substring(filed,m,n):截取filed字段从第m个字符开始的长度为n的字符串；
注意:substring 索引从1开始
UPDATE t5 SET sname = CONCAT('r',sname) WHERE id = 101;
UPDATE t5 SET sname = CONCAT(sname,'rt') WHERE id = 101;
concat 添加 前缀 或 后缀 字符串
delete :
truncate : 用于完全清空表数据,但表结构,索引,约束等不变 , 没有条件
语法: truncate table table_name;
和 delete 区别:
相同:都能删除数据,不删除表结构,但truncate速度更快
不同:使用truncate table 重新设置 auto_increment 计数器(自增列)
使用truncate table 不会对事务有影响
31,mysql事务实现方式
set autocommit = 0 ; #关闭自动提交模式
set autocommit = 1 ; #开启自动提交模式
mysql中默认是自动提交
**使用事务时应先关闭自动提交
set autocommit = 0
关闭MySQL的自动提交
start transaction
开始一个事务,标记事务的起始点
commit 或 rollback
提交一个事务给数据库 将事务回滚,数据回到本次事务的初始状态
set autocommit = 1;
还原MySQL数据库的自动提交
--------------------
CRUD : 增删改查
--------------------
设置 MySQL 为严格模式
set sql_mode = 'strict_trans_tables';
--------------------------------------
原子性,一致性,隔离性,持久性(ACID)
---------------------------------------
32, count(*) 统计 eg : SELECT COUNT(studentno) 学生总人数 FROM bdqn_student;
33, as 别名,可以省略, 可以加引号,也可以不加
34, distinct 去重 eg : SELECT DISTINCT r.SubjectNo FROM result r;
==>如有多列,按整体算没有重复的则显示
35, concat 前缀 eg : SELECT CONCAT('学生:', b.StudentName) 姓名, b.Address 地址 FROM bdqn_student b;
36, SELECT VERSION(); 查询版本号
==> 避免SQL返回结果中包含".", "*"和括号干扰开发语言程序,父:
SELECT VERSION()AS mysql_v, 123.44*100 AS expression;
-->表头"123.44*100"使用别名 返回结果不会与后台开发程序发生混淆
37, 逻辑操作符
and或&&
or或||
xor或^
not或!
38,比较操作符
is null
is not null
between and (推荐 <= 更优越)
like
in
39,ESCAPE 定义转义符
40, % 任意字符 0个或多个
_ 任意一个字符
41, LIMIT [m,]n 或 LIMIT n OFFSET m
例如:
select * from result limit 5; //返回前5条记录
select * from result limit 5,10 //返回6-15条记录 (从大于5开始返回10条记录)
42, SHOW COLUMNS FROM 数据表: 等价于 desc 数据表;
显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。
43,SHOW INDEX FROM 数据表:
显示数据表的详细索引信息，包括PRIMARY KEY（主键）。
44,SHOW TABLE STATUS LIKE [FROM db_name] [LIKE 'pattern'] \G:
该命令将输出Mysql数据库管理系统的性能及统计信息。
mysql> SHOW TABLE STATUS FROM bdqn; # 显示数据库 RUNOOB 中所有表的信息
mysql> SHOW TABLE STATUS from bdqn LIKE 'student%'; # 表名以runoob开头的表的信息
mysql> SHOW TABLE STATUS from bdqn LIKE 'student%'\G; # 加上 \G，查询结果按列打印
45,MySQL客户端输出窗口显示中文乱码问题解决办法
第一步 ：先查看字符集编码：show variables like '%char%';
第二步：之所以会显示乱码，就是因为MySQL客户端输出窗口显示中文时使用的字符编码不对造成的
现在是使用utf8字符编码来显示中文数据的，但是因为操作系统是中文操作系统，默认使用的字符集是GB2312，
所以需要把输出窗口使用的字符编码改成gb2312才能够正常显示中文。使用如下的命令设置输出窗口使用的字符编码
第三步: set character_set_results=gb2312; ==>显示结果乱码
set character_set_client=gb2312;
set character_set_connection=gb2312; ==>输入中文乱码
---->以上一句话解决:set names gb2312; 命令用于设置使用的字符集。
注意utf8与UTF-8的分别.
utf8的好处是java虚拟机可以自动将它与gbk进行转换，因而显示都不会有乱码。可是在控制台下(cmd)，显示就有问题了
在java程序里边使用"jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8"这样的url,表明使用GBK进行编码。
46,MySQL 正则表达式
^ 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$ 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
. 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。
[...] 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...] 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
p1|p2|p3 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。
* 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
+ 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n} n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,m} m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。
例:
查找name字段中以'st'为开头的所有数据：mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
查找name字段中以'ok'为结尾的所有数据：mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
查找name字段中包含'mar'字符串的所有数据：mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';
查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';
47,默认值 增加及修改
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
48, 数据表 类型及修改
查看数据表类型: SHOW TABLE STATUS LIKE 'student'\G;
修改数据表类型: ALTER TABLE student ENGINE = MYISAM;
49,修改表名
mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;
50,MySQL 索引
1)普通索引
创建索引 : 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;
2)唯一索引 ====>注意,唯一索引列不能有重复数据,否则报 duplicate entry *** for key 2;
创建索引 : CREATE UNIQUE INDEX indexName ON mytable(username(length))
已有索引给表添加索引:ALTER table mytable ADD UNIQUE [indexName] (username(length))
创建表时直接指定:
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 ，用于全文索引。
使用 ALTER 命令添加和删除主键
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
显示索引信息
mysql> SHOW INDEX FROM table_name; \G
51,MySQL 临时表
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
CREATE TEMPORARY TABLE SalesSummary
当你使用 SHOW TABLES命令显示数据表列表时，你将无法看到 SalesSummary表。
如果你退出当前MySQL会话，再使用 SELECT命令来读取原先创建的临时表数据，那你会发现数据库中没有该表的存在，因为在你退出时该临时表已经被销毁了。
如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。
删除MySQL 临时表 DROP TABLE SalesSummary;
52,MySQL 复制表
如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。 如果仅仅使用CREATE TABLE ... SELECT 命令，是无法实现的。
*使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
*复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。
*如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现。
53,MySQL 元数据
查询结果信息： SELECT, UPDATE 或 DELETE语句影响的记录数。
数据库和数据表的信息： 包含了数据库及数据表的结构信息。
MySQL服务器信息： 包含了数据库服务器的当前状态，版本号等。
命令 描述
SELECT VERSION( ) 服务器版本信息
SELECT DATABASE( ) 当前数据库名 (或者返回空)
SELECT USER( ) 当前用户名
SHOW STATUS 服务器状态
SHOW VARIABLES 服务器配置变量
54,MySQL 序列使用
mysql> ALTER TABLE t AUTO_INCREMENT = 100;
55,MySQL 处理重复数据
1):INSERT IGNORE INTO与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据，
如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。
如: INSERT IGNORE INTO t5 VALUES(101,'tert','dd');
2):INSERT IGNORE INTO当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。
而REPLACE INTO into如果存在primary 或 unique相同的记录，则先删除掉。再插入新记录。
如: REPLACE INTO t5 VALUES(101,'tert','dd');
3):统计重复数据
如: SELECT COUNT(*) AS total , userName FROM users GROUP BY username HAVING total >= 2;
*确定哪一列包含的值可能会重复。
*在列选择列表使用COUNT(*)列出的那些列。
*在GROUP BY子句中列出的列。
*HAVING子句设置重复数大于1。
4):过滤重复数据
使用 DISTINCT 关键字来过滤重复数据
如: SELECT DISTINCT userName FROM users;
使用 GROUP BY 来读取数据表中不重复的数据
如: SELECT userName FROM users GROUP BY username;
5);删除重复数据
新建表
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
-> FROM person_tbl;
-> GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;
添加索引
mysql> ALTER IGNORE TABLE person_tbl
-> ADD PRIMARY KEY (last_name, first_name);
56,MySQL 及 SQL 注入
所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。
1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。
2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。
采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。
57,MySQL 导出数据
1):导出到 TXT
SELECT * FROM users INTO OUTFILE 'f:/users.txt';
2):值用逗号隔开并换行
SELECT * FROM users INTO OUTFILE 'f:/users3.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
3):值用逗号隔开,加引号并换行
SELECT * FROM users INTO OUTFILE 'f:/users2.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
FIELDS TERMINATED BY ',' : 表示每列以 逗号 隔开
ENCLOSED BY '"' : 表示值加引号
LINES TERMINATED BY '\r\n' : 表示回车并换行
===>注意:输出不能是一个已存在的文件。防止文件数据被篡改。
58,MySQL 导入数据
load data infile 'file_name' into table tb_name;
mysqlimport的常用选项介绍
选项 功能
-d or --delete 新数据导入数据表中之前删除数据数据表中的所有信息
-f or --force 不管是否遇到错误，mysqlimport将强制继续插入数据
-i or --ignore mysqlimport跳过或者忽略那些有相同唯一 关键字的行， 导入文件中的数据将被忽略。
-l or -lock-tables 数据被插入之前锁住表，这样就防止了， 你在更新数据库时，用户的查询和更新受到影响。
-r or -replace 这个选项与－i选项的作用相反；此选项将替代 表中有相同唯一关键字的记录。
--fields-enclosed- by= char 指定文本文件中数据的记录时以什么括起的， 很多情况下 数据以双引号括起。 默认的情况下数据是没有被字符括起的。
--fields-terminated- by=char 指定各个数据的值之间的分隔符，在句号分隔的文件中， 分隔符是句号。您可以用此选项指定数据之间的分隔符。
默认的分隔符是跳格符（Tab）
--lines-terminated- by=str 此选项指定文本文件中行与行之间数据的分隔字符串 或者字符。 默认的情况下mysqlimport以newline为行分隔符。
您可以选择用一个字符串来替代一个单个的字符： 一个新行或者一个回车。
59,MySQL数据库备份
>mysqldump -u root -p 数据库名[表名1,表名2...] > 绝对路径名文件,如 d:/myschool.sql
EnterPassword:***
60.MySQL数据库恢复
mysql -u root - p 数据名 < 文件名,如 d:/myschool.sql
或
source d:/myschool.sql;
61,表设计
1:N : 多的一方添加另一方主键作为外键
M:N : 必须引入第三张关系数据表,同时引入另两张表的主键作为外键
62, mysql 类型 Java 类型
datetime Timestamp ---> Date date = new Date(); Timestamp tamp = new Timestamp(date.getTime()); --> rs.getTimestamp("createtime")
decimal BigDecimal ---> BigDecimal.doubleValue() 返回 double 类型 -->rs.getBigDecimal("bookprice")
63,触发器
示例:新增时记录
DELIMITER $ CREATE TRIGGER tr_student_insert AFTER INSERT ON student FOR EACH ROW BEGIN SET @txt = CONCAT('新增学生:[id:',new.id,',name:',new.name,',city:',new.city,',age:',new.age,',gradeId:',new.gradeId,']'); INSERT INTO student_log VALUES(NULL,'新增',@txt,'admin',NOW()); END $ DELIMITER ;
修改:
DELIMITER $ CREATE TRIGGER tr_student_update AFTER UPDATE ON student FOR EACH ROW BEGIN SET @txt = CONCAT('修改学生:修改前[id:',old.id,',name:',old.name,',city:',old.city,',age:',old.age,',gradeId:',old.gradeId,']修改后[id:',new.id,',name:',new.name,',city:',new.city,',age:',new.age,',gradeId:',new.gradeId,']'); INSERT INTO student_log VALUES(NULL,'修改',@txt,'admin',NOW()); END $ DELIMITER ;
删除:
1 DELIMITER $ 2 CREATE TRIGGER tr_student_delete AFTER DELETE ON student FOR EACH ROW 3 BEGIN 4 SET @txt = CONCAT('删除学生:[id:',old.id,',name:',old.name,',city:',old.city,',age:',old.age,',gradeId:',old.gradeId,']'); 5 INSERT INTO student_log VALUES(NULL,'删除',@txt,'admin',NOW()); 6 END $ 7 DELIMITER ;
注意: 1,MySQL 不能将 insert,update,delete 放在一起,需分开建; 当前系统时间使用 NOW();
2,oracle支持 or replace 而mysql是不可以的
3,oracle在使用的变量要在begin前面声明。而mysql则用 @ 符号直接使用
4,oracle变量赋值用的是 := ，而mysql用的是 = 。
5,oracle新表和旧表用的是：new 和：old，而mysql用的是new 和old。
ORACLE 示例:
1 CREATE OR REPLACE TRIGGER tr_teacher AFTER INSERT OR UPDATE OR DELETE ON teacher FOR EACH ROW 2 declare 3 v_opt_type teacher_log.opt_type%TYPE; 4 v_old_value teacher_log.old_value%TYPE; 5 v_tno teacher_log.tno%TYPE; 6 begin 7 IF inserting THEN 8 v_opt_type:=1; 9 v_tno:=:new.tno; 10 ELSIF deleting THEN 11 v_opt_type:=2; 12 v_tno:=:old.tno; 13 v_old_value:=:old.tname||','||:old.sal||','||:old.comm; 14 ELSE 15 v_opt_type:=3; 16 v_tno:=:old.tno; 17 v_old_value:=:old.tname||','||:old.sal||','||:old.comm; 18 END IF; 19 INSERT INTO teacher_log VALUES(sq_log.nextval,v_old_value,SYSDATE,v_opt_type,v_tno); 20 end tr_teacher;
