Mysql基础指令
查看show
mysql
show databases;
use db1;
show tables;
创建create
show databases;
create database db2;
show databases;
use db2;
show tables;
create table test1 like db1.stu1;
desc test1;
修改alter
增add
alter table stu1 add phone varchar(11) after age;
删drop
alter table stu1 drop column phone;
改名rename
alter table stu1 rename stu;
修改字段名称和类型change
alter table stu change phone mobile char(10);
增加主键-primary key
alter table stu add primary key (id);
增加主键自增-auto_increment
alter table stu modify column id int unsigned not null auto_increment;
自增起始值
alter table stu auto_increment=1;
插入数据
insert stu (name,age) values ('zhangsan',15);
移除drop
删除表
drop table user3;
DML语句-insert、update、delete
插入数据
插入单行数据-insert
insert stu (name,age) values('xiaoming',20);
insert stu (name,age,is_del) values('xiaohong',18,false);
插入单行数据-insert into
insert into stu values(12,'xiaoli',11111111112,19,null);
插入多行数据
insert into xxx values(,),(,)...;
insert into stu (name,age) values('test1',20),('test2',21),('test3',22);
以更新的方式插入数据
insert into xxxx values() on duplicate key update xxx='xxx';
insert into stu (id,name) values(12,'zhangsan') on duplicate key update name='zhangsan';
将查询结果当值插入
insert into stu (name,age) select name,age from stu where id=11;
更新数据
根据条件更新数据-update
update stu set age=26 where id>12;
多个条件or、and
update stu set age=21 where (id=15 or name is null);
update stu set age=22 where (id=16 and name='xiaohong');
删除数据
根据条件删除-delete
delete from stu where id=16;
指定多条件删除
delete from stu where (mobile is null and id=15);
清空表数据
清空表
truncate table xxx
delete from xxx
DQL 语句
查询-select
普通查询
select * from stu;
AS方式将标题改名
select id as 学号,name as 姓名,age as 姓名 from stu;
多条件查询
select id,name from stu where id>15;
select id,name,age from stu where id in (10,14,15,17);
排序
指定排序order by
select * from stu order by name;
去重distinct
select distinct(age) from stu order by age desc;
Mysql视图基础
view视图
创建view视图
create view xxx as xxxxx;
create view v_stu as select * from stu where age=18;
更新视图信息
update v_stu set age=20 where id=16;
删除view视图
drop view v_stu;