SQL语句（mysql为模板）

SQL：（结构化查询语言）

通过SQL语句与数据库交互。

常用数据库：SQLserver 微软 Oracle甲骨文  MySql甲骨文  DB2IBM  SyBase

MySQL数据库：

windows命令：mysql -u用户名 -p密码

Mysql -h localhost -uroot

Mysql -uroot -p

默认运行在：3306端口 

MySql是数据库管理软件。

创建数据库：

创建数据库：create database mydata1;

创建指定字符编码的数据库：Create database mydb2 character set utf8;（编码不要横线）

细节：

Character set utf8;指定编码

Collate name;指定校对规则（规则参考文档）

编码和校对规则要对应。一般使用默认的校对规则即可。

Create database mydb3 character set utf8 collate utf8_general_ci;

查看数据库：

查看当前库中所有的表：show tables;

查看所有数据库：show databases;

查看数据库穿件的详细信息(编码)：show create database mydb3;

删除数据库：

drop database mydata1;

修改数据库：(库名不能改)

Alter database mydb3 character set gb2312;

备份数据库：

命令：mysql dump -uroot -p 数据库名>文件名.sql

Mysql dump -uroot -p mydb2>c:test.sql（windows 命令）

恢复数据库：

只能恢复数据库中的数据，不能恢复库，如果库被删除，要新建库，推出库，然后恢复数据

Source 文件名.sql(要先进入库，然后恢复)

Source c:\test.sql

Mysql -uroot -proot mydb2<文件名.sql（window 命令）

Mysqldump -uroot -p mydb2<c:test.sql

注意区分windowS命令和SQL语句：命令后不加分号，SQL语句后要加分号

 创建表：

Create table table_name

(

Field1 type,

Field2 type// 最后一个没逗号

)character set Utf8 collate ...;

MYSQL常用数据类型：详细参考文档

数值类型：

BIT位

TINYINT 字节

BOOL BOOLEAN 存储的是0和1

SMALUNT对应SHORT

INT

BIGINT相当于LONG

FLOAT(M,D)//M是数据宽度，D是小数位数

DOUBLE(M,D)

文本，二进制类型：

Char(size)最大255

Varchar(size)长度可变字符串，最大数65535

BLOB ,LONGBLOB大的二进制数据，如电影TINYBLOB,BOLOB,MEDIUMBLOB，LONGBLOB

TEXT ,LONTTEXT大文本,其他数据库中使用的是clob ,longclob

时间日期：

DATA

DATATIME

Timestamp时间戳，可记录操作时间

查看上面创建的表的详细信息：

Show create table person;//显示创建语句

查看表结构：

 desc person;

防止乱码：库和表的编码一致

修改表结构：

追加：

Alter talbe person add column type;

修改：

Alter talbe person modify cloumn type;

删除：

Alter talbe person drop column;（这里是删除表结构的一列，不是数据）

修改表名：

Rename table person to person1;

修改表的字符编码：

Alter talbe person character set utf8;

数据库CRUD语句：

字符串和日期要用’‘单引号。

Insert:

 Insert into person(id,name) values(1,'hd');

细节：

要指定插入到哪个字段

往指定的列插入数据，只需指定这一列，其他列为NULL

可以把所有的值都用''单引号括起来。会自动转型，防止出错。

命令窗口中使用SQL语句插入中文乱码问题：

因为在命令窗口中输入中文的编码是GB2312，而表，库，MYSQL的编码是UTF-8，插入会报错。

解决方法：

Show variables like 'chara%'；查找客户端编码变量

Character_set_client。显示的将客户端编码改为GB2312：

Set character_set_client=gb2312; mysql会自动将数据转为UTF-8。

因为 窗口是GB2312编码，显示UTF-8的中文时会乱码。把结果编码改为GB2312即可。

Set character_set_results=gb2312;

显示修改只对当前窗口有效。

一劳永逸需要修改mysql配置。（不推荐，因为开发很少用命令操作数据库。）

Update:

Update person set salary=5000;修改表中所有记录

Update person set salary=5000 where name='aa';//修改某一条数据。

Update person set salary=5000,job='java',sex='female' where name='bb';

Update person set salary=salary+1000 where name='cc';

细节：如果不是修改所有数据，要写where

Delete:

仅删除记录不能删除表（删除表用drop table person;），不能删除某一列数据(可用update更新列数据)

Delete from person where ....;

Delete from person;//删除表中所有数据

Truncate table person;也可以删除表中的所有数据，区别是：delete 是一条一条的删，而truncate table 是删除整个表数据，并摧毁表结构，然后再重建表结构。在删除的数据很多时，效率更高。

Select:

查询有单表查询和多表查询。

Select [distinct] * from person;

Distinct是否剔除重复数据（不是指记录）。指定多个字段时，只有多个字段都相同才会剔除。

Select * from person;//查询所有数据

Select name,english form person;//查询学生的姓名和英文。

Select distinct english from person;

Secelt name,chinese+10,english+10 from student;//为所有学生的每门功课加10分。（参与查询的列可运算。）表中的数据没有变化

Select name,(chinese+english+math) from student;查询每个人的总分。

使用别名：

Select name as 姓名，（chinese+english+math） as 总分 from student;//(as可省略)

使用where过滤：

Select name from student where english>'90';

Select name from student where (chinese+english+math)>200;

Where语句中的常用运算符：

< ,> ,<=,>=,=,<>

Between ...and..显示某一区间的值

In(set)//显示在in 列表中的值

Like '张'模糊查询，like语句中，%代表0个或多个任意字符，_代表一个字符

Is NULL判断是否为空

And 多个条件同时成立

Or 多个条件任一成立

Not 不成立

Select name form student where english between 80 and 90;查询英语成绩在80到90之间的名字。（包含80和90）

Select  name from student where math in 95,100; //查询数学为95或100的名字。

Select * from student where name like'李%';查询姓李的数据。

Select * from student where chinese>80 and math>80;语文和数学都大于80的数据。

Order by子句对查询结果排序：

Selct column from person order by column asc|desc;

排序的列可以是表中列名，也可以是查询语句中的别名。

Order by语句位于查询语句结尾。

Select name,math from student order by math;(默认升序)

Select name,(chinese+english+math)as total from student order by total desc;

Select name,(chinese+english+math)as total from  student where name like '李%' order by total desc;

Count合计函数：

Count()统计列或行的总数

Select count(*) from student;// 统计记录数

Select count(name) from student;//按名字统计记录数

Select count(*) from student where math>=90;//统计数学满90分得人数。

Sum合计函数：

Select sum(math) from student;//查询表中所有人的数学总和。

Select sum(chinese),sum(english),sum(math) from student;各科班级总分。

Select sum(chinese)/count(chinese) from student;求班级语文平均分

Avg平均分合计函数：

Select avg(math) from student;求数学平均分。

MAX/MIN合计函数：

求满足where条件的一列的最大值和最小值。

Select max(chinese+english+math),min(chinese+english+math)from student;//求班级最高总分，和最低总分

Group by子句：

按列进行分组后，再查询

Select product,sum(price) from orders group by(product);

按产品名称归类后，查询每类商品的总价。

Having子句：

和where一样可以过滤。Having 子句中可以使用合计函数常跟在group by 后，作用于组。如果过滤条件中用合计函数，则使用having ,否则用where .

Select product from orders group by(product) having sum(price)>100;

查询总价大于100的商品。

SQL提供了一些数学函数：

时间日期函数：

Select addtime('10:25:20','01:00:10')加上后面的时间后的

加，减

现在的时间

时间差

字符串函数：

\s得到Mysql系统信息

数学函数：

定义表的约束：

主键约束：

创建表时把数据唯一的字段指定为主键id int primary key

Id int primary key auto_increment数据库来管理主键的值，操作时用户不用管主键值。（不推荐，因为编程常用到ID值。）

主键要非空且唯一，用来唯一标示每条数据

Alter table person drop primary key;删除主键

唯一约束：

Name varchar(40) unique限定指定列数据唯一

非空约束：

Name varchar(40) unique not null唯一非空

Check约束，mysql不支持

外键约束：

将本表中的某一列做为另一个表的外键，用来唯一标示另一个表中的数据，和与本表的数据对应关系。

为表加外键：constraint husband_id_ FK foreign key(husband_id) references husband(id);

Husband_id_Fk是外键名

Husband_id是在哪一列加外键。

References husband(id)外键来自husband表的id 列；

在有外键的表中插入数据：要指定外键列的值

有关系对象的表的设计：

一个实体一张表，表的字段看实体有什么属性。

为维护关系，需要在多的一方，加外键约束。

一对多：(多对一)

一个部门对应多个员工,可在员工表中加外键。

多对多：

一个学生对应多个老师，一个老师也对应多个学生。

设计三个表：学生表student，老师表teacher，中间表teacher_student.

中间表保存的是2列来自student,teacher的外键，将多对多转成了2个一对多。

中间表除了保存2列外键，通常还会添加主键，开发中，常用2列外键做联合主键。

Create table teacher_student

(

Teacher_id int,

Student_id int,

Constraint teacher_id_FK foreign key teacher_id references teacher(id);

Constraint student_id_FK foreign key student_id references student(id);

Primary key(teacher_id,student_id)//用2个外键做联合主键

);

一对一：

人和身份证。人是主表，身份证是从表。主可以没有从，从不可以没有主

设计2个表，并在从表上加外键，且外键非空，唯一

可以直接在身份证表的ID列加外键。ID主键本身非空唯一。

Constraint id_Fk foreign key(id) references person(id);

自关联：（无限级分类）

目录表中，要分类，可通过外键parent_id引用id来指定子类的父类。父类的parent_id为空

Constraint id_Fk foreigh key(parent_id) references category(id);