mysql操作

MySQL数据库操作指南

最新推荐文章于 2022-11-02 19:42:39 发布

原创最新推荐文章于 2022-11-02 19:42:39 发布 · 443 阅读

1 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

2 篇文章

订阅专栏

MySQL服务器登录、退出–命令行
1. 语法
mysql[.exe] [–h主机名] [–p端口号] –u用户名 –p密码
2. 退出
Exit;quit;\q

数据库操作
1)查看服务器中的所有数据库:show databases;
2)查看数据库的创建语句: show create database db_name;
3)创建数据库:
create database [if not exists] db_name [charset 字符集名称];
这里写图片描述
4)设置字符集：set names gbk;
5)选择数据库:use db_name;————\s可以查看当前选择的数据库
6)删除数据库: drop database;

数据表操作：
1）显示所有数据表：show tables
2）显示表结构：desc tb_name
3）显示数据表创建语句：show create table tb_name;——–通过添加\G 参数，使显示更清晰
4）创建数据表

-- 要求：
-- 1）   要求的字段有：编号、姓名、性别、年龄、学历、爱好、毕业院校、籍贯、访问量、添加时间
-- 2）   编号不能为负数，格式为：0001、0002、0003、0004、……
-- 3）   姓名要求不能相同(唯一键)。
-- 4）   如果不添加年龄，默认值为21
-- 5）   性别使用enum类型
-- 6）   学历使用enum类型
-- 7）   爱好使用set类型
-- 8）   添加日期时间格式为：YYYY-MM-DD hh:mm:ss
-- 9）   访问量，默认值为100。
-- 10）  编号从111开始
-- 11）  要求表的字符集为latin1
-- 12）  使用MyISAM存储引擎
create table stu_info(
id int(4) unsigned zerofill not null primary key auto_increment,
name varchar(4)  charset utf8 not null unique key,
gender enum ('男','女','保密') charset utf8 not null default '保密',
age tinyint unsigned not null default 21,
edu  enum ('初中','高中','大专','大本','研究生') charset utf8 not null default '大本',
hobby set ('阅读','跑步','游戏','编程') charset utf8 not null default '跑步,编程',
school varchar(12) charset utf8,
city varchar(10) charset utf8,
hits int unsigned not null default 100,
add_time timestamp 
) engine =myisam charset =latin1 auto_increment=111 ;

这里写图片描述
微信支付的订单表

CREATE TABLE ‘o2o_order’{
‘id’ int(11) unsigned NOT NULL auto_increment,
‘out_trade_no’ varchar(100) NOT NULL DEFAULT ‘ ’, 订单编号
‘transaction_id’ varchar(100) NOT NULL DEFAULT ‘ ’, 微信支付的编号
‘user_id’ int(11) NOT NULL DEFAULT 0, 用户的id
‘username’ varchar(50) NOT NULL DEFAULT ‘ ’, 用户名
‘pay_time’ varchar(20) NOT NULL DEFAULT ‘ ’,
‘payment_id’ tinyint(1) NOT NULL DEFAULT 1, 支付的方式：1代表微信支付
‘deal_id’ int(11) NOT NULL DEFAULT 0, 商品id
‘deal_count’ int(11) NOT NULL DEFAULT 0, 商品的数量
‘pay_status’ tinyint(1) NOT NULL DEFAULT 1 COMMENT ‘ 支付的状态 0：未支付 1 支付成功’,
‘total_price’ DECIMAL(20,2) NOT NULL DEFAULT ‘0.00’,
‘pay_amount’ DECIMAL(20,2) NOT NULL DEFAULT ‘0.00’,微信支付返回的总额
‘status’ tinyint(1) NOT NULL DEFAULT 1,
‘referer’ varchar(255) NOT NULL DEFAULT ‘ ’,
‘create_time’ int(11) unsigned NOT NULL DEFAULT 0,
‘update_time’ int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (‘id’),
UNIQUE ‘out_trade_no’ (‘out_trade_no’ ),
Key user_id(‘user_id’)
Key create_time(‘create_time’)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET = utf8;
}

修改数据表部分
1）添加字段
– 为stu6数据表添加奖金（位于工资字段之后）及手机号码字段
alter table stu6 add bonus float not null default 200 after salary,add tel char(11) ;
2）修改字段
–修改stu3年龄字段的类型
alter table stu3 modify age tinyint not null default 23;
–修改stu6 电话号码字段的类型及名称
alter table stu6 change tel telphone varchar(32) not null;
3)重表名数据表
alter table 原来数据表名称 rename 新数据表名称;
4)删除数据表
drop table [if exists ]表名;——–同时删除结构及数据
5)复制表结构
create table 新表名 like 源数据表名称;

数据库的基本原子操作
1、插入操作
–王宝强，34岁，男，大本
insert into stu_info (name,age,gender,edu) values (‘王宝强’,34,’男’,4);

–宋仲基，男，21岁，大本，传媒大学,河北省,2300，编程
insert into stu_info values (null,’宋仲基’,’男’,default,’大本’,’编程’,’传媒大学’,’河北省’,2300,now());

–郑容和，27，大专
insert into stu_info set name=’郑容和’,age=27,edu=’大专’;

–同时插入多条语句
–郭靖，23；黄蓉，24；杨过，25；小龙女，26
insert into stu_info (name,age) values (‘郭靖’,23),(‘黄蓉’,24),(‘杨过’,25),(‘小龙女’,26);

–然后反复获取数据表中的数据后插入
insert into wish_list (name,wish) select name,wish from wish_list;

2、更新操作
update student set salary=1600 where name=’苍老师’;
update student set salary=salary+500,bonus=bonus+500 where id=9;

3、删除操作
delete from wish_list where name=’朱同学’ or name=’宋同学’;
–清空student数据表
truncate student ;
–删除student数据表
drop table student;

4、查询操作
select name from student;
select name,age,city from student;
select * from student;
select id,username from itcast.user;
select * from student where city=’山东省’;
select * from student where id>20 and id<30;
select id,name,salary,bonus,salary+bonus as 总工资 from student where salary+bonus>9000;

select * from student where city=’山东省’ or city=’山西省’;

select * from student where age in (23,25,27) and sex=’女’;

select * from student where city not in (‘北京市’,’天津市’,’河北省’,’山西省’,’内蒙古’);

select * from student where salary between 9400 and 9500;
select * from student where city between ‘山东省’ and ‘山西省’;

select * from student where city is null;

select * from student where name like ‘%张%’;

–查询最高工资,最低工资，平均工资，总工资(所有人的基本工资总和 )；
select max(salary) as 最高工资,min(salary) as 最低工资,avg(salary) as 平均工资,
sum(salary) as 所有人总工资 from student;

–查询总人数
select count(*) from student;

–查询男生、女生各自的总人数；
select sex,count(*) from student group by sex;
–查询男生、女生各自的最高工资、最低工资、平均工资、总工资（salary）
select sex,max(salary),min(salary),avg(salary),sum(salary) from student group by sex

–查询男、女生总数（包括姓名）
select name,sex,count(*) from student group by sex;

-平均工资大于10000的‘城市’工资的相关统计信息
select city,avg(salary),max(salary),min(salary),sum(salary) from student
group by city having avg(salary) >10000;

–学号前20的薪水排行榜
select * from student where id<20 order by salary desc;
select * from student where id<20 order by salary asc;

–学号前20，按工资升序、奖金降序排列
select * from student where id<20 order by salary asc,bonus desc;

–省份平均工资前五甲的省份信息
select city,avg(salary) from student group by city order by avg(salary) desc limit 5;

–每页10行，查询数据表中第二页数据
select * from student limit 10,10;

/** 查询华北地区，最低工资大于6000的各省市中，男、女生各自平均工资
(‘河北省’,’北京市’,’天津市’,’内蒙古’,’山西省’)
**/
select city,sex,avg(salary) as 平均工资,min(salary) as 最低工资 from student
where city in (‘河北省’,’北京市’,’天津市’,’内蒙古’,’山西省’)
group by city,sex
having min(salary)>6000 ;

–去除重复行
select distinct name,salary,bonus,salary+bonus as 总工资 from student;

5、连接查询
select cid,apple_prod.id,apple_cat.name,apple_prod.name from apple_cat,apple_prod where apple_cat.id=apple_prod.cid;

inner join
select i.*,salary,bonus from stu_info as i inner join stu_pays as p on i.id=p.id;

–查询女神工资（山东女青年）
select name,city,sex,salary,bonus from stu_info as i inner join stu_pays as p
on i.id=p.id
where city=’山东省’ and sex=’女’;

–查询id<10，工资5000到13000之间的学生信息及其工资
select i.*,salary from stu_info as i inner join stu_pays as p
on i.id=p.id
where i.id<10 and salary between 5000 and 13000;

–三表查询
– 1)查询选修了 MySQL 的学生姓名；
select name,kecheng_name from students as st inner join scores as sc
on st.id=sc.stu_id
inner join majors as ma
on sc.kecheng_id=ma.id
where kecheng_name=’MySQL’;

/**
* left join
*/

–显示作者所发布的文章信息
select username,aid,title from user as u left join article as a
on a.uid=u.id;

/**
* right join
*/
–显示文章信息(标题及作者)
select aid,username,title from user as u right join article as a
on a.uid=u.id;

/**
* 标量子查询
*/
–显示“基本工资”>“平均工资”，且籍贯为“山东省”的学员
select * from student where salary>(select avg(salary) from student) and city=’山东省’;
–高于9号同学工资两倍的同学信息
select * from student where salary >(select 2*salary from student where id=9);
/**
* 列子查询
*/
–查询和洪荒少女、邓超同岁的学生信息
select * from student where age in (select age from student where name in (‘洪荒少女’,’邓超’));

–跟张姓同学同籍贯的学生信息
select * from student where city in (select city from student where name like ‘张%’);

–比宁泽涛，舒淇，邓超其中一位的工资高的学生信息？
select * from student where salary >
any (select salary from student where name in (‘邓超’,’舒淇’,’宁泽涛’));

-比胡歌，舒淇，邓超工资都高的学生信息？
select * from student where salary >
all (select salary from student where name in (‘邓超’,’舒淇’,’胡歌’));

/**
* 行子查询
*/
–跟贾原同岁的老乡的同学信息
select * from student where (age,city)= (select age,city from student where name=’贾原’);

6、联合查询
联合查询语法
格式：
select 语句1
union [all|distinct]
select 语句2
all: 所有的
distinct: 去除重复的，默认是distinct
二者的区别
连接查询：通常将多个数据表的通过公有字段连接，结果集相当于多个数据表的饿字段进行横向的组织。
联合查询：将多个select查询结果进行纵向的拼接。
这里写图片描述

存储引擎（构成、安全、锁）
Myisam：数据操作快速的一种引擎，支持全文检索。文件保存在数据库名称为目录名的

目录中，有3个文件，分别是表定义文件（.frm）、数据文件（.MYD）、索引文件
（.MYI），强调性能，查询效率较高，不支持事务和外键。

Innodb：功能强大的一种引擎，支持事务处理功能，不支持全文检索。文件保存在两个
地方，一个是在数据库名称为目录名的目录中存放表结构文件，它的数据是保存在一个共有的文件中的。

MyISAM支持表锁，而InnoDB支持行锁。

事务
事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中所有操作都
成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，
事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务回滚，
所有操作的影响都将取消。

SQL编程
1）数学函数
Avg(), sum(), max(), min(), count(),abs()
Ceil(), floor(), round()
2）字符串函数
Length(), char_length()
3）日期时间函数
Now(),unix_timestamp()
Date(), time(), curdate(), curtime()
4）其它函数
Md5(),password()

数据管理
/备份数据/
mysqldump.exe -hlocalhost -p3306 -uroot -p9990728 itcast > c:/itcast_dt_str.sql
mysqldump.exe -hlocalhost -p3306 -uroot -p9990728 -d itcast student > c:/itcast_dt_str.sql
/-d（只输出结构）后对应数据库数据表/

/还原数据/
mysql.exe -hlocalhost -p3306 -uroot -p9990728 db1 < c:/itcast_dt_str.sql

直接导入sql文件

用户管理
1)查看服务器用户
select host,user from mysql.user;
这里写图片描述
2)创建用户
CREATE USER ‘username’@’hostname’ [IDENTIFIED BY ‘pass’];
完整的用户信息包括：用户名及主机名，‘用户名’@’主机名’
IDENTIFIED BY ‘pass’：设置用户密码
举例说明：create user ‘admin’@’%’ identified by ‘123456’;（所有的主机）
这里写图片描述
3)设置密码
SET password [FOR ‘username’ @ ‘hostname’ ]= password(‘new_pass’);
举例：set password for ‘admin’@’%’ =password(‘123456’);

4）用户授权
语法：
GRANT 权限列表 ON 目标对象 TO ‘user’ @ ‘hostname’ [with grant option]；
注意：
权限名字可以小写。
目标对象即数据库.数据表，可以使用db.*
[with grant option]，带上之后用户有授权的权限。
这里写图片描述
举例：grant insert,update,select,delete on /数据库/ to ‘admin’@’%’;
5）
语法：
REVOKE 权限列表 ON 目标对象 FROM ‘username’@ ‘hostname’;
举例：revoke insert,update,select,delete on /数据库/ from ‘admin’@’%’;

6）
/删除用户/
drop user ‘admin’@’%’;

在所有的主机都可以通过用户root连接到mysql数据库
你想root用户名使用root密码从任何主机连接到MySQL服务器的话。
运行命令：（此例中用户为root 密码 root）

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;