数据库命令大全

八、数据的操作


show databases;//显示所有数据库
use testdb//使用testdb数据库
show tables;//显示所有表
create table users(id int auto_increment,username varchar(20),address varchar(200),age int,key(id));//创建表，id自增长
insert into users values(1,'petter','beijing');//表中插入数据
另外insert可以插入多行，多个括号跟在后面就行。


九、表单数据记录查询


select * from users;//查看users表中全部数据
select * from users limit 2;//查看前两行数据
select * from users limit 1,2;//查看从第一行之后的两行数据
select id,username from users;//查看表的id,username两列
desc users;//查看表的属性
select address,count(address) from users group by address;//通过统计address来进行分组
 
select host,User from mysql.user;//显示数据库的使用者
select address,count(address) from users group by address having count(address)>1;//只把count（address）大于一的显示出来
select * from users order by age;//按照age进行排序
select * from users order by age asc;//按照age升序排列
select * from users order by age desc;//按照age降序排列
create table users_new like users;//根据users的属性新建users_new但不复制数据
insert into users_new select * from users;//把users的全部数据插入users_new
drop table users_new;//删除表
update users set age=28,address='xian' where id=4;//修改id为4的age为28，address为xian
delete from users where id=4;//删除id为4的行
source C:\Users\yckj\Downloads\census-income.sql//将本地文件导入
select distinct(address) from users;//查看所有的address的种类
select 1+1;//select可以实现四则运算、求余
select concat('hello','world');//连接两个字符串
select * from users where age>29;//条件查询
select * from users where (id>2 and age>29) or address='shanghai';//条件查询 
select * from users where age between 30 and 32;//between条件查询
select * from users where age in (32,29);//in条件选择
select * from users where age not in (32,29);//not in 条件选择
show variables like '%max%';//把包含max的变量都会显示出来
show variables like '%max';//把以max结尾的变量显示出来
show variables like 'max%';//把以max开头的变量显示出来
select * from users where address like 'be%';//把users中address以be开头的显示行找出来；
select count(id) from users;//统计id的个数
select count(*) from users; //把表中所有的记录统计出来
select avg(age) from users;//计算age的平均值
select sum(age) from users;//计算所有age的和
select max(age) from users;//age的最大值
select min(age) from users;//age的最小值
select age,count(1) from users group by age;//根据age分组并统计数目
select age,id,count(1) from users group by age,id;// 根据age和id分组并统计数目；
select age,id,group_concat(username) from users group by age,id;//显示按照age，id分组之后的username；
select age,id,group_concat(username) as uname from users group by age,id having id>1;//把group_concat(username)重命名为uname，并且用having id>1过滤id为1的行；


十、多表数据记录查询


select * from users2 union select * from users;//合并查询两个表的内容
select * from users where (address,age)=(select address,age from users where username='lixin');//查询和lixin地址，age一样的user；
select * from users where age in (29,30);//查询age是29和30的
select * from users where age<any(select age from users where address="shanghai");//>any查询比any中最小的大的数据记录，<any表示查询比any中最大的要小的记录。
select * from users where age<all(select age from users where address="shanghai");//>all查询比all中最大的大的数据记录，<all表示查询比all中最小的要小的记录。
delete from users where id=4;//删除表中id=4的数据；
select bin(5);//显示5的二进制


十一、mysql运算符


select a,a+5,a*2 from t1;//加减乘除
select a,a/3,a div 3,a%5,mod(a,5) from t1;//除、取余
select a,a=24,a<12,a>40,a>=24,a<=24,a!=24,a<>24,a<=>24 from t1;//数值比较
select a,a='24','ha'<>'ha','xa'='xa','b'!='b' from t1;//字符串比较
select a,a is null, a is not null from t1;//is null  not is null
select a,a in(1,2,23),a in(24,12,22) from t1;//in
select s,s like 'beijing',s like 'b%g',s like 'bei____',s like '%jing' from t2;//like
select a,a between 15 and 30,a not between 15 and 30 from t1;//between
select 2&&2,2&&null,2 and 3,2 and 2;//与
select 2||2,2||null,2 or 3,2 or 0;//或
select !1,!2,!null;//非


十二、使用字符串常用函数


select concat('M','y','SQL',"  5.5");//字符串连接
select concat_ws('-',curdate(),12.34);//把指定的分割符插入到字符串中
select strcmp("abc",'ab');//字符串比较函数
select length('sdfsgdfg');//返回字符串长度
select upper('sdfsgdfg124');//大写字符串
select ucase('sdfsgdfg124'); //大写字符串
select lower('sDFGSDGgdfg124');//小写字符串
select find_in_set('mysql','a,b,csdf,mysql,sky');//查找位置
SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');//查找位置
select left('2015-03-23 23:23:32',10);//从字符串左边开始截取10个字符
select right('2015-03-23 23:23:32',10);//从字符串右边开始截取10个字符
select mid('this is the test',4,6);//把字符串从4开始截取6位
select substring('this is the test',4,6);//把字符串从4开始截取6位
select trim('   sdfd  sd   ');//去除字符串首尾空格
select ltrim('   sdfd  sd   ');//去除字符串首空格
select rtrim('   sdfd  sd   ');//去除字符串尾空格
select replace('this is test','is','is not');//将字符串is替换为is not
select rand(1);//随机数
select ceil(4.5);//向上取整5
select round(123.4567,3);//四舍五入保留3位小数123.457
select floor(4.5);//向下取整4
select truncate(213.234435,2);//保留两位小数
select now();//获取当前时间：日期，时分秒
select curdate();//获取当前日期；
select sysdate();//系统时间，日期，时分秒
select year(now());//返回现在的年


十三、存储过程和函数操作


delimiter /$/$//以/$/$为结束符
mysql> create procedure pro_test()//创建存储过程
    -> begin
    -> select address from users
    -> end
-> /$/$
call pro_test;//调用存储过程
mysql> create function fun_test(age int(11))//创建函数
    -> return int(2,11)
    -> begin
    -> return (select id from users where users.address='beijing');
    -> end
-> /$/$
Select fun_test; //调用函数
show procedure status \G   //显示所有存储过程
show create procedure user_count \G //显示user_count存储过程
drop procedure pro_test;//删除pro_test


十四、MYSQL事务


begin;//开启事务
commit;//提交事务
rollback;//撤销
set autocommit=1;//设置自动提交，经提交的事务不能再撤销
set global transaction isolation level read uncommitted;//设置隔离级别，修改之后要重新登录
show variables like '%tx%';//查看隔离级别
所有事务都可以看到其他未提交事务的执行结果，读取未提交的数据称为脏读。
update users set id=111 where id=2;//修改users id为111的数据变成id为2
show engine innodb status \G;//可以查看数据库中的锁状态


十五、mysql安全机制
select * from user \G;//可以查看所有用户及其权限，注意这个user是数据库mysql自带的table；
mysql -uroot -padmin -h127.0.0.1//登录mysql，用户为root，密码为admin，ip127.0.0.1
mysql -uroot -padmin -h127.0.0.1 mysql//使用mysql数据库
grant usage on *.* to lixin@192.168.0.100 identified by "12345"；//为数据库创建一个名为lixin，指定ip为192.168.0.100， 密码为12345的新用户，这个用户只有连接数据权限，无修改权限
show grants;//显示当前用户权限
grant usage on *.* to lixin@192.168.0.100 identified by "abc"；修改密码为abc
flush privileges;//修改用户之后刷新权限相关表
drop user lixin@192.168.0.100;//删除用户lixin


十六、mysql日志管理


在my.ini文件中加入log-bin启动日志文件，注释掉则停用，记录修改数据库的操作
加入log文件启动通用日志文件，记录所有操作
Mysqlbinlog lixin-bin.000001//查看lixin-bin.000001日志文件
Flush logs;//刷新日志文件并产生新的log文件
purge master logs to lixin-bin.30//删除小于30的所有lixin-bin文件
purge master logs before ‘2016-05-16 22:35:50’//删除2016-05-16 22:35:50之前创建的所有日志文件
flush logs //刷新日志文件
set global general_log=on;//启动通用日志
show variables like '%general_log%' \G;//显示通用日志状态
set global general_log=off;//关闭通用日志
慢查询：让MySQL记录下查询超过指定时间的语句，我们将超过指定时间的SQL语句查询称为“慢查询”。