• 欢迎访问VPS岛网站,国外VPS,国内VPS,国外服务器,国内服务器,服务器主机,测评及优惠码,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站 QQ群

MySQL语句、内连接、外连接、子查询

mysql技术 爱与梦想 16次浏览 已收录 0个评论

视频课程学习:
https://edu.51cto.com//center/course/lesson/index?id=192296

登录mysql:
mysql -u root -p
输入密码:

mysqladmin -u root -p[oldpassword] password newpassword
旧密码可选,没有就为空(了解)

使用Navicat_for_MySQL客户端工具连接mysql
如果无法远程连接,请在虚机中进入mysql命令行,输入如下命令:
GRANT ALL PRIVILEGES ON . TO ‘用户名’@’%’ IDENTIFIED BY ‘密码’ WITH GRANT OPTION;

显示所有的数据库
show databases;

创建数据库fcy
create database fcy default charset=utf8;

删除数据库
drop database db1

切换到fcy数据库
use fcy;

查看所有的表
show tables;

创建表
create table person_info
(
person_id smallint(5) unsigned auto_increment,
name varchar(50) not null comment ‘姓名’,
country varchar(60) default ‘China’,
salary decimal(10,2) default 0.00 comment ‘工资’,
primary key (person_id)
)engine=innodb default charset=utf8;

删除表
drop table person_info123

查看表结构
desc person_info;

插入数据
insert into person_info(name, country, salary) values
(‘xiaoqiang’, ‘China’, 1000.20),
(‘xiaowang’, ‘USA’, 800.10),
(‘xiaozhang’, ‘UK’, 300),
(‘xiaohu’, ‘Canada’, 600.45);

修改表结构–增加字段
alter table person_info add sex VARCHAR(4)

修改表结构–修改字段类型
alter table person_info modify sex INT(4)
alter table person_info change sex sex INT(4)

修改表结构–修改字段名
alter table person_info change sex sex_123 INT(4)

修改表结构–删除字段
alter table person_info drop sex_123

查询
SELECT * from person_info where name = ‘xiao’ and salary = 20

插入
insert into person_info(name, salary) VALUES(‘xiao’, 10)

删除
DELETE from person_info where salary = 20

修改
UPDATE person_info set salary = 20 where name = ‘xiao’

查找
select from person_info where name like ‘%xiaoqiang%’;
select
from person_info where name like ‘xiao%’;
select * from person_info where name like ‘%qiang’;

排序(默认升序asc, 降序desc)
select from person_info order by name;
select
from person_info order by country desc, salary ASC;

统计有多少条记录
select count(*) as totalcount from person_info;

去重
SELECT count(distinct(country)) from person_info;

求和
select sum(salary) as sumvalue from person_info;

平均
select avg(salary) as sumvalue from person_info;

最大
select max(salary) as sumvalue from person_info;

最小
select min(salary) as sumvalue from person_info;

#表连接(内连接
select 表1.列1,表2.列2 from 表1,表2 where 表1.列3 = 表2.列3;

#表外连接
select 表1.列1,表2.列2 from 表1 left join 表2 on 表1.列3 = 表2.列3;
select 表1.列1,表2.列2 from 表1 right join 表2 on 表1.列3 = 表2.列3;

#子查询
select * from 表1 where 列1 in[=] (select A_id from 表2 where 列2 xxx);

#表连接练习:
新建表A
create table A(
id smallint(5) unsigned auto_increment,
name varchar(50) not null,
primary key(id)
)engine=innodb default charset=utf8;

新建表B
create table B(
id smallint(5) unsigned auto_increment,
address varchar(50) not null,
A_id smallint(5) unsigned,
primary key(id)
)engine=innodb default charset=utf8;

插入数据
insert into A (name) values (‘zhang’), (‘li’), (‘wang’);
insert into B (address, A_id) values (‘beijing’,1), (‘shanghai’,3), (‘nanjing’,10);

内连接练习

select A.name, B.address from A, B where A.id = B.A_id;

左连接练习,左表的字段全部显示

select A.name, B.address from A left join B on A.id = B.A_id;

右连接练习,右表的字段全部显示

select A.name, B.address from A right join B on A.id = B.A_id;

子查询练习

select from A where id in (select A_id from B where address=’beijing’);
select
from A where id in (select A_id from B where address=’beijing’ or address=’shanghai’);

推荐使用内连接,相比其他三种,效率要高。

插入四条数据,一次插入,相比四次插入,效率要高。


VPS岛 的文章和资源来自互联网,仅作为参考资料,如果有侵犯版权的资源请尽快联系站长,我们会在24h内删除有争议的资源。丨 转载请注明MySQL语句、内连接、外连接、子查询
喜欢 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址