菜鸟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 studentwhere sex='M'return the view:+----------+----------+| count(*) | avg(age) |+----------+----------+|       66 |  14.5152 |+----------+----------+1 row in set (0.01 sec)

other

//找出任课为Math的老师名字 select teacher_id,teacher_namefrom teacherwhere 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)//找出没有任课的老师的名字和idselect teacher_id,teacher_namefrom teacherwhere teacher_id not in (                         select teacher_id                         from class);