MySQL
一、服务
系统:services.mcs
net start mysql 开启服务
net stop mysql 关闭服务
mysql -uroot -p(pwd) -h127.0.0.1 3306 -P
链接本地是简写为:mysql -uroot -p
passwd:hch
二、基本操作
1、操作文件夹、即库 :
(库名无法改变)
增:create database db1 charset utf8;
查看所有数据库:show databases;
单独查看某一个:show create database db1;
显示当前数据库:select database();
改变编码:alter database db1 charset gbk;
删除: drop database db1;
上课所讲的创建数据库的方法:
--建立数据库xsgl
create database xsgl
on
(
name=xsgl_data,
filename='D:\xupeng\db\xsgl.mdf',
size=10mb,
maxsize=50mb,
filegrowth=10%
)
log on
(
name=xsgl_log,
filename='D:\xupeng\db\xsgl.ldf',
size=5mb,
maxsize=20mb,
filegrowth=10%
)
2、操作文件,即表:
先切换到文件夹下:use db1 进入mysql数据库
增:create table t1(id int,name char);
查:show tables; 显示当前库中所有表的名称
show create table tt; 查看tt表的详细信息
desc tt; 查看表结构(describe)
改:alter table t1 modify name char(3);
alter table t1 change name name1 char(2); 顺便改名
删:drop table t1;
3、操作文件中的内容/记录 (重难点):
3.1 添加数据
普通添加数据:
insert into hao.tt values
(1,'egon1'),
(2,'egon2'),
(3,'egon3');
添加一行数据:
insert into hao.tt(name,id) values
('egon2', 4);
添加一列数据:
alter table info add math_score int;
将mysql.user表中查询内容添加到trymore.user1表中,没有此表则创建。两种方案:
create table trymore.user1 select Host,User from mysql.user;
insert into user1 select Host,User from mysql.user;
3.2 查询数据
_ 指一个字符 % 任意长度的字符
[] 括号中所指定范围内的一个字符 [^] 不再括号指定范围中的一个字符
查询数据:
select * from tt; 查看tt表中所有内容
select * into newtable from tt; 查看tt表中所有内容并将他们写入新表
select * from mysql.user\G; 当表中数据过多时,使用\G分行显示
select id,name from tt; 查看tt表中id和name列的数据
select name as '姓名' from tt; 查看tt表的name列并使用别名‘姓名’显示
select * from tt where sex = '女'; 查看tt表中sex列为女的数据
select * from tt where name = '李%'; 查看tt中姓李的数据
select * from tt where name = '李_'; 查看tt中姓李且名字两个字的
select * from tt where sex = '女' and name like '李%';
select * from student where studentname like '[^陈,傅,葛]%'
找出不是姓陈,傅,葛的
select * from student where mail like '%@[163,126]%'
找出mail列是以163或者126开头的
select name,birthday,addr from tt,dd where tt.id=dd.i;
从表tt和表dd中查询关于name,birthday,addr的数据,两表外键链接为tt中的id和dd中的i。
select * from tt where YEAR(birthday)=1990;
查表tt中1990年出生的(前提:存在birthday列,且存储年月日信息)
select * from tt order by id asc,name asc;
将表tt中的内容按照id升序排列并显示(asc-升序、、desc-降序)
若是id相同,则按照name升序排列
select top 3 * from tt order by id desc;
将表tt中的内容按照id降序排列并显示前三(好像在命令行中不可使用)
select top 3 studentid,SUM(score) as '总分' from tt group by studentid order by SUM(score) desc;
分类函数group by往往与聚合函数一起使用
---between and
select * from student where birthdate between '1996-01-01' and '1996-12-31'
--in
select * from student where studentid in (select studentid from cj where grade<60)
--找出至少三门课不及格的同学信息
select * from student where studentid in (select studentid from cj where grade<60 group by studentid having COUNT(*)>=3)
select * from student where left(studentname,1) in ('陈','葛')
3.3 更改数据
改:update tt set name='sb' where id=2; 改那些(where),改id为2的行
update score set grade=grade+1 where studentid in (select studentid from student where name like "陈%")
查询表student中姓陈的同学,将他们的在表score中的成绩都增加一分
修改表名:alter table 表名 rename 新表名;
3.4 删除数据
删:delete from tt; 删除整张表
delete from tt where id=1; 删除特定内容
清空表:truncate tt;
4、创建默认时间表:
create table tt2(name timestamp); 创建新表(空)
insert into tt3 values(); 向里面传空值
select * from tt3; 默认出现当前时间
三、函数
1、时间函数
--getdate()获得是当前系统日期
print getdate()
--getutcdate()获得系统的UTC时间
print getutcdate()
--datename()函数
print datename(year,getdate())
print datename(month,getdate())
print datename(day,getdate())
print datename(week,getdate())
print datename(quarter,getdate())
print datename(hour,getdate())
print datename(minute,getdate())
print datename(second,getdate())
--datepart()函数
print datepart(year,getdate())
print datepart(month,getdate())
--day()year()month()week()
print day(getdate())
print year(getdate())
print month(getdate())
--dateadd()过了多长时间是什么日期
print dateadd(year,150,getdate())
print dateadd(month,1000,getdate())
print dateadd(day,10000,getdate())
print dateadd(hour,10000,getdate())
print dateadd(week,100,getdate())
--datediff(year|month|day|hour|minute|week|quarter,date1,date2)
--计算从date1到date2的year数
select studentid,studentname,datediff(year,birthdate,getdate()) as age,datediff(month,birthdate,getdate()),datediff(day,birthdate,getdate()) from student
2、数学函数
--abs()绝对值
print abs(-1.25)
--ceiling()天花板
print ceiling(1.25)
print ceiling(-1.25)
--floor()地板
print floor(1.25)
print floor(-1.25)
--power()求幂值
print power(2,3)
--round()四舍五入函数
print round(12.568,2)
print round(12.568,1)
--sign()符号函数,只有0,+1,-1三种结果
print sign(0)
print sign(1.258)
print sign(-2.58)
--sqrt()平方根函数
print sqrt(9)
3、聚合函数
--聚合函数sum()
select * from cj
select studentid,SUM(grade) from cj group by studentid
--只有分类汇总的字段才能出现在字段列表中
--聚合函数avg()
select studentid,avg(grade) from cj group by studentid
--聚合函数max()/min()
select studentid,max(grade) from cj group by studentid
select studentid,min(grade) from cj group by studentid
--聚合函数 计算函数count()
select sex,COUNT(*) from student group by sex
select YEAR(birthdate),COUNT(*) from student group by YEAR(birthdate)
select * from student
4、字符串函数
--1.left函数
declare @c varchar(100)
set @c='information'
print left(@c,1)
--2.right函数
print right(@c,1)
--3.len函数 求字符串的长度
print len(@c)
--4.ascii函数,求字符的ascii码
print ascii('b')
--5.char函数 将ascii码值转为字符
print char(102)
--6.charindex函数,串1在串2中的起始位置
print charindex('at','informationation')
--7.lower函数,改小写
print lower('ABGffgg')
--8.upper函数,改大写
print upper('ABGffgg')
--9.ltrim函数,除字符串的左边的空格
declare @a varchar(100)
set @a=' am a teacher '
print 'I'+@a+',but'
print 'I'+rtrim(ltrim(@a))+',but'
--9.rtrim函数,除字符串的右边的空格
--10.patindex函数,字符第一次出现的位置
print patindex('%mat%','information')
print patindex('%ni%', 'haonini')
--11.replace函数,字符串替换函数
print replace('I are a teacher,I are a student','are','am')
print replace('ni hao, my name is h', 'ni hao', 'hello')
--12.stuff函数,从串1的什么位置,替换多少个字符,从零开始数
print stuff('informinion',7,2,'at')
print stuff('hello', 2, 1, 'a')
--13.space函数 ,空格函数
print 'I'+space(10)+'am a teacher'
--14.str函数
print 'your english grade is '+str(102)
--15.substring函数,取子串函数
print substring('中华人民共和国',5,2)
四、高级操作
1、非空约束
关键词:not null与default
1、创建表
create table tt6(
id int primary key auto_increment,
name varchar(17) not null, (必须不为空,为空就报错)
sex enum('male','female') not null default 'male'
(必须不为空,为空就使用默认值,male)
);
2、插入数据
insert into tt6(name) value('egon'),('han'),('hao');
3、查看结构
desc tt6;
2、unique key
三种方法效果一样。附加约束,标识其是唯一的
第一种方法:create table tt3(x int unique);
第二种方法:cteate table tt4(
x int,
y varchar(18),
unique key(x)
);
最后一行可写成constraint uni_x unique key(x)
表示为约束起一个名字uni_x
第三种方法:create table service(
ip varchar(15),
port int,
unique key (ip, port) 最后没有逗号
);
3、primary key
1、站在约束角度看primary key = not null unique
以后但凡建表,必须注意:
1、必须有且只有一个主键
2、通常是id为主键
create table tt5(
id int primary key auto_increment
);
相当于固定模式,明确指定主键
2、联合主键
create table tt6(
x int,
y int,
constraint pri_x_y primary key(x,y)
);
desc tt6;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x | int(11) | NO | PRI | NULL | |
| y | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
3、存储引擎
show engines; 存储引擎。
使用这个InnoDB,并且其是默认的
4、foreign key
(限制关联表某一个字段的值必须来自被关联表的字段)
1、被关联的字段必须是一个key,通常是id字段
2、创建表时:必须先建立被关联表,才能建立关联表
create table dep(
id int primary key auto_increment,
dname varchar(20),
info varchar(50)
);
create table emp(
id int primary key auto_increment,
name varchar(15),
age int,
dep_id int,
foreign key(dep_id) references dep(id) 本质是同一行
on update cascade 两表更改同步
on delete cascade 两表删除同步
);
最后一行表示我自己的dep_id和dep表的id建立关联,references,
特别注意事项:最后的三行都是一行。。。
3、在插入记录时:必须先插入被关联表,才能往关联表中插入记录
insert into dep(id,dname,info) values
(1, 'IT', '技术部门'),
(2, 'Sale', '文化部门'),
(3, 'HR', '招生部门');
insert into emp(name, age, dep_id) values
('egon',18,3),
('alex',28,1),
('wjj',27,2),
('sdu',23,1);
4、删除记录时:先删除emp表中dep_id为2的内容(第二个部门的所有员工),
才能删除dep表中的第二个部门。
5、使用on update cascade和on delete cascade同步后,直接把第二个部门删除,
emp表中的第二个部门的人也一同被删除。
5、找两张表的关系
5.1 多对一
1、先站在左表的角度:去找左表emp的多条记录能否对应右表dep的一条记录,
翻译:多个员工能否属于一个部门。
2、然后站在右表的角度:去找右表dep的多条记录能否对应左表emp的一条记录,
翻译:多个部门能否拥有同一名员工。
3、若有一个成立,则最终关系为多对一或者一对多。
4、在emp表中新增dep_id字段,该字段外键关联dep(id)。
5.2 多对多
1、双向的多对一就是多对多
2、需要建立第三张表,有一个字段值fk左表,有一个字段的值fk右表
create table author(.....);
create table book(.....);
create table author2book(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id) onupdate cascade on delete cascade,
foreign key(book_id) references book(id) onupdate cascade on delete cascade,
);
五、功能表
名称 | 代码 | 备注 |
---|---|---|
修改新密码 | mysqladmin -uroot -phch password | |
自增 | auto_increment | |
约束 | primary key | |
查看当前库 | select database(); | |
创账号并授权 | grant all on trymore.tt; | all表除grant外的所有权限 |
有关权限的表 | user–db–tables_priv–columns_priv | 优先级从高到低 |
标识该字段的值是唯一的 | unique key (uk) | |
该字段为表的主键 | primary key (pk) | 唯一的标识记录 |
该字段为该表的主键 | foreign key (fk) |
名称 | 代码 |
---|---|
无符号 | unsigned |
使用0填充 | zerofill |
为该字段设置默认值 | default |
标识不能为空 | not null |
六、不常用操作
1、创建账号:
基于服务端输入:
1、create user tom@"客户端IP" identified by "123";
表示允许客户端IP这台电脑拿着tom账号和123密码来登录我服务端。
2、create user jack@"192.168.15.%" indentified by "123";
表示允许客户端IP为这个网段的电脑拿着jack账号和123密码来登录我服务端
3、create user ming@"%" indentified by "123";
表示允许任何IP地址的客户端拿着ming账号和123密码来登录我服务端。
2、创建账号并且授权:
(只有root账号才能为其他账号授权)
grant all on trymore.tt1 # all代表除了grant以外的所有权限
3、破解密码:
1、关闭系统服务中的服务端
2、使用管理员模式mysqld --skip-grant-tables跳过权限开启数据库
3、使用cmd无密码进入mysql -uroot -p
4、update mysql.user set password=password("hch") where user="root" and host="location"; 更改密码,指定用户,指定本机或者IP地址
5、flush privileges;刷新一下
6、使用管理员模式taskkill /F /PID port(进程号)杀死MySQL,然后正常启动服务
4、统一字符编码:
1、在MySQL安装目录下新建my.ini文件
2、修改文件:
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
user="root"
password="hch"
注:user和password可有可无,加上时在命令行输mysql就直接进入了
3、重启MySQL
4、客户端登录,\s查看结果
5、不常用
set global sql_mode=“strict_trans_tables”;
设置sql_mode为严格模式,必须遵守各种规定,例如超过长度,则报错
必须重启MySQL才生效
create table tt4(x tinyint);
整型,默认是有符号的,下面改为无符号的
create table tt4(x tinyint unsigned);
强调:整数类型后面的限制不是存储宽度,而是显示宽度限制,,其他的都是显示储存宽度
create table tt5(id int(11),name char);