MySQL笔记创建表结构_mysql笔记

mysql端口号：3306

mysql默认管理员：root

进入本机mysql的命令：mysql -u 用户名 -p密码，例如：mysql -u root -p123

远程访问mysql的指令：mysql -h 主机地址 -u 用户名 -p密码 例如：mysql -h 172.18.101.33 -u root -p123

查看数据库的指令：show databases;

------------------------------------创建与删除数据库-------------------------------------------

创建数据库的指令： create database 数据库名; 例如：create database wg1302a;

如果不存在则创建数据库的指令：create database if not exists 数据库名; 例如：create database if not exists wg1302a;

删除数据库的指令： drop database 数据库名; 例如：drop database wg1302a;

如果存在则删除数据库的指令： drop database if exists 数据库名; 例如：drop database if exists wg1302a;

-------------------------启动和关闭mysql服务-------------------------------------------

第一种方法：控制面板->管理工具->服务->mysql->右键属性来启动或关闭mysql服务

第二种方法：net start mysql (开启mysql服务的指令) 或者 net stop mysql(关闭mysql服务的指令)

打开指定的数据库：use 数据库名 例如：use wg1302a

显示所有表的指令：show tables;

----------------------------创建表----------------------------------

create table student( 创建一个名为student的表

xuehao int primary key auto_increment, 学号字段，类型为整型，主键，自动增长

xingming varchar(15) not null, 姓名字段，类型为可变长字符，最长15个字符，不能为空

xingbie enum('m','w'), 性别字段，枚举类型，有两个值“w”和“m”

csrq date, 出生日期字段，日期类型

dianhua varchar(11) not null, 电话字段，可变长字符，最长11，不能为空

dizhi varchar(50), 地址字段，可变长字符，最长50

youbian char(6), 邮编字段，定长字符，长度为6

gongzi decimal(8,2) default 0.00, 工资字段，类型为浮点型，最长8位，小数点后保留2位

beizhu text 备注字段，类型为不限长字符

);

create table student(

xuehao int primary key auto_increment,

xingming varchar(15) not null,

xingbie enum('m','w'),

csrq date,

dianhua varchar(11) not null,

dizhi varchar(50),

youbian char(6),

gongzi decimal(8,2) default 0.00,

beizhu text

);

create table rgstudent(

id int primary key auto_increment,

name varchar(15),

sex enum('男','女'),

tel varchar(11),

addr varchar(50),

email varchar(20),

sal decimal(8,2)

);

create table student(

id int primary key auto_increment,

name varchar(15) not null,

sex enum('m','w'),

address varchar(50),

youbian char(6),

gongzi decimal(8,2)default 0.00,

beizhu text

);

删除数据库的指令：drop table 表名; 例如：drop table student;

显示表结构的指令：desc 表名; 或者describe 表名; 例如：desc student;

--------------------------------------------修改表结构的方法-------------------------------------

alter table student add age int after birth; student表中的birth字段后增加一个age字段，类型为int

alter table student drop other; 将student表中的other字段删除

alter table student change tel telphone varchar(11); 将student表中的tel字段的名字修改为telphone，类型为可变长11

alter table student modify sal decimal(9,2); 将student表中的sal字段的类型改为decimal(9,2)

----------------------------------复制表的指令---------------------------

create table stu like student; 创建一个与旧表student表结构相似的新表stu，注意：这种方法只复制表结构，不复制数据

insert into stu select * from student; 将student表中的所有复制到stu表中

create table stu select * from student;创建一个与旧表student表结构相似的新表stu，注意：这种方法不仅复制表结构，还复制数据

----------------数据表中的四种操作“增删改查”，对应的指令分别为“insert into，delete，update，select ”---------------

插入数据：insert into student values (1,'zhangsan','男','1996-09-08','12345678901','beijing',8000,'010001',''); 这种方式必须按个依次插入

insert into student values (1,'zhangsan','男',null ,'12345678901','beijing',8000,'010001',null);

insert into student(name,sal) values('lisi',10000); 这种方式是选择性的插入数据，但是必须包括所有非空字段

delete from student where id=4; 删除student表中id为4的记录

delete from student where id =2; 删除student表中id为2的记录

select * from 表名;

select * from student; 查询student表中所有字段对应的值，其中“*”代表所有字段

update student set sex='男' where id=7; 将student表中的id为7的记录对应的sex字段的值改为“男”

update student set name='宋旭尧' where id=88; 将id为88的记录对应的name字段的值修改为‘宋旭尧’

-------------------------------------------表的重命名--------------------------------------------

alter table student rename [to] teacher; 将student表重新命名为teacher

alter table student rename teacher; 同上

rename table teacher to student; 将teacher表重命名为student

------------------------------------------------多种查询语句------------------------------------

select * from student where name like '%陈%'; 查询student表中的name字段中包含“陈”的所有记录，其中“%”代表任意的0到多个字符

select * from student where xinzi>=8000 or sex='女'; 查询薪资大于等于8000或者性别为女的记录

select * from student where xinzi>=8000 and xinzi<=10000; 查询薪资在8000到10000之间的记录

select * from student where xinzi between 8000 and 10000; 同上

select * from student where addr in ('河南','北京'); 查询地区在集合(北京或河南)的记录

select addr,count(id) from student group by addr; 查询每个地区有几人

select addr,count(id) from student where xinzi<5000 group by addr; 查询每个地区薪资小于5000的各有几人

select addr,count(id) from student where xinzi>1000 group by addr order by count(id); 查询每个地区薪资小于5000的各有几人并排序，默认为升序

select addr,count(id) from student where xinzi>1000 group by addr order by count(id) asc;查询每个地区薪资小于5000的各有几人并升序排列

select addr,count(id) from student where xinzi>1000 group by addr order by count(id) desc;查询每个地区薪资小于5000的各有几人并降序排列

select addr,count(id) from student where xinzi>1000 group by addr order by count(id) desc limit 3; 查询每个地区薪资小于5000的各有几人并降序排列取前三

Select xueyuan, count(xuehao) from student where sal>=9500 group by xueyuan having count(xuehao)>3;统计student表中每个学院薪资大于9500的学生人数，并筛选出统计人数大于3的

Select xueyuan, count(xuehao) from student where sal>=9500 group by xueyuan having count(xuehao)>3 order by count(xuehao) desc limit 0,4;统计student表中每个学院薪资大于9500的学生人数，并筛选出统计人数大于3的，然后按统计人数降序排列后限定前3名

注：group by 按...进行分组

order by 按...进行排序

ascend 升序

descend 降序

----------------------------------常用函数------------------------------

select max(xinzi) from student; 求student表中薪资的最大值 max( ) 最大值函数

select min(xinzi) from student; 求student表中薪资的最小值 min( ) 最小值函数

select avg(xinzi) from student; 求student表中薪资的平均值 avg( ) 平均值函数

select sum(xinzi) from student; 求student表中薪资的总和 sum( ) 求和函数

select count(id) from student; 统计student表中id字段的总个数 count( )统计个数的函数

-------------------------------------索引的创建与删除----------------------------

1.在建表的同时创建索引

create table xueyuan(

xyid int primary key auto_increment,

xymc enum('网工','软工','广告','动画','基础') not null,

xyrs int not null,

yz varchar(10),

index in_xymc(xymc) 为xuyuan表中的xymc字段创建名为in_xymc的索引

);

2.为已经创建好的表创建索引

create index in_name on student(name);

查看索引的指令

show index from 表名; 例如：show index from student;

show keys from 表名; 作用同上。

drop index in_name on student; 删除student表中的名为in_name的索引

-------------------------------------扩展知识--------------------------------------------

create table jy(

id int not null,

xyid int not null,

name varchar(15),

sal decimal(8,2),

primary key (id,xyid),

foreign key (id) references student(id),

foreign key (xyid) references xueyuan(xyid)

);

------------------------------------------------修改用户密码的方法--------------------------------

1.update mysql.user set password=password('新密码') where host='主机地址' and user='用户名'; 其中“localhost”代表本地主机，“%”代表远程主机

flush privileges; 刷新权限列表

例如：update mysql.user set password=password('123') where host='localhost' and user='root'; 将本地root用户的密码改为‘123’

flush privileges;

update mysql.user set password=password('123') where host='%' and user='root'; 将远程root用户的密码改为‘123’

flush privileges;

2.set password for 用户@'主机地址'=password('新密码');注意：这种方法不需要刷新权限

例如：set password for root@'localhost'=password('789');

----------------------------忘记密码的恢复步骤------------------------------

1.cmd

2.net stop mysql

3.mysqld --skip-grant-tables

4.再次cmd

5.mysql

6.修改密码

7.关掉所有窗口，以新密码进入mysql

----------------------------------------数据库的备份与恢复------------------------------------

mysqldump -u 用户名 -p密码 数据库名>盘符:\路径\文件名.sql

例如：

1.mysqldump -u root -p123 --all-databaes>d:\wg\all.sql 备份所有数据库到d盘wg文件夹下的all.sql文件中

2.mysqldump -u root -p123 --database wg1302a>d:\wg\wg1302a.sql 备份wg1302a数据库到d盘wg文件夹下的wg1302a.sql文件中

3.mysqldump -u root -p123 wg1302a student>d:\wg\stu.sql 备份wg1302a数据库中的student表到d盘wg文件夹下的stu.sql文件中

source 盘符:\路径\文件名.sql

例如：

source d:\wg\all.sql 恢复d盘wg文件夹下的all.sql文件中备份的内容

----------------------------------------------------------用户的创建与授权----------------------------

create user 用户名@'主机地址' identified by'密码';

用户的权限可以分为四个层级：全局(标志是：“.”)，数据库(标志是：“数据库名.*”)，表(标志是：数据库名.表名)，列(标志是：权限(列名))

例子：

create user caopeng@'172.18.101.99' identified by'123';

create user liuzican@'%' identified by'123';

create user yaotian@'localhost' identified by'123';

create user wangzhiqiang@'localhost' identified by'123';

grant 权限列表 on 数据库名.表名 to 用户名@'主机地址';

例子：

grant all on . to caopeng@'172.18.101.99'; 其中,all代表所有权限，.代表所有数据库的所有表

grant all on wg1302a.* to liuzican@'%';

grant select,insert,update on wg1302a.student to yaotian@'localhost';

grant select(id,name,xinzi),update(xinzi) on wg1302a.student to wangzhiqiang@'localhost';

grant 权限列表 on 数据库名.表名 to 用户名@'主机地址' identified by'密码' ; 在授权的同时创建用户

grant all on . to tom@'localhost' identified by'123';

revoke 权限列表 on 数据库名.表名 from 用户名@'主机地址'; 收回权限

revoke all on . from caopeng@'172.18.101.99';

-----------------------------------------------------------------------------------------------------------------------------数据库的字符集———————————————————

mysql> show variables like 'character_set_%'; 查看数据库的字符集

mysql> set globle character_set_server=utf8; 临时修改字符集

vim /etc/my.cnf 在[mysql]后添加character-set-server=utf8 永久设置字符集(在linux服务器中)

vim /etc/my.ini 在[mysql]后添加default-character-set=utf8

在[mysqld]后添加character-set-server=utf8

重启数据库 永久设置字符集(在linux服务器中)

alter table 表名 default charset utf8; 修改表的字符集

alter database 数据库名 character set utf-8; 修改数据库的字符集