MySql Handbook.sql
来源:原创
时间:2016-07-15
作者:脚本小站
分类:代码笔记
//================================================================== //------------------------常用字段解释------------------------------ 一个汉字 = 三个字节 一个字母、数字只 = 一个字节 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() //================================================================== //------------------------------------------------------------------