MySQL安装及基本命令

• Windows dos 下安装及启动mysql命令
  注册mysql服务
  mysqld -install
  启动mysql服务
  net start mysql
  登录mysql
  mysql -u root

• mysql 基本命令

  设置密码
  mysql -u root mysql> use mysql; mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root'; mysql> FLUSH PRIVILEGES;
  create database 数据库名 创建数据库；
  在对数据库操作前必须使用数据库；
  用 use 数据库名;

• MySQL登录与退出
  登录Mysql：“输入mysql -u帐号 -p密码 这是登陆
  mysql退出：mysql > exit;
  以下是实例参考下：
  mysql退出三种方法：
  mysql > exit;
  mysql > quit;
  mysql > \q;

登入:mysql -u root -p/mysql -h localhost -u root -p databaseName;
列出数据库：show databases;
选择数据库：use databaseName;
列出表格：show tables；
列出创建表的sql语句:show create table tableName
显示表格列的属性：show columns from tableName；
建立数据库：source fileName.txt;
匹配字符：可以用通配符_代表任何一个字符，％代表任何字符串;
增加一个字段：alter table tabelName add column fieldName dateType;
增加多个字段：alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType;
多行命令输入:注意不能将单词断开;当插入或更改数据时，不能将字段的字符串展开到多行里，否则硬回车将被储存到数据中;
增加一个管理员帐户：grant all on *.* to user@localhost identified by "password";
每条语句输入完毕后要在末尾填加分号';'，或者填加'\g'也可以；
查询时间：select now();
查询当前用户：select user();
查询数据库版本：select version();
查询当前使用的数据库：select database();

1、删除student_course数据库中的students数据表：rm -f student_course/students.*;

2、备份数据库：(将数据库test备份)
mysqldump -u root -p test>c:\test.txt
备份表格：(备份test数据库下的mytable表格)
mysqldump -u root -p test mytable>c:\test.txt
将备份数据导入到数据库：(导回test数据库)
mysql -u root -p test

3、创建临时表：(建立临时表zengchao)
create temporary table zengchao(name varchar(10));

4、创建表是先判断表是否存在
create table if not exists students(……);

5、从已经有的表中复制表的结构
create table table2 select * from table1 where 1<>1;

6、复制表
create table table2 select * from table1;

7、对表重新命名
alter table table1 rename as table2;

8、修改列的类型
alter table table1 modify id int unsigned;//修改列id的类型为int unsigned
alter table table1 change id sid int unsigned;//修改列id的名字为sid，而且把属性修改为int unsigned

9、创建索引
alter table table1 add index ind_id (id);
create index ind_id on table1 (id);
create unique index ind_id on table1 (id);//建立唯一性索引

10、删除索引
drop index idx_id on table1;
alter table table1 drop index ind_id;

11、联合字符或者多个列(将列id与":"和列name和"="连接)
select concat(id,':',name,'=') from students;

12、limit(选出10到20条)<第一个记录集的编号是0>
select * from students order by id limit 9,10;

13、MySQL不支持的功能
事务，视图，外键和引用完整性，存储过程和触发器

14、MySQL会使用索引的操作符号
<,<=,>=,>,=,between,in,不带%或者_开头的like

15、使用索引的缺点
1)减慢增删改数据的速度；
2）占用磁盘空间；
3）增加查询优化器的负担；
当查询优化器生成执行计划时，会考虑索引，太多的索引会给查询优化器增加工作量，导致无法选择最优的查询方案；

16、分析索引效率
方法：在一般的SQL语句前加上explain；
分析结果的含义：被缓存。

17、mysql远程登录
mysql> use mysql
mysql> update user set host='%' where user ='root';
mysql>flush privileges;

SQL语言基础

MySQL基本数据类型

数值型

整数类型	字节	范围（有符号）	范围（无符号）	用途
TINYINT	1字节	(-128，127)	(0，255)	小整数值
SMALLINT	2字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4字节	(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8字节	(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

字符串型

字符串类型	字节大小	描述及存储需求
CHAR	0-255字节	定长字符串
VARCHAR	0-255字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65535字节	二进制形式的长文本数据
TEXT	0-65535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEX	T 0-16 777 215字节	中等长度文本数据
LOGNGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据
VARBINARY(M)	允许长度0-M个字节的定长字节符串，值的长度+1个字节	可变长度的字符
BINARY(M)	M	允许长度0-M个字节的定长字节符串

位串型

bit
bit varying 

时间型

|类型 | 大小(字节) | 范围| 格式 | 用途|
|:--|:--|:--|:--|
|DATE | 4 | 1000-01-01/9999-12-31| YYYY-MM-DD | 日期值 |
| TIME | 3 |'-838:59:59'/'838:59:59' |HH:MM:SS |时间值或持续时间 |
|YEAR |1| 1901/2155 | YYYY | 年份值|
|DATETIME| 8| 1000-01-01 00:00:00/9999-12-31 23:59:59| YYYY-MM-DD HH:MM:SS| 混合日期和时间值 |
| TIMESTAMP |4 |1970-01-01 00:00:00/2037 年某时 |YYYYMMDD HHMMSS |混合日期和时间值，时间戳

复合型

set
enum

创建模式

create schema<模式名> authorization 用户名

create schema ST_CO authorization tyq

*drop schema ST_CO [cascade|restrict] *
cascade（级联式） 执行drop语句时 把sql模式及其下属的基本表，视图，索引等所有元素全部撤销
restrict（约束式） 在执行drop语句是 只有当sql模式中没有任何下属元素是 才能撤销sql模式 否则拒绝执行

创建一个名为s的表

not null指定不能为空值

create table s (
s#  integer(4) not null,
name varchar(8) not null,
age integer(1) not null,
primary key(s#));/*指定主键*/
/*foreign key(s#) references T(T#)*/ /*创建外键约束*/

alter table <基本表> add <列名><类型> 增加列 [after|first]
例：在基本表S中增加 列

alter table s add address varchar(30) ;

alter table <基本表> drop <列名> [cascade|restrict]删除列
例：删除S中的age列

alter table s drop age cascade;

修改字段名称

alter table s change name sname varchar(120) not null;

基本表的撤销
drop table <表名> [cascade|restrict]
例：删除基本表s

drop table s restrict

数据库查询语句
select语句基本用法
select A1,...,An;
from r1,...r
where F

• F表示表达式*

  1.算术比较运算符 <,<=,>,>=,=...
  2.逻辑运算符 AND,OR,NOT
  3.集合成员资格运算符 IN ,NOT IN;
  4.谓词 EXISTS（存在） ALL SOME UNIQUE
  5.聚合函数 AVG(平均值) MIN（最小值），MAX（最大值） SUM（和） COUNT（计数）

  教师关系 T(T#,TNAME,TITLE)
  课程关系 C(C#,CNAME,T#)
  学生关系 S(S#,SNAME,AGE,SEX)
  选课关系 SC(S#,C#,SCORE)

数据的插入

1.insert into <基本表名>[(列名序列)] values(<元组值>)
2.insert into <基本表名>
*列如 *
1.往表s中插入一个元组
（s36,GU,20,M）;

insert into s(s#,sname,age,sex)
values('S36','GU',20,'M');

2.往表sc插入一个选课元组(S5,C8)其中成绩为空值

insert into sc(s#,c#) values('s5','c8');

3.往sc连续插入3个元组，可用下列语句实现

insert into sc values('s4','c4',85),('s3','c6',90),('s7','c2',70);

4.在基本表sc中，把平均成绩大于80分的男学生的学号和平均成绩存入另一个已存在的基本表s_score (s#,AVG_SCORE),可用下列语句实现

insert into s_score(s#,AVG_SCORE)
from sc
where s# in (select s# from s where sex='M')
group by s# --group by{col_name|position}[asc|desc];asc升序 desc降序
having avg(score)>80;--分组条件
order by {col_name|expr|position}[ASC|DESC] --对查询结果进行排序

5.某一个班级的选课情况已在基本表sc4(s#,c#)中 把sc4的数据
插入到表sc中

insert into sc(s#,c#)
table sc4;
select s#,AVG(SCORE)

选择范围

limit 2--指定返回2条记录的数量 
limit 2,2 --指定从第二个开始，返回2条【从0开始】

*数据的删除 *
基本语法 delete from<基本表名> where <条件表达式>
例：把课程名为MATHS的成绩从基本表sc中删除

delete from sc where c# in(select c# from c where cname ='MATHS');

数据的修改
基本语法 update <基本表名> set ... where...
例如对基本表sc和c中的值进行修改
1.把C5课程的课程名改为DB

update c set cname='DB' where c#='c5';

2.把女同学的成绩提高10%

update sc set score=score*1.1
where s# in(select s# from s where sex='F');
*auto_increment unique key *

配置文件

mysql my.ini

#For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# DO NOT EDIT THIS FILE. It's a template which will be copied to the
# default location during install, and will be replaced if you
# upgrade to a newer version of MySQL.

[mysqld]
#设置编码
loose-default-character-set=gbk
character-set-server=gbk
innodb_buffer_pool_size=128M
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
#mysql安装目录
# basedir = mysqldir
# datadir =mysqldir\data
#端口配置
# port = 3306
# server_id = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[client]
loose-default-character-set=gbk
[WinMySQLadmin]
#mysql服务器
Server=mysqldir\bin\mysqld.exe