数据库教程: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 https://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 https://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 

实例

需要了解更多数据库技术:mysql系列-安装及增删改查，都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!

新建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;