mysql系列-安装及增删改查

基本介绍

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性

mysql就是一个基于socket编写的C/S架构的软件。

Mysql5.7安装

MySQL数据库的线上环境安装，建议采取编译安装的方式，这样性能会有较大的提升。源码包的编译参数会默认以Debug模式生成二进制代码，而Debug模式给MySQL带来的性能损失是比较大的，所以当我们编译准备安装的产品代码时，一定不要忘记使用--without-debug参数禁止Debug模式。如果把--with-mysqld-ldflags和--with-client-ld-flags两个编译参数设置为--all-static的话，可以告诉编译器以静态的方式编译，编译结果将得到最高的性能。使用静态编译和使用动态编译的代码相比，性能差距可能会达到5%至10%之多。

卸载系统自带的 mysql和mariadb-lib
/bin/rpm -e $(/bin/rpm -qa | grep mysql|xargs) --nodeps
rpm：未给出要擦除的软件包
/bin/rpm -e $(/bin/rpm -qa | grep mariadb|xargs) --nodeps

安装编译代码需要的包
yum -y install make gcc-c++ cmake bison-devel ncurses-devel openssl-devel

安装boost
mkdir -p /usr/local/boost
cd /usr/local/boost
wget http://www.sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz
tar -zvxf boost_1_59_0.tar.gz

编译安装mysql5.7.28
groupadd mysql
useradd -g mysql mysql -M -s /sbin/nologin
cd /usr/local/src
wget -c http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-5.7/mysql-5.7.28.tar.gz 
tar -zxvf mysql-5.7.28.tar.gz
cd mysql-5.7.28/
/usr/bin/cmake -DCMAKE_INSTALL_PREFIX=/data/mysql  -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci  -DWITH_BOOST=/usr/local/boost -DMYSQL_DATADIR=/data/mysql/data -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1
make && make install

修改/data/mysql权限
mkdir -p /data/mysql/data
chown -R mysql:mysql /data/mysql
chown -R mysql:mysql /data/mysql/data

执行初始化配置脚本，创建系统自带的数据库和表
 /data/mysql/bin/mysqld --basedir=/data/mysql --datadir=/data/mysql/data --user=mysql --initialize
MySQL 5.7初始化完后会生成一个临时的密码，A temporary password is generated for : gB.7ZgUskpf.

如果想初始化表空间，在后面加上 --innodb_data_file_path=ibdata1:1G:autoextend即可

配置my.cnf
# cat /data/mysql/my.cnf
[client]
port = 3306
socket = /data/mysql/var/mysql.sock
   
[mysqld]
port = 3306
socket = /data/mysql/var/mysql.sock
   
basedir = /data/mysql/
datadir = /data/mysql/data
pid-file = /data/mysql/data/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1
sync_binlog=1
log_bin = mysql-bin
   
skip-name-resolve
#skip-networking
back_log = 600
   
max_connections = 3000
max_connect_errors = 3000
##open_files_limit = 65535
table_open_cache = 512
max_allowed_packet = 16M
binlog_cache_size = 16M
max_heap_table_size = 16M
tmp_table_size = 256M
   
read_buffer_size = 1024M
read_rnd_buffer_size = 1024M
sort_buffer_size = 1024M
join_buffer_size = 1024M
key_buffer_size = 8192M
   
thread_cache_size = 8
   
query_cache_size = 512M
query_cache_limit = 1024M
   
ft_min_word_len = 4
   
binlog_format = mixed
expire_logs_days = 30
   
log_error = /data/mysql/data/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/data/mysql-slow.log
   
performance_schema = 0
explicit_defaults_for_timestamp
   
##lower_case_table_names = 1
   
skip-external-locking
   
default_storage_engine = InnoDB
[mysqldump]
quick

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
port = 3306


启动mysql服务
cd /data/mysql
mkdir var
chown -R mysql.mysql var
cp support-files/mysql.server /etc/init.d/mysqld
chkconfig mysqld on
service mysqld start

设置环境变量
echo "export PATH=$PATH:/data/mysql/bin" >> /etc/profile
source  /etc/profile

设置mysql登陆密码,初始密码为gB.7ZgUskpf.
mkdir -p /var/lib/mysql
ln -s /data/mysql/var/mysql.sock /var/lib/mysql/mysql.sock

修改密码
MySQL从5.7开始不允许首次安装后默认使用空密码进行登录！并且mysql5.7之后的数据库里mysql.user表里已经没有password这个字段了，
password字段改成了authentication_string。
mysql登录密码忘记，其实解决办法很简单，只需要在mysql的主配置文件my.cnf里添加一行“跳过授权表”的参数选择即可。
vim /data/mysql/my.cnf   添加
skip-grant-tables        //跳过授权表
service mysqld restart
mysql
	select host,user,authentication_string from mysql.user;
	update mysql.user set authentication_string=password("123456") where host="localhost" and user="root";
	flush privileges;
再次将my.cnf里添加的那一行注释，然后重启mysql
mysql -uroot -p
		123456
	alter user ‘root‘@‘localhost‘ identified by ‘123456‘;

Mysql常用操作

数据库

查看数据库: 
	show databases;
创建数据库：
	create database db1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; #创建带字符集和校验的utf8编码,强烈建议使用utf8
使用数据库:     
	use db1;
删除数据库：
	DROP DATABASE db1;
修改数据库编码：
	ALTER DATABASE db1 character set gb2312;
查看创建数据库的语句：
	show create database databasename;
查看创建表的语句：
	show create table tablename;
查看表结构：
	desc tablenmae;

用户

创建用户
   create user ‘用户名‘@‘IP地址‘ identified by ‘密码‘;
删除用户
   drop user ‘用户名‘@‘IP地址‘;
修改用户
   rename user ‘用户名‘@‘IP地址‘; to ‘新用户名‘@‘IP地址‘;;
修改密码
   set password for ‘用户名‘@‘IP地址‘ = Password(‘新密码‘)
查看MYSQL数据库中所有用户
	SELECT DISTINCT CONCAT(‘User: ‘‘‘,user,‘‘‘@‘‘‘,host,‘‘‘;‘) AS query FROM mysql.user;
查看当前所有MySQL用户的username、host、密码（注意MySQL从某个版本开始，密码字段不是password，而是authentication_string）
	select User,Host,authentication_string from mysql.user;
查看数据库中具体某个用户的权限
	show grants for ‘cactiuser‘@‘%‘;   
	select * from mysql.user where user=‘cactiuser‘ \G   

查看user表结构　需要具体的项可结合表结构来查询
	desc mysql.user;

说明：CREATE USER ‘username‘@‘host‘ IDENTIFIED BY ‘password‘;
	username：你将创建的用户名
	host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
	password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

例子
CREATE USER ‘zisefeizhu‘@‘localhost‘ IDENTIFIED BY ‘123456‘;
CREATE USER ‘zisefeizhu‘@‘20.0.0.101‘ IDENDIFIED BY ‘123456‘;
CREATE USER ‘zisefeizhu‘@‘%‘ IDENTIFIED BY ‘123456‘;
CREATE USER ‘zisefeizhu‘@‘%‘ IDENTIFIED BY ‘‘;
CREATE USER ‘zisefeizhu‘@‘%‘;

权限

查看权限：
	show grants for ‘用户‘@‘IP地址‘ 
授权：
	grant 权限 on 数据库.表 to ‘用户‘@‘IP地址‘
取消授权：
	revoke 权限 on 数据库.表 from ‘用户‘@‘IP地址‘
	
命令:
	GRANT privileges ON databasename.tablename TO ‘username‘@‘host‘
说明:
	privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
	databasename：数据库名
	tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*

例子:
	GRANT SELECT, INSERT ON test.user TO ‘zisefeizhu‘@‘%‘;
	GRANT ALL ON *.* TO ‘zisefeizhu‘@‘%‘;
	GRANT ALL ON db1.* TO ‘zisefeizhu‘@‘%‘;


注意:
用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:
GRANT privileges ON databasename.tablename TO ‘username‘@‘host‘ WITH GRANT OPTION;

表

查看表
	show tables;                    # 查看数据库全部表
	select * from 表名;             # 查看表所有内容
创建表
	create table 表名(
    	列名  类型  是否可以为空，
    	列名  类型  是否可以为空
	)ENGINE=InnoDB DEFAULT CHARSET=utf8

实例详解
	CREATE TABLE `tab1` (
  	`nid` int(11) NOT NULL auto_increment,    # not null表示不能为空,auto_increment表示自增
  	`name` varchar(255) DEFAULT zisefeizhu,   # default 表示默认值
  	`email` varchar(255),
  	PRIMARY KEY (`nid`)                       # 把nid列设置成主键
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
注：
	默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
	自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列）注意：		1、对于自增列，必须是索引（含主键）
		2、对于自增可以设置步长和起始值
	主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。

删除表
	drop table 表名
清空表内容
	delete from 表名
	truncate table 表名
修改表
	添加列：   
		alter table 表名 add 列名 类型
	删除列：   
		alter table 表名 drop column 列名
	修改列：
    alter table 表名 modify column 列名 类型;  -- 类型
    alter table 表名 change 原列名 新列名 类型; -- 列名，类型
添加主键：
  alter table 表名 add primary key(列名);
删除主键：
  alter table 表名 drop primary key;
  alter table 表名  modify  列名 int, drop primary key;
添加外键： 
	alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除外键： 
	alter table 表名 drop foreign key 外键名称
修改默认值：
	ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：
	ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
	
例子：
	create table zisefeizhu
    -> (
    ->  id int,
    ->  name varchar(40),
    ->  sex char(4),
    ->  birthday date,
    ->  job varchar(100)
    -> );
表中增加image字段：
	alter table zisefeizhu add image blob;
修改job值，使其长度为60（原长度为100）：
	alter table zisefeizhu modify job varchar(60);
删除sex列：
	alter table zisefeizhu drop sex;
表名改为user（原名zisefeizhu）:
	rename table zisefeizhu to user;
修改表的字符集为utf-8：
	alter table user character set utf8;
列name修改为username：
	alter table user change column name username varchar(100);
删除表：
	drop table user;

表内容

增
	insert into 表 (列名,列名...) values (值,值,...)
	insert into 表 (列名,列名...) values (值,值,...),(值,值,值...)
	insert into 表 (列名,列名...) select (列名,列名...) from 表
例：
	insert into tab1(name,email) values(‘zisefeizhu‘,‘‘)

删
	delete from 表                                      # 删除表里全部数据
	delete from 表 where id＝1 and name＝‘zisefeizhu‘   # 删除ID =1 和name=‘zhangyanlin‘ 那一行数据
改
	update 表 set name ＝ ‘zhangyanlin‘ where id>14
查
	select * from 表
	select * from 表 where id > 1
	select nid,name,gender as gg from 表 where id > 1
	条件判断where
    select * from 表 where id > 1 and name != ‘aylin‘ and num = 12;
    select * from 表 where id between 5 and 16;
    select * from 表 where id in (11,22,33)
    select * from 表 where id not in (11,22,33)
    select * from 表 where id in (select nid from 表)
	通配符like
    select * from 表 where name like ‘zise%‘  #zise开头的所有（多个字符串）
    select * from 表 where name like ‘zi_‘  # zi开头的所有（一个字符）
	限制limit
    select * from 表 limit 5;            - 前5行
    select * from 表 limit 4,5;          - 从第4行开始的5行
    select * from 表 limit 5 offset 4    - 从第4行开始的5行
	排序asc，desc
    select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
	分组group by
    select num from 表 group by num
    select num,nid from 表 group by num,nid
    select num,nid from 表  where nid > 10 group by num,nid order nid desc
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
    select num from 表 group by num having max(id) > 10
	特别的：group by 必须在where之后，order by之前

增删改查语句

增删改查的语句命令为：
	增：insert
	删：delete
	改：update
	查：SELECT或者show

实例

新建user表：
	create table user
    -> (
    ->  id int,
    ->  name varchar(40),
    ->  sex varchar(4),
    ->  birthday date,
    ->  entry_date date,
    ->  salary decimal(8,2),
    ->  resume text
    -> );

插入数据：
	insert into user(id,name,sex,birthday,entry_date,salary,resume) values(1,‘zisefeizhu‘,‘male‘,‘1997-08-20‘,‘2020-05-04‘,‘5000‘,‘i am a container_devOps‘);
指定某些列插入数据：
	insert into user(id) values(2);
	insert into user(id,name) values(6,‘紫色飞猪‘);
修改表数据：
	将所有员工薪水修改为6000元：
		update user set salary=6000;
	将姓名为’zisefeizhu’的员工薪水修改为9000元：
		update user set salary=9000 where name=‘zisefeizhu‘;
	将姓名为’紫色飞猪’的员工薪水修改为4000元,resume改为love jingxing：
		update user set salary=4000,resume=‘love jingxing‘ where name=‘紫色飞猪‘;
	将zisefeizhu的薪水在原有基础上增加1000元：
		update user set salary=salary+1000 where name=‘zisefeizhu‘;
查询表数据库：
	查询表中所有员工的信息：
		select * from user;
	查询表中所有员工的姓名和对于的薪水：
		select name,salary from  user;
	查询姓名为紫色飞猪的员工的信息：
		select * from user where name=‘紫色飞猪‘;
	查询薪水大于9000的员工信息:
		select * from user where salary > 9000;
	查询薪水在9000-12000之间的员工信息：
  	select * from user where salary between 9000 and 12000;
删除表数据：
	删除表中名称为"zisefeizhu"的记录：
		delete from user where name=‘zisefeizhu‘;
	删除表中所有记录：
		delete from user;