MySql语句大全（1）增、查、删、改

ゝ一世哀愁。 2022-05-13 09:48 181阅读 0赞

#查数据
    select * from test;  #取所有数据
    select * from test limit 0,2;  #取前两条数据 
    select * from test email like '%qq%' #查含有qq字符 _表示一个 %表示多个
    select * from test order by id asc;#降序desc
    select * from test id not in('2','3');#id不含2,3或者去掉not表示含有
    select * from test timer between 1 and 10;#数据在1,10之间
    
    #---------------------------表连接知识------------------------------
    #等值连接又叫内链接 inner join 只返回两个表中连接字段相等的行
    select * from A inner join B on A.id = B.id; #写法1
    select * from A,B where A.id = B.id; #写法2
    select a.id,a.title from A a inner join B b on a.id=b.id and a.id=1;#写法3 表的临时名称
    select a.id as ID,a.title as 标题 from A inner join B on A.id=B.id;#添加as字句
    
    #左连接又叫外连接 left join 返回左表中所有记录和右表中连接字段相等的记录
    select * from A left join B on A.id = B.id;
    
    select * from A left join (B,C,D) on (B.i1=A.i1 and C.i2=A.i2 and D.i3 = A.i3);#复杂连接
    
    #右连接又叫外连接 right join 返回右表中所有记录和左表中连接字段相等的记录
    select * from A right join B on A.id = B.id;
    
    #完整外部链接 full join 返回左右表中所有数据
    select * from A full join B on A.id = B.id;
    
    #交叉连接 没有where字句 返回卡迪尔积
    select * from A cross join B;
    -------------------------表连接结束------------------------------------------------------------

#显示表结构
    describe 
    #删除表
    drop table test；
    #重命名表
    alter table test_old rename test_new;
    #添加列
    alter table test add cn int(4) not null;
    #修改列
    alter table test change id id1 varchar(10) not null;
    #删除列 
    alter table test drop cn;
    #创建索引
    alter table test add index (cn,id);
    #删除索引
    alter table test drop index cn
    #插入数据
    insert into test (id,email,ip,state) values(2,'qq@qq.com','127.0.0.1','0');
    #删除数据 
    delete from test where id = 1;
    #修改数据
    update test set id='1',email='q@qq.com' where id=1;

实用的insert select语句：

INSERT INTO KM_MEMBER_RELA_FULLCUT (
    	SELECT
    		a.MB_ID,
    		b.MPFC_ID,
    		1
    	FROM
    		KM_MEMBER a,
    		KM_MEMBER_PREF_FULLCUT b
    	WHERE
    		b.MPFC_STATUS = 0
    	AND NOT EXISTS (
    		SELECT
    			1
    		FROM
    			KM_MEMBER_RELA_FULLCUT
    		WHERE
    			MB_ID = a.MB_ID
    		AND MPFC_ID = b.MPFC_ID
    	)
    )

#登录数据库
    mysql -hlocalhost -uroot -p;
    #修改密码
    mysqladmin -uroot -pold password new;
    
    
    #显示数据库
    show databases;
    #显示数据表
    show tables;
    #选择数据库
    use examples;
    #创建数据库并设置编码utf-8 多语言
    create database `examples` default character set utf8 collate utf8_general_ci;
    #删除数据库
    drop database examples;
    #创建表
    create table test(
        id int(10) unsigned zerofill not null auto_increment,
        email varchar(40) not null,
        ip varchar(15) not null,
        state int(10) not null default '-1',
    primary key (id)
    )engine=InnoDB;

转自：https://blog.csdn.net/suifenglie/article/details/74981817