mysql入门

发表于 2017-12-04 更新于 2024-08-01 分类于 sql

本文记录mysql的一些常用命令。

启动与停止mysql
1
2
net start mysql
net stop mysql
创建一个名称为mydb1的数据库
1
create database mydb1;
显示所有数据库
1
show databases;
创建一个使用utf-8字符集的mydb2数据库。
1
create database mydb2 character set utf8;

创建一个使用utf-8字符集，并带校对规则的mydb3数据库

1	create database mydb3 character set utf8 collate utf8_general_ci;

查看前面创建的mydb2数据库的定义信息
1
show create database mydb2;
删除前面创建的mydb1数据库
1
drop database mydb1;
查看服务器中的数据库，并把mydb2库的字符集修改为gb2312;
1
alter database mydb2 character set gb2312;

备份mydb3库中的数据，并恢复

备份（退到window命令行窗口）：

1	mysqldump -u root -p mydb3>c:\test.sql

恢复：

1
2
3

create database mydb3;
use mydb3;
source c:\test.sql

创建一个员工表

create table employee
(
    id int,
    name varchar(20),
    sex varchar(4),
    birthday date,
    entry_date date,
    job varchar(100),
    salary double,
    resume text
)character set utf8 collate utf8_general_ci;

在上面员工表的基本上增加一个image列。
1
alter table employee add image blob;
查看表
1
desc employee;

修改job列，使其长度为60。

1	alter table employee modify job varchar(60);

删除sex列
1
alter table employee drop sex;
表名改为user
1
rename table employee to user;
修改表的字符集为utf-8
1
alter table user character set utf8;

查看表的字符集

1	show create table user;(表的创建语句)

列名name修改为username

1	alter table user change column name username varchar(20);

使用insert语句向表中插入三个员工的信息。

1
2
3

rename table user to employee;
insert into employee(id,username,birthday,entry_date,job,salary,resume) values(1,'aaa','1980-09-09','1980-09-09','bbb',90,'aaaa');
insert into employee(id,username,birthday,entry_date,job,salary,resume) values(2,'bbb','1980-09-09','1980-09-09','bbb',90,'aaaa');

插入中文数据

insert into employee(id,username) values(2,'小李子');
show variables like 'chara%';
set character_set_cilent=gb2312;
insert into employee(id,username) values(2,'小李子');

查询时如果发生乱码：

1 2	set character_set_results=gb2312; select * from employee;

将所有员工薪水修改为5000元。
1
update employee set salary=5000;

将姓名为’aaa’的员工薪水修改为3000元。

1	update employee set salary=3000 where username='aaa';

将姓名为’aaa’的员工薪水修改为4000元,job改为ccc。

1	update employee set salary=4000,job='ccc' where username='aaa';

将aaa的薪水在原有基础上增加1000元。

1	update employee set salary=salary+1000 where username='aaa';

删除表中名称为’zs’的记录

1	delete from employee where username='aaa';

删除表中所有记录。
1
delete from employee;
使用truncate删除表中记录
1
truncate table employee
执行sql脚本
1
source c:\student.sql
查询表中所有学生的信息。
1
select * from student;
查询表中所有学生的姓名和对应的英语成绩。
1
select name,english from student;
过滤表中重复数据。
1
select distinct english from student;

在所有学生总分上加10分特长分。

1	select name,(english+chinese+math)+10 from student;

使用别名表示学生分数。

1 2	select name as 姓名,(english+chinese+math)+10 as 总分 from student; select name 姓名,(english+chinese+math)+10 总分 from student;

查询姓名为wu的学生成绩

1	select * from student where name='王五'

查询英语成绩大于90分的同学

1	select name from student where english>90;

查询总分大于200分的所有同学

1	select name from student where (english+chinese+math)>200;

查询英语分数在 80－90之间的同学

1 2	select name from student where english>80 and english<90; select name from student where english between 80 and 90;

查询数学分数为89,90,91的同学

1 2	select * from student where math=80 or math=90 or math=91; select * from student where math in(80,90,91);

查询所有姓李的学生成绩。

1	select * from student where name like '李%'

查询数学分>80，语文分>80的同学。

1	select name from student where math>80 and chinese>80;

对数学成绩排序后输出
1
select * from student order by math;

对总分排序后输出，然后再按从高到低的顺序输出

1	select * from student order by (math+english+chinese) desc;

对姓李的学生成绩排序输出

1	select * from student where name like '李%' order by (math+english+chinese);

统计一个班级共有多少学生？
1
select count(*) from student;
统计数学成绩大于90的学生有多少个？
1
select count(*) from student where math>90;

统计总分大于250的人数有多少？

1	select count(*) from student where (math+english+chinese)>250;

统计一个班级数学总成绩？
1
select sum(math) from student;

统计一个班级语文、英语、数学各科的总成绩

1	select sum(chinese),sum(english),sum(math) from student;

统计一个班级语文、英语、数学的成绩总和
1
select sum(chinese+math+english) from student;

统计一个班级语文成绩平均分

1	select sum(chinese)/count(chinese) from student;

求一个班级数学平均分？
1
select avg(math) from student;

求一个班级总分平均分

1	select avg(math+english+chinese) from student;

求班级最高分和最低分

1	select max(math+english+chinese),min(math+english+chinese) from student;

第6种形式的select:

create table orders(
    id int,
    product varchar(20),
    price float
);
insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);

对订单表中商品归类后，显示每一类商品的总价
1
select product,sum(price) from orders group by product;

查询购买了几类商品，并且每类总价大于100的商品

1 2	select product from orders group by product where sum(price)>100;× select product from orders group by product having sum(price)>100;

主键约束

	create table test1
	(
	    id int primary key,
	    name varchar(20)
	);
	

1. 主键自动增长：

	```sql
	create table test2
	(
	    id int primary key auto_increment,
	    name varchar(20)
	 );

唯一约束和非空约束

create table user
(
    id int primary key auto_increment,
    username varchar(40) not null unique,
    password varchar(40) not null,
    email varchar(100) not null unique
);

外键约束

create table male
(
    id int primary key auto_increment,
    name varchar(40)
);

create table female
(
    id int primary key auto_increment,
    name varchar(40),
    male_id int,
    constraint male_id_FK foreign key(male_id) references male(id)
);

创建部门表和员工表(一对多或多对一)

create table department
(
    id int primary key auto_increment,
    name varchar(100)
);

create table employee
(
    id int primary key auto_increment,
    name varchar(100),
    salary double,
    department_id int,
    constraint department_id_FK foreign key(department_id)references department(id)
);

创建学生、老师表（多对多）

create table teacher
(
    id int primary key auto_increment,
    name varchar(100),
    salary double
);

create table student
(
    id int primary key auto_increment,
    name varchar(100)
);

create table teacher_student
(
    teacher_id int,
    student_id int,
    primary key(teacher_id,student_id),
    constraint teacher_id_FK foreign key(teacher_id) references teacher(id),
    constraint student_id_FK foreign key(student_id) references student(id)
);

insert into teacher(name,salary) values('老王',1000);
insert into teacher(name,salary) values('老李',1000);
insert into student(name) values('aaa');
insert into student(name) values('bbb');
insert into student(name) values('ccc');
insert into teacher_student(teacher_id,student_id) values(1,1);
insert into teacher_student(teacher_id,student_id) values(1,2);
insert into teacher_student(teacher_id,student_id) values(1,3);
insert into teacher_student(teacher_id,student_id) values(2,1);

已知老师的id为1，查询出1号老师所有的学生

1	select s.* from teacher_student t_s,student s where teacher_id=1 and t_s.student_id=s.id;

1号学生有几个老师

1	select teacher.* from teacher_student,teacher where student_id=1 and teacher_student.teacher_id=teacher.id;

创建人、身份证表（一对一）

create table person
(
    id int primary key auto_increment,
    name varchar(30)
);

create table idcard
(
    id int primary key,
    num varchar(30) not null unique,
    constraint person_id_FK foreign key(id) references person(id)
);

数据分页

1	select * from news limits 0,20;第0个位置取20条

当数据库是自动生成主键时，可以使用如下的语句：

1
2
3

st = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
st.executeUpdate();
rs = st.getGeneratedKeys();

事物：逻辑上的一组操作，要么全部成功，要么全部都不成功。

	start transaction 开启事物
	Rollback 回滚事物
	Commit提交事物
	

1. JDBC控制事物语句

	```sql
	conn.setAutoCommit(false);
	conn.commit();
	conn.rollback();

事物设置回滚点

1
2
3

sp = conn.setSavepoint();
conn.rollback(sp);
conn.commit(); 回滚了也要提交事物