show databses;   #查看数据库 select database();  #查看当前数据库 use mysql;    #进入数据库 show create database p1807;  #查看数据库字符集类型   desc xxxx;  #查看表类型 select * from students;   #查看表的内容 show create table students;  #查看创建的表 show tables;  #查看所有表   select * from students where age in (12,18,34); select * from students where (age between 18 and 34) order by high desc, age desc; select sum(age)/count(*) from students;  select round(avg(age), 2) from students;   

计算人数

select count(*) as ‘男性‘ from students where gender=1; select gender, count(*) from students group by gender;  select gender, count(*) from students where gender=1 group by gender;   

拼接

select concat(id,‘ ‘,name,‘ ‘,age) from students;     select gender, group_concat(name,age," ",id) from students where gender=1 group by gender; select gender, group_concat(name), avg(age) from students group by gender having avg(age)>30; select gender, group_concat(name) from students group by gender having count(*)>4;  

分页

select * from students limit 2; select * from students limit 0, 2; select * from students where gender=2 order by high asc limit 0, 2;    

创建（表，库）

  create database test05; 创建数据库 create database `p-1807`;  特殊字符用`` create database p1807 charset utf8;默认字符类型为utf8    

--创建students表 (id, name, age, high, gender, cls_id)

create table students ( id int unsigned not null auto_increment primary key, name varchar(20), age tinyint unsigned default 0, high decimal(5,2), gender enum(‘男‘, ‘女‘, ‘中性‘, ‘保密‘) default ‘保密‘, cls_id int unsigned );   create table xxxx(id int, name varchar(20)); create table yyyy(id int primary key not null auto_increment, name varchar(30));    

--创建classes表(id, name)

create table classes( id int unsigned not null auto_increment primary key, name varchar(20) );  

增（添加数据）

insert into yyyy values (1,‘老铁‘); #1表示id往表里面添加数据 insert into yyyy values (0,‘老铁‘); #0表示自增   insert into students values(0,‘金星‘,18, 188.88, ‘中性‘, 0);   insert into students values (0, ‘小乔‘, 20, ‘女‘, 1, ‘1990-01-01‘) insert into students values (null, ‘蔡文姬‘, 20, ‘女‘, 1, ‘1990-01-01‘) insert into students values (default, ‘科比‘, 20, ‘1‘, 1, ‘1990-01-01‘) insert into students values (default, ‘ 康师傅‘, 20, ‘3‘, 1, ‘1990-01-01‘) insert into students(name, gender) values (‘大乔‘, 2),(‘貂蝉‘, 2);  

改（列表名，类型）

                     表名               列名       类型  alter table students add birthday datetime;  在表里添加列表   (modify 和chang 都是修改 用哪个根据个人喜好 修改列表名或类型 alter table students modify birthday date;  alter table students change birthday birth date default ‘2000-01-01‘; update students set  age=15 where id=8;  修改某列某行数据 alter database students default character set=uft8; 修改数据库默认字符集类型  

删（行，列，库）

delete from students where id=15;  删除某行 alter table students drop high;  删除列 drop database test03; 删除数据库  

-- 逻辑删除

  -- 用一条字段来表示 这条信息是否已经不能在使用了 -- 给students表添加一个is_delete字段 bit 类型 -- alter table students add is_delete bit default 0; -- update students set is_delete=1 where id=6;   alter table students add is_delete bit default 0; select * from students where is_delete=0;   bit(1)=1,0 bit(2)=(0,0,),(0,1),(1,0),(1,1)  

-- 数据库的备份与恢复(shell下运行)

备份

mysqldump -uroot -proot --databases testdb > testdb.sql   mysqldump -uroot -proot --all-databases > all_databases.sql  备份所有库 mysqldump -uroot -proot testdb students > students.sql  备份表  

恢复

source testdb.sql  

mysql增删改查

标签：str   enum   xxx   HERE   avg   创建   自增   字段   group