# 登录MySQL
$ mysql -u root -p12345612 -h

# 退出MySQL数据库服务器
exit;

1、选择数据库
show databases;
use crashcourse;
show tables;
show grants;   【用来显示授予用户（所有用户或特定用户）的安全权限】
show status;   【用于显示广泛的服务器状态信息】
show columns from 表名; 等同于 describe 表名;
检索单个列
select 列名 from 表名;
注意：MySQL是不区分大小写的，且其中所有的空格都被忽略，所以sql语句可以分成多行
检索多个列
select 列名,列名,列名 from 表名;
检索所有列
select * from 表名;
检索不同的行（去重）
select distinct 列名 from 表名; 【distinct 是关键字,目的是为了去重】
限制输出结果（limit）
(limit 5代表是返回5行结果)
select 列名 from products limit 5;
(limit 5,3代表是从第五行开始返回3行结果，也可写成 limit 3 offset 5【顺序不可错，offset代表从第几行开始】)
select 列名 from products limit 5,3
select 列名 from products limit 3 offset 5;               offset：偏移量】

使用完全限定的表名
select 表名.列名 from products;
select 表名.列名 from 数据库名.表名;

单列排序数据
select prod_name from products order by prod_name（按照prod_name的字母顺序进行排序,（默认是升序））

多列排序数据
select prod_id,prod_price,prod_name from products order by prod_price,prod_name;

指定排序方向
select prod_id,prod_price,prod_name from products order by prod_price desc; 【desc是关键字，意为对列进行降序排序，只应用到直接位于其前面的列名】
select prod_id,prod_price,prod_name from products order by prod_price asc;【asc是关键字，意为对列进行升序排序，默认是升序，可不写】

使用where子句进行过滤
select prod_name,prod_price from products where prod_price ==2.50;

检查单个值
select prod_name,prod_prod_price from products where prod_name = '..';

计算次序
select vend_id,prod_name,prod_price from products where vend_id = 1002 or vend_id=1003 and prod_price >=10;
和
select vend_id,prod_name,prod_price from products where (vend_id = 1002 or vend_id=1003 )and prod_price >=10;

select vend_id,prod_name,prod_price from products where vend_id not in (1002,1003) order by prod_name;
【匹配除vend_id 等于1002和1003之外其他所有的id】

select cust_name from customers where cust_contact is null;
【表示空时只能写成is NULL】

select cust_name,ifnull(cust_contact,0) from customers

select prod_id,prod_name from products where prod_name like 'jet%';【匹配prod_name中所有以jet开头的值】
select prod_id,prod_name from products where prod_name like '%anv%';【匹配prod_name中任何位置含有anv的值】
...

select prod_id,prod_name from products where prod_name like '_arrots';【匹配prod_name中值为Carrots】
select prod_id,prod_name from products where prod_name like '__rrots';【匹配prod_name中值为Carrots】

select prod_name from products where prod_name regexp '1000' order by prod_name;

select prod_name from products where prod_name like '%1000';

select prod_name from products where prod_name regexp '.000' order by prod_name

select prod_name from products where prod_name like '%000';

select prod_name from products where prod_name like '%000';

select prod_name from products where prod_name regexp '1000|2000' order by prod_name;【匹配含1000或者2000】

select prod_name from products where prod_name like '%1000' or prod_name like '%2000';

select prod_name from products where prod_name regexp '[123]' order by prod_name;【[123]定义一组字符，它的意思是匹配1或2或3】

select prod_name from products where prod_name regexp '[1|2|3]';

select prod_name from products where prod_name regexp '1|2|3';【任意匹配所有含有1或者2或者3的值】

select prod_name from products where prod_name regexp '[^123]' order by prod_name;【匹配除含123之外的其他值】

select prod_name from products where prod_name regexp '^[1|2|3]';   【匹配以1或2或3开头的字段】

select prod_name from products where prod_name regexp '[1-3] Ton' order by prod_name;

select vend_name  from vendors where vend_name regexp '\\.';【匹配含.的所有值】

select prod_name from products where prod_name regexp '\\( [0-9] sticks? \\)' order by prod_name;

select prod_name from products where prod_name regexp '[[:digit:]]{4}';  【匹配列值含有任意四个连续数字的列值】

select prod_name from products where prod_name regexp '[0-9][0-9][0-9][0-9]';

select prod_name from products where prod_name regexp '^[0-9\.]';

select prod_name from products where prod_name regexp '^[[:digit:]\.]';

select prod_name from products where prod_name regexp '[0-9\.]';【匹配含数字或者.的值】

select concat(vend_name, vend_id) from vendors order by vend_name;
+----------------------------+
| concat(vend_name, vend_id) |
+----------------------------+
| ACME1003                   |
| Anvils R Us1001            |
| Furball Inc.1004           |
| Jet Set1005                |
| Jouets Et Ours1006         |
| LT Supplies1002            |
+----------------------------+

select concat(vend_name, ' (', vend_id, ')') from vendors order by vend_name;
+---------------------------------------+
| concat(vend_name, ' (', vend_id, ')') |
+---------------------------------------+
| ACME (1003)                           |
| Anvils R Us (1001)                    |
| Furball Inc. (1004)                   |
| Jet Set (1005)                        |
| Jouets Et Ours (1006)                 |
| LT Supplies (1002)                    |
+---------------------------------------+

select concat(vend_name,' (',vend_country,')') from vendors order by vend_name;
+-----------------------------------------+
| concat(vend_name,' (',vend_country,')') |
+-----------------------------------------+
| ACME (USA)                              |
| Anvils R Us (USA)                       |
| Furball Inc. (USA)                      |
| Jet Set (England)                       |
| Jouets Et Ours (France)                 |
| LT Supplies (USA)                       |
+-----------------------------------------+

select concat(rtrim(vend_name), ' (',rtrim(vend_country),')') from vendors order by vend_name;
+--------------------------------------------------------+
| concat(rtrim(vend_name), ' (',rtrim(vend_country),')') |
+--------------------------------------------------------+
| ACME (USA)                                             |
| Anvils R Us (USA)                                      |
| Furball Inc. (USA)                                     |
| Jet Set (England)                                      |
| Jouets Et Ours (France)                                |
| LT Supplies (USA)                                      |
+--------------------------------------------------------+

select concat(vend_name,' (',vend_country,')') as vend_title from vendors order by vend_name;
+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+
【AS关键字要紧跟concat函数后面】

select prod_id,quantity,item_price,quantity*item_price as expand_price from orderitems where order_num = 20005;
+---------+----------+------------+--------------+
| prod_id | quantity | item_price | expand_price |
+---------+----------+------------+--------------+
| ANV01   |       10 |       5.99 |        59.90 |
| ANV02   |        3 |       9.99 |        29.97 |
| TNT2    |        5 |      10.00 |        50.00 |
| FB      |        1 |      10.00 |        10.00 |
+---------+----------+------------+--------------+

select vend_city,locate('ari',vend_city) as locate from vendors;
+-------------+--------+
| vend_city   | locate |
+-------------+--------+
| Southfield  |      0 |
| Anytown     |      0 |
| Los Angeles |      0 |
| New York    |      0 |
| London      |      0 |
| Paris       |      2 |
+-------------+--------+

select vend_city,substr(vend_city,2,3) from vendors where vend_city = 'Paris' ;
+-----------+-----------------------+
| vend_city | substr(vend_city,2,3) |
+-----------+-----------------------+
| Paris     | ari                   |
+-----------+-----------------------+

select cust_id,order_num from orders where date(order_date) = '2005-09-01';

select cust_id,order_num from orders where year(order_date) = 2005 and Month(order_date) =9;
【返回年份为2005年的和月份为9月的值】

select cust_id from orders where time(order_date) = '14:17:00';

select avg(prod_price) as avg_price from products;

select count(*) as num_count from customers;

select count(cust_email) as count_email from customers;

select sum(item_price*quantity) as total_price from orderitems where order_num = 20005

select distinct prood_price from products;

select count(*) as num_items,
    min(prod_price) as price_min,
    max(prod_price) as price_max,
    avg(distinct prod_price) as price_avg
from products;
【返回四列，每列单个值】

select vend_id,count(*) as num_prods from products group by vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

select vend_id,count(*) as num_prods from products;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |        14 |
+---------+-----------+
【返回vend_id列的第一个值，并计算所有vend_id的行数】

select vend_id,count(*) as num_prods from products group by vend_id with rollup;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
|    NULL |        14 |
+---------+-----------+
【with rollup是用来在分组统计数据的基础上再进行统计汇总，即用来得到group by的汇总信息】

select cust_id,count(*) as orders from orders group by cust_id having count(*) >= 2;
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+

 select vend_id,count(*) as num_prods from products where prod_price >= 10 group by vend_id having num_prods >= 2;

select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2');
【处理操作时由里向外进行处理，即先执行内部的查询操作，再执行外部的查询操作】

select cust_name,cust_state,(select count(*) from orders where orders.cust_id = customers.cust_id) as orders from customers order by cust_name ;

select vend_name,prod_name, prod_price from vendors,products where vendors.vend_id = products.vend_id
order by vend_name,prod_name;
【from子句中列出了两个表，通过where子句将列列名一样，要使用完全限定列名】

select vend_name,prod_name,prod_price from vendors,products order by vend_name,prod_name;

select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id;
【这里两个表的联结关系是接在from子句后面的，通过INNER JOIN关键字，只返回两个表中联结字段相等的行，联结条结用特定的ON子句】

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

SELECT
  prod_name,
  vend_name,
  prod_price,
  quantity
FROM
  orderitems
  JOIN products ON products.prod_id = orderitems.prod_id
  JOIN vendors ON products.vend_id = vendors.vend_id
WHERE
  order_num = 20005

SELECT
  cust_name,
  cust_contact
FROM
  customers
WHERE
  cust_id IN (
  SELECT
    cust_id
  FROM
    orders
  WHERE
  order_num IN ( SELECT order_num FROM orderitems WHERE prod_id = 'TNT2' ));

SELECT
  cust_name,
  cust_contact
FROM
  customers
  JOIN orders ON customers.cust_id = orders.cust_id
  JOIN orderitems ON orders.order_num = orderitems.order_num
WHERE
  orderitems.prod_id = 'TNT2'

SELECT
  e.ename,
  e.sal,
  s.grade
FROM
  emp AS e
  JOIN salgrade AS s
  ON e.sal BETWEEN s.losal AND s.hisal

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

SELECT
  c.cust_id,
  o.order_num
FROM
  customers AS c
  LEFT OUTER JOIN orders AS o ON c.cust_id = o.cust_id;

SELECT
  c.cust_i,
  o.order_num
FROM
  customers AS c
  LEFT OUTER JOIN orders AS o ON o.cust_id = c.cust_id;

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 REGEXP '1001|1002';

insert into customers values (NULL,'li shi run','100 main street','los angeles','ca','90046','usa',null,null);
【此例插入一个新客户到customers表。存储到每个列中的数据在values子句中列出，每个列必须提供一个值，若没有具体值，则用NULL替代】

insert into customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
values('li shi run','100 main street','los angeles','ca','90046','usa',null,null);
【此列在表名后的括号里先给出列名，在插入行时，MySQL用values列表中的相应值填入列表的对应项】

insert into customers(cust_id,cust_address,cust_email,cust_name,cust_city,cust_state,cust_zip,cust_country)select cust_id,cust_address,cust_email,cust_name,cust_city,cust_state,cust_zip,cust_country from custnew;
【select语句从custnew表中检索出要插入的值，如果这个表为空，则没有数据插入到customers，如果表中含有数据，则所有数据全插入到customers】

update customers
set cust_email = 'elemer@fudd.com'
where cust_id = 10005;
【update语句以要更新的表的名字开始。set命令用来将新值赋给被更新的列，通过where子句来告知MySQL要更新哪一行】

update customers
set cust_name = 'The Fudds',
  cust_email = 'elmer@qq.com'
where cust_id = 10005;

delete from customers where cust_id = 10006;
【delete form要求指定从表中删除数据的表名。where子句过滤要删除的行】

CREATE TABLE test_creat (
  cust_id int NOT NULL AUTO_INCREMENT,
  cust_name varc NOT NULL,
  cust_address char DEFAULT NULL,
  cust_city char DEFAULT NULL,
  PRIMARY KEY (cust_id)
)ENGINE = InnoDB;
【MySQL实际的表定义（所有列）都在圆括号中。各列之间用逗号分隔
除主键外每列由列名开始，接着是数据类型（例如char数据类型，其长度要在创建时确定），紧接着是是否支持NULL值
表的主键可使用PRIMARY KEY关键字指定】

insert into 创建的表名
values (相关列的数据)

alter table vendors
add vend_phone char(20)
【这条语句在vendors表中增加一个名为vend_phone的列，且必须明确其数据类型】

alter table vendors
drop (column) vend_phone
【这条语句在vendors表中删除一个名为vend_phone的列】

alter table vendors modify vend_zip char(30);

alter tables custnew change cust_zip cust_zips char(25);

alter table test_creat rename to create_test;

rename table create_test to test_new;

select cust_name,cust_contact
from customers,orders,orderitems
where customers.cust_id = orders.cust_id
    and orderitems.order_num = orders.order_num
    and prod_id = 'TNT2';

create view productcustomers as
select cust_name,cust_contact,prod_id
from customers,orders,orderitems
where customers.cust_id = orders.cust_id
  and orderitems.order_num = orders.order_num;

select cust_name,cust_contact
from productcustomers
where prod_id = 'TNT2';
【productcustomers是视图，是一个虚拟的表，它不包含表中应该有的任何列和数据，它包含的是一个相同的查询】

select concat(rtrim(vend_name), ' (', rtrim(vend_country) ,')') as vend_title  from vendors order by vend_name;

create view vendorlocations as
select concat(rtrim(vend_name), ' (', rtrim(vend_country) ,')') as vend_title from vendors order by vend_name;

select * from vendorlocations;

SELECT
  SNAME
FROM
  s
WHERE
  SNAME NOT IN (
  SELECT
    ss.SNAME
  FROM
    s ass
    JOIN sc ON ss.SNO = sc.SNO
    JOIN C ON C.CNO = sc.CNO
  WHERE
    c.CTEACHER = '黎明'
  )

SELECT
  SNAME,
  avg_grade
FROM
  s
  JOIN (
  SELECT
    SNO,
    AVG( SCGRADE ) AS avg_grade
  FROM
    sc
  WHERE
    SNO IN ( SELECT SNO from sc WHERE SCGRADE < 60 GROUP BY SNO HAVING COUNT(*) >= 2 )
  GROUP BY
  SNO
  ) t ON (s.SNO = t.SNO)

SELECT SNAME FROM s
WHERE SNO in (
SELECT
  SNO
FROM
  sc
WHERE
  CNO = 1
  AND SNO IN ( SELECT SNO FROM sc  WHERE CNO = 2))

-- 订单表
CREATE TABLE myorder (
    product_id INT,
    customer_id INT,
    product_name VARCHAR(20),
    customer_name VARCHAR(20),
    PRIMARY KEY (product_id, customer_id)
);

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE product (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    customer_phone VARCHAR(15)
);

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    phone VARCHAR(15)
);

select table_name from information_schema.tables where table_schema='数据库名称' and table_type='base table';
#直接使用下面的查询语句，“数据库名称”替换为你自己的“dbname”即可

select column_name from information_schema.columns where table_schema='数据库名称' and table_name='表名'
#直接使用下面的查询语句，“数据库名称”替换为你自己的“dbname”；“表名”替换为你自己的“table_name”;

select   employers emp ,last_anme lname from   e]]mployee
#等同于 select employers as emp ,last_anme as lanme from employee

show create table 表名

mysql>show variables like'character%';
+--------------------------+-----------------------------------+
| Variable_name            | Value                             |
+--------------------------+-----------------------------------+
| character_set_client     | utf8                              |
| character_set_connection | utf8                              |
| character_set_database   | utf8                              |
| character_set_filesystem | binary                            |
| character_set_results    | utf8                              |
| character_set_server     | utf8                              |
| character_set_system     | utf8                              |
| character_sets_dir       | D:\phpStudy\MySQL\share\charsets\ |
+--------------------------+-----------------------------------+
mysql> show variables like 'collation_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

mysql>create database mydb character set utf-8;