mysql指令与用例

Ubuntu 装mysql:
sudo apt install mysql-server
sudo apt install mysql-client
pip install mysqlclient

声明 statement

CREATE TABLE table_name (
column_1 data_type,
column_2 data_type,
column_3 data_type
);

CREATE TABLE is a clause. Clauses perform specific tasks in SQL. By convention, clauses are written in capital letters. Clauses can also be referred to as commands.
table_name refers to the name of the table that the command is applied to.
(column_1 data_type, column_2 data_type, column_3 data_type) is a parameter. A parameter is a list of columns, data types, or values that are passed to a clause as an argument. Here, the parameter is a list of column names and the associated data type.

建立表 CREATE

CREATE TABLE celebs (
   id INTEGER, 
   name TEXT, 
   age INTEGER
);

.CREATE TABLE is a clause that tells SQL you want to create a new table.
2. celebs is the name of the table.
3. (id INTEGER, name TEXT, age INTEGER) is a list of parameters defining each column, or attribute in the table and its data type:

插入表INSERT

INSERT INTO celebs (id, name, age) 
VALUES (1, 'Justin Bieber', 22);

1. INSERT INTO is a clause that adds the specified row or rows.
2. celebs is the name of the table the row is added to.
3. (id, name, age) is a parameter identifying the columns that data will be inserted into.
4. VALUES is a clause that indicates the data being inserted.

获取SELECT

SELECT name FROM celebs;

插入新的列ALTER

ALTER TABLE celebs 
ADD COLUMN twitter_handle TEXT;

更新信息UPDATE

UPDATE celebs 
SET twitter_handle = '@taylorswift13' 
WHERE id = 4; 

删除信息DELETE

DELETE FROM celebs 
WHERE twitter_handle IS NULL;
``

    DELETE FROM is a clause that lets you delete rows from a table.
    celebs is the name of the table we want to delete rows from.
    WHERE is a clause that lets you select which rows you want to delete. Here we want to delete all of the rows where the twitter_handle column IS NULL.
    IS NULL is a condition in SQL that returns true when the value is NULL and false otherwise.

约束

CREATE TABLE celebs (
   id INTEGER PRIMARY KEY, 
   name TEXT UNIQUE,
   date_of_birth TEXT NOT NULL,
   date_of_death TEXT DEFAULT 'Not Applicable'
);

1. PRIMARY KEY columns can be used to uniquely identify the row. Attempts to insert a row with an identical value to a row already in the table will result in a constraint violation which will not allow you to insert the new row.

2. UNIQUE columns have a different value for every row. This is similar to PRIMARY KEY except a table can have many different UNIQUE columns.

3. NOT NULL columns must have a value. Attempts to insert a row without a value for a NOT NULL column will result in a constraint violation and the new row will not be inserted.

4. DEFAULT columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.

如果我们只关心两个列属性

SELECT column1, column2 
FROM table_name;

案例

SELECT name, genre 
FROM movies;

对获取的数据重新命名属性AS

SELECT name AS 'Titles'
FROM movies;

案例

SELECT name AS '______'
FROM movies;

获取数据 去掉重复的数据DISTINCT

举例

SELECT tools 
FROM inventory;

可能是
tools
hammer
nails
nails
nails

用

SELECT DISTINCT tools 
FROM inventory;

只会有
tools
hammer
nails

选择条件where

where

SELECT *
FROM movies
WHERE imdb_rating > 8;

可以用如下符号

= equal to
!= not equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to

查找like

like

SELECT * 
FROM movies
WHERE name LIKE 'Se_en';

以上案例查找Se_en name 如可以使Seven, 或者Se7en等等
like 2

SELECT * 
FROM movies 
WHERE name LIKE '%man%';

以上案例查找带有Man的name 不分大小写

当查找带有null或者没有null的数据

SELECT name
FROM movies 
WHERE imdb_rating IS NOT NULL;

用 IS NULL或者 IS NOT NULL

获取条件between

获取年份之间， 用例包括1999

SELECT *
FROM movies
WHERE year BETWEEN 1990 AND 1999;

获取开头字母之间 用例不包括‘J’

SELECT *
FROM movies
WHERE name BETWEEN 'A' AND 'J';

并集条件and

案例如下

SELECT * 
FROM movies
WHERE year BETWEEN 1990 AND 1999
   AND genre = 'romance';

或条件 or

SELECT *
FROM movies
WHERE year > 2014
 OR genre = 'action';

排序 order by

SELECT *
FROM movies
ORDER BY name;

按名字A到Z排列

SELECT *
FROM movies
WHERE imdb_rating > 8
ORDER BY year DESC;

按年降序排列

DESC is a keyword used in ORDER BY to sort the results in descending order (high to low or Z-A).

ASC is a keyword used in ORDER BY to sort the results in ascending order (low to high or A-Z).

限制信息数limit

SELECT *
FROM movies
LIMIT 10;

条件查找分类 case when else end

SELECT name,
 CASE
  WHEN imdb_rating > 8 THEN 'Fantastic'
  WHEN imdb_rating > 6 THEN 'Poorly Received'
  ELSE 'Avoid at All Costs'
 END AS 'Review'
FROM movies;

最快计算表中行数count()

SELECT COUNT(*)
FROM table_name;

计算某一列所有值的和 sum()

SELECT SUM(downloads)
FROM fake_apps;

计算某一列最大值 max() 最小值Min（）

SELECT MAX(downloads)
FROM fake_apps;

计算平均值avg()

SELECT AVG(downloads)
FROM fake_apps;

对某一行取值求整 round()

SELECT ROUND(price, 0)
FROM fake_apps;

获取数据分组 group by

SELECT price, COUNT(*) 
FROM fake_apps
group by price;

The GROUP BY statement comes after any WHERE statements, but before ORDER BY or LIMIT.
案例2

SELECT price, downloads, count(*)
FROM fake_apps
where downloads > 20000
group by price;

对分组后进行filter, 用having

SELECT price, 
   ROUND(AVG(downloads)),
   COUNT(*)
FROM fake_apps
GROUP BY price
HAVING COUNT(*)>10;

选择两张表的信息

table_name.column_name

SELECT orders.order_id,
   customers.customer_name
FROM orders
JOIN customers
  ON orders.customer_id = customers.customer_id;

如果只写join 则为inner join 多表中不想同的行则被去掉

如果在表合并时不相同的行保留左表优先，则使用left join

SELECT *
FROM table1
LEFT JOIN table2
ON table1.c2 = table2.c2;

如果只找出并合并两张表相同的部分

SELECT month,
 COUNT(*)
FROM newspaper
CROSS JOIN months
WHERE start_month <= 3 AND end_month >= 3
GROUP BY month;

合并两个表union

SELECT *
FROM table1
UNION
SELECT *
FROM table2;

更新

update agvDoc set finishTIme=“1111-11-11” where startTime=“1111-11-11”;

设置远程链接
https://www.cnblogs.com/lyq-biu/p/10859273.html