mysql常用语句，mysql基本语句
mysql常用语句
安装MYSQL
解压安装包，放置到如d:\
进入MySql5.6\bin目录，通过cmd窗口打开，输入 cd MySql安装目录\MySql5.6\bin
安装，输入mysqld install
启动mysql服务 net start mysql
停止mysql服务 net stop mysql
手动开始服务：管理工具--服务--MySQL
配置环境变量：打环境变量窗口-->右击我的电脑-->属性-->高级系统设置-->环境变量-->path变量-->双击将mysql路径复制进去(路径之间用';'隔开)
登录mysql
语法：mysql -h 服务器 -u 用户名 -p
密码
默认用户为root
mysql -h localhost -u root -p
mysql -h localhost -u root@localhost
localhost本地服务器
查看当前服务器信息:\s
退出mysql:exit
修改密码
修改当前用户密码：set password for root@服务器=password('新密码');
set password for root@localhost=password('qweqwe');
修改其他用户密码：mysqladmin -u用户名 -p旧密码 password 新密码
创建数据库
数据库包含对象：表，视图，存储过程...
create database 数据库名;
查看数据库:show databases;
删除数据库:drop database 数据库名;
引用数据库:use 数据库名;
打开sql文件
source d:\1.txt
常用数据类型：数值型，字符型，日期型
数值型，整型int，浮点型float/double
整型:tinyint(1字节) smallint（2字节）mediumint(3字节) int（4字节）* bigint（8字节）
浮点型:decimal精确类型（例：decimal(5,2)表未小数取值范围~999.99） float* double
字符型varchar(变长)* char（定长） text（变长）存储大型文本数据
日期型
date只能存年月日,time只能存时分秒,datetime存年月日 时分秒*
创建表
create table 表名(
字段名 类型（长度）,
字段名 类型（长度）,
...
);
创建表员工表
查看表结构desc 表名;
修改数据表结构语法：
增加字段
alter table 表名 add 列名 类型(长度)
修改字段
alter table 表名 modify 列名 类型（长度）
删除字段
alter table 表名 drop 列名
重命名字段
alter table 表名 change 旧列名 新列名 类型（长度）
查看字符集编号
show variables like 'character%';
create database test1 character set utf8;创建数据库设置字符集编码
连接mysql指定字符集
mysql --default-character-set -h 服务器 -u 用户名 -p 密码
约束：唯一unique，非空not null，检查check，主键primary key，外键foreign key，默认值default，自动增长auto_increment
语法
create table 表名(
字段 类型 [comment '注释'] [auto_increment|primary key |not null|unique|default 默认值],
[constraint 主键名] primary key(字段，字段)，
[constraint 外键名] foreign key(当前表外键字段) references 父表(字段)
);
用户表
编号（非空，唯一），姓名，性别，年龄
create table test(
id int auto_increment primary key,
name varchar(20)
);
自动增长auto_increment序列会自动添加数值，序列只能建立在整型的主键上
修改序列起始值的方法
alter table 表名 auto_increment=1000;
添加约束
alter table 表名 add primary key(字段);
删除约束
alter table 表名 drop primary key;
复制表与数据
create table 新表名 as select * from users;
复制表结构
create table 新表名 like 源表;
添加一整条数据
insert into 表名 values (数据,数据,……);
insert into 表名(字段名,字段名,……) values (数据,数据,……);
复制数据
insert into 表名 as select * from 源表名;
删除数据
delete from 表名 where 条件;
修改数据
update 表名 set 列=值 where 条件;
查询数据
select * from 表名;
mysql中的运算符
算术运算符:+ - * / %(取余 5%3相当于5除以3商1余2)
关系运算符:=、<、>、<=、>=、!=、<>(不等于)、in、between .. and ..、 is null、is not null、like
逻辑运算符 and(与)、or(或)、not(非)
mysql常用函数:字符串函数，数值函数，日期函数，聚合（统计函数），其他函数
字符串函数
concat(字符串,字符串……)连接字符串
select concat(uid,'-',uname,',',usex) stu from users;
insert(字符串1,2,5,字符串2) 从位置‘2’开始向‘字符串1’中取‘5’个字符替换成字‘符串2’
select insert('qwertyu',2,4,'1234');结果q1234yu
upper(字符串)将字符串转换为大写
lower(字符串)将字符串转换为小写
length(字符串)字符串长度，中文长度为2、
char_legnth(字符串)字符串长度，中文长度为1
trim(字符串)清除字符左右空格
repeat(字符串,n)将字符串重复n次
select repeat(uname,2) from users;
replace(str1,str2,str3);在str1中查找str2,找到后替换成str3;
select replace('zzqdgdsgh','zzq','zlz');结果zlzdgdsgh
substring(字符串,开始位置,长度)截取从‘开始位置’的指定长度的字符串，如果长度为负数，从后截取字符串
select substring('zzqdgdsgh',3,2);结果qd
加密函数
不可逆加密码函数
password(str)用于对str进行加密
select password(123456);
md5(str)用于对str进行加密
加密-解密函数
encode(str,key)和decode(str,key)使用key加密码解密字符串str
对字符串进行加密
select encode('zzq','49');
对加密字符串进行解密
select decode(encode('zzq','49'),'49');
数值函数
abs(n)求绝对值
ceil(n)向上取整
select ceil(-1.1); -1
select ceil(1.1); 2
floor(n)向下取整
select floor(-1.1); -2
select floor(1.1); 1
mod(n1,n2)n1取余n2
select mod(5,2); 5除以2商2余1
rand()求0-1之间的小数，随机
求0-10之间的随机数
select ceil(rand()*10);
round(n,小数位数)四舍五入
select round(1.1145687,5);保留五位小数
select round(1541.1145687,-3);如果长度为负数，将小数点左边的数据进行四舍五入
日期函数
curdate()返回当前系统日期
select curdate();
curtime()返回当前系统时间
select curtime();
now()返回当前系统日期时间
select now();
week(日期)返回日期为当前的第几个星期
select week(curdate());
year(日期)返回年
select year(curdate());
month(日期)返回月份
day(日期)返回天
hour(时间)返回小时数
select hour('03:02:00');
select hour(now());
minute(时间)返回分钟数
second(时间)返回秒数
date_format(日期,格式化字符);格式化日期时间
select date_format(curdate(),'%Y年%m月%d日');
设置日期格式：
%Y 4位年数
%y 2位年数
%m 月
%M 英文月
%d 天
%D 天
%h 时（12小时制）
%H 时（24小时制）
%I 时（12小时制）
%i 分
%s 秒
%T 24制时间
流程函数
if(条件,结果1,结果2)；当条件为真时,结果为第一个,否则为结果2
select id, if(name='pengchaom1','zlz',name)name,sex from emp;
case [字段]
when 值1 then 语句1
[when 值2 then 语句1]
...
[else 值]
end
如字段的值为值1时结果为语句1
select id,case name
when 'pengchaom' then '彭超明'
end name,sex from emp;
其他函数
database()返回当前数据库名称
select database();
version()返回当前数据库的版本信息
user()当前用户名
聚合函数（统计函数）
avg()平均值
sum()求和
max()最大值
min()最小值
count()统计数量
借书系统中有用户表，图书表，借阅表，会员等级表
创建用户表users
uid用户编号，主键自增 uname用户名 usex性别 vippoints积分
jack,男,200
tom,男,50
rose,女,400
lucy,女,1000
tim,男,100
black,男,700
jan,女,900
insert into users(uname,usex,vippoints) values('jack','男',200);
insert into users(uname,usex,vippoints) values('tom','男',50);
insert into users(uname,usex,vippoints) values('jack','男',200);
创建图书表books
bid图书编号,主键自增 bname图书名称 bcount图书数量
JAVA,清华大学出版社,50
PHP,电子工业大学出版社,60
HTML+CSS+JS,新华电脑出版社,70
C语言,新华电脑出版社,10
ORACLE,电子工业大学出版社,20
MYSQL,四川大学出版社,35
C#,清华大学出版社,15
PYTHON,北京大学出版社,25
创建借阅表borrow：记录用户什么时间借还哪些书。此表uid和bid比较特殊，这两个字段都需要依赖于用户表(uid)和图书表(bid)，uid和bid有才能添加到借阅表。
uid,bid,status,bdate默认值为系统时间,
外键关联users（uid）表字段
外键关联books(bid)表字段
1,1,1,2016-09-19
1,2,2,2016-08-20
1,3,2,2016-07-21
1,4,2,2016-06-22
1,5,1,2016-05-23
2,1,1,2016-04-24
2,2,1,2016-03-25
2,3,1,2016-02-26
创建用户等级表vipgrade
vid等级id,主键自增 vname会员名称 vlowpoints最低积分 vheighpoints最高积分
黄铜会员,0,100
黄金会员,101,200
铂金会员,201,500
钻石会员,501,1000
/*创建用户表*/
create table users(
uid int primary key auto_increment,
uname varchar(20),
usex varchar(2),
vippoints int default 0
);
/*添加用户*/
insert into users(uname,usex,vippoints) values('jack','男',200);
insert into users(uname,usex,vippoints) values('tom','男',50);
insert into users(uname,usex,vippoints) values('rose','女',400);
insert into users(uname,usex,vippoints) values('lucy','女',1000);
insert into users(uname,usex,vippoints) values('tim','男',100);
insert into users(uname,usex,vippoints) values('black','男',700);
insert into users(uname,usex,vippoints) values('jan','女',900);
/*创建图书表*/
create table books(
bid int primary key auto_increment comment '图书编号',
bname varchar(50) comment '图书名称',
bpub varchar(30) comment '出版社',
bcount int comment '图书数量'
);
/*添加数据*/
insert into books(bname,bpub,bcount)values('JAVA','清华大学出版社',50);
insert into books(bname,bpub,bcount)values('PHP','电子工业大学出版社',60);
insert into books(bname,bpub,bcount)values('HTML+CSS+JS','新华电脑出版社',70);
insert into books(bname,bpub,bcount)values('C语言','新华电脑出版社',10);
insert into books(bname,bpub,bcount)values('ORACLE','电子工业大学出版社',20);
insert into books(bname,bpub,bcount)values('MYSQL','四川大学出版社',35);
insert into books(bname,bpub,bcount)values('C#','清华大学出版社',15);
insert into books(bname,bpub,bcount)values('PYTHON','北京大学出版社',25);
insert into books(bname,bpub,bcount)values('JAVYA','11111清华大学出版社',50);
insert into books(bname,bpub,bcount)values('JAYVYA','112222清华大学出版社',50);
/*创建借阅表*/
create table borrow(
uid int,
bid int,
status int,
bdate datetime default now(),
constraint fk_uid foreign key(uid) references users(uid) on delete cascade,
constraint fk_bid foreign key(bid) references books(bid) on delete set null
/*constraint pk_uid_bid primary key(uid,bid) 组合主键*/
);
insert into borrow(uid,bid,status,bdate) values(1,1,1,'2016-09-19');
insert into borrow(uid,bid,status,bdate) values(1,2,2,'2016-08-20');
insert into borrow(uid,bid,status,bdate) values(1,3,2,'2016-07-21');
insert into borrow(uid,bid,status,bdate) values(1,4,2,'2016-06-22');
insert into borrow(uid,bid,status,bdate) values(1,5,1,'2016-05-23');
insert into borrow(uid,bid,status,bdate) values(2,1,1,'2016-04-24');
insert into borrow(uid,bid,status,bdate) values(2,2,1,'2016-03-25');
insert into borrow(uid,bid,status,bdate) values(2,3,1,'2016-02-26');
insert into borrow(uid,bid,status) values(2,4,1);
insert into borrow(uid,bid,status) values(3,4,1);
insert into borrow(uid,bid,status) values(3,5,null);
insert into borrow(uid,bid,status) values(3,6,null);
insert into borrow(uid,bid,status) values(3,7,null);
insert into borrow(uid,bid,status) values(4,7,null);
insert into borrow(uid,bid,status) values(4,8,null);
insert into borrow(uid,bid,status) values(4,9,null);
insert into borrow(uid,bid,status) values(5,2,null);
insert into borrow(uid,bid,status) values(5,3,null);
insert into borrow(uid,bid,status) values(6,1,null);
insert into borrow(uid,bid,status) values(6,3,null);
insert into borrow(uid,bid,status) values(7,2,null);
/*创建用户等级表*/
create table vipgrade(
vid int primary key auto_increment comment '等级id',
vname varchar(20) comment '会员名称',
vlowpoints int comment '最低积分',
vheighpoints int comment '最高积分'
);
insert into vipgrade(vname,vlowpoints,vheighpoints)values
('黄铜会员',0,100),
('黄金会员',101,200),
('铂金会员',201,500),
('钻石会员',501,1000);
添加数据
添加单行数据
insert into 表名(字段，字段)values(数据，数据);
添加多行数据
insert into 表名(字段，字段)values(数据，数据),
(数据，数据),
(数据，数据);
查询语法
查询所有字段
select * from 表名;
查询指定列，distinct取消重复行
select [distinct] 字段,字段 from 表名;
(5)select [distinct] *|[表名|表别名.] 字段 [别名],[函数]字段 [别名]，[子查询] [别名]
(1)from 表名 [别名],[子查询] [别名]
(2)where 条件
(3)group by 分组字段[,分组字段]
(4)having 分组条件|[聚合函数]
(6)order by 排序字段[desc|asc],[排序字段[desc|asc]]
(7)limit 起始位置[,记录数]
查询执行过程（1）-（7）
distinct取消重复行
取消姓名及性别重复行
select distinct uname,usex from users;
limit 起始位置[,记录数]，数据从0开始
类似于oracle中的分页查询
select * from limit 2,5;//从第3条数据开始取5条数据（3-7）
取前3条数据
select * from users limit 0,3;
等同于
select * from users limit 3;
查询图书数量大于等于30小于等50的图书
select * from books between bcount>30 and bcount<50;
select * from books where bcount between 30 and 50;
模糊查询[not] 字段 like 通配符
%匹配多个字符
_匹配单个字符
姓名以t开头的员工信息
select * from users where uname like't%';
空判断is null为空
select * from borrow where status is null;
不为空
select * from borrow where status is not null;
多表查询，多表之间使用连接查询，否则会出现笛卡尔积X乘以Y
{1,2,3}{4,5,6}它们的笛卡尔积{4,5，6，8，10，12，12，15，18}
内连接与外连接
内连接：多表满足条件的记录，相等连接（自然连接），不等连接
select [表名|表别名.]字段 ，[表名|表别名.]字段
from 表名 [别名],表名 [别名],……
[natural join 表名]
join 表名 on 关联条件
where 条件……
natural join:自然连接(相等)连接,自动关联表中的共同字段
join 表名 on 关联条件：内连接
查询借了图书的所有用户的编号，姓名，图书名，出版社，借阅日期
分析：users表：uid uname
books表:bname bpub
borrow表:bdate
建立三个表的连接：users.uid=borrow.uid and books.bid=borrow.bid
（1）select u.uid,u.uname,b.bname,b.bpub,x.bdate from users u,books b,borrow x where u.uid=x.uid and b.bid=x.bid;
（2）select u.uid,u.uname,b.bname,b.bpub,x.bdate from users u natural join books b natural join borrow x;
（3）select u.uid,u.uname,b.bname,b.bpub,x.bdate from users u join borrow x on u.uid=x.uid join books b on b.bid=x.bid;
不等连接
查询用户的姓名，性别，积分和会员名称
users表：uname,usex,vippoints
vipgrade表： vname
select u.uname,u.usex,u.vippoints,v.vname from users u,vipgrade v where u.vippoints between v.vlowpoints and v.vheighpoints;
外连接
多表查询，满足条件的记录全部显示，不满足条件的记录显示为空，左连接右表显示为空，右连接左表显示为空；
select [表名| 表别名.]字段，[表名| 表别名.]字段
from 表名 [别名] [left|right join] 表名[别名]
where……
左连接
所有用户姓名，性别，借书名
要求是将用户全部显示出来，如果使用相等连接，会所借书表里有的用户显示出来，不会显示没有借书的用户
select u.uname,u.usex,b.bname from users u left join borrow w on u.uid=w.uid left join books b on w.bid=b.bid;
右连接
所有用户姓名，性别，借书名
select u.uname,u.usex,b.bname from books b right join borrow w on b.bid=w.bid right join users u on u.uid=w.uid;
子查询，查询的嵌套
单行单列，单行多列，多行多列
查询与tom积分相同的员工
select * from users where vippoints=(select vippoints from users where uname='tom');
多行多列
查询用户jack和tom的借书情况，显示姓名，性别，图书名称
首先，查询jack和tom的姓名，性别，借书编号
select u.uname,u.usex,b.bid from users u,borrow b where u.uid=b.uid and u.uname in('jack','tom');
然后，查询姓名，性别，图书名称
（1）mysql> select u.uname,u.usex,s.bname from users u,borrow b,books s where u.uid=b
.uid and u.uname in('jack','tom') and b.bid=s.bid;
（2）mysql> select t.uname,t.usex,b.bname from books b,(select users.uname,users.usex,borrow.bid from users,borrow where users.uid=borrow.uid and users.uname in('jack','tom')) t where b.bid=t.bid;
子查询in,>any大于最小值，<any，,>all大于最大值,<all
使用正则表达式模糊查询
^字符串开始
$字符串结束
.匹配单个字符
查询姓名以t开头的员工
select * from users where uname regexp '^t';
like
select * from users where uname like 't%';
姓名以m结尾的员工
select * from users where uname regexp 'm$';
姓名以t开头以m结尾的员工
select * from users where uname regexp '^t.m$';//.只匹配单个字符，如果为..匹配两个字符
[0-9a-z]集合中包含的字符
[abc]匹配包含a,b,c的记录
姓名包含a,b,c的记录
[^0-9a-z]除去括号里匹配的字符
aa|bb|cc 包含其中某一个字符 'aa|bb|cc'
* 区配0个或多个在它前面的字符 x*
+ 1个或多个在它前面的字符
？0个或1个在它前面的字符
字符串{n} 字符串出现n次 a{2}
字符串{n,m} 字符串出现n到m次,最少出现n次，最多出现m次
用正则检查手机号
13/5/8/7 9位0-9
^1[3578][0-9]{9}
借书系统中有用户表，图书表，借阅表，会员等级表
创建用户表users
uid用户编号，主键自增 uname用户名 usex性别 vippoints积分
jack,男,200
tom,男,50
rose,女,400
lucy,女,1000
tim,男,100
black,男,700
jan,女,900
insert into users(uname,usex,vippoints) values('jack','男',200);
insert into users(uname,usex,vippoints) values('tom','男',50);
insert into users(uname,usex,vippoints) values('jack','男',200);
创建图书表books
bid图书编号,主键自增 bname图书名称 bcount图书数量
JAVA,清华大学出版社,50
PHP,电子工业大学出版社,60
HTML+CSS+JS,新华电脑出版社,70
C语言,新华电脑出版社,10
ORACLE,电子工业大学出版社,20
MYSQL,四川大学出版社,35
C#,清华大学出版社,15
PYTHON,北京大学出版社,25
创建借阅表borrow：记录用户什么时间借还哪些书。此表uid和bid比较特殊，这两个字段都需要依赖于用户表(uid)和图书表(bid)，uid和bid有才能添加到借阅表。
uid,bid,status,bdate默认值为系统时间,
外键关联users（uid）表字段
外键关联books(bid)表字段
1,1,1,2016-09-19
1,2,2,2016-08-20
1,3,2,2016-07-21
1,4,2,2016-06-22
1,5,1,2016-05-23
2,1,1,2016-04-24
2,2,1,2016-03-25
2,3,1,2016-02-26
创建用户等级表vipgrade
vid等级id,主键自增 vname会员名称 vlowpoints最低积分 vheighpoints最高积分
黄铜会员,0,100
黄金会员,101,200
铂金会员,201,500
钻石会员,501,1000