MySql Handbook.sql

//==================================================================
//------------------------常用字段解释------------------------------
一个汉字		 = 	三个字节
一个字母、数字只 = 	一个字节

BLOB、TEXT、varchar
-- 是变长类型，对于其存储需求取决于列值的实际长度*

BLOB和TEXT	列不能有默认值,超出截断

varchar(n) 这里的n代表字符数,无论是汉字还是英文字母或是数字都可以存n个。
varchar 在5.03以前长度可以为0~255, 之后是65535

TINYTEXT 最大长度是 255 (2^8 - 1) 个字符。
TEXT 最大长度是 65535 (2^16 - 1) 个字符				0.062499 兆字节(mb) 0.06M
MEDIUMTEXT 最大长度是 16777215 (2^24 - 1) 个字符	15.999999 兆字节(mb) 16M
LONGTEXT 最大长度是 4294967295 (2^32 - 1) 个字符	4 千兆字节(gb)	4G

int(m)		m表示显示的位数,
如果int的值为10,int（10）显示结果为0000000010,int（3）显示结果为010,
就是显示的长度不一样而已,都是占用四个字节的空间。

bigint		从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807),8字节。
int			从 -2^31 (-2,147,483,648) 到 2^31 – 1 (2,147,483,647),4字节。
smallint	从 -2^15 (-32,768) 到 2^15 – 1 (32,767),2字节。
tinyint		从 0 到 255,1字节。
//==================================================================
//-------------------------常用sql语句-----------------------------
	
		增
	
	insert into tableName(title,name) values("","");
	
		删
	
	delete from tableName where id = 12;
	
		改
	
	update tableName set title = "",name = "" where id = 12;
	
		查
	
	select id,title,name from tableName [where][group][order][limit];

	改表字段与表名
		
	alter table 表名
				add		name char not null;
				drop		name;
				change 	旧字段名 新字段名 char not null;
	alter table 表名 modify column 字段名 varchar(20);
				rename as	新表名;
				add column id int not null auto_increment UNIQUE after fieldname;
	
	创建索引
	
	alter table tb_name add index	index_name(name);
	alter table tb_name add unique	unique_name(name);
		
	删除索引
	
	alter table tb_name drop index index_name;
	
	显示索引
	
	show index from tb_name;
	
	显示创建表
	
	show create table tb_name;
	
	多表查询
	
	select * from user1 a,user2 b where a.id=b.id order by id desc
	
	select	表.字段,表.字段from表1,表2 where 表1的id=表2的id;
	例：select bbs_content.content,bbs_reply.title from bbs_content,bbs_reply where bbs_content.id=bbs_reply.cid;
		
	select xx.字段 as xxx from tb_name as xx;
	
	(as后为表名或显示名,as 当做)
	
	
		批量操作
	
	update from tb_name set jiajing=1 where id in(1,2,3);
	
	其他
	
	show columns from tbl_name;			列出资料表字段
	show full columns from tbl_name;	列出字段及详情
	
	show fields from tbl_name;			列出字段属性
	show full fields from tbl_name;		列出字段完整属性
	
	show status;						列出 db server 状态
	show table status;					列出表的状态信息
	
	show variables;						列出 mysql 系统环境变量
	show processlist;					列出执行命令
	show grants for user;				列出某用户权限

	执行顺序：
	from... where...group by... having.... select ... order by...
//==================================================================
//----------------------------insert--------------------------------

	表中插入数据

		insert into tb_name value('1','emma');
		insert into tb_name(id,name) values('1','emma'),('2','justin');
		
	将数据从一个表导入另一个表
		
		整表导入
		insert into tb_name select * from tb_name_two;
		
		单字段导入
		insert into tb_name(name) select name from tb_name_two;

//==================================================================
//----------------------------delete--------------------------------

	删除表中数据

		清空整个表的数据
		delete from tb_name;
		
		删除表中某条数据
		delete from tb_name where id=1;
	
//==================================================================
//----------------------------update--------------------------------

	修改表中数据

		update tb_name set name='justin',age='21' where id='2';
		
		更新表时运算
		update tb_name set price=price+1 where id=20;	//价钱自加1
//==================================================================
//----------------------------select--------------------------------

	select * from tb_name;
	select id,name from tb_name;
	
	为查看的字段起别名	关键字： as 或 空格
	select name as new_name from tb_name;
	select name new_name from tb_name;
	
	查询每行不重复的数据	关键字： distinct
	select distinct name from tb_name;
	
	查询时加入运算
	select name,price,price*0.8 dzprice from tb_name;	//price*0.8 dzprice 将价钱乘以0.8并起别名dzprice
	
	查询条件
	select * from tb_name where price < 100;
	
	多表查询
	select tb_name1.age,tb_name2.age from tb_name1,tb_name2;
				表起别名
	select new_name1.age,new_name2.age from tb_name1 as new_name1,tb_name2 as new_name2;
				字段和表同起别名(未加where 出现乘积)
	select xx.age as xxx,cc.age as ccc from tb_name1 as xx,tb_name2 as cc;
				加上where查询
	select tb_name1.age as age1,tb_name2.age as age2 from tb_name1,tb_name2 where tb_name1.id=tb_name2.id;
	
	嵌套查询
	select * from tb_name1 where cid in(select id from tb_name2 where name like 'j%');
	
	select * from tb_name where name like '%a%';
	select * from tb_name where name like '%a__';	右边包含两个字符
	select * from tb_name where name like '__a%';	左边包含两个字符
	
	//--------------------------------------------------------------
	//------------------[where][group][order][limit]----------------
		
	分组:(不出现重复内容,分类汇总)
		[group by 分组字段]
		[group by 分组字段 having 条件]
		
	排序 order by
		[order by 字段 asc] 正序
		[order by 字段 desc]倒序
		[order by 排序字段]
		
		order by id asc age desc
		order by id age asc
		order by id desc age desc
		
		select * from tb_name order by id asc;
		
	显示条数 limit
		[limit 显示条数]
		例：limit 5;
		[limit 起始位置,显示条数]
		例:limit 0,5;
		
		选出下一条记录（php翻页sql语句）
		select * from tb_name where id>14 order by id asc limit 0,1;

//==================================================================
//---------------------------创建表--------------------------------

	create table db_name.tb_name(id int);
	
		防止报错
	create table  if not exists db_name.tb_name(id int);
	
		创建表的SQL语句模型
	create table [if not exists] tb_name(
		字段1 列类型 [属性][索引]
		字段2 列类型 [属性][索引]
	)[表类型][表字符集];
	
		创建表中途退出命令 \c

	建表实例
		create table tb_name(
			id int(11) unsigned not null auto_increment,
			pid int(11) unsigned not null,
			name varchar(10) not null default '',
			height double(7,2) not null default '0.00',
			area char(8) not null default 'BJ',
			sex enum('1','2','3') not null default '2',
			content text not null,
			picture blob,
			primary key(id),
			index(name,sex),
			unique key `pid` (`pid`)
		)engine=myisam default charset=utf8;
		
	-- range分区
		CREATE TABLE employees (
			id INT NOT NULL,
			store_id INT NOT NULL
		)engine=myisam default charset=utf8
			PARTITION BY RANGE (store_id) (
			PARTITION p0 VALUES LESS THAN (6),
			PARTITION p1 VALUES LESS THAN (11)
		);
		
	-- list分区 分表的字段必须是主键索引
		CREATE TABLE IF NOT EXISTS `goods` (
		  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
		  `season` tinyint (1) not null default '2' comment '季节',
		  primary key(id,season)
		)engine=MyISAM default charset=utf8 partition by list (season)(
			partition g1 values in (1),
			partition g2 values in (2),
			partition g3 values in (3),
			partition g4 values in (4)
		);
	-- hash分区
	-- key分区
	
//==================================================================
//----------------------------删除表-------------------------------

	删除表
		drop table tb_name;
		防止出错
		drop if exists table tb_name;

//==================================================================
//---------------------------修改表--------------------------------

	修改表 alter table
		添加字段	关键字: add
		alter table tb_name add name varchar(30) not null;
		
		在某个字段后添加字段  关键字：after
		alter table tb_name add name varchar(20) after id;
		
		添加一个首字段	关键字：first
		alter table tb_name add height double first;
		
		更改表字段类型	关键字: modify
		alter table tb_name modify sex char(3);	//sex 为要修改类型的字段 char(3)为修改为的类型
		
		更改字段名	关键字: change
		alter table tb_name change name new_name varchar(30) default 'emma';	//name为原名 username为改为名
		
		更改表名	关键字: rename as
		alter table tb_name rename as new_tb_name;
		
		删除字段	关键字: drop
		alter table tb_name drop age;
		
		创建索引
		alter table tb_name add index(index_name,index_name2) ;
		
		删除索引
		alter table tb_name drop index(index_name,index_name2) ;
		
//==================================================================
//----------------------------库操作-------------------------------

	创建数据库
		create database db_name;
			防止报错,加上 if not exists 如果不存在这创建
		create database if not exists db_name;
		create database character set utf8 collate utf8_bin;
		
	删除数据库
		drop database db_name;
			防止报错加上 if exists 如果存在则删除
		drop database if exists db_name;


//==================================================================
//---------------------------创建索引------------------------------

	1.主键索引 primary key
		主要作用是确定数据库表里一条特定数据记录的位置
		最好为每一张数据表定义一个主键
		一个表只能指定一个主键索引
		主键值不能为空
		
	2.唯一索引 unique
		避免数据重复
		都可以创建重复的值
		每个表都可以有多个唯一索引
		
	3.常规索引 index in_name
		提升了查询速度
		
			创建索引
		create index in_name on tb_name(name,age);
			创建表时创建索引
		create table tb_name(
			id int not null,
			sid int not null,
			num int not null,
			index(num,sid)
		);
			删除索引
		drop index in_name on tb_name;
		
		index 为创建索引
		in_name 为索引名
		tb_name 为添加索引的表
		index 与 key 同意

	4.全文索引 fulltext
		只能用在 MyISAM 表类型使用
		只有在varchar char text文本字符串上使用
		也可以多个数据列使用
		create table books(
			id int,
			bookname varchar(30),
			price double,
			detail text not null,
			fulltext(detail,bookname),	//创建全文索引
			index ind(price)
		);
		select bookname,price from books where match(要查询的字段) against('包含的内容');
		
			创建索引
		alter table tb_name add index index_name name;
			删除索引
		alter table tb_name drop index index_name;
			显示索引
		show index from tb_name;
//==================================================================
//----------------------------表类型-------------------------------

	MySQL有存储引擎的概念
	MySQL可以针对不同的存储引擎需求可选择最优的存储引擎
	
	查看所有引擎
		show engines;
	
	查看默认引擎
		show variables like 'table_type';
		我们只学 MyISAM 和 InnoDB两个
	
	创建表时自定义表引擎
		create table() engine=InnoDB;
		engine 与 type 为同义词
	
	注意：在同一个库中可以创建不同表类型
	
	MyISAM 表类型特点
		optimize table 表名;	//用于整理表
		强调快速读取操作
	
	InnoDB 表类型
		支持一些 MyISAM 所不支持的功能
		缺点：占用空间大
		
	功能			MyISAM			InnoDB
	事物处理		不支持			支持
	数据行锁定		不支持			支持
	外键约束		不支持			支持
	表空间占用		相对小			相对大 最大2倍
	全文索引		支持			不支持
//==================================================================
//---------------------------数据类型------------------------------

	1.数值型
		(1)整型(整数)
		非常小的整形	1字节(2^8)	-128~127		0-255(无符号)
		较大的整形		2字节(2^16)	-32768~32767	0-65535
		中等大小的整形	3字节(2^32)					0-16777215
		标准的整数型	4字节						-2147483648-2147483647
		打整数型		8字节

		(2)浮点型(小数)
		float(m,d)		4字节
		double(m,d)		8字节
		定点数
		decimal(m,d)	m+2字节
		
	2.字符型	
		char()			255	固定长度
		varchar()		255 可变长度
		
		text		2^16-1	文本数据(文章)
		mediuntext	2^24-1
		longtext	2^32-1
		
		blob		2^16-1	二进制数据(照片)
		mediunblob	2^24-1
		longblob	2^32-1
		
		enum 枚举	1或2字节	最多有65535个成员
								一次只能有一个值
		set  集合	1,2,4字节	最多有64个成员
		一次可以拥有集合中的多个值，中间用逗号隔开
		
	3.日期型
		date		yy-mm-dd
		time		hh:mm:ss
		datetime	yyyy-mm-dd hh:mm:ss
		timestamp	yyyymmddhhmmss
		year		yyyy
		(用整形int保存时间有便于计算)

	数据字段属性
		1.unsigned	无符号
			只能用在数值型字段
		2.zerofill	(默认为无符号)
			只能用在数值型字段，前导0(在数子之前自动用零补齐)
		3.auto_increment
			只能是整数,自动增长,使用时加上唯一索引primary key
			NULL 留空 和 0 都会自动增长
		4.NULL 和 NOT NULL
			NULL 为空
		5.default 缺省值
		
//==================================================================
//--------------------------MySQL默认字符集------------------------
	
	永久修改字符集(配置文件)
	default-character-set = utf8
	character-set-server = utf8
	default-storage-engine = MyISAM
	
	临时修改字符集(命令行)
	set character_set_client = utf8;
	set character_set_connection = utf8;
	set character_set_database = utf8;
	set character_set_results = utf8;
	set character_set_server = utf8;
	set collation_connection = utf8;
	set collation_database = utf8;
	set collation_server = utf8;
	
	set names 'utf8';
	它相当于下面的三句指令:
	set character_set_client = utf8;  
	set character_set_results = utf8;   
	set character_set_connection = utf8; 
	
	
	查看默认字符集
	show variables like "character_set_%";
	
	修改完后，重启mysql的服务
	使用 show variables like 'character%'; 查看是否已被修改

	查看数据库支持的字符集
	show character set;
	
	字符集；是用来定义MySQL存储字符串的方式
	校对字符集：是对规则定义了比较字符串的方式，一对多的关系
	
	查看校对字符集：show collation like 'uft%';
	
	更改数据库字符集
	alter database mydb character set utf8;
	
	创建库时设置字符集
	create database mydb character set utf8;
	
	创建表时设置字符集
	create table tb_name(
	)type=MyISAM default character set utf8 collate utf8_general_ci;

//==================================================================
//---------------------------条件符号------------------------------

	函数查询:
		count()	统计函数
		sum()	求和
		avg()	平均值
		max()	最大值
		min()	最小值
		
	比较运算符
		=
		<=>	和=一样，但可用于null字符运算
		!=	<>
		<
		<=
		>
		>=
		or
		is null 是空
		is not null 不是空
		between and (between 什么and什么之间)
		not between and
		in 		例：select * from x1 where id in(5,10,15);
		like
		not like
		regexp 	正则	select * from x2 where id regexp '\d';

//==================================================================
//----------------------------常用操作-----------------------------

	启动数据库
		net start mysql
		net stop mysql
		
	连接数据库
		mysql -h localhost -u root -p123
		mysql -uroot -p123
		
	去表格显示
		\G
	
	查看数据库状态
		\s
		
	退出数据库
		ctrl+c	\q	exit	quit
		
	取消本行命令
		\c
		
	更改执行符
		\d
	
	查看配置文件中所有的变量
		show variables;
			查找时区
		show variables like 'time_zone';
			查找端口
		show variables like 'port';
	
	选择库\表
		use db_name;
		use tb_name;
	
	查看库\表
		show databases;
		show tables;
		
	查看表结构
		desc tb_name;
		
	帮助信息
		问号后加要查看的帮助信息
		？create databases;
	
	添加用户
		grant 权限 on 数据库.数据表 to 用户名@登录主机 identified by '密码',
		grant insert,update  on *.* to username@'%' identified by '密码';
	
	数据库备份
		mysqldump -hlocalhost -uroot -p 数据库名>物理路径\文件名;

	导入备份数据
		mysql -hlocalhost -uroot -p 数据库名<物理路径\文件名;
			
//==================================================================
//---------------------------各种函数------------------------------

	字符串函数
	
		select order by where 等语句中使用
		
		字符串连接函数
		concat('str','str','str'..);
		
		字段名作为变量连接字符串
		select concat(name,'is a boy');
		
		将字符串str从x到y替换为hello
		insert(str,x,y,hello);
		
		字符串大小写转换
		lower() upper()
		
		取出左边的x位字符串与取出右边x位字符串
		left(str,x) right(str,x)
		
		用str1填充str直到长度为n
		lpad(str,n,str1)
		
		取出字符串边上的空格
		trim() ltrim() rtim()
		
		用b替换str中的a
		replace(str,a,b)
		
		比较字符串str1比str2小返回-1
		strcmp(str1,str2)
		
		截取字符串从x到y
		substring(str,x,y)
		
	数值函数
		
		取出x的绝对值
		abs(x);
		
		进一取整
		ceil(x)
		
		舍去取整
		floor(x)
		
		求膜(取余)
		mod(x,y)
		
		0到1之间的随机数
		rand()
		
		四舍五入保留y位小数
		round(x,y)
		
		截断保留y位小数
		truncate(x,y)
		
	日期函数
		
		返回当前日期
		curdate()
		
		返回当前时间
		curtime()
		
		返回时间与日期
		now()
		
		返回当前时间戳
		unix_timestamp(now())
		
		返回时间戳的日期值
		from_nuixtime()
		
		通过时间取出信息
		week(now())
		year()
		hour()
		minute()
	
	流程控制函数
	
		条件成立则a否则为b
		if(条件,a,b)
		
		如果不为空则a否则b
		ifnull(a,b)
		
		如果value1正则result1否则default
		case when value1 then result1 ... else default end
		
		返回当前数据库名
		database()
		
		返回当前数据库的版本
		version()
		
		返回当前用户
		user()
		
		返回ip网络字节序
		inet_aton(ip)
		
		返回网络字节序代表的ip地址
		inet_ntoa()
		
		MySQL系统用户密码加密
		password()
		
		网站密码加密
		md5()

//==================================================================
//------------------------------------------------------------------