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：移除授权 |
安装mariadb
安装
直接yum安装mariadb，但是安装的时候需要安装一些依赖包
[root@localhost ~]# yum -y install mariadb mariadb-devel mariadb-common mariadb-server
//开启服务并设置开机自启
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl enable mariadb
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
登录mysql
[root@localhost ~]# mysql -uroot
//设置密码
MariaDB [(none)]> set password = password('wa123');
//退出数据库，使用quit和exit都可以
//使用密码登录mysql
[root@localhost ~]# mysql -uroot -p'wa123'
//使用密码登录
[root@localhost ~]# mysql -uroot -p
Enter password:
mysql的程序组成
- 客户端
- mysql：CLI交互式客户端程序
- mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
- mysqldump：mysql备份工具
- mysqladmin
- 服务器端
- mysqld
mysql工具使用
//语法：mysql [OPTIONS] [database]
//常用的OPTIONS：
-uUSERNAME //指定用户名，默认为root
-hHOST //指定服务器主机，默认为localhost，推荐使用ip地址
-pPASSWORD //指定用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
-V //查看当前使用的mysql版本
-e //不登录mysql执行sql语句后退出，常用于脚本
[root@localhost ~]# mysql -V
mysql Ver 15.1 Distrib 10.3.28-MariaDB, for Linux (x86_64) using readline 5.1
[root@localhost ~]# mysql -uroot -p'wa123' -h'127.0.0.1'
//注意，不推荐直接在命令行里直接用-pPASSWORD的方式登录，而是使用-p选项，然后交互式输入密码
[root@localhost ~]# mysql -uroot -p -h'127.0.0.1'
Enter password:
[root@localhost ~]# mysql -uroot -p -P3306
Enter password:
[root@localhost ~]# mysql -uroot -p -e'show databases'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
服务器监听的两种socket地址
| socket类型 | 说明 |
|---|---|
| ip socket | 默认监听在tcp的3306端口，支持远程通信 |
| unix sock | 监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock） 仅支持本地通信 server地址只能是：localhost，127.0.0.1 |
mysql数据库操作
DDL操作
数据库操作
- 查看当前实例有哪些数据库 show databases
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
//创建数据库
//语法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';
//创建数据库school
MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
//创建数据库时可以加上判断if not exists(如果存在不创建，如果不存在则创建)
ariaDB [(none)]> create database if not exists school;
Query OK, 0 rows affected, 1 warning (0.000 sec)
//删除数据库
//语法：DROP DATABASE [IF EXISTS] 'DB_NAME';
//删除数据库school
MariaDB [(none)]> drop database school;
Query OK, 0 rows affected (0.001 sec)
//删除数据库时可以加上判断if exists（存在则删除，不存在不报错）
MariaDB [(none)]> drop database if exists school;
Query OK, 0 rows affected, 1 warning (0.001 sec)
表操作
//查看表首先要先进入你需要的数据库
//进入school数据库
MariaDB [(none)]> use school;
Database changed
//查看school数据库中的所有表
MariaDB [school]> show tables;
Empty set (0.000 sec)
//创建表
//语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';
//创建表student id为int类型，不允许为空；name为varchar类型，最大50个字符，不允许为空；age为tinyint类型，不允许为空
MariaDB [school]> create table student(id int not null,age tinyint, name varchar(50) not null);
Query OK, 0 rows affected (0.005 sec)
//查看某个表的结构类型
//desc table_name;
//查看student表结构
MariaDB [school]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| name | varchar(50) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)
//删除表 drop table table_name;
//删除student表
MariaDB [school]> drop table student;
Query OK, 0 rows affected (0.003 sec)
查看命令SHOW
- 查看支持的所有字符集 show character set;
MariaDB [(none)]> 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 |
........
- 查看数据库信息 show databases;
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
4 rows in set (0.001 sec)
- 不进入某数据库而列出其包含的所有表 show tables from database_name;
- 例：不进入school数据库查看它的所有表
MariaDB [(none)]> show tables from school;
+------------------+
| Tables_in_school |
+------------------+
| student |
+------------------+
1 row in set (0.001 sec)
- 不进入数据库查看表结构
- 语法：DESC [db_name.]table_name;
- 例：不进入数据库school查看其表student结构
MariaDB [(none)]> desc school.student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(50) | NO | | NULL | |
| age | tinyint(4) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)
- 查看某表的创建命令（前提是进入数据库）
- 语法：SHOW CREATE TABLE table_name;
MariaDB [school]> show create table student;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`age` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
- 查看某表的状态
- 语法：SHOW TABLE STATUS LIKE ‘table_name’\G
//查看表student状态， 只匹配student这一张表
MariaDB [school]> show table status like 'student'\G;
//匹配单个字符，student
MariaDB [school]> show table status like 'student_'\G;
//查看所有名字叫student
MariaDB [school]> show table status like 'student%'\G;
获取帮助
//获取命令使用帮助
//语法：HELP keyword;
MariaDB [(none)]> help create table;
//获取创建表的帮助
MariaDB [(none)]> help create table;
修改表
- 修改表alter
- 在表中添加你想要的 alter table + 需要修改的表的名字 add +需要加的字段（描述）
- 例：表student，添加score，类型为float
MariaDB [school]> alter table student add score float;
Query OK, 0 rows affected (0.001 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 修改表中的内容
- alter table +需要修改的表的名字 modify +修改的字段；
- 修改表student 中的name
MariaDB [school]> alter table student modify name varchar(10);
Query OK, 0 rows affected (0.008 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 删除表中的字段
- alter table student drop +需要删除的字段
MariaDB [school]> alter table student drop name;
Query OK, 0 rows affected (0.006 sec)
Records: 0 Duplicates: 0 Warnings: 0
用户操作
mysql用户帐号由两部分组成，如’USERNAME’@‘HOST’，表示此USERNAME只能从此HOST上远程登录
这里（‘USERNAME’@‘HOST’）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：
- IP地址，如：172.16.12.129
- 通配符
- %：匹配任意长度的任意字符，常用于设置允许从任何主机登录
- _：匹配任意单个字符
//数据库用户创建
//语法：CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
//创建数据库用户tom 只能在本机登录
MariaDB [(none)]> create user 'tom'@'127.0.0.1' identified by 'wa123';
Query OK, 0 rows affected (0.000 sec)
//创建用户在远程主机登录
//创建用户lisi 可以在192.168.218.133主机上登录
MariaDB [(none)]> create user 'lisi'@'192.168.218.133' identified by 'wa123';
Query OK, 0 rows affected (0.000 sec)
//在主机192.168.218.133登录
[root@localhost ~]# mysql -ulisi -p'wa123' -h192.168.218.133
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用户在数据库本机上登录访问所有数据库
MariaDB [(none)]> grant all on *.* to 'tom'@'127.0.0.1' identified by 'wa123';
Query OK, 0 rows affected (0.000 sec)
//授权lisi用户在192.168.218.133主机上登录，用所所有权限，访问所有数据库
MariaDB [(none)]> grant all on *.* to 'lisi'@'192.168.218.133' identified by 'wa123';
Query OK, 0 rows affected (0.000 sec)
授权完成之后一般需要刷新一下，使用命令：
flush privileges;
取消授权REVOKE
//语法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';
//取消lisi用户在192.168.218.133所有权限
MariaDB [(none)]> revoke all on *.* from 'lisi'@'192.168.218.133';
Query OK, 0 rows affected (0.000 sec)
mysql> flsuh privileges;
Query OK, 0 rows affected (0.00 sec)
查看授权
//查看当前登录用户的授权信息
MariaDB [(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*88E7F770ABAE849F1A0C61F5480E505E00EA2FDC' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)
//查看指定用户tom的在指定主机授权信息
MariaDB [(none)]> show grants for 'lisi'@'192.168.218.133';
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for lisi@192.168.218.133 |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `lisi`@`192.168.218.133` IDENTIFIED BY PASSWORD '*88E7F770ABAE849F1A0C61F5480E505E00EA2FDC' |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
DML
DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。
INSERT语句
//DML操作之增操作insert
//语法：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),...
MariaDB [school]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| age | tinyint(4) | NO | | NULL | |
| pay | float | NO | | NULL | |
| name | varchar(50) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.001 sec)
//插入一条记录
MariaDB [school]> insert student(id,name,age,pay) value(1,'lisi',20,5000);
Query OK, 1 row affected (0.001 sec)
//查看是否插入成功
MariaDB [school]> select * from student;
+------+-----+------+------+
| id | age | pay | name |
+------+-----+------+------+
| 1 | 20 | 5000 | lisi |
+------+-----+------+------+
1 row in set (0.000 sec)
//一次插入多个记录，多个数据时values
MariaDB [school]> insert student(id,name,age,pay) values(2,'zhangsan',20,6000),(3,'wangwu',25,8000),(4,'tom',30,10000);
Query OK, 3 rows affected (0.002 sec)
Records: 3 Duplicates: 0 Warnings: 0
update语句
//DML操作之改操作update
//语法：UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
//修改表student 当name时lisi的时候，修改age为40
MariaDB [school]> update student set age = 40 where name = 'lisi';
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
//查询是否修改成功
MariaDB [school]> select * from student where name = 'lisi';
+------+-----+------+------+
| id | age | pay | name |
+------+-----+------+------+
| 1 | 40 | 5000 | lisi |
+------+-----+------+------+
1 row in set (0.001 sec)
delete语句
//DML操作之删操作delete
//语法：DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
MariaDB [school]> select * from student;
+------+-----+-------+----------+
| id | age | pay | name |
+------+-----+-------+----------+
| 1 | 40 | 5000 | lisi |
| 2 | 20 | 6000 | zhangsan |
| 3 | 25 | 8000 | wangwu |
| 4 | 30 | 10000 | tom |
//删除表student中id为4的tom
MariaDB [school]> delete from student where id = 4;
Query OK, 1 row affected (0.004 sec)
MariaDB [school]> select * from student;
+------+-----+------+----------+
| id | age | pay | name |
+------+-----+------+----------+
| 1 | 40 | 5000 | lisi |
| 2 | 20 | 6000 | zhangsan |
| 3 | 25 | 8000 | wangwu |
+------+-----+------+----------+
3 rows in set (0.000 sec)
//当id或者其他参数具有重复的时候，在删除的时候可以添加多重判断
MariaDB [school]> select * from student;
+------+-----+------+----------+
| id | age | pay | name |
+------+-----+------+----------+
| 1 | 40 | 5000 | lisi |
| 2 | 20 | 6000 | zhangsan |
| 3 | 25 | 8000 | wangwu |
| 3 | 22 | 1000 | tom |
| 4 | 21 | 2000 | jerry |
+------+-----+------+----------+
5 rows in set (0.001 sec)
//删除id为3的tom
MariaDB [school]> delete from student where id = 3 and name = 'tom';
Query OK, 1 row affected (0.002 sec)
//查看是否删除成功
MariaDB [school]> select * from student;
+------+-----+------+----------+
| id | age | pay | name |
+------+-----+------+----------+
| 1 | 40 | 5000 | lisi |
| 2 | 20 | 6000 | zhangsan |
| 3 | 25 | 8000 | wangwu |
| 4 | 21 | 2000 | jerry |
+------+-----+------+----------+
//若想要删除表中的所有数据，但是表结构依然存在
MariaDB [school]> delete from student1;
Query OK, 0 rows affected (0.000 sec)
//表结构依然存在
MariaDB [school]> desc student1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(50) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| pay | float | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.001 sec)
truncate语句
| 语句类型 | 特点 |
|---|---|
| delete | DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项 新添加的行计数值不能重置为初始值 |
| truncate | 删除表中所有数据，且无法恢复 表结构、约束和索引等保持不变，新添加的行计数值重置为初始值 |
//语法：TRUNCATE table_name;
MariaDB [school]> select * from student;
+------+-----+------+----------+
| id | age | pay | name |
+------+-----+------+----------+
| 1 | 40 | 5000 | lisi |
| 2 | 20 | 6000 | zhangsan |
| 3 | 25 | 8000 | wangwu |
| 4 | 21 | 2000 | jerry |
+------+-----+------+----------+
4 rows in set (0.000 sec)
//删除表student数据
MariaDB [school]> truncate student;
Query OK, 0 rows affected (0.008 sec)
//查看是否删除成功
MariaDB [school]> select * from student;
Empty set (0.001 sec)
//查看表结构是否存在
MariaDB [school]> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| age | tinyint(4) | NO | | NULL | |
| pay | float | NO | | NULL | |
| name | varchar(50) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.002 sec)
//再次插入数据，新添加行计数值重置为初始值
MariaDB [school]> insert student(name,age,pay) values('tom',12,1000),('lisi',21,1999);
Query OK, 2 rows affected (0.003 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [school]> select * from student;
+----+-----+------+------+
| id | age | pay | name |
+----+-----+------+------+
| 1 | 12 | 1000 | tom |
| 2 | 21 | 1999 | lisi |
+----+-----+------+------+
2 rows in set (0.000 sec)
SELECT语句
字段column表示法
| 表示符 | 代表什么？ |
|---|---|
| * | 所有字段 |
| as | 字段别名，如col1 AS alias1 当表名很长时用别名代替 |
//查看表student中所有数据
MariaDB [school]> select * from student;
+----+-----+------+------+
| id | age | pay | name |
+----+-----+------+------+
| 1 | 12 | 1000 | tom |
| 2 | 21 | 1999 | lisi |
+----+-----+------+------+
//查看表student中name
MariaDB [school]> select name from student;
+------+
| name |
+------+
| tom |
| lisi |
+------+
2 rows in set (0.000 sec)
//查看表student中name和age
MariaDB [school]> select name,age from student;
+------+-----+
| name | age |
+------+-----+
| tom | 12 |
| lisi | 21 |
+------+-----+
2 rows in set (0.000 sec)
条件判断语句WHERE
| 操作类型 | 常用操作符 |
|---|---|
| 操作符 | >，<，>=，<=，=，!= BETWEEN column# AND column# LIKE：模糊匹配 RLIKE：基于正则表达式进行模式匹配 IS NOT NULL：非空 IS NULL：空 |
| 条件逻辑操作 | AND OR NOT |
//使用操作符
MariaDB [school]> select * from student where age < 40;
+----+-----+------+----------+
| id | age | pay | name |
+----+-----+------+----------+
| 1 | 12 | 1000 | tom |
| 2 | 21 | 1999 | lisi |
| 3 | 35 | 800 | zhangsan |
| 4 | 25 | 1888 | jerry |
+----+-----+------+----------+
4 rows in set (0.001 sec)
MariaDB [school]> select * from student where age between 20 and 30;
+----+-----+------+-------+
| id | age | pay | name |
+----+-----+------+-------+
| 2 | 21 | 1999 | lisi |
| 4 | 25 | 1888 | jerry |
+----+-----+------+-------+
2 rows in set (0.001 sec)
//查询名字以l开头的用户
MariaDB [school]> select * from student where name like 'l%';
+----+-----+------+------+
| id | age | pay | name |
+----+-----+------+------+
| 2 | 21 | 1999 | lisi |
+----+-----+------+------+
1 row in set (0.001 sec)
//查询名字以y结尾的用户
MariaDB [school]> select * from student where name like '%y';
+----+-----+------+-------+
| id | age | pay | name |
+----+-----+------+-------+
| 4 | 25 | 1888 | jerry |
+----+-----+------+-------+
1 row in set (0.000 sec)
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 [school]> select * from student order by age;
+----+-----+-------+----------+
| id | age | pay | name |
+----+-----+-------+----------+
| 1 | 12 | 1000 | tom |
| 2 | 21 | 1999 | lisi |
| 4 | 25 | 1888 | jerry |
| 3 | 35 | 800 | zhangsan |
| 5 | 45 | 28888 | wangwu |
+----+-----+-------+----------+
5 rows in set (0.001 sec)
//以pay降序排列
MariaDB [school]> select * from student order by pay desc;
+----+-----+-------+----------+
| id | age | pay | name |
+----+-----+-------+----------+
| 5 | 45 | 28888 | wangwu |
| 2 | 21 | 1999 | lisi |
| 4 | 25 | 1888 | jerry |
| 1 | 12 | 1000 | tom |
| 3 | 35 | 800 | zhangsan |
+----+-----+-------+----------+
5 rows in set (0.001 sec)
//以年龄升序排列，取第一名
MariaDB [school]> select * from student order by age limit 1;
+----+-----+------+------+
| id | age | pay | name |
+----+-----+------+------+
| 1 | 12 | 1000 | tom |
+----+-----+------+------+
1 row in set (0.000 sec)
//以年龄升序排列，滤过第一名，取后面两个结果
MariaDB [school]> select * from student order by age limit 1,2;
+----+-----+------+-------+
| id | age | pay | name |
+----+-----+------+-------+
| 2 | 21 | 1999 | lisi |
| 4 | 25 | 1888 | jerry |
+----+-----+------+-------+
2 rows in set (0.001 sec)
扫一扫
6545
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
