mysql常用操作

CMD命令创建数据库

DDL is Data Definition Language :数据定义语言，用于定义和管理 SQL 数据库中的所有对象的语言

 

DML is Data Manipulation Language:数据操作语言，SQL中处理数据等操作统称为数据操纵语言

 

DCL is Data Control Language:数据控制语言，用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。

()

DQL is Data Query Language: 数据查询语言

 

创建新用户

create user 'myname(用户名)'@'localhost(主机或ip)' identified by '1234(密码)';

drop user 'myname(用户名)'@‘localhost(主机或ip)’; 删除用户

grant all privileges(所有权限) on *.*(星点星代表所有数据库所有表) to myname@localhost identified by '1234' with grant option(还可以给他人赋权限) flush privileges(刷新数据 可加可不加); 给用户赋权限

 

grant all privileges(所有权限) on 数据库名.*(某一数据库所有表) to myname@localhost identified by '1234' flush privileges(刷新数据 可加可不加); 对指定数据库的所有权限

 

grant insert,update select delete on 数据库名.表名 to myname@localhost identified by '1234';对指定数据库指定表指定操作权限

 

show grants; 查看权限

show grants for 用户名@localhost; 查看指定用户权限

revoke 权限 on 数据库对象 from 用户; 删除权限

 

 

DDL----------------------------------------------------------------------------------------------

查看所有数据库：show databases;

删除数据库：drop database 数据库名

创建数据库：create database 数据库名 character set utf8;

进入某张表：use 表名;

查看数据库里表：show tables;

创建表：create table 表名(

列名1 列的类型 [约束],

列名2 列的类型 [约束],

 

列名n 列的类型 [约束]

);注意：最后一行没有逗号

修改表名：rename table 原表名 to 新表名

查看表和创建细节：show create table 表名;

修改表的字符集：alter table 表名 character set 新字符集名称;

 

 

查看表的结构：desc 表名;

添加一列：alter table 表名 add 列名 数据类型;

删除一列：alter table 表名 drop 字段名;

修改表和列名：alter table 表名 change 原列名 新列名 数据类型;

修改一列类型：alter table 表名 modify 字段名 数据类型;

删除表：drop table 表名;

 

DML----------------------------------------------------------------------------------------------

查询表中的所有数据：select * from 表名;

插入数据：insert into 表名(列名1，列名2...) values(列值1，列值2...);

批量插入：insert into 表名(列名1，列名2...) values(列值1，列值2...),(列值1，列值2...);

更新数据：update 表名 set 列名1 = 列值1，列名2 = 列值2...where 列名 = 值;

把年龄在原来的基础上加1岁： update 表名 set age = age+1 where id=1;

 

删除操作：delete from 表名 where 条件 如：id = 1; 不加条件可删除全部，也可只删除

某一条数据，可找回

truncate table 表名;只能全部删除 ，速度快，不可找回;

 

修改数据库密码： 选进入 use mysql

5.7之前的版本：update user set password = password('abc') where User='root';

新版本：update (mysql.在当前表内可不加) user set authentication_string = password('123456') where user = 'root' and Host = 'localhost';

mysqladmin -u root -p password 新密码;回车后输入原密码

 

DQL----------------------------------------------------------------------------------------------

图形化界操作

查询： select * from 表名 where 条件1 = ' ' and(与) 条件2 = ' ' ;

select * from 表名 where 条件1 = ' ' or(或) 条件2 = ' ' ;

select * from 表名 where 同一个字段(如id) in(id1,id2,id3);固定的范围

select * from 表名 where xx is null; 查询xx为null的;

select * from 表名 where xx is not null; 查询xx不为null的;

select * from 表名 where xx ！= ' ' ; 查询xx不等于某个值的;

select * from 表名 where age >= 18 and age <= 20;查询年龄在18 到20之间的

select * from 表名 where age between 18 and 30 ; 查询年龄在18 到30之间的

select * from 表名 where xx字段 like '_ _ _';查询xx由三个字符(汉字)构成的

select * from 表名 where xx字段 like '_ _ _x';查询xx由四个字符(汉字)并且最后一

个是x 构成的

select * from 表名 where xx字段 like 'x%'; 查询xx字段以x开头的记录

select * from 表名 where xx字段 like '_x%'; 查询xx字段第二个字符为x的记录

select * from 表名 where xx字段 like '%x%'; 查询xx字段包含x的记录

select distinct 字段 from 表名;去重查询

select *,字段+字段 form 表名;字段(必须是数值型)相加查询，并有一个新字段

select * , ifnull(字段，0)+ifnull(字段，0) as(as可以不要) 设置新字段 from 表名;

表示字段为null，值设置为0;

select * from 表名 order by 字段名 排序方法,字段名 排序方法;(不写默认升序

asc(升序) desc(降序))

 

常用的聚合函数-----------------------------------

select count(*(或某个字段)) from 表名; 查询表内总共有多少条记录 只统计数据

不为null的

select count(*) from 表名 where 字段 >xx;查询字段大于xx总共多少条记录

select conut(*) from 表名 where ifnull(字段1，0) + ifnull(字段2，0) > xxxx;

select conut(字段1) ,conut(字段2) from 表名;查询 字段1 字段2分别有多少条记录

 

select sum(字段1) from 表名; 查询所有字段1之和

select sum(字段1)，sum(字段2) from 表名; 查询所有字段1,字段2之和

select sum(字段1+ ifnull(字段2,0)) from 表名; 查询所有字段1+字段2和

 

select avg(字段1) from 表名;查询字段1的平均值

 

select max(字段1)，min(字段1) from 表名;查字段1最大值和最小值

 

分组-----------------------------------

分组不是去重

select 字段1 from 表名 group by 字段1;查询根据字段1进行分组

select 字段1，group_concat('字段(如:name)') from 表名 group by 字段1;查询分

组里面的所有(姓名);

select 字段1，sum(字段2) from 表名 group by 字段1 having sum(字段2) > xxx;

having是对分组后进行条件筛选

 

select * from 表名 limit 0,3;表示从0行开始查，查询3条

select * from 表名 limit 3,3;表示从3行开始查，查询3条

 

书写顺序-----------------

select ——> from ——> where ——> group by ——> having ——> order by ——> limit

执行顺序-------------

form ——> where ——> group by ——> having ——> select ——> order by ——> limit

 

primary key 主键约束； 特点： 数据唯一 且不能为null

unique 唯一约束； 特点：指定列的数据不能重复 可以为null

auto_increment 自动增长约束； 特点：指定列的数据自动增长，即便数据删除，还是从

删除的序号继续往下;

not null 非空约束

default 默认约束

 

create table 表名(id bigint primayr key,name varchar(25),age int);

create table 表名(id bigint,name varchar(25),age int ,primayr key(id));以上两种主键约束

写法都可以

create table 表名(id bigint,name varchar(25),age int ,primayr key(id，age)); 联合主键，

两个字段数据同时相同时，才违么联合主键约束;

alter table 表名 add constraint primary key(字段)；为表添加主键约束

 

关联表

表2 表1关联约束

create table 表名1(b1)(id int primary key, name varchar(50),age int);

create table 表名2(b2)( sid int,score int, cid int constraint(添加) b2_b1_fk(这名字最好取从那张表到那张表)

foreing key(foreing key外键)(sid（当前表的外键）) references(关联) 表名1(id));

create table 表名3(b3)(cid int, name varchar(50));

 

主外键保持一致 （称99查询）

select * from b1,b2 where b1.id = b2.sid;

select * from b1 aa(aa是b1表的别名),b2 bb where aa.id = bb.sid;以上两种写一样

三张表查询

select b1.'name'(显示的字段),b2.'score',b3.name from b1 aa,b2 bb,b3 cc where aa.id = bb.sid and bb.cid = cc.cid; （99查询）

select * from b1 aa join b2 bb on aa.id = bb.sid join b3 cc on bb.cid = cc.cid; 内连查询

 

内连接 inner_join

select * from b1 aa inner join b2 bb on aa.id = bb.sid;

左连接 把左边表的数据全部查询出来 右边表的只有满足条件的才查询出来

select * from b1 aa left outer join b2 bb on aa.id = bb.sid

右连接 把右边表的数据全部查询出来 左边表的只有满足条件的才查询出来

select * from b1 aa right outer join b2 bb on aa.id = bb.sid

 

 

多对多关联 要创建中间关系表

列如：

create table teach(tid int primary key,name varchar(20));老师表

create table stu(sid int primary key,name varchar(20));学生表

create table tea_stu(tid int ,sid int);中间表

alter table tea_stu add constraint foreign key(tid) references teach(tid); 关联老师表

alter table tea_stu add constraint foreign key(sid) references stu(sid);关联学生表

 

合并结果集查询

union 去重

select * from 表1 union select * from 表2

union_all 不去重

select * from 表1 union_all select * from 表2

 

常用函数

--字符串函数

select concat('aaa','bbb') 将传入的字符串连接成一个字符串 aaabbb

select concat('aaa','bbb',null) null 任何字符串与null进行连接结果都是null

insert(str,x,y,instr) 将字符串str从x位置开始，y个字符长的子串替换为指定的(instr)字告符

left(str,x)和right(str,x)分别返回字符串最左(右)边的x个字符,如果第二个参数为null那么不返回任何字符

lpad(str,n,pad)和rpad(str,n,pad) 用字符串pad对str最左(右)边进行填充，直到长度为n

select lpad('my',5,123456) //123my

select rpad('my',6,123456) //my1234

ltrim(str) 和rtrim(str)去掉字符串中最左(右)侧的空格

trim(str) 去掉字符左右的空格

repeat(str,n) 返回字符串str重复n次结果

replace(str,a,b) 用字符串b替换字符串str中所有出现的字符串a

substring(str,x,y)返回字符串str中第x位置起y个字符长度的字符

事务的一致性

start transaction;开始时务

rollback 回滚事务 返回到初始状态

commit;提交 所有语句执行完毕 时务结束

 

事务隔离级别 脏读 不可重复读 幻读

read uncommitted 是 是 是

就是一个事务可以读取另一个未提交的事务数据

read committe 否 是 是

一个事务要等另一个事务提交后才能读取数据

repeatable read 否 否 是

重复读 mysql默认事务隔离级别

serializable 否 否 否

串行化 效率低下

 

select @@global.tx_isolation,@@tx_isolation; 查询事务级别

set global transaction isolation level 事务级别; 设置事务级别

 

create view 表名 as (select * from 表名);创建视图

creae or replace view 视图名 as(select * from 表名); 修改视图

drop view 表名;删除视图

 

delimiter $$ 就是把' ; ' 改成$$

create procedure 名称() begin 语句; end$$ 创建存储

delimiter ; 就是把'$$' 改成 ' ; '

call 名称

show proceduer status 查看所有存储过程

show procedure status where db = '数据库名'; 查看指定数据库中的存储过程

drop procedure 名称; 删除存储过程