mysql基础

1.开启mysql的远程访问(remote access)
1.sudo vim /etc/mysql/my.cnf------//打开配置文件
2.bind-address = 127.0.0.1---------//将本句注释掉，任何IP都可以访问。
3.重启mysql服务---------指令：sudo service mysql restart
2.在windows中
启动mysql服务:net start mysql
关闭mysql服务:net stop mysql
3.mysql的登录：mysql -u 用户名 -p (-P(指定登录的端口号，默认为3306)) （-h(要连接服务器的ip地址，默认为127.0.0.1（该地址称为本地回环地址）)）
  查看mysql的版本信息:mysql -V(大写)
  mysql退出：exit/quit/(\q)
4.修改MySQl提示符：
在连接mysql时：mysql -u root -p --prompt 提示符(\h:服务器的名称,\D:完整的日期,\d:当前数据库，\u：当前用户)
连接上客户端后，通过prompt + 提示符
5.显示当前服务器版本：SELECT VERSION();
  显示当前日期时间：SELECT NOW();
  显示当前用户：SELECT USER();
6.MYSQL语句的规范
1.关键字与函数名称全部大写
2.数据库名称、表名称、字段名称全部小写
3.sql语句必须以分号结尾
7.创建数据库：CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name(花括号是必选项，中括号是可选项，竖线表示从两项中做选择)
  查看当前服务器下的数据库列表:show {DATABASES | SCHEMAS} [LIKE 'pattern' | SHERE expr]
  查看警告信息:SHOW WARNINGS;
  显示创建数据库的语句:SHOW CREATE DATABASE 数据库名称;
  修改mysql数据库的编码方式：ALTER DATABASE test CHARACTER SET = UTF8;
  删除数据库:DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
8.在linux下修改mysql的默认编码方式
1.打开my.cnf配置文件 sudo vim /etc/mysql/my.cnf
2.在[client]下添加
default-charater-set=utf8
 在[mysqld]下添加
collation_server = utf8_general_ci
character_set_server = utf8
3.保存退出后重启mysqld：
[root@flyHome gaoxiang]#service mysqld restart
9.mysql数据库的数据类型:
1.整型:
TINYINT占用1个字节;
SMALLINT占用2个字节;
MEDIUMINT占用3个字节;
INT占用4个字节;
BIGINT占用8个字节;
2.浮点型
FLOAT[(M,D)],DOUBLE[(M,D)]:M是数字总位数，D是小数点后面的位数(M>=D)。如果M和D被省略，则根据硬件允许的限制来保存值。
3.日期时间类型（用的比较少）
YEAR占用1个字节:2位或者4位（默认4位），1970到2069年，允许70到69
TIME占用3个字节:-8385959到8385959
DATE占用3个字节:1000.01.01---9999.12.31
DATETIME占用8个字节:1000.01.01 00:00:00---9999.12.31 23:59:59
TIMESTAMP占用4个字节:1970.01.01 00点起--2037年之间的一个值
4.字符类型
CHAR(M):定长类型（少于指定位数，自动用空格补全），占M个字节，0<= M <= 255
VARCHAR(M):变长类型，占L+1个字节，其中L<=M且0<=M<=65535
TINYTEXT:占L+1个字节，其中L<2的8次方
TEXT:占L+2个字节，其中L<2的16次方
MEDIUMTEXT:占L+3个字节，其中L<2的24次方
LONGTEXT:占L+4个字节，其中L<2的32次方
ENUM('value1','value2',...)：占1或2个字节，所占字节数取决于枚举值的个数（最多为65535个值）
SET('value1','value2',...):占1、2、3、4或者8个字节，所占的字节数取决于set成员的数目(做多64个成员)
10.数据表（行：记录，列：字段）:
1.打开数据库：USE 数据库名称;
2.显示当前已经打开的数据库:SELECT DATABASE();
3.创建数据表:CREATE TABLE [IF NOT EXISTS] table_name(列名称 该列的数据类型,...);
4.查看数据表:SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];
5.查看数据表的结构:SHOW COLUMNS FROM tb1_name;
6.往数据表中插入数据：INSERT [INTO] tb1_name[(col_name,...)]（如果省略掉则为给所有的列都赋值） VALUES(value1,...);
INSERT tb5(username,age) values('Tom',22);
7.查找某一条记录:SELECT expr,... FROM tb1_name;
8.CREATE TABLE tb2(
-> username VARCHAR(20) NOT NULL,//表示username这个字段不能为空，在赋值时必须给予值
-> age TINYINT UNSIGNED NULL//表示age这个字段可以为空，在赋值时可以给予NULL
-> );
9.为了保证数据库中某一个记录的唯一性,可以为该记录添加AUTO_INCREMENT属性,它的作用是使每条该记录自动编号,这个属性必须与主键组合使用，默认情况下，起始值为1，每次增量为1
10.主键约束(key)：
每张数据表只能存在一个主键
主键保证记录的唯一性
主键自动为NOT NULL
AUTO_INCREMENT属性只能与主键一起使用，但是主键不一定要与AUTO_INCREMENT一起使用
11.唯一约束(UNIQUE KEY):
唯一约束可以保证记录的唯一性
唯一约束的字段可以为空值（NULL）
每张数据表可以存在多个唯一约束
mysql> CREATE TABLE tb4(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL UNIQUE KEY,
-> age TINYINT UNSIGNED
-> );
12.默认约束:当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。
mysql> CREATE TABLE tb5(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL UNIQUE KEY,
-> sex ENUM('1','2','3') DEFAULT '3'
-> );
13.非空约束(NOT NULL)
14.约束:
约束保证数据的完整性和一致性。
约束分为表级约束和列级约束。
约束类型包括:
NOT NULL(非空约束)
PRIMARY KEY(主键约束)
UNIQUE KEY(唯一约束)
DEFAULT(默认约束)
FOREIGN KEY(外键约束)
15.外键约束:保持数据一致性和完整性,实现数据表的一对一或一对多的关系.
外键约束的要求:
1.父表和子表(外键所在的表为子表，被参照的表为父表)必须使用相同的存储引擎，而且禁止使用临时表。
2.数据表的存储引擎只能为InnoDB
3.外键列和参照列必须具有相似的数据类型，其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
4.外键列和参照列必须创建索引。如果外键列不存在索引的话，mysql将自动创建索引.
外键约束的参照操作：
1.CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
2.SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL约束
3.RESTRICT:拒绝对父表的删除或更新操作。
4.NO ACTION:标准SQL的关键字,在mysql中与RESTRICT相同。
16.查看创建表的指令信息:SHOW CREATE TABLE 表的名称
17.检测某个表是否创建了索引:SHOW INDEXES FROM 表名
18.删除表中的某一条记录:DELETE FROM 表名+条件;
19.添加一列数据:ALTER TABLE 要添加记录的表名 ADD [COLUMN] 新记录的名称  新记录的数据类型 [FIRST(插入到记录的开头)|AFTER记录的名称(在哪个记录之后)](如果不写的话，就表示插入在末尾)
20.添加多列数据:ALTER TABLE 要添加记录的表名 ADD [COLUMN] (第一条记录的名称  第一条记录的数据类型，...)//一次性插入多条记录只能插入到末尾，不能自己指定位置.
21.删除某条记录:ALTER TABLE 要删除记录的表名 DROP 记录的名称;ALTER TABLE users1 DROP password,DROP age;
22.添加主键约束:ALTER TABLE 表名 ADD [constraint PK_users2_id] PRIMARY KEY(记录名称);
23.添加唯一约束:ALTER TABLE users2 ADD UNIQUE KEY(username);
24.添加外键约束:ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES province (id);
25.添加/删除默认约束:ALTER TABLE tb1_name ALTER [COLUMN] 列的名称 {SET DEFAULT 默认值|DROP DEFAULT}
26.删除约束:
删除主键约束：
ALTER TABLE table_name DROP PRIMARY KEY;
删除唯一约束：
ALTER TABLE table_name DROP {INDEX | KEY} index_name;
删除外键约束：
ALTER TABLE table_name DROP FOREIGN KEY fk_symbol;
查看外键约束名称（fk_symbol）：SHOW CREATE TABLE table_name;
27.修改列定义：
ALTER TABLE tb1_name MODIFY [COLUMN] 列名称 列的数据类型 [FIRST|AFTER col_name]
28.修改列名称/列定义(功能比上面的要强大)
ALTER TABLE tb1_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
29.修改数据表的名称
1.ALTER TABLE tb1_name RENAME [TO|AS] new_tb1_name
2.RENAME TABLE tb1_name TO new_tb1_name[,tb1_name2 TO new_tb1_name2]
30.往数据表中插入数据：INSERT
1.insert插入记录
INSERT [INTO] table_name [(column_name,...)] {VALUE} ({exprDEFAULT},...),(...),...;
说明：此方法比较常用，可以一次性插入多条记录，并且可以输入表达式甚至是函数
但是无法进行子查询
2.insert插入记录
INSERT [INTO] tb1_name SET col_name={exprDEFAULT},……
说明：与第一种方式的区别在于，此方法可以使用子查询（SubQuery）;且只能一次性插入一条记录
eg：INSERT users SET username='Ben',password='456';
3、INSERT [INTO] table_name [(column_name,...)] SELECT...
此方法可以将查询结果插入到指定数据表中
31.更新记录
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={exp1|DEFAULT}[,col_name2=...][WHERE where_condition] 【一般来说要用WHERE指定位置，不然所有数据都会被更新】
例：
不指定位置：UPDATE users set age = age + 5,sex = 0; 使表里所有记录的age加5
指定位置： UPDATE users set age = age+ + 10 WHERE id % 2=0; 取id为偶数的位置
update更新数据
update <表名>
set <列名>=<表达式>，[,<列名>=<表达式>]...
[where <条件>];
32.删除记录:
1、删除记录DELETE：分为单表删除和多表删除
2、单表删除：DELETE FROM tbl_name [WHERE where_conditon]; 
3、若不添加WHERE则删除【全部记录】
删除后再插入，插入的id号从最大的往上加，而不是填补删除的。
33.查找记录:
查询表达式
每一个表达式表示想要的一列，必须至少有一个
多个列之间以英文逗号分隔
星号(*)表示所以列 tbl_name.*可以表示命名表的所有列
查询表达式可以使用[As]alias_name为其赋予别名
别名可用于GROUP BY，ORDRE BY或HAVING子句


SELECT 字段出现顺序影响结果集出现顺序，字段别名也影响结果集字段别名。
1、语法：
SELECT select_expr [,select expr2...] 只查找某一个函数或表达式
[
FROM table_references 查询表名
[WHERE where_conditon] 查询条件
[GROUP BY {col_name|position} [ASC|DESC],...] 按某个字段进行分组，相同的只显示第一个
[HAVING where_conditon] 分组时，给出显示条件
[ORDER BY {col_name|expr|position} [ASC|DESC],...] 排序
[LIMIT {[offset,]row_count|row_count OFFSET offset}] 限制返回数量
}
2、查询表达式的顺序会影响结果顺序
每个表达式表示想要的一列，必须有至少一个
多个列直接以英文逗号分隔
星号*表示所有列
3、使用tbl_name.col_name来表示列记录，这多表查询时可以区别开同名的列
4、使用[AS] alias_name为其赋予别名，别名可以用于GROUP BY、ORDER BY或HAVING子句，例如SELECT id AS userId,username AS uname FROM users; 这样查询出来的结果会用别名表示
34.查询结果分组：
GROUP BY语句对查询结果分组
查询结果分组，（多个分组用“，”隔开）；
[group by{col_name/posittion}[ASC（升序默认）/DESC（降序）]....]
SELECT*FROM users GROUP BY sex;
SELECT*FROM users GROUP BY 1;(这里的一表示查询的第一个字段，这里查询所有，第一个字段就是id，就就是会按照字段进行分组）
  分组条件:
利用GROUP BY 分组 添加分组条件 [HAVING where_condition]
条件要么为一个聚合函数，要么出现在SELECT 要查询的字段中。
eg：
SELECT sex FROM users GROUP BY 1 HAVING age > 35; //有错（原因是age没有出现在查询字段中）
SELECT sex, age FROM users GROUP BY 1 HAVING age > 35;//正确
SELECT sex FROM users GROUP BY 1 HAVING count(id) >= 2;
（这条指令，指按照sex分组，分成了两组sex=0和sex=NULL，条件id数大于2的留下显示，显然sex=0个数有8>2故留下，而sex=NULL个数为1，且count函数不计入NULL,不满足条件，故不显示。所以如果条件改成count(id)>=1,还是不会显示NULL分组。）
聚合函数永远只有一个返回结果，count为记录指定列的值的个数（NULL不计入）
对查询结果进行排序:
order by{col_name}
select * from users order by id desc; //对一个字段排序
select * from users order by age,id desc; //两个字段同时排序
desc是降序
1、对查询结果进行排序：[ORDER BY [col_name | expr | position } [ASC|DESC],...] 
Select * from user order by id desc;
2、可以同时按多条字段进行排序，规则是先按前面的字段排，在基础上再按后面字段排。
3、如：SELECT * FROM users ORDER BY age,id DESC; 先按照age排序，如果age有重复的，重复的字段里按id排序
限制返回查询结果的数量:
（1）SELECT * FROM users LIMIT 2; // 一个数字限制查询结果数量为 2 条
（2）SELECT * FROM users LIMIT 2,3 ;从第三个开始（第一个为0），返回三条。
（3）第三种insert，数据从一个表插入到另一个表：
INSERT test SELECT username FROM users WHERE age >=30; // 字段不匹配提示
INSERT test（username） SELECT username FROM users WHERE age >=30;
这是INSERT的第三种方法，与前两种相比，这种方法是使用了子选择，这样就相当于将A表中满足条件的数据导入了B表，无需手动写了
mysql分页要用到：
[LIMIT{[offset,]row_count|row_count OFFSET offset}]
35.在mysql客户端控制台以GDK的方式显示数据：SET NAMES gbk;不影响数据库中的真实数据