MySQL学习(持续更新中…)

发布于 / Course / 0 条评论

一、基础操作

TIPS

因为我觉得比较重要,所以就放最前面了,醒目一点。

1.分组后的条件使用 HAVING 来限定,WHERE 是对原始数据进行条件限制。几个关键字的使用顺序为 where 、group by 、having、order by ,例如:

SELECT name ,sum(*)  FROM employee_tbl WHERE id<>1 GROUP BY name  HAVING sum(*)>5 ORDER BY sum(*) DESC;


alter和update区别
alter是对表的结构进行的操作,比如增加删除表的字段,或者是修改字段的顺序
update是对表中的数据进行的操作,比如依据某个字段修改当前记录中另一个字段的值
drop和delete区别
drop是对表本体进行的操作,比如删除整张表。如果需要对表的结构进行增删,使用alter
delete是对表的数据进行的操作,比如删除某一条记录
总的来说,如果你不想要这张表了,用drop,如果你还想用这张表,只是删除一些数据,用delete

创建数据库

CREATE DATABASE 数据库名;
CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

创建数据库,该命令的作用:

  •  1. 如果数据库不存在则创建,存在则不创建。
  •  2. 创建RUNOOB数据库,并设定编码集为utf8

 

删除数据库

drop database <数据库名>;

创建数据库

use <数据库名>;

数据类型

1、整型

MySQL数据类型 含义(有符号)
tinyint(m) 1个字节  范围(-128~127)
smallint(m) 2个字节  范围(-32768~32767)
mediumint(m) 3个字节  范围(-8388608~8388607)
int(m) 4个字节  范围(-2147483648~2147483647)
bigint(m) 8个字节  范围(+-9.22*10的18次方)

取值范围如果加了 unsigned,则最大值翻倍,如 tinyint unsigned 的取值范围为(0~256)。

int(m) 里的 m 是表示 SELECT 查询结果集中的显示宽度,并不影响实际的取值范围,没有影响到显示的宽度,不知道这个 m 有什么用。

2、浮点型(float 和 double)

MySQL数据类型 含义
float(m,d) 单精度浮点型    8位精度(4字节)     m总个数,d小数位
double(m,d) 双精度浮点型    16位精度(8字节)    m总个数,d小数位

设一个字段定义为 float(5,3),如果插入一个数 123.45678,实际数据库里存的是 123.457,但总个数还以实际为准,即 6 位。

3、定点数

浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值。

decimal(m,d) 参数 m<65 是总个数,d<30 且 d<m 是小数位。

4、字符串(char,varchar,_text)

MySQL数据类型 含义
char(n) 固定长度,最多255个字符
varchar(n) 固定长度,最多65535个字符
tinytext 可变长度,最多255个字符
text 可变长度,最多65535个字符
mediumtext 可变长度,最多2的24次方-1个字符
longtext 可变长度,最多2的32次方-1个字符

char 和 varchar:

  •  1.char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉。所以 char 类型存储的字符串末尾不能有空格,varchar 不限于此。
  •  2.char(n) 固定长度,char(4) 不管是存入几个字符,都将占用 4 个字节,varchar 是存入的实际字符数 +1 个字节(n<=255)或2个字节(n>255),所以 varchar(4),存入 3 个字符将占用 4 个字节。
  •  3.char 类型的字符串检索速度要比 varchar 类型的快。

varchar 和 text:

  •  1.varchar 可指定 n,text 不能指定,内部存储 varchar 是存入的实际字符数 +1 个字节(n<=255)或 2 个字节(n>255),text 是实际字符数 +2 个字节。
  •  2.text 类型不能有默认值。
  •  3.varchar 可直接创建索引,text 创建索引要指定前多少个字符。varchar 查询速度快于 text, 在都创建索引的情况下,text 的索引似乎不起作用。

5.二进制数据(_Blob)

  •  1._BLOB和_text存储方式不同,_TEXT以文本方式存储,英文存储区分大小写,而_Blob是以二进制方式存储,不分大小写。
  •  2._BLOB存储的数据只能整体读出。
  •  3._TEXT可以指定字符集,_BLO不用指定字符集。

6.日期时间类型

MySQL数据类型 含义
date 日期 ‘2008-12-2’
time 时间 ’12:25:36′
datetime 日期时间 ‘2008-12-2 22:06:44’
timestamp 自动存储记录修改时间

若定义一个字段为timestamp,这个字段里的时间数据会随其他字段修改的时候自动刷新,所以这个数据类型的字段可以存放这条记录最后被修改的时间。

数据类型的属性

MySQL关键字 含义
NULL 数据列可包含NULL值
NOT NULL 数据列不允许包含NULL值
DEFAULT 默认值
PRIMARY KEY 主键
AUTO_INCREMENT 自动递增,适用于整数类型
UNSIGNED 无符号
CHARACTER SET name 指定一个字符集

创建数据表

CREATE TABLE table_name (column_name column_type); //数据库课程中的三张表 create  table  Student( Sno  char(12)  primary  key, Sname  char(8)  unique,  #sql server写法,mysql则在m末尾加unique(Sname) Age  smallint, Sex  char(2)); create  table  course( Cno   char(4), Cname  char(20)  not  null, Teacher  char(8)); create  table  SC( Sno  char(12), Cno  char(4), Grade  smallint, Primary  key(sno,cno));   //联合主键,列名不区分大小写 

删除数据表

DROP TABLE table_name ;

插入数据

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
//数据库作业中的例子


insert into course(Cno,cname,teacher)
values('1','信息系统','LIU');
insert into course(Cno,cname,teacher)
values('2','数学','LIU');
insert into course(Cno,cname,teacher)
values('3','数据库','LI');

insert into Student(Sno,sname,age,sex)
values('2017010901','李林',18,'男');
insert into Student(Sno,sname,age,sex)
values('2017010902','李勇',24,'男');
insert into Student(Sno,sname,age,sex)
values('2017010903','刘英',18,'女');
insert into Student(Sno,sname,age,sex)
values('2017010904','刘翔',25,'男');
insert into Student(Sno,sname,age,sex)
values('s3','贝贝',24,'女');
insert into Student(Sno,sname,age,sex)
values('2017010905','wang',23,'男');

insert into SC(Sno,Cno,grade)
values('2017010901','1','95');
insert into SC(Sno,Cno,grade)
values('2017010903','1',90);
insert into SC(Sno,Cno,grade)
values('2017010902','1',90);
insert into SC(Sno,Cno,grade)
values('2017010904','1',90);
insert into SC(Sno,Cno,grade)
values('2017010905','1',90);
insert into SC(Sno,Cno,grade)
values('s3','1',95);
insert into SC(Sno,Cno,grade)
values('2017010905','3',90);
insert into SC(Sno,Cno,grade)
values('s3','3',90);

insert into SC(Sno,Cno,grade)
values('2017010901','2','95');

insert into SC(Sno,Cno,grade)
values('2017010901','3','95');



查询数据

/*websites  表名   NAME alexa url country  字段*/
SELECT * FROM websites;                      /* 查询表所有数据 */

SELECT NAME FROM websites;                   /* 查询表字段数据 */

SELECT * FROM websites where name = "广西";   /* 查询表字段下条件数据 */

SELECT * from websites where name like "_o%"; /* 模糊查询表下数据 */

SELECT * FROM websites where id BETWEEN "1" AND "5";    /* 查询表下字段范围数据 */

SELECT * FROM websites WHERE name in ("广西","百度");    /* 查询表字段下固定条件数据 */

SELECT DISTINCT country FROM Websites;                  /* 查询去重值 */

SELECT * FROM Websites WHERE country = "CN" AND alexa > 50;  /*查询表下范围条件数据*/

SELECT * FROM Websites WHERE country = "USA" OR country="sh"; /* 查询表下条件不同值 */

SELECT * FROM Websites ORDER BY alexa;                      /* 查询表下值排序结果 */

SELECT * FROM Websites ORDER BY alexa DESC;                 /* 查询表下排序结果降序 */

SELECT * FROM Websites LIMIT 2;      /* 查询表下范围数据 */

SELECT name as zzz from websites;    /*别名查询表下数据*/


WHERE字句

操作符 描述 实例
= 等号,检测两个值是否相等,如果相等返回true (A = B) 返回false。
<>, != 不等于,检测两个值是否相等,如果不相等返回true (A != B) 返回 true。
> 大于号,检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true (A > B) 返回false。
< 小于号,检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true (A < B) 返回 true。
>= 大于等于号,检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true (A >= B) 返回false。
<= 小于等于号,检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true (A <= B) 返回 true。

如果我们想在 MySQL 数据表中读取指定的数据,WHERE 子句是非常有用的。

使用主键来作为 WHERE 子句的条件查询是非常快速的。

如果给定的条件在表中没有任何匹配的记录,那么查询不会返回任何数据。

//1、检索LIU老师所授的课程号和课程名
select Cno,Cname from course
where course.teacher='LIU';

//2、检索年龄大于23岁的男学生的学号和姓名
select Sno,Sname from Student
where Age>23 and sex='男';

//3、 检索学号为S3学生所学课程的课程名与任课教师名
select cname,teacher from course,SC
where(course.Cno=SC.Cno and Sno='s3' );

//4、 检索至少选修LIU老师所授课程中一门课程的女学生姓名
select sname from Student,SC
where Student.Sno=SC.Sno and sex='女' and (Cno='1' or Cno='2');

//5、 检索WANG同学不学的课程的课程号
select Cno from course
where Cno not in (select SC.Cno from course,SC,Student
where SC.Sno=Student.Sno and sname ='wang' )

UPDATA字句

只能改,增删用ALTER;

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause] update SC set grade=59 where sno='2017010901';

DELETE字句

可以认为是元组层面的操作;

DELETE FROM table_name [WHERE Clause]

delete,drop,truncate 都有删除表的作用,区别在于:

  •  1、delete 和 truncate 仅仅删除表数据,drop 连表数据和表结构一起删除,打个比方,delete 是单杀,truncate 是团灭,drop 是把电脑摔了。
  •  2、delete 是 DML 语句,操作完以后如果没有不想提交事务还可以回滚,truncate 和 drop 是 DDL 语句,操作完马上生效,不能回滚,打个比方,delete 是发微信说分手,后悔还可以撤回,truncate 和 drop 是直接扇耳光说滚,不能反悔。
  •  3、执行的速度上,drop>truncate>delete,打个比方,drop 是神舟火箭,truncate 是和谐号动车,delete 是自行车。

LIKE字句

模糊查找:

SQL LIKE 子句中使用百分号 %字符来表示任意字符,类似于UNIX或正则表达式中的星号 *

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

like 匹配/模糊匹配,会与 % 和 _ 结合使用。

'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的

查询以 java 字段开头的信息。

SELECT * FROM position WHERE name LIKE 'java%';

查询包含 java 字段的信息。

SELECT * FROM position WHERE name LIKE '%java%';

查询以 java 字段结尾的信息。

SELECT * FROM position WHERE name LIKE '%java'; 

UNION字句

 

UNION 语句:用于将不同表中相同列中查询的数据展示出来;(不包括重复数据)

UNION ALL 语句:用于将不同表中相同列中查询的数据展示出来;(包括重复数据)

使用形式如下:

SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称;
SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称; 

ORDER BY语句

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE Clause]
 ORDER BY field1, [field2...] [ASC [DESC]] 默认ASC(升序),可以在最后加DESC(降序)的参数实现降序; 

GROUP BY语句

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。、

 

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name; //数据库课程里的 //6、	检索至少选修两门课程的学生学号 select Sno from SC group by Sno having count(*)>=2;

1、group by 可以实现一个最简单的去重查询,假设想看下有哪些员工,除了用 distinct,还可以用:

SELECT name FROM employee_tbl GROUP BY name;

返回的结果集就是所有员工的名字。

2、分组后的条件使用 HAVING 来限定,WHERE 是对原始数据进行条件限制。几个关键字的使用顺序为 where 、group by 、having、order by ,例如:

SELECT name ,sum(*)  FROM employee_tbl WHERE id<>1 GROUP BY name  HAVING sum(*)>5 ORDER BY sum(*) DESC;



正则表达式

模式 描述
^ 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性,^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。
$ 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性,$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。
. 匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符,请使用象 ‘[.\n]’ 的模式。
[…] 字符集合。匹配所包含的任意一个字符。例如, ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
[^…] 负值字符集合。匹配未包含的任意字符。例如, ‘[^abc]’ 可以匹配 “plain” 中的’p’。
p1|p2|p3 匹配 p1 或 p2 或 p3。例如,’z|food’ 能匹配 “z” 或 “food”。'(z|f)ood’ 则匹配 “zood” 或 “food”。
* 匹配前面的子表达式零次或多次。例如,zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。
+ 匹配前面的子表达式一次或多次。例如,’zo+’ 能匹配 “zo” 以及 “zoo”,但不能匹配 “z”。+ 等价于 {1,}。
{n} n 是一个非负整数。匹配确定的 n 次。例如,’o{2}’ 不能匹配 “Bob” 中的 ‘o’,但是能匹配 “food” 中的两个 o。
{n,m} m 和 n 均为非负整数,其中n <= m。最少匹配 n 次且最多匹配 m 次。

查找name字段中以’st’为开头的所有数据:

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以’ok’为结尾的所有数据:

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok

查找name字段中包含'mar'字符串的所有数据:

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据:

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok


ALTER命令

表级操作。操作表的字段,等级比较高。

//数据库作业

//修改基本表
 student表中加入属性Sdeptchar型)

   ②在SC表中,建立两个外码约束,Sno是外码,被参照表是Student(sno);Cno是外码,被参照表是Course(Cno)。

外码就存在外码约束。不能乱删和乱改。

 

ALTER TABLE Student ADD Sdept char;

ALTER TABLE SC add constraint  foreign key (Sno) references Student(Sno);

ALTER TABLE SC add constraint  foreign key (C#) references course(C#);


索引

//数据库作业

 

建立索引
student表上建立关于属性S#的唯一索引。
 course表上建立关于C#的唯一索引。

create unique index sstu on student(Sno); create unique index ssc on course(Cno);

;
查找name字段中包含'mar'字符串的所有数据:


查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据:



ALTER命令

表级操作。操作表的字段,等级比较高。

//数据库作业

//修改基本表
 student表中加入属性Sdeptchar型)

   ②在SC表中,建立两个外码约束,Sno是外码,被参照表是Student(sno);Cno是外码,被参照表是Course(Cno)。

外码就存在外码约束。不能乱删和乱改。

 

ALTER TABLE Student ADD Sdept char;

ALTER TABLE SC add constraint  foreign key (Sno) references Student(Sno);

ALTER TABLE SC add constraint  foreign key (C#) references course(C#);


索引

//数据库作业

 

建立索引
student表上建立关于属性S#的唯一索引。
 course表上建立关于C#的唯一索引。


;


ALTER命令

表级操作。操作表的字段,等级比较高。

//数据库作业

//修改基本表
 student表中加入属性Sdeptchar型)

   ②在SC表中,建立两个外码约束,Sno是外码,被参照表是Student(sno);Cno是外码,被参照表是Course(Cno)。

外码就存在外码约束。不能乱删和乱改。

 

ALTER TABLE Student ADD Sdept char;

ALTER TABLE SC add constraint  foreign key (Sno) references Student(Sno);

ALTER TABLE SC add constraint  foreign key (C#) references course(C#);


索引

//数据库作业

 

建立索引
student表上建立关于属性S#的唯一索引。
 course表上建立关于C#的唯一索引。


;

查找name字段中包含’mar’字符串的所有数据:


查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据:



ALTER命令

表级操作。操作表的字段,等级比较高。

//数据库作业

//修改基本表
 student表中加入属性Sdeptchar型)

   ②在SC表中,建立两个外码约束,Sno是外码,被参照表是Student(sno);Cno是外码,被参照表是Course(Cno)。

外码就存在外码约束。不能乱删和乱改。

 

ALTER TABLE Student ADD Sdept char;

ALTER TABLE SC add constraint  foreign key (Sno) references Student(Sno);

ALTER TABLE SC add constraint  foreign key (C#) references course(C#);


索引

//数据库作业

 

建立索引
student表上建立关于属性S#的唯一索引。
 course表上建立关于C#的唯一索引。


				
转载原创文章请注明,转载自: 静沐暖阳 » MySQL学习(持续更新中…)
Not Comment Found