mysql

本文详细介绍了数据库的基本操作,包括创建、删除和选择数据库,以及数据表的创建和删除。进一步阐述了数据的插入、查询、删除和更新,同时涉及了LIKE、UNION等关键字的使用。此外,还讲解了多表查询、ALTER命令的应用,以及索引的创建、管理和查询。最后,提到了处理重复数据的方法,并提供了复制表和删除重复数据的实例。
摘要由CSDN通过智能技术生成

一、数据库操作
创建数据库 create database name;

删除数据库 drop database name;

选择数据库 use name;

二、数据表操作
创建数据表 create table name(column_name column_type)

删除数据表 drop table name

三、数据操作
插入数据 insert into table (field1, field2, field3) values (values1, values2, values3)

查询数据 select * from table where id=1

删除数据 delete from table where id=1

更新数据 update table set field1=value1, field2=value2 where id=1

四、关键字
like
模糊查询 select * from school where like ‘%com%’;

union
唯一 select * from table union [all] select * from school order by field desc

group by
分组 select district_id, count(*) from school group by district_id;

                   select name, sum(sing) as count from  school group by name with rollup;

is null 、 is not null
五、多表查询
select a.id, a.author, b.count from table1 a, table2 b where a.author = b.author;

select a.id, a.author, b.count from table1 a insert join table2 b on a.author = b.author;

select a.id, a.author, b.count from table1 a left join table2 b on a.author = b.author;

select a.id, a.author, b.count from table1 a right join table2 b on a.author = b.author;

六、ALTER 命令
删除字段 alter table table_name drop field1;

增加字段 alter table table_name add field1 int;

修改字段类型 alter table table_name modify field char(10);

修改字段名称 alter table table_name change field1 field2 int;

修改表名 alter table table_name rename to table_name;

七、索引
创建索引 create index indexName on table (column_name)

添加索引 alter table tableName add index indexName(column_name)

删除索引 drop index [indexName] on table_name;

查询索引 show index from table_name;

八、复制表
show create table school;

create table school_copy;

insert into school_copy (field1, field2) select field1, field2 from school;

九、处理重复数据
统计重复数据 select count(*) as count, last_name, first_name from person group by last_name, first_name having count > 1;

十、运算符

      • / = <>(!=) <=>(严格比较相等) < > <= >=

between in not in is null is not null like

select 5 between 1 and 10;

select 5 in (2,3,4,5,6)

select ‘12345’ like ‘%2345’

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值