mysql系列安装及增删改查

Z时代
2024-01-10
分类：综合

database

基本介绍
Mysql5.7安装
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;