目录
简介
关系型数据库：
Oracle、DB2、Microsoft SQL Server、Microsoft Access、MySQL
非关系型数据库：
NoSql、Cloudant、MongoDb、redis、HBase
基本结构
mysql---库---表---数据
MySQL账户操作
# 如何进入mysql
mysql -uroot -pqwe123
# 退出
\q exit;
# 查看数据库
show databases;
# python交互的时候 因为root 用户限制了远程登录， 所以需要创建新的有远程登录的账号进行使用
创建账户
create user 'xiaoqi'@'%' identified by 'qwe123';
% --- 通配符 指 用户可以从任何地方进行登录
给权限：
grant all on *.* to 'xiaoqi'@'%';
all --- 所有的增删改查
*.* ---- 所有的库.所有的表
刷新：
flush privileges;
# 查看当前账号
select user();
# 删除用户
drop user 'hk'@'%';
MySQL库操作
数据库
select database(); # 查看当前在哪个数据库
show databases; # 打开多有数据库
create database mydb; # 创建数据库
show create database mydb;
drop database 库名; # 删库
# 进入数据库
use database;
MySQL表操作
数据表
# 创建 名为hk的表格 表格包含 id name class 三个字段
create table hk(id int,
name varchar(20),
class varchar(20));
show tables; # 查看当前库所有的表
desc hk; # 查看表结构
show create table hk; # 查看表结构
5.5以上的mysql 使用的都是 innoDB引擎
5.5以下用的是myisam引擎
# 删除表
drop table hk;
MySQL表数据操作
# ------插入------
insert
insert into tongyao(id, name, class) values(1,'haha','10');
insert into tongyao value(2,'hehe','10'),(3,'heihei','10');
insert tongyao set id=6, name='ran', class='10';
# ------查询-------
select
select * from tongyao; # * 代表所有的字段
select id, name from tongyao where id=1;
# ------修改------
update
update tongyao set name="haha" where id=1; # 如果不指定条件就会全部进行修改
# ------删除-------
delete
delete from tongyao where id=4;# 如果不指定条件就会全部删除
truncate table 表名； # 保留字段清空数据
mysql表的修改
# 改表名
alter table old_tb rename to new_tb;
# 改字段
alter table new_tb change id new_id int(11);
# 改字段类型
alter table new_tb modify new_id varchar(20);
表的查询
# 判断是否为空
```mysql
is null
is not null
create table t1(id int, name varchar(10));
insert into t1(id) values(1),(2);
insert into t1 values(3,'tongyao');
select * from t1 where name is not null;
select * from t1 where name is null;
```
## 逻辑判断
```mysql
and or not
select * from students where 16<=age and age<=21;
select * from students where age<=16 or age >=22;
select * from students where not age=16;
```
### 排序
```mysql
order by
正序
select * from students order by age;
倒序
select * from students order by age desc;
```
## 限制
```mysql
limit
展示5条数据
select * from students limit 5;
从第4条数据开始输出3条
select * from students limit 3,3;
```
## 去重
```mysql
distinct
select distinct subject_number from grades;
```
## 模糊查询
```mysql
like
select * from students where name like 'xi%'; # % 任意多个
select * from students where name like 'li__'; # _ 匹配任意一个
```
## 范围查询
```mysql
between
# age 在 (1,16,17,22)这里面的就全部展示
select * from students where age in (1,16,17,22);
# 一个区间内所有的数据 16到22所有的数据
select * from students where age between 16 and 22;
```
## 聚合函数
```mysql
select count(*) from students;
select count(name) from students;
select sum(age) from students;
select avg(age) from students;
```
## 分组
```mysql
# 通过subject_number,grade进行分组 然后做筛选
select subject_number,grade,count(*) from grades group by subject_number,grade having grade>=80;
where having
```
## 子查询
```mysql
# 每一个派生表 必须有自己的名字 as tongyao 这种方式
select * from (select * from students order by age limit 5) as tongyao where age <18;
```
约束
# default 默认约束
```mysql
create table t1(id int default 110, name varchar(10));
insert into t1(name) value("thenew"),("ameng");
insert into t1 value(1,'tongyao'),(55,'talent');
+------+---------+
| id | name |
+------+---------+
| 110 | thenew |
| 110 | ameng |
| 1 | tongyao |
| 55 | talent |
+------+---------+
create table a(id timestamp default now(), name varchar(10));
insert into a(name) values("ha");
+---------------------+------+
| id | name |
+---------------------+------+
| 2023-04-29 20:42:52 | ha |
| 2023-04-29 20:42:53 | ha |
| 2023-04-29 20:42:54 | ha |
| 2023-04-29 20:42:54 | ha |
| 2023-04-29 20:42:54 | ha |
| 2023-04-29 20:42:54 | ha |
| 2023-04-29 20:42:55 | ha |
| 2023-04-29 20:42:55 | ha |
| 2023-04-29 20:42:55 | ha |
| 2023-04-29 20:42:55 | ha |
| 2023-04-29 20:42:55 | ha |
| 2023-04-29 20:42:56 | ha |
| 2023-04-29 20:42:56 | ha |
| 2023-04-29 20:42:56 | ha |
| 2023-04-29 20:42:56 | ha |
| 2023-04-29 20:42:58 | ha |
+---------------------+------+
```
## not null 非空约束
```mysql
create table t2(id int not null, name varchar(20));
insert into t2(name) values("haha");
# Field 'id' doesn't have a default value
```
## unique key 唯一约束
```mysql
create table t3(id int unique key, name varchar(20) not null);
insert into t3 value(1,'xiaoming');
insert into t3 value(1,'xiaoming'); # 报错
```
## primary key 主键约束
```mysql
# 非空且唯一 就是主键约束
create table t4(id int primary key,
name varchar(20) not null unique key);
insert into t4 values(1,'yy');
```
## auto_increment 自增长约束
```mysql
create table t5(id int primary key auto_increment, name varchar(10));
insert into t5(name) values('xiaowang'),('laowang');
create table tb5(id int primary key auto_increment, name varchar(10))auto_increment=100;
```
## foreign key 外键约束
```mysq
create table a(a_id int primary key auto_increment,
a_name varchar(20) not null
)
insert into a values(1,'a1'),(2,'a2');
select * from a;
+------+--------+
| a_id | a_name |
+------+--------+
| 1 | a1 |
| 2 | a2 |
+------+--------+
学生表 （id name） 10 个学员
学生详情表 （id name age sex weight height hobby address ）
create table b(b_id int primary key,
b_name varchar(20) not null,
fy_id int not null,
foreign key(fy_id) references a(a_id)
);
insert into b values(1,'haha',1),(2,'heihei',2);
mysql> select * from b;
+------+--------+-------+
| b_id | b_name | fy_id |
+------+--------+-------+
| 1 | haha | 1 |
| 2 | heihei | 2 |
| 3 | haha | 2 |
| 4 | heihei | 1 |
+------+--------+-------+
create table b(b_id int primary key,
age int not null,
hobby varchar(20) not null,
address varchar(100) not null,
fy_id int not null,
foreign key(fy_id) references a(a_id)
);
insert into b values(1,18,'sing','hunan',1),(2,19,'pingpong','wuhan',3);
| a_id | a_name |
+------+----------+
| 1 | tong |
| 2 | Goldan |
| 3 | Ariticle |
| 4 | Dya |
+------+----------+
+------+-----+----------+---------+-------+
| b_id | age | hobby | address | fy_id |
+------+-----+----------+---------+-------+
| 1 | 18 | sing | hunan | 1 |
| 2 | 19 | pingpong | wuhan | 3 |
+------+-----+----------+---------+-------+
```
数值类型
字符类型
时间日期类型
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
