mysql系列-安裝及增刪改查-有解無憂

[TOC]

基本介紹

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 root@localhost: 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','[email protected]')

刪
	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;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/48518.html

標籤：MySQL

上一篇：MySQL5.6升級5.7步驟

下一篇：資料庫學習之一：資料庫介紹