mysql系列-安装及增删改查
程序员文章站
2022-07-09 19:23:14
[TOC] 基本介绍 MySQL是一种关系型数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性 mysql就是一个基于socket编写的C/S架构的软件。 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;
上一篇: 【计算机系统设计】 数据通路设计思想
下一篇: mysql安装简易版