使用命令行方式操作mysql数据库:
- 登录数据库:mysql -uroot -p
- 创建数据库:
create database python_test charset=utf-8;
- 查看创建的数据库:
show databases;
- 使用刚才创建的数据库:
use python_test;
- 查询当前使用的数据库:
select database();
- 创建表:
create table students(
id int unsigned primary key auto_increment,
name varchar(20) default '',
age tinyint unsigned default 0,
gender enum('男','女','中性','保密') default '保密',
cls_id int unsigned default 0,
is_delete bit default 0
);
- 查看表信息:
desc students
- 查看创建表信息:
show create table students;
- 插入数据:
insert into students values
(0,'小明',18,2,1,0,180.00),
(0,'小月月',18,2,2,1,180.00),
(0,'彭于晏',18,1,1,0,185.00),
(0,'刘德华',29,1,2,0,175.00),
(0,'黄蓉',38,2,1,0,160.00),
(0,'凤姐',28,4,2,1,150.00),
(0,'王祖贤',18,2,1,1,172.00),
(0,'周杰伦',36,1,1,0,NULL),
(0,'程坤',27,1,2,0,181.00),
(0,'刘亦菲',25,2,2,0,166.00),
(0,'金星',33,3,3,1,162.00),
(0,'静香',12,2,4,0,180.00),
(0,'郭靖',11,1,4,0,170.00),
(0,'周杰',34,2,5,0,176.00);
- 查询数据:
select * from students;
- 添加字段:
alter table students add height decimal(5,2);
- 使用别名:as
select s.name,s.age from students as s;
- 去重查询:distinct
select distinct s.gender from students as s;
- 条件语句查询:
select * from students where age > 18;
select * from students where age > 18 and age < 30;
select * from students where age < 18 or age > 30;
select * from students where not age > 30;
- 模糊查询:
//%表示无或者多个匹配位置,_表示一个匹配位置
select name from students where name like "小%";
select name from students where name like "小_";
select name from students where name like "%小%";
//rlike使用正则匹配
- 范围查询:in
select name,age from students where age in (12,18,30);
select name,age from students where age not in (12,18,30);
select name,age from students where age between 12 and 34;
select name,age from students where age not between 12 and 34;
select name,age from students where not age between 12 and 34;
//错误写法:select name,age from students where age not (between 12 and 34);
- 空判断:
select * from students where height is null;
select * from students where height is not null;