MySQL的开发必会的SQL语句
source link: http://database.51cto.com/art/202004/614471.htm
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
本文谈谈MySQL的开发必会的sql语句
创建数据库
create database db1;
删除数据库
drop database db1;
创建数据表
create table tb1用户表( id int not null auto_increment primary key, name char(10), department_id int, p_id int, )engine=innodb default charset=utf8;
主键(primary key)一个表只能有一个主键,主键可以由一列或者多列组成
外键的创建
CREATE TABLE t5 ( nid int(11) NOT NULL AUTO_INCREMENT, pid int(11) not NULL, num int(11), primary key(nid,pid) --这里就是把两列设置成了主键 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; create table t6( id int auto_increment primary key, name char(10), id1 int, id2 int, CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t1(nid,pid) --这里是设置外键 )engine=innodb default charset=utf8;
数据行的操作
数据的插入
insert into tb1(name,age) values('ax',8); insert into tb12(name,age) select name,age from tb11;
表中的数据的删除
delete from t1; truncate table t1; drop table t1 delete from tb1 where id > 10 delete from tb12 where id >=2 or name='alex'
数据的更新
update tb1 set name='root' where id > 10
数据的查询
select * from tb; select id,name from tb;
表结构的查看
show create table t1; desc t1;
其他
select * from tb12 where id != 1 select * from tb12 where id in (1,5,12); select * from tb12 where id not in (1,5,12); select * from tb12 where id in (select id from tb11) select * from tb12 where id between 5 and 12;
通配符
select * from tb12 where name like "a%" select * from tb12 where name like "a_"
分页
select * from tb12 limit 10; select * from tb12 limit 0,10; select * from tb12 limit 10,10; select * from tb12 limit 20,10; select * from tb12 limit 10 offset 20; # page = input('请输入要查看的页码') # page = int(page) # (page-1) * 10 # select * from tb12 limit 0,10; 1 # select * from tb12 limit 10,10;2
排序
select * from tb12 order by id desc; 大到小 select * from tb12 order by id asc; 小到大 select * from tb12 order by age desc,id desc; 取后10条数据 select * from tb12 order by id desc limit 10;
分组
select count(id),max(id),part_id from userinfo5 group by part_id; 聚合函数有下面几个: count max min sum avg **** 如果对于聚合函数结果进行二次筛选时?必须使用having **** select count(id),part_id from userinfo5 group by part_id having count(id) > 1; select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;
自增值设置
表自增值的设置
alter table t1 auto_increment=20; -- 这个就表示从开始20开始算,用上面的show create table t1\G 就可以看到当前的值是多少。
基于会话级别
-- 查看当前的会话值 show session variables like 'auto_incre%' -- 设置会话步长 set session auto_increment_increment=2; -- 设置起始值 set session auto_increment_offset=10;
基于全局设置
-- 查看全局的设置值 show global variables like 'auto_inc%'; -- 设置全局步长值 set global auto_increment_increment=3; -- 设置起始值 set global auto_increment_offset=11;
sql server 是在创建表的时候就可以自己设置,灵活度很高REATE TABLE t5 (nid int(11) NOT NULL AUTO_INCREMENT,pid int(11) NOT NULL,num int(11) DEFAULT NULL,PRIMARY KEY (nid,pid)) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8
CREATE TABLE `t6` (
nid int(11) NOT NULL AUTO_INCREMENT,pid int(11) NOT NULL,num int(11) DEFAULT NULL,PRIMARY KEY (nid,pid)) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=20 DEFAULT CHARSET=utf8
唯一索引
create table t1( id int, num int, xx int, unique qu1 (num ,xx) -- 意思就是这两列在一行上面数据不能相同,例如都是1,1,就不行 );
唯一索引:约束不能重复(可以为空)主键索引:约束不能重复(不可以为空)他们的特点都是加速查询
外键一对一
create table userinfo1( id int auto_increment primary key, name char(10), gender char(10), email varchar(64) )engine=innodb default charset=utf8; create table admin( id int not null auto_increment primary key, username varchar(64) not null, password VARCHAR(64) not null, user_id int not null, unique uq_u1 (user_id), CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id) )engine=innodb default charset=utf8;
外键多对多
示例1: 用户表 相亲表 示例2: 用户表 主机表 用户主机关系表 ===》多对多 create table userinfo2( id int auto_increment primary key, name char(10), gender char(10), email varchar(64) )engine=innodb default charset=utf8; create table host( id int auto_increment primary key, hostname char(64) )engine=innodb default charset=utf8; create table user2host( id int auto_increment primary key, userid int not null, hostid int not null, unique uq_user_host (userid,hostid), CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id), CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id) )engine=innodb default charset=utf8;
连表操作
select * from userinfo5,department5 select * from userinfo5,department5 where userinfo5.part_id = department5.id select * from userinfo5 left join department5 on userinfo5.part_id = department5.id select * from department5 left join userinfo5 on userinfo5.part_id = department5.id # userinfo5左边全部显示 # select * from userinfo5 right join department5 on userinfo5.part_id = department5.id # department5右边全部显示 select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id 将出现null时一行隐藏 select * from department5 left join userinfo5 on userinfo5.part_id = department5.id left join userinfo6 on userinfo5.part_id = department5.id select score.sid, student.sid from score left join student on score.student_id = student.sid left join course on score.course_id = course.cid left join class on student.class_id = class.cid left join teacher on course.teacher_id=teacher.ti select count(id) from userinfo5;
Recommend
-
9
删除MySQL表的SQL语句-DROP-TABLE-简介
-
4
查看mysql执行sql语句中慢的部分 作者: wencst 分类: Uncategorized,数...
-
5
查看mysql已经执行过的sql语句 作者: wencst 分类: Uncategorized,数据...
-
7
MySQL高级SQL语句 use kgc; create table location (Region char(20),store_name char(20)); insert into location values ('East','Boston') ; insert into location values ('East','New York'); insert into location values ('west','Los...
-
6
以下两个表格做讲解
-
6
MySQL高级(进阶) SQL 语句二 推荐 原创 呼噜呼噜懒洋洋 2022-03-08 22:30:29...
-
4
MySQL高级(进阶) SQL 语句一 原创 呼噜呼噜懒洋洋 2022-03-08 22:07:01...
-
5
MySQL: 一条SQL语句是如何执行的? 2019年10月11日 | 字数 207 |
-
4
用了这么久数据库,却不知道数据库是如何执行我们输入的语句的,经过一番查阅后,记录下执行一条查询和更新SQL语句后,MySQL是如何工作的。 SQL语句基本的执行链路: 1 查询语句是如何执行一条查询语句的执行过程一般是经过连接器、...
-
10
MySQL客户端工具的使用 1、MySQL程序的组成 mysql:CLI交互式客户端程序 mycli:CLI交互式客户端程序;使用sql语句时会有提示信息 mysql_secure_installation:安全...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK