Python34_01数据库数据库基础语法[Python基础]
数据库基础语法
1. 登录和登出数据库
1. 连接数据库
mysql -uroot -pmysql
2. 退出数据库
quit, exit, ctrl+D
2. 数据库操作的sql语句
1. 显示时间
select now();
2. 查看所有数据库
show databases;
3. 创建数据库(例如: python1)
create datsbase python1 charse=utf8;
4. 使用数据库
use python1
5. 查看当前使用的数据库
select database();
6. 删除数据库
drop database python1;
3. 表结构操作的sql语句
1. 查看数据库中的所有表
show tables;
2. 创建表(例如: students表(id(无符号, 主键, 非空),姓名(字符串, 非空), 年龄(短整型, 非空, 默认为0)))
create table students(id int unsigned primary key auto_increment not null, name varchar(30) notnull, age tinyint not null default 0);
3. 查看表结构
desc students;
4. 修改表--添加字段
alter table students add birthday datetime default 0;
5. 修改表--修改字段, 不重命名
alter table students modify birthday date not null;
6. 修改表--修改字段, 重命名
alter table students change birthday birth date not null;
7. 修改表--删除字段
alter table students drop birthday;
8. 查看表创建语句
select create table students;
9. 查看库创建语句
select create database python1;
10. 删除表
drop table students;
11. 删除库
drop database pytohn1;
4. 表数据操作的sql语句
1. 增加--全列插入(向students表中增加列)
insert into students values(0, "张三", 18);
2. 增加--部分插入(向students表中增加name)
insert into students(name) values("张三")
3. 修改--全列修改
update students set name = "李四";
4. 修改--部分修改
update students set name = "李四" where id = 1;
5. 删除--物理删除
delect from students where id = 2;
6. 逻辑删除
alter table students add id_delect bit default 0;
update studets det students.id_delect = 1 where id = 2;
select * from students where id_delect = 1;
7. 查询--查询所有列
select * from students;
8. 查询指定列
select id, birth from students;
9. 字段顺序(按年龄从大到小排序)
select * from students order by age desc;
10. 使用关键字as起别名
(给字段起别名) select id as i, birth as b from students;
(给表起别名) select s.id, s.birth from student as s;
11. 消除重复行
select distinct id from students;
12. where 条件查询(比较, 逻辑, 模糊, 范围, 空)
(比较) select * from students where age > 7;
(逻辑) select * from students where age > 5 and age < 7;
(模糊) select * from students where name like "黄%_" ----(%替换任意个, _替换一个)
(范围) select * from students where age in (1, 2, 3, 5) ----(不连续范围)
(空) select * from students where age is not null;
13. between... and...连续范围内
select * from students where age between 2 and 7;
以上是 Python34_01数据库数据库基础语法[Python基础] 的全部内容， 来源链接： utcz.com/z/530405.html
