mysql数据库

最新推荐文章于 2024-08-28 13:56:44 发布

得呢

最新推荐文章于 2024-08-28 13:56:44 发布

阅读量146

点赞数

本文链接：https://blog.csdn.net/hwjos/article/details/109162339

版权

msyql

关系型数据库的常见组件有：

数据库 database
表 table 行row 列（column）
索引 index
视图 view
用户 user
权限 privilege
存储过程 procedure
存储函数 function
触发器 trigger
事件调度器 event scheduler

sql语句

sql 语句有三种类型

ddl data defination language 数据定义语言
dml data manipulation language 数据操纵语言
dcl data control language 数据控制语言

SQL语句类型	对应操作
DDL	CREATE：创建
DML	INSERT：向表中插入数据DELETE：删除表中数据UPDATE：更新表中数据SELECT：查询表中数据
DCL	GRANT：授权REVOKE：移除授权

详细的数据类型如下

分类	类型名称	说明
整数类型	tinyInt	很小的整
整数类型	smallint	小的整数
整数类型	mediumint	中等大小的整数
整数类型	int(integer)	普通大小的整数
小数类型	float	但精度浮点数
小数类型	double	双精度浮点数
小数类型	decimal（m,d）	压缩严格的定点数
日期类型	year	YYYY 1901~2155
日期类型	time	HH:MM:SS -838:59:59~838:59:59
日期类型	date	YYYY-MM-DD 1000-01-01~9999-12-3
日期类型	datetime	YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
日期类型	timestamp	YYYY-MM-DD HH:MM:SS 1970₀₁01 00:00:01 UTC~2038-01-19 03:14:07UTC
文本、二进制类型	CHAR(M)	M为0~255之间的整数
文本、二进制类型	VARCHAR(M)	M为0~65535之间的整数
文本、二进制类型	TINYBLOB	允许长度0~255字节
文本、二进制类型	BLOB	允许长度0~65535字节
文本、二进制类型	MEDIUMBLOB	允许长度0~167772150字节
文本、二进制类型	LONGBLOB	允许长度0~4294967295字节
文本、二进制类型	TINYTEXT	允许长度0~255字节
文本、二进制类型	TEXT	允许长度0~65535字节
文本、二进制类型	MEDIUMTEXT	允许长度0~167772150字节
文本、二进制类型	LONGTEXT	允许长度0~4294967295字节
文本、二进制类型	VARBINARY(M)	允许长度0~M个字节的变长字节字符串
文本、二进制类型	BINARY(M)	允许长度0~M个字节的定长字节字符串

mariadb安装

//安装mariadb
[root@localhost ~]# yum -y install mariadb mariadb-common mariadb-devel mariadb-server

//启动  并设置开机自动启动
[root@localhost ~]# systemctl enable --now mariadb
[root@localhost ~]# systemctl  status mariadb



//确保3306端口已经监听起来
[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 ~]# mysql -uroot
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 ('abcd!123');
Query OK, 0 rows affected (0.000 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 [OPTIONS] [database]
//常用的OPTIONS：
    -u  USERNAME      //指定用户名，默认为root
    -h HOST          //指定服务器主机，默认为localhost，推荐使用ip地址
    -p PASSWORD      //指定用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
    -V              //查看当前使用的mysql版本
    -e          //不登录mysql执行sql语句后退出，常用于脚本**l 



[root@localhost ~]# mysql  -V
mysql  Ver 15.1 Distrib 10.3.17-MariaDB, for Linux (x86_64) using readline 5.1
[root@localhost ~]# 



[root@localhost ~]# mysql -uroot -p -h 127.0.0.1 -e 'SHOW DATABASES;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
[root@localhost ~]# 


[root@localhost ~]# mysql -uroot -p -P3306 -h127.0.0.1
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
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)]>

数据库操作

//创建数据库
//语法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';
//创建数据库HENHE
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS HENHE;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> 



//查看当前实例有哪些数据库
MariaDB [(none)]> DROP DATABASE IF EXISTS wangqingge;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> show databases ;
+--------------------+
| Database           |
+--------------------+
| HEHE               |
| HENHE              |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.000 sec)

MariaDB [(none)]> 


//删除数据库
//语法：DROP DATABASE [IF EXISTS] 'DB_NAME';
//删除数据库
MariaDB [(none)]> DROP DATABASE IF EXISTS HEHE;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> 

MariaDB [(none)]> DROP DATABASE IF EXISTS HEHE;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> show databases ;
+--------------------+
| Database           |
+--------------------+
| HENHE              |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]>

表操作

 //创建表
//语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';
//在数据库woshishi里创建表woshishi
MariaDB [(none)]>  use woshishi
Database changed
MariaDB [woshishi]> CREATE TABLE woshishi (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);
Query OK, 0 rows affected (0.011 sec)
MariaDB [woshishi]> show tables;
+--------------------+
| Tables_in_woshishi |
+--------------------+
| woshishi           |
+--------------------+
1 row in set (0.000 sec)

MariaDB [woshishi]> 



//删除表
//语法：DROP TABLE [ IF EXISTS ] 'table_name';
//删除表woshishi
MariaDB [woshishi]> DROP TABLE woshishi;
Query OK, 0 rows affected (0.008 sec)

MariaDB [woshishi]> 
MariaDB [woshishi]> show tables;
Empty set (0.000 sec)

MariaDB [woshishi]>

用户操作

mysql用户帐号由两部分组成，如’USERNAME’@‘HOST’，表示此USERNAME只能从此HOST上远程登录

这里（‘USERNAME’@‘HOST’）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：

IP地址，如：172.16.12.129
通配符
%：匹配任意长度的任意字符，常用于设置允许从任何主机登录
_：匹配任意单个字符

//数据库用户创建
//语法：CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
//创建数据库用户
MariaDB [woshishi]>  CREATE USER 'lishi'@'127.0.0.1' IDENTIFIED BY '1234';
Query OK, 0 rows affected (0.000 sec)

MariaDB [woshishi]> set password = password('1234');
Query OK, 0 rows affected (0.000 sec)

[root@localhost ~]# mysql -ulishi -p1234 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 40
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)]> 


//删除数据库用户
//语法：DROP USER 'username'@'host'; 
mysql> DROP USER 'wangqing'@'127.0.0.1';
MariaDB [(none)]> drop user 'lishi'@'127.0.0.1';
Query OK, 0 rows affected (0.000 sec)

查看命令show

//mysql> SHOW CHARACTER SET;      //查看支持的所有字符集
MariaDB [woshishi]> 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 |
.....
.....
mysql> SHOW ENGINES;        //查看当前数据库支持的所有存储引擎
MariaDB [woshishi]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| CSV                | YES     | Stores tables as CSV files                                                       | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)                   | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint          | NO           | NO   | NO         |
| ARCHIVE            | YES     | gzip-compresses tables for a low storage footprint                               | NO


MariaDB [woshishi]> SHOW DATABASES;    //查看数据库信息
+--------------------+
| Database           |
+--------------------+
| HENHE              |
| information_schema |
| mysql              |
| performance_schema |
| wangqingge         |
| woshishi           |
+--------------------+
6 rows in set (0.000 sec)

MariaDB [woshishi]> 

mysql> SHOW TABLES FROM wangqingge;         //不进入某数据库而列出其包含的所有表
MariaDB [(none)]> show tables from woshishi;
+--------------------+
| Tables_in_woshishi |
+--------------------+
| woshishi           |
+--------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> 



//查看表结构
//语法：DESC [db_name.]table_name;
mysql> DESC  
MariaDB [(none)]> desc abc.student;                     
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)


//查看某表的创建命令
//语法：SHOW CREATE TABLE table_name;
MariaDB [woshishi]> show create table woshishi;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                     |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| woshishi | CREATE TABLE `woshishi` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)




//查看某表的状态
//语法：SHOW TABLE STATUS LIKE 'table_name'\G
MariaDB [woshishi]> show engines\G;  
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MyISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV

获取帮助

//获取创建表的帮助
MariaDB [hehe]> create database hehn;
Query OK, 1 row affected (0.001 sec)
MariaDB [hehe]>  HELP CREATE TABLE
Nothing found
Please try to run 'help contents' for a list of all accessible topics

MariaDB [hehe]>

DML操作

DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。

INSERT语句

//DML操作之增操作insert
//语法：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),...
MariaDB [yt]> 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    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.002 sec)

MariaDB [yt]> INSERT INTO student VALUE(12, 'lili',20);  // 插入一行数据
Query OK, 1 row affected (0.003 sec)

MariaDB [yt]> INSERT INTO student VALUES(13,'lilti',20),(14,'lii',20),(15,'lilii',20); // 插入多行数据
Query OK, 3 rows affected (0.005 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [yt]>

update语句

//DML操作之改操作update
//语法：UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
MariaDB [yt]> select * from student;  //查看表
| 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 |
| 12 | lili        |   20 |
| 13 | lilti       |   20 |
| 14 | lii         |   20 |
| 15 | lilii       |   20 |
+----+-------------+------+
15 rows in set (0.001 sec)

MariaDB [yt]> UPDATE  student  SET age = 3 where name = 'yt';  
Query OK, 0 rows affected (0.001 sec)
Rows matched: 0  Changed: 0  Warnings: 0

MariaDB [yt]> select * from student;
+----+-------------+------+
| 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 |
| 12 | lili        |   20 |
| 13 | lilti       |   20 |
| 14 | lii         |   20 |
| 15 | lilii       |   20 |
+----+-------------+------+
15 rows in set (0.001 sec)

MariaDB [yt]>

truncate语句

语法：TRUNCATE table_name;

truncate与delete的区别：

语句类型	特点
delete	DELETE删除表内容时仅删除内容，但会保留表结构DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项可以通过回滚事志恢复数据非常占用空间
truncate	删除表中所有数据，且无法恢复表结构、约束和索引等保持变新添加的行计数值重置为初始值执行速度比DELETE快，且使用的系和事务日志资源少

//语法：TRUNCATE table_name;

delete语句

delete是删除表的内容，drop是删除表本身

//DML操作之删操作delete
//语法：DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name'[DESC]] [LIMIT [m,]n]


MariaDB [yt]> select * from student;   //查看表里的内容
+------+------+------+
| id   | name | age  |
+------+------+------+
| NULL | tom  |   12 |
| NULL | jiji |   23 |
| NULL | lili |   29 |
+------+------+------+
3 rows in set (0.000 sec)

MariaDB [yt]> delete from student where age  = 12;  // 删除age = 12 的
Query OK, 1 row affected (0.003 sec)
MariaDB [yt]> select * from student;
+------+------+------+
| id   | name | age  |
+------+------+------+
| NULL | jiji |   23 |
| NULL | lili |   29 |
+------+------+------+
2 rows in set (0.001 sec)


MariaDB [yt]> delete from student;  //删除表格里的所有内容
Query OK, 2 rows affected (0.003 sec)
MariaDB [yt]> select * from student;
Empty set (0.000 sec)

dcl操作

创建授权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];

//授权tom用户在192.168.11.145上远程登录数据库，
MariaDB [(none)]> grant all on hehn.student to tom@192.168.11.145 identified  by '123';  //设置权限
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> 

[root@localhost ~]# mysql -utom -p123 -h192.168.11.131
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
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)]> show databases; 能看到yt的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| yt                 |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> show tables from yt;      能看到该数据库下的student表
+--------------+
| Tables_in_yt |
+--------------+
| student      |
+--------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 

//重读授权表
MariaDB [(none)]> flush privileges;  //刷新权限
Query OK, 0 rows affected (0.000 sec)

查看授权

//查看当前登录用户的授权信息
MariaDB [(none)]> 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.000 sec)


//语法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';
MariaDB [(none)]> show grants for tom@192.168.11.145;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for tom@192.168.11.145                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'192.168.11.145' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT ALL PRIVILEGES ON `yt`.`student` TO 'tom'@'192.168.11.145'                                                |
| GRANT ALL PRIVILEGES ON `abc`.`student` TO 'tom'@'192.168.11.145'                                               |
| GRANT ALL PRIVILEGES ON `hehn`.`student` TO 'tom'@'192.168.11.145'                                              |
+-----------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)


//查看指定用户的授权信息
MariaDB [student]> show grwts for wangqing;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'grwts for wangqing' at line 1
MariaDB [student]>

取消授权REVOKE

//语法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';
MariaDB [(none)]> revoke select on abc.student from tom@192.168.50.135;        删除select权限
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> show grants for tom@192.168.50.135;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for tom@192.168.50.135                                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'192.168.50.135' IDENTIFIED BY PASSWORD '*31FBE3B6BBC9714F78109B4A42C5869DC227B39B'                                                    |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER, DELETE HISTORY ON `abc`.`student` TO 'tom'@'192.168.50.135' |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

刷新权限
MariaDB [(none)]> flush privileges;

注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：

GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表

mysql> FLUSH PRIVILEGES;

内连接、左连接以及右连接

内连接
组合两个表中的记录，返回关联字段相符的记录，也就是返回两张表中都包含的内容

//创建一个info的表
MariaDB [yy]> create table info(id int not null primary key auto_increment,name varchar(50) not null,department varchar(100),salary int);
Query OK, 0 rows affected (0.00 sec)

MariaDB [yy]> desc info;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| nmae       | varchar(50)  | NO   |     | NULL    |                |
| department | varchar(100) | YES  |     | NULL    |                |
| salary     | int(11)      | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

//插入数据
MariaDB [yy]> insert info(name,department,salary ) values('tom','sales',5000),('jerry','dev',20000),('zhangshan','dev',10000),('lisi','sales',30000),('wangwu','office',8000),('qianliu','financial',10000),('zhaoqi','financial',20000),('sunba','operation',25000),('zhoujiu','operation',9000),('wutian','dev',15000);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0
查看名字，部门，最高工资，再将这一结果组成个新组（group by的用法）
//最高工资
MariaDB [yy]> select name,department,max(salary) as salary from info group by department;
+---------+------------+--------+
| name    | department | salary |
+---------+------------+--------+
| jerry   | dev        |  20000 |
| qianliu | financial  |  20000 |
| wangwu  | office     |   8000 |
| sunba   | operation  |  25000 |
| tom     | sales      |  30000 |
+---------+------------+--------+
5 rows in set (0.00 sec)
//最小工资
MariaDB [yy]> select name,department,min(salary) as salary from info group by department;
+---------+------------+--------+
| name    | department | salary |
+---------+------------+--------+
| jerry   | dev        |  10000 |
| qianliu | financial  |  10000 |
| wangwu  | office     |   8000 |
| sunba   | operation  |   9000 |
| tom     | sales      |   5000 |
+---------+------------+--------+
5 rows in set (0.00 sec)

MariaDB [yy]> alter table info add jn int not null;
Stage: 2 of 2 'Enabling keys'      0% of stage don                                                  Query OK, 30 rows affected (0.01 sec)
Records: 30  Duplicates: 0  Warnings: 0


MariaDB [yy]> desc info;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(50)  | NO   |     | NULL    |                |
| department | varchar(100) | YES  |     | NULL    |                |
| salary     | int(11)      | YES  |     | NULL    |                |
| jn         | int(11)      | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

MariaDB [yy]> select * from info;
+----+-----------+------------+--------+----+
| id | name      | department | salary | jn |
+----+-----------+------------+--------+----+
|  1 | tom       | sales      |   5000 |  0 |
|  2 | jerry     | dev        |  20000 |  0 |
|  3 | zhangshan | dev        |  10000 |  0 |
|  4 | lisi      | sales      |  30000 |  0 |
|  5 | wangwu    | office     |   8000 |  0 |
|  6 | qianliu   | financial  |  10000 |  0 |
|  7 | zhaoqi    | financial  |  20000 |  0 |
|  8 | sunba     | operation  |  25000 |  0 |
|  9 | zhoujiu   | operation  |   9000 |  0 |


MariaDB [yy]> update info set jn = 1 where id = 1;Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [yy]> select * from info;
+----+-----------+------------+--------+----+
| id | name      | department | salary | jn |
+----+-----------+------------+--------+----+
|  1 | tom       | sales      |   5000 |  1 |
|  2 | jerry     | dev        |  20000 |  0 |
|  3 | zhangshan | dev        |  10000 |  0 |
|  4 | lisi      | sales      |  30000 |  0 |
|  5 | wangwu    | office     |   8000 |  0 |
|  6 | qianliu   | financial  |  10000 |  0 |
|  7 | zhaoqi    | financial  |  20000 |  0 |
|  8 | sunba     | operation  |  25000 |  0 |
|  9 | zhoujiu   | operation  |   9000 |  0 |
| 10 | wutian    | dev        |  15000 |  0 |

MariaDB [yy]> create table basic_info(job_number int not null,age int not null);
Query OK, 0 rows affected (0.00 sec)
MariaDB [yy]> desc basic_info;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| job_number | int(11) | NO   |     | NULL    |       |
| age        | int(11) | NO   |     | NULL    |       |
+------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [yy]> insert basic_info values(1,21),(3,25),(5,30),(7,26),(9,28),(10,30),(2,20);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0


MariaDB [yy]> select * from basic_info;
+------------+-----+
| job_number | age |
+------------+-----+
|          1 |  21 |
|          3 |  25 |
|          5 |  30 |
|          7 |  26 |
|          9 |  28 |
|         10 |  30 |
|          2 |  20 |
+------------+-----+
7 rows in set (0.00 sec)


MariaDB [yy]> select * from info;
+----+-----------+------------+--------+----+
| id | name      | department | salary | jn |
+----+-----------+------------+--------+----+
|  1 | tom       | sales      |   5000 |  1 |
|  2 | jerry     | dev        |  20000 |  2 |
|  3 | zhangshan | dev        |  10000 |  3 |
|  4 | lisi      | sales      |  30000 |  4 |
|  5 | wangwu    | office     |   8000 |  5 |
|  6 | qianliu   | financial  |  10000 |  6 |
|  7 | zhaoqi    | financial  |  20000 |  7 |
|  8 | sunba     | operation  |  25000 |  3 |
|  9 | zhoujiu   | operation  |   9000 |  9 |
| 10 | wutian    | dev        |  15000 | 10 |

MariaDB [yy]> select * from info as a inner join basic_info as b where a.jn = b.job_number;
+----+-----------+------------+--------+----+------------+-----+
| id | name      | department | salary | jn | job_number | age |
+----+-----------+------------+--------+----+------------+-----+
|  1 | tom       | sales      |   5000 |  1 |          1 |  21 |
|  2 | jerry     | dev        |  20000 |  2 |          2 |  20 |
|  3 | zhangshan | dev        |  10000 |  3 |          3 |  25 |
|  5 | wangwu    | office     |   8000 |  5 |          5 |  30 |
|  7 | zhaoqi    | financial  |  20000 |  7 |          7 |  26 |
|  8 | sunba     | operation  |  25000 |  3 |          3 |  25 |
|  9 | zhoujiu   | operation  |   9000 |  9 |          9 |  28 |
| 10 | wutian    | dev        |  15000 | 10 |         10 |  30 |
+----+-----------+------------+--------+----+------------+-----+
8 rows in set (0.00 sec)

左连接

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

MariaDB [yy]> select * from info as a left  join basic_info as b on  a.jn = b.job_number;
+----+-----------+------------+--------+----+------------+------+
| id | name      | department | salary | jn | job_number | age  |
+----+-----------+------------+--------+----+------------+------+
|  1 | tom       | sales      |   5000 |  1 |          1 |   21 |
|  3 | zhangshan | dev        |  10000 |  3 |          3 |   25 |
|  8 | sunba     | operation  |  25000 |  3 |          3 |   25 |
|  5 | wangwu    | office     |   8000 |  5 |          5 |   30 |
|  7 | zhaoqi    | financial  |  20000 |  7 |          7 |   26 |
|  9 | zhoujiu   | operation  |   9000 |  9 |          9 |   28 |
| 10 | wutian    | dev        |  15000 | 10 |         10 |   30 |
|  2 | jerry     | dev        |  20000 |  2 |          2 |   20 |
|  4 | lisi      | sales      |  30000 |  4 |       NULL | NULL |
|  6 | qianliu   | financial  |  10000 |  6 |       NULL | NULL |

右连接

right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。


MariaDB [yy]> select * from info as a right  join basic_info as b on  a.jn = b.job_number;
+------+-----------+------------+--------+------+------------+-----+
| id   | name      | department | salary | jn   | job_number | age |
+------+-----------+------------+--------+------+------------+-----+
|    1 | tom       | sales      |   5000 |    1 |          1 |  21 |
|    2 | jerry     | dev        |  20000 |    2 |          2 |  20 |
|    3 | zhangshan | dev        |  10000 |    3 |          3 |  25 |
|    5 | wangwu    | office     |   8000 |    5 |          5 |  30 |
|    7 | zhaoqi    | financial  |  20000 |    7 |          7 |  26 |
|    8 | sunba     | operation  |  25000 |    3 |          3 |  25 |
|    9 | zhoujiu   | operation  |   9000 |    9 |          9 |  28 |
|   10 | wutian    | dev        |  15000 |   10 |         10 |  30 |
+------+-----------+------------+--------+------+------------+-----+
8 rows in set (0.00 sec)



MariaDB [yy]> insert basic_info value (11,35);
Query OK, 1 row affected (0.00 sec)


MariaDB [yy]> select * from  basic_info;
+------------+-----+
| job_number | age |
+------------+-----+
|          1 |  21 |
|          3 |  25 |
|          5 |  30 |
|          7 |  26 |
|          9 |  28 |
|         10 |  30 |
|          2 |  20 |
|         11 |  35 |

MariaDB [yy]> select * from info as a right  join basic_info as b on  a.jn = b.job_number;
+------+-----------+------------+--------+------+------------+-----+
| id   | name      | department | salary | jn   | job_number | age |
+------+-----------+------------+--------+------+------------+-----+
|    1 | tom       | sales      |   5000 |    1 |          1 |  21 |
|    2 | jerry     | dev        |  20000 |    2 |          2 |  20 |
|    3 | zhangshan | dev        |  10000 |    3 |          3 |  25 |
|    5 | wangwu    | office     |   8000 |    5 |          5 |  30 |
|    7 | zhaoqi    | financial  |  20000 |    7 |          7 |  26 |
|    8 | sunba     | operation  |  25000 |    3 |          3 |  25 |
|    9 | zhoujiu   | operation  |   9000 |    9 |          9 |  28 |
|   10 | wutian    | dev        |  15000 |   10 |         10 |  30 |
| NULL | NULL      | NULL       |   NULL | NULL |         11 |  35 |

1.搭建mysql服务

yum -y install mysql

2.创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，age），表结构如下：

MariaDB [hehe]> create database hehn;
Query OK, 1 row affected (0.001 sec)

MariaDB [hehe]> 

MariaDB [(none)]> use hehe
Database changed
MariaDB [hehe]> 
MariaDB [hehn]> create table student(id int null,name varchar(50),age tinyint);
MariaDB [hehn]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

MariaDB [hehn]>

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

mysql> select * from student;

4.往新建的student表中插入数据（用insert语句），结果应如下所示：

mysql>insert into student (id,name,age) VALUES (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); 
Query OK, 11 rows affected (0.04 sec)
Records: 11  Duplicates: 0  Warnings: 0
+----+-------------+------+
| 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 |
+----+-------------+------+
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),(8,'chenshuo',10),(9,'wangwu',3),(10,'qiuyi',15),(11,'qiuxiaotian',20);

5.修改lisi的年龄为50

mysql> update student set age = 50 where name = 'lisi';
Query OK, 1 row affected (0.80 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 | zhangshan   |   20 |
|    7 | lisi        |   50 |
|    8 | chenshuo    |   10 |
|    9 | wangwu      |    3 |
|   10 | qiuyi       |   15 |
|   11 | qiuxiaotian |   20 |
+------+-------------+------+
11 rows in set (0.00 sec)

6.以age字段降序排序

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

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

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

mysql> select * from student where name = 'zhangshan';

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

MariaDB [yt]> select * from student where name='zhangshan'and age >20;

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

MariaDB [yt]>SELECT * FROM student WHERE age BETWEEN 23 and 30;

12.修改wangwu的年龄为100

MariaDB [yt]> update student set age =100 where name ='wangwu';

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

MariaDB [yt]> delete from student where name = 'zhangshan' and age <= 20;

得呢

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫