一直以来。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);