MySQL增删改查插入必会命令大全
基本命令
1、登录mysql
[root@localhost ~]# mysql -u root -p
2、查询当前所存在的库
MariaDB [(none)]> show databases;
3、选择需要增删改查的库
MariaDB [(none)]> use zwl;
4、查看当前库中所存在的表
MariaDB [zwl]> show tables;
5、查看表内所存在的信息
MariaDB [zwl]> select * from sziit;
6、刷新系统权限命令
MariaDB [zwl]> flush privileges;
——————————————————————————————————————
命令 —— 增
1、创建一个新的数据库
MariaDB [zwl]> create database villian;
2、在数据库中新增表
MariaDB [villian]> create table student(
-> ID INT,
-> NAME VARCHAR(20),
-> age int,
-> address varchar(20)
-> );
Query OK, 0 rows affected (0.01 sec)
3、向表中插入数据
MariaDB [villian]> insert into student (id,name,age) values(1,“xiaoming”,20);
——————————————————————————————————————
命令 —— 删
1、删除数据库
MariaDB [villian]> drop database test;
2、删除库中的表
MariaDB [zwl]> drop table sziit;
3、条件删除
MariaDB [zwl]> delete from banji where id=2;
4、删除表内所有数据
MariaDB [zwl]> delete from home;
——————————————————————————————————————
条件增改数据
insert into 是对行进行操作,而alter table是对字段进行操作。
1、使用命令“insert into student (id,name) values(5,“xiaoming”);”来增加部分数据,再使用select * from student;
MariaDB [villian]> insert into student (id,name) values (2,“xiaohong”);
2、使用alter table 语句对指定字段进行添加修改删除操作
MariaDB [villian]> alter table student drop address;
3、使用alter table 语句修改表名
MariaDB [villian]> alter table student rename to teacher;
或者是 - rename teble student to teacher;
4、修改制定数据
MariaDB [villian]> update teacher set age=30 where id=1;
5、修改多个指定数据
MariaDB [villian]> update teacher servlet =65,age=73 where id=3;
6、查询指定数据
MariaDB [villian]> select name,id from teacher;
7、查询指定数据时,并向该表内添加数据
MariaDB [villian]> select id,NAME,“80” as “mate” from teacher ;
8、修改表字段名(要重新指定该字段的类型)
MariaDB [villian]> alter table teacher change name nameID varchar(20)not null;
9、查询内容时,自定义合并某项内容
MariaDB [villian]> select id, (age+grate) as “he” from teacher;
10、逻辑查询 and
MariaDB [villian]> select * from teacher where id =1 and name=“xiaoming”;
11、逻辑查询 or
MariaDB [villian]> select * from teacher where id =2 or name=“xiaoming”;
12、比较条件 > ,<, >=, <=, ==, 不等于:<>, BETWEEN, AND
MariaDB [villian]> select * from teacher where grate > 99;
13、模糊条件查询 like
MariaDB [villian]> select * from teacher where name like “xiao%”; 后面加%
14、聚合函数:sum() avg() max() min() count()
MariaDB [villian]> select sum(grate) as “sum” from teacher;
15、排序查询,默认情况下按插入记录的顺序排序。order byasc 正序 desc倒序
MariaDB [villian]> select * from teacher order by id desc;
MariaDB [villian]> select * from teacher order by id asc;
16、分页查询limit 起始行,查询几行,主要用于分页。注意这里的行数从零开始。
MariaDB [villian]> select * from teacher limit 0,1;
MariaDB [villian]> select * from teacher limit 0,2;
——————————————————————————————————————
MySQL修改表结构(alter table),多列/多字段
--创建测试表
create table test(
id int,
name varchar(20)
);
--add支持多列,change/drop需要在每列前添加关键字,逗号隔开,'column'可有可无
--添加多列
alter table test add (c1 char(1),c2 char(1)); --正确,add支持多列
alter table test add column (c1 char(1),c2 char(1)); --正确
alter table test add c1 char(1),add c2 char(1); --正确
--修改多列
alter table test change c1 c3 char(1),change c2 c4 char(1); --正确
alter table test change column c1 c3 char(1),change column c2 c4 char(1); --正确
--name关键字作为字段名,重命名需要加反引号(`)
alter table table_name change `name` field_name varchar(50);
alter table test change (c1 c3 char(1),c2 c4 char(1)); --错误
--删除多列
alter table test drop c1,drop c2; --正确
alter table test drop column c1,drop column c2; --正确
alter table test drop c1,c2; --错误
alter table test drop (c1,c2); --错误
其他优秀作品:MySQL alter 用法,修改表,字段等信息
https://www.runoob.com/mysql/mysql-tutorial.html