数据库系统系统结构
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: 0mysql> 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';