>>与软件开发有关的知识:操作系统,数据库,网络通信等 书籍支持  卫琴直播  品书摘要  在线测试  资源下载  联系我们
发表一个新主题 开启一个新投票 回复文章 您是本文章第 21691 个阅读者 刷新本主题
 * 贴子主题:  MySQL的开发必会的SQL语句 回复文章 点赞(0)  收藏  
作者:flybird    发表时间:2024-04-16 00:35:40     消息  查看  搜索  好友  邮件  复制  引用

创建数据库

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;

----------------------------
原文链接:https://blog.51cto.com/sgk2011/2051641


程序猿的技术大观园:www.javathinker.net
  Java面向对象编程-->集合(上)
  JavaWeb开发-->Web运作原理(Ⅰ)
  JSP与Hibernate开发-->域对象在持久化层的四种状态
  Java网络编程-->RMI框架
  精通Spring-->组合(Composition)API
  Vue3开发-->绑定表单
  一文读懂非关系型数据库(NoSQL)
  数据库建立索引的原则
  我必须得告诉大家的MySQL优化原理
  DNS原理及其解析过程
  谈谈网络通信中的 ACK、NACK 和 REX
  Linux下网络流量实时监控工具大全
  MySQL的UPDATE更新语句的用法
  MySQL 删除数据表
  DTD 验证
  DTD中声明元素
  Mysql提升效率的神器: 索引
  数据库设计(一)——数据库设计-生命不息,奋斗不止
  深入认识二进制序列化--记一次生产事故的思考
  TCP三次握手和四次挥手以及11种状态
  Mysql并发保证数据一致性——实例
  更多...
 IPIP: 已设置保密
楼主      
1页 0条记录 当前第1
发表一个新主题 开启一个新投票 回复文章


中文版权所有: JavaThinker技术网站 Copyright 2016-2026 沪ICP备16029593号-2
荟萃Java程序员智慧的结晶,分享交流Java前沿技术。  联系我们
如有技术文章涉及侵权,请与本站管理员联系。