数据库操作命令大全

—查看用户—

select host,user from mysql.user;

—创建用户—

create user '用户名' @ '允许链接的主机IP' identified by '密码';
例如:create user test @ 127.0.0.1 identified by test;
create user test @ localhost identified by test;

—删除用户—

drop user '用户名' @ '允许连接的主机IP'
delete from mysql.user where user = '用户名' and host = '允许连接的主机IP';

—修改用户密码—

update mysql.user set password = password("新密码") where user = '用户名' and host='允许连接的主机IP';

—设置用户权限—

grant 权限 on 数据库.* to '用户名' @ '允许连接的主机IP';//需要用户存在
grant 权限 on 数据库.* to '用户名' @ '允许连接的主机IP' identified by '密码';
例如 grant insert,delete,update,select on test.* to test @ localhost;
    grant all privileges on test.* to test @ localhost; //为test用户设置test数据库所有的权限

—刷新权限表—

针对用户表的操作,都需要:flush privilegs;

---------------------------数据库---------------------------
—查看所有的数据库

show databases;

—使用数据库—

use 数据库名字;

—查看当前使用的数据库—

select database();

—创建数据库—

create database python(数据库名) charset=utf8;

—删除数据库—

drop database python(数据库名);

---------------------数据表------------------------------
—查看当前数据库中的所有表

show tables;

—查看表结构—

desc table(表名);

—创建表—

create table table_name(表名)(
            字段名 字段类型 约束);
            foreign key(字段名) references 副表(字段名)  //

—导入数据—
导入 文件名

source xxx.txtx

---------------对表和字段的增删改–alter-------------------

—删除数据表—

drop table +(表名) //删除表名和表名里面的数据

—修改数据表— 一般table 后面都加上 (表名)
—//操作跟表里面的字段有关系的,都要加上(table + 表名)
1) 修改表名

 rename table 原表名 to 新表名;

--------- 2)增加字段----------

alter table 表名 add 字段名 字段类型;  (插入字段名)
alter table 表名 add column 字段名 字段类型 first  //插入字段名到首列
alter table 表名 add column 字段名 字段类型 after age  //插入字段名到age列的后面

3)修改字段类型
alter table 表名 modify column 字段名 新字段类型;

4)修改字段名和字段类型
alter table 表名 change 原名 新名 类型及约束;

5)删除字段
alter table 表名 drop column 字段名;//包括该字段下的所有数据

6)修改表的字符集
alter table 表名 character set utf8;

—查看创建表时的语句—

show create table 表名;

--------------------------类型---------------------------
—数据类型—

tinyint 1字节
smallint 2字节
int 或 interger 4字节
bigint 8字节
float(m,n) 4字节 // 不指定m,n有可能无法通过where 条件查询
double 8字节
decimal(m,d) m代表数据的宽度  d代表小数宽度

—字符串类型—

char 定长字符串
varchar 变长字符串
text 长文本数据
blob 二进制长文本数据

—日期和时间类型—

date 4字节 日期
time 3字节 时间
year 1字节 年份
datetime 8字节
timestamp 4字节

—复合类型—

enum 只能使用集合中的一个值或null
set 只能使用集合中的多个值或null

---------------对数据的插入–insert into- values--------------------
—全列单行插入—

insert into 表名 values();
insert into students values(0,'郭靖',1,'蒙古','2016-1-1');

—部分列插入—
------拓展------//凡是表名后面直接写字段名的,需要加上括号表名(字段名)

insert into 表名(字段1,字段2..) values(值1,值2..)/一个括号只添加每一个字段的各一个值
 例如 insert into student(name,height) values("黄蓉",180.00);

/如果所有字段插入数据可不指定字段名

insert into 表名 values(0,'小米',18,180.00,2,1,0)
          (....)
          (....);  //插入多行数据,往往刚开始创建表的时候导入进去

—全列多行插入—

insert into 表名(字段1,字段2..) values(值1,值2..)(值1,值2..)(值1,值2..);

//多个括号表示每个字段添加多个值,即添加多个列

例如 insert into student(name) values ('杨过')('郭靖')('龙女');

-----------------对数据的修改-update set-------------------------

—修改字段的全部值—

update 表名 set 字段名=值;    ---//修改单个字段的全部值

—修改字段的值—

update 表名 set 列1=值1,列2=值2..where 条件;     --- //修改字段第几行的值
例如 update student set gender=0,name='老王' where id =5;

------------删除表 字段名 数据的区别-drop-(alter-drop)-delete----------
凡是数据库或者表或者字段的操作前面都要加上database 或table—

—删除表名—

drop table 表名 ;

// 删除表名及里面的数据

—删除字段名—

alter table 表名 drop column 字段名;       ---  //删除字段名及该字段名下的数据

—删除数据— — //针对表名里面的数据的删除

delete from 表名;   ---   //删除表中所有的数据(表还存在)
delete from 表名 where 字段名=值;     ---  //删除该值所在行的全部值

truncate 表名;    --- //这种方式效率高

—逻辑删除— //本质修改操作

update student set is_delete=1 where id =1;
is_detele bit default 0   // 创建逻辑删除命令

------------------查询表的字段 select ----------------------------
—查询所有字段—

select * from 表名;    ---//展示所有字段名的数据信息

—查询指定字段—

select 字段名1,字段名2,.. from 表名;    ---//单单展示字段名1,2的数据信息
select 字段名1,字段名2,... from 表名 where 字段名=?    ---/根据条件查询

—使用as给字段起别名— as 可以省略不写

select id as 序号, name as 名字, gender as 性别 from student;
例如  select id 4,name '老王', gender '男' from student; //as省略

—使用as给表起别名—

select student.id, student.name from student;
select s.id, s.name from student as s;

---------------------对表进行排序—order by------------------------
—对字段名排序—

select * from 表名 order by 字段名1 asc|desc  ---//asc可以省略不写,默认升序

select * from where 查询条件 order by 字段名 asc|desc;

显示所有男生的信息,先按照年龄从小到大排序,年龄相同时,按照身高从高到矮排序

select * from student where gender='男' order by name asc,height desc;

/同一级用空格隔开,不用and order by 前面不用and来链接 字段名在order by 后面

---------------------获取部分行–limit----------------------------
—语法—

limit start,count  ---// start 表示数据的起始位置 下标,默认0  count代码数据的数量
select * from 表名 limit start,count ;

根据身高降序排序,获取前三名

select * from student order by height desc limit 0,3;

每页显示m条数据,当前显示第n页

select * from student where 查询条件 limit (n-1)*m , m;

---

—消除重复行—

select distinct 字段名1,字段名2,... from 表名;

—滤空修正—

select ifnull(字段名,替换值) from 表名

//滤空修正指的是如果该字段名下面的数据含有空即null的话,替换值就是补充null的值,如果该字段名下面含有多个空null的话,则所有含有null的空格都变成替换值

—使用算术表达式—

select 字段名 + 字段名 as 别名 from 表名;

—字符串拼接—

select concat(str1,str2,..) as 别名 from 表名;

------------------模糊查询------------------------------------
like
% 表示任意多个任意字符 包括1
_ 表示一个任意字符

查询姓黄的学生

select * from student where name like '黄%'

查询姓黄并且名是一个字的学生

select * from student where name like '黄_'

------------------范围查询------------------------------------
—in 表示在一个非连续的范围内
查询编号是1或8或4的学生

select * from student where id in(1,4,8);  ---/非连续

—between…and… 表示在一个连续的范围内
查询编号是1至8的学生

select * from student where id between 1 and 8;  ---//连续数据

查询编号是1至8的男学生

select * from student where id (between 1 and 8) and gender='男';  /注意括号

------------------空判断------------------------------------
null 与 ‘’ 是不同的
判空 is null
判非空 is not null

—查询没有填写身高的学生

select * from student where height is null;   //该字段名下面的数据还有null

—查询填了身高的学生

select * from student where height is not null;

------------------聚合函数---------------------------------

---count(*)表示计算总行数,括号中写星与列名,结果是相同的
select count(*) from student;

---max(字段名) 表示求列的最大值
select max(id) from student where gender=2;

---min(字段名) 表示求此列的最小值
select min(id) from student where gender=1;

---sun(列)表示求此列的和
select sun(age) from students where gender=1;

---avg(列) 表示求此列的平均值
select avg(age) from student where gender=2;

---------------分组—group by------------------------
group by 含义: 将查询结果按照1个或多个字段进行分组,字段值相同的为一组

—查看单个字段名—

select 字段名 from 表名 group by 字段名; 
select gender from student group by gender;

//前后两个字段名必须相同
//如果想要不相同,则必须在第一个字段名前面加上 group_concat(字段名),才不会报错

select group_concat(id) from student group by gender;

含义: 根据性别来分组,相同性别为一组,分别查看每组性别中每个人含有的id

---------------group by + group_concat()------------------
group_concat(字段名) 可以作为一个输出字段来使用,显示具体包含在该组的含有什么数据
—多个字段名—

1. select gender, group_concat(name) from student group by gender;

// 如果前面的字段名 和 group by后面的字段名相同,就不用加group_concat,如果字段名不相同,字段名前面就必须加上group_concat

---------------group by + 集合函数---------------------------

 2)select gender,avg(age),count(*) from student group by gender;

// 如果前面的数据不是字段名而是集合函数,则不用加group_concat,直接写函数即可,其他字段名跟上面的相同

---------------group by + having------------------------------
having条件表达式: 用来分组查询后制定一些条件来输出查询结果

select gender,count(*) from student group by gender having count(*)>2;

// 按照性别分组,查询每组性别里面含有的个数 挑选出性别组个数大于2的组别

---------------group by + with rollup--------------------------
with rollup 作用是:在最后新增一行,来记录当前每一个字段名下面数据的总和;

select gender,count(*) from student group by gender with rollup;

------------------笛卡尔积运算(交叉连接) join------------------------
笛卡尔积运算 join将左表和右表中每一行分别进行拼接组合 - 交叉连接

select * from hero join gongfu;  

// hero gongfu分别是一张表,合起来可当做一张虚表

—内连接-----
查询的结果为两个表共同匹配(拥有)到的数据,使用on 制定过滤笛卡尔积数据的条件
在MYSQL中,inner join 和join是等价的,inner可不写,但是其他数据库中就不一样

select * from hero join gongfu on hero.kongfuid = gongfu.id;

—右连接—
查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充

select * from hero right join gongfu on hero.kongfuid=gongfu.id;

—左连接—
查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充

select * from hero left join gongfu on hero.kongfuid=gongfu.id;

------------------------子查询------------------------------
—子查询概念—
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

—子查询和主查询的关系—

1. 子查询是嵌入到主查询中
2. 子查询是辅助主查询的,要么充当条件,要么充当数据源
3. 子查询是可以独立存在的语句,是一条完整的 select 语句

—子查询分类—

1. 标量子查询: 子查询返回的结果是一个数据(一行一列)
2. 列子查询: 返回的结果是一列(一列多行)
3. 行子查询: 返回的结果是一行(一行多列)

—标量子查询—
查询大于平均年龄的学生

select * from student where age > (select avg(age) from student);

—列级子查询— /一列多行
查询已经开班的学生信息

select * from student where cls_id in (1,2);  ---/ in(1,8)  在1-8里
select * from student where cls_id in (select id from classes);

—行级子查询— / 一行多列
行级子查询,产生的数据是一个元祖(记录) 所以可以()=()
查询出班级上 年龄最大 和 身高最高的信息

select max(height),max(age) from student;  /只会显示身高和年龄
select * from student where (height,age)=(185,59);
select * from student where (height,age)=(select max(height),max(age)from 

student); — /显示年龄最大和身高最高的那个人的全部信息

---------------------------完整的select语句------------------

select distinct *
from 表名
where.....
group by....having...
order by ......
limit start,count

---------------------常用字符串函数---------------------------
—upper 和 ucase—
//把所有字符转换为大写字母

select upper(name) from 表名;

—lower 和 lcase—
//把所有字符串转换为小写字母

select lcase(name) from 表名;

—replace(str,from_str,to_str)—
//把str中的from_str替换为to_str

select replace(字段名,替换前的值,替换后的值) from 表名;

—repeat(str,count)—
//返回str重复count 次的字符串

select repeat('abc',2) from 表名;  //abcabc

—reveres(str)—
//逆序字符串

select reverse('abc') from 表名;  //cba

—insert(str,pos,len,newstr)—
//把str中pos位置开始长度为len的字符串替换为newstr

slect insert('abcdef',2,3,'hhh'); //ahhhef

—substring_index(str,delim,count)—
//返回str中第count次出现的delim之前的所有字符,如果count为负数,则从右向左

select substring_index('abacadae','a',3); // abac

—ltrim(str)—
//去除字符串左边的空格

select ltrim('abc');

—rtrim—
//去除字符串右边的空格

select rtrim('abc');