MySQL

一、SQL

Structured Query Language 结构化查询语言

作用：与数据库进行交互

知识扩展：

SQL标准，由ANSI（美国标准学会，属于ISO的核心成员）进行管理和维护的。

数据库厂商都支持该标准，并进行了扩展。扩展的部分，一般称之为方言。

 

SQL标准和方言：普通话和方言。

二、常用的数据库

1、Oracle

2、DB2

3、MySQL

4、SQL Server

三、安装MySQL

验证是否安装成功：

登录数据库：

shell>mysql -u root -p

四、数据库的基本概念：

知识点：使用关键字作为表名或数据库名或字段名，使用``（反引号）引起来

约定：shell>命令 windows命令

  mysql>命令 mysql命令或语句

User类对应User表结构

User对象对应User表中的一条记录

五、SQL语句的分类：

*DDL：Data Definition Language数据定义语言

*DML：Data Manipulation Language 数据操作语言

DCL：Data Control Language 数据控制语言 ( 赋予一种权限 )grant  revoke

*DQL：Data Query Language 数据查询语言

*TPL：事务处理语言

六、DDL：数据定义语言

作用：定义数据库或表结构用的

关键字：CREATEALTER DROP

6.1数据库结构操作：

--------------------------------

 注释：#

创建一个名称为mydb1的数据库。

mysql>CREATE DATABASE mydb1;       (字符集采用数据库默认的--安装时的那个)

查看数据库的创建细节

mysql>SHOW CREATE DATABASE mydb1;

查看当前所有的数据库

mysql>SHOW DATABASES;

创建一个使用gbk字符集的mydb2数据库。

mysql>CREATE DATABASE mydb2 CHARACTERSET gbk;

查看数据库中所有的校对规则：

show collation;

创建数据库mydb2，字符集用gbk，校验规则用gbk_bin:

create database mydb2 character set gbk collate

查看数据库中部分的校对规则：中文的：

show collation like '%gb%';

创建一个使用utf8字符集，并带校对规则的mydb3数据库。

mysql>CREATE DATABASE mydb3 CHARACTERSET utf8 COLLATE utf8_general_ci;

查看当前数据库服务器中的所有数据库

mysql>SHOW DATABASES;

查看前面创建的mydb2数据库的定义信息

mysql>SHOW CREATE DATABASE mydb2;

删除前面创建的mydb1数据库

mysql>DROP DATABASE mydb1;

查看服务器中的数据库，并把mydb2的字符集修改为utf8;

mysql>ALTER DATABASE mydb2 CHARACTER SETutf8;

 

--------------------------------

6.2表结构操作

创建表之前要先选择数据库。

选择数据库

mysql>USE mydb2;

创建一个员工表  (字段之间要用逗号隔开)

mysql>CREATE TABLE employee(

       id int,

       name varchar(100),

       gender varchar(10),

       birthday date,

       entry_datedate,

       jobvarchar(100),

       salaryfloat(8,2),

       resumetext

);

查看所有的(*)的字段从表t4：

select * from t4:

查看当前数据库中的所有表

mysql>SHOW TABLES;

查看表的创建细节

mysql>SHOW CREATE TABLE employee;

在上面员工表的基本上增加一个image列。

mysql>ALTER TABLE employee ADD imageblob;

修改job列，使其长度为60。

mysql>ALTER TABLE employee MODIFYjob varchar(60);

删除image列。

mysql>ALTER TABLE employee DROP image;

表名改为user。

mysql>RENAME TABLE employee TO user;

修改表的字符集为gbk

mysql>ALTER TABLE user CHARACTER SETgbk;

列名name修改为username

mysql>ALTER TABLE user CHANGEname username varchar(100);

 

七、DML:数据操作语言

作用：操作的是表中的记录（数据）

关键字：INSERTUPDATE DELETE

 

MySQL:

字符串类型   使用单引号引起来   ‘abcdefg’

日期时间       使用单引号引起来   ‘2001-01-08’

特殊值   null

 

7.1插入数据

---------------------------------

向user表中插入三条员工信息

mysql>INSERT INTOuser (id,username,gender,birthday,entry_date,job,salary,resume)VALUES(1,'zhw','male','1990-08-09','2014-03-29','CTO',10000,'description');

mysql>INSERT INTO userVALUES(2,'hch','female','1989-08-09','2014-03-29','CEO',10000,'aaaaaa');

mysql>INSERT INTO user(id,username,gender,birthday,entry_date,job,salary,resume) VALUES(3,'皇甫张军','male','1990-08-09','2014-03-29','UFO',10000,'帅锅一枚');

 

插入数据：

insert into t4(id,name) values(1,'张无忌')；

insert  t4(id,name) values(2,'乔峰')；into可以省略

当省略字段时，所有的字段都必须给值（自增例外）：

insert t4 values(3,'杨过','2016-8-20');

 

告知服务器客户端使用的编码为gbk

mysql>set character_set_client=gbk;

告知服务器客户端查看结果集用的编码为gbk；

mysql>set character_set_results=gbk;

 

 

7.2更新数据

------------------------------------

 

将所有员工薪水修改为5000元。

mysql>UPDATE user SET salary=5000;

将姓名为’zhw’的员工薪水修改为3000元。

mysql>UPDATE user SET salary=3000 WHEREusername=’zhw’;

将姓名为’hch’的员工薪水修改为4000元,job改为ccc。

mysql>UPDATE user SET salary=4000,job=’ccc’WHERE username=’hch’;

将”皇甫张军”的薪水在原有基础上增加1000元。  

mysql>UPDATE user SET salary=salary+1000where username=’皇甫张军’;

 

更新：

将表t4的第三条记录姓名字段改为杨康

把id为3的这条记录的名字改为杨康

update t4 set name='杨康' where id=3;

将所有记录的名字都改为东方不败：

update t4 set name='东方不败'；

修改多个字段：

update t4 set id=6,name='小风' where id=2;

7.3删除数据

--------------------------------------

删除表中名称为’zhw’的记录。

msyql>DELETE FROM user WHERE username=’zhw’;

删除表中所有记录。

方式一：

mysql>DELETE FROM user;

方式二：(属于DDL语句)

mysql>TRUNCATE TABLE user; 把整张表格摧毁，然后重建的表结构。这比一行一行的删除行要快很多

 

删除：只能删除行，不能珊瑚列

delete from t4 where id=4;

删除所有的记录：

delete from t4;

truncate table t4;

增加字段：

给表t4增加一个字段address:

alter table t4 add address varchar(100);

删除字段address：

alter table t4 drop column address;

查看表的结构：

desc t4

八、DQL：数据查询语言

作用：查询

关键字：SELECT

8.1简单查询

-----------------------------

查询表中所有学生的信息。查询所有字段用*

mysql>SELECT * FROM student;

查询表中所有学生的姓名和对应的英语成绩。查询多个字段，字段之间用，隔开

mysql>SELECT name,english FROMstudent;  

过滤表中重复数据。

msyql>SELECT DISTINCT english FROMstudent;

在所有学生数学分数上加10分特长分。

mysql>SELECT name,math+10 FROM student;

统计每个学生的总分。

mysql>SELECT name,chinese+english+math FROM student;

使用别名表示学生分数。

mysql>SELECT name AS 姓名,chinese+english+math 总分 FROM student;

给字段起别名：

select id as 编号,china as语文,english as英语,history as历史 from score;（as可以省略）

 

查询姓名为王五的学生成绩

msyql>SELECT * FROM student WHERE name='王五';

查询英语成绩大于90分的同学

mysql>SELECT * FROM student WHEREenglish>90;

查询总分大于200分的所有同学

mysql>SELECT * FROM student WHERE chinese+english+math>200;

 

查询英语分数在 80－90之间的同学。用between and是包括了界限的

mysql>SELECT * FROM student WHEREenglish BETWEEN 80 AND 90;

查询数学分数为89,90,91的同学。

mysql>SELECT * FROM student WHERE mathIN (89,90,91);

模糊查询：

select * from 表名 where 字段名 like 字段表达式（表达式要用单引号引起来）

% 表示任意字符数

_ 表示任意的一个字符

[ ] 表示在某个区间

查询所有姓李的学生成绩。

mysql>SELECT * FROM student WHERE nameLIKE ‘李%’;

查询数学分>80，语文分>80的同学。

mysql>SELECT * FROM student WHEREmath>80 AND chinese>80;

 

查询以张开头的人：

select * from stu like name like'张%';

查询姓名中含有张这个字的人

select * from stu where name like '%张%'；

查询姓名中含有张这个字的人并且姓名的长度为3个字的人：

select * from stu where name like '张__' or name like '_张_' or name like '__张';

查找姓名和性别都不同的记录：

select distince name，sex from stu;

distinct：

查询表中有几种性别：

select distinct sex from stu;

 排序：order by

对数学成绩排序后输出。

mysql>SELECT name,math FROM studentORDER BY math;

对总分排序后输出，然后再按从高到低的顺序输出

mysql>SELECT name,chinese+english+mathFROM student ORDER BY chinese+english+math DESC;

对姓李的学生语文成绩排序输出(由高到低)

msyql>SELECT name,chinese FROM student WHERE name LIKE ‘李%’ ORDER BYchinese DESC;

 升序：asc

 降序：desc

根据多个字段进行排序（语文升序，对语文成绩一样的人再进行历史降序来排,只针对语文成绩一样的记录，其他记录不参与）

select * from score order by china asc,history desc;

根据考试总分降序进行排序：

select *,china + english + history 总分 from score order by china + english + history desc;

IN：

查询家在桃花岛的或者黑木崖的人

select * from stu where address='桃花岛' or  address='黑木崖';

select * from stu where address in('桃花岛','黑木崖');

子查询：

查询没有参加考试的人：

select id,name form stu where id no in (select sid from score);

null:

查询没有地址的人：

select * from stu where address is null;

查询有地址的人：

select * from stu where address is not  null;

九、数据完整性

1、数据完整性是为了保证插入到数据中的数据是正确的，它防止了用户可能的输入错误

2、分为三类

l  实体完整性

l  域完整性

l  参照完整性

9.1实体完整性

规定表的一行（即每一条记录）在表中是唯一的实体。实体完整性通过表的主键来实现

 

主键：唯一的去区分每一条记录的一列或者多列的值

主键的特点：不能为null，必须有值，且不能重复。（唯一，非空）

主键分类：

逻辑主键：不代表实际意义，只是区分不同记录用的。比如id

业务主键：代表者具体的实际意义。比如身份证号  用户名

CREATE TABLE t2(

       idint PRIMARY KEY,#PRIMARY KEY 声明id是主键

       namevarchar(100)

);

 

CREATE TABLE t4(

       idint,

       namevarchar(100),

PRIMARY KEY(id)

);

 

 

CREATE TABLE t3(

       idint PRIMARY KEY auto_increment,#auto_increment 数据库自动增长

       namevarchar(100)

);

 

9.2域完整性

指数据库表的列（即字段）必须符合某种特定的数据类型或约束。

 

非空约束：not null

唯一约束：unique

 

CREATE TABLE t6(

       idint PRIMARY KEY auto_increment,

       usernamevarchar(100) not null unique, 非空和唯一约束

       gendervarchar(10) not null  非空约束

);

9.3参照完整性（多表）

保证一个表的外键和另一个表的主键对应

外键：另外一个表里面所引用这个表主键的那个键

表间的关系：

一对多（用的最多）

多对多（用的很多）

一对一（几乎不用）

表的约束：

自动增长：id必须是整型
create table t5

(

      id int primary key auto_increment,

      name varchar(20)

};

创建引用约束
alter table score add constraint str_score_FK foreign key(sid) references stu(id);

删除约束

alter table score drop foreign key  stu_score_FK;

#引用约束：

1.添加记录时必须先添加主表中的记录，再添加子表中的记录

2.不能更改主表中具有外键约束的记录的主键

3.删除记录的时候不允许删除具有外键关系的主表中的记录（删除的顺序应当是先删除子表中的记录，然后删除主表中的记录）

一对多：部门和员工的关系

CREATE TABLE department(

       id int primary key, 

       name varchar(100) , 

);

 

CREATE TABLE employee(

       idint primary key,

       namevarchar(100),

       salaryfloat(8,2),

       department_idint,

       CONSTRAINTdepartment_id_fk FOREIGN KEY(department_id) REFERENCES department(id)

);

多对多：老师和学员

CREATE TABLE teacher(

       idint primary key,

       namevarchar(100),

       salaryfloat(8,2)

);

CREATE TABLE student(

       idint primary key,

       namevarchar(100),

       gradevarchar(10)

);

CREATE TABLE teacher_student(

       t_idint,

       s_idint,

       CONSTRAINTt_id_fk FOREIGN KEY(t_id) REFERENCES teacher(id),

       CONSTRAINTs_id_fk FOREIGN KEY(s_id) REFERENCES student(id),

       PRIMARYKEY(t_id,s_id)

);

一对一（了解）

l  按照外键关联：

CREATE TABLE person(

       idint primary key,

       namevarchar(100)

);

CREATE TABLE idcard(

       idint primary key,

       numbervarchar(20),

       person_idint unique,

       CONSTRAINTperson_id_fk FOREIGN KEY(person_id) REFERENCES person(id)

);

l  按照主键关联：

CREATE TABLE person(

       idint primary key,

       namevarchar(100)

);

CREATE TABLE idcard(

       idint primary key,

       numbervarchar(20),

       CONSTRAINTperson_id_fk FOREIGN KEY(id) REFERENCES person(id)

);

DQL：数据查询复杂的（多表）

连接查询

基本语法形式：FROM 表1连接类型表2 [on连接条件][where筛选条件]

约定：表1在连接类型的左边，称之为左表

  表2在连接类型的右边，称之为右表

l  交叉连接：cross join

返回左表和右表的笛卡尔积（左表5条记录 ，右表6条记录 5*6=30条）

select * from customer,orders;

select * from customer cross join orders;

l  内连接：inner join

返回满足连接条件的所有记录。

隐式内连接：(不使用inner join关键字)

select c.*,o.* from customer c,orders o where c.id=o.customer_id;

显式内连接：(使用inner join关键字)

select * from customer c inner join orders o on c.id=o.customer_id;

l  外连接：outer join

左外连接:left outer join=left join

返回满足连接条件的所有记录，同时返回左表中剩余的其他记录

查询所有客户，有订单的把订单也显示出来

select * from customer c left outer join orders o onc.id=o.customer_id;

右外连接:right outer join=right join

返回满足连接条件的所有记录，同时返回右表中剩余的其他记录

查询所有订单，同时打印订单所属的客户

select * from customer c right outer join orders o onc.id=o.customer_id;

子查询

子查询也叫嵌套查询，是指在select子句或者where子句中又嵌入select查询语句

查询“陈冠希”的所有订单信息

 

select id fromcustomer where name=’陈冠希’;

select * fromorders where customer_id=1;

 

子查询：

select * from orderswhere customer_id=(select id from customer where name=’陈冠希’);

联合查询

union关键字。

联合查询能够合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复数据行的查询结果

SELECT * FROM orders WHERE price>200UNION SELECT * FROM orders WHERE customer_id=1;

报表查询（使用数据库提供的函数）

统计一个班级共有多少学生？

msyql>SELECT COUNT(*) FROM student;

统计数学成绩大于90的学生有多少个？

mysql>SELECT COUNT(*) FROM student WHEREmath>90;

统计总分大于250的人数有多少？

mysql>SELECT COUNT(*) FROM student WHERE(chinese+english+math)>250;

 

统计一个班级数学总成绩？

mysql>SELECT SUM(math) FROM student;

统计一个班级语文、英语、数学各科的总成绩

mysql>SELECTSUM(chinese),SUM(english),SUM(math) FROM student;

统计一个班级语文、英语、数学的成绩总和

mysql>SELECT SUM(chinese+english+math)FROM student;

统计一个班级语文成绩平均分

mysql>SELECT SUM(chinese)/COUNT(*) FROMstudent;

 

求一个班级数学平均分？

mysql>SELECT AVG(math) FROM student;

求一个班级总分平均分

mysql>SELECT AVG(chinese+english+math)FROM student;

 

求班级语文最高分和数学最低分

mysql>SELECT MAX(chinese) FROM student;

mysql>SELECT MIN(math) FROM student;

对订单表中商品归类后，显示每一类商品的总价

mysql>SELECT product,SUM(price) FROMorders GROUP BY product;

查询购买了几类商品，并且每类总价大于100的商品

mysql>SELECT product,SUM(price) FROMorders GROUP BY product HAVING SUM(price)>100;

备份mydb：用户名和密码都为root；
mysqldump -u root -proot mydb>d:\mydb.sql