Mysql基础

Mysql安装

mysql安装方式有三种：

源代码：编译安装
二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
程序包管理器管理的程序包：
- rpm：有两种
  - OS Vendor：操作系统发行商提供的
  - 项目官方提供的
- deb

[root@localhost ~]# yum -y install mariadb*

mysql配置

//启动mysql并设置开机自动启动
[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.

//确保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                     0.0.0.0:3306                0.0.0.0:*     
LISTEN      0           128                       [::]:22                     [::]:*  


[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)]> QUIT
Bye

[root@localhost ~]# mysql
mysql                       mysqld_safe                 mysql_secure_installation
mysqlaccess                 mysqld_safe_helper          mysql_setpermission
mysqladmin                  mysqldump                   mysqlshow
mysqlbinlog                 mysqldumpslow               mysqlslap
mysqlcheck                  mysql_find_rows             mysqltest
mysql_client_test           mysql_fix_extensions        mysqltest_embedded
mysql_client_test_embedded  mysqlhotcopy                mysql_tzinfo_to_sql
mysql_config                mysqlimport                 mysql_upgrade
mysql_convert_table_format  mysql_install_db            mysql_waitpid
mysqld_multi                mysql_plugin                

//安全初始化
[root@localhost ~]# mysql_secure_installation 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] Y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB 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? [Y/n] 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? [Y/n] n
 ... skipping.

By default, MariaDB 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? [Y/n] 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? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

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：移除授权

DDL操作

//查看数据库信息
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

//创建数据库
MariaDB [(none)]> CREATE DATABASE yuqinghao;
Query OK, 1 row affected (0.001 sec)

//进入yuqinghao数据库
MariaDB [(none)]> use yuqinghao;
Database changed

//创建表
MariaDB [yuqinghao]> CREATE TABLE yuqinghao (id int NOT NULL,name VARCHAR(50) NOT NULL,age tinyint(4));
Query OK, 0 rows affected (0.008 sec)

//查看当前数据库有哪些表
MariaDB [yuqinghao]> SHOW TABLES;
+---------------------+
| Tables_in_yuqinghao |
+---------------------+
| yuqinghao           |
+---------------------+
1 row in set (0.001 sec)

//查看表结构
MariaDB [yuqinghao]> DESC yuqinghao;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.003 sec)

//添加表数据
MariaDB [yuqinghao]> ALTER TABLE yuqinghao ADD class varchar(20);
Query OK, 0 rows affected (0.004 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [yuqinghao]> DESC yuqinghao;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
| class | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.002 sec)

//删除表数据
MariaDB [yuqinghao]> ALTER TABLE yuqinghao DROP class;
Query OK, 0 rows affected (0.010 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [yuqinghao]> DESC yuqinghao;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

//删除表
MariaDB [yuqinghao]> DROP TABLE yuqinghao;
Query OK, 0 rows affected (0.004 sec)

MariaDB [yuqinghao]> SHOW TABLES;
Empty set (0.000 sec)

//删除数据库
MariaDB [yuqinghao]> DROP DATABASE yuqinghao;
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)

获取帮助

//获取创建表的帮助
MariaDB [(none)]> HELP CREATE TABLE;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]
······

DCL操作

权限类型(priv_type)

权限类型	代表什么？
ALL	所有权限
SELECT	读取内容的权限
INSERT	插入内容的权限
UPDATE	更新内容的权限
DELETE	删除内容的权限

创建授权grant

//授权root用户在数据库本机上登录访问所有数据库
MariaDB [(none)]> GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> GRANT ALL ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.000 sec)

//授权root用户在192.168.237.1上远程登录访问root数据库
MariaDB [(none)]> GRANT ALL ON *.* TO 'root'@'192.168.237.1' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.000 sec)

//授权root用户在所有位置上远程登录访问root数据库
MariaDB [(none)]> GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.000 sec)

//刷新权限表
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)

查看授权

//查看当前登录用户的授权信息
MariaDB [(none)]> SHOW GRANTS;

//查看指定用户root的授权信息
MariaDB [(none)]> SHOW GRANTS FOR 'root'@'192.168.237.1';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for root@192.168.237.1                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT ALL ON *.* TO 'root'@'192.168.237.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

取消授权REVOKE

//取消指定用户root的授权信息
MariaDB [(none)]> REVOKE ALL ON *.* from 'root'@'192.168.237.1';
Query OK, 0 rows affected (0.001 sec)

//刷新权限表
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'root'@'127.0.0.1';
+---------------------------------------------------------------------+
| Grants for root@127.0.0.1                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'root'@'192.168.237.1';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for root@192.168.237.1                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'192.168.237.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
//USAGE表示只有连接的权限

DML操作

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

INSERT语句

MariaDB [yuqinghao]> CREATE TABLE student(id int not null,name varchar(50),age tinyint);
Query OK, 0 rows affected (0.007 sec)

//插入一条数据到student表里
MariaDB [yuqinghao]> INSERT INTO student VALUES(1,'tom',19);
Query OK, 1 row affected (0.002 sec)

MariaDB [yuqinghao]> SELECT * FROM student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   19 |
+----+------+------+
1 row in set (0.000 sec)

//插入多条数据到student表里
MariaDB [yuqinghao]> INSERT INTO student VALUES(2,'sam',18),(3,'eric',20),(4,'mary',18);
Query OK, 3 rows affected (0.002 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [yuqinghao]> SELECT * FROM student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   19 |
|  2 | sam  |   18 |
|  3 | eric |   20 |
|  4 | mary |   18 |
+----+------+------+
4 rows in set (0.000 sec)

主键设置自动增长

//创建teacher表，主键设置为自动增长
MariaDB [yuqinghao]> CREATE TABLE teacher(id int NOT NULL PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20) NOT NULL,age TINYINT,salary FLOAT);
Query OK, 0 rows affected (0.007 sec)

MariaDB [yuqinghao]> DESC teacher;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | NO   |     | NULL    |                |
| age    | tinyint(4)  | YES  |     | NULL    |                |
| salary | float       | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

//插入数据到teacher表，且给id数据
MariaDB [yuqinghao]> INSERT INTO teacher(name,age,salary) VALUES('tom',19,2000),('sam',20,1900),('jim',22,3000);
Query OK, 3 rows affected (0.003 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [yuqinghao]> SELECT * FROM teacher;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | tom  |   19 |   2000 |
|  2 | sam  |   20 |   1900 |
|  3 | jim  |   22 |   3000 |
+----+------+------+--------+
3 rows in set (0.001 sec)

update语句

问：如何把id为3的那条数据的age改为18？

MariaDB [yuqinghao]> SELECT * FROM teacher;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | tom  |   19 |   2000 |
|  2 | sam  |   20 |   1900 |
|  3 | jim  |   22 |   3000 |
+----+------+------+--------+
3 rows in set (0.000 sec)

MariaDB [yuqinghao]> UPDATE teacher SET age = 18 WHERE id = 3;
Query OK, 1 row affected (0.004 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [yuqinghao]> SELECT * FROM teacher;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | tom  |   19 |   2000 |
|  2 | sam  |   20 |   1900 |
|  3 | jim  |   18 |   3000 |
+----+------+------+--------+
3 rows in set (0.000 sec)

SELECT语句

字段column表示法

表示符	代表什么？
*	所有字段
as	字段别名，当表名很长时用别名代替

条件判断语句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个结果

MariaDB [yuqinghao]> SELECT * FROM teacher;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | tom  |   19 |   2000 |
|  2 | sam  |   20 |   1900 |
|  3 | jim  |   18 |   3000 |
+----+------+------+--------+
3 rows in set (0.000 sec)

//给name和salary起个别名
MariaDB [yuqinghao]> SELECT name AS '姓名',salary AS '薪资' FROM teacher;
+--------+--------+
| 姓名   | 薪资   |
+--------+--------+
| tom    |   2000 |
| sam    |   1900 |
| jim    |   3000 |
+--------+--------+
3 rows in set (0.000 sec)

//查找薪资为3000人的名字
MariaDB [yuqinghao]> SELECT name FROM teacher WHERE salary = 3000;
+------+
| name |
+------+
| jim  |
+------+
1 row in set (0.001 sec)

//查找薪资大于等于2000的工号
MariaDB [yuqinghao]> SELECT id FROM teacher WHERE salary >= 2000;
+----+
| id |
+----+
|  1 |
|  3 |
+----+
2 rows in set (0.001 sec)

//查找年龄在20到30之间的名字
MariaDB [yuqinghao]> SELECT name FROM teacher WHERE age BETWEEN 20 and 30;

MariaDB [yuqinghao]> SELECT name FROM teacher WHERE age >= 20 and age <= 30;
+------+
| name |
+------+
| sam  |
+------+
1 row in set (0.000 sec)

//查找名字为t开头的信息
MariaDB [yuqinghao]> SELECT * FROM teacher WHERE name LIKE 't%';
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | tom  |   19 |   2000 |
+----+------+------+--------+
1 row in set (0.001 sec)

//查找名字为m结尾的信息
MariaDB [yuqinghao]> SELECT * FROM teacher WHERE name LIKE '%m';
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | tom  |   19 |   2000 |
|  2 | sam  |   20 |   1900 |
|  3 | jim  |   18 |   3000 |
+----+------+------+--------+
3 rows in set (0.000 sec)

//查找名字为s开头或者年龄为18的信息
MariaDB [yuqinghao]> SELECT * FROM teacher WHERE name LIKE 's%' or age = 18;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  2 | sam  |   20 |   1900 |
|  3 | jim  |   18 |   3000 |
+----+------+------+--------+
2 rows in set (0.000 sec)

注意：NULL不等于空格“ ”

MariaDB [yuqinghao]> INSERT INTO teacher(name,age,salary) VALUES('eric',23,NULL),("",25,3500);
Query OK, 2 row affected (0.004 sec)

MariaDB [yuqinghao]> SELECT * FROM teacher;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | tom  |   19 |   2000 |
|  2 | sam  |   20 |   1900 |
|  3 | jim  |   18 |   3000 |
|  4 | eric |   23 |   NULL |
|  5 |      |   25 |   3500 |
+----+------+------+--------+
5 rows in set (0.000 sec)

排序查询

//按年龄从小到大排序（升序）
MariaDB [yuqinghao]> SELECT * FROM teacher ORDER BY age;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  3 | jim  |   18 |   3000 |
|  1 | tom  |   19 |   2000 |
|  2 | sam  |   20 |   1900 |
|  4 | eric |   23 |   NULL |
|  5 |      |   25 |   3500 |
+----+------+------+--------+
5 rows in set (0.000 sec)

//按薪资从大到排序（降序）
MariaDB [yuqinghao]> SELECT * FROM teacher ORDER BY salary DESC;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  5 |      |   25 |   3500 |
|  3 | jim  |   18 |   3000 |
|  1 | tom  |   19 |   2000 |
|  2 | sam  |   20 |   1900 |
|  4 | eric |   23 |   NULL |
+----+------+------+--------+
5 rows in set (0.000 sec)

//按薪资从大到排序，且只取前两个
MariaDB [yuqinghao]> SELECT * FROM teacher ORDER BY salary DESC LIMIT 2;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  5 |      |   25 |   3500 |
|  3 | jim  |   18 |   3000 |
+----+------+------+--------+
2 rows in set (0.000 sec)

//按薪资从大到小排序，且去掉前两条，再取前两条
MariaDB [yuqinghao]> SELECT * FROM teacher ORDER BY salary DESC LIMIT 2,2;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | tom  |   19 |   2000 |
|  2 | sam  |   20 |   1900 |
+----+------+------+--------+
2 rows in set (0.000 sec)

delete语句和truncate语句

truncate与delete的区别：

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

语句类型

特点

delete

DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项可以通过回滚事务日志恢复数据非常占用空间

truncate

删除表中所有数据，且无法恢复表结构、约束和索引等保持不变，新添加的行计数值重置为初始值执行速度比DELETE快，且使用的系统和事务日志资源少通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据不能用于加入了索引视图的表

delete

MariaDB [yuqinghao]> SELECT * FROM teacher;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | tom  |   19 |   2000 |
|  2 | sam  |   20 |   1900 |
|  3 | jim  |   18 |   3000 |
|  4 | eric |   23 |   NULL |
|  5 |      |   25 |   3500 |
+----+------+------+--------+
5 rows in set (0.000 sec)

//删除薪资为空的数据
MariaDB [yuqinghao]> DELETE FROM teacher WHERE salary IS NULL;
Query OK, 1 row affected (0.005 sec)

MariaDB [yuqinghao]> SELECT * FROM teacher;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | tom  |   19 |   2000 |
|  2 | sam  |   20 |   1900 |
|  3 | jim  |   18 |   3000 |
|  5 |      |   25 |   3500 |
+----+------+------+--------+
4 rows in set (0.001 sec)

//删除名字为空格的数据
MariaDB [yuqinghao]> DELETE FROM teacher WHERE name = "";
Query OK, 1 row affected (0.002 sec)

MariaDB [yuqinghao]> SELECT * FROM teacher;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | tom  |   19 |   2000 |
|  2 | sam  |   20 |   1900 |
|  3 | jim  |   18 |   3000 |
+----+------+------+--------+
3 rows in set (0.001 sec)

//清空teacher表的数据，保留结构
MariaDB [yuqinghao]> DELETE FROM teacher;
Query OK, 3 rows affected (0.003 sec)

MariaDB [yuqinghao]> SELECT * FROM teacher;
Empty set (0.000 sec)

MariaDB [yuqinghao]> SHOW TABLES;
+---------------------+
| Tables_in_yuqinghao |
+---------------------+
| student             |
| teacher             |
+---------------------+
2 rows in set (0.000 sec)

//插入数据，发现工号还在自动增长，并没有从1开始
MariaDB [yuqinghao]> INSERT INTO teacher(name,age,salary) VALUES('tom',19,2000),('sam',20,1900),('jim',22,3000);
Query OK, 3 rows affected (0.005 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [yuqinghao]> SELECT * FROM teacher;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  6 | tom  |   19 |   2000 |
|  7 | sam  |   20 |   1900 |
|  8 | jim  |   22 |   3000 |
+----+------+------+--------+
3 rows in set (0.000 sec)

truncate

//清空teacher表的数据，不保留结构
MariaDB [yuqinghao]> TRUNCATE teacher;
Query OK, 0 rows affected (0.011 sec)

MariaDB [yuqinghao]> SELECT * FROM teacher;
Empty set (0.000 sec)

//重新插入数据，发现工号重置了
MariaDB [yuqinghao]> INSERT INTO teacher(name,age,salary) VALUES('tom',19,2000),('sam',20,1900),('jim',22,3000);
Query OK, 3 rows affected (0.004 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [yuqinghao]> SELECT * FROM teacher;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | tom  |   19 |   2000 |
|  2 | sam  |   20 |   1900 |
|  3 | jim  |   22 |   3000 |
+----+------+------+--------+
3 rows in set (0.001 sec)

练习题

1.搭建mysql服务

[root@localhost ~]# yum -y install mariadb*
[root@localhost ~]# systemctl enable --now mariadb

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    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

MariaDB [(none)]> create database yuqinghao;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> use yuqinghao;
Database changed

MariaDB [yuqinghao]> create table student(id int(11) not null primary key auto_increment,name varchar(100) not null,age tinyint(4));
Query OK, 0 rows affected (0.112 sec)

MariaDB [yuqinghao]> 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.001 sec)

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

MariaDB [yuqinghao]> select * from student;
Empty set (0.000 sec)

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 |
+----+-------------+------+

MariaDB [yuqinghao]> insert into student(name,age) values('tom',20),('jerry',23),('wangqing',25),('sean',28),('zhangshan',26),('zhangshan',20),('lisi',null),('chengshuo',10),('wangwu',3),('qiuyi',15),('qiuxiaotian',20);
Query OK, 11 rows affected (0.001 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [yuqinghao]> 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 | chengshuo   |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.000 sec)

5.修改lisi的年龄为50

MariaDB [yuqinghao]> update student set age = 50 where name = 'lisi';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [yuqinghao]> 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 | chengshuo   |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.000 sec)

6.以age字段降序排序

MariaDB [yuqinghao]> 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 | zhangshan   |   20 |
| 11 | qiuxiaotian |   20 |
| 10 | qiuyi       |   15 |
|  8 | chengshuo   |   10 |
|  9 | wangwu      |    3 |
+----+-------------+------+
11 rows in set (0.000 sec)

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

MariaDB [yuqinghao]> 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.000 sec)

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

MariaDB [yuqinghao]> 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.000 sec)

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

MariaDB [yuqinghao]> select * from student where name = 'zhangshan';
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
+----+-----------+------+
2 rows in set (0.000 sec)

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

MariaDB [yuqinghao]> select * from student where name = 'zhangshan' and age > 20;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
+----+-----------+------+
1 row in set (0.000 sec)

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

MariaDB [yuqinghao]> 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.000 sec)

12.修改wangwu的年龄为100

MariaDB [yuqinghao]> update student set age = 100 where name = 'wangwu';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [yuqinghao]> 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 | chengshuo   |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.000 sec)

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

ariaDB [yuqinghao]> delete from student where name = 'zhangshan' and age <= 20;
Query OK, 1 row affected (0.001 sec)

MariaDB [yuqinghao]> 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 | chengshuo   |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
10 rows in set (0.000 sec)

相关阅读:
python 数据结构--Set(集合)
python 数据结构--Dictionary(字典)
idea使用心得
 SpringBoot（一）：构建第一个SpringBoot工程
 springboot服务的一些问题
 maven使用问题总结
 锁
 多线程学习三：Thread API，ThreadLocal，synchronized，volatile和Condition
多线程学习二：线程池 ExecutorService
多线程学习一：创建多线程的方式
原文地址：https://www.cnblogs.com/yuqinghao/p/14187799.html