数据库

1.DELETE语句: DELETE FROM table1 WHERE name='Linc' (无需指定删对象,它会删除一行对象)

2.UPDATE语句: UPDATE table1 SET name='Linc' WHERE age=15 (使用SET关键字,他可以改变一列值)

3.使用运算表达式: UPDATE table1 SET price=price+1 WHERE name='Linc' (是price全部加1)

4.设置Primary Key(主键): CREATE TABLE table1
(
    id INT NOT NULL,
    name VARCHAR(20) default NULL,
    age INT default NULL,
    PRIMARY KEY (id)
)

  规则:1.PK不能为NULL
       2.插入新纪录必须指定PK
       3.PK不可被修改
       4.PK必须独一无二.用于识别出每条记录

5.关键字AUTO_INCREMENT: CREATE TABLE table1
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) default NULL,
    age INT default NULL,
    PRIMARY KEY (id)
)
  用于是PK id每插入一条新纪录就自动+1

6.DESCRIBE命令: DESCRIBE table1 (显示table1的表格属性)

7.SHOW命令: SHOW TABLES (显示当前数据库的所有表)
            SHOW DATABASES (显示所有数据库)

            SHOW CREATE TABLE table1 (显示创建table1时的命令)

8.USE命令: USE test (切换到test数据库)

9.DROP TABLE命令: DROP TABLE table1 (删除table1表格)
  DROP DATABASE my_database (删除my_database数据库)

10.ALTER命令: ALTER TABLE table1
      ADD COLUMN name VARCHAR(20) NOT NULL, (给原有表加入新列并设置为主键)
      ADD PRIMARY KEY (name);

              ALTER TABLE table1
      ADD COLUMN name VARCHAR(20) NOT NULL FIRST(SECOND THIRD...); (将name属性加到第一列的位置)

      ADD COLUMN name VARCHAR(20) NOT NULL BEFORE(AFTER) age; (将name属性加到age之前或者之后)

      ALTER TABLE table1
      RENAME TO test_table; (将表重命名)

      ALTER TABLE test_table
      CHANGE COLUMN name test_id INT NOT NULL, (修改列名name为test_id)
      ADD PRIMARY KEY (test_id);

      ALTER TABLE test_table
      MODIFY COLUMN test_id VARCHAR(100); (讲test_id数据类型修改为VARCHAR)

      ALTER TABLE test_table
      DROP COLUMN test_id; (删除test_id列)

11.INSERT INTO my_table (location) values ('Donlin\'s Street'); (字符串内单引号用反斜杠)

12.RIGHT()和LEFT()命令: RIGHT(location, 2)返回location从右数开始的2个字符
        LEFT(location, 2)返回location从左数开始的2个字符

13.SUBSTRING_INDEX()命令:SUBSTRING_INDEX(location, ',' 1) (截取location的第一个逗号前的所有内容)

14.UPDATE和取值函数的搭配: UPDATE my_table SET state = RIGHT(location, 2)

15.SQL工具集: SUBSTRING(your_string, start_position, length) 截取字符串的一部分
      UPPER(your_string)和LOWER(your_string)分别将字符串改为大小写
      REVERSE(your_string)反转字符排序
      LTRIM(your_string)和RTRIM(your_string)清除字符串左边或右边的所有空格
      LENGTH(your_string)返回字符串的长度

16.通配符%和_: %匹配多个字符如 WHERE name LIKE 'A%x' 可匹配 Alx 和 Alex
              _匹配一个字符如 WHERE name LIKE 'A_x' 只可匹配 Alx 不可匹配 Alex

17.IN关键字: WHERE name in ('Alex', 'Jack', 'Bob');

18.ORDER关键字: SELECT namne FROM my_table WHERE age=15 ORDER BY name DESC, age ASC; (按name降序,然后按age升序select出结果)

19.SUM()函数,求和.AVG()求均值
   GROUP BY分组操作

mysql> SELECT name, SUM(age) FROM table1 ORDER BY SUM(age) DESC;
+------+----------+
| name | SUM(age) |
+------+----------+
| Jack |       82 |
+------+----------+
1 row in set (0.00 sec)

mysql> SELECT name, SUM(age) FROM table1 GROUP BY name  ORDER BY SUM(age) DESC;
+------+----------+
| name | SUM(age) |
+------+----------+
| Jack |       43 |
| Alex |       20 |
| Bob  |       19 |
+------+----------+
3 rows in set (0.01 sec)

20.MAX()和MIN()函数.分别求最大值和最小值
   COUNT(name)  计算出name列的行数
 
21.DISTINCT关键字: SELCET DISTINCT name FROM table (找出所有不同的name)

22.LIMIT关键字: SELECT name FROM table LIMIT 5(显示5条记录)
LIMIT 1, 5 (从第2条记录开始,显示5条记录)
LIMIT 1, 1 (只显示第二条记录)

23.创建子表并设置外键和约束

CREATE TABLE sub_table (
int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
nick_name VARCHAR(20) NOT NULL,
id_fk INT NOT NULL, (这是来自父表的主键,要将其设为外键)
CONSTRAINT table1_id_fk FOREIGN KEY (id_fk) REFERENCES table1 (id)
指定约束名,指定外键并说明外键来源
);

27.AS关键字,

创建只有一列并填充好数据的表
CREATE TABLE profession AS
  SELECT profession FROM my_contact
  GROUP BY profession ORDER BY profession;

创建有主键id的表并插入profession
CREATE TABLE profession (
  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  profession VARCHAR(20)
) AS
  SELECT profession FROM my_contact
  GROUP BY profession ORDER BY profession;

AS用作别名
1.用别名查询
  SELECT profession AS my_pro FROM my_contact
  GROUP BY my_pro ORDER BY my_pro;

2.创建表别名
  CREATE TABLE profession (
  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  profession VARCHAR(20)
) AS
  SELECT profession FROM my_contact AS mc
  GROUP BY profession ORDER BY profession;
缩写为
  SELECT profession FROM my_contact mc
  GROUP BY profession ORDER BY profession;

28.以SELECT语句填充新表
INSERT INTO table (profession)
  SELECT profession FROM my_contact
  GROUP BY profession ORDER BY profession

29.CROSS JOIN命令(交叉联接)

mysql> SELECT * FROM boys;
+------+
| boy  |
+------+
| Jack |
| Alex |
| Tony |
| Bob  |
| Wade |
+------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM toys;
+---------+
| toy     |
+---------+
| Chicken |
| Duck    |
| Pig     |
| Dog     |
+---------+
4 rows in set (0.00 sec)

mysql>
mysql> SELECT b1.boy, b2.toy FROM boys AS b1 CROSS JOIN toys AS b2; //此为交叉联接命令,其中 CROSS JOIN可省略不写
+------+---------+
| boy  | toy     |
+------+---------+
| Jack | Chicken |
| Jack | Duck    |
| Jack | Pig     |
| Jack | Dog     |
| Alex | Chicken |
| Alex | Duck    |
| Alex | Pig     |
| Alex | Dog     |
| Tony | Chicken |
| Tony | Duck    |
| Tony | Pig     |
| Tony | Dog     |
| Bob  | Chicken |
| Bob  | Duck    |
| Bob  | Pig     |
| Bob  | Dog     |
| Wade | Chicken |
| Wade | Duck    |
| Wade | Pig     |
| Wade | Dog     |
+------+---------+
20 rows in set (0.00 sec)

30.INNER JOIN命令
  SELECT somecolumns
  FROM table1
    INNER JOIN  //内联接
    table2
  ON somecondition; //一个条件表达式(比较运算), ON可改为WHERE

例子
  SELECT boys.boy, toys.toy
    FROM boys INNER JOIN toys
    ON boys.toy_id = toys.toy_id;

31.NATURAL JOIN 命令:当一张表外键和另一张表的主键名相同时使用
   SELECT boys.boy, toys.toy
     FROM boys NATURAL JOIN toys;

32.子查询

SELECT some_column, another_column FROM table1
  WHERE column = (SELECT column FROM table2);

1.非关联子查询
SELECT last_name, first_name FROM my_contacts
  WHERE zip_code = (SELECT zip_code FROM zip_code_table
    WHERE city = 'Beijing");

2.关联子查询与EXIST
SELECT email FROM my_contacts mc
  WHERE EXISTS (SELECT * FROM contact_interest ci
    WHERE mc.contact_id = ci.contact_id)
  AND NOT EXISTS (SELECT * FROM job_current jc
    WHERE mc.contact_id = jc.contact_id)

此处子查询用到了外查询的mc缩写符
  
33.左外联接: LEFT OUTER JOIN

mysql> SELECT * FROM girls;
+---------+-------+--------+
| girl_id | girl  | toy_id |
+---------+-------+--------+
|       1 | Kate  |      1 |
|       2 | Lily  |      3 |
|       3 | Sarah |      3 |
+---------+-------+--------+
3 rows in set (0.00 sec)

mysql> SELECT g.girl, t.toy FROM toys t LEFT OUTER JOIN girls g ON g.toy_id = t.
toy_id;
+-------+---------+
| girl  | toy     |
+-------+---------+
| Kate  | Chicken |
| NULL  | Duck    |
| Lily  | Pig     |
| Sarah | Pig     |
| NULL  | Dog     |
+-------+---------+
5 rows in set (0.00 sec)

将左表中的每条记录与右表中的每条记录相匹配,如果有记录不存在,则用NULL表示


34.自联接

SELECT c1.name, c2.name boss FROM clown c1 INNER JOIN clown c2 ON c1.boss_id = c2.id;
将clown当做两张表使用


35.UNION 关键字

SELECT b.boy name FROM boys b
UNION
SELECT g.girl FROM girls g;

将boy和girl的数据联合查询成一列

规则:1.SELECT语句中的列数量必须一致
     2.SELECT语句表达式和函数要相同
     3.SELECT语句中列数据类型必须是相同或可相互转换的

UNION关键字会自动剔除重复的结果,若要显示重复结果,则使用UNION ALL

36.INTERSECT(交集)与EXCEPT(差集)
使用方法同UNION
INTERSECT返回查询表的交集
EXCEPT返回只出现在第一个查询中而不出现在后面查询的结果

SELECT b.boy name FROM boys b
INTERSECT
SELECT g.girl FROM girls g;

SELECT b.boy name FROM boys b
EXCEPT
SELECT g.girl FROM girls g;

37.使用视图 VIEW

CREATE VIEW boys_list AS
SELECT * FROM BOYS WHERE age = 18 CHECK OPTION;  CHECK OPTION表示以后使用该视图进行INSERT或者UPDATE是必须符合WHERE子句 age = 18.

SELECT * FROM boys_list
相当于输入
SELECT * FROM BOYS;

CREATE VIEW 所选出的结果可视为一张虚拟表,他可以用于INSERT UPDATE语句
例如 INSERT INTO boys_list VALUES ('Jack', 18);

删除视图 DROP VIEW boys_list;
视图就像表,查看它可以使用SHOW TABLES或者DESC


38.SQL事务

START TRANSACTION; 开始事务

SELECT UPDATE INSERT....

COMMIT; 提交事务,执行成功,结束事务

或者

ROLLBACK; 取消START与ROLLBACK之间所有事务的执行,并结束事务