你学会了些什么190611？--MySQL数据库常用命令

增：insert into user (username,password,realname) values (‘byhy1’,‘password1’,‘realname1’);
删：delete from customer1 where id=16;
改：update customer1 set coin=100 where id=16;
查：select * from user;

select id,username from user;
select * from user where username=‘byhy3’;
select * from customer1 where id > 10 and level < 15;
select * from customer1 where id > 10 or level < 15;
select * from customer1 where id > 10 order by coin asc;
select * from customer1 where id > 10 order by coin desc;

启动MySQL服务：systemctl start mysqld
查看MySQL服务服务状态：systemctl status mysqld
登录root用户：mysql -uroot -p
列出所有数据库：show databases;
创建名为xlhy的数据库：create database xlhy;
指定utf8创建名为xlhy的数据库： create database xlhy character set utf8mb4 collate utf8mb4_unicode_520_ci;
删除dthy数据库：drop database dthy;
切换到dthy数据库：use dthy;
列出当前数据库中所有表：show tables;
创建名为user的表：CREATE TABLE user (
id int NOT NULL AUTO_INCREMENT,
username varchar(150) NOT NULL,
password varchar(128) NOT NULL,
realname varchar(30) NOT NULL,
PRIMARY KEY (id),
UNIQUE (username)
) ENGINE=InnoDB;
查看创建表的语句：show create table user_数据库名称\g
删除表：drop table user;
更改表名字：rename table user to user111;
在user表中添加两列 description 和 qq：alter table user111 add description varchar(850) not null, add qq varchar(20) not null;
在user表中删除两列 description 和 qq：alter table user drop description,drop qq;
user表中修改列 realname 名字为 realname1：alter table user change realname realname1 varchar(30) not null;
插入记录：insert into user (username,password,realname) values (‘byhy1’,‘password1’,‘realname1’);
添加多条记录：insert into user (username,password,realname) values
(‘byhy2’,‘password2’,‘realname2’),
(‘byhy3’,‘password3’,‘realname3’),
(‘byhy4’,‘password4’,‘realname4’);
查询表记录：select * from user;
select id,username from user;
过滤查询：select * from user where username=‘byhy3’;
and查询：select * from customer1 where id > 10 and level < 15;
or查询：select * from customer1 where id > 10 or level < 15;
（or 和 and 同时出现的时候， and 优先级高）
查询结果升序：select * from customer1 where id > 10 order by coin asc;
查询结果降序：select * from customer1 where id > 10 order by coin desc;
修改表记录：update customer1 set coin=100 where id=16;
删除表记录：delete from customer1 where id=16;
给username列创建索引：create index index_username on customer1 (username);
添加唯一索引：create unique index username on customer1(username);
指定 表里面 user_id 字段是 一个外键：
FOREIGN KEY (user_id)
REFERENCES user(id)
ON UPDATE CASCADE
ON DELETE RESTRICT
ON UPDATE CASCADE 自动修改
ON DELETE RESTRICT 禁止该操作
事务
– 1. 创建一个事务
START TRANSACTION;
– 2. 更新book 表
update book set status=1 where id=888;
– 3. 更新 borrow表
insert into borrow (user_id,book_id) VALUES (666,888);
– 4. 提交事务
COMMIT;
– 或者回滚事务
ROLLBACK;
查询student表的第2条到4条记录：select * from student limit 1,3;
从student表中查询计算机系和英语系的学生的信息：select * from student where department in (‘计算机系’,‘英语系’);

（SUM，AVG，COUNT，MAX，MIN)
sum：求和
avg：平均值
count：行数
max：最大值
min：最小值

实战训练


1、创建student和score表
create table student (
id int(10) not null primary key unique auto_increment,
name varchar(20) not null ,
sex varchar(4) ,
birth year,
department varchar(20) not null,
address varchar(50)
);

create table score (
id int(10) not null primary key unique auto_increment,
stu_id int(10) not null,
c_name varchar(20),
grade int(10)
);

2、为student表和score表增加记录
insert into student values(901,‘张老大’,‘男’, 1985,‘计算机系’,‘北京市海淀区’);
insert into student values(902,‘张老二’,‘男’, 1986,‘中文系’,‘北京市昌平区’);
insert into student values(903,‘张二’,‘女’, 1990,‘中文系’,‘湖南省永州市’);
insert into student values(904,‘李四’,‘男’, 1990,‘英语系’,‘辽宁省阜新市’);
insert into student values(905,‘王五’,‘女’, 1991,‘英语系’,‘福建省厦门市’);
insert into student values (906,‘王六’,‘男’, 1988,‘计算机系’,‘湖南省衡阳市’);

insert into score values (NULL,901, ‘计算机’,98);
insert into score values (NULL,901, ‘英语’, 80);
insert into score values(NULL,902, ‘计算机’,65);
insert into score values(NULL,902, ‘中文’,88);
insert into score values(NULL,903, ‘中文’,95);
insert into score values(NULL,904, ‘计算机’,70);
insert into score values(NULL,904, ‘英语’,92);
insert into score values(NULL,905, ‘英语’,94);
insert into score values(NULL,906, ‘计算机’,90);
insert into score values(NULL,906, ‘英语’,85);

3、查询student表的所有记录
select * from student;

4、查询student表的第2条到4条记录
select * from student limit 1,3;

5、从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
select id,name,department from student;

6、从student表中查询计算机系和英语系的学生的信息
select * from student where department in (‘计算机系’,‘英语系’);

7、从student表中查询年龄18~22岁的学生信息
select *,2013-birth as age from student where 2013-birth>=18 and 2013-birth<=22;

8、从student表中查询每个院系有多少人
select department,count(id) from student group by department;

9、从score表中查询每个科目的最高分
select c_name,max(grade) from score group by c_name;

10、查询李四的考试科目（c_name）和考试成绩（grade）
select c_name,grade from score where stu_id = (select id from student where name=‘李四’);

11、用连接的方式查询所有学生的信息和考试信息
select student.id,name,sex,birth,department,address,c_name,grade from student,score where student.id=score.stu_id;

12、计算每个学生的总成绩
select name,sum(grade) from student,score where student.id=score.stu_id group by name;

13、计算每个考试科目的平均成绩
select department,avg(grade) from score group by department;

14、查询计算机成绩低于95的学生信息
select student.id,name,c_name,grade from student,score where grade<95 and c_name in (‘计算机’) and student.id=score.stu_id;

15、将计算机考试成绩按从高到低进行排序
select stu_id,c_name,grade from score where c_name = ‘计算机’ order by grade desc;