sql常用命令练习（一）

use myemployees;
show tables;
select first_name from employees where department_id in (select department_id from departments where location_id=1700)
#子查询
select last_name from employees where salary >(select salary from employees where last_name = ‘Abel’);
select employee_id,last_name,job_id,salary from employees where salary < All (select salary from employees where job_id = ‘IT_PROG’) and job_id <> ‘IT_PROG’;
#在查询时基于未知的值时，应使用子查询。
create database employees;
show databases;
use employees;
create table dept(deptno int(2),dname varchar(14),loc varchar(13));
describe dept;
use employees;

create table emp(
emp_id int auto_increment,
emp_name char(20),
salary double,
birthday date,
primary key(emp_id)
);
desc emp;
create table emp1(id int(10) not null,name varchar(20) not null default ‘abc’,sex char null);
alter table emp1 modify sex varchar(30) not null;
alter table emp1 modify sex varchar(30) null;
alter table emp modify name varchar(15) default ‘abc’ null;
create table user(id int not null,name varchar(25),password varchar(16),constraint uk_name_pwd unique(name,password));
alter table user add unique(name,password);
alter table user add constraint uk_name_pwd unique(name,password);
alter table user modify name varchar(20) unique;
alter table user drop index uk_name_pwd;
create table emp4(id int auto_increment primary key,name varchar(20));
create table emp5(id int not null auto_increment,name varchar(20),pwd varchar(15),constraint emp5_id_pk primary key(id));
create table emp6(
id int not null,
name varchar(20),
pwd varchar(15),
constraint emp7_pk primary key(name,pwd)
);

alter table emp5 drop primary key;
alter table emp5 add primary key(name,pwd);
alter table emp5 modify id int primary key;
create table dept1(dept_id int auto_increment primary key,dept_name varchar(20));
create table emp(
emp_id int auto_increment primary key,
last_name varchar(15),
dept_id int,
constraint emp_dept_id_fk foreign key(dept_id) references dept(dept_id)
);

create table classes(
id int,
name varchar(20),
number int,
primary key(name,number)
);
create table student(
id int auto_increament primary key,
classer_name varchar(20),
classes_number int,
foreign key(classes_name,classes_number)
peferences classes(name,number)
);

alter table emp drop foreign key emp_dept_id_fk;
alter table emp add [] foreign key(dept_id) references dept(dept_id);

create table student(
id int auto_increment primary key,
name varchar(20),
classes_name varchar(20),
classes_number int,
foreign key(classes_name,classes_number)
references classes(name,number) on delete cascade
);
desc temp;
create table temp(
id int auto_increment,
name varchar(20),
age int check(age > 20),
primary key(id)
);

select * from table limit 0,10;
select * from table limit 10,10;
select * from table limit 20,10;

#mysql中使用limit实现分页公式 ：
（当前页数-1）*每页条数，每页条数
select * from table limit(PageNo - 1)*PageSize,PageSize;