MySQL实践笔记

MySQL实践笔记

安装employee数据库，参考MySQL安装示例employee数据库

---

SQL中View的作用？
参考：Why do you create a View in a database?

down vote
accepted
A view provides several benefits.

1.Views can hide complexity

If you have a query that requires joining several tables, or has complex logic or calculations, you can code all that logic into a view, then select from the view just like you would a table.

2.Views can be used as a security mechanism

A view can select certain columns and/or rows from a table, and permissions set on the view instead of the underlying tables. This allows surfacing only the data that a user needs to see.

3.Views can simplify supporting legacy code

If you need to refactor a table that would break a lot of code, you can replace the table with a view of the same name. The view provides the exact same schema as the original table, while the actual schema has changed. This keeps the legacy code that references the table from breaking, allowing you to change the legacy code at your leisure.

SELECT DISTINCT
SELECT DISTINCT语句用于返回唯一不同的值

Using group by on multiple columns
Using group by on multiple columns

on与where的区别
在MySQL中，join连接中使用on或者where有什么区别吗？
参考In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?

The ON clause defines the relationship between the tables.

The WHERE clause describes which rows you are interested in.

为datetime类型的列设置默认值
请参考How do you set a default value for a MySQL Datetime column?

mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> desc test;
+-------+-------------+------+-----+-------------------+-------+
| Field | Type        | Null | Key | Default           | Extra |
+-------+-------------+------+-----+-------------------+-------+
| str   | varchar(32) | YES  |     | NULL              |       | 
| ts    | timestamp   | NO   |     | CURRENT_TIMESTAMP |       | 
+-------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into test (str) values ("demo");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| str  | ts                  |
+------+---------------------+
| demo | 2008-10-03 22:59:52 | 
+------+---------------------+
1 row in set (0.00 sec)

mysql>

为auto increment设置初始值
请参考How to set initial value and auto increment in MySQL?

MySQL - Setup an auto-incrementing primary key that starts at 1001:

Step 1, create your table:

create table penguins(
  my_id       int(16) auto_increment, 
  skipper     varchar(4000),
  PRIMARY KEY (my_id)
)

Step 2, set the start number for auto increment primary key:

ALTER TABLE penguins AUTO_INCREMENT=1001;

Step 3, insert some rows:

insert into penguins (skipper) values("We need more power!");
insert into penguins (skipper) values("Time to fire up");
insert into penguins (skipper) values("kowalski's nuclear reactor.");

Step 4, interpret the output:

select * from penguins

prints:

'1001', 'We need more power!'
'1002', 'Time to fire up'
'1003', 'kowalski\'s nuclear reactor'

在mysql中将string转为float类型
请参考How can I convert a string to a float in mysql?

UPDATE table SET
latitude = CAST(old_latitude AS DECIMAL(10,6)),
longitude = CAST(old_longitude AS DECIMAL(10,6));

mysql中获取月份
请参考how do I get month from date in mysql

select month(now());