澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

mySQL数据库操作语句

2019-06-23 作者:数据库网络   |   浏览(136)

1、表字段的操作
1、语法 :alter table 表名 执行动作;
2、添加字段(add)
alter table 表名 add 字段名 数据类型;
alter table 表名 add 字段名 数据类型 first;
alter table 表名 add 字段名 数据类型 after 字段名;
3、删除字段(drop)
alter table 表名 drop 字段名;
4、修改数据类型(modify)
alter table 表名 modify 字段名 新数据类型;
5、表重命名(rename)
alter table 表名 rename 新表名;
6、练习
1、创建库 studb2
2、在库中创建表 t1 ,字段有3个:name、age、phnumber
use studb2;
create table t1(
name char(20),
age tinyint unsigned,
phnumber char(11)
);
3、查看表结构
desc t1;
4、在表中第一列添加一个 id 字段
alter table t1 add id int first;
5、把 phnumber 的数据类型改为 bigint
alter table t1 modify phnumber bigint;
6、在表中最后一列添加一个字段 address
alter table t1 add address varchar(50);
7、删除表中的 age 字段
alter table t1 drop age;
8、查看表结构
desc t1;
2、数据类型
1、数值类型
2、字符类型
1、字符类型宽度和数值类型宽度的区别
1、数值类型宽度为显示宽度,只用于select查询显示,和占用存储无关,可用zerofill查看效果
2、字符类型的宽度超过之后则无法存储
3、枚举类型
1、单选(enum) :字段名 enum(值1,值2,...)
2、多选(set) :字段名 set(值1,值2,...)
## 插入记录时: "F,study,Python"
create table t5(
id int(3) zerofill,
name varchar(15),
sex enum("M","F","Secret"),
likes set("F","M","study","Python")
);
insert into t5(likes) values("F,study,Python");
4、日期时间类型
1、date :"YYYY-MM-DD"
2、time :"HH:MM:SS"
3、datetime :"YYYY-MM-DD HH:MM:SS"
4、timestamp :"YYYY-MM-DD HH:MM:SS"
5、注意
1、datetime :不给值默认返回NULL值
2、timestamp :不给值默认返回系统当前时间
create table t7(
id int,
name varchar(15),
birthday date,
money int,
shijian datetime
);
insert into t7 values(2,"王",date(now()),10000,now());
3、日期时间函数
1、now() 返回服务器当前时间
2、curdate() 返回当前日期
3、curtime() 返回当前时间
4、year(date) 返回指定时间的年份
5、date(date) 返回指定时间的日期
6、time(date) 返回指定时间的时间
7、练习
1、在表中插入3条记录
insert into t7 values
(3,"小昭",19000520,3000,20180630000000),
(4,"赵敏",19000521,4000,20180702000000),
(5,"周芷若",19010522,3500,20180702100000);
2、查找2018年7月2日有哪些用户充值了
select * from t7 where date(shijian)="2018-07-02";

认识mysql第三篇,发出的内容适合初学者,如果能持续关注我的博客,可以全面的掌握mysql的常用知识,后续我也会陆续发出python相关的知识,关注我,和我一共进步吧!

    此前在校学习期间,只是知道数据库很重要,但是并未主动去学习了解。现在的学习过程中学了一遍mysql,就简单的做一个总结吧。

1.显示所有数据库

今日是MySQL的第四篇,难度会稍微加大,加油!

3、查找2018年7月份充值的信息
select * from t7
where
date(shijian)>="2018-07-01" and date(shijian)<="2018-07-31";

1、SQL查询
  1、执行顺序
    3、select ...聚合函数 from 表名
      1、where ...
      2、group by ...
      4、having ...
      5、order by ...
      6、limit ...
  2、group by
    1、作用 :给查询结果进行分组
    2、示例
      1、查询表中一共有几个国家

    首先记住三个概念:

show databases;//展示所有数据库

开始吧!

4、查找7月30日10:00-12:00充值的信息
select * from t7
where
date(shijian)="2018-07-31" and
time(shijian)>="10:00:00" and
time(shijian)<="12:00:00";
4、日期时间运算
1、语法格式
select * from 表名
where 字段名 运算符 (时间-interval 时间间隔单位);
时间间隔单位:
1 day | 2 hour | 1 minute | 2 year | 3 month
2、练习
1、查询1天以内的记录
select * from t7
where shijian > (now()-interval 1 day);
age > 20
2、查询1年以前的记录
select * from t7
where shijian < (now()-interval 1 year);
3、查询1天以前,3天以内的记录
select * from t7
where
shijian < (now()-interval 1 day) and
shijian > (now()-interval 3 day);
5、表记录管理
1、删除表记录
1、delete from 表名 where 条件;
2、注意
delete语句后如果不加where条件,所有记录全部清空
2、更新表记录
1、update 表名 set 字段1=值1,字段2=值2,... where 条件;
2、注意
必须加where条件
3、练习(表hero)
1、查找所有蜀国人的信息
select * from hero where country="蜀国";
2、查找所有女英雄的姓名、性别和国家
select name,sex,country from hero
where sex="女";
3、把id为2的记录改为典韦,性别男,国家魏国
update hero set name="典韦",sex="男",country="魏国" where id=2;
4、删除所有蜀国英雄
delete from hero where country="蜀国";
5、把貂蝉的国籍改为魏国
update hero set country="魏国"
where name="貂蝉";
6、删除所有表记录
delete from hero;
4、运算符操作
1、数值比较/字符比较
1、数值比较 := != > >= < <=
2、字符比较 := !=
3、练习
1、查找攻击力高于150的英雄的名字和攻击值
select name,gongji from sanguo where gongji>150;
2、将赵云的攻击力设置为360,防御力设置为68
update sanguo set gongji=360,fangyu=68
where name="赵云";
2、逻辑比较
1、and (两个或多个条件同时成立)
2、or (任意一个条件成立即可)
3、练习
1、找出攻击值高于200的蜀国英雄的名字、攻击力
select name as n,gongji as g from sanguo
where gongji>200 and country="蜀国";
2、将吴国英雄中攻击值为110的英雄的攻击值改为100,防御力改为60
update sanguo set gongji=100,fangyu=60
where country="吴国" and gongji=110;
3、查找蜀国和魏国的英雄信息
select * from sanguo
where country="蜀国" or country="魏国";
3、范围内比较
1、between 值1 and 值2
2、where 字段名 in(值1,值2,...)
3、where 字段名 not in(值1,值2,...)
4、练习
1、查找攻击值100-200的蜀国英雄信息
select * from sanguo
where gongji between 100 and 200 and
country="蜀国";
2、找到蜀国和吴国以外的国家的女英雄信息
select * from sanguo
where country not in("蜀国","吴国")
and sex="女";
3、找到id为1、3或5的蜀国英雄 和 貂蝉的信息
select * from sanguo
where
(id in(1,3,5) and country="蜀国") or name="貂蝉";
4、匹配空、非空
1、空 :where name is null
2、非空:where name is not null
3、示例
1、姓名为NULL值的蜀国女英雄信息
select * from sanguo
where
name is null and country="蜀国" and sex="女";
2、姓名为 "" 的英雄信息
select * from sanguo where name="";
4、注意
1、NULL :空值,只能用 is 或者 is not 去匹配
2、"" :空字符串,用 = 或者 != 去匹配
5、模糊比较
1、where 字段名 like 表达式
2、表达式
1、_ : 匹配单个字符
2、% : 匹配0到多个字符
3、示例
select name from sanguo where name like "_%_";
select name from sanguo where name like "%";
## NULL不会被统计,只能用is、is not去匹配
select name from sanguo where name like "___";
select name from sanguo where name like "赵%";
5、SQL查询
1、总结
3、select ...聚合函数 from 表名
1、where ...
2、group by ...
4、having ...
5、order by ...
6、limit ...;
2、order by
1、给查询结果进行排序
2、... order by 字段名 ASC/DESC
3、升序:ASC(默认)
降序:DESC
4、示例
1、将英雄按防御值从高到低排序

      2、计算每个国家的平均攻击力
      select country,avg(gongji) from sanguo
      group by country;
    3、查找所有国家中英雄数量最多的前2名的 国家名称和英雄数量
      select country,count(id) as number from sanguo
      group by country
      order by number desc
      limit 2;
3、注意
  1、group by之后的字段名必须要为select之后的字段名
  2、如果select之后的字段名和group by之后的字段不一致,则必须对该字段进行聚合处理(聚合函数)
  3、having语句
1、作用
对查询的结果进行进一步筛选
2、示例
  1、找出平均攻击力>105的国家的前2名,显示国家名和平均攻击力
  select country,avg(gongji) as pjgj from sanguo
  group by country
  having pjgj>105
  order by pjgj DESC
  limit 2;
3、注意
  1、having语句通常和group by语句联合使用,过滤由group by语句返回的记录集
  2、where只能操作表中实际存在字段,having可操作由聚合函数生成的显示列
4、distinct
  1、作用 :不显示字段重复值
  2、示例
1、表中都有哪些国家
  select distinct country from sanguo;
2、计算蜀国一共有多少个英雄
  select count(distinct id) from sanguo
  where country="蜀国";
3、注意
  1、distinct和from之间所有字段都相同才会去重
  2、distinct不能对任何字段做聚合处理
5、查询表记录时做数学运算
1、运算符
   - * / %
2、示例
1、查询时所有英雄攻击力翻倍
select id,name,gongji*2 as gj from sanguo;

    1.数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。   

2,创建数据管理系统
create database 数据库名字;

1、外键(foreign  key)

2、将蜀国英雄按攻击值从高到低排序

## sudo apt-get install python3-pip
## sudo pip3 install pymysql
2、约束
  1、作用 :保证数据的完整性、一致性、有效性
2、约束分类
  1、默认约束(default)
  1、插入记录,不给该字段赋值,则使用默认值
2、非空约束(not NULL)
  1、不允许该字段的值有NULL记录
  sex enum("M","F","S") not null defalut "S"

    2.SQL :结构化查询语言(Structured Query Language)

3,删除数据库
drop database 数据库名字;

  1.定义:让当前表字段的值在另一个表的范围内选择

3、将魏蜀两国英雄中名字为三个字的按防御值升序排列
select * from sanguo
where
country in("蜀国","魏国") and name like "___"
order by fangyu ASC;

3、索引
  1、定义
  对数据库表的一列或多列的值进行排序的一种结构(Btree方式)
  2、优点
  加快数据检索速度
3、缺点
  1、占用物理存储空间
  2、当对表中数据更新时,索引需要动态维护,降低数据维护速度
4、索引示例
  1、开启运行时间检测 :set profiling=1;
  2、执行查询语句
  select name from t1 where name="lucy99999";
3、查看执行时间
  show profiles;
4、在name字段创建索引
  create index name on t1(name);
5、再执行查询语句
  select name from t1 where name="lucy88888";
6、查看执行时间
  show profiles;
5、索引
1、普通索引(index)
1、使用规则
1、可设置多个字段
2、字段值无约束
3、key标志 :MUL
2、创建index
1、创建表时
  create table 表名(...
  index(字段名),index(字段名));
2、已有表
  create index 索引名 on 表名(字段名);
  create index name on t3(name);
3、查看索引
  1、desc 表名; --> KEY标志为:MUL
  2、show index from 表名G;
  4、删除索引
  drop index 索引名 on 表名;
2、唯一索引(unique)
  1、使用规则
    1、可设置多个字段
    2、约束 :字段值不允许重复,但可为 NULL
    3、KEY标志 :UNI
    2、创建
    1、创建表时创建
    unique(字段名),
    unique(字段名)
2、已有表
  create unique index 索引名 on 表名(字段名);
3、查看、删除 同普通索引
3、主键索引(primary key)
自增长属性(auto_increment,配合主键一起使用)
1、使用规则
1、只能有一个主键字段
2、约束 :不允许重复,且不能为NULL
3、KEY标志 :PRI
4、通常设置记录编号字段id,能唯一锁定一条记录
2、创建
1、创建表时
  (id int primary key auto_increment,
  )auto_increment=10000;##设置自增长起始值
已有表添加自增长属性:
  alter table 表名 modify id int auto_increment;
已有表重新指定起始值:
  alter table 表名 auto_increment=20000;
2、已有表
  alter table 表名 add primary key(id);
3、删除
1、删除自增长属性(modify)
  alter table 表名 modify id int;
2、删除主键索引
  alter table 表名 drop primary key;
4、外键索引
4、数据导入
1、作用 :把文件系统的内容导入到数据库中
2、语法
load data infile "/var/lib/mysql-files/文件名"
into table 表名
fields terminated by "分隔符"
lines terminated by "n";
3、将scoretable.csv文件导入到数据库的表中
1、在数据库中创建对应的表
create table scoretab(
id int,
name varchar(15),
score float(5,2),
number bigint,
class char(7)
);
2、把文件拷贝到数据库的默认搜索路径中
1、查看默认搜索路径
  show variables like "secure_file_priv";
  /var/lib/mysql-files/
2、拷贝文件
  sudo cp ~/scoretable.csv /var/lib/mysql-files/
3、执行数据导入语句
load data infile "/var/lib/mysql-files/scoretable.csv"
into table scoretab
fields terminated by ","
lines terminated by "n";
4、文件权限
rwxrw-rw- 1 tarena tarena scoretable.csv
所有者 所属组
rwx : tarena用户
rw- : 同组其他用户
rw- : 其他组的其他用户(mysql用户)

    3.MySQL:关系型数据库管理系统

4,使用数据库
use 数据库名字;-->database changed;

  2.语法:

select * from sanguo
where
(country="魏国" or country="蜀国") and name like "___"
order by fangyu;
3、limit (永远放在SQL语句的最后写)
1、作用 :限制显示查询记录的个数
2、用法
1、limit n -> 显示 n 条记录
2、limit m,n
m 表示 从第m 1条记录开始显示,显示 n 条
limit 2,3 : 第 3、4、5 三条记录
3、示例
1、在蜀国英雄中,查找防御值倒数第二名至倒数第四名的英雄的记录
select * from sanguo
where country="蜀国"
order by fangyu asc
limit 1,3;
2、在蜀国英雄中,查找攻击值前3名且名字不为 NULL 的英雄的姓名、攻击值和国家
select name,gongji,country from sanguo
where
country="蜀国" and name is not NULL
order by gongji DESC
limit 3;
4、分页
每页显示5条记录,显示第4页的内容

r -> 4
w -> 2
x -> 1
chmod 644 文件名 rw-r--r--
5、Excel表格如何转化为CSV文件
1、打开Excel文件 -> 另存为 -> CSV(逗号分隔)
6、更改文件编码格式
1、用记事本/编辑器 打开,文件->另存为->选择编码
5、数据导出
1、作用
将数据库中表的记录导出到系统文件里
2、语法格式
select ... from 表名
into outfile "/var/lib/mysql-files/文件名"
fields terminated by "分隔符"
lines terminated by "n";
3、把MOSHOU库下的sanguo表英雄的姓名、攻击值、国家导出来,sanguo.txt
select name,gongji,country from MOSHOU.sanguo
into outfile "/var/lib/mysql-files/sanguo.txt"
fields terminated by " "
lines terminated by "n";
$ sudo -i
$ cd /var/lib/mysql-files/
$ ls
$ cat sanguo.txt
4、将mysql库下的user表中 user、host两个字段的值导出到 user.txt
select user,host from mysql.user
into outfile "/var/lib/mysql-files/user.txt"
fields terminated by " "
lines terminated by "n";

    database中存储着各种数据,sql语句用于从database中找出我们需要的数据,mysql是一种应用软件,通过语句对database进行操作。

5,创建数据表
creat table 表名(字段名 字段类型(约束),字段名2 字段类型(约束),);
字段类型有:int(Integer) 整数型;
float/double 单精度/双精度浮点型
date 日期类型
datetime 日期时间类型
char 定长字符串
varchar 可变长度字符串
text 大文本对象,可用于存储超长字符串
bold 二进制大对象,可用于存储图片等二进制数据

    foreign key(参考字段名)

第1页 :limit 0,5 # 1 2 3 4 5
第2页 :limit (2-1)*5,5 # 6 7 8 9 10
第3页 :limit (3-1)*5,5 # 11 12 13 14 15
第4页 :limit (4-1)*5,5 # 16 17 18 19 20

本节完!

    MySQL我使用的是5.6版本,通过管理员身份打开cmd后,启用mysql服务为:net start mysql56,关闭服务为:net stop mysql56。登录:mysql -h localhost -u root -p     回车后输入密码:123456(用户名和密码在安装时进行设置)

6,查看创建的数据表
show tables;//仅仅是表名的展示;

    references 主表(被参考字段名)

每页显示n条记录,显示第m页 :limit (m-1)*n,n
4、聚合函数
1、分类
avg(字段名) : 求该字段平均值
sum(字段名) : 求和
max(字段名) : 最大值
min(字段名) : 最小值
count(字段名) : 统计该字段记录的个数
2、示例
1、攻击力最强值是多少
select max(gongji) from MOSHOU.sanguo;
2、统计id 、name 两个字段分别有几条记录
select count(id),count(name) from sanguo;
## 空值 NULL 不会被统计,""会被统计


7,查看表的结构
desc 表名//desc student;

    on   delete  级联动作

3、计算蜀国英雄的总攻击力
select sum(gongji) from MOSHOU.sanguo
where country="蜀国";
4、统计蜀国英雄中攻击值大于200的英雄的数量
select count(*) from MOSHOU.sanguo
where gongji>200 and country="蜀国";

    下面将会从四个方面进行总结:

8,修改数据表名称
alter table 表名 rename 新表名;//alter table student rename stu;

    on   update  级联动作 

 

    1.数据定义语言(DDL)

9,删除数据表
drop table 表名;//delete table stu;

  以上的语法连在一起看

    2.数据操作语言(DML)

10,添加表字段
alter table 表名 add column 字段名 字段类型;//alter table student add column phone varchar(20);
添加带默认值的字段名
alter table student add column sex varchar() default'男';

  3.使用规则

    3.数据查询语言(DQL)

11,修改表字段类型
alter table 表名 modify 字段名 修改的类型;//alter table student modify sex varchar(10);

    主表、从表字段数据类型要一致

    4.函数

12,删除表字段
alter table 表名 drop 字段名//alter table student drop phone;

    主表被参考字段:主键

    本篇小结大部分是语句格式,如果需要代码实现的截图,以及部分额外知识点标注,可以下载安装xmind软件后,下载云盘里的思维导图进行查看。

13,约束 NOT NULL:非空约束;
UNIQUE:唯一约束;
PRIMARY KEY:主键,唯一标识该记录,非空且唯一;
FOREIGN KEY:外键,指定该记录从属的主表记录;
添加非空约束和主键;
主键添加:alter table student add column id int primary key;

  4.示例

链接: 密码:qrie

14,创建表数据时设置主键自增 ,以及名字非空;
create table student(
id int primary key auto_increment,
name varchar(20) not null,
age int,
birthday date,
xuefen float(3,1),//3代表一共有三位,1代表小数点后的位数.);

    表1、缴费信息表(财务)

ximd内容如下:

15,索引:索引的作用类似于书的目录,一个表可以有多个索引,每个索引都可用于加速该列的查询速度
创建索引 creat index 索引名称 on 表名(字段名);
create index nameindex on student(name);//创建指向student name
删除索引 drop index 索引名称 on 表名;
create index nameindex on student;
劣势:当数据进行修改时,索引也需要进行更新,较为麻烦,同时索引信息需要一定的磁盘空间。

      id  姓名  班级  缴费金额

图片 1

16,中文乱码需要键入 set names gbk;//解决名字乱码问题;

      1  唐伯虎  AD06  300

 

mySQL数据库操作语句。17,插入数据 insert into 表名(字段名1,字段名2) values(数据1,数2);
不带自定义id的插入学生信息:
insert into student(name,age,birthday,xuefen) values('科比',39,'1978-8-30',1.8);
展示输入的学生所有信息:
select * from student;
展示表名中的部分学生信息:
select age,xuefen from student;
自带id的插入学生信息:
select into student(id,name,age,birthday,xuefen) values(5,'杜兰特',29,'1988-9-29',1.9);
若此时再进行不带自定义id的形式插入学生信息,则会从自带id的后面进行自增长
insert into student(name,age,brithdaty,xuefen) values('艾弗森',42,'1975-6-7',3.0);
若输入的顺序和展示出来表的字段的顺序相同,可省略字段部分;
insert into student values(2,'汤普森','27','1990-2-8','3.5');

      2  点秋香  AD06  200

数据定义语言(DDL):

18,修改数据 update 表名 set 字段1=值1,字段2=值2,where条件,
没有添加where条件的会修改全部记录,
uodate student set sex=男;
添加where 一般指定id;
update student set xuefen=3.0 where id=4;

      3  祝枝山  AD06  100

1.建表:

create table 表名(                                                    create table test(

          列名称1 数据类型 ,                                                           id int,

          列名称2 数据类型,                                                            name char(10),

          .........                                                                                    ......

          列名称n 数据类型);                                                       birthday date);

常用的数据类型:整数(int,tinyint,smallint),小数(float,decimal),字符串(char,varchar),时间(date,time)

19,删除数据 delete from 表名 where 条件;//以行为单位进行整行删除
delete from student where id=7;

    表2、学生信息表(班主任)

2.约束:

primary key(主键):约束唯一标识数据库表中的每条记录

foreign key(外键):foreign key就是表与表之间的某种约定的关系,从一个表指向另一个表

unique:约束用于限制加入表的数据的类型

建表时可在数据类型后添加:

create table test(id int primary key);

create table test(id int,primary key(id);

或者建表时未添加,后面需要时再添加:

alter table test add primary key(id);

删除主键:alter table test drop primary key;

unique的用法与primary key 相同。

外键的用法:

create table t1(id int primary key,name char(10));

create table t2(id int primary key,pri_id int,name char(10),

constraint i foreign key(pri_id) references t1(id));

意思为:把t2中的pri_mySQL数据库操作语句。id 作为外键,指向t1中的主键id

删除外键:constraint i,把外键命名成i,方便了我们删除外键

alter table t2 drop foreign key i; 

20,查询数据{单表查询,联合查询};
单表查询:单条件查询,去重查询,查询条件中的运算符,函数查询,分组查询,分页查询,结果排序

      id 姓名 缴费金额

3.字段属性

1.unsigned(无符号型),只能用在数值型字段

2.zerofill(自动补零),只能用在数值型字段,前导零,同时该字段自动式UNSIGNED

3.AUTO_INCREMENT(自动增长),寄生于主键

4.NOT NULL:强制约束列不守NULL值,即不添加数值就无法插入数据

5.缺省值(default):给数据一个默认值(系统默认值是NULL),不能与auto_increment同时用于一个字段上

写法均为:create table test(id int 字段属性);

 


20.1 单条件查询:select * from 表名;(全部元素都会显示出来);
select * from student;
展示特殊字段 select 字段1,字段2 from 表名
select name,age from student;
查询特殊条件的 select * from 表名 where 条件;
select * from student where age>30;
查询多个特殊条件的;条件1 and 条件2;//与
条件2 or 条件2;//或

      1 唐伯虎 300

DML(数据操作语言):

20.2 去重查询:可以使用distinct关键字从查询结果中消除重复数据;
select distinct 字段1,字段2 from 表名;
select distinct name,xuefen from student;name和学分都重复才会去除;

      2 点秋香 260

1.索引:

创建:create index 索引名 on 表名(字段);            create index i on test(id);

删除:drop index 索引名;                                        drop index i;

20.3 查询条件中的运算符;比较运算符=,!=,<> ,>,>=,<,<=;
逻辑运算符 and:与 or 或 not:非 优先级 not>and>or
优先级的检测;
select * from student where 1<>1 and 1=1 or 1=1 or 1=1;
select * from student where 1=1 or 1=1 or 1=1 and 1<>1;
使用between and
between 16 and 20 ;//可以取到16 和20,这两个边界值。
使用in运算符
select * from student where age in(10,30,40,29);
跟表中的年龄进行比对,看有没有能够匹配相等的,有,则显示
使用like运算符
模糊运算符,像
select * from student where name like '%张';
'%'代表无限个字符 '%张'是指以张结尾的名字
'%张%'中间有张字的名字 '张%'是指以张开头的名字
'_张'以'张'字结尾的三个字的名字,''代表一个字符;
使用is null运算符

*      3   xxx    xxx*

2.对数据操作:

20.4 函数查询
max:求最大值,min:求最小值,avg:求平均值,sum:求和,count:求数量
case 函数,类似于switch;
select max(age) from student;//选择最大年龄;
最大年龄对应的名字
select age,max(age) from student;
//展示max(age)对应的所有信息;
//select max(age) from student-->返回的是一个最大年龄的表单
select * from student where age=(select max(age) from student);
count的应用:
select count(1) from student;
case:的应用
select * ,case
where age>30 then '老年人'
where age<30 then '中年人’
end
from student;
实例1:查询所有大于平均年龄的学生记录
//平均年龄 select avg(age) from student;
//select * from student when age>(select avg(age) from student);

*      ....................
*

insert(插入):

插入单独数据:insert into 表名 字段1、字段2... values(值1、值2...);

插入默认数据:insert inro 表名 values(值1、值2...);

insert into test(id,name) values(008,'周星星');

20.5 分组查询
group by 分组字段;
//案例:平均年龄按照男女性别分组
select avg(age) from student gronp by sex;
//显示男女性别,同时平均年龄按照男女性别分组
select age,avg(age) from student group by sex;
having 分组条件
//案例,显示男女性别,同时平均年龄按照男女性别分组但是大于平均年龄的要小于3个
select sex avg(age) from student group by sex having count<3;
where 和having 的区别
where用于过滤行,having 用于过滤组,where 子句中不能使用组函数,
having子句中可以使用组函数

*    创建表*

update(修改、更新):修改(更新)数据:update 表名 set 字段=新值 where 列名称=某值

update test set name='詹姆斯' where id=008;

修改多条数据:

update test set name=case id

when 001 then 'qwe'

when 002 then 'asd'

end

where id in(001,002)

20.6 分页查询 limit x,y;x代表下标,y代表个数
select * from student limit 0,2;起始下标为0的位置显示两天信息;

*    表1(主表)*

replace(批量更新数据)(也可用于数据添加):replace into 表名(字段1、字段2...) values(值1、值2...)

replace into test(id,name) values(002,'777');

未添加的数据会默认为null

20.7 排序查询 order by 字段//默认是升序
select * from student order by age (asc);//默认按年龄升序排列
select * from studnet order by age desc;//按年龄降序排列;
案例1:先按年龄升序排序,再取出年龄最大的两个
select * from student by age asc limit 0,2;
案例2:先按年龄升序排序,如果年龄相同再按学分降序排序
select * from student by age ase,xuefen desc;

      create table jftab(
      id int primary key,
      name varchar(15),
      class char(5),
      money int
      );

批量更新(通过update):

insert into test(id,name) values(001,'i am 001'),(002,'i am 002')

on duplicate key update

id=values(id),name=values(name);

分组的顺序优先级:

select * from student group by () having where order by limit;

21 关联查询
21.1 连接查询
交叉连接,使用on子句的连接,左连接,右连接;

  21.1.1 交叉连接
  select * from student cross join class;
  添加别名进行交叉连接选择
  //用于挑选一个学生选择一门课程时的展示数据
  select * from student s cross join class c where s.kid=c.id; 
  //挑选所有选了某个课程的学生
  select s.* from studnet s cross join class c where s.kid=c.id and c.classname="crossover";

  21.1.2 on连接
     select * from student s join class c on;

  21.1.3 左连接
    select * from student s left join class c on s.kid=c.id;

21.1.4 右连接
select * from student s right join class c on s.kid=c.id;

21.2 子查询
1.出现在from语句后当成数据表,
2.出现在where条件后作为过滤条件的值,
//案例查询所有选择某门课程中学生年龄最大的;
//所有选择某门课程的学生
select * from student s join class c on s.kid=c.kid and classname="";
//筛选年龄最大的
先进行排序再进行分页
select * from( select * from student s join class c on s.kid=c.kid and classname="") t
order by t.age desc limit 0,1;

    插入数据

delete删除数据(一条):delete from 表名 where 条件

delete from test where id=001;

      insert into jftab values
      (1,"唐伯虎","AD06",300),
      (2,"点秋香","AD06",260),
      (3,"祝枝山","AD06",250);

 

    表2(从表)     

3.对表操作

      create table bjtab(
      stu_id int,
      name varchar(15),
      money int,
      foreign key(stu_id) references jftab(id)
      on delete cascade
      on update cascade
      );

修改表名:alter table 旧表名 rename as 新表名;

alter table test rename as father;

    插入数据

修改字段的数据类型:alter table 表名 modify 字段名 数据类型

alter table test modify id char(10);

      insert into bjtab values

修改字段名:alter table 表名 change 字段名 新字段名 数据类型; 

alter table test change name address char(50);

      (1,"唐伯虎",300),(2,"点秋香",260);

增加字段:alter table 表名 add 字段名1 数据类型

alter table test add address char(30);

*  5、删除外键*

删除字段:alter table 表名 drop 字段名;

alter table test drop address;

 


*    alter  table  表名  drop  frreign  key  外键名;*

DQL(数据查询语句insert):

*    查看外键:show  create  table  表名;*

1.交叉查询:select 表1.字段,表2.字段 from 表1,表2;

*  6、级联动作*

多表联合查询:select 表1.字段,表2.字段 from 表1,表2 where 表1.id=表2.id;

select * from t1,t2 where t1.id=t2.id;

*    cascade*

2.查询不重复的数据:select distinct 字段 from 表名;

select distinct id from test;

*    数据级联删除、更新(参考字段)*

3.取别名alias:select * from 表名 as 别名;

自连接:select b.* from shop as a,shop as b where a.name='包子' and a.price

把shop表取别名为a,b,通过a和b进行自己数据的对比

*    restrict(默认)*

4.limit(偏移量):

查询前n条数据:select *from 表名 limit n;

查询前n条数据后的i条数据:select *from 表名 limit n,i;

*    从表有相关联记录,不允许主表操作*

5.in(在where后规定多个值):select 字段 from 表名 where 字段 in(值1,值2);

select * from test where id in(1,2,3);

*    set  null*

6.like(用于where子句中搜索列中的指定模式):select 字段 from 表名 where 字段 like 表达式;

例如搜索王姓青年:

select *from test where name like'王%';

%:表示0~多个字符

_:表示一个字符

*    主表删除、更新,从表相关联记录字段值为NULL*

7.order by(排序):select 字段 from 表名 order by 字段(排序方式) [desc](使用倒序排列)

select * from test order by id desc;

表示通过id从大到小进行排序显示

*  7、已有表添加外键
*

8.join连接:

inner jor(内连接),left join(左连接),right join(右连接)

full join(全连接)(mysql不支持全连接)

格式都相同:select 字段 from 表1 inner/left/right join 表2 on 表1.字段=表2.字段;

*  alter  table  表名  add  for  foreeign  key(参考字段)  references  主表(被参考字段)
*

9.union(联合查询):select *from 表1 union select *from 表2;

 


*  on delete ...*

函数:

*  on  update ...*

1.合集函数:操作面向一系列的值,并返回一个单一的值

写法都相同:

查询某列的平均值:select avg(字段) from 表名;

返回某列的行数:select count(字段) from 表名;

查询某列最大值:select max(字段) from 表名;

查询某列最小值:select min(字段) from 表名;

返回某列总和:select sum(字段) from 表名;

分组显示总和:select sum(字段) from 表名 group by 字段;

2、表的复制

2.标量函数:操作面向某个单一的值,并返回基于输入值的一个单一的值

写法也都相同:

ucase(把字段的值转换为大写):select ucase(字段) from 表名;

lcase(把字段的值转换为小写):select lcase(字段) from 表名;

mid(提取字符):mid(字段,起始,结束):

select mid(name,2,3) from test;

表示从name列的第二个数据开始,每个数据只显示3位

len(返回文本长度):select length(字段) from 表名;

round(把数值四舍五入,并保留相应小数位):select round(字段,数字) from 表名;

now()(查询当前时间):select now() from 表名;(有几个数据就出现几个)

 


    此文章虽然是自己的学习小结,而且还是身为初学者的我写的,但也希望更多的朋友能看到我的文章,如果有不足之处或疑问,欢迎到留言区留言。

*  1.复制MOSHOU.sanguo的全部记录和字段到sanguo2*

*  create table sanguo2 select * from MOSHOU.sanguo;
*

*  2.复制MOSHOU.sanguo表的前3条记录,sanguo3*

*  create table sanguo3
*

*  select * from MOSHOU.sanguo limit 3;*

*  3.复制MOSHOU.sanguo表的id,name,country三个字段的前5条记录,sanguo4*

*  create table sanguo4*

*  select id,name,country from MOSHOU.sanguo limit 5;*

*   复制表结构*

   create table 表名 select * from 表名 where false;

  注意:

   复制表的时候不会把原表的键(key)属性复制过来

3、嵌套查询

  定义 :把内层的查询结果作为外层的查询条件

  语法格式

  select ... from 表名 where 条件(select ....);

  示例

    把攻击值小于平均攻击值的英雄名字和攻击值显示出来   

       1、先计算平均值

       select avg(gongji) from MOSHOU.sanguo;

       2、找到 < 平均值

      select name,gongji from MOSHOU.sanguo

      where gongji<平均值;

     子查询:

      select name,gongji from MOSHOU.sanguo

      where

       gongji<(select avg(gongji) from  MOSHOU.sanguo);

   2、找出每个国家攻击力最高的英雄的名字和攻击值

     select name,gongji from sanguo where 

     gongji in(select max(gongji) from sanguo  group by country);

 4、多表查询

  select 字段名列表 from 表名列表; (笛卡尔积)

   1、显示省和市的详细信息

  河北省 石家庄市
  河北省 廊坊市
  湖北省 武汉市

*  *select sheng.s_name,city.c_name from sheng,city

  where
  sheng.s_id=city.cfather_id;

   2、显示省市县详细信息

  select sheng.s_name as sheng,city.c_name as city,xian.x_name as xian from sheng,city,xian
  where
  sheng.s_id=city.cfather_id and
  city.c_id=xian.xfather_id;

5、连接查询 

  内连接 

*  1.语法格式*

    select 字段名 from
    表1 inner join 表2 on 条件
    inner join 表3 on 条件;

  2、显示省市详细信息

    select sheng.s_name,city.c_name from sheng
    inner join city on sheng.s_id=city.cfather_id;

  3、显示省市县详细信息  

    select sheng.s_name,city.c_name,xian.x_name from
    sheng inner join city
    on sheng.s_id=city.cfather_id
    inner join xian
    on city.c_id=xian.xfather_id;

  2、外连接

    以 左表 为主显示查询结果

    select 字段名 from 表1 left join 表2 on 条件

    left join 表3 on 条件;

    显示省市详细信息

    select sheng.s_name,city.c_name from sheng

     left join city

    on sheng.s_id=city.cfather_id;

     右连接

    用法同左连接,以右表为主显示查询结果

 6、数据备份(mysqldump,在Linux终端中操作)

  命令格式

    mysqldump  -u用户名  -p  源库名  > ~>***.sql

  源库名的表示方式

  --all-databases  备份所有库

  -B 库1 库2 库3   备份多个库

  库名 表1 表2 表3 备份指定库的多张表

  示例:

    备份所有库,放到 mydata 目录下:all.sql

    mysqldump -uroot -p --all-databases > all.sql

    备份 db4 库中的 sheng city xian 三张表,scx.sql

     mysqldump -uroot -p db4 sheng city xian > scx.sql

     备份 MOSHOU 和 db4 库, md.sql

     mysqldump -uroot -p -B MOSHOU db4 > md.sql

     备份 db4 库, db4.sql

     mysqldump -uroot -p db4 > db4.sql

7、数据恢复

  1、命令格式(Linux终端)

  mysql -uroot -p 目标库名 < ***.sql

  2、从所有库备份中恢复某一个库(--one-database)

  mysql -uroot -p --one-database 目标库名 < all.sql

  mysql -uroot -p --one-database db4 < all.sql

8、mysql的用户账户管理

  1、开启MySQL远程连接  

    1、sudo -i
    2、cd /etc/mysql/mysql.conf.d/
    3、subl mysqld.cnf
      #bind-address = 127.0.0.1 ctrl s保存  ----此行为注释
    4、/etc/init.d/mysql restart

  2、添加授权用户

    用root用户登录mysql

    mysql -uroot -p123456

    授权:   

      grant 权限列表 on 库.表 to "用户名"@"%"
      identified by "密码" with grant option;   

    权限列表:all privileges 、select 、insert
    库.表 : *.* 所有库的所有表

  3、示例
    1、添加授权用户tiger,密码123,对所有库的所有表有所有权限
    grant all privileges on *.* to "tiger"@"%" identified by "123" with grant option;
    2、添加用户rabbit,对db4库有所有权限
    grant all privileges on db4.* to "rabbit"@"%" identified by "123" with grant option;

 

本小结完!

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:mySQL数据库操作语句

关键词: