mysql

1、mysql存储结构：数据库 -> 表 -> 数据 sql语句

2、管理数据库：

增加： create database 数据库 default character utf8;

删除： drop database 数据库;

修改： alter database 数据库 default character gbk; 只能修改数据的属性，不能修改名字

查询： show databases ；查询所有的数据库

　　　　　　　　　 show create database 数据库; 插寻创建数据库的语句

3、管理表：

选择数据库：use 数据库;

创建： create table 表(字段名1 字段类型,字段名2 字段类型......)[charset 属性（一般修改字符集）];

　　　　　　　　　　eg:　 create table student(

　　　　　　　　　　　　　　　　id int,

　　　　　　　　　　　　　　　　name varchar(11),

　　　　　　　　　　　　　　　　age int

　　　　　　　　　　　　　　);　

删除： drop table 表1,表2;

修改：

添加字段： alter table 表 add [column] 字段名字段类型;

　　　　　　　　　　　　　　eg:　　alter table student add class varchar(11); 给学生表添加一个班级字段

删除字段: alter table 表 drop [column] 字段名;

　　　　　　　　　　　　　　eg:　　alter table student drop class; 删除班级字段

修改字段类型： alter table 表 modify 字段名新的字段类型;

　　　　　　　　　　　　　　eg: 　　alter table student modify name int; 把name字段的类型改为int 型

修改字段名称： alter table 表 change 旧字段名新字段名字段类型;

　　　　　　　　　　　　　　eg: 　　alter table student change id name varchar(11); 把id字段改为那么字段，并把name字　　　　段设置为varchar类型

修改表名称： alter table 表 rename [to] 新表名;

　　　　　　　　　　　　　　　　或者 rename table 老表名 to 新表名

　　　　　　　　　　　　　　eg:alter table student rename to class; 把student 表改为class表

查询：

show tables ；查询所有表

　　　　　　　　　　　　 desc student; 查询表的详情

4、管理数据:

增加：　

　　　　　　　　　　格式1： insert into 表 valuse(字段值1，字段值2，…… 字段值了）；

　　　　　　　　　　　注意：

　　　　　　　　　　　　　　　默认插入全部字段,
　　　　　　　　　　　　　必须保证values后面的内容的类型和顺序和表结构中的一致
　　　　　　　　　　　　　若字段类型为数字,可以省略引号　

　　　　　　　　　　eg:

　　　　　　　　　　　　insert into student valuse(1,'jack',18);

　　　　　　　　　　　　insert into student valuse(2,'tom',19);

　　　　　　　　　　　　insert into student valuse(3,'jmary',20);

　　　　　　　　　　格式2： insert into 表(字段1，字段2,。。。) values(值1，值2.……);

　　　　　　　　　　注意:
　　　　　　　　　　　　插入指定的字段
　　　　　　　　　　　　必须保证values后面的内容的类型和顺序和表名后面的字段的类型和顺序保持一致.

　　　　　　　　　　eg：

　　　　　　　　　　　　insert into student (id, name)values(4,'jon');

　　　　　　　　　　　　insert into student(id)values(5);

　　　　　　　　　　　　insert into student(id,name,age) values(6,'helon',21);

删除： delete from 表 [where 条件];

　　　　　　　　　　eg: delete from student where id =5; 删除id=5的数据

修改： update 表 set 字段1=值1，字段2=值2...... [where 条件];

　　　　　　　　　　eg: update student set name = 'jack' where id =3; 把id=3的这条记录的名字改为jack

查询：关键词:select

　　　　　　　　　　初始化环境：创建一张新的商品表

　　　　　　　　　　　　　creat table products(

　　　　　　　　　　　　　　　　　id int,

　　　　　　　　　　　　　　　　　name varchar(20),

　　　　　　　　　　　　　　　　　price double,

　　　　　　　　　　　　　　　　）；

　　　　　　　　　　　insert into products values (1,'泰国大榴莲',98);
　　　　　　　　　　 insert into products values (2,'新疆大枣',38);
  　　　　　　　　　　 insert into products values (3,'新疆切糕',68);
  　　　　　　　　　　 insert into products values (4,'十三香',10);
  　　　　　　　　　　 insert into products values (5,'老干妈',20);
  　　　　　　　　　　 insert into products values (6,'豌豆黄',20);

1、所有字段： select * from 表;

　　　　　　　　　　　　　　　eg: select *from products; 查询products表中的所有数据

2、指定字段： select 字段1,字段2.... from 表;

　　　　　　　　　　　　　　　eg: select name,price from student; 查询student表中的所有商品名字和价格

3、指定别名： select 字段1 as 别名 from 表;

　　　　　　　　　　　　　　 eg：select name as '商品名',price as '价格' from products;

4、合并列： select (字段1+字段2) from 表;

5、去重： select distinct 字段 from 表;

　　　　　　　　　　　　　　eg:select distinct name from products; 去掉商品中重复的名字

6、条件查询：

a)逻辑条件：and(与) or（或）

select * from 表 where 条件1 and/or 条件2

b）比较条件： > < >= <= = <> between and(在。。。之间)

select * from 表 where servlet>=90;

c）判空条件：

判断null： is null / is not null

判断空字符串： ='' / <>''

d）模糊条件： like

%: 替换任意个字符

_: 替换一个字符

　　　　7 分页查询：limit 起始行,查询行数

起始行从0开始

8 排序： order by 字段 asc/desc

asc: 正序，顺序

desc：反序，倒序

9 分组查询：group by 字段

10: 分组后筛选： having 条件

5、SQL语句的分类：

DDL：数据定义语言

create / drop / alter

DML：数据操作语句

insert / delete /update / truncate

DQL：数据查询语言：

select / show

6、数据约束——默认值：当用户对使用默认值的字段不插入值的时候，就使用默认值。

　　create table student(

　　　　id int,

　　　　name varchar(11)，

　　　　adress carchar(11) default '重庆 ' -- 设置重庆为默认值，即用户在插入数据时，不添加地址值的话就默认添加“重庆”，

　　　　　　　　　　　　　　　　　　　　　　　地址栏会显示重庆

　　　　) charset utf8;

　　　　eg:insert into student(id, name) value(1,'jack'); 这个时候地址栏字段的值会显示默认值 -- 重庆

注意：

　　　　1、对默认值字段插入 null 是可以的,这时候会显示 null

　　　　　　　　eg: insert into student( id, name, adress) values(2,'张三'，null);

　　　　2、对默认值字段也可以插入非null,这时候会显示插入的值

　　　　　　　　eg：insert into student(id,name,address) values(3,'李四’，’北京‘）;

7、数据约束—— 非空：限制字段必须赋值，不能为null

　　　　create table student(

　　　　　　id int,

　　　　　 name varchar(11),

　　　　　　address varchar (11) not null -- 非空约束，即输入不能为null，必须输入一个有效值

　　　　) charset utf8;

　　　　eg: insert into student(id,name) values(1,jack);这是错误的

　　　　　 insert into student(id,name,address) valuse(2,'李四‘，null);这也是错误的

　　　　　　insert into student(id,name,address)valuse(3,'tom'，’重庆‘）；这是正确的

　　注意：非空字段必须赋值，且不能为null

8、数据约束——唯一：对字段的值不能重复，对 null 不起作用

　　　　create table student (

　　　　　　　　id int unique,-- 唯一约束,保证字段值不重复

　　　　　　　　name varchar(11),

　　　　　　　　address varchar(11)

　　　　　　) charset utf8;

　　eg: insert into student(id,name) values(1,'tom');

　　　　insert into student(id,name) vlaues(1,'jack'); 这个时候会报错, id不能有重复值

　　注意：　　　

　　　　　　1）唯一字段可以插入null

2）唯一字段可以插入多个null

9、数据约束——主键：非空+唯一

　　　　create table student (

　　　　　　id int primary key， -- 主键约束，保证id非空和唯一

　　　　　　name varchar(11),

　　　　　　address varchar(11)

　　　　) charset utf8;

　　eg:

　　　　　　insert into student (id, name, adress) values(1,'tom','cq');

　　　　　　insert into student(name)vlause('jack'); 错误，违反了主键的非空特性

　　　　　　insert into student(id,name) values(1,'mary');错误，违反了主键的唯一性

注意：

　　　1）通常情况下，每张表都会设置一个主键字段。用于标记表中的每条记录的唯一性。

2）建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的id字段。

10、数据约束——自增长：自动递增

　　　　create table student(

　　　　　　　　id int primary key auto_increment ，-- 主键自动增长，从0开始

　　　　　　　　//id int(4) zerofill primary key auto_increment -- 主键自动增长， id的长度为4为，不满足的位数以 0 填充

　　　　　　　　name varchar(11)

　　　　　　）charset utf8；

　　eg: 　自增长字段可以不用赋值，自动递增　　　

　　　　INSERT INTO student(NAME) VALUES('张三');

　　　　INSERT INTO student(NAME) VALUES('李四');

　　　　INSERT INTO student(NAME) VALUES('王五');

注意： 2个删除

　　delete from student; 不影响自增约束，即删除只会删除数据不会删除记录。比如说你删除第3条记录，你新插入一条记录会从第4　　　　条开始。

　　truncate table student; 可以影响自增长约束，即会删除数据也会删除记录。比如说你删除第3条记录，你新插入一条记录会从第3条开始。

11、数据约束——外键：约束两种表的数据，一般外键设置在副表中　　　

　　　　出现两种表的情况：

解决数据冗余高问题：独立出一张表

例如：员工表和部门表

问题出现：在插入员工表数据的时候，员工表的部门ID字段可以随便插入！！！！！

使用外键约束：约束插入员工表的部门ID字段值

　　　　　　解决办法：在员工表的部门ID字段添加一个外键约束

部门表（主表）约束表

　　create table dept(

　　　　id int primary key,--设置为主键

　　　　deptName varchar（11）

）;

员工表（副表/从表）被约束的表

　　create table emplovee(

　　　　id int primary key,

　　　　empName varchar(11),

　　　　deptId int, -- 把部门名称改为部门id，使2张表关联起来

　　　　-- 声明一个外键约束

　　　　constraint enply_ dept_fk (外键名称) foreign key( deptId【外键，副表中被约束的字段】）references 【dept(id)，参考表，即主表， id是指约束的字段，这里其实写的就是主表（约束字段）】

　　);

　　eg：　　

　　　　INSERT INTO dept(id,deptName) VALUES(1,'软件开发部');

　　　　INSERT INTO dept(id,deptName) VALUES(2,'应用维护部');

　　　　INSERT INTO dept(id,deptName) VALUES(3,'秘书部');

　　　　INSERT INTO employee VALUES(1,'张三',1);

　　　　INSERT INTO employee VALUES(2,'李四',1);

　　　　INSERT INTO employee VALUES(3,'王五',2);

　　　　INSERT INTO employee VALUES(4,'陈六',3);

注意：

　　1）被约束的表称为副表，约束别人的表称为主表，外键设置在副表上的！！！

2）主表的参考字段通用为主键！

3）添加数据： 先添加主表，再添加副表

4）修改数据： 先修改副表，再修改主表

5）删除数据： 先删除副表，再删除主表

12、级联操作

　　当有了外键约束的时候，必须先修改或删除副表中的所有关联数据，才能修改或删除主表！但是，我们希望直接修改或删除主表数据，从而影响副表数据。可以使用级联操作实现！！！

　　级联修改： on update cascade

级联删除： on delete cascade

　　create table dept(

　　　　id int,

　　　　deptName varchar(11)

　　)；

　　create table employee(

　　　　id int,

　　　　empName varchar(11),

　　　　deptId int,

　　　　constraint emplyee_dept_fk foreign key(deptId) references dept(id) on update cascade on delete cascade,级联操作，直接修改或删除主表数据，从而影响副表数据

　　　　注意:级联操作必须在外键的基础上才能使用

　　　　)；

　　eg:级联修改

　　update dept set id =5 where id =4; 将id=4 的全部修改为5，这个时候员工表id=4 的也会同时修改为5

　　级联删除

　　delete from dept where id =1;将id =1 的部门删除，这个时候员工表中部门id =1 的数据也会删除

13、三大范式（即规范）

　　　　　　第一范式：要求表的每个字段必须是不可分割的独立单元

student : name -- 违反第一范式

张小名|狗娃

sutdent ： name old_name --符合第一范式

张小名狗娃

第二范式：在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。

employee（员工）: 员工编号员工姓名部门名称订单名称 --违反第二范式

员工表：员工编号员工姓名部门名称

订单表：订单编号订单名称 -- 符合第二范式

第三范式：在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。

员工表：员工编号（主键）员工姓名部门编号部门名 --符合第二范式，违反第三范式（数据冗余高）

员工表：员工编号（主键）员工姓名部门编号 --符合第三范式（降低数据冗余）

部门表：部门编号部门名

14、多表查询

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

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

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

　　1、多表查询规则　　

　　　　　　1）确定查询哪些表

　　　　　　2）确定查询哪些字段

　　　　　　3）表与表之间连接条件 (规律：连接条件数量是表数量-1)

　　2、交叉连接查询:cross join（不推荐。产生笛卡尔乘积现象，有些是重复记录）

　　　　eg：select empName, deptId from employee ,dept;

　　3、内连接查询: inner join (只有满足条件的才会显示，使用最频繁）

　　　　eg:（隐式内连接，不使用inner join)

　　　　　　select empName,deptId --确定第2个规则：确定查询哪些字段

　　　　　　　　　　from employee,dept -- 确定第1个规则：确定查询哪些表

　　　　　　　　　　where employee.deptId =dept.id; -- 确定第3个规则：表与表之间的关系

　　　　eg:显示内连接，使用 inner join

　　　　　　select empName , deptId === 确定查询哪个字段

　　　　　　　　　from employee

　　　　　　　　　inner join dept ====确定从哪些表查询

　　　　　　　　 on enployee.deptId = dept.id; === 确定表与表之间的关系

　　　　eg: 使用别名查询

　　　　　　select empName,deptId

　　　　　　　　from employee e

　　　　　　　　　inner join dept d

　　　　　　　　on e.deptId = d.id;

15、外连接 outer join

左外连接查询:　　

　　　　　　　　left outer join=left join

　　　　　　　　使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null

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

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

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

　　eg：select d.deptName,e.empName

　　　　　　from dept d

　　　　　　left outer join employee e

　　　　　　on d.id =e.deptId;

　　注意：左外连接：左表的数据一定会完成显示！

　　右[外]连接查询:

　　　　　　right outer join=right join

　　　　　　使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null

　　　　　　注意：右外连接：右表的数据一定会完成显示！

　　select d.deptName, e.empName

　　　　　　from employee e

　　　　　　right outer join dept d

　　　　　　on d.id=e.deptId;

自连接查询:在自身的表中查询

需求：查询员工及其上司，在自身表中添加bossId字段并插入值

-- 预期结果：

-- 张三 null

-- 李四张三

-- 王五李四

-- 陈六王五

　　eg: select e.empName,b.empName

　　　　　　from employee e

　　　　　　left outer join employee b

　　　　　　on e.bossId =b.id;

16、存储过程

　　1、什么是存储过程

　　　　储存过程，就是带有逻辑的sql语句，之前的sql没有条件，没有判断，存储过程带上了流程控制语句（if，while）

　　2、存储过程的特点

　　　　1、执行效率非常快，存储过程是在数据库的服务器段执行的！！

　　　　2、移植性比较差！不同数据库的存储过程是不能移植的。

　　3、存储过程语法

eg:创建存储过程

　　1、声明存储过程的结束符

　　　　delimiter $

　　2、创建存储过程名称（参数列表）

　　　　create procedure pro_test()

　　3、开始

　　　　begin

　　4、写sql语句+流程控制，可以写多个sql

　　　　select*from employee;

　　5、结束符

　　　　end$

　　6、执行存储过程

　　　　call pro_test();

　　eg:不带参数的存储过程

　　delimiter$

　　create procedure pro_test()

　　begin

　　　　select*fromemployee;

　　end$

　　call pro_test();

　　eg：带有输入参数的存储过程

　　参数：

　　　　IN：表示输入参数，可以携带数据带存储过程中

　　　　OUT：表示输出参数，可以从存储过程中返回结果

　　　　INOUT：表示输入输出参数，既可以输入功能，也可以输出功能

　　delimiter$

　　create procedure pro_findById(in eid int)== in:输入参数

　　begin

　　　　select*fromemployee where id =eid;

　　end$

　　调用

　　call pro_findById(4);

　　eg:带有输出参数的存储过程

　　delimiter$

　　create procedure pro_testOut(out srt varchar(11))---out:输出参数

　　begin

　　　　set str='helloJava'; 给参数赋值

　　end$

　　call pro_testOut;

　　4、删除存储过程 :drop procedure 参数名

　　drop procedure pro_testOut;

17、mysql的变量

　　1、全局变量（内置变量）：mysql数据库内置的变量（对所有的连接都起作用）

　　　　查看所有的全局变量： show variables;

　　　　查看某个全局变量：select @@变量名

　　　　修改全局变量： set 变量名 = 新值

　　　　character_set_client: mysql服务器的接收数据的编码

character_set_results：mysql服务器输出数据的编码

　　2、会话变量：只存在于当前客户端与数据库服务器端的一次链接中，如果连接断开，那么会话变量就会全部丢失

　　　　定义会话 @变量 =值

　　　　查看会话变量：select @变量

　　3、局部变量：在存储过程中使用的变量就是局部变量，只要存储过程执行完毕，局部变量就丢失

　　eg:定义一个会话变量name ,并使用name 会话变量接收存储过程的返回值

　　　　call pro_test@name);

　　　　select @ name; 查看变量值

　　eg:带有输入输出参数的存储过程

　　　　delimter $

　　　　create procedure peo_testInOut(inout n int) ---inout:输入输出参数

　　　　begin　　

　　　　　　select n; 查看变量

　　　　　　set n =500;

　　　　end$

　　　　set @n=10; 调用

　　　　call pro_testInOut(@n);

　　　　select @n;

　　eg:带有条件判断的存储过程
　　　　需求：输入一个整数，如果1，则返回“星期一”,如果2，返回“星期二”,如果3，返回“星期三”。其他数字，返回“错误输入”;
　　　　DELIMITER $
　　　　CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
　　　　BEGIN
　　　　　　IF num=1 THEN
  　　　　　　　　　SET str='星期一';
　　　　　　ELSEIF num=2 THEN
  　　　　　　　　　SET str='星期二';
　　　　　　ELSEIF num=3 THEN
  　　　　　　　　　SET str='星期三';
　　　　　　ELSE
  　　　　　　　　　SET str='输入错误';
　　　　END IF;
　　　　END $

　　　　CALL pro_testIf(4,@str);

　　　　SELECT @str;

posted @ 2017-05-02 11:51 皮皮虾我们上阅读(259) 评论(0) 编辑收藏举报

刷新页面返回顶部

mysql

公告