JavaSQL极简教程 · MySQL · MyBatis · JPA 技术笔记 教程 总结MySQL

史上最全的mysql基础入门教程

2019-11-05  本文已影响0人  智障小鲁班

完整的demo和笔记整理传送门

启动与停止
数据库相关操作
数据库表相关操作
create table 表名(
    列名1 数据类型1,
    列名2 数据类型2,
    ....
    列名n 数据类型n
    );

insert into user2 values (1,'李四','123'); // 增
delete from pet where ower = 'disn'; //删
update pet set name = '后裔' where ower = 'dfn'; //改


①> 、< 、<= 、>= 、= 、<>    
②BETWEEN...AND  
③ IN( 集合)   
④LIKE 模糊查询  
⑤_单个任意字符
⑥%多个任意字符
⑦IS NULL  
⑧and  或 &&
⑨or  或 || 
⑩not  或 !
查询条件应用举例:
SELECT * FROM user WHERE age >= 18;
SELECT * FROM user WHERE age >= 18 AND  age <=36;
SELECT * FROM user WHERE age BETWEEN 40 AND 70;
SELECT * FROM user WHERE age IN (6,18,37);
// 关于NULL
SELECT * FROM user WHERE height = NULL; 错误,因为null值不能使用=或（!=) 判断
SELECT * FROM user WHERE height IS NULL;(正确)
SELECT * FROM user WHERE height  IS NOT NULL;(正确)
// 查询姓陈的有哪些？< like>
SELECT * FROM user WHERE NAME LIKE '陈%';
// 查询姓名第二个字是新的人
SELECT * FROM user WHERE NAME LIKE "_新%";
// 查询姓名是三个字的人
SELECT * FROM user WHERE NAME LIKE '___';
// 查询姓名中包含狗的人
SELECT * FROM user WHERE NAME LIKE '%狗%';
约束相关
 create table user (
    id int primary key auto_increment, // 在创建表时，添加主键约束，并且完成主键自增 
    name varchar(20)
 );
-- 联合主键: 由多个字段联合组成的主键, 只要联合的主键加起来不重复就可以.联合主键中的任何一个字段都不能为空.
create table user2 (
    id int,
    name varchar(20),
    password varchar(20),
    primary key(id, name)
);

表创建完成后:
添加主键.如:
①alter table user add primary key(id);②alter table user modify id int primary key;
删除主键:alter table user drop primary key;

 create table user1 (
    id int primary key auto_increment,
    phone_num varchar(20) unique
     );
 create table user2 (
    id int primary key auto_increment,
    name varchar(20),
    unique(id, name) // 表示两个字段在一起不重复就可以
     );

也可以在表创建完成后, 通过alter table user3 add unique(phone_num);或alter table user3 modify phone_num varchar(20) unique;来添加unique约束.
删除unique约束:alter table user3 drop index phone_num;

create table user3 (
    id int primary key auto_increment,
    name varchar(20) not null
    );

删除非空约束:alter table user3 modify name varchar(20);

create table user4(
    id int primary key auto_increment,
    age int default 18,
    name varchar(20) not null
    );

create table 表名(
....
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
// 班级
create table classes(
    id int primary key,
    name varchar(20)
    );  
// 学生表
create table student (
        id  int primary key,
        name varchar(20),
        class_id int,
        foreign key(class_id) references classes(id)
        );
数据库查询进阶
select * from score where cno = '3-105' and degree > any(select degree from score where cno = '3-245') order by degree desc;


select * from score where cno = '3-105' and degree > all(select degree from score where cno = '3-245') order by degree desc;


select tname as name, tsex as sex, tbirthday as birthday from teacher union select sname, ssex, sbirthday from student;


select tname as name, tsex as sex, tbirthday as birthday from teacher where tsex = '女' union select sname, ssex, sbirthday from student where ssex = '女';



select * from score a where degree < (select avg(degree) from score b where a.cno = b.cno);
表a
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     91 |
| 102 | 3-105 |     92 |
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 104 | 3-105 |     81 |
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
12 rows in set (0.00 sec)   

表b
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     91 |
| 102 | 3-105 |     92 |
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 104 | 3-105 |     81 |
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
12 rows in set (0.00 sec) 


select class from student where ssex= '男' group by class having count(*) > 1


select * from student where sname not like '王%';

select sname, year(now()) - year(sbirthday)  as '年龄' from student;

select max(sbirthday) as '最大', min(sbirthday) as '最小' from student;

select * from student order by class desc, sbirthday;

select * from course where tno in (select tno from teacher where tsex = '男');


select * from score where degree = (select max(degree) from score);

select sname from student where ssex = (select ssex from student where sname = '李军');


select sname from student where ssex = (select ssex from student where sname = "李军") and class = (select class from student where sname = '李军');


select * from score where cno = (select cno from course where cname = '计算机导论') and sno in(select sno from student where ssex = '男');
SQL的四种连接查询
分析用例的数据准备:
mysql> select * from person;
+----+--------+--------+
| id | name   | cardId |
+----+--------+--------+
|  1 | 张三   |      1 |
|  2 | 李四   |      3 |
|  3 | 王五   |      6 |
+----+--------+--------+
3 rows in set (0.00 sec)
mysql> select * from card;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 饭卡      |
|    2 | 建行卡    |
|    3 | 农行卡    |
|    4 | 工商卡    |
|    5 | 邮政卡    |
+------+-----------+
5 rows in set (0.00 sec)

select * from person inner join card on person.cardId = card.id;
+----+--------+--------+------+-----------+
| id | name   | cardId | id   | name      |
+----+--------+--------+------+-----------+
|  1 | 张三   |      1 |    1 | 饭卡      |
|  2 | 李四   |      3 |    3 | 农行卡    |
+----+--------+--------+------+-----------+
2 rows in set (0.00 sec)


select * from person left join card on person.cardId = card.id;

+----+--------+--------+------+-----------+
| id | name   | cardId | id   | name      |
+----+--------+--------+------+-----------+
|  1 | 张三   |      1 |    1 | 饭卡      |
|  2 | 李四   |      3 |    3 | 农行卡    |
|  3 | 王五   |      6 | NULL | NULL      |
+----+--------+--------+------+-----------+
3 rows in set (0.00 sec)

右外连接:右连接 right join 或者right outer join
----右外连接, 会把右边表里面的所有数据取出来, 而左边表中的数据,如果有相等的,就显示出来, 如果没有, 则会补NULL.

select * from person right join card on person.cardId = card.id;

+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+
5 rows in set (0.01 sec)

全外连接:完全外连接 full join 或者full outer join<mysql不支持full join>

mysql> select * from person full join card on person.cardId= card.id;
ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'
**** 解决mysql不支持full join的方法****
 <左连接 + 右链接> , 即通过union来连接左右连接. <左连接 union 右链接>.
eg:

select * from person left join card on person.cardId = card.id union select * from person right join card on person.cardId = card.id;

+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+
6 rows in set (0.01 sec)
要点梳理
MYSQL执行语句顺序，严格遵循次顺序，不能改变
select
from
where
group by
having
order by
mysql的事务
例如:
a --> -100
update user set money = money - 100 where name = 'a';
b --> +100
update user set money = money + 100 where name = 'b';
-- 实际程序中, 如果只有一条sql语句执行成功了,而另外一条没有执行成功?则会出现前后数据不一致的情况.
update user set money = money - 100 where name = 'a';
update user set money = money + 100 where name = 'b';
在多条sql语句,可能会有同时成功的要求,要么就同时失败.


mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
// 建表
create database bank;
create table user (
    id int primary key,
    name varchar(20),
    money int
    );
// 首先在表中插入一条用户数据a.
insert into user values (1,'a',1000);
Query OK, 1 row affected (0.00 sec)
// 进行回滚操作.
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
// 执行回滚后,查看数据表信息,发现即使调用了rollback,但插入的数据依然存在.说明当前不能回滚.
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)
// 可以通过设置msql的回滚自动提交为false.
set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)
// 也就说, 通过上面的set autocommit = 0;操作关闭了mysql的自动提交(commit).
*******再次插入数据:*******
insert into user values (2,'b',1000);
Query OK, 1 row affected (0.00 sec)
// 插入数据后查看表,用户2数据添加成功.
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)
// 执行回滚操作.
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
// 回滚后再次查看表,发现刚才插入的数据已经被干掉了.
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.01 sec)
**** 对于这种场景,如果想让用户b数据成功提交, 可以通过commit;命令执行手动提交操作.手动提交后,如果想再次通过rollback来撤销,则是不可以的.也就是说,事务一旦提交,执行的sql语句就不可以再撤销,也就是说事务一旦提交数据就会持久的产生效果.

(3)手动开启事务
begin和start transaction都可以手动开启一个事务. 也就是说,当我们当前的mysql如果默认的是自动提交模式,则执行rollback进行事务回滚则是无效的. 但是可以通过begin和start transaction手动开启事务.

即:
  当前默认为自动提交模式,此时执行rollback无效.执行下面sql语句:
  start transaction;(或者begin;)
  update user set money = money - 100 where name = 'a';
  update user set money = money + 100 where name = 'b';
  执行完插入a,b用户数据后,再执行rollback,发现可以成功回滚事务.可以成功切换成手动开启事务的模式.若想使得插入的数据生效,也需要手动执行commit进行提交操作.
  事务开启之后,一旦commit提交,就不可以回滚,也就说,当前的这个事务在提交的时候就已经结束了.
  

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)

(2)修改隔离级别
set global tansaction isolation level read uncomitted;

数据库的三大范式
例如:
create table myorder(
        product_id int,
        customer_id int,
        product_name varchar(20),
        customer_name varchar(20),
        primary key(product_id, customer_id
    );
    当前表中, 除主键以外的其他列, 只依赖于主键的部分字段.则不满足第二范式,通常需要拆表.
create table myorder(
        order_id int primary key,
        product_id int,
        customer_id int
    );
create  table product (
        id int primary key,
        name varchar(20)
    );
create table customer(
        id int primary key,
        name varchar(20)
        );
拆分成三个表后,满足第二范式.
附件

查询语句所涉及的sql语句

create table student(
    sno varchar(20) primary key,
    sname varchar(20) not null,
    ssex varchar(20) not null,
    sbrithday datetime,
    class varchar(20)
    );

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(20) not null,
    tbirthday datetime,
    prof varchar(20) not null,
    depart varchar(20) not null
    );

create table course(
    cno varchar(20) primary key,
    cname varchar(20) not null,
    tno varchar(20) not null,
    foreign key(tno) references teacher(tno)
    );

create table score(
    sno varchar(20) not null,
    degree decimal,
    primary key (sno, cno),
    foreign key (sno) references student(sno),
    foreign key (cno) references course(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','王芳','女','1975-02-10','95031');
insert into student values ('106','陆君','男','1974-06-03','95031');
insert into student values ('107','王尼玛','男','1976-02-20','95033');
insert into student values ('108','张全蛋','男','1975-02-10','95031');
insert into student values ('109','赵铁柱','男','1974-06-03','95031');

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');
insert into score values('103','3-105','64');
insert into score values('105','6-166','79');
insert into score values('109','6-166','81');


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

create table card (
    id int,
    name varchar(20)
);

insert into card values (1,'饭卡');
insert into card values (2,'建行卡');
insert into card values (3,'农行卡');
insert into card values (4,'工商卡');
insert into card values (5,'邮政卡');

insert into person values (1,'张三',1);
insert into person values (2,'李四',3);
insert into person values (3,'王五',6);
上一篇 下一篇

猜你喜欢

热点阅读