mysql安装 以及 workbench连接数据库:
https://blog.csdn.net/weixx3/article/details/80782479
grant all privileges on . to 报错问题:
https://blog.csdn.net/anthony1314/article/details/100612544
关系型数据库:
数据分类存放,数据之间可以有联系(多线程)
nosql数据库系统–Redis:
数据分类存放,但是数据之间没有关联关系;
常用于秒杀库存、登陆信息、消息通知;
读写速度快;
适用于保存海量低价值的数据;
sql语言分类:
DML数据操作语言:增删改查
DDL数据定义语言:逻辑库 数据表 视图 索引
DCL数据控制语言:用户 权限 事务
注释:
# 这是一行注释文字
/*这是一段注释文字
xxxx*/
逻辑库常用命令:
xxx@xxx:~$ sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.37-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE test;
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
| test |
| todoDB |
+--------------------+
7 rows in set (0.00 sec)
mysql> USE school;
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_school |
+------------------+
| students |
| users |
+------------------+
2 rows in set (0.00 sec)
mysql> DESC users;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| userId | int | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| password | varchar(255) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> DROP DATABASE test; # 删除test逻辑库
数据表常用命令:
# 创建数据表
CREATE TABLE student(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, # 无字符数字,也就是从0开始的数字,自动增加
name VARCHAR(20) NOT NULL,
sex CHAR(1) NOT NULL,
birthday DATE NOT NULL,
tel CHAR(11) NOT NULL,
remark VARCHAR(200)
);
# 插入数据
INSERT INTO student VALUES(1,"ABC","女","1999-01-01","11111111111", NULL);
# 显示表创建语句
SHOW CREATE TABLE student;
# 删除表
DROP TABLE student;
数据类型:
整数常用:INT
浮点数常用:DECIMAL(10,2) # 整数加小数共10位,精确到两位小数
字符串:CHAR(2) 固定长度…VARCHAR(5) 可变长度
修改表结构:
# 添加字段
ALTER TABLE student
ADD address VARCHAR(50) NOT NULL,
ADD home_tel CHAR(10) NOT NULL;
# 修改字段
ALTER TABLE student
MODIFY home_tel CHAR(20) NOT NULL;
# 修改字段名称
ALTER TABLE student
CHANGE address home_addr VARCHAR(50) NOT NULL;
#删除字段
ALTER TABLE student
DROP home_addr,
DROP home_tel;
第一范式:原子性(元素是不可分割的基本数据项)
第二范式:唯一性 (主键)
第三范式:关联性(不存在传递依赖)
约束:
CREATE TABLE t_teacher(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
tel CHAR(11) NOT NULL UNIQUE,
sex ENUM("F","M") NOT NULL,
m BOOLEAN NOT NULL DEFAULT FALSE
);
# 不建议使用外键约束
索引:提升数据查找速度
# 创建表时增加索引
CREATE TABLE t_teacher(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
sex ENUM("F","M") NOT NULL,
INDEX idx_sex (sex)
);
# 修改表时增加索引
ALTER TABLE t_teacher
ADD INDEX idx_sex (sex)
----
# 查看表的所有索引
SHOW INDEX FROM t_teacher;
----
# 删除索引
DROP INDEX idx_sex ON sex;