mysql增删改查
查
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