数据库及表的基本操作

数据库的基本操作

一、数据库的操作

1、数据库的建立

create database ljh;
 create database if not exists ljh;//表示如果ljh数据库不存在就建立，防止出错

2、展示数据库

show databases;//看数据库都有哪些

+--------------------+
| Database           |
+--------------------+
| information_schema |
| class_13_db2       |
| ljh                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

3、使用数据库

 use ljh;//如果在数据库里建表需要确定在哪个里面建表，用哪个use哪个

4、 select database();

+------------+
| database() |
+------------+
| ljh        |
+------------+

5、删除数据库

drop database if exists ljh;//如果该数据库存在就删除，防止没有数据库出现删除错误

表的基本操作

一、
①CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。
②数据库的注释 “–空格+描述”
二、
1、建表create

create table stu_test (
     id int,
     name varchar(2)
    );

2、展示表中的字段

 desc student;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| id               | int(11)      | YES  |     | NULL    |       |
| sequence_numbers | int(11)      | YES  |     | NULL    |       |
| name             | varchar(20)  | YES  |     | NULL    |       |
| qq_mail          | varchar(100) | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+

3、插入数据： 数量必须和定义表的列的数量及顺序一致
①单行数据全列插入

insert into student values(09,20170205,'mike','150317');

②多行数据指定列插入

insert into student(id,name,qq_mail) values(10,'章','187956'),(11,'章','167956'),
(12,'章','187956');

③多行数据全列插入

insert into student(id,sequence_numbers,name,qq_mail) values
(09,20170205,'mike','150317'),(09,20170205,'mike','150317'),(09,20170205,'mike','150317');

insert into student values(09,20170205,'mike','150317'),(09,20170205,'mike','150317'),(09,20170205,'mike','150317');

4、查询：
①全列查询

 select *from student;

②、指定列查询

select id,qq_mail from student;

③、查询字段为表达式

 select id,qq_mail,10 from student;
+------+--------------------+----+
| id   | qq_mail            | 10 |
+------+--------------------+----+
|    1 | 1503179376@qq.com  | 10 |
|    2 | 15258697888@qq.com | 10 |
|    3 | NULL               | 10 |
|    4 | NULL               | 10 |
|    4 | NULL               | 10 |
|   10 | 1503179            | 10 |
|    9 | 150317             | 10 |
|   10 | 187956             | 10 |
|   11 | 167956             | 10 |
|   12 | 187956             | 10 |
|    9 | 150317             | 10 |
|    9 | 150317             | 10 |
|    9 | 150317             | 10 |
|    9 | 150317             | 10 |
|    9 | 150317             | 10 |
|    9 | 150317             | 10 |
+------+--------------------+----+

select 10 from student;
+----+
| 10 |
+----+
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
+----+

④为查询结果中的列指定别名，表示返回的结果集中，以别名作为该列的名称

 select qq_mail as qq from student;
+--------------------+
| qq                 |
+--------------------+
| 1503179376@qq.com  |
| 15258697888@qq.com |
| NULL               |
| NULL               |
| NULL               |
| 1503179            |
| 150317             |
| 187956             |
| 167956             |
| 187956             |
| 150317             |
| 150317             |
| 150317             |
| 150317             |
| 150317             |
| 150317             |
+--------------------+
 select 10 as jj from student;
+----+
| jj |
+----+
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
+----+

⑤去重：使用DISTINCT关键字对某列数据进行去重：

select distinct id from student;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|   10 |
|    9 |
|   11 |
|   12 |
+------+
select distinct id,sequence_numbers from student;

⑥、排序order by;
①降序 DESC

 select name,math from stu_score order by math desc;
+--------+------+
| name   | math |
+--------+------+
| 小明   |   89 |
| 鹿鸣   |   89 |
| 程野   |   88 |
| 魏聪   |   75 |
| 肖战   |   68 |
| 王哲   |   68 |
| 李哲   |   68 |
| 马敏   | NULL |
+--------+------+

②升序 ASC

select name,math from stu_score order by math asc;
+--------+------+
| name   | math |
+--------+------+
| 马敏   | NULL |
| 肖战   |   68 |
| 王哲   |   68 |
| 李哲   |   68 |
| 魏聪   |   75 |
| 程野   |   88 |
| 小明   |   89 |
| 鹿鸣   |   89 |
+--------+------+

⑦、条件查询
①运算符
运算符 说明

, >=, <, <= 大于，大于等于，小于，小于等于
= 等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=> 等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <> 不等于
BETWEEN a0 AND a1
范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, …) 如果是 option 中的任意一个，返回 TRUE(1)
IS NULL 是 NULL
IS NOT NULL 不是 NULL
LIKE
模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字 符

select name,math from stu_score where math is null; 

select name,chinese from stu_score where chinese between 80 and 100;

select name,chinese from stu_score where math in(88,85,95);//查询数学成绩是 88或者 85 或者 95 分的同学及数学成绩 （or）

select name from stu_score where name like '程%';

⑧、分页查询Limit

LIMIT m,n : 表示从第m+1条开始，取n条数据；
LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。

select name,math from stu_score limit 3;//从0开始筛选三条结果

select name,math from stu_score order by math limit 3 offset 0;//：按 id 进行分页，每页 3 条记录
select name,math from stu_score order by math limit 3 offset 3;
select name,math from stu_score order by math limit 3 offset 6;

5、删除delete DELETE FROM ?table_name [WHERE …] [ORDER BY …] [LIMIT…]

delete from stu_score where name='小明';

delete from student;//删除整张表数据

6、修改update UPDATE table_name SET column = expr [, column = expr …] [WHERE …] [ORDER BY …] [LIMIT …]

update stu_score set math=60 where name='程野';
update stu_score  set math=math+30 order by chinese+math+english limit 3;// 将数学成绩倒数前三的 3 位同学的数学成绩加上 30 分 

7、总结

--单行插入 insert into 表(字段1, ..., 字段N) values (value1, ..., value N);

-- 多行插入 insert into 表(字段1, ..., 字段N) values (value1, ...), (value2, ...), (value3, ...);

-- 全列查询 select * from 表 -- 指定列查询 

select 字段1,字段2... from 表 -- 查询表达式字段 
select 字段1+100,字段2+字段3 from 表 -- 别名 select 字段1 别名1, 字段2 别名2 from 表 
-- 去重DISTINCT select distinct 字段 from 表

 -- 排序ORDER BY select * from 表 order by 排序字段
 -- 条件查询WHERE：
 -- (1)比较运算符 (2)BETWEEN ... AND ... (3)IN (4)IS NULL (5)LIKE (6)AND (7)OR (8)NOT select * from 表 where 条件

update 表 set 字段1=value1, 字段2=value2... where 条件

delete from 表 where 条件

二、进阶

**一、约束：**保证数据记录符合现实世界的意义

数据库的完整性包括：
1、实体完整性
primary key

2、参照完整性
foreign key
3、用户自定义完整性
not null ,unique,check,default
4、语法
创建班级表，有使用MySQL关键字作为字段时，需要使用‘ ’来标识
DROP TABLE IF EXISTS classes;
CREATE TABLE classes (
id INT PRIMARY KEY auto _increment,
name VARCHAR(20) ，
desc”VARCHAR(100)
);

CREATE TABLE student (
id INT PRIMARY KEY auto i ncrement,
sn INT UNIQUE，
name VARCHAR(20) DEFAULT ‘unkown’ ，
qq mail VARCHAR(20)，
classes_id int,
FOREIGN KEY (classes_id) REFERENCES classes(id)
);

三、修改
1、修改表名mysql> alter table book rename book1;
2、修改字段名 alter table book2 change price price1 int;
**mysql> rename table book1 to book3;

四、聚合函数
2、查询表里的记录个数 count
*mysql> select count(name) from book2;count(字段) 忽略空null
*mysql> select count(*) from book2;注意count(*)不忽略空与0

3、求和sum
mysql> select sum(price) from book2;
4、求平均数、
mysql> select avg(price) from book2;
5、最大最小max,min
6、group by 可以对指定列进行分组查询（需要满足的条件select 指定的字段必须是分组依据字段，其他字段想要出现在select中则必须包含在聚合函数中）
7、having跟在group by后面，对分组的条件进行过滤。（作用相当于where，但是聚合函数不能用where）

五、联合查询
1、内连接

select  字段 from 表1 别名1，表2 别名2 where 连接条件 and  连接条件;
select  字段 from 表1 别名1 join 表2 别名2  on 连接条件 and  连接条件;

2、左外连接：联合查询时左侧的表完全显示

select  字段 from 表1 别名1 left  join 表2 别名2  on 连接条件 and  连接条件;

3、右外连接：联合查询时右侧的表完全显示

select  字段 from 表1 别名1 right  join 表2 别名2  on 连接条件 and  连接条件;

4、自连接：同一张表进行自连接查询

select . . . from 表1，表1 where 条件

六、SQL各个关键字的执行顺序：
from>on>join>where>group by >with>having>select>distinct>order by>limit

设计一个选课系统要求,要求
1、学生新学期开始能够选课，选择某老师的课
2、学生必须要有所在的班级信息
3、学期末老师录入成绩

create database course_system;
create table student(
sn int primary key,
name varchar(20) not null
classid int

);
create table course(
cname varchar(10) not null,
cid int primary key,
credit int not null  default 3
);
create table teacher(
tname varchar(10) not null,
tid int primary key
);
create table class(
id int primary key,
classname varchar(10) not null
);
create table take(
  sn int ,
  id int ,
  score double(5,2),
  primary key(sn,id)
);
create 
需求分析：
①确定实体(entity)
学生，老师，课程，班级
②找出实体之间的关系(relational)
老师：课程   m:n
学生：课程   m:n
学生：班级   m:1

E-R图
矩形：实体
菱形：关系
椭圆：属性
1、画出实体
2、建立关系
3、标出属性
4、标注主键