mysql基础

最新推荐文章于 2023-03-10 10:51:12 发布
最新推荐文章于 2023-03-10 10:51:12 发布
阅读量686 收藏
点赞数
文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/cn_zemo/article/details/125985489
版权

#mysql基础

文章目录


##关系型数据库介绍
###1.1数据结构模型
数据结构模型主要有：

层次模型
网状结构
关系模型

关系模型：
二维关系：row，column

数据库管理系统：DBMS
关系：Relational，RDBMS
###1.2RDBMS专业名词
常见的关系型数据库管理系统：

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.4SQL语句
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：移除授权
##2mysql安装与配置
###2.1mysql安装
mysql安装方式有三种：
源代码：编译安装
二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
程序包管理器管理的程序包：
rpm：有两种
OS Vendor：操作系统发行商提供的
项目官方提供的
deb
###源码安装mysql：
安装源码包

[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
--2022-07-22 20:06:31--  http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 23.64.178.143, 2600:140b:2:a9b::2e31, 2600:140b:2:a93::2e31
Connecting to dev.mysql.com (dev.mysql.com)|23.64.178.143|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm [following]
--2022-07-22 20:06:31--  https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Connecting to dev.mysql.com (dev.mysql.com)|23.64.178.143|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm [following]
--2022-07-22 20:06:32--  https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 23.58.116.230
Connecting to repo.mysql.com (repo.mysql.com)|23.58.116.230|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 25680 (25K) [application/x-redhat-package-manager]
Saving to: ‘mysql57-community-release-el7-11.noarch.rpm’

mysql57-community 100%[===========>]  25.08K   342KB/s    in 0.2s    

2022-07-22 20:06:33 (167 KB/s) - ‘mysql57-community-release-el7-11.noarch.rpm’ saved [25680/25680]

[root@localhost ~]# lls
-bash: lls: command not found
[root@localhost ~]# ls
anaconda-ks.cfg  mysql57-community-release-el7-11.noarch.rpm

[root@localhost ~]# rpm -Uvh mysql57-community-release-el7-11.noarch.rpm 
warning: mysql57-community-release-el7-11.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
	package mysql57-community-release-el7-11.noarch is already installed
[root@localhost ~]# ls /etc/yum.repos.d/
mysql-community.repo  mysql-community-source.repo  xx.repo
[root@localhost ~]#

下载mysql的服务包，数据库的客户包，数据库的命令包，数据库的工具包
首先，需要先禁用mysql模块

[root@localhost ~]# yum module disable mysql
Last metadata expiration check: 0:13:06 ago on Fri 22 Jul 2022 09:25:48 PM CST.
Dependencies resolved
 Package                Architecture          Version                Repository              Size
==================================================================================================
Disabling modules:
 mysql                                                                                           

Transaction Summary
==================================================================================================

Is this ok [y/N]: y
Complete!

再下载包

[root@localhost ~]# yum -y install mysql-community-server mysql-community-client  mysql-community-common mysql-community-devel  --nogpgcheck

一定要加nogpgcheck

查看

[root@localhost ~]# rpm -qa | grep mysql
mysql57-community-release-el7-11.noarch
mysql-community-libs-5.7.38-1.el7.x86_64
mysql-community-server-5.7.38-1.el7.x86_64
mysql-community-common-5.7.38-1.el7.x86_64
mysql-community-client-5.7.38-1.el7.x86_64
mysql-community-devel-5.7.38-1.el7.x86_64
[root@localhost ~]#

启动数据库设置开启自启

[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2022-07-22 21:43:52 CST; 43s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 4562 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYS>
  Process: 4513 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 4565 (mysqld)
    Tasks: 27 (limit: 4840)
   Memory: 267.9M
   CGroup: /system.slice/mysqld.service
           └─4565 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jul 22 21:43:50 localhost.localdomain systemd[1]: Starting MySQL Server...
Jul 22 21:43:52 localhost.localdomain systemd[1]: Started MySQL Server.
lines 1-15/15 (END)

[root@localhost ~]# 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@localhost ~]#

找到数据库的临时密码

[root@localhost ~]# grep "password"  /var/log/mysqld.log 
2022-07-22T13:43:51.031414Z 1 [Note] A temporary password is generated for root@localhost: hjpf7dy!uJp4
[root@localhost ~]#

登入数据库

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38

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>

修改数据库密码

1.设置密码安全性
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

2.设置密码长度

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

设置密码

mysql> alter user 'root'@'localhost' identified by '@123';
Query OK, 0 rows affected (0.00 sec)

mysql>

退出验证

mysql> quit
Bye

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.38 MySQL Community Server (GPL)

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>

删除mysql的源码包，避免mysql自动升级

[root@localhost ~]# rpm -e mysql57-community-release
[root@localhost ~]# ls /etc/yum.repos.d/
xx.repo
[root@localhost ~]#

下载mariadb

[root@localhost ~]# mount /dev/cdrom /mnt/
mount: /mnt: WARNING: device write-protected, mounted read-only.
[root@localhost ~]# yum install -y mariadb*

查看

[root@localhost ~]# systemctl enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# ss -antl
State      Recv-Q     Send-Q         Local Address:Port           Peer Address:Port     
LISTEN     0          80                   0.0.0.0:3306                0.0.0.0:*        
LISTEN     0          128                  0.0.0.0:22                  0.0.0.0:*        
LISTEN     0          128                     [::]:22                     [::]:*        
[root@localhost ~]#

进入到数据库（mariadb第一次不需要密码）

[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

设置密码

MariaDB [(none)]> set password  = password('@123');
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]>

验证

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

##mysql的程序组成
客户端
mysql：CLI交互式客户端程序
mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
mysqldump：mysql备份工具
mysqladmin

服务器端
mysqld

##mysql工具使用
语法：mysql [OPTIONS] [database]
常用的OPTIONS：
­-uUSERNAME 指定用户名，默认为root
-­hHOST 指定服务器主机，默认为localhost，推荐使用ip地址
-­pPASSWORD 指定用户的密码
­P# 指定数据库监听的端口，这里的#需用实际的端口号代替，如­P3307
-­V 查看当前使用的mysql版本

[root@localhost ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.38, for Linux (x86_64) using EditLine wrapp
er

­-e 不登录mysql执行sql语句后退出，常用于脚本

[root@localhost ~]# mysql -uroot -p@123 -e 'show databases'
mysql: [Warning] Using a password on the command line interface can be in
secure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@localhost ~]#

##服务器监听得两种socket地址
socket类型及说明
ip
socket
默认监听在tcp的3306端口，支持远程通信
unix
sock
监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock）仅支持本地通信 ;
server地址只能是：localhost，127.0.0.1
##mysql数据库操作
###DDL操作
1.创建数据库
语法：CREATE DATABASE [IF NOT EXISTS] ‘DB_NAME’;
创建数据库wangqingge

mysql> CREATE DATABASE IF NOT EXISTS wangqingge;
mysql>
mysql> create database zbc;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zbc |
+--------------------+

2.删除数据库
语法：DROP DATABASE [IF EXISTS] ‘DB_NAME’;
删除数据库wangqingge

mysql> drop database zbc;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>

3.查看当前实例有哪些数据库

mysql> SHOW DATABASES;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zbc |
+--------------------+

表操作
1.创建表
语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE=’存储
引擎类型’;
在数据库wangqingge里创建表wangqing
mysql> CREATE DATABASE wangqingge; 创建数据库wangqingge
mysql> use wangqingge; 进入wangqingge数据库
mysql> CREATE TABLE wangqing (id int NOT NULL,name VARCHAR(100) NOT NULL,age
tinyint); 创建wangqing表

mysql> use zbc
Database changed
mysql> CREATE TABLE zbc1 (id int NOT NULL,name VARCHAR(100) NOT NULL,age
tinyint);
Query OK, 0 rows affected (0.01 sec)
mysql> show tables
-> ^C
mysql> show tables;
+---------------+
| Tables_in_zbc |
+---------------+
| zbc1 |
+---------------+
1 row in set (0.00 sec)
mysql>

2.查看当前数据库有哪些表

mysql> SHOW TABLES;
mysql> show tables
-> ^C
mysql> show tables;
+---------------+
| Tables_in_zbc |
+---------------+
| zbc1 |
+---------------+
1 row in set (0.00 sec)
mysql>

3.删除表
语法：DROP TABLE [ IF EXISTS ] ‘table_name’;
删除表wangqing

mysql> DROP TABLE wangqing;
mysql> drop table zbc1;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql>

int(大整数值)
date(日期值xx­xx­xx )
datetime（混合日期和时间值 xx­xx­xx xx:xx:xx）
varchar （变长字符串）
###用户操作
mysql用户帐号由两部分组成，如’USERNAME’@’HOST’，表示此USERNAME只能从此HOST上远
程登录
这里（’USERNAME’@’HOST’）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其
值可为：
IP地址，如：172.16.12.129
通配符
%：匹配任意长度的任意字符，常用于设置允许从任何主机登录
_：匹配任意单个字符
###查看命令show
1.mysql> SHOW CHARACTER SET; 查看支持的所有字符集

mysql> show character set;
+----------+---------------------------------+---------------------+-----
---+
| Charset | Description | Default collation | Maxl
en |
+----------+---------------------------------+---------------------+-----
---+
| 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 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci |
1

2.mysql> SHOW ENGINES; 查看当前数据库支持的所有存储引擎

mysql> show engines;
+--------------------+---------+-----------------------------------------
-----------------------+--------------+------+------------+
| Engine | Support | Comment
| Transactions | XA | Savepoints |
+--------------------+---------+-----------------------------------------
-----------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level lockin
g, 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 w
rite 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>

3.mysql> SHOW DATABASES; 查看数据库信息

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

4.mysql> SHOW TABLES FROM wangqingge; 不进入某数据库而列出其包含的所有表

mysql> show tables from zbc;
+---------------+
| Tables_in_zbc |
+---------------+
| zbc1 |
+---------------+
1 row in set (0.00 sec)
mysql>

5.查看表结构
语法：DESC [db_name.]table_name;

mysql> DESC wangqingge.wangqing;
mysql> desc zbc.zbc1;
+-------+--------------+------+-----+---------+-------+
| 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)
mysql>

6.查看某表的创建命令
语法：SHOW CREATE TABLE table_name;

mysql> SHOW CREATE TABLE wangqingge.wangqing;
mysql> show create table zbc.zbc1;
+-------+----------------------------------------------------------------
-------------------------------------------------------------------------
--------------+
| Table | Create Table
|
+-------+----------------------------------------------------------------
-------------------------------------------------------------------------
--------------+
| zbc1 | CREATE TABLE `zbc1` (
`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>

7.查看某表的状态
语法：SHOW TABLE STATUS LIKE ‘table_name’\G
mysql> use wangqingge; 进入数据库wangqingge

mysql> show table status like 'zbc1'\G;
*************************** 1. row ***************************
Name: zbc1
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: 2022-07-25 15:13:31
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>

获取帮助
获取命令使用帮助
语法：HELP keyword;
mysql> HELP CREATE TABLE; 获取创建表的帮助

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

###DML操作
DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。
###insert语句
DML操作之增操作insert
语法：INSERT [INTO] table_name [(column_name,…)] {VALUES | VALUE} (value1,…),(…),…
（一次只能插入一条）
VALUE (1,’tom’,20); 一次插入一条记录

mysql> use zbc;
Database changed
mysql> insert into zbc1 (id,name,age)value(1,'tom',20);
Query OK, 1 row affected (0.01 sec)
mysql> select * from zbc1;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | tom | 20 |
+----+------+------+
1 row in set (0.00 sec)
mysql>

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); 一次插入多条记录

mysql> insert into zbc1 (id,name,age)values(1,'tom',20),(2,'z',21),
(3,'b',23),(4,'c',21);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from zbc1;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | tom | 20 |
| 1 | tom | 20 |
| 2 | z | 21 |
| 3 | b | 23 |
| 4 | c | 21 |
+----+------+------+
5 rows in set (0.00 sec)
mysql>

###select语句
字段column表示法
表示符 代表什么

  • 所有字段
    as 字段别名，如select name as 姓名当表名很长时用别名代替
    条件半段语句where
    ###操作类型
    常用操作符
    操作符
    ，<，>=，<=，=，!= ;BETWEEN column# AND column#;LIKE：模糊匹配（要用百分
    号%）;RLIKE：基于正则表达式进行模式匹配;IS NOT NULL：非空;IS NULL：空
    条件逻辑操作
    AND ; OR ;NOT
    模糊查找
mysql> select * from zbc1 where name like '%z%';
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | z | 21 |
+----+------+------+
1 row in set (0.00 sec)
mysql>

not用法

mysql> select * from zbc1 where not name = 'z';
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | tom | 20 |
| 1 | tom | 20 |
| 3 | b | 23 |
| 4 | c | 21 |
+----+------+------+
4 rows in set (0.00 sec)
mysql>

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个结果
update语句
DML操作之改操作update
语法：UPDATE table_name SET column1 = new_value1[,column2 = new_value2,…] [WHERE
clause] [ORDER BY ‘column_name’ [DESC]] [LIMIT [m,]n];

mysql> update wangqing set age = 30 where name = 'lisi';
mysql> select * from wangqing where name = 'lisi';
+----+------+------+
| id | name | age |
+----+------+------+
| 7 | lisi | 30 |
+----+------+------+
1 row in set (0.00 sec)

###delete语句
DML操作之删操作delete
语法：DELETE FROM table_name [WHERE clause] [ORDER BY ‘column_name’ [DESC]] [LIMIT
[m,]n];

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)

删除表时，只会删除表中的数据，但不会删除表本身
###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)

###DCL操作
创建授权grant
权限类型
权限类型 代表含义
all 所有权限
select 读取内容的权限
insert 插入内容的权限
update 更新内容的权限
delete 删除内容的权限
grant all on . to x用户 加上密码–给用户x所有库下的所有表的所有权限
指定要操作的对象db_name.table_name
表达方式 意义
` * .* 所有库的所有表
db_name.* 指定库的所有表
db_name.table_name 指定库的指定表
WITH GRANT OPTION：被授权的用户可将自己的权限副本转赠给其他用户，说白点就是将自己的
权限完全复制给另一个用户。不建议使用。
授权wangqing用户在数据库本机上登录访问所有数据库

mysql> GRANT ALL ON *.* TO 'zbc'@'localhost' IDENTIFIED BY '@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> GRANT ALL ON *.* TO 'zbc'@'127.0.0.1' IDENTIFIED BY '@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

授权wangqing用户在192.168.200.128上远程登录访问wangqingge数据库

mysql> GRANT ALL ON wangqingge.* TO 'zbc'@'192.168.200.128' IDENTIFIED BY
'@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

授权wangqing用户在所有位置上远程登录访问wangqingge数据库

mysql> GRANT ALL ON *.* TO 'zbc'@'%' IDENTIFIED BY '@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看授权
查看当前登录用户的授权信息

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 zbc;
+-----------------------------------------------+
| Grants for zbc@% |
+-----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zbc'@'%' |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR 'zbc'@'localhost';
+-------------------------------------------------------+
| Grants for zbc@localhost |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zbc'@'localhost' |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR 'zbc'@'127.0.0.1';
+-------------------------------------------------------+
| Grants for zbc@127.0.0.1 |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zbc'@'127.0.0.1' |
+-------------------------------------------------------+
1 row in set (0.00 sec)

取消授权
语法：REVOKE priv_type,… ON db_name.table_name FROM ‘username’@‘host’;

mysql> REVOKE ALL ON *.* FROM 'wangqing'@'192.168.200.128';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：
GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更
新至内存中
对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表
mysql> FLUSH PRIVILEGES;（刷新权限）
mysql数据类型
数值：
数值类型 大小（bytes）范围（有符号） 范围（无符号） 用途
tinyint 1 (­128,127) (0,255) 小整数值
smallint 2 (­32768,32767) (0,65535) 大整数值mediumint 3 (­8388608,8388607) (0,16777215) 大整数值
int 或integer4 (­2147483648,2147483647(0,4294967295) 大整数值
bigint 8 (­9,223,372,036,854,775,808，9 223
372 036 854 775 807)
(0，18 446 744
073 709 551 615)
极大整数值
float 4 (­3.402 823 466 E+38，­1.175 494
351 E­38)，0，(1.175 494 351 E38，3.402 823 466 351 E+38))
0，(1.175 494
351 E­38，3.402
823 466 E+38)
单精度，浮点数值
double 8 (­1.797 693 134 862 315 7
E+308，­2.225 073 858 507 201 4 E308)，0，(2.225 073 858 507 201 4
E­308，1.797 693 134 862 315 7
E+308)
0，(2.225 073
858 507 201 4 E308，1.797 693
134 862 315 7
E+308)
双精度，浮点
数值
decimal 对
decimal(M,D)
，如果 M>D
，为 M+2 否
则为 D+2
依赖于 M 和 D 的值 依赖于 M 和 D 的值小数值
日期和时间：
数值类型 大小
（bytes）
范围 格式 用途
date 3 1000­01­01/9999­12­31 YYYY­MMDD
日期
值
time 3 ‘­838:59:59’/’838:59:59’ HH:MM:SS 时间
值或持续时间
year 1 1901/2155 YYYY 年份
值
datetime 8 1000­01­01 00:00:00/9999­12­31 23:59:59 YYYY­MMDD
HH:MM:SS
混合日期和时间值
timestamp 4 1970­01­01 00:00:00/2038
结束时间是第 2147483647 秒，北京时间
2038­1­19 11:14:07，格林尼治时间 2038年1
月19日 凌晨 03:14:07
YYYYMMDD
HHMMSS
混合日期和时间值，时间戳
字符串：
数值类型 大小（bytes） 用途
char 0~255 定长字符串
varchar 0~65535 变长字符串
tinyblob 0~255 不超过 255 个字符的二进制字符串
tinytext 0~255 短文本字符串
blob 0~65535 二进制形式的长文本数据
text 0~65535 长文本数据
mediumblob 0~16777215 二进制形式的中等长度文本数据
mediumtext 0~16777215 中等长度文本数据
longblob 0~4294967295 二进制形式的极大文本数据
longtext 0~4294967295 极大文本数据
###练习题
1.搭建mysql服务
2.创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，
age），表结构如下：
mysql> desc student;
+——­+————–+——+—–+———+—————­+
| Field | Type | Null | Key | Default | Extra |
+——­+————–+——+—–+———+—————­+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+——­+————–+——+—–+———+—————­+

mysql> create table student (id int not null,name VARCHAR(100) NOT NULL,a
ge tinyint);
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------+
| Tables_in_zbc |
+---------------+
| student |
| zbc1 |
+---------------+
2 rows in set (0.00 sec)
mysql> desc student;
+-------+--------------+------+-----+---------+-------+
| 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)
mysql>

3.查看下该新建的表有无内容（用select语句）

mysql> select * from student;
Empty set (0.00 sec)
mysql>

4.往新建的student表中插入数据（用insert语句），结果应如下所示：
+—­+————­+——+
| id | name | age |
+—­+————­+——+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+—­+————­+——+

mysql> insert into student (id ,name,age)values(1,'tom',20),(2,'jerry',2
3),(3,'wangqing',25),(4,'sean',28),(5,'zhangshan',26),(6,'zhangsan',20),
(7,'lisi',null),(8,'chenshuo',10),(9,'wangwu',3),(10,'qiuyi',15),(11,'qiu
xiaotian',20);
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangsan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
mysql>

5.修改lisi的年龄为50

mysql> update student set age = 50 where name ='lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangsan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
mysql>

6.以age字段降序排序

mysql> select * from student order by age desc;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
| 2 | jerry | 23 |
| 1 | tom | 20 |
| 6 | zhangsan | 20 |
| 11 | qiuxiaotian | 20 |
| 10 | qiuyi | 15 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
+----+-------------+------+
11 rows in set (0.00 sec)
mysql>

7.查询student表中年龄最小的3位同学跳过前2位

mysql> select * from student order by age limit 2,3;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 10 | qiuyi | 15 |
| 1 | tom | 20 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
3 rows in set (0.00 sec)
mysql>

8.查询student表中年龄最大的4位同学

mysql> select * from student order by age desc limit 4;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
+----+-----------+------+
4 rows in set (0.00 sec)
mysql>

9.查询student表中名字叫zhangshan的记录

mysql> select * from student where name = 'zhangshan';
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
+----+-----------+------+
1 row in set (0.00 sec)
mysql>

10.查询student表中名字叫zhangshan且年龄大于20岁的记录

mysql> select * from student where name = 'zhangshan' and age >=20;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
+----+-----------+------+
1 row in set (0.00 sec)
mysql>

11.查询student表中年龄在23到30之间的记录

mysql> select * from student where age between 23 and 30;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
+----+-----------+------+
4 rows in set (0.00 sec)
mysql>

12.修改wangwu的年龄为100

mysql> update student set age = 100 where name ='wangwu';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangsan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
mysql>

13.删除student中名字叫zhangshan且年龄小于等于20的记录

mysql> delete from student where name = 'zhangsan' and age <=20;
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
10 rows in set (0.00 sec)
mysql>
cn_zemo
关注
Linux进阶指令笔记（一）
m0_62064241的博客
07-16 213
Linux
Ubuntu显示lines 1-14/14（end）
m0_65280246的博客
02-03 1247
Ubuntu下载MySQL，运行MySQL时，终端显示这个，一直退出不了，我查了之后以为是要你输入密码，结果不行，然后搜到按q键退出，md。
退出 (END)
丿灬安之若死
04-16 548
终端翻阅到最后出现这个退出 (END) 按q键即可退出
line 1-19/19（END）怎么解决？
CharmingC1的博客
03-10 1672
line 1-19/19（END）怎么解决？
康师傅mysql基础pdf
01-28
康师傅mysql基础pdf
MySQL基础.docx
03-16
mysql基础部分内容，适用于小白学习。主要包括mysql基本概念、单行函数、常见函数、连接查询、分组查询、联合查询、分页查询、子查询、事务、视图、存储过程、函数、流程控制、
mysql mysql基础介绍
09-24
mysql mysql基础介绍
mysql基础知识
01-28
这个是我自己总结的mysql的基本知识，里面从数据库的增删到表的增删，sql查询，事务等知识，简单通俗易懂，值得下载
mysql lines_MySQL
weixin_29146779的博客
01-27 717
一.语法介绍LOAD DATA[LOW_PRIORITY | CONCURRENT] [LOCAL]INFILE 'file_name'[REPLACE | IGNORE]INTO TABLE tbl_name[PARTITION (partition_name [, partition_name] ...)][CHARACTER SET charset_name][{FIELDS | COLUM...
Linux常用命令行汇总（2）
wys_NO1的博客
11-21 2792
15、使用gnome-screenshot实现屏幕截图 使用gnome-screenshot实现延时10秒屏幕截图。输入下面命令，然后打开Applications/Accessories/Text Editor 方法：gnome-screenshot -d 10 16、使用中文输入法 方法：点击菜单System/Preferences/Keyboard Input Methods
linux启动http启动失败,apache2 启动失败，出现下列错误，请问怎么解决，谢谢
weixin_28716973的博客
05-15 481
该楼层疑似违规已被系统折叠隐藏此楼查看此楼apache2.service - The Apache HTTP ServerLoaded: loaded (/lib/systemd/system/apache2.service; enabled; vendor preset:Drop-In: /lib/systemd/system/apache2.service.d└─apache2-system...
终端出现 (END)，如何退出？
weixin_30371875的博客
04-20 4904
按q键 转载于:https://www.cnblogs.com/niushuangmeng/p/10740933.html
apache linux启动失败,apache2 启动失败，出现下列错误，请问怎么解决，谢谢
weixin_42126749的博客
05-15 1250
该楼层疑似违规已被系统折叠隐藏此楼查看此楼apache2.service - The Apache HTTP ServerLoaded: loaded (/lib/systemd/system/apache2.service; enabled; vendor preset:Drop-In: /lib/systemd/system/apache2.service.d└─apache2-system...
R语言中的函数9：line()和lines()
辉的博客
11-14 4094
R语言中的line()和lines()函数介绍
常用的Linux 指令大全
The Last Song 的专栏
09-24 1376
cat cd chmod chown cp cut名称：cat 使用权限：所有使用者 使用方式：cat [-AbeEnstTuv] [--help] [--version] fileName 说明：把档案串连接后传到基本输出（萤幕或加 > fileName 到另一个档案） 参数： -n 或 --number 由 1 开始对所有输出的行数编号 -b 或 --number-nonblank 和 -n
docker 常用命令
01-07 825
镜像列表 $ sudo docker image ls REPOSITORY TAG IMAGE ID CREATED SIZE nginx/unit latest c3609c1acd08 11 days ago 236MB...
mysql 基础教程 pdf
最新发布
01-15
MySQL基础教程PDF是一本介绍MySQL数据库基础知识的电子书。MySQL是一个开源的关系型数据库管理系统，被广泛应用于Web应用程序和企业级软件开发中。这本教程通过详细讲解MySQL的基本概念、语法和常用操作，帮助读者...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值