mysql基础
1. 关系型数据库介绍
1.1 数据结构模型
数据结构模型主要有:
- 层次模型
- 网状结构
- 关系模型
关系模型:
二维关系:row,column
数据库管理系统:DBMS
关系:Relational,RDBMS
1.2 RDBMS专业名词
常见的关系型数据库管理系统:
- MySQL:MySQL,MariaDB,Percona-Server
- PostgreSQL:简称为pgsql
- Oracle
- MSSQL
**SQL:**Structure Query Language,结构化查询语言
**约束:**constraint,向数据表提供的数据要遵守的限制
- 主键约束:一个或多个字段的组合,填入的数据必须能在本表中唯一标识本行。且必须提供数据,不能为空(NOT NULL)。
- 一个表只能存在一个
- 惟一键约束:一个或多个字段的组合,填入的数据必须能在本表中唯一标识本行。允许为空(NULL)
- 一个表可以存在多个
- 外键约束:一个表中的某字段可填入数据取决于另一个表的主键已有的数据
- 检查性约束
**索引:**将表中的一个或多个字段中的数据复制一份另存,并且这些数据需要按特定次序排序存储
1.3 关系型数据库的常见组件
关系型数据库的常见组件有:
- 数据库:database
- 表:table,由行(row)和列(column)组成
- 索引:index
- 视图:view
- 用户:user
- 权限:privilege
- 存储过程:procedure
- 存储函数:function
- 触发器:trigger
- 事件调度器:event scheduler
1.4 SQL语句
SQL语句有三种类型:
- DDL:Data Defination Language,数据定义语言
- DML:Data Manipulation Language,数据操纵语言
- DCL:Data Control Language,数据控制语言
SQL语句类型 | 对应操作 |
---|---|
DDL | CREATE:创建 DROP:删除 ALTER:修改 |
DML | INSERT:向表中插入数据 DELETE:删除表中数据 UPDATE:更新表中数据 SELECT:查询表中数据 |
DCL | GRANT:授权 REVOKE:移除授权 |
2. mysql安装与配置
2.1 mysql安装
mysql安装方式有三种:
- 源代码:编译安装
- 二进制格式的程序包:展开至特定路径,并经过简单配置后即可使用
- 程序包管理器管理的程序包:
- rpm:有两种
- OS Vendor:操作系统发行商提供的
- 项目官方提供的
- deb
- rpm:有两种
由于sysql8.0以上的版本是要收费的所以我们要去官网安装我们需要的软件包
网站:Index of /232905/yum/mysql-5.7-community/el/7/x86_64
需要下载的五个软件包:
http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-server-5.7.43-1.el7.x86_64.rpm
http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-common-5.7.43-1.el7.x86_64.rpm
http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-client-5.7.43-1.el7.x86_64.rpm
http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-libs-5.7.43-1.el7.x86_64.rpm
http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-devel-5.7.43-1.el7.x86_64.rpm
用yum安装mysql:
#下载mysql5.7的软件包
[root@mysql ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-server-5.7.43-1.el7.x86_64.rpm http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-common-5.7.43-1.el7.x86_64.rpm http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-client-5.7.43-1.el7.x86_64.rpm http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-libs-5.7.43-1.el7.x86_64.rpm http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-devel-5.7.43-1.el7.x86_64.rpm
#下载成功
[root@mysql ~]# ls
anaconda-ks.cfg
mysql-community-client-5.7.43-1.el7.x86_64.rpm
mysql-community-common-5.7.43-1.el7.x86_64.rpm
mysql-community-devel-5.7.43-1.el7.x86_64.rpm
mysql-community-libs-5.7.43-1.el7.x86_64.rpm
mysql-community-server-5.7.43-1.el7.x86_64.rpm
#下载好了安装
[root@mysql ~]# yum -y localinstall *.rpm
2.2mysql配置
#安装好的mysql是没有设置开机自启的
[root@mysql ~]# systemctl enable --now mysqld #开启mysql服务并开机自启
[root@mysql ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled>
Active: active (running) since Tue 2023-08-29 15:03:48 CST; 10m>
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 13199 (mysqld)
Tasks: 27 (limit: 11301)
Memory: 284.3M
CGroup: /system.slice/mysqld.service
└─13199 /usr/sbin/mysqld --daemonize --pid-file=/var/ru>
Aug 29 15:03:45 mysql systemd[1]: Starting MySQL Server...
Aug 29 15:03:48 mysql systemd[1]: Started MySQL Server.
查看3306端口是否已经存在
[root@mysql ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
[root@mysql ~]#
查看mqspl的临时密码,并重新设置密码:
[root@mysql ~]# grep password /var/log/mysqld.log
2023-08-29T07:03:46.563007Z 1 [Note] A temporary password is generated for root@localhost: (8C<-ols+F.f
进入mysql里面设置密码:
[root@mysql ~]# mysql -uroot -p'(8C<-ols+F.f' #进入mqsql设置密码
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.43
Copyright (c) 2000, 2023, 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> #登录成功
#取消密码复杂的的设置并设置密码为123456
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
#删除mysql57-community-release这个软件包避免他自动升级mysql的包
[root@mysql ~]# rpm -qa | grep mysql
mysql-community-libs-5.7.43-1.el7.x86_64
mysql-community-devel-5.7.43-1.el7.x86_64
mysql57-community-release-el7-10.noarch
mysql-community-common-5.7.43-1.el7.x86_64
mysql-community-server-5.7.43-1.el7.x86_64
mysql-community-client-5.7.43-1.el7.x86_64
[root@mysql ~]# rpm -e mysql57-community-release
[root@mysql ~]# rpm -qa | grep mysql
mysql-community-libs-5.7.43-1.el7.x86_64
mysql-community-devel-5.7.43-1.el7.x86_64
mysql-community-common-5.7.43-1.el7.x86_64
mysql-community-server-5.7.43-1.el7.x86_64
mysql-community-client-5.7.43-1.el7.x86_64
3. mysql的程序组成
- 客户端
- mysql:CLI交互式客户端程序
- mysql_secure_installation:安全初始化,强烈建议安装完以后执行此命令
- mysqldump:mysql备份工具
- mysqladmin
- 服务器端
- mysqld
#mysql初始化
[root@mysql ~]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root: #在这里虽然自己设置的密码或者临时密码
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.
Estimated strength of the password: 50
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n #这里是否修改root的密码我选择no
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success. 是否删除匿名用户
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n
#是否禁止root用户远程登录
... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y #是否删除测试数据库访问它?
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success. #是否选择重新加载权限表吗
All done!
[root@mysql ~]#
3.1 mysql工具使用
//语法:mysql [OPTIONS] [database]
//常用的OPTIONS:
-uUSERNAME //指定用户名,默认为root
-hHOST //指定服务器主机,默认为localhost,推荐使用ip地址
-pPASSWORD //指定用户的密码
-P# //指定数据库监听的端口,这里的#需用实际的端口号代替,如-P3307
-V //查看当前使用的mysql版本
-e //不登录mysql执行sql语句后退出,常用于脚本
#演示不登录查看数据库
[root@mysql ~]# mysql -uroot -p123456 -e'show databases'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
#演示查看版本
[root@mysql ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.43, for Linux (x86_64) using EditLine wrapper
查看mysql的本版
#-h演示:登录到mysql的服务端,登录前必须在客户端下载好mariadb和关闭防火墙,且在服务端的mysql里给需要登录的客户端进行复权,才可以登录:
#服务端:
[root@mysql ~]# ss -antl #查看端口
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
[root@mysql ~]# mysql -uroot -p123456 #登录到MySQL
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.43 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, 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> grant all on *.* to 'dxw'@'192.168.227.133' identified by '123456';
#对客户机进行授权允许dxw这个用户用123456的密码登录
Query OK, 0 rows affected, 1 warning (0.00 sec)
#授权后我们要在服务端在刷星权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
#客户端:
[root@kh2 ~]# yum -y install mariadb #安装mariadb
[root@kh2 ~]# systemctl stop firewalld #关闭防火墙
[root@kh2 ~]# mysql -udxw -p123456 -h192.168.227.134
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.43 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> #登录成功
4. mysql数据库操作
4.1 DDL操作
4.1.1 数据库操作
#数据库的创建和删除和查看
1.创建数据库
//语法:CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';
//创建数据库sjk
mysql> create database if not exists sjk; #创建一个名为sjk的数据库
Query OK, 1 row affected (0.04 sec) #if not exists 如果不存在就创建可以不加
mysql> show databases; #查看sjk数据库是否创建成功
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sjk |
| sys |
+--------------------+
5 rows in set (0.00 sec)
//删除数据库
//语法:DROP DATABASE [IF EXISTS] 'DB_NAME';
//删除数据库sjk
mysql> DROP DATABASE IF EXISTS sjk;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
#删除成功!!!
4.1.2 表操作
//创建表
//语法:CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';
//在数据库sjk里创建表yuangong
mysql> create database sjk; #创建sjk的数据库
Query OK, 1 row affected (0.00 sec)
mysql> show databases; #查看是否创建成功
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sjk |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> use sjk; #进入到sjk的数据库里面,
Database changed
mysql> create table yuangong(id int not null,name varchar(20),age tinyint);
#创建一个名为yuangong的表
int #写入的数据必须是整数
not null #这个这段不能为空
varchar(20) #字母最大20个
tinyint #无符号值最大127
#创建一个名为student的表
mysql> CREATE TABLE student(id int not null,name varchar(20),age tinyint);
Query OK, 0 rows affected (0.06 sec)
#创建一个名为student1的表,并且就是主键primary key,意思就是id值不能一样。
mysql> CREATE TABLE student1(id int not null primary key,name varchar(20),age tinyint);
Query OK, 0 rows affected (0.05 sec)
#创建一个名为student10的表,并且就是主键primary key,且id自动增长auto_increment
mysql> CREATE TABLE student10(id int not null primary key auto_increment,name varchar(20),age tinyint);
Query OK, 0 rows affected (0.02 sec)
//查看当前数据库有哪些表
mysql> show tables;
+---------------+
| Tables_in_sjk |
+---------------+
| yuangong |
+---------------+
1 row in set (0.00 sec)
#查看表的结构
mysql> desc sjk.yuangong;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
//删除表
//语法:DROP TABLE [ IF EXISTS ] 'table_name';
//删除表yuangong
mysql> drop table yuangong;
Query OK, 0 rows affected (0.02 sec)
#查看表是否删除
mysql> SHOW TABLES;
Empty set (0.00 sec)
4.1.3 用户操作
mysql用户帐号由两部分组成,如’USERNAME’@‘HOST’,表示此USERNAME只能从此HOST上远程登录
这里(‘USERNAME’@‘HOST’)的HOST用于限制此用户可通过哪些主机远程连接mysql程序,其值可为:
- IP地址,如:172.16.12.129
- 通配符
- %:匹配任意长度的任意字符,常用于设置允许从任何主机登录
- _:匹配任意单个字符
#对所以227网段的所以主机都进行授权,并创建一个dxw的用户并设置密码为123456
mysql> grant all on *.* to 'dxw'@'192.168.227.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
#查看是否对277的所有网段都授权成功
MySQL [(none)]> show grants for dxw@'192.168.227.%';
+------------------------------------------------------+
| Grants for dxw@192.168.227.% |
+------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'dxw'@'192.168.227.%' |
+------------------------------------------------------+
1 row in set (0.000 sec)
#[root@kh2 ~]# mysql -udxw -p123456 -h192.168.227.134
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.43 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> #还是可以登录成功,但是一定要把之前的授权取消掉
4.1.4 查看命令SHOW
4.1.4 查看命令SHOW
mysql> SHOW CHARACTER SET; //查看支持的所有字符集
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
......
......
mysql> SHOW ENGINES; //查看当前数据库支持的所有存储引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment
| Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and
foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables
| NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql> SHOW DATABASES; //查看数据库信息
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| wangqingge |
+--------------------+
5 rows in set (0.00 sec)
mysql> SHOW TABLES FROM wangqingge; //不进入某数据库而列出其包含的所有表
+----------------------+
| Tables_in_wangqingge |
+----------------------+
| wangqing |
+----------------------+
1 row in set (0.00 sec)
//查看表结构
//语法:DESC [db_name.]table_name;
mysql> DESC wangqingge.wangqing;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
//查看某表的创建命令
//语法:SHOW CREATE TABLE table_name;
mysql> SHOW CREATE TABLE wangqingge.wangqing;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| wangqing | CREATE TABLE `wangqing` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//查看某数据库的创建命令
mysql> show create database sjk;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| sjk | CREATE DATABASE `sjk` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
//查看某表的状态
//语法:SHOW TABLE STATUS LIKE 'table_name'\G
mysql> use wangqingge; //进入数据库wangqingge
Database changed
mysql> SHOW TABLE STATUS LIKE 'wangqing'\G //查看wangqing表的状态
*************************** 1. row ***************************
Name: wangqing
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2018-08-13 00:53:21
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
#查看前面是student所有表的表状态
mysql> show tables;
+---------------+
| Tables_in_sjk |
+---------------+
| student |
| student1 |
| student10 |
| yuangong |
+---------------+
4 rows in set (0.00 sec)
mysql> show table status like 'student%'\G
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2023-08-31 15:01:11
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
*************************** 2. row ***************************
Name: student1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2023-08-31 15:02:51
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
*************************** 3. row ***************************
Name: student10
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 1
Create_time: 2023-08-31 15:03:24
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
3 rows in set (0.00 sec)
查看和杀死进程:
#查看当前正在运行的进程
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------+
| 14 | root | localhost | NULL | Sleep | 64 | | NULL |
| 15 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
#杀死进行
语法: kill 【加进程ip】
mysql> kill 14;
Query OK, 0 rows affected (0.00 sec)
成功杀死
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------+
| 16 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)
#给数据库添加一个带读锁的表,意思就是只让它读不让它写
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
#添加成功无法参加表,退出在重新加入就解除了
mysql> create database jjyy;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databse test' at line 1
4.1.5 获取帮助
//获取命令使用帮助
//语法:HELP keyword;
mysql> HELP CREATE TABLE; //获取创建表的帮助
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
......
......
#查看怎么创建表
mysql> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
......
......
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
大写的是固定死的,小写的是可以自己写的就是表名
[]里面的是可选项
[TEMPORARY] #创建临时表
[IF NOT EXISTS] #如果不存在就创建
(create_definition,...) #是代词,去下面找更多写法
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[STORAGE {DISK | MEMORY}]
[reference_definition]
4.2 DML操作
DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT),均属针对表的操作。
4.2.1 INSERT语句
//DML操作之增操作insert
//语法:INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),...
mysql> use wangqingge;
Database changed
mysql> INSERT INTO wangqing (id,name,age) VALUE (1,'tom',20); //一次插入一条记录
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO wangqing (id,name,age) VALUES (2,'jerry',23),(3,'wangqing',25),(4,'sean',28),(5,'zhangshan',26),(6,'zhangshan',20),(7,'lisi',NULL); //一次插入多条记录
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
#对values这个表,进行数据写入
mysql> insert student values(1,'tom',80),(2,'jerr',18),(1,'zhangsan)',20),(4,'ftx',20),(1,'dxw',19);
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0
#查看刚刚写入的数据,写入成功
mysql> select * from student;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | tom | 80 |
| 2 | jerr | 18 |
| 1 | zhangsan | 20 |
| 4 | ftx | 20 |
| 1 | dxw | 19 |
+----+----------+------+
5 rows in set (0.04 sec)
#对指定student1这个表的id和age的这段进行写入:
mysql> insert student1(id,age) values(7,20),(8,20);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student1;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | dtx | 20 |
| 2 | dxw | 20 |
| 3 | zhnagsan | 20 |
| 4 | lishi | 18 |
| 7 | NULL | 20 |
| 8 | NULL | 20 |
+----+----------+------+
6 rows in set (0.00 sec)
#id自动写入和关键字
mysql> desc student10;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
auto_increment #自动写入
PRI #id不能一样
#自动插入id
mysql> insert student10(name,age) values('tom',20),('jerry',23),('z(hangsan',25),('lisi',25);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from student10;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | zhangsan | 25 |
| 4 | lisi | 25 |
+----+----------+------+
4 rows in set (0.00 sec)
输入成功!
4.2.2 SELECT语句
字段column表示法
表示符 | 代表什么? |
---|---|
* | 所有字段 |
as | 字段别名,如col1 AS alias1 当表名很长时用别名代替 |
#别名的演示和使用
mysql> select * from student1;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | dtx | 20 |
| 2 | NULL | 20 |
| 3 | zhnagsan | 20 |
| 4 | lishi | 18 |
| 7 | jjyy | 20 |
| 8 | NULL | 20 |
+----+----------+------+
6 rows in set (0.00 sec)
#把name替换为姓名
mysql> select name as '姓名' from student1;
+----------+
| 姓名 |
+----------+
| dtx |
| NULL |
| zhnagsan |
| lishi |
| jjyy |
| NULL |
+----------+
6 rows in set (0.00 sec)
条件判断语句WHERE**
操作类型 | 常用操作符 |
---|---|
操作符 | >,<,>=,<=,=,!= BETWEEN column# AND column# LIKE:模糊匹配 RLIKE:基于正则表达式进行模式匹配 IS NOT NULL:非空 IS NULL:空 |
条件逻辑操作 | AND OR NOT |
ORDER BY:排序,默认为升序(ASC)
ORDER BY语句 | 意义 |
---|---|
ORDER BY ‘column_name’ | 根据column_name进行升序排序 |
ORDER BY ‘column_name’ DESC | 根据column_name进行降序排序 |
ORDER BY ’column_name’ LIMIT 2 | 根据column_name进行升序排序 并只取前2个结果 |
ORDER BY ‘column_name’ LIMIT 1,2 | 根据column_name进行升序排序 并且略过第1个结果取后面的2个结果 |
//DML操作之查操作select
//语法:SELECT column1,column2,... FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
mysql> use wangqingge;
Database changed
#显示wangqing这个表的所有字段
mysql> select * from wangqing;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)
#只显示wangqing这个表的name字段
mysql> SELECT name FROM wangqing;
+-----------+
| name |
+-----------+
| tom |
| jerry |
| wangqing |
| sean |
| zhangshan |
| zhangshan |
| lisi |
+-----------+
7 rows in set (0.01 sec)
语法:ORDER BY ‘column_name' 根据column_name进行升序排序
#对age字段里的数字进行升序排序:
mysql> SELECT * FROM wangqing ORDER BY age;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 7 | lisi | NULL |
| 1 | tom | 20 |
| 6 | zhangshan | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 5 | zhangshan | 26 |
| 4 | sean | 28 |
+----+-----------+------+
7 rows in set (0.00 sec)
语法:ORDER BY 'column_name' DESC 根据column_name进行降序排序
#对age字段里的数字进行降序排序:
mysql> SELECT * FROM wangqing ORDER BY age DESC;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
| 2 | jerry | 23 |
| 1 | tom | 20 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)
#对age字段进行升序排序并且,只取前两名
语法:ORDER BY ’column_name' LIMIT 2 根据column_name进行升序排序并只取前2个结果
mysql> SELECT * FROM wangqing ORDER BY age limit 2;
+----+------+------+
| id | name | age |
+----+------+------+
| 7 | lisi | NULL |
| 1 | tom | 20 |
+----+------+------+
2 rows in set (0.00 sec)
#对age字段进行升序排序并且,跳过第一名,取出后两名:
语法:ORDER BY ‘column_name' LIMIT 1,2 根据column_name进行升序排序并且略过第1个结果取后面的2个结果
mysql> SELECT * FROM wangqing ORDER BY age limit 1,2;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 6 | zhangshan | 20 |
+----+-----------+------+
2 rows in set (0.00 sec)
#取出age字段中大于且等于25的值:
语法:ELECT * FROM [表名] where [字段名] [条件]
mysql> SELECT * FROM wangqing WHERE age >= 25;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
+----+-----------+------+
3 rows in set (0.00 sec)
# and 两个条件都要满足
# or 两个条件满足一个就行
# 取出年龄大于等于25岁且name等于'zhangshan'值
mysql> SELECT * FROM wangqing WHERE age >= 25 AND name = 'zhangshan';
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
+----+-----------+------+
1 row in set (0.00 sec)
#取出年龄23到28之间的值:
between //之间
mysql> SELECT * FROM wangqing WHERE age BETWEEN 23 and 28;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
+----+-----------+------+
4 rows in set (0.00 sec)
# is not null //不为空的值
#取出年龄不为空的值:
mysql> select * from wangqing where age is not null;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
+----+-----------+------+
6 rows in set (0.00 sec)
# is null //为空
#取出年龄为空的值:
mysql> select * from wangqing where age is null;
+----+------+------+
| id | name | age |
+----+------+------+
| 7 | lisi | NULL |
+----+------+------+
1 row in set (0.00 sec)
mysql> select * from student1;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | dtx | 20 |
| 2 | NULL | 20 |
| 3 | zhnagsan | 20 |
| 4 | lishi | 18 |
| 7 | jjyy | 20 |
| 8 | NULL | 20 |
+----+----------+------+
6 rows in set (0.01 sec)
#取出年龄等于20的人数:
mysql> select count(1) from student1 where age = 20;
+----------+
| count(1) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
#取出年龄等于20的人数,且把前面的值替换为人数两个字:
mysql> select count(*) as '人数' from student1 where age = 20;
+--------+
| 人数 |
+--------+
| 5 |
+--------+
1 row in set (0.00 sec)
LIKE:模糊匹配
#取出年龄开头为1的值:
mysql> select * from student1 where age like '1%';
+----+-------+------+
| id | name | age |
+----+-------+------+
| 4 | lishi | 18 |
+----+-------+------+
1 row in set (0.00 sec)
4.2.3 update语句
//DML操作之改操作update
//语法:UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
mysql> select * from wangqing;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> update wangqing set age = 30 where name = 'lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from wangqing where name = 'lisi';
+----+------+------+
| id | name | age |
+----+------+------+
| 7 | lisi | 30 |
+----+------+------+
1 row in set (0.00 sec)
#修改第7排name的内容为jjyy
mysql> select * from student1;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | dtx | 20 |
| 2 | dxw | 20 |
| 3 | zhnagsan | 20 |
| 4 | lishi | 18 |
| 7 | NULL | 20 |
| 8 | NULL | 20 |
+----+----------+------+
6 rows in set (0.00 sec)
修改 要更新的表名 更新的字段 怎么定位
mysql> update student1 set name = 'jjyy' where id = 7;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student1;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | dtx | 20 |
| 2 | dxw | 20 |
| 3 | zhnagsan | 20 |
| 4 | lishi | 18 |
| 7 | jjyy | 20 |
| 8 | NULL | 20 |
+----+----------+------+
6 rows in set (0.00 sec)
#修改成功!!!
4.2.4 delete语句
//DML操作之删操作delete
//语法:DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
mysql> select * from wangqing;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 30 |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> delete from wangqing where id = 7; //删除某条记录
Query OK, 1 row affected (0.00 sec)
mysql> select * from wangqing;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
+----+-----------+------+
6 rows in set (0.00 sec)
mysql> delete from wangqing; //删除整张表的内容
Query OK, 6 rows affected (0.00 sec)
mysql> select * from wangqing;
Empty set (0.00 sec)
mysql> desc wangqing;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
4.2.5 truncate语句
truncate与delete的区别:
语句类型 | 特点 |
---|---|
delete | DELETE删除表内容时仅删除内容,但会保留表结构 DELETE语句每次删除一行,并在事务日志中为所删除的每行记录一项 可以通过回滚事务日志恢复数据 非常占用空间 |
truncate | 删除表中所有数据,且无法恢复 表结构、约束和索引等保持不变,新添加的行计数值重置为初始值 执行速度比DELETE快,且使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放 对于有外键约束引用的表,不能使用TRUNCATE TABLE删除数据 不能用于加入了索引视图的表 |
//语法:TRUNCATE table_name;
mysql> select * from wangqing;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> truncate wangqing;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from wangqing;
Empty set (0.00 sec)
mysql> desc wangqing;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
4.3 DCL操作
4.3.1 创建授权grant
权限类型(priv_type)
权限类型 | 代表什么? |
---|---|
ALL | 所有权限 |
SELECT | 读取内容的权限 |
INSERT | 插入内容的权限 |
UPDATE | 更新内容的权限 |
DELETE | 删除内容的权限 |
指定要操作的对象db_name.table_name
表示方式 | 意义 |
---|---|
. | 所有库的所有表 |
db_name | 指定库的所有表 |
db_name.table_name | 指定库的指定表 |
WITH GRANT OPTION:被授权的用户可将自己的权限副本转赠给其他用户,说白点就是将自己的权限完全复制给另一个用户。不建议使用。
GRANT priv_type,... ON [object_type] db_name.table_name TO ‘username'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| wangqingge |
+--------------------+
5 rows in set (0.00 sec)
//授权wangqing用户在数据库本机上登录访问所有数据库
mysql> GRANT ALL ON *.* TO 'wangqing'@'localhost' IDENTIFIED BY 'wangqing123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> GRANT ALL ON *.* TO 'wangqing'@'127.0.0.1' IDENTIFIED BY 'wangqing123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
//授权wangqing用户在172.16.12.129上远程登录访问wangqingge数据库
mysql> GRANT ALL ON wangqingge.* TO 'wangqing'@'172.16.12.129' IDENTIFIED BY 'wangqing123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
//授权wangqing用户在所有位置上远程登录访问wangqingge数据库
mysql> GRANT ALL ON *.* TO 'wangqing'@'%' IDENTIFIED BY 'wangqing123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
4.3.2 查看授权
//查看当前登录用户的授权信息
mysql> SHOW GRANTS;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
//查看指定用户wangqing的授权信息
mysql> SHOW GRANTS FOR wangqing;
+-----------------------------------------------+
| Grants for wangqing@% |
+-----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wangqing'@'%' |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR 'wangqing'@'localhost';
+-------------------------------------------------------+
| Grants for wangqing@localhost |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wangqing'@'localhost' |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR 'wangqing'@'127.0.0.1';
+-------------------------------------------------------+
| Grants for wangqing@127.0.0.1 |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wangqing'@'127.0.0.1' |
+-------------------------------------------------------+
1 row in set (0.00 sec)
4.3.3 取消授权REVOKE
//语法:REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';
mysql> revoke all on *.* from dxw@192.168.227.133; #取消dxw用户的远程登录
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges; #重新更新授权
Query OK, 0 rows affected (0.00 sec)
#查看是否删除
mysql> show grants for dxw@192.168.227.133;
+-----------------------------------------------+
| Grants for dxw@192.168.227.133 |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'dxw'@'192.168.227.133' |
+-----------------------------------------------+
1 row in set (0.05 sec)
USAGE就是没有权限
注意:mysql服务进程启动时会读取mysql库中的所有授权表至内存中:
- GRANT或REVOKE等执行权限操作会保存于表中,mysql的服务进程会自动重读授权表,并更新至内存中
- 对于不能够或不能及时重读授权表的命令,可手动让mysql的服务进程重读授权表
mysql> FLUSH PRIVILEGES;
对mysql workench进行连接:
实战:
第一步先在win上查看自己电脑的IP地址:
服务端:
[root@mysql ~]# mysql -uroot -p123456
加入mysql数据库里面
mysql> grant all on *.* to 'root'@'192.168.227.1' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
加入到workench进行连接:
链接成功!!!