(^_^)/ mysql语句 一、连接mysql。 格式： mysql -h主机地址 -u用户名 -p用户密码 1、连接到本机上的mysql。 首先打开dos窗口，然后进入目录mysqlbin，再键入命令mysql -u root -p，回车后提示你输密码.注意用户名前可以有空格也可以没有空格，但是密码前必
(^_^)/ mysql语句一、连接mysql。
格式： mysql -h主机地址 -u用户名 -p用户密码
1、连接到本机上的mysql。
首先打开dos窗口，然后进入目录mysqlbin，再键入命令mysql -u root -p，回车后提示你输密码.注意用户名前可以有空格也可以没有空格，但是密码前必须没有空格，否则让你重新输入密码.
如果刚安装好mysql，超级用户root是没有密码的，故直接回车即可进入到mysql中了，mysql的提示符是： mysql>
2、连接到远程主机上的mysql。假设远程主机的ip为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令：
? ?mysql -h110.110.110.110 -u root -p 123;（注:u与root之间可以不用加空格，其它也一样）
3、退出mysql命令： exit （回车）
?
?
?
?
二、修改密码。
格式：mysqladmin -u用户名 -p旧密码 password 新密码
1、给root加个密码ab12。首先在dos下进入目录mysqlbin，然后键入以下命令
? ?mysqladmin -u root -password ab12
? ?注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。
2、再将root的密码改为djg345。
? ?mysqladmin -u root -p ab12 password djg345
? ?
? ?
? ?
? ?
三、增加新用户。
（注意：和上面不同，下面的因为是mysql环境中的命令，所以后面都带一个分号作为命令结束符）
格式：grant select on 数据库.* to 用户名@登录主机 identified by "密码"
1、增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用root用户连入mysql，然后键入以下命令：
? ?grant select,insert,update,delete on *.* to test1@"%" identified by "abc";
? ?但增加的用户是十分危险的，你想如某个人知道test1的密码，那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了，解决办法见2。
2、增加一个用户test2密码为abc,让他只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作（localhost指本地主机，即mysql数据库所在的那台主机），
? ?这样用户即使用知道test2的密码，他也无法从internet上直接访问数据库，只能通过mysql主机上的web页来访问了。
? ?grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc";
? ?如果你不想test2有密码，可以再打一个命令将密码消掉。
? ?grant select,insert,update,delete on mydb.* to test2@localhost identified by "";
?
? ?
? ?
? ?
四、显示命令?
mysql> select version(); ? ? ? ?查看mysql的版本号
mysql> select current_date(); ? ? ? ?查看mysql的当前日期
mysql> select version(),current_date(); 同时查看mysql的版本号和当前日期
mysql> show databases; ? ? ? ? ? ?显示当前存在的数据库
mysql> use mysql ? ? ? ? ? ?选择使用数据库(use和quit命令不需要分号结束)
database changed
mysql> select database(); ? ? ? ?显示当前选择的数据库
mysql> show tables; ? ? ? ? ? ?显示当前数据库中存在的表
mysql> select * from db; ? ? ? ?显示表(db)的内容 ? ?
mysql> describe mytable; ? ? ? ?显示表的结构
?
1、显示当前数据库服务器中的数据库列表：
mysql> show databases;
注意：mysql库里面有mysql的系统信息，我们改密码和新增用户，实际上就是用这个库进行操作。
2、显示数据库中的数据表：
mysql> use 库名；?
mysql> show tables;
3、显示数据表的结构：
mysql> describe 表名;
4、建立数据库：
mysql> create database 库名;
5、建立数据表：
mysql> use 库名;
mysql> create table 表名 (字段名 varchar(20), 字段名 char(1));
6、删除数据库：
mysql> drop database 库名;
7、删除数据表：
mysql> drop table 表名；
8、将表中记录清空：
mysql> delete from 表名;
9、显示表中的记录：
mysql> select * from 表名;
10、往表中插入记录：
mysql> insert into 表名 values ("hyq","m");
11、更新表中数据：
mysql-> update 表名 set 字段名1='a',字段名2='b' where 字段名3='c';
12、用文本方式将数据装入数据表中：
mysql> load data local infile "d:/mysql.txt" into table 表名;
13、导入.sql文件命令：
mysql> use 数据库名;
mysql> source d:/mysql.sql;
14、命令行修改root密码：
mysql> update mysql.user set password=password('新密码') where user='root';
mysql> flush privileges;
注意用此种方法修改密码时别忘了 password=password('新密码') 如果直接用 password='新密码'，下次登录时会出现问题以至于登录不进去，因为此密码没用经过password()函数处理，这样写入到user表中的密码系统不能识别就会显示用户名和密码错误的信息。
如果在修改密码的过程中出现问题以至于没有一个用户可以进入mysql则可以用以下的方法重新修改用户名和密码：
bin>mysqld --skip-grant-tables;然后再在 另外一个bin>mysql 便可直接进入mysql ,进入后便修改相关数据。
15、显示use的数据库名：
mysql> select database();
16、显示当前的user：
mysql> select user();
?
?
?
?
五、一个建库和建表以及插入数据的实例
drop database if exists school; //如果存在school则删除
create database school; //建立库school
use school; //打开库school
create table teacher //建立表teacher
(
id int(3) auto_increment not null primary key,
name char(10) not null,
address varchar(50) default '深圳',
year date
); //建表结束
//以下为插入字段
insert into teacher values('','allen','大连一中','1976-10-10');
insert into teacher values('','jack','大连二中','1975-12-23');
如果你在mysql提示符键入上面的命令也可以，但不方便调试。
（1）你可以将以上命令原样写入一个文本文件中，假设为school.sql，然后复制到c:下，并在dos状态进入目录，然后键入以下命令：
? ? ?mysql -uroot -p密码 < c:school.sql
? ? ?如果成功，空出一行无任何显示；如有错误，会有提示。（以上命令已经调试，你只要将//的注释去掉即可使用）。
（2）或者进入命令行后使用 mysql> source c:school.sql; 也可以将school.sql文件导入数据库中。
?
?
?
?
六、将文本数据转到数据库中
1、文本数据应符合的格式：字段数据之间用tab键隔开，null值用来代替.例：
? ?3 rose 大连二中 1976-10-10
? ?4 mike 大连一中 1975-12-23
? ?假设你把这两组数据存为school.txt文件，放在c盘根目录下。
2、数据传入命令 load data local infile "c:school.txt" into table 表名;
? ?注意：你最好将文件复制到目录下，并且要先用use命令打表所在的库。
?
? ?
? ?
? ?
七、备份数据库：（命令在dos的目录下执行）
1.导出整个数据库
导出文件默认是存在mysqlbin目录下
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u user_name -p123456 database_name > outfile_name.sql
2.导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u user_name -p database_name table_name > outfile_name.sql
3.导出一个数据库结构
mysqldump -u user_name -p -d --add-drop-table database_name > outfile_name.sql
-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table
4.带语言参数导出
mysqldump -uroot -p --default-character-set=latin1 --set-charset=gbk --skip-opt database_name > outfile_name.sql
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
--语 句 功 能
?
--数据操作
select --从数据库表中检索数据行和列-select
insert --向数据库表添加新数据行-insert
delete --从数据库表中删除数据行-delete
update --更新数据库表中的数据-update
?
--数据定义
create table --创建一个数据库表-create table
drop table --从数据库中删除表-drop table
alter table --修改数据库表结构-alter table
create view --创建一个视图-create view
drop view --从数据库中删除视图-drop view
create index --为数据库表创建一个索引-create index
drop index --从数据库中删除索引-drop index
create procedure --创建一个存储过程-create procedure
drop procedure --从数据库中删除存储过程-drop procedure
create trigger --创建一个触发器-create trigger
drop trigger --从数据库中删除触发器-drop trigger
create schema --向数据库添加一个新模式-create schema
drop schema --从数据库中删除一个模式-drop schema
create domain --创建一个数据值域-create domain
alter domain --改变域定义-alter domain
drop domain --从数据库中删除一个域-drop domain
?
--数据控制
grant --授予用户访问权限-grant
deny --拒绝用户访问-deny
revoke --解除用户访问权限-revoke
?
--事务控制
commit --结束当前事务-commit
rollback --中止当前事务-rollback
set transaction --定义当前事务数据访问特征-set transaction
?
--程序化sql
declare --为查询设定游标-declare
explan --为查询描述数据访问计划-explan
open --检索查询结果打开一个游标-open
fetch --检索一行查询结果-fetch
close --关闭游标-cloce
prepare --为动态执行准备sql 语句-repare
execute --动态地执行sql 语句-execute
describe --描述准备好的查询 -describe
?
?
---局部变量
---必须以@@开头
?
--if else
declare @x int @y int @z int
select @x = 1 @y = 2 @z=3
if @x > @y
print 'x > y' --打印字符串'x > y'
else if @y > @z
print 'y > z'
else print 'z > y'
?
--case
use pangu
update employee
set e_wage =
case
when job_level = ’1’ then e_wage*1.08
when job_level = ’2’ then e_wage*1.07
when job_level = ’3’ then e_wage*1.06
else e_wage*1.05
end
?
--while continue break
declare @x int @y int @c int
select @x = 1 @y=1
while @x < 3
begin
print @x --打印变量x 的值
while @y < 3
begin
select @c = 100*@x + @y
print @c --打印变量c 的值
select @y = @y + 1
end
select @x = @x + 1
select @y = 1
end
?
--waitfor
--例 等待1 小时2 分零3 秒后才执行select 语句
waitfor delay ’01:02:03’
select * from employee
--例 等到晚上11 点零8 分后才执行select 语句
waitfor time ’23:08:00’
select * from employee
?
?
***select***
select *(列名) from table_name(表名) where column_name operator value
ex:(宿主)
select * from stock_information where stockid = str(nid)
stockname = 'str_name'?
stockname like '% find this %'?
stockname like '[a-za-z]%' --------- ([]指定值的范围)
stockname like '[^f-m]%' --------- (^排除指定范围)
--------- 只能在使用like关键字的where子句中使用通配符)
or stockpath = 'stock_path'
or stocknumber < 1000
and stockindex = 24
not stock*** = 'man'
stocknumber between 20 and 100
stocknumber in(10,20,30)
order by stockid desc(asc) --------- 排序，desc-降序，asc-升序
order by 1,2 --------- by列号
stockname = (select stockname from stock_information where stockid = 4)
--------- 子查询
--------- 除非能确保内层select只返回一个行的值，
--------- 否则应在外层where子句中用一个in限定符
select distinct column_name form table_name --------- distinct指定检索独有的列值，不重复
select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
select stockname , "stocknumber" = count(*) from table_name group by stockname
--------- group by 将表按行分组,指定列中有相同的值
having count(*) = 2 --------- having选定指定的组
?
select *?
from table1, table2?
where table1.id *= table2.id -------- 左外部连接，table1中有的而table2中没有得以null表示
table1.id =* table2.id -------- 右外部连接?
?
select stockname from table1
union [all] ----- union合并查询结果集，all-保留重复行
select stockname from table2
?
?
***insert***
insert into table_name (stock_name,stock_number) value ("xxx","xxxx")
value (select stockname , stocknumber from stock_table2)---value为select语句
?
?
***update***
update table_name set stockname = "xxx" [where stockid = 3]
stockname = default
stockname = null
stocknumber = stockname + 4
?
?
***delete***
delete from table_name where stockid = 3
truncate table_name ----------- 删除表中所有行，仍保持表的完整性
drop table table_name --------------- 完全删除表
?
?
***alter table*** --- 修改数据库表结构
alter table database.owner.table_name add column_name char(2) null .....
sp_help table_name ---- 显示表已有特征
create table table_name (name char(20), age smallint, lname varchar(30))
insert into table_name select ......... ----- 实现删除列的方法（创建新表）
alter table table_name drop constraint stockname_default ---- 删除stockname的default约束
?
?
***function(/*常用函数*/)***
----统计函数----
avg --求平均值
count --统计数目
max --求最大值
min --求最小值
sum --求和
?
--avg
use pangu
select avg(e_wage) as dept_avgwage
from employee
group by dept_id
?
--max
--求工资最高的员工姓名
use pangu
select e_name
from employee
where e_wage =
(select max(e_wage)
from employee)
?
--stdev()
--stdev()函数返回表达式中所有数据的标准差
?
--stdevp()
--stdevp()函数返回总体标准差
?
--var()
--var()函数返回表达式中所有值的统计变异数
?
--varp()
--varp()函数返回总体变异数
?
?
?
----算术函数----
?
/***三角函数***/
sin(float_expression) --返回以弧度表示的角的正弦
cos(float_expression) --返回以弧度表示的角的余弦
tan(float_expression) --返回以弧度表示的角的正切
cot(float_expression) --返回以弧度表示的角的余切
?
/***反三角函数***/
asin(float_expression) --返回正弦是float 值的以弧度表示的角
acos(float_expression) --返回余弦是float 值的以弧度表示的角
atan(float_expression) --返回正切是float 值的以弧度表示的角
atan2(float_expression1,float_expression2)?
?
--返回正切是float_expression1 /float_expres-sion2的以弧度表示的角
degrees(numeric_expression)
--把弧度转换为角度返回与表达式相同的数据类型可为
--integer/money/real/float 类型
radians(numeric_expression) --把角度转换为弧度返回与表达式相同的数据类型可为
--integer/money/real/float 类型
exp(float_expression) --返回表达式的指数值
log(float_expression) --返回表达式的自然对数值
log10(float_expression)--返回表达式的以10 为底的对数值
sqrt(float_expression) --返回表达式的平方根
?
/***取近似值函数***/
ceiling(numeric_expression) --返回>=表达式的最小整数返回的数据类型与表达式相同可为
--integer/money/real/float 类型
floor(numeric_expression) --返回
注意，如果是连接到另外的机器上，则需要加入一个参数-h机器ip
?
第三招、增加新用户
格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码"
如，增加一个用户user1密码为password1，让其可以在本机上登录， 并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql，然后键入以下命令：
grant select,insert,update,delete on *.* to user1@localhost identified by "password1";
如果希望该用户能够在任何机器上登陆mysql，则将localhost改为"%"。
如果你不想user1有密码，可以再打一个命令将密码去掉。
grant select,insert,update,delete on mydb.* to user1@localhost identified by "";
?
第四招： 操作数据库
登录到mysql中，然后在mysql的提示符下运行下列命令，每个命令以分号结束。
1、 显示数据库列表。
show databases;
缺省有两个数据库：mysql和test。 mysql库存放着mysql的系统和用户权限信息，我们改密码和新增用户，实际上就是对这个库进行操作。
2、 显示库中的数据表：
use mysql;
show tables;
3、 显示数据表的结构：
describe 表名;
4、 建库与删库：
create database 库名;
drop database 库名;
5、 建表：
use 库名;
create table 表名(字段列表);
drop table 表名;
6、 清空表中记录：
delete from 表名;
7、 显示表中的记录：
select * from 表名;
?
第五招、导出和导入数据
1. 导出数据：
mysqldump --opt test > mysql.test
即将数据库test数据库导出到mysql.test文件，后者是一个文本文件
如：mysqldump -u root -p123456 --databases dbname > mysql.dbname
就是把数据库dbname导出到文件mysql.dbname中。
2. 导入数据:
mysqlimport -u root -p123456 < mysql.dbname。
不用解释了吧。
3. 将文本数据导入数据库:
文本数据的字段数据之间用tab键隔开。
use test;
load data local infile "文件名" into table 表名;
1:使用show语句找出在服务器上当前存在什么数据库：
mysql> show databases;
2:2、创建一个数据库mysqldata
mysql> create database mysqldata;
3:选择你所创建的数据库
mysql> use mysqldata; (按回车键出现database changed 时说明操作成功!)
4:查看现在的数据库中存在什么表
mysql> show tables;
5:创建一个数据库表
mysql> create table mytable (name varchar(20), sex char(1));
6:显示表的结构：
mysql> describe mytable;
7:往表中加入记录
mysql> insert into mytable values ("hyq","m");
8:用文本方式将数据装入数据库表中(例如d:/mysql.txt)
mysql> load data local infile "d:/mysql.txt" into table mytable;
9:导入.sql文件命令(例如d:/mysql.sql)
mysql>use database;
mysql>source d:/mysql.sql;
10:删除表
mysql>drop table mytable;
11:清空表
mysql>delete from mytable;
12:更新表中数据
mysql>update mytable set sex="f" where name='hyq';
posted on 2006-01-10 16:21 happytian 阅读(6) 评论(0) 编辑 收藏 收藏至365key
13：备份数据库
mysqldump -u root 库名>xxx.data
14：例2：连接到远程主机上的mysql
假设远程主机的ip为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令：
mysql -h110.110.110.110 -uroot -pabcd123
(注:u与root可以不用加空格，其它也一样)
3、退出mysql命令： exit (回车)
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
mysql函数大全：
?
ascii(str)?
返回字符串str的第一个字符的ascii值(str是空串时返回0)
mysql> select ascii('2');
-> 50
mysql> select ascii(2);
-> 50
mysql> select ascii('dete');
-> 100
ord(str)?
如果字符串str句首是单字节返回与ascii()函数返回的相同值。
如果是一个多字节字符,以格式返回((first byte ascii code)*256+(second byte ascii code))[*256+third byte ascii code...]
mysql> select ord('2');
-> 50
?
conv(n,from_base,to_base)?
对数字n进制转换,并转换为字串返回(任何参数为null时返回null,进制范围为2-36进制,当to_base是负数时n作为有符号数否则作无符号数,conv以64位点精度工作)
mysql> select conv("a",16,2);
-> '1010'
mysql> select conv("6e",18,8);
-> '172'
mysql> select conv(-17,10,-18);
-> '-h'
mysql> select conv(10+"10"+'10'+0xa,10,10);
-> '40'
?
bin(n)?
把n转为二进制值并以字串返回(n是bigint数字,等价于conv(n,10,2))
mysql> select bin(12);
-> '1100'
oct(n)?
把n转为八进制值并以字串返回(n是bigint数字,等价于conv(n,10,8))
mysql> select oct(12);
-> '14'
?
hex(n)?
把n转为十六进制并以字串返回(n是bigint数字,等价于conv(n,10,16))
mysql> select hex(255);
-> 'ff'
?
char(n,...)?
返回由参数n,...对应的ascii代码字符组成的一个字串(参数是n,...是数字序列,null值被跳过)?
mysql> select char(77,121,83,81,'76');
-> 'mysql'
mysql> select char(77,77.3,'77.3');
-> 'mmm'
?
concat(str1,str2,...)?
把参数连成一个长字符串并返回(任何参数是null时返回null)?
mysql> select concat('my', 's', 'ql');
-> 'mysql'
mysql> select concat('my', null, 'ql');
-> null
mysql> select concat(14.3);
-> '14.3'
length(str)?
octet_length(str)
char_length(str)
character_length(str)
返回字符串str的长度(对于多字节字符char_length仅计算一次)
mysql> select length('text');
-> 4
mysql> select octet_length('text');
-> 4
locate(substr,str)?
position(substr in str)?
返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)
mysql> select locate('bar', 'foobarbar');
-> 4
mysql> select locate('xbar', 'foobar');
-> 0
??
locate(substr,str,pos)
返回字符串substr在字符串str的第pos个位置起第一次出现的位置(str不包含substr时返回0)
mysql> select locate('bar', 'foobarbar',5);
-> 7
instr(str,substr)?
返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)
mysql> select instr('foobarbar', 'bar');
-> 4
mysql> select instr('xbar', 'foobar');
-> 0
lpad(str,len,padstr)?
用字符串padstr填补str左端直到字串长度为len并返回
mysql> select lpad('hi',4,'??');
-> '??hi'
?
rpad(str,len,padstr)?
用字符串padstr填补str右端直到字串长度为len并返回
mysql> select rpad('hi',5,'?');
-> 'hi???'
left(str,len)?
返回字符串str的左端len个字符
mysql> select left('foobarbar', 5);
-> 'fooba'
right(str,len)?
返回字符串str的右端len个字符?
mysql> select right('foobarbar', 4);
-> 'rbar'
substring(str,pos,len)?
substring(str from pos for len)?
mid(str,pos,len)?
返回字符串str的位置pos起len个字符(使用from的丑陋语法是ansi sql92标准)
mysql> select substring('quadratically',5,6);
-> 'ratica'
substring(str,pos)?
substring(str from pos)?
返回字符串str的位置pos起的一个子串
mysql> select substring('quadratically',5);
-> 'ratically'
mysql> select substring('foobarbar' from 4);
-> 'barbar'
substring_index(str,delim,count)?
返回从字符串str的第count个出现的分隔符delim之后的子串(count为正数时返回左端,否则返回右端子串)
mysql> select substring_index('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> select substring_index('www.mysql.com', '.', -2);
-> 'mysql.com'
ltrim(str)?
返回删除了左空格的字符串str
mysql> select ltrim(' ?barbar');
-> 'barbar'
rtrim(str)?
返回删除了右空格的字符串str
mysql> select rtrim('barbar ? ');
-> 'barbar'
trim([[both | leading | trailing] [remstr] from] str)?
返回前缀或后缀remstr被删除了的字符串str(位置参数默认both,remstr默认值为空格)
mysql> select trim(' ?bar ? ');
-> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx');
-> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx');
-> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz');
-> 'barx'
soundex(str)?
返回str的一个同音字符串(听起来“大致相同”字符串有相同的同音字符串,非数字字母字符被忽略,在a-z外的字母被当作元音)
mysql> select soundex('hello');
-> 'h400'
mysql> select soundex('quadratically');
-> 'q36324'
?
space(n)?
返回由n个空格字符组成的一个字符串
mysql> select space(6);
-> ' ? ? ?'
?
replace(str,from_str,to_str)?
用字符串to_str替换字符串str中的子串from_str并返回
mysql> select replace('www.mysql.com', 'w', 'ww');
-> 'wwwwww.mysql.com'
repeat(str,count)?
返回由count个字符串str连成的一个字符串(任何参数为null时返回null,count select repeat('mysql', 3);
-> 'mysqlmysqlmysql'
?
reverse(str)?
颠倒字符串str的字符顺序并返回
mysql> select reverse('abc');
-> 'cba'
insert(str,pos,len,newstr)?
把字符串str由位置pos起len个字符长的子串替换为字符串newstr并返回
mysql> select insert('quadratic', 3, 4, 'what');
-> 'quwhattic'
elt(n,str1,str2,str3,...)?
返回第n个字符串(n小于1或大于参数个数返回null)
mysql> select elt(1, 'ej', 'heja', 'hej', 'foo');
-> 'ej'
mysql> select elt(4, 'ej', 'heja', 'hej', 'foo');
-> 'foo'
field(str,str1,str2,str3,...)?
返回str等于其后的第n个字符串的序号(如果str没找到返回0)
mysql> select field('ej', 'hej', 'ej', 'heja', 'hej', 'foo');
-> 2
mysql> select field('fo', 'hej', 'ej', 'heja', 'hej', 'foo');
-> 0
find_in_set(str,strlist)?
返回str在字符串集strlist中的序号(任何参数是null则返回null,如果str没找到返回0,参数1包含","时工作异常)
mysql> select find_in_set('b','a,b,c,d');
-> 2
?
make_set(bits,str1,str2,...)
把参数1的数字转为二进制,假如某个位置的二进制位等于1,对应位置的字串选入字串集并返回(null串不添加到结果中)
mysql> select make_set(1,'a','b','c');
-> 'a'
mysql> select make_set(1 | 4,'hello','nice','world');
-> 'hello,world'
mysql> select make_set(0,'a','b','c');
-> ''
export_set(bits,on,off,[separator,[number_of_bits]])?
按bits排列字符串集,只有当位等于1时插入字串on,否则插入off(separator默认值",",number_of_bits参数使用时长度不足补0而过长截断)?
mysql> select export_set(5,'y','n',',',4)
-> y,n,y,n
lcase(str)
lower(str)?
返回小写的字符串str
mysql> select lcase('quadratically');
-> 'quadratically'
?
ucase(str)?
upper(str)?
返回大写的字符串str
mysql> select ucase('quadratically');
-> 'quadratically'
load_file(file_name)?
读入文件并且作为一个字符串返回文件内容(文件无法找到,路径不完整,没有权限,长度大于max_allowed_packet会返回null)
mysql> update table_name set blob_column=load_file("/tmp/picture") where id=1;
[color=red]数学函数[/color]
abs(n)
返回n的绝对值
mysql> select abs(2); ?
-> 2 ?
mysql> select abs(-32); ?
-> 32 ?
?
sign(n)
返回参数的符号(为-1、0或1)
mysql> select sign(-32); ?
-> -1 ?
mysql> select sign(0); ?
-> 0 ?
mysql> select sign(234); ?
-> 1?
mod(n,m) ?
取模运算,返回n被m除的余数(同%操作符) ?
mysql> select mod(234, 10); ?
-> 4 ?
mysql> select 234 % 10; ?
-> 4 ?
mysql> select mod(29,9); ?
-> 2?
floor(n)
返回不大于n的最大整数值
mysql> select floor(1.23); ?
-> 1 ?
mysql> select floor(-1.23); ?
-> -2?
ceiling(n)
返回不小于n的最小整数值
mysql> select ceiling(1.23); ?
-> 2 ?
mysql> select ceiling(-1.23); ?
-> -1?
round(n,d)
返回n的四舍五入值,保留d位小数(d的默认值为0)
mysql> select round(-1.23); ?
-> -1 ?
mysql> select round(-1.58); ?
-> -2 ?
mysql> select round(1.58); ?
-> 2 ?
mysql> select round(1.298, 1); ?
-> 1.3 ?
mysql> select round(1.298, 0); ?
-> 1?
exp(n)
返回值e的n次方(自然对数的底)
mysql> select exp(2); ?
-> 7.389056 ?
mysql> select exp(-2); ?
-> 0.135335?
log(n)
返回n的自然对数
mysql> select log(2); ?
-> 0.693147 ?
mysql> select log(-2); ?
-> null?
log10(n)
返回n以10为底的对数
mysql> select log10(2); ?
-> 0.301030 ?
mysql> select log10(100); ?
-> 2.000000 ?
mysql> select log10(-100); ?
-> null?
pow(x,y) ?
power(x,y) ?
返回值x的y次幂
mysql> select pow(2,2); ?
-> 4.000000 ?
mysql> select pow(2,-2); ?
-> 0.250000
sqrt(n)
返回非负数n的平方根
mysql> select sqrt(4); ?
-> 2.000000 ?
mysql> select sqrt(20); ?
-> 4.472136?
pi() ?
返回圆周率?
mysql> select pi(); ?
-> 3.141593?
cos(n)
返回n的余弦值
mysql> select cos(pi());
-> -1.000000?
sin(n)
返回n的正弦值?
mysql> select sin(pi()); ?
-> 0.000000?
tan(n)
返回n的正切值
mysql> select tan(pi()+1); ?
-> 1.557408?
acos(n)
返回n反余弦(n是余弦值,在-1到1的范围,否则返回null)
mysql> select acos(1); ?
-> 0.000000 ?
mysql> select acos(1.0001); ?
-> null ?
mysql> select acos(0); ?
-> 1.570796?
asin(n)
返回n反正弦值
mysql> select asin(0.2); ?
-> 0.201358 ?
mysql> select asin('foo'); ?
-> 0.000000?
atan(n)
返回n的反正切值
mysql> select atan(2); ?
-> 1.107149 ?
mysql> select atan(-2); ?
-> -1.107149 ?
atan2(x,y) ?
返回2个变量x和y的反正切(类似y/x的反正切,符号决定象限)
mysql> select atan(-2,2); ?
-> -0.785398 ?
mysql> select atan(pi(),0); ?
-> 1.570796?
cot(n)
返回x的余切
mysql> select cot(12); ?
-> -1.57267341 ?
mysql> select cot(0); ?
-> null?
rand()
rand(n) ?
返回在范围0到1.0内的随机浮点值(可以使用数字n作为初始值)
mysql> select rand(); ?
-> 0.5925 ?
mysql> select rand(20); ?
-> 0.1811 ?
mysql> select rand(20); ?
-> 0.1811 ?
mysql> select rand(); ?
-> 0.2079 ?
mysql> select rand(); ?
-> 0.7888?
degrees(n)
把n从弧度变换为角度并返回
mysql> select degrees(pi()); ?
-> 180.000000?
radians(n)
把n从角度变换为弧度并返回?
mysql> select radians(90); ?
-> 1.570796?
truncate(n,d) ?
保留数字n的d位小数并返回
mysql> select truncate(1.223,1); ?
-> 1.2 ?
mysql> select truncate(1.999,1); ?
-> 1.9 ?
mysql> select truncate(1.999,0); ?
-> 1?
least(x,y,...) ?
返回最小值(如果返回值被用在整数(实数或大小敏感字串)上下文或所有参数都是整数(实数或大小敏感字串)则他们作为整数(实数或大小敏感字串)比较,否则按忽略大小写的字符串被比较)
mysql> select least(2,0); ?
-> 0 ?
mysql> select least(34.0,3.0,5.0,767.0); ?
-> 3.0 ?
mysql> select least("b","a","c"); ?
-> "a"?
greatest(x,y,...) ?
返回最大值(其余同least())
mysql> select greatest(2,0); ?
-> 2 ?
mysql> select greatest(34.0,3.0,5.0,767.0); ?
-> 767.0 ?
mysql> select greatest("b","a","c"); ?
-> "c" ?
[color=red]时期时间函数[/color]
dayofweek(date) ?
返回日期date是星期几(1=星期天,2=星期一,……7=星期六,odbc标准)
mysql> select dayofweek('1998-02-03'); ?
-> 3?
weekday(date) ?
返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)。 ?
mysql> select weekday('1997-10-04 22:23:00'); ?
-> 5 ?
mysql> select weekday('1997-11-05'); ?
-> 2?
dayofmonth(date) ?
返回date是一月中的第几日(在1到31范围内) ?
mysql> select dayofmonth('1998-02-03'); ?
-> 3?
dayofyear(date) ?
返回date是一年中的第几日(在1到366范围内) ?
mysql> select dayofyear('1998-02-03'); ?
-> 34?
month(date) ?
返回date中的月份数值 ?
mysql> select month('1998-02-03'); ?
-> 2?
dayname(date) ?
返回date是星期几(按英文名返回)
mysql> select dayname("1998-02-05"); ?
-> 'thursday'?
monthname(date) ?
返回date是几月(按英文名返回)
mysql> select monthname("1998-02-05"); ?
-> 'february'?
quarter(date) ?
返回date是一年的第几个季度 ?
mysql> select quarter('98-04-01'); ?
-> 2?
week(date,first)?
返回date是一年的第几周(first默认值0,first取值1表示周一是周的开始,0从周日开始)
mysql> select week('1998-02-20'); ?
-> 7 ?
mysql> select week('1998-02-20',0); ?
-> 7 ?
mysql> select week('1998-02-20',1); ?
-> 8?
year(date) ?
返回date的年份(范围在1000到9999) ?
mysql> select year('98-02-03'); ?
-> 1998?
hour(time) ?
返回time的小时数(范围是0到23)?
mysql> select hour('10:05:03'); ?
-> 10?
minute(time) ?
返回time的分钟数(范围是0到59) ?
mysql> select minute('98-02-03 10:05:03'); ?
-> 5?
second(time) ?
返回time的秒数(范围是0到59)?
mysql> select second('10:05:03'); ?
-> 3?
period_add(p,n) ?
增加n个月到时期p并返回(p的格式yymm或yyyymm) ?
mysql> select period_add(9801,2); ?
-> 199803?
period_diff(p1,p2) ?
返回在时期p1和p2之间月数(p1和p2的格式yymm或yyyymm)
mysql> select period_diff(9802,199703); ?
-> 11?
date_add(date,interval expr type)
date_sub(date,interval expr type) ?
adddate(date,interval expr type) ?
subdate(date,interval expr type)
对日期时间进行加减法运算
(adddate()和subdate()是date_add()和date_sub()的同义词,也可以用运算符+和-而不是函数
date是一个datetime或date值,expr对date进行加减法的一个表达式字符串type指明表达式expr应该如何被解释
[type值 含义 期望的expr格式]:
second 秒 seconds ?
minute 分钟 minutes ?
hour 时间 hours ?
day 天 days ?
month 月 months ?
year 年 years ?
minute_second 分钟和秒 "minutes:seconds" ?
hour_minute 小时和分钟 "hours:minutes" ?
day_hour 天和小时 "days hours" ?
year_month 年和月 "years-months" ?
hour_second 小时, 分钟， "hours:minutes:seconds" ?
day_minute 天, 小时, 分钟 "days hours:minutes" ?
day_second 天, 小时, 分钟, 秒 "days hours:minutes:seconds"
expr中允许任何标点做分隔符,如果所有是date值时结果是一个date值,否则结果是一个datetime值)
如果type关键词不完整,则mysql从右端取值,day_second因为缺少小时分钟等于minute_second)
如果增加month、year_month或year,天数大于结果月份的最大天数则使用最大天数) ?
mysql> select "1997-12-31 23:59:59" + interval 1 second; ?
-> 1998-01-01 00:00:00 ?
mysql> select interval 1 day + "1997-12-31"; ?
-> 1998-01-01 ?
mysql> select "1998-01-01" - interval 1 second; ?
-> 1997-12-31 23:59:59 ?
mysql> select date_add("1997-12-31 23:59:59",interval 1 second); ?
-> 1998-01-01 00:00:00 ?
mysql> select date_add("1997-12-31 23:59:59",interval 1 day); ?
-> 1998-01-01 23:59:59 ?
mysql> select date_add("1997-12-31 23:59:59",interval "1:1" minute_second); ?
-> 1998-01-01 00:01:00 ?
mysql> select date_sub("1998-01-01 00:00:00",interval "1 1:1:1" day_second); ?
-> 1997-12-30 22:58:59 ?
mysql> select date_add("1998-01-01 00:00:00", interval "-1 10" day_hour);
-> 1997-12-30 14:00:00 ?
mysql> select date_sub("1998-01-02", interval 31 day); ?
-> 1997-12-02 ?
mysql> select extract(year from "1999-07-02"); ?
-> 1999 ?
mysql> select extract(year_month from "1999-07-02 01:02:03"); ?
-> 199907 ?
mysql> select extract(day_minute from "1999-07-02 01:02:03"); ?
-> 20102?
to_days(date) ?
返回日期date是西元0年至今多少天(不计算1582年以前)
mysql> select to_days(950501); ?
-> 728779 ?
mysql> select to_days('1997-10-07'); ?
-> 729669?
from_days(n) ?
给出西元0年至今多少天返回date值(不计算1582年以前)?
mysql> select from_days(729669); ?
-> '1997-10-07'?
date_format(date,format) ?
根据format字符串格式化date值
(在format字符串中可用标志符:
%m 月名字(january……december) ?
%w 星期名字(sunday……saturday) ?
%d 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） ?
%y 年, 数字, 4 位 ?
%y 年, 数字, 2 位 ?
%a 缩写的星期名字(sun……sat) ?
%d 月份中的天数, 数字(00……31) ?
%e 月份中的天数, 数字(0……31) ?
%m 月, 数字(01……12) ?
%c 月, 数字(1……12) ?
%b 缩写的月份名字(jan……dec) ?
%j 一年中的天数(001……366) ?
%h 小时(00……23) ?
%k 小时(0……23) ?
%h 小时(01……12) ?
%i 小时(01……12) ?
%l 小时(1……12) ?
%i 分钟, 数字(00……59) ?
%r 时间,12 小时(hh:mm:ss [ap]m) ?
%t 时间,24 小时(hh:mm:ss) ?
%s 秒(00……59) ?
%s 秒(00……59) ?
%p am或pm ?
%w 一个星期中的天数(0=sunday ……6=saturday ） ?
%u 星期(0……52), 这里星期天是星期的第一天 ?
%u 星期(0……52), 这里星期一是星期的第一天 ?
%% 字符% )
mysql> select date_format('1997-10-04 22:23:00','%w %m %y'); ?
-> 'saturday october 1997' ?
mysql> select date_format('1997-10-04 22:23:00','%h:%i:%s'); ?
-> '22:23:00' ?
mysql> select date_format('1997-10-04 22:23:00','%d %y %a %d %m %b %j'); ?
-> '4th 97 sat 04 10 oct 277' ?
mysql> select date_format('1997-10-04 22:23:00','%h %k %i %r %t %s %w'); ?
-> '22 22 10 10:23:00 pm 22:23:00 00 6'?
time_format(time,format)
和date_format()类似,但time_format只处理小时、分钟和秒(其余符号产生一个null值或0)
curdate() ??
current_date()
以'yyyy-mm-dd'或yyyymmdd格式返回当前日期值(根据返回值所处上下文是字符串或数字) ?
mysql> select curdate(); ?
-> '1997-12-15' ?
mysql> select curdate() + 0; ?
-> 19971215?
curtime() ?
current_time()
以'hh:mm:ss'或hhmmss格式返回当前时间值(根据返回值所处上下文是字符串或数字) ? ?
mysql> select curtime(); ?
-> '23:50:26' ?
mysql> select curtime() + 0; ?
-> 235026?
now() ?
sysdate() ?
current_timestamp()
以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss格式返回当前日期时间(根据返回值所处上下文是字符串或数字) ??
mysql> select now(); ?
-> '1997-12-15 23:50:26' ?
mysql> select now() + 0; ?
-> 19971215235026?
unix_timestamp() ?
unix_timestamp(date) ?
返回一个unix时间戳(从'1970-01-01 00:00:00'gmt开始的秒数,date默认值为当前时间)
mysql> select unix_timestamp(); ?
-> 882226357 ?
mysql> select unix_timestamp('1997-10-04 22:23:00'); ?
-> 875996580?
from_unixtime(unix_timestamp) ?
以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss格式返回时间戳的值(根据返回值所处上下文是字符串或数字) ??
mysql> select from_unixtime(875996580); ?
-> '1997-10-04 22:23:00' ?
mysql> select from_unixtime(875996580) + 0; ?
-> 19971004222300?
from_unixtime(unix_timestamp,format) ?
以format字符串格式返回时间戳的值
mysql> select from_unixtime(unix_timestamp(),'%y %d %m %h:%i:%s %x'); ?
-> '1997 23rd december 03:43:30 x'?
sec_to_time(seconds) ?
以'hh:mm:ss'或hhmmss格式返回秒数转成的time值(根据返回值所处上下文是字符串或数字) ??
mysql> select sec_to_time(2378); ?
-> '00:39:38' ?
mysql> select sec_to_time(2378) + 0; ?
-> 3938?
time_to_sec(time) ?
返回time值有多少秒 ?
mysql> select time_to_sec('22:23:00'); ?
-> 80580 ?
mysql> select time_to_sec('00:39:38'); ?
-> 2378
?
?
?
?
?
mysql有关权限的表:
mysql服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。
下面分别介绍一下这些表的结构和内容：
1.user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
2.db权限表：记录各个帐号在各个数据库上的操作权限。
3.table_priv权限表：记录数据表级的操作权限。
4.columns_priv权限表：记录数据列级的操作权限。
5.host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受grant和revoke语句的影响。
?
?
?
?
?
mysql支持事务：
在缺省模式下，mysql是autocommit模式的，所有的数据库更新操作都会即时提交，所以在缺省情况下，mysql是不支持事务的。
但是如果你的mysql表类型是使用innodb tables 或 bdb tables的话，你的mysql就可以使用事务处理,使用set autocommit=0就可以使mysql允许在非autocommit模式，
在非autocommit模式下，你必须使用commit来提交你的更改，或者用rollback来回滚你的更改。
示例如下：
start transaction;
select @a:=sum(salary) from table1 where type=1;
update table2 set summmary=@a where type=1;
commit;
?
?
?
mysql中的索引：
索引类型: b-tree索引，哈希索引
?
b-tree索引加速了数据访问，因为存储引擎不会扫描整个表得到需要的数据。相反，它从根节点开始。根节点保存了指向子节点的指针，并且存储引擎会根据指针寻找数据。它通过查找节点页中的值找到正确的指针，节点页包含子节点的指针，并且存储引擎会根据指针寻找数据。它通过查找节点页中的值找到正确的指针，节点页包含子节点中值的上界和下界。最后，存储引擎可能无法找到需要的数据，也可能成功地找到包含数据的叶子页面。
例：b-tree索引 对于以下类型查询有用。匹配全名、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配一部分并且匹配某个范围中的另一部分；
b-tree索引的局限：如果查找没有从索引列的最左边开始，它就没什么用处。不能跳过索引中的列，存储引擎不能优先访问任何在第一个范围条件右边的列。例：如果查询是where last_name=’smith’ and first_name like ‘j%’ and dob=’1976-12-23’;访问就只能使用索引的头两列，因为like是范围条件。
?
哈希索引建立在哈希表的基础上，它只对使用了索引中的每一列的精确查找有用。对于每一行，存储引擎计算出了被索引列的哈希码，它是一个较小的值，并且有可能和其他行的哈希码不同。它把哈希码保存在索引中，并且保存了一个指向哈希表中每一行的指针。
因为索引只包含了哈希码和行指针，而不是值自身，mysql不能使用索引中的值来避免读取行。
mysql不能使用哈希索引进行排序，因为它们不会按序保存行。
哈希索引不支持部分键匹配，因为它们是由被索引的全部值计算出来的。也就是说，如果在（a，b）两列上有索引，并且where子句中只使用了a，那么索引就不会起作用。
哈希索引只支持使用了= in（）和的相等比较。它们不能加快范围查询。例如where ?price > 100;
访问哈希索引中的数据非常快，除非碰撞率很高。当发生碰撞的时候，存储引擎必须访问链表中的每一个行指针，然后逐行进行数据比较，以确定正确的数据。如果有很多碰撞，一些索引维护操作就有可能会变慢。
?
?
?
mysql中数据类型：
varchar和char类型，varchar是变长的，需要额外的1-2个字节存储，能节约空间，可能会对性能有帮助。但由于是变长，可能发生碎片，如更新数据；
使用enum代替字符串类型，数据实际存储为整型。
字符串类型
要尽可能地避免使用字符串来做标识符，因为它们占用了很多空间并且通常比整数类型要慢。特别注意不要在myisam表上使用字符串标识符。myisam默认情况下为字符串使用了压缩索引（packed index），这使查找更为缓慢。据测试，使用了压缩索引的myisam表性能要慢6倍。
还要特别注意完全‘随机’的字符串，例如由md5（）、sha1（）、uuid（）产生的。它们产生的每一个新值都会被任意地保存在很大的空间范围内，这会减慢insert及一些select查询。1）它们会减慢insert查询，因为插入的值会被随机地放入索引中。这会导致分页、随机磁盘访问及聚集存储引擎上的聚集索引碎片。2)它们会减慢select查询，因为逻辑上相邻的行会分布在磁盘和内存中的各个地方。3）随机值导致缓存对所有类型的查询性能都很差，因为它们会使缓存赖以工作的访问局部性失效。如果整个数据集都变得同样“热”的时候，那么把特定部分的数据缓存到内存中就没有任何的优势了。并且如果工作集不能被装入内存中，缓存就会进行很多刷写的工作，并且会导致很多缓存未命中。
如果保存uuid值，就应该移除其中的短横线，更好的办法是使用uhex（）把uuid值转化为16字节的数字，并把它保存在binary（16）列中。
?
?
?
?
?
聚集索引：
聚集索引
?术语“聚集”指实际的数据行和相关的键值都保存在一起。每个表只能有一个聚集索引。但是，覆盖索引可以模拟多个聚集索引。存储引擎负责实现索引，因此不是所有的存储索引都支持聚集索引。当前，soliddb和innodb是唯一支持聚集索引的存储引擎。
优点：
?可以把相关数据保存在一起。这样从磁盘上提取几个页面的数据就能把某个用户的邮件全部抓取出来。如果没有使用聚集，读取每个邮件都会访问磁盘。
?数据访问快。聚集索引把索引和数据都保存到了同一棵b-tree中，因此从聚集索引中取得数据通常比在非聚集索引进行查找要快。
缺点：
?聚集能最大限度地提升i/o密集负载的性能。如果数据能装入内存，那么其顺序也就无所谓了。这样聚集就没有什么用处。
?插入速度严重依赖于插入顺序。更新聚集索引列是昂贵的，因为强制innodb把每个更新的行移到新的位置。
?建立在聚集索引上的表在插入新行，或者在行的主键被更新，该行必须被移动的时候会进行分页。
?聚集表可会比全表扫描慢，尤其在表存储得比较稀疏或因为分页而没有顺序存储的时候。
?第二（非聚集）索引可能会比预想的大，因为它们的叶子节点包含了被引用行的主键列。第二索引访问需要两次索引查找，而不是一次。 innodb的第二索引叶子节点包含了主键值作为指向行的“指针”，而不是“行指针”。 这种策略减少了在移动行或数据分页的时候索引的维护工作。使用行的主键值作为指针使得索引变得更大，但是这意味着innodb可以移动行，而无须更新指针。
?
?
?
?
?
?
?
?
?
?
mysql优化：
1.数据库的设计
尽量把数据库设计的更小的占磁盘空间.
1).尽可能使用更小的整数类型.(mediumint就比int更合适).
2).尽可能的定义字段为not ? null,除非这个字段需要null.
3).如果没有用到变长字段的话比如varchar,那就采用固定大小的纪录格式比如char.
4).表的主索引应该尽可能的短.这样的话每条纪录都有名字标志且更高效.
5).只创建确实需要的索引。索引有利于检索记录，但是不利于快速保存记录。如果总是要在表的组合字段上做搜索，那么就在这些字段上创建索引。索引的第一部分必须是最常使用的字段.如果总是需要用到很多字段，首先就应该多复制这些字段，使索引更好的压缩。
6).所有数据都得在保存到数据库前进行处理。
7).所有字段都得有默认值。
8).在某些情况下,把一个频繁扫描的表分成两个速度会快好多。在对动态格式表扫描以取得相关记录时，它可能使用更小的静态格式表的情况下更是如此。
2.系统的用途
1).尽量使用长连接.
2).explain ? 复杂的sql语句。
3).如果两个关联表要做比较话，做比较的字段必须类型和长度都一致.
4).limit语句尽量要跟order ? by或者 ? distinct.这样可以避免做一次full ? table ? scan.
5).如果想要清空表的所有
【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！