mysql数据库基本语法与操作

sql: structured query language(结构化查询语言)

sql登录：mysql -uroot -p
*************************************************************建库语句*************************************************************
创建一个字符集为utf-8的mydb1的数据库：
create database mydb1 character set utf8;

创建一个字符集为utf-8,并带校对规则的mydb2的数据库：
create database mydb2 character set utf8 collate utf8_general_ci;

显示库的创建信息：
show create database mydb2;
*************************************************************删库语句*************************************************************
删除前面创建的库：
drop database mydb1;

修改数据库的一些基本信息（不能修改数据库的名字）:
alter database mydb2 character set gb2312;

*************************************************************备份库*************************************************************
1.退出mysql客户端

2.在window命令窗口下执行： mysqldump -uroot -p mydb2>c:\test.sql



*************************************************************恢复库*************************************************************
（1）(恢复库只能恢复库里的数据，不能恢复库)
1.创建库
2.在mysql命令下执行： source c:/test.sql（通过执行脚本文件）

恢复库（2）
1.创建库
2.在window命令窗口下执行 mysql -uroot -p mydb2<c:\test.sql

*******************************************************mysql常用数据类型*********************************************************
1.bit(M)                            M指定位数，默认值为1，范围为1-64
2.tinyint[unsigned][zerofill]       带符号的范围为-126-127，无符号为0-255
3.bool,boolean                      使用0或1 表示真或假
4.smallint                          2的16次方 对应java中的short
5.int                               2的32次方 
6.bigint                            2的64次方 long
7.float(M,D)                        M指定显示长度 D指定小数位数
8.double(M,D)                       表示比float更精确的小数
9.char(size)                        固定长度字符串，size表示可保存的字符串长度
10.varchar(size)                    可变长度字符串
11.blob longblob                    二进制数据
12.text(clob)  longtext(longclob)   大文本
13.date/datetime/timeStamp          日期类型，timestamp表示时间戳，可用于自动记录insert update操作的时间

*************************************************************建表语句*************************************************************
create table employee
(
id int,
name varchar(20),
gender varchar(4),
birthday date,
entry_date date,
job varchar(40),
salary double,
resume text
)character set utf8 collate utf8_general_ci;

查看库中所有的表
show tables;

查看创建的表信息：
show create table employee;

查看表结构;
desc employee;

*************************************************************修改语句*************************************************************
1.在上面的表信息中增加一列image列：
alter table employee add image blob; 

2.修改job列的长度为60
alter table employee modify job varchar(60);

3.删除gender列
alter table employee drop gender;

4.修改表名
rename table employee to user;

5.修改表的字符集
alter table user character set gb2312;

6.修改列的名字
alter table user change column name username varchar(20);

*************************************************************插入语句*************************************************************
insert into employee(id,username,birthday,entry_date,job,salary,resume) values(1,'小李子','1996-4-08','1996-5-8','fdsf',4555,'bbbbbbbbb');

表的编码为utf8时插入中文数据失败解决方法：
show variables like 'chara%';
set character_set_client=gb2312;

显示乱码的解决方法
set character_set_results=gb2312;

*************************************************************修改语句*************************************************************
修改表中某一列的所有数据
update employee set salary=5000;

修改表中某一列的指定行的数据
update employee set salary=1000 where username='佛挡杀佛';

修改两列的数据
update employee set salary=4000,job='ccc' where username='lfd';

set后面可以跟一个表达式： 工资加一千
update employee set salary=salary+1000 where username='lfd';

*************************************************************删除语句*************************************************************
删除表中的数据(delete语句只删除记录，不删除表本身，drop删除表)：
delete from employee where id=1 and username='lfd';

删除表中所有记录
delete from employee;

注意：
delete语句删除记录是一条一条删除的，当数据多时效率太低，所以有一个truncate语句可以用来删除大数据时的表记录，truncate是先摧毁表，然后再重建表结构，效率高
truncate table employee;

*************************************************************查询语句****************************************************************

1.查询表中所有的信息
select * from student;

select id,name,chinese,english,math from student;(建议使用，代码易懂，容易维护)

取出表中名字和对应的英语成绩
select name,english from student;

过滤表中重复的数据 
select distinct english from student;

在每个人的英语成绩加上10分的特长分
select name,english+10 from student;

统计每个学生的总分
select name,(english+math+chinese) from student;

使用别名显示学生的分数
select name,(english+math+chinese) as '总分' from student;(as可省略)

2.过滤查询：
查询出姓名为王五的学生成绩
select * from student where name="王五";

查询出英语成绩大于90的同学
select * from student where english>90;

3.在where关键字后面经常使用的运算符

比较运算符
*****************************************************************
    *> < <= >= = <>  * 大于、小于、大于(小于)等于、不等于
    *****************************************************
比较运算 符   *between....and  *显示在某一区间的值
    *****************************************************
    * in(set)        *显示在in列表中的值，例：in(100,200)
    *****************************************************
    *like'张pattern' *模糊查询
    *****************************************************
    *  is null       *判断是否为空
*****************************************************************
    *   and        *多个条件同时成立
    ****************************************************
逻辑运算符    *     or      *多个条件任一成立
    ****************************************************
    *   not     *不成立，例where not(salary>100)
*****************************************************************
like语句中。%代表零个或多个任意字符，_代表一个字符，例first_name like '_a%';

查询英语分数在80-90之间的同学
select * from student where english between 80 and 90;
select * from student where english>80 and english<90;

查询数学分数为89,90,91的同学
select * from student where math=89 or math=90 or math=91;
select * from student where math in(89,90,91);

查询数学大于80 语文大于80的同学
select * from student where math>80 and chinese>80;

查询所有姓李的学生的成绩
select * from student where name like '李%';

4.用order by 语句进行排序（asc升序 desc降序）
对数学成绩排序输出
select name,math from student order by math;

对总分进行排序输出（从高到低）
select name,(chinese+math+english) '总分' from student order by '总分' desc;

对姓李的学生成绩进行排序输出
select name,(math+english+chinese) from student where name like '李%' order by (math+english+chinese) desc;

*************************************************************函数*************************************************************
(1).count函数
统计一个班有几个学生
select count(*) from student;

统计数学成绩大于90的学生
select count(*) from student where math>90;

统计总分大于两百五
select count(*) from student where (math+chinese+english)>250;

(2).sum函数
统计一个班的数学总成绩
select sum(math) from student;

统计一个班数学 英语 语文的成绩总和
select sum(math+english+chinese) from student;

统计一个班语文成绩的平均分
select sum(chinese)/count(chinese) from student;

(3).AVG函数
统计一个班语文成绩的平均分
select avg(chinese) from student;

(4）.max/min函数
1.select max(math+chinese+english),min(math+chinese+english) from student;

6.group by 语句
(1).对订单中的商品进行归类
select product from orders group by product;

(2).对订单中的商品进行归类,并显示各类商品的总价
select product,sum(price) from orders group by product;

having语句
(1) 查询购买了几类商品，并且每类总价大于100的商品
select product from orders group by product having sum(price)>100;（这是错误的语句，where后面不能跟合计函数）

*************************************************************表的数据*************************************************************
1.定义带有主键约束的表
create table test1
(
id int primary key,
name varchar(20),
password varchar(20)
);

2.定义主键一个自动增长的表
create table test2
(
id int primary key auto_increment,
name varchar(20),
password varchar(20)
);

3.定义唯一约束
create table test3
(
id int primary key auto_increment,
name varchar(20) unique
);

4.定义非空约束
create table test4
(
id int primary key auto_increment,
name varchar(20) unique not null
);

5.外键约束
create table husband
(
id int primary key,
name varchar(20)
);

create table wife
(
id int primary key,
name varchar(20),
husband_id int,
constraint husband_id_FK foreign key(husband_id) references husband(id)
);

*************************************************************软件开发与表的设计*************************************************************
1.实体之间一对多的关系（员工与部门）：外键设计在在多的那个实体所在的表中，如员工中的部门编号、
2.实体之间多对多的关系（老师与学生）：设计一个老师表，一个学生表，一个老师学生表，老师学生表里只有老师编号与学生编号，且同时是主键和外键

create table teacher
(
id int primary key,
name varchar(20),
salary double
);

create table student
(
id int primary key,
name varchar(20)
);

create table teacher_student
(
teacher_id int,
student_id int,
primary key(teacher_id,student_id),
constraint teacher_id_FK foreign key(teacher_id) references teacher(id),
constraint student_id_FK foreign key(student_id) references student(id)
);

3.实体之间一对一的关系（人(主)与身份证(从)）：设计一个人表，设计一个身份证表，身份证的id既是主键又是外键(保证非空，唯一，且一对一)

create table person
(
id int primary key,
name varchar(20)
);

create table idcard
(
id int primary key,
address varchar(20),
constraint idcard_id_FK foreign key(id) references person(id)
);

**********************************事务********************************************
1.开启事务：start transaction;
2.提交事务: commit

事务的级别：
1.Serializable: 可避免脏读，不可重复读，虚读情况发生（串行化）
2.Repeatable read: 可避免脏读，不可重复读情况发生
3.Read committed: 可避免脏读情况发生
4.Read uncommitted: 最低级别，以上情况均无法保证

设置事务级别：set transaction isolation level 级别;

查询事务隔离级别： select @@tx_isolation;

脏读：一个事务读取了另一个事务未提交的数据
幻读： 一个事务读取了另一个事物提交了的插入的数据
不可重复读：一个事务读取了另一个事务提交了的修改的数据，两次读取数据不一致

create table account
(
id int primary key auto_increment,
name varchar(20),
money float
)character set utf8;

insert into account(name,money) values('aaa',1000);

insert into account(name,money) values('bbb',1000);

insert into account(name,money) values('ccc',1000);

多表查询：
select s.id,s.name from teacher_student t_s,student s where id='1' and s.id=t_s.student_id;