MYSQL语句
一. SQL语句的三种类型：
DDL ： 数据定义语言
DML ： 数据操纵语言
DCL ： 数据控制语言
二. Maysql语言的操作类型
| 类型 | 对应操纵 |
|---|---|
| DDL | CREATE :创建 ,DROP：删除，ALTER：修改 |
| DML | INSERT：向表中插入数据，DELETE：删除表中数据，UPDATE：更新表中数据，SELECT：查询表中数据 |
| DCL | GRANT：授权，REEVOKE：移除授权 |
三 . mysql的安装方式：
- 源代码：编译安装
- 二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
- 程序包管理器的程序包：
rpm：
OS Vendor：
操作系统发行商提供的
项目官方提供的
四 .Mysql的安装与配置
1. 配置Mysql 的yum 源
[root@Mysql-server ~]# cd /usr/src/
[root@Mysql-server src]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
[root@Mysql-server src]# ls
debug kernels mysql57-community-release-el7-10.noarch.rpm
[root@Mysql-server src]# yum install -y mysql57-community-release-el7-10.noarch.rpm
[root@Mysql-server src]# ls /etc/yum.repos.d/
cs.repo mysql-community.repo mysql-community-source.repo
2. 安装mysql 5.7
[root@Mysql-server ~]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-deve
安装过程略...
3. 检查环境，启动服务
[root@Mysql-server ~]# systemctl stop firewalld
[root@Mysql-server ~]# systemctl disable firewalld
[root@Mysql-server ~]# setenforce 0
[root@Mysql-server ~]# systemctl start mysqld
[root@Mysql-server ~]# ss -anlt
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 :::80 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 80 :::3306 //mysql 服务端口已经监听 :::*
4. 配置Mysql
在日志文件中找出临时密码
[root@Mysql-server ~]# grep "password" /var/log/mysqld.log
2018-08-17T12:46:26.744740Z 1 [Note] A temporary password is generated for root@localhost: soiFXZwVR7(y //此处是随机密码，每次随机密码不一样
使用临时密码登陆mysql
[root@Mysql-server ~]# mysql -uroot -p // -u 指定登录的用户 -p 指定登陆的密码 （可在-p后面接密码，为了安全性，不建议直接写密码）
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 出现这个提示符说明成功登录了！
修改mysql 登录密码
mysql> set global validate_password_policy=0; //
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> alter user 'root'@'localhost' identified by 'chen';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
为了避免mysql 自动升级，需要卸载最开始安装的yum 源
[root@Mysql-server ~]# yum remove -y mysql57-community-release.noarch
5. mysql工具的使用
语法：mysql [OPTIONS] [database]
常用的 OPTIONS：
-uUSERNAME //指定用户名，默认为root
-hHOST //指定服务器主机，默认为localhost，推荐使用IP地址
-pPASSWORD //指定用户密码
-P# //指定数据库监听的端口号，这里的# 需要用实际的端口号代替，例 ：-P3307
-V //查看当前使用的mysql版本
-e //不登录mysql执行sql语句后退出，常用于脚本
[root@Mysql-server ~]# mysql -uroot -pchen -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
[root@Mysql-server ~]# mysql -uroot -p -h127.0.0.1 -e 'show databases;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6. 服务器监听的两种socket地址
| socket类型 | 说明 |
|---|---|
| ip socket | 默认监听在tcp的3306端口，支持远程通信 |
| unix sock | 监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock)，仅支持本地通信 ，server地址只能是：localhost ，127.0.0.1 |
7. mysql 数据库的操作
7.1 DDL操作
7.1.1 数据库操作
创建数据库chens
mysql> create database chens;
Query OK, 1 row affected (0.00 sec)
mysql> show databases; // 查看有哪些数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| chens |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
删除数据库
mysql> drop database if exists chens;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database chens; // 这样是直接删除
Query OK, 0 rows affected (0.00 sec)
7.1.2 表操作
创建表
语法： CREATE TABLE table_name (col1 datatype 修饰符，col2 datatype 修饰符）ENGINE='存储引擎类型’；
创建一个表chens
mysql> use chens; // 进入到chens 这个库里面，只有在库里面才能对表操作！
Database changed
mysql> create table chens(id int not null,name varchar(100) not null,age tinyint);
Query OK, 0 rows affected (0.05 sec)
// int 这里表示整数，not null 这里表示不能为空，vachar（100）这里表示取的字符长度不能超过100
**查看当前数据库有哪些表**
mysql> show tables;
+-----------------+
| Tables_in_chens |
+-----------------+
| chens |
+-----------------+
1 row in set (0.00 sec)
**可查看表chens的结构！**
mysql> desc chens;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
删除表
语法： DROP TABLE [ IF EXISTS ] ‘table_name’;
删除chens 表
mysql> drop table chens;
Query OK, 0 rows affected (0.01 sec
用户的操作
mysql用户账号由两部分组成，如‘USERNAME’@‘HOST’，表示此USERNAME只能从此HOST上远程登录，这里的（‘USERNAME’@‘HOST’）的HOST用于限制此用户可以通过哪些主机远程连接mysql程序，其值可设为：
IP 地址：192.168.168.10
通配符
.%：匹配任意长度的任意字符，常用于设置允许从任何主机登录
._：匹配任意单个字符
数据库用户创建
语法： CREATE USER ‘username’@‘host’ [IDENTIFIED BY ‘password’ ]；
创建数据库用户chens
mysql> create user 'chens'@'127.0.0.1' IDENTIFIED BY 'chens';
Query OK, 0 rows affected (0.01 sec)
使用新创建的用户密码登录
[root@localhost src]# mysql -uchens -pchens -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
删除数据库用户
语法： DROP USER ‘username’@‘host’；
删除数据库用户chens
mysql> DROP USER 'chens'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)
查看命令 SHOW
查看有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| chens |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
查看数据库里面有哪些表
mysql> use mysql; //use 用于切换数据库
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
查看支持的所有字符集
mysql> 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 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.01 sec)
查看当前数据库支持的所有存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
查看表的结构
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N
.....此处省略
查看某表的创建命令
语法： SHOW CREATE TABLE table_name；
mysql> show create table chens;
获取帮助
// 获取命令使用帮助
语法： HELP keyword；
mysql> help create table; //获取创建表的帮助
INSERT语句
DML操作之增操作insert
在chens 这个表里面插入一条信息
mysql> insert into chens (id,name,age)value(1,'tom',20);
Query OK, 1 row affected (0.01 sec)
插入多条信息
mysql> insert into chens (id,name,age)values(1,'tom',20),(2,'jerry',23);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
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进行升序排列，并只取前两个结果 |
| order by ‘column_name’ LIMIT 1,2 | 根据column_name 进行升序排列并略过第一个结果取后面的2个结果 |
SELECT 操作
查看chens 这个表的内容
mysql> select * from chens;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
+----+-------+------+
2 rows in set (0.00 sec)
查看chens表里面，name这一栏信息
mysql> select name from chens;
+-------+
| name |
+-------+
| tom |
| jerry |
+-------+
2 rows in set (0.00 sec)
把chens这个表按照 age ，升序排列
mysql> select * from chens order by age;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
+----+-------+------+
2 rows in set (0.00 sec)
把chens 这个表按照age 降序排列
mysql> select * from chens order by age desc ;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 2 | jerry | 23 |
| 1 | tom | 20 |
+----+-------+------+
2 rows in set (0.00 sec)
把chens这个表按照升序排列，取出age 前面2位
mysql> select * from chens order by age limit 2;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
+----+-------+------+
2 rows in set (0.00 sec)
update 语句
修改chens 这个表里面tom 的age 为50
mysql> update chens set age = 50 where name= 'tom';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
delete 语句
删除操作 delete
删除chens 这张表里面id为7 的这条记录
mysql> delete from chens where id=1;
Query OK, 1 row affected (0.00 sec)
删除整张表的内容
mysql> delete from chens;
Query OK, 1 row affected (0.00 sec)
创建授权grant
| 权限类型 | 代表什么 |
|---|---|
| ALL | 所有权限 |
| SELECT | 读取内容的权限 |
| INSERT | 插入权限 |
| update | 更新内容权限 |
| DELETE | 删除内容权限 |
指定要操作的对象db_name.table_name
| 表示方法 | 意义 |
|---|---|
| . | 所有库的所有表 |
| db_name | 指定库的所有表 |
| db_name.table_name | 指定库的指定表 |
WITH GRANT OPTION：被授权的用户可将自己的权限副本转赠给其他用户，就是将自己的权限完全复制给另一个用户。不建议使用
授权chens用户在数据库本机上登录访问所有数据库
mysql> grant all on *.* to 'chens'@'localhost' identified by 'chen';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> grant all on *.* to 'chens'@'127.0.0.1' identified by 'chen';
Query OK, 0 rows affected, 1 warning (0.01 sec)
授权chens 用户在192.168.169.20 上远程登录访问chens数据库
mysql> grant all on chens.* to 'chens'@'192.168.169.20' identified by 'chen';
Query OK, 0 rows affected, 1 warning (0.00 sec)
授权chens 用户在所有位置上远程登录访问chens数据库
mysql> grant all on *.* to 'chens'@'%' identified by 'chen';
Query OK, 0 rows affected, 1 warning (0.00 sec)
查看当前登录用户的授权信息
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
查看指定用户chens的授权信息
mysql> show grants for chens;
+--------------------------------------------+
| Grants for chens@% |
+--------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'chens'@'%' |
+--------------------------------------------+
1 row in set (0.00 sec)
取消授权revoke
mysql> revoke all on *.* from 'chens'@'192.168.169.20';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
注意： MySQL服务进程启动时会读取mysql库中的所有授权表至内存中：
grant 或 revoke 等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表
重读授权表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql avg函数求平均值
MariaDB [chens]> select * from score;
+------------+-----------------+---------+------+---------+
| student_id | name | chinese | Math | English |
+------------+-----------------+---------+------+---------+
| 21001 | jetlee | 90 | 80 | 91 |
| 21002 | jackchan | 80 | 100 | 70 |
| 21003 | Brucelee | 70 | 58 | 90 |
| 21004 | Mlcheal jackson | 60 | 90 | 96 |
+------------+-----------------+---------+------+---------+
求出English 的平均数
MariaDB [chens]> select avg (English) from score;
+---------------+
| avg (English) |
+---------------+
| 86.7500 |
+---------------+
1 row in set (0.00 sec)
把平均使用GROUP BY 子句设置的各种记录，下面例子采取平均到一个人的所有相关记录，每个人的平均页打印纸
MariaDB [chens]> select name,avg (English) from score group by name;
+-----------------+---------------+
| name | avg (English) |
+-----------------+---------------+
| Brucelee | 90.0000 |
| jackchan | 70.0000 |
| jetlee | 91.0000 |
| Mlcheal jackson | 96.0000 |
+-----------------+---------------+
4 rows in set (0.00 sec)
mysql 数据求和
MariaDB [chens]> select sum(chinese+Math+English) as suma from score group by English order by suma desc;
+------+
| suma |
+------+
| 261 |
| 250 |
| 246 |
| 218 |
+------+
4 rows in set (0.00 sec)
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
