数据库基础语法

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;