mysql常用语句

1.查询数据库信息(数据库使用中能不用事务尽量不用事务,事务会锁表,操作多张表可以用事务)

show databases;

2.查询所在的数据库

select database();

3.查询sakila数据库存在的所有表

select table_name from information_schema.tables where table_schema='sakila';

4.创建数据库 :create database 名称 [character 字符集 collate 校队规则;


5.查看校队规则 :show collation;


6.查看数据库/表 :show databases/tables;


7.显示创建的数据库/表 :show create database/table 数据库名/表名[G];


8.查看当前使用的数据库 :select database();


9.创建表 :create table 表名(

字段名 数据类型 [该字段约束条件],

字段名 数据类型 [该字段约束条件],

......

)charcater set 字符集 collate 校队规则;


10.删除数据集/表 :drop database/table 名;

11.添加数据 :insert into 表名(字段名,字段名,字段名...不写默认全部字段) values

(数据,数据,数据),

(数据,数据,数据),

...

(数据,数据,数据);

或者 :insert into 表名

set 字段名=值,字段名=值,...;


12.更新数据 :update 表名 

set 字段名=值,字段名=值...where 过滤条件;


13.删除数据 :delete from 表名 where 过滤条件;

或者 truncate 表名;


14.复制表 :create table 新表名 like 旧表名; (拷贝结构)

create table 新表名 as (select * from 旧表名); (拷贝数据)

create table 新表名 like 旧表名;

insert into 新表名 as (select * from 旧表名); (结构数据都拷贝)


15.查询 :select * from 表名;

select 字段名,字段名,...from 表名;


过滤:where 过滤条件

in(集合) between...and(范围) is null(空值) distinct(排除重复值) like'%/_' (模糊查询) and(并且) or(或者)


16.聚合函数 :count()返回某列的行数 sum()返回某列的和 avg()返回某列的平均值 max()返回某列的最大值 min()返回某列的最小值 IFNULL(参数,如果为空则改为的值)


17.排序 :order by 字段名 asc/desc;

asc为升序 desc为降序 默认为asc


18.分组 :group by 字段名 having 过滤条件;


19.限制查询结果 :limit a,b;

a代表开始的标号(第一条为0) b代表显示的条数


20.子查询 :select 字段名,(子查询) from 表名;

select * from (子查询);

select * from 表名 where (子查询);

子查询关键字:exists(子查询)  只要里面的子查询返回了行,exists的值为真,外部查询就执行

         字段名 (比较运算符如>) all (子查询) 大于子查询里的每一个值

字段名 (比较运算符如>) any (子查询) 大于子查询里的任何值,也就是最小值

in(子查询) 存在


21.集合操作 :select * from a

union

select * from b;

将两张表纵向连接起来,但是两张表的字段数必须相同


22.外键约束 :alter table 表名

add constraint 外键名 foreign key (外键字段名) references 外键表名 (主键字段名);

删除外键 :alter table 表名

drop foreign key 外键名


23.联表查询 :内连接 :select * from 表1 [inner] join 表2 [on/where 表1.关系字段=表2.关系字段]; (on是内联前过滤 ,where是产生笛卡尔乘积之后再过滤)

cross join(交叉连接,在MySQL中等价于inner join)

外连接(必须写关联条件) :左外 select * from 表1 left [outer] join 表2;

右外 select * from 表1 right [outer] join 表2;

全外 select * from 表1 full [outer] join 表2 (MySQL不支持,可以用union)

自连接 :select * from 表 别名1 join 表 别名2;


24.创建视图 :create view 视图名

as

select语句

查看视图 : ①desc 视图名

②show table statas like '视图名'

③show create view 视图名

修改视图 :create or replace view 视图名

as

select语句

或者 alter view 视图名

as

select语句

25 case when 表名=数据1 then '数据别名' when 表名=数据2 then '数据别名2'... else '别名3’ end 表别名


例如:select id,name,(case when ismale=1 then '男' when ismale=2 then '不明' else '女' end) 性别 from employee;

26.查看数据表 :describe 表名 ;

或者 desc 表名;


27.修改表名 :alter table 表名 rename 新表名;

或者 rename table 表名 to 新表名;


28.主键(=非空 唯一) :primary key

唯一约束 :unique

非空约束 :not unll

默认约束 :default 默认值

检查约束 :check 条件

自动增长 :auto_increment


29.查询分页数据

/*
* sql:可以是单表的查询语句,也可以是多表的联合查询语句
* firstIndex:其实的索引
* pageSize:每页显示的记录数
*/
select o.* from (sql) o limit firstIndex,pageSize

30.查询表的大小

select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size,
concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables where TABLE_NAME = 'tablename';

31.随机取一个多个数据

select * from table_name order by rand() [limit num];

32.存储过程

-- 存储过程
-- 1、需求:传入一个学生ID,查询该学生的信息
    -- 带有输入参数的存储过程
delimiter $
create PROCEDURE stu_findByid(in sid int)
begin
    select * from student where id=sid;
END $
-- 调用存储过程
call stu_findByid(1);
-- 2、带有输出参数的存储过程
delimiter $
create procedure stu_out(out str varchar(20))
begin
    -- 给参数赋值
    set str='这是一个输出参数';
end $
-- 删除存储过程
drop procedure stu_inout;
-- 调用存储过程
-- 1)定义了一个变量
-- 2)定义了一个会话变量接收存储过程输出的参数
call stu_out(@name);
select @name;
-- 3、输入输出参数的存储过程
delimiter $
create procedure stu_inout(inout n int)
begin
    select n;
    set n=500;
end $
set @n=10;
call stu_inout(@n);
select @n;
-- 4、带条件判断的存储过程
    -- 需求:输入一个正整数,如果1,返回“星期一”,如果2,返回“星期二。。。。其他返回输入错误”
delimiter $
create procedure stu_testIf(in num int,out str varchar(20))
begin
    if num=1 then   -- 开始
        set str='星期一';
    elseif num=2 then
        set str='星期二';
    elseif num=3 then
        set str='星期三';
    else
        set str='输入错误';
    end if;         -- 结束
end $
-- 执行存储过程
call stu_testIf(2,@str);
select @str;
-- 5、带循环功能的存储过程
    -- 输入一个整数,求和。比如输入100,统计1-100的和
delimiter $
create procedure stu_testWhile(in num int,out result int)
begin
        -- 定义两个局部变量
        DECLARE i int default 1;
        declare vsum int default 0;
        while i<=num do
                set vsum = vsum+i;
                set i = i+1;
        end while;
        set result = vsum;
END $
call stu_testWhile(100,@result);
select @result;
drop procedure stu_testWhile;
-- 6、使用查询的结果赋值给变量(into)
delimiter $
create procedure stu_findByid2(in eid int,out vname varchar(20))
begin
    select name into vname from student where id=eid;
end
call stu_findByid2(1,@vname);
select @vname;
-- 7、练习,编写一个存储过程
use students;
select * from user;
    -- 如果学生的英语平均分小于等于70分,刚输出‘一般’
    -- 如果学生的英语平均分大于70,且小于等于90分,刚输出‘良好’
    -- 如果学生的英语平均分大于90分,刚输出‘优秀’
delimiter $
create procedure stu_testAvg(out str varchar(20))
begin
    -- 计算英语平均分
    declare savg double;
    select avg(score) into savg from user;
    if savg<=70 then
        set str='一般';
    elseif savg>70 and savg<=90 then
        set str='良好';
    else 
        set str='优秀';
    end if;
end $
call stu_testAvg(@str);
select @str;

33.插入数据并返回id

delimiter $
CREATE PROCEDURE AddRtmpData(in myrtmp VARCHAR(500),in idx VARCHAR(255),in platform INT,out addid INT)
BEGIN
    INSERT INTO rtmplive (rtmp,roomidx,createtime,edittime,state,plat) VALUES(myrtmp,idx,now(),now(),0,platform);
SELECT @@IDENTITY INTO addid;
END $
/*************************************
-- 调用
call AddRtmpData('http://www.baidu.com',1,1,@addid);
select @addid;
**************************************/


云笔记 | 创客说| 课程 | 用户中心

版权所有:机遇屋在线 Copyright © 2017-2020 aaoit Co., Ltd.

鲁ICP备16042261号