mysql基础命令
常见的关系型数据库管理系统：
- MySQL：MySQL，MariaDB，Percona-Server
- PostgreSQL：简称为pgsql
- Oracle
- MSSQL
SQL：Structure Query Language，结构化查询语言
约束：constraint，向数据表提供的数据要遵守的限制
- 主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL）。
- 一个表只能存在一个
- 惟一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（NULL）
- 一个表可以存在多个
- 外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据
- 检查性约束
索引：将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储
关系型数据库的常见组件有：
- 数据库：database
- 表：table，由行（row）和列（column）组成
- 索引：index
- 视图：view
- 用户：user
- 权限：privilege
- 存储过程：procedure
- 存储函数：function
- 触发器：trigger
- 事件调度器：event scheduler
SQL语句类型：
| SQL语句类型 | 对应操作 |
|---|---|
| DDL | CREATE：创建 DROP：删除 ALTER：修改 |
| DML | INSERT：向表中插入数据 DELETE：删除表中数据 UPDATE：更新表中数据 SELECT：查询表中数据 |
| DCL | GRANT：授权 REVOKE：移除授权 |
mysql安装与配置：
1.安装mariadb的所有服务，设置开机自动启动并查看端口号。
[root@win10 private]# mount /dev/cdrom /mnt/ mount: /mnt: WARNING: device write-protected, mounted read-only. [root@win10 ~]# yum -y install mariadb* [root@win10 ~]# 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@win10 ~]# 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 *:80 *:* LISTEN 0 128 [::]:22 [::]:* LISTEN 0 128 *:443 *:* //语法：mysql [OPTIONS] [database] //常用的OPTIONS： -uUSERNAME //指定用户名，默认为root -hHOST //指定服务器主机，默认为localhost，推荐使用ip地址 -pPASSWORD //指定用户的密码 -P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307 -V //查看当前使用的mysql版本 -e //不登录mysql执行sql语句后退出，常用于脚本
2.给数据库的root管理员账户设置密码123，再修改成1234
[root@win10 ~]# mysql -uroot Enter password: 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)]> set password = password('123'); #后面的password是加密 Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> [root@win10 ~]# mysql -uroot -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [root@win10 ~]# 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)]> [root@win10 ~]# mysql -uroot -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 11 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)]> alter user root@localhost identified by '1234'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> quit Bye
3.安全初始化数据库，更改root密码为12345
[root@win10 ~]# 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!
4.用两种方法查看数据库
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.000 sec) MariaDB [(none)]> quit Bye [root@win10 ~]# mysql -uroot -p12345 -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+
（DDL）
5.创建数据库school之后再删除
MariaDB [(none)]> create database if not exists school; #(if not exists)可以不 加 Query OK, 1 row affected (0.000 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | +--------------------+ 4 rows in set (0.000 sec) MariaDB [(none)]> drop database if exists school; #(if exists)可以不加 Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.000 sec)
6.创建数据库school，在其里面创建表student，表里面有id，name，age，使用desc命令查看表 student数据。
MariaDB [(none)]> create database school; Query OK, 1 row affected (0.000 sec) MariaDB [(none)]> use school; Database changed MariaDB [school]> show tables; Empty set (0.000 sec) MariaDB [school]> create table student(id int not null,name varchar(50) null,age tinyint); Query OK, 0 rows affected (0.004 sec) MariaDB [school]> show tables; +------------------+ | Tables_in_school | +------------------+ | student | +------------------+ 1 row in set (0.000 sec) MariaDB [school]> desc 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)
7.修改表student，插入班级class，之后再删除age一行，最后删除表student。
MariaDB [school]> alter table student add class varchar(20); Query OK, 0 rows affected (0.001 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [school]> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(50) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | | class | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.001 sec) MariaDB [school]> alter table student drop age; Query OK, 0 rows affected (0.007 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [school]> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(50) | YES | | NULL | | | class | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.001 sec) MariaDB [school]> drop table student; Query OK, 0 rows affected (0.003 sec) MariaDB [school]> show tables; 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：被授权的用户可将自己的权限副本转赠给其他用户，说白点就是将自己的权限完全复制给另一个用户。不建议使用。
8.授权192.168.44.1（自己的主机）与192.168.44.129登录数据库的所有数据库与所有表，权限为所有权限，用户为root，密码为12345
MariaDB [(none)]> grant all on *.* to 'root'@'192.168.44.1' identified by '12345'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> grant all on *.* to 'root'@'192.168.44.129' identified by '12345'; Query OK, 0 rows affected (0.000 sec)
9.查看root用户在的192.168.44.1的权限，并取消root用户的192.168.44.129的授权
MariaDB [(none)]> show grants for 'root'@'192.168.44.1'; +-------------------------------------------------------------------------------------------------------------------------+ | Grants for root@192.168.44.1 | +-------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.44.1' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' | +-------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> revoke delete on *.* from 'root'@'192.168.44.129'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> show grants for 'root'@'192.168.44.129'; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@192.168.44.129 | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DELETE HISTORY ON *.* TO 'root'@'192.168.44.129' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec)
（DML）
10.在school数据库中创建表student，在其中先单次插入一条数据，再连续插入五条数据。表student包含id,name,age。
MariaDB [(none)]> use school; Database changed MariaDB [school]> create table student(id int not null,name varchar(50),age tinyint); Query OK, 0 rows affected (1.671 sec) MariaDB [school]> show tables; +------------------+ | Tables_in_school | +------------------+ | student | +------------------+ 1 row in set (0.000 sec) MariaDB [school]> desc 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.194 sec) MariaDB [school]> insert into student values(1,'tom',15); Query OK, 1 row affected (0.001 sec) MariaDB [school]> select * from student; +----+------+------+ | id | name | age | +----+------+------+ | 1 | tom | 15 | +----+------+------+ 1 row in set (0.000 sec) MariaDB [school]> insert into student values(2,'zhangshan',20),(3,'lisi',18),(4,'wangwu',20),(5,'zhaosan',13),(6,'qianliu',14); Query OK, 5 rows affected (0.001 sec) Records: 5 Duplicates: 0 Warnings: 0 MariaDB [school]> select * from student; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 1 | tom | 15 | | 2 | zhangshan | 20 | | 3 | lisi | 18 | | 4 | wangwu | 20 | | 5 | zhaosan | 13 | | 6 | qianliu | 14 | +----+-----------+------+ 6 rows in set (0.000 sec)
11.创建表teacher，设置id为主键且自动增长。（只有主键才能设置自动增长）
MariaDB [school]> 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.034 sec) MariaDB [school]> 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)
12.往表teacher里插入三条数据，再往表teacher里连续插入多条数据。（需指定插入数据的字段）
MariaDB [school]> insert into teacher values(1,'chengsongling',21,4000); Query OK, 1 row affected (0.001 sec) MariaDB [school]> insert into teacher values(2,'wangming',25,5000.50); Query OK, 1 row affected (0.001 sec) MariaDB [school]> insert into teacher values(3,'taochi',25,8000); Query OK, 1 row affected (0.001 sec) MariaDB [school]> select * from teacher; +----+---------------+------+--------+ | id | name | age | salary | +----+---------------+------+--------+ | 1 | chengsongling | 21 | 4000 | | 2 | wangming | 25 | 5000.5 | | 3 | taochi | 25 | 8000 | +----+---------------+------+--------+ 3 rows in set (0.000 sec) MariaDB [school]> insert into teacher(name,age,salary) values('meijianbiao',30,9000),('mufeng',23,5000),('fangxinxin',24,10000),('leichen',21,10000),('yuqinhao',50,100000),('tanghaolun',10,10000); Query OK, 6 rows affected (0.001 sec) Records: 6 Duplicates: 0 Warnings: 0 MariaDB [school]> select * from teacher; +----+---------------+------+--------+ | id | name | age | salary | +----+---------------+------+--------+ | 1 | chengsongling | 21 | 4000 | | 2 | wangming | 25 | 5000.5 | | 3 | taochi | 25 | 8000 | | 4 | meijianbiao | 30 | 9000 | | 5 | mufeng | 23 | 5000 | | 6 | fangxinxin | 24 | 10000 | | 7 | leichen | 21 | 10000 | | 8 | yuqinhao | 50 | 100000 | | 9 | tanghaolun | 10 | 10000 | +----+---------------+------+--------+ 9 rows in set (0.000 sec)
13.修改表teacher中的数据,先把id为9的那行数据年龄改成35，再把年龄改成30，工资改成50000。
MariaDB [school]> update teacher set age = 35 where id = 9; Query OK, 1 row affected (0.009 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [school]> select * from teacher; +----+---------------+------+--------+ | id | name | age | salary | +----+---------------+------+--------+ | 1 | chengsongling | 21 | 4000 | | 2 | wangming | 25 | 5000.5 | | 3 | taochi | 25 | 8000 | | 4 | meijianbiao | 30 | 9000 | | 5 | mufeng | 23 | 5000 | | 6 | fangxinxin | 24 | 10000 | | 7 | leichen | 21 | 10000 | | 8 | yuqinhao | 50 | 100000 | | 9 | tanghaolun | 35 | 10000 | +----+---------------+------+--------+ 9 rows in set (0.000 sec) MariaDB [school]> update teacher set age = 30,salary = 50000 where id = 9; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [school]> select * from teacher; +----+---------------+------+--------+ | id | name | age | salary | +----+---------------+------+--------+ | 1 | chengsongling | 21 | 4000 | | 2 | wangming | 25 | 5000.5 | | 3 | taochi | 25 | 8000 | | 4 | meijianbiao | 30 | 9000 | | 5 | mufeng | 23 | 5000 | | 6 | fangxinxin | 24 | 10000 | | 7 | leichen | 21 | 10000 | | 8 | yuqinhao | 50 | 100000 | | 9 | tanghaolun | 30 | 50000 | +----+---------------+------+--------+ 9 rows in set (0.000 sec)
SELECT语句
字段column表示法
| 表示符 | 代表什么？ |
|---|---|
| * | 所有字段 |
| as | 字段别名，如col1 AS alias1 当表名很长时用别名代替 |
条件判断语句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个结果 |
14.查找teacher表name与salary一项,再给name设置显示的别名为姓名，给salary设置显示的别名为薪资。
MariaDB [school]> select name,salary from teacher; +---------------+--------+ | name | salary | +---------------+--------+ | chengsongling | 4000 | | wangming | 5000.5 | | taochi | 8000 | | meijianbiao | 9000 | | mufeng | 5000 | | fangxinxin | 10000 | | leichen | 10000 | | yuqinhao | 100000 | | tanghaolun | 50000 | +---------------+--------+ 9 rows in set (0.000 sec) MariaDB [school]> select name as '姓名',salary as '薪资' from teacher; +---------------+--------+ | 姓名 | 薪资 | +---------------+--------+ | chengsongling | 4000 | | wangming | 5000.5 | | taochi | 8000 | | meijianbiao | 9000 | | mufeng | 5000 | | fangxinxin | 10000 | | leichen | 10000 | | yuqinhao | 100000 | | tanghaolun | 50000 | +---------------+--------+ 9 rows in set (0.000 sec)
15.先查找薪资为50000的数据的姓名，再查找薪资大于8000的所有记录，最后查找年龄再25到30之间的记录。
MariaDB [school]> select name from teacher where salary = 50000; +------------+ | name | +------------+ | tanghaolun | +------------+ 1 row in set (0.000 sec) MariaDB [school]> select * from teacher where salary > 8000; +----+-------------+------+--------+ | id | name | age | salary | +----+-------------+------+--------+ | 4 | meijianbiao | 30 | 9000 | | 6 | fangxinxin | 24 | 10000 | | 7 | leichen | 21 | 10000 | | 8 | yuqinhao | 50 | 100000 | | 9 | tanghaolun | 30 | 50000 | +----+-------------+------+--------+ 5 rows in set (0.000 sec) MariaDB [school]> select * from teacher where age between 25 and 30; +----+-------------+------+--------+ | id | name | age | salary | +----+-------------+------+--------+ | 2 | wangming | 25 | 5000.5 | | 3 | taochi | 25 | 8000 | | 4 | meijianbiao | 30 | 9000 | | 9 | tanghaolun | 30 | 50000 | +----+-------------+------+--------+ 4 rows in set (0.000 sec)
16.查找表teacher中以c开头的姓名的记录，再查找以ing结尾的姓名的记录，最后再查找以ing结尾或ao结尾的姓名的记录。
MariaDB [school]> select * from teacher where name like 'c%'; +----+---------------+------+--------+ | id | name | age | salary | +----+---------------+------+--------+ | 1 | chengsongling | 21 | 4000 | +----+---------------+------+--------+ 1 row in set (0.000 sec) MariaDB [school]> select * from teacher where name like 'ing%'; Empty set (0.000 sec) MariaDB [school]> select * from teacher where name like '%ing'; +----+---------------+------+--------+ | id | name | age | salary | +----+---------------+------+--------+ | 1 | chengsongling | 21 | 4000 | | 2 | wangming | 25 | 5000.5 | +----+---------------+------+--------+ 2 rows in set (0.000 sec) MariaDB [school]> select * from teacher where name like '%ing' or name like '%ao'; +----+---------------+------+--------+ | id | name | age | salary | +----+---------------+------+--------+ | 1 | chengsongling | 21 | 4000 | | 2 | wangming | 25 | 5000.5 | | 4 | meijianbiao | 30 | 9000 | | 8 | yuqinhao | 50 | 100000 | +----+---------------+------+--------+ 4 rows in set (0.000 sec)
17.往表teacher里插入两条数据，再增加一行字段为department，默认为空，再把id为11的数据的department改为空格。
MariaDB [school]> insert teacher(name,age,salary) values('yanchuang',80,null),('chenben',69,0); Query OK, 2 rows affected (0.001 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [school]> select * from teacher; +----+---------------+------+--------+ | id | name | age | salary | +----+---------------+------+--------+ | 1 | chengsongling | 21 | 4000 | | 2 | wangming | 25 | 5000.5 | | 3 | taochi | 25 | 8000 | | 4 | meijianbiao | 30 | 9000 | | 5 | mufeng | 23 | 5000 | | 6 | fangxinxin | 24 | 10000 | | 7 | leichen | 21 | 10000 | | 8 | yuqinhao | 50 | 100000 | | 9 | tanghaolun | 30 | 50000 | | 10 | yanchuang | 80 | NULL | | 11 | chenben | 69 | 0 | +----+---------------+------+--------+ 11 rows in set (0.000 sec) MariaDB [school]> alter table teacher add department varchar(50) null; Query OK, 0 rows affected (0.002 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [school]> select * from teacher; +----+---------------+------+--------+------------+ | id | name | age | salary | department | +----+---------------+------+--------+------------+ | 1 | chengsongling | 21 | 4000 | NULL | | 2 | wangming | 25 | 5000.5 | NULL | | 3 | taochi | 25 | 8000 | NULL | | 4 | meijianbiao | 30 | 9000 | NULL | | 5 | mufeng | 23 | 5000 | NULL | | 6 | fangxinxin | 24 | 10000 | NULL | | 7 | leichen | 21 | 10000 | NULL | | 8 | yuqinhao | 50 | 100000 | NULL | | 9 | tanghaolun | 30 | 50000 | NULL | | 10 | yanchuang | 80 | NULL | NULL | | 11 | chenben | 69 | 0 | NULL | +----+---------------+------+--------+------------+ 11 rows in set (0.000 sec) MariaDB [school]> update teacher set department = ' ' where id = 11; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [school]> select * from teacher; +----+---------------+------+--------+------------+ | id | name | age | salary | department | +----+---------------+------+--------+------------+ | 1 | chengsongling | 21 | 4000 | NULL | | 2 | wangming | 25 | 5000.5 | NULL | | 3 | taochi | 25 | 8000 | NULL | | 4 | meijianbiao | 30 | 9000 | NULL | | 5 | mufeng | 23 | 5000 | NULL | | 6 | fangxinxin | 24 | 10000 | NULL | | 7 | leichen | 21 | 10000 | NULL | | 8 | yuqinhao | 50 | 100000 | NULL | | 9 | tanghaolun | 30 | 50000 | NULL | | 10 | yanchuang | 80 | NULL | NULL | | 11 | chenben | 69 | 0 | | +----+---------------+------+--------+------------+ 11 rows in set (0.000 sec)
18.查找表teacher中dapartment数据不为空的数据。（数据中null等于空，空白不等于空）
MariaDB [school]> select * from teacher where department is not null; +----+---------+------+--------+------------+ | id | name | age | salary | department | +----+---------+------+--------+------------+ | 11 | chenben | 69 | 0 | | +----+---------+------+--------+------------+ 1 row in set (0.000 sec)
19.先在表teacher里按age升序排列，再按salary降序排列。
MariaDB [school]> select * from teacher order by age; +----+---------------+------+--------+------------+ | id | name | age | salary | department | +----+---------------+------+--------+------------+ | 1 | chengsongling | 21 | 4000 | NULL | | 7 | leichen | 21 | 10000 | NULL | | 5 | mufeng | 23 | 5000 | NULL | | 6 | fangxinxin | 24 | 10000 | NULL | | 3 | taochi | 25 | 8000 | NULL | | 2 | wangming | 25 | 5000.5 | NULL | | 4 | meijianbiao | 30 | 9000 | NULL | | 9 | tanghaolun | 30 | 50000 | NULL | | 8 | yuqinhao | 50 | 100000 | NULL | | 11 | chenben | 69 | 0 | | | 10 | yanchuang | 80 | NULL | NULL | +----+---------------+------+--------+------------+ 11 rows in set (0.000 sec) MariaDB [school]> select * from teacher order by salary desc; +----+---------------+------+--------+------------+ | id | name | age | salary | department | +----+---------------+------+--------+------------+ | 8 | yuqinhao | 50 | 100000 | NULL | | 9 | tanghaolun | 30 | 50000 | NULL | | 6 | fangxinxin | 24 | 10000 | NULL | | 7 | leichen | 21 | 10000 | NULL | | 4 | meijianbiao | 30 | 9000 | NULL | | 3 | taochi | 25 | 8000 | NULL | | 2 | wangming | 25 | 5000.5 | NULL | | 5 | mufeng | 23 | 5000 | NULL | | 1 | chengsongling | 21 | 4000 | NULL | | 11 | chenben | 69 | 0 | | | 10 | yanchuang | 80 | NULL | NULL | +----+---------------+------+--------+------------+ 11 rows in set (0.000 sec)
truncate语句
truncate与delete的区别：
| 语句类型 | 特点 |
|---|---|
| delete | DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项 可以通过回滚事务日志恢复数据 非常占用空间 |
| truncate | 删除表中所有数据，且无法恢复 表结构、约束和索引等保持不变，新添加的行计数值重置为初始值 执行速度比DELETE快，且使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放 对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据 不能用于加入了索引视图的表 |
20.先删除表teacher中工资为空的数据，再删除department不为空的数据。最后删除整个表teacher的数据。（使用delete命令删除，表依然会存在，且会保留表结构）
MariaDB [school]> delete from teacher where salary is null; Query OK, 1 row affected (0.001 sec) MariaDB [school]> select * from teacher; +----+---------------+------+--------+------------+ | id | name | age | salary | department | +----+---------------+------+--------+------------+ | 1 | chengsongling | 21 | 4000 | NULL | | 2 | wangming | 25 | 5000.5 | NULL | | 3 | taochi | 25 | 8000 | NULL | | 4 | meijianbiao | 30 | 9000 | NULL | | 5 | mufeng | 23 | 5000 | NULL | | 6 | fangxinxin | 24 | 10000 | NULL | | 7 | leichen | 21 | 10000 | NULL | | 8 | yuqinhao | 50 | 100000 | NULL | | 9 | tanghaolun | 30 | 50000 | NULL | | 11 | chenben | 69 | 0 | | +----+---------------+------+--------+------------+ 10 rows in set (0.000 sec) MariaDB [school]> delete from teacher where department is not null; Query OK, 1 row affected (0.001 sec) MariaDB [school]> select * from teacher; +----+---------------+------+--------+------------+ | id | name | age | salary | department | +----+---------------+------+--------+------------+ | 1 | chengsongling | 21 | 4000 | NULL | | 2 | wangming | 25 | 5000.5 | NULL | | 3 | taochi | 25 | 8000 | NULL | | 4 | meijianbiao | 30 | 9000 | NULL | | 5 | mufeng | 23 | 5000 | NULL | | 6 | fangxinxin | 24 | 10000 | NULL | | 7 | leichen | 21 | 10000 | NULL | | 8 | yuqinhao | 50 | 100000 | NULL | | 9 | tanghaolun | 30 | 50000 | NULL | +----+---------------+------+--------+------------+ 9 rows in set (0.000 sec) MariaDB [school]> delete from teacher; Query OK, 9 rows affected (0.001 sec) MariaDB [school]> select * from teacher; Empty set (0.000 sec) MariaDB [school]> show tables; +------------------+ | Tables_in_school | +------------------+ | student | | teacher | +------------------+ 2 rows in set (0.000 sec)
21.再往teacher表里插入数据，会发现id还在顺延，使用truncate可以彻底删除。之后插入数据会发现id重新开始计数。（使用truncate删除的话数据无法恢复）
MariaDB [school]> insert teacher(name,age,salary) values('tom',20,8000),('jerry',23,6000),('zhangsan',22,9000); Query OK, 3 rows affected (0.002 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [school]> select * from teacher; +----+----------+------+--------+------------+ | id | name | age | salary | department | +----+----------+------+--------+------------+ | 12 | tom | 20 | 8000 | NULL | | 13 | jerry | 23 | 6000 | NULL | | 14 | zhangsan | 22 | 9000 | NULL | +----+----------+------+--------+------------+ 3 rows in set (0.000 sec) MariaDB [school]> truncate teacher; Query OK, 0 rows affected (0.005 sec) MariaDB [school]> select * from teacher; Empty set (0.000 sec) MariaDB [school]> insert teacher(name,age,salary) values('tom',20,8000),('jerry',23,6000),('zhangsan',22,9000); Query OK, 3 rows affected (0.001 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [school]> select * from teacher; +----+----------+------+--------+------------+ | id | name | age | salary | department | +----+----------+------+--------+------------+ | 1 | tom | 20 | 8000 | NULL | | 2 | jerry | 23 | 6000 | NULL | | 3 | zhangsan | 22 | 9000 | NULL | +----+----------+------+--------+------------+ 3 rows in set (0.000 sec)
作业
1.搭建mysql服务
[root@win10 private]# mount /dev/cdrom /mnt/ mount: /mnt: WARNING: device write-protected, mounted read-only. [root@win10 ~]# yum -y install mariadb* [root@win10 ~]# 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 if not exists csl; Query OK, 1 row affected (0.000 sec) MariaDB [(none)]> use csl; Database changed MariaDB [csl]> 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 [csl]> 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 [csl]> 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 [csl]> 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 [csl]> 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 [csl]> update student set age = 50 where id = 7; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [csl]> 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 [csl]> 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 [csl]> 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 [csl]> 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 [csl]> 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 [csl]> 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 [csl]> 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 [csl]> update student set age = 100 where id = 9; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [csl]> 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 [csl]> delete from student where name = 'zhangshan' and age<=20; Query OK, 1 row affected (0.001 sec) MariaDB [csl]> 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)
