sql入门

###################################################################

1.数据库介绍

 * 数据库（DataBase，DB），数据库用于存放数据的文件系统

 * 数据库软件系统（DBMS）

 * 关系：用二维表描述实体与实体之间联系

 * 记录：元组，二维表中一个一行数据

 * 字段：列，属性，域，二维表中一个一列

 * 注意事项：

  * 列名必须唯一

  * 列没有顺序

  * 行没有顺序

2.关系数据库

 * Oracle，oracle公司，数据库的性能非常好。收费

 * DB2，IBM公司，性能不错。收费

 * Sql server ，微软 IIS

 * Mysql，Oracle公司。mysql升级，性能优化

 

3.Mysql的安装

 * 安装指定“数据库文件路径” ： %mysql%/data

 * mysql数据的utf-8编码为：utf8

 * mysql的默认端口：3306

 * mysql的超级管理员：root ，安装只需要给root设置密码

 * %mysql%/my.ini文件

  * [mysql]

   * default-character-set=utf8 ，默认编码

   * datadir="C:/java/MySQL/MySQL Server 5.5/Data/" ，数据库文件路径

4.mysql的登录

 * 前提，必须启动mysql服务

 * windows启动服务：“运行” services.msc

 * 查询mysql的命令参数：mysql -? 或 mysql --help

  * 设置用户名

   * 格式1：-u用户名

   * 格式2：--user=用户名

  * 设置密码：

   * 格式1：-p密码

   * 格式2：--password=密码

  * 设置主机

   * 格式1：-hIP

   * 格式2：--host=IP地址

  * 设置端口号：

   * 格式1：-P端口

   * 格式2：--port=端口

 * 登录格式：

  * 指定书写密码：mysql -uroot -p1234 回车登录

  * 先登录在书写密码：mysql -uroot -p 回车，输入密码回车，登录

 

5.常用命令

 * 显示所有的数据库：mysql > show databases;

  * 创建数据库将%mysql%/data创建一个文件夹

  * 默认数据库

   * mysql，mysql数据库的核心数据库，【不能删除】

   * performance_schema，mysql提供记录数据的数据库

   * information_schema，视图，虚拟表

   * test，空数据库

 * 使用数据库：mysql > use 数据库名称;

  * 对数据库操作之前必须使用

 * 查询当前数据库中具有的表：mysql > show tables;

 * 确定当前数据库： mysql > select database();

 * 查询表结构：mysql > desc biaomingc:

  * describe

 * 查询数据库支持的字符集：mysql > show character set;

 * 显示当前系统的环境变量，并且只显示编码：mysql > show variables like '%char%';

6.用户管理

 * 创建用户

  * mysql > create user 用户名称 [identified by '密码']

   * 用户名称的格式：'名称'@'主机' ，对应user表，'mysql.user'@'mysql.host'

   * 主机可以不指定，默认是%，表示所有人都可以访问

  * 创建用户，就是给mysql数据库的user表添加记录

  * 查询用户表的部分信息

   * mysql > select host,user,password from user;

  * 例如：

   * 添加itcast用户

   * mysql > create user 'itcast'@'192.168.1.100' identified by '1234';

 * 修改用户密码

  * 登录之后，使用set password进行修改

  * mysql > set password for 用户名称 = password('1234');

 * 删除用户l

  * 格式：drop user 用户名称

  * mysql > drop user 'itcast'@'192.168.1.100';

 * 设置权限

  * 授权格式：grant 权限 on 数据库.表 用户名称 with grant option;

   * 权限：all表示所有的权限

   * 数据库.表：*.*所有数据库的所有表；mysql.*，mysql数据库的所有表；mysql.user，mysql数据库的user表

   * with grant option：表示当前用户可以将自己所具有的权限，再次授权给其他人。

  * 例如：grant all on *.* to 'demo'@'%' with grant option;

  * 取消权限：revoke all privileges ,grant option from 用户名称;

 * 思考：如果mysql，root用户的密码忘记？

  * 关闭mysql服务器（mysqld.ext结束）

  * 使用命令启动mysql服务器

   * cmd > mysqld --console --skip-grant-tables ,可以不使用密码登录，之后将可以设置密码

   * 注意：如果set password不能设置，可以使用update

    * update user set password = password("1234") where user="root" and host="%"

7.mysql异常

 * 服务器没有启动

  * ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

 * 没有使用数据库，及当前数据库为null

  * ERROR 1046 (3D000): No database selected

  * 解决办法：mysql > use 数据库名;

 * 没有权限

  * ERROR 1044 (42000): Access denied for user 'demo'@'%' to database 'mysql'

 * 启动mysql服务：net start mysql

  * 发生系统错误 3。

  * 系统找不到指定的路径。

  * 原因：没有在添加服务时，将目录切换到bin目录下

 * win7系统是net无法启动

  * 发生系统错误 5。

  * 解决办法：使用超级管理员运行cmd（cmd右键）

8.绿色版mysql的安装

 * 解压mysql-5.5.27-win32.zip

 * 复制my-small.ini，并将名称修改为my.ini

 * 通过命令启动：mysqld --console

  * --console 控制台，将启动信息打印

  * 脚本位置：%mysql%/bin/mysqld.exe

 * 需要配置系统环境变量path，任意位置都可以使用脚本

 * 将mysql配置成windows系统服务

  * 注意：如果要配置系统服务，必须将命令行的目录切换到%mysql%/bin

  * 注册服务：cmd > mysqld --install

  * 移除服务：cmd > mysqld --remove

 

 * 启动已经成为系统服务服务项(操作系统的命令)

  * 启动服务：cmd > net start mysql

  * 停止服务：cmd > net stop mysql

 

9.sql介绍

 * sql ： SQL被称为结构化查询语言(structured query language）

 * SQL分类

  * DDL，数据定义语言。（结构）

   * 创建：create

   * 修改：alter

   * 删除：drop

  * DML，数据操纵语言（内容）

   * 录入：insert

   * 修改：update

   * 删除：delete

  * DQL，数据查询语言

   * 查询：select

   

### 数据库中存放表，表中存放的数据

 

10.DDL--数据定义语言

 * 数据库（itcast）

  * 创建1：mysql > create database 数据库名称;

  * 创建2：mysql > create database 数据库名称 character set 字符集;

  * 删除: mysql > drop database 数据库名称;

  * 注意：

   * 在创建的数据库目录中db.opt文件

    * default-character-set=latin1 ，数据库使用的默认编码为ISO-8859-1，所以不支持中文

 * 表（users）

  * 注意：先使用数据库

  * 创建：mysql > create table 表名(字段名称1 字段类型1,字段名称2 字段类型2,...);

   * 在帮助文档中可以“data types”

   * 创建user表

    create table users(

     id varchar(32),

     username varchar(50),

     age int ,

     count double(4,1)

    );

  * 修改表名：alter table 表名 rename 新表名；

   * 将表名修改成users

    * mysql > alter table user rename users;

  * 删除表: drop table 表名

 

 * 字段（id，firstname,secondname，age，count）

  * 添加 ： alter table 表名 add [column] 字段名称 字段类型;

   * users表添加一个firstname字段

  * 修改:alter table 表名 change 旧字段名称 字段名称 字段类型;

   * 将字段username重新命名为secondname

  * 删除：alter table 表名 drop [column] 字段名称

 * 类型

  * 字符：（java String）

    * char(n),定长字符

    * varchar(n)，变长字符

    * 例如：fengjie --> char(10) 将原有数据，再加3个空格 ； varchar(10) 具体数据的长度

  * 数字

   * int，表示整型

   * double(m,n),表示双精度，m表示所有数字的个数，n表示小数位数。例如：double(5,2)， 最大值999.99

   * decimal，表示所有的数字

 * 约束??

 

11.DML,数据操纵语言

 * 插入数据：insert into 表名[(字段名称1,字段名称2,...) ] values(值1，值2，。。。);

  * 给users表添加一条记录：u001 张 飞 28 97

   * mysql > insert into users values('u001','飞',28,97,'张');

   * mysql > insert into users(id,firstname,secondname,age,count) values('u002','张','飞2',85,100);

   * mysql > insert into users(id) values('u003');

  * 注意：

   * 如果使用字符串，数据必须使用引号括起来。建议使用单引号

   * 字段与值必须匹配

   * 如果没有指定字段，则字段将使用默认值，默认值null ,不是"null"

 * 修改数据: update 表名 set 字段名称 = 值 , 字段名称2 = 值2 , ... [ where 条件 ]

  * 将u003成绩修改成20

   * mysql > update users set count = 20; # 将所有的程序修改成功20

   * mysql > update users set count = 20 where id = 'u003';

 * 删除数据: delete from 表名 [where 条件]

  * 删除u003

   * mysql > delete from users where id = 'u003';

  * 注意：不要随意执行 delete from 表名;

 

12.DQL--数据查询语言

 * 准备数据

  * insert into `users`(`id`,`firstname`,`age`,`secondname`,`count`) values ('u001','张',18,'飞',60),('u002','赵',20,'云',58),('u003','关',22,'羽',80),('u004','刘',25,'备',98),('u006','黄',18,'盖',NULL),('u005','王',12,'子云',20),('u007','诸葛',24,'亮',100);

 * 使用帮助文档搜索：lock

 * 格式：select 字段1,字段2,... from 表名 [where 条件]

 * 没有条件查询

  * 查询所有

   * mysql > select * from users;

   * mysql > select id,firstname,secondname,age,count from users;

  * 查询部分信息

   * mysql > select firstname,secondname from users;

  * 查询用户编号、姓名，及格(60差值)

   * mysql > select id,firstname,secondname ,count from users;

   * mysql > select id,firstname,secondname ,count - 60 from users;

   * mysql> select id,concat(firstname,secondname) ,count - 60 from users;

  * 修改上面查询显示字段名称，用"姓名"表示姓名，用"及格"表示及格

   * 别名：字段 [as] 别名

   * mysql> select id,concat(firstname,secondname) as 姓名 ,count - 60 及格 from users;

   * mysql> select id,concat(firstname,secondname) as '姓 名' ,count - 60 '及 格' from users;

   * 注意：别名可以不使用引号，但如果别名之间存在空格则必须使用引号

 

 * 带有条件查询

  * 查询分数等于60的学生

   * mysql > select * from users where count = 60;

  * 查询姓"张"学生

   * mysql > select * from users where firstname = '张';

  * 查询年龄大于18的学生

   * mysql > select * from users where age > 18;

  * 显示分数在60-80的学生 [60,80]

   * mysql > select * from users where count >= 60 and count <= 80;

   * mysql > select * from users where count between 60 and 80;

  * 查询编号为u001和u002的学生

   * mysql > select * from users where id = 'u001' or id = 'u002';

   * mysql > select * from users where id in ('u001','u002');

  * 查询年龄是18或20的学生

   * mysql > select * from users where age = 18 or age = 20;

  * 查询名中含有"云"的学生

   * mysql > select * from users where secondname like '%云%';

   * %表示，某一边字符个数不限定

  * 查询名中第二字还有"云"的学生

   * mysql > select * from users where secondname like '_云%';

   * _表示，一个任意字符

  # 添加数据：mysql> update users set secondname='备备云' where id = 'u004';

  * 查询名中第三字还有"云"的学生

   * mysql > select * from users where secondname like '__云%';

  # 添加数据：mysql> update users set secondname ='%' where id = 'u007';

  * 查询包含%特殊符号

   * mysql> select * from users where secondname like '%\%%';

   * 注意：可以使用转义字符\

  * 查询分数小于60 或 大于90分的学生

   * mysql> select * from users where count < 60 or count > 90;

  * 查询分数等于60 或者 分数大于90并且年龄大于23

   * mysql> select * from users where count = 60 or count > 90 and age > 23;

   * mysql> select * from users where count = 60 or ( count > 90 and age > 23 ); #建议使用

   * 总结：运算符的优先级， 条件运算符 > and > or

  * 查询没有考试的学生

   * mysql> select * from users where count is null;

 * 聚合函数的使用

  * 有多少条记录

   * mysql > select count(*) from users;

   * mysql > select count(count) from users; # 不计算null值

   * mysql > select count(1) from users; # 推荐

  * 平均成绩

   * mysql > select avg(count) from users;

   * 注意：

    * avg中使用的数字字段

    * 不计算null值

   * 数据准确：mysql > select sum(count)/count(1) from users;

  * 最高成绩

   * mysql > select max(count) from users;

  * 最小年龄

   * mysql > select min(age) from users;

  * 班级总成绩

   * mysql > select sum(count) from users;

  * 查询所有的年龄数

   * mysql> select age from users order by age asc ;

    * order by用于排序：格式：order by 字段 asc | desc

   * mysql> select distinct age from users order by age asc ;

.

 * 分组

  * 添加班级字段（classes）

   * mysql> alter table users add column classes varchar(3);

  # 添加数据

   * update users set classes = '1' where id in ('u001','u002','u003','u004');

   * update users set classes = '2' where id in ('u005','u006','u007');

  * 查询1班和2班的平均成绩

   * 平均成绩：select sum(count)/count(1) from users;

   * group by进行分组，格式：group by 字段

   * mysql > select sum(count)/count(1) from users group by classes;

   * mysql > select sum(count)/count(1) , classes from users group by classes;

   * mysql > select sum(count)/count(1) as '平均成绩' , classes as '班级' from users group by classes;

   * 错误数据：mysql > select sum(count)/count(1) as '平均成绩' , classes as '班级' ,id from users group by classes;

   * 注意：

    * 如果使用分组，在select查询的字段中，只能使用分组的字段和聚合函数

  * 查询班级的平均成绩为不及格的

  select sum(count)/count(1) as avg ,classes from users group by classes having avg <=60

   * 分组的条件需要使用having

   * mysql > select sum(count)/count(1) , classes from users group by classes having sum(count)/count(1) <=60 ;

   * mysql > select sum(count)/count(1) as avg , classes from users group by classes having avg <=60 ;

   * 如果不是having

    * 错误数据：mysql > select sum(count)/count(1) <= 60 , classes from users group by classes ;

     +---------------------------+---------+

     | sum(count)/count(1) <= 60 | classes |

     +---------------------------+---------+

     | 0 | 1 |

     | 1 | 2 |

     +---------------------------+---------+

    * 所有的平均成绩：select sum(count)/count(1) as avg , classes from users group by classes;

    * 虚拟的查询结果：select * from demo where avg < 60;

    * 合并

     select * from (select sum(count)/count(1) as avg , classes from users group by classes) as demo where demo.avg < 60;

13.中文处理

 * 1.创建数据库时，指定编号：mysql > create database itcast3 character set UTF8;

 * use itcast3；

 # 准备表： create table demo(name varchar(30));

 # 准备数据：insert into demo values('凤姐');

 * 2.临时设置编码：mysql > set names gbk;

 * 总结

  * 查询 mysql> show variables like '%char%';

  * 1.数据库必须支持中文 ，character_set_database | utf8

  * 2.三个编码统一，并且需实际客户端的编码相同（cmd默认编码为gbk）

   character_set_client | gbk

   character_set_connection | gbk

   character_set_results | gbk

操作步骤

 * 创建数据库（支持中文）

 * 创建表

 * 录入数据

 * 查询