mysql基本命令小结

注：

本文所用的表

一、数据库的操作

1、连接数据库

mysql -uusername -ppassword

2、退出数据库

exit

3、显示数据库版本

select version();

4、显示时间

select now();

5、查看当前使用的数据库

select database()

6、查看所有数据库

show databases;

7、创建数据库

create database 数据库名 charset=utf8；

8、查看创建数据库的语句

show create database 数据库名

9、使用数据库

use 数据库名

10、删除数据库

drop database 数据库名

二、数据表的操作

1、查看当前数据库中所有表

show tables

2、创建表

-- int unsigned 无符号整形
    -- auto_increment 表示自动增长
    -- not null 表示不能为空
    -- primary key 表示主键
    -- default 默认值
    -- create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]);

create table user(id int unsigned auto_increment primary key,name varchar(30) not null,age int unsigned);

3、查看表结构

desc user

4、查看表的创建语句

show create table user

5、增加表字段

alter table 表名 add 列名 类型
alter table user add sex varchar(30) not null;

6、修改表字段，不重命名版

alter table 表名 modify 原字段名 类型(约束)；
alter table user modify name varchar(40) ；

7、修改表字段，重命名版

alter table 表名 change 原字段名 新字段名 类型(约束)；
alter table user change name newname varchar(30) ；

8、删除表字段

alter table 表名 drop 列名
alter table user drop newname

9、删除表

drop table 表名
drop table user

三、增删改查

1、增

在user表中插入数据
insert into user(id,name,age) values(1,'jose','15');

2、改

update 表名 set 列1=值1，列2=值2... where 条件;
# 全部修改
update user set name = 'zhuzhu';
# 按条件修改
update user set name = 'wangwang' where id=4;
# 按条件修改多个值
update user set name='huahua',age='12' where id=4;

3、查

# 查询所有列
select * from 表名;
select * from user;
# 按一定条件查询
select * from 表名 where 条件
select * from user where id = 2;
# 查询指定列
select 列1,列2... from 表名;
select name,age from user;
# as别名
select 字段[as 别名],字段[as 别名] from 数据表;
select name as '姓名',age as '年龄' from user;
# 按字段的顺序
select age,name from user;

4、删

# 物理删除
delete from 表名 where 条件
delete from user where id=1;
# 逻辑删除
用一个字段来表示这条信息是否已经不能再使用了
给students表添加一个is_delete字段bit类型
alter table 表名 add 字段 类型 default 默认值
alter table user add is_delete bit default 0;

四、数据库备份与恢复

mysqldump -uroot -p 数据库名 > 名字.sql;
mysql -uroot -p 新数据库名 < 名字.sql;

如：
mysqldump -uroot -p python > 1.sql;
mysql -uroot -p python10 < 1.sql;

五、高级用法

1、消除重复行

select distinct name from user;

2、模糊查询

-- like 
-- % 替换任意个
-- _ 替换1个

# 查询名字中有‘小’所有的名字
select * from user where name like "%小%";
# 查询名字中以‘小’开头的名字
select * from user where name like "小%";
# 查询有两个字的名字
select * from user where name like "__";
# 查询有三个字的名字
select * from user where name like "___";
# 查询至少有2个字的名字
select * from user where name like "__%";

3、范围查询

-- in (1, 3, 8)表示在一个非连续的范围内
-- not in 不非连续的范围之内
-- between ... and ...表示在一个连续的范围内
-- not between ... and ...表示不在一个连续的范围内

# 查询年龄是18或30的信息
select * from user where age in (18,30);
# 查询年龄不是18或30的信息
select * from user where age not in (18,30);
# 查询年龄在18到30之间的的信息
select * from user where age between 18 and 30；
# 查询年龄不在18到30之间的的信息
select * from user where age not between 18 and 30；

4、排序

-- order by 字段
-- asc
-- asc从小到大排列，即升序
-- desc
-- desc从大到小排序，即降序

# 查询年龄在18到30的男性，按照年龄从小到大排序
select * from user where age between 18 and 34 order by age asc;
# 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序
select * from user where age between 18 and 34 and gender = "女" order by height desc,age asc; 

5、分组

group by
# 按照性别分组，查询所有的性别
select gender from user group by gender;
# 计算每种性别中的人数
select gender,count(*) from students group by gender;
# 查询同种性别中的姓名
select gender,group_concat(name) from students group by gender;

6、分页

limit start, count
# 查询前五个数据
select * from user limit 0,5;

7、连接查询

inner join ... on
select ... from 表A inner join 表B;
# 查询有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id=classes.id;

left join on
# 查询每位学生对应的班级信息
select * from students left join classes on students.cls_id = classes.id;
# 查询没有对应班级信息的学生
select * from students left join classes on students.cls_id = classes.id where classes.name is null;

right join on
select * from students right join classes on students.cls_id = classes.id;

8、子查询

查询学生的班级号能够对应的 学生名字
select id from classes;
select * from students where cls_id in( select id from classes );

六、外键的使用

1、在现有表添加外键

alter table 主表名 add foreign key（主表的外键）references 外键表(外键主键);
alter table students add  foreign key(cls_id) references classes(id);

2、创建表的同时设置外键 (注意 goods_cates 和 goods_brands 两个表必须事先存在)

-- foreign key (主表的外键) references 外键表(主键)
create table if not exists goods_key(
    id int primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    is_show bit default 1,
    is_saleoff bit default 0,
    --  添加外键
    foreign key(cls_id) references classes(id)

);

3、取消外键约束

需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
--show create table goods;

show create table goods;

-- 获取名称之后就可以根据名称来删除外键约束
--alter table students drop foreign key students_ibfk_1;
alter table students drop foreign key students_ibfk_1;