01 MySQL 完整笔记

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);