MySQL练习1

一、创建数据库和表

1.新建产品库mydb6_product

create database mydb6_product;        //创建（create）数据库（database）

use mydb6_product;        //进入数据库

select database();        //查看当前数据库

2.employees表
列1:id，整型，主键

列2:name，字符串，最大长度50，不能为空

列3:age，整型

列4:gender，字符串，最大长度10，不能为空，默认值"unknown"

列5:salary，浮点型。

（1）

create table employees(        //创建（create）表（table）

in int primary key,        //int-整型，primary key-主键

age int,

gender varchar(50) not null default 'unknown',        //varchar-字符串且最大为50，not null-不为0，default 'unknown'-设置默认值为'unknown'

salary float);        //float-浮点型

（2）

desc employees;        //查看表设置属性

3.orders表
列1:id，整型，主键

列2:name，字符串，最大长度100，不能为空”

列3:price，浮点型

列4: quantity，整型

列5:category，字符串，最大长度50。

（1）

create table orders(        //创建表

id int primary key,        //primary key-设置主键

name varchar(100) not null;        //varchar(100)-最大为100的字符串，not null-不为0

price float,

quantity int,

category varchar(50));

（2）

desc orders;        //查看表设置属性

4.invoices表
列1:number，整型，主键自增长

列2:order_id，整型，外键关联到orders表的id列

列3:in_date:日期型

列4:total_amount:浮点型，要求数据大于0

（1）

create table invoices(        //创建表

number int primary key auto_increment,        //primary key-设置主键，auto_increment-设置自增长

order_id int,

in_date date,        //date-日期

total_amount float,

check(total_amount>0),        //设置数据大于0

foreign key(order_id) rederences orders(id));        //foreign key(order_id)-外键，rederences-引用（主键）

二、查询表信息

创建数据库

create database mydb8_worker;

use mydb8_worker;        //进入数据库

select database();        //查看当前数据库

创建表

create table t_worker(

department_id int(11) not null comment '部门号',

//int(11)--长度为11的整型，not null--不为0，comment--注释

worker_id int(11) primary key not null comment '职工号',

//int(11)--长度为11的整型，primary key--设置主键，not null--不为0，comment--注释

worker_date date not null comment '工作时间',

//date--日期型

wages float(8,2) not null comment '工资',

//float(8,2)--长度为8并保留2位的浮点型

politics varchar(20) not null default '' comment '政治面貌',

//varchar(20)--最大长度为20的字符串

name varchar(20) not null comment '姓名',

borth_date date not null comment '出生日期');

插入数据

insert into t_worker values

(101, 1001, '2015-5-4', 7500.00, '群众', '张春燕', '1990-7-1'),

(101, 1002, '2019-2-6', 5200.00, '团员', '李名博', '1997-2-8'),

(102, 1003, '2008-1-4', 10500.00, '党员', '王博涵', '1983-6-8'),

(102, 1004, '2016-10-10', 5500.00, '群众', '赵小军', '1994-9-5'),

(102, 1005, '2014-4-1', 8800.00, '党员', '钱有财', '1992-12-30'),

(103, 1006, '2019-5-5', 5500.00, '党员', '孙菲菲', '1996-9-2');

1.显示所有职工的基本信息。    

select * from t_worker;

//select--查询，*--所有，from--指定表

2.查询所有职工所属部门的部门号，不显示重复的部门号。  

select distinct department_id from t_worker;

//select--查询，distinct--不重复，department_id--被指定字段（列），from--指定表

3.求出所有职工的人数。    

select count(*) as t_num from t_worker;

//count(*)--统计数据，as t_num--起别名为t_num

4.列出最高工和最低工资。

select max(wages),min(wages) from t_worker;

//max(wages)--求wages数据中的最大值，min(wages)--求wages数据中的最小值

    

5.列出职工的平均工资和总工资。  

select avg(wages),sum(wages) from t_worker;

//avg(wages)--求wages数据的平均值，sum(wages)--求wages数据的总和 

6.创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。    

create table w_date select worker_id,worker_date,name from t_worker;

//create table--创建表，select--查询

select * from w_date;

7.显示所有党员的年龄。    

select name,year(curdate())-year(borth_date) as age from t_worker where politics='党员';

//year(curdate())-year(borth_date) as age--现在日期-出生日期并起别名为age，where--筛选，politics='党员'--指定政治面貌为党员

8.列出工资在4000-8000之间的所有职工姓名。  

select worker_id,name from t_worker where wages>=4000 and wages<=8000;

//where--筛选 

9.列出所有孙姓和李姓的职工姓名。    

select worker_id,name from t_worker where name like '孙%' or name like '李%';

//name like '孙%'--指定姓孙的职工

10.列出所有部门号为102和103且不是党员的职工号、姓名。  

select worker_id,name from t_worker 

where (department_id=102 or department_id=103) and politics!='党员';

//(department_id=102 or department_id=103)--指定部门号为102或103，politics!='党员'--指定不是党员 

11.将职工表t_worker中的职工按出生的先后顺序排序。    

select name,borth_date from t_worker order by borth_date;

//order by--排序，在字段后写asc是升序，写desc是降序，默认不写是asc（升序）

12.显示工资最高的前3名职工的职工号和姓名。  

select worker_id,name from t_worker order by wages desc limit 3;

//order by--排序，desc--降序，limit 3显示前3条信息

13.求出各部门党员的人数。    

select department_id,count(*) from t_worker where politics='党员' group by department_id;

//count(*)--统计数据，group by--分组

14.统计各部门的工资和平均工资并保留2位小数。

select department_id,round(sum(wages),2) as w_sum,round(avg(wages),2) as w_avg

from t_worker group by department_id;

round(sum(wages),2)--round(数值，2)对数值保留2为小数，sum(wages)--对wages数据求总和，avg(wages)--对wages数据求平均值，group by--分组

15.列出总人数大于等于3的部门号和总人数。

select department_id,count(*) as d_count from t_worker 

group by department_id having count(*)>=3;

//count(*)--统计数据，group by--分组，having--对分组后的数据进行筛选