图片 38

MySQL常用命令(一)

(1)库的基础操作

1.user表
        如果需要从其他机器连接 mysql 服务器
报这个错“ERROR 1130: Host ‘root’ is not allowed to connect to this MySQL
server”
我们需要为连接用户设置权限

MySQL常用命令:

启动MySQL:net start mysql
停止MySQL:net stop mysql
登录MySQL:mysql -h hostname -u username -p 或者mysql -u username
-ppassword
退出MySQL:exit 或者 quit

数据库:database 表:table

注:– 双中划线 + 空格:注释(单行注释),也可以使用#号

  • 查看已有库: show databases;
  • 创建库(制定默认字符集): ccreate database 库名 default
    charset=utf8;
  • 查看创建库的语句: show create database 库名;
  • 查看当前所在库: select database();
  • 切换库: use 库名;
  • 查看库中已有表: show tables;
  • 删除库: drop database 库名;

在mysql库 user表中

数据库的基本操作:

— 库操作

(2)表的管理

图片 1

创建和查看数据库:

创建数据库:create database 数据库名称;
查看所有数据库:show databases;
查看某个已经创建的数据库信息:show create database 数据库名称;
修改数据库编码:alter database 数据库名称 default character set
编码方式;
删除数据库:drop database 数据库名称;

(一)创建数据库

  • 创建表(制定字符集)

 【1】Host——对应的允许访问域(%表示全域,192.168.%.%表示对应账号对该段均可访问,
           127.0.0.1表示对应账号仅这台机器可访问)
【2】User——用户名
【3】Password——密码加密串
【29】xxx_priv——各种权限 

数据表的基本操作:

注意事项:操作数据表之前,需要使用”use 数据库名”指定操作哪个数据库。

创建数据表:
<code>
Create table 表名
(
字段名1,数据类型 [完整性约束条件],
字段名2,数据类型 [完整性约束条件],

字段名n,数据类型 [完整性约束条件],
);
</code>
查看数据表:
Show create table 表名;
<code>使用describe语句查看数据表:describe 表名;或者 desc
表名;</code>
修改数据表:
<code>修改表名:alter table 旧表名 rename [to] 新表名;
修改字段名:alter table 表名 change 旧字段名 新字段名 新数据类型;
即使新字段与旧字段数据类型相同,也不能省略,必须重写。
修改字段数据类型:alter table 表名 modify 字段名 数据类型;
添加字段:alter table 表名 add 新字段名 数据类型 [约束条件]
[first|after 已存在字段名];
删除字段:alter table 表名 drop 字段名;
修改字段的排列位置:
alter table 表名 modify 字段名1 数据类型 first|after 字段名2;
删除数据表:drop table 表名;
</code>

例:mysql> create database mystudent charset
uft8;

    create table tablename(

2.mysql默认库
一个刚建好的mysql数据库 一般有一下几个库
# show database;
‘information_schema’
‘mysql’
‘performance_schema’
‘test’

表的约束:

主键约束:primary key;
外键约束:foreign key;
非空约束:not null;
唯一性约束:unique;
默认值约束,用于设置字段的默认值:default;

  1. 主键约束:
    单字段主键:字段名 数据类型 primary key
    多字段主键:primary key(字段名1,字段名2,…,字段名n)
  2. 非空约束:字段名 数据类型 not null
  3. 唯一约束:字段名 数据类型 unique
  4. 默认约束:字段名 数据类型 default 默认值

#SQL语句报错:只会告诉用户报错的大概位置不会说明原因(静默模式)

    字段名 数据类型,

information_schema 库:
information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权限等。再简单点,这台MySQL服务器上,到底有哪些数据库、各个数据库有哪些表,每张表的字段类型是什么,各个数据库要什么权限才能访问,等等信息都保存在information_schema表里面。

设置表的字段值自动增加:

字段名 数据类型 auto_increment
默认情况下,该字段的值是从1开始自增的。
索引:
创建索引:
创建表的时候创建索引:
<code>
create table 表名(
字段名 数据类型 [完整性约束条件],
字段名 数据类型 [完整性约束条件],

字段名 数据类型 [完整性约束条件],
[unique|fulltext|spatial] index|key [别名] (字段名 [(长度)])
[asc|desc]
);
</code>

查看索引是否被使用:explain select * from 表名 where 字段名=值 g

  1. 使用create index 语句在已经存在的表上创建索引
    <code>create [unique|fulltext|spatial] index 索引名 on
    表名(字段名 [(长度)]) [asc|desc]
    </code>
  2. 使用alter table 语句在已经存在的表上创建索引:
    <code>
    Alter table 表名 add [unique|fulltext|spatial] index 索引名(字段名
    [(长度)]) [asc|desc]
    </code>
    删除索引:
  3. 使用alter table 删除索引:
    Alter table 表名 drop index 索引名;
  4. 使用drop index 删除索引:
    Drop index 索引名 on 表名;

添加,更新与删除数据:
添加数据:

  1. 为表中所有字段添加数据:
    A.insert语句中指定所有字段名
    Insert into 表名 (字段名1,字段名2,…)
    Values (值1,值2,…);
    B.insert语句中不指定字段名
    Insert into 表名 values(值1,值2,…)
    注意事项:添加的值得顺序必须和字段在表中的定义的顺序相同
  2. 为表的指定字段添加数据:
    Insert into 表名 (字段名1,字段名2,…)
    Values(值1,值2,…);
    多学一招:insert语句的其它写法:
    Insert into 表名
    Set 字段名1=值1[,字段名2=值2,…];
  3. 同时添加多条记录:
    <code>
    Insert into 表名 [(字段名1,字段名2,…)]
    Values (值1,值2,…),
    (值1,值2,…),

    (值1,值2,…);
    </code>

图片 2

    字段名 数据类型,

mysql 库:
mysql数据库是mysql的参数数据库,这个是mysql的核心数据库,类似于sql
server中的master表,主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。不可以删除

更新数据:
  1. 更新部分数据
  2. 更新全部数据(不使用where语句)
    <code>
    Update 表名
    Set 字段名1=值1[,字段名2=值2]
    [where 条件表达式];
    </code>

例:mysql> create database `databases`
charset utf8;

    … …

performance_schema 库:
5.5 版本起 新增了一个性能优化的引擎
PERFORMANCE_SCHEMA。主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表,这个功能默认是关闭的:
需要设置参数: performance_schema 
才可以启动该功能,这个参数是静态参数,只能写在my.cnf 中
不能动态修改。
Performance的开启很简单,在my.cnf中[mysqld]加入performanc_schema,检查性能数据库是否启动的命令:
SHOW VARIABLES LIKE ‘performance_schema’;
若是返回的 值为ON,则说明性能数据库正常开启状态。

删除数据:
  1. 删除部分数据
  2. 删除全部数据(不使用where语句)
    Delete from 表名 [where 条件表达式];
    多学一招:
    使用关键字truncate(截断)删除表中数据:
    Truncate [table] 表名;
    总结:delete删除部分数据,truncate删除全部数据。

图片 3

    );

 

单表查询:

Select语句:
<code>
Select [distinct] *|{字段名1,字段名2,字段名3,…}
From 表名
[where 条件表达式1]
[group by 字段名 [having 条件表达式2]]
[order by 字段名 [asc|desc]]
[limit [offset] 记录数];
</code>

#数据库名称不能使用关键字如果非要使用需前后加上符号 ‘ (esc 键下面的键再英文状态下输出)

  • 查看创建表的语句(字符集)

test 库:
做测试使用的。

简单查询:

查询所有字段:

  1. 在select语句中指定所有字段:
    Select 字段名1,字段名2,… From 表名;
  2. 在select语句中使用“*”通配符代替所有字段
    Select * from 表名;

查询指定字段:
<code>Select 字段名1,字段名2,…from 表名;</code>

1.创建中文数据库

    show create table tablename;

 

按条件查询:
  1. 带关系运算符的查询
    <code>
    Select *|字段名1,字段名2,…
    From 表名
    Where 条件表达式;
    </code>
  2. 带in关键字的查询:判断某个字段的值是否在指定的集合中
    <code>
    Select *|字段名1,字段名2,…
    From 表名
    Where 字段名 [not] in (元素1,元素2,…);
    </code>
  3. 带between and 关键字的查询:判断某个字段的值是否在指定的范围内
    <code>
    Select *|字段名1,字段名2,…
    From 表名
    Where 字段名 [not] between 值1 and 值2;
    </code>
  4. 空值查询
    <code>
    Select *|字段名1,字段名2,…
    From 表名
    Where 字段名 is [not] null;
    </code>
  5. 带distinct关键字的查询
    <code>
    Select distinct *|字段名1,字段名2,…]
    From 表名;
    </code>
  6. 带like关键字的查询
    a) 百分号(%)通配符:匹配任意长度的字符串
    b) 下划线()通配符:匹配单个字符
    <code>
    Select
    |字段名1,字段名2,…
    From 表名
    Where 字段名 [not] like ‘匹配字符串’;
    </code>
    如果需要匹配%或者*,则使用转义字符’’,如:% 或者 _
  7. 带and关键字的多条件查询
    <code>
    Select *|字段名1,字段名2,…
    From 表名
    Where 条件表达式1 and 条件表达式2 […and 条件表达式3];
    </code>
  8. 带or关键字的多条件查询
    <code>
    Select *|字段名1,字段名2,…
    From 表名
    Where 条件表达式1 or 条件表达式2 […or 条件表达式3];
    </code>
  9. or和and关键字一起使用的情况:
    And的优先级高于or,因此当两者一起使用时,应该先运算and两边的条件表达式,再运算or两边的条件表达式。

例:mysql> set names gbk;

  • 查看表结构

3..显示查看命令
查看mysql的当前登陆用户 
select user();

例:mysql> create database 中文 charset utf8;

    desc tablename;

查看当前版本
select  @@version ;

图片 4

  • 删除表

1、显示当前数据库服务器中的数据库列表:

#MySQL也能使用中文数据库.但需告诉服务器当前中文的字符集(不建议使用)

    drop table tablename;

mysql> SHOW DATABASES;

#创建数据库成功会再daya目录下创建与数据库对应名字的文件夹

(3)表记录管理

注意:mysql库里面有MYSQL的系统信息,我们改密码和新增用户,实际上就是用这个库进行操作。

(二)查看数据库

  • 在表中插入记录

2、显示数据库中的数据表:

1.查看所有数据库

    insert into tablename values(值1),(值2),…,(值N);

mysql> USE 库名;
mysql> SHOW TABLES;

mysql> show
databases;

  • 查询表记录

3、显示数据表的结构:

图片 5

    select * from tablename;

mysql> DESCRIBE 表名;

2.查看指定部分的数据库

    select 字段名1,字段名2,…,字段名N from tablename;

4、显示当前时间
mysql>select now();

mysql> show database like [数据库名];

(4)表字段的操作

5、显示年月日
SELECT DAYOFMONTH(CURRENT_DATE);
+————————–+
| dayofmonth(current_date) |
+————————–+
|                       24 |
+————————–+
1 row in set (0.02 sec)

#
%:表示匹配多个字符 # _:表示匹配单个字符

    语法:

 SELECT MONTH(CURRENT_DATE);
+———————+
| MONTH(CURRENT_DATE) |
+———————+
| 9 |
+———————+
1 row in set (0.00 sec)

3.查看数据库的创建语句

      alter table tablename 执行动作;

SELECT YEAR(CURRENT_DATE); +——————–+
| YEAR(CURRENT_DATE) |
+——————–+
| 2009 |
+——————–+
1 row in set (0.00 sec)

mysql> show create database
[数据库名]; #如果涉及关键字或中文需加反引号

  • MySQL常用命令(一)。添加字段
    •   alter table tablename add 字段名 数据类型 first | after
      字段名;
  • 删除字段
    •   alter table tablename drop 字段名;
  • 修改字段数据类型
    •   alter table tablename modify 字段名 新数据类型;

 

(三)
更新数据库

      注: 修改数据类型时,会受到表中原有数据的限制!

  1. 当计算器用
    select ((4 * 4) / 10 ) + 25;
    +———————-+
    | ((4 * 4) / 10 ) + 25 |
    +———————-+
    | 26.60 |
    +———————-+
    1 row in set (0.00 sec)

#数据库的名字不可以修改,数据库的修改仅限库选项:字符集和校队集

  • 修改字段名
    •   alter table tablename change 旧名 新名 数据类型;
  • 修改表明
    •   alter table tablename rename 新表明;

 

mysql> alter database [数据库名] charset utf8;

(5)表记录的管理

 显示字符串
mysql> SELECT “welecome to my blog!”;
+———————-+
| welecome to my blog! |
+———————-+
| welecome to my blog! |
+———————-+
1 row in set (0.00 sec)

(四)
删除数据库

  1. 删除表记录
    1.   delete from tablename where 条件;
  2. 更新表记录
    1.   update tablename set 字段名1=值1, 字段名2=值2,… where
      条件;

串接字符串
select CONCAT(f_name, ” “, l_name) AS Name
from employee_data
where title = ‘Marketing Executive’;
+—————+
| Name |
+—————+
| Monica Sehgal |
| Hal Simlai |
| Joseph Irvine |
+—————+
3 rows in set (0.00 sec)
注意:这里用到CONCAT()函数,用来把字符串串接起来。另外,我们还用到以前学到的AS给结果列’CONCAT(f_name,
” “, l_name)’起了个假名。

mysql> drop database [数据库名];

(6)一些特殊的匹配方式

 

#删除操作后会将数据库名字对应的文件夹一并删除(级联删除:里面的数据表全部删除)删除数据库是不可逆的,删除需谨慎

  1. 模糊匹配
    1.   where 字段名 like 表达式;
    2.   表达式
      1.   _   :  匹配单个字符
      2.   %  : 匹配 0 到多个字符
  2. 正则匹配 regexp
    1.   where 字段名 regexp “正则表达式”;
    2.   正则表达式符号
      1.   ^ : 以…开头
      2.   $: 以…结尾
      3.   . : 匹配任意一个字符
      4.   []: 包含…内容
        1.         [0-9]:匹配带数字的
        2.         [a-z]:匹配带小写字母的
        3.         [A-Z]
      5.   * : 星号前面的字符出现 0 次或者多次

 

— 表操作

 

(一)**创建表**

 

例:mysql>use [数据库名]

可以把 SQL 分为两个部分:

数据定义语言(DDL),例如:CREATE、DROP、ALTER等语句。

数据操作语言(DML),例如:INSERT(插入)、UPDATE(修改)、DELETE(删除)语句。

数据查询语言(DQL),例如:SELECT语句。(一般不会单独归于一类,因为只有一个语句)。
※ 数据控制语言(DCL),例如:GRANT、REVOKE等语句。
※ 事务控制语句(TCL),例如:COMMIT、ROLLBACK等语句

mysql> create table [if not exists]
[表名] charset utf8;

查询和更新指令构成了 SQL 的 DML 部分:
SELECT – 从数据库表中获取数据
UPDATE – 更新数据库表中的数据
DELETE – 从数据库表中删除数据
INSERT INTO – 向数据库表中插入数据

或:

SQL 的数据定义语言 (DDL)
部分使我们有能力创建或删除表格。我们也可以定义索引(键),规定表之间的链接,以及施加表间的约束。
SQL 中最重要的 DDL 语句:
CREATE DATABASE – 创建新数据库
ALTER DATABASE – 修改数据库
DROP DATABASE – 删除数据库

CREATE TABLE 
– 创建新表
ALTER TABLE – 变更(改变)数据库表
DROP TABLE – 删除表

CREATE INDEX 
– 创建索引(搜索键)
DROP INDEX – 删除索引
ALTER INDEX –修改索引

mysql> create
table if not exists student(name varchar(10),gender varchar(10),number
varchar(10),age int ) charset utf8;

 

# if not
exists:如果表名不存在,那么创建,否则不执行代码

4.库操作

表选项:字符集:chart 具体字符集;保证表中数据存储的字符集

4.1 创建数据库
注意:创建数据库之前要先连接Mysql服务器
命令:
create database
<数据库名>

例1:建立一个名为xhkdb的数据库
   mysql> create database xhkdb;

校对集:collate 具体校对集;

例2:创建数据库并分配用户
①CREATE DATABASE 数据库名;
②GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON 数据库名.*
TO 数据库名@localhost IDENTIFIED BY ‘
密码’;
③SET PASSWORD FOR ‘数据库名’@’localhost’ =
OLD_PASSWORD(‘密码‘);

存储引擎:engine 具体的存储引擎(innodb 和myisam)

依次执行3个命令完成数据库创建。注意:中文
“密码”和“数据库”是户自己需要设置的。

(二)查看表

4.2 显示数据库
命令:show databases   (注意:最后有个s)
mysql> show databases;
注意:为了不再显示的时候乱码,要修改数据库默认编码。以下以GBK编码页面为例进行说明:
1、修改MYSQL的配置文件:my.ini里面修改default-character-set=gbk
2、代码运行时修改:
 
 ①Java代码:jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=gbk
   ②PHP代码:header(“Content-Type:text/html;charset=gb2312”);
   ③C语言代码:int mysql_set_character_set( MYSQL * mysql, char *
csname);
该函数用于为当前连接设置默认的字符集。字符串csname指定了1个有效的字符集名称。连接校对成为字符集的默认校对。该函数的工作方式与SET
NAMES语句类似,但它还能设置mysql- >
charset的值,从而影响了由mysql_real_escape_string() 设置的字符集。

1.查看所有表

4.3 删除数据库
命令:drop database
<数据库名>
例如:删除名为 xhkdb的数据库
mysql> drop database xhkdb;
例子1:删除一个已经确定存在的数据库
   mysql> drop database drop_database;
   Query OK, 0 rows affected (0.00 sec)
例子2:删除一个不确定存在的数据库
   mysql> drop database drop_database;
   ERROR 1008 (HY000): Can’t drop database ‘drop_database’; database
doesn’t exist
      //发生错误,不能删除’drop_database’数据库,该数据库不存在。
   mysql> drop database if exists drop_database;
   Query OK, 0 rows affected, 1 warning (0.00
sec)//产生一个警告说明此数据库不存在
   mysql> create database drop_database;
   Query OK, 1 row affected (0.00 sec)
   mysql> drop database if exists drop_database;//if exists
判断数据库是否存在,不存在也不产生错误
   Query OK, 0 rows affected (0.00 sec)

例:mysql> show tables;

4.4 连接数据库
命令: use
 <数据库名>
例如:如果xhkdb数据库存在,尝试存取它:
   mysql> use xhkdb;
屏幕提示:Database changed
use
语句可以通告MySQL把db_name数据库作为默认(当前)数据库使用,用于后续语句。该数据库保持为默认数据库,直到语段的结尾,或者直到发布一个不同的USE语句:
   mysql> USE db1;
   mysql> SELECT COUNT(*) FROM mytable;   # selects from
db1.mytable
   mysql> USE db2;
   mysql> SELECT COUNT(*) FROM mytable;   # selects from
db2.mytable
使用USE语句为一个特定的当前的数据库做标记,不会阻碍您访问其它数据库中的表。下面的例子可以从db1数据库访问作者表,并从db2数据库访问编辑表:
   mysql> USE db1;
   mysql> SELECT author_name,editor_name FROM author,db2.editor
       ->        WHERE author.editor_id = db2.editor.editor_id;
USE语句被设立出来,用于与Sybase相兼容。
有些网友问到,连接以后怎么退出。其实,不用退出来,use 数据库后,使用show
databases就能查询所有数据库,如果想跳到其他数据库,用
   use 其他数据库名字
就可以了。

2.查看部分表:模糊匹配:mysql> show tables like
‘pattern’;

4.5 当前选择的数据库
命令:mysql> select database();
MySQL中SELECT命令类似于其他编程语言里的print或者write,你可以用它来显示一个字符串、数字、数学表达式的结果等等。如何使用MySQL中SELECT命令的特殊功能?
1.显示MYSQL的版本
mysql> select version(); 
+———————–+ 
| version()             | 
+———————–+ 
| 6.0.4-alpha-community | 
+———————–+ 
1 row in set (0.02 sec) 

#
%:表示匹配多个字符 # _:表示匹配单个字符

4.5 修改数据库

3.查看表创建语句

alter
database  数据库名
用于更改数据库的全局特性。这些特性储存在数据库目录中的db.opt文件中

例:mysql> show create table
student;

create
database <数据库名>  增

drop database <数据库名>     删
alter <数据库名>                      改
show
databases                      查

select database();
use 数据库名

例:mysql> show create table student g
#g 等同于;

 

图片 6

 

例:mysql> show create table student G
#将查到的结构旋转90度

 

图片 7

5.表结构操作

4.查看表结构:查看表中的字段信息

1.创建表
**
create table
**mysql> create table yuangong(
    -> id int unsigned not null auto_increment,
    -> firstname varchar(25) not null,
    -> lastname varchar(25) not null,
    -> email varchar(45) not null,
    -> phone varchar(10) not null,
    -> primary key(id));
Query OK, 0 rows affected (0.03 sec)

mysql> desc [表名]; mysql> describe [表名]; show columns from [表名];

 

图片 8

create table(
字段1名 数据类型  约束条件,
字段2名 数据类型   约束条件,
字段3名 数据类型   约束条件,
[index(字段名)]**

**);

图片 9

 

5.修改数据表

无论当前是否在使用目标数据库,都可以创建表,只要在表名前面加上目标数据库即可。例如:
mysql>create table 数据库名. 表名(
)

表本身存在,还包含字段:表的修改分为两个部分;修改表本身和修改字段,表本身可以修改;表名和表选项

在sql语句中注意“约束的概念":

5-1.修改表名:

1.实体完整性约束(主键``--唯一且非空)`` primary key``()

mysql> rename table [老表名] to [新表名];

``违约处理:``No action``(拒绝执行)

图片 10

 

5-2.修改表选项:字符集

2.参照完整性约束(外键约束)foregin ``key``() ``references tableName(filedName) [``on delete``|``update casecade | ``no action``]

mysql> alter table [表名] charset = utf8;

``违约处理:级联更新或拒绝执行

图片 11

 

6.修改字段

3.用户自定义完整性约束 非空、唯一、核对(``not null``,``unique``,``check``短语)

字段操作很多;新增,修改,重名,删除

``违约处理:拒绝执行

6-1.新增字段

 

例:mysql> alter table [表名]add [字段名] [数据类型列属性] [位置];

4.默认值   default "默认值"

#
位置:字段名可以存放表中的任意位置

 

#
first :第一个位置,after :在哪个字段之后:alter 字段名;默认的是在最后一个字段之后

CREATE
TABLE Person
(
 EmployeeEducationTypeID int
NOT NULL PRIMARY KEY,
 EmployeeID
int NOT NULL,
 EducationTypeID
int NULL,
 GPA
numeric(4,3) NOT NULL CHECK (GPA>2.5 AND
GPA<=4.0)
)
———–在列上直接定义CHECK约束

例:mysql> alter table my_student add colum
id int first;

 

图片 12

2.复制表 复制一个表命名为yuangong2
mysql> *create 新表  select
from   库 . 表;

#注:可分为多行,mysql会自动寻找分号:只认分号为语句结束符。

**

6-2 修改字段

3.查看表
3.1表
mysql>show tables;
3.2表结构
mysql> describe 表名;
也可以
mysql> show columns in 表名;

alter
table [表名] modify [字段名] [数据类型] [属性] [位置];

mysql>show create table 表名;
 mysql>show full columns
from 表名;
 

例:mysql> alter table my_student modify
number char(10) after id;

4.删除表
drop
[temporary] table [if exists] 表名 [, tbl_name, …]

图片 13

 

6-3 重命名字段

5.改表结构

alter
table [表名] change [旧字段] [新字段名] 数据类型 [属性] [位置]

修改表名: mysql> alter table 原表名 rename
新表名

增加一个字段:
mysql>alter table  表名 add  字段名
数据类型 约束 [first|after 列名];

例:修改学生表中的gender 字段为 sex

删除一个字段:
mysql>**alter table  表名 drop
字段名;

mysql> alter
table my_student change gender sex varchar(10);

修改字段的名称及类型:(不论是否改变字段类型,一定要重新指定该字段的类型)
mysql>alter table 表名 change  原字段名  字段新名 新数据类型
新约束条件;**

图片 14

修改字段类型: mysql>alter table 表名  modify 
原字段名 新数据类型 新约束条件;

  1. 删除字段

修改字段排序: mysql>alter table
表名  modify  
原字段名
新数据类型 first | after  字段名2;

alter
table [表名] drop [字段名];

6.创建临时表      有的时候,当工作在非常大的表上时,可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录保存到一个临时表可能更快一些,然后对这些临时表进行查询操作。可以通过使用temporary关键字和create
table语句来实现。

例:mysql> alter table my_student drop age;

 mysql> create temporary table
临时表名 select firstname, lastname from 表名;

#如果表中存在数据,删除表会删除字段中的全部数据,且删除不可逆需备份好在做删除

临时表的创建与其它表一样,只是它们存储在操作系统指定的临时目录中。临时表将在你连接MySQL期间存在,当你断开时,MySQL将自动删除表并释放所有的内存空间;当然了,你也可以手动的使用drop
table命令删除临时表。

图片 15

 
增:create  
删:drop
改:alter
查:select     show     describe

8.删除数据表

 

drop
table [表名 1] ,[表名2] ..; #可以一次性删除多张表,数据库不能多个删除

 

例:mysql> drop table class;
#删除数据表也会删除数据库对应的文件夹下的文件(与存储引擎有关),且删除不可逆需谨慎

6.表内容操作
1.增加数据(插入)

图片 16

**insert 语句一次插入整条数据:
   insert 表名
values(‘字段1值’ , ‘字段2值’ , ‘字段3值’ , ‘字段n值’);

— 数据操作

insert
语句插入几个字段数据:
   insert 表名(‘字段2’ ,
‘字段3’ )  values(‘字段2值’ , ‘字段3值’ );**

1.1 新增数据

insert 语句一次插入多条数据:
  
insert 表名 values(‘字段1值’ , ‘字段2值’ ),(‘字段1值’ , ‘字段2值’
);

方案1.给权标字段插入数据,不需要指定字段列表;要求数据的值出现的顺序必须与表中设计的字段出现的顺序一致;凡是非数值数据,都需要使用引号(建议单引号)包裹

使用insert…select语句插入从其他表选择的行
   insert
表名(col1,col2) select col3,col4 from tbl_name2;

insert
into [表名] values[(值列表1),(值列表2)]; #可以一次性插入多条记录

2.删除数据
     删除整个表记录
       delete from 表名    (慎用!慎用!一定看清楚)

例:mysql> insert into my_student
values(1,’itcast0001′,’jim’,’male’),
(2,’itcast0002′,’hanmeimei’,’famale’);

    
删除一条数据
       delete from 表名 where 字段名=值;    (
一次最少删除一行记录,不能只删除一行的一个字段记录)

图片 17

 

方案2.给部分字段插入数据,需选定字段列表;字段列表出现的顺序与字段的顺序无关,但值列表的顺序必须与选定的字段的顺序一致。

3.更新数据
     更改一个字段的所有值
       update 表名 set 
字段=值                  (一定!一定!看清楚!更改该字段的所有记录值)

insert
into [表名] [字段列表] values[(值列表1),(列表2)];

     更改单条记录的一个字段值
       update 表名  set  字段=值 where 
字段=值 

例:mysql> insert into my_student
(number,sex,name,id)
values(‘itcast0003′,’male’,’tom’,3),(‘itcast0004′,’female’,’lily’,4);

     更改单条记录的多个字段值
       update 表名 set
字段1=值1, 字段2=值2  where
字段=值

图片 18

 

1.2 查看数据

4.查询数据
       =  !=  >=   <= 
 in()     between 值and值    or     
and           like  _  
%

select
*/字段列表 from 表名 where [条件]; #星号代表所有也可选定字段列表,也可不跟条件

     输出表全部记录
       select * from 表名

例1:mysql> select * from my_student;
#查看所有数据

     输出某几个字段值
       select   字段1,字段2,字段3  from 表名 

图片 19

    
查询不重复的记录

       select  distinct
字段1,字段2,字段3 from 表名

例子2:mysql> select id,number,sex,name from
my_student where id = 1; #查看my_student id为1的数据

 

图片 20

条件查询
      
select * from 表名 where 
字段名 (条件运算符)  值
        
条件运算符:=   !=   >=   <=    
in()
在列表内的值        between 
值and值  
范围内的值
        select * from TableName where id=2;
        select * from TableName where id>2;
        select * from TableName where id<2;     
        select * from TableName where id in(2,5,8);   
        select * from TableName where id between  2 and 8;
           以上运算符条件查询只能用在
数值匹配上

1.3 更新数据

 

update
[表名] set [字段] = [值] where [条件]; #where 可有可无,但建议都有where 要不就更新全部

逻辑运算符
       or    and
        select * from TableName where id<2  or  gz=100;        (符合一个条件即可)
        select * from TableName where id<2  and  gz=100;     (要符合两个条件)

mysql> update
my_student set sex = ‘female’ where name = ‘jim’;

 

图片 21

模糊查询
       通配符    like     _  匹配单个字符       %匹配n个字符
       select  * from yuangong  where 字段名  like ‘_b_’;
       select * from yuangong   where 字段名 like ‘b__’;
       select * from yuangong   where 字段名 like ‘%b’;

# 如果没有真正的数据更新不会成功。

正则表达式
   regexp    ^   $  
.

图片 22

        含有某个字符组(其中含有hello即可)
                      select * from yuangong where 字段名
regexp ‘hello’;
       ^  以什么开头
                      select * from yuangong where 字段名 regexp
‘^b’;

1.4 删除数据

       **$  以什么结尾
                      *select
from yuangong where 字段名  regexp ‘b$’;
      
.   匹配任意个字符
                     
select * from yuangong where 字段名 regexp
‘b.’; 
查询以b开头的
       [0~9]  匹配所有数字
                     
select * from
yuangong where 字段名 regexp ‘1[1-2]’; 
查询第一个数字为1 
第二个数字为1到2
      [a~z] 匹配所有字幕
**
                      select *
from yuangong where 字段名 regexp ‘1[a-e]’;**查第一个为1
第二个数字为a到e的字符串

delete
from [表名] where [条件]; #建议加条件以防止全部删除

 

mysql> delete
from my_student where sex = ‘male’; #删除需谨慎

四则运算
          +   –    *   
/

图片 23

                select 
name,字段1 * 字段2   from 表名;
                    select  字段1*12   from  表名  where 
name=”mike”;
                    select 字段1*字段2  as  别名 from 表名 where
name=”mike”;

— 中文数据问题

 

中文数据问题本质是字符集问题,计算机只识别二进制,而人类更多只识别符号,需要有个二进制与字符的对应关系(字符集)

内置聚合函数
算数函数
             min()      max()    avg()    
sum()     count()

案例:mysql> insert into my_student
values(5,’itcast0005′,’张越’,’男’); #插入中文数据

          
select  min(money)  from  people;  字段最小值
           select  max(money) from people;   字段最大值
           select  avg(money)  from people;   字段平均值
           select  sum(money) from people;    获得字段的和
            select count(*) from people;              总行数
            select count(字段名) from people;      字段行数

图片 24

 

如上客户端向服务器插入中文数据不成功

排序操作 order by 
         
order by    默认从低到高,desc从高到低
          select *from 表名 order by
字段1,字段2 [desc];

原因:xD5xC5xD4xBD 代表的是中文’张越’ 在当前编码(字符集)下对应的二进制编码转换成的十六进制:两个汉字等于四个字节(JBK)。报错的原因是服务器没有识别对应的四个字节,服务器认为数据是utf8(一个汉字有三个字节),读取三个字节转换成汉字(失败),剩余的再读三个字节(不够),最终导致失败.

limit显示设置
       
limit
      
select *from 表名 order by
字段1 limit 4;    
显示前四行
       **select *from 表名
order by 字段1 limit 2,4;    **
显示3到5行(从0开始计数)

所有的数据库服务器认为的一些特性都是通过服务器端的变量来保存;系统先读取自己的变量,看看应该怎样表现。

 

查看服务器识别哪些字符集

 

mysql> show
character set; #查看所有字符集

分组查询 group by
         group by
              select子句中的列名必须为分组列或列函数
                  select 后面的所有列中,没有使用聚合函数的列,必须出现在
group by后面
         select 部门,avg(工资) from 员工表 group by 部门;
每个部门平均工资
           SELECT DEPT, MAX(SALARY) AS MAXIMUM FROM STAFF GROUP BY
DEPT;
每个部门最高工资
           select 部门,count(*) from 员工表 group by 部门;
每个部门人数

图片 25

 

mysql> show variables like
‘character_set%’; #查看服务器默认的对外处理的字符集

having结果筛选
       
having             
从已经查询出的结果缓存中,再次查询
                    把 HAVING 加入 SQL
的原因是,WHERE 无法应用于合计函数,而如果没有 HAVING,
                         就无法测试结果条件。
          select name,sum(工资)  from table_name GROUP BY name  HAVING
sum(工资)>1000 ;
         select  地区, SUM(人口), SUM(面积) FROM 表 GROUP BY 地区
having  sum(面积)>1000;

图片 26

  

问题解析:客户端数据智能是GBK ,而服务器认为是UTF8,告诉服务器默认的接收字符集为JBK。

增:insert   
删:delete
改:update 查:select

mysql> set character_set_client = gbk;
#修改服务器认为的客户端的字符集为GBK

 

图片 27

 

插入中文效果:

7.索引操作

图片 28

        
MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍。
      
  唯一性索引,这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。
         主键是一种唯一非空性索引,但它必须指定为“PRIMARY
KEY”。主键一般在创建表的时候指定

但查看数据,依然乱码,原因:数据来源是服务器而解析数据是客户端(客户端只识别GBK,而客户端给的是utf8 所以乱码)

 

图片 29

1.创建索引
      **
   create index 索引名 on
表名(字段名(数据类型));                   普通索引          create unique index 索引名 on
表名(字段名(数据类型));    ** 唯一索引

解决方案:修改服务器给客户端的数据字符集为GBK

2.在表结构中添加索引
         alter table 表名 add INDEX [索引名] (字段名);
         alter table 表名 add UNIQUE [索引名] (字段名);
        altertable 表名 add  primary key(字段名);  

mysql> set
character_set_results = gbk;

3.创建表时添加索引
       **
create table 表名(
              id int,
              name varchar(),
              age int,
             index (id),
              );
       **create table 表名(
            id int,
            name varchar(),
            age int,
            UNIQUE
[索引名] (字段名)
             );
    
create table 表名(
            id int,
           name varchar(),
           age int,
           primary key  (字段名)
           );**

图片 30

4.查询表中索引
        show  index  from 表名;

查看数据效果

5.删除索引
        drop index 索引名 on 表名;

图片 31

 

set
变量 = 值
;修改只是会话级别(当前客户端,当次连接有效,关闭失效)退出登陆效果如下

 

图片 32

 

设置服务器对客户端的字符集的认识:可以使用快捷方式:set names [字符集]

 

mysql> set names gbk; #等价于三步骤(character_set_client,character_set_results,character_set_connection;)

 

图片 33

connection
连接层:是字符集转变的中间者,如果统一了效率更高,不统一也没问题。

— 校队集问题

校队集:数据比较的方式

校队集有三种格式:

_bin:binary,二进制比较,取出二进制位,一位一位的比较,区分大小写

_cs:case
sensitive,大小写敏感,区分大小写

_ci:case insensitice,大小写不敏感,不区分大小写

mysql> show collation; #查看数据库所支持的校队集,校对集的应用:只有当数据产生比较的时候校对集才会生效,对比:使用utf8 的_bin 和_ci 来验证效果。

创建表使用不同的校队集

mysql> create
table my_collate_bin(name char(1)) charset utf8 collate
utf8_bin;

mysql> create
table my_collate_ci(name char(1)) charset utf8 collate
utf8_general_ci;

1.创建不同校队集的

图片 34

2.插入数据

mysql> insert
into my_collate_bin values(‘a’),(‘A’),(‘B’),(‘B’);

mysql> insert
into my_collate_ci values(‘a’),(‘A’),(‘B’),(‘B’);

图片 35

3.比较:根据某个字段进行排序:order by 字段名 [asc|desc];asc 升序,desc 降序,默认升序

mysql> select *
from my_collate_bin order by name; #排序查找

mysql> select *
from my_collate_ci order by name; #排序查找

图片 36

校队集必须再没有数据之前声明好,如果有了数据再进行的话那么修改无效。

mysql> alter
table my_collate_ci collate = utf8_bin;

图片 37

— 乱码问题

web 乱码问题

动态网站有三部分构成:浏览器,apache 服务器(php) 数据库服务器,三个部分都有自己的字符集(中文);数据需要再三个部分之间来回传递:很容易产生乱码

解决方案:统一编码(三码合一),但实际上不可能因为浏览器是用户管理(根本不可能控制),但又必须解决此类问题主要靠PHP

图片 38