菜鸟MySql

一直以来。mysql的sql语句除了大学时写过，之后就没有怎么写了。下面简单介绍一个mysql入门级的。总结，sql语句的查询有三种，笛卡尔积查询，嵌套查询，存在量词查询。

MySql learming

start mysql server

‘cd /usr/local/mysql/’ use the command: ‘sudo ./support-files/mysql.server start’

login mysql

mysql -uroot -p input root

show mysql databases

show databases; return the view
+——————–+
| Database |
+——————–+
| information_schema |
| ApiDB |
| mysql |
| performance_schema |
| sys |
+——————–+
5 rows in set (0.00 sec)

use Database

use apidb;

show tables

show tables; return tables;
+—————–+
| Tables_in_apidb |
+—————–+
| api_classes |
| function |
| package |
+—————–+
3 rows in set (0.00 sec)

select command study

• select * from package limit 3;

create database

• create database school;

create table

create table <TableName> (
<列名 类型 NOT NULL>,
...
<完整约束>
);

example:

create table teacher(
       teacher_id    char(4) not null,
       teacher_name  char(8) not null,
       title         char(10),
       primary key (teacher_id)
);

create table class(
       class_id char(4),
       class_name char(10) not null,
       teacher_id char(4),
       primary  key (class_id),
       foreign key (teacher_id) references teacher(teacher_id)
);

create table student(
       student_id char(4) not null,
       student_name char(8) not null,
       age smallint,
       sex char(1),
       primary key (student_id)
);

create table choose(
       student_id char(4),
       class_id   char(4),
       score smallint,
       primary key (student_id,class_id),
       foreign key (student_id) references student(student_id),
       foreign key (class_id) references class(class_id)
);

show table describle

mysql> desc student;
+————–+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————–+————-+——+—–+———+——-+
| student_id | char(4) | NO | PRI | NULL | |
| student_name | char(8) | NO | | NULL | |
| age | smallint(6) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+————–+————-+——+—–+———+——-+
4 rows in set (0.01 sec)

insert data

insert into student(student_id,student_name,age,sex)
values('00','GU0',16,'F');

insert into teacher(teacher_id,teacher_name,title)
values('00','PM0','English');

insert into class(class_id,class_name,teacher_id)
values('00','English',teacher_id);

insert into choose(student_id,class_id,score)
values('00','00',80);

count the sex=’M’ and avg the age

select  count(*),avg(age)
from student
where sex='M'

return the view:
+----------+----------+
| count(*) | avg(age) |
+----------+----------+
|       66 |  14.5152 |
+----------+----------+
1 row in set (0.01 sec)

other

//找出任课为Math的老师名字 

select teacher_id,teacher_name
from teacher
where teacher_id in (
                   select teacher_id
                   from class
                   where class_name='Math');

return view
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
| 010        | PM10         |
| 015        | PM15         |
| 02         | PM2          |
+------------+--------------+
3 rows in set (0.01 sec)


//找出没有任课的老师的名字和id
select teacher_id,teacher_name
from teacher
where teacher_id not in (
                         select teacher_id
                         from class);