Mysql自学笔记

SQL(strucut query language)

DDL (数据库定义语言)
DML (数据库操作语言)
DCL (数据库的控制语言)
DTL (数据库的高级语言)
查看版本的函数
select version();
查看日期的函数 (只显示当前的日期)
select curdate();
查看时间的函数
select curtime();
查看日期+时间的函数
select now();
查看字节数(如果是中文的话则会根据不同的字符集来输出相关的的内容)
select length('abc');
查看当前的内容
select char_length('');
查看当前使用的数据库
select database();
数据库的引擎
1.myisam
2.innodb(新版本的默认的引擎)
查看默认的引擎
show engines;
创建一个数据库 (如果这个数据库不存在的情况下才创建名称为aa)
create database if not exists aa;
mysql> create database if not exists aa;
Query OK, 1 row affected (0.00 sec)
--------------------------------------------------------
对于已经建立成的表进行 CURD操作
命名+table +表名+动作 +执行的内容

1.重命名一个表
alter table bro_jiaoban rename bro_zhilei;

2.添加一个新属性
ALTER TABLE bro_jiaoban ADD AGE VARCAHR(200);

3. 删除一个列
ALTER TABLE bro_jiaoban DROP COLUMN link;

4.修改属性的数据类型
ALTER TABLE bro_jiaoban MODIFY teacher varchar(100);

5.对于一个列名进行重命名 (后面的数据的类型一定要加如果不加的话则会报错)
ALTER TABLE bro_jiaoban CHANGE COLUMN age nianling varchar(300);

6.查看系统中状态
show variables like "characeter_%"
show variables like "colletction_%"

7.查看系统中唯一值
select distinct name from jiankong;

8. 添加用户
create user zhilei identified by "password";
删除用户
drop user zhilei ;

9.如果能于一个表进行授权
grant all privileges on *.* to "user1"@"%"" identified by "";
(注意：这个过程会加一个授权的用户，用要给这个用户与授权的host给单引号或者是双引号)

10.改变密码

1.如果改变当前登录帐户的密码的话
set password =password('123');
2.如果改变其它的帐密码的话则要用
set password for "user1" =password('124');

12.删除查询语句中的重复项
distinct
英 [dɪˈstɪŋkt] 美 [dɪˈstɪŋkt]
adj. 明显的，清楚的; 卓越的，不寻常的; 有区别的; 确切的

mysql> SELECT * FROM score;
+-----+---------+-------+---------+
| id | english | yuwen | teacher |
+-----+---------+-------+---------+
| 4 | 19 | 29 | zhilei |
| 5 | 39 | 45 | yinchao |
| 101 | 39 | 45 | yinchao |
| 102 | 39 | 45 | yinchao |
| 103 | 39 | 45 | yinchao |
| 104 | 39 | 45 | yinchao |
| 105 | 39 | 45 | yinchao |
+-----+---------+-------+---------+
7 rows in set (0.00 sec)

mysql>
字符集设置
latin Big5(繁体中文) BG2312 UTF8

默认的编码是
character set: latin1
collation :latin1_swedish_ci

在创建数据库的时候设置默认的字符编码
DEFAULT CHARACTER SET utf8
DEFATULT COLLATE utf8_genereal_ci;
例：
mysql> create database jiankong
-> default character set utf8
-> default collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

如果修改已经存在的数据库的字符集的话（没有default)
mysql> alter database work character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

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

数据库配置文件的字符集设置
客户端的字符集
[client]
defalut-character-set=utf8
服务端的字符集
[mysqld]
defalut-character-set=utf8
collation-server=utf8_general_ci;
init-connect="SET NAMES utf8"
character-set-server=utf8
[mysql]

高级的查询
符号有这样的几个
=
>
>=
<
<=
in
like 后面配置%来使用通配符的意思表示0个或者是多个的字符
between
and
or
group by "one";
having

查询功能的顺序

where

--分页
limit

--排序
正序（从小到大）
order by asc;
倒序 (从大到小)
order by desc;

分页
mysql> select * from score;
+-----+---------+-------+-----------+
| id | english | yuwen | teacher |
+-----+---------+-------+-----------+
| 4 | 19 | 29 | zhilei |
| 5 | 39 | 45 | yinchao |
| 101 | 39 | 45 | yinchao |
| 102 | 39 | 45 | yinchao |
| 103 | 39 | 45 | yinchao |
| 104 | 39 | 45 | yinchao |
| 105 | 39 | 45 | yinchao |
| 106 | 34 | 43 | zongbin |
| 107 | 4 | 3 | huangkai |
| 108 | 33 | 67 | 志雷 |
| 109 | 33 | 67 | 张三 |
| 110 | 33 | 67 | 李四 |
| 111 | 22 | 90 | 王五 |
| 112 | 22 | 90 | 王志五 |
| 113 | 22 | 90 | 王三志 |
+-----+---------+-------+-----------+

limita b
----------------------------------
n代表第几页，b代表一页显示的个数
a=n*b-b
a=b*(n-1)

1.数据的插入

1.选择字段插入
数据库的插入先取出想插入数据的字段，再一一的赋值
insert into score ('host' ,"user","password") values("");

2.整体的插入(如果id为自增的话)
insert into score values(null,"","");

数据的类型
---------------------------------------------------
1.字符串
2.数值型
3.日期型
4. null
------------------------

合计函数 (合计函数不能与where一起使用但是能和having一起使用)
1.sum()
2.max()
3.min()
4.count()
5.avg()

合计函数与having一起使用
1.select name 姓名 from ks having max(yingyu);

查询英语的总合是与对应的姓名的同学，并再做一个过滤就是总合大于60分的
2.select name 姓名,sum(yingyu) from ks group by name having sum(yingyu)>60;

show 用法
1. show databases;
2. show tables;
3. show variables like "character%"
4. show variables like "collaction%"
5. show engines; (查看所有的引擎)

配置文件
windows my.ini
(linux/nuix) my.cnf

修改库与表的结构的内容的是
alter
如查修改表中的内容的话则用
update
-------------------------
数据的类型
表的创建
create table test(
id int not null auto_increment ,
primary key(id)
)
------------------------

unsignedl (无符号的（也就是没有负数）)
zerefull (零填充)
comment (对于字段的描述相当于注释)
CREATE TABLE tt(
id INT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT COMMENT "学生ID" ,
NAME VARCHAR(30),
PRIMARY KEY(id)
);
增加外键
constraints foreign key(std) references stdentd('id');
------------
SHOW TABLES;
查看详细的字段的信息
SHOW FULL COLUMNS FROM tt;
(注意：not null 要写在unsigned zerofill 的后面)
DROP TABLE tt;
SHOW TABLES;
CREATE TABLE tt(
id INT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT "学生id",
sname VARCHAR(40) COMMENT "学生姓名",
sex VARCHAR(40) COMMENT "学生的性别")ENGINE=INNODB DEFAULT CHARSET utf8 AUTO_INCREMENT=11 COMMENT "学生信息表";
DROP TABLE tt;
SHOW FULL COLUMNS FROM tt;

(zerefill) 注意是:fill
(show full columns from kk) 注意是：full
(select * from kk ) 注意：是from 并不是form

查看创建数据库的信息
show create database work;
查看表创建的数据
show create table aa;
查看索引
show triggers from work;
查看一个用户的权取(grant 要加s)
show grants for username

mysql的数据类型
-----------------------
1.数值型
a.整型
微整型 tinyint（-128-127）
小整型 smallint
中整型 mediumint
大整型 bigint
b.浮点型
decimal(6,2) --代表

2.日期
3.字符串

对于数据表的重命名的两种方法
第一种：rename table one to two
第二种：alter table test rename to one;

改变表引擎
ALTER TABLE t1 ENGINE = InnoDB;

改变一个表自增的起始的值
ALTER TABLE t2 AUTO_INCREMENT = value;

删除一个表中的某些列
ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

对表中一列进行重命名并加更多的条件
ALTER TABLE t1 CHANGE a b INTEGER;

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;
ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;

改变列的属性
ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

更改表的字符集
ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

索引

1.主键索引

2.唯一索引
unique key(字段名);
3.普通索引
第一种的情况
CREATE TABLE IF NOT EXISTS tt(
id MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(30) COMMENT "学生名称",
sex VARCHAR(30) UNIQUE, //建立唯一索引也可以用 unique key(sname)
KEY aa(sname); //第一步要指定索引的名称，再指定索引的列

第二种的情况
如果表已经创建完成，但没有建立索引的话，可以用
create index aa on 表(字段1，字段2);

删除索引的
drop index aa on 表名; 这样就可以了。

4.全文件索引 fulltext
create table book(
id tinyint unsigned not null auto_cerement
);

/
删除所有的记录

delete from s;

删除的时候不记日志，自增的编号从头开始，速度快
删除时不记日志，不触发删除
truncate ss;

总结修改操作:
----------------------------------------
查看表的字段信息：desc 表名;
查看表的所有信息：show create table 表名;
添加主键约束：alter table 表名 add constraint 主键（形如：PK_表名） primary key 表名(主键字段);
添加外键约束：alter table 从表 add constraint 外键（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
(alter table 主表名 add foreign key (字段 ) references 从表名(字段) on delete cascade)
添加唯一约束:
ALTER table 表名 add unique key 约束名 (字段);
删除主键约束：alter table 表名 drop primary key;
删除外键约束：alter table 表名 drop foreign key 外键（区分大小写）;
修改表名：alter table t_book rename to bbb;
添加列：alter table 表名 add column 列名 varchar(30);
删除列：alter table 表名 drop column 列名;
修改列名MySQL： alter table bbb change nnnnn hh int;
修改列名SQLServer：exec sp_rename't_student.name','nn','column';
修改列名Oracle：alter table bbb rename column nnnnn to hh int;
修改列属性：alter table t_book modify name varchar(22);
sp_rename：SQLServer 内置的存储过程，用与修改表的定义。

mysql函数:

-- 定义函数
delimiter //
create function two(a int)
returns int -- 注意一定要有返回值
begin
return a*a;
end//
-- 再将结束符号定义为分号
delimiter ;
-- 删除函数
drop function one;
-- 调用函数
select two(4);

mysql存储过程:
----------------------------------------
-- mysql存储过程

show tables;

desc t;

select tname 姓名 from t where id=3;

delimiter //
create procedure shuchu(a int) -- 数据的类型一定要加
begin
select * from t where id=a ;
end
//
delimiter ;

call shuchu(2);

-- 创建好存储过程后我们用call关键字来调用

call charu("one");

drop procedure charu;

select * from t;

alter table t add sscore int;

call shuchu();

drop procedure shuchu;

update t set tgender="男" where tname="ABC";
-- 优点：存储过程较快，且具备一定的安全性

call shuchu(3)

mysql事件:
------------------------------------
-- 查看事件状态

show variables like "event_scheduler";

-- 开启事件

set global event_scheduler=on;

-- 第二种开启事件方式

set global event_scheduler=1;

-- 关闭事件

set global event_scheduler=0;

-- 第二种关闭事件方法

删除事件：

drop event 事件名称

关闭事件
alter event a a on scheduler disables / enable

set global event_scheduler=off;

-- 创建事件

create event aa on schedule every 1 second
do
insert into t1 (tname) values('zhilei');

show tables;

select * from t1;

mysql跳过授权表:
---------------------------------------------
第一步：终止进程
pkill mysql
第二步：运行
mysqld_safe --skip-grant-tables &
第三步：用root帐号登录并改改密码（如果root密码给忘记了进去后只能通过这样的方式执行）
update mysql.user set password =password('kkk') where user="root";

如果root用户被删除:
----------------------------------------------
.停止数据库，并在mysql配置文件my.cnf中添加skip-grant-tables参数到[mysqld]配置块中
执行以下命令：./mysql.server stop

2.启动数据库，添加root用户，并授予权限，启动数据库后，可以以密码登陆，并插入一条添加用户名为root的命令，命令执行如下：
1，./mysql.server start
2，./mysql -p
3，insert into user set user=’root’,ssl_cipher=”,x509_issuer=”,x509_subject=”;
添加完成后，对该root用户更新权限，更新权限命令如下：

update user set Host='%',select_priv='y', insert_priv='y',update_priv='y', Alter_priv='y',delete_priv='y',create_priv='y',drop_priv='y',reload_priv='y',shutdown_priv='y',Process_priv='y',file_priv='y',grant_priv='y',References_priv='y',index_priv='y',create_user_priv='y',show_db_priv='y',super_priv='y',create_tmp_table_priv='y',Lock_tables_priv='y',execute_priv='y',repl_slave_priv='y',repl_client_priv='y',create_view_priv='y',show_view_priv='y',create_routine_priv='y',alter_routine_priv='y',create_user_priv='y' where user='root';

执行完成后，执行quit 退出数据库。

3.重启数据库并更新root密码
执行完添加与更新root用户权限后，可以使用命令：

./mysql.server restart 来重启数据库
利用./mysql -p 命令无密码登陆进入数据库，执行以下命令：
update mysql.user set password=password('newpassword') where user='root'
#将password()中的newpassword字符更改为你自己的密码
执行完成后，记得刷新权限：

4.注释掉skip-grant-tables参数，重启数据库
权限刷新完成后，修改mysql的配置文件my.cnf，注释或删除掉添加的skip-grant-tables参数，然后利用命令：

./mysql.server restart 重启数据库即可
flush privileges;
5.重启MYSQL就可以使用ROOT用户了。

mysql数据类型:
------------------------------------------------

MySQL数据类型格式及长度

标签： mysql数据类型
2012-03-06 21:31 8087人阅读评论(0) 收藏举报
分类：

MySQL（31）
目录(?)[+]
数值类型

MySQL 的数值数据类型可以大致划分为两个类别，一个是整数，另一个是浮点数或小数。许多不同的子类型对这些类别中的每一个都是可用的，每个子类型支持不同大小的数据，并且 MySQL 允许我们指定数值字段中的值是否有正负之分或者用零填补。
　　表列出了各种数值类型以及它们的允许范围和占用的内存空间。

类型
大小
范围（有符号）
范围（无符号）
用途
TINYINT
1 字节
(-128，127)
(0，255)
小整数值
SMALLINT
2 字节
(-32 768，32 767)
(0，65 535)
大整数值
MEDIUMINT
3 字节
(-8 388 608，8 388 607)
(0，16 777 215)
大整数值
INT或INTEGER
4 字节
(-2 147 483 648，2 147 483 647)
(0，4 294 967 295)
大整数值
BIGINT
8 字节
(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)
(0，18 446 744 073 709 551 615)
极大整数值
FLOAT
4 字节
(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)
0，(1.175 494 351 E-38，3.402 823 466 E+38)
单精度
浮点数值
DOUBLE
8 字节
(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
双精度
浮点数值
DECIMAL
对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2
依赖于M和D的值
依赖于M和D的值
小数值

INT 类型

　　在 MySQL 中支持的 5 个主要整数类型是 TINYINT，SMALLINT，MEDIUMINT，INT 和 BIGINT。这些类型在很大程度上是相同的，只有它们存储的值的大小是不相同的。
　　MySQL 以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展，这样当从数据库检索一个值时，可以把这个值加长到指定的长度。例如，指定一个字段的类型为 INT(6)，就可以保证所包含数字少于 6 个的值从数据库中检索出来时能够自动地用空格填充。需要注意的是，使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。
　　万一我们需要对一个字段存储一个超出许可范围的数字，MySQL 会根据允许范围最接近它的一端截短后再进行存储。还有一个比较特别的地方是，MySQL 会在不合规定的值插入表前自动修改为 0。
　　UNSIGNED 修饰符规定字段只保存正值。因为不需要保存数字的正、负符号，可以在储时节约一个“位”的空间。从而增大这个字段可以存储的值的范围。
　　ZEROFILL 修饰符规定 0（不是空格）可以用来真补输出的值。使用这个修饰符可以阻止 MySQL 数据库存储负值。
FLOAT、DOUBLE 和 DECIMAL 类型

　　MySQL 支持的三个浮点类型是 FLOAT、DOUBLE 和 DECIMAL 类型。FLOAT 数值类型用于表示单精度浮点数值，而 DOUBLE 数值类型用于表示双精度浮点数值。
　　与整数一样，这些类型也带有附加参数：一个显示宽度指示器和一个小数点指示器。比如语句 FLOAT(7,3) 规定显示的值不会超过 7 位数字，小数点后面带有 3 位数字。
　　对于小数点后面的位数超过允许范围的值，MySQL 会自动将它四舍五入为最接近它的值，再插入它。
　　DECIMAL 数据类型用于精度要求非常高的计算中，这种类型允许指定数值的精度和计数方法作为选择参数。精度在这里指为这个值保存的有效数字的总个数，而计数方法表示小数点后数字的位数。比如语句 DECIMAL(7,3) 规定了存储的值不会超过 7 位数字，并且小数点后不超过 3 位。
　　忽略 DECIMAL 数据类型的精度和计数方法修饰符将会使 MySQL 数据库把所有标识为这个数据类型的字段精度设置为 10，计算方法设置为 0。
　　UNSIGNED 和 ZEROFILL 修饰符也可以被 FLOAT、DOUBLE 和 DECIMAL 数据类型使用。并且效果与 INT 数据类型相同。
字符串类型

　　MySQL 提供了 8 个基本的字符串类型，可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据。
类型
大小
用途
CHAR
0-255字节
定长字符串
VARCHAR
0-65536字节
变长字符串
TINYBLOB
0-255字节
不超过 255 个字符的二进制字符串
TINYTEXT
0-255字节
短文本字符串
BLOB
0-65 535字节
二进制形式的长文本数据
TEXT
0-65 535字节
长文本数据
MEDIUMBLOB
0-16 777 215字节
二进制形式的中等长度文本数据
MEDIUMTEXT
0-16 777 215字节
中等长度文本数据
LOGNGBLOB
0-4 294 967 295字节
二进制形式的极大文本数据
LONGTEXT
0-4 294 967 295字节
极大文本数据
CHAR 和 VARCHAR 类型

　　CHAR 类型用于定长字符串，并且必须在圆括号内用一个大小修饰符来定义。这个大小修饰符的范围从 0-255。比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补。
　　CHAR 类型可以使用 BINARY 修饰符。当用于比较运算时，这个修饰符使 CHAR 以二进制方式参于运算，而不是以传统的区分大小写的方式。
　　CHAR 类型的一个变体是 VARCHAR 类型。它是一种可变长度的字符串类型，并且也必须带有一个范围在 0-255 之间的指示器。CHAR 和 VARCHGAR 不同之处在于 MuSQL 数据库处理这个指示器的方式：CHAR 把这个大小视为值的大小，不长度不足的情况下就用空格补足。而 VARCHAR 类型把它视为最大值并且只使用存储字符串实际需要的长度（增加一个额外字节来存储字符串本身的长度）来存储值。所以短于指示器长度的 VARCHAR 类型不会被空格填补，但长于指示器的值仍然会被截短。
　　因为 VARCHAR 类型可以根据实际内容动态改变存储值的长度，所以在不能确定字段需要多少字符时使用 VARCHAR 类型可以大大地节约磁盘空间、提高存储效率。
　　VARCHAR 类型在使用 BINARY 修饰符时与 CHAR 类型完全相同。
TEXT 和 BLOB 类型

　　对于字段长度要求超过 255 个的情况下，MySQL 提供了 TEXT 和 BLOB 两种类型。根据存储数据的大小，它们都有不同的子类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型。
　　TEXT 和 BLOB 类型在分类和比较上存在区别。BLOB 类型区分大小写，而 TEXT 不区分大小写。大小修饰符不用于各种 BLOB 和 TEXT 子类型。比指定类型支持的最大范围大的值将被自动截短。

日期和时间类型

　　在处理日期和时间类型的值时，MySQL 带有 5 个不同的数据类型可供选择。它们可以被分成简单的日期、时间类型，和混合日期、时间类型。根据要求的精度，子类型在每个分类型中都可以使用，并且 MySQL 带有内置功能可以把多样化的输入格式变为一个标准格式。
类型
大小
(字节)
范围
格式
用途
DATE
3
1000-01-01/9999-12-31
YYYY-MM-DD
日期值
TIME
3
'-838:59:59'/'838:59:59'
HH:MM:SS
时间值或持续时间
YEAR
1
1901/2155
YYYY
年份值
DATETIME
8
1000-01-01 00:00:00/9999-12-31 23:59:59
YYYY-MM-DD HH:MM:SS
混合日期和时间值
TIMESTAMP
4
1970-01-01 00:00:00/2037 年某时
YYYYMMDD HHMMSS
混合日期和时间值，时间戳
DATE、TIME 和 TEAR 类型

　　MySQL 用 DATE 和 TEAR 类型存储简单的日期值，使用 TIME 类型存储时间值。这些类型可以描述为字符串或不带分隔符的整数序列。如果描述为字符串，DATE 类型的值应该使用连字号作为分隔符分开，而 TIME 类型的值应该使用冒号作为分隔符分开。
　　需要注意的是，没有冒号分隔符的 TIME 类型值，将会被 MySQL 理解为持续的时间，而不是时间戳。
　　MySQL 还对日期的年份中的两个数字的值，或是 SQL 语句中为 TEAR 类型输入的两个数字进行最大限度的通译。因为所有 TEAR 类型的值必须用 4 个数字存储。MySQL 试图将 2 个数字的年份转换为 4 个数字的值。把在 00-69 范围内的值转换到 2000-2069 范围内。把 70-99 范围内的值转换到 1970-1979 之内。如果 MySQL 自动转换后的值并不符合我们的需要，请输入 4 个数字表示的年份。
DATEYIME 和 TIMESTAMP 类型

　　除了日期和时间数据类型，MySQL 还支持 DATEYIME 和 TIMESTAMP 这两种混合类型。它们可以把日期和时间作为单个的值进行存储。这两种类型通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。
　　如果我们对 TIMESTAMP 类型的字段没有明确赋值，或是被赋与了 null 值。MySQL 会自动使用系统当前的日期和时间来填充它。

复合类型

　　MySQL 还支持两种复合数据类型 ENUM 和 SET，它们扩展了 SQL 规范。虽然这些类型在技术上是字符串类型，但是可以被视为不同的数据类型。一个 ENUM 类型只允许从一个集合中取得一个值；而 SET 类型允许从一个集合中取得任意多个值。

ENUM 类型

　　ENUM 类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排拆的数据时容易让人理解，比如人类的性别。ENUM 类型字段可以从集合中取得一个值或使用 null 值，除此之外的输入将会使 MySQL 在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配，MySQL 会自动使用插入值的大小写转换成与集合中大小写一致的值。
　　 ENUM 类型在系统内部可以存储为数字，并且从 1 开始用数字做索引。一个 ENUM 类型最多可以包含 65536 个元素，其中一个元素被 MySQL 保留，用来存储错误信息，这个错误值用索引 0 或者一个空字符串表示。
　　MySQL 认为 ENUM 类型集合中出现的值是合法输入，除此之外其它任何输入都将失败。这说明通过搜索包含空字符串或对应数字索引为 0 的行就可以很容易地找到错误记录的位置。
SET 类型

　　SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。并且与 ENUM 类型相同的是任何试图在 SET 类型字段中插入非预定义的值都会使 MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录，MySQL 将会保留合法的元素，除去非法的元素。
　　一个 SET 类型最多可以包含 64 项元素。在 SET 元素中值被存储为一个分离的“位”序列，这些“位”表示与它相对应的元素。“位”是创建有序元素集合的一种简单而有效的方式。并且它还去除了重复的元素，所以 SET 类型中不可能包含两个相同的元素。
　　希望从 SET 类型字段中找出非法的记录只需查找包含空字符串或二进制值为 0 的行。

mysql用户管理:
----------------------------------------------------`
-- mysql权限管理篇
-- 查看系统中的用户
select user,host,password from mysql.user;

-- 建立账号并指定登录地址,只有登录的权限，并没有其它的操作的权限
create user "admin"@"localhost" identified by "kkk";

-- 如果在创建用户不指定登录主机的时候，则默认的情况是所有的主机都是可以访问的
create user zongbin identified by "kkk";

-- 这样的创建的话，任何主机都是可用登录，且不需要密码，但是也没有相关的操作的权限
create user zhilei;

-- 刷新权限表
flush privileges;

-- 定义查看的权限
grant select on work.t1 to zhilei;

-- 去掉权限
revoke select on work.t1 from zhilei;

-- 查看当前的用户

select user();

grant all privileges on *.*

mysql触发器
------------------------------------
-- 新建触发器

show tables;

create table t1(
id int not null auto_increment,
tname varchar(20),
sscore tinyint unsigned,
primary key(id))engine=innodb default charset=utf8

create table t2 like t1;

show tables;

desc t1;
desc t2;

insert into t1 values(null,'小明',30);
insert into t1 values(null,'小黄',60);
insert into t1 values(null,'小殷',56);
insert into t1 values(null,'小马',37);
insert into t1 values(null,'小宗',67);
insert into t1 values(null,'小钱',99);

select * from t1;

select * from t2;

delimiter //
-- 定认插入触发器
create trigger charu after insert on t1 for each row
begin
if new.sscore <60 then
insert into t2 select * from t1 where new.id = t1.`id`;
end if; -- end if 结尾要有分号
end//
delimiter ;

-- ###########################################################
-- 定义删除触发器
delimiter //
create trigger shanchu after delete on t1 for each row
begin
delete from t2 where id = old.id;
end//
delimiter ;

-- ###################################################

-- 定义修改触发器

delimiter //
create trigger xiu after update on t1 for each row
begin
if old.sscore >60 then
if new.sscore <60 then
insert into t2 select * from t1 where t1.`id`=new.id;
end if;

else
if new.sscore > 60 then
delete from t2 where id=new.id;
else
replace into t2 select * from t1 where t1.`id`=new.id;
end if;

end if;
end//
delimiter ;

select * from t1;
select * from t2;
update t1 set sscore=56 where id=1;

mysql总结性操作:
--------------------------------------------
时间的数据类型

datetime 日期+时间（日期要以 2001-4-4 3:4:5
date 日期
time
timestamp 它是一人时间戳会随时的更新，只要你操作就会更新

1.查看有哪些的数据库
show databases;
2.使用哪个数据库
use work
3. 查看哪个数据库里面有哪些数据表
show tables;

创建数据库
1.了解数据的类型
2. 对于主键，唯一键，与外键，默认键，非空键进行了解
3. 对于数据库的引擎进行了解（myisam innodb)
4. 对于数据库中数据表有增删改查的操作
5. 对于数据库的集合函数进行操作
6. 了解sql语言间的简单的流程控制
6.对于数据库中的视图进行了解

Char与varchar的区别：
CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。

删除外键
alter table AdItem drop constraint c

添加外键
alter table AdItem
add constraint c foreign key (AI_nOrderNo) references AdOrder(AO_nOrderNo)

添加主键
alter table tt add constraint kk primary key()

删除主键
alter table tt drop primary key;
------------------------------------------------------------------------------------------------------

SQL正则
delete form tt where sname regexp "";

SHOW TABLES;
SHOW FULL COLUMNS FROM tt;
INSERT INTO tt(tname) VALUES('张三'),('李四'),('王五');
INSERT INTO tt(tname) VALUES('张三丰'),('李四'),('王五');
INSERT INTO tt(tname) VALUES('宗斌'),('李桐'),('王五');
INSERT INTO tt(tname) VALUES('陆瑞'),('杨敏'),('王五');
INSERT INTO tt(tname) VALUES("黄恺"),('李四'),('王五');
INSERT INTO tt(tname) VALUES('马维奇'),('李四同'),('王五');
SELECT * FROM tt;

SELECT * FROM tt WHERE tname REGEXP "李四";
SELECT * FROM tt WHERE tname REGEXP "李|杨|宗";

show tables;

drop table one,tt;

-- 创建一个数据表

create table one(
id int not null auto_increment primary key,
sname varchar(30) not null default "监控中心");

alter table one add index kk(sname);

-- 删除索引的两种方法
alter table one drop index kk;
drop index kk on one;

添加主键
alter table one add constraint kk foreign key(laoshi) reference one(id)
删除外键：
alter table one drop foreign key kk;

-- 删除索引
alter table t drop index kk;
-- 添加索引
alter table t add index kk(sname,sscore);
-- 删除索引
drop index kk on t;
alter table t add index kk(sname,sscore);
-- 删除唯一索引

-- 查看所有的索引

show index from bb;

或者是加复数的形式

show indexed from bb;

-- 添加唯一索引

alter table tt add constraint kk unique key(id)

-- 删除唯一索引

按分组来查询
select pro 专业,count(sname) 人数 from t group by pro having count(*) >2;

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

show tables;

-- 删除表

drop table one;

-- 创建表

create table if not exists student (
sid tinyint unsigned not null auto_increment primary key,
sname varchar(30) ,
sscore tinyint ,
index kk(sname,sscore),
pro char(30));

-- 删除索引
alter table t drop index kk;
-- 添加索引
alter table t add index kk(sname,sscore);
-- 删除索引
drop index kk on t;

alter table student rename t;

desc t;

insert into t values(null,"张三",40,"计算机");
insert into t values(null,"李四",59,"会计");
insert into t values(null,"王志雷",40,"计算机");
insert into t values(null,"殷超",40,"美术");
insert into t values(null,"宗斌",65,"java");
insert into t values(null,"张总",null,"武术")

insert into t (sname,sscore,pro) select sname ,sscore ,pro from t;

select * from t;

select distinct pro from t;

select * from t where sscore >40;

select count(*) from t;

select pro 专业,avg(sscore)平均分 from t group by pro having avg(sscore)>40 ;

select * from t;

select * from t where sname="张三";

select pro 专业,count(sname) 人数 from t group by pro having count(*) >2;

select count(*) from t where pro="计算机";

select * from t;
-- 查询内容
select sname 姓名 ,avg(sscore) from t group by sname;

-- 查分姐中每个专业的成绩的总合
select pro 专业,sum(sscore) from t group by pro having sum(sscore) <100;

-- 查看不同专业的有几个人

select pro ,count(*) from t group by pro having count(*)>4;

-- 查询相关专业的平均分数（并且将专业成总合等于200的给找出来）
select pro 专业, avg(sscore) from t group by pro having sum(sscore)<200;

select * from t where sscore = (select max(sscore) from t);

show tables;
drop table t;

-- 建立老师表
create table t(
id int not null auto_increment ,
sname varchar(30),
primary key(id));

-- 向老师表插入相应的数据
insert into t values(null,"王老师");
insert into t values(null,"张老师");
insert into t values(null,"殷老师");

-- 查看老师表
select * from t;

-- 建立学生表
create table s(
id int not null auto_increment,
sname varchar(40),
laoshi int,
primary key(id));

-- 向学生表中插入数据

insert into s values(null,"殷超",3);
insert into s values(null,"陆瑞",2);
insert into s values(null,"宗斌",1);

-- 查看学生数据
select * from s;

-- 跨表进行查询
select * from t,s where t.id=s.laoshi;

-- 跨表查询
select s.sname 姓名,t.sname from t,s where t.id=s.laoshi;

-- 另外两种的查询的方法

select t.sname 老师, s.sname 学生 from t left join s on t.id=s.laoshi;

select s.sname 学生, t.sname 老师学生 from s right join t on t.id=s.laoshi;

-- 另外的查询的方式
select * from t left join s on s.laoshi=t.`id`;

select * from t right join s on t.id=s.`laoshi`;

select t.sname,s.`sname` from t join s on s.`laoshi`=t.`id`;

-- 左插与右插是一样的，

常用的函数

length() 查看字节数

-- UTF8的一个字是三个字节
select sname ,length(sname) from t;

-- GBK的一个字是两个字节
create table t1(
sname char(10))engine=myisam default charset=gbk;

insert into t1 values('中国');

select * from t1;

select sname ,length(sname) from t1;

-- 全球唯一算法
select uuid();

select uuid_short();

-- 从数据表中随机出一个数

select * from t order by rand() limit 1;

时间函数

-- 当前的系统的日期
select curdate();

-- 查看当前的时间

select curtime();

-- 查看当前的日期+时间

select now();

-- 常用来取一个取间，如一周内注册的个数

select count(*) from login where logtime between "2016-6-24" and curdate();

查看一周内注册会员的数据

select count(*) from tt where regtime between date_add(curdate(),interval -7 day) and curdate();

select count(*) from tt where regtime between date_add(curdate(),interval -1 month) and curdate();

-- 求两个时间的差值,通常用来求，一个会员已经注册了多久等时间差值datediff(大时间，小时间 );

select datediff(curdate(),"1990-01-29");

-- 求两个时间的差值,通常用来求，一个会员已经注册了多久等时间差值datediff(大时间，小时间 );

select datediff(curdate(),"1990-01-29");

-- 求两个时间的差值,通常用来求，一个会员已经注册了多久等时间差值datediff(大时间，小时间 );

select datediff(curdate(),"1990-01-29");

-- 求两个时间差代表当前的时间减去两天的数字就是前两天的时间，如果-2的话则是两天后了（这个与date_add()相反

select date_sub(curdate(),interval 2 day);

-- 加密函数 (都是单向加密，不能解密）

select md5('aa'),sha('aa'),sha1('aa'),password('aa');

-- 查看字符个数

select char_length(md5('aa')),char_length(sha('aa')),char_length(sha1('aa')),char_length(password('aa'));

-- char_length(md5('aa')) char_length(sha('aa')) char_length(sha1('aa')) char_length(password('aa'))
-- 32 40 40 41

-- 全球唯一随机生成的ID号
select uuid();

-- 36位密文长度36
select char_length(uuid());

-- 数字的格式化

select format(10.445,2);

-- 将 IP地址转换成数字(里面的IP要加引号引起来)

select inet_ntoa('3232238089');

-- 将地址转换为ip

select inet_aton('192.168.10.9');

-- 流程判断 case when then end

select sname 姓名 ,english 英语成绩,case when english >90 then "优秀"
when english >= 80 then "良好"
when english <60 then "不及格"
end 等级 from t;

-- 流程判断2 （最后一个else一定要加）（就是补考的位置）

select sname 姓名, english 英语成绩,
if(english>90,"优秀",if(english<60,"不及格","补考")) 等级 from t;

-- 时间格式化
select date_format('19900129',"%Y年%m月%d旧");
select date_format('19900129',"%H");

下面是时间的格式化参数

Specifier Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2;
used with %X
%v Week (01..53), where Monday is the first day of the week; WEEK() mode 3;
used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)

mysql认识:
---------------------------------------
本节课的学习的目标
1.对于mysql进行简单的认识
2.对于数据库的增删改查进行操作
3.数据的类型
4.对于数据查询的条件进行了解
5.对于集合函数有一定的认识

唯一标识就是主键（一个是唯一性也就是说主键是不能重复的，且主键是不能为空

查询一个集合中的数据
select * from tt where id in(4,6);

同样的结果我们还有另外的一种查询方法

select * from tt where id=4 or id=6;

查询一个区间内的数据（这个数据是连续性的，中间也是可以出现断层现象）
select * from tt where id between 4 and 6;

= > >= < <= <> != and or like in betwen regexp

like用法
select * from tt where sname like "志%";

监控中心线上管理平台实现一个模糊的查询效果的话就是封装sql进行模糊查询

正则表达式（正则表达式一定要与通配符区分开来）

1.通配符中的“*”代表的任意的一个或者是多个字符
2.在正则表达式中代表的是个数（用来修饰前面的原子出现的0个或者是多个）
select * from tt where sname regexp "[^志]";
"=" 在一般的语言当中“=”是赋值的符号 “==”是比较的符号
而在sql语言中“=”是比较的符号

数据的插入

主要有三种的数据插入方式

第一种insert [into] table_name (sname ,pro) values("","");

第二种 insert into table_name values(null,"","","",);
insert into tt values(null,"老马","java");

第三种的插入方式
insert into table_name select * from tt;

(这样操作是不对的，因为所有的数据当中就有主键就不能重复)

insert into table_name (sname ) select sname from tt.;

constraint(约束)
PK 主键
UK 唯一键
FK 外键
key(index)

时间的数据类型

datetime 日期+时间（日期要以 2001-4-4 3:4:5
date 日期
time
timestamp 它是一人时间戳会随时的更新，只要你操作就会更新

1.查看有哪些的数据库
show databases;
2.使用哪个数据库
use work
3. 查看哪个数据库里面有哪些数据表
show tables;

删除外键

添加外键
alter table AdItem
add constraint c foreign key (AI_nOrderNo) references AdOrder(AO_nOrderNo)

添加主键
alter table tt add constraint kk primary key()

删除主键
alter table tt drop primary key;
------------------------------------------------------------------------------------------------------

SQL正则
delete form tt where sname regexp "";

SELECT * FROM tt WHERE tname REGEXP "李四";
SELECT * FROM tt WHERE tname REGEXP "李|杨|宗";

show tables;

drop table one,tt;

-- 创建一个数据表

create table one(
id int not null auto_increment primary key,
sname varchar(30) not null default "监控中心");

alter table one add index kk(sname);

-- 删除索引的两种方法
alter table one drop index kk;
drop index kk on one;

添加外键
alter table one add constraint kk foreign key(laoshi) reference one(id)
删除外键：
alter table one drop foreign key kk;

-- 删除索引
alter table t drop index kk;
-- 添加索引
alter table t add index kk(sname,sscore);
-- 删除索引
drop index kk on t;

按分组来查询
select pro 专业,count(sname) 人数 from t group by pro having count(*) >2;

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

show tables;

-- 删除表

drop table one;

-- 创建表

create table if not exists student (
sid tinyint unsigned not null auto_increment primary key,
sname varchar(30) ,
sscore tinyint ,
index kk(sname,sscore),
pro char(30));

-- 删除索引
alter table t drop index kk;
-- 添加索引
alter table t add index kk(sname,sscore);
-- 删除索引
drop index kk on t;

alter table student rename t;

desc t;

insert into t (sname,sscore,pro) select sname ,sscore ,pro from t;

select * from t;

select distinct pro from t;

select * from t where sscore >40;

select count(*) from t;

select pro 专业,avg(sscore)平均分 from t group by pro having avg(sscore)>40 ;

select * from t;

select * from t where sname="张三";

select pro 专业,count(sname) 人数 from t group by pro having count(*) >2;

select count(*) from t where pro="计算机";

select * from t;
-- 查询内容
select sname 姓名 ,avg(sscore) from t group by sname;

-- 查分姐中每个专业的成绩的总合
select pro 专业,sum(sscore) from t group by pro having sum(sscore) <100;

-- 查看不同专业的有几个人

select pro ,count(*) from t group by pro having count(*)>4;

-- 查询相关专业的平均分数（并且将专业成总合等于200的给找出来）
select pro 专业, avg(sscore) from t group by pro having sum(sscore)<200;

select * from t where sscore = (select max(sscore) from t);

show tables;
drop table t;

-- 建立老师表
create table t(
id int not null auto_increment ,
sname varchar(30),
primary key(id));

-- 向老师表插入相应的数据
insert into t values(null,"王老师");
insert into t values(null,"张老师");
insert into t values(null,"殷老师");

-- 查看老师表
select * from t;

-- 建立学生表
create table s(
id int not null auto_increment,
sname varchar(40),
laoshi int,
primary key(id));

-- 向学生表中插入数据

insert into s values(null,"殷超",3);
insert into s values(null,"陆瑞",2);
insert into s values(null,"宗斌",1);

-- 查看学生数据
select * from s;

-- 跨表进行查询
select * from t,s where t.id=s.laoshi;

-- 跨表查询
select s.sname 姓名,t.sname from t,s where t.id=s.laoshi;

-- 另外两种的查询的方法

select t.sname 老师, s.sname 学生 from t left join s on t.id=s.laoshi;

select s.sname 学生, t.sname 老师学生 from s right join t on t.id=s.laoshi;

-- 另外的查询的方式
select * from t left join s on s.laoshi=t.`id`;

select * from t right join s on t.id=s.`laoshi`;

select t.sname,s.`sname` from t join s on s.`laoshi`=t.`id`;

-- 左插与右插是一样的，

常用的函数

length() 查看字节数

-- UTF8的一个字是三个字节
select sname ,length(sname) from t;

-- GBK的一个字是两个字节
create table t1(
sname char(10))engine=myisam default charset=gbk;

insert into t1 values('中国');

select * from t1;

select sname ,length(sname) from t1;

-- 全球唯一算法
select uuid();

select uuid_short();

-- 从数据表中随机出一个数

select * from t order by rand() limit 1;

时间函数

-- 当前的系统的日期
select curdate();

-- 查看当前的时间

select curtime();

-- 查看当前的日期+时间

select now();

-- 常用来取一个取间，如一周内注册的个数

select count(*) from login where logtime between "2016-6-24" and curdate();、
、
抓包命令
tcpdump -i eth0 -nnX port 21

insert into t3 values(null,"1990-01-29 23:00:00","柏浩")

-- 查看当前的mysql版本所支持的引擎名
show engines;

-- 第一个参数，只能存话表达式，并不能存其它的值
select if(4<0,"c","d");\

select inet_aton('192.168.254.13'); -- 结果是 3232300557

-- 如果ping 3232300557 也是可以ping通

-- SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19'); 将日期+时间变成时间戳

select unix_timestamp('2016-06-26 23:49:20');

-- 从时间戳变成日期
select from_unixtime(1466956160);

-- 将时间进行格式化
select date_format(now(),"%Y年%m月%d日 %T");

-- 将字转成大家

select upper('hello');

-- 将字转成小定

select lower('HELLO');

-- 截取左面的字符数

select left('王志雷',2);

-- 截取右面的字符数

select right('监控中心',3);

-- 截取中间的字数符(从第二个字符开始截取，（下标第一个为1）取多个少）

select mid('abcd',2,3);

-- 取余
select mod(5,3);
show tables;

drop table t,t3;

create table t(
id int not null auto_increment primary key,
tname varchar(30) not null default "aa",
tgener enum('男','女',"保密")
)engine=innodb default character set =utf8;

insert into t (tname) values(left('abc',2));

select * from t;

alter table t change tgener tgender enum('男','女');

select * from t order by rand() limit 1;

-- 字符连接

select concat('王',"五");

-- 以一个字符串来连接

select concat_ws("_","志","雷");

-- 对于内空进行调整、如果则显示后面的内容,只有等于null的情况下才会执行第二个参数的内容，如果不是空的话则输出第一个参数

select ifnull(null,"未");

select tname 姓名,ifnull(tgender,"未知") 性别 from t;

-- 如果A条件成立，则执行第二个参数，否则不成立的话则执行第三个 “第一个内容为字符串的话则系统会默认的认为是假
select if("志雷","b","c");

-- 相当于if(第一个参数) 真的话执行第二个参数假的话执行第三个参数正常的情况下都是放的是表达式

-- 创建视图
create view test as
select * from t;

-- 视频相当于函数一样，但是函数是用select直接来调用，而视图相当于一个表，

-- 我们要用select * from 视图名长来显示里面的数据
select * from test;

删除视图
-- drop view test

-- 新建触发器

show tables;

create table t1(
id int not null auto_increment,
tname varchar(20),
sscore tinyint unsigned,
primary key(id))engine=innodb default charset=utf8

create table t2 like t1;

show tables;

desc t1;
desc t2;

select * from t1;
select * from t2;

delimiter //
create trigger charu after insert on t1 for each row
begin
if new.sscore <60 then
insert into t2 select * from t1 where new.id = t1.`id`;
end if;
end//
delimiter ;

posted @ 2018-03-18 16:05 雷神约阅读(213) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

让学习成为一种习惯

Mysql自学笔记

公告