如何连接数据库:
- command line client:
输入初始password
然后就可以进行连接
- workbanch:
cmd路径下进行连接(bin)
连接数据库的语句
- 连接数据库:(在cmd中)
mysql-uroot-p(密码)
- 不显示密码:mysql-uroot-p
- 退出数据库:exit;quit
- 查看数据库的类型:select version()
- 显示当前时间:select now()
- 查看当前使用的数据库:select database()
- 查看所有数据库:show databases()
- ==创建数据库==
```
:create database test_02
```
charset=utf8;
- 查看创建数据库的语句:show create database
- ==使用创建的数据库:==
```
use database XXX
```
- 展示数据库:
```
show database
```
- 删除数据库:
```
drop database+数据库名
数据表的操作
navicat注册机可以使用
右键创建new database,charset=utf8
- 查看当前数据库中的数据表:
show tables
- ==创建表:create table== 数据表名字(字段,类型,约束)
- int unsinged auto_increasment not null primary key default
- 创建classes表(id,name):
```
create table classes(id int unsigned primary key not null auto_increment,name varchar(20) not null);
```
- 创建student表:
```
create table students(id int unsigned primary key not null auto_increment ,
name varchar(20) not null,
age int unsigned,
high decimal(5,2),
gender enum('男','女','中性','保密')default '保密',
cls_id int unsigned);
```
- 查看创建的表:show create table students
- ==查看表的结构:desc students;==
- 修改表:alter
==修改表都是用alter==
```
alter table students add pet varchar(20) default 'dog';
```
修改表的字段,最后要加类型及约束(不重名名版)约束写不写都行
```
alter table students modify pet varchar(20) default 'cat';
```
修改表字段,重命名版本
```
alter table students change pet chongwu varchar(20);
```
删除列
```
alter table students drop pet
```
==删除表和数据库:==
```
drop database XXX;
drop table students
``
对数据的操作-增删改查
增加:==全列插入==
```
insert into classes values(1,"zhangsan");
```
向学生表插入一个信息
```
insert into students values(1,'list',18,178,'男','001')
```
==学生表信息,auto_increment:默认自增,主键部分添加 0, null, default==
- ==枚举类型:enum('男','女','中性','保密')==
```
insert into students values(null,'zhaoqi',19,168,2,'002')
```
==zhaoqi的性别就是女,因为枚举类型中女是第二位。==
部分插入
```
insert into students (name) value('老李')
```
多行插入:
```
insert into students values(0,'老刘',45,178,1,'003'),(0,'老张',30,168,2,'002')
```
**修改 update set**
```
update students set gender='中性'
```
按照条件修改
```
update students set gender='女' where
id=2;
```
修改多个值(好多个cls_id为3的)
```
update students set gender='女' where cls_id=003;
```
**查询:select**(略)
**删除 delete from**
```
delete * from students where cls_id=1;
```
以上为物理删除
- ==逻辑删除:用一个字段表示这条信息是否已经不能再使用了。==
- 给students表添加一个is_delete字段bit类型
```
alter table students add is_delete bit default 0;
```
==group_concat==
```
select gender,group_concat(name) as names from students group by gender.
```
==查询出同种性别中的姓名。==
==with rollup 汇总的作用==
```
select gender, count(1) from students group by gender with rollup;
```
最终除了各个性别的数量,还有多了一行总数。
Mysql 查询2
- 分页查询:limit
```
select * from students limit 0,4;
```
选取id从1-4
```
select * from students limit 1,4
```
选取id从2-4
- 内连接
- 左连接
- 子链接
从student表中查询年龄22~26岁的学生信息
```
select
id,
name,
gender,
date_format(now(),'%Y')-birth as age,
department,
address
from student
where year(now())-birth>=22 and year(now())-birth
```
网友评论