数据库-学习笔记

qq_52691375

已于 2024-01-21 15:14:17 修改

阅读量1.3k

点赞数 28

文章标签：数据库学习笔记

于 2024-01-13 17:54:06 首次发布

本文链接：https://blog.csdn.net/qq_52691375/article/details/135461211

版权

数据库系统系统结构
1.数据库系统系统DBS

数据库管理系统

sql数据库（RDS关系型数据库）：ORCACLE，Oracle Mysql，DB2（IBM），SQL-server（MS），Mysql，MariaDB，percona server（taobao）

DBA工程师

2.sql语言（结构化查询语言）

DDL语句数据库定义语言，DML数据库操纵语言，DQL数据库表查询语言，DCL数据库控制语言

3.数据访问技术

ODBC PHP JDBC JAVA ASP.NET C#

版本

Mysql5.7 MySQL8.0

1.名词解释

数据库服务器

1.运行着数据库服务的设备 2.硬件+系统软件+MYSQL软件

数据库

1. 默认数据库

mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema | ##虚拟库，主要存储系统中的一些数据库对像信息，例如表，列，权限，字符等。
| mysql | ##授权库，主要存储系统用户的权限信息。
| performance_schema | ##数据库服务器的性能参数。
| sys | ##
+--------------------+
4 rows in set (0.04 sec)

2.表EXCEL的管理单元

表记录的管理单元

行（记录）信息的载体字段的管理单元

字段字段名，字段类型，字段约束组成

类型字符，数字，日期

约束不能为空，自动增长

2.DDL语句数据库定义语言

DDL-库

定义库

mysql>CREATE DATABASE discuz;
Query OK, 1 row affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| discuz |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)

注意：区分大小写，唯一性，不能使用关键字，不单独使用数字和特殊符号，

选择|进入数据库

mysql> use discuz
Database changed
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| discuz |
+------------+
1 row in set (0.00 sec)

删除数据库

mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| A1 |
| A2 |
| discuz |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.01 sec)

mysql> DROP DATABASE A1;
Query OK, 0 rows affected (0.08 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| A2 |
| discuz |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)

系统中的位置数据库在系统中的实体位置

[root@localhost ~]# ls /var/lib/mysql
A2 ca-key.pem '#ib_16384_1.dblwr' mysql public_key.pem
auto.cnf ca.pem ib_buffer_pool mysql.ibd server-cert.pem
binlog.000001 client-cert.pem ibdata1 mysql.sock server-key.pem
binlog.000002 client-key.pem ibtmp1 mysql.sock.lock sys
binlog.000003 discuz '#innodb_redo' performance_schema undo_001
binlog.index '#ib_16384_0.dblwr' '#innodb_temp' private_key.pem undo_002

备份

DDL-表

创表目的

1.表是数据库的存储单元

2.表由若干个字段（列）组成

3.主要用来存储数据记录（行）

实例1

mysql> CREATE DATABASE text1;

mysql> SHOW DATABASES;
mysql> SELECT DATABASE();
mysql> CREATE TABLE t1 (id int);

mysql> SHOW TABLES;

mysql> INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO t1 VALUES(2);

mysql> SELECT * FROM t1;

mysql> CREATE DATABASE text1;
Query OK, 1 row affected (0.02 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| A2 |
| discuz |
| information_schema |
| mysql |
| performance_schema |
| sys |
| text1 |
+--------------------+
7 rows in set (0.00 sec)
mysql> USE text1;
Database changed
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| text1 |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (id int);
Query OK, 0 rows affected (0.12 sec)
mysql> SHOW TABLES;
+-----------------+
| Tables_in_text1 |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.01 sec)
mysql> INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO t1 VALUES(2);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)

实列2

mysql> CREATE TABLE t2(id int(10),name varchar(20));

mysql> show tables;

mysql> desc t2; ##查看表结构

mysql> INSERT INTO t2 VALUES(1,"张三");

mysql> SELECT * FROM t2; ##查询表所有数据

mysql> CREATE TABLE t2(id int(10),name varchar(20));
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> show tables;
+-----------------+
| Tables_in_text1 |
+-----------------+
| t1 |
| t2 |
+-----------------+
2 rows in set (0.01 sec)

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> INSERT INTO t2 VALUES(1,"张三");
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM t2;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
+------+--------+
1 row in set (0.01 sec)

实列3

mysql> CREATE DATABASE school;

mysql> USE school;

mysql> SELECT DATABASE();

mysql> CREATE TABLE student1(id int(10),name varchar(20),sex varchar(20),age int(10));

mysql> show tables;

mysql> DESC student1;

mysql> INSERT INTO student1 VALUES(1,"zhangsan","male",21);

mysql> INSERT INTO student1 VALUES(2,"lisi","male",22);

mysql> INSERT INTO student1 VALUES(3,"limei","female",23);

mysql> SELECT * FROM student1;

mysql> CREATE DATABASE school;
Query OK, 1 row affected (0.01 sec)

mysql> USE school;
Database changed
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| school |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE student1(id int(10),name varchar(20),sex varchar(20),age int(10));
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student1 |
+------------------+
1 row in set (0.01 sec)

mysql> DESC student1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> INSERT INTO student1 VALUES(1,"zhangsan","male",21);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO student1 VALUES(2,"lisi","male",22);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO student1 VALUES(3,"limei","female",23);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM student1;
+------+----------+--------+------+
| id | name | sex | age |
+------+----------+--------+------+
| 1 | zhangsan | male | 21 |
| 2 | lisi | male | 22 |
| 3 | limei | female | 23 |
+------+----------+--------+------+
3 rows in set (0.00 sec)

3.DML

插入数据

完整插入：insert into 表名 values （值1,值2,值3,...);

部分插入：insert into 表名（列名，列名...）values(值1,值2,值3,...);

更新数据

update 表名 set 列名=值 where condition;

实列：use mysql; alter user 'root'@'localhost' indentified by 'Wanlele@123456'; flush privileges;

删除数据

delete from 表名 where condition;

实列-主键，外键

通过表yg 影响yg_pay

mysql> create table yg_pay(
-> id int not null auto_increment,
-> name varchar(20) not null,
-> payroll float(10.2) not null,
-> primary key(id),
-> foreign key(name) references yg1(name) on update cascade on delete cascade
-> );

mysql> insert into yg_pay values(1,'jack',2333.22)
-> (2,'tom',10000.00)
-> (3,'alic',20000);

mysql> create table yg1(
-> name varchar(20) not null,
-> sex enum('female','male') default 'female' not null,
-> bobby set('book','game')
-> primary key (name));

mysql> insert into yg1 values('jack','female','boook'),
-> ('tom','male','game'),
-> ('alic','female','book');

mysql> select * from yg1;
+------+--------+-------+
| name | sex | bobby |
+------+--------+-------+
| alic | female | book |
| jack | female | book |
| tom | male | game |
+------+--------+-------+
3 rows in set (0.00 sec)

mysql> select * from yg_pay;
+----+------+---------+
| id | name | payroll |
+----+------+---------+
| 1 | jack | 2333.22 |
| 2 | tom | 10000 |
| 3 | alic | 20000 |
+----+------+---------+
3 rows in set (0.00 sec)

mysql> update yg1 set name='jackk' where name='jack';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from yg_pay;
+----+-------+---------+
| id | name | payroll |
+----+-------+---------+
| 1 | jackk | 2333.22 |
| 2 | tom | 10000 |
| 3 | alic | 20000 |
+----+-------+---------+
3 rows in set (0.00 sec)

mysql> select * from yg1;
+-------+--------+-------+
| name | sex | bobby |
+-------+--------+-------+
| alic | female | book |
| jackk | female | book |
| tom | male | game |
+-------+--------+-------+
3 rows in set (0.00 sec)

4.DQL

准备环境

准备表

mysql> create database company

mysql> use company;
Database changed
mysql> create table employee1(
-> id int primary key auto_increment not null,
-> name varchar(20) not null,
-> sex enum('male','female') default 'male' not null,
-> hire_date date not null,
-> post varchar(50) not null,
-> job_description varchar(100),
-> salary double(15,2) not null,
-> office int,
-> dep_id int);

检查数据表

mysql> desc employee1;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | NO | | NULL | |
| job_description | varchar(100) | YES | | NULL | |
| salary | double(15,2) | NO | | NULL | |
| office | int | YES | | NULL | |
| dep_id | int | YES | | NULL | |
+-----------------+-----------------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)
mysql> show create table employee1;
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee1 | CREATE TABLE `employee1` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`sex` enum('male','female') NOT NULL DEFAULT 'male',
`hire_date` date NOT NULL,
`post` varchar(50) NOT NULL,
`job_description` varchar(100) DEFAULT NULL,
`salary` double(15,2) NOT NULL,
`office` int DEFAULT NULL,
`dep_id` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

插入数据

insert into employee1(name,sex,hire_date,post,job_description,salary,office,dep_id) values
-> ('jack','male',20230201,'instructor','teacher',5000,501,100),
-> ('tom','female',20230202,'instructor',NULL,2000,503,102),
-> ('zhangsan','male',20230203,'hr','hrcc',4000,502,101);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

查询数据

mysql> select * from employee1;
+----+----------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+----------+--------+------------+------------+-----------------+---------+--------+--------+
| 1 | jack | male | 2023-02-01 | instructor | teacher | 5000.00 | 501 | 100 |
| 2 | tom | female | 2023-02-02 | instructor | NULL | 2000.00 | 503 | 102 |
| 3 | zhangsan | male | 2023-02-03 | hr | hrcc | 4000.00 | 502 | 101 |
+----+----------+--------+------------+------------+-----------------+---------+--------+--------+
3 rows in set (0.00 sec)

MySQL查询

单条件查询where

select name,sex,salary from employee1 where sex='female'; ##查询部门女性打印表格name sex，salary显示

多条件查询and，or

select name,sex,salary from employee1 where sex='female' and salary>1000;

##查询部门女性且薪水大于1000打印表格name sex，salary显示

select name,sexsalary from employee1 where salary=2000 or salary=5000;

##查询部门薪水等于1000或等于5000打印表格name sex，salary显示

关键字between A and B在什么之间

mysql> select name,salary from employee1 where salary between 1000 and 2000; ##在什么之间

mysql> select name,salary from employee1 where salary not between 1000 and 2000; ##不在什么之间

关键字in集合查询

mysql> select name,salary from employee1 where salary in(2000,4000); ##薪水是2000或4000

mysql> select name,salary from employee1 where salary not in(2000,4000); ##薪水不是2000或4000

关键字is null

mysql> select name,hire_date,job_description from employee1 where job_description is null; ##工作描述为空

mysql> select name,hire_date,job_description from employee1 where job_description is not null; ##工作描述不为空

关键字like模糊查询

mysql> select id,name from employee1 where name like 'j%'; ##模糊查询，以j开头

！！注意%！！

mysql> select id,name from employee1 where name like 'j___'; ##模糊查询，以j开头，j后面三个字符的名字
+----+------+
| id | name |
+----+------+
| 1 | jack |
+----+------+
1 row in set (0.00 sec)

mysql> select id,name from employee1 where name like 'j_';##模糊查询，以j开头，j后面1个字符的名字
Empty set (0.01 sec)

查询排序

select * from employee1 order by salary asc;##薪水升序排

select * from employee1 order by salary desc;##薪水降序排

mysql> select id,name from employee1 order by id desc limit 2; ##显示前两行

5.DCL

dcl

权限级别

1.global level 所有库，所有表的权限

2.Database level 某个数据库中的所有表的权限

3.table 库中某个表的权限

4，column level 表中某个字段的权限

mysql用户管理

创建用户

create user user2@"localhost" identified by "Wanlele@123"; ##创建

mysql> select * from mysql.user\G; ##验证是否创建成功

修改用户密码(知道密码)

在centos系统里面修改

mysqladmin -uroot -p password 'Wanlele@123';

在mysql里面修改

alter user root@'localhost' identified by "Wanlele@123";

修改用户密码(不知道密码)

1.修改MySQL启动设置

vim /etc/my.cnf

在里面添加 skip-grant-tables

2.重启MySQL，无需密码登入,修改密码

systemctl restart mysqld

mysql -uroot

update mysql.user set authentication_string='' where user='root'; ##将密码为空

3.修改MySQL启动设置，重启设置

vim /etc/my.cnf

在里面注释掉添加的skip-grant-tables

systemctl restart mysqld

登入MySQL

[root@localhost ~]# mysql -P 3306 -uroot -p'Wanlele@123' -e"show databases;";
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| company |
| date |
| discuz |
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
| test1 |
| text1 |
+--------------------+
-P 端口号默认为3306

-e 执行于语句 -e"show databases;" ##可免去数据库交互步骤

MySQL权限管理

赋予权限

本地

1.创建用户 create user admin@'localhost' identified by 'Wanlele@123';

2.赋予权限

                grant all on *.* to admin@'localhost';   ##授予admin对bbs库的所有表所有权限（不包含授权）

                grant all on *.* to admin@'localhost' whit grant option;        ##授予admin对bbs库的所有表所有权限（包含授权）

                grant select(id),insert(name) on bbs.t1 to admin@'localhost';##授予admin对bbs库的t1表 id列的select权限，name列的insert权限（不包含授权）

远程ip

1.创建用户 create user admin@'192.168.137.%' identified by 'Wanlele@123';

##注意在这admin@'192.168.137.%' 和admin@'localhost'是不同账号，即远程登入账号admin和本地账户admin是不同的

问题注意

mysql> grant select(id),insert(name) on bbs.* to admin3@'localhost';
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used
mysql> grant select(id),insert(name) on bbs.t1 to admin3@'localhost';
Query OK, 0 rows affected (0.01 sec)

##在赋予权限进行table中列权限单独赋予要进行准确的表名选择

回收权限

查看自己权限

show grants\G;

查看他人权限

show grants for admin@'%';

回收权限

revoke 权限列表 on 数据库.表 from 用户名@客户端主机

revoke all on bbs.* from admin@'localhost';

删除用户 drop user admin@'localhost';

qq_52691375

关注

28
点赞
踩
26

收藏

觉得还不错? 一键收藏
1
评论
数据库-学习笔记

DDL-库定义库注意：区分大小写，唯一性，不能使用关键字，不单独使用数字和特殊符号，选择|进入数据库删除数据库系统中的位置数据库在系统中的实体位置备份DDL-表创表目的1.表是数据库的存储单元2.表由若干个字段（列）组成3.主要用来存储数据记录（行）实例1。
复制链接

扫一扫