数据库基础
mysql相关操作
数据库
创建数据库
create database 数据库名;
选择数据库
use 数据库名;
删除数据库
drop database 数据库名;
查询数据库
show databases;
mysql表
创建新表
creat table 表名();
例如:
CREATE TABLE my_family(
id INT,
NAME VARCHAR(20) COMMENT'姓名',
age INT,
sex VARCHAR(1)
);
复制已有表
creat table 表名 like 已有的表明(想复制的表的表名);
查看表
查看表的名称
show tables;
查看表的基本结构
show columns 表名;
或者
desc 表名;
查看表的详细结构
show create table 表名;
修改表
alter table tbl_name(数据库名.表名):
add column (列名)(字符等);/*添加列*/
例子:
ALTER TABLE my_family ADD qinmidu INT;
alter col_name/*修改默认值*/
change col_name 该语句可同时修改表中的指定列的名称和数据类型
modify col_name ;该表只可修改表中指定列的位置,还可以修改列在表中的位置
alter table stusys.student1 drop column sid; 删除表中的sid列
alter table stusys.student1 rename to stusys.student2; 将student1重命名为student2
drop table table_name;删除表
数据
插入数据
insert into table_name(列名(如果该列都填可以不写)) values(表里想要填的内容'','','');
例如
mysql> INSERT INTO runoob_tbl
-> (runoob_title, runoob_author, submission_date)
-> VALUES
-> ("学习 PHP", "菜鸟教程", NOW());
插入多条数据
INSERT INTO table_name (field1, field2,...fieldN) VALUES (valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)......;
查询数据
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
查询全部数据
select * from runoob_tbl;
select语句
select 列名或者表达式 用于指定显示的列或者表达式
from 用于指定查询数据来源的表或者视图,可以指定一个表,也可以指定多个表.
where 用于指定行的条件
group by 指定分组表达式
having 满足分组条件
order by 指定行的升序或者降序排列
投影查询
select 列名或者表达式 from 表名;
select 列名 as 新列名 from 表名; 该命令不改变表的列名只改变查询时当前显示的列名。
计算列值
使用select查询时,可以使用+,-,*,/等算数运算符,select可以用算术表达式
去掉重复行
select distinct spect from student; 查询student中spect列,删除重复行
选择查询
where 条件
1.判定运算符包括比较运算,模式匹配,指定范围,空值判断,子查询等.
2.判定结果为:ture,false,unknown
3.逻辑运算符包括:and,or,not,其中not的优先级最高,or最低
4,字符串和日期必须用单引号括起来
5,between,not between,in,用于指定范围
select * from score where grade in(92,95);查询score表92,95分的记录.
不在用 not between
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
is [not] null判断表达式的值是否为空.
select *from student where sanme like '董%';查询student表中姓董的学生情况 %代表0或者多个字符,_代表,一个字符.like用于字符串匹配.
......where cname regexp '系统|数字'查询含有系统数字的所有课程名称,regexp正则表达式.
SELECT * FROM my_family WHERE id BETWEEN 10001 AND 10003; between实例
分组查询和统计计算
聚合函数
- count函数,用于计算满足条件的行数或则总行数
select count(*)as总人数 from student;
.....where speciality='通信' 查询通信专业总人数
SELECT o_num, SUM(quantity) AS items_totalFROM orderitems GROUP BY o_num;
例:
SELECT * FROM student WHERE Ssex='男';
SELECT COUNT(*) AS 性别 FROM student WHERE Ssex='男';
-
sum和avg求总和和平均值
-
max和min函数,其中all表示对所有值进行计算,distinct去掉重复值
-
group by用于要分组的列,通常与聚合函数一块使用
求平均值
SELECT AVG(Sno) FROM student; 注:sum,avg,max,min,count也同理
也可以这样写:
SELECT
max( salary ) AS 最高工资,
min( salary ) AS 最小工资,
avg( salary ) AS 平均值,
sum( salary ) AS 总合
FROM
employees;
分组查询
group by
SELECT Depart FROM teacher GROUP BY Depart; 这样只会输出每组的首个数据
对分组后的结果进行查询
group_concat()
select Depart,group_concat(Tname) from teacher group by Depart; 相当于把一个组都放在一块
group + 聚合函数
select department,group_concat(salary),sum(salary) from employee group by department;
计算各科目的成绩以及总人数
SELECT cNo,GROUP_CONCAT(score),COUNT(*) FROM score GROUP BY cNo;
having
按条件进行删选,过滤出满足条件的分组,要放在group by子句后面,order by放在having后面
select sno as 学号,avg(grade)as 平均成绩 from score droup by sno having avg(grade)>90;查询平均成绩在九十分以上学生的学号和平均成绩
例子:
SELECT Sno AS 学号,AVG(score)AS 平均成绩 FROM score GROUP BY Sno HAVING AVG(score)>80;
1,排序查询 order by name\ desc/asc
SELECT * FROM student ORDER BY Sclass DESC;
2,限制查询结果的数量
limit 0,3; 表示第一行到第三行
SELECT * FROM student ORDER BY Sclass DESC LIMIT 0,3;
offset 表示位置偏移量 上式也可写成limit 3offset1;
3,连接查询
-
有两张表如下表
course表
teacher表
内连接
SELECT * FROM course INNER JOIN teacher ON course.Tno=teacher.Tno;
例子:
SELECT a.Cno AS 课程号,a.Cname AS 课程名称,a.Tno AS 教师号,b.Tname AS 教师姓名,b.Tsex AS 性别,b.Tbirthday AS 出生年月日,b.Tprof AS 职位,b.Depart AS 系别 FROM course AS a INNER JOIN teacher AS b ON a.Tno=b.Tno;
除了内连接 inner join 还有左连接(左表不动右表滑动,如果右表没有左表拥有的即为null) left join 右连接 right join
修改数据
update table_name
set column1()=value1;column1指定要修改的列名及值,value1指列修改后的值
where用于限定表中要修改的行
修改指定记录
update student1
set sbirthday='1993-03-10'
where sname='周俊文';修改指定记录需要通过where子句指定要修改的记录满足的条件。
修改全部记录
update student
set tc=tc+2;在student表中,将所有学生的学分加两分
删除数据
delete from table_name
where sno='196001'删除该行,如没有该行则为删除全部记录
truncate 删除原来的表并且重建一个表
视图
```mysql
create view 视图名 as select * from 表名;
```
视图是一张虚拟表,可以用select 查询,也可以使用show tables来显示出视图的名称。
修改视图
CREATE OR REPLACE VIEW 视图名 AS SELECT [...] FROM [...];
查看视图
>DESC 视图名;
或者
>SHOW FIELDS FROM 视图名;
视图是一张虚拟表,不保存数据。
可以通过视图插入数据,但是只能基于一个基础表进行插入,不能跨表更新数据。
索引s
分类
-
普通索引 index
-
唯一索引 unique
-
主键 primary key
-
聚簇索引:索引数据存储物理顺序
-
全文索引 fulltext
mysql实战
mysql表相关数据
学生表
CREATE TABLE student (
sno VARCHAR(20) PRIMARY KEY,
sname VARCHAR(20) NOT NULL ,
ssex VARCHAR(10) NOT NULL,
sbirthday DATETIME,
class VARCHAR(20));
教师表
CREATE TABLE teacher(
tno VARCHAR(20) PRIMARY KEY,
tname VARCHAR(20) NOT NULL,
tsex VARCHAR(10) NOT NULL,
tbirthday DATETIME,
prof VARCHAR(20) NOT NULL,
depart VARCHAR(20) NOT NULL
);
课程表
CREATE TABLE course(
cno VARCHAR(20) NOT NULL,
cname VARCHAR(20) NOT NULL,
tno VARCHAR(20) NOT NULL,
FOREIGN KEY(tno) REFERENCES teacher(tno),
PRIMARY KEY(cno)
);
成绩表
CREATE TABLE score(
sno VARCHAR(20) NOT NULL,
cno VARCHAR(20) NOT NULL,
degress INT(20) ,
FOREIGN KEY(sno) REFERENCES student(sno),
FOREIGN KEY(cno) REFERENCES course(cno),
PRIMARY KEY(sno,cno)
);
数据插入
INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES('104','李军','男','1976-02-20','95033');
INSERT INTO student VALUES('105','王芳','女','1976-02-10','95033');
INSERT INTO student VALUES('106','陆君','男','1974-06-03','05031');
INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');
INSERT INTO student VALUES('108','赵铁柱','男','1976-02-10','95033');
INSERT INTO student VALUES('109','张全蛋','男','1974-06-03','05031');
添加教师表
INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');
添加课程表
INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');
添加成绩表
INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('103','3-105','92');
INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('109','3-105','76');
加粗样式