MySQL简单学习

RDBMS术语:
Database -数据库是由保存相关数据的表组成数据源
Table -表 ,这意味电子表格,是包含数据的矩阵
Column -表示数据元素的列是保存一种类型的数据结构;如 送货日期
Row -行是对相关数据进行分组的结构,例如,用户的数据,它被称为元组,条目或记录.
Redundancy -此术语指的是存储数据两次,以加速系统.
Primary key -这指的是唯一的标识值,此值不能再表中出现两次,并且只有一个行与其关联
Foreign key -外键用作两个表之间的链接
Index -索引实际上与书的索引相同
Referential Integrity -此术语指确保所有外键值指向现有行

[root@VM_244_82_centos ~]# mysql -uroot -pbin 登入MySQL root是我MySQL的账号,bin是我的密码.

登入数据库后用show databases 查看数据库(我用的是mariadb,和MySQL一样的)
如没有数据库也可以创建数据库 create database school,
我们选择school 进入 使用use school,进入后使用show tables;查看表,如没表那就创建一个表 ,create table 表名称(列声明);
如下

#如果存在这个表就删除,否则创建drop table if exists `to_student`;MariaDB [school]> create table `to_class`    (     `id` int(11) not null auto_increment primary key,     `Name` varchar(32)not null     );

创建to_class表成功;

视图
视图是一张虚表，所谓虚表，就是说这张表里面没有数据，他的数据是从别的基础表中获取到的
以上表为例创建简单视图:
create view myview as select *from to_class;
得到一个一样的表 在show tables; 可以看到多出了一张myview表

创建一个学生信息表：学生id（自增，主键），姓名，年龄，性别，所属班级id（外键）。

create table `tb_student` (  `id` int(11) not null auto_increment primary key,  `Name` varchar(32) not null,  `gender` boolean default 0,check(`gender`=0 or `gender`=1) );

创建成功

创建一个学生成绩表：成绩id（自增，主键），科目，成绩，学生id（外键），创建时间

create table `to_score`(`id` int(11) not null auto_increment primary key,`course` varchar(32) not null,`Score` float(3,1) not null,`stuid` int(11) not null,constraint `stuid` foreign key(`stuid`)references`tb_student`(`id`));

alter table tb_student add `圣墟` varchar(15) after `Name`;在tb_student表中,在name之后插入`圣墟`

避免出现中文乱码,建表的时候 CREATE TABLE `database_user` (`ID` varchar(40) NOT NULL default '',`UserID` varchar(40) NOT NULL default '',) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table student    （        id int unsigned not null auto_increment primary key,        name char(8) not null,        sex char(4) not null,        age tinyint unsigned not null,        tel char(13) null default "-"    );刷新:flush privileges;插入数据:insert into student values (1,"小明","人",20,"13974251100");查询数据:select *from student 或select id from student;删除数据: delete from student where name="小明"; #删除一行查询数据:select *from student where age=20;更新表中的数据:update 表名称 set 列名称=新值 where 更新条件;将id为5的手机号改为默认的"-": update students set tel=default where id=5;将手机号为 110 的姓名改为 "张伟鹏", 年龄改为 19: update students set name="张伟鹏", age=19 where tel="110";

创建后表的修改alter table 语句用于创建后对表的修改, 基础用法如下:添加列基本形式: alter table 表名 add 列名 列数据类型 [after 插入位置];示例:在表的最后追加列 address: alter table students add address char(60);在名为 age 的列后插入列 birthday: alter table students add birthday date after age;修改列基本形式: alter table 表名 change 列名称 列新名称 新数据类型;示例:将表 tel 列改名为 telphone: alter table students change tel telphone char(13) default "-";将 name 列的数据类型改为 char(16): alter table students change name name char(16) not null;删除列基本形式: alter table 表名 drop 列名称;示例:删除 birthday 列: alter table students drop birthday;重命名表基本形式: alter table 表名 rename 新表名;示例:重命名 students 表为 workmates: alter table students rename workmates;删除整张表基本形式: drop table 表名;示例: 删除 workmates 表: drop table workmates;删除整个数据库基本形式: drop database 数据库名;示例: 删除 samp_db 数据库: drop database samp_db;**多表查询**

表设计：创建一个woman表和一个man表，在man表中通过外键连接相关关系。
eg:
一对一关系：
create table woman(
woman_id int primary key,
name varchar(30),
age int
);

insert into woman values(1,’mary’,20);
insert into woman values(2,’lucy’,22);
insert into woman values(3,’lili’,21);
insert into woman values(4,’angle’,18);

create table man(
man_id int primary key,
name varchar(30),
age int,
wife int unique,
foreign key (wife) references woman(woman_id) on delete cascade
);

[注：]foreign key (wife) references woman(woman_id) on delete cascade;为外键关联

insert into man values(1,’smith’,24,2);
insert into man values(2,’jack’,22,1);
insert into man values(3,’tom’,25,4);
insert into man values(4,’tim’,23,3);
insert into man (man_id,name,age) values(5,’fang’,19);

/查询2张表的所有相匹配的数据，对应图-1select * from man,woman;//查询2张表的夫妻对应的关系数据，对应图-2select * from man,woman where wife = woman_id;(如果列名不重复可以这样写，但不正规)select * from man,woman where man.wife = woman.woman_id;（正规写法）//查询tom和他老婆的信息(man和woman谁放在前面，他们对应的列就在前面),对应图-3select * from man,woman where man.wife = woman.woman_id and man.name = 'tom';或者（man m是为了避免表名太长，采用简单的字符替代获取指定的列名）select m.man_id, m.name, m.age, m.wife, w.woman_id, w.name, w.age from woman w,man m where w.woman_id = m.wife and m.name = 'tom';//查询tom老婆的信息，对应图-4select woman.woman_id,woman.name,woman.age from man,woman where man.wife = woman.woman_id and man.name='tom';或者select w.woman_id, w.name, w.age from woman w,man m where w.woman_id = m.wife and m.name = 'tom';

图-1

图-2

图-3

图-4

内联查询

//查询2张表中夫妻关系的对应的数据图-5select * from man inner join woman on man.wife = woman.woman_id;或者select * from woman inner join man on man.wife = woman.woman_id;//这2者的区别，列的排序不同，前者是man表中的列排在前面(从左向右)，后者反之。//查询tom和他老婆的信息，对应图-6select m.man_id, m.name, m.age, m.wife, w.woman_id, w.name, w.age from woman w inner join man m on w.woman_id = m.wife and m.name = 'tom';//查询tom的老婆信息。对应图-7select w.woman_id, w.name, w.age from woman w inner join man m on w.woman_id = m.wife and m.name = 'tom';

图 -5

图-6

图-7

外连接

eft join(左连接),见图-8eg:select * from man left join woman  on woman_id = wife ;select * from woman left join man  on woman_id = wife ;right join(右连接)，见图-9select * from man right join woman  on woman_id = wife ;select * from woman right join man  on woman_id = wife ;

图-8

图-9

案例：建一个班级表class,创建一个学生表students,具体看下面语句。

create table class(    class_id int primary key,    grade varchar(30) unique,    count int);insert into class values (1,'first grade',4);insert into class values (2,'second grade',3);insert into class values (3,'third grade',4);insert into class values (4,'fourth grade',3);insert into class values (5,'fifth grade',4);insert into class values (6,'sixth grade',3);create table students(    stu_id int primary key,    name varchar(30),    age int,    class_id int,    foreign key(class_id) references class (class_id));insert into students values(101,'tom',6,1);insert into students values(102,'tim',7,1);insert into students values(103,'tam',5,1);insert into students values(104,'tem',6,1);insert into students values(201,'blue',7,2);insert into students values(202,'lusi',8,2);insert into students values(203,'keou',6,2);insert into students values(301,'lisi',9,3);insert into students values(302,'lusi',7,3);insert into students values(303,'lucy',7,3);insert into students values(304,'kiki',8,3);insert into students values(401,'mumu',10,4);insert into students values(402,'bubu',8,4);insert into students values(403,'mimi',9,4);insert into students values(501,'koko',10,5);insert into students values(502,'pop',12,5);insert into students values(503,'bob',10,5);insert into students values(504,'coco',11,5);insert into students values(601,'ailis',13,6);insert into students values(602,'reti',11,6);insert into students values(603,'pipo',12,6);//查询一年级的学生信息select *from students,class where students.class_id = class.class_id and class.class_id = 1;//查询tom的信息select *from students,class where students.class_id = class.class_id and students.name = 'tom';//查询tom和mimi的年龄，上几年级,班上多少人select s.name,s.age,c.grade,c.count from students s,class c where s.class_id = c.class_id and (s.name = 'tom' or s.name = 'mimi');//查询年龄8岁的学生信息select * from students s,class c where s.class_id = c.class_id and age=8;//查询__m的学生select * from students s,class c where s.class_id = c.class_id and s.name like '__m';//查询%m%的学生select * from students s,class c where s.class_id = c.class_id and s.name like '%m%';//查询6,8,10的学生,按class_id降序排列,年龄升序排列select *from students s,class c where s.class_id=c.class_id and age in(6,8,10) order by s.class_id desc, s.age ;

多对多

create table students(    stu_id int primary key,    name varchar(30),    age int);insert into students values (01,'tom',16);insert into students values (02,'tim',15);insert into students values (03,'jim',14);insert into students values (04,'mary',16);insert into students values (05,'lili',17);insert into students values (06,'lucy',15);create table teachers(    tea_id int primary key,    name varchar(30),    age int);insert into teachers values (01,'blue',34);insert into teachers values (02,'green',36);insert into teachers values (03,'red',37);create table stu_tea(    id int primary key,    stu_id int,    tea_id int,    foreign key (stu_id) references students(stu_id),    foreign key (tea_id) references teachers(tea_id));insert into stu_tea values (1,01,01);insert into stu_tea values (2,01,03);insert into stu_tea values (3,02,01);insert into stu_tea values (4,03,02);insert into stu_tea values (5,03,03);insert into stu_tea values (6,04,01);insert into stu_tea values (7,04,02);insert into stu_tea values (8,05,02);insert into stu_tea values (9,06,01);//查询tom的老师及他们的信息select * from students s,teachers t,stu_tea st where s.stu_id=st.stu_id and t.tea_id = st.tea_id and s.name = 'tom';//查询tom的老师的信息(tom姓名，tom年龄，tom老师的name,tom老师的年龄)select s.name as stu_name ,s.age as stu_age,t.name as tea_name,t.age as tea_age  from students s,teachers t,stu_tea st where s.stu_id=st.stu_id and t.tea_id = st.tea_id and s.name = 'tom';//查询blue老师所教的学生select * from teachers t,students s,stu_tea st where s.stu_id = st.stu_id and t.tea_id = st.tea_id and t.name = 'blue';//查询blue老师所教的学生(blue姓名，blue年龄，blue的学生name,blue的学生年龄)select t.name as tea_name,t.age as tea_age,s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where t.tea_id = st.tea_id and s.stu_id = st.stu_id and t.name = 'blue';//查询red和blue所教的所有的学生（2者的并集 union）select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'red' union select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'blue';//查询red和blue所教的共有的学生（2者的交集）***第一种：笛卡尔积select a.* from (select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'red' ) as  a  cross join (select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'blue') as b on a.stu_name = b.stu_name;第二种：内连接select a.* from (select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'red' ) as  a  inner join (select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'blue') as b on a.stu_name = b.stu_name;第三种：where查询select a.* from (select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'red' ) as  a ,(select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'blue') as b where a.stu_name = b.stu_name;[注]：(select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'red' )作为查询结果的表，这种查询方式成为子查询。

附录
修改 root 用户密码

按照本文的安装方式, root 用户默认是没有密码的, 重设 root 密码的方式也较多, 这里仅介绍一种较常用的方式。

使用 mysqladmin 方式:

打开命令提示符界面, 执行命令: mysqladmin -u root -p password 新密码

执行后提示输入旧密码完成密码修改, 当旧密码为空时直接按回车键确认即可。
“`