1、查看数据库里面所有的数据库
show databases;
创建数据库
create database student;
使用数据库
use student;
查看上面利用的数据库里面的表
创建表(五个属性,账号,名字,年龄,性别,地址)
create table student_info(
id int not null ,
name varchar(20) default 'name',
age int,
sex varchar(5),
adress varchar(30)
);
展示表结构:
mysql> desc student_info;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | name | |
| age | int(11) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
| adress | varchar(30) | YES | | NULL | |
| class | int(11) | NO | | | |
+--------+-------------+------+-----+---------+----------------+
| id | name | age | sex | adress | class |
+----+------+------+------+--------+-------+
| 1 | a | 18 | girl | nanhua | 2 |
| 2 | b | 18 | girl | nanhua | 3 |
| 3 | c | 18 | girl | nanhua | 4 |
insert into class (id,name) values(1,'网络');
设置一下主键:
alter table student_info add primary key(id);
s设置自增(modify)
alter table student_info modify id int not null auto_increment;
增 Insert into student_info(name ,age, sex,address)value ("a",18,“女”,“南华大学”),("a",18,“女”,“南华大学”);
多条记录,就是可以直接在后面加上逗号再添加。
删 delete if from class were id=3; 根据一个属性删除某条记录
查
选出 年龄小于20的名字和班级
select s.name,s.class from student_info s where age<20
改
update student_info set age:17 where id=3;修改多个属性,就在属性后面打逗号,继续加(更改摸个属性的某个值)
(增加一类属性) alter table student——info add column class int not null;
alter table student_info modify id int auto_increment;(设置自增)
两种方式表达
update student_info set age:17 where id=3 or id=4;
update student_info set age:17 where id in (3,4);
求纪录条数
select count(*) as 总记录数 from student_info;
select avg(age) as 平均年龄 from student_info;
select c.name,s.name from class c ,student_info s where s.age<20 and s.class=c.id;
模糊查询:
select *from student_info where address like '%nanhua%';(360里面用的是模糊查询,每次可以根据输入的东西得到一大堆相关子查询)
升序:
select name,age from student_info order by age (默认升序) desc (降序)
show databases;
创建数据库
create database student;
使用数据库
use student;
查看上面利用的数据库里面的表
show tables
创建表(五个属性,账号,名字,年龄,性别,地址)
create table student_info(
id int not null ,
name varchar(20) default 'name',
age int,
sex varchar(5),
adress varchar(30)
);
展示表结构:
mysql> desc student_info;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | name | |
| age | int(11) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
| adress | varchar(30) | YES | | NULL | |
| class | int(11) | NO | | | |
+--------+-------------+------+-----+---------+----------------+
更改当前表里的属性:
设置一下主键:
alter table student_info add primary key(id);
s设置自增(modify)
alter table student_info modify id int not null auto_increment;
在当前表里增加一类属性:
alter table student——info add column class int not null;
展示表里面存储的数据:
mysql> select *from student_info;
+----+------+------+------+--------+-------+| id | name | age | sex | adress | class |
+----+------+------+------+--------+-------+
| 1 | a | 18 | girl | nanhua | 2 |
| 2 | b | 18 | girl | nanhua | 3 |
| 3 | c | 18 | girl | nanhua | 4 |
+----+------+------+------+--------+-------+
插入一组数据:
mysql> insert into student_info (name ,age,sex,adress,class) values('b',18,'girl','nanhua',3);insert into class (id,name) values(1,'网络');
主键:
唯一、索引(例如:数组的下标),非空设置一下主键:
alter table student_info add primary key(id);
s设置自增(modify)
alter table student_info modify id int not null auto_increment;
增 Insert into student_info(name ,age, sex,address)value ("a",18,“女”,“南华大学”),("a",18,“女”,“南华大学”);
多条记录,就是可以直接在后面加上逗号再添加。
删 delete if from class were id=3; 根据一个属性删除某条记录
查
选出 年龄小于20的名字和班级
select s.name,s.class from student_info s where age<20
改
update student_info set age:17 where id=3;修改多个属性,就在属性后面打逗号,继续加(更改摸个属性的某个值)
(增加一类属性) alter table student——info add column class int not null;
alter table student_info modify id int auto_increment;(设置自增)
两种方式表达
update student_info set age:17 where id=3 or id=4;
update student_info set age:17 where id in (3,4);
求纪录条数
select count(*) as 总记录数 from student_info;
select avg(age) as 平均年龄 from student_info;
select sum(age) as 总年龄 from student_info;
//查询出学生表里的学生的名字和班次:
seletct sub.name,c.name from class c,(select name,class from student_info where age<20) sub where c.id=sub.class;select c.name,s.name from class c ,student_info s where s.age<20 and s.class=c.id;
模糊查询:
select *from student_info where address like '%nanhua%';(360里面用的是模糊查询,每次可以根据输入的东西得到一大堆相关子查询)
升序:
select name,age from student_info order by age (默认升序) desc (降序)