centos6默认安装MySQL（已安装）
centos7默认mariadb
实验：yum安装
centos6
[root@CentOS6 ~]# service mysqld start #开启服务，端口3306
/usr/bin/mysqladmin -u root password 'new-password' #安全加固命令
/usr/bin/mysqladmin -u root -h CentOS6.localdomain password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation #安全加固脚本（常用）
[root@CentOS6 ~]# mysql #进入（无密码）
mysql>\h #查看帮助命令
mysql>status #查看状态（\s）
mysql> show batabases; #显示数据库（服务器命令用;表示结束，客户端命令不需要）
+--------------------+
| Database |
+--------------------+
| information_schema | #内存中的信息
| mysql |
| test |
+--------------------+
mysql> show tables; #查看该数据库所有表格
mysql> select * from user; #显示user表的所有信息（太乱、显示不全）；可以查询字段再显示内容
mysql> desc user; #显示user表的所有字段
mysql> select host,user,password from user; #查看字段host，user，password的属性
+---------------------+------+----------+
| host | user | password |
+---------------------+------+----------+
| localhost | root | |
| centos6.localdomain | root | | #远程登录
| 127.0.0.1 | root | | #密码为空（安全隐患）
| localhost | | | #用户为空表示任何用户都可登录 mysql -u123可直接登录（并没有123用户）随便写什么都能登录
| centos6.localdomain | | | #太危险需要安全加固，运行mysql_secure_installation
+---------------------+------+----------+
5 rows in set (0.00 sec)
[root@CentOS6 ~]# mysql_secure_installation #安全加固
Enter current password for root (enter for none): #输入密码（输入none或直接回车）
Set root password? [Y/n] y #设置root密码
Remove anonymous users? [Y/n] y #移除嘉宾登录
Disallow root login remotely? [Y/n] y #禁止远程登录
Remove test database and access to it? [Y/n] y #删除test数据库
Reload privilege tables now? [Y/n] y #是否立即生效（前面的操作其实已经生效）
[root@CentOS6 ~]# mysql -pcentos #可直接进入，但显示密码（centos）
[root@CentOS6 ~]# mysql -p #交互式登录，回车后输入密码，密码隐藏
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql | #已删除test
+--------------------+
mysql> select user,host,password from user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED | #已加密，删除其他用户
| root | 127.0.0.1 | *128977E278358FF80A246B5046F51043A2B1FCED |
+------+-----------+-------------------------------------------+
[root@CentOS7 ~]# mysql -uroot -pcentos -h192.168.8.6 #已不能远程登录
ERROR 1130 (HY000): Host '192.168.8.7' is not allowed to connect to this MySQL server
[root@CentOS6 ~]# mysqladmin -pcentos ping #显示数据库是否活跃
mysqld is alive
[root@CentOS6 ~]# mysqladmin ping -p
Enter password:
mysqld is alive
[root@CentOS6 ~]# mysqladmin -pcentos shutdown #关闭数据库
[root@CentOS6 ~]# service mysqld start #开启
[root@CentOS6 ~]# mysqladmin password centos #添加密码（开始密码为空）
[root@CentOS6 ~]# mysqladmin -pcentos password magedu #修改新密码
[root@CentOS6 ~]# mysqladmin -pmagedu creat db1 #创建数据库db1
[root@CentOS6 ~]# mysql -e "show databases" -p #不登录数据库显示数据库信息
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 | #已创建DB1
| mysql |
+--------------------+
[root@CentOS6 ~]# cat test.sql #由于MySQL是交互式命令，可以以下方式实现
show databases;
use mysql;
select user,host,password from mysql.user;
[root@CentOS6 ~]# mysql -pmagedu< test.sql #第一种
[root@CentOS6 ~]# cat test.sql |mysql -pmagedu #第二种
##选择文件时区分大小写，而表格内容是不区分大小写
centos7
[root@CentOS7 ~]#yum install mariadb-server #安装
[root@CentOS7 ~]# rpm -qi mariadb-server 系统安装版本太低，需要建立yum仓库在安装
Version : 5.5.65
官网找到相应版本进入找到RPM Package点击use the Repository Configuration Tool.进入选取相应版本，复制制作repo仓库
[root@CentOS7 ~]# vim /etc/yum.repos.d/aliyun.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
[root@CentOS7 ~]# yum install mariadb-server
实验：二进制安装
二进制安装为完成编译过程后进行的部分，官网下载已编译完成的软件包进行安装
[root@CentOS7 ~]# mkdir /data/mysql #第一步
[root@CentOS7 ~]# useradd -r -s /sbin/nologin -d /data/mysql mysql #第二步：创建用户及组，默认家目录为/data/mysql
[root@CentOS7 ~]# tar xvf mariadb-10.2.25-linux-x86_64.tar.gz -C /usr/local #3解压到/usr/local
[root@CentOS7 ~]# du -sh /usr/local/
1.5G /usr/local/
[root@CentOS7 /usr/local]# ln -s mariadb-10.2.25-linux-x86_64/ mysql #创建软连接（编译生成的文件）
[root@CentOS7 /usr/local]# chown -R mysql.mysql mysql/ #修改用户和组权限
[root@CentOS7 /usr/local]# cd mysql/ #进入mysql（后面跟源码编译编译完成后步骤一致）
[root@CentOS7 /usr/local/mysql]# ./scripts/./mysql_install_db --datadir=/data/mysql --user=mysql #复制安装脚本
[root@CentOS7 /usr/local/mysql]# cp support-files/my-huge.cnf /etc/mysql/my.cnf #复制启动配置
[root@CentOS7 /usr/local/mysql]# vim /etc/mysql/my.cnf #编辑配置
[mysqld]
datadir = /data/mysql
[root@CentOS7 /usr/local/mysql]# cp support-files/mysql.server /etc/init.d/mysqld #复制命令
[root@CentOS7 /usr/local/mysql]# chkconfig --add mysqld #查看开机服务裂口
[root@CentOS7 /usr/local/mysql]# service mysqld start #重启
[root@CentOS7 /usr/local/mysql]# echo 'PATH=/usr/local/mysql/bin:$PATH'> #修订path浪费时间 /etc/profile.d/mysql.sh
[root@CentOS7 /usr/local/mysql]# mysql
实验：源码编译安装
#####做准备用户和数据目录#####
[root@CentOS7 ~]# yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel libdb4-cxx-devel #安装环境
[root@CentOS7 ~]# useradd -r -s /sbin/nologin -d /data/mysql mysql
[root@CentOS7 ~]# mkdir /data/mysql
[root@CentOS7 ~]# chown mysql.mysql /data/mysql
[root@CentOS7 ~]# tar xvf mariadb-10.2.25.tar.gz
[root@CentOS7 ~]# cd mariadb-10.2.25/
#################################################编译开始
[root@CentOS7 ~/mariadb-10.2.25]#cmake .\
-DCMAKE_INSTALL_PREFIX=/app/mysql \
-DMYSQL_DATADIR=/data/mysql/ \
-DSYSCONFDIR=/etc/ \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
[root@CentOS7 ~/mariadb-10.2.25]# make -j 8 && make install
#############################################编译完成
[root@CentOS7 ~/mariadb-10.2.25]# cd /app/mysql/
[root@CentOS7 /app/mysql]# ./scripts/mysql_install_db --datadir=/data/mysql --user=mysql #生成数据库
[root@CentOS7 /app/mysql]# cp support-files/my-huge.cnf /etc/my.cnf #准备配置文件
[root@CentOS7 /app/mysql]# cp support-files/mysql.server /etc/init.d/mysqld #准备启动脚本
[root@CentOS7 /app/mysql]# chkconfig --add mysqld #启动服务
[root@CentOS7 /app/mysql]# service mysqld start
[root@CentOS7 /app/mysql]# echo 'PATH=/app/mysql/bin:$PATH' > /etc/profile.d/mysql.sh #准备环境变量
[root@CentOS7 /app/mysql]# . /etc/profile.d/mysql.sh
[root@CentOS7 /app/mysql]# mysql #启动
实验：三实列（3个数据库软件）安装
[root@CentOS7 ~]# vim /etc/yum.repos.d/aliyun.repo #修改yum仓库
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
[root@CentOS7 ~]# yum install mariadb-server #安装
[root@CentOS7 ~]# mkdir /mysql/{3306,3307,3308}/{data,bin,log,socket,pid,etc} -pv
[root@CentOS7 ~]# chown -R mysql.mysql /mysql/
[root@CentOS7 ~]# mysql_install_db --datadir=/mysql/3306/data --user=mysql
[root@CentOS7 ~]# cp /etc/my.cnf /mysql/3306/etc/
[root@CentOS7 ~]# vim /mysql/3306/etc/my.cnf
[mysqld]
port=3306
datadir=/mysql/3306/data/
socket=/mysql/3306/socket/mysql.sock
[mysqld_safe]
log-error=/mysql/3306/log/mariadb.log
pid-file=/mysql/3306/pid/mariadb.pid
[root@CentOS7 ~]# mysql_install_db --datadir=/mysql/3307/data --user=mysql
[root@CentOS7 ~]# mysql_install_db --datadir=/mysql/3308/data --user=mysql
[root@CentOS7 ~]# cp /mysql/3306/etc/my.cnf /mysql//3307/etc/
[root@CentOS7 ~]# cp /mysql/3306/etc/my.cnf /mysql//3308/etc/
[root@CentOS7 ~]# sed -i 's/3306/3307/' /mysql/3307/etc/my.cnf
[root@CentOS7 ~]# sed -i 's/3306/3308/' /mysql/3308/etc/my.cnf
####准备启动脚本
[root@CentOS7 ~]# cd /mysql/3306/bin
[root@CentOS7 /mysql/3306/bin]# vim mysqld #线下上传脚本
port=3306
mysql_user="root"
mysql_pwd="centos"
cmd_path="/usr/bin" #修改可以ps aux查看mysqld_safe的路径（启动服务）
mysql_basedir="/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"
[root@CentOS7 /mysql/3306/bin]# chmod +x mysqld
[root@CentOS7 /mysql/3306/bin]# cp mysqld /mysql/3307/bin/
[root@CentOS7 /mysql/3306/bin]# cp mysqld /mysql/3308/bin/
[root@CentOS7 /mysql/3306/bin]# sed -i 's/3306/3307/' /mysql/3307/bin/mysqld
[root@CentOS7 /mysql/3306/bin]# sed -i 's/3306/3308/' /mysql/3308/bin/mysqld
#####
[root@CentOS7 /mysql/3306/bin]# /mysql/3306/bin/mysqld start #启动服务
[root@CentOS7 /mysql/3306/bin]# mysql -S /mysql/3306/socket/mysql.sock #进入数据库（空口令）
[root@CentOS7 ~]# mysqladmin -S /mysql/3306/socket/mysql.sock password centos #修改密码
[root@CentOS7 ~]# mysqladmin mysql -S /mysql/3306/socket/mysql.sock -p #进入数据库（密码）
[root@CentOS7 ~]# /mysql/3306/bin/mysqld stop #关闭服务
字符集
推荐使用utf8mb4
MariaDB [(none)]> show charset set #查看支持字符集
MariaDB [(none)]> show create database test2; #显示test2数据库属性
MariaDB [(none)]> create database test3 character set utf8mb4; #创建test3（设置字符集为utf8mb4）
[root@CentOS7 /var/local]# cat /var/lib/mysql/test2/db.opt #字符集保存配置文件
default-character-set=latin1
default-collation=latin1_swedish_ci
MariaDB [test]> show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 | 客户端
| character_set_connection | utf8 |
| character_set_database | latin1 | 数据库
| character_set_filesystem | binary | 字段
| character_set_results | utf8 |
| character_set_server | latin1 | 服务器
| character_set_system | utf8 | 系统
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
[root@CentOS7 ~]# vim /etc/my.cnf #配置服务器端默认字符集（以后新建全部为默认utf8mb4）
[mysqld]
character_set_server=utf8mb4
[root@CentOS7 ~]# mysql --default-character-set=utf8mb4 #临时修改客户端字符集登录，退出消失
[root@CentOS7 ~]# vim /etc/my.cnf.d/mysql-clients.cnf #永久修改客户端字符集
[mysql]
default-character-set=utf8mb4
DDL
DDL: Data Defination Language 数据定义语言（表创建与修改）
CREATE，DROP，ALTER
MariaDB [(none)]> help create help后跟命令可以查看该命令后面命令的帮助
MariaDB [(none)]> show databases; #显示数据库
MariaDB [(none)]> create database test2; #创建
MariaDB [(none)]> show create database test2; #显示属性
MariaDB [(none)]> drop database test2; #删除数据库（实际是删除/var/lib/mysql/test2/目录）
MariaDB [(none)]> use test;
MariaDB [test]> create table student (id smallint unsigned auto_increment primary key,name varchar(20) not null,gender enum('M','F') default 'M',mobile char(11) not null); #在test中创建表student（第一种方式）
MariaDB [test]> desc student; #显示表student的字段
+--------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| gender | enum('M','F') | YES | | M | |
| mobile | char(11) | NO | | NULL | |
+--------+----------------------+------+-----+---------+----------------+
MariaDB [test]> show create table student; #显示表student的字段所有属性
MariaDB [test]> show indexes from student; #显示表student的索引
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
MariaDB [test]> use test3;
Database changed
MariaDB [test3]> create table student2 like test.student; #创建表单（第二种方式）只是复制test.student属性
MariaDB [test3]> alter table student2 change name name varchar(30) character set utf8mb4; #修改student2字段name的默认字符集及字段长度
MariaDB [test3]> alter table student2 change mobile mobile char(20);
MariaDB [test6]> create table student select * from test.student; #创建（第三种方法）复制test.student属性及内容，相当于批量导入
MariaDB [test6]> alter table student change name name varchar(20 )character set utf8mb4; #改字符集
MariaDB [test6]> insert student (name,mobile) values ("王",'123000'),("张丽",'11325'); #插入
MariaDB [test6]> select * from student; #查询
+----+---------+--------+-------------+
| id | name | gender | mobile |
+----+---------+--------+-------------+
| 1 | wang | M | 13880912480 |
| 2 | zhang | M | 18006 |
| 3 | zhangli | M | 1862414 |
| 0 | 王 | M | 123000 |
| 0 | 张丽 | M | 11325 |
+----+---------+--------+-------------+
DML
DML: Data Manipulation Language 数据操纵语言 （内容创建修改删除）
INSERT，DELETE，UPDATE
数字型后面加unsigned表示为正数.
char类型的字符串检索速度要比varchar类型的快
.varchar类型的字符串占用空间要比char类型的小
MariaDB [test]> insert student (name,mobile) values ('wang','13880912480'),('zhang','18006'); #插入数据
MariaDB [test]> select * from student; #查询
+----+-------+--------+-------------+
| id | name | gender | mobile |
+----+-------+--------+-------------+
| 1 | wang | M | 13880912480 |
| 2 | zhang | M | 18006 |
+----+-------+--------+-------------+
MariaDB [test]> insert student (name,mobile) values ('zhangli','1862414') #插入（方法一常用）
[root@CentOS7 ~]# mysql <hellodb_innodb.sql #hellodb_innodb.sql本地上传，导入数据库，生成测试表
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> insert students set name='mage',age=30,gender='M'; #插入（方法二不常用）
MariaDB [hellodb]> insert students (name,age) select name,age from teachers; #插入（方法三，插入查询数据）
MariaDB [hellodb]> update students set ClassID=1 where StuID=25; #修改数据
##where必须添加，否则会修改所有字段（危险），为防止不添加where修改数据，安全起见可以跟-U登录或者更改客户端配置
[root@CentOS7 ~]# mysql -U
MariaDB [(none)]> update students set ClassID=1; #-U登录，不添加where报错
[root@CentOS7 ~]# vim /etc/my.cnf.d/mysql-clients.cnf
[mysql]
safe-update
[root@CentOS7 ~]# mysql
MariaDB [(none)]> update students set ClassID=1; #配置修改后，不带选项登录后修改报错
MariaDB [hellodb]> delete from students where StuID>20; #删除大于20的，不带清空全部内容，速度慢（会写入日志）TRUNCATE table students 删除速度更快（不写入日志）
DQL
DQL：Data Query Language 数据查询语言（重点掌握）
SELECT
字段显示可以使用别名：
col1 AS alias1, col2 AS alias2, …
WHERE子句：指明过滤条件以实现“选择”的功能：
过滤条件：布尔型表达式
算术操作符：+, -, *, /, %
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
BETWEEN min_num AND max_num
IN (element1, element2, …)
IS NULL
IS NOT NULL
DISTINCT 去除重复列
SELECT DISTINCT gender FROM students;
LIKE:
% 任意长度的任意字符
_ 任意单个字符
RLIKE：正则表达式，索引失效，不建议使用
REGEXP：匹配字符串可用正则表达式书写模式，同上
逻辑操作符：
NOT
AND
OR
XOR
GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
avg(), max(), min(), count(), sum()
HAVING: 对分组聚合运算后的结果指定过滤条件
ORDER BY: 根据指定的字段对查询结果进行排序
升序：ASC
降序：DESC
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制
对查询结果中的数据请求施加“锁”
FOR UPDATE: 写锁，独占或排它锁，只有一个读和写
LOCK IN SHARE MODE: 读锁，共享锁，同时多个读
单表查询
MariaDB [hellodb]> select 'hello';
+-------+
| hello |
+-------+
| hello |
+-------+
MariaDB [hellodb]> select 2 * 3 ;
+-------+
| 2 * 3 |
+-------+
| 6 |
+-------+
MariaDB [hellodb]> select * from students; #查询所有
MariaDB [hellodb]> select stuid,name,age from students; #查询部分
+-------+---------------+-----+
| stuid | name | age |
+-------+---------------+-----+
MariaDB [hellodb]> desc students; #显示表字段
MariaDB [hellodb]> select name as 姓名,age 年龄, stuid from students; #字段用相应别名显示
+---------------+--------+-------+
| 姓名 | 年龄 | stuid |
+---------------+--------+-------+
MariaDB [hellodb]> select name as 姓名,age 年龄, stuid from students as 学生表; #表用别名表示，后面有用
MariaDB [hellodb]> select * from students where stuid >=20; #显示stuid号大于20的内容
MariaDB [hellodb]> select * from students where stuid =20; #显示stuid号大于20的内容
MariaDB [hellodb]> select * from students where stuid <>20; #显示stuid号不等于20的内容
MariaDB [hellodb]> select * from students where stuid !=20; #显示stuid号不等于20的内容（不是SQL标准语言，使用较少）
MariaDB [hellodb]> select * from students where name = 'Yu Yutong'; #显示特定姓名
MariaDB [hellodb]> select * from students where age>=20 and gender='f'; #显示年龄大于等于20且为女
MariaDB [hellodb]> select *from students where age>=20 and age<=30; #查询20-30
MariaDB [hellodb]> select *from students where age between 20 and 30; #查询20-30
MariaDB [hellodb]> select *from students where name like 's%'; #查询姓名s开头
MariaDB [hellodb]> select *from students where name like '%yu%'; #查询姓名中间含义yu的
MariaDB [hellodb]> select *from students where name rlike '^s'; #查询姓名s开头（正则表达式，不常用）
MariaDB [hellodb]> select distinct age from students; #年龄合并查询（只显示一条，不显示重复的）
MariaDB [hellodb]> select *from students where classid is null; #显示classid为空字符串
MariaDB [hellodb]> select *from students where classid is not null; #显示classid为非空字符串
MariaDB [hellodb]> select count(stuid) as 记录数 from students; #查询统计数
+-----------+
| 记录数 |
+-----------+
| 20 |
+-----------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select count(teacherid) as 记录数 from students; #查询统计数（默认不包括空字符串）
+-----------+
| 记录数 |
+-----------+
| 5 |
+-----------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select count(*) as 记录数 from students; #查询统计数（一般使用、总有字段为非空）
+-----------+
| 记录数 |
+-----------+
| 20 |
+-----------+
MariaDB [hellodb]> select max(age) as 年龄 from students; #查最大
MariaDB [hellodb]> select min(age) as 年龄 from students; #查最小
MariaDB [hellodb]> select avg(age) as 年龄 from students; #查平均
MariaDB [hellodb]> select gender,avg(age) as 平均数 from students group by gender; #查男女年龄平均数
+--------+-----------+
| gender | 平均数 |
+--------+-----------+
| F | 18.5000 |
| M | 28.7500 |
+--------+-----------+
MariaDB [hellodb]> select classid,avg(age) as 平均数 from students group by classid having classid>3; #查班级大于3每班年龄平均数（having在后）
MariaDB [hellodb]> select classid,avg(age) as 平均数 from students where classid>3 group by classid; #查班级大于3每班年龄平均数（where在前）
+---------+-----------+
| classid | 平均数 |
+---------+-----------+
| 4 | 25.3333 |
| 5 | 46.0000 |
| 6 | 20.3333 |
| 7 | 19.6667 |
+---------+-----------+
MariaDB [hellodb]> select classid,avg(age) as 平均数 from students where classid>3 group by classid having 平均数>30; # #查班级大于3年龄平均数大于30
+---------+-----------+
| classid | 平均数 |
+---------+-----------+
| 5 | 46.0000 |
+---------+-----------+
MariaDB [hellodb]> select * from students group by classid,gender; #根据班级，性别查询，只显示每个不同的第一条
MariaDB [hellodb]> select classid,gender,avg(age) from students group by classid,gender; #根据班级，性别查询，只显示平均数
MariaDB [hellodb]> select *from students order by age; #按年龄正序排列（从小到大）
MariaDB [hellodb]> select *from students order by age asc; #按年龄正序排列（从小到大）
MariaDB [hellodb]> select *from students order by age desc; #按年龄倒序排列（从大到小）
MariaDB [hellodb]> select *from students order by -teacherid desc; #按教师编号正序排列（从小到大）（空字符串排除在外，否则默认空字符串排前）
MariaDB [hellodb]> select classid,sum(age) from students group by classid order by classid; #查询班级总年龄并按班级排序
MariaDB [hellodb]> select classid,sum(age) from students group by classid having classid is not null order by classid; ##查询班级总年龄（班级不为空）并按班级排序
MariaDB [hellodb]> select classid,sum(age) from students where classid is not null group by classid order by classid; ##查询班级（班级不为空）总年龄并按班级排序（先排除）
MariaDB [hellodb]> select classid,sum(age) from students where classid is not null group by classid order by classid limit 3; #显示前3行
MariaDB [hellodb]> select classid,sum(age) from students where classid is not null group by classid order by classid limit 2,3; #排除前2行，显示3-5行
MariaDB [hellodb]> select *from students where classid=1 or classid=3 or classid=5; #显示1、3、5班
MariaDB [hellodb]> select *from students where classid in (1,3,5); #显示1、3、5班
实验：SQL注入
一般情况下，用户密码存放于数据库中，输入用户名密码查询出即登录成功，但黑客可以通过一些方式也可以查询出结果，进而登录系统
MariaDB [hellodb]> create table users(id int,username char(30),password char(30)); #创建表
MariaDB [hellodb]> insert users values (1,'admin','magedu'),(2,'mage','magedu'),(2,'wang','centos'); #添加用户信息
MariaDB [hellodb]> select *from users ;
+------+----------+----------+
| id | username | password |
+------+----------+----------+
| 1 | admin | magedu |
| 2 | mage | magedu |
| 2 | wang | centos |
+------+----------+----------+
MariaDB [hellodb]> select *from users where username='admin' and password='magedu'; #当用户名和密码匹配出查询结果
当黑客输入用户：admin，密码：' or '1'='1也能查询，是一个SQL的安全隐患，成为SQL注入，现阶段的MySQL软件已解决相关问题，但也可以注意一下
MariaDB [hellodb]> select *from users where username='admin' and password='' or '1'='1';
+------+----------+----------+
| id | username | password |
+------+----------+----------+
| 1 | admin | magedu |
| 2 | mage | magedu |
| 2 | wang | centos |
+------+----------+----------+
当黑客输入用户：admin'--，密码：'也能查询，
MariaDB [hellodb]> select *from users where username='admin'--' and password=''';
+------+----------+----------+
| id | username | password |
+------+----------+----------+
| 1 | admin | magedu |
| 2 | mage | magedu |
| 2 | wang | centos |
+------+----------+----------+
多表查询
纵向查询
MariaDB [hellodb]> select stuid,name,age,gender from students union select *from teachers;
##相同字段内容会合并
+-------+---------------+-----+--------+
| stuid | name | age | gender |
+-------+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M |
| 2 | Shi Potian | 22 | M |
| 3 | Xie Yanke | 53 | M |
| 4 | Ding Dian | 32 | M |
MariaDB [hellodb]> select stuid,name,age,gender from students union all select *from teachers;
##相同字段内容不会合并
+-------+---------------+-----+--------+
| stuid | name | age | gender |
+-------+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M |
| 2 | Shi Potian | 22 | M |
MariaDB [hellodb]> select *from users;
+------+----------+----------+
| id | username | password |
+------+----------+----------+
| 1 | admin | magedu |
| 2 | mage | magedu |
| 2 | wang | centos |
| 2 | wang | centos |
+------+----------+----------+
MariaDB [hellodb]> select *from users union select *from users; #去重
+------+----------+----------+
| id | username | password |
+------+----------+----------+
| 1 | admin | magedu |
| 2 | mage | magedu |
| 2 | wang | centos |
+------+----------+----------+
MariaDB [hellodb]> select distinct *from users; #去重
+------+----------+----------+
| id | username | password |
+------+----------+----------+
| 1 | admin | magedu |
| 2 | mage | magedu |
| 2 | wang | centos |
+------+----------+----------+
横向查询
教材连接（笛卡尔乘积）
成倍增加
MariaDB [hellodb]> select * from users cross join teachers;
+------+----------+----------+-----+---------------+-----+--------+
| id | username | password | TID | Name | Age | Gender |
+------+----------+----------+-----+---------------+-----+--------+
| 1 | admin | magedu | 1 | Song Jiang | 45 | M |
| 2 | mage | magedu | 1 | Song Jiang | 45 | M |
| 2 | wang | centos | 1 | Song Jiang | 45 | M |
| 2 | wang | centos | 1 | Song Jiang | 45 | M |
| 1 | admin | magedu | 2 | Zhang Sanfeng | 94 | M |
| 2 | mage | magedu | 2 | Zhang Sanfeng | 94 | M |
| 2 | wang | centos | 2 | Zhang Sanfeng | 94 | M |
| 2 | wang | centos | 2 | Zhang Sanfeng | 94 | M |
| 1 | admin | magedu | 3 | Miejue Shitai | 77 | F |
| 2 | mage | magedu | 3 | Miejue Shitai | 77 | F |
| 2 | wang | centos | 3 | Miejue Shitai | 77 | F |
| 2 | wang | centos | 3 | Miejue Shitai | 77 | F |
| 1 | admin | magedu | 4 | Lin Chaoying | 93 | F |
| 2 | mage | magedu | 4 | Lin Chaoying | 93 | F |
| 2 | wang | centos | 4 | Lin Chaoying | 93 | F |
| 2 | wang | centos | 4 | Lin Chaoying | 93 | F |
+------+----------+----------+-----+---------------+-----+--------+
内连接inner join
MariaDB [hellodb]> select * from students inner join teachers on students.teacherid=teachers.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
MariaDB [hellodb]> select * from students inner join teachers on teacherid=tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
MariaDB [hellodb]> select stuid,s.name,s.age,tid,t.name from students as s inner join teachers as t on s.teacherid=t.tid; #正确书写格式
+-------+-------------+-----+-----+---------------+
| stuid | name | age | tid | name |
+-------+-------------+-----+-----+---------------+
| 5 | Yu Yutong | 26 | 1 | Song Jiang |
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying |
+-------+-------------+-----+-----+---------------+
MariaDB [hellodb]> select stuid,s.name studentname,s.age,tid,t.name teachername from students as s inner join teachers as t on s.teacherid=t.tid; #别名显示
+-------+-------------+-----+-----+---------------+
| stuid | studentname | age | tid | teachername |
+-------+-------------+-----+-----+---------------+
| 5 | Yu Yutong | 26 | 1 | Song Jiang |
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying |
+-------+-------------+-----+-----+---------------+
MariaDB [hellodb]> select stuid,s.name,s.age,tid,t.name from students s,teachers t where s.teacherid=t.tid; #以前老式方法
+-------+-------------+-----+-----+---------------+
| stuid | name | age | tid | name |
+-------+-------------+-----+-----+---------------+
| 5 | Yu Yutong | 26 | 1 | Song Jiang |
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying |
+-------+-------------+-----+-----+---------------+
MariaDB [hellodb]> select stuid,s.name studentname,s.age,tid,t.name teachername from students as s inner join teachers as t on s.teacherid=t.tid and s.age>30; #过滤年龄大于30
+-------+-------------+-----+-----+--------------+
| stuid | studentname | age | tid | teachername |
+-------+-------------+-----+-----+--------------+
| 4 | Ding Dian | 32 | 4 | Lin Chaoying |
+-------+-------------+-----+-----+--------------+
左边没有显示完
左外连接left outer join
MariaDB [hellodb]> select stuid,s.name,s.age,tid,t.name from students as s left outer join teachers as t on s.teacherid=t.tid;
+-------+---------------+-----+------+---------------+
| stuid | name | age | tid | name |
+-------+---------------+-----+------+---------------+
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai |
| 2 | Shi Potian | 22 | NULL | NULL |
| 3 | Xie Yanke | 53 | NULL | NULL |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying |
| 5 | Yu Yutong | 26 | 1 | Song Jiang |
| 6 | Shi Qing | 46 | NULL | NULL |
| 7 | Xi Ren | 19 | NULL | NULL |
| 8 | Lin Daiyu | 17 | NULL | NULL |
| 9 | Ren Yingying | 20 | NULL | NULL |
| 10 | Yue Lingshan | 19 | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | NULL | NULL |
| 12 | Wen Qingqing | 19 | NULL | NULL |
| 13 | Tian Boguang | 33 | NULL | NULL |
| 14 | Lu Wushuang | 17 | NULL | NULL |
| 15 | Duan Yu | 19 | NULL | NULL |
| 16 | Xu Zhu | 21 | NULL | NULL |
| 17 | Lin Chong | 25 | NULL | NULL |
| 18 | Hua Rong | 23 | NULL | NULL |
| 19 | Xue Baochai | 18 | NULL | NULL |
| 20 | Diao Chan | 19 | NULL | NULL |
| 34 | xiaom | 20 | NULL | NULL |
+-------+---------------+-----+------+---------------+
MariaDB [hellodb]> select stuid,s.name,s.age,tid,t.name from students as s left outer join teachers as t on s.teacherid=t.tid where t.tid is null; #左外连接扩展
+-------+---------------+-----+------+------+
| stuid | name | age | tid | name |
+-------+---------------+-----+------+------+
| 2 | Shi Potian | 22 | NULL | NULL |
| 3 | Xie Yanke | 53 | NULL | NULL |
| 6 | Shi Qing | 46 | NULL | NULL |
| 7 | Xi Ren | 19 | NULL | NULL |
| 8 | Lin Daiyu | 17 | NULL | NULL |
| 9 | Ren Yingying | 20 | NULL | NULL |
| 10 | Yue Lingshan | 19 | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | NULL | NULL |
| 12 | Wen Qingqing | 19 | NULL | NULL |
| 13 | Tian Boguang | 33 | NULL | NULL |
| 14 | Lu Wushuang | 17 | NULL | NULL |
| 15 | Duan Yu | 19 | NULL | NULL |
| 16 | Xu Zhu | 21 | NULL | NULL |
| 17 | Lin Chong | 25 | NULL | NULL |
| 18 | Hua Rong | 23 | NULL | NULL |
| 19 | Xue Baochai | 18 | NULL | NULL |
| 20 | Diao Chan | 19 | NULL | NULL |
| 34 | xiaom | 20 | NULL | NULL |
+-------+---------------+-----+------+------+
18 rows in set (0.00 sec)
MariaDB [hellodb]> select stuid,s.name,s.age,tid,t.name from students as s left outer join teachers as t on s.teacherid=t.tid where t.tid is not null;
+-------+-------------+-----+-----+---------------+
| stuid | name | age | tid | name |
+-------+-------------+-----+-----+---------------+
| 5 | Yu Yutong | 26 | 1 | Song Jiang |
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying |
+-------+-------------+-----+-----+---------------+
右外连接right outer join
右边显示完全
MariaDB [hellodb]> select stuid,s.name,s.age,tid,t.name from students as s right outer join teachers as t on s.teacherid=t.tid;
+-------+-------------+------+-----+---------------+
| stuid | name | age | tid | name |
+-------+-------------+------+-----+---------------+
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying |
| 5 | Yu Yutong | 26 | 1 | Song Jiang |
| NULL | NULL | NULL | 2 | Zhang Sanfeng |
+-------+-------------+------+-----+---------------+
完全外连接union
左外连接union右外连接（两张表都全部显示，没有部分空字符串）
MariaDB [hellodb]> select stuid,s.name,s.age,tid,t.name from students as s left outer join teachers as t on s.teacherid=t.tid
-> union
-> select stuid,s.name,s.age,tid,t.name from students as s right outer join teachers as t on s.teacherid=t.tid;
+-------+---------------+------+------+---------------+
| stuid | name | age | tid | name |
+-------+---------------+------+------+---------------+
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai |
| 2 | Shi Potian | 22 | NULL | NULL |
| 3 | Xie Yanke | 53 | NULL | NULL |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying |
| 5 | Yu Yutong | 26 | 1 | Song Jiang |
| 6 | Shi Qing | 46 | NULL | NULL |
| 7 | Xi Ren | 19 | NULL | NULL |
| 8 | Lin Daiyu | 17 | NULL | NULL |
| 9 | Ren Yingying | 20 | NULL | NULL |
| 10 | Yue Lingshan | 19 | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | NULL | NULL |
| 12 | Wen Qingqing | 19 | NULL | NULL |
| 13 | Tian Boguang | 33 | NULL | NULL |
| 14 | Lu Wushuang | 17 | NULL | NULL |
| 15 | Duan Yu | 19 | NULL | NULL |
| 16 | Xu Zhu | 21 | NULL | NULL |
| 17 | Lin Chong | 25 | NULL | NULL |
| 18 | Hua Rong | 23 | NULL | NULL |
| 19 | Xue Baochai | 18 | NULL | NULL |
| 20 | Diao Chan | 19 | NULL | NULL |
| 34 | xiaom | 20 | NULL | NULL |
| NULL | NULL | NULL | 2 | Zhang Sanfeng |
+-------+---------------+------+------+---------------+
子查询
MariaDB [hellodb]> select *from students where age>(select avg(age) from students); #大于平均年龄
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
+-------+--------------+-----+--------+---------+-----------+
MariaDB [hellodb]> update students set age=(select avg(age) from teachers) where stuid=20; #修改编号20的年龄为平均数
MariaDB [hellodb]> select *from students where stuid=20;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
| 20 | Diao Chan | 77 | F | 7 | NULL |
自连接
MariaDB [hellodb]> create table emp(id int,name char(20),leaderid int);
MariaDB [hellodb]> insert emp values (1,'mage',null),(2,'zhang',1),(3,'wang',2),(4,'li',3);
MariaDB [hellodb]> select *from emp;
+------+-------+----------+
| id | name | leaderid |
+------+-------+----------+
| 1 | mage | NULL |
| 2 | zhang | 1 |
| 3 | wang | 2 |
| 4 | li | 3 |
+------+-------+----------+
MariaDB [hellodb]> select e.name,l.name leadername from emp as e left outer join emp as l on e.leaderid=l.id; #as可省略
+-------+------------+
| name | leadername |
+-------+------------+
| zhang | mage |
| wang | zhang |
| li | wang |
| mage | NULL |
+-------+------------+
MariaDB [hellodb]> select e.name,l.name leadername from emp e left outer join emp l on e.leaderid=l.id;
+-------+------------+
| name | leadername |
+-------+------------+
| zhang | mage |
| wang | zhang |
| li | wang |
| mage | NULL |
+-------+------------+
三表查询
MariaDB [hellodb]> select st.name,co.course,sc.score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian | 77 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Kuihua Baodian | 47 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Xie Yanke | Weituo Zhang | 75 |
| Ding Dian | Daiyu Zanghua | 71 |
| Ding Dian | Kuihua Baodian | 89 |
| Yu Yutong | Hamo Gong | 39 |
| Yu Yutong | Dagou Bangfa | 63 |
| Shi Qing | Hamo Gong | 96 |
| Xi Ren | Hamo Gong | 86 |
| Xi Ren | Dagou Bangfa | 83 |
| Lin Daiyu | Taiji Quan | 57 |
| Lin Daiyu | Jinshe Jianfa | 93 |
+-------------+----------------+-------+
视图view
虚拟表格，相当于命令的别名（工作中不使用）
MariaDB [hellodb]> create view view_test as (select st.name,co.course,sc.score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid);
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| emp |
| scores |
| students |
| teachers |
| toc |
| users |
| view_test |
+-------------------+
10 rows in set (0.01 sec)
MariaDB [hellodb]> select * from view_test;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian | 77 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Kuihua Baodian | 47 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Xie Yanke | Weituo Zhang | 75 |
| Ding Dian | Daiyu Zanghua | 71 |
| Ding Dian | Kuihua Baodian | 89 |
| Yu Yutong | Hamo Gong | 39 |
| Yu Yutong | Dagou Bangfa | 63 |
| Shi Qing | Hamo Gong | 96 |
| Xi Ren | Hamo Gong | 86 |
| Xi Ren | Dagou Bangfa | 83 |
| Lin Daiyu | Taiji Quan | 57 |
| Lin Daiyu | Jinshe Jianfa | 93 |
+-------------+----------------+-------+
MariaDB [hellodb]> show table status like 'view_test'\G #查询表格状态
Comment: VIEW
MariaDB [hellodb]> create view view_oldstudents as select *from students where age>30; #创建
MariaDB [hellodb]> select *from view_oldstudents; #查询
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 20 | Diao Chan | 77 | F | 7 | NULL |
+-------+--------------+-----+--------+---------+-----------+
MariaDB [hellodb]> insert view_oldstudents values(25,'xiaoming',40,'F',3,null); #插入
MariaDB [hellodb]> drop view view_test; #删除视图
##使用视图插入时，大于30的查询能查到，小于30的查不到（实际已经写入），会给用户造成困惑，因此工作中不用
自定义函数
系统函数和自定义函数
在mysql.proc中
MariaDB [mysql]> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "hello world"; #创建
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> select simpleFun(); #调用
+-------------+
| simpleFun() |
+-------------+
| hello world |
+-------------+
1 row in set (0.00 sec)
MariaDB [mysql]> show function status; #查看函数列表
MariaDB [mysql]> SHOW CREATE FUNCTION simpleFun; #查看函数定义
MariaDB [mysql]> DROP FUNCTION simpleFun; #删除函数
Query OK, 0 rows affected (0.00 sec)
存储过程
流程控制
触发器
用户管理
权限管理
存储引擎
MyISAM引擎特点
不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键约束
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发
崩溃恢复性较差
MySQL5.5.5前默认的数据库引擎
MyISAM存储引擎适用场景
只读（或者写较少）、表较小（可以接受长时间进行修复操作）
MyISAM引擎文件
tbl_name.frm 表格式定义
tbl_name.MYD 数据文件
tbl_name.MYI 索引文件
InnoDB引擎特点
行级锁
支持事务，适合处理大量短期事务
读写阻塞与事务隔离级别相关
可缓存数据和索引
支持聚簇索引
崩溃恢复性更好
支持MVCC高并发
从MySQL5.5后支持全文索引
从MySQL5.5.5开始为默认的数据库引擎
InnoDB数据库文件
所有InnoDB表的数据和索引放置于同一个表空间中
表空间文件：datadir定义的目录下
数据文件：ibddata1, ibddata2, …
每个表单独使用一个表空间存储表的数据和索引
启用：innodb_file_per_table=ON（5.5后默认为on）
两类文件放在数据库独立目录中
数据文件(存储数据和索引)：tb_name.ibd
表格式定义：tb_name.frm
MySQL中的系统数据库
performance_schema数据库
MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表
information_schema数据库
MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information_schema数据库类似与“数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）
服务器配置
获取mysqld的可用选项列表：
mysqld --help --verbose
mysqld --print-defaults 获取默认设置
设置服务器选项方法：
在命令行中设置
shell> ./mysqld_safe --skip-name-resolve=1
在配置文件my.cnf中设置
skip_name_resolve=1
服务器系统变量：分全局和会话两种
获取系统变量
mysql> SHOW GLOBAL VARIABLES;
mysql> SHOW [SESSION] VARIABLES;
mysql> SELECT @@VARIABLES;
修改服务器变量的值：
mysql> help SET
修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效
mysql> SET GLOBAL system_var_name=value;
mysql> SET @@global.system_var_name=value;
修改会话变量：
mysql> SET [SESSION] system_var_name=value;
mysql> SET @@[session.]system_var_name=value;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
