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 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‘,‘zisefeizhu@qq.com‘)

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

mysql系列-安装及增删改查

上一篇:sql server如何用不同语种语言显示报错的错误消息


下一篇:关于mysql的事务隔离模式、MVCC、next-key,这一篇就够了