Mysql笔记(一)

一、SQL简介

1、SQL：Structured Query Language的缩写

主流“关系"数据库

2、SQL有一套工业标准（ANSI维护）

各大数据库厂商都支持SQL的标准，但也进行扩展。

Oracle的扩展语言：方言

常用的数据库

*Oracle     甲骨文,java写的

*DB2    IBM的,java写的

*SQL Server 微软的

ProstgreSQL 面向对象数据库开源

MySQL 免费的,小....跨平台

Access office里面自带的.

SQLite    android里面的数据库

带星号的至少熟悉一个.

 

SQL标准和各个数据库的扩展：普通话和方言

3、SQL的分类

*DQL：数据查询语言

*DML：数据操作语言

*DDL：数据定义语言

DCL：数据控制语言

*TPL：事务处理语言

CCL：指针控制语言

 

二、验证MySQL安装是否成功

MySQL安装步骤,安装图解

命令提示符下输入：mysql -u root -psorry (-u后面空格可有可无,但是-p后面要加密码的话,必须没有空格)

看到提示符mysql>SHOW DATABASES;

 

命令约定：（学习过程中：mysql中的关键字一般采取全部小写）

shell>window下的命令

mysql>mysql中的命令

 

在window里面,MySQL输入大写,它也变成小写

mysql的windows版本不区分大小写的。但是其他系统严格区分大小写的。sql语句都必须以分号结尾。

 

表的一行称之为一条记录

表中一条记录对应一个java对象的数据

 

三、SQL语句

1、DDL：Data Definition Language

    作用：定义数据库及表的结构的。(下表知道就好,但要知道哪里查)

VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度。 (开发中常用)

    关键字：CREATE ALTER DROP TRUNCATE(摧毁整张表，然后重建表结构)

存数据的时候,如果编码问题,也提示数据长度不够

用命令行改client编码,只是临时性的,只对当前命令行有效.

results,修改服务端显示给客户端的编码

 

mysql有六处使用了字符集，分别为：

client 、connection、database、results、server 、system。

client是客户端使用的字符集。

connection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。      

database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。   

results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。  

    练习：

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

创建一个名称为mydb1的数据库(用这个)。

mysql> create database MYDB1;

查看数据库的创建细节

mysql> show create database MYDB1;

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

mysql>create database MYDB2 character set gbk;

创建一个使用gbk字符集，并带校对规则的mydb3数据库。 (例如排序顺序查文档)  

mysql>create database MYDB3 character set gbk collate gbk_chinese_ci;

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

mysql>show databases;

删除前面创建的mydb3数据库

mysql> drop database MYDB3;

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

mysql> alter database MYDB2 character set utf8;

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

创建一个员工表

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

mysql> use MYDB1;

命令成功提示Database changed;

mysql>create table EMPLOYEE(

    ID int,

    NAME varchar(200),

    GENDER varchar(10),

    BIRTHDAY date,

    ENTRY_DATE date,

    JOB varchar(100),

    SALARY float(8,2),

    RESUME text

);

查看表的结构

mysql> desc 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;

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

mysql>show tables;

查看表的创建细节

mysql>show create table USER;

修改表的字符集为gbk

mysql>alter table USER character set gbk;

列名name修改为username

mysql>alter table USER change NAME USERNAME varchar(200);

 

 

2、DML:Data Manipulation Language

    作用：用于向数据库表中插入、删除、修改数据

    常用关键字：INSERT UPDATE DELETE

 

    Tips：a、在MySQL中，字符、字符串、日期、时间、日期时间，这样的值要使用单引号引起来。

查询数据库内容,加数据,只是修改查询结果,不会修改数据库

where 后面是布尔型的 支持表达式   不支持 ....函数

优先级:not最高 and中 or最低

 

          b、特殊值：null

 

使用insert语句向表中插入三个员工的信息。

mysql> insert into USER values(1,'cll','female','1989-02-09','2013-08-26','CEO',10000,'beautful girl');(不建议)

mysql>insert into USER (ID,USERNAME,GENDER,BIRTHDAY,ENTRY_DATE,JOB,SALARY,RESUME) values(2,'lm','female','1990-02-09','2013-08-26','CTO',10000,'beautful girl');

mysql>insert into USER (ID,USERNAME,GENDER,BIRTHDAY,ENTRY_DATE,JOB,SALARY,RESUME) values(3,'梁海君','male','1990-12-09','2013-08-26','UFO',10000,'hansemu man');

 

中文编码：

查看MySQL目前设置的6处编码的值：

mysql>show variables like 'character%';

通知mysql，客户端使用的编码

mysql>set character_set_client=gbk;

通知mysql，客户端显示结果时用的编码

mysql>set character_set_results=gbk;

 

查看表中的所有记录

mysql> select * from USER;

mysql> select * from USER where filename is not null;表示filename这个字段不为空的所有数据行打印

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

mysql>update USER set SALARY=5000;

将姓名为’梁海君’的员工薪水修改为3000元。

mysql>update USER set SALARY=30000 where USERNAME='梁海君';

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

mysql>update USER set SALARY=4000,JOB='CMO' where USERNAME='cll';

将lm的薪水在原有基础上增加1000元。   

mysql>update USER set SALARY=SALARY+1000 where USERNAME='lm';

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

mysql>delete from USER where USERNAME='cll';

删除表中所有记录。

mysql>delete from USER;

使用truncate删除表中记录。删减操作会取消并重新创建表

mysql>truncate USER;

 

3、DQL:Data Query Language

常用关键字：select

 

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

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

mysql>select * from STUDENT;

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

mysql>select NAME,ENGLISH from STUDENT;

过滤表中重复数据。

mysql>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;

查询姓名为wu的学生成绩

mysql>select * from STUDENT where NAME='王五';

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

mysql>select * from STUDENT where ENGLISH>90;

查询总分大于200分的所有同学(where 语句中支持表达式)

mysql>select * from STUDENT where (CHINESE+ENGLISH+MATH)>200;

查询英语分数在 80－90之间的同学。

mysql>select * from STUDENT where ENGLISH<=90 and ENGLISH>=80;

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 总分 desc;

对姓李的学生成绩排序输出

mysql>select NAME,CHINESE+ENGLISH+MATH 总分 from STUDENT where NAME like '李%' order by 总分;

 

四、数据完整性

实体完整性：能够唯一确定一条记录。使用主键（ Primary key：PK），特点：唯一且不会为null。

    主键：

        逻辑主键：与具体业务无关 int id，为了确定一条记录

        业务主键：

删除主键：alter table tablename drop primary key ;

域完整性： not null 不能为空； unique 唯一

参照完整性：针对多表

    外键： foreign key

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

五、多表设计

1、一对多(最好做成一个表,主要看情况而定)

create table DEPARTMENT1(

    ID int primary key,

    NAME varchar(100) not null

);

create table EMPLOYEE1(

    ID int primary key,

    NAME varchar(100) not null,

    SALARY float(8,2),

    DEPT_ID int,

 

    constraint DEPT_ID_FK1 foreign key(DEPT_ID) references DEPARTMENT1(ID)

);

#外键约束

#DEPT_ID_FK 定义外键名称，同一个数据库中要保持唯一

从业务角度去考虑  一对多还是多对一

单项的多对多,也应该是3个表   多对多  和多对多

 

2、多对多

create table TEACHER(

    ID int primary key,

    NAME varchar(100),

    SALARY float(8,2)

);

create table STUDENTS(

    ID int primary key,

    NAME varchar(100),

    GRADE varchar(100)

);

create table TEACHER_STUDENTS(

    T_ID int,

    S_ID int,

    primary key(T_ID,S_ID),

    constraint T_ID_FK foreign key(T_ID) references TEACHER(ID),

    constraint S_ID_FK foreign key(S_ID) references STUDENTS(ID)

);

3、一对一(最好不要使用,完全可以做成一个表)

create table PERSON(

    ID int primary key,

    NAME varchar(100)

);

create table ID_CARD(

    ID int primary key,

    NUM varchar(20),

    constraint P_ID_FK foreign key(ID) references PERSON(ID)

);