MySQL基础命令

#查看mysql版本

Windows中：mysql --version
mysql中：select version();

 

#查看mysql端口号

 

cmd命令进入mysql后输入show global variables like 'port';

 

#启动mysql服务器

net start mysql

#关闭 

net stop mysql

  
#进入

mysql -h主机地址 -u 用户名 -p 用户密码

#退出

exit
ctrl+c
quit

 

#修改密码:首先在DOS 下进入mysql安装路径的bin目录下，然后键入以下命令:

mysqladmin -uroot -p原密码 password 新密码;

#增加用户

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 "";
*/

数据库的数据类型：
int，char，double，date，varchar(可变长度字符串)，date(日期类型)

 

----------------------------------------
-----MySql数据库操作基础-----

 
#查看当前使用数据库

select database();

 

#终止语句

\c

 

#显示数据库

show databases;

 
#判断是否存在数据库kj,有的话先删除

drop database if exists kj;
 

#创建数据库

create database 数据库名;

 
#删除数据库

drop database 数据库名;

 
#使用该数据库

use 数据库名;

 
#显示数据库中的表

show tables;

 

#显示如何创建的表

show create table 表名；

 

#显示其他数据库的表

show tables from 数据库名；

 
#先判断表student是否存在,存在先删除

drop table if exists student;

 
#创建表

create table 表名(
name char(50),
sex char(20)
);
 

#删除表

drop table 表名;

 
#查看表的结构

describe 表名;  
可以简写为desc 表名;

 
#插入数据

insert into 表名 values('...' , '...' , '...' ......);

 

#查询表中的数据

select * from 表名;

 

#查询表中的一个字段

select 字段 from 表；

 

#查询表中的多个字段

select 字段名,字段名,..... from 表名；
select 字段名,字段名*10（数字） from 表名；
重命名： as + 名

 

#修改某一条数据

update 表名 set sex='男' where id=4;

 
#删除数据

delete from student where id=5;

 

----------------------------------------
查询

 

查询若输入与规定类型不同，会自动转化

#不等于
！=
<>
 

# and 且

select * from student where date>'1988-1-2' and date<'1988-12-1';
 

# or 或

select * from student where date<'1988-11-2' or date>'1988-12-1';
  
优先级 and > or

 

# like 模糊查询

select 字段名 from 表名 where 字段名 like '%s%';  //含有s
select 字段名 from 表名 where 字段名 like 's%';  //第一个是s
select 字段名 from 表名 where 字段名 like '%s';  //最后一个是s
select 字段名 from 表名 where 字段名 like '_s%';  //第二个是s
select 字段名 from 表名 where 字段名 like '__s%';  //第三个是s
select 字段名 from 表名 where 字段名 like '%s_';  //倒数第二个是s

 

#between

select * from student where date between '1988-1-2' and '1988-12-1'; 
若用在数字上，包括端点，闭区间，必须前小后大
若用在字母上，包括前不包括后，前闭后开

 
#in 查询制定集合内的数据

select * from 表 where 字段 in (1,3,5);

 
#查询第i条以后到第j条的数据(不包括第i条)

select * from student limit 2,5;  
显示3-5条数据

 

----------------------------------------
排序

 

#排序 asc 升序  desc 降序

select * from 表 order by 字段 desc;
默认升序

 
#若1相同按2排序

select 字段1,字段2 from 表 order by 1 desc, 2 asc;
 

#可加字段编号

select 字段1,字段2 from 表 order by 2；
按2的升序排列，只能按2排，不能根据别的字段改顺序

 

----------------------------------------
数据处理函数（别的数据库中可能不存在）

 

#转小写

select lower(字段) from 表;
重命名  as...

 

#转大写

select upper(字段) from 表;

 

#取子串

select substr(字段-字符串,起始位置,长度) from 表;
查找第二位是A的：select 字段 from 表 where 字段 like '_A%';
或：select 字段 from 表 where substr(字段,2,1) = 'A';

 

#取长度

select length(字段) from 表;

 

#去空格

select 字段 from 表 where 字段 = trim('          元素         ');

 

#四舍五入

select round(小数,保留位数);
不写保留位数，默认保留到整数；
保留到十位(123.456，-1) => 120
保留到百位(123.456，-2) => 100

 

#随机数

select rand();
一到一百的随机数：select round(rand()*100);

 

#若为空，设为数字n

select ifnull(字段,n) from 表；

 

----------------------------------------
日期处理（每个数据库处理日期时采用机制不同，在实际开发中，一般使用"日期串"表示日期）

 

#字符字符串转化为日期类型 varchar => date

str_to_date('日期字符串','日期格式');

    java中的日期格式：yyyy年，MM月，dd日，HH时，mm分，ss秒，SSS毫秒
    java中将字符串转化为日期类型：SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                      Date date = sdf.parse("1970-10-10");
    mysql中的日期格式：%Y，%m，%d，%H，%i，%s

    查询1980-12-17（字符串）的人
    ①select 字段 from 表 where 字段='1980-12-17';
        MYSQL默认的日期格式：%Y-%m-%d，以上字符串'1980-12-17'正好和默认格式一样，自动转化类型，所以可以查出结果
    
    ②select 字段 from 表 where 字段='12-17-1980'; 
        会出错，因为数据库存的日期时date类型，'12-17-1980'是varchar类型，不匹配，且格式不同

    ③select 字段 from 表 where 字段=str_to_date('12-17-1980','%m-%d-%Y');    
        正确

    通常使用在插入函数

 

#日期类型转化为 '具有特定格式' 的日期字符串

date_foemat('日期类型数据' , '日期格式');

    查询日期，以'%m/%d/%Y'格式显示
    select date_format(字段,'%m/%d/%Y) from 表; 

 

----------------------------------------
分组函数，聚合函数，多行处理函数（输入多行数据出一行结果）

自动忽略NULL

#最大，最小，求和，平均，记录数

max();  min();  sum();  avg();  count();
    
    count(字段);  不算空值
    count(*);  算空值

分组函数不能直接用在where后

 

#修改表的名字

格式:alter table tbl_name rename to new_name
alter table c rename to a;

  
#表结构修改

create table test
(
id int not null auto_increment primary key, #设定主键
name varchar(20) not null default 'NoName', #设定默认值
department_id int not null,
position_id int not null,
unique (department_id,position_id) #设定唯一值
);
 

#向表中增加一个字段(列)

格式:alter table tablename add columnname type;/alter table tablename add(columnname type);
alter table test add  columnname varchar(20);
 

#修改表中某个字段的名字

alter table tablename change columnname newcolumnname type;  #修改一个表的字段名
alter table test change name uname varchar(50);
 

#表position 增加列test

alter table position add(test char(10));

#表position 修改列test

alter table position modify test char(20) not null;

#表position 修改列test 默认值

alter table position alter test set default 'system';

#表position 去掉test 默认值

alter table position alter test drop default;

#表position 去掉列test

alter table position drop column test;

#表depart_pos 删除主键

alter table depart_pos drop primary key;

#表depart_pos 增加主键

alter table depart_pos add primary key PK_depart_pos
(department_id,position_id);

 
#用文本方式将数据装入数据库表中（例如D:/mysql.txt）

load data local infile "D:/mysql.txt" into table MYTABLE;

 
#导入.sql文件命令（例如D:/mysql.sql）#可以直接拖进去

source d:/mysql.sql;  #或者  /. d:/mysql.sql;

show index from A #查看索引
alter table A add primary key(id) #主键索引
alter table A add unique(name) #唯一索引
alter table A add index name(name) #普通索引
alter table A add fulltext(name) #全文索引
alter table A add index name(id,name) #多列索引

 

#常用函数

abs(-1)#绝对值
pi()#pi值
sqrt(2)#平方根
mod(-5,3)#取余-2
ceil(10.6)#进位+1 结果11 ceil(10.0)结果10
floor(10.6)#取整 10
round(2.5)#四舍五入到整数 结果3
round(2.5,2)#保留两位小数 结果2.50
truncate(2.5234,3)#取小数后3位不四舍五入 2.523
sign(-2);#符号函数 返回-1 0还是0 正数返回1
pow(2,3),exp(2);#2的3次幂 或e的2次幂
log(2),log10(2);#求对数
radians(180),degrees(0.618);#角度弧度转换
sin(0.5),asin(0.5)#正弦和反正弦 类似cos acos tan atan
length('hi')#计算字符长度
concat('1',1,'hi')#合并字符串
insert('12345',1,0,'7890');#从开头第1个字符开始到0个结束，替换成后边字符串，0表示在最前边插入
ucase('a'),lcase('A')#转成大写和小写
left('abcd',2),right('abcd',2);#返回前两个字符和后两个字符
ltrim('  0  '),rtrim(' 0 '),trim('  0  ')#删除空格
replace('1234567890','345678','0');#替换输出12090
substring('12345',1,2)#取字符 输出12 1是位置 2是长度
instr('1234','234');#取得234位置是2
reverse('1234');#反序输出4321
current()#返回日期
curtime()#返回时间
now()#返回日期时间
month(now())#当前月份 monthname 英文月份
dayname(now())#星期英文 dayofweek()1是星期天 weekday()1是星期二
week(now())#本年第多少周
dayofyear(now()),dayofmonth(now())#今天是本年第多少天 今天是本月第多少天
year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now())#返回年月日 时分秒
time_to_sec(now()),sec_to_time(3600*8);#转换时间为秒和还原
version()#mysql版本
database()#当前连接的数据库 没有为null
user()#获取用户名
md5('a')#加密字符串
ascii('a')#ascii值97
bin(100),hex(100),oct(100)#返回二进制 十六进制 八进制
conv(10001,2,8);#各种进制相互转换
rand()#生成0到1之间随机数
sleep(0.02)#暂停秒数