首页 > 代码库 > sql

sql

一、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语句的分类:

*DDLData Definition Language数据定义语言

*DMLData Manipulation Language 数据操作语言

DCLData Control Language 数据控制语言

*DQLData Query Language 数据查询语言

*TPL:事务处理语言

 

六、DDL:数据定义语言

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

关键字:CREATE  ALTER DROP

6.1数据库结构操作:

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

 

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

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

查看数据库的创建细节

mysql>SHOW CREATE DATABASE mydb1;

查看当前所有的数据库

mysql>SHOW DATABASES;

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

mysql>CREATE DATABASE mydb2 CHARACTER SET gbk;

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

mysql>CREATE DATABASE mydb3 CHARACTER SET 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 SET utf8;

 

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

6.2表结构操作

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

选择数据库

mysql>USE mydb2;

创建一个员工表

mysql>CREATE TABLE employee(

id int,

name varchar(100),

gender varchar(10),

birthday date,

entry_date date,

job varchar(100),

salary float(8,2),

resume text

);

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

mysql>SHOW TABLES;

查看表的创建细节

mysql>SHOW CREATE TABLE employee;

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

mysql>ALTER TABLE employee ADD image blob;

修改job列,使其长度为60

mysql>ALTER TABLE employee MODIFY job varchar(60);

删除image列。

mysql>ALTER TABLE employee DROP image;

表名改为user

mysql>RENAME TABLE employee TO user;

修改表的字符集为gbk

mysql>ALTER TABLE user CHARACTER SET gbk;

列名name修改为username

mysql>ALTER TABLE user CHANGE name username varchar(100);

 

七、DML:数据操作语言

作用:操作的是表中的记录(数据)

关键字:INSERT UPDATE DELETE

 

MySQL:

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

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

特殊值   null

 

7.1插入数据

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

user表中插入三条员工信息

mysql>INSERT INTO user (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 user VALUES(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,‘帅锅一枚‘);

 

 

 

 

告知服务器客户端使用的编码为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 WHERE username=’zhw’;

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

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

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

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

 

7.3删除数据

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

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

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

删除表中所有记录。

方式一:

mysql>DELETE FROM user;

方式二:(属于DDL语句)

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

 

八、DQL:数据查询语言

作用:查询

关键字:SELECT

8.1简单查询

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

查询表中所有学生的信息。

mysql>SELECT * FROM student;

查询表中所有学生的姓名和对应的英语成绩。

mysql>SELECT name,english FROM student;   

过滤表中重复数据。

msyql>SELECT DISTINCT english FROM student;

在所有学生数学分数上加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;

 

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

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

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

mysql>SELECT * FROM student WHERE english>90;

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

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

 

查询英语分数在 8090之间的同学。

mysql>SELECT * FROM student WHERE english BETWEEN 80 AND 90;

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

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

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

mysql>SELECT * FROM student WHERE name LIKE ‘%’;

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

mysql>SELECT * FROM student WHERE math>80 AND chinese>80;

 

 

对数学成绩排序后输出。

mysql>SELECT name,math FROM student ORDER BY math;

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

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

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

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

 

九、数据完整性

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

2、分为三类

l 实体完整性

l 域完整性

l 参照完整性

9.1实体完整性

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

 

主键的特点:不能为null,必须有值,且不能重复。

主键分类:

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

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

CREATE TABLE t2(

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

name varchar(100)

);

 

CREATE TABLE t4(

id int,

name varchar(100),

PRIMARY KEY(id)

);

 

 

CREATE TABLE t3(

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

name varchar(100)

);

 

9.2域完整性

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

 

非空约束:not null

唯一约束:unique

 

CREATE TABLE t6(

id int PRIMARY KEY auto_increment,

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

gender varchar(10) not null  非空约束

);

9.3参照完整性(多表)

多表设计

一对一(311教室和20130405班级,两方都是一):在任意一方保存另一方的主键

一对多、多对一(班级和学生,其中班级为1,学生为多):在多的一方保存一的一方的主键

多对多(教师和学生,两方都是多):使用中间表,保存对应关系

表间的关系:

一对多(用的最多)

多对多(用的很多)

一对一(几乎不用)

 

9.3.1一对多:部门和员工的关系

CREATE TABLE department(

id int primary key,

name varchar(100)

);

 

CREATE TABLE employee(

id int primary key,

name varchar(100),

salary float(8,2),

department_id int,

constraint department_id_fk foreign key (department_id) references department(id)

);

 

 

 

9.3.2多对多:老师和学员

CREATE TABLE teacher(

id int primary key,

name varchar(100),

salary float(8,2)

);

CREATE TABLE student(

id int primary key,

name varchar(100),

grade varchar(10)

);

CREATE TABLE teacher_student(

t_id int,

s_id int,

CONSTRAINT t_id_fk FOREIGN  KEY(t_id) REFERENCES  teacher(id),references

CONSTRAINT s_id_fk FOREIGN  KEY(s_id) REFERENCES  student(id),

PRIMARY  KEY(t_id,s_id)

);

 

 

9.3.3一对一(了解)

l 按照外键关联:

CREATE TABLE person(

id int primary key,

name varchar(100)

);

CREATE TABLE idcard(

id int primary key,

number varchar(20),

person_id int unique,

CONSTRAINT person_id_fk FOREIGN KEY(person_id) REFERENCES person(id)

);

 

 

l 按照主键关联:

CREATE TABLE person(

id int primary key,

name varchar(100)

);

CREATE TABLE idcard(

id int primary key,

number varchar(20),

CONSTRAINT person_id_fk FOREIGN KEY(id) REFERENCES person(id)

);

 

 

十、DQL:数据查询复杂的(多表)

10.1连接查询(面试几率很大)

 

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

约定:表1在连接类型的左边,称之为左表

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

 

交叉连接:cross join

返回左表和右表的笛卡尔积(左表5条记录 ,右表6条记录 5*6=30条)

select * from customer,orders;

select * from customer cross join orders;

内连接: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;

 

外连接:outer join

左外连接:left outer join=left join

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

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

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

 

右外连接:right outer join=right join

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

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

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

 

10.2子查询

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

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

 

select id from customer where name=’陈冠希’;

select * from orders where customer_id=1;

 

子查询:

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

 

10.3联合查询

union关键字。

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

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

 

10.4报表查询(使用数据库提供的函数)

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

msyql>SELECT COUNT(*) FROM student;

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

mysql>SELECT COUNT(*) FROM student WHERE math>90;

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

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

 

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

mysql>SELECT SUM(math) FROM student;

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

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

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

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

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

mysql>SELECT SUM(chinese)/COUNT(*) FROM student;

 

求一个班级数学平均分?

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) FROM orders GROUP BY product;

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

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

 

sql