mysql数据库操作(1卡塔 尔(英语:State of Qatar)

 

数据库和表的增删改

1.mysql
数据库备份:

创建数据库

CREATE  DATABASE 数据库名;

# mysql中还可使用如下语句
CREATE SCHEMA 数据库名;

语法:

数据库选择

USE 数据库名;
MYSQLDUMP -uUSER -pPASS  DataBase > Path

创建表

create table students
(
    id int unsigned not null auto_increment primary key,
    name char(8) not null,
    sex char(4) not null,
    age tinyint unsigned not null,
    tel char(13) null default "-"
);

 其中:USER
是用户名,PASS 是密码,DataBase 是数据库名, Path
是数据库备份存储的位置。

重命名表名

RENAME TABLE 旧表名 TO 新表名;

备注:1)执行备份是在系统条件下,而非
mysql 状态下。

删除数据库和表

DROP DATABASE 数据库名;
DROP TABLE 表名;

   2)请使用正确的文件地址,在文件地址中要使用双斜杠
“\\” 来代替单斜杠 “\”。

插入和更新数据

 

使用INSERT插入行

INSERT INTO Customer
VALUES(NULL,
'100 Main Street',
'Los Angles',
'CA');

插入一个新客户到customers表。存储到每个表列中的数据在VALUES子句中给出,对每个列必须提供一个值。虽然这种语法很简单,但并不安全,应该尽量避免使用。
更安全的写法:

INSERT INTO Customer(cust_name,
    cust_address,
    cust_city,
    cust_state)
VALUES('Pep E',
    '100 Main Street',
    'Los Angeles',
    'CA');

这种写法即使表的结构改变,此INSERT语句仍然能正确工作。

2.mysql
数据库的恢复

使用UPDATA更新数据

UPDATA Customers
SET cust_email = 'elmer@fudd.com'
WHERE cust id = 10005;

语法:

使用IGNORE忽略错误

如果用UPDATE语句更新多行,并且在更新这些行中的一行或多行时出一个现错误,则整个UPDATE操作被取消。为即使是发生错误,也继续进行更新,可使用IGNORE关键字:

UPDATE IGNORE customers...
mysql -uUSER -pPASS  DataBase < Path

使用DELETE删除数据

WHERE FROM customers
WHERE cust_id = 10006;

如果想从表中删除所有行,不要使用DELETE。可使用TRUNCATE
TABLE语句,它完成相同的工作,但速度更快(TRUNCATE实际是删除原来的表并重新创建一个表,而不是逐行删除表中的数据)。

 其中:USER
是用户名,PASS 是密码,DataBase 是数据库名, Path
是备份文件存储的位置。

表的查询

 

查询列

SELECT 列名 FROM 表名;

3.实现跨数据库表的内容的复制

查询列中不重复项DISTINCT

SELECT DISTINCT 列名 FROM 表名;

如实现将
db_database3 数据库中的 user 表复制到 database4 中的 user
表,在实现数据库表的内容复制时,需要在每个数据库表前要加上数据库的名称。

限制结果数量LIMIT

SELECT 列名 FROM 表名 LIMIT 数量;

具体语法如:

排序结果

SELECT 列名 FROM 表名 ORDER BY 一个或多个列的名字;

默认为升序排列, 若要降序排序, 通过DESC可指定降序排序.

SELECT 列名 FROM 表名 ORDER BY 一个或多个列的名字 DESC;
 insert into db_database4.user(column1,column2) select column1,column2 from db_database3.user;

过滤结果

SELECT 列名 FROM 表名 WHERE 条件;

//使用LIKE来匹配通配符
SELECT 列名 FROM 表名 WHERE 列名 LIKE 条件;

//使用REGEXP来使用正则表达式
SELECT 列名 FROM 表名 WHERE 列名 REGEXP 条件;

SQL的不等于通过'<>’来表示, 判断NULL通过IS NULL来表示, BETWEEN ..
AND ..或者IN (.., ..)表示范围.
多个条件组合时, AND比OR的优先级要高.
MySQL中正则表达式不区分大小写, 若要区分大小写, 可用REGEXP BINARY.

 备注:1)仍然需要你自己在
db_database4 中建立一个与 db_database3 结构一样的 user 表;

组合查询

利用UNION,组合数条SQL查询结果作为单个查询结果集返回。这些组合查询通常称为并或复合查询.

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <=5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);

     
2)要保证表中字段的数据类型的一致性,否则会导致出入数据的错误。

拼接

 

Concat函数实现拼接

SELECT Concat (列名1, '(', 列名2, ')') FROM 表名 WHERE 条件;

多数DBMS通过+或||来实现拼接, MySQL通过Concat()函数来实现.

4.使用 UNION
ALL 语句批量插入数据

命名列的别名

SELECT Concat (列名1, '(', 列名2, ')') AS 新列名 FROM 表名 WHERE 条件;

通过AS关键字, 讲拼接后的列命名一个别名.

是用 UNION
ALL 语句实现批处理,其语法如下:

子查询

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
                    FROM orderitems
                    where prod_id = 'TNT2');

使用IN来进行子查询

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM orders
        WHERE orders.cust_id = custom.cust_id) AS orders
FROM customers
ORDER BY cust name;
INSERT  tableName  SELECT columnValue,... UNION  ALL SELECT columnValue,......

函数

 1.tableName:要添加数据的数据表。

文本函数

  • Upper() 文本转换为大写
  • Soundex() 寻找读音相近的数据
  • Trim() 删除多余的空格
  • RTrim() 删除右侧多余的空格
  • LTrim() 删除左侧多余的空格

2.columnValue:要添加到数据表中的数据。

日期及时间处理函数

图片 1

例如:往一个
user 表中一次插入多条数据:其中表结构如下:

数值处理函数

图片 2

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(20) | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

聚集函数

图片 3
可在函数中以DISTINCT来仅汇总不同的值

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

 我们使用常规的
insert into 插入语句如下:

分组

mysql>  insert into user values('1','tom');
Query OK, 1 row affected (0.06 sec)

mysql>  insert into user values('2','kill');
Query OK, 1 row affected (0.09 sec)

mysql>  insert into user values('3','bill');
Query OK, 1 row affected (0.09 sec)

在SELECT语句的GROUP BY子句中建立分组

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

 而若使用
union all 语句如下:

通过HAVING来在分组中过滤数据

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
mysql> insert user select '1','jim' union all select '2','kill' union all select '3','bill';

分组使用的注意事项

  • GROUP
    BY子句可以包含任意数目的列。这使得能对分组进行嵌套,为数据分组提供更细致的控制
  • 在建立分组时,指定的所有列都一起计算
  • GROUP
    BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数),
    如果在SELECT中使用表达式,则必须在GROUP BY子句中指定相同的表达式,
    不能使用别名
  • 除聚集计算语句外, SELECT语句中的每个列都必须在GROUP BY子句中给出
  • 如果分组列中具有NULL值,则NULL将作为一个分组返回。如果列中有多行NULL值,它们将分为一组
  • GROUP BY子句必须出现在WHERE子句之后, ORDER BY子句之前
  • 一般在使用GROUP BY子句时,应该也给出ORDER
    BY子句。这是保证数据正确排序的唯一方法

 备注:我们常用的
insert into
语句每次只能实现插入一条语句,这样重复输入往往不方便,这个时候可以选择使用union
all 来替代。

联结

 

使用WHERE创建等值联结

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

 5.更新数据库中的表—其中包括:对表本身结构的更改与对表中数据的更改

使用INNER JOIN创建等值联结

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

1)对表本身结构的更改,使用
alter 语句。

联结多个表

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
    AND orderitems.prod_id = products.prod_id;

语法:

自联结

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的,但有时候处理联结远比处理子查询快得多。

SELECT p1.prod_id, p1.prod_name
FROM products AS p1, producrs AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';

//等价于
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id FROM products
                WHERE prod_id = 'DTNTR');
alter table 表名

自然联结

无论何时对表进行联结,应该至少有一个列出现在不止一个表中(被联结的列)。标准的联结返回所有数据,甚至相同的列多次出现。
自然联结排除多次出现,使每个列只返回一次。

//通配符只对第一个表使用。所有其他列明确列出,所以没有重复的列被检索出来
SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, OI.price
FROM customers AS c, orders AS o, orderitem AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'FB';

 例如:

外部联结

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。例如,可能需要使用联结来完成以下工作:

  • 对每个客户下了多少订单进行计数,包括那些至今尚未下订单的客户
  • 列出所有产品以及订购数量,包括没有人订购的产品

    SELECT customers.cust_id, orders.order_num
    FROM custormers, INNER JOIN orders
    ON customers.cust_id = order.cust_id;
    

    MySQL不支持简化字符=和=的使用,这两种操作符在其他DBMS中是很流行的。

a.追加语法:alter table 表名 
add (column datatype,column datatype,...);
例如:alter table tt_table add image blob; //在tt_table表中添加一个image列用来保存大头照 

b.修改语法:alter table 表名 modify (column datatype,column datatype,...);
例如:alter table tt_table modify job varchar(60);//修改tt_table表的job列,使其长度为60 

c.删除语法:alter table 表名 drop (column);
例如:alter table tt_table drop job; //删除tt_table表中的job列  

d,修改表名:rename table 表名 to 新表名;
例如:rename table tt_table to tt;//修改tt_table表的表名为tt  

e.将tt_table表的字符集修改为utf8
alter table tt_table character set utf8;

f.将tt_table原表中的列名food 属性为varchar(20)修改为sale_food 属性为varchar(40)
alter table tt_table change column food sale_food varchar(40);

 2)对表中数据的更改,使用
update 语句。

语法:

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

例1:我们为
lastname 是 “Wilson” 的人添加 firstname

    UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson' ;

 例2:我们会修改地址(address),并添加城市名称(city):

    UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson';

备注:alter的修改只涉及表结构方面,而不能对其中保存的数据进行修改!掌握这点可以混淆使用
alter 和 update 。

 

6.将数据表清空—–DELETE
语句与TRUNCATE TABLE 语句

1)DELETE
语句

DELETE
语句用于删除表中的行。
语法:

DELETE FROM 表名称 WHERE 列名称 = 值

 例1:删除lastname为wilson的行:

DELETE FROM Person WHERE LastName = 'Wilson'

 例2:删除所有行,可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的:

DELETE FROM table_name

 或者:

DELETE * FROM table_name

 2)TRUNCATE
TABLE 语句

TRUNCATE
TABLE 在功能上与不带 WHERE 子句的 DELETE
语句相同,二者均删除表中的全部行;但 TRUNCATE TABLE 比 DELETE
速度快,而且使用的系统和事物日志资源少。

DELETE
语句每次删除一行,并在事务日志中为所删除的每一行做一项记录;

TRUNCATE
TABLE
则通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放,减少了日志空间的额占用。

TRUNCATE
TABLE 语法:

TRUNCATE TABLE 数据表名

 

 7.聚集函数
SUM 实现数据汇总

sum函数具体语法:

SUM ([ALL|DISTINCT]  expression)

 ①ALL:对所有的值进行聚集函数运算。ALL
是默认值。

②DISTINCT:指定 SUM
返回唯一值的和。

③expression:是常量、列或函数,或者是算术、按位与字符串等运算符的任意组合。如果
expression
是精确数字或近似数字数据类型分类(bit数据类型除外)的表达式,则不允许使用聚集函数和子查询。

例如:统计一个班级语文,数学,英语各科的总成绩:

select sum(math),sum(chiense),sum(english) from tt_table ;

 统计一个班级语文成绩平均分:

select sum(chinese)/count(*) from tt_table;

 注意:a.sum仅对数值起作用,否则会报错。

          
b.对多列求和,逗号不能少。

     
c.在统计过程中,null 值将被忽略。

 

8.聚集函数
AVG 实现计算平均值

AVG函数具体语法:

AVG ([ALL|DISTINCT]  expression)

①ALL:对所有的值进行聚集函数运算。ALL
是默认值。

②DISTINCT:指定 AVG
操作只能使用每个值的唯一实例,而不管该值出现多少次。