总结了数据库操作的一些基本指令、数据完整性和多表复杂查询等。
MySQL中有六个地方使用了字符集,可以通过命令查看
show variables like 'character%';
修改客户端字符集:set character_set_client=gbk;
设置结果集用gbk:setcharacter_set_results=gbk;
数据库操作包括
库操作
表操作
数据操作
排序子句:order by
一、首先总结库操作的相关知识点:
创建一个库:
增
创建一个库:create database 库名;
创建一个gbk字符集的库:createdatabase 库名 character set gbk;
创建一个带有校对规则的库:create database 库名 character set gbk collate gbk_chinese_ci;
删
删除一个库:drop database 库名;
改
修改库的字符集为utf8:alter database 库名 character set utf8;
查
查看有多少库:show databases;
查看库创建细节:show create database 库名;
查询gbk字符集下main包含的校对规则:show collation like 'gbk%';
二、表操作的相关知识点
首先选择一个已经存在的库:use 库名;
增:
创建一个表:
CREATE TABLE employee(
id int,
name varchar(100),
gender varchar(10),
);
create table ifnot exists person(
num int,
name varchar(60)
);
删:
删除一个表:drop table 表名;或者是:drop table ifexists 表名;
查:
查看一个表的详情:desc 表名;
查看库中的表:show tables;
改:
修改表名字:alter table oldname rename to newname;
rename table oldname to newname;
修改表的字符集:alter table 表名 character set utf8;
三、对表中数据的操作
小小的总结:对表中的数据进行操作的时候,用到的是update关键字,而当需要对表中的列属性进行增删改查的时候,用到的是alter关键字!君要记牢哦!!!
增:
在表中增加一列:alter table 表名 add column 列名 列的类型;
插入数据:
例句1:insert into 表名values(1,'az','male','2015-15-12','ceo',50000,'hello');//不建议使用
例句2:insert into 表名(id,username,gender,birth,job,salary,resume)values(1,'az','male','2015-15-12','ceo',50000,'hello');
删:
删除一个列:alter table 表名 drop column 列名;
删除一行数据:delete from 表名 where 属性='某个属性';
delete from userwhere username='zhangsan';
删除表中所有数据:delete from 表名; truncate table 表名;
改:
修改表的字符集:alter table 表名 character set utf8;
修改列的字符长度:alter table 表名 modify column 列名 要修改成的值;
alter tableemployee modefy column job varchar(60);
修改具体属性值:
例句1:将所有员工薪水改为5000。
UPDATE user SETsalary = 5000;
例句2:将姓名为’zs’的员工薪水修改为3000元。
UPDATE user SETsalary = 3000 WHERE username = 'zs';
例句3:将姓名为’aaa’的员工薪水修改为4000元,job改为ccc。
UPDATE user SETsalary = 4000 , job = 'ccc' WHERE username='aaa';
例句4:将'张三'的薪水在原有基础上增加1000元。
UPDATE user SETsalary = salary+1000 WHERE username = '张三';
查:
1、查询数据操作 select
练习:
查询表中所有学生的信息。
SELECT * FROM student;
查询表中所有学生的姓名和对应的英语成绩。
SELECT name,english FROMstudent;
过滤表中重复数据。
SELECT DISTINCT english FROMstudent;
练习:(使用表达式和列(表)别名)
在所有学生数学分数上加10分特长分。
SELECT name,math+10 FROMstudent;
统计每个学生的总分。
SELECTname,chinese+english+math FROM student;
使用别名表示学生分数。
SELECTname,chinese+english+math AS '总成绩' FROM student;
练习:(使用条件进行过滤查询)
查询姓名为王五的学生成绩
SELECT * FROM student WHEREname = '王五';
查询英语成绩大于90分的同学
SELECT * FROM student WHEREenglish > 90;
查询总分大于200分的所有同学
SELECT * FROM student WHEREchinese+english+math > 200;
查询英语分数在 80-90之间的同学。
SELECT * FROM student WHEREenglish BETWEEN 80 AND 90;
查询数学分数为89,90,91的同学。
SELECT * FROM student WHEREmath in (89,90,91);
查询所有姓李的学生成绩。
SELECT * FROM student WHEREname LIKE '李%';
查询数学分>80,语文分>80的同学。
SELECT * FROM student WHEREmath>80 and chinese > 80;
练习:(将查询结果排序)
对数学成绩排序后输出。
SELECT name,math FROM studentORDER BY math;
对总分排序后输出,然后再按从高到低的顺序输出。
SELECT name,chinese+english+mathAS 总分 FROM studentORDER BY 总分 DESC;
对姓李的学生成绩排序输出。
SELECT name,chinese+english+math 总分 FROM student WHERE name LIKE '李%' ORDER BY 总分;
四、数据完整性和多表设计。
在向表中插入数据的时候,可能会存在插入的数据相同的情况,为了保证插入的数据是正确的完整的
就用到了数据的完整性,
数据完整性主要分类
1. 实体完整性(通过创建带主键的表)primary key
设置主键存在一个排序的问题,当只是设置一个主键的话,表中的数据按照这个主键进行升序排列,如果设置了联合主键的话,当第一个主键(在前面的主键)相同时候,按照第二个主键进行升序排列。
四种创建带主键的方式
1.写在字段(属性)后,id int primary key ,
2.写在所有字段后,(可以创建联合主键)create table t2(id int,namevarchar(100),primary key(id,name));
3.使用alter添加,(可以创建联合主键)ALTERTABLE t3 ADD PRIMARY KEY (id,name);
4.使用自增长 id int primary key auto_increment,
2.域完整性(通过数据类型<属性>的主键、非空以及唯一性设置)如下:
创建带主键,非空约束和唯一约束的表
create table T5(
ID int primary keyauto_increment,
USERNAMEvarchar(100) not null unique,
GENDER char(1) not null,
PHONE varchar(11) unique
);
3.参照完整性(多表设计):如下例子:
创建一对多关系的表,使用外键
CREATE TABLE customer(
id int primary key,
name varchar(100)not null,
address varchar(200)not null,
type varchar(100)not null
);
CREATE TABLE orders(
id int primary key,
amount int ,
price float(7,2),
code varchar(100),
customer_id int,
/*添加外键约束*/
CONSTRAINTFK_CUSTOMER_ID FOREIGN KEY (customer_id) REFERENCES customer(id)
);
五、DQL:数据查询语言之复杂查询。
1.连接查询(显示和隐式)
交叉连接(显示和隐式)
Select * from table1,table2;此处是两个交叉的表。
Selct * from customer crossjoin orders;
内连接(显示和隐式)
Select * from customer c,orders o where c.id=o.customer_id;
显式:select * from customer cinner join orders on c.id=o.customer_id;
外连接(左外连接、右外连接)
Select * from customer c left outer join orders o onc.id=o.customer_id;
Select * from customer c right outer join orders o onc.id=o.customer_id;
2. 子查询
小括号内的先执行
3.常用聚合函数 其他函数
count sum avg
分组查询:group by having可以使用聚合函数,,where不行