Mysql数据库操作

Mysql 基础

/************

* 数据库 *

************/

1.连接数据库

mysql -u root -p

2.查看所有数据库

show databases;

3.查看数据表

show tables;

4.查看要使用的某个数据库(指定)

use + 数据库名字;

5.创建数据库名

create database 数据库名字 charset=utf8;

6.删除数据库

drop database 数据库名;

7.创建数据表

create table 表名(

id int(4),

name varchar(4),

sex char(4),

age int,

height char(4)

);

8.查看数据表中有哪些字段

desc 表名;

9.在数据表中插入数据

// 单挑插入信息

insert into 表名 values(1,'小强','男',23,176);

// 多条插入信息

insert into 表名(id,字段名称a,字段名称b,字段名称c,字段名称d)values(id,字段值a1,字段值b2,字段值c3,字段值d4),(id,字段值a2,字段值b2,字段值c2,字段值d2);

10.查看数据表有哪些信息

select * from 表名;

11.修改数据表里面的信息

// 修改单个字段信息

update 表名 set 字段名称='要修改成的值' where id=要修改的id值;

// 修改多个字段信息

update 表名 set 字段名称='要修改成的值',字段名称='要修改成的值' where id='id是几';

// 一个字段同时+1

update 表名 set 字段名称=字段名称+1;

12.删除某个表中的一条信息

delete from 表名 where id=要删除的id值;

13.删除某个数据库中的某个数据表

drop table 表名;

14.删除数据库名字0

drop database 数据库名;

14.退出

exit;

/*******字段约束********/

15. 无符号 unsigned

// 创建一个数据表 unsigned 约束id不能为负数

create table 表名(

id int(10) unsigned,

name char(4),

sex char(4)

);

16.主键 primary key:这个字段的值必须是唯一的并且必须有值

// 创建一个数据表设置主键

// primary key (PRI)说明这个id字段是主键索引并且本数据表中与这个主键id不能有相同的

create table 表名(

id int unsigned primary key,

name char(4),

sex char(4)

);

17.自增 auto_increment

// auto_increment id的自动增长

create table 表名(

id int unsigned primary key auto_increment,

name char(4),

sex cahr(4)

);

18.唯一 unique 值必须唯一或者不给值（不给值显示NULL）

// 创建一个数据表

// name后面的unique为这个字段的唯一

create table 表名(

id int unsigned primary key auto_increment,

name char(4) unique,

sex char(4)

) ;

19.默认值 default 如果不给定字段值就默认一个值

// 创建一个数据表

// default 'nv' 给定默认值的方法

create table 表名(

id int unsigned primery key auto_increment,

name char(4) unique,

sex cahr(4) default 'nv'

);

20.不为空 not null 值不能为空

// 创建一个数据表

// not null 如果没有给值，int会转为0，char会转为空字符串

create table 表名(

id int unsigned primary key auto_increment,

name char(4) unique,

age int not null

);

21.int 类型id不会被截取

// 创建一个数据表

// int

create table 表名(

id int(4),

name char(4)

);

// 插入数据 id=123456会被全部插入数据表 name=abcdef只会插入4个字符abcd

insert into 表名(id,name) values(123456,abcdef);

22.零填充 zerofill 前导0填充 00001 0032 0003...

// 创建一个数据表

// zerofill当数组不够4位时,用前导0填充 int(4)零填充后就是0001,0002...

create table 表名(

id int(4) zerofill,

name char(4),

sex char(4)

);

/*********常用的数据类型**********/

23.整型 int

create table 表名(

id int unsigned primary key auto_increment,

age int

);

无符号 0 - 42亿

有符号 -21亿 - 21亿

24.小整型 tinyint 占用一个字节

// 新建一个数据表

create table 表名(

id int unsigned primary key auto_increment,

age tinyint unsigned,

ages tinyint

);

无符号 0 - 255

有符号 -128 - 127

25.浮点 float(m,n)=float(6,2)=float(总位数,小数点后的位数)

// 新建一个数据表

// 如果这个值参与运算就会出现精度问题

create table 表名(

id int unsigned primary key auto_increment,

price float(6,2)

);

26.字符串浮点数 decimal(m,n) = decimal(总位数,小数点后的位数)

// 可以放心进行参与运算使用deciml(m,n)

create table 表名(

id int unsigned primary key auto_increment,

price float(5,2),

prices deciml(5,2)

);

// float 123.46 = float 123.45 精度不准确

// decimal 123.46 = decimal 123.46 精度准确

(1) 定长字符串 char(n) n代表几个字符

‘abcd’实际占4个字符写几个字符就占几个字节

存取效率高

char() 最长是255字符

(2) 变长字符串 varchar(n) n代表几个字符

‘abcd’实际占3个字符不管写几个字符还是按实际字节占用用一个额外字节表示长度

更节省空间

varcahr() 最长是 21800字符左右约小于等于 65535/3

(3) 枚举字符串:enum多选一

enum('w','m','x','n','t'); 只能在这其中任意选一个否则就为空可以使用数值代替

enum('1','2','3','4','5'); 可以数值代替上面的内容结果一样

//新建数据表 enum

create table 表名(

id int unsigned primary key auto_increment,

name char(4),

sex enum('男','女','未知','保密')

);

// 数值使用方法将id为2的值改变成了x 因为数值3=x

update 表名 set sex=3 where id=2;

(4) 集合字符串:set多选多

set('w','r','t','o','d','g'); 只能在这个范围内选取多个值否则为空可以使用数值代替

set('1','2','4','8','16','32'); 可以数值代替上面内容结果一样多选方法：例选择15(数字相加) 就是选择了 w r t o

// 新建数据表

create table 表名(

id int unsigned primary key auto_increment,

name char(4),

aihao set('电影','购物','睡觉','篮球','游戏','运动')

);

27.日期时间 detetime 8个字节不能设置默认值

// 在php项目中常用int类型来存储时间戳

// 在mysql中，有timestamp这种类型就叫时间戳

// mysql中一般使用的时间戳表达方式 created_at / updated_at

// 新建数据表 0000-9999年

create table 表名(

created_at datatime,

updated_at datetime

);

28.索引原理

建立索引

好处:能够快速查询到记录

缺点:始终要调整索引树的平衡

添加修改删除都需要调整索引树

结论,当数据表中大量查询操作,少量增删改操作是,可以使用索引

什么样的字段适合建立索引？

取值范围大

取值要均匀

索引的种类：普通索引主键索引唯一索引全文索引

1(23)

2(21) 4(34)

3(20) 6(22) 5(32) 11(40)

8(18) 7(26) 12(36)

10(17) 19(19) 13(27)

16(26)

29.事物

用户名账号金额

张三 100000

李四 10000

update 工资表 set 账号金额=账号金额+1000 where 用户名=李四

update 工资表 set 账号金额=账号金额-1000 where 用户名=张三

基本命令：

begin 开启事物(记录之后数据库所有表中对记录的操作)

commit 提交事物(确认之前所有记录的操作)

rollback 撤销回滚(撤销begin之后所有对记录的操作)

//新建一个数据表

create table 表名(

id int unsigned primary key auto_increment,

name char(4) unique,

sex enum('男','女') default '男',

age tinyint unsigned default 0,

classid char(6)

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

1.begin 开启事物

2.删除一条信息修改一条信息

输入命令：rollback 就会返回到原来的数据

3.commit 确认提交事物不可填入实际数据中

特性:SCID

原子性(Atomicity) 不可分割的完成一件事

一致性(Consistency) 要不都成功或者要不都失败

隔离性(Isolation) 一件事情完成后再执行下一件事情

持久性(Durability) 如果事件提交后不可改变

表引擎:

只有InnoDB这种表引擎支持事物

create table users(

......

)engine=innodb;//多数是默认的

30.授权

//grant 可以使用的权限

//创建一个用户允许的权限

grand 可以使用的权限 ON 库.表 TO '账号'@'登录地址' identified BY '密码'

grant select,insert ON php217.users TO 'xxoo2'@'%' identified by '123456'

命令行：grant select,insert on php217.php3 to 'xxoo'@'%' identified by '123456';

flush privileges 确认授权

//删除用户

drop user '用户名'@'%';

//mysql用户表放在哪里?

......

//可以登录到讲师的服务器

mysql -h 192.168.217,250 -uxxoo -p123456 登录到老师的mysql服务器

insert into users() 插入的你的名称和年龄

31.生成sql语句

//新建一个数据表

create table 表名(

id int unsigned primary key auto_increment,

name char(4),

sex enum('w','n') default 'w',

age tinyint unsigned default 0,

classid char(6)

);

//php文件循环遍历出数据

getsql.php

/*******************************************************

<?php

// 读取文件信息

$str_name = file_get_contents('./names.txt');

// echo $str_name;

// 正则表达式 \S表示匹配非空白字符 +表示一次或者多次

$pth = '/\S+/';

// 进行匹配

preg_match_all($pth,$str_name,$arr);

// echo '<pre>';

// 随机打乱数组

// shuffle($arr[0]);

// print_r($arr[0]);

// 准备一个字符串

$sql = 'insert into php4(name,sex,age,classid) values';

// 将有字段多选的值定义成数组

$arr_sex = ['w','m'];

$arr_classid = ['php217','php218','php219','php220','php221'];

// 循环遍历数据

foreach($arr[0] as $name){

// 随机出来一个性别年龄班级

$sex = $arr_sex[rand(0,1)];

$age = mt_rand(1,40);

$classid = $arr_classid[rand(0,4)];

// 拼接上面的$sql语句

$sql .= "('$name','$sex','$age','$classid'),";

}

//去掉最后的一个逗号

$sql = rtrim($sql,',');

echo $sql;

//将打印出来的数据直接赋值到cmd命令行执行即可

*******************************************************/

names.txt

/******************************************************

陈泽鑫李永桦黄伟贺重阳徐文瑞孙传飞蒋毅

崔松江金盛

闫菲崔文志林勇齐夏星

陈同谢聪柴海清王利桢

于兆林田世成张振宇徐凯姚忠鹏

张硕卢天寅刘世龙赵晋平李浩侯雪锋刘喆

苑鹏刚孙晨旭龚亚桐董严严卓建法王启涛杨坤城

杨佳鑫梁世龙丁佳根刘亚琦周炬成朱少康丁毅

丁立民金宗坤孙佳涛郝睿哲姚存智孙晓美

郝帅许相坤韩家兴徐澳庆安忠亮张洋卢雅丽

边汉卿曹宇黄成蕊史子旭赵少猛王小平王艳艳

李博林张森何凯付文涛田非凡陈松杨雷

杨煦黄熙康孔凡昊来鹏飞佘浩阳李彦鑫柴晓航

李景磊张立哲马子豪张贺王强强肖庆虎

********************************************************/

32.命令行方式从数据库中导入导出数据（31 的数据）

//导出

1.退出mysql

exit

2.mysqldum -u root -p php217 php4>d:/php4.sql

库名字表名导出的文件名

//导入

1.退出mysql

exit

2.mysql -u root -p php217 < d:/php4.sql

库名称文件位置和名称

33.查询练习

格式：

select[字段列表] | *

from 表名

where 搜索条件

group by 分组字段 [having子条件]

order by 排序 asc | desc

limit 分页参数

注意：顺序必须是这个顺序

//一张表名字为:stuphp

id name sex age classid

1 张三男 19 php217班

2 李四女 23 php218班

3 王五男 40 php219班

4 赵六女 45 php220班

5 田七男 34 php221班

6 张九女 22 php219班

//字段部分:

//显示所有字段

select * from stuphp;

select * form 表名; (尽量不使用*)

//部分字段

select name,age from stuphp;

select 字段,字段 from 表名;

//查询十年后的年龄

select name,age+10 from stuphp;

select 字段,字段+10 from 表名;

//字段别名给字段起别名（将字段名称变好看一些）

select name,age+10 as ages from stuphp;

同上

select name,age+10 ages from stuphp;

//表别名

select name,age from stuphp as stuphps;

同上

select name,age from stuphp stuphps;

//字段合并(函数：concat() 年龄和性别合并在一起)

select name,concat(age,sex) from stuphp;

同上 '--'无意义就相当于 age~sex

select name,concat(age,'~',sex) from stuphp;

//去除重复的字段 (查看有几个班级)

1.首先获取班级信息

select calssid from stuphp;

2.去除重复 distinct

select distinct calssid from stuphp;

//在查询结果中添加一列也就是添加一个字段(添加讲师一列)

1.select *,'王老师' from stuphp;

2.将字段名称更改一下

select *, '王老师' as '讲师' from stuphp;

//条件部分: where

//某个字段的值等于什么(查询php217的学生)

select * from stuphp where calssid='php217';

//多条件and查询(php217的女生)

select * from stuphp where calssid='php217' and sex='w';

//年龄大于20岁的学生

select * from stuphp where age>20;

//年龄在30到40的人

select * from stuphp where age>=30 and age=<40;

同上使用between(包括)

select * from stuphp where age between 30 and 40;

//年龄不在10到35之间的人

select * from stuphp where age<10 or age>35;

同上使用not between

select * from stuphp where age not between 10 and 35;

//php217班和php218班的女生

select * from stuphp where (classid='php217' or classid='php218') and sex='w';

注意：和有时候应该是or 在mysql中and优先级比or高

//217班的男生和218班的女生

select * from stuphp where(classid='php217' and sex='m') or (classid='php218' and sex='w');

//找id为 3,5,8,9,12的人

select * from student where uid=3 or uid=5 or uid=8 or uid=9 or uid=12;

select * from stuphp where id in(3,5,8,9,12);

//找id不是3,5,8,9,12 的人

select * from stuphp where id not in(3,5,9,8,12);

//查询classid值不为null的人

select * from studphp where classid is NULL;

select * from studphp where classid is not NULL;

//找名字为2个字的人 _代表一个字符 like

select * from stuphp where name like '__';

//找名字中以李开头的字 %代表0个到多个字符

select * from stuphp where name like '李%';

//找名字以文结尾的

select * from student where name like '%文';

//找包含旭的人

select * from student where name like '%旭%';

//分组查询:

group by 分组伴随着统计男生多少女生多少

//统计函数：

count(*) 统计个数但是不会统计值为NULL的记录

sum() 求和比如符合条件的人的总年龄

avg() 平均和

max() 最大值

min() 最小值

//217总共多少人？

select conut(*) from stuphp where classid='php217';

//每个班人数

select classid,count(*) from stuphp group by classid;

//最大年龄最小年龄平均年龄

select max(age) from stuphp;

select min(age) from stuphp;

select avg(age) from stuphp;

//最大年龄最小年龄平均年龄每个班的

select classid,max(age) from stuphp;

select classid,min(age) from stuphp;

select classid,avg(age) from stuphp;

//查找年龄最大的那个人

select * from stuphp where age=(select max(age) from student);

//查找在平均年龄之上的人

select * from stuphp where age>(select avg(age) from stuphp);

//每个班的男生,女生各多少人

select classid,sex,count(*) from stuphp group by classid,sex;

//每个班的男生女生各多少人,要求在20-30岁的

select classid,sex,count(*) from stuphp where age>=20 and age<=30 group by classid,sex;

//每个班多少人只显示班级人数大于16的

select classid,count(*) from php4 group by classid having count(*)>16

//按班级分组并获取每个班中学生的姓名

select classid,group_concat(name) from stuphp group by classid;

//排序

prder by 字段名默认 asc升序 desc降序

//年龄从小到大的排序

select * from stuphp order by age asc;

select * from stuphp order by age;

//年龄从大到小

select * from stuphp order by age desc;

//先女后男年龄再小到大 (先按性别排序相同性别再按年龄排序)

select * from stuphp order by sex,age;

//先男后女

select * from stuphp order by sex,desc,age desc;

//先按班级排序再按年龄从大到小

select * from stuphp order by classid,age desc;

/*******************************************/

//分页查询

//取出前5条记录

select * from stuphp limi 5;

//跳过2条显示5条记录

select * from stuphp limit 2,5;

第一页:limit 0,5;

第二页:limit 5,5;

第三页:limit 10,5;

第四页:limit 15,5;

第五页:limit 20,5;

第 n页:limit (n-1)*5,5;

//年龄最大的3个人(如果第3名和第4名年龄相同,取id最大的)

select * from stuphp order by age desc,id desc limit 3;

//218班女生年龄最小的3个人

select * from stuphp where class='php218' and sex='w' order by age limit 3;

//女生最多的班级

select classid,count(*) from stuphp where sex='w' group by classid order by count(*) desc limit 1;

/**********************************************************/

//多表联查

数据库名字:test

1.数据表:users

CREATE TABLE `users` (

`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,

`uname` char(4) NOT NULL,

`upwd` char(32) DEFAULT NULL,

PRIMARY KEY (`uid`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

2.数据表:score

CREATE TABLE `score` (

`uid` int(10) unsigned DEFAULT NULL,

`yuwen` tinyint(3) unsigned DEFAULT NULL,

`math` tinyint(3) unsigned DEFAULT NULL,

`english` tinyint(3) unsigned DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.数据表:details

CREATE TABLE `details` (

`uid` int(10) unsigned DEFAULT NULL,

`sex` enum('w','m','x') DEFAULT 'w',

`age` tinyint(4) DEFAULT '0',

`tel` varchar(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

//查询2个表中的数据 (users details)

select * from users,details where users.uid=details.uid;

同上别名

select * from users u,details d where u.uid=d.uid;

同上 INNER JOIN 内联

select * from users u INNER JOIN details d ON u.uid=d.uid;

//查询2个表中的数据并且获取到uid=5的信息

select * from users u,details d where u.uid=d.uid and u.uid=5;

同上 INNER JOIN 内联

select * from users u INNER JOIN details d ON u.uid=d.uid where u.uid=5;

//左联查询查询学生信息表和成绩表联合起来以左边为主

select * from users u LEFT JOIN score s ON u.uid=s.uid;

//右连查询查询学生信息表和成绩表联合起来以右边为主

select * from users u RIGHT JOIN score s ON u.uid=s,uid;

//不连不进行联合查询拿到同样的数据效率也会很高

select * from users where uid=5;

select * from details where uid=5;

最后编辑于：2019.06.26 23:52:55

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,937评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,503评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,712评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,668评论 1赞 276
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,677评论 5赞 366
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,601评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,975评论 3赞 396
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,637评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,881评论 1赞 298
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,621评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,710评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,387评论 4赞 319
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,971评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,947评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,189评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 44,805评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,449评论 2赞 342

Mysql数据库操作

推荐阅读更多精彩内容